For each question below, find the approriate SQL query to obtain the information requested. Put all your answers in the solutions.sql
file.
To get started we'll need to import the booktown.sql file.
- Fork and clone this repository
cd
into the repository- use the command
psql -f booktown.sql
- type
psql
to open your psql console - type
\list
to ensure the booktown database was successfully completed - type
\c booktown
to connect to the booktown database - type
\d
to see a list of all the tables in the booktown database - type
\d [TABLE_NAME]
to see information about columns and their types for a specific table. You should see output like below:
booktown=# \d books
Table "public.books"
Column | Type | Modifiers
------------+---------+-----------
id | integer | not null
title | text | not null
author_id | integer |
subject_id | integer |
Indexes:
"books_id_pkey" PRIMARY KEY, btree (id)
"books_title_idx" btree (title)
Complete the following exercises to practice using SQL.
- Find all subjects sorted by subject
- Find all subjects sorted by location
- Find the book "Little Women"
- Find all books containing the word "Python"
- Find all subjects with the location "Main St" sort them by subject
- Find all books about Computers and list ONLY the book titles
- Find all books and display a result table with ONLY the following columns
- Book title
- Author's first name
- Author's last name
- Book subject
- Find all books that are listed in the stock table
- Sort them by retail price (most expensive first)
- Display ONLY: title and price
- Find the book "Dune" and display ONLY the following columns
- Book title
- ISBN number
- Publisher name
- Retail price
- Find all shipments sorted by ship date display a result table with ONLY the following columns:
- Customer first name
- Customer last name
- ship date
- book title
- Get the COUNT of all books
- Get the COUNT of all Locations
- Get the COUNT of each unique location in the subjects table. Display the count and the location name. (hint: requires GROUP BY).
- List all books. Display the book_id, title, and a count of how many editions each book has. (hint: requires GROUP BY and JOIN)
- All content is licensed under a CC-BY-NC-SA 4.0 license.
- All software code is licensed under GNU GPLv3. For commercial use or alternative licensing, please contact [email protected].