DDL y DML
DDL y DML
DDL y DML
OBJETIVOS
• Repasar las sentencias que forman el lenguaje de definición de datos (DDL Data
Definition Language) de SQL, es decir, el subconjunto de órdenes que nos permitirán
crear o editar tablas de la base de datos.
• Repasar el lenguaje de consultas para el acceso a las tablas de la BD.
MATERIAL:
TECNOLOGIA ORACLE
TEMAS
• Definición de Datos
• Creación de Tablas
• Restricciones de Columnas
• Restricciones de Tablas
• Renombrar Tablas
• Eliminar Tablas
• Modificar Tablas
• Crear y Borrar Sinónimos
• Crear Dominios
• Consultas I
• Select
• From
• Where
• Diccionario de Datos
SQL
Tipos de datos:
Oracle maneja tipos de datos para manejar desde números hasta objetos como fotos, archivos,
cada dato se debe especificar de una manera especial:
− Los valores alfanuméricos van encerrados entre comilla simple: 'Alfanumérico'
− Los valores numéricos son número simples: 123
− Las fechas van encerradas entre comillas simples: '1/12/2000'
− Los valores binarios no pueden ser representados (son fotos, videos…)
Los diferentes tipos de datos que maneja Oracle son los siguientes:
Si se intenta introducir un valor demasiado grande para el campo, se intentará eliminar los
espacios finales, y si cabe sin espacios, se introduce. Si aún así no cabe, se retorna un error.
Mantisa x 10exponente
La mantisa (parte de una representación en punto flotante que contiene los dígitos significativos
del número a representar) puede contener cualquier número, entero o decimal, positivo o
negativo. El exponente podrá contener cualquier número entero, positivo o negativo. El
parámetro “p” indica la precisión (número de dígitos contando los decimales) que contendrá el
número como máximo. Oracle garantiza los datos con precisiones de 1 a 38. El parámetro “s”
indica la escala, esto es, el máximo de dígitos decimales. Hay que tener en cuenta que una
columna definida NUMBER(10,5), podrá contener como máximo cualquier número siempre y
cuando el número de dígitos enteros más el número de dígitos decimales no supere 10 (y no 15).
La escala puede ir de -84 a 127. Para definir número enteros, se puede omitir el parámetro s o
bien poner un 0 en su lugar. Se puede especificar una escala negativa, esto lo que hace es
redondear el número indicado a las posiciones indicadas en la escala. Por ejemplo un número
definido como NUMBER(5,-2), redondeará siempre a centenas. Así si intentamos introducir el
valor 1355, en realidad se almacenará 1400.
Esto es: Dos dígitos para el día Las tres primeras siglas del año (depende del idioma instalado).
Cuatro dígitos para el año.
Internamente un fecha se almacena como el número de días desde cierto punto de inicio (por
ejemplo el año 0). Esto permite que las fechas puedan ser tratadas en operaciones aritméticas
normales:
Siempre que queramos obtener una fila de la firma más rápida posible, debemos hacerlo a través
de su ROWID. Un uso típico suele ser obtener un listado de ROWIDs con un SELECT, y después
acceder a cada una de las filas directamente con la condición del ROWID.
Create Table
Crea una tabla en la base de datos, sintaxis General de la sentencia CREATE TABLE:
Restricciones de columnas:
Restricciones de tablas:
PRIMARY KEY (columna1, columna2...): Permite indicar las columnas que forman la clave
primaria.
FOREIGN KEY (columna1, columna2....) REFERENCES NombreTabla: Indica las columnas que son
clave ajena referenciando a una clave candidata de otra tabla.
UNIQUE (columna1, columna2...): El valor combinado de una o varias columnas es único.
CHECK (condición): Permite indicar una condición que deben cumplir las filas de la tabla. Puede
afectar a varias columnas.
EJEMPLOS
areas(codigo, nombre, departamento) (código es la clave primaria)
departamentos(codigo_dpto, nombre) (código_dpto es la clave primaria)
ON DELETE Set Null Significa que si se borra algún departamento de la tabla departamentos el
campo departamento de las filas de la tabla areas que le reverenciaban se pone como Null.
ON UPDATE CASCADE Significa que si se modifica el código_dpto de una fila de la tabla
departamento, también se modificara en las filas de la tabla áreas que le referencian.
EJEMPLOS
Agregar a la tabla areas el campo Responsable de tipo char(30)
alter table areas ADD responsable char(30) not null;
Modificar el campo nombre de la tabla departamentos a char(50)
alter table departamentos MODIFY nombre char(50);
Utilizando oracle 10g crear las tablas correspondientes al siguiente esquema de base de dato
relacional relativo a la gestión de los préstamos de una biblioteca:
Libro (LI_id, autor, titulo, editor, clase, precio)
Usuario (carnet, nombre, direccion)
Clase (CL_id, tiempo_de_prestamo)
Prestamo (numero, carnet, fecha_inicio, fecha_fin)
1. Cree las tablas del esquema anterior, ubicalas en un tablespace previamente definido por
usted, tenga en cuenta que puede crear más de un tablespace si quiere, en todo caso
justifique el por qué de la creación y la ubicación de las tablas.
El DDL (Data Definition Language) es el conjunto de sentencias que está orientadas a la creación,
modificación y configuración de objetos en base de datos.
El DDL es el subconjunto más extenso dentro de SQL así que sólo vamos a hacer una referencia
rápida a algunas sentencias.
Ventas
Referencia Codigo_Cliente Codigo_Ciudad Total
1 1 12 50000,12
2 2 11 34343,00
3 1 10 23444,12
4 3 11 2372382,2
5 4 13 3232323,01
6 2 12 234000,00
Figura 2 Tabla Ejemplo de ventas
Instrucción INSERT
La sentencia INSERT nos permite introducir nuevas filas en una tabla de base de datos.
La sintaxis básica es:
INSERT INTO tabla[( campos )]
VALUES( lista de valores );
Los nombres de los campos son opcionales y si no se ponen se supondrá que se agregarán valores
a todos los campos de la tabla en su orden original,si se ponen, se podrán indicar cualquier
número de columnas, en cualquier orden.
La lista de valores es el registro que se insertará en la tabla. Los tipos de datos deben coincidir con
los campos indicados o con la definición de la tabla si omitimos el nombre de los atributos. Las
columnas que no se incluyan se inicializarán con NULL, (si no se ha definido valor en el DEFAULT).
Ejemplo:
INSERT INTO FACTURA
VALUES('A111', 'Factura nueva', 1, 5, 50000);
Este tipo de INSERT permite introducir un gran número de registros en una sola sentencia, al igual
que con el INSERT normal, los tipos de datos del SELECT deben coincidir con los de los campos
indicados en la sentencia.
Ejemplo:
INSERT INTO FACTURA(C_PAIS, C_CLIENTE)
(SELECT C_PAIS, C_CLIENTE FROM CLIENTE);
Instrucción DELETE
La sentencia DELETE nos permite eliminar nuevas filas en una tabla de base de datos conforme a
una condición, es equivalente al SELECT, pero en vez de mostrar las filas que cumplan la condición,
las elimina.
Su sintaxis es:
DELETE {FROM} tabla
{WHERE condición};
Si se omite la cláusula WHERE se borrarán todas las filas de la tabla, las condiciones pueden ser las
mismas que las aplicadas en una sentencia SELECT.
Instrucción SELECT
La sentencia SELECT es la encargada de la recuperación (selección) de datos, con cualquier tipo de
condición, agrupación u ordenación.
Una sentencia SELECT retorna un único conjunto de resultados, por lo que podrá ser aplicada en
cualquier lugar donde se espere un conjunto de resultados.
Clausulas en el Select: Estas se escriben después del Select y antes del from
Codigo_Cliente
1
2
1
3
4
2
*: indica que muestre todos los campos de las tablas o resultados del from, este no puede ir
acompañado de ningún nombre de campo.
Ejemplo: Select * from ventas;
Ventas
Referencia Codigo_Cliente Codigo_Ciudad Total
1 1 12 50000,12
2 2 11 34000,00
3 1 10 23500,12
4 3 11 23750000,2
5 4 13 3200323,01
6 2 12 234000,00
Codigo_Cliente
1
2
3
4
NOTAS:
- Las columnas ambiguas se preceden del nombre de la tabla: <tabla>.<columna>
- * refiere a todas las columnas de todas las tablas.
Cláusula WHERE.
Sirve para indicar la condición que deben cumplir las filas resultantes.
WHERE <condición>
Una condición está formada por una o varias expresiones condicionales conectadas por los
operadores lógicos AND, OR y NOT. Una expresión condicional tiene una de las formas indicadas
en la figura 3.
Ejemplo:
SELECT codigo_cliente
FROM ventas
WHERE total > 40000 and total < 300000
Codigo_Cliente
1
2
Figura 3. Operadores lógicos
CLAUSULA ORDER BY: Esta clausula se utiliza para ordenar el resultado de una consulta según la o
las columnas seleccionadas, esta clausula se escribe al final de la consulta.
Sintaxis: … ORDER BY CAMPO1 [DESC|ASC][,CAMPON [DESC|ASC]]. (valor por defecto es ASC)
Si se desea ordenar más de un campo, estos se separan por coma, cuando esto ocurre el resultado
de la consulta ordena teniendo en cuenta el primer campo indicado, luego sin desorganizar este
campo, ordena el siguiente, y así hasta llegar al ultimo atributo.
Ejemplo: Mostrar los datos de las ventas, ordenando por cliente de forma descendente y por el
total de la factura en forma ascendente.
SELECT *
FROM ventas
ORDER BY codigo_cliente DESC, total ASC;
Ventas
Referencia Codigo_Cliente Codigo_Ciudad Total
5 4 13 3200323,01
4 3 11 23750000,2
2 2 11 34000,00
6 2 12 234000,00
3 1 10 23500,12
1 1 12 50000,12
En este ejemplo se observa que primero se ordena de forma descendente el codigo_cliente, y
después se ordena el total de forma descendente, sin desordenar el codigo_cliente.
CLAUSULA GROUP BY: Una consulta con GROUP BY se utiliza para considerar los registros cuyos
ciertos campos tienen el mismo valor, y procesarlos de la misma manera, para contarlos,
sumarlos, hacer la media…, esto quiere que los valores iguales se agrupan en uno solo, por
ejemplo para calcular el total comprado por cada cliente, se deben tomar los diferentes valores
del campo codigo_cliente y mostrar un valor.
Las funciones más comunes que se pueden utilizar son:
Max(campo): calcula el máximo valor almacenado en el campo indicado, con respecto al grupo
descrito.
Min(campo): calcula el mínimo valor almacenado en el campo indicado, con respecto al grupo
descrito.
Sum(campo): Suma los valores almacenados en el campo indicado, que pertenezcan al grupo
especificado.
Avg(campo): calcula el valor medio o promedio de los valores almacenados en el campo indicado,
que pertenezcan al grupo especificado.
la función que se desee calcular se debe escribir en el SELECT junto con los campos que
conformarán el grupo (de necesitarse), en la clausula GROUP BY que va después del FROM O DEL
WHERE (de haber condición) se especifica el o los atributos que generan el grupo.
Sintaxis:
SELECT [campos|funcion]
FROM tabla
[WHERE condicion]
GROUP BY campos_grupo;
• Calcular el total vendido (la función es el único campo a mostrar, no hay grupos)
SELECT sum(total)
FROM ventas;
Sum(total)
73500,24
268000,00
23750000,2
3200323,01
27291823,45
Ejemplo: Mostrar el total comprado por cada cliente, pero que superen un 1000000 de pesos. (el
grupo lo forma el codigo_cliente)
SELECT codigo_cliente, sum(total)
FROM ventas
GROUP BY codigo_cliente;
Codigo_Cliente Sum(total)
3 23750000,2
4 3200323,01
CONSULTAS CON COMBINACION ENTRE TABLAS (JOIN)
Todas las explicaciones que están a continuación utilizan las siguientes dos tablas para ilustrar el
efecto de diferentes clases de uniones JOIN.
Tabla Empleado
Apellido IDDepartamento
Rafferty 31 31
Jordán 33
Steinberg 33
Róbinson 34
Smith 34
Gaspar 36
Tabla Departamento
NombreDepartamento IDDepartamento
Ventas 31
Ingeniería 33
Producción 34
Marketing 35
La tabla Empleado contiene a los empleados con el número del departamento al que pertenecen;
mientras que la tabla Departamento, contiene el nombre de los departamentos de la empresa, se
puede notar que existe un empleado que tiene asignado un número de departamento que no se
encuentra en la tabla Departamento (Gaspar), igualmente, en la tabla Departamento existe un
departamento al cual no pertenece empleado alguno (Marketing). Esto servirá para presentar
algunos ejemplos más adelante.
SQL especifica dos formas diferentes para expresar estas combinaciones. La primera, conocida
como explícita usa la palabra JOIN, mientras que la segunda es implícita y usa ',' para separar las
tablas a combinar en la sentencia FROM de la declaración SELECT. Entonces siempre se genera el
producto cruzado del cual se seleccionan las combinaciones que cumplan lo que indica la
sentencia WHERE.
Es necesario tener especial cuidado cuando se combinan columnas con valores nulos NULL ya que
el valor nulo no se combina con otro valor o con otro nulo, excepto cuando se le agregan
predicados tales como IS NULL o IS NOT NULL.
Como ejemplo, la siguiente consulta toma todos los registros de la tabla Empleado y encuentra
todas las combinaciones en la tabla Departamento. La sentencia JOIN compara los valores en la
columna IDDepartamento en ambas tablas. Cuando no existe esta correspondencia entre algunas
combinaciones, éstas no se muestran; es decir que si el número de departamento de un empleado
no coincide con los números de departamento de la tabla Departamento, no se mostrará el
empleado con su respectivo departamento en la tabla resultante.
Las dos consultas siguientes son similares, y se realizan de manera explicita (A) e implícita (B).
SELECT *
FROM empleado
INNER JOIN departamento
ON empleado.IDdepartamento = departamento.IDdepartamento
SELECT *
FROM empleado, departamento
WHERE empleado.IDdepartamento = departamento.IDDepartamento
Resultado
Empleado.Apellido Empleado.IDdepartamento departamento.NombreDepartamento departamento.IDDepartamento
Smith 34 Producción 34
Jordán 33 Ingeniería 33
Róbinson 34 Producción 34
Steinberg 33 Ingeniería 33
Rafferty 31 Ventas 31
A la combinación que utiliza comparaciones dentro del predicado JOIN se le llama theta-join.
SELECT *
FROM empleado
INNER JOIN departamento
ON empleado.IDDepartamento < departamento.IDDepartamento
SELECT *
FROM empleado NATURAL JOIN departamento
El uso de esta la sentencia NATURAL puede producir resultados ambiguos y generar problemas si
la base de datos cambia, porque al añadir, quitar, o renombrar las columnas, puede perder el
sentido la sentencia; por esta razón es preferible expresar el predicado usando las otras
expresiones nombradas anteriormente (ejemplos A y B).
Presenta el producto cartesiano de todos los registros de las dos tablas, el código SQL para realizar
este producto cartesiano enuncia las tablas que serán combinadas, pero no incluye algún
predicado que filtre el resultado.
SELECT *
FROM empleado CROSS JOIN departamento
SELECT *
FROM empleado, departamento;
Rafferty 31 Ventas 31
Jordán 33 Ventas 31
Steinberg 33 Ventas 31
Smith 34 Ventas 31
Róbinson 34 Ventas 31
Gaspar 36 Ventas 31
Rafferty 31 Ingeniería 33
Jordán 33 Ingeniería 33
Steinberg 33 Ingeniería 33
Smith 34 Ingeniería 33
Róbinson 34 Ingeniería 33
Gaspar 36 Ingeniería 33
Rafferty 31 Producción 34
Jordán 33 Producción 34
Steinberg 33 Producción 34
Smith 34 Producción 34
Róbinson 34 Producción 34
Gaspar 36 Producción 34
Rafferty 31 Marketing 35
Jordán 33 Marketing 35
Steinberg 33 Marketing 35
Smith 34 Marketing 35
Róbinson 34 Marketing 35
Gaspar 36 Marketing 35
Esta clase de combinaciones son usadas pocas veces, generalmente se les agregan condiciones de
filtrado con la sentencia WHERE para hallar resultados específicos.
Este tipo de operación se subdivide dependiendo de la tabla a la cual se le admitirán los registros
que no tienen correspondencia, ya sean de tabla izquierda, de tabla derecha, o combinación
completa.
La sentencia LEFT OUTER JOIN retorna la pareja de todos los valores de la tabla izquierda con los
valores de la tabla de la derecha correspondientes, o retorna un valor nulo NULL en caso de no
correspondencia.
A diferencia del resultado presentado en los ejemplos A y B (de combinación interna) donde no se
mostraba el empleado cuyo departamento no existía; en el siguiente ejemplo se presentarán los
empleados con su respectivo departamento, e inclusive se presentará el empleado, cuyo
departamento no existe.
SELECT distinct *
FROM empleado LEFT OUTER JOIN departamento
ON empleado.IDDepartamento = departamento.IDDepartamento;
Empleado.Apellido Empleado.IDdepartamento departamento.NombreDepartamento departamento.IDDepartamento
Jordán 33 Ingeniería 33
Rafferty 31 Ventas 31
Róbinson 34 Producción 34
Smith 34 Producción 34
Gaspar 36 NULL NULL
Steinberg 33 Ingeniería 33
La sentencia RIGHT OUTER JOIN retorna la pareja de todos los valores de la tabla derecha con los
valores de la tabla de la izquierda correspondientes, o retorna un valor nulo NULL en caso de no
correspondencia.
H. Ejemplo de tabla derecha para la combinación externa:
SELECT *
FROM empleado RIGHT OUTER JOIN departamento
ON empleado.IDDepartamento = departamento.IDDepartamento
Smith 34 Producción 34
Jordán 33 Ingeniería 33
Róbinson 34 Producción 34
Steinberg 33 Ingeniería 33
Rafferty 31 Ventas 31
NULL NULL Marketing 35
En este caso el área de Marketing fue presentada en los resultados, aunque aún no hay empleados
registrados en dicha área.
SELECT *
FROM empleado
FULL OUTER JOIN departamento
ON empleado.IDDepartamento= departamento.IDDepartamento
Empleado.Apellido Empleado.IDdepartamento departamento.NombreDepartamento departamento.IDDepartamento
Smith 34 Producción 34
Jordán 33 Ingeniería 33
Róbinson 34 Producción 34
Gaspar 36 NULL NULL
Steinberg 33 Ingeniería 33
Rafferty 31 Ventas 31
NULL NULL Marketing 35
Como se puede notar, en este caso se encuentra el empleado Gaspar con valor nulo en su área
correspondiente, y se muestra además el departamento de Marketing con valor nulo en los
empleados de esa área.
Algunos sistemas de bases de datos no soportan esta funcionalidad, pero esta puede ser emulada
a través de las combinaciones de tabla izquierda, tabla derecha y de la setencia de union union.
SELECT *
FROM empleado
LEFT JOIN departamento
ON empleado.IDDepartamento = departamento.IDDepartamento
UNION
SELECT *
FROM empleado
RIGHT JOIN departamento
ON empleado.IDDepartamento = departamento.IDDepartamento
WHERE empleado.IDDepartamento IS NULL
Entre las funciones que manejan cadenas de caracteres se encuentran las siguientes:
cad1||cad2: Concatena las cadenas de caracteres. Los nombres de columnas son cadenas
validas.
TO_NUMBER (cadena): Convierte los datos carácter (compuestos por números) en valore
numéricos.
NVL (cad1,cad2): Si cad1 es nulo retorna cad2. En otro caso retorna cad1.