Day 5: Advanced SELECT Queries and JOINs

Today we'll explore more powerful query techniques and learn how to combine data from multiple tables using JOINs.

Understanding Relationships

One-to-Many Relationship

Let's create a realistic scenario:

-- Authors table (One)
CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    country VARCHAR(50)
);

-- Books table (Many)
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INTEGER REFERENCES authors(author_id),
    price DECIMAL(10, 2),
    published_year INTEGER
);

Insert Sample Data

INSERT INTO authors (name, country) VALUES
    ('J.K. Rowling', 'UK'),
    ('George Orwell', 'UK'),
    ('Haruki Murakami', 'Japan'),
    ('Gabriel García Márquez', 'Colombia');

INSERT INTO books (title, author_id, price, published_year) VALUES
    ('Harry Potter 1', 1, 19.99, 1997),
    ('Harry Potter 2', 1, 21.99, 1998),
    ('1984', 2, 15.99, 1949),
    ('Animal Farm', 2, 12.99, 1945),
    ('Norwegian Wood', 3, 18.99, 1987),
    ('One Hundred Years of Solitude', 4, 16.99, 1967);

JOIN Operations

INNER JOIN

Returns only matching records from both tables:

SELECT 
    books.title,
    authors.name AS author_name,
    books.price
FROM books
INNER JOIN authors ON books.author_id = authors.author_id;

LEFT JOIN (LEFT OUTER JOIN)

Returns all records from left table, matching records from right:

-- Get all authors, even those without books
SELECT 
    authors.name,
    books.title
FROM authors
LEFT JOIN books ON authors.author_id = books.author_id;

RIGHT JOIN

Returns all records from right table, matching from left:

SELECT 
    authors.name,
    books.title
FROM books
RIGHT JOIN authors ON books.author_id = authors.author_id;

FULL OUTER JOIN

Returns all records when there's a match in either table:

SELECT 
    authors.name,
    books.title
FROM authors
FULL OUTER JOIN books ON authors.author_id = books.author_id;

Advanced Filtering

BETWEEN

SELECT * FROM books 
WHERE published_year BETWEEN 1980 AND 2000;

IN

SELECT * FROM books 
WHERE author_id IN (1, 2, 3);

-- With subquery
SELECT * FROM books
WHERE author_id IN (
    SELECT author_id FROM authors WHERE country = 'UK'
);

NOT IN

SELECT * FROM authors
WHERE country NOT IN ('UK', 'USA');

IS NULL / IS NOT NULL

SELECT * FROM books WHERE price IS NULL;
SELECT * FROM books WHERE price IS NOT NULL;

Aggregate Functions with JOINs

-- Count books per author
SELECT 
    authors.name,
    COUNT(books.book_id) as book_count
FROM authors
LEFT JOIN books ON authors.author_id = books.author_id
GROUP BY authors.author_id, authors.name;

-- Total revenue per author
SELECT 
    authors.name,
    SUM(books.price) as total_value
FROM authors
INNER JOIN books ON authors.author_id = books.author_id
GROUP BY authors.author_id, authors.name
ORDER BY total_value DESC;

HAVING Clause

Filter grouped results (WHERE filters before grouping, HAVING after):

-- Authors with more than 1 book
SELECT 
    authors.name,
    COUNT(books.book_id) as book_count
FROM authors
INNER JOIN books ON authors.author_id = books.author_id
GROUP BY authors.author_id, authors.name
HAVING COUNT(books.book_id) > 1;

Subqueries

In WHERE clause

SELECT * FROM books
WHERE price > (SELECT AVG(price) FROM books);

In FROM clause

SELECT avg_price.category, avg_price.average
FROM (
    SELECT author_id, AVG(price) as average
    FROM books
    GROUP BY author_id
) AS avg_price;

CASE Statements

SELECT 
    title,
    price,
    CASE 
        WHEN price < 15 THEN 'Budget'
        WHEN price BETWEEN 15 AND 20 THEN 'Mid-range'
        ELSE 'Premium'
    END AS price_category
FROM books;

String Functions

-- Concatenation
SELECT 
    CONCAT(name, ' from ', country) as author_info
FROM authors;

-- Uppercase/Lowercase
SELECT UPPER(title), LOWER(title) FROM books;

-- Substring
SELECT SUBSTRING(title, 1, 10) FROM books;

-- Length
SELECT title, LENGTH(title) FROM books;

Date Functions

-- Current date and time
SELECT CURRENT_DATE, CURRENT_TIME, NOW();

-- Date arithmetic
SELECT NOW() + INTERVAL '7 days';
SELECT NOW() - INTERVAL '1 month';

-- Extract parts
SELECT 
    EXTRACT(YEAR FROM NOW()),
    EXTRACT(MONTH FROM NOW()),
    EXTRACT(DAY FROM NOW());

DISTINCT

-- Get unique countries
SELECT DISTINCT country FROM authors;

-- Count unique countries
SELECT COUNT(DISTINCT country) FROM authors;

EXISTS

-- Authors who have written books
SELECT name FROM authors a
WHERE EXISTS (
    SELECT 1 FROM books b 
    WHERE b.author_id = a.author_id
);

Real-World Example

-- Complex query: Find authors from UK with books published after 1940,
-- show total books and average price
SELECT 
    a.name,
    a.country,
    COUNT(b.book_id) as total_books,
    ROUND(AVG(b.price), 2) as avg_price,
    STRING_AGG(b.title, ', ') as book_titles
FROM authors a
INNER JOIN books b ON a.author_id = b.author_id
WHERE a.country = 'UK' 
  AND b.published_year > 1940
GROUP BY a.author_id, a.name, a.country
HAVING COUNT(b.book_id) >= 1
ORDER BY avg_price DESC;

---

Tomorrow's Preview: Day 6 - Database Relationships and Foreign Keys

Practice:

1. Create orders and customers tables
2. Write a query to find customers who haven't placed orders
3. Calculate total order value per customer

Share your queries! 🎯