Explore

SQL Lab Learning Lab Workbook

A student lab learning Lab Workbook for students learning relational database and SQL work flows.

We start simple with data defintion language sql queries to guide the students through making a movie tickets database, with tables for customers, movies, purchase of tickets.

⁠

Relational Database and SQL Class Lab Workbook

Lab 1: Introduction to Data Definition Language

Welcome to the first lab of the Relational Database and SQL class!

In this lab, we will walk through creating a simple database for a movie ticketing system.

Our database will consist of three tables: customers, movies, and ticket_purchases.

(Next week: WE will do Lab Book Part 2: Focusing on the four kinds of Predicate JOINS : Left Join, Right Join, Innner Join, Outer Join).

Learning Objectives

Understand the basics of the Data Definition Language (DDL) in SQL.

Create a database and tables using SQL.

Define appropriate fields for tables.

Task 1: Database Creation

Let's start by creating a new database named MovieTicketDB.

CREATE DATABASE MovieTicketDB;

Use the above SQL command to create a new database.

Task 2: Table Creation

We will create three tables: customers, movies, and ticket_purchases. Below are the details for each table.

2.1 customers Table

customer_id (Primary Key, Auto Increment)

first_name

last_name

USE MovieTicketDB;

CREATE TABLE customers (

customer_id INT AUTO_INCREMENT PRIMARY KEY,

first_name VARCHAR(50),

last_name VARCHAR(50),

email VARCHAR(100) UNIQUE

);

2.2 movies Table

movie_id (Primary Key, Auto Increment)

title

genre

rating

release_date

CREATE TABLE movies (

movie_id INT AUTO_INCREMENT PRIMARY KEY,

title VARCHAR(100),

genre VARCHAR(50),

rating DECIMAL(3, 1),

release_date DATE

);

2.3 ticket_purchases Table

purchase_id (Primary Key, Auto Increment)

customer_id (Foreign Key)

movie_id (Foreign Key)

purchase_date

show_time

CREATE TABLE ticket_purchases (

purchase_id INT AUTO_INCREMENT PRIMARY KEY,

customer_id INT,

movie_id INT,

purchase_date DATE,

show_time TIME,

FOREIGN KEY (customer_id) REFERENCES customers(customer_id),

FOREIGN KEY (movie_id) REFERENCES movies(movie_id)

);

Task 3: Review

Make sure to verify the creation of your database and tables. Use the following SQL commands to do this:

SHOW DATABASES;

USE MovieTicketDB;

SHOW TABLES;

Congratulations! You have created a basic database structure for a movie ticketing system.

Takeaway

In this lab exercise, you have learned to use SQL to create a database and define tables with appropriate fields. Understanding the structure and how to define databases and tables is foundational knowledge for working with any relational database management system.

There are 2 categories of JOBS in the Database realm:

Database Administrator: creates the database, run back ups

Reporting Analysts

check mark: Ensure you are comfortable with these concepts before moving forward, as future labs will build upon the knowledge gained in this exercise.

Below is a sample data in CSV format for each of the tables: customers, movies, and ticket_purchases. This data can be used for import into SQLiteStudio or any other database management tool and to perform DML operations.

customers.csv

customer_id,first_name,last_name,email

1,John,Doe,johndoe@email.com

2,Alice,Smith,alicesmith@email.com

3,Bob,Johnson,bobjohnson@email.com

movies.csv

movie_id,title,genre,rating,release_date

1,Inception,Sci-Fi,8.8,2010-07-16

2,Titanic,Romance,7.8,1997-12-19

3,The Lion King,Animation,8.5,1994-06-24

ticket_purchases.csv

purchase_id,customer_id,movie_id,purchase_date,show_time

1,1,1,2023-09-25,19:30:00

2,2,3,2023-09-26,15:00:00

3,3,2,2023-09-27,20:00:00

Steps to Import CSV into SQLiteStudio:

Open SQLiteStudio.

Open your database or create a new one.

Right-click on the database name and select "Import" > "Import Data" > "From CSV file".

For each table, choose the corresponding CSV file (customers.csv, movies.csv, ticket_purchases.csv) and proceed with the import.

Make sure the table names in SQLiteStudio match the names in the CSV files and SQL queries (customers, movies, ticket_purchases).

After importing, you can perform various Data Manipulation Language operations [CRUD create read update delete], such as INSERT, UPDATE, DELETE, and SELECT on these tables.

Lab 2: Data Manipulation Language (DML)

Welcome to Lab 2!

In this lab, we will focus on performing various DML operations – INSERT, UPDATE, DELETE, and SELECT on the customers, movies, and ticket_purchases tables.

Learning Objectives:

Understand the basics of Data Manipulation Language (DML).

