www.jooq.org/sakila Heading 2 1 Normal 1 No Spac- Heading 1 9- Heading Find the

Question

www.jooq.org/sakila

Heading 2 1 Normal 1 No Spac- Heading 1 9- Heading Find the film category and average sales for each category between the two stores. set should look like 8. Result 10. Find the unique identifier for country "United States". Result set should look like: Sperts el-F Aninatlon 657 1eseee 2370 400000 328 15eaee 293.69seee 191 79eeee 187925eee 175,81e0 14665e9 835 3356 Docunentary2108766e 861-278980 827 77s00 819 795e0 Chilcren 178 86009 9. Find the film category and average sales for each category between the two stores, rename the average as storeaverage. Result set should look like: 11. Find the film name and description where rating is G' and the length is less than 50 minutes. Result set should look like: 12 Generate the Cartesian product based on tables staff,list and store. Result should look C052545 Assignmentz -Compatitslty Mo t-o-Heading 2 Normal 1No Resew View HelpTell me wht you want o 7. Select the Ineral value Haka as an attnbe 4 Find all data about the stores. Result set with the from clause table store. Reoult set hould ook like 11. Find the film name and description where rating is 'G and the length is less than 50 minutes. Result set should look like: 5 Select the literal value Haka as an atribute withool a from clause. Result set should 12. Generate the Cartesian product based on tables staff list and store. Result should look 6. Select the literal value Haka as an atribute giving the colunn name pareot without a from clause, Result sct should look like: S Ficd the fils cetegory and average sales tor each category betwen dhe two stores. Resal set should look like Select the lisal value . Haka-as an attribute with the from elause table store. Result ct should look like:

Explanation / Answer

Below are the SQl queries to fetch given records from the database Sakila.

8) For this 6 tables are joined.  Aggregate function AVG() is used to find the average of amount by grouping the result based on Category.

SELECT c.Name, AVG(p.AMOUNT)
FROM CATEGORY AS c
INNER JOIN FILM_CATEGORY AS fc
ON c.CATEGORY_ID = fc.CATEGORY_ID
INNER JOIN FILM AS f
ON f.FILM_ID = fc.FILM_ID
INNER JOIN INVENTORY AS i
ON i.FILM_ID = f.FILM_ID
INNER JOIN RENTAL AS r
ON r.INVENTORY_ID = i.INVENTORY_ID
INNER JOIN PAYMENT AS p
ON p.RENTAL_ID = r.RENTAL_ID
GROUP BY c.Name;

9) Same as above, only 'AS' is used to alias the AVERAGE as Store_Average.

SELECT c.Name, AVG(p.AMOUNT) AS Store_Average
FROM CATEGORY AS c
INNER JOIN FILM_CATEGORY AS fc
ON c.CATEGORY_ID = fc.CATEGORY_ID
INNER JOIN FILM AS f
ON f.FILM_ID = fc.FILM_ID
INNER JOIN INVENTORY AS i
ON i.FILM_ID = f.FILM_ID
INNER JOIN RENTAL AS r
ON r.INVENTORY_ID = i.INVENTORY_ID
INNER JOIN PAYMENT AS p
ON p.RENTAL_ID = r.RENTAL_ID
GROUP BY c.Name;

10) COUNTRY_ID is the unique identifier in table COUNTRY. WHERE clause is used to get the id of country 'United States'.

SELECT COUNTRY_ID
FROM COUNTRY
WHERE COUNTRY = 'United States';

11) The title and description of a film are fetched whose Rating is 'G' and Length is less than 50
SELECT TITLE, DESCRIPTION
FROM FILM
WHERE RATING = 'G'
AND LENGTH < 50;

12) The cartesian product of STAFF and STORE is done to get all the records of these two tables.

SELECT *
FROM STAFF, STORE
ON STAFF.STORE_ID = STOTE.STORE_ID;

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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