SQL Assignment

Instructions: Write the queries specified below in SQL Server. Use ANSI standard SQL. For each query, copy and paste the SQL code and up to 10 lines of the result into a word document. Then submit the word document via Canvas.

1. List all columns and all rows from the Customer table.

2. List all columns and all rows from the PlacedOrders table.

3. List all the countries in the Supplier table. Show each country only once.

4. List the FirstName, Lastname, City and the Country columns from the Customer table. List all rows.

5. List the OrderNumber, OrderDate, and the TotalAmount columns from the PlacedOrders table. List only the rows that have a TotalAmount of more than five thousand dollars.

6. List the OrderNumber, OrderDate, and the TotalAmount columns from the PlacedOrders table. Show only the rows that have a TotalAmount of more than five thousand dollars. Arrange the answer in TotalAmount order. This means for example that an order worth $5001 should appear before one worth $10001.

7. List the Customerld, OrderDate, and TotalAmount. List only those orders that occurred in the year 2013 (Use "BETWEEN" for this query). Arrange the answer in order of the dates for each customer, which means that for a given customer, orders should appear in chronological
order.

8. List the Customerld, OrderDate, and TotalAmount. List only those orders that occurred in the year 2013 (Use the YEAR() function for this query) Arrange the answer in order of the dates for each customer, which means that for a given customer, orders should appear in chronological order.

9. List the ProductName, Supplierld, and Package, from the Product table. List only products that include the word "box" in the package description.

10. List the ProductName, Supplierld, and Package, from the Product table. List only products that do not include the word "box" in the package description.

11. List the Customerld, OrderDate and the TotalAmount from the PlacedOrders table. List only tuples for Customer 24 that occurred between January 1, 2013 and June 30, 2013. Arrange the answer in order of the order date.

12. List the CompanyName, ContactName, City, Country, and Phone, from the Supplier table. List only the Suppliers in Germany and USA. Arrange the answer in order of the Country, and the CompanyName.

13. Return all attributes for Orders placed in the month of June in 2012. List the trade date in descending order.

14. List the OrderDate and TotalAmount of all Orders that were placed in 2014. List the order dates in ascending order.

15. List the order dates and amounts of all orders that either were over 5000 dollars and were traded before December 31st, 2012, or orders that were placed after December 31st 2013 and were below below 1000 dollars. List the amounts in descending order.

16. List the product name, unit price, and package description from the Product table in descending order of all the products that were 25 dollars or higher and come in jars.

17. List the number of orders that have a TotalAmount of of 45 dollars and were placed in 2014. Call it Number0f45DollarOrdersIn2014.

18. Look for missing data by listing any rows in Customer that contain nulls.

19. For all products list the ProductName, UnitPrice, supplier company name, and supplier country.

20. List the first name, last name, and total amount for all orders that were placed after January 1, 2014, with an amount over $5000. Arrange the list in alphabetical order by last name, then by first name, and finally by descending amount.