Funciones Predefinidas_Unidad03
-
Upload
angel-huerta-robles -
Category
Documents
-
view
111 -
download
4
Transcript of Funciones Predefinidas_Unidad03
sábado, 8 de abril de 2023
2
Sesión 4Puntos a Tratar1. Funciones predefinidas de Oracle
2. Funciones de Caracter
3. Funciones de Numero
4. Funciones de Fecha
5. Funciones de Conversiòn
6. Funciones de Grupo
sábado, 8 de abril de 2023
3
Sesión 4Logros
1. El alumno podrá utilizar adecuadamente el Lenguaje SQL para manipular información en una BD ORACLE.
2. El alumno conocerá las funciones predefinidas en una BD ORACLE
sábado, 8 de abril de 2023
4
Sesión 4
Funciones predefinidas de Oracle
sábado, 8 de abril de 2023
5
LOWER (‘sql course’)
Funciones de Carácter : (Conversion)
Instrucciones DML - Funciones Predefinidas
UPPER (‘sql course’)
INITCAP (‘sql course’)
Función
select lower(ename) from emp;
select upper(dname) from dept;
select initcap(Loc) from dept;
sábado, 8 de abril de 2023
6
Funciones de Carácter(Conversiòn)
Mostrar el nombre del empleado en minusculas y el nombre de sus localidad con la primera letra en mayúscula.
sábado, 8 de abril de 2023
7
CONCAT (‘Hola’,’como estas ’)
Funciones de Carácter :
SUBSTR (‘Profesor’,1,3)
LENGTH (‘Hola’)
Función
INSTR (‘Amigos’,’g’)
LPAD (salario,10,’*’)
TRIM (‘ Hola a todos ‘)
Instrucciones DML - Funciones Predefinidas
select concat(desplat,codplat) from tbplatillos;
2 Columnas
sábado, 8 de abril de 2023
8
Mostrar el segunto ,tercero , cuarto y penúltimo caracter del nombre de los empleado pero en minuscula
Funciones de Caracter
Ejercicios:
Rellenar el nombre de los empleados y el nombre de la localidad con 15 caracteres utilizando el simbolo ‘$’ teniendo el cuenta el lado derecho
Mostrar el siguiente formato
sábado, 8 de abril de 2023
9
ROUND
Funciones númericas :
TRUNC
MOD
Función
ROUND(45.926,2)
TRUNC(45.926,2)
MOD (1600,300)
Ejemplo
45.93
45.92
100
Resultado
Instrucciones DML - Funciones Predefinidas
select sal/7 Salario,round(sal/7,2) Redondeo, trunc(sal/7,2) Trunca from emp;
sábado, 8 de abril de 2023
10
Mostrar el nombre y la novena parte (con 3 decimales) del sueldo de los empleados cuyo sueldo es mayor a 1500
Funciones de Caracter
Mostrar el residuo del sueldo dividido entre dos de todos los empleados cuyo sueldos son mayores a 500
Mostrar el sueldo entre 7 con 1 decimal y el sueldo entre 3 con dos decimales.
Ejercicios:
sábado, 8 de abril de 2023
11
MONTHS_BETWEEN(Sysdate+40,sysdate)
Funciones de Fecha :
ADD_MONTHS(sysdate,6)
ADD_MONTHS(sysdate,6)
Función
1.23
14/09
Resultado
31/03LAST_DAY(sysdate) ‘Último dia de la semana’
19/03
ROUND(sysdate,’MONTH’) 01/03
ROUND(sysdate,’YEAR’) 01/01/2012
TRUNC(sysdate,’MONTH’) 01/01/2012
Instrucciones DML - Funciones Predefinidas
Dual : Tabla sistema (memorìa)
sábado, 8 de abril de 2023
12
Funciones de Fechas :
Instrucciones DML - Funciones Predefinidas
SQL> SELECT SYSDATE FECHA _ACTUAL,ADD_MONTHS(SYSDATE,4)
AGREGAR_MES , NEXT_DAY(SYSDATE,3)
FECHA_DIA_SEMANA FROM DUAL;
PropuestosMostrar la fecha de hoy , la fecha de hoy incrementada en 4 meses y la fecha del primer día de la siguiente semana.
sábado, 8 de abril de 2023
13
sábado, 8 de abril de 2023
14
Función LEAST( ) :
Retorna el menor valor de las lista de expresiones.
Sintaxis:
LEAST(expres 1, expres 2, expres 3,…….., expres n )
Instrucciones DML - Funciones Predefinidas
sábado, 8 de abril de 2023
15
SQL> SELECT LEAST( 10, 5, 45 ) menor FROM DUAL ;
MENOR-----------
5
Ejemplo 1:
Función LEAST( ) :
Instrucciones DML - Funciones Predefinidas
SQL> SELECT LEAST( comm,sal ) menor FROM emp ;
sábado, 8 de abril de 2023
16
Función GREATEST( ) : Valor máximo.
Ejemplo1:
SQL> SELECT GREATEST(SYSDATE, to_date(’05/05/2006’,
'dd/mm/yyyy’ ) ) FROM DUAL ;
FECHA-----------
?
Instrucciones DML - Funciones Predefinidas
sábado, 8 de abril de 2023
17
FUNCIONES DE CONVERSION
Instrucciones DML – Conversiòn Datos Explisitos
TO_CHAR ()
TO_NUMBER ()
TO_DATE ()
sábado, 8 de abril de 2023
18
FUNCIONES DE CONVERSION
Instrucciones DML – Conversiòn Datos Explisitos
TO_NUMBER (d,fmto)
Convierte la cadena “d” a un número, opcionalmente de acuerdo con el formato “fmto”.
select to_number('12345') from dual;
124345Resultado
sábado, 8 de abril de 2023
19
FUNCIONES DE CONVERSION
Instrucciones DML – Conversiòn Datos Explisitos
TO_DATE(cad,fmto)
Convierte la cadena “cad” de tipo varchar2 a fecha, opcionalmente de acuerdo con el formato “fmto”.
select to_date('1-JAN-97') from dualSELECT GREATEST(1-JAN-97,’05/05/2006’) ) FROM DUAL ;
01-JAN-97Resultado
sábado, 8 de abril de 2023
20
FUNCIONES DE CONVERSION
Instrucciones DML – Conversiòn Datos Explisitos
TO_CHAR (d, fmto)
Convierte la fecha “d” a una cadena de caracteres, opcionalmente de acuerdo con el formato “fmto.”
select to_char(sysdate) from dual;
'14-MAR-97' Resultado
sábado, 8 de abril de 2023
21
Funciones de TO_CHAR( ) :
YYYY
YEAR
MM
Formato
Año completo en nùmero
Año en texto
Valor dos dígitos para el mes
Descripción
MONTH Nombre completo del mes
DY Abreviatura (3 letras)Dia
DAY Nombre Dia en texto
Instrucciones DML – Conversiòn Datos Explisitos
TO_CHAR con Fecha :TO_CHAR(fecha,’formato’)
sábado, 8 de abril de 2023
22
Función To_Char :
SQL> select to_char(hiredate,'MM/YYYY') from emp;
Ejemplo2:
Instrucciones DML - Funciones Predefinidas
SQL> select to_char(sysdate,'YYYY-MM-DD HH:MI:SS') fecha from dual;
sábado, 8 de abril de 2023
23
Funciones de Caracter
Utilizando la tabla emp.hiredate
sábado, 8 de abril de 2023 24
Funciones de Caracter
Pensar - Soluciòn:
Utilizar la tabla emp.hiredate
sábado, 8 de abril de 2023
25
Funciones de Caracter
Pensar - Soluciòn:
Utilizar la tabla emp.hiredate
sábado, 8 de abril de 2023
26
Funciones de TO_CHAR( ) :
9
0
$
Formato
Presenta un nùmero
Fuerza que O se muestre
Coloca signo de dolar
Descripción
To_Char con nùmeros
L Utiliza simbolo de moneda
. Imprime Simbolo Decimal
; Imprime indicador de miles
Instrucciones DML - Funciones Predefinidas
sábado, 8 de abril de 2023
27
Función TO_CHAR( ) :
SQL> SELECT TO_CHAR(SAL,'$99,999')Salario from emp where ename='SCOTT';
Ejemplo2:
Instrucciones DML - Funciones Predefinidas
To_Char con números
sábado, 8 de abril de 2023
28
Función NVL( ) :
Instrucciones DML - Funciones Predefinidas
Convierte un nulo a un valor que puede ser un número , un carácter o una fecha.
NVL(job,’no tiene cargo’)
select nvl(job,’no existe’) CARGOS from emp;
NVL(COMM,0)Select nvl(comm,0) COMISION from emp;
NVL(hiredate,’01-JAN-97’)select nvl(hiredate,’01-JUN-97’) CONTRATOS from emp;
sábado, 8 de abril de 2023
29
Instrucciones DML - Funciones Predefinidas
Función DECODE( ) :
Envia un resultado dependiendo de el valor de una variable.
SQL>select decode(user,'SCOTT','USTED ES SCOTT','SYS','ES ADMINISTRADOR','NO ES CONOCIDO') RESULTADO FROM DUAL;
USTED ES SCOTTRESULTADO
sábado, 8 de abril de 2023
30
FUNCIONES DE GRUPO
Instrucciones DML – Funciones de Grupo
AVG()
MAX()
MIN ()
SUM()
STDDEV
VARIANCE
sábado, 8 de abril de 2023
31
SELECT [ column, ] group_function (column)
FROM table
[ WHERE condition ]
[ GROUP BY column ]
[ ORDER BY column ] ;
Estas funciones operan en grupos de registros para entregar un resultado por grupos. Estas funciones son:
Instrucciones DML – Funciones de Grupo
FUNCIONES DE GRUPO
sábado, 8 de abril de 2023
32
AVG (Select avg(sal) from emp;)
Funciones Grupo :
COUNT (Select count(ename) from emp;
MAX (Select count(ename) from emp;
Función
Promedio
Cuenta
Resultado
Máximo
MIN (Select min(sal) from emp; Minimo
Instrucciones DML - Funciones Predefinidas
Operan en grupos de registros y retornan grupos de registros.
SUM (Select sum(sal) from emp; Suma
sábado, 8 de abril de 2023
33
Error Comùn :
Instrucciones DML – Funciones de Agrupamiento
ORA-00979: NOT A GROUP BY EXPRESSIONORA-00979: NOT A GROUP BY EXPRESSION
Esto debido al modo que tiene ORACLE de analizar las consultas agrupadas: lo que hace es comprobar que todos las columnas incluidos en la cláusula select fuera de funciones sumarias, estén dentro de la clàsula group by.Si encuentra alguna columna en el select( que no este dentro de una funciòn sumaria) que no aparezca en el group by, entonces nos retorna el error anterior.
sábado, 8 de abril de 2023
34
Ejemplo 1:
SQL> SELECT deptno, AVG(sal) FROM emp
GROUP BY deptno ;
DEPTNO AVG(SAL)------ ---------- 10 2916.66666666667 20 2175 30 1566.66666666667
Instrucciones DML – Funciones de Grupo
Mostrar el promedio de los salarios agrupados por departamento
Resultado
sábado, 8 de abril de 2023
35
Ejemplo 2:
SQL> SELECT deptno, job, sum(sal) FROM emp
GROUP BY deptno, job ;DEPTNO JOB SUM(SAL)----------- --------------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600
Instrucciones DML – Funciones de Grupo
Mortar la sumatoria de salarios agrupado por departamento y por cargo.
Resultado
sábado, 8 de abril de 2023
36
ROLLUP, CUBE
Estos comandos se utilizan
para totalizar los grupos de
Datos. Solamente una porciòn
de combinaciones
Ejemplo:
SQL> SELECT deptno, job,
sum(sal) FROM emp
GROUP BY
ROLLUP(deptno,job) ;
DEPTNO JOB SUM(SAL)------ --------------- ------------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 20 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 30 9400 2902529025
Instrucciones DML – Funciones de Grupo
sábado, 8 de abril de 2023
37
ROLLUP, CUBE
Ejemplo:
SQL> SELECT deptno, job,
sum(sal) FROM emp
GROUP BY
CUBE (deptno, job) ;
DEPTNO JOB SAL------ --------------- ----------
2902529025
CLERK 4150ANALYST 6000MANAGER 8275SALESMAN 5600PRESIDENT 5000
10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 20 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 30 9400
Instrucciones DML – Funciones de Grupo
Retorna el total de todas las
Combinaciones posibles
(Referecia Cruzada)
Ejemplo:
sábado, 8 de abril de 2023
38
SINTAXIS:
SELECT [ column, ] group_function (column)
FROM table
[ WHERE condition ]
[ GROUP BY column ]
[ HAVING column ]
[ ORDER BY column ] ;
Se utiliza HAVING para excluir resultados de grupos.
HAVING
Instrucciones DML – Funciones de Grupo
sábado, 8 de abril de 2023
39
Ejemplo :Ejemplo :
SQL> SELECT deptno, max(sal) FROM emp
GROUP BY deptno HAVING max(sal) >2900 ;SQL> SELECT deptno, max(sal) FROM emp
GROUP BY deptno HAVING max(sal) >2900 ;
DEPTNO MAX(SAL)------ ---------- 10 5000 20 3000 50 3000
DEPTNO MAX(SAL)------ ---------- 10 5000 20 3000 50 3000
Instrucciones DML – Funciones de Grupo
sábado, 8 de abril de 2023
40
Funciones de Grupo :
Instrucciones DML - Funciones de Grupo
SQL> select deptno ,avg(sal) from emp Group by deptno;
SQL> select deptno, job, sum(sal) from emp group by deptno, job ;
sábado, 8 de abril de 2023
41
Fin de la Sesión