Elm04_09
1 pág.

Elm04_09

Disciplina:Banco de Dados II332 materiais2.948 seguidores
Pré-visualização1 página
�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.