SQL Discussion Problems

Consider the following portion of a database schema:

Using the tables and column names in this schema, write out the SQL that would achieve the result set specified in the problems below.*

1. The company which uses this database is called AdventureWorks. The ProductVendor table shows the supplier (also called the vendor) for each product. Write out the SQL that would provide a count of number of products from each vendor. The output should show the name of each vendor and a count of the number of products they supply. Write out the SQL that would accomplish this.

2. The Product and Product Vendor tables show a variety of related information. Write out the SQL that will display the VendorID, VendorName, each ProductID and the standard price of the product. Also, provide an additional column that increases the standard price by 10%, rounding the results to two decimal places. Additionally, display the LastReceiptDate as mm/dd/yyyy with no timestamp. Write out the SQL that would accomplish this.

3. Change the previous query to include the vendorcontacttypeID. (This requires adding an additional table to your SQL statement and modifying the SELECT clause.) Write out the SQL that would accomplish this.

4. Provide a list of productid’s, the name of the vendor and their credit rating. The results should be ordered by vendor name and then by credit rating. Write out the SQL that would accomplish this.

5. Produce a list of all productid’s, product names, the quantity on order, and the name of the vendor from which the product is supplied. Write out the SQL that accomplishes this.

6. The company would like to create a list of products that are currently on order. The results should display the name of each product and the total OnOrder quantity. Display the list in ascending order of the total quantity on order. Write out the SQL that accomplishes this

7. The company would like to produce a list that shows the name of each vendor as well as its addressline1, city, and postal code. The list should be sorted by vendor name in descending order. Write out the SQL that accomplishes this.

8. Create the SQL that will generate a list of all vendor names and the average quantityonorder from each vendor. Order the result set by Vendor name in ascending order. Write out the SQL that accomplishes this.

9. Create the SQL that would generate a list of all vendor names, the left 3 characters of their name (to be used as a short-form code), and their AddressTypeName. Order the data by AddressTypeName in descending order.

10. Create the SQL that would generate a list of Cities and a count of the number of vendors within each city. Order the data by city in ascending order.

___________________________________________________________________________

*For the purpose of this exercise, you can ignore the table name portions in parentheses when writing out your SQL queries. However, if you were to actually type this SQL into a SQL Server Query window, you would reference each table using the word in parenthesis followed by a period and then the table name. For example, the Vendor table would be referenced as follows: Purchasing.Vendor