UNIVERSIDAD CENTRAL DEL ECUADOR FACULTAD DE INGENIERIA ESCUELA DE CIENCIAS
ORACLE MATERIA BASE DE DATOS II CECILIA TULCÁN
2009-2010 Ing. Jorge Gordillo
Contenido Contenido ................ ........................... ................ ..... ....................... ................................ ......... .................... ................................ ............ ..................... ............................... ............ 1 CARACTERISTICA CARACTERISTICAS S DE UNA BASE DE DATOS DATOS ............... ......................... ................. ....... ..................... ............................... ............ .............. .............. 2 Definición Definición de base de datos..................... ................................ ........... .................... .............................. ............ .. ....................... ................................ ......... .. 2 Características Características.................... .............................. ............ .. ...................... ................................ .......... .................... ................................ ............ .................... ...................... .. 2 Sistema Sistema de Gestión de Base de Datos (SGBD) (SGBD) ..................... ............................... ............ .................... ................................ ............ ....... 2 TIPOS DE RELACIONES RELACIONES ............... ......................... ................. ....... ..................... ............................... ............ ....................... ................................ ......... .............. .............. 3 Relación Relación identificadora identificadora y no identificadora identificadora...................... ................................ .......... .................... ................................ ............ ........... ........... 3 GRADOS GRADOS DE NORMALIZAC NORMALIZACION ION ........................ ................................ ........ .................... .............................. ............ .. ....................... ................................ ......... .. 3 PRIMERA FORMA NORMAL ......................... ................................ ....... .................... .............................. ............ .. ........................ ................................ ........ .. 3 SEGUNDA SEGUNDA FORMA NORMAL NORMAL .................... ................................ ............ ..................... ............................... ............ ....................... ................................ ......... . 4 TERCERA FORMA FORMA NORMAL NORMAL .............. ........................ .................. ........ .................... .............................. ............ .. ....................... ................................ ......... ... 4 MANIPULACIO MANIPULACION N DE DATOS........... DATOS..................... ..................... ........... .................... ............................... ............. ........................ ................................ ........ ....... 4 Tipos de sentencias sentencias SQL ........................ ................................ ........ .................... .............................. ............ .. ........................ ................................ ........ ....... 4 Sentencias Sentencias de definición de datos (DDL) ...................... ................................ .......... ..................... ................................ ........... ........... 4 Sentencias Sentencias de manipulación de datos (DML)...................... ................................ .......... ..................... ................................ ........... ..... 5 FUNCIONES FUNCIONES DE AGREGACION AGREGACION ............... .......................... ................. ...... ..................... ............................... ............ ....................... ................................ ......... ... 8 USO DE LA CLAUSULA CLAUSULA GROUP BY............................. BY................................ ... ..................... ............................... ............ ....................... ............................ ..... 10 USO DE LA CLAUSULA CLAUSULA HAVING .................... ................................ ............ .................... ............................... ............. ........................ ............................... ....... 13 SELECT ANIDADOS ANIDADOS..................... ............................... ............ ...................... ................................ .......... .................... ................................ ............ .................. .................. 15 JOINS ...................... ................................ .......... ........................ ................................ ........ ..................... ............................... ............ .................... .............................. ............ .. ..... 21 JOIN EQUIJOINS EQUIJOINS ................ .......................... ................ ...... ...................... ................................ .......... .................... ................................ ............ .................. .................. 21 SELECT SINCRONIZADO SINCRONIZADOS.......... S.................... .................... ............ .. ..................... ............................... ............ ....................... ................................ ......... ......... 23
CARACTERISTICAS DE UNA BASE DE DATOS Definición de base de datos Se define una base de datos como una serie de datos organizados y relacionados entre sí, los cuales son recolectados y explotados por los sistemas de información de una empresa o negocio en particular.
Características Entre las principales características de los sistemas de base de datos podemos mencionar: Independencia lógica y física de los datos. Redundancia mínima. Acceso concurrente por parte de múltiples usuarios. Integridad de los datos. Consultas complejas optimizadas. Seguridad de acceso y auditoría. Respaldo y recuperación. Acceso a través de lenguajes de p rogramación estándar. y y y y y y y y
Sistema de Gestión de Base de Datos (SGBD) Los Sistemas de Gestión de Base de Datos (en inglés DataBase Management System) son un tipo de software muy específico, dedicado a servir d e interfaz entre la base de datos, el usuario y las aplicaciones que la utilizan. Se compone de un lenguaje de definición de datos, de un lenguaje de manipulación de datos y de un lenguaje de consulta.
TIPOS DE RELACIONES Relación identificadora y no identificadora Relaciones Identificadores
Las llaves primarias de la entidad origen pasa a formar parte de la entidad destino. Se representa con línea continua. Las llaves primarias tienen algunos atributos y al relacionarse con otra entidad la llave primaria sigue amentando. Cuando usamos relaciones identificadoras las llaves primarias se van haciendo más grandes.
Relaciones no Identificadoras
Las llaves primarias de la entidad origen no pasan a formar parte de la entidad destino. Se representa con línea punteada. Las llaves primarias solo tienen un atributo.
GRADOS DE NORMALIZACION Existen básicamente tres niveles d e normalización: Primera forma normal, segunda forma normal, tercera forma normal. Cada una de estas formas tiene sus propias reglas. Cuando una base de datos se conforma a un nivel, se considera normalizada a esa forma de normalización. Por ejemplo una base de datos cumple con todas las reglas del segundo nivel de normalización, se considera que la base de datos está en la segunda forma normal. No siempre es buena idea tener una base de datos conformada en el nivel más alto de normalización, esto puede llevar a un nivel de complejidad que pudiera ser evitado si estuviera en un nivel más bajo de normalización. En general el nivel apropiado de normalización dependerá del diseño de cada base de d atos.
PRIMERA FORMA NORMAL La primera forma normal establece que las columnas repetidas deben eliminarse y colocarse en tablas separadas, esta es una r egla muy fácil de seguir. Como un ejemplo podríamos considerar el caso en que se tiene una tabla correspondiente a los clientes de una empresa en donde se almacenan datos del cliente y datos correspondientes a los productos que compro el cliente en este cas o habrían que definir varios campos correspondientes a producto1, producto2, producto3, así la tabla contendría varias columnas repetidas por los que tendríamos que crear una tabla aparte para los productos.
S
DA F
A
A
Una r aci n est en 2FN si est en 1FN y si los atri uto s que no for an part e de ninguna clave dependen de fo r a complet a de la clave principal. Es decir que no exist en dependencias parciales.
En otr as palabras podríamos decir que la segunda forma normal est basada en el concept o de dependencia complet ament e f uncional. Una dependencia f uncional es complet ament e f uncional si al eliminar los atribut os de X signif ica que la dependencia no es mant enida, est o es que X, (X { }) -x-> Y. Una dependencia f uncional es una dependencia parcial si hay algunos atribut os que pueden ser eliminados de X y la dependencia t odavía se mantiene, est o es X, (X { }) -> Y. Por ejemplo {DNI, ID_PROYECTO} HOR S_TR B JO (con el DNI de un empleado y el ID de un proyect o sabemo s cu nt as horas de tr abajo por semana trabaja un empleado en dicho proyect o) es complet ament e dependient e dado que ni DNI HOR S_TR B JO ni ID_ PROYECTO HOR S_TR B JO mantienen la dependencia. Sin embargo {DNI, ID_PROYECTO} NOMBRE_EMPLEADO es parcialment e dependient e dado que DNI NOMBR E_EMPLEADO mantiene la dependencia.
A F
A
A
Una relaci n est a en 3FN si est a en 2FN y ademá s se debe eliminar y separar cualquier dat o q ue no sea clave. Todos los valo res deben identif icarse únicament e por la clave.
A
P
ip
d
A
D DA ci
S
SQ
Sentencias de definición de datos (DDL)
Un lenguaje de def inici n de dat os (Dat a Def initionLanguage, DDL por sus siglas en inglés) es un leng uaje proporcionado por el sist ema de gesti n de base de dat os que permit e a los usuarios de la mis ma llevar a cabo las t areas de def inici n de las estructuras que almacenarán lo s dat os así como de los procedimient os o f unciones que permit an consult arlos.
El lenguaje de programaciónSQL, admite las siguientes sentencias de definición: CREATE, DR P y ALTER, cada una de las cuales se puede aplicar a las tablas , vistas , procedimientos almacenados y triggers de la base de datos.
Sentencia DDL Alter procedure Alter table
Createtable Createindex Droptable Dropindex
Objetivo Recompilar un procedimiento almacenado Añadir o redefinir una columna, modificar la asignación de almacenamiento de una tabla Crear una tabla Crear un índice Eliminar una tabla y su contenido Eliminar un índice
Otras que se incluyen dentro del DDL, son GRANT y REVOKE, los cual es sirven para otorgar permisos o quitarlos, ya sea a usuarios específicos o a un rol creado dentro de la base de datos.
Sentenci
e manipulaci n de datos (DM L
DML son las siglas d e Data ManipulationLanguage y se refiere a los comandos que permiten a un usuario manipular los datos en un repositorio, es decir, añadir, consultar, borrar o actualizar. En SQL los comandos SELECT, INSERT, UPDATE y DELETE son comandos DML.
SELECT Utilizado para consultar registros de una base datos que s atisfagan un criterio determinado. INSERT Utilizado para insertar registros en una tabla. UPDATE Utilizado para modificar los valores de los campos y registros en tabla. DELETE Utilizado para borrar registros de una tabla. Otras órdenes como: COMMITo ROLLBACK, las cuales sirven para validar o deshacer los cambios dentro de una transacción, están en el límite de lo que podemos considerar órdenes DML de SQL puesto que no inciden en la manipulación o consulta de datos pero sí en el proceso en que esto se realiza. Ejemplo: 1. Cree la tabla provincia con los siguientes atributos: Cod_prov, nom_prov, desc_prov. CREATE TABLE PROVINCIA ( COD_PRO NUMBER NOT NULL, NOM_PRO VARC AR2(20), DESC_PROV VARC AR2(20), CONSTRAINT PROVINCIA_PK PRIMAR KEY (COD_PRO ));
2. Cree la tabla estudiante con los siguientes atributos: Cod_estudiante, cod_prov, nom_estudiante, telf_estudiante. CREATE TABLE ESTUDIANTE ( COD_ESTUDIANTE NUMBER NOT NULL, COD_PRO NUMBER, NOM_ESTUDIANTE VARC AR2(20), TELF_ESTUDIANTE VARC AR2(20), CONSTRAINT PROVINCIA_PK PRIMARY KEY (COD_ESTUDIANTE ) ); Para establecer la relación de uno a muchos de la tabla provincia hacia estudiante utilizamos la siguiente instrucción SQL: ALTER TABLE ESTUDIANTE ADD ( FOREKIGN KEY (COD_PRO) REFERENCES PROVINCIA); 3. Inserte datos en las tablas provincia y estudiantes haciendo uso de la cláusulainsert. INSERT INTO provincia (cod_pro, nom_pro, desc_prov) VALUES (001,'PIC INC A',''); INSERT INTO provincia (cod_pro, nom_pro, desc_prov) VALUES (002,'GUAYAS',''); INSERT INTO ESTUDIANTE (cod_estudiante, cod_pro, nom_estudiante, telf_estudiante) VALUES (101,001,'PEDRO','09876543'); INSERT INTO ESTUDIANTE (cod_estudiante, cod_pro, nom_estudiante, telf_estudiante) VALUES (102,001,'JUAN','098888098'); INSERT INTO ESTUDIANTE (cod_estudiante, cod_pro, nom_estudiante, telf_estudiante) VALUES (103,002,'ANDRES','0977777098'); 4. Actualice el número de teléfono del estudiante con código 103 update estudiante settelf_estudiante='11111111' wherecod_estudiante=103; 5. Borre todos los datos de los estudiantes de nombre Andrés delete * from estudiante wherenom_estudiante=ANDRES;
Ejercicios 1. Escribir la instrucción SQL que presente en pantalla todos los datos de los empleados que ganan entre 1000 y 2500 y que trabajan en el departamento 10 y 30. Select * from emp where sal BETWEEN 1000 and 2500 and (deptno=10 ordeptno=30); Observación: Nótese que la presencia de paréntesis en la clausula and es necesaria puesto que esta tiene mayor precedencia que la cláusulaor. La consulta anterior se puede realizar también usando la clausula in que funciona de forma similar a la cláusulaor. select * from emp where sal BETWEEN 1000 and 2500 anddeptno in(10,30);
2. Repetir la misma instrucción anterior pero presentar los datos ordenados en forma ascendente por departamento y d escendente por salario. select * from emp where sal BETWEEN 1000 and 2500 anddeptno in(10,30) order by deptnoasc, saldesc;
Nota: La palabra asc de as cendente puede ser omitida puesto que es el valor por defecto.
3. Escribir la instrucción SQL que presente en pantalla el nombre del empleado, la comisión y valor total que gana entre salario y comisión cada empleado. Select ename, sal, comm, sal+nvl(comm,0) from emp;
FUNCIONES DE AGREGACION Avg() Devuelve un valor que es el promedio de todos los valores válidos y que no estén en blanco de campo. y
Selectavg(sal) from emp; AVG(SAL) ---------------------2073,214285714285714285714285714285714286
Max() Devuelve el valor válido más alto del campo. y
select max (sal) from emp; MA (SAL) ---------------------5000
Min() Devuelve el valor válido más pequeño y que no esté en blanco de campo. y
select min (sal) from emp; MIN(SAL) ---------------------800
Count() Devuelve el número de valores válidos y que no estén en blanco de campo. y
select count (*) from emp; COUNT(*) ---------------------14
Sum() Devuelve el total de valores válidos y que no estén en blanco de campo. y
select sum (sal) from emp; SUM(SAL) ---------------------29025 y
Variance()
Devuelve la varianza de una muestra representada por una serie de valores que no están en blanco de campo. select variance (sal) from emp; VARIANCE(SAL) ---------------------1398313,87362637362637362637362637362637
Stddev() Devuelve la desviación estándar de una muestra representada por una serie de valores que no están en blanco de campo. y
Selectstddev (sal) from emp; STDDEV(SAL) ---------------------1182,503223516271699458653359613061928508
USO DE LA CLAUSULA GROUP BY Agrupa los registros similares siempre y cuando sean del mismo atributo. En el groupby van todos los atributos que estén en el select y que no tengan ninguna función que tenga una aplicación de agregación. Ejemplo: Escribir la instrucción SQL que calcule el sueldo promedio que ganan los empleados pero sin usar la función avg(). select sum(sal)/count(empno) from emp; SUM(SAL)/COUNT(EMPNO) ---------------------2073,214285714285714285714285714285714286 Observación: Selectdeptnofromemp; 14 registros Select sum(sal) from emp; 1 registro Selectdeptno, sum(sal) fromemp; Se produce un error (no es posible match)
Sin función de agregación
Función de agregación
Para evitar este inconveniente se debe hacer uso de la funciónGroupby , en esta función deben ir al menos todos los atributos que no contienen función de agregación .(Pueden ir mas) De esta manera la instrucción anterior debe ser escrita de la siguiente manera: Select deptno, sum(sal) from emp group by deptno; DEPTNO SUM(SAL) ---------------------- ---------------------30 9400 20 10875 10 8750 Esta instrucción muestra el total de salarios que se p aga en cada departamento.
Ejercicios: 1. Escribir la instrucción SQL que presente en pantalla la suma total de salarios, el sueldo mínimo, el sueldo máximo y el sueldo promedio que se paga en cada uno de los departamentos. Select deptno, sum(sal), min(sal),max(sal),avg(sal) from empgroup by deptno;
2. Escribir la instrucción SQL que presente en pantalla el número de empleados que existe por departamento y el ingreso total que reciben los empleados de cada departamento durante un año. Selectdeptno, count(*), sum(sal)*12 Fromemp group by deptno;
DEPTNO SUM(SAL)*12 COUNT(*) ---------------------- ---------------------- ---------------------30 6 112800 20 5 130500 10 3 105000 3. Escribir la instrucción SQL que presente en pantalla el número d e personas que desempeña cada uno de los trabajos en la empresa. select job, count(*) from emp group by job; JOB COUNT(*) --------- ---------------------CLERK 4 SALESMAN 4 PRESIDENT 1 MANAGER 3 ANALYST 2
4. Cuantos empleados existen en cada departamento y que desempeñen un mismo trabajo. Select deptno,job, count(*) from emp group by deptno, job; DEPTNO JOB COUNT(*) ---------------------- --------- ---------------------20 2 CLERK 30 SALESMAN 4 20 MANAGER 1 30 1 CLERK 10 PRESIDENT 1 30 MANAGER 1 10 CLERK 1 10 MANAGER 1 20 2 ANALYST 5. Escribir la instrucción SQL que presente en pantalla la desviación estándar del salario que reciben los empleados. Select stddev(sal) from emp; STDDEV (SAL) ---------------------1182,503223516271699458653359613061928508
6. Escribir la instrucción SQL que presente en pantalla el número de empleados que realice el trabajo de obrero(CLERK) en cada uno de los departamentos. Select deptno, count (*) From emp Where job='CLERK' Group by deptno; DEPTNO COUNT (*) ---------------------- ---------------------30 1 20 2 10 1
USO DE LA CLAUSULA HA VING Trabaja sobre una tabla temporal que se crea cuando se utiliza el groupby es igual que el where y solo se utiliza cuando utilizamos groupby. En el where no se puede utilizar funciones de agregación ya que trabaja en la tabla original mientras que con having se trabaja en la tabla temporal por lo que se puede utilizar cualquier función de agregación Ejemplo: Escribir la instrucción SQL que presente en pantalla el número de empleados que tiene cada departamento de aquellos departamentos que tienen más de 4 empleados. Selectdeptno, count(*) Fromemp group by deptno having count(*)>4; DEPTNO COUNT (*) ---------------------- ---------------------30 6 20 5 Observación: La instrucción SQL anterior sin la cláusulahaving: Selectdeptno, count(*) Fromemp group by deptno; DEPTNO COUNT(*) ---------------------- ---------------------30 6 20 5 10 3 Esta consulta crea una tabla temporal con el número de empleados de cada departamento, para filtrar datos o presentar datos de esta tabla temporal que cumplan una determinada condición se usa la cláusulahaving, como se puede apreciar en el ejercicio 7, en donde se usa la cláusulahaving presentando solo los departamentos que tienen más de 4 empleados. Con la cláusulahaving se puede hacer uso de cualquiera de las funciones de agregación vistas anteriormente.
La cláusulawhereactúa de igual manera filtrando datos pero que están sobre la tabla original, con la cláusulawhere no podemos hacer uso de funciones de agregación. Ejercicios 1. Escribir la instrucción SQL que presente en pantalla el número de obreros (CLERK) que existe por departamento de todos aquellos departamentos que tengan más de 1 obrero. Selectdeptno,job, count(*) fromemp where job='CLERK' group by deptno, job having count(*)>1; DEPTNO JOB COUNT(*) ---------------------- --------- ---------------------20 CLERK 2 2. Escribir la instrucción SQL que presente en pantalla el ingreso total mensual que ganan los empleados de cada departamento de aquellos departamentos que ganan más de 10000 dólares. Selectdeptno,sum (sal+nvl(comm,0)) fromemp group by deptno having sum (sal+nvl(comm,0))>10000; DEPTNO SUM(SAL+NVL(COMM,0)) ---------------------- ---------------------30 11600 20 10875 3. Escribir la instrucción SQL que presente en pantalla el ingreso total mensual que ganan los empleados de cada departamento de aquellos departamentos que ganan más de 5000 dólares considerando solamente a los manager, presidentes, salesman. Selectdeptno,sum (sal+nvl(comm,0)) Fromemp where job in ('MANAGER','PRESIDENT','SALESMAN') group by deptno having sum (sal+nvl(comm,0))>5000; DEPTNO SUM(SAL+NVL(COMM,0)) ---------------------- ---------------------30 10650 10 7450
SELECT ANIDADOS Los select simplemente anidados trabajan desde el select interno hasta el select externo. Los select internos pasan los datos a los select externos donde el select externo es ejecutado. En el where se hace operaciones con datos similares es decir tienen que ser del mismo tipo. Ejemplo: Escribir la instrucción SQL que presente en pantalla todos los datos de los empleados que trabajan en la ciudad de DALLAS. En este caso se puede realizar la consulta en dos pasos: y
Obteniendo primero el deptno del departamento de l a ciudad de DALLAS:
Select deptno from dept where loc= 'DALLAS'; DEPTNO ---------------------20 Seleccionamos los empleados que están asociados con este deptno: y
Select * from emp wheredeptno=20;
La consulta anterior puede ser realizada en un solo paso: Select * from emp wheredeptno=(select deptno from d eptwhere loc='DALLAS');
A este tipo de consulta se le denomina con select anidado puesto que se utiliza el resultado que devuelve un selectpara utilizarlo con otro en una misma consulta.
Ejercicios: 1. Escribir la instrucción SQL que presente en pantalla todos los datos de los empleados que desempeñan el mismo trabajo que realizan los empleados del departamento 10. select * from emp where job in (select job from empwhere deptno=10);
2. Escribir la instrucción SQL que presente en pantalla todos los datos de los empleados de aquellos departamentos que tienen más de 4 empleados. select * from emp wheredeptno in (select deptno from emp group by deptno having count(*)>4);
Nota: Los select se ejecutan desde el mas interno al más externo
3. Escribir la instrucción SQL que presente en pantalla todos los datos de aquellos empleados que ganan el máximo salario de la empresa. Select * from emp Where sal in (select max(sal) from emp);
4. Escribir la instrucción SQL que presente en pantalla todos los datos del empleado que es el más nuevo de la empresa.
Select * fromemp Where hiredate in (select max(hiredate) from emp);
5. Escribir la instrucción SQL que presente en pantalla todos los datos del empleado que es el más antiguo de la empresa. Select * fromemp Where hiredate in (select min(hiredate) from emp);
6. Escribir la instrucción SQL que presente en pantalla todos los datos de los empleados que ganan un salario superior al salario promedio de la empresa. select * from emp wheresal> (select avg(sal) from emp);
7.
Escribir la instrucción SQL que presente en pantalla todos los datos de los empleados que ganan más que todos los empleados del departamento 20 individualmente.
select * from emp wheresal> (select max(sal) from emp wheredeptno=20); Otra forma de obtener el mismo resultado: select * from emp wheresal>all (select sal from emp wheredeptno=20);
8. Escribir la instrucción SQL que presente en pantalla todos los datos de los empleados que ganan un salario mayor a cualquiera de los salarios ganan los empleados del departamento 10. select * from emp wheresal> (select min(sal) from emp wheredeptno=10); Otra forma de obtener el mismo resultado: select * from emp wheresal>any (select sal from emp wheredeptno=10);
Nota: y
La expresión all es equivalente a un and puesto que debe cumplirse la condición para todos los datos que devuelve una consulta, en el ejemplo anterior ejercicio 7:
select * from emp wheresal>all (select sal from emp wheredeptno=20); El select interno: Selectsal from emp wheredeptno=20 devuelve como resultados: SAL ---------------------800 2975 3000 1100 3000 Al hacer uso de la cláusulaall el select externo sería equivalente a: Sal>800 and sal>2975 and sal>3000 and sal>1000 and sal>3000 Y se presentan los datos de los empleados que tienen un salario mayor a todos los salarios que devuelve el select interno en este caso los salario del deptno=20. y
De manera similar la cláusulaany es equivalente a la cláusulaor.
9. Escribir la instrucción SQL que presente en pantalla todos los datos de aquellos empleados que ganan el máximo salario de la empresa. Select * from emp wheresal in (select max(sal) from emp); 10. Escribir la instrucción SQL que presente en pantalla todos los datos del empleado que es el más nuevo de la empresa. Select * from emp wherehiredate in (select max(hiredate) from emp);
11. Escribir la instrucción SQL que presente en pantalla todos los datos del empleado que es el más antiguo de la empresa. Select * from emp wherehiredate in (select min(hiredate) from emp); 12. Escribir la instrucción SQL que presente en pantalla todos los datos de los empleados que ganan un salario superior al salario promedio de la empresa. Select * from emp wheresal>(select avg(sal) from emp); 13. Escribir la instrucción SQL que presente en pantalla todos los datos de los empleados que ganan más que todos los empleados del departamento 20 individualmente. Select * from emp Where sal>(select max (sal) from emp wheredeptno=20);
Otra forma: Select * from emp Where sal> all (select sal from emp wheredeptno=20); 14. Escribir la instrucción SQL que presente en pantalla todos los datos de los empleados que ganan un salario mayor a cualquiera de los salarios que ganan los empleados del d epartamento 10. Select * from emp wheresal>(select min(sal) from emp wheredeptno=10);
Otra forma: Select * from emp wheresal> any (select sal from emp wheredeptno=10);
15. Escribir la instrucción SQL que presente en pantalla todos los datos de los empleados que ganan el máximo salario en cada uno de sus departamentos. Select * from emp Where (detpno,sal) in (select deptno, max(sal) from emp groupbydeptno);
NOTA: La consulta anterior es válida únicamente en Oracle, pero existe una forma general para obtener el mismo resultado que es válida en cualquier base de datos, a esto se le conoce con el nombre de SELECT SINCRONIZADO, a continuación se da el formato de esta consulta haciendo uso de este tipo select, únicamente como una introducción, pues más adelante se estudiara en detalle este tipo de consultas sincronizadas. select * from emp x where sal in (select max(sal) from emp e where e.deptno = x.deptno);
JOINS JOIN E UIJOINS Este tipo de join el más usado, se usa para unir dos o más tablas y presentar los registros relacionados entre sí.Para este tipo de join es necesario colocar en la cláusulawhere de la consulta al menos n-1 condiciones,donde n es el número de tablas que se relacionan. Ejercicios. 1. Escribir la instrucción SQL que presente en pantalla todos los datos de los empleados y de sus d epartamentos. select * from emp e, d ept d where e.deptno=d.deptno;
NOTA: Se puede hacer uso de alias para las tablas, como en el ejemplo anterior, esto facilita la escritura y simplicidad de la consulta. Una vez que se hace uso de un alias ya no se puede hacer uso del nombre de la tabla, en adelante se usa solamente el alias, en caso contrario se producirá un error en la ejecución. Como una acotación más en el ejemplo anterior se usó el * para indicar que se seleccionen todos los datos, en este caso como se trata de un join se seleccionan todos los datos de las tablas que se encuentran en el from es decir de la tabla emp y dept. Si se desea seleccionar únicamente los datos de una tabla se debería escribir por ejemplo e.* que mostraría todos los datos de la tabla emp únicamente. 2. Escribir la instrucción SQL que presente en pantalla el código del empleado, el nombre del empleado, el ingreso total que recibe cada empleado y el nombre del departamento al que pertenece de todos los empleado del departamento 20 y 30. Selectempno, ename, sal+nvl(comm,0), dname Fromemp e, dept d where e.deptno=d.deptno ande.deptno in (20,30);
3. Escribir la instrucción SQL que presente en pantalla el nombre del departamento y el salario total que se paga por departamento. selectdname, sum(sal+nvl(comm,0)) fromemp e, dept where e.deptno=d.deptno groupbydname; 4. Escribir la instrucción SQL que presente en pantalla todos los datos de los empleados que trabajan en la ciudad de DALLAS. select * from emp e, d ept d where e.deptno=d.deptno andd.loc='DALLAS'; 5. Escribir la instrucción que presente en pantalla el nombre del departamento, el número de empleados y el ingreso total que reciben los empleados de cada departamento. Selectdname, count(*), sum(sal+nvl(comm,0)) fromemp e, dept d where e.deptno=d.deptno groupbydname; 6. Escribir la instrucción SQL que presente en pantalla el nombre del departamento, el salario promedio del departamento de todos aquellos departamentos que tienen más de 4 empleados. Selectdname, count(*), avg(sal+nvl(comm,0)) fromemp e, dept d where e.deptno=d.deptno group by dname having count(*)>4; 7. Escribir la instrucción SQL que presente en pantalla todos los datos de los empleados más el nombre del departamento en el que trabaja, de aquellos empleados que trabajan en el departamento de contabilidad y que son gerentes. select e.*, dname from emp e, dept d where e.deptno=d.deptno anddname='ACCOUNTING' andjob='MANAGER';
8. Escribir la instrucción SQL que presente en pantalla todos los datos de los empleados más el nombre del departamento y la localización del departamento de todos aquellos empleados que trabajan en el departamento 10 o en el departamento 30 y que tienen un ingreso total anual superior a 30000 dólares. select e.*,d.dname, d.loc from emp e, dept d where e.deptno=d.deptno ande.deptno in (10,30) and (e.sal + nvl(e.comm,0))*12 >30000; 9. Escribir la instrucción SQL que presente en pantalla todos los datos de los empleados más el nombre del departamento de todos aquellos empleados que ganen un salario superior al salario promedio de la empresa. select e.*, d.dname from emp e, dept d where e.deptno=d.deptno andsal>(select avg(sal) from emp);
NOTA: Para obtener en la consulta el salario promedio de la empresa se p uede colocar un select anidado. select e.*, d.dname, (select avg(sal) from emp) salario_promedio fromemp e, dept d where e.deptno=d.deptno andsal>(select avg(sal) from emp);
SELECT SINCRONIZADOS Llamados también select anidados correlacionados, el select anidado interno hace referencia a la tabla de afuera es decir:
El select interno necesita del dato ext erno El select externo necesita d el dato interno
Los select correlacionados no se ejecutan solos es decir el select interno depende del select externo asi como el select externo depende del select interno a diferencia de los select simplemente anidados se pueden ejecutar por si solos.
Ejercicios 1. Escribir la instrucción SQL que presente en pantalla todos los datos de los empleados más el nombre del departamento de todos aquellos empleados que ganan el salario máximo de su departamento. select e.*, dname from emp e, dept d where e.deptno=d.deptno andsal =(select max(sal) from emp wheredeptno=e.deptno); 2. Escribir la instrucciónsql que presente en pantalla todos los datos de los empleados más el nombre del departamento de aquel departamento que tenga el mayor número de empleados. Esta consulta función únicamente en el RGBD ORACLE: Select deptno from emp Group by deptno Having count(*)>= all(Select cont(*) from emp Groupbydeptno) Funciona para todas las demas bases de d atos: Select e.* d.dname from emp e, dept d Where e. deptno=d.deptno And e.deptno=(Select deptno from emp Group by deptno Having count(*)>= all(select count(*)from emp Groupbydeptno)) 3. Escribir la instrucción sql que presente en pantalla todos los datos de los empleados más el nombre del departamento del empleado más antiguo y del empleado más nuevo. Select e.* from empe,dept d Where e.deptno=d.deptno And empno in (select empno from emp Where hiredate=(select max (hiredate) from emp) Or hiredate = (select min (hiredate) from emp)); Otra forma de hacer: Select e.* d.dname From empe ,dept d Where e.deptno=d.deptno And hiredate in (select max(hiredate)from emp),( s elect min(hiredate)from emp));
4. Escribir la instrucción sql que presente en pantalla todos los datos del departamento más el valor total de sueldos que se pagó por departamento de aquel departamento en el que se pagó el mayor valor total de sueldos. Select * from dept Where deptno in (select d eptno from emp Group by deptno Having sum(sal)>=all(select sum(sal) from emp groupbydeptno)); El mismo ejercicio anterior pero que la respuesta salga el total del salario. Select e.deptno,d.dname,d.loc,sum(e.sal) from deptd,emp e Where e.deptno=d.deptno Group by e.deptno,d.dname Having e.deptno in (select deptno from emp Group by deptno Having sum(sal)>=all(select sum(sal) from emp groupbydeptno));
5. Escribir la instrucción sql que presente en pantalla todos los datos del empleado más el nombre del departamento de todos aquellos empleados que tienen un ingreso total anual superior al ingreso total anual de cualquiera de los empleados del departamento 20. Select e.*, d.dname from emp e, dept d Where e.deptno = d.deptno And (sal+nvl(comm,0))*12 > any (select (sal+nvl(com,0))*12 from emp Where d eptno=20); 6. Escribir la instrucción sql que presente en pantalla todos los datos del empleado y todos los datos del departamento de todos aquellos empleados que trabajan en la ciudad de NEY YORK y que desepeñan el trabajo de obrero, Select e.* from emp e, dept d Where e.deptno=d.deptno And loc=NEY YORK And job = CLERK; 7. Escribir la instrucción sql que presente en pantalla todos los datos del empleado más el nombre del departamento de todos aquellos empleados que ganan una comisión superior al 20% de su salario. Select e.*, d.dname from emp e, dept d Where e.deptno = d.deptno And comm>(select sal*0.12 from emp x Where e.empno = x.empno);
Otra forma sin el uso de select sincronizados Select e.*, d.dname from emp e, dept d Where e.deptno = d.deptno And comm>sal*0.12;
