Identify all queries that meet the stated specifications. 2. For each query that does not meet...

        

Transcribed Image Text:

Identify all queries that meet the stated specifications. 2. For each query that does not meet the stated specifications briefly state exactly what is wrong with the query. If there is an error briefly explain what the error is. If it does not provide the required results briefly explain why Question#1(40 points) Which of the following queries display the total number of customer records that are being selected by the query? (Choose all that apply-if any. Give a brief but detailed explanation of what is wrong with each query you did not each select.) a) SELECT COUNT(*, SUM (balance) FROM customer INNER JOIN rep ON rep.rep_num= customer.customer_num; b) SELECT COUNT(*), SUM (balance) FROM rep INNER JOIN customer ON rep.rep_num= customer.rep_num; c) SELECT COUNT (DISTINCT customer.rep_num), SUM (balance) FROM customer RIGHT JOIN rep ON rep.rep_num= customer.rep_num; d) SELECT COUNT(rep.rep num), SUM (balance) FRUM rep LEFT JOIN customer ON rep.rep_num= customer.rep_num; e) SELECT COUNT(customer.rep_num), SUM (balance) FROM rep LEFT JOIN customer ON rep.rep_num= customer.rep_num; Enter your answer here: Question#2(40 points) Which of the following queries displays the customer name and customer number for customers that have a balance greater than the average balance for all customers and live in a city where no sales representatives live? (Choose all that apply -if any. Give a brief but detailed explanation of what is wrong with each query you did not each select.) a) SELECT customer_name, customer_num FROM customer WHERE customer.city (SELECT city FROM rep) AND customer.balance > (SELECT AVG (customer. balance) FROM customer); b) SELECT premiere.customer.customer_name, premiere.customer.customer_num FROM customer WHERE customer.city NOT IN (SELECT city FROM rep) AND customer.balance < (SELECT AVG (customer.balance) FROM customer); c) SELECT customer_name, premiere. customer.customer_num FROM customer WHERE customer.city NOT IN (SELECT city FROM rep) AND customer.balance > (SELECT SUM (customer.balance) /COUNT(*) FROM customer); d) SELECT customer.rep_num, customer.customer_name FROM customer WHERE customer.city NOT IN (SELECT city FROM rep) AND customer.balance > (SELECT AVG (customer.balance) FROM customer); e) SELECT customer.rep_num, customer.customer_name FROM customer WHERE customer.city NOT IN (SELECT city FROM rep) AND customer.balance < (SELECT AVG (customer.balance) FROM customer); Enter your answer here: Question#3 (40 points) Which of the following queries show the representative name and representative number of all representatives who are currently earning a rate equal to the minimum rate for all representatives and share a first initial with the customer that they represent? (Choose all that apply -if any. Give a brief but detailed explanation of what is wrong with each query you did not each select.) a) SELECT first_name, last_name, rep.rep_num FROM rep INNER JOIN customer ON rep.rep_num = customer.rep_num WHERE rate= (SELECT MIN (rate) FROM rep) AND LEFT (first_name, 1) = LEFT (customer_name, 1); b) SELECT first_name, last_name, rep.rep_num FROM rep WHERE rate = (SELECT MIN (rate) FROM rep) AND rep_num IN (SELECT c.rep_num FROM customer C, rep R. WHERE C.rep_num= R.Rep_num AND LEFT (C.customer_name, 1) LEFT (R.first_name, 1)); c) SELECT rep.rep_num, rep.first_name, rep.last_name FROM rep LEFT JOIN rep ON rep.rep_num= rep.rep_num WHERE rate = MIN (rate) AND first name LIKE customer_name; d) SELECT rep.rep_num, first_name, last_name FROM rep, customer WHERE rep.rep _num= customer.rep_num AND rate= (SELECT MIN (rate) FROM rep) AND LEFT (first_name, 1) = LEFT (customer_name, 1); e) SELECT rep.rep_num, first_name, last_name FROM rep, customer WHERE rep.rep_num= customer.customer_num AND rate = (SELECT MIN (rate) FROM rep) AND LEFT (first_name, 1) = LEFT (customer_name, 1); Enter your answer here: = Question#4 (40 points) Which query would show which customers have purchased a Home Gym and live in the same city as a sales representative? (Choose all that apply -if any. Give a brief but detailed explanation of what is wrong with each query you did not each select.) a) SELECT customer.customer num, customer name. FROM customer INNER JOIN rep ON customer.rep_num = rep.rep_num INNER JOIN customer.order_num= orders.order_num INNER JOIN order line ON orders.order num = order line.order num INNER JOIN part ON part.part_num= order_line.part_num WHERE description LIKE 'Home Gym' b) SELECT customer.customer_num, customer_name FROM customer INNER JOIN orders ON customer.customer_num = orders.customer_num INNER JOIN order_line ON orders.order_num = order_line.order_num INNER JOIN part ON part.part_num= order_line.part_num WHERE description LIKE 'Home Gym' AND city IN (SELECT city FROM rep; c) SELECT customer.customer_num, customer_name FROM customer, orders, order_line, part WHERE customer.customer_num = orders.customer_num orders.order_num= order_line.order_num AND AND part.part_num= order_line.part_num AND description LIKE 'Home Gym' AND city IN (SELECT city FROM rep); d) SELECT rep.city, customer.city, part.description FROM rep, customer, part WHERE description = 'Home Gym' AND customer.city = rep.city; e) SELECT customer.customer_num, customer_name FROM customer UNION SELECT rep.city Sua FROM rep UNION ALL SELECT part.description FROM part WHERE description = 'Home Gym'; Enter your answer here: Question# 5 (40 points) Which query would show which sales representatives (that have at least one customer) what the number of orders their customers have made sorting the list from highest number of orders to lowest number of orders? (Choose all that apply -if any. Give a brief but detailed explanation of what is wrong with each query you did not each select.) a) SELECT rep.rep_num, rep.first_name, rep.last_name, COUNT (orders.order num) FROM rep INNER JOIN customer ON rep.rep_num= customer.rep_num INNER JOIN orders ON orders. customer num = customer.customer num GROUP BY rep.rep_num HAVING COUNT (customer.customer_num) >= 1 ORDER BY COUNT (orders.order_num) DESC; b) SELECT rep.rep_num, rep.first_name, rep.last_name, COUNT (orders.order_num) FROM rep, customer, orders WHERE rep.rep_num= customer.rep_num AND orders. customer_num= customer.customer_num GROUP BY rep.rep_num HAVING COUNT (customer.customer_num) >= 1 ORDER BY COUNT (orders.order_num) DESC; c) SELECT rep.rep_num, rep.first_name, rep.last_name, COUNT (orders.order num). FROM rep, customer, orders WHERE rep.rep_num = customer.rep_num AND orders.customer_num = customer.customer_num AND COUNT (customer.customer num). >= 1 GROUP BY rep.rep_num ORDER BY COUNT (orders.order_num) DESC; d) SELECT rep.rep_num, rep. first_name, rep.last_name, COUNT (orders.order_num) FROM rep, customer, orders WHERE rep.rep_num = customer.rep_num AND orders.customer num = customer.customer num AND COUNT (customer.customer_num) > 1 GROUP BY rep.rep_num opp ORDER BY COUNT (orders.order num) DESC; ******* UNION SELECT rep.rep_num, rep.first_name, rep.last_name, COUNT COUNT (orders.order_num) FROM FROM rep, customer, orders ... WHERE rep.rep_num = customer.rep_num AND orders.customer_num= customer.customer_num. AND COUNT (customer.customer_num) = 1 GROUP BY rep.rep_num; e) SELECT rep.rep_num, rep.first_name, rep.last_name, COUNT (orders.order num) FROM rep INNER JOIN customer ON rep.rep_num= customer.rep_num INNER JOIN orders ON orders.order_num= customer.customer_num GROUP BY rep.rep_num HAVING COUNT (customer.customer_num) >= 1 ORDER BY COUNT (orders.order num) DESC; Enter your answer here: Section B (100 points): Additional Topics Question#1 (60 points) You company's online ordering system has a login screen and you detected the following input being entered: OR 0 = 0; DROP DATABASE GatorWareSoftware; a) What is probably being attempted here? b) Explain in detail what each part of the input above is supposed to do -starting with the single quote at the beginning. c) Explain in detail what measures can be used to protect your database against user inputs such as this. Enter your answer here: Question #2 (20 points) Using the testbed database: a) Create a view called LastHireDate for the GatorWare Employee table that displays the date the most recent employee was hired (if necessary recreate the GatorWareEmployee table first using the code provided in the folder on the Azure desktop). Provide both the code listing for the view, the code to run the view, and a screenshot of the view being run. (15 points) b) Explain how views can enhance database security. (5 points) Enter your answer here: Question# 3 (20 points) Using the testbed database: a) Create an index called JobCodeIndex for the GatorWare Employee table that speeds up searches using the jobCode column (if necessary recreate the GatorWareEmployee table first using the code provided in the folder on the Azure desktop). Provide the code for creating the index, and a screenshot of index being created. (10 points) b) What are the advantages and disadvantages of creating additional indexes on a table? (10 points) Enter your answer here: *Please note the due date and the final date for submissions to be accepted! Submissions past these dates will not be accepted and will receive a grade of zero. Identify all queries that meet the stated specifications. 2. For each query that does not meet the stated specifications briefly state exactly what is wrong with the query. If there is an error briefly explain what the error is. If it does not provide the required results briefly explain why Question#1(40 points) Which of the following queries display the total number of customer records that are being selected by the query? (Choose all that apply-if any. Give a brief but detailed explanation of what is wrong with each query you did not each select.) a) SELECT COUNT(*, SUM (balance) FROM customer INNER JOIN rep ON rep.rep_num= customer.customer_num; b) SELECT COUNT(*), SUM (balance) FROM rep INNER JOIN customer ON rep.rep_num= customer.rep_num; c) SELECT COUNT (DISTINCT customer.rep_num), SUM (balance) FROM customer RIGHT JOIN rep ON rep.rep_num= customer.rep_num; d) SELECT COUNT(rep.rep num), SUM (balance) FRUM rep LEFT JOIN customer ON rep.rep_num= customer.rep_num; e) SELECT COUNT(customer.rep_num), SUM (balance) FROM rep LEFT JOIN customer ON rep.rep_num= customer.rep_num; Enter your answer here: Question#2(40 points) Which of the following queries displays the customer name and customer number for customers that have a balance greater than the average balance for all customers and live in a city where no sales representatives live? (Choose all that apply -if any. Give a brief but detailed explanation of what is wrong with each query you did not each select.) a) SELECT customer_name, customer_num FROM customer WHERE customer.city (SELECT city FROM rep) AND customer.balance > (SELECT AVG (customer. balance) FROM customer); b) SELECT premiere.customer.customer_name, premiere.customer.customer_num FROM customer WHERE customer.city NOT IN (SELECT city FROM rep) AND customer.balance < (SELECT AVG (customer.balance) FROM customer); c) SELECT customer_name, premiere. customer.customer_num FROM customer WHERE customer.city NOT IN (SELECT city FROM rep) AND customer.balance > (SELECT SUM (customer.balance) /COUNT(*) FROM customer); d) SELECT customer.rep_num, customer.customer_name FROM customer WHERE customer.city NOT IN (SELECT city FROM rep) AND customer.balance > (SELECT AVG (customer.balance) FROM customer); e) SELECT customer.rep_num, customer.customer_name FROM customer WHERE customer.city NOT IN (SELECT city FROM rep) AND customer.balance < (SELECT AVG (customer.balance) FROM customer); Enter your answer here: Question#3 (40 points) Which of the following queries show the representative name and representative number of all representatives who are currently earning a rate equal to the minimum rate for all representatives and share a first initial with the customer that they represent? (Choose all that apply -if any. Give a brief but detailed explanation of what is wrong with each query you did not each select.) a) SELECT first_name, last_name, rep.rep_num FROM rep INNER JOIN customer ON rep.rep_num = customer.rep_num WHERE rate= (SELECT MIN (rate) FROM rep) AND LEFT (first_name, 1) = LEFT (customer_name, 1); b) SELECT first_name, last_name, rep.rep_num FROM rep WHERE rate = (SELECT MIN (rate) FROM rep) AND rep_num IN (SELECT c.rep_num FROM customer C, rep R. WHERE C.rep_num= R.Rep_num AND LEFT (C.customer_name, 1) LEFT (R.first_name, 1)); c) SELECT rep.rep_num, rep.first_name, rep.last_name FROM rep LEFT JOIN rep ON rep.rep_num= rep.rep_num WHERE rate = MIN (rate) AND first name LIKE customer_name; d) SELECT rep.rep_num, first_name, last_name FROM rep, customer WHERE rep.rep _num= customer.rep_num AND rate= (SELECT MIN (rate) FROM rep) AND LEFT (first_name, 1) = LEFT (customer_name, 1); e) SELECT rep.rep_num, first_name, last_name FROM rep, customer WHERE rep.rep_num= customer.customer_num AND rate = (SELECT MIN (rate) FROM rep) AND LEFT (first_name, 1) = LEFT (customer_name, 1); Enter your answer here: = Question#4 (40 points) Which query would show which customers have purchased a Home Gym and live in the same city as a sales representative? (Choose all that apply -if any. Give a brief but detailed explanation of what is wrong with each query you did not each select.) a) SELECT customer.customer num, customer name. FROM customer INNER JOIN rep ON customer.rep_num = rep.rep_num INNER JOIN customer.order_num= orders.order_num INNER JOIN order line ON orders.order num = order line.order num INNER JOIN part ON part.part_num= order_line.part_num WHERE description LIKE 'Home Gym' b) SELECT customer.customer_num, customer_name FROM customer INNER JOIN orders ON customer.customer_num = orders.customer_num INNER JOIN order_line ON orders.order_num = order_line.order_num INNER JOIN part ON part.part_num= order_line.part_num WHERE description LIKE 'Home Gym' AND city IN (SELECT city FROM rep; c) SELECT customer.customer_num, customer_name FROM customer, orders, order_line, part WHERE customer.customer_num = orders.customer_num orders.order_num= order_line.order_num AND AND part.part_num= order_line.part_num AND description LIKE 'Home Gym' AND city IN (SELECT city FROM rep); d) SELECT rep.city, customer.city, part.description FROM rep, customer, part WHERE description = 'Home Gym' AND customer.city = rep.city; e) SELECT customer.customer_num, customer_name FROM customer UNION SELECT rep.city Sua FROM rep UNION ALL SELECT part.description FROM part WHERE description = 'Home Gym'; Enter your answer here: Question# 5 (40 points) Which query would show which sales representatives (that have at least one customer) what the number of orders their customers have made sorting the list from highest number of orders to lowest number of orders? (Choose all that apply -if any. Give a brief but detailed explanation of what is wrong with each query you did not each select.) a) SELECT rep.rep_num, rep.first_name, rep.last_name, COUNT (orders.order num) FROM rep INNER JOIN customer ON rep.rep_num= customer.rep_num INNER JOIN orders ON orders. customer num = customer.customer num GROUP BY rep.rep_num HAVING COUNT (customer.customer_num) >= 1 ORDER BY COUNT (orders.order_num) DESC; b) SELECT rep.rep_num, rep.first_name, rep.last_name, COUNT (orders.order_num) FROM rep, customer, orders WHERE rep.rep_num= customer.rep_num AND orders. customer_num= customer.customer_num GROUP BY rep.rep_num HAVING COUNT (customer.customer_num) >= 1 ORDER BY COUNT (orders.order_num) DESC; c) SELECT rep.rep_num, rep.first_name, rep.last_name, COUNT (orders.order num). FROM rep, customer, orders WHERE rep.rep_num = customer.rep_num AND orders.customer_num = customer.customer_num AND COUNT (customer.customer num). >= 1 GROUP BY rep.rep_num ORDER BY COUNT (orders.order_num) DESC; d) SELECT rep.rep_num, rep. first_name, rep.last_name, COUNT (orders.order_num) FROM rep, customer, orders WHERE rep.rep_num = customer.rep_num AND orders.customer num = customer.customer num AND COUNT (customer.customer_num) > 1 GROUP BY rep.rep_num opp ORDER BY COUNT (orders.order num) DESC; ******* UNION SELECT rep.rep_num, rep.first_name, rep.last_name, COUNT COUNT (orders.order_num) FROM FROM rep, customer, orders ... WHERE rep.rep_num = customer.rep_num AND orders.customer_num= customer.customer_num. AND COUNT (customer.customer_num) = 1 GROUP BY rep.rep_num; e) SELECT rep.rep_num, rep.first_name, rep.last_name, COUNT (orders.order num) FROM rep INNER JOIN customer ON rep.rep_num= customer.rep_num INNER JOIN orders ON orders.order_num= customer.customer_num GROUP BY rep.rep_num HAVING COUNT (customer.customer_num) >= 1 ORDER BY COUNT (orders.order num) DESC; Enter your answer here: Section B (100 points): Additional Topics Question#1 (60 points) You company's online ordering system has a login screen and you detected the following input being entered: OR 0 = 0; DROP DATABASE GatorWareSoftware; a) What is probably being attempted here? b) Explain in detail what each part of the input above is supposed to do -starting with the single quote at the beginning. c) Explain in detail what measures can be used to protect your database against user inputs such as this. Enter your answer here: Question #2 (20 points) Using the testbed database: a) Create a view called LastHireDate for the GatorWare Employee table that displays the date the most recent employee was hired (if necessary recreate the GatorWareEmployee table first using the code provided in the folder on the Azure desktop). Provide both the code listing for the view, the code to run the view, and a screenshot of the view being run. (15 points) b) Explain how views can enhance database security. (5 points) Enter your answer here: Question# 3 (20 points) Using the testbed database: a) Create an index called JobCodeIndex for the GatorWare Employee table that speeds up searches using the jobCode column (if necessary recreate the GatorWareEmployee table first using the code provided in the folder on the Azure desktop). Provide the code for creating the index, and a screenshot of index being created. (10 points) b) What are the advantages and disadvantages of creating additional indexes on a table? (10 points) Enter your answer here: *Please note the due date and the final date for submissions to be accepted! Submissions past these dates will not be accepted and will receive a grade of zero.