Solución a Pedidos

----------------------------------------------------------------------------------------------------

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

----------------------------------------------------------------------------------------------------

Drop table if exists detalleordenes;
Drop table if exists ordenes;
Drop table if exists empleados;
Drop table if exists clientes;
Drop table if exists productos;
Drop table if exists proveedores;
Drop table if exists categorias;

----------------------------------------------------------------------------------------------------

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

----------------------------------------------------------------------------------------------------

create table empleados (
empleadoid int4 not null,
nombre varchar(30) null,
apellido varchar(30) null,
fechanac date null,
reportaa int4 null,
extension int4 null,
constraint pkempleados primary key (empleadoid));

create table proveedores(
proveedorid int4 not null,
nombreprov varchar(50) not null,
contacto varchar(50) not null,
celuprov varchar(12) null,
fijoprov varchar(12) null,
constraint pkproveedores primary key (proveedorid ) );

create table categorias(
categoriaid int4 not null,
nombrecat varchar(50) not null,
constraint pkcategorias primary key (categoriaid) ) ;

create table clientes(
clienteid int4 not null,
cedularuc varchar(10) not null,
nombrecia varchar(30) not null,
nombrecontacto varchar(50) not null,
direccioncli varchar(50) not null,
fax varchar(12) null,
email varchar(50) null,
celular varchar(12) null,
fijo varchar(12) null,
constraint pkclientes primary key (clienteid) );

create table ordenes(
ordenid int4 not null,
empleadoid int4 not null,
clienteid int4 not null,
fechaorden date not null,
descuento int4 null,
constraint pkordenes primary key (ordenid) );

create table detalleordenes(
ordenid int4 not null,
detalleid int4 not null,
productoid int4 not null,
cantidad int4 not null,
constraint pkdetalleordenes primary key (ordenid,detalleid ) );

create table productos(
productoid int4 not null,
proveedorid int4 not null,
categoriaid int4 not null,
descripcion varchar(50) null,
preciounit decimal (10,1) not null,
existencia int4 not null,
constraint pkproductos primary key (productoid )) ;

---------------------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------------------

alter table ordenes
add constraint fkordenesclienordclientes foreign key(clienteid)
references clientes (clienteid)
on delete restrict on update restrict;

alter table ordenes add constraint fkordenesempleordempleado foreign key(empleadoid)
references empleados (empleadoid)
on delete restrict on update restrict;

alter table detalleordenes add constraint fkdetalleordendetordenes foreign key(ordenid)
references ordenes (ordenid)
on delete restrict on update restrict;

alter table detalleordenes add constraint fkdetalleproddetaproducto foreign key(productoid)
references productos (productoid)
on delete restrict on update restrict;

alter table productos add constraint fkproductocateprodcategori foreign key(categoriaid)
references categorias (categoriaid)
on delete restrict on update restrict;

alter table productos add constraint fkproductoprovprodproveedo foreign key(proveedorid)
references proveedores (proveedorid)
on delete restrict on update restrict;

alter table empleados add constraint fkempleadoreporta foreign key(reportaa)
references empleados (empleadoid)
on delete restrict on update restrict;

----------------------------------------------------------------------------------------------------

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

----------------------------------------------------------------------------------------------------

insert into categorias (categoriaid, nombrecat) values (100, 'Carnicos');
insert into categorias (categoriaid, nombrecat) values (200, 'Lacteos');
insert into categorias (categoriaid, nombrecat) values (300, 'Limpieza');
insert into categorias (categoriaid, nombrecat) values (400, 'Higine personal');
insert into categorias (categoriaid, nombrecat) values (500, 'Medicinas');
insert into categorias (categoriaid, nombrecat) values (600, 'Cosmeticos');
insert into categorias (categoriaid, nombrecat) values (700, 'Revistas');

insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) 
values (10, 'Don Diego', 'Manuel Andrade', ' 317430298','6324456');
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) 
values (20, 'Pronaca', 'Juan Perez', ' 3157179535','6512456');
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) 
values (30, 'Tony', 'Jorge Brito', ' 3005087876','6124456');
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) 
values (40, 'Miraflores', 'Maria Paz', ' 3202015320','6245879');
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) 
values (50, 'Almay', 'Pedro Gonzalez', '3177654567','6207190');
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) 
values (60, 'Revlon', 'Monica Salas', '3150992456','6260986');
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) 
values (70, 'Yanbal', 'Bety Arias', '3008124458','6245087');
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) 
values (120, 'Juris', 'Sergio Ortiz', '3179923417','6212456');
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) 
values (80, 'Cleaner', 'Manuel Lopez', '3159923467','6214456');
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) 
values (90, 'Bayer', 'Lucas Salazar', '3009923567','6224456');
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) 
values (110, 'Palmolive', 'Gustavo Rodriguez', '3209924567','6124456');

