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. - 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.
LIKE and wildcards
LIKE matches a pattern. % stands for any number of characters (even none) and _ for exactly one.
WHERE Title LIKE 'S%'matches titles starting with S.WHERE Name LIKE '%an'matches names ending in an.WHERE Name LIKE '_a%'matches names whose second letter is a.
Two tables
A query can use two linked tables. The WHERE clause must match the foreign key to the primary key, or every customer would be paired with every sale.
SELECT Customer.Name, Sale.Item
FROM Customer, Sale
WHERE Customer.CustomerID = Sale.CustomerID
AND Customer.Town = 'Leeds'Find the customers in Leeds first, note their IDs, then pick out the sales with those IDs. Table.Field says which table a field comes from.
Run a query in your head
Go down the table one record at a time and keep the ones that meet WHERE. Then sort if there is an ORDER BY.