Database Management

HW2.Marcoa’s Dry Clean Case Questions. 4 points (Text book, page 117~121)

Please answer the following questions on page 120 and 121: B, E, G, I,K, M,N,O, R,T:

1. Please use the provided sql files to create the tables.

2. To answer the questions, please provide BOTH your queries and the results.

3. You may directly copy and paste the output tables, or take a screenshot (preferred) using snipping tool (All programs (Accessories (Snipping tool)

Questions:

B. List the LastName, FirstName, and Phone of all customers.

[pic]

[pic]

[pic]

E. List the LastName, FirstName, and Phone of all customers whose first name starts with 'B'.

[pic]

[pic]

[pic]

G. List the LastName, FirstName, and Phone for all customers whose second and third characters of phone number is 23.

[pic]

[pic]

[pic]

I. Determine the average TotalAmount.

[pic][pic]

[pic]

K. Group customers by LastName and then by FirstName.

[pic]

[pic]

[pic]

M. Show the FirstName and LastName of all customers who have had an order with TotalAmount greater than $100.00. Use a subquery. Present the results sorted by LastName in ascending order and then FirstName in descending order.

[pic]

[pic]

[pic]

N. Show the LastName, FirstName and Phone of all customers who have had an order with TotalAmount greater than 100. Use a join, but do not use JOIN ON syntax. Present the results sorted by LastName in ascending order and then FirstName in descending order.

[pic]

[pic]

[pic]

O. Show the LastName, FirstName and Phone of all customers who have had an order with TotalAmount greater than 100. Use a join using JOIN ON syntax. Present the results sorted by LastName in ascending order and then FirstName in descending...