SQL SELECT Check Answers

From Juneday education
Jump to: navigation, search

About this page

These are the answers to the check your progress questions here.

Check 01

Download the SQL file cars.sql. Create the database cars.db from that file.

$ wget https://raw.githubusercontent.com/progund/databases-introduction/master/assignment-01/cars.sql
$ sqlite3 cars.db < cars.sql
  • What is the name of the only table in the database?
  • What is the schema of that table?
$ # from the bash shell:
$ sqlite3 cars.db ".tables"
cars
$ sqlite3 cars.db ".schema"
CREATE TABLE cars (Make text,Color text, LicenseNumber text primary key);
$ # interactively in the SQLite3 shell:
$ sqlite3 cars.db 
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> .tables
cars
sqlite> .schema cars
CREATE TABLE cars (Make text,Color text, LicenseNumber text primary key);
sqlite>
  • What is the count of rows? (how many rows exist in the table?)
$ sqlite3 cars.db "SELECT count(*) FROM cars;"
1000
$ sqlite3 cars.db 
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> SELECT COUNT(*) FROM cars;
1000

Check 02

SELECT the 10 cars with the greatest license number (you have to order by license number and also use LIMIT).

The output should come in the following order:

licenseNumber Make Color

$ sqlite3 cars.db "SELECT LicenseNumber, Make, Color FROM cars ORDER BY LicenseNumber DESC LIMIT 10;"
ZZX 117|Suzuki|Blue
ZYT 514|Ford|Blue
ZYO 227|Mazda|Silver
ZXL 136|Simca|Yellow
ZWX 235|Simca|Yellow
ZWQ 666|Suzuki|Red
ZWF 975|Chevrolet|Brown
ZWE 201|Mazda|Blue
ZWA 270|Volvo|Black
ZVS 665|Suzuki|Brown
$ sqlite3 cars.db 
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> SELECT LicenseNumber, Make, Color FROM cars ORDER BY LicenseNumber DESC LIMIT 10;
ZZX 117|Suzuki|Blue
ZYT 514|Ford|Blue
ZYO 227|Mazda|Silver
ZXL 136|Simca|Yellow
ZWX 235|Simca|Yellow
ZWQ 666|Suzuki|Red
ZWF 975|Chevrolet|Brown
ZWE 201|Mazda|Blue
ZWA 270|Volvo|Black
ZVS 665|Suzuki|Brown

Check 03

Do the same again, but include the name of the columns in the output and use a tabular format.

Expected output:

LicenseNumber  Make        Color     
-------------  ----------  ----------
ZZX 117        Suzuki      Blue      
ZYT 514        Ford        Blue      
ZYO 227        Mazda       Silver    
ZXL 136        Simca       Yellow    
ZWX 235        Simca       Yellow    
ZWQ 666        Suzuki      Red       
ZWF 975        Chevrolet   Brown     
ZWE 201        Mazda       Blue      
ZWA 270        Volvo       Black     
ZVS 665        Suzuki      Brown 
sqlite> .headers on
sqlite> .mode column
sqlite> SELECT LicenseNumber, Make, Color FROM cars ORDER BY LicenseNumber DESC LIMIT 10;
LicenseNumber  Make        Color     
-------------  ----------  ----------
ZZX 117        Suzuki      Blue      
ZYT 514        Ford        Blue      
ZYO 227        Mazda       Silver    
ZXL 136        Simca       Yellow    
ZWX 235        Simca       Yellow    
ZWQ 666        Suzuki      Red       
ZWF 975        Chevrolet   Brown     
ZWE 201        Mazda       Blue      
ZWA 270        Volvo       Black     
ZVS 665        Suzuki      Brown

Check 04 Challenge

Really hard: SELECT the same 10 cars as the above, but the output of these exact 10 cars should be ordered by LicenseNumber ascending. Expected output:

LicenseNumber  Make        Color     
-------------  ----------  ----------
ZVS 665        Suzuki      Brown     
ZWA 270        Volvo       Black     
ZWE 201        Mazda       Blue      
ZWF 975        Chevrolet   Brown     
ZWQ 666        Suzuki      Red       
ZWX 235        Simca       Yellow    
ZXL 136        Simca       Yellow    
ZYO 227        Mazda       Silver    
ZYT 514        Ford        Blue      
ZZX 117        Suzuki      Blue

HINT: Use a SELECT from a sub-select of the ten cars, and order the result of the sub-select on LicenseNumber.

sqlite> SELECT LicenseNumber, Make, Color FROM
         (SELECT LicenseNumber, Make, Color FROM cars ORDER BY LicenseNumber DESC LIMIT 10)
        ORDER BY LicenseNumber;
LicenseNumber  Make        Color     
-------------  ----------  ----------
ZVS 665        Suzuki      Brown     
ZWA 270        Volvo       Black     
ZWE 201        Mazda       Blue      
ZWF 975        Chevrolet   Brown     
ZWQ 666        Suzuki      Red       
ZWX 235        Simca       Yellow    
ZXL 136        Simca       Yellow    
ZYO 227        Mazda       Silver    
ZYT 514        Ford        Blue      
ZZX 117        Suzuki      Blue

OR we can use WITH and create a temp table named e.g. lastcars:

sqlite> WITH lastcars AS (SELECT * FROM cars ORDER BY LicenseNumber DESC LIMIT 10) SELECT LicenseNumber, Make, Color FROM lastcars ORDER BY LicenseNumber;
LicenseNumber  Make        Color     
-------------  ----------  ----------
ZVS 665        Suzuki      Brown     
ZWA 270        Volvo       Black     
ZWE 201        Mazda       Blue      
ZWF 975        Chevrolet   Brown     
ZWQ 666        Suzuki      Red       
ZWX 235        Simca       Yellow    
ZXL 136        Simca       Yellow    
ZYO 227        Mazda       Silver    
ZYT 514        Ford        Blue      
ZZX 117        Suzuki      Blue

Check 05

Explain the following SQLite3 specific commands:

  • .once
  • .dump
  • .mode
  • .headers
  • .schema
  • .tables
.headers on|off        Turn display of headers on or off

.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         ascii    Columns/rows delimited by 0x1F and 0x1E
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator strings
                         tabs     Tab-separated values
                         tcl      TCL list elements

.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.

.once FILENAME         Output for the next SQL command only to FILENAME

.schema ?TABLE?        Show the CREATE statements
                         If TABLE specified, only show tables matching
                         LIKE pattern TABLE.

.tables ?TABLE?        List names of tables
                         If TABLE specified, only list tables matching
                         LIKE pattern TABLE.