SQL Intro Lesson

Creating tables and adding data (VERY BASIC INTRO)¶

Summary: Explains how to create new tables (e.g., authors) and insert data into them.

SQL Examples:

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

INSERT INTO authors (author_id, first_name, last_name)
VALUES (1, 'George', 'Orwell');

SQL statement rules and conventions¶

Summary: General rules for writing SQL
- Ensuring column and table names match exactly as defined in the database schema
- Use lower case for table names and UPPER CASE for SQL keywords.
- Every statement ends in a ;

Retrieving data using SELECT¶

Retrieving specific columns¶

Summary: This section covers how to retrieve specific columns from tables like books and authors.

SQL Example:

SELECT title, publication_year FROM books;

Filtering rows: the WHERE clause¶

Summary: Describes how to filter rows from books based on conditions.

SQL Example 1:

SELECT title FROM books WHERE publication_year > 2000;

SQL Example 2:

SELECT first_name, last_name FROM authors WHERE first_name = 'David';

SQL Example 3:

SELECT title, publication_year FROM books WHERE publication_year BETWEEN 1950 AND 2000;

Ordering data: the ORDER BY clause¶

Summary: Explains how to order query results from authors.

SQL Example 1:

SELECT last_name, first_name FROM authors ORDER BY last_name ASC;

SQL Example 2:

SELECT title, `condition` FROM bookstore_inventory ORDER BY title ASC, `condition` DESC;

Retrieving unique rows: the DISTINCT keyword¶

Summary: Shows how to retrieve unique publication years from books.

SQL Example:

SELECT DISTINCT publication_year FROM books;

Column expressions¶

Summary: Using expressions to calculate or manipulate columns in queries (e.g., in books).

SQL Example:

SELECT title, (price * 1.1) AS price_with_tax FROM bookstore_inventory;

Literals¶

Summary: Introduces how to use literal values in queries.
SQL Example:
1
SELECT 'All Books' AS description;

Operators and functions¶

Comparison operators¶

Summary: Describes how to compare values (e.g., in awards).

SQL Example:

SELECT * FROM books_awards WHERE year = 2020;

Operator	Description	Example
=	Equal to	`SELECT * FROM books WHERE price = 20;`
<>	Not equal to (can also use `!=`)	`SELECT * FROM books WHERE price <> 20;`
!=	Not equal to (alternative to `<>`)	`SELECT * FROM books WHERE price != 20;`
>	Greater than	`SELECT * FROM books WHERE price > 20;`
<	Less than	`SELECT * FROM books WHERE price < 20;`
>=	Greater than or equal to	`SELECT * FROM books WHERE price >= 20;`
<=	Less than or equal to	`SELECT * FROM books WHERE price <= 20;`
BETWEEN	Between a range of values (inclusive)	`SELECT * FROM books WHERE price BETWEEN 10 AND 20;`
NOT BETWEEN	Not between a range of values	`SELECT * FROM books WHERE price NOT BETWEEN 10 AND 20;`
IN	Matches any value in a list	`SELECT * FROM books WHERE genre IN ('Fiction', 'Fantasy');`
NOT IN	Does not match any value in a list	`SELECT * FROM books WHERE genre NOT IN ('Fiction', 'Fantasy');`
IS NULL	Checks if the value is NULL	`SELECT * FROM books WHERE price IS NULL;`
IS NOT NULL	Checks if the value is not NULL	`SELECT * FROM books WHERE price IS NOT NULL;`
LIKE	Matches a pattern using wildcards (`%` or `_`)	`SELECT * FROM books WHERE title LIKE 'Harry%';`
NOT LIKE	Does not match a pattern	`SELECT * FROM books WHERE title NOT LIKE 'Harry%';`

Mathematics¶

Summary: Covers mathematical operations in queries.

SQL Example:

SELECT price, price * 0.9 AS discounted_price FROM bookstore_inventory;

Common Mathematics Operators

Operator	Description	Example
+	Addition	`SELECT price + 10 FROM books;`
-	Subtraction	`SELECT price - 5 FROM books;`
*	Multiplication	`SELECT price * 1.1 FROM books;`
/	Division	`SELECT price / 2 FROM books;`
%	Modulus (remainder of division)	`SELECT price % 3 FROM books;`
-	Unary minus (negates the value)	`SELECT -price FROM books;`

Common Mathematics Functions (MySQL)

Function	Description	Example
`ABS()`	Returns the absolute (positive) value of a number	`SELECT ABS(-5);`
`CEIL()`	Returns the smallest integer greater than or equal to the number	`SELECT CEIL(4.1);`
`FLOOR()`	Returns the largest integer less than or equal to the number	`SELECT FLOOR(4.9);`
`ROUND()`	Rounds the number to a specified number of decimal places	`SELECT ROUND(4.567, 2);`
`MOD()`	Returns the remainder of division (modulus)	`SELECT MOD(10, 3);`
`POW()`	Returns the result of raising a number to a power	`SELECT POW(2, 3);`
`SQRT()`	Returns the square root of a number	`SELECT SQRT(16);`
`SIGN()`	Returns -1, 0, or 1 depending on the sign of the number	`SELECT SIGN(-5);`
`GREATEST()`	Returns the largest value from a list of arguments	`SELECT GREATEST(1, 5, 3);`
`LEAST()`	Returns the smallest value from a list of arguments	`SELECT LEAST(1, 5, 3);`

