create database ventas
go
use ventas
Create table tbl_clientes (
id_cliente Integer not null,
nombre_cliente Char(40) not null,
dir_cliente Char(50) not null,
tel_cliente Char(10) not null,
rfc_cliente Char(13) not null
)
Create table tbl_venta (
id_venta Integer not null,
id_cliente Integer not null,
nofactura_venta Integer not null,
iva_venta Decimal(2,0) not null,
fecha_venta Date not null
)
Create table tbl_vta_detalle (
id_vtadetalle Integer not null,
id_venta Integer not null,
id_prod Integer not null,
cantidad_vtadetalle Decimal(5,0) not null,
precio_vtadetalle Decimal(4,2) not null
)
--check
alter table tbl_vta_detalle add constraint chk_vtadetalle
check(cantidad_vtadetalle>0)
Create table tbl_productos (
id_prod Integer not null,
nombre_prod Char(20) not null,
descripcion_prod Char(40) not null,
existencia_prod Integer not null
)
--check
alter table tbl_productos add constraint chk_prod check(existencia_prod>0)
Create table tbl_compra_detalle (
id_compradet Integer not null,
id_prod Integer not null,
id_compra Integer not null,
cantidad_compradet Decimal(5,0) not null,
precio_compradet Decimal(4,2) not null
)
--check
alter table tbl_compra_detalle add constraint chk_compradet
check(cantidad_compradet>0)
Create table tbl_compra (
id_compra Integer not null,
id_prov Integer not null,
iva_compra Decimal(2,0) not null,
fecha_compra Date not null
)
Create table tbl_proveedor (
id_prov Integer not null,
nombre_prov Char(40) not null,
dir_prov Char(50) not null,
tel_prov Char(10) not null,
rfc_prov Char(15) not null
)
--Primary keys
alter table tbl_clientes add constraint pk_tbl_clientes
primary key(id_cliente);
alter table tbl_venta add constraint pk_tbl_venta
primary key(id_venta);
alter table tbl_vta_detalle add constraint pk_tbl_vta_detalle
primary key(id_vtadetalle, id_venta, id_prod);
alter table tbl_productos add constraint pk_tbl_productos
primary key(id_prod);
alter table tbl_compra_detalle add constraint pk_tbl_compra_detalle
primary key(id_compradet, id_prod, id_compra);
alter table tbl_compra add constraint pk_tbl_compra
primary key(id_compra);
alter table tbl_proveedor add constraint pk_tbl_proveedor
primary key(id_prov);
--primary foreign keys
alter table tbl_vta_detalle add constraint pfk_tbl_vta_detalle_tbl_venta
foreign key (id_venta) references tbl_venta(id_venta) on update cascade on delete
cascade
alter table tbl_vta_detalle add constraint pfk_tbl_vta_detalle_tbl_productos
foreign key (id_prod) references tbl_productos(id_prod) on update cascade on delete
cascade
alter table tbl_compra_detalle add constraint pfk_tbl_compra_detalle_tbl_productos
foreign key (id_prod) references tbl_productos(id_prod) on update cascade on delete
cascade
alter table tbl_compra_detalle add constraint pfk_tbl_compra_detalle_tbl_compra
foreign key (id_compra) references tbl_compra(id_compra) on update cascade on delete
cascade
--Foreign keys
alter table tbl_venta add constraint fk_tbl_venta_tbl_clientes
foreign key (id_cliente) references tbl_clientes(id_cliente) on update cascade on
delete no action
alter table tbl_compra add constraint fk_tbl_compra_detalle_tbl_proveedor
foreign key (id_prov) references tbl_proveedor(id_prov) on update cascade on delete
no action
--Atributo ciudad
alter table tbl_proveedor add ciudad varchar(15);
--Index
CREATE INDEX ID_rfc_cliente
ON tbl_clientes(rfc_cliente)
CREATE INDEX ID_rfc_prov
ON tbl_proveedor(rfc_prov)
--Atributo precio
alter table tbl_productos add precio_producto money;
--Lenado de la tabla tbl_clientes
insert into tbl_clientes values (1111, 'Alberto Albino Maza', 'El Palchan SN',
'23532387', 'ALMA0305782A7');
insert into tbl_clientes values (1112, 'Gerardo Segura Villa', 'Benito Juárez #
152', '23532350', 'VISG1507592B4');
insert into tbl_clientes values (1113, 'Fernando Salas Santos', 'AV. Zapata # 4785',
'23532340', 'SASF0109703S5');
insert into tbl_clientes values (1114, 'Diana Vidal Contreras', 'Clavijero # 467',
'235323 87', 'COVD0811712B8');
insert into tbl_clientes values (1115, 'Elias Rodiz Martines', 'Serdan # 745',
'23532398', 'MARE0305885C3');
insert into tbl_clientes values (1116, 'Paloma Soto Cabrera', 'Lázaro cárdenas #
45', '23532312', 'CASP0505775D3');
insert into tbl_clientes values (1117, 'Edith Reyes Condado', 'AV. Zapata # 47',
'23532300', 'CORE0201722B5');
insert into tbl_clientes values (1118, 'Adriana Villa Salas', 'AV. Zapata # 875',
'23532341', 'SAVA0407803D3');
insert into tbl_clientes values (1119, 'Valentín Juárez Flores', 'Benito Juárez #
1025', '23532316', 'FLJV0412812B3');
select * from tbl_clientes
--Llenado de la tabla tbl_provedor
insert into tbl_proveedor values (4564, 'José Fernández Cervantes', 'Carranza # 45',
'23589568', 'Fecj0108712d5', 'Veracruz');
insert into tbl_proveedor values (4565, 'Julio Carballo Lujan', 'Díaz Mirón # 84',
'23589571', 'CALJ0509753E2', 'Puebla');
insert into tbl_proveedor values (4566, 'Cesar Aggi Ceron', 'Benito Juárez # 78',
'23589571', 'CEAC1207702S2', 'Xalapa');
insert into tbl_proveedor values (4568, 'Pedro López Carnoso', 'Serdan # 457',
'23589433', 'CALP0912682D3', 'Veracruz');
select * from tbl_proveedor
--Llenado de la tabla tbl_productos
insert into tbl_productos values (4455, 'Hojas', 'Paquete de 100 hojas', 200,
50.00);
insert into tbl_productos values (4456, 'Capeta ', 'Color blanco ', 100, 15.50);
insert into tbl_productos values (4457, 'Computadora HP', 'Procesador 1Gb y 2 GB en
RAM', 20, 6800.00);
insert into tbl_productos values (4458, 'CD', 'Caja de 10 CD Sony', 50, 45.00);
insert into tbl_productos values (4459, 'DVD', 'Caja de 10 DVD', 40, 50.00);
insert into tbl_productos values (4460, 'Porta hojas', 'Paquete de 10', 50, 15.00);
insert into tbl_productos values (4461, 'Separadores', 'Paquete de 12', 70, 25.00);
insert into tbl_productos values (4462, 'Bandeja separadora', 'Color negro', 20,
25.00);
insert into tbl_productos values (4463, 'SILIMPO', 'Espuma limpiadora', 40, 40.50);
insert into tbl_productos values (4464, 'Impresora HP ', 'Color bando Láser', 20,
1250.00);
insert into tbl_productos values (4467, 'Laptop DELL', 'Procesador 1 GB y 2 Gb en
RAM', 0, 15000.00);
insert into tbl_productos values (4468, 'Laptop DELL', 'Procesador 1.7 GB y 1 Gb en
RAM', 0, 13000.00);
select * from tbl_productos
--Se debe realizar una o más ventas por cada cliente con al menos 3 productos
insert into tbl_venta values(1, 1115, 1, 0.16, '07/03/2023');
insert into tbl_venta values(2, 1119, 2, 0.16, '08/03/2023');
insert into tbl_venta values(3, 1118, 3, 0.16, '09/03/2023');
insert into tbl_venta values(4, 1117, 4, 0.16, '08/03/2023');
insert into tbl_venta values(5, 1116, 5, 0.16, '09/03/2023');
insert into tbl_venta values(6, 1114, 6, 0.16, '09/03/2023');
insert into tbl_venta values(7, 1113, 7, 0.16, '08/03/2023');
insert into tbl_venta values(8, 1112, 8, 0.16, '07/03/2023');
insert into tbl_venta values(9, 1111, 9, 0.16, '08/03/2023');
insert into tbl_vta_detalle values(1, 1 , 4455, 1, 30)
insert into tbl_vta_detalle values(2, 1 , 4457, 3, 40)
insert into tbl_vta_detalle values(3, 1 , 4458, 2, 60)
insert into tbl_vta_detalle values(4, 2 , 4459, 1, 80);
insert into tbl_vta_detalle values(5, 2 , 4460, 1, 50);
insert into tbl_vta_detalle values(6, 2 , 4461, 1, 30);
insert into tbl_vta_detalle values(7, 3 , 4464, 2, 20);
insert into tbl_vta_detalle values(8, 3 , 4456, 4, 60);
insert into tbl_vta_detalle values(9, 3 , 4458, 1, 80);
insert into tbl_vta_detalle values(10, 4 , 4457, 2, 30);
insert into tbl_vta_detalle values(11, 4 , 4458, 4, 75);
insert into tbl_vta_detalle values(12, 4 , 4459, 3, 70);
insert into tbl_vta_detalle values(13, 5 , 4464, 1, 70);
insert into tbl_vta_detalle values(14, 5 , 4463, 2, 40);
insert into tbl_vta_detalle values(15, 5 , 4460, 1, 20);
insert into tbl_vta_detalle values(16, 6 , 4459, 4, 40);
insert into tbl_vta_detalle values(17, 6 , 4455, 1, 50);
insert into tbl_vta_detalle values(18, 6 , 4457, 3, 30);
insert into tbl_vta_detalle values(19, 7 , 4461, 1, 50);
insert into tbl_vta_detalle values(20, 7 , 4462, 3, 40);
insert into tbl_vta_detalle values(21, 7 , 4463, 1, 25);
insert into tbl_vta_detalle values(22, 8 , 4459, 2, 30);
insert into tbl_vta_detalle values(23, 8 , 4457, 2, 25);
insert into tbl_vta_detalle values(24, 8 , 4464, 3, 20);
insert into tbl_vta_detalle values(25, 9 , 4462, 1, 25);
insert into tbl_vta_detalle values(26, 9 , 4460, 1, 25);
insert into tbl_vta_detalle values(27, 9 , 4459, 1, 25);
select*from tbl_venta
select*from tbl_vta_detalle
--Se debe realizar 2 o más compras a proveedores al menos 2 productos
insert into tbl_compra values (01, 4564, 0.16, '02/03/2023')
insert into tbl_compra_detalle values (001,4455,01,15,100)
insert into tbl_compra_detalle values (002,4458,01,25,300)
insert into tbl_compra values (02, 4565, 0.16, '05/03/2023')
insert into tbl_compra_detalle values (003,4457,02,8,120)
insert into tbl_compra_detalle values (004,4460,02,1,60)
insert into tbl_compra values (03, 4566, 0.16, '07/03/2023')
insert into tbl_compra_detalle values (005,4463,03,5,200)
insert into tbl_compra_detalle values (006,4458,03,4,400)
insert into tbl_compra values (04, 4568, 0.16, '09/03/2023')
insert into tbl_compra_detalle values (007,4457,04,8,180)
insert into tbl_compra_detalle values (008,4455,04,30,1000)
--Incremente los precios de los productos en un 10%
update tbl_productos set precio_producto=(precio_producto*0.10)+precio_producto
select*from tbl_productos
--eliminar productos sin existencia
delete from tbl_productos where existencia_prod=0
select*from tbl_productos