Use SQL Developer to create the My Guitar Shop database, to review the tables in this...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Use SQL Developer to create the My Guitar Shop database, to review the tables in this database, and to enter SQL statements and run them against this database. Use SQL Developer to create the My Guitar Shop database 1. Start SQL Developer. 2. Connect as system/system. 3. Open the script file named create_mgs_user.sql from Blackboard MGS folder. 4. Execute the entire script by clicking the Run Script button in the code editor toolbar or by pressing F5. When you do, the Output window displays messages that indicate whether the script executed successfully. You must be connected as the system user for this script to execute successfully. If you get an error the first time you run this script, run it again. 5. Open the script file named create_mgs_tables.sql from the same folder. 6. Execute the entire script by clicking the Run Script button in the code editor toolbar or by pressing F5. When you do, the Output window displays messages that indicate whether the script executed successfully. If you get an error the first time you run this script, run it again. Use SQL Developer to review the My Guitar Shop database 7. Create a connection named mgs for the user named mgs. The password for this user should be "mgs". 8. In the Connections window, expand the node for the connection named mgs so you can see all of the database objects it contains. If this connection isn't displayed in the Connections window, you may need to click on the Refresh button to display it. Write the following SQL Statements: 1. Write a SELECT statement that returns one row for each customer that has orders with these columns: The email_address 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. 2. 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 Order_Items (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 ROLLUP operator to include a row that gives the grand total. 3. 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 4. 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 category_name FROM categories c JOIN products p ON c.category_id = ORDER BY category_name p.category_id 5. 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 product_name and list_price columns for each product. Sort the results by the list_price column in descending sequence. 6. Write a SELECT statement that returns the category_name 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. 7. 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 product_name column. 8. 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: email_address, order_id, and order_date. Use SQL Developer to create the My Guitar Shop database, to review the tables in this database, and to enter SQL statements and run them against this database. Use SQL Developer to create the My Guitar Shop database 1. Start SQL Developer. 2. Connect as system/system. 3. Open the script file named create_mgs_user.sql from Blackboard MGS folder. 4. Execute the entire script by clicking the Run Script button in the code editor toolbar or by pressing F5. When you do, the Output window displays messages that indicate whether the script executed successfully. You must be connected as the system user for this script to execute successfully. If you get an error the first time you run this script, run it again. 5. Open the script file named create_mgs_tables.sql from the same folder. 6. Execute the entire script by clicking the Run Script button in the code editor toolbar or by pressing F5. When you do, the Output window displays messages that indicate whether the script executed successfully. If you get an error the first time you run this script, run it again. Use SQL Developer to review the My Guitar Shop database 7. Create a connection named mgs for the user named mgs. The password for this user should be "mgs". 8. In the Connections window, expand the node for the connection named mgs so you can see all of the database objects it contains. If this connection isn't displayed in the Connections window, you may need to click on the Refresh button to display it. Write the following SQL Statements: 1. Write a SELECT statement that returns one row for each customer that has orders with these columns: The email_address 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. 2. 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 Order_Items (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 ROLLUP operator to include a row that gives the grand total. 3. 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 4. 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 category_name FROM categories c JOIN products p ON c.category_id = ORDER BY category_name p.category_id 5. 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 product_name and list_price columns for each product. Sort the results by the list_price column in descending sequence. 6. Write a SELECT statement that returns the category_name 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. 7. 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 product_name column. 8. 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: email_address, order_id, and order_date.
Expert Answer:
Related Book For
Modern Database Management
ISBN: 978-0133544619
12th edition
Authors: Jeff Hoffer, Ramesh Venkataraman, Heikki Topi
Posted Date:
Students also viewed these databases questions
-
Reverse the slope of the line in Exercise 12.51 by reordering the y observations, as follows: Repeat the steps of Exercise 12.51. Notice the change in the sign of r and the relationship between the...
-
Alton Appliance Service had net income for the year of $32,500. In addition, the balance sheet reports the following balances: (Click the icon to view the balances.) Calculate the return on assets...
-
In Exercises 7980, find the value of y if the line through the two given points is to have the indicated slope. (3, y) and (1, 4), m = -3
-
The bond strength when mounting an integrated circuit on a metalized glass substrate was studied as a function of factor A = adhesive type, factor B = curve time, and factor C = conductor material...
-
The following information pertains to the January 2, year 2 transactions replacing a print machine for Hidden Creek Enterprises, Inc. Net book value - old print machine $20,000 Total cost of new...
-
Carry out a full decision analysis for Classical Reproductions Ltd, using the following information: Calculation of expected profit with perfect information Prior probabilities for the various events...
-
Hasselback Company acquired a plant asset at the beginning of Year 1. The asset has an estimated service life of 5 years. An employee has prepared depreciation schedules for this asset using three...
-
Explain the Game theory What are the components of Game theory What are applications of game theory in decision making in organizations small to medium organizations Explain the importance of game...
-
Let us consider the a small village. The power network of the village is shown in Figure 1. Load demands at Zone A, Zone B and Zone C, are 5MVA (0.95pf lagging), 7MVA (0.94pf lagging) and 7MVA...
-
Ray's GamePlace sells all the hottest gear and video games. On January 1, 20X7, Ray's had the following account balances: Accounts Receivable Less Allowance for Doubtful Accounts Net Accounts...
-
Imagine a scenario where you need to follow up with a potential investor after an initial meeting. How would you structure a follow-up letter to express gratitude, reiterate key points, and encourage...
-
Discuss the measurable outcomes of a successful business letter campaign. How can you track responses, conversions, or other relevant metrics to evaluate the effectiveness of your communication?
-
Create a fictional case study where a company is launching a new product. Draft a sales letter introducing the product, emphasizing its features, benefits, and a limited-time offer. Include a...
-
Imagine your company wants to collaborate with another organization on a joint project. Write a business letter proposing the partnership, outlining mutual benefits, and suggesting next steps. Use a...
-
Core BC University has been operating for 30 years, offering classes in two modalities, including in-person and online. The University is headquartered in Vancouver, BC. It has ten partner...
-
Suresh Co. expects its five departments to yield the following income for next year. Dept. N $ 35,000 Dept. P $42,000 Total Dept. M $63,000 Dept. O $56,000 Dept. T Sales $ 28,000 $224,000 Expenses...
-
In exchange for land, the company received a 12-month note on January 1. The face amount of the note is $1,000, and the stated rate of interest is 13%, compounded annually. The 13% rate is equal to...
-
Explain three conditions that suggest a surrogate key should be created for the primary key of a relation.
-
SQU2006 and SQL:2008 introduced a new keyword, MERGE. Explain how using this keyword allows one to accomplish updating and merging data into a table using one command rather than two.
-
List all active students in June by name. (Make up names and other data if you are actually building a prototype database.) Include the number of hours students received tutoring and how many lessons...
-
What critical elements are important to support the planning process?
-
What are impediments to planning, both at the beginning of a project and throughout the project?
-
Since the bar chart is ineffective as a planning and scheduling tool, why is it still widely used in the construction industry?
The New Art Of Managing People Updated And Revised Person 1st Edition - ISBN: B005UVQSVY - Free Book
Study smarter with the SolutionInn App