# 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?

```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';`

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';`

```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?

```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?

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%';`

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’?

```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.

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