Database:Exercise - Retrieving rows of data - SQL SELECT

From Juneday education
Jump to: navigation, search

Work in progress

Exercises for the topic SQL SELECT

Setup

For these questions, we have a table of the following form:

books: author TEXT, title TEXT, isbn TEXT PRIMARY KEY, publisher TEXT

sqlite> SELECT * FROM books;
author          title           isbn         publisher 
--------------  --------------  -----------  ----------
John Smith      Life            0-0-0-0-0-1  Bonnier   
James Woody     Love            0-0-0-0-0-2  Bonnier   
Joan Carmen     Guns            0-0-0-0-0-3  Bonnier   
Johnanna Boyd   Code            0-0-0-0-0-4  Bonnier   
Eva Peron       Cars            0-0-0-0-0-5  Books R us

Instructions

You are encouraged to try and answer the questions without the use of an up and running database.

Later on in the course, please revisit this page and do the exercises again, but with an actual database up and running. We suggest that you do this after the Getting started with SQLite lecture. But the questions on this page is written so that you can answer them without a running database as well as with a live database.

Questions

Q1

How would you retrieve (select) only the ISBNs of the table?

Expand using link to the right to see a suggested answer.

Answer:

SELECT ISBN FROM books;

Q2

How many rows would be the result set if we issue the following SQL query?

SELECT * FROM books WHERE isbn > '0-0-0-0-0-3';

Expand using link to the right to see a suggested answer.

Answer: One row. Just kidding. Two rows!

Q3

Which rows would be returned from the previous question? SELECT * FROM books WHERE isbn > '0-0-0-0-0-3';

Expand using link to the right to see a suggested answer.

Johnanna Boyd   Code            0-0-0-0-0-4  Bonnier   
Eva Peron       Cars            0-0-0-0-0-5  Books R us

Both have an ISBN greater than '0-0-0-0-0-3'

Q4

What would an SQL query look like if we wanted to select all rows from the books table and order the result set by ISBN in a descending manner, alphabetically?

Expand using link to the right to see a suggested answer.

Answer:

SELECT * FROM books ORDER BY ISBN DESC;

Q5

If we want to select more than one named column from a table, what character is used to delimit the columns when we enumerate them in the query?

Expand using link to the right to see a suggested answer.

Answer: a comma

Example, select author and title from books:

SELECT author, title FROM books;

Q6

How many rows would be returned from the following query?

SELECT title FROM books WHERE publisher LIKE 'B%';

Expand using link to the right to see a suggested answer.

Answer: 5 - All the books whose publisher starts with a "B", which is all books! Both Bonnier and Books R Us start with "B".

For those who want to learn more than just the basics, here's how you could use SELECT to get the number of rows from another SELECT:

SELECT count(*) FROM (SELECT title FROM books WHERE publisher LIKE 'B%');

The answer from that SELECT would be 5. Using a select with a second select is sometimes called "A nested SELECT query". The SELECT count(*) is a useful trick to simply count the number of rows.

Q7

What would the query look like if we wanted to select all Titles that end with an ‘e’?

Expand using link to the right to see a suggested answer.

Answer:

SELECT title FROM books WHERE title LIKE '%e';

The result would be:

Life      
Love      
Code

Remember, in a LIKE expression, the % stands for "any characters". So %e means "any characters followed by "e". In a question above, we used % to get rows where a column started with a "B". There, we wrote B% which means "B" followed by any characters.

In most DBMSs, the LIKE operator is case insensitive (doesn't distinguish between upper or lower case letters). Consult your manual to see what the case is for the one you are using.

Check your progress

If you don't know all answers to these questions, they are repeated in the Exercise_-_Getting_started_with_SQLite chapter, so don't panic. That chapter actually asks you to revisit the questions on this page and do them again. That's why we have them on both pages. Do the questions you can here, and do the rest after reading Exercise_-_Getting_started_with_SQLite .

Check 01

Note: the coding style of this example is inconsistent with other examples. We are reviewing this book in order to make names more consistent. For instance, LicenseNumber is a horrible choice, where license_number would have been much better. Note, however, that SQLite is case insensitive, when it comes to names. Note also, that there is little consensus on coding style for SQL, but some standards have been proposed by various sources.

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

  • What is the name of the only table in the database?
  • What is the schema of that table?
  • What is the count of rows? (how many rows exist in the table?)

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

Expected output:

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 

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.

This one is harder than we would expect you to be able to solve on, e.g. a written exam aiming for a Pass grade. It's just a challenge. The difficulty is of level "Pass with excellence" (or "Very Good"). In order to solve this, you need not only to do the exercises, watch the video lectures, but also read the external links we provide, and do some research of your own.

Check 05

Explain the following SQLite3 specific commands:

  • .once
  • .dump
  • .mode
  • .headers
  • .schema
  • .tables

Check your answers

SQL SELECT Check Answers

Links

Files

  • No files for this chapter

Where to go next

« PreviousBook TOCNext »