Subqueries

Question 1

Show the ‘department’ column from both the employees table and the departments table

Answer 1

SELECT d.department, e.department
FROM employees AS e, departments AS d

Question 2

T/F: You can only specify tables in a FROM clause

Answer 2

False, you can nest other subqueries in place of a table, e.g:
~~~
SELECT department
FROM (SELECT * FROM my_table)
~~~

Question 3

Why does this fail?:
~~~
SELECT *
FROM (SELECT * FROM employees WHERE salary > 150000)
~~~

Answer 3

Because in PostgreSQL, you need to provide an alias to the source query. To fix it, do this:
~~~
SELECT *
FROM (SELECT * FROM employees WHERE salary > 150000) AS my_table
~~~

Question 4

T/F: When creating a subquery in a WHERE clause, you need to provide an alias

Answer 4

False, unlike subqueries in FROM clauses, you don’t provide an alias (it will fail if you do)

Question 5

What will be returned from this query?
~~~
SELECT first_name, last_name, (SELECT first_name FROM employees LIMIT 1)
FROM employees
~~~

Answer 5

SELECT first_name FROM employees returns the first_name column, so LIMIT 1 will return only the first value of this column. So, the end result is a table of first names, last names, AND the first of the first name column repeated for each row.

Question 6

What does this WHERE clause check for?
~~~
WHERE region_id > ANY (
SELECT region_id FROM regions WHERE country = ‘United States’
)
~~~

Answer 6

It checks if region_id is greater than ANY of the returned values from the subquery

Question 7

What does this WHERE clause check for?
~~~
WHERE region_id > ALL (
SELECT region_id FROM regions WHERE country = ‘United States’
)
~~~

Answer 7

It checks if region_id is greater than ALL of the returned values from the subquery

Question 8

T/F: Subqueries execute before the outer query

Answer 8

True

Question 9

T/F: If a subquery within a WHERE clause returns null, the outer query will also return a null value

Answer 9

False, the outer query won’t return any row

Question 10

What is a scalar subquery?

Answer 10

A subquery that only returns one value

Question 11

What does this WHERE clause check for?
~~~
WHERE EXISTS (
SELECT region_id FROM regions WHERE country = ‘United States’
)
~~~

Answer 11

It checks if any values are returned from the inner query