SQL Practice Notes

Index

• 0. Why SQL?
• 1. Database Fundamentals
  • 1.1 What is a DBMS?
  • 1.2 Database Types
• 2. Core SQL Commands
• 3. Additional Commands
• 4. NULL Handling & Conditional Logic
• 5. Aggregation Concepts
• 6. Intermediate Query Structures
• 7. SQL JOINS
• 8. Date Time Feature Extraction

---

0. Why SQL?

• Used to query large datasets efficiently (Excel breaks at scale)
• Used across teams: analytics, engineering, product, web
• Works with databases managed by a DBMS (Database Management System)
• Databases live on servers (persistent + accessible)

↑ Back to top

---

1. Database Fundamentals

1.1 What is a DBMS?

A system that: - Stores data - Organizes data - Executes queries efficiently - Manages permissions & performance

---

1.2 Database Types

Relational (Most common in SQL)

• Structured as rows and columns
• Tables linked via Primary Keys and Foreign Keys
• Example systems: PostgreSQL, MySQL, SQL Server

Non-Relational (NoSQL)

1. Key-Value -- dictionary-like structure\
2. Column-based -- data grouped by columns\
3. Graph-based -- focuses on relationships between entities\
4. Document-based -- stores flexible JSON-like documents (e.g., MongoDB)

↑ Back to top

---

2. Core SQL Commands (Level 1)

Basic Query Structure

SELECT column1, column2
FROM table_name
WHERE condition
GROUP BY column
HAVING condition
ORDER BY column ASC;

Command Summary

• SELECT -- choose columns
• FROM -- specify table
• WHERE -- filter rows
• GROUP BY -- aggregate rows
• HAVING -- filter aggregated results
• ORDER BY -- sort results

---

Execution Order (Important)

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. TOP / LIMIT

Note: Columns made using alternate methods like SELECT SUM(COL) as COL1 are not recognized by GROUP BY. ↑ Back to top

---

3. Additional Commands (Level 2)

• SELECT DISTINCT
• TOP / LIMIT
• INSERT INTO
• VALUES
• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• UPDATE
• DELETE
• TRUNCATE
• Logical operators: AND, OR, NOT
• Pattern matching: LIKE

Note: When inserting into specific columns, ensure VALUES match the selected columns.

↑ Back to top

---

4. NULL Handling & Conditional Logic (Level 3)

NULL Handling

• IFNULL(value, replacement) -- replaces NULL (2 arguments only)
• COALESCE(val1, val2, val3, ...) -- returns first non-null value

Returning Floats

Multiply by 1.0 to force float division if needed.

---

CASE Statement

Used for conditional logic inside queries.

CASE
    WHEN condition THEN result
    ELSE result
END

• Used to classify data
• Often used inside SELECT
• Can be used inside aggregates (conditional aggregation)

↑ Back to top

---

5. Aggregation Concepts

Common aggregate functions:

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()

Conditional Aggregation Pattern

SUM(CASE WHEN condition THEN column ELSE 0 END)

Used to segment metrics in a single query.

↑ Back to top

---

6. Intermediate Query Structures

Common Table Expression (CTE)

Used to create intermediate result sets.

WITH intermediate_table AS (
    SELECT ...
    FROM ...
)
SELECT *
FROM intermediate_table;

• Improves readability
• Breaks complex problems into steps
• Exists only for that query

↑ Back to top

---

7. SQL JOINS

• INNER JOIN → Returns only rows where there is a match in both tables (intersection of two tables).
• LEFT JOIN (LEFT OUTER JOIN) → Returns all rows from the left table, and matching rows from the right table. Non-matches on the right become NULL.
• RIGHT JOIN (RIGHT OUTER JOIN) → Returns all rows from the right table, and matching rows from the left table. Non-matches on the left become NULL.
• FULL OUTER JOIN → Returns all rows from both tables. Non-matching rows from either side contain NULL values.
• Join behavior depends on where filtering conditions are placed (ON vs WHERE), especially with outer joins.

---

Basic JOIN Syntax

SELECT *
FROM trades
JOIN users
  ON trades.user_id = users.user_id;

---

Aggregation After JOIN (Example Pattern)

SELECT users.city,
       COUNT(trades.order_id) AS total_orders
FROM trades
INNER JOIN users
  ON trades.user_id = users.user_id
  AND trades.status = 'Completed'
GROUP BY users.city
ORDER BY total_orders DESC
LIMIT 3;

---

LEFT JOIN to Find Missing Matches

SELECT pages.page_id
FROM pages
LEFT JOIN page_likes
  ON pages.page_id = page_likes.page_id
WHERE page_likes.liked_date IS NULL
ORDER BY page_id;

↑ Back to top

8. DATE TIME FEATURE EXTRACTION

1. EXTRACT(YEAR FROM date_column) used for getting specific parts of dates. You can replace YEAR with DAY or MONTH as well.
2. DATE_TRUNC rounds the date down to a specific unit i.e. DATE_TRUNC('month', sent_date) AS truncated_to_month
3. INTERVAL you can modify date strings easily by using INTERVAL. For example sent_date + INTERVAL '2 days' adds 2 days of time in the given timestamp
4. TO_CHAR is used to reformat dates into specific formats. For instance - TO_CHAR(sent_date, 'YYYY-MM-DD HH:MI:SS') AS formatted_iso8601
5. ::DATE or TO_DATE() converts strings into dates.
6. ::TIMESTAMP or TO_TIMESTAMP() converts strings into timestamps.
7. You can also use MIN() and MAX() with dates clubbed with the functions above to solve certain problems.

For example:

SELECT user_id, 
      EXTRACT(DAY FROM (MAX(post_date) - MIN(post_date))) as days_between
FROM posts
WHERE EXTRACT(YEAR FROM post_date) = 2021
GROUP BY user_id
HAVING COUNT(user_id) >= 2

↑ Back to top