Consultas SQL y definiciones 1.-Crear una consulta que permita mostrar el apellido y salario de los empleados que ganan más de 12 000 select *from employees; SELECT e.last_name, e.salary FROM employees e where e.salary > 12000; 2.- Crear una consulta que permita mostrar el apellido y el número de departamento del empleado No. 176 select e.last_name , d.department_id from employees e , departments d where e.department_id = d.department_id and e.employee_id = 176;
3.-Crear una consulta que permita mostrar el apellido y salario de los empleados que su salario no está en el rango de 5000 y 12 000 select e.last_name, e.salary from employees e where not(e.salary>= 500 and 12000>=e.salary); 4.- Crear una consulta que permita mostrar el apellido, el código de trabajo (job_id), de los empleados que empezaron a laborar entre el 20 de febrero de 2003 y el 1 de mayo de 2005, ordénelo de forma ascendente por fecha select e.last_name , jb.job_id , e.hire_date from employees e, jobs jb where jb.job_id = e.job_id and (e.hire_date between '20/02/03' and '01/05/05') order by hire_date asc; 5.-Crear una consulta que permita mostrar el apellido y el número de departamento de los empleados de los departamentos 20 y 50, en orden alfabético select e.last_name, e.department_id from employees e where (e.department_id=20 or e.department_id=50) order by e.last_name;
6.- Muestre el apellido y la comisión (en soles) de los empleados; cuyos salarios se encuentren entre 5000 y 12 000 y además pertenezcan a los departamentos 20, 10 y 80 select last_name, commission_pct, salary, department_id from employees e where salary between 5000 and 12000 and e.department_id in(10,20,80); 7.-Muestre el nombre y apellido en una sola columna llamada nombre_completo y la fecha de contratación de los empleados que entraron en el año 2004. select e.first_name|| ' ' || e.last_name "Nombre Completo", e.hire_date from employees e where e.hire_date between '01-01-2004' and '31-12-2004'; 8.- Muestre el nombre y el código de trabajo de todos los empleados que no tienen un supervisor (manager_id). select first_name, last_name, job_id ,nvl(to_char(manager_id),'Sin supervisor') Supervisor from employees where manager_id is null; 9.-Muestre el apellido, salario y las comisiones de todos los empleados que ganan comisión, ordénelo de forma descendente los 2 campos select e.last_name, e.salary, e.commission_pct from employees e where e.commission_pct is not null order by e.last_name desc ; select e.last_name, e.salary, e.commission_pct from employees e where e.commission_pct is not null order by e.salary desc;
10.- Muestre el apellido de los empleados que tengan como tercera letra una a. select last_name from employees where last_name like '__a%'; 11.-Muestre el apellido de los empleados que tengan una a y una e en su apellido select e.last_name from employees e where (e.last_name like '%e%a%') or (e.last_name like '%a%e%') ; 12.- Muestre el apellido, código de trabajo y salario de los empleados que laboran como Sales representative (SA_REP) o Stock clerk (ST_CLERK) y que su salario no sea igual a 2500, 3000 o 7000. select last_name , job_id, salary from employees where (job_id like 'SA_REP' or job_id like 'ST_CLERK') and salary not in (2500,3000,7000); 13.-Construya una vista llamada sueldo_empleado que permita visualizar el código, nombre completo concatenado con el alias Nombre_completo a dicha columna, el sueldo total (incluyendo el porcentaje de su comision), solo para los empleados que tienen más de 5 años en la empresa.
CREATE VIEW SUELDO_EMPLEADO as SELECT CONCAT(employee_id,CONCAT(first_name, last_name)) AS NOMBRE_COMPLETO , (salary + salary*commission_pct/100) AS SALARIO_TOTAL FROM EMPLOYEES WHERE CEIL(MONTHS_BETWEEN(sysdate,EMPLOYEES.hire_date)) > 60; 14.-Construya una vista llamada datos_empleado que permita mostrar el código, nombre completo, correo electrónico, cargo, nombre del departamento y nombre del jefe de todos los empleados que fueron contratados en el mes actual.
CREATE VIEW DATOS_EMPLEADO as SELECT CONCAT(employee_id,CONCAT(e1.first_name, e1.last_name)), e1.email, e1.job_id, d.department_name, e2.first_name FROM (EMPLOYEES e1 INNER JOIN EMPLOYEES e2
ON CEIL(MONTHS_BETWEEN(sysdate, e1.hire_date)) <= 1 AND e1.manager_id = e2.employee_id), (DEPARMENTS d INNER JOIN EMPLOYEES e1 ON e1.deparment_id = d.deparment_id); 15.-Construya una vista llamada proyección que permita visualizar el código, apellido, monto a recibir mensual y el monto a percibir en el año para todos los empleados que son manager en la empresa.
CREATE VIEW PROYECCION as SELECT DISTINCT e2.employee_id, e1.last_name, (e2.salary + e2.salary*commission_pct/100) AS MENSUAL, (e2.salary + e2.salary*commission_pct*12/100) AS ANUAL FROM EMPLOYEES e1 INNER JOIN EMPLOYEES e2 ON e1.manager_id = e2.employee_id; 16.- Muestre la estructura de la tabla DEPARTMENTS. Seleccione todos los datos de la tabla para los departamentos que están localizados en la región Americas. SELECT deparment_id, department_name, manager_id FROM ((DEPARMENTS INNER JOIN LOCATION ON DEPARMETS.location_id = LOCATION.location_id) INNER JOIN COUNTRY ON LOCATION.country_id = COUNTRY.country_id) INNER JOIN REGION ON COUNTRY.region_id = REGION.region_id; 17.- Muestre la estructura de la tabla EMPLOYEES. Cree una consulta para mostrar el apellido, el código de cargo, la fecha de contratación y el número de empleado para cada empleado, con el número de empleado en primer lugar. SELECT employee_id, last_name, job_id, hire_date FROM EMPLOYEES; 18.-Cree una consulta para mostrar los códigos de cargo que existen en la tabla EMPLOYEES. SELECT DISTINCT job_id FROM EMPLOYEES; 19.-Muestre el apellido concatenado con el identificador de cargo, separados por una coma y un espacio y llame a la columna Titulo_empleado. SELECT LAST_NAME ||', '|| JOB_ID "TITULO EMPLEADO" FROM EMPLOYEES; 20.-Mostrar los nombres y la primera inicial del apellido para todos los empleados que trabajan en el código del departamento 100. SELECT FIRST_NAME || SUBSTR(LAST_NAME,1,1) "NOMBRE CONCATENADO" FROM EMPLOYEES E WHERE E.DEPARTMENT_ID = 100;
21.-Mostrar los apellidos y el nombre del mes que fue contratado los empleados del código del departamento 30. Rotule esta última columna como MES_CONTRATO. SELECT LAST_NAME APELLIDOS, TO_CHAR(HIRE_DATE,'MONTH') "MES CONTRATO" FROM EMPLOYEES WHERE DEPARTMENT_ID = 30; 22.-Muestre las primeras cinco letras del nombre del departamento en mayúsculas para la tabla departments. SELECT UPPER(SUBSTR(DEPARTMENT_NAME,1,5)) DEPARTAMENT FROM DEPARTMENTS; 23.-Mostrar el nombre, fecha de contratación y el número de días trabajados. SELECT FIRST_NAME NOMBRE, TRUNC(SYSDATE - HIRE_DATE) FROM EMPLOYEES; 24.-Mostrar el nombre y la fecha de contrato para todos los empleados en el siguiente formato:“dd de mes de aaaa”, rotule esta columna con el nombre de FECHA_CONTRATO. Ejemplo: 17 de Junio de 1987. SELECT FIRST_NAME NOMBRE,TO_CHAR(HIRE_DATE, 'DD')|| ' de '|| TO_CHAR(HIRE_DATE, 'MONTH')|| ' de '|| TO_CHAR(HIRE_DATE, 'YYYY') "FECHA DE CONTRATO" FROM EMPLOYEES; 25.-Mostrar el nombre, fecha de contratación y la fecha del primer chequeo médico, sabiendo que este se realiza cada seis meses, para los empleados con código de departamento 60. SELECT FIRST_NAME NOMBRE, HIRE_DATE "FECHA CONTRATO", ADD_MONTHS(HIRE_DATE,6) FROM EMPLOYEES WHERE DEPARTMENT_ID = 60;
28.-Construya una sentencia que te permita obtener los nombres de todos los empleados que no ganan comisión y además no pertenecen al departamento 50 ni 80 . SELECT FIRST_NAME ||' '||LAST_NAME "NOMBRE COMPLETO", COMMISSION_PCT, DEPARTMENT_ID FROM EMPLOYEES WHERE COMMISSION_PCT > 0 AND DEPARTMENT_ID NOT IN (80,50); 30.-Construya una sentencia que muestre lo siguiente para cualquier empleado
Código 123
Empleado juan Perez
Nombre_jefe Luis Casas
SELECT E.EMPLOYEE_ID CODIGO, E.FIRST_NAME ||' '|| E.LAST_NAME "EMPLEADO", M.FIRST_NAME ||' '|| M.LAST_NAME "NOMBRE JEFE" FROM EMPLOYEES E, EMPLOYEES M WHERE E.MANAGER_ID = M.EMPLOYEE_ID; 31.-Para cada empleado, visualice su número, apellido, salario y salario incrementado en el 15 % y expresado como número entero. Etiquete la columna como New Salary. SELECT EMPLOYEE_ID NUMERO, LAST_NAME APELLIDO, SALARY SALARIO, ROUND(SALARY*1.15) "NEW SALARY" FROM EMPLOYEES; 32.-Escriba una consulta que muestre los apellidos de los empleados con la primera letra en mayúsculas y todas las demás en minúsculas, así como la longitud de los nombres, para todos los empleados cuyos nombres comienzan por J, A o M. Asigne a cada columna la etiqueta correspondiente. Ordene los resultados según los apellidos de los empleados. SELECT INITCAP(LAST_NAME) APELLIDO, LENGTH(FIRST_NAME) "LONGITUD DEL NOMBRE" FROM EMPLOYEES WHERE SUBSTR(FIRST_NAME,1,1) IN ('J','A','M'); 33.-Para cada empleado, muestre su apellido y calcule el número de meses entre el día de hoy y la fecha de contratación.Etiquete la columna como MONTHS_WORKED. Ordene los resultados según el número de meses trabajados. Redondee el número de meses hacia arriba hasta el número entero más próximo. SELECT LAST_NAME APELLIDO, CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) "MONTHS WORKED" FROM EMPLOYEES ORDER BY CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE));
34. -Muestre el apellido de cada empleado, así como la fecha de contratación y la fecha de revisión de salario, que es el primer lunes después de cada seis meses de servicio. Etiquete la columna REVIEW. Formatee las fechas para que aparezca en un formato similar a “Monday, the Thirty-First of July, 2000”. SELECT LAST_NAME APELLIDO,TO_CHAR(HIRE_DATE,'DD')||' '|| TO_CHAR(HIRE_DATE,'DAY MONTH YYYY') "FECHA DE CONTRATACION", TO_CHAR(NEXT_DAY(ADD_MONTHS(HIRE_DATE,6),'LUNES'),'DD')||' '|| TO_CHAR(NEXT_DAY(ADD_MONTHS(HIRE_DATE,6),'LUNES'),'DAY MONTH YYYY') "REVIEW" FROM EMPLOYEES;
35.-Muestre el apellido, la fecha de contratación y el día de la semana en el que comenzó el empleado. Etiquete la columna DAY. Ordene los resultados por día de la semana, comenzando por el lunes. SELECT LAST_NAME APELLIDO, HIRE_DATE FECHA, TO_CHAR (HIRE_DATE, 'DAY') "DAY" FROM EMPLOYEES ORDER BY TO_CHAR(HIRE_DATE, 'DAY');
36.Cree una consulta que muestre el apellido y las comisiones de los empleados. Si un empleado no gana comisión, ponga “No Commission”. Etiquete la columna COMM. SELECT LAST_NAME APELLIDO, DECODE(COMMISSION_PCT,NULL,'NO COMISION', COMMISSION_PCT*SALARY) COM FROM EMPLOYEES;
37.-Utilizando la función DECODE O CASE, escriba una consulta que muestre el grado de todos los empleados basándose en el valor de la columna JOB_ID, según los datos siguientes: Cargo AD_PRES ST_MAN IT_PROG SA_REP ST_CLERK Ninguno de los anteriores
Grado A B C D E 0
SELECT EMPLOYEE_ID CODIGO, LAST_NAME APELLIDO,
DECODE(
JOB_ID,'AD_PRES','A', 'ST_MAN','B','IT_PROG', 'C','SA_REP', 'D', 'ST_CLERK','E','0') GRADO FROM EMPLOYEES ;
IS NOT NAN La condicion de punto flotante determina si una expresion es infinita o es un resultado indefinido de un operador ( que este, es un no numero or NAN) ejemplo : SELECT * from employee where salary IS NOT NAN; IS NULL En Oracle IS NULL condition es usado para testear para un valor NULL. La sintaxis para Oracle es “ expresion IS NULL” Ejemplo: SELECT * FROM suppliers WHERE supplier_name IS NULL;
ORDER BY Es usado para buscar los archivos en el resultado de la sentencia SELECT. Ejemplo: SELECT expressions FROM tables WHERE conditions ORDER BY expression [ASC | |DESC ]; NOT La condicional NOT( tambien llamado operador NOT) es usado para negar una condicion en una sentencia SELECT, INSERT, UPDATE, or DELETE. Ejemplo: SELECT * FROM customers WHERE customer_name NOT IN ( ‘IN’, ‘Hewlett Packard’, ‘Microsoft’); GROUP BY La clausula SQL GROUP BY puede ser usada en una sentencia SELECT para recoger datos a travez de multiples archivos y gurpos del resultados por una o mas columnas. Ejmeplo: SELECT expression1, expression2, …. expression_n. aggregate_function (expression) FROM tables WHERE conditions GROUP BY expression1, expression2, … expression_n; NOT BETWEEN Es usada para recuperar valores que no estan entre ese rango,es decir, negamos la a la condicion BETWEEN, en un rango determinado. Ejemplos SELECT * FROM employee WHERE id NOT BETWEEN 1 AND 3; HAVING Es usada en combinaciones con la clausula “GROUP BY” para restringir los grupos de filas de retorno para solo cuyos la condicion es TRUE. Ejemplos:
SELECT expression1, expression2, … expression_n aggregate_function ( expression ) FROM tables WHERE conditions GROUP BY expression1, expression2, … expression_n HAVING condition; NOT IN Es usado para ayudar a reducir la necesidad de multiples condiciones OR en una sentencia SELECT, UPDATE, or DELETE. EJEMPLO: expression NOT IN (value1, value2, … value_n); DISTINCT ES usado para quitar duplicados desde el conjunto de resultados de una sentencia SELECT. Ejemplo: SELECT DISTINCT expressions FROM tables WHERE conditions; NOT LIKE Permite usar comodines para realizar un patron de encuentro. Ejemplo: expression NOT LIKE pattern [ESCAPE ‘escape_character’ ] LEVEL En Oracle, el termino LEVEL, se refiere a una pseudocolumna en Oracle que es usad en una consulta jerarquica para identificar el nivel de jerarquia en formato numerico. Ejemplo: SELECT EMPNO, MGR_ID, LEVEL FROM EMPLOYEES E START WITH MGR_ID IS NULL CONNECT BY PRIOR EMPNO = MGR_ID;
ROWID Para cada fila de la base de datos, la pseudocolumna ROWID retorna la direccion de la fila.
Ejemplos: SELECT ROWID, last_name FROM employees WHERE department_id = 20; AND OR En Oracle “las condiciones AND y OR” pueden ser combinadas en una sentencia SELECT, INSERT, UPDATE o DELETE. Cuando combinas estas condiciones, es importante usar soportes, asi la base de datos tiene un orden de evaluar cada condicion. Ejemplo: WHERE condition1 AND condition2, … OR condition_n; GUID Si tu quieres generes una nueva Guid(Unica Identificacion) en SQL, podemos usar la funcion NEWID(). Ejemplo: SELECT NEWID() GO --This will return a new random uniqueidentifies e.g. E75B92A3 - 3299 - 4406 - A913C5CA196B3CAB
ALL Es un operador el cual selecciona al conjunto total de una consulta. Ejemplo con el operador “UNION ALL” SELECT expression1, expression2, … expression_n FROM tables WHERE conditions UNION ALL SELECT expression1, expression2, … expression_n FROM tables WHERE conditions; START WITH ES usado para seleccionar datos que tiene un relacion jerarquica. Ejemplo:
SELECT .. start with initial-condition connect by nocycl recurse-condition ANY Es usado para comparar un valor de una lista de subconsulta. Es necesario ser precedido por =,>,<,<=,>= y seguido por una lista o subconsulta. Ejemplo: SELECT empno, sal FROM emp WHERE sal > ANY (2000, 3000, 4000); CASE Tiene la funcionalidad de una sentencia IF-THEN-ELSE. Ejemplo: CASE [ expression ] WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 …. WHEN condition_n THEN result_n ELSE result END BETWEEN Es usado para recuperar valores entre un rango en una declaracion SELECT, INSERT, UPDATE o DELETE. Ejemplo: SELECT * FROM employees WHERE employee_id BETWEEN 25 AND 100; IN Es usado para ayudar a reducir la necesidad de multiples condiciones OR en una sentencia SELECT, INSERT, UPDATE o DELETE. Ejemplo: SELECT* FROM customers WHERE customer_name IN (‘IBM’, ‘Hewlett Packard’, ‘Microsoft’); EXISTS Es usada en combinacion con subconsultas y es considerado ser encontrado, si la subconsult retorna almenos una fila. Ejemplo: SELECT *
FROM suppliers WHERE EXISTS ( SELECT* FROM orders WHERE suppliers.supplier_id=orders.supplier_id) LIKE Permite usar comodines para realizar un patron de encuentro. Ejemplo: SELECT supplier_name FROM suppliers WHERE supplier_name LIKE ‘Hew%’; DECODE Tiene la funcionalidad de una sentencia IF - THEN - ELSE Ejemplo: SELECT supplier_name, DECODE( supplier_id, 100000, ‘IBM’ 100001, ‘Microsoft’, 100002, ‘Hewlett Packard’, ‘Gateway’) result FROM suppliers;
