1. (TOT answer the following questions should work re AL: 15 points) You are giv

Question

1. (TOT answer the following questions should work re AL: 15 points) You are given the following tables. (Note that the queries that you write to gardless of the data present in the tables.) store DVD Sells size title length name dvdid storeid price storeid Half Books 20000 Al pha Beta 27 32 51 11 100 12.99 101 10.99 102 100 11.99 2 3 Delta B & N Amazon 101 102 100 101 101 ota 18.99 19.19 7.99 3 102 7.99 101 21.99 a) (5 points) Write an SQL query to determine the Names of the Stores that sell at least one DVD pricier than 19.00. (Project only the names of the stores.) Show the result of the query for the given data b) (5 points) Write an SQL query to determine the average sale price of each DVD. (Project only the average price and the title of the DVDs.) Show the result of the query for the given data.

Explanation / Answer

a) Select s.name
from Store s, Sells s1
where s.storeid=s1.storeid
and s1.price>19.00
group by s.name
having count(s.storeid)>0
order by s.name;

b)Select d.title, avg(s.price)
from DVD d, Sells s
where d.dvdid=s.dvdid
group by d.title
order by d.title;

c) In this question the average amount is not mentioned so i am giving my own avg value 15.00 in this sql statement. You can replace it by your own choice.

Select d.title, avg(s.price)
from DVD d, Sells s
where d.dvdid=s.dvdid
group by d.title
having avg(s.price) > 15.00
order by d.title;

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

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