Question 1 (3 points) Please use My Guitar Shop (MGS) Database. Write a SELECT statement that...

  

  

 

         

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.