1.Basic SQL Commands
- SELECT Data
SELECT column1, column2 FROM table_name WHERE condition;
- Insert Data
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
- Update Data
UPDATE table_name SET column1 = value1 WHERE condition;
- Delete Data
DELETE FROM table_name WHERE condition;
2.Creating and Managing Tables
- Create Table
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... );
- Alter Table (Add Column)
ALTER TABLE table_name ADD column_name datatype;
- Alter Table (Drop Column)
ALTER TABLE table_name DROP COLUMN column_name;
- Drop Table
DROP TABLE table_name;
3.Indexes
- Create Index
CREATE INDEX index_name ON table_name (column_name);
- Drop Index
DROP INDEX index_name ON table_name;
4.Joins
- Inner Join
SELECT columns FROM table1 INNER JOIN table2 ON table1.common_column = table2.common_column;
- Left Join
SELECT columns FROM table1 LEFT JOIN table2 ON table1.common_column = table2.common_column;
- Right Join
SELECT columns FROM table1 RIGHT JOIN table2 ON table1.common_column = table2.common_column;
- Full Join
SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.common_column = table2.common_column;
5.Aggregate Functions
- Count
SELECT COUNT(column_name) FROM table_name;
- Sum
SELECT SUM(column_name) FROM table_name;
- Average
SELECT AVG(column_name) FROM table_name;
- Min/Max
SELECT MIN(column_name), MAX(column_name) FROM table_name;
6.Grouping and Filtering
- Group By
SELECT column1, COUNT(column2) FROM table_name GROUP BY column1;
- Having
SELECT column1, COUNT(column2) FROM table_name GROUP BY column1 HAVING COUNT(column2) > value;
7.Transactions
- Begin Transaction
BEGIN TRANSACTION;
- Commit Transaction
COMMIT;
- Rollback Transaction
ROLLBACK;
8.Stored Procedures
- Create Procedure
CREATE PROCEDURE procedure_name AS BEGIN -- SQL statements END;
- Execute Procedure
EXEC procedure_name;
9.Functions
- User-Defined Function (UDF)
CREATE FUNCTION function_name (@param datatype) RETURNS datatype AS BEGIN -- Function logic RETURN value; END;
10.Views
- Create View
CREATE VIEW view_name AS SELECT columns FROM table_name WHERE condition;
- Drop View
DROP VIEW view_name;
11.Common Functions
- String Functions
LEN(): Get string length.SUBSTRING(): Extract substring.CONCAT(): Concatenate strings.
- Date Functions
GETDATE(): Get current date and time.DATEDIFF(): Calculate date difference.
- Math Functions
ABS(): Absolute value.ROUND(): Round a number.
12.Tips and Best Practices
- Back Up Your Database Regularly.
- Use Transactions for Critical Operations.
- Optimize Queries Using Indexes.
- Regularly Analyze and Update Statistics.