Answer Happy

a. Give the execution result of the following
SQL command when the referential actions for all data sets are one
of CASCADE, SET NULL, SET DEFAULT, and NO ACTION for using on
DELETE clause.
DELETE
FROM Video WHERE title =
‘Die Another Day’;
b. Identify each one of the following SQL
commands as legal or illegal. If the command is legal,
show its execution result. Otherwise, explain why the
command is illegal.
(1) SELECT v.*,
character FROM Video v, Actor a, Role
r WHERE actorNo =
‘A7525’ AND a.actorNo =
r.actorNo AND r.catalogNo =
v.catalogNo;
(2) SELECT branchNo,
staffNo, COUNT(staffNo) AS totalStaff,
SUM(salary)
AS totalSalary FROM Staff GROUP
BY branchNo, staffNo
HAVING COUNT(staffNo) >
1 ORDER BY branchNo;
(3) SELECT DISTINCT title,
category, directorName
FROM Video v,
Director d WHERE v.directorNo =
d.directorNo;
(4) SELECT fName, lName,
address FROM Member WHERE staffNo
= ‘S0003’;
c. Write down the SQL command for each one
of the following requests.
(1) List the videos on rent in branch B001 or B004.
(2) Summarize the average salary for each position sorted by the
average salary in descending order.
(3) Increase the salary of the staffs whose salary is below
average by $1,000.
(4) Summarize the number of unreturned videos for each member
registered in branch B001, including member names.