El lenguaje SQL

www.kybele.es

Índice 1.

2.

INTRODUCCIÓN „

1.1.- ¿Qué es SQL?

„

1.2.- Estado actual y futuro del SQL

SINTÁXIS BÁSICA DEL SQL-92 „

„

„

3. 4. © Belén Vela 2008

2.1.- Lenguaje de Definición de Datos „

2.1.1.- Definición del esquema

„

2.1.2.- Evolución del esquema

2.2.- Lenguaje de Manipulación de Datos „

2.2.1.- Actualizaciones

„

2.2.2.- Consultas

2.3.- Lenguaje de Control de Datos „

2.3.1.- Recuperación y concurrencia

„

2.3.2.- Seguridad y confidencialidad

EJERCICIOS BIBLIOGRAFIA

1. Introducción ¿QUÉ ES SQL? Structured Query Language

• Evolución de SEQUEL de IBM para prototipo System R • LENGUAJE DE: * Definición * Manipulación - Consulta interactiva - Programación * Control • OPERA CON CONJUNTOS DE REGISTROS • LENGUAJE RELACIONAL y DECLARATIVO © Belén Vela 2008

1. Introducción Estado actual y futuro del SQL

SQL:2003, actual estándar: capacidades de Orientación al Objeto (SQL:1999) capacidades multicolecciones y XML

© Belén Vela 2008

1. Introducción SQL:2003 +

•Multisets, BigInt •XML …

SQL:1999 SQL 92

SQL 89 SQL 86-87 Intersección de implementaciones existentes

© Belén Vela 2008

+ IEF Integridad Referencial básica

+ + •Mejor tratamiento de restricciones •Sintaxis de dominios •Tablas temporales •Nuevos tipos de datos •L. manipulac. esquema •Combinación externa •SQL dinámico...

•Disparadores •L. procedimental... •Orientación al Objeto TAD Encapsulamiento Jerarquías Herencia IDO...

2. Sintaxis Básica del SQL-92 La Arquitectura ANSI/X3/SPARC

NIVEL EXTERNO

ESQUEMA EXTERNO 1

ESQUEMA EXTERNO n

Correspondencia Externa/Conceptual NIVEL CONCEPTUAL/ CONCEPTUAL LÓGICO GLOBAL

NIVEL INTERNO

ESQUEMA CONCEPTUAL Correspondencia Conceptual/Interna ESQUEMA INTERNO

DATOS ALMACENADOS © Belén Vela 2008

2. Sintaxis Básica del SQL-92 Lenguaje de Definición de Datos - LDD Nivel lógico global Definición del esquema

Evolución del esquema

CREATE DOMAIN CREATE TABLE CREATE ASSERTION

Nivel físico

CREATE VIEW

CREATE INDEX

DROP VIEW

DROP INDEX

ALTER DOMAIN ALTER TABLE DROP DOMAIN DROP TABLE DROP ASSERTION

© Belén Vela 2008

Nivel externo

2. Sintaxis Básica del SQL-92 Lenguaje de Manipulación de Datos - LMD

Actualizaciones • Altas

INSERT INTO...

• Bajas

DELETE FROM...

• Modificaciones

UPDATE...

Consultas

© Belén Vela 2008

SELECT...

2. Sintaxis Básica del SQL-92 Lenguaje de Control de Datos - LCD

Recuperación y control de concurrencia

COMMIT ROLLBACK

Seguridad y protección GRANT REVOKE

© Belén Vela 2008

2. Sintaxis Básica del SQL-92 EJEMPLO: ALUMNO (num_mat, nombre, ciudad, cod_grupo) Modificación: Cascada Borrado: Puesta a nulos

GRUPO (cod_grupo, curso, turno)

© Belén Vela 2008

2. Sintaxis Básica del SQL-92

2.1 Lenguaje de Definición de Datos Tipos de datos del SQL-92: CHARACTER [VARYING] (n) BIT [VARYING] (n) NUMERIC (p,s) DECIMAL (p,s) INTEGER SMALLINT REAL DOUBLE PRECISION FLOAT (p) DATE TIME TIMESTAMP INTERVAL © Belén Vela 2008

