-- Camille Barnard IS 610
-- Project 2 Step 4

-- start creating tables

-- create customer table
CREATE TABLE customer (
  customer_id INTEGER PRIMARY KEY NOT NULL UNIQUE,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  street_address TEXT NOT NULL,
  city TEXT NOT NULL,
  state TEXT NOT NULL,
  zip_code TEXT NOT NULL,
  phone_number TEXT NOT NULL
);

-- create rental table
CREATE TABLE rental (
  rental_id INTEGER PRIMARY KEY NOT NULL UNIQUE,
  rental_date DATE,
  due_date DATE NOT NULL,
  return_date DATE,
  late_return BOOLEAN NOT NULL,
  rental_fee CURRENCY NOT NULL,
  late_fee CURRENCY,
  damage_fee CURRENCY,
  not_rewound_fee CURRENCY,
  tax CURRENCY NOT NULL,
  discount CURRENCY
);

-- create distributor table
CREATE TABLE distributor (
  dist_id INTEGER PRIMARY KEY NOT NULL UNIQUE,
  dist_name TEXT NOT NULL
);

-- create distributor movie table
CREATE TABLE dist_movie (
  dist_movie_id INTEGER PRIMARY KEY NOT NULL UNIQUE
);

-- create distributor video table
CREATE TABLE dist_video (
  dist_video_id INTEGER PRIMARY KEY NOT NULL UNIQUE
);

-- create distributor dvd table
CREATE TABLE dist_dvd (
  dist_dvd_id INTEGER PRIMARY KEY NOT NULL UNIQUE
);

-- create movie table
CREATE TABLE movie (
  movie_id INTEGER PRIMARY KEY NOT NULL UNIQUE,
  title TEXT NOT NULL,
  actors TEXT NOT NULL,
  actresses TEXT NOT NULL,
  running_length INTEGER NOT NULL,
  genre TEXT NOT NULL,
  rating CHAR NOT NULL,
  release_year TEXT NOT NULL,
  director TEXT NOT NULL,
  academy_awards TEXT,
  dist_movie_id INTEGER NOT NULL,
  FOREIGN KEY(dist_movie_id) REFERENCES dist_movie(dist_movie_id)
);

-- create video table
CREATE TABLE video (
  video_id INTEGER PRIMARY KEY NOT NULL UNIQUE,
  price CURRENCY NOT NULL,
  genre TEXT NOT NULL,
  dist_video_id INTEGER NOT NULL,
  FOREIGN KEY(dist_video_id) REFERENCES dist_video(dist_video_id)
);

-- create dvd table
CREATE TABLE dvd (
  dvd_id INTEGER PRIMARY KEY NOT NULL UNIQUE,
  price CURRENCY NOT NULL,
  genre TEXT NOT NULL,
  dist_dvd_id INTEGER NOT NULL,
  FOREIGN KEY(dist_dvd_id) REFERENCES dist_dvd(dist_dvd_id)
);

-- create actor table
CREATE TABLE actors (
  actor_id INTEGER PRIMARY KEY NOT NULL UNIQUE,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL
);

-- create actress table
CREATE TABLE actresses (
  actress_id INTEGER PRIMARY KEY NOT NULL UNIQUE,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL
);

-- create award table
CREATE TABLE awards (
  award_id INTEGER PRIMARY KEY NOT NULL UNIQUE,
  award_name TEXT NOT NULL
);

-- end creating tables

-- start inserting values into tables

