Guía 5. SQL.

1

6.1. Introducción.

Guía 5. SQL.

2

6.2. Lenguaje de Definición de Datos (Data Definition Language –DDL-).

Guía 5. SQL.

3

Guía 5. SQL.

4

Guía 5. SQL.

5

Guía 5. SQL.

6

-------------------------------------------------------------------------------------------------------------------------

Guía 5. SQL.

7

Guía 5. SQL.

8

-------------------------------------------------------------------------------------------------------------------------

Guía 5. SQL.

9

Guía 5. SQL.

10

6.3. Lenguaje de Manipulación de Datos (Data Manipulation Language –DML-).

-------------------------------------------------------------------------------------------------------------------------

Guía 5. SQL.

11

-------------------------------------------------------------------------------------------------------------------------

Guía 5. SQL.

12

-----------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------

Guía 5. SQL.

13

Guía 5. SQL.

14

-----------------------------------------------------------------------------------------------------------------------

Guía 5. SQL.

15

-----------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------

Guía 5. SQL.

16

------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------

Guía 5. SQL.

17

------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------

Guía 5. SQL.

18

6.4. Consultas.

-----------------------------------------------------------------------------------------------------------------------

Guía 5. SQL.

19

-----------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------

Guía 5. SQL.

20

-----------------------------------------------------------------------------------------------------------------------

Guía 5. SQL.

21

-----------------------------------------------------------------------------------------------------------------------

Guía 5. SQL.

22

-----------------------------------------------------------------------------------------------------------------------

Guía 5. SQL.

23

-----------------------------------------------------------------------------------------------------------------------

Guía 5. SQL.

24

-----------------------------------------------------------------------------------------------------------------------

Guía 5. SQL.

25

Guía 5. SQL.

26

-----------------------------------------------------------------------------------------------------------------------

Guía 5. SQL.

27

Guía 5. SQL.

28

-----------------------------------------------------------------------------------------------------------------

Guía 5. SQL.

29

Guía 5. SQL.

30

Guía 5. SQL.

31

Guía 5. SQL.

32

Guía 5. SQL.

33

---------------------------------------------------------------------------------------------------------------------

Ejemplo 1: Obtener, de la tabla inmediatamente anterior, el id del departamento, el nombre y el sueldo del empleado con mayor salario y del empleado con el menor salario (en una sola consulta).

SELECT deptno, ename , sal

FROM EMP

WHERE sal in (select max(sal) from emp) OR sal in (select min(sal) from emp);

deptno ename sal

10 KING 5000

20 SMITH 800

Guía 5. SQL.

34

Ejemplo 2: Obtener, de la tabla inmediatamente anterior, el id del departamento, el nombre y el sueldo del

empleado con mayor salario y del empleado con el menor salario de cada departamento (en una misma

consulta).

SELECT deptno, ename, sal

FROM EMP

WHERE

sal IN (

select max(sal)

from emp

group by deptno

)

OR sal IN (

select min(sal)

from emp

group by deptno

)

ORDER BY deptno, sal DESC;

deptno ename sal

10 KING 5000

10 MILLER 1300

20 SCOTT 3000

20 FORD 3000

20 SMITH 800

30 BLAKE 2850

30 JAMES 950

Guía 5. SQL.

35

Guía 5. SQL.

36

Guía 5. SQL.

37

Guía 5. SQL.

38

Cláusula HAVING con subconsulta

Ejemplo: Consultar los departamentos donde el empleado que menos salario recibe, gana más que el de menor sueldo del departamento 30. Los datos completos son estos:

empno ename job sal deptno

7839 KING PRESIDENT 5000 10

7698 BLAKE MANAGER 2850 30

7782 CLARK MANAGER 2450 10

7566 JONES MANAGER 2975 20

7654 MARTIN SALESMAN 1250 30

7499 ALLEN SALESMAN 1600 30

7844 TURNER SALESMAN 1500 30

7900 JAMES CLERK 950 30

7521 WARD SALESMAN 1250 30

7902 FORD ANALYST 3000 20

7369 SMITH CLERK 800 20

7788 SCOTT ANALYST 3000 20

7876 ADAMS CLERK 1100 20

7934 MILLER CLERK 1300 10

Identifiquemos primero cual es el menor salario del departamento 30

Y ahora se usará como subconsulta y su resultado es el parámetro de comparación para la consulta externa:

Guía 5. SQL.

39

