BD_KYO_PARTE2, Sql Server

Language: Layout:

/********************************************************/
/* Crear la base de datos KYO con las siguientes tablas */
/********************************************************/

--CREATE DATABASE KYO
--GO

--USE KYO
--GO

CREATE TABLE CLIENTES
(
	COD_CLI  CHAR(5) NOT NULL PRIMARY KEY,
	NOM_CLI VARCHAR(50),
	APE_PAT_CLI VARCHAR(50),
	FEC_NAC_CLI DATETIME,
	DNI_CLI CHAR(8),
	DIR_CLI VARCHAR(50), 
	SEX_CLI CHAR (1),
	GAS_MEN_CLI DECIMAL (9,2)
)

CREATE TABLE COMIDAS
(
	COD_COMI CHAR(5) NOT NULL PRIMARY KEY,
	DES_COMI VARCHAR(20),
	PRE_COMI DECIMAL(9,2)
)

CREATE TABLE INGREDIENTES
(
	COD_ING CHAR(5)NOT NULL PRIMARY KEY,
	DES_ING VARCHAR(20),
	TIP_ING VARCHAR(30),
	FEC_INGRE_ING DATETIME,
	RAZ_PRO_ING VARCHAR(20),
	RUC_PRO_ING CHAR(11)
)

CREATE TABLE COMPROBANTES
(
	NRO_COMP INT IDENTITY (100,1) NOT NULL PRIMARY KEY,
	FEC_COMP DATETIME,
	COD_VEND CHAR (5),
	IGV_COMP DECIMAL(9,2),
	MON_TOT_COMP DECIMAL(9,2),
	COD_CLI  CHAR(5) REFERENCES CLIENTES
)

CREATE TABLE DETALLECOMPROBANTES
(
	NRO_COMP INT NOT NULL REFERENCES COMPROBANTES,
	COD_COMI CHAR(5) NOT NULL REFERENCES COMIDAS,
	CAN_VEN INT,
	MON_PAR DECIMAL(9,2), 
	PRIMARY KEY(NRO_COMP,COD_COMI)
)

CREATE TABLE DETALLECOMIDAS
(
	COD_COMI CHAR(5) NOT NULL REFERENCES COMIDAS,
	COD_ING CHAR(5)NOT NULL REFERENCES INGREDIENTES,
	CANT_ING INT  PRIMARY KEY(COD_COMI,COD_ING)
)
GO

/*********************/
/* Insertar registro */
/*********************/

