home / study / engineering / computer science / computer science questions and a

Question

home / study / engineering / computer science / computer science questions and answers / sql database queries question: consider the following database which has the following relations: ...

Question: SQL Database queries question: Consider the following database which has the following relations:...

Edit question

SQL Database queries question:

Consider the following database which has the following relations:

Movies ( title, year, length, genre, studioName, producer_id )

Stars ( movieTitle, movieYear, starName )

MovieStar ( name, address, gender, birthdate )

MovieExec ( name, id, address, networth )

Studio ( name, address, president_id )

Where movie executives can be producers or studio presidents and they have unique id numbers. Assume that names of people are unique, there is one producer of each movie, and each studio has one president.

Express the following queries using SQL:

3. (a) Find names of movie stars who starred in no movies produced by “Columbia Pictures” in 2017 (Using the “EXCEPT” operator)

(b) Find names of movie stars who starred in no movies produced by “Columbia Pictures” in 2017 (Without using the “EXCEPT” operator)

(c) Find movie stars who only star in movies that are produced by producers that have the same address as them.

Explanation / Answer

Hi,
we can first get all movie stars from all movies and using except remove them via where conditon

1.

select a.starName from Stars a, Movies b where a.movieTitle=b.title EXCEPT
select a.starName from Stars a, Movies b where a.movieTitle=b.title and b.year=2017 and b.studioName='Columbia Pictures'
2, now the same thing we can do one single query like,
select a.starName from Stars a, Movies b where a.movieTitle=b.title and b.year!=2017 and b.studioName!='Columbia Pictures'; //notice the not equal to
3.select c.Name from Stars a, Movies b, MovieStar c, MovieExec dwhere a.movieTitle=b.title and c,name=a.starName and b.producer_id=d.id and d.address = a.address;
here joined 4 tables, first to get star names, then to get movies starred then to get the producer details
Thumbs up if this was helpful, otherwise let me know in comments.Good Day.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.