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”))