-- TABLA CLIENTES
INSERT INTO CLIENTES (COD_CLI,NOM_CLI ,APE_PAT_CLI ,FEC_NAC_CLI ,DNI_CLI ,DIR_CLI,SEX_CLI,GAS_MEN_CLI)	
VALUES('CI001','MERCEDES','PEREZ','01/01/1979','45235678','JR.LOS PINOS 124 SAN ISIDRO','F',20.2)	
INSERT INTO CLIENTES (COD_CLI,NOM_CLI ,APE_PAT_CLI ,FEC_NAC_CLI ,DNI_CLI ,DIR_CLI,SEX_CLI,GAS_MEN_CLI)	
VALUES('CI002','JOSE','BUSTAMANTE','10/12/1970','22536045','AV.LA  MARINA 123 SAN MIGUEL','M',115.3)	
INSERT INTO CLIENTES (COD_CLI,NOM_CLI ,APE_PAT_CLI ,FEC_NAC_CLI ,DNI_CLI ,DIR_CLI,SEX_CLI,GAS_MEN_CLI)	
VALUES('CI003','ROBERTO','MUÑOZ','01/11/1972','00245876','CALLE PLUTON 155 SANTIAGO DE SURCO','M',125.47)	
INSERT INTO CLIENTES (COD_CLI,NOM_CLI ,APE_PAT_CLI ,FEC_NAC_CLI ,DNI_CLI ,DIR_CLI,SEX_CLI,GAS_MEN_CLI)	
VALUES('CI004','MANUELA','RODRIGUEZ','11/11/1965','45824586','JR.LOS PINOS 124 SAN ISIDRO','F',85.78)	
INSERT INTO CLIENTES (COD_CLI,NOM_CLI ,APE_PAT_CLI ,FEC_NAC_CLI ,DNI_CLI ,DIR_CLI,SEX_CLI,GAS_MEN_CLI)	
VALUES('CI005','JUAN','RAMON','12/06/1970','00124578','JR.LOS LOROS 124 LOS OLIVOS','M',58.5)	
INSERT INTO CLIENTES (COD_CLI,NOM_CLI ,APE_PAT_CLI ,FEC_NAC_CLI ,DNI_CLI ,DIR_CLI,SEX_CLI,GAS_MEN_CLI)	
VALUES('CI006','LUCIO','PEREZ','01/01/1989','43335678','JR. PINAR 124 ATE VITARTE','M',120.2)	
INSERT INTO CLIENTES (COD_CLI,NOM_CLI ,APE_PAT_CLI ,FEC_NAC_CLI ,DNI_CLI ,DIR_CLI,SEX_CLI,GAS_MEN_CLI)	
VALUES('CI007','CARLOS','BUSTIOS','10/12/1970','34536045','AV.LA  MARINA 134 SAN MIGUEL','M',215.3)	
INSERT INTO CLIENTES (COD_CLI,NOM_CLI ,APE_PAT_CLI ,FEC_NAC_CLI ,DNI_CLI ,DIR_CLI,SEX_CLI,GAS_MEN_CLI)	
VALUES('CI008','ROSA','CADIZ','11/11/1972','98745876','CALLE SATURNO 1855 SANTIAGO DE SURCO','M',33.25)	
INSERT INTO CLIENTES (COD_CLI,NOM_CLI ,APE_PAT_CLI ,FEC_NAC_CLI ,DNI_CLI ,DIR_CLI,SEX_CLI,GAS_MEN_CLI)	
VALUES('CI009','ILIANA','LOPEZ-RODRIGUEZ','1/1/1966','45834586','JR.LOS PUYOLES 678 SAN ISIDRO','F',85.78)	
INSERT INTO CLIENTES (COD_CLI,NOM_CLI ,APE_PAT_CLI ,FEC_NAC_CLI ,DNI_CLI ,DIR_CLI,SEX_CLI,GAS_MEN_CLI)	
VALUES('CI010','ULISES','PONTE','3/6/1976','52314578','JR.LOS MOLLOS 723 LOS OLIVOS','M',358.5)	
GO
-- TABLA COMIDAS
INSERT INTO COMIDAS(COD_COMI,DES_COMI,PRE_COMI)	VALUES('CO001','ARROZ CON POLLO',8.5)	
INSERT INTO COMIDAS(COD_COMI,DES_COMI,PRE_COMI)	VALUES('CO002','CEVICHE',12.0)	
INSERT INTO COMIDAS(COD_COMI,DES_COMI,PRE_COMI)	VALUES('CO003','MONDONGUITO ITALIANO',9.0)
INSERT INTO COMIDAS(COD_COMI,DES_COMI,PRE_COMI)	VALUES('CO004','SECO DE CABRITO',10.5)
INSERT INTO COMIDAS(COD_COMI,DES_COMI,PRE_COMI)	VALUES('CO005','SUDADO DE PESCADO',12.0)
INSERT INTO COMIDAS(COD_COMI,DES_COMI,PRE_COMI) VALUES ('CO006', 'LOMO SALTADO', 20.00)
GO
--TABLA INGREDIENTES
INSERT INTO INGREDIENTES(COD_ING ,DES_ING ,TIP_ING ,FEC_INGRE_ING,RAZ_PRO_ING,RUC_PRO_ING)
VALUES('IN001','ARROZ','CEREALES','11/06/2004','EL MOLINO','45678921456')
INSERT INTO INGREDIENTES(COD_ING ,DES_ING ,TIP_ING ,FEC_INGRE_ING,RAZ_PRO_ING,RUC_PRO_ING)	
VALUES('IN002','SAL','SECOS','07/10/2004','METRO','22356458721')
INSERT INTO INGREDIENTES(COD_ING ,DES_ING ,TIP_ING ,FEC_INGRE_ING,RAZ_PRO_ING,RUC_PRO_ING)
VALUES('IN003','CEBOLLA','VERDURAS','07/12/2004','METRO','12345682')
INSERT INTO INGREDIENTES(COD_ING ,DES_ING ,TIP_ING ,FEC_INGRE_ING,RAZ_PRO_ING,RUC_PRO_ING)
VALUES('IN004','AZUCAR','SECOS','01/08/2004','METRO','12345682')
INSERT INTO INGREDIENTES(COD_ING ,DES_ING ,TIP_ING ,FEC_INGRE_ING,RAZ_PRO_ING,RUC_PRO_ING)
VALUES('IN005','POLLO','CARNES','07/12/2004','SAN FERNANDO','03254678952')
GO
--TABLA COMPROBANTES
INSERT INTO COMPROBANTES(FEC_COMP ,COD_VEND ,IGV_COMP ,MON_TOT_COMP ,COD_CLI)
VALUES('11/06/2004','C0235',11.4608,60.3200,'CI001')
INSERT INTO COMPROBANTES(FEC_COMP ,COD_VEND ,IGV_COMP ,MON_TOT_COMP ,COD_CLI)
VALUES('01/07/2004','C0456',28.5950,150.5000,'CI001')
INSERT INTO COMPROBANTES(FEC_COMP ,COD_VEND ,IGV_COMP ,MON_TOT_COMP ,COD_CLI)
VALUES('11/12/2004','C1254',44.7564,235.5600,'CI002')
INSERT INTO COMPROBANTES(FEC_COMP ,COD_VEND ,IGV_COMP ,MON_TOT_COMP ,COD_CLI)
VALUES('06/10/2004','C3580',6.4600,34.0000,'CI002')
INSERT INTO COMPROBANTES(FEC_COMP ,COD_VEND ,IGV_COMP ,MON_TOT_COMP ,COD_CLI)
VALUES('07/12/2004','C0357',18.9050,99.5000,'CI003')
INSERT INTO COMPROBANTES(FEC_COMP ,COD_VEND ,IGV_COMP ,MON_TOT_COMP ,COD_CLI)
VALUES('1/06/2004','C1254',44.7564,235.5600,'CI006')
INSERT INTO COMPROBANTES(FEC_COMP ,COD_VEND ,IGV_COMP ,MON_TOT_COMP ,COD_CLI)
VALUES('1/03/2004','C3580',6.4600,34.0000,'CI006')
INSERT INTO COMPROBANTES(FEC_COMP ,COD_VEND ,IGV_COMP ,MON_TOT_COMP ,COD_CLI)
VALUES('07/11/2004','C0357',18.9050,99.5000,'CI008')
GO
--TABLA DETALLE COMPROBANTES
INSERT INTO DETALLECOMPROBANTES(NRO_COMP,COD_COMI,CAN_VEN,MON_PAR)
VALUES('100','CO001',4,34)	
INSERT INTO DETALLECOMPROBANTES(NRO_COMP,COD_COMI,CAN_VEN,MON_PAR)	
VALUES('100','CO002',3,36)
INSERT INTO DETALLECOMPROBANTES(NRO_COMP,COD_COMI,CAN_VEN,MON_PAR)
VALUES('101','CO003',1,9)
INSERT INTO DETALLECOMPROBANTES(NRO_COMP,COD_COMI,CAN_VEN,MON_PAR)
VALUES('101','CO004',1,12)
INSERT INTO DETALLECOMPROBANTES(NRO_COMP,COD_COMI,CAN_VEN,MON_PAR)
VALUES('102','CO005',54,60)
GO
--TABLA DETALLE COMIDAS
INSERT INTO DETALLECOMIDAS(COD_COMI ,COD_ING,CANT_ING)	VALUES('CO002','IN001',5)	
INSERT INTO DETALLECOMIDAS(COD_COMI ,COD_ING,CANT_ING)	VALUES('CO002','IN003',5)
INSERT INTO DETALLECOMIDAS(COD_COMI ,COD_ING,CANT_ING)	VALUES('CO003','IN001',5)	
INSERT INTO DETALLECOMIDAS(COD_COMI ,COD_ING,CANT_ING)	VALUES('CO003','IN002',1)
INSERT INTO DETALLECOMIDAS(COD_COMI ,COD_ING,CANT_ING)	VALUES('CO003','IN003',5)
GO

