SQL Basics and Intermediate

Chapter 1

To install DB Browser for SQLite using Homebrew, follow these steps:

Installation Steps

1. Update Homebrew (optional but recommended):

   brew update

2. Install DB Browser for SQLite:

   brew install --cask db-browser-for-sqlite

3. Verify Installation: Once installed, you can open the app from your Applications folder or by typing:

   open -a "DB Browser for SQLite"

About DB Browser for SQLite

DB Browser for SQLite is a visual tool to help you interact with SQLite databases. It allows you to:

• Create, edit, and browse SQLite databases.

• Run SQL queries in a user-friendly interface.

• Export and import data easily.

Let me know if you encounter any issues!

---

Chapter 2

Here’s how you can perform basic CRUD operations in SQLite directly from the terminal:

---

Step 1: Open SQLite

Run the SQLite command-line interface:

sqlite3 example.db

This creates or opens the database file example.db.

---

Step 2: Create a Table

Execute the following SQL command to create a table:

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT UNIQUE
);

---

Step 3: Insert Data (CREATE)

Insert a record into the table:

INSERT INTO users (name, age, email)
VALUES ('Alice', 30, '[e-mail]');

---

Step 4: Read Data (READ)

Retrieve all data from the table:

SELECT * FROM users;

Output:

1|Alice|30|[e-mail]

---

Step 5: Update Data (UPDATE)

Modify a record in the table:

UPDATE users
SET age = 31
WHERE name = 'Alice';

Verify the change:

SELECT * FROM users;

---

Step 6: Delete Data (DELETE)

Remove a record from the table:

DELETE FROM users
WHERE name = 'Alice';

Verify the deletion:

SELECT * FROM users;

---

Step 7: Exit SQLite

Type the following to exit the SQLite shell:

.exit

---

Summary of Commands

1. Start SQLite: sqlite3 example.db

2. Create Table:

   CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, email TEXT UNIQUE);

3. Insert Data:

   INSERT INTO users (name, age, email) VALUES ('Alice', 30, '[e-mail]');

4. Read Data:

   SELECT * FROM users;

5. Update Data:

   UPDATE users SET age = 31 WHERE name = 'Alice';

6. Delete Data:

   DELETE FROM users WHERE name = 'Alice';

7. Exit: .exit

You’ve now performed CRUD operations in SQLite directly from the terminal!

---

Chapter 3

To install DB Browser for SQLite using Homebrew, follow these steps:

Installation Steps

1. Update Homebrew (optional but recommended):

   brew update

2. Install DB Browser for SQLite:

   brew install --cask db-browser-for-sqlite

3. Verify Installation: Once installed, you can open the app from your Applications folder or by typing:

   open -a "DB Browser for SQLite"

About DB Browser for SQLite

DB Browser for SQLite is a visual tool to help you interact with SQLite databases. It allows you to:

• Create, edit, and browse SQLite databases.

• Run SQL queries in a user-friendly interface.

• Export and import data easily.

---

Chapter 4

Here are 20 SQL commands with slightly more complex operations that you can try in SQLite:

---

1. Add a New Column to an Existing Table

ALTER TABLE users ADD COLUMN phone TEXT;

---

2. Insert Multiple Rows

INSERT INTO users (name, age, email) VALUES 
('Bob', 25, '[e-mail]'),
('Charlie', 28, '[e-mail]'),
('Diana', 22, '[e-mail]');

---

3. Filter Rows with WHERE and AND

SELECT * FROM users WHERE age > 25 AND email LIKE '%example.com';

---

4. Sort Results with ORDER BY

SELECT * FROM users ORDER BY age DESC;

---

5. Limit Results with LIMIT

SELECT * FROM users ORDER BY age DESC LIMIT 2;

---

6. Find Rows with Missing Data (NULL)

SELECT * FROM users WHERE phone IS NULL;

---

7. Update Multiple Columns

UPDATE users SET phone = '123-456-7890', age = 29 WHERE name = 'Charlie';

