Questions from Database Systems

Q: SELECT P_CODE, P_DESCRIPT, P_

SELECT P_CODE, P_DESCRIPT, P_PRICE, PRODUCT.V_CODE, V_STATE FROM PRODUCT P, VENDOR V WHERE P.V_CODE = V.V_CODE AND V_STATE = ‘NY’ AND V_AREACODE = ‘212’; ORDER BY P_PRICE; What indexes would you rec...

Q: SELECT P_CODE, P_DESCRIPT, P_

SELECT P_CODE, P_DESCRIPT, P_PRICE, PRODUCT.V_CODE, V_STATE FROM PRODUCT P, VENDOR V WHERE P.V_CODE = V.V_CODE AND V_STATE = ‘NY’ AND V_AREACODE = ‘212’; ORDER BY P_PRICE; Write the commands requir...

Q: SELECT P_CODE, P_DESCRIPT, P_

SELECT P_CODE, P_DESCRIPT, P_PRICE, PRODUCT.V_CODE, V_STATE FROM PRODUCT P, VENDOR V WHERE P.V_CODE = V.V_CODE AND V_STATE = ‘NY’ AND V_AREACODE = ‘212’; ORDER BY P_PRICE; Write the command(s) used...

Q: SELECT P_CODE, P_DESCRIPT, P_

SELECT P_CODE, P_DESCRIPT, P_QOH, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = ‘21344’ ORDER BY P_CODE; What index would you recommend, and what command would you use?

Q: SELECT P_CODE, P_DESCRIPT, P_

SELECT P_CODE, P_DESCRIPT, P_QOH, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = ‘21344’ ORDER BY P_CODE; How should you rewrite the query to ensure that it uses the index you created in your solution...

Q: Write a query to produce the total number of hours and charges

Write a query to produce the total number of hours and charges for each of the projects represented in the ASSIGNMENT table. The output is shown in Figure P7.23. Figure P7.23 Total hour and charges by...

Q: SELECT P_CODE, P_DESCRIPT, P_

SELECT P_CODE, P_DESCRIPT, P_QOH, P_PRICE, V_CODE FROM PRODUCT WHERE P_QOH < P_MIN AND P_MIN = P_REORDER’ AND P_REORDER = 50; ORDER BY P_QOH; Use the recommendations given in Section 11-5b to rewri...

Q: SELECT P_CODE, P_DESCRIPT, P_

SELECT P_CODE, P_DESCRIPT, P_QOH, P_PRICE, V_CODE FROM PRODUCT WHERE P_QOH < P_MIN AND P_MIN = P_REORDER’ AND P_REORDER = 50; ORDER BY P_QOH; What indexes you would recommend? Write the commands to...

Q: SELECT CUS_CODE, MAX(LINE_UNITS*

SELECT CUS_CODE, MAX(LINE_UNITS*LINE_PRICE) FROM CUSTOMER NATURAL JOIN INVOICE NATURAL JOIN LINE WHERE CUS_AREACODE = ‘615’ GROUP BY CUS_CODE; Assuming that you generate 15,000 invoices per month, wh...

Q: Using Table 11.4 as an example, create two alternative

Using Table 11.4 as an example, create two alternative access plans. Use the following assumptions: a. There are 8,000 employees. b. There are 4,150 female employees. c. There are 370 employees in ar...