Question

Write MySQL query statements for the questions below including the output that proves the accuracy of your solution. Your answers should be stored in a text file that captures your interaction with MySQL.

1. Find the movieID, title, year and DVDPrice of all movies where the DVD-Price is equal to the discountPrice.

2. Find the actorID, lastName, firstName, middleName, and suffix of all actors whose middleName is not NULL.

3. Suppose you remember a movie quote as “Play it again, Sam.” However, when you write a query to find this quote, you get no results. Write a query that will find the text of all movie quotes that begin with the word “Play.”

4. Suppose you remember the role of Joker in one of the Batman movies, but when you write a query to find this role, you get no results. Write a query that will find the roleID and roleName of any role where Joker appears as part of the role name.

5. Find the movieID, title, year, and totalNoms of all movies that were nominated for more than five Awards and were released before 1950.

6. Find the movieID, title, year, and awardsWon of all movies that either won more than five Awards or were released after 1990.

7. Find the movieID, title, year, and discountPrice of all movies with discount prices between $15.00 and $20.00 inclusive.

8. Find the movieID, title, and year of all movies, along with the difference between totalNoms and awardsWon. For the column heading of the calculated column, use “nominated but not won.”

9. Suppose you have a special membership at a store that qualifies you for 10% off all of your purchases, including discount prices. Find the movieID, title, and year of all movies, along with the discount price minus 10% of the discount price. For the column heading of the calculated column, use “my price.”

10. Find the gender and the count (of all rows of that gender) in the Actor table, grouped by gender.

11. Find the totalNoms and the maximum number of awardsWon in the Movie table, grouped by totalNoms and ordered by the maximum number of awardsWon.

12. Find the company, year, and sum of awardsWon for movies, grouped by company and year.

13. Find the actorID, lastName, firstName, middleName,suffix, and birthdate of rows in the Actor table where the gender is F. Order the results by the actor’s date of birth.

14. Find the movieID, title, year, DVDPrice, and half of the DVDPrice (the price for a half-off sale) of movies that have a DVDPrice of at least $20.00.

15. Find the sum of the discount prices of movies made after 1979.

16. Find the awards won and the average discount price of movies made before 1980, grouped by awards won.

17. Find the movieID, title, and year of movies, and the roleID and roleName of roles for the join of the Movie and Role tables where the gender of the role is female.

18. Find the roleID, quoteID, and quoteText in the join between the Quote andRoleQuote tables where the roleID is 00001 or 00003.

19. Find the movie title and the number of roleIDs associated with it. Group the result by movie title, having a count of six or more roles.

20. Find the maximum DVDPrice for a movie with a role played by actorID 00001.

Actor table Actor actorlDlastName firstName middleName suffix gender birthDate deathDate 00001 Hanks 00002 Paxton 00003Bacon 00004 Sinise 00005 Harris 00006 Quinlan 00007 Bogart 00008 Bergman 00009 Henreid 00010 Rains 00011 Veidt 00012 Greenstreet Sydney 00013 Lorre 00014 Wilson 00015 Eastwood 00016 Guardino 00017 Santoni 00018Cruise 00019 Nicholson 00020 Moore 00021 Sutherland 00022 Pollak 00023 Costner 00024 Madigan 00025 Hoffman 00026 Liotta 00027 Goldblum 00028 Davis 00029 Getz 00030 Wright 00031 Williamson 00032 Field 00033 Humphreys 00034 Brando 00035 Pacino 00036 Caan 00037 Castellano M 07/09/1956 M 05/17/1955 M 07/08/1958 om Kevin 03/17/1955 ar Ed Kathleen Humphrey M 11/28/1950 11/19/1954 01/23/1899 01/14/1957 08/29/1913 08/29/1982 M |01/10/1908|03/29/1992 M 11/10/1889 05/30/1967 M 01/22/1893|04/03/1943| M 12/27/1879 01/18/1954 M 06/26/1904 03/23/1964 M 04/03/1886 05/30/1953 Paul Claude Conrad Peter Dooley M05/31/1930 Harry M 12/23/1925 07/17/1995 M 04/21/1939 M 07/03/1962 04/22/1937 11/11/1962 M 12/21/1966 M 10/30/1958 M 01/18/1955 09/11/1950 01/08/1982 M 12/18/1955 M 10/22/1952 01/21/1957 M 10/15/1947 04/08/1966 04/04/1960 11/06/1946 Reni om ?? Demi Kiefer Kevin Kevin Jeff Geena Robin Mykelti Sally onner Marlon M 04/03/1924 07/01/2004 M 04/25/1940 M 03/26/1939 M 09/04/193312/10/1988 ames Richard

