lunes, 27 de octubre de 2014

Solución - Empleados & Departamentos


----------------------------------------------------------------------------------------------------
----------------------------------ELIMINA LAS TABLAS SI EXISTEN-------------------------------------
----------------------------------------------------------------------------------------------------

drop table if exists empleado;
drop table if exists departamento;

----------------------------------------------------------------------------------------------------
------------------------------------------CREA LAS TABLAS-------------------------------------------
----------------------------------------------------------------------------------------------------

create table departamento (
dptonro int4 not null,
dptonom varchar(30),
dptoubi varchar(40),
constraint pkdepartamento primary key (dptonro)
);

create table empleado (
empnro int4 not null,
empnom varchar(60),
empcar varchar(30),
empsup int4,
empfco date,
empsal decimal(11,2),
comm decimal(11,2),
dptonro int4,
constraint pkempleado primary key (empnro)
);

----------------------------------------------------------------------------------------------------
-----------------------------------------ALTERA LAS TABLAS -----------------------------------------
----------------------------------------------------------------------------------------------------

alter table empleado add constraint fkdptoemp foreign key (dptonro)
references departamento (dptonro) on delete restrict on update cascade;

alter table empleado add constraint fkemoemp foreign key (empsup)
references empleado (empnro) on delete restrict on update cascade;

----------------------------------------------------------------------------------------------------
----------------------------------------ALIMENTA LAS TABLAS-----------------------------------------
----------------------------------------------------------------------------------------------------

insert into departamento values(10,'Contabilidad','Bucaramanga');
insert into departamento values(20,'Investigacion','Cali');
insert into departamento values(30,'Ventas','Medellin');
insert into departamento values(40,'Operaciones','Bogota DC');

insert into empleado 
values (7839,'Cesar Andres Manrique','Presidente',null,'1981/11/17',5000000,null,10);
insert into empleado 
values (7698,'Isabella Arevalo','Director',7839,'1981/05/01',2850000,null,30);
insert into empleado 
values (7782,'Lina Salazar','Director',7839,'1981/06/09',2450000,null,10);
insert into empleado 
values (7566,'Milena Ortiz','Director',7839,'1981/04/02',2975000,null,20);
insert into empleado 
values (7788,'Samira Garcia','Analista',7566,'1987/07/13',3000000,null,20);
insert into empleado 
values (7902,'Edward Herrera','Anarlista',7566,'1981/12/03',3000000,null,20);
insert into empleado 
values (7369,'Alejandra Pinzon','Secretaria',7902,'1980/12/17',800000,null,20);
insert into empleado 
values (7876,'Tanya Muller','Secretaria',7788,'1987/07/13',1100000,null,20);
insert into empleado 
values (7900,'Jenny Ardila','Secretaria',7698,'1981/12/03',950000,null,30);
insert into empleado 
values (7934,'Luisa Anaya','Secretaria',7782,'1982/01/23',1300000,null,10);
insert into empleado 
values (7499,'Ligia Dominguez','Vendedor',7698,'1981/02/20',1600000,300000,30);
insert into empleado 
values (7521,'Maria Angel Puertas','Vendedor',7698,'1981/02/22',1250000,500000,30);
insert into empleado 
values (7654,'Sandra Quintero','Vendedor',7698,'1981/09/28',1250000,140000,30);
insert into empleado 
values (7844,'Alexander Nikos','Vendedor',7698,'1981/09/08',1500000,0,30);

----------------------------------------------------------------------------------------------------
---------------------------------------------CONSULTAS----------------------------------------------
----------------------------------------------------------------------------------------------------


--1.    Muestre los datos de los empleados.

select * from empleado;


--2.    Liste los departamentos.

select * from departamento;

--3.    Halle la información de los empleados con el cargo de Secretaria.

select * from empleado where empcar= 'Secretaria';


--4.    Halle la información de los empleados con el cargo de Secretaria, pero ordenados por el nombre.

select * from empleado where empcar= 'Secretaria' order by empnom;


--5.    Obtén el nombre y salario de los empleados.

select empnom,empsal from empleado;


--6.    Muestra los departamentos, ordenados por el nombre.

select * from departamento order by dptonom;


--7.    Muestra el nombre de los departamentos, ordenados de forma ascendente por la ciudad.

select * from departamento order by dptoubi asc;


--8.    Muestra el nombre de los departamentos, ordenados de forma descendente por la ciudad.

