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

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.