GUÍA N°4 SQL

--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"

...