SQL-DataCamp-Applying SQL to Real-World Problems

1. Use Real-World SQL

1.1 Essential SQL (video)

1.2 Review the essentials

Instruction 1:

• Return the

  title

  and

  description

  from the

  film

  table. Make sure to alias this table

  AS

  f

  .

SELECT title, description
FROM film AS f
           

Instruction 2:

• Use an

  INNER JOIN

  to bring in language data about your films using the

  language_id

  as the join column.

SELECT title, description
FROM film AS f
INNER JOIN language AS l
  ON f.language_id = l.language_id
           

Instruction 3:

• Use the

  IN

  command to limit the results where the language

  name

  is either

  Italian

  or

  French

  .
• Ensure only the

  release_year

  of the films is

  2005

  .

SELECT title, description
FROM film AS f
INNER JOIN language AS l
  ON f.language_id = l.language_id
WHERE name IN ('Italian', 'French')
  AND release_year = 2005;
           

1.3 Practice the essentials

• Return the

  first_name

  ,

  last_name

  and

  amount

  .

• INNER JOIN

  the tables you need for this query:

  payment

  and

  customer

  using the

  customer_id

  for the join.
• Ensure only

  active

  customers are returned.
• Sort the results in

  DESC

  ending order by the amount paid.

Instruction :

SELECT first_name,
	   last_name,
       amount
FROM payment AS p
INNER JOIN customer AS c
  ON p.customer_id = c.customer_id
WHERE active = 'true'
ORDER BY amount DESC;
           

1.4 Transforming your results (video)

1.5 Transforming numeric & strings

Instruction 1:

• Return the

  LOWER

  -case

  titles

  of the films.
• Return the original

  rental_rate

  and the 50% discounted

  sale_rate

  by multiplying

  rental_rate

  by

  0.5

  .
• Ensure only films prior to

  2006

  are considered for this

SELECT LOWER(title) AS title, 
  rental_rate AS original_rate, 
  0.5 * rental_rate AS sale_rate 
FROM film
-- Filter for films prior to 2006
WHERE release_year < 2006;
           

1.6 Extract what you need

Instruction 1:

• EXTRACT

  the

  DAY

  from

  payment_date

  and return this column

  AS

  the

  payment_day

  .

SELECT payment_date,
  EXTRACT(DAY FROM payment_date) AS payment_day 
FROM payment;
           

Instruction 2:

• EXTRACT

  the

  YEAR

  from

  payment_date

  and return this column

  AS

  the

  payment_day

  .

SELECT payment_date,
  EXTRACT(YEAR FROM payment_date) AS payment_year 
FROM payment;
           

Instruction 3:

• EXTRACT

  the

  HOUR

  from

  payment_date

  and return this column

  AS

  the

  payment_day

  .

SELECT payment_date,
  EXTRACT(HOUR FROM payment_date) AS payment_hour 
FROM payment;
           

1.7 Working with aggregate functions (video)

1.8 Aggregating finances

Instruction 1:

• Find out the differences in their total number of payments by

  COUNT()

  ing the

  payment_id

  .
• Find out the differences in their average payments by using

  AVG()

  .
• Find out the differences in their total payments by using

  SUM()

  .
• Ensure the aggregate functions

  GROUP BY

  whether customer payments are

  active

  .

SELECT active, 
       COUNT(payment_id) AS num_transactions, 
       AVG(amount) AS avg_amount, 
       SUM(amount) AS total_amount
FROM payment AS p
INNER JOIN customer AS c
  ON p.customer_id = c.customer_id
GROUP BY active;
           

1.9 Aggregating strings

Instruction 1:

• Return a column with a list of comma-separated film

  title

  s by using the

  STRING_AGG()

  function.
• Limit the results to films that have a

  release_year

  of

  2010

  AND

  have a rating of

  'G'

  .
• Ensure that the operation is grouped by the language

  name

  .

SELECT name, 
	STRING_AGG(title, ',') AS film_titles
FROM film AS f
INNER JOIN language AS l
  ON f.language_id = l.language_id
WHERE release_year = 2010
  AND rating = 'G'
GROUP BY name;
           

1.10 Which tables?

2. Find Your Data

2.1 Find the right table (video)

2.2 LIMITing your search

Instruction 1:

• Use

  LIMIT

  to return all columns for the first 10 rows for the

  payment

  table.

SELECT * 
FROM payment
LIMIT 10;
           

Instruction 2:

• Use

  LIMIT

  to return all columns for the 10 highest amount paid in the

  payments

SELECT * 
FROM payment
ORDER BY amount DESC
LIMIT 10;
           

2.3 Which table to use?

2.4 What tables are in your database?

• List the tables that exist in your database by querying the table:

  pg_catalog.pg_tables

  .
• Filter the query to ensure the result contains entries where the

  schemaname

  is

  'public'

  .

Instruction:

SELECT * 
FROM pg_catalog.pg_tables
WHERE schemaname = 'public';
           

2.5 Determine the monthly income

Instruction 1:

