Post on 19-Aug-2018
El Lenguaje SQL
Funciones en SQL
• Las funciones realizan una acción tomando argumentos de entrada y retornando un valor como resultado.• Son utilizados para:
– Realizar cálculos sobre datos.– Modificar elementos de datos individuales.– Manipular salida para grupos de filas.– Formatear fechas y números para su despliegue.– Convertir los tipos de datos de las columnas.
• Varias funciones pueden estar anidadas, como ejemplo,F3(F2(F1(col, arg1), arg2), arg3)
Funciones en SQL
Resultados
Funciones
arg 1
arg 2
arg N
Entrada Salida
AccionesEjecutadas
por la función
• Funciones de una sola fila: operan sobre una filay retornan un resultado por fila.– Caracter: LOWER, UPPER, INITCAP, CONCAT,
SUBSTR, LENGTH, INSTR, LPAD.– Número: ROUND, TRUNC, MOD.– Fecha: SYSDATE , MONTHS_BETWEEN,
ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND,TRUNC.
– Conversión: TO_CHAR, TO_NUMBER, TO_DATE.• Funciones de múltiples filas: Manipulan grupos
de filas y dan un resultado por grupo.
Funciones de SQL
Trabajando con fechas
• ORACLE almacena las fechas internamente en unformato numérico: siglo, año, mes, día, minutos,segundos.
• El formato de fecha por defecto es ‘DD-MM-YY’.• El SYSDATE es una función que retorna la fecha
y hora actual.• Se puede sumar o restar un número de días a una
fecha.• Se pueden restar dos fechas para obtener el
número de días entre ambas.• Para sumar horas a una fecha se divide el número
de días entre 24.
Conversión de datos implícita
• Para las asignaciones, ORACLE convierteautomáticamente:
• Para evaluación de expresiones, ORACLEconvierte automáticamente:
DE AVARCHAR2 o CHAR NUMBERVARCHAR2 o CHAR DATENUMBER VARCHAR2DATE VARCHAR2
DE A
VARCHAR2 o CHAR NUMBER
VARCHAR2 o CHAR DATE
Conversión de datos explícita
• TO_CHAR(numero | fecha, [‘fmt’]) : Convierteun número o fecha a un VARCHAR2 con elmodelo de formato fmt.
• TO_NUMBER(char) : Convierte una cadenade caracteres que contiene dígitos a número.
• TO_DATE(char, [‘fmt’]) : Convierte unacadena de carac-teres que representa unafecha a un valor de fecha de acuerdo alformato fmt especificado.
Elementos del formato de fechasYYYY Año en número
YEAR Año en letras
MM Mes en dos dígitos
MONTH Nombre completo del mes
DY Abreviación de 3 letras del día de la semana
DAY Nombre completo del día
HH24:MI:SS AM Horas (militar), minutos y segundos
Funciones de agregación
Las funciones de agregación operan sobreconjuntos de tuplas para dar un resultado parael conjunto.
Ejemplo: Tabla ESTUDIANTE
"Obtener el índice máximo de todos los estudiantes" 4.12
Carnet ApyNombre Indice
92-134111 Miriam Jiménez 4.1294-326423 Angel López 3.7595-098127 Mark Ruiz 4.09
Funciones de agregación - tiposAVG([DISTINCT | ALL] n) Valor promedio de n, ignorando nulos.
COUNT(* | [DISTINCT | ALL] expr) Número de filas, en donde la expresiónevalúa a un valor diferente de nulo. Sise coloca * cuenta todas las filasincluyendo duplicados y filas con nulos.
MAX([DISTINCT | ALL] expr) Máximo valor de la expresión ignorandonulos.
MIN([DISTINCT | ALL] expr) Mínimo valor de la expresión ignorandonulos.
STDDEV([DISTINCT | ALL] n) Desviación estándar de n ignorandonulos.
SUM([DISTINCT | ALL] n) Suma de valores de n ignorando nulos
VARIANCE([DISTINCT|ALL] n) Varianza de n ignorando nulos.
•Los tipos de datos para expr son CHAR, VARCHAR2, NUMBER yDATE.
•Los tipos de datos para n son numéricos.
SintaxisSELECT columna, funcion_de_agregacion(columna)FROM tabla[WHERE condicion][ORDER BY columna];
Ejemplo:"Encuentre el índice máximo de los estudiantes
de la carrera '001' "SELECT MAX(indice) "indice maximo"FROM ESTUDIANTEWHERE idcarrera = '001';
Consultas particionadas
La cláusula GROUP BY se utiliza para dividir lasfilas de una tabla en particiones. Luego se puedenutilizar funciones de agregación para obtenerinformación sumarizada de cada partición.Ejemplo:Tabla ESTUDIANTE
"Número de estudiantes por carrera"
Carnet ApyNombre Indice Carrera
92-134111 Miriam Jiménez 4.12 00294-326423 Angel López 3.75 00195-098127 Mark Ruiz 4.09 001
Carrera Num
001 1 002 2
Consultas particionadasSintaxis
SELECT columna,funcion_de_agregacion(columna)
FROM tabla[WHERE condicion][GROUP BY expresion_de_agrupamiento][ORDER BY columna][HAVING condicion_grupo];
La expresión de agrupamiento indica lascolumnas cuyos valores dan la base para lasparticiones.
Consultas particionadasEjemplo
Ejemplo:"Obtenga el número de estudiantes de cada
carrera (el resultado debe tener el nombre dela carrera)"SELECT COUNT(*) "total estudiantes", C.nombreFROM ESTUDIANTE E, CARRERA CWHERE E.idcarrera = C.idcarreraGROUP BY C.nombre
Consultas ParticionadasLineamientos
Si se incluye una expresión de agrupamiento no sepueden seleccionar también columnas "individuales"a no ser que éstas aparezcan en la cláusula GROUPBY.El ejemplo de la página anterior daría un error si seescribiera así:
SELECT COUNT(*) "total estudiantes", E.indice, C.nombreFROM ESTUDIANTE E, CARRERA CWHERE E.idcarrera = C.idcarreraGROUP BY C.nombre
Lineamientos
• Se puede utilizar la cláusula WHEREpara excluir filas antes de dividir enparticiones.
• Se deben incluir todas las columnasseleccionadas en la cláusula GROUPBY.
• Se puede particionar por más de unacolumna.
Cláusula HAVING
La cláusula HAVING restringe las particiones queaparecerán en el resultado.Ejemplo:
"Obtenga el promedio del índice de los estudiantes decada carrera con promedio mayor que 3.5 ordenadosen orden creciente de promedio“
SELECT idcarrera, AVG(indice) FROM ESTUDIANTEGROUP BY idcarreraHAVING AVG(indice) > 3,5ORDER BY AVG(indice);
Consultas anidadas
Ejemplo:• Consulta principal: ¿Cuáles estudiantes tienen un número
mayor de créditos inscritos que el estudiante 'FranciscoTorres'?
• Subconsulta o consulta interna: ¿Cuántos créditosinscritos tiene el estudiante 'Francisco Torres'?
Para resolver esta consulta se requieren 2 consultas: unapara obtener el número de créditos inscritos delestudiante 'Francisco Torres', y otra para obtener aquellosestudiantes con un número de créditos mayor que elprimer resultado.
Consultas anidadas - Sintaxis
SELECT lista_de_columnasFROM tablaWHERE expr operador(SELECT lista_columnas FROM tabla);
• La subconsulta se ejecuta antes que laconsulta principal.
• El resultado de la subconsulta esutilizado por la consulta principal.
Consultas anidadas - Ejemplo
El ejemplo anterior se expresa de la siguientemanera:SELECT carnet, apynombreFROM ESTUDIANTEWHERE credins > (SELECT credins
FROM ESTUDIANTE WHERE apynombre = 'Francisco Torres');
Consultas anidadasLineamientos
• Las subconsultas van encerradas entreparéntesis.
• Las subconsultas deben colocarse al ladoderecho del operador de comparación.
• Use operadores de "una sola fila" parasubconsultas de una sola fila. El ejemploanterior tiene una subconsulta de una sola fila.
• Use operadores de "múltiples filas" parasubconsultas que den como resultado múltiplesfilas.
Lineamientos - Ejemplo
Ejemplo: "Obtenga los profesores que dictan al
menos una de las asignaturas dictadaspor 'Marta Medina' "– Subconsulta de varias filas: Obtenga las
asignaturas dictadas por 'Marta Medina'.
Consultas anidadasOperadores
• Los operadores de comparación parasubconsultas de una sola fila son =, >,>=, <, <=, <>.
• Los operadores de comparación parasubconsultas de múltiples filas son:
Operador Significado
IN Igual a cualquier elemento de la lista.
ANY Compara el valor con cada uno de loselementos retornados por la subconsulta.
ALL Compara el valor con todos los valoresretornados por la subconsulta.
Ejemplo de subconsultade múltiples filas
El ejemplo anterior se expresa de lasiguiente manera:SELECT nomprof FROM SECCIONWHERE codasig IN
(SELECT codasig FROM SECCION WHERE nomprof = 'Marta Medina');
Vistas
• Una vista representa un subconjuntológico de los datos de una o más tablas.
Carnet ApyNombre Indice Carrera
92-134111 Miriam Jiménez 4.12 00294-326423 Angel López 3.75 00195-098127 Mark Ruiz 4.09 001
Vista de "estudiantes de la carrera '001' "
Razones para utilizar vistas
• Restringen el acceso a la Base de Datos.• Simplifican consultas complejas.• Permiten la independencia de los datos.• Presentan diferentes visiones de los
mismos datos.
Vistas - Simples y complejas
Característica Vistas Simples Vistas Complejas
Número de tablas Una Una o más
Contienenfunciones
No Sí
Contienenparticiones
No Sí
Son actualizables Sí No siempre
Vistas - Sintaxis
• Para crear una vista se coloca unasubconsulta dentro de la instrucción CREATEVIEW.CREATE [OR REPLACE] [FORCE | NOFORCE]VIEW vista [(alias[, alias] ....)]AS subconsulta[WITH CHECK OPTION [CONSTRAINT restriccion]]
Vistas - Sintaxis
• FORCE crea la vista aunque no existan lastablas bases.
• Los alias permiten crear nombres decolumnas para las vistas.
• WITH CHECK OPTION especifica quesolamente las tuplas que sean accesibles através de la vista pueden ser eliminadas oactualizadas.
• CONSTRAINT permite asignar un nombre ala restricción del CHECK OPTION.
Vistas - ejemplo"Crear una vista con los carnet y apellidos y
nombres de los estudiantes de cada asignatura"CREATE VIEW LISTA_ESTAS SELECT A.codasig, A.carnet, apynombreFROM ASIGNA A, ESTUDIANTE EWHERE A.carnet = E.carnet;
"Obtener los carnets, apellidos y nombres de los
estudiantes de la asignatura 'AA0001'"SELECT carnetFROM LISTA_ESTWHERE codasig = 'AA0001';
Inserción con subconsulta
INSERT INTO nombre_tablasubconsulta;
Ejemplo:“Inserte a todos los empleados que son
gerentes en una nueva tabla de gerentes”INSERT INTO GERENTES(idger, nombre, salario, fechaingreso)SELECT idemp, nombre, sal, fechaingresoFROM EMP WHERE cargo = ‘gerente’;
Modificación con subconsultaUPDATE nombre_tabla SET columna = subconsultaWHERE expresion comp subconsulta
Ejemplo:“Actualice la tabla de empleados para que todas lastuplas que tengan el mismo cargo que el empleado‘12345’, trabajen en el mismo departamento”
UPDATE EMPSET iddpto = (SELECT iddpto FROM EMP
WHERE idemp = ‘12345’)WHERE cargo = (SELECT cargo FROM EMP WHERE idemp = ‘12345’);
Eliminación con subconsulta
DELETE FROM nombre_tablaWHERE expresion comp subconsulta;
Ejemplo:“Borre todos los empleados del departamento
de ‘ventas’”DELETE FROM EMP EWHERE E.iddpto = (SELECT D.iddpto FROM DPTO D WHERE nombre = ‘ventas’);