SQL

Some of The Most Important SQL Commands

1. SELECT - extracts data from a database
2. UPDATE - updates data in a database
3. DELETE - deletes data from a database
4. INSERT INTO- inserts new data into a database
5. CREATE DATABASE- creates a new database
6. ALTER DATABASE- modifies a database
7. CREATE TABLE- creates a new table
8. ALTER TABLE- modifies a table
9. DROP TABLE- deletes a table
10. CREATE INDEX- creates an index (search key)
11. DROP INDEX- deletes an index

SYNTAX

SELECT column1, column2, ... FROM table_name;

EXAMPLE

SELECT CustomerName, City FROM Customers;

The SELECT DISTINCT statement is used to return only distinct (different) values.

SYNTAX

SELECT DISTINCT column1, column2, ... FROM table_name;

EXAMPLE

SELECT DISTINCT Country FROM Customers;

COUNT DISTINCT

By using the DISTINCT keyword in a function called COUNT, we can return the number of different countries.

SELECT COUNT(DISTINCT Country) FROM Customers;

Note: The COUNT(DISTINCT column_name) is not supported in Microsoft Access databases. LEARN WHY!!!

Workaround for MS Access:

SELECT Count(*) AS DistinctCountries 
FROM (SELECT DISTINCT Country FROM Customers);

The WHERE clause is used to filter records based on specified conditions.

SYNTAX

SELECT column1, column2 FROM table_name WHERE condition;

EXAMPLE

SELECT * FROM Customers WHERE Country='Mexico';

OPERATORS

Common operators: =, <>, >, <, >=, <=, BETWEEN, LIKE, IN

SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

The ORDER BY keyword sorts the result set in ascending or descending order.

SYNTAX

SELECT column1, column2 
FROM table_name 
ORDER BY column1 [ASC|DESC];

EXAMPLE

SELECT * FROM Customers ORDER BY Country DESC;

MULTIPLE COLUMNS

SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;

The GROUP BY statement groups rows that have the same values into summary rows.

SYNTAX

SELECT column_name(s) 
FROM table_name 
GROUP BY column_name(s);

EXAMPLE

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

WITH HAVING

HAVING filters groups after GROUP BY is applied

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

JOINs combine rows from two or more tables based on related columns.

TYPES

1. INNER JOIN: Returns matching records
2. LEFT JOIN: All left table + matching right
3. RIGHT JOIN: All right table + matching left
4. FULL JOIN: All records when match in either

INNER JOIN EXAMPLE

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

LEFT JOIN EXAMPLE

SELECT Customers.CustomerName, Orders.OrderID 
FROM Customers 
LEFT JOIN Orders 
ON Customers.CustomerID = Orders.CustomerID 
ORDER BY Customers.CustomerName;

The INSERT INTO statement adds new records to a table.

SYNTAX

INSERT INTO table_name (column1, column2) 
VALUES (value1, value2);

EXAMPLE

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

INSERT MULTIPLE ROWS

INSERT INTO Customers (CustomerName, City, Country) 
VALUES 
('Cardinal', 'Stavanger', 'Norway'), 
('Greasy Burger', 'Bergen', 'Norway'), 
('Tasty Tee', 'Oslo', 'Norway');

The UPDATE statement modifies existing records in a table.

SYNTAX

UPDATE table_name 
SET column1 = value1, column2 = value2 
WHERE condition;

EXAMPLE

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

WARNING: Without a WHERE clause, all records will be updated!

UPDATE Customers SET ContactName='Juan'; -- Updates ALL records

The DELETE statement removes existing records from a table.

SYNTAX

DELETE FROM table_name WHERE condition;

EXAMPLE

DELETE FROM Customers 
WHERE CustomerName='Alfreds Futterkiste';

DANGER: Omitting WHERE clause will delete all records!

DELETE FROM Customers; -- Deletes ALL records

TRUNCATE

For deleting all records more efficiently:

TRUNCATE TABLE Customers;

Creates a new table in the database with specified columns and constraints.

SYNTAX

CREATE TABLE table_name 
(column1 datatype constraints, 
column2 datatype constraints, 
...);

EXAMPLE

CREATE TABLE Users 
(UserID INT PRIMARY KEY AUTO_INCREMENT,
Username VARCHAR(50) NOT NULL UNIQUE, 
Email VARCHAR(100) CHECK (Email LIKE '%@%.%'), 
JoinDate DATETIME DEFAULT CURRENT_TIMESTAMP);

TECHNICAL NOTE: Behind the scenes, this creates:

1. Table metadata in the data dictionary
2. Storage allocation (data files, indexes)
3. Primary key index (usually a B+ tree)

Modifies an existing table structure (add/remove columns, change constraints).

SYNTAX

ALTER TABLE table_name
ADD column_name datatype; 
-- or DROP COLUMN column_name;
-- or MODIFY COLUMN column_name new_datatype;

EXAMPLE

ALTER TABLE Customers
ADD Phone VARCHAR(15),
MODIFY COLUMN Address VARCHAR(200);

WARNING: Some ALTER operations may lock the table or rewrite all rows!

Improve query performance by creating optimized data access paths.

SYNTAX

CREATE INDEX index_name ON table_name (column1, column2); 
-- or DROP INDEX index_name;

EXAMPLE

