Correlated Subqueries

Question 1

What is a correlated subquery?

Answer 1

It’s a subquery that uses information from the outer query

Question 2

T/F: A correlated subquery is run on every row of the outer query

Answer 2

True

Question 3

When would you want to use a correlated subquery?

Answer 3

You’d want to use one when the subquery depends on the value of each row in the outer query.

Question 4

T/F: You have to alias the outer query’s table so it can be referenced in the inner subquery

Answer 4

True, e.g.
~~~
SELECT first_name, salary, department FROM employees outerr
WHERE salary > (SELECT ROUND(AVG(salary), 2)
FROM employees
WHERE department = outerr.department)
~~~

Question 5

Use implicit join syntax to join employees and regions on region_id

Answer 5

SELECT *
FROM employees, regions
WHERE employees.region_id = regions.region_id

Question 6

What type of JOIN is an implicit JOIN?

Answer 6

It performs an INNER JOIN

Question 7

Why are implicit joins bad practice?

Answer 7

Because they are less explicit in their syntax, which can make it difficult to read/track what is happening in a more complex query

Question 8

How would you rewrite this to use explicit syntax?
~~~
SELECT first_name, region
FROM employees, regions
WHERE employees.region_id = regions.region_id
~~~

Answer 8

SELECT first_name, region
FROM employees
INNER JOIN regions
ON employees.region_id = regions.region_id

Question 9

What is the function of an INNER JOIN?

Answer 9

It joins two tables on a single column, and returns the rows where the values in that column are equal in a single table

Question 10

What is the order of operations in this query?:
~~~
SELECT first_name, email, division, country
FROM employees e
INNER JOIN departments d
ON e.department = d.department
INNER JOIN regions r
ON e.region_id = r.region_id
WHERE email IS NOT NULL
~~~

Answer 10

1. FROM clause is executed from top to bottom, i.e. the first INNER JOIN is performed, then the next INNER JOIN
2. WHERE clause is executed, filtering on email column
3. SELECT statement returns specified columns

Question 11

Why does this fail?
~~~
SELECT department FROM employees e
INNER JOIN departments d
ON employees.department = departments.department
~~~

Answer 11

Because department exists in both tables and is ambiguously called in the SELECT statement. You need to add a table prefix to specify which department column you want to return, e.g.:
~~~
SELECT e.department FROM employees e
INNER JOIN departments d
ON employees.department = departments.department
~~~

Question 12

What is the function of a LEFT JOIN?

Answer 12

It returns ALL the values from the left table, and only the matching values from the right table. Any non-matching rows will have NULL in the right table columns.

Question 13

What is the function of a RIGHT JOIN?

Answer 13

It returns ALL the values from the right table, and only the matching values from the left table. Any non-matching rows will have NULL in the left table columns.

Question 14

What is LEFT/RIGHT JOIN also known as?

Answer 14

LEFT/RIGHT OUTER JOIN

Question 15

What does FULL OUTER JOIN return?

Answer 15

It returns all match and non-match rows in a single table

Question 16

In words using JOIN syntax, how would you find all of the values that DON’T exist in the right joining column but exist in the left joining column?

Answer 16

Do a LEFT JOIN on the column, then only return rows where the right column is NULL

Question 17

Why does this error?
~~~
SELECT DISTINCT department, region_id
FROM employees
UNION ALL
SELECT department, division
FROM departments
~~~

Answer 17

Because the type of region_id and division are different, preventing them from being concatenated

Question 18

What is happening in this query?
~~~
SELECT DISTINCT department
FROM employees
UNION
SELECT department
FROM departments
~~~

Answer 18

It is concatenating the top and bottom query values together and removing duplicates

Question 19

What is the difference between UNION and UNION ALL?

Answer 19

Both will concatenate one query result after the other, but UNION will remove duplicate values while UNION ALL will not

Question 20

T/F: you can only UNION one column at a time

Answer 20

False. Multiple columns can be UNION’d as long as the paired columns are the same type

Question 21

What does this query return?
~~~
SELECT DISTINCT department
FROM employees
EXCEPT
SELECT department
FROM departments
~~~

Answer 21

It returns the values from the employees table which DO NOT exist in the departments table

Question 22

What is the equivalent of EXCEPT in Oracle?

Answer 22

MINUS

Question 23

What type of JOIN does this return?
~~~
SELECT *
FROM departments, employees
~~~

Answer 23

It returns a CROSS JOIN, or Cartesian Product, of both of the tables. This means that every row from departments will be joined with every row of employees, resulting in and row length of len(departments) * len(employees)

Question 24

What’s a more explicit way of writing this query?
~~~
SELECT *
FROM departments, employees
~~~

Answer 24

SELECT *
FROM departments CROSS JOIN employees

Question 25

T/F: You need to specify a common column to join on with CROSS JOIN

Answer 25

False, CROSS JOIN doesn’t join on any column, instead returning the Cartesian Product of both tables