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.

IS IT YOUR FIRST TIME HERE? WELCOME

USE COUPON "11OFF" AND GET 11% OFF YOUR ORDERS