Perform INSERT, UPDATE, DELETE, and SELECT operations on database tables.

Pre-requisites:

Completed Lab 1 or have the MovieTicketDB database and associated tables (customers, movies, ticket_purchases) set up.

Have some sample data in the tables to perform operations on.

Exercise 1: INSERT Statement

In this exercise, you will learn to insert records into the database tables.

Task 1.1: Insert a New Customer

Insert a new record into the customers table.

INSERT INTO customers (first_name, last_name, email)

VALUES ('Emily', 'Clark', 'emilyclark@email.com');

Task 1.2: Insert a New Movie

Insert a new record into the movies table.

sqlCopy code

INSERT INTO movies (title, genre, rating, release_date)

VALUES ('Avatar', 'Sci-Fi', 7.8, '2009-12-18');

Task 1.3: Insert a New Ticket Purchase

Insert a new record into the ticket_purchases table.

sqlCopy code

INSERT INTO ticket_purchases (customer_id, movie_id, purchase_date, show_time)

VALUES (4, 4, '2023-09-27', '21:00:00');

Exercise 2: UPDATE Statement

In this exercise, you will learn to update existing records in the database tables.

Task 2.1: Update a Customer's Email

Update the email address of a customer in the customers table.

sqlCopy code

UPDATE customers

SET email = 'newemail@email.com'

WHERE customer_id = 4;

Task 2.2: Update a Movie's Rating

Update the rating of a movie in the movies table.

sqlCopy code

UPDATE movies

SET rating = 8.5

WHERE movie_id = 4;

Exercise 3: DELETE Statement

In this exercise, you will learn to delete records from the database tables.

Task 3.1: Delete a Ticket Purchase

Delete a record from the ticket_purchases table.

sqlCopy code

DELETE FROM ticket_purchases

WHERE purchase_id = 3;

Exercise 4: SELECT Statement

In this exercise, you will learn to retrieve records from the database tables.

Task 4.1: Retrieve All Customers

Retrieve all records from the customers table.

sqlCopy code

SELECT * FROM customers;

Task 4.2: Retrieve Movies Released After 2000

Retrieve all movies released after the year 2000 from the movies table.

sqlCopy code

SELECT * FROM movies

WHERE release_date > '2000-01-01';

Task 4.3: Retrieve Ticket Purchases for a Specific Movie

Retrieve all ticket purchases for a specific movie from the ticket_purchases table.

sqlCopy code

SELECT * FROM ticket_purchases

WHERE movie_id = 4;

Lab Review

In this lab, you learned to insert, update, delete, and select records in a SQL database. Make sure to practice these commands further to solidify your understanding.

Takeaway:

Manipulating data is at the core of any database system. Understanding how to effectively insert, update, delete, and select data is crucial for working with databases. Continue practicing these operations, and consider exploring more complex queries and operations as you progress.

Lab 3: DQL Data Query Language: In this lab, we will focus on the skills of being an Informatics Data Reporting Analyst.

Lab 3: Data Query Language (DQL)

Welcome to Lab 3! In this lab, we'll explore various aspects of Data Query Language (DQL) focusing on skills vital for an Informatics Data Reporting Analyst. We'll practice using filters, sorting results, grouping data, and utilizing aggregate functions.

Learning Objectives:

Develop proficiency in writing SQL queries to filter, sort, and aggregate data.

Understand the GROUP BY, ORDER BY, and aggregate functions like SUM, AVG, MAX, MIN, and COUNT.

Exercise 1: Using WHERE to Filter Data

Task 1.1: Retrieve Customers with Specific Last Name

Retrieve all records from the customers table where the last_name is 'Smith'.

sqlCopy code

SELECT * FROM customers WHERE last_name = 'Smith';

Task 1.2: Retrieve Movies with Rating Above 8

Retrieve all movies with a rating above 8 from the movies table.

sqlCopy code

SELECT * FROM movies WHERE rating > 8;

Exercise 2: Sorting Results with ORDER BY

Task 2.1: Order Customers by Last Name

Retrieve all customers ordered by last_name alphabetically.

sqlCopy code

SELECT * FROM customers ORDER BY last_name;

Task 2.2: Order Movies by Release Date

Retrieve all movies ordered by release_date in descending order.

sqlCopy code

SELECT * FROM movies ORDER BY release_date DESC;

Exercise 3: Grouping Data with GROUP BY

Task 3.1: Count Movies by Genre

Count the number of movies in each genre.

sqlCopy code

SELECT genre, COUNT(*) FROM movies GROUP BY genre;

Task 3.2: Average Rating by Genre

Find the average rating for each genre.

sqlCopy code

