https://www.jooq.org/img/sakila.png Type the command use sakila; to change to th
ID: 3746146 • Letter: H
Question
https://www.jooq.org/img/sakila.png
Type the command use sakila; to change to the database
Tasks
Query Description
Query Statement
Find the unique identification, first name, and last name of all staff members. Result set should look like:
Find all the film ratings and remove duplicates. Result set should look like:
Find all the film ratings, do not remove duplicates. Result set should look like:
Depending upon which table you use you may also receive
Find all data about the stores. Result set should look like:
Select the literal value ‘Haka’ as an attribute without a from clause. Result set should look like:
Select the literal value ‘Haka’ as an attribute giving the column name parrot without a from clause. Result set should look like:
Select the literal value ‘Haka’ as an attribute with the from clause table store. Result set should look like:
Find the film category and average sales for each category between the two stores. Result set should look like:
Find the film category and average sales for each category between the two stores, rename the average as store_average. Result set should look like:
Find the unique identifier for country “United States”. Result set should look like:
Find the film name and description where rating is ‘G’ and the length is less than 50 minutes. Result set should look like:
Generate the Cartesian product based on tables staff_list and store. Result should look like:
Find the name, city, and country of the staff who manage the two stores. Result set should look like:
Find film titles where the title starts with ‘wo’. Result set should look like:
Find film titles that are only eight characters long. Result set should look like:
List in alphabetical order film titles that start with ‘z’. Result set should look like:
List in descending alphabetical order film titles that start with ‘z’. Result set should look like:
Find payment amounts between the values of 8 and 12 with duplicates removed. Result set should look like:
Using an aggregate function, find the average amount paid for film rentals. Result set should look like:
Using an aggregate function, find the average amount paid for film rentals for each store manager. Result set should look like:
Using an aggregate function, find the total amount paid for film rentals for each store manager. Result set should look like:
Query Description
Query Statement
Find the unique identification, first name, and last name of all staff members. Result set should look like:
Find all the film ratings and remove duplicates. Result set should look like:
Find all the film ratings, do not remove duplicates. Result set should look like:
Depending upon which table you use you may also receive
Find all data about the stores. Result set should look like:
Select the literal value ‘Haka’ as an attribute without a from clause. Result set should look like:
Select the literal value ‘Haka’ as an attribute giving the column name parrot without a from clause. Result set should look like:
Select the literal value ‘Haka’ as an attribute with the from clause table store. Result set should look like:
Find the film category and average sales for each category between the two stores. Result set should look like:
Find the film category and average sales for each category between the two stores, rename the average as store_average. Result set should look like:
Find the unique identifier for country “United States”. Result set should look like:
Find the film name and description where rating is ‘G’ and the length is less than 50 minutes. Result set should look like:
Generate the Cartesian product based on tables staff_list and store. Result should look like:
Explanation / Answer
Please find below all the queries in sequence, execute these one by one to get results as reuqired.
-- 1
Select distinct staff_id, first_name, last_name From Staff;
-- 2
Select distinct rating From film;
-- 3
Select title, rating From film;
-- 4
Select * From Store;
-- 5
Select 'Haka';
-- 6
Select 'Haka' as 'Parrot';
-- 7
Select "Haka",store_id,manager_staff_id, address_id, last_update From store;
-- 8
select i.store_id, c.name, avg(p.amount)
from film f join inventory i
on f.film_id = i.film_id
join rental r
on i.inventory_id = r.inventory_id
join payment p
on r.rental_id = p.rental_id
join film_category fc
on f.film_id = fc.film_id
join category c
on fc.category_id = c.category_id
group by i.store_id, c.name
order by 1;
-- 9
select i.store_id, c.name, avg(p.amount) 'store_average'
from film f join inventory i
on f.film_id = i.film_id
join rental r
on i.inventory_id = r.inventory_id
join payment p
on r.rental_id = p.rental_id
join film_category fc
on f.film_id = fc.film_id
join category c
on fc.category_id = c.category_id
group by i.store_id, c.name
order by 1;
-- 10
Select country_id from country where country = "United States";
-- 11
select title, description from film where rating = "G" and length < 50;
-- 12
select * from store, staff_list;
-- 13
select sid, name, city, country from staff_list;
-- 14
select title from film where title like 'wo%';
-- 15
select title from film where length(title) = 8;
-- 16
select title from film where title like 'z%' order by title;
-- 17
select title from film where title like 'z%' order by title desc;
-- 18
select amount from payment where amount between 8 and 12 group by amount;
-- 19
select f.film_id, f.title,avg(p.amount)
from film f join inventory i
on f.film_id = i.film_id
join rental r
on i.inventory_id = r.inventory_id
join payment p
on r.rental_id = p.rental_id
group by f.film_id, f.title;
-- 20
select s.manager_staff_id, avg(p.amount)
from film f join inventory i
on f.film_id = i.film_id
join store s
on i.store_id = s.store_id
join rental r
on i.inventory_id = r.inventory_id
join payment p
on r.rental_id = p.rental_id
group by s.manager_staff_id;
-- 21
select s.manager_staff_id, sum(p.amount)
from film f join inventory i
on f.film_id = i.film_id
join store s
on i.store_id = s.store_id
join rental r
on i.inventory_id = r.inventory_id
join payment p
on r.rental_id = p.rental_id
group by s.manager_staff_id;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.