SQL Tools Assessment

Your Task

For the SQL Tools Assessment, you will create and submit a new .SQL script containing SQL statements that successfully generate the correct output in the designated format for the two queries described below.

Query 1

Using the UMD_PRODUCTS table, create and run a query that addresses the following:

  • Select PRODID, PRODNAME, STANDARDCOST, UNITPRICE, WEIGHT, MARKUP (created in step 3 below), and COSTPERWT (created in step 4 below).
  • Label the output of the following columns with the more descriptive names in quotes: the PRODID column "ID", the PRODNAME column "Product Name", and the STANDARDCOST column "Standard Cost".
  • Calculate the MARKUP column (UNITPRICE - STANDARDCOST).
  • Calculate the COSTPERWT column to show STANDARDCOST divided by WEIGHT, and round the result to two decimal places.
  • Limit to products with a STANDARDCOST greater than 100 and WEIGHT less than or equal to 1.25.
  • Sort the products by STANDARDCOST in descending order.
  • Only show the first 5 products, based on the above criteria, in the output.

Query 2

Using the ADMIN.UMD_CUSTOMERMASTER and UMD_SALESTERRITORY tables, create and run a query that addresses the following:

  • Select TERRITORYID, SALESVP, Number of Customers (created in step 3 below), MIN_CREDLIMIT (created in step 4 below), AVG_CREDLIMIT(created in step 5 below), MED_CREDLIMIT (created in step 6 below), and MAX_CREDLIMIT (created in step 7 below).
  • Join the UMD_CUSTOMERMASTER and UMD_SALESTERRITORY tables on TERRITORYID.
  • Calculate the Number of Customers (based on a count of CUSTNAME or CUSTID) for each Sales VP.
  • Calculate the minimum credit limit for each Sales VP and name it "MIN_CREDLIMIT".
  • Calculate the average credit limit for each Sales VP and name it "AVG_CREDLIMIT" and round it to two decimal places.
  • Calculate the median credit limit for each Sales VP and name it "MED_CREDLIMIT".
  • Calculate the maximum credit limit for each Sales VP and name it "MAX_CREDLIMIT".
  • Sort the output in ascending order by TERRITORYID.

Database Tables

UMD_SALESTERRITORY

COLUMN_NAME DATA_TYPE NULLABLE DATA_DEFAULT COLUMN_ID COMMENTS
TERRITORYID NUMBER(1, 0) No (null) 1 (null)
TERRITORYNAME VARCHAR2 (10 BYTE) Yes (null) 2 (null)
SALESVP VARCHAR2 (15 BYTE) Yes (null) 3 (null)
SALESGOALQTR NUMBER (9, 2) Yes (null) 3 (null)
MODIFIEDDATE DATE Yes (null) 5 (null)

UMD_CUSTOMERMASTER

COLUMN_NAME DATA_TYPE NULLABLE DATA_DEFAULT COLUMN_ID COMMENTS
CUSTID NUMBER (2, 0) No (null) 1 (null)
TERRITORYID NUMBER (1, 0) Yes (null) 2 (null)
CUSTNAME VARCHAR2 (30 BYTE) Yes (null) 3 (null)
SHIPADDR VARCHAR2 (90 BYTE) Yes (null) 4 (null)
BILLADDR VARCHAR2 (90 BYTE) Yes (null) 5 (null)
CREDLIMIT NUMBER (8, 2) Yes (null) 6 (null)
MODIFIEDDATE DATE Yes (null) 7 (null)

Academic Honesty!
It is not our intention to break the school's academic policy. Posted solutions are meant to be used as a reference and should not be submitted as is. We are not held liable for any misuse of the solutions. Please see the frequently asked questions page for further questions and inquiries.
Kindly complete the form. Please provide a valid email address and we will get back to you within 24 hours. Payment is through PayPal, Buy me a Coffee or Cryptocurrency. We are a nonprofit organization however we need funds to keep this organization operating and to be able to complete our research and development projects.