SELECT genre, AVG(rating) FROM movies GROUP BY genre;

Exercise 4: Utilizing Aggregate Functions

Task 4.1: Total Number of Customers

Find the total number of customers.

sqlCopy code

SELECT COUNT(*) FROM customers;

Task 4.2: Highest and Lowest Movie Ratings

Find the highest and lowest movie ratings.

sqlCopy code

SELECT MAX(rating), MIN(rating) FROM movies;

Task 4.3: Total Tickets Sold for Each Movie

Find the total number of tickets sold for each movie.

sqlCopy code

SELECT movie_id, COUNT(*) FROM ticket_purchases GROUP BY movie_id;

Exercise 5: Complex Queries

Task 5.1: Number of Tickets Sold for Each Movie, Ordered by Title

Find the number of tickets sold for each movie, and order the results by movie title.

SELECT m.title, COUNT(tp.purchase_id)

FROM movies m

JOIN ticket_purchases tp ON m.movie_id = tp.movie_id

GROUP BY m.title

ORDER BY m.title;

Task 5.2: Average Movie Rating for Movies Released After 2000

Calculate the average movie rating for movies released after the year 2000.

SELECT AVG(rating)

FROM movies

WHERE release_date > '2000-01-01';

Lab Review

In this lab, you learned to write more complex SQL queries involving filtering, ordering, grouping, and aggregate functions to analyze data in a SQL database. These are essential skills for any Data Reporting Analyst and will form the basis for more advanced analytics work.

Takeaway:

Continue to practice writing complex queries, combining multiple functions and clauses to analyze data. Consider exploring subqueries, joins, and other advanced SQL topics to further enhance your data analytics and reporting skills.

Advanced Lab: Advanced Data Query SQL Formulations

In this advanced lab, we will delve into more intricate SQL query concepts. The lab will cover subqueries, joins, and window functions, enhancing your SQL abilities further.

Learning Objectives:

Understand and implement subqueries.

Use various types of joins to combine data from multiple tables.

Explore window functions for advanced data processing.

Exercise 1: Subqueries

Subqueries are a query nested inside another query and are a powerful tool for performing advanced data retrieval and analysis.

Task 1.1: Retrieve Movies with Above Average Ratings

Use a subquery to find all movies with a rating above the average rating of all movies.

sqlCopy code

SELECT * FROM movies

WHERE rating > (SELECT AVG(rating) FROM movies);

Task 1.2: Find Customers Without Purchases

Use a subquery to find all customers who have not made a purchase.

sqlCopy code

SELECT * FROM customers

WHERE customer_id NOT IN (SELECT customer_id FROM ticket_purchases);

Exercise 2: Joins

Joins are used to combine rows from two or more tables based on a related column.

Task 2.1: Retrieve Movie Titles with Purchase Information

Use a join to retrieve movie titles along with purchase information.

sqlCopy code

SELECT m.title, tp.purchase_date, tp.show_time

FROM movies m

JOIN ticket_purchases tp ON m.movie_id = tp.movie_id;

Task 2.2: Retrieve Customer Names and Their Purchased Movie Titles

Use a join to retrieve customer names and the titles of the movies they have purchased tickets for.

sqlCopy code

SELECT c.first_name, c.last_name, m.title

FROM customers c

JOIN ticket_purchases tp ON c.customer_id = tp.customer_id

JOIN movies m ON tp.movie_id = m.movie_id;

Exercise 3: Window Functions

Window functions perform a calculation across a set of table rows that are related to the current row.

Note: Ensure your database management system supports window functions as it’s an advanced feature not available in all DBMS (e.g., not available in MySQL, available in PostgreSQL).

Task 3.1: Rank Movies by Ratings in Each Genre

Rank movies by their ratings within each genre.

sqlCopy code

SELECT title, genre, rating,

RANK() OVER (PARTITION BY genre ORDER BY rating DESC) as rank_in_genre

FROM movies;

Task 3.2: Calculate Cumulative Ticket Sales for Each Movie

Calculate the cumulative number of ticket sales for each movie, ordered by purchase date.

sqlCopy code

SELECT m.title, tp.purchase_date,

SUM(1) OVER (PARTITION BY tp.movie_id ORDER BY tp.purchase_date) as cumulative_sales

FROM movies m

JOIN ticket_purchases tp ON m.movie_id = tp.movie_id;

Lab Review

This advanced lab has taken you through subqueries, joins, and window functions, expanding your SQL expertise and providing tools to solve complex query tasks. These skills are fundamental for data analysis and reporting.

Takeaway:

Keep practicing these advanced SQL concepts. Try creating your own complex queries, involving multiple tables, subqueries, and window functions to continue improving your SQL knowledge and capabilities.

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.