------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 📝 CMD 📄 EXPLANATION 💡 EXAMPLE ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 🔍 SELECT Query data from one/more tables SELECT * FROM Employees; ➕ INSERT Add a new record INSERT INTO Employees (Name, City) VALUES ('John', 'London'); 📝 UPDATE Modify existing record(s) UPDATE Employees SET City='Berlin' WHERE Name='John'; ❌ DELETE Remove record(s) DELETE FROM Employees WHERE Name='John'; 🗂 CREATE TABLE Define a new table CREATE TABLE Depts (DeptID INT, DeptName VARCHAR(20)); 🗑 DROP TABLE Delete a table & its data DROP TABLE OldData; ✏️ ALTER TABLE Change table structure ALTER TABLE Employees ADD HireDate DATE; 🔗 JOIN (INNER) Combine rows from 2+ tables SELECT e.Name, d.DeptName FROM Employees e INNER JOIN Depts d ON e.DeptID = d.DeptID; 🔎 WHERE Filter results by a condition SELECT * FROM Products WHERE Price > 50; 🔢 ORDER BY Sort query results SELECT Name FROM Users ORDER BY Name DESC; 📊 GROUP BY Aggregate rows with same value(s) SELECT DeptID, COUNT(*) FROM Employees GROUP BY DeptID; 🧮 COUNT Count rows SELECT COUNT(*) FROM Sales; ➡️ LIMIT / TOP Limit number of returned records SELECT * FROM Orders LIMIT 10; or SELECT TOP 10 * FROM Orders; 🔁 DISTINCT Return only unique values SELECT DISTINCT City FROM Customers; 🗃 CREATE DB Create a new database CREATE DATABASE CompanyDB; 🗑 DROP DB Delete a database DROP DATABASE TestDB; 🔎 LIKE Search pattern in string SELECT * FROM Customers WHERE Name LIKE 'A%'; 🔔 HAVING Filter groups (post aggregation) SELECT DeptID, COUNT(*) FROM Employees GROUP BY DeptID HAVING COUNT(*) > 5; 🔐 PRIMARY KEY Uniquely identify each row CREATE TABLE Users (ID INT PRIMARY KEY, Name VARCHAR(50)); 🔗 FOREIGN KEY Reference a key in another table CREATE TABLE Orders (OrderID INT, UserID INT, FOREIGN KEY (UserID) REFERENCES Users(ID)); ➕ CREATE INDEX Create fast lookup index on a column CREATE INDEX idx_name ON Employees (Name); 🗑 DROP INDEX Remove index DROP INDEX idx_name ON Employees; 👁 CREATE VIEW Virtual table based on a SELECT CREATE VIEW ActiveUsers AS SELECT * FROM Users WHERE Active=1; 🗑 DROP VIEW Remove a view DROP VIEW ActiveUsers; 🛠 EXECUTE PROC Run a stored procedure EXEC GetEmployeeList; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 🆔 SQL Statement 📝 Explanation 💡 Example ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1️⃣ SELECT Retrieve data from table SELECT * FROM Customers; 2️⃣ SELECT DISTINCT Get unique values SELECT DISTINCT City FROM Customers; 3️⃣ SELECT INTO Copy data into new table SELECT * INTO BackupCustomers FROM Customers; 4️⃣ SELECT TOP Limit number of returned rows SELECT TOP 10 * FROM Orders; 5️⃣ FROM Specify table(s) to query SELECT Name FROM Employees; 6️⃣ WHERE Filter rows by condition SELECT * FROM Employees WHERE Age > 30; 7️⃣ AND Combine multiple conditions with AND SELECT * FROM Employees WHERE Age>30 AND City='NY'; 8️⃣ OR Combine multiple conditions with OR SELECT * FROM Employees WHERE Age>30 OR City='NY'; 9️⃣ BETWEEN Filter by range SELECT * FROM Orders WHERE OrderDate BETWEEN '2025-01-01' AND '2025-03-31'; 🔟 LIKE Pattern match strings SELECT * FROM Customers WHERE Name LIKE 'A%'; 1️⃣1️⃣ IN Filter against list of values SELECT * FROM Products WHERE Category IN ('Books','Toys'); 1️⃣2️⃣ IS NULL Check for NULL values SELECT * FROM Employees WHERE ManagerID IS NULL; 1️⃣3️⃣ ORDER BY Sort results SELECT * FROM Employees ORDER BY Salary DESC; 1️⃣4️⃣ GROUP BY Aggregate by one or more columns SELECT DeptID, COUNT(*) FROM Employees GROUP BY DeptID; 1️⃣5️⃣ HAVING Filter grouped records SELECT DeptID, COUNT(*) FROM Employees GROUP BY DeptID HAVING COUNT(*) > 5; 1️⃣6️⃣ JOIN (INNER) Combine rows matching in two tables SELECT * FROM Orders o INNER JOIN Customers c ON o.CustID=c.ID; 1️⃣7️⃣ LEFT JOIN All rows left + matched right rows SELECT * FROM Employees e LEFT JOIN Orders o ON e.ID=o.EmpID; 1️⃣8️⃣ RIGHT JOIN All rows right + matched left rows SELECT * FROM Orders o RIGHT JOIN Employees e ON o.EmpID=e.ID; 1️⃣9️⃣ FULL OUTER JOIN All rows both tables, matched where possible SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.ID=TableB.ID; 2️⃣0️⃣ UNION Combine distinct results from queries SELECT City FROM Customers UNION SELECT City FROM Suppliers; 2️⃣1️⃣ UNION ALL Combine all results including duplicates SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers; 2️⃣2️⃣ INSERT INTO Add new records INSERT INTO Employees (Name, Age) VALUES ('Ann', 28); 2️⃣3️⃣ UPDATE Modify existing records UPDATE Employees SET Age=29 WHERE Name='Ann'; 2️⃣4️⃣ DELETE Remove records DELETE FROM Employees WHERE Age < 20; 2️⃣5️⃣ TRUNCATE TABLE Remove all rows fast TRUNCATE TABLE TempData; 2️⃣6️⃣ CREATE TABLE Define a new table CREATE TABLE Products (ID INT, Name VARCHAR(50)); 2️⃣7️⃣ DROP TABLE Delete table and data DROP TABLE OldProducts; 2️⃣8️⃣ ALTER TABLE Change table structure ALTER TABLE Employees ADD Salary DECIMAL(10,2); 2️⃣9️⃣ CREATE INDEX Create index for faster queries CREATE INDEX idx_name ON Employees(Name); 3️⃣0️⃣ DROP INDEX Delete an index DROP INDEX idx_name ON Employees; 3️⃣1️⃣ CREATE VIEW Virtual table based on query CREATE VIEW ActiveEmployees AS SELECT * FROM Employees WHERE Active=1; 3️⃣2️⃣ DROP VIEW Remove view DROP VIEW ActiveEmployees; 3️⃣3️⃣ CREATE PROCEDURE Store a query-run procedure CREATE PROCEDURE GetAllEmployees AS SELECT * FROM Employees; 3️⃣4️⃣ EXECUTE PROCEDURE Run a stored procedure EXEC GetAllEmployees; 3️⃣5️⃣ BEGIN TRANSACTION Start transaction BEGIN TRANSACTION; 3️⃣6️⃣ COMMIT Save transaction changes COMMIT; 3️⃣7️⃣ ROLLBACK Undo changes in current transaction ROLLBACK; 3️⃣8️⃣ GRANT Give user privileges GRANT SELECT ON Employees TO User1; 3️⃣9️⃣ REVOKE Remove user privileges REVOKE SELECT ON Employees FROM User1; 4️⃣0️⃣ CREATE DATABASE Make a new database CREATE DATABASE CompanyDB; 4️⃣1️⃣ DROP DATABASE Delete a database DROP DATABASE OldDB; 4️⃣2️⃣ USE Select the database in context USE CompanyDB; 4️⃣3️⃣ CASE Conditional logic in queries SELECT Name, CASE WHEN Age>30 THEN 'Senior' ELSE 'Junior' END FROM Employees; 4️⃣4️⃣ COALESCE Return first non-NULL value SELECT COALESCE(Phone, 'N/A') FROM Contacts; 4️⃣5️⃣ CAST Convert data type SELECT CAST(Price AS INT) FROM Products; 4️⃣6️⃣ EXISTS Test for existence of rows SELECT * FROM Employees WHERE EXISTS (SELECT 1 FROM Orders WHERE EmpID=Employees.ID); 4️⃣7️⃣ ANY / SOME Compare with any/some value in list SELECT * FROM Products WHERE Price > ANY (SELECT Price FROM Sales); 4️⃣8️⃣ ALL Compare with all values in list SELECT * FROM Products WHERE Price > ALL (SELECT Price FROM Sales); 4️⃣9️⃣ SUBQUERY Nested query in WHERE/SELECT/etc. SELECT Name FROM Employees WHERE DeptID IN (SELECT ID FROM Departments WHERE Location='NY'); 5️⃣0️⃣ DISTINCT ON PostgreSQL unique on specified column SELECT DISTINCT ON (DeptID) * FROM Employees ORDER BY DeptID, Salary DESC; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ DB TASKS EXAMPLES ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 🛠 Backup DB Backup the database BACKUP DATABASE CompanyDB TO DISK = 'backupfile.bak'; 🔙 Restore DB Restore from backup RESTORE DATABASE CompanyDB FROM DISK = 'backupfile.bak'; 🧑‍💼 Add Column Add new column ALTER TABLE Employees ADD Email VARCHAR(100); 🗑 Remove Column Delete column from table ALTER TABLE Employees DROP COLUMN Email; 🎚 Change Datatype Modify datatype ALTER TABLE Employees ALTER COLUMN Phone VARCHAR(20); 🗂 Create User Add new DB user CREATE USER johndoe WITH PASSWORD = 'abc123'; 🗝 Grant Privilege Give permission to user GRANT SELECT ON TABLE Employees TO johndoe; 🚫 Revoke Privilege Revoke user permission REVOKE SELECT ON TABLE Employees FROM johndoe; 🚫 Drop User Remove user DROP USER johndoe; 🤓 Show Tables List all tables in DB SHOW TABLES; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 🗂 Backup Database BACKUP DATABASE MyDB TO DISK = 'C:\Bkp\MyDB.bak'; Create regular DB backup 💾 Restore Database RESTORE DATABASE MyDB FROM DISK = 'C:\Bkp\MyDB.bak'; Load DB from backup file 🛠 Create Table CREATE TABLE Users (ID INT, Name VARCHAR(50)); Start a new table 🗑 Drop Table DROP TABLE Employees; Remove a table 📝 Modify Table ALTER TABLE Users ADD Email VARCHAR(50); Add/remove/change table columns 🔍 Create Index CREATE INDEX idx_name ON Users (Name); Speed lookup w/index 🗑 Remove Index DROP INDEX idx_name; Remove unneeded index 🚦 Monitor Perf SELECT * FROM sys.dm_exec_query_stats; View query perf stats 📈 Optimize Query EXPLAIN SELECT * FROM Orders; See/tune execution plan 🔒 Create User CREATE USER johndoe IDENTIFIED BY 'pass123'; Add access account 🔑 Grant Privilege GRANT SELECT ON Users TO johndoe; Permit user/table ops 🚫 Revoke Privilege REVOKE INSERT ON Users FROM johndoe; Remove permission 🔑 Enforce Security ALTER USER johndoe WITH LOGIN DISABLED; Disable user account ⏱ Schedule Job -- SQL Agent EXEC msdb.dbo.sp_add_job ... Automate tasks (SQL) ⚙️ Update/Patch ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL=150; Change DB version 📊 Generate Report SELECT Dept, COUNT(*) FROM Employees GROUP BY Dept; Get data/extract reports 📉 Rebuild Index ALTER INDEX ALL ON Users REBUILD; Defrag/reorg indexes 🔄 Replication Setup -- (SQL: sample) sp_addpublication ... Start cluster/sync 🧮 Data Migration INSERT Archive SELECT * FROM Users WHERE Left=1; Move/copy data 🔗 Join Tables SELECT u.Name,o.Id FROM Users u JOIN Orders o ON u.Id=o.Uid; Fetch from multiple tables 📞 Check Connectivity SELECT 1; Test if DB is up 📅 Schedule Event CREATE EVENT ev ON SCHEDULE EVERY 1 DAY DO ...; Automate recurring actions 🌦 Cloud Integration -- CLI: az sql db create ... Link cloud platforms 📂 Manage Storage CHECKPOINT; Force DB writes, manage files 🗄 Archive Data INSERT INTO Archive SELECT * FROM Table WHERE Done=1; Offload cold data 📜 Audit Log Setup CREATE AUDIT ... Track changes/compliance 📝 Manage Constraints ALTER TABLE T ADD CONSTRAINT c CHECK (col>0); Ensure good data 🧾 Manage Views CREATE VIEW v AS SELECT Name FROM Users; Build logical table 🔁 Automate Scripts -- Scripting, SQL Agent or cron jobs Batch SQL tasks 🗄 Table Partition CREATE PARTITION FUNCTION ... Improve big data mgmt 🔒 Data Encryption CREATE DATABASE ENCRYPTION KEY ... Secure at rest 🧩 Schema Versioning -- Tool: Liquibase/Flyway Track changes in scripts 🧑‍🤝‍🧑 Manage Roles CREATE ROLE Reader; GRANT SELECT TO Reader; Group security 📋 Monitor Transactions SELECT * FROM sys.dm_tran_locks; See locks/deadlocks ♻️ Failover/Clustering -- cluster tool, or config Resilience/HA 📦 Manage Procs/Functs CREATE PROC listUsers AS SELECT * FROM Users; Encapsulate logic 🔘 Set Resource Limits ALTER RESOURCE GOVERNOR ... Control usage per user 🎛 DB Tuning Params ALTER DATABASE SCOPED CONFIGURATION ... Change settings 📖 Document DB -- External: ER diagrams, docs Track system/env 📊 Capacity Planning SELECT SUM(size) FROM sysfiles; Forecast growth/usage 🗯 Handle Alerts -- Monitoring tool or SQL Agent creates alerts Proactive error action 🧪 Test Restores RESTORE VERIFYONLY FROM DISK='C:\Bkp\MyDB.bak'; Check backups are OK 🚧 Troubleshoot Issues DBCC CHECKDB; Diagnose/fix problems 🛠 Install Server/Client -- Setup wizard, package mgrs, or CLI install Deploy new DB 🔁 Load Testing -- Tool: HammerDB/JMeter/goose Simulate heavy users 👐 Dev Support GRANT SELECT TO devuser; Help with test data 🔬 Review Exec Plans SET SHOWPLAN_ALL ON; SELECT * ...; Examine/tune cost 🧼 Clean Up Objects DROP TABLE old_t; DROP INDEX idx_old; Remove unused items 🌐 Network Security -- Set firewall; GRANT CONNECT ON ENDPOINT ... Control remote access ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ STORED PROCEDURES EXAMPLES ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 🛠 Create Proc Make a procedure CREATE PROCEDURE GetEmployees AS SELECT * FROM Employees; 🏃 Exec Proc Run a procedure EXEC GetEmployees; 🛠 Proc with Param Proc with argument CREATE PROCEDURE GetByDept @DeptID INT AS SELECT * FROM Employees WHERE DeptID = @DeptID; 🏃 Exec Proc Param Run proc with param EXEC GetByDept 2; 🛠 Proc Insert Insert inside proc CREATE PROCEDURE InsertEmp @Name NVARCHAR(50) AS INSERT INTO Employees(Name) VALUES (@Name); 🏃 Exec Insert Proc Run insert proc EXEC InsertEmp 'Alice'; 🛠 Proc Update Update inside proc CREATE PROCEDURE UpdateDept @ID INT, @Dept NVARCHAR(20) AS UPDATE Departments SET DeptName=@Dept WHERE DeptID=@ID; 🏃 Exec Update Proc Run update proc EXEC UpdateDept 2, 'HR'; 🛠 Delete via Proc Delete row via proc CREATE PROCEDURE DeleteOld AS DELETE FROM Employees WHERE HireDate<'2020-01-01'; 🏃 Exec Delete Proc Run delete proc EXEC DeleteOld; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------