2. Sintaxis Básica del SQL-92

2.1 Lenguaje de Definición de Datos LDD: Definición del esquema Nivel lógico global

DOMINIOS: Sintaxis ::= CREATE DOMAIN [ AS ] [ DEFAULT ] literal, [ ] ::= [ ] < definición de restricción de verificación> [ ] ::= CONSTRAINT ::= CHECK ( ) © Belén Vela 2008

función de valor tiempo o fecha, o bien USER, SYSTEM USER o NULL

2. Sintaxis Básica del SQL-92

2.1 Lenguaje de Definición de Datos LDD: Definición del esquema Nivel lógico global

¡ORACLE no soporta la creación de dominios!

DOMINIOS: • CREATE DOMAIN nomb_valido AS CHAR(20); • CREATE DOMAIN nota_valida INTEGER CHECK (VALUE BETWEEN 0 AND 10); • CREATE DOMAIN turno_valido CHAR(1) DEFAULT ‘M’ CONSTRAINT Mañana_o_Tarde CHECK (VALUE IN (‘M’,’T’)); © Belén Vela 2008

2. Sintaxis Básica del SQL-92

2.1 Lenguaje de Definición de Datos Tiempo de acción de una restricción: Los atributos de restricción, sirven para indicar si la restricción es inmediata o diferida. • Modo de verificación es inmediato: la restricción se verificará al finalizar cada sentencia • Modo de verificación diferido: se verificará al finalizar la transacción. ::= [ [NOT] DEFERRABLE] |[[NOT]DEFERRABLE]

::= INITIALLY DEFERRED | INITIALLY INMEDIATE

NOT NULL

DEPARTAMENTO (num_dep, localidad, …, jefe_dep) NOT NULL

EMPLEADO (cod_emp, nombre,…, departamento) © Belén Vela 2008

2. Sintaxis Básica del SQL-92

2.1 Lenguaje de Definición de Datos Tiempo de acción de una restricción: Los atributos de restricción, sirven para indicar si la restricción es inmediata o diferida. • Modo de verificación es inmediato: la restricción se verificará al finalizar cada sentencia • Modo de verificación diferido: se verificará al finalizar la transacción. ::= [ [NOT] DEFERRABLE] |[[NOT]DEFERRABLE]

::= INITIALLY DEFERRED | INITIALLY INMEDIATE

NOT NULL

DEPARTAMENTO (num_dep, localidad, …, jefe_dep) NOT NULL

EMPLEADO (cod_emp, nombre,…, departamento) © Belén Vela 2008

2. Sintaxis Básica del SQL-92

2.1 Lenguaje de Definición de Datos LDD: Definición del esquema Nivel lógico global

TABLAS: Sintaxis (I) ::= CREATE [TEMPORARY ] TABLE ::= ( [{ } ... ] ) ::= | ::= { | } [ ] [ .... ] ::= [ ] [ ] © Belén Vela 2008

2. Sintaxis Básica del SQL-92

2.1 Lenguaje de Definición de Datos LDD: Definición del esquema Nivel lógico global

TABLAS: Sintaxis (II) ‘0’ AND curso < ‘4’) CHECK (curso > ‘0’ AND curso < ‘4’) ); );

© Belén Vela 2008

2. Sintaxis Básica del SQL-92

2.1 Lenguaje de Definición de Datos LDD: Definición del esquema Nivel lógico global

CREATE TABLE alumno ( num_mat CHAR(3), nombre NOMB_VALIDO UNIQUE, ciudad CHAR(25) NOT NULL, cod_grupo CHAR(3), PRIMARY KEY (num_mat), FOREIGN KEY (cod_grupo) REFERENCES grupo ON UPDATE CASCADE ON DELETE SET NULL);

© Belén Vela 2008

2. Sintaxis Básica del SQL-92

2.1 Lenguaje de Definición de Datos LDD: Definición del esquema Nivel lógico global

INTEGRIDAD REFERENCIAL

ON UPDATE CASCADE SET NULL SET DEFAULT NO ACTION © Belén Vela 2008

