QuestionQuestion

*In this exercise, you’ll use SQL Server Management Studio to create and review the MyGuitarShop database, and execute queries. Compose answers to the questions that follow the exercise in a MS Word document. Submit the MS Word document after completing the exercises.

The exercise consists of the 15 steps that follow.

Create the database

Start SQL Server Management Studio and open a connection.

Open the script file named CreateMyGuitarShop.sql that’s in the Exercise Starts directory by clicking the Open File button in the toolbar and then using the resulting dialog box to locate and open the file.

Execute the entire script by clicking the Execute button in the SQL Editor toolbar or by pressing F5. When you do, the Messages tab displays indicates whether the script executed successfully.

Review the database

In the Object Explorer window, expand the node for the database named MyGuitarShop so you can see all of the database objects it contains. If it isn’t displayed in the Object Explorer window, you may need to click on the Refresh button to display it.

View the data for the Categories and Products tables.

Navigate through the database objects and view the column definitions for at least the Categories and Products tables.

Enter and run SQL statements

Select the MyGuitarShop database from the Available Databases dropdown menu (Crtl + U) to choose it as the default database.

Open a new Query Editor window. Then, enter and run this SQL statement:

SELECT ProductName FROM Products

Delete the e at the end of ProductName and run the statement again. Note the error number and the description of the error.

Open another Query Editor window by clicking the New Query button. Then, enter and run this statement:

SELECT COUNT(*) AS NumberOfProducts FROM Products

Open and run scripts

Open the script named ProductDetails.sql that’s in the Exercise Starts directory. Note that this script contains just one SQL statement. Then, run the statement.

Open the script named ProductSummary.sql that’s in the Exercise Starts directory.

Note that this opens another Query Editor window.

Open the script named ProductStatements.sql that’s in the Exercise Starts directory.

Notice that this script contains two SQL statements that end with semicolons.

Press the F5 key or click the Execute button to run both of the statements in this script. Note that this displays the results in two Results tabs. Make sure to view the results of both SELECT statements.

Exit from SQL Server Management Studio.

Compose answers to the following questions in MS Word and submit your answers using the link provided.

Describe the MyGuitarShop database

How many tables are in the database?

List the column names and its data type for each column defined for each table.

Indicate the primary key for each table.

Describe the purpose of the query and the result of the query in step 8.

Paste a screenshot of the error message from step 9 as your answer to this question.

Describe the purpose of the query and the result of the query in step 10.

Describe the purpose of the query and the result of the query in step 11.

Describe the purpose of the query and the result of the query in step 12.

Describe the purpose of the queries and the result of the queries in step 14.


*In this exercise, you’ll use SQL Server Management Studio to retrieve data using the SELECT command. You write and execute seven queries described below. Each query retrieves data from a table in the MyGuitarShop database. Create a MS Word document that contains a copy of the final SELECT query described and the result of the query final. Submit the MS Word document upon completion of this assignment.

Enter and run your SELECT statements

Write a SELECT statement that returns 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 list price in descending sequence.

Then, run this statement again to make sure it works correctly.

Write a SELECT statement that returns the concatenation of the contents of the LastName and FirstName columns from the Customers table. Name the resulting column FullName.

Format this column with the last name, a comma, a space, and the first name like this:

Doe, John

Sort the result set by last name in ascending sequence.

Return only the contacts whose last name begins with letters from M to Z.

Write a SELECT statement that returns these column names and data from the Products table: ProductName, ListPrice, DateAdded

Return only the rows with a list price that’s greater than 500 and less than 2000.

Sort the result set in descending sequence by the DateAdded column.

Write a SELECT statement that returns these column names and data from the Products table: ProductName, ListPrice, DiscountPercent, DiscountAmount (calculated from the previous two columns), DiscountPrice (calculated from the previous three columns)

Sort the result set by discount price in descending sequence.

Write a SELECT statement that returns these column names and data from the OrderItems table: ItemID, ItemPrice, DiscountAmount, Quantity, PriceTotal (calculated by multiplying the item price with the quantity), DiscountTotal (calculated by multiplying the discount amount with the quantity), ItemTotal (calculated by subtracting the discount amount from the item price and then multiplying by the quantity)

Only return rows where the ItemTotal is greater than 500.

Sort the result set by item total in descending sequence.

Write a SELECT statement that returns these columns from the Orders table:

OrderID , OrderDate , ShipDate

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

Write a SELECT statement without a FROM clause that creates a row with these columns: Price 100 (dollars), TaxRate .07 (7 percent), TaxAmount (The price multiplied by the tax rate), Total (The price plus tax)



*In this exercise, you’ll use SQL Server Management Studio to retrieve data from more than one table. You write and execute seven queries described below. Each query retrieves data from more than one table in the MyGuitarShop database. Create a MS Word document that contains a copy of the final SELECT query described and the result of the query final. Submit the MS Word document upon completion of this assignment.

How to retrieve data from two or more tables

Write a SELECT statement that joins the Categories table to the Products table and returns these columns: CategoryName, ProductName, and ListPrice.

Sort the result set by CategoryName and then by ProductName in ascending order.

2. Write a SELECT statement that joins the Customers table to the Addresses table and returns these columns: FirstName, LastName, Line1, City, State, and ZipCode.

Return one row for each address for the customer with an email address of

3. Write a SELECT statement that joins the Customers table to the Addresses table and returns these columns: FirstName, LastName, Line1, City, State, and ZipCode.

