Purpose:

As IS professionals, you should be comfortable with writing SQL to maintain and query tables. For

this assignment, you will use MS SQL Server to execute SQL queries.

Assignment:

Before starting on this assignment, please first download and run the script to create the tables in MS

SQL Server from the Assignment 4 Dropbox

For this assignment, write SQL statements that use the tables created above to address

the following fifteen problems.

1. Show all data in Customer table. (1 Point)

2. Show all data in Invoice table. (1 Point)

3. Show all data in Invoice_Item table. (1 Point)

4. List the Phone and LastName for all customers with a FirstName of 'Nikki'. (1 Point)

5. List the Phone, DateIn, and DateOut of all orders in excess of 100. (1 Point)

6. List the Phone and FirstName of all customers whose first name starts with 'B'. (1 Point)

7. List the Phone and FirstName of all customers whose last name includes the characters, 'cat'. (1

Point)

8. List the Phone, FirstName, and LastName for all customers whose second and third characters of

phone number is 23. (1 Point)

9. Determine the maximum and minimum TotalAmts. (1 Point)

10. Determine the average TotalAmt. (1 Point)

11. Count the number of customers. (1 Point)

12. Group customers by LastName and then by FirstName. (1 Point)

13. Count the number of customers having each combination of LastName and FirstName. (1 Point)

14. Show the FirstName and LastName of all customers who have had an order with TotalAmt greater

than 100. Present the results sorted by LastName in ascending order and then FirstName in

descending order. (1 Point)

15. Show the FirstName, LastName and TotalAmt of all customers who have had an order with an

Item named 'Dress Shirt'. Use a join with a subquery. Present results sorted by LastName in

ascending order and then FirstName in descending order. (1 Point

Please help me write the sql queries for this assignment.