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).
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)
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(100) UNIQUE
2.2 movies Table
movie_id (Primary Key, Auto Increment)
CREATE TABLE movies (
movie_id INT AUTO_INCREMENT PRIMARY KEY,
rating DECIMAL(3, 1),
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,
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:
Congratulations! You have created a basic database structure for a movie ticketing system.
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.
3,The Lion King,Animation,8.5,1994-06-24
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.
Understand the basics of Data Manipulation Language (DML). Perform INSERT, UPDATE, DELETE, and SELECT operations on database tables.
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', 'firstname.lastname@example.org');
Task 1.2: Insert a New Movie
Insert a new record into the movies table.
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.
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.
SET email = 'email@example.com'
WHERE customer_id = 4;
Task 2.2: Update a Movie's Rating