Las vistas sirven para: Restringir el acceso a los datos. Mostrar sólo algunas columnas o filas de una tabla a determinados usuarios. Hacer las consultas complejas más fáciles de usar para los usuarios. Hacer una vista que oculte una consulta que reúne (join) varias tablas.

Presentar diferentes vistas de los mismos datos.

Guía 5. SQL.

40

SINTAXIS PARA LA CREACIÓN DE VISTAS

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW [Esquema]Vista

[ (AliasColumna[, AliasColumna] … ) ]

AS SubConsulta

[WITH CHECK OPTION [CONSTRAINT Nombre] ]

[WITH READ ONLY [CONSTRAINT Nombre] ]

OR REPLACE borra y re-crea la vista si ésta ya existe en la base de datos.

FORCE Crea la vista así las tablas base no existan o el SELECT tenga errores de compilación.

NO FORCE Crea la vista sólo si las tablas base existen y el SELECT no tiene errores de compilación.

[Esquema]Vista Nombre de la vista y el esquema en el que se va a crear, por defecto

el esquema del usuario que ejecuta la sentencia.

AliasColumna

Son los nombres que se le van a asignar a las columnas de la vista.

Debe existir una correspondencia uno a uno entre los Alias y las columnas proyectadas en la cláusula SELECT de la SubConsulta.

SubConsulta: Es una sentencia SELECT con todas las cláusulas conocidas (SELECT,FROM,WHERE,GROUPBY,HAVING,…), pero no se recomienda la cláusula ORDERBY, ésta última se utiliza cuando se consulta la vista.

WITH CHECK OPTION Define que solo las filas que se pueden acceder a través de la vista, pueden ser insertadas o modificadas.

WITH READ ONLY Define que NO se pueden hacer operaciones DML sobre la vista.

Nombre Es el nombre de la restricción que asigna el usuario.

Guía 5. SQL.

41

Guía 5. SQL.

42

Guía 5. SQL.

43

NEGAR OPERACIONES DML SOBRE UNA VISTA

(CLÁUSULA WITH READ ONLY)

Guía 5. SQL.

44

BORRADO DE VISTAS

Guía 5. SQL.

45

VISTAS EN LÍNEA

Es una subconsulta con nombre (alias) que se puede usar dentro de una sentencia SQL No es un objeto del esquema

Guía 5. SQL.

46

6.5. Lenguaje de Control de Datos (Data Control Language –DCL-).

Un Lenguaje de Control de Datos es un lenguaje proporcionado por el Sistema de Gestión de Base de Datos que incluye una serie de comandos SQL que permiten al administrador controlar el acceso a los datos contenidos en la Base de Datos.

GRANT: Permite dar permisos a uno o varios usuarios o roles para realizar tareas determinadas. REVOKE: Permite eliminar permisos que previamente se han concedido con GRANT.

PRIVILEGIOS En general, los privilegios se asignan del siguiente modo: 1) Un usuario que crea una tabla o cualquier otro objeto de la base de datos es el propietario y se le garantizan

automáticamente todos los privilegios apliCables a dicho objeto, con la posibilidad de darles también a otros usuarios dichos privilegios (privilegio de concesión).

2) Un usuario que tenga un privilegio y posea además un privilegio de concesión puede asignarle tal privilegio

a otro usuario y pasarle también el privilegio de concesión. 3) Los privilegios los concede quien tiene el permiso (es decir el propietario del objeto y quien tiene el

privilegio de concesión) mediante la orden GRANT, y los revoca mediante la orden REVOKE. Los privilegios son de dos tipos:

System Privileges (Privilegios del Sistema). Son normalmente otorgados por el Administrador de la Base de Datos a los usuarios. Ejemplos de estos privilegios: CREATE SESSION, CREATE TABLE, CREATE USER, CREATE ROL, etc. Object privileges (Privilegios de Objetos). Son privilegios sobre objetos tales como tablas, vistas,

procedimientos, etc. Estos privilegios son otorgados por el propietario del objeto.

GRANT - Privilegios de objetos

La sintaxis de la orden GRANT es la siguiente:

GRANT privilegios ON objeto TO usuario [ WITH GRANT OPTION ]

Esto asigna al usuario una serie de privilegios sobre el objeto especificado. La privilegios asignables y combinables entre sí son los siguientes:

SELECT Privilegio para acceder a todas las columnas de una tabla o de una vista a través de la sentencia select.

INSERT Adicionar nuevas filas a la tabla con la sentencia insert.

UPDATE[(nombre_columna)] Si se especifica la opción nombre_columna, se trata del privilegio para actualizar el valor en la columna indicada. Si no se coloca, permite actualizar el valor de todas las columnas incluidas.

