Make an SQL queries for displaying the following information. Please remember each query requires using
only one table. No JOINs are required.
1. Provide the number of Vendors hailing from each state. Provide suitable alias for the vendor count.
This query will display one row for each Vendor area code in the descending order of Vendor count.
2. List the employee details for employees born after Jan 1, 1970 whose last name falls between 'Jenner' and 'York'. Sort the results in the descending order of employee data of birth. For each employee the query should list employee number, employee last name & first name and employee date of birth.
3. Display customer code and count of invoices placed on '17-JAN-2010'. This query should display one
row for each customer (i.e, CUS_CODE) sorted in the descending order of invoice counts.
4. List Product code, Product description, quantity on hand, minimum quantity specified for products
which may not need to be reordered shortly (i.e., whose Quantities on hand are more than twice
the respective minimum quantities). Sort the results in the ascending order of quantities on hand.
5. Which employees born before Jan 1, 1970 live in area code 901? Display their last names, first names, dates of birth and area codes sorted in ascending order by first name and descending order by data of birth..
6. Display invoice numbers, product line unit totals, and total amount for all invoices. This query will display one row for each invoice sorted in the descending order of invoice total amounts.
7. For each area code with total of customer balances below 500, list customer counts and the total of
customer balances. This query will list one row for each area code.
8. For each product discount offered (P_DISCOUNT), list number of products and average prices (provide suitable headings as aliases) for products whose quantity on hand is greater than 10 units. Your query will display one line for each product discount value.
9. For each area code, display the number of employees residing in the area code (give suitable heading as alias) whose EMP_MGR is not null. Your query should display one line for each area code sorted in the descending order of number of employees.
10. Display customer details (customer code, customer last name, area code and customer balance) for customers living in area code '713' with customer balances either less than 200 or greater than 350.

The following is the diagram that is being referred to:
 

 

Transcribed Image Text:

REGION PK REGION_ID REGION_NAME CUSTOMER PK CUS CODE CUS_LNAME CUS_FNAME CUS_INITIAL CUS_AREACODE CUS_BALANCE VENDOR PK V_CODE V_NAME V_CONTACT V_AREACODE V_PHONE V_STATE V_ORDER -H---has OPK -H--generates- HO-supplies - STORE STORE_ID STORE_ZIPCODE FK1 REGION_ID GENERATES INVOICE PK INV_NUMBER FK1 CUS_CODE FK3 EMP_NUM INV_DATE FK2 STORE_ID PRODUCT PK | P_CODE P_DESCRIPT P_INDATE P_QOH P_MIN P_PRICE P_DISCOUNT FK1 V_CODE P_MIN_ORDER P_REORDER FK2 CAT_ID HO- employs- has-O PK -H-appears in O PK,FK1 PK manages FK2 F EMPLOYEE EMP_PHONE EMP_MGR O-records -OFK2 STORE_ID EMP NUM EMP_TITLE EMP_LNAME EMP_FNAME EMP_INITIAL EMP_DOB EMP_HIRE_DATE EMP_AREACODE LINE INV NUMBER LINE_NUMBER P_CODE LINE_UNITS LINE_PRICE CATEGORY has---H-PK CAT_ID CAT_NAME REGION PK REGION_ID REGION_NAME CUSTOMER PK CUS CODE CUS_LNAME CUS_FNAME CUS_INITIAL CUS_AREACODE CUS_BALANCE VENDOR PK V_CODE V_NAME V_CONTACT V_AREACODE V_PHONE V_STATE V_ORDER -H---has OPK -H--generates- HO-supplies - STORE STORE_ID STORE_ZIPCODE FK1 REGION_ID GENERATES INVOICE PK INV_NUMBER FK1 CUS_CODE FK3 EMP_NUM INV_DATE FK2 STORE_ID PRODUCT PK | P_CODE P_DESCRIPT P_INDATE P_QOH P_MIN P_PRICE P_DISCOUNT FK1 V_CODE P_MIN_ORDER P_REORDER FK2 CAT_ID HO- employs- has-O PK -H-appears in O PK,FK1 PK manages FK2 F EMPLOYEE EMP_PHONE EMP_MGR O-records -OFK2 STORE_ID EMP NUM EMP_TITLE EMP_LNAME EMP_FNAME EMP_INITIAL EMP_DOB EMP_HIRE_DATE EMP_AREACODE LINE INV NUMBER LINE_NUMBER P_CODE LINE_UNITS LINE_PRICE CATEGORY has---H-PK CAT_ID CAT_NAME

Answer rating: 100% (QA)

Based on the Entity Relationship Diagram ERD provided the following SQL queries can fulfill your requirements Each query will reference the appropriat... View the full answer