Chapter 5 SQL II

5.1 `SELECT`

5.1.1 Basic Select

-- Select specific columns from a table
SELECT column1, column2, ...
FROM table;

-- Select all columns from a table
SELECT *
FROM table;

-- Select distinct combinations of the specified columns
SELECT DISTINCT column1, column2, ...
FROM table;

Exercise 5.1 Select all instructor’s IDs and names.

Solution.

SELECT instructor_id, name
FROM instructor;

##    instructor_id       name
## 1          10101 Srinivasan
## 2          12121         Wu
## 3          15151     Mozart
## 4          22222   Einstein
## 5          32343    El Said
## 6          33456       Gold
## 7          45565       Katz
## 8          58583  Califieri
## 9          76543      Singh
## 10         76766      Crick
## 11         83821     Brandt
## 12         98345        Kim

5.1.2 Select with Constraint (`WHERE`)

In order to filter certain results from being returned, we need to use a WHERE clause in the query.

-- Select query with constraint
SELECT column1, column2, ...
FROM table
WHERE condition1
  AND|OR condition2
  AND|OR ...;

Remark. You can use NOT to negate a condition in the WHERE clause.

Exercise 5.2 (NOT, AND, OR)

Given the table instructor:

Find all instructors from the Physics department.
Find all instructors from Physics whose salary is greater than 90,000.
Find all instructors not from the Physics department.

Solution.

Find all instructors from the Physics department.

SELECT instructor_id, name
FROM instructor
WHERE dept_name = 'Physics';

##   instructor_id     name
## 1         22222 Einstein
## 2         33456     Gold

Find all instructors from Physics whose salary is greater than 90,000.

SELECT instructor_id, name
FROM instructor
WHERE dept_name = 'Physics'
    AND salary > 90000;

##   instructor_id     name
## 1         22222 Einstein

Find all instructors not from Physics department.

SELECT instructor_id, name
FROM instructor
WHERE NOT dept_name = 'Physics';

-- equivalently, you can write
-- WHERE dept_name != 'Physics';

##    instructor_id       name  dept_name
## 1          10101 Srinivasan Comp. Sci.
## 2          12121         Wu    Finance
## 3          15151     Mozart      Music
## 4          32343    El Said    History
## 5          45565       Katz Comp. Sci.
## 6          58583  Califieri    History
## 7          76543      Singh    Finance
## 8          76766      Crick    Biology
## 9          83821     Brandt Comp. Sci.
## 10         98345        Kim Elec. Eng.

Exercise 5.3 (BETWEEN, LIKE, IN)

Refer to the table instructor:

Find all instructors whose salaries between 50,000 and 90,000
Find all instructors whose names start with letter C.
Find all instructors from the following departments: Computer Science, Physics, History and Music.

Solution.

Find all instructors whose salaries between 50,000 and 90,000

SELECT instructor_id, name, salary
FROM instructor
WHERE salary BETWEEN 50000 AND 90000;

##   instructor_id       name salary
## 1         10101 Srinivasan  65000
## 2         12121         Wu  90000
## 3         32343    El Said  60000
## 4         33456       Gold  87000
## 5         45565       Katz  75000
## 6         58583  Califieri  62000
## 7         76543      Singh  80000
## 8         76766      Crick  72000
## 9         98345        Kim  80000

Find all instructors whose names start with letter C.

SELECT instructor_id, name
FROM instructor
WHERE name LIKE 'C%';

##   instructor_id      name
## 1         58583 Califieri
## 2         76766     Crick

Find all instructors from the following departments: Computer Science, Physics, History and Music.

SELECT instructor_id, name, dept_name
FROM instructor
WHERE dept_name IN ('Comp. Sci.', 'Physics', 'History', 'Music');

##   instructor_id       name  dept_name
## 1         10101 Srinivasan Comp. Sci.
## 2         15151     Mozart      Music
## 3         22222   Einstein    Physics
## 4         32343    El Said    History
## 5         33456       Gold    Physics
## 6         45565       Katz Comp. Sci.
## 7         58583  Califieri    History
## 8         83821     Brandt Comp. Sci.

5.1.3 Select with Ordering (`ORDER BY`)

Data in databases is usually unordered. The ORDER BY clause sorts query results by one or more columns in ascending or descending order.

