SQL: Consultas. Fragmento del original: Asignatura: Sistemas de Información, Tema 2 José Ramón Ríos Viqueira Curso

SQL:2003 - Consultas

Fragmento del original:

SISTEMAS DE BASES DE DATOS BASADOS EN OBJETOS

Asignatura: Sistemas de Información, Tema 2

José Ramón Ríos Viqueira

Curso 2005-2006



Tipos complejos: ROW, ARRAY, MULTISET

CONSULTAS

CREATE TABLE empleado (

id_emp INTEGER PRIMARY KEY, nombre VARCHAR(50),

direccion ROW(calle VARHCAR(100), num INTEGER, loc VARCHAR(10), salario DECIMAL(9, 2),

hijos VARCHAR(50) ARRAY,

cursos ROW(nombre VARCHAR(50), nota DECIMAL(3, 1)) MULTISET dep INTEGER,

FOREING KEY dep REFERENCES departamento(id_dep) );

CREATE TABLE departamento ( id_dep INTEGER PRIMARY KEY, nombre VARCHAR(50),

direccion ROW(calle VARHCAR(100), num INTEGER, loc VARCHAR(10), presupuesto DECIMAL(12, 2),



Tipos complejos: ROW, ARRAY, MULTISET



Desanidar tipos colección

CONSULTAS

Obtener una lista con los hijos de cada empleado

SELECT e.nombre, h.hijo

FROM empleado AS e, UNNEST (e.hijos) AS h(hijo)

Obtener para cada empleado de Santiago la nota media en los cursos

que aprobó

SELECT e.nombre, AVG(c.nota)

FROM empleado AS e, UNNEST (e.cursos) AS c(nota) WHERE e.direccion.loc = 'Santiago' and c.nota >= 5 GROUP BY e.nombre



Tipos complejos: ROW, ARRAY, MULTISET



Anidar tipos colección

CONSULTAS

Obtener un informe en el que para cada departamento cuyo presupuesto

sea mayor que la suma de los sueldos de sus empleados se indique:

• Nombre del departamento,

• Presupuesto del departamento,

• Relación de empleados con el nombre, salario y número de hijos

de cada empleado

• Suma del salario de todos sus empleados

SELECT d.nombre, d.presupuesto,

MULTISET(SELECT e2.nombre, e2.salario, count(h.hijo) AS Hijos FROM empleado AS e2, UNNEST (e2.hijos) AS h(hijo) WHERE d.id_dep = e2.dep

GROUP BY e2.nombre, e2.salario), SUM(e.salario) AS SalarioTotal

FROM departamento AS d, empleado AS e WHERE e.id_dep=e.dep

GROUP BY d.nombre, d.presupuesto HAVING d.presupuesto > SUM(e.salario)



Uso de tipos estructurados: métodos y funciones

CONSULTAS

CARRETERAS cod_carrt N-651 A-9 F cp-0804 064-001 propietario Estatal Estatal Provincial Municipal Estado Malo Bueno Regular Bueno Trazado L1 L2 L3 L4 CENTROS_SALUD id_cs 1 2 nombre Viñas San Cide camas 0 10 posicion P1 P2

Valores de tipo Linea

Valores de tipo Punto

CREATE TYPE punto UNDER geo (

x FLOAT, y FLOAT)

INSTANTIABLE NOT FINAL;

CREATE TYPE linea UNDER geo( coords punto ARRAY)

INSTANTIABLE NOT FINAL

INSTANCE METHOD longitud() RETURNS FLOAT;

CREATE FUNCTION distancia (g1 geo, g2 geo) RETURNS FLOAT BEGIN ... END

Longitud total de carreteras municipales en mal estado

CONSULTAS

SELECT

sum

(trazado.longitud)

FROM

carreteras

WHERE

propietario = 'Municipal'

and

estado = 'Malo'

Número total de camas de los centros de salud que se

encuentran a menos de 1 km de un tramo de carretera con

longitud mayor de 2 km y estado bueno

SELECT

sum

(camas)

FROM

carreteras

AS

c, centros_salud

AS

cs

WHERE

c.estado = 'bueno'

and

c.longitud < 2000

and

distancia(c.trazado, cs.posicion) < 1000

CONSULTAS



Uso de tipos estructurados: Herencia

CREATE TABLE rios AS (

id_rio INTEGER PRIMARY KEY, nombre VARHCAR(50),

geom geo)

SELECT nombre, SUM(TREAT(geom AS linea).longitud) FROM rios

WHERE geom IS OF (linea) GROUP BY nombre

Para cada río, devolver la longitud total de

las partes que son de tipo línea

2, 'Tambre'

1, 'Tambre'



Referencias

CONSULTAS

CREATE TYPE Empleado AS ( nombre VARCHAR (50),

salario_base DECIMAL (9, 2), complementos DECIMAL(9, 2), dep REF(Departamento))

NOT FINAL

REF IS SYSTEM GENERATED INSTANCE METHOD salario() RETURNS DECIMAL(9, 2);

CREATE TYPE Departamento AS ( nombre VARCHAR (50),

dir DIRECCION,

emps REF(Empleado) MULTISET, director REF(Empleado))

NOT FINAL

REF IS SYSTEM GENERATED;

CREATE TABLE Empleados OF Empleado

(REF IS oid SYSTEM GENERATED,

dept WITH OPTIONS SCOPE Departamentos);

CREATE TABLE Departamentos OF Departamento

(REF IS oid SYSTEM GENERATED,

emps WITH OPTIONS SCOPE Empleados, director WITH OPTIONS SCOPE Empleados);



Referencias



Dereferenciación de atributos

CONSULTAS

Para cada departamento de santiago, obtén su nombre y el nombre

de sus empleados

SELECT e.nombre, e.dept->nombre FROM Empleados e

WHERE e.dep->dir.loc = 'Santiago'

SELECT e.nombre, (SELECT d.nombre FROM departamentos d WHERE d.oid = e.dep) FROM Empleados e

WHERE (SELECT dir.loc FROM departamentos d WHERE d.oid = e.dep) = 'Santiago' SELECT e.nombre, d.nombre

FROM Empleados e LEFT JOIN Departamentos d ON (e.dep = d.oid)

SELECT e.nombre, DEREF(e.dept).nombre FROM Empleados e

CONSULTAS

Para cada departamento obtener la suma de los salarios de sus

empleados



Referencias



Dereferenciación de métodos

SELECT d.nombre, SUM(e.ptr->salario)

FROM Departamentos d, UNNEST(d.emps) AS e(ptr) GROUP BY d.nombre



Los métodos no se puede aplicar directamente a las tuplas

SELECT e.nombre, e.salario FROM Empleados e

Nombre y salario de cada empleado

ERROR: e no es de tipo empleado,

ya que tiene un atributo oid

SELECT e.nombre, DEREF(e.oid).salario

CONSULTAS



Herencia de tipos

CREATE TYPE rectangulo AS (

x1 FLOAT, y1 FLOAT, x2 FLOAT, y2 FLOAT) NOT FINAL

INSTANCE METHOD area() RETURNS FLOAT; CREATE TYPE cuadrado

UNDER rectangulo (tamano FLOAT) NOT FINAL

OVERRIDING METHOD area() RETURNS FLOAT;

CREATE TABLE rectangulos AS ( id_rect INTEGER PRIMARY KEY, rec rectangulo)

Obten el área de cada cuadrado de la tabla rectángulos

SELECT r.id_rect, r.rec.area

FROM rectangulos

CONSULTAS



Herencia de tablas



Una selección en una supertabla, devuelve los atributos de

esa supertabla para todas las tuplas de esa supertabla y de

todas sus subtablas.

CREATE TABLE EMPLEADOS OF empleado (REF IS id_emp SYSTEM GENERATED,

dep WITH OPTIONS SCOPE DEPARTAMENTOS);

CREATE TABLE ADMINISTRADORES OF administrador UNDER EMPLEADOS;

CREATE TABLE PROGRAMADORES OF programador UNDER EMPLEADOS

(proys WITH OPTIONS SCOPE PROYECTOS);

SELECT *

FROM Empleados

Selecciona todos los empleados, de cualquier tipo

Selecciona el nombre y el lenguaje que domina cada programador

SELECT p.nombre, p.lenguaje

CONSULTAS



Herencia de tablas



Selección de las túplas de un sólo tipo

•

ONLY

SELECT *

FROM ONLY (Empleados)

Selecciona todos los empleados, que no son ni

programadores ni administradores

SELECT *

FROM Empleados

CONSULTAS



Definición de vistas



Posibilidad de definir vistas con tipo



Definición de jerarquías de vistas



Las referencias entre tablas base pueden transformarse en

referencias entre vistas

Vistas para el departamento de ventas

CREATE TYPE empVentas AS ( nombre VARCHAR(50),

salario DECIMAL(6, 2)) NOT FINAL;

CREATE TYPE admVentas UNDER empVentas ( sistema VARCHAR(20)) NOT FINAL;

CREATE TYPE progVentas UNDER empVentas (

lenguaje VARCHAR(20),

proys REF(proyecto) MULTISET ) NOT FINAL;

CREATE TYPE proyVentas AS ( duracion INTEGER,

presup DECIMAL(9, 2),

progs REF(programador) MULTISET) NOT FINAL;

CONSULTAS

Vistas para el departamento de ventas

Vistas

CREATE VIEW vistaEmpVentas OF empVentas

REF IS oid SYSTEM GENERATED AS (SELECT nombre, salario

FROM ONLY (Empleados)

WHERE dep->nombre = 'Ventas');

CREATE VIEW vistaAdmVentas

OF admVentas UNDER vistaEmpVentas AS (SELECT sistema

FROM ONLY(Administradores) WHERE dep->nombre = 'Ventas');

CREATE VIEW vistaProgVentas

OF progVentas UNDER vistaEmpVentas

(proys WITH OPTIONS SCOPE vistaProyVentas) AS (SELECT lenguaje, proys

FROM ONLY(Programadores) WHERE dep->nombre = 'Ventas');

CREATE VIEW vistaProyVentas OF proyVentas REF IS oid SYSTEM GENERATED

(progs WITH OPTIONS SCOPE vistaProgVentas) AS (SELECT duracion, presup, progs

FROM Proyectos