�PAGE � �PAGE �1� Chapter 9: Introduction to SQL Programming Techniques CHAPTER 9: INTRODUCTION TO SQL PROGRAMMING TECHNIQUES No exercises. 9.15 (optional) A materialized view is a view that is stored as a physical table. a. Rewrite the views created in Figure 9.1 so that they are materialized views. b. What are the advantages of materialized views over traditional views? What are the disadvantages? 9.16 Create a function in PSM that computes the median salary for the EMPLOYEE table shown in Figure 5.5. 9.16 a. CREATE MATERIALIZED VIEW WORKS_ON1 AS SELECT FNAME, LNAME, PNAME, HOURS FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN = ESSN AND PNO = PNUMBER; CREATE MATERIALIZED VIEW DEPT_INFO (DEPT_NAME, NO_OF_EMPS, TOTAL_SAL) AS SELECT DNAME, COUNT(*), SUM(SALARY) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER = DNO GROUP BY DNAME b. If a materialized view that contains enough information, we are able to answer our query much faster. 9.17 This implementation of MEDIAN_SALARY avoids the use of procedural loops which are much slower than even such a complicated query. This function can be broken down into three major steps. First, we create an ordered list of salaries with ranking. Second, we select the element on or elements around the median point. Finally, we average those median values that were selected. CREATE FUNCTION MEDIAN_SALARY () RETURNS FLOAT RETURN (SELECT AVG(SALARY) AS MedianSalary FROM (SELECT COUNT(e1.SALARY) AS SalaryRank, e1.SALARY FROM EMPLOYEE AS e1 INNER JOIN EMPLOYEE AS e2 ON e1.SALARY > e2.SALARY OR (e1.SALARY = e2.SALARY AND e1.SSN >= e2.SSN) GROUP BY e1.SSN HAVING COUNT(e1.SALARY) >= (SELECT COUNT(*)/2 FROM EMPLOYEE) - 0.5 AND COUNT(e1.SALARY) <= (SELECT COUNT(*)/2 FROM EMPLOYEE) + 0.5)) Alternatively, this function could have been implemented using a CURSOR and looping. These two methods are functionally the same, however the method implemented here will be performed quicker due to the fact that the looping is internal to the database engine. �PAGE � Copyright © 2007 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.