CIS355

CIS355
Week 1 Assignment
40 points

Grading: Review Questions are worth 2 points each, Problems are 4 points each and Reasearch and Readings are 6 points each.

For the following problems, please include only your SQL syntax (and not the result set). Use the Ch07_SaleCo database for these problems. I’ve posted a Microsoft
Access version of this and the SQL script you can load into Microsoft SQL Server to Resources folder in the header section in Scholar.

These Problems are taken from CM Chapter 7.

1. Problem #29.
SELECT INVOICE.CUS_CODE, INVOICE.INV_NUMBER, PRODUCT.P_DESCRIPT,
LINE.LINE_UNITS AS [Units Bought], LINE.LINE_PRICE AS [Unit Price],
LINE.LINE_UNITS*LINE.LINE_PRICE AS Subtotal
FROM CUSTOMER, INVOICE, LINE, PRODUCT
WHERE CUSTOMER.CUS_CODE = INVOICE.CUS_CODE
AND INVOICE.INV_NUMBER = LINE.INV_NUMBER
AND PRODUCT.P_CODE = LINE.P_CODE
ORDER BY INVOICE.CUS_CODE, INVOICE.INV_NUMBER, PRODUCT.P_DESCRIPT;
2. Problem #30.
SELECT INVOICE.CUS_CODE, CUSTOMER.CUS_BALANCE,
Sum(LINE.LINE_UNITS*LINE.LINE_PRICE) AS [Total Purchases]
FROM CUSTOMER, INVOICE, LINE
WHERE INVOICE.INV_NUMBER = LINE.INV_NUMBER
AND CUSTOMER.CUS_CODE = INVOICE.CUS_CODE
GROUP BY INVOICE.CUS_CODE, CUSTOMER.CUS_BALANCE;

3. Problem #32.
SELECT INVOICE.CUS_CODE, CUSTOMER.CUS_BALANCE,
Sum(LINE.LINE_UNITS*LINE.LINE_PRICE) AS [Total Purchases],
Count(*) AS [Number of Purchases],
AVG(LINE.LINE_UNITS*LINE.LINE_PRICE) AS [Average Purchase Amount]
FROM CUSTOMER, INVOICE, LINE
WHERE INVOICE.INV_NUMBER = LINE.INV_NUMBER
AND CUSTOMER.CUS_CODE = INVOICE.CUS_CODE
GROUP BY INVOICE.CUS_CODE, CUSTOMER.CUS_BALANCE;
4. Create the SQL to generate a listing of product code and description of products whose list price is greater than $100 for vendors from Tennessee. Include the name
and contact of the vendor.

5. Using an outer join, write a query to list the vendors who do not have any products listed in the inventory.

6. Create a customer directory that includes the customer code, the first, middle, and last name combined into one name column, and last four digits of the phone
number in a separate column as search index (“What are the last four digits of your phone number?”). Code your query so that if any of the customer’s name elements are
left null, the non-null portions of the name are still returned. Sort by the search index and present the results alphabetically. Here is a sample of the data set the
query should produce:
CustName SearchIndex
James G Brown 1228
Leona K Dunne 1238
Myron Orlando 1672
Paul F Olowski 2180

7. Calculate the inventory cost (quantity on hand * price) for each product. Round the result set to two decimal places. Return the inventory cost, product code and
description in order of inventory cost from highest cost to lowest. Additionally, skip the first 2 rows and then present the next 7 rows.

8. Write a query that returns the number of days that have passed since the current date and the date on which each product was received (P_INDATE). Include the
product code, description, vendor name and contact in your result set and order the result set by largest to smallest gap (between the current date and date received).

9. Write a query to create a list of invoices sorted by invoice date. Code the query so as to present only the date portion of the invoice date in your result set.

10. SQL String and Numeric Functions Workshop, #5.