Question
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
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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started