prueb final , PostgreSQL

prueb final

CREATE TABLE articulo (
    codigo INTEGER NOT NULL,
    nombre VARCHAR(50) NOT NULL,
    descripcion VARCHAR(50) NOT NULL,
    unidades_disponibles INTEGER NOT NULL,
    PRIMARY KEY (codigo)
);

INSERT INTO articulo(codigo,nombre,descripcion,unidades_disponibles)
VALUES(1,'Yerba','Paquete de 1kg de yerba',90);
INSERT INTO articulo(codigo,nombre,descripcion,unidades_disponibles)
VALUES(2,'Azucar','Paquete de 1kg de yerba',87);
INSERT INTO articulo(codigo,nombre,descripcion,unidades_disponibles)
VALUES(3,'Pan','Bolsa de ½ kg de pan',105);
INSERT INTO articulo(codigo,nombre,descripcion,unidades_disponibles)
VALUES(4,'Leche larga vida','Caja tetrapack de leche',103);
INSERT INTO articulo(codigo,nombre,descripcion,unidades_disponibles)
VALUES(5,'Bolsa de caramelos','Bolsa de 250 g de caramelos',66);
INSERT INTO articulo(codigo,nombre,descripcion,unidades_disponibles)
VALUES(6,'Queso cremoso','Paquete de 250 g de queso',42);

CREATE TABLE persona (
    dni VARCHAR(9) NOT NULL,
    nombre VARCHAR(50) NOT NULL,
    apellido VARCHAR(50) NOT NULL,
    telefono VARCHAR(15) NOT NULL,
    fecha_alta DATE NOT NULL,
    PRIMARY KEY (dni)
);

INSERT INTO persona(dni,nombre,apellido,telefono,fecha_alta)
VALUES('33445566','Juan','Piquete','011-1567899879','2015-05-15');
INSERT INTO persona(dni,nombre,apellido,telefono,fecha_alta)
VALUES('34347788','Carlos','Calabresa','011-1564566549','2016-06-16');
INSERT INTO persona(dni,nombre,apellido,telefono,fecha_alta)
VALUES('33556677','Esteban','Quito','011-1561233219','2014-04-14');
INSERT INTO persona(dni,nombre,apellido,telefono,fecha_alta)
VALUES('33445588','Andrea','Lira','0342-155398022','2011-01-11');
INSERT INTO persona(dni,nombre,apellido,telefono,fecha_alta)
VALUES('22445566','Laura','Agnat','0342-156378022','2013-03-13');
INSERT INTO persona(dni,nombre,apellido,telefono,fecha_alta)
VALUES('25335999','Marixa','Bella','0342-154348022','2012-02-12');

CREATE TABLE venta (
    codigo_articulo INTEGER NOT NULL,
    dni_persona VARCHAR(9) NOT NULL,
    fecha_hora TIMESTAMP NOT NULL,
    unidades INTEGER NOT NULL,
    FOREIGN KEY (codigo_articulo) REFERENCES articulo(codigo),
    FOREIGN KEY (dni_persona) REFERENCES persona(dni),
    PRIMARY KEY (codigo_articulo, dni_persona, fecha_hora)
);

INSERT INTO venta(codigo_articulo,dni_persona,fecha_hora,unidades)
VALUES(1,'33445566','2019-01-01 11:30:00',5);
INSERT INTO venta(codigo_articulo,dni_persona,fecha_hora,unidades)
VALUES(2,'34347788','2019-02-01 12:30:00',4);
INSERT INTO venta(codigo_articulo,dni_persona,fecha_hora,unidades)
VALUES(1,'33556677','2019-05-01 10:30:00',7);
INSERT INTO venta(codigo_articulo,dni_persona,fecha_hora,unidades)
VALUES(2,'33445588','2019-04-13 09:30:00',3);
INSERT INTO venta(codigo_articulo,dni_persona,fecha_hora,unidades)
VALUES(3,'22445566','2019-07-13 09:00:00',2);
INSERT INTO venta(codigo_articulo,dni_persona,fecha_hora,unidades)
VALUES(3,'25335999','2019-01-14 10:00:00',1);
INSERT INTO venta(codigo_articulo,dni_persona,fecha_hora,unidades)
VALUES(4,'33445566','2019-02-14 11:00:00',1);
INSERT INTO venta(codigo_articulo,dni_persona,fecha_hora,unidades)
VALUES(1,'34347788','2019-06-14 18:00:00',4);
INSERT INTO venta(codigo_articulo,dni_persona,fecha_hora,unidades)
VALUES(5,'33556677','2019-06-14 18:05:00',6);
INSERT INTO venta(codigo_articulo,dni_persona,fecha_hora,unidades)
VALUES(4,'33445588','2019-07-14 18:30:00',3);
INSERT INTO venta(codigo_articulo,dni_persona,fecha_hora,unidades)
VALUES(6,'22445566','2019-08-14 18:00:00',5);
INSERT INTO venta(codigo_articulo,dni_persona,fecha_hora,unidades)
VALUES(3,'25335999','2019-09-14 19:00:00',2);

