These are two questions Exercise 811 From the following base

These are two questions:

Exercise 8.1.1: From the following base tables of our running example

MovieStar(name, address, gender, birthdate)

MovieExec(name, address, cert#, netWorth)   

Studio(name, address, presC#)

Construct the following views:

A) A view RichExec giving the name, address, certificate number and net worth of all executives with a net worth of at least $10,000,000.

B) A view StudioPres giving the name, address, and certificate number of all executives who are studio presidents.

C) A view ExecutiveStar giving the name,address, gender, birth date, cer­tificate number, and net worth of all individuals who are both executives and stars.

Exercise 8.1.2: Write each of the queries below, using one or more of the views from Exercise 8.1.1 and no base tables.

A) Find the names of females who are both stars and executives.

B) Find the names of those executives who are both studio presidents and worth at least $10,000,000.

! c) Find the names of studio presidents who are also stars and are worth at least $50,000,000.

Solution

Exercise 8.1.1:

A)

CREATE VIEW RICHEXEC_VIEW AS
SELECT name, address, cert#, netWorth
FROM MovieExec
WHERE netWorth >= 10000000;

B)

CREATE VIEW STUDIOPRES_VIEW AS
SELECT name, address, cert#
FROM Studio s,Exec E
WHERE E.cert# = S.presC#;

C)

CREATE VIEW ExecutiveStar_VIEW (name, address,gender, birthdate, cert#, netWorth) AS
SELECT star.name, star.address, star.gender,star.birthdate, exec.cert#, exec.netWorth
FROM MovieStar star, MovieExec exec
WHERE star.name = exec.name;