Hello, this is the SQL code I have so far. It is based off of the ERD shown in the image attached. Basically, I want to add customer payment tracking, an employee table with work schedules and which videos they checked out to the customers all within the code. Current SQL code: -- Create Customer table CREATE TABLE Customer ( CUST_NUM INT PRIMARY KEY, CUST_LNAME VARCHAR(50), CUST_FNAME VARCHAR(50), CUST_INITIAL VARCHAR(10), CUST_E_MAIL VARCHAR(100) ); -- Insert some sample data into Customer table INSERT INTO Customer (CUST_NUM, CUST_LNAME, CUST_FNAME, CUST_INITIAL, CUST_E_MAIL) VALUES (1, 'Smith', 'John', 'A', 'jsmith@example.com'), (2, 'Johnson', 'Jane', 'M', 'jjohnson@example.com'), (3, 'Garcia', 'Maria', 'L', 'mgarcia@example.com'), (4, 'Lee', 'David', 'K', 'dlee@example.com'); -- Create Video table CREATE TABLE Video ( VIDEO_ID INT PRIMARY KEY, VIDEO_CLASS VARCHAR(50), VIDEO_TITLE VARCHAR(100), VIDEO_CHG DECIMAL(5,2) ); -- Insert some sample data into Video table INSERT INTO Video (VIDEO_ID, VIDEO_CLASS, VIDEO_TITLE, VIDEO_CHG) VALUES (1, 'Action', 'Die Hard', 2.99), (2, 'Comedy', 'The Hangover', 1.99), (3, 'Drama', 'The Shawshank Redemption', 3.99), (4, 'Science Fiction', 'Blade Runner', 2.49); -- Create Rental table with rental date, due date, and return date columns CREATE TABLE Rental ( VIDEO_ID INT, CUST_NUM INT, RENTAL_OUT DATE, RENTAL_DUE DATE, RENTAL_RETURN DATE, PRIMARY KEY (VIDEO_ID, CUST_NUM), FOREIGN KEY (VIDEO_ID) REFERENCES Video(VIDEO_ID), FOREIGN KEY (CUST_NUM) REFERENCES Customer(CUST_NUM) ); -- Insert some sample data into Rental table INSERT INTO Rental (VIDEO_ID, CUST_NUM, RENTAL_OUT, RENTAL_DUE, RENTAL_RETURN) VALUES (1, 1, '2022-01-01', '2022-01-08', NULL), (2, 2, '2022-02-01', '2022-02-08', NULL), (3, 3, '2022-03-01', '2022-03-08', NULL), (4, 4, '2022-04-01', '2022-04-08', '2022-04-05'); select * from Customer; select * from Rental; select * from Video;
Hello, this is the SQL code I have so far. It is based off of the ERD shown in the image attached.
Basically, I want to add customer payment tracking, an employee table with work schedules and which videos they checked out to the customers all within the code.
Current SQL code:
-- Create Customer table
CREATE TABLE Customer (
CUST_NUM INT PRIMARY KEY,
CUST_LNAME VARCHAR(50),
CUST_FNAME VARCHAR(50),
CUST_INITIAL VARCHAR(10),
CUST_E_MAIL VARCHAR(100)
);
-- Insert some sample data into Customer table
INSERT INTO Customer (CUST_NUM, CUST_LNAME, CUST_FNAME, CUST_INITIAL, CUST_E_MAIL)
VALUES
(1, 'Smith', 'John', 'A', 'jsmith@example.com'),
(2, 'Johnson', 'Jane', 'M', 'jjohnson@example.com'),
(3, 'Garcia', 'Maria', 'L', 'mgarcia@example.com'),
(4, 'Lee', 'David', 'K', 'dlee@example.com');
-- Create Video table
CREATE TABLE Video (
VIDEO_ID INT PRIMARY KEY,
VIDEO_CLASS VARCHAR(50),
VIDEO_TITLE VARCHAR(100),
VIDEO_CHG DECIMAL(5,2)
);
-- Insert some sample data into Video table
INSERT INTO Video (VIDEO_ID, VIDEO_CLASS, VIDEO_TITLE, VIDEO_CHG)
VALUES
(1, 'Action', 'Die Hard', 2.99),
(2, 'Comedy', 'The Hangover', 1.99),
(3, 'Drama', 'The Shawshank Redemption', 3.99),
(4, 'Science Fiction', 'Blade Runner', 2.49);
-- Create Rental table with rental date, due date, and return date columns
CREATE TABLE Rental (
VIDEO_ID INT,
CUST_NUM INT,
RENTAL_OUT DATE,
RENTAL_DUE DATE,
RENTAL_RETURN DATE,
PRIMARY KEY (VIDEO_ID, CUST_NUM),
FOREIGN KEY (VIDEO_ID) REFERENCES Video(VIDEO_ID),
FOREIGN KEY (CUST_NUM) REFERENCES Customer(CUST_NUM)
);
-- Insert some sample data into Rental table
INSERT INTO Rental (VIDEO_ID, CUST_NUM, RENTAL_OUT, RENTAL_DUE, RENTAL_RETURN)
VALUES
(1, 1, '2022-01-01', '2022-01-08', NULL),
(2, 2, '2022-02-01', '2022-02-08', NULL),
(3, 3, '2022-03-01', '2022-03-08', NULL),
(4, 4, '2022-04-01', '2022-04-08', '2022-04-05');
select * from Customer;
select * from Rental;
select * from Video;
Step by step
Solved in 3 steps
