Question 1 (3 points) Please use My Guitar Shop (MGS) Database. Write a SELECT statement that...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Question 1 (3 points) Please use My Guitar Shop (MGS) Database. Write a SELECT statement that returns these columns: - The count of the number of orders in the Orders table - The sum of the tax_amount columns in the Orders table Please write the answer in the answer box and attach a screenshot of the final result. Failing to do so will result in a 0 mark for this question. Paragraph V Lato (Recom... v Add a File B I U A 19px... Record Audio Record Video + v 11. Question 2 (3 points) Please use My Guitar Shop (MGS) Database. Write a SELECT statement that returns one row for each category that has products with these columns: - The category_name 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. Question 3 (3 points) Please use My Guitar Shop (MGS) Database. 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 WITH ROLLUP operator to include a row that gives the grand total. Question 4 (3 points) Please use My Guitar Shop (MGS) Database. 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 and include the NOT EXISTS operator as part of your solution. Question 5 (2 points) Please use Software Expert (SWE) Database. Display the average evaluation score for consultant ID (EVALUATEE_ID) 105. Round the retrieved value to one decimal place. Question 6 (2 points) Please use Software Expert (SWE) Database. Count the number of consultants who are certified in skill ID 1. Question 7 (4 points) Please use Software Expert (SWE) Database. List the first and last name of every consultant who has ever worked on a project with consultant Mark Myers. Include Mark Myers in the result set. Use a subquery. You must also use the consultant's full name in your query. Question 8 (4 points) Please use Software Expert (SWE) Database. Use the UNION operator to generate a result set consisting of two columns: project ID and name. The result must include all the projects with completed evaluations AND projects managed by consultant with last name that starts with 'Z'. Use a subquery. Remove any duplicate results. Question 9 (4 points) Please use Software Expert (SWE) Database. a. Write an ALTER TABLE statement that adds a new 'total_days' column to the project_consultant table. This new column should have a default value of 0 (zero). b. Update the new column with the difference of ROLL OFF and ROLL ON dates. c. Display all the contents of project_consultant table. d. Drop the 'total_days' column. Question 10 (4 points) Please use Software Expert (SWE) Database. a. Include statements to drop the table if it already exists. b. Create a new evaluation_audit table with these columns: - audit_id (primary key, auto increment): integer - audit_e_id: integer (do not allow nulls) - audit_score: integer - audit_user: string (length of 20) c. Insert a new row for consultant (e_id) 100 with a score of 90. d. Display all the contents of evaluation_audit table. Question 11 (5 points) Please use Software Expert (SWE) Database. Modify evaluation_audit table as follows: a. Assign empty string to audit_user column that have NULL values. Write a single ALTER TABLE statement that will disallow null values for the audit_user column. b. Write an ALTER TABLE statement that adds a new 'audit_date' column. c. Insert another row for consultant 100 with a score of 95 along with the current user and current date. Hint: Use the USER() and SYSDATE() functions. d. Display all the contents of evaluation_audit table. e. Write a negative test case by inserting a row for consultant 100 with a score of 99 with an unknown user and date. This will show an error message in the ERR file. Question 12 (1 point) Please use Software Expert (SWE) Database. Write a negative test case by inserting a new row with an unknown (NULL) skill_id to the project_skill table. Question 13 (2 points) Please use Software Expert (SWE) Database. Write a negative test case by deleting a row from the consultant table that will violate a foreign key constraint. Question 1 (3 points) Please use My Guitar Shop (MGS) Database. Write a SELECT statement that returns these columns: - The count of the number of orders in the Orders table - The sum of the tax_amount columns in the Orders table Please write the answer in the answer box and attach a screenshot of the final result. Failing to do so will result in a 0 mark for this question. Paragraph V Lato (Recom... v Add a File B I U A 19px... Record Audio Record Video + v 11. Question 2 (3 points) Please use My Guitar Shop (MGS) Database. Write a SELECT statement that returns one row for each category that has products with these columns: - The category_name 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. Question 3 (3 points) Please use My Guitar Shop (MGS) Database. 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 WITH ROLLUP operator to include a row that gives the grand total. Question 4 (3 points) Please use My Guitar Shop (MGS) Database. 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 and include the NOT EXISTS operator as part of your solution. Question 5 (2 points) Please use Software Expert (SWE) Database. Display the average evaluation score for consultant ID (EVALUATEE_ID) 105. Round the retrieved value to one decimal place. Question 6 (2 points) Please use Software Expert (SWE) Database. Count the number of consultants who are certified in skill ID 1. Question 7 (4 points) Please use Software Expert (SWE) Database. List the first and last name of every consultant who has ever worked on a project with consultant Mark Myers. Include Mark Myers in the result set. Use a subquery. You must also use the consultant's full name in your query. Question 8 (4 points) Please use Software Expert (SWE) Database. Use the UNION operator to generate a result set consisting of two columns: project ID and name. The result must include all the projects with completed evaluations AND projects managed by consultant with last name that starts with 'Z'. Use a subquery. Remove any duplicate results. Question 9 (4 points) Please use Software Expert (SWE) Database. a. Write an ALTER TABLE statement that adds a new 'total_days' column to the project_consultant table. This new column should have a default value of 0 (zero). b. Update the new column with the difference of ROLL OFF and ROLL ON dates. c. Display all the contents of project_consultant table. d. Drop the 'total_days' column. Question 10 (4 points) Please use Software Expert (SWE) Database. a. Include statements to drop the table if it already exists. b. Create a new evaluation_audit table with these columns: - audit_id (primary key, auto increment): integer - audit_e_id: integer (do not allow nulls) - audit_score: integer - audit_user: string (length of 20) c. Insert a new row for consultant (e_id) 100 with a score of 90. d. Display all the contents of evaluation_audit table. Question 11 (5 points) Please use Software Expert (SWE) Database. Modify evaluation_audit table as follows: a. Assign empty string to audit_user column that have NULL values. Write a single ALTER TABLE statement that will disallow null values for the audit_user column. b. Write an ALTER TABLE statement that adds a new 'audit_date' column. c. Insert another row for consultant 100 with a score of 95 along with the current user and current date. Hint: Use the USER() and SYSDATE() functions. d. Display all the contents of evaluation_audit table. e. Write a negative test case by inserting a row for consultant 100 with a score of 99 with an unknown user and date. This will show an error message in the ERR file. Question 12 (1 point) Please use Software Expert (SWE) Database. Write a negative test case by inserting a new row with an unknown (NULL) skill_id to the project_skill table. Question 13 (2 points) Please use Software Expert (SWE) Database. Write a negative test case by deleting a row from the consultant table that will violate a foreign key constraint.
Expert Answer:
Related Book For
Concepts of Database Management
ISBN: 978-1285427102
8th edition
Authors: Philip J. Pratt, Mary Z. Last
Posted Date:
Students also viewed these databases questions
-
Using Exhibit 12-1, determine the life expectancy of a 40-year old male.
-
ABC Corporation has a machine that requires repairs or should be replaced. ABC has evaluated the two options and calculated the cash flows resulting from each option as follows: Option A: Repair the...
-
Summarize the reasons for using a three-tiered client/ server architecture.
-
During the year ended 30 June 2025, Trendline Pty Ltd incurred the following costs. The company charges factory overhead costs to work in process inventory and finished goods inventory, using an...
-
Cost classification and target profit Walts Woodwork Company makes and sells wooden shelves. Walts carpenters make the shelves in the companys rented building. Walt has a separate office at another...
-
3) Find these terms of the sequence {an}, where an -2 (-3)" +5". a) ao b) a c) 24 d) as Answer: a) 3 b) c) d)
-
Bert, an Australian Resident, was forced to relocate to Sydney because of his sick parents. Before moving, he sold the following assets: Item Purchase Price Sale Price Lounge Chair and Sofa set...
-
Find 20 job advertisements for sales representatives and summarise the range of characteristics and skills sought. Which are the most commonly required and to what extent do you think that they are...
-
What is viral marketing and why is it so useful to the marketer?
-
What factors might contribute to successful exhibition attendance?
-
Develop a weighted set of five or six criteria for good labelling. Collect a number of competing brands of the same product and rate each of them against your criteria. Which brand comes out best? As...
-
Describe the stages in developing an advertising campaign.
-
The new enterprise resource planning (ERP) system at Al Rawahi has enabled the company to slash its costs associated with purchasing and accounts payable, Mr Abdul, Al Rawahis president, is convinced...
-
If M = 7, s = 2, and X = 9.5, what is z?
-
The owner of Colonial Adventure Tours would like you to complete the following tasks to help him maintain his database. In the following exercises, you will use the data in the Colonial Adventure...
-
What is a domain?
-
The management of Colonial Adventure Tours wants you to complete the following exercises. You do not use the Colonial Adventure Tours database for any of these exercises. 1. The DBA asks for your...
-
Dynamic Industries Ltd have recently purchased new premises in order to expand their warehouse space. The cost of the premises was \($1\)500000 and on 1 November they financed the purchase with a...
-
The following information relates to a debentures issue of White Noise Ltd dated 1 January 2024. Required (a) Prepare general journal entries to record the: (i) issue of the debentures (ii) 30 June...
-
At 30 June 2023, Huang Sun Electronics adjusted its Provision for Warranties so that it would be equal to 4% of sales for the year ended on that date. Sales for the year ended 30 June 2023 were...
Study smarter with the SolutionInn App