Assistance needed with SQL commands

I need assistance with the query commands assigned to an assignment. I have the databases properly created and do not need assistance with the commands associated with creating the databases. Here is the complete assignment. I have attached the database information.

Save Time On Research and Writing
Hire a Pro to Write You a 100% Plagiarism-Free Paper.
Get My Paper

  

The structure of the movies database is as follows:

Director (

DIRNUB

Save Time On Research and Writing
Hire a Pro to Write You a 100% Plagiarism-Free Paper.
Get My Paper

, DIRNAME, DIRBORN, YR-DIRDIED)

STAR (

STARNUB

, STARNAME, BIRTHPLACE, STARBORN, YR-STARDIED)

MOVIE (

MVNUB

, MVTITLE, YRMDE, MVTYPE, CRIT, MPAA, NOMINATIONS, AWRD,

DIRNUB)

MOVSTAR (

MVNUB, STARNUB

, AMTPAID)

MEMBER (

MMBNUB

, MMBNAME, MMBADD, MMBCITY, MMBST, NUMRENT, BONUS, JOINDATE)

TAPE (

TAPENUM,

MVNUB, PURDATE, TMSRNT,

MMBNUB) 

Create Video Store database as discussed in the class. Make sure to correct column widths/types before creating tables. Use SQL to form queries to produce the following reports

:  

  1. ** List the names and numbers of directors whose names begin with the alphabet ‘K’.
  2. List the tape no, movie title, and the membership number and name of members, who are currently borrowing tapes numbered below 20. Arrange the report in descending order by tape number.
  3. List the names and respective numbers of stars and directors who have worked together.
  4. ** List the tape numbers for movies of movie type: ‘HORROR’.
  5. List the name of the director who has received the maximum number of total awards considering all his/her movies: AWRD.
  6. ** List the names of all members who have not borrowed any movie currently.
  7. List the movie type and number of tapes for each type in the database.
  8. ** For each movie list total how many times it has been rented: TMSRNT.
  9. Report the total times rented (TMSRNT) for each movie type.
  10. The database administrator discovers that the name of director whose number is 7 in the database should be spelt as ‘JOHNNY FORD’. Make corrections to the data.
  11. Delete the movie number 14 and all its tapes. Print both tables to verify.
  12. List all tape numbers and their movie titles, and indicate the member number and member name if the tape is currently rented out.

13. List all tape numbers, and also indicate the member’s city if a tape is currently rented out by a member.

14. Who is the youngest director?

 

How many movies did he/she direct?

15. Grant access to me (joshi) to your movstar table for select and update.

16. Create a unique index on movstar table.

17. For each movie type list the average age of movies given the current year is 2011.

18. ** Create a view MEMB_TAPES that includes the currently rented movies and the members who are renting them, include movie type.

19. ** Use the view MEMB_TAPES to find all currently rented “COMEDY” type movies and members who are renting them.

20. ** List all tape numbers, along with movie name and member name if rented out (leave member name blank if not rented out).

  

drop table director;
create table director (dirnumb int, dirname char(36), dirborn int, dirdied int);

insert into director values (1, ‘Allen, Woody’ , 1935, null);
insert into director values (2, ‘Hitchcock, Alfred’, 1899, 1980);
insert into director values (3, ‘DeMille, Cecil B.’, 1881, 1959);
insert into director values (4, ‘Kramer, Stanley’, 1913, null);
insert into director values (5, ‘Kubrick, Stanley’, 1928, 1999);
insert into director values (6, ‘Preminger, Otto’, 1906, null);
insert into director values (7, ‘Ford, John’, 1895, null);

drop table movie;
create table movie (mvnumb int, mvtitle char(100), yrmde int, mvtype char(9), crit int, mpaa char(6), noms int, awrd int, dirnumb int);

insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (1, ‘Annie Hall’, 1977, ‘COMEDY’, 4, ‘PG’, 5, 4, 1);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (2, ‘Dr. Strangelove’, 1964, ‘COMEDY’, 4, ‘PG’, 4, 0, 5);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (3, ‘Clockwork Orange’, 1971, ‘SCI FI’, 4, ‘R’, 3, 0, 5);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (4, ‘North by Northwest’, 1959, ‘SUSPEN’, 4, ‘PG’, 1, 0, 2);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (5, ‘Rope’,1948, ‘SUSPEN’, 3, ‘NR’, 0, 0, 2);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (6, ‘Psycho’, 1960, ‘HORROR’, 4, ‘PG’, 3, 0, 2);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (7, ‘Interiors’, 1978, ‘DRAMA’, 3, ‘PG’, 3, 0, 1);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (8, ‘The Birds’, 1963, ‘HORROR’, 3, ‘NR’, 0, 0, 2);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (9, ‘Samson and Delilah’, 1949, ‘RELIGI’, 2, ‘NR’, 1, 0, 3);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (10, ‘Guess Whois Coming to Dinner’, 1967, ‘COMEDY’, 3, ‘NR’, 6, 2, 4);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (11, ‘Manhattan’, 1979, ‘COMEDY’, 4, ‘R’, 2, 0, 1);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (12, ‘Vertigo’, 1958, ‘SUSPEN’, 4, ‘NR’, 0, 0, 2);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (13, ‘Judgement at Nuremberg’, 1961, ‘DRAMA’, 3, ‘NR’, 6, 3, 4);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (14, ‘2001’, 1968, ‘SCI FI’, 4, ‘G’, 2, 0, 5);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (15, ‘The Man with the Golden Arm’, 1955, ‘DRAMA’, 3, ‘NR’, 1, 0, 6);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (16, ‘Anatomy of a Murder’, 1959, ‘SUSPEN’, ‘4’, ‘NR’, 4, 0, 6);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (17, ‘Inherit the Wind’, 1960, ‘DRAMA’, ‘4’, ‘NR’, 2, 0, 4);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (18, ‘Laura’, 1944, ‘SUSPEN’, 4, ‘NR’, 3, 1, 6);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (19, ‘The Ten Commandments’, 1956, ‘RELIGI’, 3, ‘NR’, 1, 0, 3);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (20, ‘The Moon is Blue’, 1953, ‘COMEDY’, 2, ‘NR’, 1, 0, 6);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (21, ‘Stagecoach’, 1939, ‘WESTER’, 4, ‘NR’, 3, 1, 7);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (22, ‘Rear Window’, 1954, ‘SUSPEN’, 4, ‘NR’, 1, 0, 2);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (23, ‘Mogambo’, 1953, ‘WESTER’, 3, ‘NR’, 2, 0, 7);
insert into movie (mvnumb, mvtitle, yrmde, mvtype, crit, mpaa, noms, awrd, dirnumb) values (24, ‘Grapes of Wrath’, 1940, ‘DRAMA’, 4, ‘NR’, 4, 2, 7);

drop table tape;

create table tape (tapenum char (7), mvnub char (5), purdate char(9), tmsrnt int, mmbnub char (6));

insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (1, 1, ‘4/26/94′, 4, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (2, 2, ’04/26/94′, 2, 2);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (3, 3, ’04/26/94′, 6, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (4, 4, ’04/28/94′, 8, 10);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (5, 5, ’05/12/94′, 3, 4);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (6, 6, ’05/12/94′, 8, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (7, 7, ’05/12/94′, 2, 2);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (8, 8, ’05/12/94′, 9, 8);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (9, 6, ’06/26/94′, 1, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (10, 9, ’06/26/94′, 7, 3);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (11, 10, ’06/26/94′, 10, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (12, 11, ’07/11/94′, 6, 6);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (13, 12, ’08/2/94′, 4, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (14, 6, ’08/2/94′, 5, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (15, 13, ’08/25/94′, 2, 2);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (16, 14, ’08/25/94′, 7, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (17, 15, ’09/7/94′, 11, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (18, 16, ’09/7/94′, 6, 8);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (19, 17, ’09/23/94′, 3, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (20, 14, ’10/12/94′, 4, 3);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (21, 18, ’11/15/94′, 8, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (22, 19, ’11/15/94′, 3, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (23, 20, ’12/21/94′, 4, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (24, 21, ’01/11/95′, 9, 7);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (25, 22, ’02/14/95′, 2, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (26, 23, ’02/14/95′, 1, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (27, 24, ’03/6/95’, 4, 3);

drop table member;
create table member (mmbnumb int, mmbname char(36), mmbaddr char(60), mmbcity char(30), mmbst char(2), numrent int, bonus int, joindate char(8));

insert into member (mmbnumb, mmbname, mmbaddr, mmbcity, mmbst, numrent, bonus, joindate) values (1, ‘Allen, Donna’, ’21 Wilson’, ‘Carson’, ‘In’, 2, 0, ‘5/25/95’);
insert into member (mmbnumb, mmbname, mmbaddr, mmbcity, mmbst, numrent, bonus, joindate) values (2, ‘Peterson, Mark’, ‘215 Raymond’, ‘Cedar’, ‘In’, 14, 1, ‘2/20/94’);
insert into member (mmbnumb, mmbname, mmbaddr, mmbcity, mmbst, numrent, bonus, joindate) values (3, ‘Sanchez, Miguel’, ’47 Chipwood’, ‘Mantin’, ‘Il’, 22, 0, ‘6/14/94’);
insert into member (mmbnumb, mmbname, mmbaddr, mmbcity, mmbst, numrent, bonus, joindate) values (4, ‘Tran, Thanh’, ‘108 College’, ‘Carson’, ‘In’, 3, 0, ‘7/3/95’);
insert into member (mmbnumb, mmbname, mmbaddr, mmbcity, mmbst, numrent, bonus, joindate) values (5, ‘Roberts, Terry’, ‘602 Bridge’, ‘Hudson’, ‘Mi’, 1, 0, ’11/16/94′);
insert into member (mmbnumb, mmbname, mmbaddr, mmbcity, mmbst, numrent, bonus, joindate) values (6, ‘MacDonald, Greg’, ’19 Oak’, ‘Carson’, ‘In’, 11, 1, ‘1/29/95’);
insert into member (mmbnumb, mmbname, mmbaddr, mmbcity, mmbst, numrent, bonus, joindate) values (7, ‘VanderJagt, Neal’, ’12 Bishop’, ‘Mantin’, ‘Il’, 19, 2, ‘8/11/94’);
insert into member (mmbnumb, mmbname, mmbaddr, mmbcity, mmbst, numrent, bonus, joindate) values (8, ‘Shippers, John’, ‘208 Grayton’, ‘Cedar’, ‘In’, 6, 1, ‘9/2/95’);
insert into member (mmbnumb, mmbname, mmbaddr, mmbcity, mmbst, numrent, bonus, joindate) values (9, ‘Franklin, Trudy’, ‘103 Bedford’, ‘Brook’, ‘Mi’, 27, 3, ’12/13/94′);
insert into member (mmbnumb, mmbname, mmbaddr, mmbcity, mmbst, numrent, bonus, joindate) values (10, ‘Stein, Shelly’, ’82 Harcourt’, ‘Hudson’, ‘Mi’, 4, 0, ‘6/21/95’);

drop table star;
create table star (starnumb int, starname char(36), brthplce char(100), starborn int, stardied int);

insert into star (starnumb, starname, brthplce, starborn, stardied) values (1, ‘Allen, Woody’, ‘New York’, 1935, null);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (2, ‘Keaton, Diane’, ‘Los Angeles’, 1946, null);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (3, ‘Sellers, Peter’, ‘Southsea, Eng.’, 1925, null);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (4, ‘Scott, George C.’, ‘Wise, Va.’, 1927, 1980);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (5, ‘McDowell, Malcom’, ‘Leeds, Eng.’, 1943, null);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (6, ‘Grant, Cary’, ‘Bristol, Eng.’, 1904, null);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (7, ‘Saint, Eva Marie’, ‘Newark, N.J.’, 1929, 1986);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (8, ‘Stewart, James’, ‘Indiana, Pa.’, 1908, null);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (9, ‘Perkins, Anthony’, ‘New York’, 1932, null);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (10, ‘Leigh Janet’, ‘Merced, Cal’, 1927, null);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (11, ‘Taylor, Rod’, ‘Sydne, Australia’, 1930, null);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (12, ‘Hedren, Tippi’, ‘Lafayette, Minn.’, 1935, null);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (13, ‘Mature, Victor’, ‘Louisville, Ky.’, 1916, null);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (14, ‘Tracy, Spencer’, ‘Milwaukee’, 1900, 1967);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (15, ‘Hepburn, Katharine’, ‘Hartford’, 1909, null);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (16, ‘Dullea, Keir’, ‘Clevelland’, 1939, null);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (17, ‘Novak, Kim’, ‘Chicago’, 1933, null);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (18, ‘Sinatra, Frank’, ‘Hoboken, N.J.’, 1915, null);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (19, ‘March, Fredric’, ‘Racine, Wis’, 1897, 1975);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (20, ‘Andrews, Dana’, ‘Collins, Miss.’, 1912, null);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (21, ‘Heston, Charlton’, ‘Evanston, Ill.’, 1923, null);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (22, ‘McNamara, Maggie’, ‘New York’, 1928, 1978);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (23, ‘Niven, David’, ‘Kirriemuir, Scot.’, 1910, 1983);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (24, ‘Wayne, John’, ‘Winterset, Iowa’, 1907, 1979);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (25, ‘Gable, Clark’, ‘Cadiz, O.’, 1901, 1960);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (26, ‘Kelly, Grace’, ‘Philadelphia’, 1929, 1982);
insert into star (starnumb, starname, brthplce, starborn, stardied) values (27, ‘Fonda, Henry’, ‘Grand Island, Neb.’, 1905, 1982);

drop table movstar;
create table movstar (mvnumb int, starnumb int);

insert into movstar (mvnumb, starnumb) values (1, 1);
insert into movstar (mvnumb, starnumb) values (1, 2);
insert into movstar (mvnumb, starnumb) values (2, 3);
insert into movstar (mvnumb, starnumb) values (2, 4);
insert into movstar (mvnumb, starnumb) values (3, 5);
insert into movstar (mvnumb, starnumb) values (4, 6);
insert into movstar (mvnumb, starnumb) values (4, 7);
insert into movstar (mvnumb, starnumb) values (5, 8);
insert into movstar (mvnumb, starnumb) values (6, 9);
insert into movstar (mvnumb, starnumb) values (6, 10);
insert into movstar (mvnumb, starnumb) values (7, 2);
insert into movstar (mvnumb, starnumb) values (8, 11);
insert into movstar (mvnumb, starnumb) values (8, 12);
insert into movstar (mvnumb, starnumb) values (9, 13);
insert into movstar (mvnumb, starnumb) values (10, 14);
insert into movstar (mvnumb, starnumb) values (10, 15);
insert into movstar (mvnumb, starnumb) values (11, 1);
insert into movstar (mvnumb, starnumb) values (11, 2);
insert into movstar (mvnumb, starnumb) values (12, 8);
insert into movstar (mvnumb, starnumb) values (12, 17);
insert into movstar (mvnumb, starnumb) values (13, 14);
insert into movstar (mvnumb, starnumb) values (14, 16);
insert into movstar (mvnumb, starnumb) values (15, 17);
insert into movstar (mvnumb, starnumb) values (15, 18);
insert into movstar (mvnumb, starnumb) values (16, 8);
insert into movstar (mvnumb, starnumb) values (17, 14);
insert into movstar (mvnumb, starnumb) values (17, 19);
insert into movstar (mvnumb, starnumb) values (18, 20);
insert into movstar (mvnumb, starnumb) values (19, 21);
insert into movstar (mvnumb, starnumb) values (20, 22);
insert into movstar (mvnumb, starnumb) values (20, 23);
insert into movstar (mvnumb, starnumb) values (21, 24);
insert into movstar (mvnumb, starnumb) values (22, 8);
insert into movstar (mvnumb, starnumb) values (22, 26);
insert into movstar (mvnumb, starnumb) values (23, 25);
insert into movstar (mvnumb, starnumb) values (23, 26);
insert into movstar (mvnumb, starnumb) values (24, 27);

Still stressed from student homework?
Get quality assistance from academic writers!

Order your essay today and save 25% with the discount code LAVENDER