1.

DDL (Data Definition Language)

DDL commands are used to define or modify the "blueprint" or structure of your database
objects (like tables), rather than the data inside them.

• CREATE: Used to create databases and tables.

SQL

CREATE DATABASE sales;

CREATE TABLE stores (

store_id INT,
store_name VARCHAR(200)
);

• Constraints: Rules applied to columns to ensure data integrity (e.g., NOT NULL,
UNIQUE, DEFAULT).

SQL

CREATE TABLE stores_new (

store_id INT UNIQUE,
store_name VARCHAR(200) NOT NULL
);

• DROP vs. TRUNCATE:

o DROP removes the entire table and its structure.

SQL

DROP TABLE stores_new;

o TRUNCATE deletes all the data inside, but keeps the table blueprint intact.

SQL

TRUNCATE TABLE stores;

• ALTER:Used to modify an existing table's structure (e.g., adding or renaming a

column).

SQL

ALTER TABLE stores ADD COLUMN store_location VARCHAR(200);

ALTER TABLE stores RENAME COLUMN store_city TO store_location;

2. Understanding SQL Keys

Keys establish relationships between tables and ensure data is uniquely identifiable.
 • Primary Key: Uniquely identifies a record. Cannot be null. There is only one per
table.
• Foreign Key: A column that links to the Primary Key of another table.

SQL

-- In this example, category_id is a Foreign Key referencing the

categories table.
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category_id INT -- This would reference the 'categories' table
);

• Composite Key: A primary key made by combining multiple columns to create a

unique identifier.

SQL

-- order_id and product_id together make a unique record

CREATE TABLE order_details (
order_id INT,
product_id INT,
PRIMARY KEY (order_id, product_id)
);

3. DQL (Data Query Language) & Filtering

DQL is used to fetch and filter data from your tables.

• SELECT and LIMIT: Fetch specific columns and limit the number of rows returned.

SQL

SELECT customer_id, email FROM dim_customer LIMIT 10;

• WHERE Clause with AND/OR: Filter records based on specific conditions. Tip: Always
use parentheses when mixing AND and OR.

SQL

SELECT * FROM dim_customer

WHERE gender = 'Female'
AND (country = 'France' OR join_date > '2022-01-01');

• LIKE Operator: Used for pattern matching (% means any number of characters).

SQL

-- Finds names starting with 'T' and ending with 'Y' (e.g., Tiffany)
SELECT * FROM dim_customer WHERE first_name LIKE 'T%Y';
 • ORDER BY: Sorts the result set. Ascending (ASC) is default; use DESC for descending.

SQL

SELECT * FROM dim_product ORDER BY unit_price DESC LIMIT 3;

4. Grouping and Aggregation

Grouping squeezes multiple rows into a summary row based on a specific category, allowing
you to run math functions on them.

• GROUP BY with Aggregate Functions (AVG, SUM):

SQL

SELECT category, AVG(unit_price) AS average_price, SUM(unit_price) AS

total_price
FROM dim_product
GROUP BY category;

• HAVING: Used instead of WHERE when you need to filter based on an

aggregated/calculated column.

SQL

SELECT category, AVG(unit_price) AS average_price

FROM dim_product
GROUP BY category
HAVING average_price > 500;

5. SQL Joins
Joins combine rows from two or more tables based on a related column.

Shutterstock
Explore

• Inner Join: Returns only records that match in both tables.

 SQL

SELECT o.order_id, [Link]

FROM orders o
INNER JOIN customers c ON o.customer_id = [Link];

• Left Join: Returns all records from the left table, and the matched records from the
right table (fills with NULL if no match).

SQL

SELECT o.order_id, [Link]

FROM orders o
LEFT JOIN customers c ON o.customer_id = [Link];

• Full Join (via UNION): MySQL doesn't support FULL OUTER JOIN directly, so you
combine a Left Join and a Right Join using UNION.

SQL

SELECT o.order_id, [Link] FROM orders o LEFT JOIN customers c ON