insert into productos values (1,10,100,'Salchichas vienesas',2600,200);
insert into productos values (2,10,100,'Salami de ajo',3600,300);
insert into productos values (3,10,100,'Carne para asado',4700,400);
insert into productos values (4,20,100,'Salchichas de pollo',2900,200);
insert into productos values (5,20,100,'Jamon de pollo',2800,100);
insert into productos values (6,30,200,'Yogurt natural',4300,80);
insert into productos values (7,30,200,'Leche chocolate',1600,90);
insert into productos values (8,40,200,'Yogurt de sabores',1600,200);
insert into productos values (9,40,200,'Crema de leche',3600,30);
insert into productos values (10,50,600,'Base de maquillaje',14700,40);
insert into productos values (11,50,600,'Rimmel',12900,20);
insert into productos values (13,60,600,'Sombra de ojos',9800,100);

set datestyle to dmy;

insert into empleados values (1,'Juan', 'Cruz', '18/01/67',null, 231);
insert into empleados values (2,'Mario', 'Sanchez', '01/03/79',1,144);
insert into empleados values (3,'Veronica', 'Arias', '23/06/77',1, 234);
insert into empleados values (4,'Pablo', 'Cely', '28/01/77',2, 567);
insert into empleados values (5,'Diego', 'Andrade', '15/05/70',2, 890);
insert into empleados values (6,'Juan', 'Andrade', '17/11/76',3, 230);
insert into empleados values (7,'Maria', 'NoVoa', '21/12/79',3, 261);

insert into clientes values (1,'1890786576','supermercado estrella','juan alban','av.amazonas',null,null,null,null);
insert into clientes values (2,'1298765477','el rosado','maria cordero','av.ael inca',null,null,null,null);
insert into clientes values (3,'1009876567','distribuidora prensa','pedro pinto','el pinar',null,null,null,null);
insert into clientes values (4,'1876090006','su tienda','pablo ponce','av.amazonas',null,null,null,null);
insert into clientes values (5,'1893456776','supermercado dorado','lorena paz','av.6 diciembre',null,null,null,null);
insert into clientes values (6,'1678999891','mi comisariato','rosario utreras','av.amazonas',null,null,null,null);
insert into clientes values (7,'1244567888','supermercado descuento','leticia ortega','av.la prensa',null,null,null,null);
insert into clientes values (8,'1456799022','el descuento','juan torres','av.patria',null,null,null,null);
insert into clientes values (9,'1845677777','de luise','jorge parra','av.amazonas',null,null,null,null);
insert into clientes values (10,'183445667','yarbantrella','pablo polit','av.republica',null,null,null,null);

insert into ordenes values(1,3,4,'17/06/07', 5);
insert into ordenes values(2,3,4,'02/06/07', 10);
insert into ordenes values(3,4,5,'05/06/07', 6);
insert into ordenes values(4,2,6,'06/06/07', 2);
insert into ordenes values(5,2,7,'09/06/07', null);
insert into ordenes values(6,4,5,'12/06/07', 10);
insert into ordenes values(7,2,5,'14/06/07', 10);
insert into ordenes values(8,3,2,'13/06/07', 10);
insert into ordenes values(9,3,2,'17/06/07', 3);
insert into ordenes values(10,2,2,'18/06/07', 2);

insert into detalleordenes values(1,1,1,2);
insert into detalleordenes values(1,2,4,1);
insert into detalleordenes values(1,3,6,1);
insert into detalleordenes values(1,4,9,1);
insert into detalleordenes values(2,1,10,10);
insert into detalleordenes values(2,2,13,20);
insert into detalleordenes values(3,1,3,10);
insert into detalleordenes values(4,1,9,12);
insert into detalleordenes values(5,1,1,14);
insert into detalleordenes values(5,2,4,20);
insert into detalleordenes values(6,1,3,12);
insert into detalleordenes values(7,1,11,10);
insert into detalleordenes values(8,1,2,10);
insert into detalleordenes values(8,2,5,14);
insert into detalleordenes values(8,3,7,10);
insert into detalleordenes values(9,1,11,10);
insert into detalleordenes values(10,1,1,5);

----------------------------------------------------------------------------------------------------

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

----------------------------------------------------------------------------------------------------

--1. Mostrar la información de los productos.

select * from productos;


--2. Obtener la información de los empleados.

select * from empleados;


--3. Listar los proveedores existentes.

select * from proveedores;


--4. Mostrar la descripción y existencias de los productos.

select descripcion, existencia from productos;


--5. Mostrar la descripción y existencias de los productos ordenados por descripción.

select descripcion, existencia from productos order by descripcion;


--6. Mostrar la descripción y existencias de los productos ordenados por las existencias de forma descendente y descripción ascendente.

select descripcion, existencia from productos order by existencia desc, descripcion asc;


--7. Agregar la columna ciudad a la tabla proveedor.

alter table proveedores add column ciudad varchar (20);