--

-- Data for table `actor`

--

('00001','Hanks','Tom',NULL,NULL,'M','1956-07-

09',NULL),('00002','Paxton','Bill',NULL,NULL,'M','1955-05-

17',NULL),('00003','Bacon','Kevin',NULL,NULL,'M','1958-07-

08',NULL),('00004','Sinise','Gary',NULL,NULL,'M','1955-03-

17',NULL),('00005','Harris','Ed',NULL,NULL,'M','1950-11-

28',NULL),('00006','Quinlan','Kathleen',NULL,NULL,'F','1954-11-

19',NULL),('00007','Bogart','Humphrey',NULL,NULL,'M','1899-01-23','1957-

01-14'),('00008','Bergman','Ingrid',NULL,NULL,'F','1913-08-29','1982-08-

29'),('00009','Henreid','Paul',NULL,NULL,'M','1908-01-10','1992-03-

29'),('00010','Rains','Claude',NULL,NULL,'M','1889-11-10','1967-05-

30'),('00011','Veidt','Conrad',NULL,NULL,'M','1893-01-22','1943-04-

03'),('00012','Greenstreet','Sydney',NULL,NULL,'M','1879-12-27','1954-01-

18'),('00013','Lorre','Peter',NULL,NULL,'M','1904-06-26','1964-03-

23'),('00014','Wilson','Dooley',NULL,NULL,'M','1886-04-03','1953-05-

30'),('00015','Eastwood','Clint',NULL,NULL,'M','1930-05-

31',NULL),('00016','Guardino','Harry',NULL,NULL,'M','1925-12-23','1995-07-

17'),('00017','Santoni','Reni',NULL,NULL,'M','1939-04-

21',NULL),('00018','Cruise','Tom',NULL,NULL,'M','1962-07-

03',NULL),('00019','Nicholson','Jack',NULL,NULL,'M','1937-04-

22',NULL),('00020','Moore','Demi',NULL,NULL,'F','1962-11-

11',NULL),('00021','Sutherland','Kiefer',NULL,NULL,'M','1966-12-

21',NULL),('00022','Pollak','Kevin',NULL,NULL,'M','1958-10-

30',NULL),('00023','Costner','Kevin',NULL,NULL,'M','1955-01-

18',NULL),('00024','Madigan','Amy',NULL,NULL,'F','1950-09-

11',NULL),('00025','Hoffman','Gaby',NULL,NULL,'F','1982-01-

08',NULL),('00026','Liotta','Ray',NULL,NULL,'M','1955-12-

18',NULL),('00027','Goldblum','Jeff',NULL,NULL,'M','1952-10-

22',NULL),('00028','Davis','Geena',NULL,NULL,'F','1957-01-

21',NULL),('00029','Getz','John',NULL,NULL,'M','1947-10-

15',NULL),('00030','Wright','Robin',NULL,NULL,'F','1966-04-

08',NULL),('00031','Williamson','Mykelti',NULL,NULL,'M','1960-04-

04',NULL),('00032','Field','Sally',NULL,NULL,'F','1946-11-

06',NULL),('00033','Humphreys','Michael','Conner',NULL,'M',NULL,NULL),('00

034','Brando','Marlon',NULL,NULL,'M','1924-04-03','2004-07-

01'),('00035','Pacino','Al',NULL,NULL,'M','1940-04-

25',NULL),('00036','Caan','James',NULL,NULL,'M','1939-03-

26',NULL),('00037','Castellano','Richard',NULL,NULL,'M','1933-09-

04','1988-12-10'),('00038','Duvall','Robert',NULL,NULL,'M','1931-01-

05',NULL),('00039','Keaton','Diane',NULL,NULL,'F','1946-01-

05',NULL),('00040','Shire','Talia',NULL,NULL,'F','1946-04-

25',NULL),('00041','Mitchell','Thomas',NULL,NULL,'M','1892-07-11','1962-

12-17'),('00042','O\'Neil','Barbara',NULL,NULL,'F','1909-07-10','1980-09-

03'),('00043','Leigh','Vivien',NULL,NULL,'F','1913-11-05','1967-07-

08'),('00044','McDaniel','Hattie',NULL,NULL,'F','1895-06-10','1952-10-

26'),('00045','McQueen','Butterfly',NULL,NULL,'F','1911-01-08','1995-12-

22'),('00046','Gable','Clark',NULL,NULL,'M','1901-02-01','1960-11-

16'),('00047','Gooding','Cuba',NULL,'jr.','M','1968-01-

02',NULL),('00048','Zellweger','Renee',NULL,NULL,'F','1969-04-

25',NULL),('00049','Preston','Kelly',NULL,NULL,'F','1962-10-

13',NULL),('00050','Hellinger','Mark',NULL,NULL,'M','1903-03-21','1947-12-

21'),('00051','Willis','Bruce',NULL,NULL,'M','1955-03-

19',NULL),('00052','Osment','Haley','Joel',NULL,'M','1988-04-

10',NULL),('00053','Collette','Toni',NULL,NULL,'F','1972-11-

01',NULL),('00054','Williams','Olivia',NULL,NULL,'F','1968-07-

26',NULL),('00055','Locke','Sondra',NULL,NULL,'F','1947-05-

28',NULL),('00056','Schwarzenegger','Arnold',NULL,NULL,'M','1947-07-

30',NULL),('00057','Biehn','Michael',NULL,NULL,'M','1956-07-

31',NULL),('00058','Hamilton','Linda',NULL,NULL,'F','1957-09-

26',NULL),('00059','Furlong','Edward',NULL,NULL,'M','1977-08-

02',NULL),('00060','Patrick','Robert',NULL,NULL,'M','1958-11-

05',NULL),('00061','Garland','Judy',NULL,NULL,'F','1922-06-10','1969-06-

22'),('00062','Morgan','Frank',NULL,NULL,'M','1890-06-01','1949-09-

18'),('00063','Bolger','Ray',NULL,NULL,'M','1904-01-10','1987-01-

15'),('00064','Lahr','Bert',NULL,NULL,'M','1895-08-13','1967-12-

04'),('00065','Haley','Jack',NULL,NULL,'M','1898-08-10','1979-06-

06'),('00066','Burke','Billie',NULL,NULL,'F','1885-08-07','1970-05-

14'),('00067','Hamilton','Margaret',NULL,NULL,'F','1902-12-09','1985-05-

16'),('00068','Blandick','Clara',NULL,NULL,'F','1880-06-04','1962-04-

15'),('00069',NULL,'Terry',NULL,NULL,'M',NULL,NULL),('00070','Hoffman','Du

stin',NULL,NULL,'M','1937-08-

08',NULL),('00071','Lange','Jessica',NULL,NULL,'F','1949-04-

20',NULL),('00072','Garr','Teri',NULL,NULL,'F','1944-12-

11',NULL),('00073','Williams','Robin',NULL,NULL,'M','1952-07-

21',NULL),('00074','Brosnan','Pierce',NULL,NULL,'M','1951-05-

16',NULL),('00075','Fierstein','Harvey',NULL,NULL,'M','1954-06-

06',NULL),('00076','Keaton','Michael',NULL,NULL,'M','1951-09-

09',NULL),('00077','Basinger','Kim',NULL,NULL,'F','1953-12-

08',NULL),('00078','DeVito','Danny',NULL,NULL,'M','1944-11-

17',NULL),('00079','Pfeiffer','Michelle',NULL,NULL,'F','1958-04-

29',NULL),('00080','Kilmer','Val',NULL,NULL,'M','1959-12-

31',NULL),('00081','Jones','Tommy','Lee',NULL,'M','1946-09-

15',NULL),('00082','Carrey','Jim',NULL,NULL,'M','1962-01-

17',NULL),('00083','Kidman','Nicole',NULL,NULL,'F','1967-06-

20',NULL),('00084','O\'Donnell','Chris',NULL,NULL,'M','1970-06-

26',NULL),('00085','Barrymore','Drew',NULL,NULL,'F','1975-02-

22',NULL),('00086','Mazar','Debi',NULL,NULL,'F','1964-08-

15',NULL),('00087','Clooney','George',NULL,NULL,'M','1961-05-

06',NULL),('00088','Thurman','Uma',NULL,NULL,'F','1970-04-

29',NULL),('00089','Silverstone','Alicia',NULL,NULL,'F','1976-10-

04',NULL),('00090','Macpherson','Elle',NULL,NULL,'F','1963-03-

29',NULL),('00091','Bale','Christian',NULL,NULL,'M','1974-01-

30',NULL),('00092','Caine','Michael',NULL,NULL,'M','1933-03-

14',NULL),('00093','Neeson','Liam',NULL,NULL,'M','1952-06-

07',NULL),('00094','Holmes','Katie',NULL,NULL,'F','1978-12-

18',NULL),('00095','Walshe','Pat',NULL,NULL,'M','1900-07-26','1991-12-

11'),('00096','Grapewin','Charley',NULL,NULL,'M','1869-12-20','1956-02-

02'),('00097','Hulce','Tom',NULL,NULL,'M','1953-12-

06',NULL),('00098','Abraham','F.','Murray',NULL,'M','1940-10-

24',NULL),('00099','Berridge','Elizabeth',NULL,NULL,'F','1962-05-

02',NULL),('00100','Jones','Jeffrey',NULL,NULL,'M','1947-09-28',NULL);

--

-- Data for table `movie`

--

('001','Apollo 13',1995,'Imagine Entertainment and Universal

Pictures',9,2,12.99,9.99),('002','Casablanca',1943,'Warner

Brothers',8,3,19.99,14.99),('003','Dirty Harry',1971,'The Malpaso

Company',0,0,16.99,16.99),('004','Few Good Men, A',1992,'Castle Rock

Entertainment',4,0,14.99,9.99),('005','Field of Dreams',1989,'Gordon

Company',3,0,12.99,9.99),('006','Fly, The',1986,'Brooksfilms

Limited',1,1,19.99,14.99),('007','Forrest Gump',1994,'Steve Tisch/Wendy

Finerman',13,6,14.99,9.99),('008','Godfather, The',1972,'Albert S.

Ruddy',10,3,14.99,9.99),('009','Gone with the Wind',1939,'Selznick

International Pictures',15,10,19.99,17.99),('010','Jerry

Maguire',1996,'TriStar Pictures',5,1,14.99,9.99),('011','Naked City,

The',1948,'Mark Hellinger',3,2,29.99,29.99),('012','Sixth Sense,

The',1999,'Kennedy/Marshall/Barry Mendel',6,0,14.99,9.99),('013','Sudden

Impact',1983,'The Malpaso Company',0,0,9.99,9.99),('014','Terminator,

The',1984,'Hemdale Film',0,0,14.99,9.99),('015','Terminator 2: Judgment

Day',1991,'CarolCo Pictures',6,4,9.99,7.99),('016','Wizard of Oz,

The',1939,'Metro-Goldwyn-

Mayer',5,2,22.99,22.99),('017','Tootsie',1982,'Mirage

Punch',10,1,14.99,14.99),('018','Mrs. Doubtfire',1993,'20th Century

Fox',1,1,14.99,14.99),('019','Batman',1989,'Warner

Brothers',1,1,26.99,24.99),('020','Batman Returns',1992,'Warner

Brothers',2,0,26.99,24.99),('021','Batman Forever',1995,'Warner

Brothers',3,0,26.99,24.99),('022','Batman & Robin',1997,'Warner

Brothers',0,0,19.99,15.99),('023','Batman Begins',2005,'Warner

Brothers',1,0,14.99,9.99),('024','Amadeus',1984,'Saul Zaentz

Company',11,8,19.99,14.99);

--

-- Data for table `quote`

--

('0001','Houston, we have a problem.'),('0002','Play it, Sam. Play \"As

Time Goes By.\"'),('0003','Of all the gin joints in all the towns in all

the world, she walks into mine.'),('0004','Ilsa, I\'m no good at being

noble, but it doesn\'t take much to see that the problems of three little

people don\'t amount to a hill of beans in this crazy

world.'),('0005','We\'ll always have Paris.'),('0006','Here\'s looking at

you, kid.'),('0007','Round up the usual suspects.'),('0008','Louis, I

think this is the beginning of a beautiful friendship.'),('0009','You\'ve

got to ask yourself one question: \"Do I feel lucky?\" Well, do ya,

punk?'),('0010','You can\'t handle the truth!'),('0011','If you build it,

he will come.'),('0012','Be afraid. Be very afraid.'),('0013','Mama says,

\"Stupid is as stupid does.\"'),('0014','My mama always said, \"Life is

like a box of chocolates. You never know what you\'re gonna

get.\"'),('0015','I\'m going to make him an offer he can\'t

refuse.'),('0016','Leave the gun. Take the cannolis.'),('0017','It\'s a

Sicilian message. It means Luca Brasi sleeps with the

fishes.'),('0018','Fiddle-dee-dee.'),('0019','I don\'t know nothin\'

\'bout birthin\' babies.'),('0020','As God is my witness, I\'ll never be

hungry again.'),('0021','After all, tomorrow is another

day.'),('0022','Frankly, my dear, I don\'t give a damn.'),('0023','Show me

the money!'),('0024','You complete me.'),('0025','You had me at

\"hello.\"'),('0026','There are eight million stories in the naked city.

This has been one of them.'),('0027','I see dead people.'),('0028','Go

ahead, make my day.'),('0029','I\'ll be back.'),('0030','Hasta la vista,

baby.'),('0031','Toto, I have a feeling we\'re not in Kansas

anymore.'),('0032','I\'ll get you, my pretty, and your little dog,

too!'),('0033','Lions and tigers and bears, oh my!'),('0034','I\'m

melting! Melting! Oh, what a world! What a world!'),('0035','Pay no

attention to that man behind the curtain!'),('0036','There\'s no place

like home.'),('0037','Have you ever danced with the Devil in the pale

moonlight?'),('0038','You cursed brat, look what you\'ve

done!'),('0039','Who would have thought a good little girl like you could

destroy my beautiful wickedness?'),('0040','There are in fact only so many

notes the ear can hear in the course of an evening.'),('0041','There are

simply too many notes.'),('0042','I speak for all mediocrities in the

world. I am their champion. I am their patron

saint.'),('0043','Mediocrities everywhere. I absolve you. I absolve you.

I absolve you. I absolve you. I absolve you all.');

--

-- Data for table `role`

--

('00001','Dorothy Gale','F','00061','016'),('00002','The Wizard of

Oz','M','00062','016'),('00003','The

Scarecrow','M','00063','016'),('00004','The Cowardly

Lion','M','00064','016'),('00005','The Tin

Man','M','00065','016'),('00006','Glinda, the Good Witch of the

North','F','00066','016'),('00007','The Wicked Witch of the

West','F','00067','016'),('00008','Auntie

Em','F','00068','016'),('00009','Toto','M','00069','016'),('00010','Gerald

O\'Hara','M','00041','009'),('00011','Ellen

O\'Hara','F','00042','009'),('00012','Scarlett

O\'Hara','F','00043','009'),('00013','Mammy','F','00044','009'),('00014','

Prissy','F','00045','009'),('00015','Rhett

Butler','M','00046','009'),('00016','Rick

Blaine','M','00007','002'),('00017','Ilsa Lund

(Laszlo)','F','00008','002'),('00018','Victor

Laszlo','M','00009','002'),('00019','Captain

Renault','M','00010','002'),('00020','Major

Strasser','M','00011','002'),('00021','Signor

Ferrari','M','00012','002'),('00022','Ugarte','M','00013','002'),('00023',

'Sam','M','00014','002'),('00024','Narrator

(voice)','M','00050','011'),('00025','Inspector Harry

Callahan','M','00015','003'),('00026','Lieutenant Al

Bressler','M','00016','003'),('00027','Inspector Chico

Gonzalez','M','00017','003'),('00028','Don Vito

Corleone','M','00034','008'),('00029','Michael

Corleone','M','00035','008'),('00030','Santino \"Sonny\"

Corleone','M','00036','008'),('00031','Peter

Clemenza','M','00037','008'),('00032','Tom

Hagen','M','00038','008'),('00033','Kay

Adams','F','00039','008'),('00034','Connie Corleone

Rizzi','F','00040','008'),('00035','Inspector Harry

Callahan','M','00015','013'),('00036','Jennifer

Spencer','F','00055','013'),('00037','The

Terminator','M','00056','014'),('00038','Kyle

Reese','M','00057','014'),('00039','Sarah

Connor','F','00058','014'),('00040','Seth

Brundle','M','00027','006'),('00041','Veronica

Quaife','F','00028','006'),('00042','Stathis

Borans','M','00029','006'),('00043','Ray

Kinsella','M','00023','005'),('00044','Annie

Kinsella','F','00024','005'),('00045','Karin

Kinsella','F','00025','005'),('00046','Shoeless Joe

Jackson','M','00026','005'),('00047','The

Terminator','M','00056','015'),('00048','Sarah

Connor','F','00058','015'),('00049','John

Connor','M','00059','015'),('00050','T-

1000',NULL,'00060','015'),('00051','Lieutenant Daniel

Kaffee','M','00018','004'),('00052','Colonel Nathan R.

Jessep','M','00019','004'),('00053','Lieutenant Commander JoAnne

Galloway','F','00020','004'),('00054','Captain Jack

Ross','M','00003','004'),('00055','Lieutenant Jonathan

Kendrick','M','00021','004'),('00056','Lieutenant Sam

Weinberg','M','00022','004'),('00057','Forrest

Gump','M','00001','007'),('00058','Jenny

Curran','F','00030','007'),('00059','Lieutenant Dan

Taylor','M','00004','007'),('00060','Private Benjamin Buford \"Bubba\"

Blue','M','00031','007'),('00061','Mrs.

Gump','F','00032','007'),('00062','Young Forrest

Gump','M','00033','007'),('00063','Jim

Lovell','M','00001','001'),('00064','Fred

Haise','M','00002','001'),('00065','Jack

Swigert','M','00003','001'),('00066','Ken

Mattingly','M','00004','001'),('00067','Gene

Kranz','M','00005','001'),('00068','Marilyn

Lovell','F','00006','001'),('00069','Jerry

Maguire','M','00018','010'),('00070','Rod

Tidwell','M','00047','010'),('00071','Dorothy

Boyd','F','00048','010'),('00072','Avery

Bishop','F','00049','010'),('00073','Dr. Malcolm

Crowe','M','00051','012'),('00074','Cole

Sear','M','00052','012'),('00075','Lynn

Sear','F','00053','012'),('00076','Anna

Crowe','F','00054','012'),('00077','Michael

Dorsey','M','00070','017'),('00078','Dorothy Michaels

(Tootsie)','F','00070','017'),('00079','Julie

Nichols','F','00071','017'),('00080','Sandy

Lester','F','00072','017'),('00081','Daniel

Hillard','M','00073','018'),('00082','Mrs.

Doubtfire','F','00073','018'),('00083','Miranda

Hillard','F','00032','018'),('00084','Stuart \'Stu\'

Dunmeyer','M','00074','018'),('00085','Uncle Frank

Hillard','M','00075','018'),('00086','Professor

Marvel','M','00062','016'),('00087','The

Gatekeeper','M','00062','016'),('00088','The Carriage

Driver','M','00062','016'),('00089','The Guard Who

Cries','M','00062','016'),('00090','Hunk','M','00063','016'),('00091','Zek

e','M','00064','016'),('00092','Hickory','M','00065','016'),('00093','Elmi

ra Gulch','F','00067','016'),('00094','The Wicked Witch of the

East','F','00067','016'),('00095','Batman','M','00076','019'),('00096','Br

uce Wayne','M','00076','019'),('00097','The

Joker','M','00019','019'),('00098','Jack

Napier','M','00019','019'),('00099','Vicki

Hale','F','00077','019'),('00100','Batman','M','00076','020'),('00101','Br

uce Wayne','M','00076','020'),('00102','The

Penguin','M','00078','020'),('00103','Oswald

Cobblepot','M','00078','020'),('00104','Catwoman','F','00079','020'),('001

05','Selina

Kyle','F','00079','020'),('00106','Batman','M','00080','021'),('00107','Br

uce Wayne','M','00080','021'),('00108','Two-

Face','M','00081','021'),('00109','Harvey

Dent','M','00081','021'),('00110','The

Riddler','M','00082','021'),('00111','Dr. Edward

Nygma','M','00082','021'),('00112','Dr. Chase

Meridian','F','00083','021'),('00113','Robin','M','00084','021'),('00114',

'Dick

Grayson','M','00084','021'),('00115','Sugar','F','00085','021'),('00116','

Spice','F','00086','021'),('00117','Mr.

Freeze','M','00056','022'),('00118','Dr. Victor

Fries','M','00056','022'),('00119','Batman','M','00087','022'),('00120','B

ruce

Wayne','M','00087','022'),('00121','Robin','M','00084','022'),('00122','Di

ck Grayson','M','00084','022'),('00123','Poison

Ivy','F','00088','022'),('00124','Dr. Pamela

Isley','F','00088','022'),('00125','Batgirl','F','00089','022'),('00126','

Barbara Wilson','F','00089','022'),('00127','Julie

Madison','F','00090','022'),('00128','Batman','M','00091','023'),('00129',

'Bruce

Wayne','M','00091','023'),('00130','Alfred','M','00092','023'),('00131','H

enri Ducard','M','00093','023'),('00132','Rachel

Dawes','F','00094','023'),('00133','Nikko, the Head Flying

Monkey','M','00095','016'),('00134','Uncle

Henry','M','00096','016'),('00135','Wolfgang Amadeus

Mozart','M','00097','024'),('00136','Antonio

Scalieri','M','00098','024'),('00137','Constanze

Mozart','F','00099','024'),('00138','Emperor Joseph

II','M','00100','024');

--

-- Data for table `rolequote`

--

('00001','0031'),('00001','0033'),('00001','0036'),('00002','0035'),('0000

3','0033'),('00005','0033'),('00007','0032'),('00007','0034'),('00007','00

38'),('00007','0039'),('00012','0018'),('00012','0020'),('00012','0021'),(

'00014','0019'),('00015','0022'),('00016','0003'),('00016','0004'),('00016

','0005'),('00016','0006'),('00016','0008'),('00017','0002'),('00019','000

7'),('00024','0026'),('00025','0009'),('00028','0015'),('00031','0016'),('

00031','0017'),('00035','0028'),('00037','0029'),('00041','0012'),('00046'

,'0011'),('00047','0030'),('00052','0010'),('00057','0014'),('00062','0013

'),('00063','0001'),('00069','0024'),('00070','0023'),('00071','0025'),('0

0074','0027'),('00097','0037'),('00136','0042'),('00136','0043'),('00138',

'0040'),('00138','0041');

Answer 1

1. Find the movieID, title, year and DVDPrice of all movies where the DVD-Price is equal to the discountPrice.

SELECT movieID,title,year,DVDPrice FROM movie WHERE DVDPrice=discountPrice;

2. Find the actorID, lastName, firstName, middleName, and suffix of all actors whose middleName is not NULL.

SELECT actorID,lastName,firstName,middleName,suffix FROM Actor WHERE middleName IS NOT NULL

3. Suppose you remember a movie quote as “Play it again, Sam.” However, when you write a query to find this quote, you get no results. Write a query that will find the text of all movie quotes that begin with the word “Play.”

SELECT quoteText FROM Quote WHERE quoteText LIKE 'play%';

4. Suppose you remember the role of Joker in one of the Batman movies, but when you write a query to find this role, you get no results. Write a query that will find the roleID and roleName of any role where Joker appears as part of the role name.

SELECT roleID,roleName FROM Role WHERE roleName LIKE '%joker%';

5. Find the movieID, title, year, and totalNoms of all movies that were nominated for more than five Awards and were released before 1950.

SELECT movieID,title,year,totalNoms FROM Movie WHERE totalNoms>5 AND year<1950;

6. Find the movieID, title, year, and awardsWon of all movies that either won more than five Awards or were released after 1990.

SELECT movieID,title,year,awardsWon FROM Movie WHERE awardsWon>5 OR year>1990;

7. Find the movieID, title, year, and discountPrice of all movies with discount prices between $15.00 and $20.00 inclusive.

SELECT movieID,title,year,discountPrice FROM Movie WHERE discountPrice BETWEEN 15 AND 20;

8. Find the movieID, title, and year of all movies, along with the difference between totalNoms and awardsWon. For the column heading of the calculated column, use “nominated but not won.”

SELECT movieID,title,year,(totalNoms-awardsWon) AS "nominated But Not Won" from Movie;

9. Suppose you have a special membership at a store that qualifies you for 10% off all of your purchases, including discount prices. Find the movieID, title, and year of all movies, along with the discount price minus 10% of the discount price. For the column heading of the calculated column, use “my price.”

SELECT movieID,title,year,0.9*discountPrice AS "my price" FROM Movie;

10. Find the gender and the count (of all rows of that gender) in the Actor table, grouped by gender

SELECT gender,COUNT(*) FROM Actor GROUPBY gender;

11. Find the totalNoms and the maximum number of awardsWon in the Movie table, grouped by totalNoms and ordered by the maximum number of awardsWon.

SELECT totalNoms,MAX(awardsWon) FROM Movie GROUPBY totalNoms ORDERBY awardsWon DESC;

12. Find the company, year, and sum of awardsWon for movies, grouped by company and year.

SELECT company,year,SUM(awardsWon) FROM Movie GROUPBY company,year;

13. Find the actorID, lastName, firstName, middleName,suffix, and birthdate of rows in the Actor table where the gender is F. Order the results by the actor’s date of birth.

SELECT actorID,lastName,firstName,middleName,suffix,birthdate FROM Actor WHERE gender='F' ORDERBY birthdate;

14. Find the movieID, title, year, DVDPrice, and half of the DVDPrice (the price for a half-off sale) of movies that have a DVDPrice of at least $20.00.

SELECT movieID,title,year,DVDPrice,0.5*DVDPrice FROM Movie WHERE DVDPrice>=20;

15. Find the sum of the discount prices of movies made after 1979.

SELECT SUM(discountPrice) FROM Movie WHERE year>1979;

16. Find the awards won and the average discount price of movies made before 1980, grouped by awards won.

SELECT awardsWon,AVG(discountPrice) FROM Movie WHERE year<1980 GROUPBY awardsWon;

17. Find the movieID, title, and year of movies, and the roleID and roleName of roles for the join of the Movie and Role tables where the gender of the role is female.

SELECT m.movieID,m.title,m.year,r.roleID,r.roleName FROM Movie m INNER JOIN Role r ON m.movieID=r.movieID WHERE r.gender='F';

18. Find the roleID, quoteID, and quoteText in the join between the Quote andRoleQuote tables where the roleID is 00001 or 00003.

SELECT r.roleID,q.quoteID,q.quoteText FROM RoleQuote r INNER JOIN Quote q ON r.quoteID=q.quoteID WHERE r.roleID IN (00001,00003);

19. Find the movie title and the number of roleIDs associated with it. Group the result by movie title, having a count of six or more roles.

SELECT m.title,COUNT(r.roleID) FROM Movie m INNER JOIN Role r ON m.movieID=r.movieID WHERE COUNT(r.roleID>6) GROUPBY m.title;

20. Find the maximum DVDPrice for a movie with a role played by actorID 00001.

SELECT MAX(DVDPrice) FROM Movie m INNER JOIN Role r ON m.movieID=r.movieID WHERE actorID=00001;