Writing SQL statements.
1. For each movie category, show the number of movies in the database of that category, the total number (sum) of nominations (NOMS) and the average length of that type. Include only those categories for which there are more than 5 movies in the database (i.e. use having). List the output with the greatest number of movies shown first (i.e. the highest count).
2. List the names (first and last) of stars, who are also directors. Include the star’s director number in the results. Please note: the director may not have starred in the movie he/she directed, but has been a star in at least one movie and directed as least one movie.
3. (10 points) List the movies which have more than one director. Include the year, title, last and first name of directors. Sort the output by last name within title. (use subquery)
4. List the movie titles that won both best actor and best actress awards.
5. List the countries (name) where no star was born. (the country name should be listed only once).
6. List the last and first names of all directors who have directed only one movie.
Movies Database
The following is an explanation of the columns in the tables in the Movie Database.
DIRECTOR (DirNum, CntryID, DirBorn, DirDied, Sex, LastName, FirstName)
DirNum – a unique numeric identifier for each director.
CntryID – Code for the country of birth. (Foreign key referencing COUNTRY).
DirBorn –The director’s date of birth (mm/dd/yyyy). Some dates are prior to 1900. Null if unknown.
DirDied – date of death if deceased, otherwise null.
Sex – Gender of the director (M or F).
LastName – Director’s last name.
FirstName – Director’s first name.
MOVIE (MvNum, MvTitle, MvYear, CatID, MPAA, Len, Noms, Awrds, WWGross, USGross,
NonUSGross, CntryID, DirNum, BestDir, BestPic, BestEdit, BestFor, BestWrite)
NonUSGross, CntryID, DirNum, BestDir, BestPic, BestEdit, BestFor, BestWrite)
MvNum – a unique numeric identifier for each movie.
MvTitle – The official title of the movie
MvYear – Usually, the year of release in the U.S. For some foreign movies, the release date in the originating country may have been a year earlier.
CatID – a 3-character code for the category of the movie. This is a foreign key referencing the CATEGORY table which contains an explanation of a code.
MPAA – Designates the U.S. rating of the system: R, PG13, PG, etc. Only movies released since the late 1960’s are rated: older ones are coded NR.
Len – An integer representing the movie’s length in minutes of running time.
Noms – An integer representing the number of Academy Awards for which the movie was nominated.
Awrds – An integer for the number of Academy Awards actually won.
WWGross – The movie’s worldwide gross, in thousands of dollars (as of 12/31/2004).
USGross – The movie’s U.S. gross, in thousands of dollars (as of 12/31/2004).
CntryID – A 2- or 3-character code for the country which produced the movie. A foreign key referencing the COUNTRY table.
DirNum – A foreign key referencing the Director table.
BestDir – A W in this column means that the movie won the Oscar for Best Director for the year of release. An N indicates a nomination for the Oscar but it did not win.
BestPic – W for Best Picture Oscar; N for nominee.
BestEdit – W for the Oscar for best editing; N for nominee.
BestFor – W for the Oscar for best foreign movie; N for nominees.
BestWrite – W for best writing; N for nominees.
MOVDIR (MVNUM, DIRNUM)
(MVNUM, DIRNUM) - composite primary key
MVNUM – A foreign key referencing the Movie table
DIRNUM - A foreign key referencing the Director table.
STAR (StarNum, CntryID, StarBorn, StarDied, Sex, DeathCause, FirstName, LastName, BirthCity,
BirthState)
BirthState)
StarNum – A unique numeric identifier for a movie actor.
CntryID – A 2- or 3-character code for the country where the star was born. A foreign key referencing the COUNTRY table.
StarBorn – Date of birth of the star: mm/dd/yyyy. May be null.
StarDied – Date of death if deceased.
Sex – Gender of the star ( M or F).
DeathCause – If the actor is deceased, the cause of death is indicated here.
FirstName – Star’s first name.
LastName – Star’s last name.
BirthCity – City where the star was born; e.g. New York. If born in Canada, the province is also given: e.g. Montreal, Quebec.
BirthState – State where the star is born.
MOVSTAR ( MvNum, StarNum, BestM, BestF, SupM, SupF)
MvNum – part of the primary key; also a foreign key referencing MOVIE.
StarNum – part of the primary key; also a foreign key referencing STAR.
BestM – A W in this column indicates an Oscar for Best Male Actor for this star. N indicates a nomination for this award.
BestF – W for the Oscar for Best Female Actress; N for the nomination
SupM – W for the Oscar for Best Supporting Male Actor; N for nominees.
SupF - W for the Oscar for Best Supporting Female Actress; N for nominees.
CATEGORY ( CatId, CategoryDesc)
Catid – A 3-character code for the category of the movie.
CategoryDesc – description for the category code.
COUNTRY (CntryID, CountryName)
CntryID – A 2-or 3-characert code for the country.
CountryName – the name of the country.