--8. Actualiza la información de los proveedores de la siguiente forma: 3 en Bucaramanga, 2 en Bogotá DC y el resto en Medellín.

update proveedores set ciudad = 'Bucaramanga' where proveedorid in (10, 20, 30);

update proveedores set ciudad = 'Bogota D.C' where proveedorid in (40, 50);

update proveedores set ciudad = 'Medellín' where ciudad is null;


--9. Muestre el identificador, nombre y contacto de los proveedores.

select proveedorid, nombreprov, contacto from proveedores;


--10. Muestre nombre y contacto de los proveedores ubicados en la ciudad de Bucaramanga y ordenados por el nombre.

select nombreprov, contacto from proveedores where lower(ciudad) = 'bucaramanga' order by nombreprov;


--11. Obtenga la información de los productos cuyas existencias son mayores a 100.

select * from productos where existencia > 100;


--12. Obtenga ahora los productos cuyas existencias son mayores a 100 pero menores a 300.

select * from productos where existencia > 100 and existencia < 300;


--13. Muestre solo los productos cuyas existencias son menores de 90 o los mayores o iguales a 200.

select * from productos where existencia < 90 and existencia <= 200;


--14. Liste los empleados cuyos nombres empiezan por la consonante 'p'.

select * from empleados where lower(nombre) like 'p%';


--15. Liste los empleados cuyos nombres empiezan por la consonante 'm' independiente si está escrito en mayúscula o minúscula.

select * from empleados where lower(nombre) like 'm%';


--16. Muestra los empleados que tienen la vocal 'a' en la segunda letra del nombre.

select * from empleados where lower(nombre) like '_a%';


--17. Obtenga los productos que tienen 'll' en su descripción.

select * from productos where lower(descripcion) like '%ll%';


--18. Obtenga los productos que tienen 'll' en su descripción y la categoría es 100 que es Cárnicos.

select * from productos where lower(descripcion) like '%ll%' and categoriaid = 100;


--19. Agregue el campo salario a la tabla empleados que sea un decimal de 10 dígitos donde uno de ellos es decimal.

alter table empleados add column salario decimal(10,1);


--20. Actualiza los salarios de la siguiente forma, 3 empleados ganan: 617000, 2 ganan 1200000 y el resto gana 2000000.

update empleados set salario = 617000 where empleadoid in (5, 6, 7);

update empleados set salario = 1200000 where empleadoid in (3, 4);

update empleados set salario = 2000000 where salario is null;


--21. Muestre los empleados que le reportan a Juan Cruz.

select * from empleados where reportaa = 1;


--22. Muestre los empleados que no le reportan a nadie.

select * from empleados where reportaa is null;


--23. Agregue la columna edad a la tabla empleado.

alter table empleados add column edad int2;


--24. Utilizando la fecha de nacimiento calcule la edad y actualice la información.

update empleados set edad = ((current_date - fechanac)/365);


--25. La empresa está estudiando la posibilidad de subir los salarios en un 5%, 8% y 11% a los empleados que actualmente ganen el salario mínimo,
--por lo tanto calcule y muestre utilizando un alias para ver como quedarían sin afectar la tabla.;

select distinct salario, salario * 1.05 as "5%", salario * 1.08 as "8%", salario * 1.11 as "11%" from empleados where salario = 617000;


--26. Teniendo en cuenta el estudio la empresa decidió aumentar en un 5% los salarios para los empleados que actualmente ganan un mínimo.

update empleados set salario = salario * 1.05 where salario = 617000;


--27. Realice un informe donde muestre el identificador, nombre, apellido y el salario ordenado por este último atributo.

select empleadoid, nombre, apellido, salario from empleados order by salario;


--28. Muestre los empleados cuya fecha de cumpleaños esta entre enero y junio.

select * from empleados where extract(month from fechanac) >=1 and extract(month from fechanac) <= 6;


--29. Arreglar el registro de la tabla de empleado donde la edad es un valor negativo; cambie la fecha de nacimiento a 1985-04-02 y re-calcule la edad.

update empleados set fechanac = '1985-04-02' where edad < 0;

update empleados set edad = ((current_date - fechanac)/365);


--30. Agregar una columna a la tabla de empleado que almacene la fecha de inicio al trabajo.

alter table empleados add column fecini date;


--31. Actualiza la fecha de inicio al trabajo en la tabla de empleados (Recuerda que la fecha de inicio de trabajo debe ser mayor a la fecha de nacimiento pero menor que la fecha actual).

update empleados set fecini = '2004-02-04' where empleadoid=1;
update empleados set fecini = '2004-02-27' where empleadoid=2;
update empleados set fecini = '2004-03-03' where empleadoid=3;
update empleados set fecini = '2004-03-10' where empleadoid=4;
update empleados set fecini = '2004-03-15' where empleadoid=5;
update empleados set fecini = '2004-03-15' where empleadoid=6;
update empleados set fecini = '2004-02-10' where empleadoid=7;