ON DELETE CASCADE SET NULL SET DEFAULT NO ACTION

Por defecto: borrado y actualización restringidos ¡ORACLE no soporta ON UPDATE ni ON DELETE SET DEFAULT!

2. Sintaxis Básica del SQL-92

2.1 Lenguaje de Definición de Datos LDD: Definición del esquema Nivel lógico global

ESQUEMA: Alumno num_mat

nombre

ciudad

Grupo cod_grupo

© Belén Vela 2008

curso

turno

cod_grupo

2. Sintaxis Básica del SQL-92

2.1 Lenguaje de Definición de Datos LDD: Definición del esquema Nivel lógico global

Para ver la estructura de una tabla en ORACLE: Ö DESC nombre_tabla;

SQL> desc alumno; Nombre ¿Nulo? Tipo ----------------------------------------- -------- ---------------------------NUM_MAT NOT NULL CHAR(3) NOMBRE VARCHAR2(20) CIUDAD NOT NULL CHAR(25) COD_GRUPO CHAR(3) SQL> desc grupo; Nombre ¿Nulo? Tipo ----------------------------------------- -------- ---------------------------COD_GRUPO NOT NULL CHAR(3) CURSO NOT NULL CHAR(1) TURNO CHAR(1) © Belén Vela 2008

2. Sintaxis Básica del SQL-92

2.1 Lenguaje de Definición de Datos LDD: Definición del esquema Nivel lógico global

¡ORACLE no soporta la creación de aserciones!

ASERCIONES: Todos los alumnos de Madrid tienen que estar matriculados en el turno de tarde. CREATE ASSERTION madrid_tarde CHECK (NOT EXISTS (SELECT * FROM ALUMNO, GRUPO WHERE alumno.cod_grupo=grupo.cod_grupo AND alumno.ciudad=‘MADRID’ AND grupo.turno‘T’)); © Belén Vela 2008

2. Sintaxis Básica del SQL-92

2.1 Lenguaje de Definición de Datos LDD: Definición del esquema Nivel externo

VISTAS: CREATE VIEW nombre_de_vista [(lista de columnas)] AS

CREATE VIEW alumnos_madrid AS (SELECT * FROM alumno WHERE ciudad=‘Madrid’);

© Belén Vela 2008

2. Sintaxis Básica del SQL-92

2.1 Lenguaje de Definición de Datos LDD: Definición del esquema Nivel físico

ÍNDICES: CREATE INDEX ind_alumno ON alumno (ciudad, cod_grupo); o CREATE UNIQUE INDEX ind_alumno ON alumno (ciudad, cod_grupo);

© Belén Vela 2008

2. Sintaxis Básica del SQL-92

2.1 Lenguaje de Definición de Datos LDD: Evolución del esquema Nivel lógico global

Borrar el nombre de los alumnos: ALTER TABLE alumno DROP COLUMN nombre; Añadir el precio que cada alumno paga al matricularse: ALTER TABLE alumno ADD (precio INTEGER);

DROP DOMAIN turno_valido; DROP TABLE alumno; DROP ASSERTION ciudad_turno; © Belén Vela 2008

2. Sintaxis Básica del SQL-92

2.1 Lenguaje de Definición de Datos LDD: Evolución del esquema Nivel externo

DROP VIEW alumnos_madrid;

LDD: Evolución del esquema Nivel físico

DROP INDEX ind_alumno;

© Belén Vela 2008

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Actualizaciones Altas

INSERT INTO [(lista_columnas)] VALUES (lista_columnas_inserción); INSERT INTO grupo VALUES (‘I11’, ‘1’, DEFAULT); Ö Todas las columnas de la tabla y en el mismo orden

INSERT INTO grupo (curso, cod_grupo) VALUES (‘2’,‘I12’); Ö Clave primaria tiene que estar incluida

GRUPO cod_grupo

© Belén Vela 2008

curso

turno

I11

1

M

I12

2

M

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Actualizaciones Altas

INSERT INTO Grupo_m SELECT * FROM Grupo WHERE turno='M'; GRUPO_M cod_grupo