Return one row for each customer, but only return addresses that are the shipping address for a customer.

4. Write a SELECT statement that joins the Customers, Orders, OrderItems, and Products tables. This statement should return these columns: LastName, FirstName, OrderDate, ProductName, ItemPrice, DiscountAmount, and Quantity.

Use aliases for the tables.

Sort the final result set by LastName, OrderDate, and ProductName

5. Write a SELECT statement that returns the ProductName and ListPrice columns from the Products table.

Return one row for each product that has the same list price as another product.

Hint: Use a self-join to check that the ProductID columns aren’t equal but the ListPrice column is equal.

Sort the result set by ProductName.

6. Write a SELECT statement that returns these two columns: CategoryName from the Categories table and the ProductID from the Products table

Return one row for each category that has never been used.

Hint: Use an outer join and only return rows where the ProductID column contains a null value.

7. Use the UNION operator to generate a result set consisting of three columns from the

Orders table:

ShipStatus (Described below), OrderID, OrderDate


If the order has a value in the ShipDate column, the ShipStatus column should contain a value of SHIPPED. Otherwise, it should contain a value of NOT SHIPPED.

Sort the final result set by OrderDate.


*In this exercise, you’ll use SQL Server Management Studio to write summary queries. You write and execute seven queries described below. Each query retrieves summary data from more than one table in the MyGuitarShop database. Create a MS Word document that contains a copy of the final SELECT query described and the result of the query final. Submit the MS Word document upon completion of this assignment.

How to code summary queries

Write a SELECT statement that returns these columns:

The count of the number of orders in the Orders table

The sum of the TaxAmount columns in the Orders table

Write a SELECT statement that returns one row for each category that has products with these columns:

The CategoryName column from the Categories table

The count of the products in the Products table

The list price of the most expensive product in the Products table

Sort the result set so the category with the most products appears first.

Write a SELECT statement that returns one row for each customer that has orders with these columns:

The EmailAddress column from the Customers table

The sum of the item price in the OrderItems table multiplied by the quantity in the OrderItems table

The sum of the discount amount column in the OrderItems table multiplied by the quantity in the OrderItems table

Sort the result set in descending sequence by the item price total for each customer.

Write a SELECT statement that returns one row for each customer that has orders with these columns:

The EmailAddress column from the Customers table

A count of the number of orders

The total amount for each order (Hint: First, subtract the discount amount from the price. Then, multiply by the quantity.)

Return only those rows where the customer has more than 1 order.

Sort the result set in descending sequence by the sum of the line item amounts.

Modify the solution to exercise 4 so it only counts and totals line items that have an ItemPrice value that’s greater than 400

Write a SELECT statement that answers this question: What is the total amount ordered for each product? Return these columns:

The product name from the Products table

The total amount for each product in the OrderItems table

(Hint: You can calculate the total amount by subtracting the discount amount from the item price and then multiplying it by the quantity)

Use the WITH ROLLUP operator to include a row that gives the grand total.

Write a SELECT statement that answers this question: Which customers have ordered more than one product? Return these columns:

The email address from the Customers table

The count of distinct products from the customer’s orders


*In this exercise, you’ll use SQL Server Management Studio to write summary queries. You write and execute six subqueries described below. Create a MS Word document that contains a copy of the final SELECT query described and the result of the query final. Submit the MS Word document upon completion of this assignment.

How to code subqueries

Write a SELECT statement that returns the same result set as this SELECT statement, but don’t use a join. Instead, use a subquery in a WHERE clause that uses the IN keyword.

SELECT DISTINCT CategoryName

FROM Categories c JOIN Products p

ON c.CategoryID = p.CategoryID

ORDER BY CategoryName

Write a SELECT statement that answers this question: Which products have a list price that’s greater than the average list price for all products?

Return the ProductName and ListPrice columns for each product.

Sort the results by the ListPrice column in descending sequence.

Write a SELECT statement that returns the CategoryName column from the Categories table.

Return one row for each category that has never been assigned to any product in the Products table. To do that, use a subquery introduced with the NOT EXISTS operator.

Write a SELECT statement that returns three columns: EmailAddress, OrderID, and the order total for each customer. To do this, you can group the result set by the EmailAddress and OrderID columns. In addition, you must calculate the order total from the columns in the OrderItems table.

Write a second SELECT statement that uses the first SELECT statement in its FROM clause. The main query should return two columns: the customer’s email address and the largest order for that customer. To do this, you can group the result set by the EmailAddress column.

Write a SELECT statement that returns the name and discount percent of each product that has a unique discount percent. In other words, don’t include products that have the same discount percent as another product.

Sort the results by the ProductName column.

Use a correlated subquery to return one row per customer, representing the customer’s oldest order (the one with the earliest date). Each row should include these three columns: EmailAddress, OrderID, and OrderDate.

Solution PreviewSolution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

Database Management Problems
    $50.00 for this solution

    PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

    Find A Tutor

    View available Management Information Systems (MIS) Tutors

    Get College Homework Help.

    Are you sure you don't want to upload any files?

    Fast tutor response requires as much info as possible.

    Decision:
    Upload a file
    Continue without uploading

    SUBMIT YOUR HOMEWORK
    We couldn't find that subject.
    Please select the best match from the list below.

    We'll send you an email right away. If it's not in your inbox, check your spam folder.

    • 1
    • 2
    • 3
    Live Chats