/*************/
/* CONSULTAS */
/*************/

/* 1. Esta consulta devuelve un recordset con todos los campos de la tabla “CLIENTES”. */
	SELECT * FROM CLIENTES
	GO

/* 2. Esta consulta devuelve un recordset con los campos COD_CLI y NOM_CLI de la tabla “CLIENTES”. */
	SELECT COD_CLI, NOM_CLI FROM CLIENTES
	GO

/* 3. Esta consulta devuelve todos los campos de la tabla “CLIENTES” ordenados por el
      campo apellido paterno del cliente. */
	SELECT * FROM CLIENTES
	ORDER BY APE_PAT_CLI
	GO

/* 3. Esta consulta devuelve todos los campos de la tabla “CLIENTES” ordenados por el
      campo apellido paterno del cliente de forma descendente. */
	SELECT * FROM CLIENTES
	ORDER BY APE_PAT_CLI DESC
	GO

/* 5. Se pueden ordenar los registros por más de un campo, como por ejemplo: */

SELECT APE_PAT_CLI, NOM_CLI, DIR_CLI
	FROM CLIENTES
	ORDER BY APE_PAT_CLI, NOM_CLI
	GO

/***************/
/* PREDICADOS */
/**************/

/* 1. ALL */
	SELECT ALL * FROM CLIENTES
	SELECT * FROM CLIENTES
	GO