© Belén Vela 2008

curso

turno

I11

1

M

I12

2

M

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Actualizaciones Altas

INSERT INTO Grupo_Curso1 (Codigo, Turno) SELECT cod_grupo, turno FROM Grupo WHERE curso=‘1’; GRUPO_Curso Codigo I11

© Belén Vela 2008

turno M

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Actualizaciones Altas

Alumno: num_mat 1 3 8 2 5

Grupo:

© Belén Vela 2008

nombre

ciudad

Juan Ana María Pedro Salomé

Madrid Leganés Leganés Getafe Madrid

cod_grupo I11 I12 I13 I21 I22 I31

cod_grupo I11 I21 I22 I21 I21

curso

turno

1 2 3 1 2 3

M M M T T T

precio 25000 80000 30000 20000 25000

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Actualizaciones Bajas

DELETE FROM grupo; cod_grupo

curso

turno

DELETE FROM grupo WHERE curso=‘1’; cod_grupo

© Belén Vela 2008

I12 I13 I22 I23

curso

turno

2 3 2 3

M M T T

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Actualizaciones Modificaciones

UPDATE alumno SET cod_grupo=‘I22’ WHERE nombre>‘Pérez’;

num_mat

nombre

ciudad

1 3 8 2 5

Juan Ana María Pedro Salomé

Madrid Leganés Leganés Getafe Madrid

© Belén Vela 2008

cod_grupo I11 I21 I22 I21 I22 I21

precio 25000 80000 30000 20000 25000

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Consultas

SELECT [ALL|DISTINCT] {lista-atributos | *} FROM nombre_tabla [, nombre_tabla, …] [WHERE condición] [cláusula GROUP BY] [cláusula HAVING] [cláusula ORDER BY];

© Belén Vela 2008

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Consultas

De qué tabla/s se selecciona la información

Todos los atributos de la tabla en el orden de su creación

SELECT * FROM alumno;

Ö Todos los atributos de la tabla ALUMNO (todas las tuplas).

num_mat

nombre

ciudad

1 3 8 2 5

Juan Ana María Pedro Salomé

Madrid Leganés Leganés Getafe Madrid

© Belén Vela 2008

cod_grupo I11 I21 I22 I21 I22

precio 25000 80000 30000 20000 25000

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Consultas

Proyección SELECT nombre, ciudad FROM alumno Ö Selección de los atributos nombre y ciudad de la tabla ALUMNO.

© Belén Vela 2008

nombre

ciudad

Juan Ana María Pedro Salomé

Madrid Leganés Leganés Getafe Madrid

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Consultas

© Belén Vela 2008

ALL: valor que se asume por omisión

DISTINCT: Eliminación de duplicados

SELECT ciudad FROM alumno;

SELECT DISTINCT ciudad FROM alumno;

ciudad

ciudad

Madrid Leganés Leganés Getafe Madrid

Getafe Leganés Madrid

Proyección????

Proyección!!!

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Consultas Por omisión: ASC

Valores nulos los más altos

ORDER BY {lista_columnas | lista_posiciones} [DESC|ASC ]

Columna/s debe/n estar incluida/s en la cláusula SELECT

© Belén Vela 2008

Posición: nº de orden de la columna dentro de la lista de columnas seleccionadas (izda a dcha)

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Consultas

SELECT ciudad FROM alumno ORDER BY ciudad ASC;

“ciudad” debe estar

incluida en la cláusula SELECT

© Belén Vela 2008

ciudad Getafe Leganés Leganés Madrid Madrid

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Consultas

1

2

SELECT nombre, precio*0.10 FROM alumno ORDER BY 2, nombre;

Ordenación por una columna calculada y por nombre

© Belén Vela 2008

nombre PEDRO JUAN SALOME MARIA ANA

precio*0.10 2000 2500 2500 3000 8000

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Consultas

SELECT * FROM alumno WHERE cod_grupo=‘I21’;

Selección

Condición: combinación de una o más expresiones (usando operadores lógicos) que da como resultado: CIERTO, FALSO o DESCONOCIDO

num_mat

nombre

ciudad

