U N I V E R S I DA D D E E L S A LVA D O R
FA C U LTA D D E I N G E N I E R I A Y A R Q U I T E C T U R A
E S C U E L A D E I N G E N I E R I A D E S I S T E M A S
I N F O R M AT I C O S
Base de Datos BAD115
Catedráticos: Ing. Elmer Arturo Carballo Ruiz MSc.
Ing. César Augusto González Rodríguez MAF.
Ciclo
I
Guía de Laboratorio #4a
Lenguaje estructurado de Consulta SQL
Básico
Contenido Objetivos .................................................................................................................................. 1
Introducción ............................................................................................................................. 1
Desarrollo. ................................................................................................................................ 2
Funciones de Nulos, Caracteres, Numéricas y de Fecha de conversión y otras ............................... 2
Funciones para Valores Nulos ................................................................................................ 2
Funciones para Caracteres ..................................................................................................... 4
Funciones Numéricas ............................................................................................................. 6
Funciones de Fecha ............................................................................................................... 8
Funciones de Conversión ......................................................................................................12
Otras Funciones....................................................................................................................16
Totalizando Datos y Funciones de Grupo ...................................................................................19
Funciones de Grupo ..............................................................................................................19
GROUP BY ............................................................................................................................21
HAVING................................................................................................................................24
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 1
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
Objetivos Que el estudiante sea capaz de:
Realizar las operaciones básicas de consulta en SQL sobre Oracle utilizando una sola tabla.
Comprender los conceptos fundamentales referentes a consultas en SQL.
Utilizar funciones para manejo de valores nulos, cadenas de caracteres, numéricas, de
fecha, de conversión y otras.
Utilizar las formas de consulta con totalización y funciones de grupo.
Introducción
En esta de laboratorio se utilizaran los esquemas Scott y HR para realizar consultas
en SQL, utilizando funciones de manejo de nulos, cadenas de caracteres, numéricas, de
fecha, de conversión y otras, ejecutando instrucciones en SQLDeveloper conectado a los
esquemas antes mencionados. Además de incorporar las funcionalidades de totalización y
agrupación de registros.
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 2
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
Desarrollo.
Funciones de Nulos, Caracteres, Numéricas y de Fecha de
conversión y otras
Funciones para Valores Nulos
a) En el esquema Scott, muestre nombre, salario, comisión y la sumatoria de salario +
comisión bajo el nombre neto.
Script: select ename, sal, comm, (sal + comm) as neto from emp;
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 3
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
Función NVL
Remplaza un valor nulo por otro valor.
Repita la consulta anterior(a), remplazando el valor nulo de comisión con cero.
Script: select ename, sal, nvl(comm,0), sal + nvl(comm,0) as neto from emp;
Función NVL2
Remplaza un valor nulo por otro valor, si no es nulo también lo remplaza por otro valor
diferente.
Repita la consulta anterior(a), remplazando el valor de salario neto(neto) por la suma de
salario + comisión cuando exista la comisión o el valor de salario si no existe la comisión.
Script: select ename, sal, comm, nvl2(comm, sal + comm, sal) as neto
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 4
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
Funciones para Caracteres
Función Descripción Ejemplo
ASCII Retorna el valor ASCII equivalente de un carácter.
Ascii('A') = 65
CHR Retorna el carácter determinado por el valor ASCII equivalente.
Chr(65) = A
CONCAT Concatena dos cadena; equivalente al operador ||.
concat('Gustavo','Coronel') = GustavoCoronel
INITCAP Retorna la cadena con la primera letra de cada palabra en mayúscula.
InitCaP('PACHERREZ') = Pacherrez
Función Descripción Ejemplo
INSTR Busca la posición de inicio de una cadena dentro de otra.
Instr('Mississippi','i') = 2 Instr('Mississippi','s',5) = 6 Instr('Mississippi','i',3,2) = 8
INSTRB Similar a INSTR, pero cuenta bytes en lugar de caracteres.
InstrB('Mississippi','i') = 2 InstrB('Mississippi','s',5) = 6 InstrB('Mississippi','i',3,2) = 8
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 5
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
LENGTH Retorna la longitud de una cadena en caracteres.
Length('Oracle is Powerful') = 18
LENGTHB Retorna la longitud de una cadena en bytes.
LengthB('Oracle is Powerful') = 18
LOWER Convierte una cadena a minúsculas.
Lower('CHICLAYO') = chiclayo
LPAD Ajustada a la derecha una cadena, rellenándola a la
izquierda con otra cadena.
LPad('56.78',8,'#') = ###56.78
LTRIM Elimina caracteres a la izquierda de una cadena, por defecto espacios en blanco.
LTrim(' Alianza') = Alianza LTrim('Mississippi','Mis') = ppi
RPAD Ajustada a la izquierda una cadena, rellenándola a la derecha con otra cadena.
RPad('56.78',8,'#') = 56.78###
RTRIM Elimina caracteres a la derecha de una cadena, por defecto espacios en blanco.
RTrim('Real ') || 'Madrid' = RealMadrid RTrim('Mississippi','ip') = Mississ
REPLACE Permite reemplaza parte de una cadena.
Replace('PagDown','Down','Up') = PagUp
SUBSTR Permite extraer parte de una cadena.
SubStr('Trujillo',4,2) = ji
SUBSTRB Similar a SUBSTR, pero la posición se indica en bytes.
SubStrB('Trujillo',4,2) = ji
SOUNDEX Retorna la representación fonética de una cadena.
Soundex('HOLA') = H400
TRANSLATE Reemplaza caracteres de una cadena por otros caracteres.
Translate('Lorena','orn','unr') = Lunera
TRIM Elimina espacios en blanco a ambos lados de una cadena.
'Alianza' || Trim(' ES ') || 'Alianza' = AlianzaESAlianza
UPPER Convierte a mayúsculas una cadena.
Upper('peru') = PERU
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 6
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
b) Desplegar el nombre y el apellido separados con un espacio en blanco de los empleados
del departamento número 30 con el formato de inicial en Mayúsculas.
Script: select initcap( first_name || ' ' || last_name) from employees where department_id = 30;
Funciones Numéricas
Función Descripción Ejemplo
ABS Retorna el valor absoluto de un valor. Abs(-5) = 5
ACOS Retorna el arco coseno. ACos(-1) = 3.14159265
ASIN Retorna el arco seno. ASin(1) = 1.57079633
ATAN Retorna el arco tangente. ATan(0) = 0
ATAN2 Retorna el arco tangente; tiene dos valores
de entrada. ATan2(0,3.1415) = 0
BITAND Retorna el resultado de una comparación a
nivel de bits de números. BitAnd(3,9) = 1
CEIL Retorna el siguiente entero más alto. Ceil(5.1) = 6
COS Retorna el coseno de un ángulo. Cos(0) = 1
COSH Retorna el coseno hiperbólico. Cosh(1.4) = 2.15089847
EXP Retorna la base del logaritmo natural
elevado a una potencia. Exp(1) = 2.71828183
FLOOR Retorna el siguiente entero más pequeño. Floor(5.31) = 5
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 7
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
LN Retorna el logaritmo natural. Ln(2.7) = 0.99325177
LOG Retorna el logaritmo. Log(8,64) = 2
MOD Retorna el residuo de una operación de
división. Mod(13,5) = 3
POWER Retorna un número elevado a una
potencia. Power(2,3) = 8
ROUND Redondea un número. Round(5467,-2) = 5500
Round(56.7834,2) = 56.78
Función Descripción Ejemplo
SIGN Retorna el indicador de signo de un
número. Sign(-456) = -1
SIN Retorna el seno de un ángulo. Sin(0) = 0
SQRT Retorna el seno hiperbólico. Sqrt(16) = 4
TAN Retorna la tangente de un ángulo. Tan(0.785398165) = 1
TANH Retorna la tangente hiperbólica. Tanh(Acos(-1)) = 0.996272076
TRUNC Trunca un número. Trunc(456.678,2) = 456.67 Trunc(456.678,-1) = 450
c) Desplegar el nombre y el apellido separados con un espacio en blanco y el salario diario sin
decimales de los empleados del departamento número 30 .
Script: select initcap( first_name || ' ' || last_name), trunc(salary/30) from employees where
department_id = 30;
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 8
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
Funciones de Fecha Establecer el Formato de Fecha
Add_Months
Adiciona un número de meses a una fecha.
d) Con el formato establecido muestre el dia de hoy y cual será la fecha dentro de 3 meses.
Script: select sysdate as Hoy, add_months(sysdate,3) as TresMesesDespues from dual;
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 9
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
Current_Date
Retorna la fecha actual.
Script: select current_date from dual;
Current_Timestamp
Retorna la fecha y hora actual.
Script: select current_timestamp from dual;
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 10
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
Extract
Extrae y retorna un componente de una expresión Date/Time.
Extraer el año: Script: select sysdate as Hoy, extract(year from sysdate) as Año2 from dual;
Extraer el mes: Script: select sysdate as Hoy, extract(month from sysdate) as Mes2 from dual;
Extraer el dia: Script: select sysdate as Hoy, extract(day from sysdate) as Día2 from dual;
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 11
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
Last_Day
Retorna el último día del mes.
e) Muestre el dia de hoy, el ultimo dia del mes y el primer dia del siguiente mes
Script: select sysdate as Hoy, last_day(sysdate) as fin_del_mes, last_day(sysdate) + 1 as proxino_mes
from dual;
Month_Between
Retorna el número de meses entre dos fechas.
f) Muestre cuantos meses hay entre el 19 de abril de 2005 y el 19 de
diciembre de 2004. Script: select months_between('19-Abr-2005','19-Dic-2004') from dual;
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 12
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
SysDate
Retorna la fecha y hora actual.
Script: select sysdate from dual;
Funciones de Conversión
Cast
Convierte una expresión a un tipo de dato específico.
Script: select cast(sysdate as varchar2(24)) from dual;
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 13
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
To_Char
Convierte un dato tipo fecha ó número a una cadena con un formato especifico.
Conversión de Datos Tipo Fecha
Script: select to_char(sysdate, 'Day, Month YYYY','NLS_DATE_LANGUAGE=English') from dual;
Script: select to_char(sysdate, 'Day, Month YYYY','NLS_DATE_LANGUAGE=S panish') from dual;
Script: Select to_char(sysdate, 'Day, DD "de" MONTH "de" YYYY') from dual;
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 14
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
Conversión de Datos Numéricos
Script: select to_char(15.6789,'99,999.00') from dual;
Script: select to_char(45.78234,'00,000.00') from dual;
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 15
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
Script: select to_char(346.4567,'L99,999.00') from dual;
To_Date
Convierte una cadena con una fecha a un dato de tipo fecha.
Script: select to_date('15-01-2005','DD-MM-YYYY') from dual;
To_Number
Convierte una cadena numérica a su respectivo valor numérico.
Script: select to_number('15.45','999.99') from dual;
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 16
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
Otras Funciones
NULLIF
Compara dos expresiones expr1 y expr2, si ambas son iguales retorna NULL, de lo contrario retorna exp1. expr1 no puede ser el literal NULL.
Script: select ename, mgr, comm, NULLIF(comm,0) test1, NULLIF(0, comm) test2,
NULLIF(mgr,comm) test3 from emp where empno in (7844,7839, 7654, 7369);
Sys_Connect_By_Path
SYS_CONNECT_BY_PATH es válido solamente en consultas jerárquicas. Devuelve la trayectoria de una columna desde el nodo raíz, con los valores de la columna separados por un carácter para cada fila devuelta según la condición especificada en CONNECT BY.
Script1: column path format a40
Script2:select last_name, sys_connect_by_path(last_name, '/') Path from employees start with
last_name = 'Kochhar' connect by prior employee_id = manager_id;
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 17
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
Sys_Context
Retorna el parámetro asociado con un namespace.
Script: select sys_context('USERENV','HOST') from dual;
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 18
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
UID
Devuelve un número entero que identifique únicamente a cada usuario.
Script: select id from dual;
User
Retorna el nombre del usuario de la sesión actual
Script: select user from dual;
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 19
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
Totalizando Datos y Funciones de Grupo
Funciones de Grupo AVG
Obtiene el promedio de una columna o expresión. Se puede aplicar la cláusula DISTINCT.
Script: select avg(salary) from employees where department_id = 30;
COUNT
Cuenta las filas de una consulta. Se puede aplicar DISTINCT.
Script : select count(*) from departments;
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 20
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
Script(Con Distinct): select count(distinct department_id) from employees;
MAX
Retorna el máximo valor de una columna ó expresión.
a) Muestre el salario maximo de los empleados del departamento numero 80
Script: select max(salary) from employees where department_id = 80;
MIN
Retorna el mínimo valor de una columna ó expresión.
b) Muestre el salario minimo de los empleados del departamento numero 80
Script: select min(salary) from employees where department_id = 80;
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 21
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
SUM
Retorna la suma de los valores de una columna. Se puede aplicar DISTINCT.
Script: select sum(salary) from employees where department_id = 80;
GROUP BY
Se utiliza para agrupar data en base a una ó más columnas, para aplicar funciones de
grupo.
Mostrar los números de departamentos y la cantidad de empleados con que cuentan
Script: select department_id as Departamento, count(*) as Empleados from employees group by
department_id;
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 22
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
Mostrar la cantidad de empleados por puesto de trabajo en los departamentos 50 y 80.
Script: select department_id as Departamento, job_id as puesto, count(*) as Empleados from
employees where department_id in (50,80) group by department_id, job_id;
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 23
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
Mostrar la cantidad de empleados que han ingresado por año.
Script: select extract(year from hire_date) as año, count(*) as empleados from employees group by
extract(year from hire_date);
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 24
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
HAVING
Permite limitar mediante una condición de grupo el resultado obtenido después de aplicar
GROUP BY, tal como se aprecia en el siguiente gráfico.
Mostrar los Departamentos que tienen más de 10 empleados.
Script: select department_id as Departamento, count(*) as Empleados from employees group by
department_id having count(*) > 10;
Universidad de El Salvador BAD-115 Guía de Laboratorio #4
Ciclo II-2012 25
Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF
Mostrar los puestos de trabajo de los que solo hay un empleado en la empresa.
Script: select job_id as Puesto, count(*) as Empleados from employees group by job_id having
count(*) = 1;