SQL and MySQL course Β· Module 8: Subqueries
Connecting the Labyrinth's corridors
In this lesson3
You now know all the tools of the Labyrinth. It is time to learn to put them together - because real questions to the Library rarely fit into a single simple subquery.
Two conditions at once
Nothing stops us from combining a scalar subquery with the IN operator in one query. We simply join the conditions with AND:
SELECT tytul
FROM ksiazki
WHERE cena < (SELECT AVG(cena) FROM ksiazki)
AND autor_id IN (SELECT id FROM autorzy WHERE kraj = 'Grecja');This query finds cheap scrolls by Greek authors - cheaper than the average and written by a creator from Greece.
Nested EXISTS
Subqueries can be nested - one inside another. This is how we ask about authors who have books, but none of them has been borrowed:
SELECT a.imie, a.nazwisko
FROM autorzy AS a
WHERE EXISTS (
SELECT 1 FROM ksiazki AS k
WHERE k.autor_id = a.id
AND NOT EXISTS (
SELECT 1 FROM wypozyczenia AS w WHERE w.ksiazka_id = k.id
)
);EXISTS checks that the author has a book, and the nested NOT EXISTS - that no one has borrowed that book.
Subquery in SELECT
A scalar subquery can also be placed on the column list of SELECT - as a single computed value next to ordinary columns:
SELECT tytul, cena, (SELECT AVG(cena) FROM ksiazki) AS srednia
FROM ksiazki;Each row will show the title, the book's price and - next to it - the average price of the whole Library. It is a convenient way to compare a value with its background without losing the details.
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. Which tool best answers the question 'does a related row exist at all'?
2. You want to select books whose autor_id is one of many returned by a subquery. Which operator will you use?
These are 2 of 4 questions for this lesson. Solve the rest in the game.
Hands-on tasks in the game
- Code editor
Write a query that retrieves from the ksiazki table the tytul column of those books whose price is lower than the average price of all books AND whose autor_id is on the list of author ids from the country 'Grecja'. Use a scalar subquery with AVG and the IN operator with a subquery.
- Horizontal ordering
Arrange in one line (left to right) the elements joining two SELECT queries with the UNION operator:
- Code editor
Write a query that uses UNION to combine into one list the imie column from the autorzy table and the imie column from the czytelnicy table.
- Click in order
Click the elements in the execution order of a query with a scalar subquery (from inside out):
- Horizontal ordering
Arrange in one line (left to right) the condition with the NOT IN operator and a subquery:
- Vertical ordering
Arrange the steps of building a query with a derived table (from first to last step):
- Click in order
Click the elements in the order of joining first names from two tables with the UNION operator:
- Code editor
Write a query that retrieves from the autorzy table (alias a) the imie and nazwisko columns of those authors who have books, but none of them has been borrowed. First use an EXISTS condition to check that the author has a book (ksiazki k, k.autor_id = a.id), then NOT EXISTS that no borrowing of that book exists (wypozyczenia w, w.ksiazka_id = k.id). Hint: use EXISTS with a nested NOT EXISTS.
- Horizontal ordering
Arrange in one line (left to right) the elements joining two queries with the UNION ALL operator:
- Horizontal ordering
Arrange in one line (left to right) the start of a derived table in the FROM clause:
- Code editor
Write a query that retrieves from the ksiazki table the tytul, cena columns and - as an extra column aliased srednia - the result of the scalar subquery (SELECT AVG(cena) FROM ksiazki). Place the subquery on the column list in SELECT.
- Click in order
Click the elements in the order of building a subquery in SELECT (one line):
Tasks after this lesson
- QuestionMap of tools
- QuestionChoosing the operator
- Coding taskCheap book by a Greek author
- Put in orderUNION of two SELECT queries
- Coding taskFirst names of authors and readers
- Click in orderRunning a scalar subquery
- Put in orderExcluding poetry with NOT IN
- Put in orderSteps of building a derived table
- Click in orderA shared list of first names with UNION
- Coding taskAuthors whose books are never borrowed
- QuestionSubquery in SELECT
- Put in orderUNION ALL of two year ranges
- Put in orderA derived table in the FROM clause
- Coding taskPrice and average side by side
- QuestionCorrelated subquery
- Click in orderThe srednia column from a subquery