-- insert values into customer table 
INSERT INTO customer VALUES (1, 'Beth', 'Smith', '123 Apple Street', 
'Baltimore', 'MD', '20400', '111-111-1111');
INSERT INTO customer VALUES (2, 'Maya', 'Baker', '456 Pear Street', 
'Baltimore', 'MD', '30411', '222-222-2222');
INSERT INTO customer VALUES (3, 'Carol', 'Lee', '999 Cherry Road', 
'Baltimore', 'MD', '20412', '333-333-3333');
INSERT INTO customer VALUES (4, 'Linus', 'Brown', '254 Orange Street', 
'Baltimore', 'MD', '21411', '444-444-4444');
INSERT INTO customer VALUES (5, 'Victor', 'Moore', '802 Pear Street', 
'Baltimore', 'MD', '30411', '555-555-5555');
INSERT INTO customer VALUES (6, 'Emma', 'Meek', '212 Pine Court', 
'Baltimore', 'MD', '25111', '666-666-6666');
INSERT INTO customer VALUES (7, 'Matt', 'Morris', '333 Oak Lane', 
'Baltimore', 'MD', '23222', '777-777-7777');
INSERT INTO customer VALUES (8, 'Lydia', 'Lorne', '121 Willow Street', 
'Baltimore', 'MD', '26721', '888-888-8888');
INSERT INTO customer VALUES (9, 'Eric', 'Newman', '544 Ash Street', 
'Baltimore', 'MD', '24876', '999-999-9999');
INSERT INTO customer VALUES (10, 'Keith', 'White', '298 Lime Road', 
'Baltimore', 'MD', '26765', '101-101-1010');

-- insert values into rental table
INSERT INTO rental VALUES (11, 2021-12-01, 2021-12-31, 2021-12-10, 0, 3.00, 
NULL, NULL, NULL, 0.50, NULL);
INSERT INTO rental VALUES (22, 2021-12-02, 2021-01-01, 2021-12-07, 0, 3.00, 
NULL, NULL, NULL, 0.50, NULL);
INSERT INTO rental VALUES (33, 2021-12-03, 2021-01-02, 2021-12-19, 0, 3.00, 
NULL, NULL, NULL, 0.50, NULL);
INSERT INTO rental VALUES (44, 2021-12-04, 2021-01-03, 2021-12-14, 0, 3.00, 
NULL, NULL, 1.00, 0.50, NULL);
INSERT INTO rental VALUES (55, 2021-12-05, 2021-01-04, 2021-12-11, 0, 3.00, 
NULL, NULL, NULL, 0.50, NULL);
INSERT INTO rental VALUES (66, 2021-12-06, 2021-01-05, 2021-12-15, 0, 3.00, 
NULL, NULL, NULL, 0.50, NULL);
INSERT INTO rental VALUES (77, 2021-12-07, 2021-01-06, NULL, 0, 3.00, 
NULL, NULL, NULL, 0.50, NULL);
INSERT INTO rental VALUES (88, 2021-12-08, 2021-01-07, 2021-12-18, 0, 3.00, 
NULL, 3.00, NULL, 0.50, NULL);
INSERT INTO rental VALUES (99, 2021-12-09, 2021-01-08, 2021-12-15, 0, 3.00, 
NULL, NULL, NULL, 0.50, NULL);
INSERT INTO rental VALUES (110, 2021-12-10, 2021-01-09, 2021-12-19, 0, 3.00, 
NULL, NULL, NULL, 0.50, NULL);

-- insert values into distributor table
INSERT INTO distributor VALUES (000, 'Dist A');
INSERT INTO distributor VALUES (111, 'Dist B');
INSERT INTO distributor VALUES (222, 'Dist C');
INSERT INTO distributor VALUES (333, 'Dist D');
INSERT INTO distributor VALUES (444, 'Dist E');
INSERT INTO distributor VALUES (555, 'Dist F');
INSERT INTO distributor VALUES (666, 'Dist G');
INSERT INTO distributor VALUES (777, 'Dist H');
INSERT INTO distributor VALUES (888, 'Dist I');
INSERT INTO distributor VALUES (999, 'Dist J');

-- insert values into dist_movie table
INSERT INTO dist_movie VALUES (1000);
INSERT INTO dist_movie VALUES (1001);
INSERT INTO dist_movie VALUES (1002);
INSERT INTO dist_movie VALUES (1003);
INSERT INTO dist_movie VALUES (1004);
INSERT INTO dist_movie VALUES (1005);
INSERT INTO dist_movie VALUES (1006);
INSERT INTO dist_movie VALUES (1007);
INSERT INTO dist_movie VALUES (1008);
INSERT INTO dist_movie VALUES (1009);