---

8. Delete Rows Matching a Condition

DELETE FROM users WHERE age < 23;

---

9. Rename a Table

ALTER TABLE users RENAME TO user_data;

---

10. Group Data with GROUP BY

SELECT age, COUNT(*) as count FROM users GROUP BY age;

---

11. Filter Groups with HAVING

SELECT age, COUNT(*) as count FROM users GROUP BY age HAVING count > 1;

---

12. Calculate Aggregate Functions

SELECT AVG(age) as average_age, MAX(age) as oldest, MIN(age) as youngest FROM users;

---

13. Use Subqueries

SELECT * FROM users WHERE age = (SELECT MAX(age) FROM users);

---

14. Join Two Tables (INNER JOIN)

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    total REAL
);

INSERT INTO orders (user_id, total) VALUES (1, 99.99), (2, 49.99);

SELECT users.name, orders.total 
FROM users
INNER JOIN orders ON users.id = orders.user_id;

---

15. Left Join to Include Unmatched Rows

SELECT users.name, orders.total 
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

---

16. Count Rows

SELECT COUNT(*) as user_count FROM users;

---

17. Find Unique Values with DISTINCT

SELECT DISTINCT age FROM users;

---

18. Create a View

CREATE VIEW user_summary AS
SELECT name, age, email FROM users WHERE age > 25;

Query the view:

SELECT * FROM user_summary;

---

19. Use a CASE Statement

SELECT name, 
       CASE 
           WHEN age < 18 THEN 'Minor'
           WHEN age BETWEEN 18 AND 60 THEN 'Adult'
           ELSE 'Senior'
       END AS age_group
FROM users;

---

20. Drop a Table

DROP TABLE orders;

---

These commands are a mix of data manipulation, querying, and database structure management, providing you with a deeper understanding of SQL operations.

---

Chapter 5

Here are 20 more advanced SQL commands that showcase complex queries and operations in SQLite. These commands include advanced joins, subqueries, window functions, and more:

---

1. Self Join

SELECT a.name AS user1, b.name AS user2
FROM users a
INNER JOIN users b ON a.age = b.age AND a.id != b.id;

---

2. Find Duplicate Records

SELECT name, COUNT(*) as count
FROM users
GROUP BY name
HAVING count > 1;

---

3. Recursive Query for Hierarchical Data

WITH RECURSIVE hierarchy(id, name, level) AS (
    SELECT id, name, 1 FROM users WHERE id = 1
    UNION ALL
    SELECT u.id, u.name, h.level + 1
    FROM users u
    JOIN hierarchy h ON u.id = h.id + 1
)
SELECT * FROM hierarchy;

---

4. Correlated Subquery

SELECT name, age
FROM users u
WHERE age > (SELECT AVG(age) FROM users WHERE id != u.id);

---

5. Window Function for Ranking

SELECT name, age, 
       RANK() OVER (ORDER BY age DESC) AS rank
FROM users;

---

6. Cumulative Sum with Window Function

SELECT name, age,
       SUM(age) OVER (ORDER BY age) AS cumulative_age
FROM users;

---

7. Find Users with Maximum Orders

SELECT name
FROM users
WHERE id = (SELECT user_id FROM orders GROUP BY user_id ORDER BY COUNT(*) DESC LIMIT 1);

---

8. Delete Duplicate Rows (Keep One Record)

DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id)
    FROM users
    GROUP BY name, age, email
);

---

9. Find the Second Highest Age

SELECT MAX(age) AS second_highest_age
FROM users
WHERE age < (SELECT MAX(age) FROM users);

---

10. Find Users with No Orders

SELECT name
FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

---

11. Update Using a Subquery

UPDATE users
SET age = age + 1
WHERE id IN (SELECT id FROM users WHERE age < 30);

---

12. Pivot Table Using Conditional Aggregation