CREATE INDEX idx_customer_country ON Customers(Country);
CREATE UNIQUE INDEX idx_product_code ON Products(ProductCode);

HOW IT WORKS: Most databases use B+ trees for indexes:

1. Leaf nodes contain actual data (clustered) or pointers (non-clustered)
2. Typically 3-4 levels deep for millions of records
3. Maintenance overhead on INSERT/UPDATE/DELETE

Group SQL statements into atomic units of work.

SYNTAX

BEGIN TRANSACTION;
-- SQL statements
COMMIT; 
-- or ROLLBACK;

EXAMPLE

BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 100 WHERE id = 1;
UPDATE Accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

UNDER THE HOOD: Databases implement this using:

1. Write-Ahead Logging (WAL) for durability
2. Locking or MVCC for isolation
3. Transaction ID tracking for atomicity

Precompiled SQL code stored in the database server.

SYNTAX

CREATE PROCEDURE procedure_name(parameters) AS 
BEGIN 
-- SQL statements 
END;

EXAMPLE

CREATE PROCEDURE GetCustomerOrders(IN customer_id INT) AS
BEGIN
  SELECT * FROM Orders WHERE CustomerID = customer_id;
END; --
-- Execute with: CALL GetCustomerOrders(5);

PERFORMANCE BENEFITS:

1. Reduced network traffic (single call vs multiple queries)
2. Execution plan caching
3. Server-side processing

Virtual tables based on the result set of a SQL query.

SYNTAX

CREATE VIEW view_name AS 
SELECT column1, column2 
FROM table_name
WHERE condition;

EXAMPLE

CREATE VIEW ActiveCustomers AS 
SELECT CustomerName, ContactName
FROM Customers WHERE Active = 1;

IMPLEMENTATION DETAILS:

1. Some views are "materialized" (stored physically)
2. Most are "virtual" (recomputed on each access)
3. Can have indexes for performance (materialized views)

Shows the execution plan of a query (critical for optimization).

SYNTAX

EXPLAIN SELECT * FROM table WHERE condition;

EXAMPLE

EXPLAIN SELECT Customers.CustomerName, COUNT(Orders.OrderID) 
FROM Customers 
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID 
GROUP BY Customers.CustomerName;

KEY PLAN ELEMENTS:

1. Access method (index scan, full table scan)
2. Join algorithms (nested loop, hash join, merge join)
3. Estimated rows/cost
4. Sort operations

Critical database maintenance operations (syntax varies by DBMS).

MYSQL EXAMPLE

-- Backup
mysqldump -u username -p database_name > backup.sql;
-- Restore
mysql -u username -p database_name < backup.sql;

POSTGRESQL EXAMPLE

-- Backup
pg_dump -U username -d database_name -f backup.sql;
-- Restore
psql -U username -d database_name -f backup.sql;

TECHNICAL CONSIDERATIONS:

1. Logical vs physical backups
2. Point-in-time recovery (using WAL logs)
3. Hot vs cold backups

SYNTAX

SELECT column1, column2 FROM table1
UNION 
SELECT column1, column2 FROM table2;

EXAMPLE

SELECT city FROM customers
UNION 
SELECT city FROM suppliers;

SYNTAX

SELECT column1, column2 FROM table_name
WHERE column1 = (SELECT column1 FROM table_name WHERE condition);

EXAMPLE

SELECT name FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = 'HR');

SYNTAX

SELECT column1, column2 FROM table_name
LIMIT 10 OFFSET 20;

EXAMPLE

SELECT * FROM employees
LIMIT 5 OFFSET 10;

SYNTAX

SELECT column1, 
    CASE 
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE result3
    END AS alias
FROM table_name;

EXAMPLE

SELECT name, 
    CASE 
        WHEN age > 30 THEN 'Senior'
        ELSE 'Junior'
    END AS experience_level
FROM employees;

SYNTAX

MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.column1 = source.column1
WHEN NOT MATCHED THEN
    INSERT (id, column1) VALUES (source.id, source.column1);

EXAMPLE

MERGE INTO employees AS target
USING new_employees AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.salary = source.salary
WHEN NOT MATCHED THEN
    INSERT (id, salary) VALUES (source.id, source.salary);

SYNTAX

CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
    -- Trigger logic here
END;

EXAMPLE

CREATE TRIGGER salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    -- Update audit log here
END;

SYNTAX

-- No specific syntax for normalization, it's a process:
-- Convert to 1NF: Ensure atomic columns
-- Convert to 2NF: Remove partial dependencies
-- Convert to 3NF: Remove transitive dependencies

EXAMPLE

-- Split address into a separate table (2NF)
CREATE TABLE addresses (
    id INT PRIMARY KEY,
    street VARCHAR,
    city VARCHAR,
    state VARCHAR
);

SYNTAX

CREATE TABLE employees (
    id INT,
    name VARCHAR,
    salary DECIMAL,
    department_id INT
) PARTITION BY RANGE (salary);

EXAMPLE

CREATE TABLE sales (
    sale_id INT,
    amount DECIMAL
) PARTITION BY RANGE (amount);

SYNTAX

-- No specific syntax for replication, usually configured at the DBMS level.

EXAMPLE

-- Set up master-slave replication on MySQL or PostgreSQL

SYNTAX

-- No specific syntax for sharding, usually handled at the application level or by DBMS.

EXAMPLE

-- Shard data across multiple servers based on user region