Chapter 7 An Introduction to Structured Query Language (SQL).

Chapter 7 An Introduction to Structured Query Language (SQL) 239 Answers to Review Questions 1. In a SELECT query, what is the difference between a WHERE clause and a HAVING clause? Both a WHERE clause and a HAVING c ...

Chapter 7 An Introduction to Structured Query Language (SQL) 239 Answers to Review Questions 1. In a SELECT query, what is the difference between a WHERE clause and a HAVING clause? Both a WHERE clause and a HAVING clause can be used to eliminate rows from the results of a query. The differences are 1) the WHERE clause eliminates rows before any grouping for aggregate functions occurs while the HAVING clause eliminates groups after the grouping has been done, and 2) the WHERE clause cannot contain an aggregate function but the HAVING clause can. 2. Explain why the following command would create an error, and what changes could be made to fix the error. SELECT V_CODE, SUM(P_QOH) FROM PRODUCT; The command would generate an error because an aggregate function is applied to the P_QOH attribute but V_CODE is neither in an aggregate function or in a GROUP BY. This can be fixed by either 1) placing V_CODE in an appropriate aggregate function based on the data that is being requested by the user, 2) adding a GROUP BY clause to group by values of V_CODE (i.e. GROUP BY V_CODE), 3) removing the V_CODE attribute from the SELECT clause, or 4) removing the Sum aggregate function from P_QOH. Which of these solutions is most appropriate depends on the question that the query was intended to answer. 3. What type of integrity is enforced when a primary key is declared? Creating a primary key constraint enforces entity integrity (i.e. no part of the primary key can contain a null and the primary key values must be unique). 4. Explain why it might be more appropriate to declare an attribute that contains only digits as a character data type instead of a numeric data type. An attribute that contains only digits may be properly defined as character data when the values are nominal; that is, the values do not have numerical significance but serve only as labels such as ZIP codes and telephone numbers. One easy test is to consider whether or not a leading zero should be retained. For the ZIP code 03133, the leading zero should be retained; therefore, it is appropriate to define it as character data. For the quantity on hand of 120, we would not expect to retain a leading zero such as 0120; therefore, it is appropriate to define the quantity on hand as a numeric data type. 5. What is the difference between a column constraint and a table constraint? A column constraint can refer to only the attribute with which it is specified. A table constraint can refer to any attributes in the table. 6. What are ―referential constraint actions‖? Referential constraint actions, such as ON DELETE CASCADE, are default actions that the DBMS should take when a DML command would result in a referential integrity constraint violation.Chapter 7 An Introduction to Structured Query Language (SQL) 240 Without referential constraint actions, DML commands that would result in a violation of referential integrity will fail with an error indicating that the referential integrity constrain cannot be violated. Referential constraint actions can allow the DML command to successfully complete while making the designated changes to the related records to maintain referential integrity. 7. Rewrite the following WHERE clause without the use of the IN special operator. WHERE V_STATE IN (‗TN‘, ‗FL‘, ‗GA‘) WHERE V_STATE = 'TN' OR V_STATE = 'FL' OR V_STATE = 'GA' Notice that each criteria must be complete (i.e. attribute-operator-value). 8. Explain the difference between an ORDER BY clause and a GROUP BY clause. An ORDER BY clause has no impact on which rows are returned by the query, it simply sorts those rows into the specified order. A GROUP BY clause does impact the rows that are returned by the query. A GROUP BY clause gathers rows into collections that can be acted on by aggregate functions. 9. Explain why the two following commands produce different results. SELECT DISTINCT COUNT (V_CODE) FROM PRODUCT; SELECT COUNT (DISTINCT V_CODE) FROM PRODUCT; The difference is in the order of operations. The first command executes the Count function to count the number of values in V_CODE (say the count returns "14" for example) including duplicate values, and then the Distinct keyword only allows one count of that value to be displayed (only one row with the value "14" appears as the result). The second command applies the Distinct keyword to the V_CODEs before the count is taken so only unique values are counted. 10. What is the difference between the COUNT aggregate function and the SUM aggregate function? COUNT returns the number of values without regard to what the values are. SUM adds the values together and can only be applied to numeric values. 11. Explain why it would be preferable to use a DATE data type to store date data instead of a character data type. The DATE data type uses numeric values based on the Julian calendar to store dates. This makes date arithmetic such as adding and subtracting days or fractions of days possible (as well as numerous special date-oriented functions discussed in the next chapter!). 12. What is a recursive join? A recursive join is a join in which a table is joined to itself.Chapter 7 An Introduction to Structured Query Language (SQL) 241 Problem Solutions O n l i n e C o n t e n t Problems 1 – 25 are based on the Ch07_ConstructCo database located www.cengagebrain.com. This database is stored in Microsoft Access format. The website provides Oracle, MySQL, and MS SQL Server script files. The Ch07_ConstructCo database stores data for a consulting company that tracks all charges to projects. The charges are based on the hours each employee works on each project. The structure and contents of the Ch07_ConstructCo database are shown in Figure P7.1. Figure P7.1 Structure and contents of the Ch07_ConstructCo database Note that the ASSIGNMENT table in Figure P7.1 stores the JOB_CHG_HOUR values as an attribute (ASSIGN_CHG_HR) to maintain historical accuracy of the data. The JOB_CHG_HOUR values are likely to change over time. In fact, a JOB_CHG_HOUR change will be reflected in the ASSIGNMENT table. And, naturally, the employee primary job assignmentChapter 7 An Introduction to Structured Query Language (SQL) 242 might change, so the ASSIGN_JOB is also stored. Because those attributes are required to maintain the historical accuracy of the data, they are not redundant. Given the structure and contents of the Ch07_ConstructCo database shown in Figure P7.1, use SQL commands to answer Problems 1–25. 1. Write the SQL code that will create the table structure for a table named EMP_1. This table is a subset of the EMPLOYEE table. The basic EMP_1 table structure is summarized in the table below. (Note that the JOB_CODE is the FK to JOB.) ATTRIBUTE (FIELD) NAME DATA DECLARATION EMP_NUM CHAR(3) EMP_LNAME VARCHAR(15) EMP_FNAME VARCHAR(15) EMP_INITIAL CHAR(1) EMP_HIREDATE DATE JOB_CODE CHAR(3) CREATE TABLE EMP_1 ( EMP_NUM CHAR(3) PRIMARY KEY, EMP_LNAME VARCHAR(15) NOT NULL, EMP_FNAME VARCHAR(15) NOT NULL, EMP_INITIAL CHAR(1), EMP_HIREDATE DATE, JOB_CODE CHAR(3), FOREIGN KEY (JOB_CODE) REFERENCES JOB); NOTE We have already provided the EMP_1 table for you. If you try to run the preceding query, you will get an error message because the EMP_1 table already exits. 2. Having created the table structure in Problem 1, write the SQL code to enter the first two rows for the table shown in Figure P7.2. Figure P7.2 The contents of the EMP_1 tableChapter 7 An Introduction to Structured Query Language (SQL) 243 INSERT INTO EMP_1 VALUES („101‟, „News‟, „John‟, „G‟, ‟08-Nov-00‟, „502‟); INSERT INTO EMP_1 VALUES („102‟, „Senior‟, „David‟, „H‟, ‟12-Jul-89‟, „501‟); 3. Assuming the data shown in the EMP_1 table have been entered, write the SQL code that will list all attributes for a job code of 502. SELECT * FROM EMP_1 WHERE JOB_CODE = „502‟; 4. Write the SQL code that will save the changes made to the EMP_1 table. COMMIT; 5. Write the SQL code to change the job code to 501 for the person whose employee number (EMP_NUM) is 107. After you have completed the task, examine the results, and then reset the job code to its original value. UPDATE EMP_1 SET JOB_CODE = „501‟ WHERE EMP_NUM = „107‟; To see the changes: SELECT * FROM EMP_1 WHERE EMP_NUM = „107‟; To reset, use ROLLBACK;Chapter 7 An Introduction to Structured Query Language (SQL) 244 6. Write the SQL code to delete the row for the person named William Smithfield, who was hired on June 22, 2004, and whose job code classification is 500. (Hint: Use logical operators to include all of the information given in this problem.) DELETE FROM EMP_1 WHERE EMP_LNAME = 'Smithfield' AND EMP_FNAME = 'William' AND EMP_HIREDATE = '22-June-04' AND JOB_CODE = '500'; 7. Write the SQL code that will restore the data to its original status; that is, the table should contain the data that existed before you made the changes in Problems 5 and 6. ROLLBACK; 8. Write the SQL code to create a copy of EMP_1, naming the copy EMP_2. Then write the SQL code that will add the attributes EMP_PCT and PROJ_NUM to its structure. The EMP_PCT is the bonus percentage to be paid to each employee. The new attribute characteristics are: EMP_PCTNUMBER(4,2) PROJ_NUMCHAR(3) (Note: If your SQL implementation allows it, you may use DECIMAL(4,2) rather than NUMBER(4,2).) There are two way to get this job done. The two possible solutions are shown next. Solution A: CREATE TABLE EMP_2 ( EMP_NUM CHAR(3) NOT NULL UNIQUE, EMP_LNAME VARCHAR(15) NOT NULL, EMP_FNAME VARCHAR(15) NOT NULL, EMP_INITIAL CHAR(1), EMP_HIREDATE DATE NOT NULL, JOB_CODE CHAR(3) NOT NULL, PRIMARY KEY (EMP_NUM), FOREIGN KEY (JOB_CODE) REFERENCES JOB); INSERT INTO EMP_2 SELECT * FROM EMP_1; ALTER TABLE EMP_2 ADD (EMP_PCT NUMBER (4,2)),Chapter 7 An Introduction to Structured Query Language (SQL) 245 ADD (PROJ_NUM CHAR(3)); Solution B: CREATE TABLE EMP_2 AS SELECT * FROM EMP_1; ALTER TABLE EMP_2 ADD (EMP_PCT NUMBER (4,2)), ADD (PROJ_NUM CHAR(3)); 9. Write the SQL code to change the EMP_PCT value to 3.85 for the person whose employee number (EMP_NUM) is 103. Next, write the SQL command sequences to change the EMP_PCT values as shown in Figure P7.9. Figure P7.9 The contents of the EMP_2 table UPDATE EMP_2 SET EMP_PCT = 3.85 WHERE EMP_NUM = '103'; To enter the remaining EMP_PCT values, use the following SQL statements: UPDATE EMP_2 SET EMP_PCT = 5.00 WHERE EMP_NUM = „101‟; UPDATE EMP_2 SET EMP_PCT = 8.00 WHERE EMP_NUM = „102‟; Follow this format for the remaining rows.Chapter 7 An Introduction to Structured Query Language (SQL) 246 10. Using a single command sequence, write the SQL code that will change the project number (PROJ_NUM) to 18 for all employees whose job classification (JOB_CODE) is 500. UPDATE EMP_2 SET PROJ_NUM = '18' WHERE JOB_CODE = '500'; 11. Using a single command sequence, write the SQL code that will change the project number (PROJ_NUM) to 25 for all employees whose job classification (JOB_CODE) is 502 or higher. When you finish Problems 10 and 11, the EMP_2 table will contain the data shown in Figure P7.11. (You may assume that the table has been saved again at this point.) Figure P7.11 The EMP_2 table contents after the modification UPDATE EMP_2 SET PROJ_NUM = '25' WHERE JOB_CODE > = '502' 12. Write the SQL code that will change the PROJ_NUM to 14 for those employees who were hired before January 1, 1994 and whose job code is at least 501. (You may assume that the table will be restored to its condition preceding this question.) UPDATE EMP_2 SET PROJ_NUM = '14' WHERE EMP_HIREDATE <= ' 01-Jan-94' AND JOB_CODE >= '501'; 13. Write the two SQL command sequences required to:Chapter 7 An Introduction to Structured Query Language (SQL) 247 There are many ways to accomplish both tasks. We are illustrating the shortest way to do the job next. a. Create a temporary table named TEMP_1 whose structure is composed of the EMP_2 attributes EMP_NUM and EMP_PCT. The SQL code shown in problem 13b contains the solution for problem 13a. b. Copy the matching EMP_2 values into the TEMP_1 table. CREATE TABLE TEMP_1 AS SELECT EMP_NUM, EMP_PCT FROM EMP_2; An alternate way would be to create the table and then, use an INSERT with a sub-select to populate the rows. CREATE TABLE TEMP_1 AS ( EMP_NUM CHAR(3), EMP_PCT NUMBER(4,2)); INSERT INTO TEMP_1 SELECT EMP_NUM, EMP_PCT FROM EMP_2; 14. Write the SQL command that will delete the newly created TEMP_1 table from the database. DROP TABLE TEMP_1; 15. Write the SQL code required to list all employees whose last names start with Smith. In other words, the rows for both Smith and Smithfield should be included in the listing. Assume case sensitivity. SELECT * FROM EMP_2 WHERE EMP_LNAME LIKE 'Smith%'; 16. Using the EMPLOYEE, JOB, and PROJECT tables in the Ch07_ConstructCo database (see Figure P7.1), write the SQL code that will produce the results shown in Figure P7.16. Figure P7.16 The query results for Problem 16Chapter 7 An Introduction to Structured Query Language (SQL) 248 SELECT PROJ_NAME, PROJ_VALUE, PROJ_BALANCE, EMPLOYEE.EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMPLOYEE.JOB_CODE, JOB.JOB_DESCRIPTION, JOB.JOB_CHG_HOUR FROM PROJECT, EMPLOYEE, JOB WHERE EMPLOYEE.EMP_NUM = PROJECT.EMP_NUM AND JOB.JOB_CODE = EMPLOYEE.JOB_CODE; 17. Write the SQL code that will produce a virtual table named REP_1. The virtual table should contain the same information that was shown in Problem 16. CREATE VIEW REP_1 AS SELECT PROJ_NAME, PROJ_VALUE, PROJ_BALANCE, EMPLOYEE.EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMPLOYEE.JOB_CODE, JOB.JOB_DESCRIPTION, JOB.JOB_CHG_HOUR FROM PROJECT, EMPLOYEE, JOB WHERE EMPLOYEE.EMP_NUM = PROJECT.EMP_NUM AND JOB.JOB_CODE = EMPLOYEE.JOB_CODE; 18. Write the SQL code to find the average bonus percentage in the EMP_2 table you created in Problem 8. SELECT AVG(EMP_PCT) FROM EMP_2; 19. Write the SQL code that will produce a listing for the data in the EMP_2 table in ascending order by the bonus percentage. SELECT * FROM EMP_2 ORDER BY EMP_PCT; 20. Write the SQL code that will list only the distinct project numbers found in the EMP_2 table. SELECT DISTINTC PROJ_NUM FROM EMP_2;