Given the following schema, use stored procedures to perform the following tasks

Question

Given the following schema, use stored procedures to perform the following tasks:

Movies(title, year, length, genre, studioName, producerC#)

Starsln(movieTitle, movieYear, starName)

MovieStar(name, address, gender, birthdate)

MovieExec(name, address, cert#, netWorth)

Studio(name, address, presC#)

a) Given the name of a movie studio, produce the net worth of its president.

b)  Given a name and address, return 1 if the person is a movie star but not an executive, 2 if the person is an executive but not a star, 3 if both, and 4 if neither.

Explanation / Answer

DELIMITER $$

DROP FUNCTION IF EXISTS `all`.`PresNetWorth` $$
CREATE FUNCTION `PresNetWorth`(studioName CHAR(15)) RETURNS int(11)
BEGIN

DECLARE presNetWorth INT;

SELECT netWorth
INTO presNetWorth
FROM Studio, MovieExec
WHERE Studio.name = studioName AND presC = cert;
RETURN presNetWorth ;

END $$

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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