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)
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)
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)
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 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 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