SELECT 
    name,
    SUM(CASE WHEN age < 20 THEN 1 ELSE 0 END) AS below_20,
    SUM(CASE WHEN age BETWEEN 20 AND 40 THEN 1 ELSE 0 END) AS between_20_40
FROM users
GROUP BY name;

---

13. Join Three Tables

SELECT u.name, o.total, p.product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN products p ON o.id = p.order_id;

---

14. Find the Most Common Age

SELECT age, COUNT(*) as frequency
FROM users
GROUP BY age
ORDER BY frequency DESC
LIMIT 1;

---

15. Aggregate with Multiple Grouping Levels

SELECT age, COUNT(*) as count
FROM users
GROUP BY age
WITH ROLLUP;

---

16. Insert Data from Another Table

INSERT INTO archived_users (id, name, age, email)
SELECT id, name, age, email
FROM users
WHERE age > 50;

---

17. Find Users Who Made Orders Above a Certain Total

SELECT u.name
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total > 100;

---

18. Full Outer Join Simulation

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

---

19. JSON Data Query

CREATE TABLE json_data (id INTEGER PRIMARY KEY, data JSON);

INSERT INTO json_data (data) VALUES ('{"name": "Alice", "age": 30}');
SELECT json_extract(data, '$.name') AS name, json_extract(data, '$.age') AS age FROM json_data;

---

20. Create a Trigger

CREATE TRIGGER update_timestamp
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
END;

---

These commands provide a range of advanced SQL techniques, including recursive queries, window functions, triggers, and JSON operations. They are ideal for deepening your knowledge of SQL and mastering complex use cases.

---

Chapter 6

Here are 20 more complex SQL commands to expand your knowledge further. These cover advanced techniques like CTEs, indexing, transactions, and more:

---

1. Find the Top 3 Ages

SELECT DISTINCT age
FROM users
ORDER BY age DESC
LIMIT 3;

---

2. Conditional Aggregation

SELECT 
    COUNT(CASE WHEN age < 20 THEN 1 END) AS under_20,
    COUNT(CASE WHEN age BETWEEN 20 AND 30 THEN 1 END) AS between_20_30,
    COUNT(CASE WHEN age > 30 THEN 1 END) AS over_30
FROM users;

---

3. Find All Users with the Same Age as Others

SELECT name, age
FROM users
WHERE age IN (
    SELECT age
    FROM users
    GROUP BY age
    HAVING COUNT(*) > 1
);

---

4. Row Number Using Window Function

SELECT name, age, 
       ROW_NUMBER() OVER (PARTITION BY age ORDER BY name) AS row_num
FROM users;

---

5. Dense Rank Using Window Function

SELECT name, age, 
       DENSE_RANK() OVER (ORDER BY age DESC) AS rank
FROM users;

---

6. NTILE Using Window Function

SELECT name, age, 
       NTILE(4) OVER (ORDER BY age) AS quartile
FROM users;

---

7. Common Table Expression (CTE) for Summing Ages

WITH AgeSums AS (
    SELECT age, COUNT(*) AS count
    FROM users
    GROUP BY age
)
SELECT * FROM AgeSums WHERE count > 1;

---

8. Generate Sequential Numbers

WITH RECURSIVE numbers(x) AS (
    SELECT 1
    UNION ALL
    SELECT x + 1
    FROM numbers
    WHERE x < 10
)
SELECT x FROM numbers;

---

9. Composite Primary Key

CREATE TABLE user_orders (
    user_id INTEGER,
    order_id INTEGER,
    PRIMARY KEY (user_id, order_id)
);

---

10. Create an Index

CREATE INDEX idx_age ON users(age);

---

11. Using Transactions

BEGIN TRANSACTION;
INSERT INTO users (name, age, email) VALUES ('Eve', 35, '[e-mail]');
UPDATE users SET age = 36 WHERE name = 'Eve';
ROLLBACK; -- Use COMMIT to save changes

---

12. Find Users Without Orders Using EXISTS

SELECT name
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

---

13. Calculate Percentage

