Skip to content
Introduction to SQL
  • AI Chat
  • Code
  • Report

    • Introduction to SQL

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

    Note: When using sample integrations such as those that contain course data, you have read-only access. You can run queries, but cannot make any changes such as adding, deleting, or modifying the data (e.g., creating tables, views, etc.).

    Take Notes

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

    Add your notes here

    Unknown integration
    DataFrameavailable as
    books
    variable
    -- Add your own queries here
SELECT title
FROM books;

    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.
    Unknown integration
    DataFrameavailable as
    books
    variable
    --Select only the title column.
SELECT title AS book_title
FROM books;
    Unknown integration
    DataFrameavailable as
    books
    variable
    --Check column name and datatype for all the columns in the table
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name='books';
    Unknown integration
    DataFrameavailable as
    books
    variable
    --Select the distinct author names from the author column.
SELECT DISTINCT author
FROM books;
    Unknown integration
    DataFrameavailable as
    books
    variable
    --Select all records from the table and limit your results to 10
SELECT *
FROM books
LIMIT 10;
    Unknown integration
    DataFrameavailable as
    books
    variable
    
SELECT genre, COUNT(*) AS genre_num
FROM books
WHERE genre !=''
GROUP BY genre;
    Unknown integration
    DataFrameavailable as
    books
    variable
    --top 3 authors and when they began writing and years of writing
SELECT author, 
		MIN(year) AS year_started_writing, 
		CASE WHEN MAX(year)-MIN(year)>1 THEN MAX(year)-MIN(year) || ' '|| 'years'
			ELSE MAX(year)-MIN(year) || ' '|| 'year'
			END AS duration_of_writing, 
		COUNT(title) AS number_of_books_written
FROM books
GROUP BY author
ORDER BY number_of_books_written DESC
LIMIT 3;
    Unknown integration
    DataFrameavailable as
    books
    variable
    --Adults article or book authors
SELECT author, COUNT(*) AS num_of_articles_written
FROM(SELECT id, 
		year, 
		author, 
		INITCAP(title)
FROM books
WHERE title LIKE '%Adult%') as adults_writings
GROUP BY author
ORDER BY num_of_articles_written DESC;
    Unknown integration
    DataFrameavailable as
    df
    variable
    --Both love and adults articles/books authors
WITH love AS 
	(SELECT id, 
		year, 
		author, 
		INITCAP(title) AS title
	FROM books
	WHERE title LIKE '%Love%'),
	adult AS
	(SELECT id,
		year, 
		author, 
		INITCAP(title) AS title
FROM books
WHERE title LIKE '%Adult%') 

SELECT l.author
FROM love AS l
INNER JOIN adult AS a
ON l.id=a.id;
    Unknown integration
    DataFrameavailable as
    books
    variable
    --Both fiction and non fiction articles/books authors
SELECT id,
		author,
		INITCAP(title) AS title
FROM books
WHERE genre='Fiction' AND id IN(
SELECT id
FROM books
WHERE genre='Non Fiction');