API3
1) create database comerciante
2)
TABLA ARTÍCULO
create table articulo (
cod_art integer not null primary key,
nom_art varchar (50) not null,
desc_art varchar (150) not null,
un_art integer not null
);
TABLA CLIENTE
create table cliente (
dni_cli varchar (9) not null primary key,
nom_cliente varchar (50) not null,
ape_cli varchar (50) not null,
tel_cli varchar (15) not null,
fecha_alta date not null
);
TABLA VENTA
create table venta (
cod_art1 integer not null,
dni_cli1 varchar (9) not null,
fecha_hora timestamp not null,
unid_venta integer not null,
primary key (cod_art1,dni_cli1,fecha_hora),
foreign key (cod_art1) references articulo (cod_art),
foreign key (dni_cli1) references cliente (dni_cli),
);
3)
INSERCIONES DENTRO DE ARTÍCULOS
insert into articulo values (1,'Yerba','Paquete de 1kg de yerba',90);
insert into articulo values (2,'Azucar','Paquete de 1kg de azucar',87);
insert into articulo values (3,'Pan','Bolsa de 1/2kg 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 gr de caramelos',66);
insert into articulo values (6,'Queso cremoso','Paquete de 250 gr de queso',42);
INSERCIONES DENTRO DE CLIENTE
insert into cliente values ('33445566','Juan','Piquete','011-1567899879','2015/05/15');
insert into cliente values ('34347788','Carlos','Calabresa','011-1564566549','2016/06/16');
insert into cliente values ('33556677','Esteban','Quito','011-1561233219','2014/04/14');
insert into cliente values ('33445588','Andrea','Lira','0342-155398022','2011/01/11');
insert into cliente values ('22445566','Laura','Agnat','0342-156378022','2013/03/13');
insert into cliente values ('25335999','Marixa','Bella','0342-154348022','2012/02/12');
INSERCIONES DENTRO DE VENTA
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);
4) CONSULTAS
a)
select dni_cli nom_cli
from cliente
where fecha_alta >='2012/09/15';
b)
select nom_cli, sum(unid_vend)
from cliente join venta on dni_cli=dni_cli1
group by nom_cli
c)
select c.nomb_cli, c.ape_cli, a.nom_art
FROM cliente c join venta v on c.dni_cli=v.dni_cli1
join articulo a on a.cod_art=v.cod_art1
ORDER BY v.fecha_hora DESC;
d)
Select nom_art, sum(unid_vent)
From articulo join venta on cod_art=cod_art1
Group by nom_art
Order by nom_art ASC;
e)
Select dni_cli
From cliente join venta on dni_cli=dni_cli1
Group by dni_cli
HAVING SUM(unid_vent) > 10