SQL Commands Cheat Sheet

SQL (Structured Query Language) is used to manage and query data in relational databases. Below, we cover essential SQL commands with examples using a sample dataset.

Sample Dataset: Customers

---

1. SQL Basics

SQL Intro
SQL, or Structured Query Language, is used to communicate with databases to retrieve, update, insert, and manage data.

SQL Syntax
SQL commands usually follow this structure:

SELECT column1, column2 FROM table_name WHERE condition;

2. Data Retrieval Commands

SQL SELECT
Retrieves data from a database table:

SELECT * FROM Customers;

SQL SELECT DISTINCT
Selects unique values:

SELECT DISTINCT Country FROM Customers;

SQL WHERE
Filters records based on a condition:

SELECT * FROM Customers WHERE Country = 'Mexico';

SQL ORDER BY
Sorts the result set:

SELECT * FROM Customers ORDER BY CustomerName ASC;

3. Logical Operators

SQL AND, OR, NOT
Combines multiple conditions:

SELECT * FROM Customers WHERE Country = 'Germany' AND ContactName = 'Maria Anders';

4. Data Insertion and Modification

SQL INSERT INTO
Inserts new records:

INSERT INTO Customers (CustomerName, ContactName, Country) 
VALUES ('Cardinal', 'Tom B. Erichsen', 'Norway');

SQL UPDATE
Updates existing records:

UPDATE Customers SET ContactName = 'Alfred Schmidt' WHERE CustomerID = 1;

SQL DELETE
Deletes records:

DELETE FROM Customers WHERE CustomerName = 'Cardinal';

5. Null Handling

SQL NULL Values
Checks for NULL values:

SELECT * FROM Customers WHERE ContactName IS NULL;

6. Aggregate Functions

SQL Aggregate Functions (COUNT, SUM, AVG, MIN, MAX)

SELECT COUNT(CustomerID) FROM Customers;
SELECT MIN(CustomerID) FROM Customers;
SELECT MAX(CustomerID) FROM Customers;

7. Filtering and Pattern Matching

SQL LIKE
Filters with patterns:

SELECT * FROM Customers WHERE CustomerName LIKE 'A%';

SQL Wildcards
Used with LIKE for more complex patterns.

SQL IN, BETWEEN
Filters within a set or range:

SELECT * FROM Customers WHERE Country IN ('Germany', 'Sweden');
SELECT * FROM Customers WHERE CustomerID BETWEEN 1 AND 5;

8. Aliases

SQL Aliases
Renames columns or tables:

SELECT CustomerName AS Name, Country AS Location FROM Customers;

9. SQL Joins

SQL INNER JOIN
Returns rows with matching values:

SELECT Orders.OrderID, Customers.CustomerName 
FROM Orders 
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

SQL LEFT JOIN, RIGHT JOIN, FULL JOIN
Fetches all rows from one or both tables based on matching criteria.

10. Grouping and Filtering

SQL GROUP BY, HAVING
Groups rows with the same values:

SELECT Country, COUNT(CustomerID) 
FROM Customers 
GROUP BY Country HAVING COUNT(CustomerID) > 1;

11. Advanced Filtering

SQL EXISTS, ANY, ALL
Used for conditional subqueries:

SELECT CustomerName 
FROM Customers 
WHERE EXISTS (SELECT OrderID FROM Orders WHERE Orders.CustomerID = Customers.CustomerID);

12. Data Transfer

SQL SELECT INTO
Copies data into another table:

SELECT * INTO CustomersBackup FROM Customers;

SQL INSERT INTO SELECT
Copies specific data:

INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers;

13. Conditional Statements

SQL CASE
Implements if-then logic:

SELECT CustomerName, Country, 
CASE WHEN Country = 'Germany' THEN 'Domestic' ELSE 'International' END AS MarketType 
FROM Customers;

14. Functions and Procedures

SQL NULL Functions
Functions like ISNULL, COALESCE handle NULL values.

SQL Stored Procedures
Reusable SQL code blocks to automate tasks.

15. Comments and Operators

SQL Comments
Add -- for single-line comments, /* ... */ for multi-line comments.

SQL Operators
Arithmetic (+, -, *, /) and comparison operators (=, <, >, etc.) are used in calculations and conditions.