/* 2. TOP */

SELECT TOP 5 APE_PAT_CLI, NOM_CLI, DIR_CLI
	FROM CLIENTES
	ORDER BY APE_PAT_CLI DESC
	GO

/* 3. TOP PERCENT */
	SELECT TOP 20 PERCENT APE_PAT_CLI, NOM_CLI, DIR_CLI
	FROM CLIENTES
	ORDER BY APE_PAT_CLI DESC
	GO

/* 4. DISTINCT */
	SELECT DISTINCT APE_PAT_CLI, NOM_CLI
	FROM CLIENTES
	GO

/* 5. ALIAS*/
	SELECT DISTINCT APE_PAT_CLI AS APELLIDO_DEL_CLIENTE
	FROM CLIENTES
	SELECT DISTINCT APE_PAT_CLI AS 'APELLIDO DEL CLIENTE'
	FROM CLIENTES
	SELECT DISTINCT APE_PAT_CLI 'APELLIDO DEL CLIENTE'
	FROM CLIENTES
	GO

/************************************************/
/* Uso del Day, Month, Year y Concatenaciones */
/************************************************/

-- USO DEL CAMPO FECHA: DIA, MES Y AÑO
SELECT NOM_CLI, APE_PAT_CLI, DAY(FEC_NAC_CLI),
MONTH(FEC_NAC_CLI), YEAR(FEC_NAC_CLI)
FROM CLIENTES
GO
-- USANDO ETIQUETAS
SELECT NOM_CLI, APE_PAT_CLI, DAY(FEC_NAC_CLI) AS DIA,
MONTH(FEC_NAC_CLI) AS 'EL MES', YEAR(FEC_NAC_CLI) AS AÑO,
FEC_NAC_CLI
FROM CLIENTES
GO
-- USANDO CONCATENACIÓN
SELECT NOM_CLI + ' ' + APE_PAT_CLI AS CLIENTE, 
DAY(FEC_NAC_CLI) AS DIA,
MONTH(FEC_NAC_CLI) AS 'EL MES', 
YEAR(FEC_NAC_CLI) AS AÑO
FROM CLIENTES
GO
-- USANDO DATEDIFF Y GETDATE PARA OBTENER LA EDAD DE UN CLIENTE
SELECT COD_CLI, APE_PAT_CLI, FEC_NAC_CLI,
DATEDIFF(YY,FEC_NAC_CLI, GETDATE()) 'EDAD DEL USUARIO'
FROM CLIENTES
GO