SELECT name, 
       age,
       ROUND((age * 100.0 / (SELECT MAX(age) FROM users)), 2) AS age_percentage
FROM users;

---

14. Dynamic Group By with CTE

WITH AgeGroups AS (
    SELECT name, 
           CASE 
               WHEN age < 20 THEN 'Under 20'
               WHEN age BETWEEN 20 AND 30 THEN '20-30'
               ELSE 'Above 30'
           END AS age_group
    FROM users
)
SELECT age_group, COUNT(*) AS count
FROM AgeGroups
GROUP BY age_group;

---

15. Find Gaps in a Sequence

SELECT x + 1 AS missing_number
FROM (SELECT DISTINCT age AS x FROM users) a
WHERE NOT EXISTS (
    SELECT 1 FROM users b WHERE b.age = a.x + 1
);

---

16. Create a Stored Procedure Equivalent with Triggers

CREATE TRIGGER log_user_insertion
AFTER INSERT ON users
BEGIN
    INSERT INTO logs (event, created_at) VALUES ('New user added', CURRENT_TIMESTAMP);
END;

---

17. Update Using Joins

UPDATE users
SET age = (SELECT MAX(o.total) FROM orders o WHERE o.user_id = users.id)
WHERE id IN (SELECT user_id FROM orders);

---

18. Generate Running Totals

SELECT name, age, 
       SUM(age) OVER (ORDER BY name) AS running_total
FROM users;

---

19. Delete Rows with Limit

SQLite doesn’t support DELETE ... LIMIT, but you can emulate it:

DELETE FROM users
WHERE id IN (
    SELECT id FROM users ORDER BY age LIMIT 2
);

---

20. Check Query Execution Plan

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE age > 30;

---

These commands showcase advanced SQL concepts like window functions, recursive queries, indexing, transactions, and triggers. They are ideal for diving deeper into SQL's capabilities! Let me know if you need clarification on any of them.

---

Chapter 7

Here are 20 more advanced and complex SQL queries covering scenarios like advanced joins, analytical functions, hierarchical data, and dynamic queries.

---

1. Find Consecutive Records

SELECT a.id, a.name, a.age, b.age AS next_age
FROM users a
JOIN users b ON a.id = b.id - 1;

---

2. Rank Users by Age and Handle Ties

SELECT name, age, 
       RANK() OVER (ORDER BY age DESC) AS rank
FROM users;

---

3. Calculate Rolling Average

SELECT name, age, 
       AVG(age) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_avg
FROM users;

---

4. Generate Sequential Dates

WITH RECURSIVE date_range(start_date) AS (
    SELECT DATE('2024-01-01')
    UNION ALL
    SELECT DATE(start_date, '+1 day')
    FROM date_range
    WHERE start_date < DATE('2024-01-10')
)
SELECT * FROM date_range;

---

5. Find the Longest Consecutive Sequence

WITH consecutive AS (
    SELECT age, 
           age - ROW_NUMBER() OVER (ORDER BY age) AS group_id
    FROM users
)
SELECT MIN(age) AS start_age, MAX(age) AS end_age, COUNT(*) AS length
FROM consecutive
GROUP BY group_id
ORDER BY length DESC
LIMIT 1;

---

6. Identify Overlapping Date Ranges

SELECT a.id, b.id
FROM reservations a
JOIN reservations b 
ON a.start_date < b.end_date AND a.end_date > b.start_date AND a.id != b.id;

---

7. Combine Two Columns into One

SELECT id, name || ' (' || email || ')' AS combined
FROM users;

---

8. Aggregate JSON Data

SELECT json_group_array(json_object('name', name, 'age', age)) AS json_output
FROM users;

---

9. Find the Nth Highest Value

SELECT DISTINCT age
FROM users
ORDER BY age DESC
LIMIT 1 OFFSET 2; -- Finds the 3rd highest value

---

10. Check if Two Tables Have the Same Data

SELECT COUNT(*) AS mismatches
FROM (
    SELECT * FROM table1
    EXCEPT
    SELECT * FROM table2
) AS diff;