3 2

Ana Pedro

Leganés Getafe

© Belén Vela 2008

cod_grupo I21 I21

precio 80000 20000

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Consultas

Predicados OPERADORES DE COMPARACIÓN: • igual (“=“), distinto (““), menor que (““), menor o igual a (“=“)

(ÖNo hay espacio entre los símbolos)

OPERADORES ARITMÉTICOS: • Suma (“+“), resta (“-“), multiplicación (“*“) y división (“/“) •OPERADORES LÓGICOS: • AND, OR y NOT

Primero condiciones con AND y luego OR

SELECT * FROM Alumno WHERE precio >20000 AND (ciudad = ‘MADRID’ OR ciudad=‘GETAFE’); SELECT * FROM Alumno WHERE precio >20000 AND ciudad = ‘MADRID’ OR ciudad=‘GETAFE’; 1º

•VALORES NULOS (desconocido): • IS [NOT] NULL

SELECT * FROM Alumno WHERE ciudad IS NULL; SELECT * FROM Alumno WHERE cod_grupo IS NOT NULL;

© Belén Vela 2008

2º

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Consultas RANGO DE VALORES: • BETWEEN … AND

SELECT * FROM Alumno WHERE precio BETWEEN 20000 AND 25000; SELECT * FROM Alumno WHERE precio NOT BETWEEN 20000 AND 25000; ~ SELECT * FROM Alumno WHERE NOT (precio BETWEEN 20000 AND 25000); OPERADOR LIKE: Se emplea para comparar el contenido de una columna con una serie de caracteres. Caracteres comodín • Subrayado (_): sustituye a un carácter en la misma posición • Tanto por ciento (%): sustituye a n caracteres, donde n puede ser 0.

SELECT * FROM Alumno WHERE nombre LIKE ‘_E%O’: OPERADOR IN: Permite comprobar si un valor pertenece a un conjunto de valores determinados. • expresión IN (lista de valores)

SELECT * FROM Alumno WHERE ciudad IN (‘BARCELONA’,’MADRID’): ~ SELECT * FROM ALUMNO WHERE ciudad =‘BARCELONA’ OR ciudad=‘MADRID’; SELECT * FROM Alumno WHERE ciudad NOT IN (‘BARCELONA’,’MADRID’): © Belén Vela 2008

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Consultas

Ejemplos: SELECT * FROM Documento WHERE Titulo LIKE '%DB%';

TIPO COD DOC

A

L L

002 004

TITULO

IDIOMA

NOMBRE

AÑO

ISBN

INTRODUCTION TO DBS RELATIONAL DB

I I

ADDISON-WESLEY ADDISON-WESLEY

1995 1995

0-201-54329-X 0-201-55483-X

SELECT * FROM Documento WHERE Isbn IS NULL;

© Belén Vela 2008

SELECT Titulo, Idioma, Nombre_E, Año FROM Documento WHERE Año BETWEEN 1976 AND 1993;

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Consultas

Funciones de Agrupación

Cuenta líneas incluyendo NULOS

SELECT COUNT(*) FROM alumno WHERE precio=25000

Máximo

SELECT MAX(precio) FROM alumno

Mínimo

SELECT MIN(precio) FROM alumno

Promedio

SELECT AVG(precio) * 0.1 FROM alumno

Suma

SELECT SUM(precio) FROM alumno

© Belén Vela 2008

COUNT(*) 2 MAX(precio) 80000 MIN(precio) 20000 AVG(precio)*0.1 3600 SUM(precio) 180000

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Actualizaciones Altas

Alumno: num_mat 1 3 8 2 5

Grupo:

© Belén Vela 2008

nombre

ciudad

Juan Ana María Pedro Salomé

Madrid Leganés Leganés Getafe Madrid

cod_grupo I11 I12 I13 I21 I22 I31

cod_grupo I11 I21 I22 I21 I21

curso

turno

1 2 3 1 2 3

M M M T T T

precio 25000 80000 30000 20000 25000

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Consultas

Cláusulas de Agrupación

SELECT ciudad, AVG(precio) FROM alumno GROUP BY ciudad