-- insert values into dist_video table
INSERT INTO dist_video VALUES (10000);
INSERT INTO dist_video VALUES (10001);
INSERT INTO dist_video VALUES (10002);
INSERT INTO dist_video VALUES (10003);
INSERT INTO dist_video VALUES (10004);
INSERT INTO dist_video VALUES (10005);
INSERT INTO dist_video VALUES (10006);
INSERT INTO dist_video VALUES (10007);
INSERT INTO dist_video VALUES (10008);
INSERT INTO dist_video VALUES (10009);

-- insert values into dist_dvd table
INSERT INTO dist_dvd VALUES (1000001);
INSERT INTO dist_dvd VALUES (1000002);
INSERT INTO dist_dvd VALUES (1000003);
INSERT INTO dist_dvd VALUES (1000004);
INSERT INTO dist_dvd VALUES (1000005);
INSERT INTO dist_dvd VALUES (1000006);
INSERT INTO dist_dvd VALUES (1000007);
INSERT INTO dist_dvd VALUES (1000008);
INSERT INTO dist_dvd VALUES (1000009);
INSERT INTO dist_dvd VALUES (1000010);

-- insert values into movie table
INSERT INTO movie VALUES (5000, 'Forrest Gump', 'Tom Hanks', 'Robin Wright', 200,
'Drama', 'R', '1994', 'Robert Zemeckis', 'Best Picture', 1000);
INSERT INTO movie VALUES (5001, 'Shawshank Redemption', 'Morgan Freeman',
'Renee Blaine', 220, 'Drama', 'R', '1994', 'Frank Darabont', 'Actor', 1001);
INSERT INTO movie VALUES (5002, 'Moonlight', 'Mahershala Ali', 'Naomie Harris', 120,
'Drama', 'R', '2016', 'Barry Jenkins', 'Best Picture', 1002);
INSERT INTO movie VALUES (5003, 'La La Land', 'Ryan Gosling', 'Emma Stone', 126,
'Musical', 'PG-13', '2016', 'Damien Chazelle', 'Best Actress', 1003);
INSERT INTO movie VALUES (5004, 'The Thing', 'Kurt Russell', 'Kim Bubbs', 110,
'Horror', 'R', '1980', 'John Carpenter', 'Best Picture', 1004);
INSERT INTO movie VALUES (5005, 'The Matrix', 'Keanu Reeves', 'Carrie-Ann Moss', 132,
'Sci-Fi', 'R', '1999', 'Lana Wachowski', 'Sound Mixing', 1005);
INSERT INTO movie VALUES (5006, 'Home Alone', 'Macaulay Culkin', 'Catherine O Hara', 115,
'Comedy', 'PG', '1990', 'Chris Columbus', NULL, 1006);
INSERT INTO movie VALUES (5007, '27 Dresses', 'James Marsden', 'Katherine Heigl', 108,
'Romance', 'PG-13', '2008', 'Anne Fletcher', NULL, 1007);
INSERT INTO movie VALUES (5008, 'Devil Wears Prada', 'Stanley Tucci', 'Anne Hathaway',
125, 'Comedy', 'PG-13', '2006', 'David Frankel', 'Best Actress', 1008);
INSERT INTO movie VALUES (5009, 'Spirited Away', 'Miyu Irino', 'Daveigh Chase', 118,
'Fantasy', 'PG', '2001', 'Hayao Miyazaki', 'Best Animated Feature', 1009);


-- insert values into video table
INSERT INTO video VALUES (6000, 5.00, 'Drama', 9000);
INSERT INTO video VALUES (6001, 6.50, 'Romance', 9001);
INSERT INTO video VALUES (6002, 7.00, 'Horror', 9002);
INSERT INTO video VALUES (6003, 5.75, 'Drama', 9003);
INSERT INTO video VALUES (6004, 8.00, 'Comedy', 9004);
INSERT INTO video VALUES (6005, 7.00, 'Comedy', 9005);
INSERT INTO video VALUES (6006, 6.20, 'Romance', 9006);
INSERT INTO video VALUES (6007, 6.00, 'Drama', 9007);
INSERT INTO video VALUES (6008, 5.00, 'Sci-Fi', 9008);
INSERT INTO video VALUES (6009, 6.70, 'Drama', 9009);