---

11. Dynamic Pivot Table

SELECT 
    name, 
    SUM(CASE WHEN age < 20 THEN 1 ELSE 0 END) AS under_20,
    SUM(CASE WHEN age BETWEEN 20 AND 30 THEN 1 ELSE 0 END) AS between_20_30,
    SUM(CASE WHEN age > 30 THEN 1 ELSE 0 END) AS over_30
FROM users
GROUP BY name;

---

12. Find Rows with Greatest N Per Group

WITH ranked AS (
    SELECT name, age, 
           RANK() OVER (PARTITION BY name ORDER BY age DESC) AS rank
    FROM users
)
SELECT name, age
FROM ranked
WHERE rank <= 3;

---

13. Split Strings into Rows

WITH split(value, rest) AS (
    SELECT '', my_string || ',' FROM (SELECT 'a,b,c,d' AS my_string)
    UNION ALL
    SELECT substr(rest, 0, instr(rest, ',')), substr(rest, instr(rest, ',') + 1)
    FROM split
    WHERE rest != ''
)
SELECT value FROM split;

---

14. Recursive Fibonacci Sequence

WITH RECURSIVE fib(a, b) AS (
    SELECT 0, 1
    UNION ALL
    SELECT b, a + b FROM fib WHERE a + b < 100
)
SELECT a AS fibonacci FROM fib;

---

15. Calculate Percentile

SELECT age,
       PERCENT_RANK() OVER (ORDER BY age) AS percentile
FROM users;

---

16. Union with Deduplication

SELECT * FROM users
UNION
SELECT * FROM archived_users;

---

17. Detect Data Drift

SELECT column_name, COUNT(*) AS frequency
FROM pragma_table_info('users')
GROUP BY column_name
HAVING frequency > 1;

---

18. Simulate Full Outer Join

SELECT u.id, u.name, o.order_total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.id, u.name, o.order_total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

---

19. Cohort Analysis

WITH cohort AS (
    SELECT id, 
           DATE(join_date, 'start of month') AS cohort_month
    FROM users
)
SELECT cohort_month, COUNT(*) AS user_count
FROM cohort
GROUP BY cohort_month;

---

20. Find Users With Matching Initials

SELECT a.name AS user1, b.name AS user2
FROM users a
JOIN users b 
ON substr(a.name, 1, 1) = substr(b.name, 1, 1)
AND a.id != b.id;

---

These examples cover a variety of advanced SQL use cases, including recursive queries, dynamic pivots, advanced joins, and analytical functions.

---

Chapter 8

Here are 20 more complex SQL queries that dive even deeper into advanced SQL concepts, such as data manipulation, window functions, advanced subqueries, and dynamic operations.

---

1. Find Users Who Placed Orders on Every Day of a Month

SELECT user_id
FROM orders
WHERE strftime('%Y-%m', order_date) = '2024-11'
GROUP BY user_id
HAVING COUNT(DISTINCT strftime('%Y-%m-%d', order_date)) = (
    SELECT COUNT(DISTINCT strftime('%Y-%m-%d', order_date))
    FROM orders
    WHERE strftime('%Y-%m', order_date) = '2024-11'
);

---

2. Calculate Median Age

SELECT AVG(age) AS median_age
FROM (
    SELECT age
    FROM users
    ORDER BY age
    LIMIT 2 - (SELECT COUNT(*) % 2 FROM users)
    OFFSET (SELECT (COUNT(*) - 1) / 2 FROM users)
);

---

3. Cumulative Percentage

SELECT name, age,
       ROUND(SUM(age) OVER (ORDER BY age) * 100.0 / SUM(age) OVER (), 2) AS cumulative_percentage
FROM users;

---

4. Find the Largest Gap in Ages

SELECT MAX(age - prev_age) AS largest_gap
FROM (
    SELECT age, LAG(age) OVER (ORDER BY age) AS prev_age
    FROM users
) AS gaps
WHERE prev_age IS NOT NULL;