• Run the first section of code to list your tables.
• Explore the tables to determine which you need to answer the question.
• Once you’ve figured out which table to use, fill in the blank in this statement

  SELECT * FROM ___ LIMIT 10

  .

-- List all tables in the public schema
SELECT * 
FROM pg_catalog.pg_tables
WHERE schemaname = 'public';

-- Explore the tables and fill in the correct one
SELECT * 
FROM payment
LIMIT 10;
           

Instruction 2:

• Calculate the

  total_payment

  per month.

-- List all tables in the public schema
SELECT * 
FROM pg_catalog.pg_tables
WHERE schemaname = 'public';

-- Explore the tables and fill in the correct one
SELECT * 
FROM payment 
LIMIT 10;

-- Prepare the result
SELECT EXTRACT(MONTH FROM payment_date) AS month, 
       SUM(amount) AS total_payment
FROM payment 
GROUP BY month;
           

2.5 Join the correct tables (video)

2.7 What columns are in your database?

Instruction 1:

• View all of the data in the

  information_schema.columns

  table by

  SELECT

  ing all the columns within it.

-- Select all columns from the information_schema.columns table
SELECT *
FROM information_schema.columns;
           

Instruction 2:

• Limit your results to only the columns you need:

  table_name

  and

  column_name

  .
• Filter the results where the

  table_schema

  is

  public

  .

SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema = 'public';
           

2.8 A ViEW of all you columns

Instruction 1:

• Concatenate the

  column_name

  (s) for each

  table_name

  into a comma-separated list using the

  STRING_AGG()

  function and save this as a new field called

  columns

  .

SELECT table_name, 
	   STRING_AGG(column_name, ', ') AS columns
FROM information_schema.columns
WHERE table_schema = 'public'
GROUP BY table_name;
           

Instruction 2:

• Store the previous query result in a new

  VIEW

  called

  table_columns

  .
• Query your newly created view by

  SELECT

  ing all its rows & columns.

-- Create a new view called table_columns
CREATE VIEW table_columns AS
SELECT table_name, 
	   STRING_AGG(column_name, ', ') AS columns
FROM information_schema.columns
WHERE table_schema = 'public'
GROUP BY table_name;

-- Query the newly created view table_columns
SELECT * FROM table_columns;
           

2.9 Testing out your new VIEW

2.10 The average length of films by category

Instruction 1:

• Calculate the average

  length

  and return this column as

  average_length

  .
• Join the two tables

  film

  and

  category

  .
• Ensure that the result is in ascending order by the average length of each category.

-- Calculate the average_length for each category
SELECT category, 
	   AVG(length) AS average_length
FROM film AS f
-- Join the tables film & category
INNER JOIN category AS c
  ON f.film_id = c.film_id
GROUP BY category
-- Sort the results in ascending order by length
ORDER BY average_length;
           

2.11 Complex joins (video)

2.12 Build the entity relationships diagram

2.13 Which films are most frequently rented?

Instruction:

• Use the entity diagram above to correctly join the required tables to answer this question.

SELECT title, COUNT(title)
FROM film AS f
INNER JOIN inventory AS i
  ON f.film_id = i.film_id
INNER JOIN rental AS r
  ON i.inventory_id = r.inventory_id
GROUP BY title
ORDER BY count DESC;
           

3. Manage Your Data

3.1 Store your data (video)

3.2 Storing new data

Instruction 1:

• CREATE

  an empty new

  TABLE

  called

  oscars

  .
• Store both columns (

  title

  and

  award

  ) as the datatype

  VARCHAR

  .

-- Create a new table called oscars
CREATE TABLE oscars (
    title VARCHAR,
    award VARCHAR
);
           

Instruction 2:

• INSERT

  the data from the table above into the newly created

  oscars

  table.

-- Create a new table called oscars
CREATE TABLE oscars (
    title VARCHAR,
    award VARCHAR
);

-- Insert the data into the oscars table
INSERT INTO oscars (title, award)
VALUES
('TRANSLATION SUMMER', 'Best Film'),
('DORADO NOTTING', 'Best Film'),
('MARS ROMAN', 'Best Film'),
('CUPBOARD SINNERS', 'Best Film'),
('LONELY ELEPHANT', 'Best Film');
           

Instruction 3:

• Confirm that your new table exists by running a

  SELECT

  query on it.

-- Create a new table called oscars
CREATE TABLE oscars (
    title VARCHAR,
    award VARCHAR
);

-- Insert the data into the oscars table
INSERT INTO oscars (title, award)
VALUES
('TRANSLATION SUMMER', 'Best Film'),
('DORADO NOTTING', 'Best Film'),
('MARS ROMAN', 'Best Film'),
('CUPBOARD SINNERS', 'Best Film'),
('LONELY ELEPHANT', 'Best Film');

-- Confirm the table was created and is populated
SELECT * 
FROM oscars;
           

3.3 Using existing data

Instruction 1:

• Write a query to select all records & column from the

  film

  table which have a

  rating

  of

  G

  or

  PG

  .

SELECT *
FROM film
WHERE rating IN ('G', 'PG')
           

Instruction 2:

• Save the results of the query in a new table named

  family_films

  .

