SELECT, FROM and WHERE, AND and OR, LIKE with the % and _ wildcards, ORDER BY ASC and DESC, and queries over two linked tables.
What you will learn
- Work out what a SELECT query returns
- Use AND, OR and comparison operators
- Match text with LIKE, % and _
- Sort results with ORDER BY
- Read a query over two tables
SELECT, FROM, WHERE
SQL (structured query language) asks questions of a database.
SELECT Title, Year
FROM Film
WHERE Genre = 'Drama' AND Year > 2005
ORDER BY Year DESC- SELECT names the fields to show;
SELECT *shows every field. - FROM names the table.
- WHERE keeps only the records that meet the condition. Use
=,<>(not equal),<,>,<=and>=. AND needs both conditions; OR needs either. Text goes in quotes.
A worked example on a Film table: WHERE Year >= 2001 AND Year <= 2010 keeps films from 2001 to 2010, both years included, so a 2001 film and a 2010 film are kept and a 2011 film is not. WHERE Genre <> 'Drama' keeps every film that is not Drama. WHERE Genre = 'Drama' OR Minutes > 120 keeps a film that is Drama, or longer than 120 minutes, or both.
To count the result, go down the table row by row and test each record against the whole condition.
- ORDER BY sorts the result: ASC (the default) is smallest, earliest or A first; DESC is largest, latest or Z first.
Try it for real: SELECT, WHERE and ORDER BY on Brainlag Code.
The rest of this lesson and its 8 practice questions are locked.
In this lesson
- SELECT, FROM, WHERE
- LIKE and wildcards
- Two tables
- Try it: Run a query in your head
8 practice questions, marked as you go; about 25 minutes in all.
Key terms
SQL, SELECT, FROM, WHERE, ORDER BY, ASC, DESC, Wildcards
In this unit: Databases and SQL
Tables, records and fields, primary and foreign keys, why linked tables beat one big table, SQL queries over one or two tables, changing data with INSERT, UPDATE and DELETE, and reading and writing CSV files.