SELECT ciudad, AVG(precio) FROM alumno WHERE num_mat ANY (SELECT precio FROM ALUMNO WHERE ciudad=‘MADRID’):

© Belén Vela 2008

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Consultas

Predicados PREDICADO EXISTS: La condición EXISTS será CIERTA si la subconsulta devuelve una fila que satisfaga las condiciones impuestas en la cláusula WHERE (se puede expresar con el operador IN).

ÖObtener los alumnos que estén matriculados en algún grupo. SELECT * FROM Alumno a WHERE EXISTS (SELECT * FROM GRUPO g WHERE a.cod_grupo=g.cod_grupo); OTROS PREDICADOS: • Unique • Overlaps • Match

© Belén Vela 2008

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Consultas

CREATE VIEW alumnos_madrid AS (SELECT * FROM alumno WHERE ciudad=‘Madrid’);

Ejemplo de vistas SELECT * FROM alumnos_madrid;

Alumnos_madrid num_mat

nombre

1 5

Juan Salomé

© Belén Vela 2008

ciudad Madrid Madrid

cod_grupo I11 I22

precio 25000 25000

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos Sección de Declaración de Variables

SQL Embebido Cuerpo

Sección de Tratamiento de Excepciones © Belén Vela 2008

Sentencia del lenguaje anfitrión EXEC SQL BEGIN DECLARE SECTION; Año Date; ISBN Char(12); ... EXEC SQL END DECLARE SECTION; Sentencia del lenguaje anfitrión Sentencia del lenguaje anfitrión EXEC SQL SELECT Año INTO :Año FROM DOCUMENTO WHERE ISBN = :ISBN; IF SQLSTATE = ... ... Sentencia del lenguaje anfitrión Sentencia del lenguaje anfitrión WHENEVER SQLERROR | NOT FOUND ... CONTINUE | GOTO ... Sentencia del lenguaje anfitrión

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Consultas

Declaración de Cursor

DECLARE cursor_e CURSOR FOR SELECT Código, Nombre, Dir, Ciudad FROM Editorial ORDER BY Ciudad DESC;

© Belén Vela 2008

2. Sintaxis Básica del SQL-92

2.2 Lenguaje de Manipulación de Datos LMD: Consultas

SQL Dinámico

SELECT Nombre, Dirección FROM Editorial WHERE Ciudad = ?;

© Belén Vela 2008

2. Sintaxis Básica del SQL-92

2.3 Lenguaje de Control de Datos Lenguaje de Control de Datos

Recuperación y control de concurrencia

COMMIT ROLLBACK

Seguridad y protección GRANT REVOKE

© Belén Vela 2008

3. Ejercicios 1. Dada la siguiente relación:

PERSONA (DNI, nombre, ciudad, salario, profesión, fecha_nac, jubilado) a) Crear el dominio Tipo_Jubilado. Puede tomar los valores ‘SI’, ‘NO’ y por defecto tomará el valor ‘NO’. b) Crear la tabla PERSONA, siendo el atributo DNI la clave primaria, el atributo nombre una clave alternativa y el atributo jubilado tomará los valores del dominio Tipo_Jubilado previamente creado. c) Insertar las siguientes tuplas en la tabla: DNI

© Belén Vela 2008

nombre

ciudad

salario

profesión

fecha-nac

jubilado

11111111

Juan

Madrid

1500

informático

01/05/1972

NO

22222222

María

Barcelona

1800

informático

05/06/1980

NO

33333333

Pedro

Valencia

1200

administrativo

31/05/1940

SI

44444444

Isabel

Madrid

1600

informático

22/03/1974

NO

55555555

Antonio

Barcelona

1000

administrativo

15/08/1935

SI

3. Ejercicios Realizar las siguientes operaciones de manipulación: d) Incrementar en un 5% el salario de los informáticos. e) Decrementar en un 2% el salario de los que viven en Madrid. f) Modificar el nombre de la persona con DNI=‘55555555’ por ‘Juan Antonio’. g) Todos los jubilados pasan a ganar 100 Euros más. h) Borrar todas las personas de Madrid que hayan nacido antes del 1940.

