Workspace
David Owino/

Introduction to SQL

0
Beta
Spinner

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;
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

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;
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
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';
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Unknown integration
DataFrameavailable as
books
variable
--Select the distinct author names from the author column.
SELECT DISTINCT author
FROM books;
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Unknown integration
DataFrameavailable as
books
variable
--Select all records from the table and limit your results to 10
SELECT *
FROM books
LIMIT 10;
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Unknown integration
DataFrameavailable as
books
variable

SELECT genre, COUNT(*) AS genre_num
FROM books
WHERE genre !=''
GROUP BY genre;
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
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;
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
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;
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
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;
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
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');
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
  • AI Chat
  • Code