Question

You work for a DVD rental company. Your manager has asked you to pull some data on the company, its inventory, and customers. You will complete this by developing and running SQL queries. Initialize Your Database (NOTE: If you alreody did this in Project 1, you do not need to do this step again): 1. Download RentalCompany.sql from Doc Sharing. Run the script to create tables and data. You should not receive errors. There may be a few warnings, but that is okay To verify you created all tables, run the following SQL: a. b. SELECT TABLE NAME, TABLE ROWS, TABLE TYPE, CREATE TIME FROM information schema.'tables' WHERE table schema,'rentalcompany, AND TABLE TYPE= BASE TABLE Your results should look like the table below. This will help verify that you properly created the schema/database and tables 200 BASE TABLE 603 BASE TABLE 16 BASE TABLIE 600 BASE TABLIE 109 BASE TABLE actor address category city country customer 599 BASE TABLE film film actor 5462 BASE TABLE film category 1000 BASE TABLE film text inventory language payment rental staff store 1000 BASE TABLE 1000 BASE TABLE 4581 BASE TABLE 6 BASE TABLE 16086 BASE TABLE 16005 BASE TABLE 2 BASE TABLE Add one new customer and provide data for all columns on the table for the new customer. Use your own data to enter (ex: enter your name as first and last name). 3. 4. Create a new actor record on the actor table for Kirk James by copying the record for Russell Bocall 5. Delete language IDs from the language table that have no films assigned to them. Grading

Answer 1

create table customer(store_id number,
                       active int,
                   first_name varchar(20),
                   last_name varchar(20),
                   address varchar(20));
                  
create table actor(id int,first_name varchar(20),
                   last_name varchar(20),
                   address varchar(20));
                  
                  
create table film(id int primary key,
                   title varchar(20),
                   rental_duration int);
                  
                  

create table language(language_id int,
                       language_name varchar(20),
                       film_id int,
                       foreign key(film_id) references film(id));
                  
insert into customer values(1,111,'Vera','Fuselier','abc');
insert into customer values(2,121,'Kacy', 'Barnett','def');
insert into customer values(3,131,'Lucia', 'Tiger','ght');
insert into customer values(4,141,'robert', 'Smith','klm');
insert into customer values(5,151,'Minta','Barnett','nop');

                  
insert into actor values(1,'Russell','Bacall','abfh');
insert into actor values(2,'Kirk', 'James','gkjj');
insert into actor values(3,'james','smith','dkjkjd');

insert into film values(1,'harry potter',11);
insert into film values(2,'operation rocket',12);
insert into film values(3,'anabAge',13);
insert into film values(4,'fault in our Age',14);
insert into film values(5,'game of throwns',23);

                      
insert into language values(1,'engilsh',1);
insert into language values(2,'engilsh',null);
insert into language values(3,'engilsh',null);
insert into language values(4,'engilsh',1);

3rd query
insert into Customer values(6,156,'brant','kacy','fed');

4TH QUERY

update actor
set id=(select id from actor where first_name='Russell' and last_name='Bacall'),address=(select address from actor where first_name='Russell' and last_name='Bacall')
where first_name='Kirk' and last_name='James';

5TH QUERY

delete from language where film_id is null;

SQL> SELECT FROM CUSTOMER; STORE ID ACTIUE FIRST NAME LAST NAME ADDRESS 1 2 3 111 Uera 121 Kacy 131 Lucia 141 robert 151 Minta Fuselier Barnett Tiger Smith Barnett abc def ght klm nop 5 SQL> SELECT FROM FILM; ID TITLE RENTAL DURATION 1 harry potter 2 operation rocket 3 anabAge 4 fault in our Age 5 game of throwns 12 13 14 23 SQL> SELECTFROM ACTOR; ID FIRST NAME LAST NAME ADDRESS 1 Russell 2 Kirk 3 james Bacall James smith abfh gkjj dkjkjd SQL> SELECT FROM LANGUAGE; LANGUAGE_ID LANGUAGENAME FILM ID 1 engilsh 2 engilsh 3 engilsh 4 engilsh

so insert into Custoner values(6,156, brant , kacy'.'fed' 1 row created

SQL> SELECTFROM CUSTOMER; STOREID HAME---- ACTIUE FIRST NAME LAST_NAME ADDRESS - 111 Uera 121 Kacy 131 Lucia 141 robert 151 Minta 156 brant Fuselier Barnett Tiger Smith Barnett kacy abc def ght klm nop Fed 2 6 rows selected. SQL> SELECT FROM ACTOR; ID FIRST NAME LAST NAME ADDRESS abfh abfh 1 Russell Bacall James smith 1 Kirk 3 james SQL> SELECT FROM FILM; ID TITLE dkjkjd RENTAL _DURATION harry potter 2 operation rocket 3 anabAge 4 fault in our Age 5 qame of throwns 1 12 13 14 23 SQL> SELECT FROM LANGUAGE; LANGUAGE ID LANGUAGE NAME FILM ID 1 engilsh 4 engilsh 1