Post on 14-Aug-2015
Ing. Marcos Montesinos
Elaboración de sentencias SQL y
Diccionario de Datos
Ing. Marcos Montesinos Yarlequé
(pmmontes@cibertec.edu.pe)
Ing. Marcos Montesinos
Logro de la Sesión
Al término de la unidad, el alumno desarrolla consultas que obtienen la información solicitada, utilizando el diccionario de datos y las principales vistas de la Metadata de la Base de Datos Oracle utilizando las mejores prácticas en la elaboración de sentencias SQL.
Ing. Marcos Montesinos
Temario
2.1. Tema 2: Elaboración de Sentencias SQL • 2.1.1. Introducción a SQL• 2.1.2. Instrucciones DML y operadores• 2.1.3. Consultando dos o más tablas• 2.1.4. Funciones predefinidas
2.2. Tema 3: Diccionario de Datos• 2.2.1 Introducción al Diccionario de Datos• 2.2.2 Estructura del Diccionario• 2.2.3 Uso del Diccionario de Datos
Ing. Marcos Montesinos
La historia de SQL empieza en 1974, cuando un grupo de IBM desarrolló un lenguaje para la especificación de las características de las bases de datos que adoptaban el modelo relacional. Este lenguaje se llamaba SEQUEL (Structured English Query Language). Sin embargo, fue ORACLE quien lo introdujo por primera vez en 1979 en un programa comercial.
El SEQUEL terminaría siendo el antecesor del SQL. En 1986 es estandarizado por el ANSI, dando lugar a la primera versión estándar de este lenguaje, el SQL-86. Al año siguiente, en 1987, este estándar es también adoptado por la ISO.
Introducción a SQL
Ing. Marcos Montesinos
Ing. Marcos Montesinos
Categorias
DML DDL TC DCL
SELECT
INSERT
UPDATE
DELETE
CREATE
ALTER
DROP
RENAME
TRUNCATE
COMMIT
ROLLBACK
SAVEPOINT
GRANT
REVOKE
Ing. Marcos Montesinos
La mayoría de sentencias SQL tienen la misma estructura.
Todas comienzan por un verbo (select, insert, update, create), a continuación le sigue una o más clausulas que nos dicen los datos con los que vamos a operar (from, where), algunas de estas son opcionales y otras obligatorias como es el caso del from.
Componentes Sintácticos
Ing. Marcos Montesinos
Otras Condiciones de Comparación
Operador
BETWEEN
...AND...
IN(set)
LIKE
IS NULL
Significado
Entre dos valores (ambos inclusive),
Coincide con cualquiera de una lista de valores
Coincide con un patrón de caracteres
Es un valor nulo
Ing. Marcos Montesinos
Condiciones Lógicas
Operador
AND
OR
NOT
Significado
Devuelve TRUE si las dos condiciones
componentes son verdaderas
Devuelve TRUE si alguna de las
condiciones componentes es verdadera
Devuelve TRUE si la siguiente condición es falsa
Ing. Marcos Montesinos
SELECT ename, job, salFROM empWHERE job = 'SALESMAN'OR job = 'MANAGER'AND sal > 1500;
Reglas de Prioridad
SELECT ename, job, salFROM empWHERE ( job = 'SALESMAN'OR job = 'MANAGER' )AND sal > 1500;
Ing. Marcos Montesinos
Las funciones de grupo operan sobre juegos de filas para proporcionar un resultado por grupo.
GROUP BY
El salario máximo en la tabla EMPLOYEES.
…
Ing. Marcos Montesinos
AVG ...PROMEDIO
COUNT ...CUENTA
MAX ...MAXIMO
MIN ...MINIMO
STDDEV ...DESVIAC STD
SUM ...SUMA
VARIANCE..VARIANZA
Tipos de Funciones de Grupo
Ing. Marcos Montesinos
SELECT job, SUM(sal)FROM empGROUP BY job;
Uso GROUP BY
Ing. Marcos Montesinos
La subconsulta (consulta interna) se ejecuta una vez antes de la consulta principal.
El resultado de la subconsulta lo utiliza la consulta principal (consulta externa).
Sintaxis de Subconsulta
SELECT select_listFROM tableWHEREexpr operator
(SELECT select_list FROM table);
Ing. Marcos Montesinos
SELECT enameFROM empWHERE sal > (SELECT sal FROM emp WHERE ename = 'SCOTT');
Uso de una Subconsulta
Ing. Marcos Montesinos
Agregue nuevas filas a una tabla mediante la sentencia INSERT.
Con esta sintaxis sólo se inserta una fila cada vez.
Sintaxis de la Sentencia INSERT
INSERT INTO table [(column [, column...])]VALUES (value [, value...]);INSERT INTO table [(column [, column...])]VALUES (value [, value...]);
Ing. Marcos Montesinos
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno )VALUES (1234, 'CURTIS', 'MANAGER', 1111, SYSDATE, 6900, 200, 10);1 row created.1 row created.
Ejemplo
Ing. Marcos Montesinos
INSERT INTO dept
(deptno, dname, loc)
VALUES (&deptno, '&dname', '&loc')
Utilice la sustitución & en una sentencia SQL para solicitar valores.
& es una variable pendiente de asignación para el valor de la variable.
Creación de un Archivo de Comandos
Ing. Marcos Montesinos
Escriba la sentencia INSERT con una subconsulta.
No utilice la cláusula VALUES.
Haga coincidir el número de columnas de la cláusula INSERT con el de la subconsulta.
Copia de Filas desde otra Tabla *
INSERT INTO sales_reps(id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE '%REP%';
Ing. Marcos Montesinos
Modifique las filas existentes con la sentencia UPDATE.
Actualice más de una fila cada vez si es necesario.
Sintaxis Sentencia UPDATE
UPDATEtableSET column = value [, column = value, ...][WHERE condition];
UPDATEtableSET column = value [, column = value, ...][WHERE condition];
Ing. Marcos Montesinos
Si incluye la cláusula WHERE, las filas específicas se modifican.
Se modifican todas las filas de la tabla si omite la cláusula WHERE.
Actualización de las Filas de una Tabla
UPDATE empSET sal = 2000WHERE ename = 'CURTIS';
UPDATE empSET mgr= 123;
UPDATE empSET mgr= 123;
Ing. Marcos Montesinos
UPDATE copy_empSET department_id = (SELECT department_id FROM employees WHERE employee_id = 100)WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 200);
Utilice subconsultas en sentencias UPDATE para actualizar las filas de una tabla basándose en valores de otra tabla.
Actualización de Filas Basándose en otra Tabla *
Ing. Marcos Montesinos
Puede eliminar las filas existentes de una tabla utilizando la sentencia DELETE.
La Sentencia DELETE
DELETE [FROM] table[WHERE condition];DELETE [FROM] table[WHERE condition];
Ing. Marcos Montesinos
Se suprimen filas específicas si incluye la cláusula WHERE.
Se suprimen todas las filas de la tabla si omite la cláusula WHERE.
Supresión de Filas de una Tabla
DELETE FROM emp WHERE ename = 'CURTIS';
DELETE FROM emp WHERE ename = 'CURTIS';
DELETE FROM emp;DELETE FROM emp;
Ing. Marcos Montesinos
DELETE FROM employeesWHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE '%Public%');
Utilice subconsultas en sentencias DELETE para eliminar las filas de una tabla basándose en los valores de otra tabla.
Supresión de Filas Basándose en otra Tabla *
Ing. Marcos Montesinos
La condición de unión para la unión natural es básicamente una unión de igualdad de todas las columnas con el mismo nombre.
Para especificar condiciones arbitrarias o especificar columnas para unir, se utiliza la cláusula ON.
La condición de unión se separa de otras condiciones de búsqueda.
La cláusula ON facilita la comprensión del código.
Creación de Uniones con la Cláusula JOIN
Ing. Marcos Montesinos
SELECT e.empno, e.ename, e.deptno, d.dnameFROM emp e JOIN dept dON (e.deptno = d.deptno);
Recuperación de Registros con la Cláusula JOIN
SELECT e.empno, e.ename, e.deptno, d.dnameFROM emp e, dept dWHERE e.deptno = d.deptno;
SELECT e.empno, e.ename, e.deptno, d.dnameFROM emp e, dept dWHERE e.deptno(+) = d.deptno;
Ing. Marcos Montesinos
Funciones de Caracteres
Funciones de Funciones de
CaracteresCaracteres
LOWERUPPERINITCAP
CONCATSUBSTRLENGTHINSTRLPAD | RPADTRIMREPLACE
Funciones de manipulación Funciones de manipulación
de mayúsculas/minúsculasde mayúsculas/minúsculas Funciones de manipulación Funciones de manipulación
de caracteresde caracteres
Ing. Marcos Montesinos
Function Result
Estas funciones convierten las mayúsculas/minúsculas para cadenas de caracteres.
Funciones de Manipulación de Mayúsculas/Minúsculas
LOWER('SQL Course')UPPER('SQL Course')INITCAP('SQL Course')
sql courseSQL COURSESql Course
Ing. Marcos Montesinos
CONCAT('Hello', 'World')SUBSTR('HelloWorld',1,5)LENGTH('HelloWorld')INSTR('HelloWorld', 'W')LPAD(salary,10,'*')RPAD(salary, 10, '*')TRIM('H' FROM 'HelloWorld')
HelloWorldHello106*****2400024000*****elloWorld
Función Resultado
Estas funciones manipulan cadenas de caracteres:
Funciones de Manipulación de Caracteres
Ing. Marcos Montesinos
ROUND: Redondea el valor a los decimales especificados.
ROUND(45.926, 2) 45.93
TRUNC: Trunca el valor a los decimales especificados.
TRUNC(45.926, 2) 45.92
MOD: Devuelve el resto de la división.
MOD(1600, 300) 100
ABS: Calcula el valor absoluto de n.
ABS(-16) 16
CEIL: Calcula el menor numero entero mayor o igual que n.
CEIL(16.7) 17
FLOOR: Calcula el mayor numero entero menor o igual que n.
FLOOR(16.7) 16
POWER: Devuelve m elevado a la n potencia, n debe ser entero
POWER(3,2) 9
Funciones Numéricas
Ing. Marcos Montesinos
La base de datos Oracle almacena fechas en un formato numérico interno: siglo, año, mes, día, horas, minutos, segundos.
El formato de visualización de fecha por defecto es DD-MON-RR.Le permite almacenar fechas del siglo XXI
en el siglo XX especificando solamente los dos últimos dígitos del año.
Le permite almacenar fechas del siglo XX en el siglo XXI de la misma forma.
Trabajo con Fechas
Ing. Marcos Montesinos
SYSDATE es una función que devuelve:
Fecha
Hora
Trabajo con Fechas
Ing. Marcos Montesinos
Funciones de Fecha
Número de mesesentre dos fechas
MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY
ROUND
TRUNC
Suma meses de calendario a una fecha
Siguiente día de la fecha especificada
Último día del mes
Redondea la fecha
Trunca la fecha
Función Descripción
Ing. Marcos Montesinos
• MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')
Uso de Funciones de Fecha
• ADD_MONTHS ('11-JAN-94',6)
• NEXT_DAY ('01-SEP-95','FRIDAY')
• LAST_DAY('01-FEB-95')
19.6774194
'11-JUL-94'
'08-SEP-95'
'28-FEB-95'
Ing. Marcos Montesinos
• ROUND(SYSDATE,'MONTH') 01-AUG-95
• ROUND(SYSDATE ,'YEAR') 01-JAN-96
• TRUNC(SYSDATE ,'MONTH') 01-JUL-95
• TRUNC(SYSDATE ,'YEAR') 01-JAN-95
Uso de Funciones de Fecha
Asuma SYSDATE = '25-JUL-95':
Ing. Marcos Montesinos
Conversión Explícita de Tipos de Dato
NUMBER CHARACTER
TO_CHAR
TO_NUMBER
DATE
TO_CHAR
TO_DATE
Ing. Marcos Montesinos
El modelo de formato:Se debe escribir entre comillas sencillas y es sensible a mayúsculas/minúsculas.
Puede incluir cualquier elemento de formato de fecha válido.
Tiene un elemento fm para eliminar espacios rellenados o suprimir ceros a la izquierda.
Se separa del valor de fecha con una coma.
Uso de la Función TO_CHAR con Fechas
TO_CHAR(date, 'format_model')TO_CHAR(date, 'format_model')
Ing. Marcos Montesinos
YYYY
Elementos del Modelo de Formato de Fecha
YEAR
MM
MONTH
DY
DAY
Año completo en números
Años en letra
Valor de dos dígitos para el mes
Abreviatura de tres letras del día de la semana
Nombre completo del día de lasemana
Nombre completo del mes
MON Abreviatura de tres letras del mes
DD Día del mes en número
Ing. Marcos Montesinos
Los elementos de hora formatean la porción de hora de la fecha.
Agregue cadenas de caracteres escribiéndolas entre comillas dobles.
Elementos del Modelo de Formato de Fecha
HH24:MI:SS AM 15:45:32 PM
DD "of" MONTH 12 of OCTOBER
Ing. Marcos Montesinos
Uso de la Función TO_CHAR con Fechas
SELECT ename, TO_CHAR(hiredate, 'fmDD Month YYYY') AS HIREDATEFROM emp;
Ing. Marcos Montesinos
Estos son algunos de los elementos de formato que puede utilizar con la función TO_CHAR para mostrar un valor numérico como carácter:
Uso de la Función TO_CHAR con Números
TO_CHAR(number, 'format_model')TO_CHAR(number, 'format_model')
9
0
$
L
.
,
Representa un número.
Obliga a mostrar un cero.
Coloca un signo de dólar flotante.
Utiliza el símbolo de divisa local flotante.
Imprime una coma decimal.
Imprime un indicador de miles.
Ing. Marcos Montesinos
SELECT TO_CHAR(sal, '99,999.00') SALARYFROM empWHERE ename = 'SCOTT';
Uso de la Función TO_CHAR con Números
Ing. Marcos Montesinos
Convierta una cadena de caracteres en formato numérico utilizando la función TO_NUMBER:
Convierta una cadena de caracteres en formato de fecha utilizando la función TO_DATE:
Estas funciones tienen un modificador fx que especifica la coincidencia exacta para el argumento de caracteres y un modelo de formato de fecha de una función TO_DATE.
Uso de las Funciones TO_NUMBER y TO_DATE
TO_NUMBER(char[, 'format_model'])TO_NUMBER(char[, 'format_model'])
TO_DATE(char[, 'format_model'])TO_DATE(char[, 'format_model'])
Ing. Marcos Montesinos
Preguntas ?
Ing. Marcos Montesinos
Laboratorio