/************************************/
/* PRODUCTO CARTESIANO E INNER JOIN */
/************************************/

-- Se selecciona todos los clientes
SELECT * FROM CLIENTES
GO
-- Se selecciona todos los comprobantes
SELECT * FROM COMPROBANTES
GO
-- Producto cartesiano entre 2 tablas
SELECT * 
FROM CLIENTES, COMPROBANTES
WHERE CLIENTES.COD_CLI = COMPROBANTES.COD_CLI
GO
-- Se selecciona los clientes que han efectuado compras
SELECT *
FROM CLIENTES INNER JOIN COMPROBANTES ON 
CLIENTES.COD_CLI = COMPROBANTES.COD_CLI
GO
-- Se selecciona los clientes que han efectuado compras mediante el uso de alias
SELECT *
FROM CLIENTES A INNER JOIN COMPROBANTES B ON 
A.COD_CLI = B.COD_CLI
GO

/**************/
/* EJERCICIOS */
/**************/

/*1. Muestre los datos de los clientes que han efectuado compras en el año 2004. */

SELECT W.COD_CLI, NOM_CLI + ' ' + APE_PAT_CLI AS CLIENTE, DIR_CLI, FEC_COMP
	FROM CLIENTES W INNER JOIN COMPROBANTES Z ON
	W.COD_CLI = Z.COD_CLI
	WHERE YEAR(FEC_COMP) = 2004
	GO

/*2. Muestre los datos de los clientes que han efectuado una compra en el año 2004 y que
     la comida consumida comience con la letra C ó M. ordénelo por el apellido paterno del
	 cliente y ante coincidencias por el nombre de ese cliente. */
	 
	SELECT W.COD_CLI, NOM_CLI + ' ' + APE_PAT_CLI AS CLIENTE, DIR_CLI, FEC_COMP, DES_COMI
	FROM ((CLIENTES W INNER JOIN COMPROBANTES Z ON
	W.COD_CLI = Z.COD_CLI) INNER JOIN DETALLECOMPROBANTES Y ON
	Z.NRO_COMP = Y.NRO_COMP ) INNER JOIN COMIDAS X ON
	Y.COD_COMI = X.COD_COMI
	WHERE YEAR(FEC_COMP) = 2004 AND (DES_COMI LIKE 'M%' OR DES_COMI LIKE 'C%')
	ORDER BY APE_PAT_CLI, NOM_CLI
	GO
	
	
/*3. Muestre los datos de las comidas en cuya preparación se ha utilizado arroz o frejoles. */

SELECT *
	FROM (COMIDAS A INNER JOIN DETALLECOMIDAS B ON
	A.COD_COMI = B.COD_COMI) INNER JOIN INGREDIENTES C ON
	B.COD_ING = C.COD_ING
	WHERE DES_ING = 'ARROZ' OR DES_ING = 'FREJOLES' 
	GO

View schema
Execution time: 0,14 sec, rows selected: 208, rows affected: 39, absolute service time: 0,42 sec, absolute service time: 0,42 sec