• Author / Uploaded
• Bryan Almendras Olivo

Citation preview

CREATE DATABASE BDParcial_PA USE BDParcial_PA CREATE TABLE Factura( N_Factura CHAR(3) NOT NULL, Nom_cliente VARCHAR(50), N_Ruc CHAR(8), M_total MONEY) CREATE TABLE Factura_Det( N_Factura CHAR(3)NOT NULL, N_item INT NOT NULL, C_prod CHAR(3), C_venta INT, M_venta MONEY, M_Total AS (C_venta * M_venta)) CREATE TABLE Producto( C_prod CHAR(3) NOT NULL, N_prod VARCHAR (50), C_stock INT, I_anulado CHAR(1), Valor_Venta MONEY) ALTER TABLE FACTURA ADD CONSTRAINT PK_N_Factura PRIMARY KEY (N_Factura) ALTER TABLE FACTURA_DET ADD CONSTRAINT PK_N_item PRIMARY KEY(N_item) ALTER TABLE PRODUCTO ADD CONSTRAINT PK_C_prod PRIMARY KEY(C_prod) ALTER TABLE FACTURA_DET ADD FOREIGN KEY (N_FACTURA) REFERENCES FACTURA(N_FACTURA) ALTER TABLE FACTURA_DET ADD FOREIGN KEY (C_prod) REFERENCES PRODUCTO(C_prod) INSERT INSERT INSERT INSERT

INTO INTO INTO INTO

Factura Factura Factura Factura

VALUES VALUES VALUES VALUES

INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO

Factura_Det Factura_Det Factura_Det Factura_Det Factura_Det Factura_Det Factura_Det Factura_Det

INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO

Producto Producto Producto Producto Producto Producto

('001','Fatima Sanchez','12345678',1000) ('002','Andre Palacios','87456321',800) ('003','Javier Pajuelo','98765421',500) ('004','Luis Garcia','12354783',1200)

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

('001',1,'P01',10,80) ('001',2,'P02',5,40) ('002',3,'P01',10,60) ('002',4,'P03',5,20) ('002',5,'P04',5,20) ('003',6,'P02',100,50) ('004',7,'P02',20,50) ('004',8,'P03',10,20)

VALUES('P01','Televisores',220,'N',1200) VALUES('P02','Laptop',149,'N',1800) VALUES('P03','Refrigeradoras',185,'N',1200) VALUES('P04','Equipos Musica',100,'N',600) VALUES('P05','Artefacto Cocina',45,'N',320) VALUES('P06','Artefacto Baño',100,'N',250)

SELECT N_FACTURA,Nom_cliente, M_TOTAL FROM FACTURA ORDER BY M_TOTAL DESC SELECT N_FACTURA,SUM(C_VENTA) AS C_VENTA,SUM(M_VENTA) AS M_VENTA,SUM(M_TOTAL) AS M_TOTAL FROM FACTURA_DET GROUP BY N_FACTURA SELECT C_PROD +' '+ N_PROD AS CODIGO_NOMBRE_PRODUCTO, VALOR_VENTA,C_STOCK FROM PRODUCTO WHERE I_ANULADO'S' AND C_STOCK=200 AND C_STOCK20 AND C_STOCK