#SQL #Oracle
Can anyone help me with this question??
Here is the tables:
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;
For cach store provide the number of different films rented this year, the number of employees at...
This assignment consists of a series of SQL questions. For each
question, you will need to include:
• SQL query.
• An explanation of the query. Please include explanations as a
comment AFTER your query, e.g., enclosed within a /* comments block
*/ ). See the first example in the SQL tutorial for a comment. You
don’t need to explain straightforward code in comments. Only the
parts that are interesting or different, so that we understand what
you did.
Question-1...
1,List the first and last name of the donators that donated in
December 2009.
2. List the ID's of the volunteers that have not worked.
3. List the ID, first name, and last name of any employees who
works in the Finance department.
4. List all the different prices suppliers have for 'Tasty Meat'
product.
5. Select the store ID and store phone number for all stores in
'St.Paul'.
6. List the member first and last name, address, city, zip...
EliteVideo is a startup company providing concierge DVD kiosk service in upscale neighborhoods. Each store can own several copies (VIDEO) of each movie (MOVIE). For example, the store may have ten copies of the movie Titanic. In this case, Titanic would be an instance in the MOVIE table, while each rentable copy would be an instance in the VIDEO table. A rental transaction (RENTAL) involves one or more VIDEOs being rented to a member (MEMBERSHIP). A video can be rented...
SQL queries and procedures TABLE: Employees Business Rules: EmployeeID is defined as the primary key. Address has been denormalized to include City and State for performance reasons. DeptNbr is a foreign key that references DeptNbr in the Department table Manager is a foreign key that references EmployeeID in the Employees table Data Structure: (EmployeeId, LastName, FirstName, Street, City, State, Zip, DateOfHire, DateOfBirth, JobTitle, Salary, DeptNbr(fk), Manager(fk)) TABLE: Department Business Rules: DeptNbr is defined as the primary key. Data Structure: (DeptNbr,...
An online company sells hundreds of office supply products on
its e-Commerce store. It has asked you to design and implement a
sales order processing system which will be used by users in our
Operations department to process sales orders. Here is an overview
of the sales order process. Customers make purchases by placing
orders. Each customer has a customer number and profile (such as
name, shipping address). To simplify the matter, each time an order
is placed, only one...
27. Display item number, item name, and amount on hand for the items that have less than data by the on hand value. 28. Display the total on hand value for each category in the Item table. Order the results by category. 29. Display item number, item name, and amount on hand for the items that have less than 20 on hand or are storehouse 2. 334 665 Rep RepNum LastName FirstName Street City State PostalCode Commission Rate 15 Campos...
please use dia to draw the e-r diagram to create Entity -
Relationship Diagrams
then use MS access
This project requires you to create a database design. Your design will be documented in a set of Entity-Relationship Crow's Foot diagrams using the representation as shown in the lecture materials. Draw a set of Entity-Relationship diagrams to model the following scenario. The Maggs Realty Company wants to track the Sales Offices that they have across all of Ontario They are interested...
Answer the following questions using mysql. Please show screenshots to make sure the queries work. 1. Use a sub-query and the IN operator to find the number and name of each customer that placed an order on October 23, 2007. 2. Repeat step 2, but this time use the EXISTS operator in your answer. 3. Using a sub-query, find the number and name of each customer that did not place an order on October 23, 2007. 4. Using a sub-query,...
My Java code from last assignment:
Previous Java code:
public static
void main(String args[]) {
// While loop set-up
boolean flag = true;
while (flag) {
Scanner sc = new
Scanner(System.in);
// Ask user to enter employee number
System.out.print("Enter employee
number: ");
int employee_number = sc.nextInt();
// Ask user to enter last name
System.out.print("Enter employee last
name: ");
String last_name = sc.next();
// Ask user to enter number of hours worked
System.out.print("Enter number of
hours worked: ");
int hours_worked =...
John and Jane Doe are married retired taxpayers who care for
their three-year-old grandson. The following information was
provided to you as documentation necessary to prepare their 2017
tax return. You will gather the appropriate information and
complete the forms provided in Blackboard (1040, Schedule A,
Schedule B and Schedule D) in preparation of their tax file. Please
note that the forms provided may not match the tax year of the
course, as IRS forms are not available until just...