DELETE Privilegio para eliminar filas de una tabla.

REFERENCES[ (nombre_columna)] Otorga permiso de crear una referencia de llave foránea a la tabla especificada. Si se indican una o más columnas, el permiso es válido sólo en la referencia de llave foránea a las columnas especificadas.

ALTER Otorga permiso para cambiar la definición <esquema> de la tabla.

INDEX Otorga permiso para crear un índice sobre la tabla.

ALL Otorga todos los privilegios sobre la tabla.

Guía 5. SQL.

47

Ejemplos:

GRANT SELECT, INSERT, UPDATE (nombre) ON Persona TO jtimana WITH GRANT OPTION

Le asigna al usuario jtimana los privilegios de SELECT e INSERT sobre todas las columnas de la tabla

Persona y el de UPDATE sobre la columna nombre de dicha tabla. Si está presente la opción WITH GRANT OPTION, se asigna además el privilegio de concesión, que permite al usuario transferir o asignar posteriormente los privilegios que se les han asignado a otros usuarios.

GRANT ALL ON Persona TO jtimana

Otorga todos los privilegios sobre la tabla Persona al usuario indicado.

GRANT SELECT ON EMP TO PUBLIC

Otorga privilegios de consulta sobre la tabla EMP a todos los usuarios de la base de datos.

GRANT UPDATE(CIUNOMBRE), INSERT(CIUID,CIUNOMBRE) ON CIUDAD TO jtimana

Otorga privilegios de actualización solo sobre el nombre de la ciudad y permite insertar sobre las columnas descritas.

GRANT EXECUTE ON PROCEDURE proc TO maria

Otorga privilegios de ejecución del procedimiento proc a María.

REVOKE

Para quitarles los privilegios a los usuarios se usa REVOKE: REVOKE [GRANT OPTION FOR] privilegios ON objeto FROM usuario { RESTRICT | CASCADE }

privilegios, objeto y usuario tienen el mismo significado que las correspondientes opciones de GRANT. La opción GRANT OPTION FOR revoca el privilegio de concesión.

Si se especifica la cláusula RESTRICT, la orden REVOKE puede fallar si el usuario al que se le han revocado los privilegios se los ha concedido posteriormente a otros. Si está presente la cláusula CASCADE, la instrucción se completará siempre con éxito y se revocarán también los privilegios de esos usuarios y de todos aquellos a quienes a su vez se les han concedido (... y así hasta que no haya más privilegios "abandonados", es decir concedidos sin que quien los haya concedido los posea todavía). Se destruirán, además, los objetos de la base de datos construidos gracias a dichos permisos.

PRIVILEGIOS DEL SISTEMA

GRANT

CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SYNONYM,

ALTER TABLE, ALTER VIEW, ALTER PROCEDURE, ALTER SYNONYM,

DROP TABLE, DROP VIEW, DROP PROCEDURE, DROP SYNONYM

TO programador

Guía 5. SQL.

48

ROLES

Un rol es un grupo de privilegios. Un rol es muy útil particularmente en situaciones donde un número de usuarios deben tener el mismo conjunto de privilegios.

Por ejemplo, jtimana, lacosta, jlopez, mmejia son usuarios de la BD. A estos usuarios se desea otorgarles privilegios de consulta y actualización sobre la tabla EMP y privilegios de consulta y eliminación sobre la tabla DEPT.

Para hacer esto, primero creamos un rol con la siguiente sentencia: CREATE ROLE FUNCIONARIOS

Luego otorguemos privilegios a este rol:

GRANT SELECT,UPDATE ON EMP TO FUNCIONARIOS ;

GRANT SELECT,DELETE ON DEPT TO FUNCIONARIOS ;

Ahora, otorgue el rol Funcionarios a los empleados:

GRANT FUNCIONARIOS TO jtimana, lacosta, jlopez, mmejia

Ahora, estos usuarios tienen todos los privilegios otorgados al rol Funcionarios.

Suponga ahora, que tiempo más adelante se requiere otorgar privilegio de eliminación a la tabla EMP. GRANT DELETE ON EMP TO FUNCIONARIOS

De forma automática todos los usuarios de este rol tendrán dicho privilegio.

De igual manera si desea revocar un privilegio:

REVOKE UPDATE ON EMP FROM FUNCIONARIOS

Para borrar un rol: DROP ROLE FUNCIONARIOS;

------------------------------------------------- FIN DEL DOCUMENTO