Query a database
Previously, you explored how SQL is an important tool in the world of cybersecurity and is essential when querying databases. You examined a few basic SQL queries and keywords used to extract needed information from a database. In this reading, you’ll review those basic SQL queries and learn a new keyword that will help you organize your output. You'll also learn about the Chinook database, which this course uses for queries in readings and quizzes.
Basic SQL query
There are two essential keywords in any SQL query: SELECT and FROM. You will use these keywords every time you want to query a SQL database. Using them together helps SQL identify what data you need from a database and the table you are returning it from.
The video demonstrated this SQL query:
SELECT employee_id, device_id
FROM employees;
In readings and quizzes, this course uses a sample database called the Chinook database to run queries. The Chinook database includes data that might be created at a digital media company. A security analyst employed by this company might need to query this data. For example, the database contains eleven tables, including an employees table, a customers table, and an invoices table. These tables include data such as names and addresses.
As an example, you can run this query to return data from the customers table of the Chinook database:
SELECT customerid, city, country
FROM customers;
+------------+---------------------+----------------+ | CustomerId | City | Country | +------------+---------------------+----------------+ | 1 | São José dos Campos | Brazil | | 2 | Stuttgart | Germany | | 3 | Montréal | Canada | | 4 | Oslo | Norway | | 5 | Prague | Czech Republic | | 6 | Prague | Czech Republic | | 7 | Vienne | Austria | | 8 | Brussels | Belgium | | 9 | Copenhagen | Denmark | | 10 | São Paulo | Brazil | | 11 | São Paulo | Brazil | | 12 | Rio de Janeiro | Brazil | | 13 | Brasília | Brazil | | 14 | Edmonton | Canada | | 15 | Vancouver | Canada | | 16 | Mountain View | USA | | 17 | Redmond | USA | | 18 | New York | USA | | 19 | Cupertino | USA | | 20 | Mountain View | USA | | 21 | Reno | USA | | 22 | Orlando | USA | | 23 | Boston | USA | | 24 | Chicago | USA | | 25 | Madison | USA | +------------+---------------------+----------------+ (Output limit exceeded, 25 of 59 total rows shown)
The SELECT keyword indicates which columns to return. For example, you can return the customerid column from the Chinook database with
SELECT customerid
You can also select multiple columns by separating them with a comma. For example, if you want to return both the customerid and city columns, you should write SELECT customerid, city.
If you want to return all columns in a table, you can follow the SELECT keyword with an asterisk (*). The first line in the query will be SELECT *.
Note: Although the tables you're querying in this course are relatively small, using SELECT * may not be advisable when working with large databases and tables; in those cases, the final output may be difficult to understand and might be slow to run.
FROM
The SELECT keyword always comes with the FROM keyword. FROM indicates which table to query. To use the FROM keyword, you should write it after the SELECT keyword, often on a new line, and follow it with the name of the table you’re querying. If you want to return all columns from the customers table, you can write:
SELECT *
FROM customers;
When you want to end the query here, you put a semicolon (;) at the end to tell SQL that this is the entire query.
Note: Line breaks are not necessary in SQL queries, but are often used to make the query easier to understand. If you prefer, you can also write the previous query on one line as
SELECT * FROM customers;
and here would be the databases answer for
SELECT * FROM customers ORDER BY country, city;
+------------+-----------+-------------+--------------------------------------------------+--------------------------------------+---------------------+-------+----------------+------------+---------------------+--------------------+-------------------------------+--------------+ | CustomerId | FirstName | LastName | Company | Address | City | State | Country | PostalCode | Phone | Fax | Email | SupportRepId | +------------+-----------+-------------+--------------------------------------------------+--------------------------------------+---------------------+-------+----------------+------------+---------------------+--------------------+-------------------------------+--------------+ | 56 | Diego | Gutiérrez | None | 307 Macacha Güemes | Buenos Aires | None | Argentina | 1106 | +54 (0)11 4311 4333 | None | diego.gutierrez@yahoo.ar | 4 | | 55 | Mark | Taylor | None | 421 Bourke Street | Sidney | NSW | Australia | 2010 | +61 (02) 9332 3633 | None | mark.taylor@yahoo.au | 4 | | 7 | Astrid | Gruber | None | Rotenturmstraße 4, 1010 Innere Stadt | Vienne | None | Austria | 1010 | +43 01 5134505 | None | astrid.gruber@apple.at | 5 | | 8 | Daan | Peeters | None | Grétrystraat 63 | Brussels | None | Belgium | 1000 | +32 02 219 03 03 | None | daan_peeters@apple.be | 4 | | 13 | Fernanda | Ramos | None | Qe 7 Bloco G | Brasília | DF | Brazil | 71020-677 | +55 (61) 3363-5547 | +55 (61) 3363-7855 | fernadaramos4@uol.com.br | 4 | | 12 | Roberto | Almeida | Riotur | Praça Pio X, 119 | Rio de Janeiro | RJ | Brazil | 20040-020 | +55 (21) 2271-7000 | +55 (21) 2271-7070 | roberto.almeida@riotur.gov.br | 3 | | 1 | Luís | Gonçalves | Embraer - Empresa Brasileira de Aeronáutica S.A. | Av. Brigadeiro Faria Lima, 2170 | São José dos Campos | SP | Brazil | 12227-000 | +55 (12) 3923-5555 | +55 (12) 3923-5566 | luisg@embraer.com.br | 3 | | 10 | Eduardo | Martins | Woodstock Discos | Rua Dr. Falcão Filho, 155 | São Paulo | SP | Brazil | 01007-010 | +55 (11) 3033-5446 | +55 (11) 3033-4564 | eduardo@woodstock.com.br | 4 | | 11 | Alexandre | Rocha | Banco do Brasil S.A. | Av. Paulista, 2022 | São Paulo | SP | Brazil | 01310-200 | +55 (11) 3055-3278 | +55 (11) 3055-8131 | alero@uol.com.br | 5 | | 14 | Mark | Philips | Telus | 8210 111 ST NW | Edmonton | AB | Canada | T6G 2C7 | +1 (780) 434-4554 | +1 (780) 434-5565 | mphilips12@shaw.ca | 5 | | 31 | Martha | Silk | None | 194A Chain Lake Drive | Halifax | NS | Canada | B3S 1C5 | +1 (902) 450-0450 | None | marthasilk@gmail.com | 5 | | 3 | François | Tremblay | None | 1498 rue Bélanger | Montréal | QC | Canada | H2G 1A7 | +1 (514) 721-4711 | None | ftremblay@gmail.com | 3 | | 30 | Edward | Francis | None | 230 Elgin Street | Ottawa | ON | Canada | K2P 1L7 | +1 (613) 234-3322 | None | edfrancis@yachoo.ca | 3 | | 29 | Robert | Brown | None | 796 Dundas Street West | Toronto | ON | Canada | M6J 1V1 | +1 (416) 363-8888 | None | robbrown@shaw.ca | 3 | | 15 | Jennifer | Peterson | Rogers Canada | 700 W Pender Street | Vancouver | BC | Canada | V6C 1G8 | +1 (604) 688-2255 | +1 (604) 688-8756 | jenniferp@rogers.ca | 3 | | 32 | Aaron | Mitchell | None | 696 Osborne Street | Winnipeg | MB | Canada | R3L 2B9 | +1 (204) 452-6452 | None | aaronmitchell@yahoo.ca | 4 | | 33 | Ellie | Sullivan | None | 5112 48 Street | Yellowknife | NT | Canada | X1A 1N6 | +1 (867) 920-2233 | None | ellie.sullivan@shaw.ca | 3 | | 57 | Luis | Rojas | None | Calle Lira, 198 | Santiago | None | Chile | None | +56 (0)2 635 4444 | None | luisrojas@yahoo.cl | 5 | | 5 | František | Wichterlová | JetBrains s.r.o. | Klanova 9/506 | Prague | None | Czech Republic | 14700 | +420 2 4172 5555 | +420 2 4172 5555 | frantisekw@jetbrains.com | 4 | | 6 | Helena | Holý | None | Rilská 3174/6 | Prague | None | Czech Republic | 14300 | +420 2 4177 0449 | None | hholy@gmail.com | 5 | | 9 | Kara | Nielsen | None | Sønder Boulevard 51 | Copenhagen | None | Denmark | 1720 | +453 3331 9991 | None | kara.nielsen@jubii.dk | 4 | | 44 | Terhi | Hämäläinen | None | Porthaninkatu 9 | Helsinki | None | Finland | 00530 | +358 09 870 2000 | None | terhi.hamalainen@apple.fi | 3 | | 42 | Wyatt | Girard | None | 9, Place Louis Barthou | Bordeaux | None | France | 33000 | +33 05 56 96 96 96 | None | wyatt.girard@yahoo.fr | 3 | | 43 | Isabelle | Mercier | None | 68, Rue Jouvence | Dijon | None | France | 21000 | +33 03 80 73 66 99 | None | isabelle_mercier@apple.fr | 3 | | 41 | Marc | Dubois | None | 11, Place Bellecour | Lyon | None | France | 69002 | +33 04 78 30 30 30 | None | marc.dubois@hotmail.com | 5 | +------------+-----------+-------------+--------------------------------------------------+--------------------------------------+---------------------+-------+----------------+------------+---------------------+--------------------+-------------------------------+--------------+ (Output limit exceeded, 25 of 59 total rows shown)
ORDER BY
Database tables are often very complicated, and this is where other SQL keywords come in handy. ORDER BY is an important keyword for organizing the data you extract from a table.
ORDER BY sequences the records returned by a query based on a specified column or columns. This can be in either ascending or descending order.
Sorting in ascending order
To use the ORDER BY keyword, write it at the end of the query and specify a column to base the sort on. In this example, SQL will return the customerid, city, and country columns from the customers table, and the records will be sequenced by the city column:
SELECT customerid, city, country
FROM customers
ORDER BY city;
+------------+--------------+----------------+ | CustomerId | City | Country | +------------+--------------+----------------+ | 48 | Amsterdam | Netherlands | | 59 | Bangalore | India | | 36 | Berlin | Germany | | 38 | Berlin | Germany | | 42 | Bordeaux | France | | 23 | Boston | USA | | 13 | Brasília | Brazil | | 8 | Brussels | Belgium | | 45 | Budapest | Hungary | | 56 | Buenos Aires | Argentina | | 24 | Chicago | USA | | 9 | Copenhagen | Denmark | | 19 | Cupertino | USA | | 58 | Delhi | India | | 43 | Dijon | France | | 46 | Dublin | Ireland | | 54 | Edinburgh | United Kingdom | | 14 | Edmonton | Canada | | 26 | Fort Worth | USA | | 37 | Frankfurt | Germany | | 31 | Halifax | Canada | | 44 | Helsinki | Finland | | 34 | Lisbon | Portugal | | 52 | London | United Kingdom | | 53 | London | United Kingdom | +------------+--------------+----------------+ (Output limit exceeded, 25 of 59 total rows shown)
The ORDER BY keyword sorts the records based on the column specified after this keyword. By default, as shown in this example, the sequence will be in ascending order. This means
-
if you choose a column containing numeric data, it sorts the output from the smallest to largest. For example, if sorting on customerid, the ID numbers are sorted from smallest to largest.
-
if the column contains alphabetic characters, such as in the example with the city column, it orders the records from the beginning of the alphabet to the end.
Sorting in descending order
You can also use the ORDER BY with the DESC keyword to sort in descending order. The DESC keyword is short for "descending" and tells SQL to sort numbers from largest to smallest, or alphabetically from Z to A. This can be done by following ORDER BY with the DESC keyword. For example, you can run this query to examine how the results differ when DESC is applied:
SELECT customerid, city, country
FROM customers
ORDER BY city DESC;
+------------+---------------------+----------------+ | CustomerId | City | Country | +------------+---------------------+----------------+ | 33 | Yellowknife | Canada | | 32 | Winnipeg | Canada | | 49 | Warsaw | Poland | | 7 | Vienne | Austria | | 15 | Vancouver | Canada | | 27 | Tucson | USA | | 29 | Toronto | Canada | | 10 | São Paulo | Brazil | | 11 | São Paulo | Brazil | | 1 | São José dos Campos | Brazil | | 2 | Stuttgart | Germany | | 51 | Stockholm | Sweden | | 55 | Sidney | Australia | | 57 | Santiago | Chile | | 28 | Salt Lake City | USA | | 47 | Rome | Italy | | 12 | Rio de Janeiro | Brazil | | 21 | Reno | USA | | 17 | Redmond | USA | | 5 | Prague | Czech Republic | | 6 | Prague | Czech Republic | | 35 | Porto | Portugal | | 39 | Paris | France | | 40 | Paris | France | | 30 | Ottawa | Canada | +------------+---------------------+----------------+ (Output limit exceeded, 25 of 59 total rows shown)
Now, cities at the end of the alphabet are listed first.
Sorting based on multiple columns
You can also choose multiple columns to order by. For example, you might first choose the country and then the city column. SQL then sorts the output by country, and for rows with the same country, it sorts them based on city. You can run this to explore how SQL displays this:
SELECT customerid, city, country
FROM customers
ORDER BY country, city;
+------------+---------------------+----------------+
| CustomerId | City | Country |
+------------+---------------------+----------------+
| 56 | Buenos Aires | Argentina |
| 55 | Sidney | Australia |
| 7 | Vienne | Austria |
| 8 | Brussels | Belgium |
| 13 | Brasília | Brazil |
| 12 | Rio de Janeiro | Brazil |
| 1 | São José dos Campos | Brazil |
| 10 | São Paulo | Brazil |
| 11 | São Paulo | Brazil |
| 14 | Edmonton | Canada |
| 31 | Halifax | Canada |
| 3 | Montréal | Canada |
| 30 | Ottawa | Canada |
| 29 | Toronto | Canada |
| 15 | Vancouver | Canada |
| 32 | Winnipeg | Canada |
| 33 | Yellowknife | Canada |
| 57 | Santiago | Chile |
| 5 | Prague | Czech Republic |
| 6 | Prague | Czech Republic |
| 9 | Copenhagen | Denmark |
| 44 | Helsinki | Finland |
| 42 | Bordeaux | France |
| 43 | Dijon | France |
| 41 | Lyon | France |
+------------+---------------------+----------------+
(Output limit exceeded, 25 of 59 total rows shown)Key takeaways
SELECT and FROM are important keywords in SQL queries. You use SELECT to indicate which columns to return and FROM to indicate which table to query. You can also include ORDER BY in your query to organize the output. These foundational SQL skills will support you as you move into more advanced queries.
No Comments