-- Create a new table named family_films using this query
CREATE TABLE family_films AS
SELECT *
FROM film
WHERE rating IN ('G', 'PG');
           

3.3 TABLE vs VIEW

3.4 Update your data (video)

3.5 What should you modify?

3.6 Update the price of rentals

Instruction 1:

• UPDATE

  the

  film

  table to increase the cost of renting (

  rental_rate

  ) by 50 cents.
• The

  rental_rate

  is shown in dollars.

-- Increase rental_rate by 0.5 in the film table
UPDATE film
SET rental_rate = rental_rate +0.5;
           

Instruction 2:

• UPDATE

  the

  film

  table to further increase the cost of renting (

  rental_rate

  ) R-rated films by 1 dollar.

-- Increase rental_rate by one dollar for R-rated movies
UPDATE film
SET rental_rate = rental_rate + 1
WHERE rating = 'R'
           

3.7 Update based on other tables

Instruction 1:

• Write a query to

  SELECT

  the

  film_id

  for the actors with the following 5 last names

  WILLIS, CHASE, WINSLET, GUINESS, HUDSON

  .

SELECT film_id 
FROM actor AS a
INNER JOIN film_actor AS f
   ON a.actor_id = f.actor_id
WHERE last_name IN ('WILLIS', 'CHASE', 'WINSLET', 'GUINESS', 'HUDSON');
           

Instruction 2:

• Use the query you created in the previous step to decrease the

  rental_rate

  by 1 dollar for all of the

  film_id

  that match.

UPDATE film
SET rental_rate = rental_rate - 1
WHERE film_id IN 
  (SELECT film_id from actor AS a
   INNER JOIN film_actor AS f
      ON a.actor_id = f.actor_id
   WHERE last_name IN ('WILLIS', 'CHASE', 'WINSLET', 'GUINESS', 'HUDSON'));
           

3.8 Delete your data (video)

3.9 Deleting all table data

3.10 Delete selected records

Instruction:

• DELETE

  records from the

  film

  table who have a

  replacement_cost

  that is greater than $25.

-- Delete films that cost most than 25 dollars
DELETE FROM film
WHERE replacement_cost > 25;
           

3.11 A family friendly video store

Instruction 1:

• Identify the

  film_id

  of all films that have a rating of R or NC-17.

-- Identify the film_id of all films that have a rating of R or NC-17
SELECT film_id 
FROM film
WHERE rating IN ('R', 'NC-17')
           

Instruction 2:

• Use the list of

  film_id

  values to

  DELETE

  all records in

  inventory

  .
• Delete records from the

  film

  table that are either rated as R or NC-17.

-- Use the list of film_id values to DELETE all R & NC-17 rated films from inventory.
DELETE FROM inventory
WHERE film_id IN (
  SELECT film_id FROM film
  WHERE rating IN ('R', 'NC-17')
);

-- Delete records from the `film` table that are either rated as R or NC-17.
DELETE FROM film
WHERE rating IN ('R', 'NC-17');
           

4. Best Practices for Writing SQL

4.1 Convey your intent (video)

4.2 How to convey our intent

4.3 Clarify the intent of this query

4.4 Fix this query- intent

Instruction 1:

• Use single letter aliases to help clarify the table source (e.g.

  x1

  ->

  r

  ).

  Note: Using

  x1

  ->

  ren

  would be appropriate as well, but for this exercise please stick to single letter aliases.

SELECT r.customer_id, r.rental_date, r.return_date 
FROM rental AS r
JOIN inventory AS i
  ON r.inventory_id = i.inventory_id
JOIN film AS f
  ON i.film_id = f.film_id
WHERE f.length < 90;
           

Instruction 2:

• Ensure that all aliases commands use

  AS

  .

SELECT r.customer_id, r.rental_date, r.return_date 
FROM rental AS r
JOIN inventory AS i
  ON r.inventory_id = i.inventory_id
JOIN film AS f
  ON i.film_id = f.film_id
WHERE f.length < 90;
           

Instruction 3:

• Clarify the type of

  JOIN

  being used by using

  INNER JOIN

  instead.

SELECT r.customer_id, r.rental_date, r.return_date 
FROM rental AS r
INNER  JOIN inventory AS i
  ON r.inventory_id = i.inventory_id
INNER JOIN film AS f
  ON i.film_id = f.film_id
WHERE f.length < 90;
           

Instruction 4:

• Add a comment to clarify that the inventory table is used to unite the rental and film tables.

SELECT r.customer_id, r.rental_date, r.return_date 
FROM rental AS r
/*Happy Analysing*/ 
INNER JOIN inventory AS i
  ON r.inventory_id = i.inventory_id
INNER JOIN film AS f
  ON i.film_id = f.film_id
WHERE f.length < 90;
           

4.5 Write readable code (video)

4.6 How to make code easier to read

4.7 Make this query easier to read (1)

4.8 Make this query easier to read (2)

4.9 Avoid common mistakes (video)

4.10 What are the don’t of writing SQL code?

4.11 Apply best practices to your code

4.12 Recap