﻿ Elm04 09 - Banco de Dados II
1 pág.

# Elm04_09

Disciplina:Banco de Dados II416 materiais3.306 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.

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 