---

5. List All Pairs of Users With the Same Age

SELECT a.name AS user1, b.name AS user2
FROM users a
JOIN users b ON a.age = b.age AND a.id < b.id;

---

6. Find Users with More Than One Email

SELECT user_id
FROM emails
GROUP BY user_id
HAVING COUNT(*) > 1;

---

7. Find the Maximum Order Amount for Each User

SELECT user_id, MAX(total_amount) AS max_order
FROM orders
GROUP BY user_id;

---

8. Filter Outliers Using the Interquartile Range

WITH stats AS (
    SELECT 
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY age) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY age) AS q3
    FROM users
)
SELECT *
FROM users
WHERE age BETWEEN (SELECT q1 - 1.5 * (q3 - q1) FROM stats) AND (SELECT q3 + 1.5 * (q3 - q1) FROM stats);

---

9. Rank Users by Total Order Amount

SELECT user_id, 
       SUM(total_amount) AS total_spent,
       RANK() OVER (ORDER BY SUM(total_amount) DESC) AS rank
FROM orders
GROUP BY user_id;

---

10. Find Consecutive Days with Orders

WITH consecutive_days AS (
    SELECT user_id, order_date,
           JULIANDAY(order_date) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS group_id
    FROM orders
)
SELECT user_id, MIN(order_date) AS start_date, MAX(order_date) AS end_date
FROM consecutive_days
GROUP BY user_id, group_id
HAVING COUNT(*) > 1;

---

11. Find Users Who Have Not Ordered for the Past 30 Days

SELECT name
FROM users
WHERE id NOT IN (
    SELECT user_id
    FROM orders
    WHERE order_date >= DATE('now', '-30 days')
);

---

12. Find Top Spending Users by Month

SELECT strftime('%Y-%m', order_date) AS month, user_id,
       SUM(total_amount) AS total_spent
FROM orders
GROUP BY month, user_id
ORDER BY month, total_spent DESC;

---

13. Find Users Who Share the Same First Name

SELECT a.name AS name1, b.name AS name2
FROM users a
JOIN users b ON substr(a.name, 1, instr(a.name, ' ') - 1) = substr(b.name, 1, instr(b.name, ' ') - 1)
WHERE a.id < b.id;

---

14. Check for Referential Integrity Violations

SELECT *
FROM orders
WHERE user_id NOT IN (SELECT id FROM users);

---

15. Update a Column Based on a Condition

UPDATE users
SET phone = 'Not Provided'
WHERE phone IS NULL;

---

16. Find Orders Where the Total Amount Exceeds the User’s Average

SELECT o.order_id, o.total_amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.total_amount > (SELECT AVG(total_amount) FROM orders WHERE user_id = u.id);

---

17. Generate All Possible Pairs of Users

SELECT a.name AS user1, b.name AS user2
FROM users a
CROSS JOIN users b
WHERE a.id < b.id;

---

18. Find Users Who Made More Than One Order in a Day

SELECT user_id, order_date, COUNT(*) AS order_count
FROM orders
GROUP BY user_id, order_date
HAVING order_count > 1;

---

19. Calculate Time Difference Between Orders

SELECT user_id, 
       order_id, 
       LEAD(order_date) OVER (PARTITION BY user_id ORDER BY order_date) AS next_order_date,
       JULIANDAY(LEAD(order_date) OVER (PARTITION BY user_id ORDER BY order_date)) - JULIANDAY(order_date) AS days_between
FROM orders;

---

20. Dynamic Query with Aggregates

SELECT user_id,
       COUNT(*) AS total_orders,
       SUM(total_amount) AS total_spent,
       AVG(total_amount) AS average_order_value
FROM orders
GROUP BY user_id
HAVING total_spent > 1000;

---

These advanced SQL queries cover a wide range of scenarios and techniques, such as handling time-based data, advanced grouping, filtering outliers, and generating dynamic aggregations.

---