select * from departamento order by dptoubi desc;


--9.    Obtén el cargo y nombre de los empleados, ordenados por el salario.

select empcar,empnom from empleado order by empsal;


--10.   Lista el cargo y nombre de los empleados, ordenados por el cargo y salario.

select empcar,empnom from empleado order by empcar, empsal;


--11.   Obtén el cargo y nombre de los empleados, ordenados por el cargo inversamente y salario ascendente.

select empcar,empnom from empleado order by empcar desc, empsal asc;


--12.   Obtenga los salarios y las comisiones de los empleados en el departamento número 30.

select empsal, comm from empleado where dptnro = 30;
  

--13.   Obtenga los salarios y las comisiones de los empleados en el departamento número 30, ordenados por la comisión.

select empsal, comm from empleado where dptnro = 30 order by comm;

 --14.   Obtén las distintas comisiones que hay.

 select comm from empleado;


--15.   Obtenga los nuevos salarios que resultarían de sumar a los empleados del departamento 30 una gratificación de 100000.

select empsal+100000 as "Nuevo salarios" from empleado where dptnro=30;


--16.   Obtenga los nuevos salarios que resultarían de sumar a los empleados del departamento 30 una gratificación de 100000, pero mostrando también el salario anterior.

select empsal as "Salario anterior", empsal+100000 as "Nuevo salario" from empleado where dptnro=30;


--17.   Hallar los empleados que tienen una comisión superior a la mitad de su salario.

select empnom from empleado where comm > (empsal/2);


--18.   Hallar los empleados cuya comisión es menor o igual que el 25% del sueldo.

select empnom from empleado where comm <= (empsal*0.25);


--19.   Haga que en cada fila figure 'Nombre:' y 'Puesto:' anteponiéndose a su respectivo valor.

select concat ('Nombre:',empnom), concat('Puesto:',empcar) from empleado;


--20.   Hallar el salario y la comisión de los empleados cuyo número de empleado supera a 7500.

select empsal, comm from empleado where empnro > 7500;


--21.   Hallar el salario, la comisión y el salario total (salario + comisión) de los empleados con comisión, ordenados por el número del empleado.

select empsal, comm, empsal+comm as "Salario Total" from empleado where comm is not null order by empnro;


--22.   Hallar el nombre de los empleados que teniendo un salario superior a 1000000, tienen como director al empleado de código 7698.

select empnom  from empleado where empsup= 7698 and empsal > 1000000;


--23.   Calcula y muestra el porcentaje que supone la comisión sobre el salario total ordenado por el nombre.

select empnom,(100*coalesce(comm,0))/empsal as "% Comisión" from empleado where comm is not null order by empnom;


--24.   Muestra los empleados del departamento 10, cuyo nombre no contiene la cadena "anri".

select * from empleado where dptonro=10 and empnom not like '%anri%';


--25.   Buscar y mostrar los empleados que no son supervisados por ningún otro.

select empnom from empleado where empsup is null;


--26.   Muestra los nombres de los departamentos que no sean Ventas ni Investigación, ordenados por ubicación.

select dptonom from departamento where dptonom not in ('Ventas','Investigación') order by dptoubi;


--27.   Deseamos conocer el nombre de los empleados y el código del departamento de las secretarias que no trabajan en el departamento 10, y cuyo salario es superior a 800000, ordenado por fecha de contratación.

select empnom, dptonro from empleado where empcar='Secretaria' and dptonro<>10 and empsal>800000 order by empfco;


--28.    Halla los datos de los empleados que, o bien su nombre empieza por A y su salario es superior a 1000000, o bien reciben comisión y trabajan en el departamento 30.

select * from empleado where (empnom like 'A%' and empsal>1000000) or (comm<>null and dptonro=30);


--29.   Lista los nombres y fecha de contratación de aquellos empleados que no son vendedores.

select empnom, empfco from empleado where empcar<>'Vendedor';


--30.   Suponiendo que el año próximo la subida del sueldo de cada empleado será del 6%, y el siguiente del 7%, halla los nombres y el salario actual,  del año próximo y del siguiente, de cada empleado.

select empnom, empsal as "Salario", empsal + coalesce(comm,0) as "Salario total", empsal*1.06 as "Salario 2015", empsal*1.07 as "Salario 2016" from empleado;

0 comments:

Publicar un comentario