-- SELECT query with filtering and sorting
SELECT column1, column2, ...
FROM mytable
WHERE condition
ORDER BY column1 ASC|DESC, column2 ASC|DESC, ...;

Example 5.1 Display instructors’ salaries in descending order (from highest to lowest)

SELECT instructor_id, name, salary
FROM instructor
ORDER BY salary DESC;

##    instructor_id       name salary
## 1          22222   Einstein  95000
## 2          83821     Brandt  92000
## 3          12121         Wu  90000
## 4          33456       Gold  87000
## 5          76543      Singh  80000
## 6          98345        Kim  80000
## 7          45565       Katz  75000
## 8          76766      Crick  72000
## 9          10101 Srinivasan  65000
## 10         58583  Califieri  62000
## 11         32343    El Said  60000
## 12         15151     Mozart  40000

5.1.4 Select with Limiting (`LIMIT`)

Another clause which is commonly used with the ORDER BY clause are the LIMIT. The LIMIT will reduce the number of rows to return.

-- SELECT query with filtering, sorting, and result limiting
SELECT column1, column2, ...
FROM mytable
WHERE condition
ORDER BY column1 ASC|DESC
LIMIT num_limit;

Example 5.2 Find top 5 instructors with highest salaries.

SELECT instructor_id, name, salary
FROM instructor
ORDER BY salary DESC
LIMIT 5;

##   instructor_id     name salary
## 1         22222 Einstein  95000
## 2         83821   Brandt  92000
## 3         12121       Wu  90000
## 4         33456     Gold  87000
## 5         76543    Singh  80000

5.1.5 Data Aggregation and Summary (`GROUP BY`)

The GROUP BY clause and aggregation functions helps answer questions such as:

How many instructors are in each department?
What is the average salary for each department?

In this section, we’ll cover key aggregation functions such as COUNT, MAX, MIN, AVG, and SUM to perform these types of summaries.

-- Apply aggregate functions to groups of rows
SELECT group_column, AGG_FUNC(column_name) AS aggregate_label
FROM table
WHERE condition
GROUP BY group_column;

Exercise 5.4 (Aggregation with COUNT and AVG)

Refer to the instructor table:

Find the total number of instructors in each department
Find the average salary in each department.

Solution.

Find the total number of instructors in each department

SELECT COUNT(name) as total_instructor
FROM instructor
GROUP BY dept_name;

##    dept_name total_instructor
## 1    Biology                1
## 2 Comp. Sci.                3
## 3 Elec. Eng.                1
## 4    Finance                2
## 5    History                2
## 6      Music                1
## 7    Physics                2

Find the average salary in each department.

SELECT dept_name, AVG(salary) as avg_salary
FROM instructor
GROUP BY dept_name;

##    dept_name avg_salary
## 1    Biology   72000.00
## 2 Comp. Sci.   77333.33
## 3 Elec. Eng.   80000.00
## 4    Finance   85000.00
## 5    History   61000.00
## 6      Music   40000.00
## 7    Physics   91000.00

5.2 Join Tables

In relational databases, information about an entity is often split across different tables.
Joins bring these pieces back together so we can answer questions that need data from more than one table.
There are 5 types of joins:
- (INNER) JOIN
- LEFT (OUTER) JOIN
- RIGHT (OUTER) JOIN
- FULL (OUTER) JOIN
- CROSS JOIN

5.2.1 Inner Join

INNER JOIN returns only the rows where a match exists in both tables A and B.

SELECT column_list
FROM table_a
INNER JOIN table_b
    ON table_a.key_column = table_b.key_column;

Exercise 5.5 Find all instructors (ID, name) who taught in 2017.

Hint: See Figure 5.1

Figure 5.1: instructor table linked to teaches table via the foreign key instructor_id.

Solution.

SELECT instructor.instructor_id,
instructor.name
FROM instructor
JOIN teaches ON instructor.instructor_id = teaches.instructor_id
WHERE teaches.year = 2017;

##   instructor_id       name
## 1         10101 Srinivasan
## 2         10101 Srinivasan
## 3         22222   Einstein
## 4         76766      Crick
## 5         83821     Brandt
## 6         83821     Brandt
## 7         98345        Kim

We can also use aliases to shorten table names.

