group by invoice cus code customer cus balance 4 create the sql to generate a listin 4953828
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, 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 (“What are the last four digits of your phone number?”). Code your query so that if any of the customers 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:
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.