Download - Sql basico parte_i_
Base de Datos: Lenguaje SQL
Profesor:
MS. Luis Serna J.
Objetivos de Hoy Introducción al SQL Basico Sentencias DDL:
CREATE TABLE ALTER TABLE DROP TABLE
Sentencia DML: SELECT
Cláusulas SELECT, FROM, INTO, WHERE Cláusulas GROUP BY, HAVING Cláusula ORDER BY Tipos de JOIN Subqueries
Sentencias INSERT, UPDATE y DELETE
SENTENCIAS DDL
SENTENCIAS DDL
CREATE TABLE
Las tablas se definen en tres pasos: Dar el nombre a la tabla Definir cada columna, posiblemente
incluyendo restricciones de columna Definir las restricciones de la tabla
SENTENCIAS DDL
CREATE TABLECREATE TABLE nombre-tabla
{campo1 (tipo tamaño / dominio) [NOT NULL],
[campo2 (tipo tamaño / dominio) [NOT NULL]], [PRIMARY KEY (c1, c2, ..., cn)],
[FOREIGN KEY (c1) REFERENCES tabla-x],
[CHECK (P)]}
SENTENCIAS DDL
Nombre-tabla: Es el nombre de la tabla que se va a crear.
Campo i: son los nombres de los campos de la tabla.
Tipo: Es el tipo de datos del campo. Tamaño:Es el tamaño del campo en caracteres Dominio: es el nombre del dominio asignado al
campo.
CREATE TABLE
SENTENCIAS DDL
Primary Key: especifica las columnas que conforman la clave primaria de la tabla.
Foreign key: indica las columnas que constituyen clave externa en la tabla
References: indica la tabla que provee la clave foránea.
Check (P): especifica un predicado que debe satisfacer cada fila de la tabla
CREATE TABLE
SENTENCIAS DDL
Ejemplos: CREATE TABLECREATE TABLE Clientes
(id-cliente char(5) not null,nombre-clientechar(20),calle-cliente char(30),primary key id-cliente)
SENTENCIAS DDL
Ejemplos: CREATE TABLECREATE TABLE Cuenta
(id-cuenta char(10) not null,id-cliente char(5),saldo integer
check (saldo >= 0),primary key id-cuentaforeign key id-cliente references clientes)
SENTENCIAS DDL
Uso de Constraints Una restricción es similar a un índice, aunque
también se puede utilizar para establecer una relación con otra tabla.
Puede utilizar la cláusula CONSTRAINT en las instrucciones ALTER TABLE y CREATE TABLE para crear o eliminar restricciones.
Hay dos tipos de cláusulas CONSTRAINT: una para crear una restricciones en un único campo y otra para crear una restricción en más de un campo.
SENTENCIAS DDL
Uso de Constraints
CONSTRAINT nombre{PRIMARY KEY (principal1 [,principal2 [, ...]]) |UNIQUE (único1 [,único2 [, ...]]) |NOT NULL (negadonulo1 [,negadonulo2 [, ...]]) |FOREIGN KEY (referencia1 [,referencia2 [, ...]])
REFERENCES tabla_externa [(campo_externo1 [,campo_externo2 [, ...]])]}
SENTENCIAS DDL
Uso de Constraints Nombre: Es el nombre de la restricción que
se va a crear. principal1, principal2: Son los nombres de
los campos que se van a designar como clave principal.
único1, único2: Son los nombres de los campos que se van a designar como clave única.
SENTENCIAS DDL
Uso de Constraints negadonulo1, negadonulo2: Son los
nombres de campos que se van a limitar a valores no Null.
referencia1, referencia2: Son los nombres de los campos de la clave externa a los que se hace referencia en otra tabla.
SENTENCIAS DDL
Uso de Constraints
Tabla_externa: Es el nombre de la tabla externa que contiene el campo o los campos especificados por campo_externo.
Campo_externo1, campo_externo2: Son los nombres de los campos en tabla_externa especificados por referencia1, referencia2. Puede omitir esta cláusula si el campo al que se hace referencia es la clave principal de tabla_externa.
SENTENCIAS DDL
ALTER TABLEALTER TABLE nombre-tabla{ADD | MODIFY [COLUMN campo tipo [(tamaño)] [NOT NULL]
[CONSTRAINT nombre-constraint tipo-constraint]] |
[CONSTRAINT nombre-constraint tipo-constraint (campo,…)]}
SENTENCIAS DDL
ALTER TABLE
Tabla: Es el nombre de la tabla que se va a modificar.
Campo: Es el nombre del campo que se va a agregar o eliminar de tabla.
Tipo: Es el tipo de datos del campo. Tamaño:Es el tamaño del campo en
caracteres
SENTENCIAS DDL
Ejemplos: ALTER TABLE Adicionar un campo:
ALTER TABLE Empleados ADD COLUMN Salario number(15,2)
Quitar el campo Salario de la tabla Empleados: ALTER TABLE Empleados DROP COLUMN
Salario;
SENTENCIAS DDL
Ejemplos: ALTER TABLE Adicionar una clave foránea:
ALTER TABLE Pedidos ADD CONSTRAINT PedidosEmpleados FOREIGN KEY (IdEmpleado) REFERENCES Empleados (IdEmpleado)
Quitar la clave externa existente de la tabla Pedidos: ALTER TABLE Pedidos DROP CONSTRAINT
PedidosEmpleados;
SENTENCIAS DDL
DROP
DROP {TABLE tabla | INDEX índice ON tabla}
tabla: Es el nombre de la tabla que se va a eliminar o la tabla de la cual se va a eliminar un índice.
Índice: Es el nombre del índice que se va a eliminar de tabla.
SENTENCIAS DDL
Ejemplos: DROP
Ejemplo de como se elimina el índice Mi_Indice de la tabla Empleados:
DROP INDEX Mi_Indice ON Empleados;
Ejemplo de cómo se elimina una tabla:DROP TABLE Practicas;
SENTENCIAS DML
SELECT INSERTUPDATE DELETE
Introducción al SQL - SQL DML
Sentencias de Manipulación de Datos
SELECT ..... FROM .... WHERE SELECT corresponde a la operación
Proyección (). Lista los atributos deseados en el resultado de la consulta.
FROM corresponde a la operación Producto Cartesiano (X). Lista las relaciones que se deben analizar.
WHERE corresponde al predicado Selección (), y se refiere a los atributos de las relaciones listadas en FROM.
Sentencia SELECT – Estructura Básica
Sentencia SELECT
[SELECT ...] [INTO ...]
[FROM ...][WHERE ...][GROUP BY …][HAVING ...][ORDER BY ...]
[UNION ...]
Cláusula SELECT
En la cláusula SELECT se especifican las columnas que van a ser mostradas en pantalla
SELECT [all | distinct] { * | tabla.* | [tabla.]campo1
[encab1] , [tabla.]campo2 [encab2] , ...}
Cláusula FROM
Específica la(s) fuente(s) de donde van a ser tomadas las columnas mostradas y/o operadas en la sentencia Select.
[SELECT…]FROM expresión tabla [as] t_alias [, ...]
Cláusula INTO
La cláusula INTO, permite guardar los resultados intermedios de una consulta
[SELECT ...] INTO tabla_into[FROM ...]
Cláusula WHERE
Donde se especifican las condiciones de búsqueda y join para las filas que conforman el conjunto resultado.[SELECT ...][FROM ...]WHERE condiciones_de_búsqueda
Tipos de condiciones Condiciones de comparación, de Join, o de
Subquery.
SELECT distinct emp_id, fecha_nac, direccionFROM EMPLEADOWHERE nombre = “Juan” AND apellido = “Perez”
fecha_nac, direccion (nombre = “Juan” AND apellido = “Perez” (EMPLEADO))
Ejemplos de la Sentencia SELECT
‘Mostrar la relación de clientes que han obtenido un préstamo en alguna sucursal de Miraflores’
SELECT DISTINCT nombre-clienteFROM PRESTATARIO PRS, PRESTAMO PTRWHERE PRS.idpréstamo = PRT.numpréstamo
AND nombre-sucursal = “Miraflores”
Ejemplos de la Sentencia SELECT
Condiciones de Búsqueda
Las condiciones de búsqueda pueden incluir: Operadores de Comparación (=, <>, < y >). Porciones de cadenas de caracteres (SUBSTR) Rangos (BETWEEN y NOT BETWEEN). Listas (IN, NOT IN). Patrones de caracteres (LIKE y NOT LIKE). Valores desconocidos (IS NULL y IS NOT NULL). Combinaciones con conjunciones (AND, OR).
Condiciones de Búsqueda Operadores de comparación:
WHERE SueldoBásico > 1000 WHERE SUBSTR(CodigoPostal, 1, 3)= “SAN”
Rangos (BETWEEN / NOT BETWEEN): WHERE SueldoBásico BETWEEN 1000 AND 5000
Listas (IN / NOT IN): WHERE Departamento NOT IN ('ADM', 'SIS')
Coincidencia de patrones o cadenas de caracteres (LIKE / NOT LIKE))
WHERE NombreCompleto LIKE “LET%”
Comodines (Wildcards) en la cláusula WHERE
Wildcard Significado
* % Cualquier cadena de cero o mas caracteres.
?, #, _ Cualquier carácter/ número único.
[-] Cualquier carácter único dentro de un rango.
[!] Cualquier carácter único que no está dentro de un rango.
Operadores y Wildcards en la cláusula WHERE
….WHERE Nombre
LIKE “Ma%” busca todos los nombres que comiencen con “Ma”(Ej.: María, Mariana, Manuel, Martín)
LIKE “%ía” busca todos los nombres que terminen con “ía”.(Ej.: Sofía, María, Estefanía).
LIKE “%ar%” busca todos los nombres que tengan las letras “ar”.(Ej.:Carlos, Arturo, Eleazar).
LIKE “_va” busca todos los nombres de tres letras que terminan en “va”. (Ej.: Eva, Iva, Ava).
LIKE “[CM]arlo[ns]” busca todos los nombres: Carlon, Marlon, Carlos y Marlos.
LIKE “[B-D]elia” busca todos los nombres que terminan en “elia” y que comiencen con las letras de la B a la D. (Ej.: Delia, Celia).
LIKE “_ _ _” busca todas las cadenas de exactamente 3 caracteres.
LIKE “_ _ _ %” busca las cadenas de al menos 3 caracteres.
Operadores y Wildcards en la cláusula WHERE
CARACTERES DE ESCAPE:Para que los patrones puedan contener los caracteres especiales comodín, se especifica un caracter de escape.
LIKE “%ab\_cd” escape “\” busca todas las cadenas que terminen con “ab_cd”.
LIKE “ab\_cd%” escape “\” busca las cadenas que empiecen con “ab_cd”.
Operadores y Wildcards en la cláusula WHERE
Tablas Combinadas
Permite especificar la tabla resultante de una operación de reunión natural en la cláusula FROM, en lugar de colocar las condiciones de JOIN en el WHERE[SELECT] FROM TABLA1 [INNER] JOIN TABLA2
ON TABLA1.COLUMNA1 = TABLA2.COLUMNA1
[WHERE]
Tablas Combinadas
SELECT DISTINCT nombre-clienteFROM PRESTATARIO PRS [INNER] JOIN PRESTAMO
PTROn PRS.idpréstamo =
PRT.numpréstamo WHERE nombre-sucursal = “Miraflores”
Cláusula GROUP BY
Especifica las columnas por las que las filas van a estar agrupadas o particionadas. Los resultados del query contienen un valor o conjunto de valores para cada conjunto de valores indicado por las funciones_de_agregación nombradas en la lista del Select
SELECT {{columnas_de_agrupación,…}, {función_de_agregación,…}}
FROM …[WHERE …]GROUP BY
{columnas_de_agrupación,…}
Cláusula GROUP BY
Funciones de Agregación más usadas
SUM([ALL|DISTINCT] expresión)
Calcula el total de una expresión numérica para todas las filas o sólo las distintas.
AVG([ALL|DISTINCT] expresión)
Calcula el promedio de una expresión numérica para las filas involucradas.
MIN([ALL|DISTINCT] expresión)
Calcula el mínimo valor de una expresión numérica para las filas involucradas
MAX([ALL|DISTINCT] expresión)
Calcula el máximo valor de una expresión numérica para las filas involucradas.
COUNT([ALL|DISTINCT] expresión)
Número de veces que se repite el valor de la expresión.
COUNT(*) Número de filas seleccionadas
‘Mostrar la suma de sueldo básico de los empleados activos, por Departamento’
SELECT Nombre_Depto, SUM(SueldoBasico)FROM EMPLEADO EM, DEPARTAMENTO DE
WHERE EM.IDDepartamento = DE.IDDepartamento AND
EstadoEmpleado= 'ACT’GROUP BY Nombre_Depto
Cláusula GROUP BY
“Obtener el número de titulares de cuenta de cada sucursal”
SELECT nombre_sucursal, COUNT ( DISTINCT nombre-cliente)
FROM TITULAR-CUENTA TC, CUENTA CUEWHERE TC.numero-cuenta = CUE. numero-cuentaGROUP BY nombre-sucursal
Cláusula GROUP BY
Especifica una restricción que aplica a las funciones de agregación de los grupos. Esto afecta a las filas que son devueltas como resultado y no al cálculo de las funciones de agregación.
La cláusula WHERE si condiciona el número de filas que intervienen en el cálculo de las funciones de agregación.
Cláusula HAVING
SELECT {{columnas_de_agrupación,…}, {función_de_agregación,…}}
FROM …[WHERE …]GROUP BY {columnas_de_agrupación,…}HAVING condiciones_de_búsqueda
Cláusula HAVING
GROUP BY y HAVING
“Mostrar el sueldo promedio de los Departamentos con promedio superior a 1000”
SELECT Nombre_Depto, AVG(SueldoBasico)FROM EMPLEADO EM, DEPARTAMENTO DEWHERE EM.IDDepartamento =
DE.IDDepartamento ANDEstadoEmpleado= 'ACT'
GROUP BY Nombre_DeptoHAVING AVG(SueldoBasico) > 1000
“Saldo promedio de cada cliente de Surco que tiene como mínimo 3 cuentas”
SELECT TC. Nombre-cliente, AVG (saldo)FROM TITULAR-CUENTA TC, CUENTA CUE, CLIENTE CLIWHERE TC.numero-cuenta = CUE.numero-cuenta
AND TC.nombre-cliente = CLI.nombre-cliente AND ciudad-cliente = “Surco”
GROUP BY TC.nombre-clienteHAVING COUNT (DISTINCT TC.numero-cuenta) >= 3
GROUP BY y HAVING
GROUP BY - HAVING - WHERE
Procedimiento: Si en una misma consulta aparece una cláusula WHERE
y una cláusula HAVING, se aplica primero el predicado de la cláusula WHERE.
Las tuplas que satisfagan la condición, se colocan en grupos según la cláusula GROUP BY.
La cláusula HAVING se aplica luego a cada grupo. Los grupos que no la satisfagan se eliminan.
La cláusula SELECT utiliza los grupos restantes para generar las tuplas resultado de la consulta.
Cláusula ORDER BY Ordena el resultado de los querys por los valores
de las columnas mencionadas. Solamente se puede ordenar por las columnas especificadas en el SELECT.
ASC: Es el valor por defecto e indica que los resultados se van a presentar ascendentemente.
DESC: Debe especificarse al lado de la columna cuyo orden se desea ver en forma descendente.
Consideración: ordenar un gran número de tuplas puede ser costoso. Es conveniente ordenar sólo cuando sea estrictamente necesario.
SELECT {columna 1, columna 2,…}FROM …[WHERE …][GROUP BY...][HAVING ...]ORDER BY columna 1 [ASC|DESC],....
Cláusula ORDER BY
SELECT Nombre 'Departamento', AVG(SueldoBasico) 'Promedio
Sueldos'FROM EMPLEADO EM, DEPARTAMENTO DEWHERE EM.Departamento =
DE.Departamento GROUP BY NombreORDER BY AVG(SueldoBasico) DESC
Cláusula ORDER BY
Permite manipular los resultados de dos o mas querys, combinándolos en un único conjunto de datos. Muestra las filas recuperadas por cualquiera de los query
A diferencia del SELECT, que por omisión conserva los duplicados, la operación de unión normalmente los suprime.
A fin de evitar esto, es necesario colocar a continuación del operador UNION la palabra clave ALL
Operador UNION
Query1:SELECT select-list [INTO]
[FROM][WHERE][GROUP BY][HAVING]
y QueryNSELECT select-list
[FROM][WHERE][GROUP BY][HAVING]
Query1 [UNION [ALL]] QueryN [ORDER BY…..]donde:
Operador UNION
Todas las select-list mencionadas con la sentencia UNION, deben tener el mismo número de expresiones (columnas, expresiones aritméticas, funciones de agregación, etc.).
Las columnas que se corresponden, deben tener el mismo tipo de dato, sino es así, debe existir una conversión de datos implícita o explícita con una función de conversión de por medio.
Operador UNION
“Todos los clientes que poseen una cuenta, un préstamo o ambos”
(SELECT nombre-clienteFROM TITULAR-CUENTA) UNION(SELECT nombre-clienteFROM PRESTATARIO)
Operador UNION
OUTER JOINS Los outer joins extienden los resultados de un join simple.
Retornan las filas coincidentes y también las filas de una de las tablas que no tiene elementos en común con la otra.[Select]From tabla1, tabla2WHERE tabla1.columna1 = tabla2.columna1 (+)
[Select]From tabla1 LEFT [OUTER] JOIN TABLA2
ON tabla1.columna1 = tabla2.columna1 …..devuelven todas las filas de la tabla1 aunque no tengan
coincidencias con la tabla2 en la columna1.
OUTER JOINSSELECT IDPersona, NombreCompleto,
IDDepartamento, SueldoBasicoFROM PERSONA, EMPLEADOWHERE IDPersona = IDEmpleado (+)
SELECT IDPersona, NombreCompleto, IDDepartamento, SueldoBasico
FROM PERSONA LEFT JOIN EMPLEADOON IDPersona = IDEmpleado
(Seleccionan todas las personas, aún las que no figuran en la tabla EMPLEADO)
OUTER JOINSSELECT DE.Nombre_Dpto, IDEmpleado, SueldoBasicoFROM EMPLEADO RIGHT JOIN DEPARTAMENTO
ON EMPLEADO.IDDepartamento = DEPARTAMENTO.IDDepartamento
(Selecciona todos los departamentos, aún los que no tienen empleados)
Un join simple descarta las filas no coincidentes. Un outer join preserva filas que de otra forma
serían descartadas.
Uso de Subqueries
Un Subquery es una sentencia SELECT anidada dentro de otras sentencias SQL como SELECT, INSERT, UPDATE o DELETE, o dentro de otro Subquery.
A una sentencia SELECT, que contiene uno o mas Subqueries, se la denomina Nested Query o SELECT anidado.
Uso de Subqueries
Subqueries con IN / NOT IN: El resultado, es una lista de cero o mas
valores. Una vez que el Subquery devuelve resultados, el Outer query los usa, por inclusión o negación.
Con la palabra IN se puede resolver el operador relacional de Intersección
Empleando NOT IN se resuelve el operador Diferencia
Uso de Subqueries
SELECT NombreCompleto, SueldoBasico
FROM PERSONA, EMPLEADOWHERE IDPersona=IDEmpleado and
IDEmpleado NOT IN (SELECT IDEmpleado FROM SANCIONES_EMPLEADO)
Uso de Subqueries
Subqueries con Operadores de comparación.
Son los subquerys que se introducen con algún operador de comparación como =, <>, <, <=, >, >=, etc.
Estos subqueries retornan un solo valor.
Uso de Subqueries
Operadores de comparación modificados por ANY o ALL. Estos subqueries retornan cero o muchos valores. > ALL, significa que todos los valores
retornados por el subquery son menores al valor de comparación del outer.
> ANY, significa que por lo menos un valor retornado por el subquery es menor al valor de comparación del outer.
Uso de Subqueries.
Mostrar ID, nombre y sueldo de todos los empleados cuyo sueldo es mayor a todos los sueldos de la tabla CARGO
SELECT IDEmpleado, NombreCompleto, SueldoBasico
FROM EMPLEADO, PERSONAWHERE IDEmpleado = IDPersona
AND SueldoBasico > ALL(SELECT Sueldo FROM CARGO)
Uso de Subqueries.
Mostrar ID, nombre y sueldo de todos los empleados cuyo sueldo es mayor a alguno de los sueldos de la tabla CARGO
SELECT IDEmpleado, NombreCompleto, SueldoBasicoFROM EMPLEADO, PERSONAWHERE IDEmpleado = IDPersona
AND SueldoBasico > ANY(SELECT Sueldo FROM CARGO)
Uso de Subqueries.
Subqueries con el uso de la palabra EXISTS:
Funciona como un test de existencia. La cláusula WHERE, examina la existencia de filas devueltas por el Subquery. El Subquery devuelve los valores de TRUE o FALSE.
Uso de Subqueries.
“Muestra los códigos de los empleados que tienen alguna sanción”
SELECT IDEmpleado FROM EMPLEADO EMWHERE EXISTS
(SELECT * FROM SANCIONES_EMPLEADO SEWHERE EM.IDEmpleado = SE.IDEmpleado)
Subqueries correlativos
Aquellos queries anidados donde el Subquery depende del outer query para devolver sus valores.
Esto significa que el Subquery se ejecuta repetidamente, una vez por cada fila seleccionada por el Outer query.
Subqueries Correlativos
SELECT a.IDEmpleado, a.SueldoBasicoFROM EMPLEADO a WHERE a.SueldoBasico >=
(SELECT b.SueldoBasico FROM EMPLEADO bWHERE a.IDJefe = b.IDEmpleado )
Muestra código y sueldo de los empleados cuyos sueldos son mayores o iguales a los sueldos de sus respectivos jefes
Sentencia INSERT
INSERT es otra sentencia DML base, usada para incorporar nuevas filas a una tabla de base de datos.
INSERT [INTO] {table-name | view-name} [(column-list)]
{VALUES | (values-list) | select-statement }
Fabricantes (Nfabricante, país) INSERT INTO FABRICANTES (Nfabricante, País)
VALUES (“Reynolds Tobacco CO.”, “EEUU”)
Marcas (marca, Nfabricante, cartón, embalaje) INSERT INTO MARCAS
VALUES (“Camel”, “Reynolds Tobacco CO.”, 10, 20)
Ejemplos de la Sentencia INSERT
Sentencia UPDATE
Modifica los datos existentes en filas de una tabla de Base de DatosUPDATE {table-name | view-name}SET{column-list
| variable-list| variable_and_column-list},…..[ WHERE { condition }]
Sentencia UPDATE
Cambiar aquellos cigarrillos de clase “Normal”, con filtro, fabricados por “Tabacalera Nacional”, de modo que ahora no tengan filtro
UPDATE CIGARRILLOSSET filtro = “N”WHERE marca in
(SELECT marca FROM MARCAS WHERE Nfabricante = “Tabacalera Nacional”
And filtro = “S” and clase = “Normal”)
Sentencia DELETE
Elimina filas de una tabla de Base de Datos
DELETE FROM {table-name | view-name}[WHERE {condition} ]
Sentencia DELETE
Eliminar todos los fabricantes que no manufacturen cigarrillos de clase “Ultra Light”
DELETE FROM FABRICANTESWHERE Nfabricante NOT IN
(SELECT DISTINCT NfabricanteFROM MARCASWHERE marca IN
(SELECT marca FROM CIGARRILLOSWHERE clase = “Ultra Light”))