select * from articulo;
select * from persona;
select * from venta;

--Consigna A
--Listar DNI, Nombre de los Clientes que se dieron de alta en una fecha posterior al 15/09/2012 (inclusive).
/*
select dni, nombre
from persona
where fecha_alta >= '2012-09-15';
*/

--Consigna B
--Listar los Nombres de los clientes y la suma de las Unidades vendidas. Será necesario agrupar por Nombre de cliente para poder mostrar los nombres.
/*
select nombre, SUM(unidades)
from persona join venta on dni = dni_persona
Group by nombre;
*/

--Consigna C
--Mostrar Nombre y Apellido de los clientes y Nombre del artículo vendido, pero ordenando por la fecha de ventas en forma descendente
/*
select p.nombre, p.apellido, a.nombre 
from persona p join venta v on dni=dni_persona
               join articulo a on codigo_articulo = codigo
Order by v.fecha_hora DESC;
*/

--Consigna D
--Listar los Nombres de los artículos y la suma de las Unidades vendidas. Ordenar alfabéticamente en forma ascendente por el nombre del artículo

/*select nombre, sum(unidades)
from articulo join venta on codigo = codigo_articulo
Group by nombre
Order by nombre;
*/

--Consigna e
--Listar los DNI de los clientes y que hayan comprado más de 10 unidades. Será necesario agrupar por DNI de cliente, sumar las unidades y filtrar usando HAVING.

select dni, nombre, apellido 
from persona join venta on dni = dni_persona
Group by dni
Having sum(unidades) > 10;

/*
Ejercicio propuesto: agregar a la tabla de empleado vista antes restricciones de
primary key al atributo DNI y de unique al par nombre-apellido.
*/

create table empleado (
    DNI varchar (10) not null,
    Nombre varchar (15) unique,
    Apellido varchar (15) unique,
    fecha_nac date not null,
    ciudad varchar (25) not null,
    sueldo int ,
    primary key (DNI)
    );
    
create table sueldo (
    DNI varchar (10) not null,
    Nombre varchar (15) unique,
    Apellido varchar (15) unique,
    fecha_nac date not null,
    ciudad varchar (25) not null,
    sueldo int ,
    primary key (DNI)
    );

insert into empleado (DNI,Nombre,Apellido,fecha_nac,ciudad,sueldo)
values ('111111','Artemio','Lucero','1979-07-20','Comodoro',120000);
insert into empleado (DNI,Nombre,Apellido,fecha_nac,ciudad,sueldo)
values ('222222','Ana','Olmos','1976-09-17','Comodoro',150000);
insert into empleado (DNI,Nombre,Apellido,fecha_nac,ciudad)
values ('333333','Elba','Gallo','1980-01-01','Cordoba');
insert into empleado (DNI,Nombre,Apellido,fecha_nac,ciudad,sueldo)
values ('444444','Esteban','Quito','1990-10-10','Buenos Aires',75000);

/*
select *
from empleado 
where sueldo >=70000
and fecha_nac <='1985-01-01'
*/

/*
select count (*) as comodorenses
from empleado
where ciudad = 'Comodoro';
*/

select * 
from empleado
order by nombre;

select * 
from empleado
order by apellido;

select * 
from empleado
order by sueldo desc;