o.customer_id = [Link]
UNION
SELECT o.order_id, [Link] FROM orders o RIGHT JOIN customers c ON
o.customer_id = [Link];

6. DML (Data Manipulation Language)

Used to insert, update, or delete data inside existing tables.

• INSERT: Add new rows.

SQL

INSERT INTO customers VALUES (101, 'Love', 'aa@[Link]');

• UPDATE: Modify existing records (always use WHERE to avoid updating the whole
table).

SQL

UPDATE customers SET name = 'Sam' WHERE id = 101;

• DELETE: Remove specific records.

SQL

DELETE FROM customers WHERE email = 'aa@[Link]';

7. Column Transformations
Applying functions to alter how data is displayed.

• Numeric: Applying math directly.

SQL

SELECT unit_price, (unit_price * 0.90) AS discounted_price FROM

dim_product;

• Date: Extracting date parts or calculating differences.

SQL

SELECT date, YEAR(date) AS date_year, DATEDIFF(UTC_TIMESTAMP(), date)

AS days_passed FROM dim_date;

• String: Combining or altering text.

SQL

SELECT CONCAT_WS(' ', first_name, last_name) AS full_name,

LOWER(city) FROM dim_customer;

• Type Casting: Changing a column's data type (e.g., Number to String) for
compatibility in joins.

SQL

SELECT CAST(customer_key AS CHAR) FROM dim_customer;

8. Conditionals (CASE WHEN)

Create logic-based tags or new columns, similar to IF/ELSE statements.

SQL
SELECT unit_price,
CASE
WHEN unit_price <= 100 THEN 'Affordable'
WHEN unit_price <= 200 THEN 'Normal'
ELSE 'Expensive'
END AS price_category
FROM dim_product;

9. Window Functions
Perform calculations across a set of rows related to the current row, without collapsing them
into a single summary row.
 • Running Totals: Summing values consecutively row by row.

SQL

SELECT launch_date, unit_price,

SUM(unit_price) OVER(ORDER BY launch_date) AS running_total
FROM dim_product;

• Ranking & Partitioning (ROW_NUMBER, DENSE_RANK): Assigning ranks, and resetting

the count for each category using PARTITION BY.

SQL

SELECT category, unit_price,

DENSE_RANK() OVER(PARTITION BY category ORDER BY unit_price
DESC) as ranking
FROM dim_product;

• LAG and LEAD: Fetching values from previous (LAG) or following (LEAD) rows.

SQL

SELECT day_id, temperature,

LAG(temperature, 1) OVER(ORDER BY day_id) AS previous_day_temp
FROM weather;

10. Subqueries and CTEs

Techniques for managing multi-step query logic.

• Subqueries: A query nested inside another query.

SQL

-- Finding products more expensive than the average price

SELECT * FROM dim_product
WHERE unit_price > (SELECT AVG(unit_price) FROM dim_product);

• CTEs (Common Table Expressions): Creates a temporary, named result set using
WITH, making complex queries easier to read.

SQL

WITH avg_price_cte AS (
SELECT AVG(unit_price) as avg_price FROM dim_product
)
SELECT * FROM dim_product, avg_price_cte WHERE unit_price >
avg_price;

11. Views, Stored Procedures, and Functions

Storing logic so you don't have to rewrite code.

• Views: A saved SQL SELECT query that acts as a virtual table.

SQL

CREATE VIEW dedup_view AS

SELECT * FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) as dedup
FROM customers
) subquery WHERE dedup = 1;

-- To use it later:
SELECT * FROM dedup_view;

• Stored Procedures: Reusable code blocks that can execute DML commands (like
inserts) and accept variables/parameters.

SQL

DELIMITER //
CREATE PROCEDURE insert_customer(IN p_id INT, IN p_name VARCHAR(100),
IN p_email VARCHAR(100))
BEGIN
INSERT INTO customers VALUES (p_id, p_name, p_email);
END //
DELIMITER ;

-- To execute:
CALL insert_customer(102, 'Alice', 'alice@[Link]');