© Belén Vela 2008

3. Ejercicios Realizar las siguientes consultas: i)

Obtener todas las personas ordenadas descendentemente por nombre.

j)

Obtener el sueldo medio de las personas de Madrid agrupadas por profesiones.

k) Obtener el sueldo medio de las personas de Madrid agrupadas por profesiones siempre que el sueldo medio por profesión supere los 1000 euros y ordenado por profesión.

© Belén Vela 2008

3. Ejercicios 2. Dado el siguiente esquema relacional:

EMPLEADO (NSS, Nombre, Apellidos, NSS_Supervisor, Fecha_Nac, Dirección Sexo, Núm_Dep, Salario) PROYECTO (Cod_P, Nombre_P, Lugar_P, Núm_Dep) DEPARTAMENTO (Número_D, Nombre_D, NSS_Gerente) FAMILIAR (NSSE, Nombre, Sexo, Parentesco) TRABAJA_EN (NSS_E, Cod_Proy, Horas)

© Belén Vela 2008

3. Ejercicios 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. © Belén Vela 2008 16.

Obtener todas las combinaciones de empleados y departamentos. Obtener todas las combinaciones de NSS de empleados y nombres de departamento. Seleccionar el salario de cada uno de los empleados. Seleccionar los distintos salarios que cobran los empleados. Obtener todos los empleados del departamento 5. Seleccionar los empleados del departamento de ‘Investigación’ y la información de este departamento. Nombre y apellidos de los empleados de Móstoles. Nombre y apellidos de los empleados que vivan en una ciudad que empiece por ‘M’ y termine por ‘les’. Nombre del departamento, nombre y apellidos de los empleados ordenados descendentemente por nombre de departamento y ascendentemente por apellidos. NSS de los empleados del departamento 1, 2 o 3. Nombre y apellidos de los empleados que tengan un salario desconocido. Nombre y apellidos de los empleados junto con el nombre y apellidos de su supervisor. Obtener el total de los salarios, el salario máximo, el salario mínimo y la media del salario de todos los empleados. Obtener el total de los salarios, el salario máximo, el salario mínimo y la media del salario de los empleados del departamento de ‘Investigación’. Número de empleados que tiene el departamento de ‘Investigación’. Número de salarios diferentes en la empresa.

3. Ejercicios 17. 18. 19. 20. 21. 22. 23. 24.

© Belén Vela 2008

Seleccionar los NSS de los empleados que trabajan en el mismo proyecto y con las mismas horas de trabajo que el empleado con NSS=‘28/342139’. NSS, nombre y apellidos de los empleados que no tienen familiares dependientes de él. Nombre y apellidos de los empleados que tengan 2 o más familiares a su cargo. Por cada departamento que tenga más de 5 empleados, obtener el nombre de departamento y el número de empleados que ganan más de 3000 euros. Nombre y apellidos de los empleados que tengan un salario mayor que cualquiera de los empleados del departamento 5. Nombre y apellidos de los empleados que tienen familiares que tienen el mismo nombre y sexo que él. (select anidada, combinación y exists) Nombre de todos los empleados que trabajan en todos los proyectos del departamento 5. Lista de todos los códigos de los proyectos en los que participa un empleado que se apellide Silvano bien como trabajador o como gerente del departamento al que pertenece el proyecto.

4. Bibliografía 9 Tecnología y Diseño de Bases de Datos M.Piattini, E. Marcos, C.Calero y B. Vela Ed.: RA-MA, 2006 Octubre Parte II, capítulo 8 9 Fundamentos y Modelos de Bases de Datos A. de Miguel y M. Piattini Ed.: RA-MA, 1997 Capítulo 7 (Pág. 215-265) y Apéndice A (Pág. 453-468) 9 Introducción

al SQL para Usuarios y Programadores Rivero, E. et al. Ed.: Thomson, Segunda Edición, 2003

9

Diseño de Bases de Datos. Problemas Resueltos. A. de Miguel et al. Ed.: RA-MA, 2001

9hhttp://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96540/toc.htm © Belén Vela 2008