GUÍA N°4 SQL
Enviado por Felipe Navarro • 4 de Abril de 2017 • Ensayo • 647 Palabras (3 Páginas) • 251 Visitas
--GUÍA DE SQL Nº3
--VISUALIZANDO DATOS DE MÚLTIPLES TABLAS
--1.-
--a)
Select CONCAT('El empleado ',CONCAT(A.first_name,CONCAT(' ',CONCAT(A.last_name,
CONCAT(' trabaja en el departamento de',CONCAT(' ',B.department_name)))))) "DEPARTAMENTO DEL EMPLEADO"
From Employees A INNER JOIN DEPARTMENTS B on(A.DEPARTMENT_ID = B.DEPARTMENT_ID);
--b)
Select (employee_id) "EMPLEADO", (salary) "SALARIO", (job_id) "ID. TRABAJO",(job_title) "DESCRIPCIÓN TRABAJO"
From Employees INNER JOIN JOBS USING(job_id)
WHERE salary<2700
Order By salary ASC;
--c)
Select (A.department_name) "DEPARTAMENTO",COUNT(B.department_id) "TOTAL DE EMPLEADOS"
From Departments A INNER JOIN Employees B ON (A.department_id=B.department_id)
GROUP By A.department_name
Order By A.department_name;
--d)
Select (A.department_name) "DEPARTAMENTO",TO_CHAR(MAX(B.salary),'$999G999') "SALARIO MÁXIMO"
From Departments A INNER JOIN Employees B ON (A.department_id=B.department_id)
GROUP BY A.department_name
HAVING MAX(salary)> 6000 AND MAX(salary)<20000
Order By MAX(B.salary) DESC;
--e)
Select CONCAT(first_name,CONCAT(' ', last_name)) "NOMBRE EMPLEADO", (job_title) "TRABAJO", (department_name) "DEPARTAMENTO"
From Employees
INNER JOIN Jobs USING (job_id)
INNER JOIN Departments USING(department_id)
INNER JOIN Locations USING(location_id)
WHERE city='Toronto';
--f)
Select (department_id) "ID. DEPARTAMENTO",(department_name) "NOMBRE DEPARTAMENTO", NVL(TO_CHAR(manager_id),'0') "TOTAL EMPLEADOS"
From Departments
LEFT JOIN Employees USING (department_id,manager_id)
WHERE manager_id IS NULL
Order By department_id;
--2.-
--a)
Select CONCAT('El empleado ',CONCAT(PNOMBRE,CONCAT(' ',CONCAT(APPATERNO,CONCAT(' se desempeña como ',DESC_TIPO_EMPLEADO))))) "PERSONAL DE LA EMPRESA"
From EMPLEADO INNER JOIN TIPO_EMPLEADO USING (tipo_empleado)
Order By APPATERNO;
--b)
Select CONCAT(PNOMBRE,CONCAT(' ',APPATERNO)) "NOMBRE EMPLEADO",(NRO_BOLETA) "NÚMERO BOLETA",(FECHA_BOLETA),TO_CHAR(MONTO_TOTAL,'$999,999') "MONTO VENTA"
...