Questions from Database Systems

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 follow the recommendations you gave in...

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; What indexes would you recommend for the query you wrote...

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; How would you rewrite the query to ensure that the index...

Q: SELECT EMP_LNAME, EMP_FNAME, EMP_

SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, YEAR(EMP_DOB) AS YEAR FROM EMPLOYEE WHERE YEAR (EMP_DOB) = 1966; What is the likely data sparsity of the EMP_DOB column?

Q: SELECT EMP_LNAME, EMP_FNAME, EMP_

SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, YEAR(EMP_DOB) AS YEAR FROM EMPLOYEE WHERE YEAR(EMP_DOB) = 1966; Should you create an index on EMP_DOB? Why or why not?

Q: SELECT EMP_LNAME, EMP_FNAME, EMP_

SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, YEAR(EMP_DOB) AS YEAR FROM EMPLOYEE WHERE YEAR (EMP_DOB) = 1966; What type of database I/O operations will likely be used by the query? (See Table 11.3.)

Q: Write the SQL code to generate the total hours worked and the

Write the SQL code to generate the total hours worked and the total charges made by all employees. The results are shown in Figure P7.24. (Hint: This is a nested query. If you use Microsoft Access, y...

Q: Given the following query SELECT P_CODE, P

Given the following query SELECT P_CODE, P_PRICE FROM PRODUCT WHERE P_PRICE >= (SELECT AVG(P_PRICE) FROM PRODUCT); Assuming that there are no table statistics, what type of optimization will...

Q: Given the following query SELECT P_CODE, P

Given the following query SELECT P_CODE, P_PRICE FROM PRODUCT WHERE P_PRICE >= (SELECT AVG(P_PRICE) FROM PRODUCT); What type of database I/O operations will likely be used by the query? (See...

Q: Given the following query SELECT P_CODE, P

Given the following query SELECT P_CODE, P_PRICE FROM PRODUCT WHERE P_PRICE >= (SELECT AVG(P_PRICE) FROM PRODUCT); What is the likely data sparsity of the P_PRICE column? Figure P11.7 The Ch...