-- Simplify JOIN syntax with table aliases
SELECT column_list
FROM table_a AS a
INNER JOIN table_b AS b
  ON a.key_column = b.key_column;

5.2.2 Left Join

LEFT JOIN returns all rows from table A, and the matching rows from table B. Rows in A without a match in B appear with NULL values.

SELECT column_list
FROM table_a
LEFT JOIN table_b
  ON table_a.key_column = table_b.key_column;

Exercise 5.6 Some instructors teach courses, while others do not. Write a query using a LEFT JOIN to list all instructors (ID, name) together with courses (ID) they teach. For instructors who are not teaching any course, show their names with NULL for the course ID.

Solution.

SELECT i.instructor_id, i.name, t.course_id
FROM instructor AS i
LEFT JOIN teaches AS t
    ON i.instructor_id = t.instructor_id;

##    instructor_id       name course_id
## 1          10101 Srinivasan    CS-101
## 2          10101 Srinivasan    CS-315
## 3          10101 Srinivasan    CS-347
## 4          12121         Wu   FIN-201
## 5          15151     Mozart    MU-199
## 6          22222   Einstein   PHY-101
## 7          32343    El Said   HIS-351
## 8          33456       Gold      <NA>
## 9          45565       Katz    CS-101
## 10         45565       Katz    CS-319
## 11         58583  Califieri      <NA>
## 12         76543      Singh      <NA>
## 13         76766      Crick   BIO-101
## 14         76766      Crick   BIO-301
## 15         83821     Brandt    CS-190
## 16         83821     Brandt    CS-190
## 17         83821     Brandt    CS-319
## 18         98345        Kim    EE-181

5.2.3 Right Join

Similar to LEFT JOIN, but it returns all rows from the right table and the matching rows from the left table. In other words, RIGHT JOIN is just LEFT JOIN with the tables swapped.

SELECT column_list
FROM table_a
RIGHT JOIN table_b
  ON table_a.key_column = table_b.key_column;

5.2.4 Full Join

FULL JOIN returns all rows from both tables A and B. Rows without a match on either side are filled with NULL values.

SELECT column_list
FROM table_a
FULL JOIN table_b
  ON table_a.key_column = table_b.key_column;

Example 5.3

5.2.5 Cross Join

A CROSS JOIN returns the Catesian product of two tables.
Every row in the first table is combined with every row in the second table.
Unlike other joins, it does not require a condition and is rarely used.

SELECT column_list
FROM table_a
CROSS JOIN table_b;

Example 5.4 (Generating Combinations)

Suppose you have two tables colors and sizes.

Figure 5.2: Example of tables colors and sizes.

SELECT c.color, s.size
FROM colors AS c
CROSS JOIN sizes AS s;

You want every possible combination of sizes and colors for product catalog.

Figure 5.3: All combinations (size, color).

Homework

Complete this as individual work (not group work)

1. (Query Practice)

Refer to the instructor and the course tables. Write SQL queries to:

Find the department each instructor belongs to.
Find there departments with highest total number of instructors.
Find the total number of course offered by Biology department.

2. (Join Practice)

Find all instructors (ID, name) who taught in Summer 2017, along with the course (ID, title) they taught.

Hint: This involves joining three tables teaches, course and instructor (see Figure 5.4). Use the syntax given below to write the query.

SELECT column_list
FROM table_a
JOIN table_b
  ON table_a.key_column = table_b.key_column
JOIN table_c
  ON table_b.key_column = table_c.key_column
-- (add more JOINs as needed)
WHERE conditions;

Figure 5.4: Relationships among instructor, teaches, and course tables.

Chapter 5 SQL II

5.1 SELECT

5.1.1 Basic Select

5.1.2 Select with Constraint (WHERE)

5.1.3 Select with Ordering (ORDER BY)

5.1.4 Select with Limiting (LIMIT)

5.1.5 Data Aggregation and Summary (GROUP BY)

5.2 Join Tables

5.2.1 Inner Join

5.2.2 Left Join

5.2.3 Right Join

5.2.4 Full Join

5.2.5 Cross Join

Homework

5.1 `SELECT`

5.1.2 Select with Constraint (`WHERE`)

5.1.3 Select with Ordering (`ORDER BY`)

5.1.4 Select with Limiting (`LIMIT`)

5.1.5 Data Aggregation and Summary (`GROUP BY`)