CS $631-$ ASSIGNMENT $4$

Due date: See the Assignment page on Canvas

Do answer all items below and submit a word$/$pdf file via Canvas. If you have a question, email me or ask during class hours. This assignment has two parts; Part I and Part II$.$

PART I$-(55$ points$)$ Suppose we have a database consisting of the following three relations

FREQUENTS$($DRINKER$,$ BAR$)$

SERVES$($BAR$,$ BEER$)$

LIKES$($DRINKER$,$ BEER$)$

The first indicates the bars each drinker visits, the second tells what beer each bar serves, the last indicates which beer each drinker likes to drink.

Assume that each drinker likes at least one beer and frequent at least one bar and that a bar serves at least one beer.

Express the queries below in SQL $(5$ points each$).$

$1.$ print the bars that serve a beer John Smith likes.

$2.$ print the drinkers that frequent at least one bar that serve a beer John Smith likes.

$3.$ print the drinkers that frequent at least one bar that serves a beer they like.

$4.$ print the drinkers that frequent only bars that serve some beer they like.

$5.$ print the drinkers that frequent all the bars that serve a beer they like.

$6.$ print the drinkers that frequent no bar that serves a beer that they like.

$7.$ print the drinkers that frequent all the bars that serve a beer John Smith likes.

$8.$ print pairs of drinkers that frequent at least one common bar.

$9.$ print pairs of drinkers that frequent exactly the same bars.

$10.$ print the number of beer served per bar.

$11.$ Which bar is the most popular?

PART II$-(55$ points$)$ Consider the following relational database schema:

Movie$($title$,$ year, length, inColor, studioName, cert$\backslash$#$)$

StarsIn$($movieTitle$,$ movieYear, starName$)$

MovieStar$($name$,$ address, gender, birthdate$)$

MovieExec$($name$,$ address, cert$\backslash$#$,$ netWorth$)$

Studio$($name$,$ address, cert$\backslash$#$,$ netWorth$)$

Write the following queries in SQL $(5$ points each$).$

$1.$ List two stars that share an address.

$2.$ Query the names and addresses of all female movie stars who are also movie executives with a net worth over $\backslash (\backslash$$ $10,000,000\backslash )$

$3.$ Query all the titles and years of movies that appeared in either the Movies or StarsIn relations.

$4.$ Query the producer of Star Wars.

$5.$ Query all the producers of movies in which LEONARDO DICAPRIO stars.

$6.$ Query the titles that have been used for two or more movies.

$7.$ Find sum of the movies length each studio is produced.

$8.$ Create a list of each producer name and the total length of film produced.

$9.$ Query the total film length for only those producers who made at least one film prior to $1930.$