Share
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.

⁠
image.png
⁠
⁠

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
email

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 codeINSERT 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 codeINSERT 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 codeUPDATE customersSET email = 'newemail@email.com'WHERE customer_id = 4;

Task 2.2: Update a Movie's Rating

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.