Questions from Database Systems

Q: SELECT P_CODE, SUM(LINE_UNITS)

SELECT P_CODE, SUM(LINE_UNITS) FROM LINE GROUP BY P_CODE HAVING SUM (LINE_UNITS) > (SELECT MAX(LINE_UNITS) FROM LINE); Write the command to create statistics for this table.

Q: SELECT P_CODE, P_QOH*P_

SELECT P_CODE, P_QOH*P_PRICE FROM PRODUCT WHERE P_QOH*P_PRICE > (SELECT AVG(P_QOH*P_PRICE) FROM PRODUCT) What is the likely data sparsity of the P_QOH and P_PRICE columns?

Q: SELECT P_CODE, P_QOH*P_

SELECT P_CODE, P_QOH*P_PRICE FROM PRODUCT WHERE P_QOH*P_PRICE > (SELECT AVG(P_QOH*P_PRICE) FROM PRODUCT) Should you create an index? If so, what would the index column(s) be, and why should you creat...

Q: SELECT V_CODE, V_NAME, V_

SELECT V_CODE, V_NAME, V_CONTACT, V_STATE FROM VENDOR WHERE V_STATE = ‘TN’ ORDER BY V_NAME; What indexes should you create and why? Write the SQL command to create the indexes.

Q: Using the data in the ASSIGNMENT table, write the SQL code

Using the data in the ASSIGNMENT table, write the SQL code that will yield the total number of hours worked for each employee and the total charges stemming from those hours worked. The results of run...

Q: SELECT V_CODE, V_NAME, V_

SELECT V_CODE, V_NAME, V_CONTACT, V_STATE FROM VENDOR WHERE V_STATE = ‘TN’ ORDER BY V_NAME; Assume that 10,000 vendors are distributed as shown in Table P11.18. What percentage of rows will be return...

Q: SELECT V_CODE, V_NAME, V_

SELECT V_CODE, V_NAME, V_CONTACT, V_STATE FROM VENDOR WHERE V_STATE = ‘TN’ ORDER BY V_NAME; What type of I/O database operations would be most likely to be used to execute that query?

Q: SELECT EMP_LNAME, EMP_FNAME, EMP_

SELECT EMP_LNAME, EMP_FNAME, EMP_AREACODE, EMP_SEX FROM EMPLOYEE WHERE EMP_SEX = ‘F’ AND EMP_AREACODE = ‘615’ ORDER BY EMP_LNAME, EMP_FNAME; What indexes should you create? Write the required SQL com...

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

Using Table 11.4 as an example, create two alternative access plans.

Q: Assume that you have 10,000 different products stored in the

Assume that you have 10,000 different products stored in the PRODUCT table and that you are writing a Web-based interface to list all products with a quantity on hand (P_QOH) that is less than or equa...