Use the given MyGuitarShop (download link: https://files.fm/u/uwghv4jg ) databas

Question

Use the given MyGuitarShop (download link: https://files.fm/u/uwghv4jg ) database to answer these questions. Submit one query per question. In these exercises, you’ll enter and run your own SELECT statements. (Total: 65 points)

1.       (10 pts) Write a SELECT statement that shows four columns from the Products table: ProductCode, ProductName, ListPrice, and DiscountPercent. Then, run this statement to make sure it works correctly.

Add an ORDER BY clause to this statement that sorts the result set by ListPrice in descending sequence. Then, run this statement again to make sure it works correctly. This is a good way to build and test a statement, one clause at a time.

2.       (15 pts) Write a SELECT statement that shows one column from the Customers table named FullName that joins the LastName and FirstName columns. Format this column with the last name, a comma, a space, and the first name like this:

Doe, John

Sort the result set by LastName in ascending sequence.

Only display the contacts whose last name starts with letters from M to Z.

3.       (15 pts) Write a SELECT statement that shows these column names & data from the Products table:

ProductName                  The ProductName column

ListPrice                            The ListPrice column

DiscountPercent             The DiscountPercent column

DiscountAmount            A column that’s calculated from the previous two columns

DiscountPrice                  A column that’s calculated from the previous three columns - - don’t forget the order of arithmetic operations (PEMDAS/BODMAS)

       Sort the result set by DiscountPrice in descending sequence.

4.       (15 pts) Write a SELECT statement that shows these column names & data from the OrderItems table:

ItemID                               The ItemID column

ItemPrice                          The ItemPrice column

DiscountAmount            The DiscountAmount column

Quantity                           The Quantity column

ItemTotal                          A column that’s calculated by multiplying the item price with the quantity

DiscountTotal                  A column that’s calculated by multiplying the discount amount with the quantity

DiscountItemTotal          A column that’s calculated by subtracting the discount amount from the item price and then multiplying the result by the quantity- don’t forget the order of arithmetic operations (PEMDAS/BODMAS)

Sort the result set by ItemTotal in descending sequence.

Only display rows where the ItemTotal is greater than 500.

5.       (10 pts) Write a SELECT statement that shows these columns from the Orders table:

OrderID                            The OrderID column

OrderDate                        The OrderDate column

ShipDate                           The ShipDate column

Show only the rows where the ShipDate column contains a null value.

Place all the sql queries in a single sql file (.sql)

Explanation / Answer

Solution:

Note: It is not allowed for us experts to visit an external link(As per Chegg guidelines) so I am writing the query based in the given question statement and the schema: Also 1 and 2 are solved, please repost others.

1)

SELECT ProductCode, ProductName, ListPrice, and DiscountPercent FROM Product ORDER BY ListPrice DESC

2)

SELECT CONCAT(c.LastName, ', ', c.FirstName) AS FullName FROM Customers c ODER BY LastName

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.