Ch 4 · SQL Basics: SELECT Queries · SQL
Topic 4 of 8 in SQL — Query a Real Database — 8 lessons.
SELECT and WHERE
Learn the fundamentals of querying data with SELECT, WHERE, and ORDER BY. SELECT picks the columns you want, FROM names the table, WHERE keeps only the rows that match a condition, and ORDER BY sorts what is left, ASC for ascending or DESC for descending. You can combine conditions with AND and OR. These few keywords power most everyday reports.
SELECT and WHERE in Code
Here the SELECT and WHERE ideas turn into real queries, building up step by step. You start by fetching every column, then pick specific ones, then filter to salaries over 80,000, then find one person by surname, and finally combine two conditions with AND and sort by salary. Notice the single quotes around 'Hopper' but none around numbers, and read the common mistakes before you try your own.
Practice: SELECT and WHERE
Now it is your turn to write a complete query from an empty template. You need to list the employees in the Engineering department (dept_id 1), with the highest salaries first. A correct answer fills in all four parts, SELECT, FROM, WHERE and ORDER BY, and sorts in the right direction. Use the hints if you get stuck.
Solution & Challenge: SELECT and WHERE
Compare your query with the worked solution, checking the columns you chose, the department filter and the sort direction. The second query shows how to filter by the department's name instead, by joining the departments table as in Card 3.1. Then take on the challenge, which stretches the same skills: several conditions at once, matching a list of departments, comparing dates and returning only the top ten rows. A correct answer runs without errors and returns no more than ten rows from just two departments.
DISTINCT, LIKE, and IN
Additional filtering tools for more precise queries. DISTINCT removes duplicate rows, LIKE matches text patterns using % for any number of characters and _ for exactly one, IN checks a value against a list, and BETWEEN picks a range that includes both ends. Together they let you search far more precisely than a plain = comparison.
DISTINCT, LIKE, and IN in Code
These examples show each filtering tool in action on the employees table. DISTINCT lists each department number once, two LIKE patterns find surnames starting with 'H' and Gmail addresses, IN matches two departments at once, and BETWEEN picks a salary range. Watch where the % sits in each pattern, because it decides whether you match the start or the end of the text.
Practice: DISTINCT, LIKE, and IN
Time to practise three short queries of your own, one for each tool. You will list every department number just once, find employees whose first name begins with A, and pick out staff in either Engineering or Sales. A correct answer uses DISTINCT, LIKE and IN in the right places, with text patterns wrapped in single quotes.
Solution & Challenge: DISTINCT, LIKE, and IN
Check your three queries against the worked solution: one DISTINCT, one LIKE pattern and one IN list. The challenge then asks you to combine these tools in a single query, filtering by email domain, part of a name and a salary range at the same time. A correct answer makes every condition apply together and uses BETWEEN for the salary range.