Skip to content

Introduction to SQL

Here you can access the books table used in the course.

Take Notes

Add notes about the concepts you've learned and SQL cells with queries you want to keep.

  1. Use SQL to return a result set of all book titles included in the books table.
  2. Select both the title and author fields from books
  3. Select all fields from the books table.

Add your notes here

Explore Datasets

Use the books table to explore the data and practice your skills!

  • Select only the title column.
  • Alias the title column as book_title.
  • Select the distinct author names from the author column.
  • Select all records from the table and limit your results to 10.
Spinner
DataFrameas
df
variable
SELECT id, title, author, genre, year
FROM books;

Making queries DISTINCT

Write SQL code that returns a result set with just one column listing the unique authors in the books table.

Spinner
DataFrameas
df
variable
SELECT DISTINCT author, year
FROM books;

Using views

A view is a virtual table that is the result of a saved SQL SELECT statement When accessed, views automatically update in response to updates in the underlying data

Notice that you found 247 unique authors in the books table overall but 249 unique combinations of authors and genres. This means there are one or two authors who have written books in multiple genres!

Spinner
DataFrameas
df
variable
-- CREATE VIEW book_author_genre AS
SELECT DISTINCT author, genre
FROM books;

Aliasing

While the default column names in a SQL result set come from the fields they are created from, you've learned that aliasing can be used to rename these result set columns. This can be helpful for clarifying the intent or contents of the column.

Add an alias to the SQL query to rename the author column to unique_author in the result set.

Spinner
DataFrameas
df
variable
-- Alias author so that it becomes unique_author
SELECT DISTINCT author AS unique_author
FROM books;

VIEWing your query

A view is a virtual table: it's very similar to a real table, but rather than the data itself being stored, the query code is stored for later use.

What if you'd like to be able to refer to it later, or allow others to access and use the results? The best way to do this is by creating a view.

Add a single line of code that saves the results of the written query as a view called library_authors.

Spinner
DataFrameas
df
variable
-- Save the results of this query as a view called library_authors
CREATE VIEW library_authors AS
SELECT DISTINCT author AS unique_author
FROM books;

-- Select all columns from library_authors
SELECT unique_author
FROM library_authors;
‌
‌
‌