-- insert values into dvd table
INSERT INTO dvd VALUES (7000, 5.00, 'Drama', 9000);
INSERT INTO dvd VALUES (7001, 6.50, 'Romance', 9001);
INSERT INTO dvd VALUES (7002, 7.00, 'Horror', 9002);
INSERT INTO dvd VALUES (7003, 5.75, 'Drama', 9003);
INSERT INTO dvd VALUES (7004, 8.00, 'Comedy', 9004);
INSERT INTO dvd VALUES (7005, 7.00, 'Comedy', 9005);
INSERT INTO dvd VALUES (7006, 6.20, 'Romance', 9006);
INSERT INTO dvd VALUES (7007, 6.00, 'Drama', 9007);
INSERT INTO dvd VALUES (7008, 5.00, 'Sci-Fi', 9008);
INSERT INTO dvd VALUES (7009, 6.70, 'Drama', 9009);

-- insert values into actor table
INSERT INTO actors VALUES (1200, 'Tom', 'Hanks');
INSERT INTO actors VALUES (1201, 'Morgan', 'Freeman');
INSERT INTO actors VALUES (1202, 'Brad', 'Pitt');
INSERT INTO actors VALUES (1203, 'Ryan', 'Gosling');
INSERT INTO actors VALUES (1204, 'Denzel', 'Washington');
INSERT INTO actors VALUES (1205, 'Johnny', 'Depp');
INSERT INTO actors VALUES (1206, 'Leonardo', 'DiCaprio');
INSERT INTO actors VALUES (1207, 'Keanu', 'Reeves');
INSERT INTO actors VALUES (1208, 'Will', 'Smith');
INSERT INTO actors VALUES (1209, 'Christian', 'Bale');

-- insert values into actress table
INSERT INTO actresses VALUES (1300, 'Anne', 'Hathaway');
INSERT INTO actresses VALUES (1301, 'Robin', 'Wright');
INSERT INTO actresses VALUES (1302, 'Katherine', 'Heigl');
INSERT INTO actresses VALUES (1303, 'Renee', 'Blaine');
INSERT INTO actresses VALUES (1304, 'Naomie', 'Harris');
INSERT INTO actresses VALUES (1305, 'Emma', 'Stone');
INSERT INTO actresses VALUES (1306, 'Kim', 'Bubbs');
INSERT INTO actresses VALUES (1307, 'Carrie-Ann', 'Moss');
INSERT INTO actresses VALUES (1308, 'Catherine', 'O Hara');
INSERT INTO actresses VALUES (1309, 'Daveigh', 'Chase');


-- insert values into award table
INSERT INTO awards VALUES (300, 'Best Picture');
INSERT INTO awards VALUES (301, 'Best Actor');
INSERT INTO awards VALUES (302, 'Best Actress');
INSERT INTO awards VALUES (303, 'Best Sound Mixing');
INSERT INTO awards VALUES (304, 'Best Director');
INSERT INTO awards VALUES (305, 'Best Animated Feature');
INSERT INTO awards VALUES (306, 'Best Supporting Actor');
INSERT INTO awards VALUES (307, 'Best Supporting Actress');
INSERT INTO awards VALUES (308, 'Best Cinematography');
INSERT INTO awards VALUES (309, 'Best Visual Effects');


-- end inserting values into table




-- STEP 4

-- Retrieve all of your customers' names, account numbers, and addresses
-- (street and zip code only), sorted by account number.

SELECT customer_id, first_name, last_name, street_address, zip_code FROM customer
ORDER BY customer_id;


-- Retrieve all of the videos rented in the last 30 days and sort in chronological
-- rental date order.

SELECT * from rental
ORDER BY rental_date;


-- Produce a list of your distributors and all their information sorted
-- in order by company name.

SELECT * from distributor
ORDER BY dist_name;

-- Update customer names to change their maiden names to married names. You can choose 
-- which records to update.

UPDATE customer
SET last_name='Black'
WHERE customer_id='3';

UPDATE customer
SET last_name='Poole'
WHERE customer_id='6';

-- Delete customers from the database. You can choose which records to delete.

DELETE FROM customer WHERE customer_id = 1;
DELETE FROM customer WHERE customer_id = 2;