Common Statistics Functions

Function	Description	Example
`AVG()`	Returns the average value of a numeric column	`SELECT AVG(price) FROM books;`
`COUNT()`	Returns the number of rows (or non-`NULL` values)	`SELECT COUNT(*) FROM books;`
`MAX()`	Returns the maximum value in a column	`SELECT MAX(price) FROM books;`
`MIN()`	Returns the minimum value in a column	`SELECT MIN(price) FROM books;`
`SUM()`	Returns the sum of all values in a numeric column	`SELECT SUM(price) FROM books;`
`VARIANCE()`	Returns the variance of a numeric column	`SELECT VARIANCE(price) FROM books;`
`STDDEV()`	Returns the standard deviation of a numeric column	`SELECT STDDEV(price) FROM books;`
`GROUP_CONCAT()`	Concatenates values from multiple rows into a single string	`SELECT GROUP_CONCAT(title) FROM books;`
`BIT_COUNT()`	Returns the number of bits set to 1 in the binary representation of a number	`SELECT BIT_COUNT(5);`
`PERCENT_RANK()`	Returns the rank of a value within a result set as a percentage	`SELECT PERCENT_RANK() OVER (ORDER BY price) FROM books;`

Character string operators and functions¶

Summary: Explains string operations, such as concatenating author names.

SQL Example:

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM authors;

Other DBs use other operators for string concatentation, like || or + so modify this example based on your desired RDBMS.

Boolean operators¶

Summary: Describes how to use boolean logic to filter results from books.

SQL Example:

SELECT title FROM bookstore_inventory WHERE `condition` > 'like new' AND price < 4.5;

Operator	Description	Example
`AND`	Returns true if both conditions are true	`SELECT * FROM books WHERE price > 10 AND genre = 'Fiction';`
`OR`	Returns true if at least one condition is true	`SELECT * FROM books WHERE price > 20 OR genre = 'Fiction';`
`NOT`	Reverses the result of the condition	`SELECT * FROM books WHERE NOT genre = 'Fiction';`
`XOR`	Returns true if one condition is true and the other is false	`SELECT * FROM books WHERE price > 15 XOR genre = 'Fantasy';`
`IS TRUE`	Checks if the expression evaluates to true	`SELECT * FROM books WHERE (price > 20) IS TRUE;`
`IS FALSE`	Checks if the expression evaluates to false	`SELECT * FROM books WHERE (price > 20) IS FALSE;`
`IS NULL`	Returns true if the value is `NULL`	`SELECT * FROM books WHERE price IS NULL;`
`IS NOT NULL`	Returns true if the value is not `NULL`	`SELECT * FROM books WHERE price IS NOT NULL;`

Comment on Ternary Logic¶

In SQL, NULL represents an unknown or missing value, making boolean logic ternary rather than binary, with three possible outcomes: TRUE, FALSE, and UNKNOWN. This means standard comparison operators do not behave as expected with NULL, since any operation involving NULL results in UNKNOWN. Operators like the IS NULL operator are necessary to explicitly check for NULL values, as they accurately identify missing data where typical comparisons will fail.

Example of Ternary Logic:
- TRUE AND NULL → UNKNOWN
- FALSE OR NULL → UNKNOWN
- NOT NULL → UNKNOWN

Ordering and NULLs¶

Summary: Explains how NULL values affect ordering.

SQL Example:

SELECT title FROM bookstore_inventory ORDER BY price IS NULL ASC, price ASC; -- Same as NULLS LAST in other DBs

Date and time operators and functions¶

Summary: Discusses using date functions with the books table.

SQL Example:

SELECT title FROM books WHERE publication_year BETWEEN 1990 AND 2020;

Conditional expressions¶

Summary: Introduces conditional expressions using the CASE statement in books.

SQL Example:

SELECT title,
       CASE
          WHEN price > 20 THEN 'Expensive'
          ELSE 'Affordable'
       END AS price_category
FROM bookstore_inventory;

Simple joins¶

Summary: Covers joining the books and authors tables.

SQL Example:

SELECT books.title, authors.first_name, authors.last_name 
FROM books 
JOIN authors ON books.author_id = authors.author_id;

Names of things¶

Name collisions and ambiguity¶

