Learnwizy Technologies Logo

Learnwizy Technologies

Class 28: Advanced SQL & Database Design

In the previous class, we covered the basics of relational databases and fundamental SQL queries. Today, we'll expand our SQL knowledge with more advanced DML operations and, more importantly, dive into the crucial topic of database design, including relationships and normalization.

SQL Data Manipulation Language (DML) - Continued

INSERT INTO: Adding New Rows

Used to add new records to a table.

-- Insert a single new book
INSERT INTO books (title, author, year)
VALUES ('The Lord of the Rings', 'J.R.R. Tolkien', 1954);

-- Insert multiple books in one statement
INSERT INTO books (title, author, year) VALUES
('Pride and Prejudice', 'Jane Austen', 1813),
('To the Lighthouse', 'Virginia Woolf', 1927);

UPDATE: Modifying Existing Rows

Used to modify existing records in a table. Always use a WHERE clause to specify which rows to update; otherwise, all rows will be updated! 

-- Update the year of a specific book
UPDATE books
SET year = 1980
WHERE id = 1;

-- Update multiple columns for a book
UPDATE books
SET title = 'Nineteen Eighty-Four', author = 'George Orwell'
WHERE id = 2;

DELETE FROM: Removing Rows

Used to delete existing records from a table. Again, always use a WHERE clause to specify which rows to delete; otherwise, all rows will be deleted! 

-- Delete a specific book
DELETE FROM books
WHERE id = 3;

-- Delete all books published before 1900
DELETE FROM books
WHERE year < 1900;

Aggregate Functions

Aggregate functions perform a calculation on a set of rows and return a single value.

GROUP BY: Grouping Rows

Used with aggregate functions to group rows that have the same values in specified columns into summary rows. 

-- Count books by author
SELECT author, COUNT(*) AS num_books
FROM books
GROUP BY author;

-- Calculate average price per category
SELECT category, AVG(price) AS average_price
FROM products
GROUP BY category;

HAVING: Filtering Groups

Used to filter groups based on a specified condition. It's like WHERE, but for groups created by GROUP BY. 

-- Find authors who have written more than 1 book
SELECT author, COUNT(*) AS num_books
FROM books
GROUP BY author
HAVING COUNT(*) > 1;

-- Find categories where the average product price is over $500
SELECT category, AVG(price) AS average_price
FROM products
GROUP BY category
HAVING AVG(price) > 500;

Joining Tables

The power of relational databases lies in their ability to establish relationships between tables. Joins are used to combine rows from two or more tables based on a related column between them.

Consider these two tables:

-- Authors Table
CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);

-- Books Table (with a foreign key to authors)
CREATE TABLE books (
    book_id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    publication_year INTEGER,
    author_id INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

-- Sample Data
INSERT INTO authors (name) VALUES ('Douglas Adams'), ('George Orwell'), ('Harper Lee');
INSERT INTO books (title, publication_year, author_id) VALUES
('The Hitchhiker''s Guide to the Galaxy', 1979, 1),
('1984', 1949, 2),
('To Kill a Mockingbird', 1960, 3),
('The Restaurant at the End of the Universe', 1980, 1);

INNER JOIN:

Returns rows when there is a match in both tables. It's the most common type of join. 

-- Get book titles along with their author names
SELECT b.title, a.name AS author_name
FROM books AS b
INNER JOIN authors AS a ON b.author_id = a.author_id;

-- Result:
-- title                                  | author_name
-- ---------------------------------------|-------------
-- The Hitchhiker's Guide to the Galaxy   | Douglas Adams
-- 1984                                   | George Orwell
-- To Kill a Mockingbird                  | Harper Lee
-- The Restaurant at the End of the Universe| Douglas Adams

LEFT JOIN (or LEFT OUTER JOIN):

Returns all rows from the left table, and the matched rows from the right table. If there's no match, NULLs are returned for the right table's columns. 

-- Find all authors and any books they've written (even if they haven't written any)
SELECT a.name AS author_name, b.title
FROM authors AS a
LEFT JOIN books AS b ON a.author_id = b.author_id;

RIGHT JOIN (or RIGHT OUTER JOIN):

Returns all rows from the right table, and the matched rows from the left table. If there's no match, NULLs are returned for the left table's columns. (Less common, as you can often achieve the same with a LEFT JOIN by swapping tables). 

-- Find all books and their authors (even if a book has no author_id or author doesn't exist)
SELECT b.title, a.name AS author_name
FROM authors AS a
RIGHT JOIN books AS b ON a.author_id = b.author_id;

FULL JOIN (or FULL OUTER JOIN):

Returns all rows when there is a match in one of the tables. If there's no match, NULLs are returned for the columns of the table that doesn't have a match. 

-- Get all authors and all books, matching where possible
SELECT a.name AS author_name, b.title
FROM authors AS a
FULL JOIN books AS b ON a.author_id = b.author_id;
SQL JOIN Types

Database Modeling and Design

Database design is the process of creating a detailed data model of a database. A well-designed database is crucial for data integrity, performance, and maintainability.

Entities and Attributes:

Relationships:

How entities are connected to each other.

Entity-Relationship Diagrams (ERDs):

ERDs are visual representations of database schemas. They show entities (tables) and the relationships between them, helping in the design process.

Entity-Relationship Diagram

Database Normalization

Normalization is a process of organizing the columns and tables of a relational database to minimize data redundancy and improve data integrity. It involves breaking down a large table into smaller, related tables.

Indexes

An index is a special lookup table that the database search engine can use to speed up data retrieval. Think of it like an index in a book.

With this deeper understanding of SQL and database design, you're well-equipped to start connecting your Node.js/Express applications to a real relational database. In the next class, we'll do exactly that, moving beyond in-memory data!