select * 
from empleado
order by dni

--CREATE DATABASE FUTBOL;

create table club (
    id_club varchar(10) not null primary key,
    nom_club varchar(50) not null
    );

create table player (
    id_player varchar(10) not null,
    nom_player varchar(20) not null,
    ape_player varchar(20) not null,
    edad_player integer not null,
    salario_player integer not null,
    id_club1 varchar(10) not null,
    primary key (id_player),
    foreign key (id_club1) references club (id_club)
    );
    
    
insert into club values ('101','CELESTE FC');
insert into club values ('202','AZUL FC');
insert into club values ('303','AMARILLO FC');
insert into club values ('404','VERDE FC');
insert into club values ('505','ROJO FC');

insert into player values ('01','Sofia','Mandala',24,100000,'101');
insert into player values ('02','Manuela','Calderon',24,110000,'101');
insert into player values ('03','Carla','Veron',26,200000,'101');
insert into player values ('04','Clara','Riquelme',27,150000,'101');
insert into player values ('05','Martina','Gallardo',28,80000,'202');
insert into player values ('06','Paola','Smith',30,120000,'202');
insert into player values ('07','Carla','Bello',21,150000,'202');
insert into player values ('08','Naomi','Castillo',23,160000,'303');
insert into player values ('09','Carmen','Perez',24,170000,'303');
insert into player values ('10','Mariana','Escobar',24,90000,'303');
insert into player values ('11','Stefania','Gutierrez',18,88000,'404');
insert into player values ('12','Sara','Morado',18,400000,'404');
insert into player values ('13','Natasha','Silva',20,120000,'404');
insert into player values ('14','Natalia','Morin',26,90000,'404');
insert into player values ('15','Veronica','Colmena',24,870000,'505');
insert into player values ('16','Lucrecia','Flores',30,300000,'505');
insert into player values ('50','Cristina','Mendez',19,120000,'505');

--Select * from club;
--select * from player; 
--Mostrar el nombre de las jugadoras que tienen menos o inclusive 20 años de edad
--select nom_player
--from player
--where edad_player <= 20;

--select nom_player, ape_player
--from player
--where id_club1 = '101';

--select nom_player, ape_player
--from player join club on id_club1 = id_club
--where nom_club = 'CELESTE FC';

--elect avg(edad_player) as promedio_Edad
--from player
--where id_club1 = '101';

--select sum(salario_player) as salario_mensual
--from player
--where id_club1 = '101';

update player set salario_player = salario_player * 1.10
where id_player = '02';

--select * from player;

--delete from player where id_player = '50';

--select * from player;

--crear una funcion que conociendo su id_player nos muestre su salario

/*create function buscador_salario (varchar(10)) returns integer
as 
'select salario_player
from player
where id_player =$1;'
language sql;

select buscador_salario('08');*/
--crear un procedimiento de almacenado que permita que los usuarios haga registro dentro de la tabla club

/*create function almacenado (varchar(10),varchar(50)) returns void
as
'insert into club values ($1,$2);'
language sql;

--select almacenado('606','CLUB BLANCO');*/

--SELECT * FROM CLUB;

--1er paso crear la tabla donde se ejecutara el trigger 
CREATE TABLE RESPALDO (
    id_club varchar(10) not null,
    nom_club varchar(50) not null,
    usuario varchar(50) not null,
    fecha date not null
    );

--2do paso 
create function tri_club() returns trigger
as
$$
Declare 
usuario varchar(50):= user;
fecha date:= current_date;
BEGIN
insert into RESPALDO values (new.id_club,new.nom_club,usuario,fecha);
return new;
END
$$
language plpgsql;

--3er paso crear el trigger

create trigger club_trigger after insert on club 
for each row 
execute procedure tri_club();

insert into club values ('808','Negro FC');

select * from club;

select * FROM RESPALDO;

create table articulo (
    codigo integer not null,
    nombre varchar (50) not null,
    descripcion varchar (150) not null,
    unidades_disponibles integer not null,
    primary key (codigo)    
    );

create table persona (
    dni varchar (9) not null,
    nombre varchar (50) not null,
    apellido varchar (50) not null,
    telefono varchar (15) not null,
    fecha_de_alta date not null,
    primary key (dni)
    );

