Question

#SQL #Oracle

Can anyone help me with this question??

Here is the tables:

For cach store provide the number of different films rented this year, the number of employees at the store, and the average number of different films rented per sales representative. Also, provide the grand total (the same calculations for stores altogether) FILM LSTORE EMPLOYEE refers to manages S1 (0, N) INVENTORY MANAGER SALES REP EMBER . N registers rented a (0, N) RENTED ITEM ues (0, N) related to RENTAL rocesse (0, N) EMPLOYEE (Emp ID, First Name, Last_Name, Email, Position, Salary, Hire _Date, Store_Numa) MANAGER (Emp IDa, Begin Date, Last_Training, Store Numa) SALES REP (Emp IDa, Last _Month Sales, Bonus_Level) STORE (Store Num, Store_Name, Street, City, State, Zip) FILM (Catalog_Num, Format, Title, Year, Number Discs, Rating, Timing, Genre) INVENTORY (Film ID, Rental_Price, New, Date In, Catalog_Numa, Store Num@) RENTED ITEM (Rental Num@, Film IDa, Price, Due_Date, Return Date, Late Fee) RENTAL (Rental Num, Rent Date, Credit Card Num, CC_Type, Member_ID@, Emp_IDa) MEMBER (Member ID, First Name, Last Name, Street, City, State, Zip, Phone, Date of Birth, Signup _Date, Rep IDa, Store Num@)

Answer 1

select
(select count(Film_ID) as FilmCount from RENTED_ITEM, FILM, INVENTORY
where RENTED_ITEM.Film_ID = INVENTORY.Film_ID and
INVENTORY.Catalog_Num = FILM.Catalog_NUM and
FILM.Year = 2019) +
(select count(EMP_ID) as IDCount from EMPLOYEE, STORE
where EMPLOYEE.Store_Num = STORE.Store_Num) +
(select avg(count)
from (select count(Film_ID) as FilmCount
from RENTED_ITEM, FILM, INVENTORY, EMPLOYEE
where RENTED_ITEM.Film_ID = INVENTORY.Film_ID and
INVENTORY.Catalog_Num = FILM.Catalog_NUM and
RENTED_ITEM.RENTAL_NUM = RENTED.RENTAL_NUM and
EMPLOYEE.EMP_ID = RENTAL.EMP_ID)) as GRAND_TOTAL;

Explanation:

select
(select count(FILM_ID)) +
(select count(EMP_ID)) +
(select avg(count(FILM_ID))) as COUNT

NEW Query

select sum(entries)
from (select count(Film_ID) as entries from RENTED_ITEM, FILM, INVENTORY
where RENTED_ITEM.Film_ID = INVENTORY.Film_ID and
INVENTORY.Catalog_Num = FILM.Catalog_NUM and
FILM.Year = 2019) union all
(select count(EMP_ID) as entries from EMPLOYEE, STORE
where EMPLOYEE.Store_Num = STORE.Store_Num) union all
(select avg(count)
from (select count(Film_ID) as entries
from RENTED_ITEM, FILM, INVENTORY, EMPLOYEE
where RENTED_ITEM.Film_ID = INVENTORY.Film_ID and
INVENTORY.Catalog_Num = FILM.Catalog_NUM and
RENTED_ITEM.RENTAL_NUM = RENTED.RENTAL_NUM and
EMPLOYEE.EMP_ID = RENTAL.EMP_ID)) as GRAND_TOTAL;