Identify all queries that meet the stated specifications. 2. For each query that does not meet...
Fantastic news! We've Found the answer you've been seeking!
Question:
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.
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
-
For the given reaction the formation of products I and II follows, which of the following mechanism: N CHO II hy CHO CHO I: Con rotatory thermal eight electron electrocyclic reaction. It: Dis...
-
ACC 122 I. On July 1st, Gillian Gardener began Gillian's Gardening Services (GGS) and had the following transactions: 7-1: Gillian Gardener contributed $200,000 in cash to GGS. 7-1: GGS signed a...
-
Complete the following assignment below: Shirley Williams Apparel Store purchases clothing merchandise on account of various vendors. Below is an invoice from Terra Wear. TERRA WEAR ACTION SPORT...
-
Following procedures similar to those for the homogeneous problem (see Section 8.4.1), develop the following stress field for a pressurized hole in an infinite nonhomogeneous medium with moduli...
-
A 10.0- resistor, 10.0-mH inductor, and 100-F capacitor are connected in series to a 50.0-V (rms) source having variable frequency. Find the energy that is delivered to the circuit during one period...
-
What is the difference between true stress and engineering stress? True strain and engineering strain?
-
The assembly consists of two 12-mm-diameter A992 steel rods AB and CD, a 20-mm-diameter 6061-T6 aluminum rod EF, and a rigid bar AEC. If P = 20 kN, determine the displacement of end F of rod EF. 600...
-
Hanna Corporation owns 80% of the outstanding voting stock of Fellow Inc. At the date of acquisition, Fellow's retained earnings were $2,100,000. On December 31, Year 2, Hanna Inc. sold equipment to...
-
What is the net electric force on charge B in (Figure 1)? Suppose that a = 2.5 cm and b = 1.2 cm. Express your answer to two significant figures and include the appropriate units. Enter negative...
-
For the case The WM. Wrigley Jr. Company: Capital Structure, Valuation, and the Cost of Capital(Darden Case: UVAF1482) please answer the following questions and explain your reasoningwhere...
-
recommend a price and marketing strategy for the newly established automobile manufacturer seeking to enter the market for specialist competition motorcycles.compare your recommendation to those for...
-
Your Listening Self. Using the four dimensions of listening effectiveness discussed here (empathic objective, nonjudgmentalcritical, surfacedepth, and activeinactive), how would you describe yourself...
-
Other information: 1. All accounts have normal balances. 2. $27,900 of the note payable balance is due by October 31, 2024. The final task in the year-end process was to assess the assets for...
-
Garden City Company changed from an accounting principle that is not generally accepted to one that is generally accepted. How should the effect of the change be reported? Explain
-
What is a labor union? Do you agree or disagree with its purpose? Defend your answer. Define procedural justice. How does it relate to unions? Discuss the commonly encountered barriers to effective...
-
Convex Mechanical Supplies produces a product with the following costs as of July 1, 20X1: Material Labor Overhead $ 5 3 2 $ 10 Beginning inventory at these costs on July 1 was 7,900 units. From July...
-
An X-ray beam of wavelength 1.54 A is diffracted from the (110) planes of a solid with a cubic lattice of lattice constant 3.08 . The first-order Bragg diffraction occurs at (a) sin (b) sin G 22 (c)...
-
Construct a 4 x 25 design confounded in two blocks of 16 observations each. Outline the analysis of variance for this design.
-
When do you use OLAP?
-
What is a domain?
-
Define a view named Item Order. It consists of the item number, description, price, order number, order date, number ordered, and quoted price for all order lines currently on file. a. Using SQL,...
-
A4-stroke, 4-cylinder, single acting spark ignition petrol engine develops \(20 \mathrm{~kW}\) brake power at \(3000 \mathrm{rpm}\). The following data are given : Bore \(=65 \mathrm{~mm}\), Stroke...
-
A six cylinder four stroke I.C. Engine is to develop \(100 \mathrm{~kW}\) i.p. at 800 RPM. The stroke to bare ratio is 1.25. Assuming Mechanical efficiency of \(80 \%\) and break mean effective...
-
The following data is available for a four stroke petrol engine stroke volume \(=6\) Litres. Mean effective pressure \(=6\) bar. Speed of engine \(=750\) RPM. Calculate i.p. of the engine.
Study smarter with the SolutionInn App