With many tables, we can have many identically named columns. How do we handle this?
- Through using Qualified names like table1.columnA and table2.columnA
But won’t that make our queries long and difficult to parse/read?
- YES! Luckily there is Aliasing
Cool, but are there limits to what I can Alias something too?
- YES! Please don’t alias to reserved SQL keywords, if you have to you can use ` to do this though.
- YES! Please don’t put spaces in your names for columns or tables, if you have to you can use “ to do this though.
- It’s suggested to alias to short lower-case only names, though this is a convention, it will make your life easier.

Aliasing¶

Summary: Covers renaming tables and columns in joins for clarity.

SQL Example 1:

SELECT b.title, a.first_name, a.last_name 
FROM books AS b 
JOIN authors AS a ON b.author_id = a.author_id;

SQL Example 2:

SELECT b.title, CONCAT(a.first_name, ' ', a.last_name) AS `name`
FROM books AS b 
JOIN authors AS a ON b.author_id = a.author_id;

Identity columns - Primary Keys, and Foreign Keys!¶

Summary: Introduces identity columns, like author_id in authors.

SQL Example:

CREATE TABLE authors (
    author_id INT AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    PRIMARY KEY (author_id)
);

Table relationships - REMEMBER Cardinality!¶

One-to-one¶

Summary: Covers one-to-one relationships between tables, such as author_details and authors.

CREATE TABLE authors (
    author_id INT AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    PRIMARY KEY (author_id)
);

CREATE TABLE biographies (
    biography_id INT AUTO_INCREMENT,
    author_id INT,
    bio TEXT,
    PRIMARY KEY (biography_id),
    UNIQUE (author_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

In this relationship, each author can have only one biography, and each biography is linked to exactly one author through the author_id. The UNIQUE constraint on author_id in the biographies table enforces the 1:1 relationship.

One-to-many¶

Summary: Describes one-to-many relationships, such as between authors and books.

CREATE TABLE authors (
    author_id INT AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    PRIMARY KEY (author_id)
);

CREATE TABLE books (
    book_id INT AUTO_INCREMENT,
    title VARCHAR(100),
    genre VARCHAR(50),
    price DECIMAL(10, 2),
    author_id INT,
    PRIMARY KEY (book_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

In this relationship, one author can be linked to many books, while each book is associated with exactly one author via author_id. This allows an author to have multiple entries in the books table.

Many-to-many¶

Summary: Explains many-to-many relationships, for example between books and awards.

CREATE TABLE books (
    book_id INT AUTO_INCREMENT,
    title VARCHAR(100),
    PRIMARY KEY (book_id)
);

CREATE TABLE genres (
    genre_id INT AUTO_INCREMENT,
    genre_name VARCHAR(50),
    PRIMARY KEY (genre_id)
);

CREATE TABLE book_genres (
    book_id INT,
    genre_id INT,
    PRIMARY KEY (book_id, genre_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (genre_id) REFERENCES genres(genre_id)
);

The book_genres table is a reference/linking table that facilitates the M relationship. Each book can be linked to multiple genres, and each genre can contain multiple books. The combination of book_id and genre_id as a composite primary key and ensures that the same book-genre pair cannot be repeated.

Inner and Outer Joins¶

Summary: Introduces the difference between inner and outer joins using books and awards.
SQL Example:
An INNER JOIN is used here because we want only those books that have a corresponding author (i.e., we exclude books without authors).
1 2 3
SELECT b.title, a.first_name, a.last_name FROM books b INNER JOIN authors a ON b.author_id = a.author_id;
An INNER JOIN is appropriate because we only want to show books that have genres, excluding any books without a genre assignment.
1 2 3
SELECT b.title, a.first_name, a.last_name FROM books b INNER JOIN authors a ON b.author_id = a.author_id;

A LEFT OUTER JOIN is used to ensure that all authors are included, regardless of whether they have written any books.

SELECT a.first_name, a.last_name, b.title
FROM authors a
LEFT OUTER JOIN books b ON a.author_id = b.author_id;

A LEFT OUTER JOIN is used to include all genres, even those that do not have any books linked to them.

SELECT g.genre_name, b.title
FROM genres g
LEFT OUTER JOIN book_genres bg ON g.genre_id = bg.genre_id
LEFT OUTER JOIN books b ON bg.book_id = b.book_id;

Implicit Join Syntax¶

Summary: Explains older implicit join syntax.

SQL Example:

SELECT books.title, authors.first_name 
FROM books, authors 
WHERE books.author_id = authors.author_id;

VS

SELECT books.title, authors.first_name
FROM books
INNER JOIN authors ON books.author_id = authors.author_id;

Adding data using INSERT¶

Specifying columns¶

Summary: Describes inserting specific values into the books table.

SQL Example:

INSERT INTO books (title, publication_year, price) 
VALUES ('1984', 1949, 19.99);

Inserting multiple rows¶

Summary: Explains how to insert multiple rows into books.

SQL Example:

INSERT INTO books (title, publication_year, price) 
VALUES ('Animal Farm', 1945, 9.99), ('Homage to Catalonia', 1938, 14.99);

Inserting query results¶

Summary: Demonstrates inserting query results into the awards table.

SQL Example:

INSERT INTO awards (book_id, award_name, year)
SELECT book_id, 'Best Fiction', 2020 FROM books WHERE publication_year > 2015;

Removing data with DELETE¶

Summary: Explains how to remove rows from books.

SQL Example:

DELETE FROM books WHERE publication_year < 1900;

Modifying data with UPDATE¶

Summary: Covers updating rows in books.

SQL Example:

UPDATE books SET price = price * 1.05 WHERE publication_year > 2000;