create table venta (
    codigo_articulo integer not null,
    dni_persona varchar (9) not null,
    fecha_hora timestamp not null,
    unidades integer not null,
    primary key (fecha_hora,codigo_articulo,dni_persona),
    foreign key (codigo_articulo) references articulo (codigo),
    foreign key (dni_persona) references persona (dni)
    );
    
insert into articulo values (1,'Yerba','Paquete de 1kg de yerba',90);
insert into articulo values (2,'Azucar','Paquete de 1kg de yerba',87);
insert into articulo values (3,'Pan','Bolsa de ½ kg de pan',105);
insert into articulo values (4,'Leche larga vida','Caja tetrapack de leche',103);
insert into articulo values (5,'Bolsa de caramelos','Bolsa de 250 g de caramelos',66);
insert into articulo values (6,'Queso cremoso','Paquete de 250 g de queso',42);

insert into persona values ('33445566','Juan','Piquete','011-1567899879','2015-05-15');
insert into persona values ('34347788','Carlos','Calabresa','011-1564566549','2016-06-16');
insert into persona values ('33556677','Esteban','Quito','011-1561233219','2014-04-14');
insert into persona values ('33445588','Andrea','Lira','0342-155398022','2011-01-11');
insert into persona values ('22445566','Laura','Agnat','0342-156378022','2013-03-13');
insert into persona values ('25335999','Marixa','Bella','0342-154348022','2012-02-12');

insert into venta values (1,'33445566','2019-01-01 11:30:00',5);
insert into venta values (2,'34347788','2019-02-01 12:30:00',4);
insert into venta values (1,'33556677','2019-05-01 10:30:00',7);
insert into venta values (2,'33445588','2019-04-13 09:30:00',3);
insert into venta values (3,'22445566','2019-07-13 09:00:00',2);
insert into venta values (3,'25335999','2019-01-14 10:00:00',1);
insert into venta values (4,'33445566','2019-02-14 11:00:00',1);
insert into venta values (1,'34347788','2019-06-14 18:00:00',4);
insert into venta values (5,'33556677','2019-06-14 18:05:00',6);
insert into venta values (4,'33445588','2019-07-14 18:30:00',3);
insert into venta values (6,'22445566','2019-08-14 18:00:00',5);
insert into venta values (3,'25335999','2019-09-14 19:00:00',2);

/*
select * from articulo;
select * from persona;
select * from venta;
*/

--a.	Listar DNI, Nombre de los Clientes que se dieron de alta en una fecha posterior al 15/09/2012 (inclusive).

select dni,nombre,apellido
from persona
where fecha_de_alta >= '2012-09-15';

--b.	Listar los Nombres de los clientes y la suma de las Unidades vendidas. 
--      Será necesario agrupar por Nombre de cliente para poder mostrar los nombres.

select nombre, sum (unidades)
from persona join venta on dni=dni_persona
group by nombre;

--c.	Mostrar Nombre y Apellido de los clientes y Nombre del artículo vendido, 
--      pero ordenando por la fecha de ventas en forma descendente

select p.nombre, p.apellido, a.articulo
from persona p join 
group by desc (fecha_hora);

/*
select * from articulo;
select * from persona;
select * from venta;
*/

--a.	Listar DNI, Nombre de los Clientes que se dieron de alta en una fecha posterior al 15/09/2012 (inclusive).
/*
select dni,nombre,apellido
from persona
where fecha_de_alta >= '2012-09-15';
*/
--b.	Listar los Nombres de los clientes y la suma de las Unidades vendidas. 
--      Será necesario agrupar por Nombre de cliente para poder mostrar los nombres.
/*
select nombre, sum (unidades)
from persona join venta on dni=dni_persona
group by nombre;
*/
--c.	Mostrar Nombre y Apellido de los clientes y Nombre del artículo vendido, 
--      pero ordenando por la fecha de ventas en forma descendente

select p.nombre, p.apellido, a.articulo
from persona p join dni = dni_persona on venta
     venta v join codigo_articulo = codigo on venta
group by desc (fecha_hora);


run \| edit \| history \| help	0