• Author / Uploaded
• asbeel_Ct

Citation preview

BASE DE DATOS I SEMANA12 IMPLEMENTACION DE BASE DE DATOS -LO PRIMERO EN IMPLEMENTACIONES ES EL MONTO A INVERTIR DE PARTE DE NUESTROS FUTUROS CLIENTES. -SQLWB HACE REFERENCIA SQL SERVER: COMPOSICION DE BASE DE DATOS EN SQL SERVER:

*-ARCH. DE DATOS

(*.MDF) -PRINCIPAL (NUNCA PUEDE FALTAR =1)(GUARDA TODOS LOS DATOS(SIST,USER)) (*.NDF) -SECUNDARIO(SI PUEDE FALTAR >=0)(SOLO GUARDA DATOS DEL USER SI SE LLENA EN UN DISCO PODEMOS CREAR OTRO EN OTRO DISCO)

-BD (*.LFD) (>=1) *-ARCH. LOGICO DE REGISTRO DE TRANSACCIONES (GUARDA LAS INSTRUCCIONES PARA LUEGO APLICARSE EN EL ARCHIVO DE DATOS) LO MAS RECOMENDABLE PARA SQL ES SEPARAR LA DATA DEL SISTEMA DE LA DATA DEL USUARIO SQL 2005 ES UNA SWEER QUE CONSIST EREN DATABASE ENGINE ANALAYSYS SERVICES(PARA CUBOS) SREPORTING SERVICES RSQL SERVER MOBILE INTEGGRATION SERVICES EN ESTE CASO VAMOS A USAR DATABASE ENGINE EN SERVER NAME ELEGIMOS LA MAQUINA QUE QUERAMOS SI KISIERAMOS CONECTARNOS A UNA PC REMOTA LOS PROTOCOLOS TCP IP DEBEN ESTAR ACTIVADOS, QUE POR SEGUNRIDAD SE ENCUENTRAN DESACTIVADOS. EN AUTHENTICATION NOS SIRVE PARA EXPRESAR EL TIPO DE SEGURIDAD EN ESTE CASO ELEGIMOS LA SEGURIDAD DE WINDOWS LUEGO PROCEDEMOS A CONECTARNOS. EN LA CARPETA SECURITY TENEMOS LA POSIBILIDAD DE CREAR USUARIOS. EN LA CARPETA REPLICATION TENEMOS LA POSIBILIDAD DR REPLICAR LA BD, ENTRE OTRAS HERRAMIENTAS QUE ENCONTRAMOS. AHORA PROCEDEMOS A CREAR UNA BASE DE DATOS ALA HORA DE PONER EL NOMBRE DE NUESTRA BASE DE DATOS, EN LA GRILLA FILEGROUP EL *MDF() SIEMPRE ES PRIMARY ASIENDO CLICK EN ADD PODEMOS AGREGARLE MAS ARCHIBOS SECUNDARIOS PARA POSIBLEMENTE GUARDAR DATA DE OTRAS AREAS EN ESTE CASO EL NOMBRE VENTAS_DATA Y EN FILE TYPE DATA EN FILEGROUP LE ASIGNAMOS EL FILE GROUP DONDE QUERAMOS QUE SE GUARDE AL FORMULARIO QUE NOS PARECE LE ASIGNAMOS EL NOMBRE DE VENTAS Y EN OPTIONS NO MARCAMOS NADA Y ACEPTAMOS. ES IMPORTANTE NO GUARDARLO EN EL FILEGROUP PRIMARY! YA QUE QUEREMOS ASIGANARLE UNA UBICACION DIFERENTE DE ESTE.

AHORA LOS TAMAÑOS DE INICIO. SE DICE QUE LO LOS ARCHIVOS LOGICOS DEBEN TENER LA TERCERA PARTE DE LOS ARCHIVOS DE DATOS EL TAMAÑO DE INICIO LO CALCULAMOS CONTANDO LAS FILAS QUE SE MUEVEN EN CADA FILA. TENER EN CUENTA QUE NUESTRO ARCHIVO DE DATOS SECUNDARIO VA A CRECER MAS. LUEGO EL AUTOROW TENEMOS QUE DEFINIR EN CUANTO POR TANTO VA A AUMENTAR UNA VEZ QUE SE ACABE EL TAMAÑO INICIAL EN ESTE CASO LE PONEMOS 2 MEGAS Y SIN RESTRICCION. LUEGO EL PARCHNAME ES LA UBICACION DONDE GRABAREMOS NUESTROS TIPOS DE ARCHIVO EN OTRA CARPETA O EN OTRO DISCO DURO EN ESTE CASO LO COLOCAREMOS EN EL DISCO C: LUEGO EN LA FICHA SCRIPT QUE SE ENCUENTRA EN LA PARTE DE ARRIVA SELECCIONAMOS SCRIPT ACTION TO NEW QUERY WINDOW PARA COPIAR LO QUE HEMOS HECHO EN UN SCRIPT.

.dIeGo

Página 1

CREATE DATABASE [BD2453] ON PRIMARY ( NAME = N'BD2453', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\BD2453.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ), FILEGROUP [VENTAS] ( NAME = N'VENTAS_DATA', FILENAME = N'C:\VENTAS_DATA.ndf' , SIZE = 3072KB , FILEGROWTH = 2048KB ) LOG ON ( NAME = N'BD2453_log', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\BD2453_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%) GO

SEMANA13 --USO DE CONSTRAINTS -------------------create database bdconstraints go use bdconstraints go create table clientes ( codcli char(5) not null, nomcli varchar(60) ) go create table ordenes ( numorden int not null, fecha datetime ) go --INSERTANDO FILAS EN LA TABLA CLIENTES insert insert insert insert insert GO

into into into into into

clientes clientes clientes clientes clientes

(codcli, (codcli, (codcli, (codcli, (codcli,

nomcli) nomcli) nomcli) nomcli) nomcli)

values values values values values

('C0003','PEDRO DIAZ') ('C0008','JOSE FERNDANDEZ') ('C0001','CINTHYA ROJAS') ('C0005','CINTHYA ROJAS') ('C0002','CINTHYA ROJAS')

SELECT * FROM CLIENTES GO --VEMOS QUE COMO NUESTRA TABLA CLIENTES NO CUENTA CON UNA PRIMARY KEY --NUESTROS DATOS INGRESADOS SE MUESTRAN DE FORMA DESORDENADA --Y PODRIAMOS INGRESAR CODIGOS DUPLICADOS --AHORA PROCEDEMOS A CREAR LA CLAVE PRIMARIA EN LA TABLA CLIENTES MEDIANTE --UN CONSTRAINT --OJO QUE QUE ESTA FORMA FUNCIONA SI A LA HORA DE CREAR EL CODCLI LE PUSIMOS NOT NULL --SI NO HUBIERAMOS TENIDO QUE CAMBAR EL NULL POR DEFECTO A NOT NULL CON EL SGT COMANDO /* ALTER TABLE CLIENTES ALTER COLUMN codcli char(5) not null

.dIeGo

Página 2

go */ ALTER TABLE CLIENTES ADD CONSTRAINT PK_CODCLI PRIMARY KEY(codcli) GO --AHORA PODEMOS APRECIAR QUE NUESTROS SELECT * FROM CLIENTES GO

REGISTROS ESTAN ORDENADOS

--INSERTAMOS AHORA UN REGISTRO CON UN CODIGO ENTRE LOS CODIGOS EXISTENTES INSERT INTO CLIENTES VALUES ('C0004','FERNANDO VILLA') GO --INSERTANDO LOS CLIENTES 'C0006' Y 'C0007' UTILIZANDO --INSERT .... SELECT INSERT INTO CLIENTES SELECT 'C0006','ROGELIO RUIZ' UNION ALL SELECT 'C0007','WALTER MEIER' GO SELECT * FROM CLIENTES GO /* USE NORTHWIND SELECT TOP 2 PRODUCTID, PRODUCTNAME FROM PRODUCTS UNION ALL SELECT TOP 4 EMPLOYEEID, LASTNAME FROM EMPLOYEES --NO SE PUEDO USAR ORDEY BY EN LA CLAUSULA UNION ALL */ /* PLOBANDO LA TABLA ORDENES DESDE LA TABLA ORDERS DE LA BASE DE DATOS NOTHWIND INSERT INTO ORDENES SELECT ORDERID, ORDERDATE FROM NORTHWIND..ORDERS GO SELECT * FROM ORDENES --TRUNCATE TABLE ELIMINA LA INFORMACION DE UNA TABLA --DE UNA MANERA RAPIDA Y EFICAZ (MEJOR QUE EL DELETE) TRUNCATE TABLE ORDENES */ --ADICIONAR UNA NUEVA COLUMNA A LA TABLA ORDENES QUE --PERMITE ENLAZARLA CON LA TABLA CLIENTES ALTER TABLE ORDENES ADD CODCLI CHAR(5) GO SELECT * FROM ORDENES GO --INSERTANDO FILAS EN LA TABLA ORDENES INSERT INTO ORDENES VALUES(1001,GETDATE()-2,'C0003') INSERT INTO ORDENES VALUES(1002,GETDATE()-2,'C0006') INSERT INTO ORDENES VALUES(1003,GETDATE()-1,'C0001') INSERT INTO ORDENES VALUES(1004,GETDATE()-1,'C0005') INSERT INTO ORDENES VALUES(1005,GETDATE(),'C0010') SELECT * FROM ORDENES --CREANDO LA CLAVE FORANEA ENTRE CLIENTES Y ORDENES ALTER TABLE ORDENES WITH NOCHECK ADD CONSTRAINT FK_CODCLI FOREIGN KEY (CODCLI)

.dIeGo

Página 3

REFERENCES CLIENTES(CODCLI) ON UPDATE CASCADE --ON DELETE NO ACTION GO --NOTA WITH NOCHECK, SE UTILIZA SPÑP AÑ CREAR UN NUEVO CONTRAINT QUE PUEDE SER DE --TIPO CHECK --DESACTIVANDO CONSTRAINTS INSERT INTO ORDENES VALUES (1006,GETDATE(),'C0020') GO --ERROR DE CONTRAINT, CODIGO DE CLIENTE NO EXISTE ALTER TABLE ORDENES NOCHECK CONSTRAINT FK_CODCLI GO INSERT INTO ORDENES VALUES (1006,GETDATE(),'C0020') GO --OK SELECT * FROM ORDENES --ACTIVANDO CONTRAINTS ALTER TABLE ORDENES CHECK CONSTRAINT FK_CODCLI GO --LISTANDO SOLO LOS CLIENTE QUE TIENEN ORDENES SELECT * FROM CLIENTES WHERE CODCLI IN (SELECT DISTINCT CODCLI FROM ORDENES) GO --CLIENTE 'C0001' TIENE LA ORDEN 1003 UPDATE CLIENTES SET CODCLI='C0010' WHERE CODCLI='C0001' GO SELECT * FROM ORDENES --------------------------------------------------------------------------------USE NORTHWIND GO --TRANSACT SQL --LISTAR LOS "N" MEJORES EMPLEADOS QUE HAYAN --REALIADO LOS MAS ALTOS IMPORTANTES DE ORDENES --EN EL AÑO 1996. SE DEBE MOSTRAR EL NOMBRE --COMPLETO: NOMBRE, APELLIDO Y EL NUMERO DE LA --ORDEN Y SU IMPORTE CORRESPONDIENTE. DECLARE @N INT SET @N=3 SELECT TOP (@N) FIRSTNAME+', ' +LASTNAME AS EMPLEADO, O.ORDERID,IMPORTE=SUM(D.QUANTITY*D.UNITPRICE) FROM EMPLOYEES E INNER JOIN ORDERS O ON E.EMPLOYEEID=O.EMPLOYEEID INNER JOIN [ORDER DETAILS] D ON O.ORDERID=D.ORDERID WHERE YEAR(O.ORDERDATE)=1996 GROUP BY FIRSTNAME+', ' +LASTNAME,O.ORDERID ORDER BY IMPORTE DESC --LISTAR EL CODIGO, NOMBRE DEL PRODUCTO Y LA --CANTIDAD DE VECES QUE UN PRODUCTO HAYA SIDO --VENDIDO DE ACUERDO A UN CODIGO DE EMPLEADO Y --A UN CODIGO DE CATEGORIA. SELECT P.PRODUCTID, PRODUCTNAME, CANT=COUNT(*)

.dIeGo

Página 4

FROM PRODUCTS P INNER JOIN [ORDER DETAILS] D ON P.PRODUCTID=D.PRODUCTID INNER JOIN ORDERS O ON O.ORDERID=D.ORDERID WHERE O.EMPLOYEEID=1 AND P.CATEGORYID=1 GROUP BY P.PRODUCTID,PRODUCTNAME GO --LISTAR EL AÑO, ULTIMO NUMERO DE ORDEN EN EL AÑO -- Y EL NOBRE DEL CLIENTE A QUIEN LE PERTEECE LA --ORDEN. SELECT AÑO=YEAR(ORDERDATE),ORDERID=MAX(ORDERID)--,COMPANYNAME FROM ORDERS O --INNER JOIN CUSTOMERS C --ON O.CUSTOMERID=C.CUSTOMERID GROUP BY YEAR(ORDERDATE)--,COMPANYNAME ORDER BY ORDERID GO ----CO SUBCONSULTAS SELECT T.AÑO, T.ORDEN, C.COMPANYNAME FROM CUSTOMERS C INNER JOIN ORDERS O ON C.CUSTOMERID=O.CUSTOMERID INNER JOIN (SELECT AÑO=YEAR(ORDERDATE),ORDEN=MAX(ORDERID) FROM ORDERS GROUP BY YEAR(ORDERDATE)) AS T ON O.ORDERID=T.ORDEN GO ---------------------------------------------------------OTRA FORMA CREATE VIEW VISTA1 AS SELECT AÑO=YEAR(ORDERDATE),ORDEN=MAX(ORDERID) FROM ORDERS GROUP BY YEAR(ORDERDATE) GO SELECT T.AÑO, T.ORDEN, C.COMPANYNAME FROM CUSTOMERS C INNER JOIN ORDERS O ON C.CUSTOMERID=O.CUSTOMERID INNER JOIN VISTA1 AS T ON O.ORDERID=T.ORDEN GO

.dIeGo

Página 5

SEMANA14 --CONSULTAS Y SUBCONSULTAS /* LISTAR LAS ORDENES DEL ULTIMO MES DEL ULTIMO AÑO DE LA TABLA ORDERS */ --PRIMERO AVERIGUAMOS EL ULTIMO MES DEL ULTIMO AÑO SELECT TOP 1 YEAR(ORDERDATE) FROM ORDERS ORDER BY YEAR(ORDERDATE) DESC GO SELECT MAX(YEAR(ORDERDATE)) FROM ORDERS GO -- 1998 --MES SELECT TOP 1 MONTH(ORDERDATE) FROM ORDERS WHERE YEAR(ORDERDATE)=1998 ORDER BY MONTH(ORDERDATE) DESC GO SELECT MAX(MONTH(ORDERDATE)) FROM ORDERS WHERE YEAR(ORDERDATE)=1998 GO -- 5 = MAYO SELECT ORDERID, ORDERDATE, CUSTOMERID FROM ORDERS WHERE YEAR(ORDERDATE) =1998 AND MONTH(ORDERDATE)=5 GO /* LISTAR LOS NOMBRES DE LOS PRODUCTOS Y LA CANTIDAD VENDIDA DEL ULTIMO PEDIDO REALIZADO POR EL EMPLEADO MAS ANTIGUO (UTILICE EL CAMPO HIREDATE DE EMPLOYEES) */ SELECT TOP 1 EMPLOYEEID, HIREDATE FROM EMPLOYEES ORDER BY HIREDATE ASC --3 SELECT MAX(ORDERID) FROM ORDERS WHERE EMPLOYEEID=3 --11063 SELECT PRODUCTNAME, QUANTITY FROM [ORDER DETAILS] D INNER JOIN PRODUCTS P ON D.PRODUCTID=P.PRODUCTID WHERE ORDERID=11063 GO -- "O" SELECT PRODUCTNAME, QUANTITY FROM [ORDER DETAILS] D, PRODUCTS P WHERE D.PRODUCTID=P.PRODUCTID AND ORDERID=11063 GO /*

.dIeGo

Página 6

LISTAR LAS VENTAS ACUMULADAS DE LOS CLIENTES POR PAIS. SE DEBE LISTAR EL NOMBRE DEL PAIS, LA CANTIDAD TOTAL DE ORDENES ASI COMO EL ACUMULADO GENERAL DE LAS VENTAS POR PAIS. */ SELECT COUNTRY,CANT=COUNT(DISTINCT(O.ORDERID)),VENTAS=SUM(UnitPrice*Quantity) FROM [Order Details] OD INNER JOIN ORDERS O ON O.ORDERID=OD.ORDERID INNER JOIN CUSTOMERS C ON O.CUSTOMERID=C.CUSTOMERID GROUP BY COUNTRY GO SELECT COUNT(ORDERID) FROM ORDERS SELECT COUNT(ORDERID) FROM [Order Details] SELECT COUNT(DISTINCT(ORDERID)) FROM [Order Details] --OJO CON EL DISTINCT QUE EN EL DETALLE SE REPITEN LAS ORDENES /* DEL RESULTADO ANTERIOR AQUELLOS PAISES QUE SUPEREN LOS 50000 EN LAS VENTAS ACUMULADAS */ SELECT COUNTRY,CANT=COUNT(DISTINCT(O.ORDERID)),VENTAS=SUM(UnitPrice*Quantity) FROM [Order Details] OD INNER JOIN ORDERS O ON O.ORDERID=OD.ORDERID INNER JOIN CUSTOMERS C ON O.CUSTOMERID=C.CUSTOMERID --WHERE GROUP BY COUNTRY HAVING SUM(UnitPrice*Quantity)>50000 ORDER BY VENTAS DESC GO /* DEL RESULTADO ANTERIOR EL PAIS QUE MAS PEDIDOS HAYA REALIZADO */ SELECT TOP 1 WITH TIES COUNTRY,CANT=COUNT(DISTINCT(O.ORDERID)),VENTAS=SUM(UnitPrice*Quantity) FROM [Order Details] OD INNER JOIN ORDERS O ON O.ORDERID=OD.ORDERID INNER JOIN CUSTOMERS C ON O.CUSTOMERID=C.CUSTOMERID --WHERE GROUP BY COUNTRY HAVING SUM(UnitPrice*Quantity)>50000 ORDER BY CANT DESC GO /* CREATE VIEW V_VISTA1 AS SELECT COUNTRY,CANT=COUNT(DISTINCT(O.ORDERID)),VENTAS=SUM(UnitPrice*Quantity) FROM [Order Details] OD INNER JOIN ORDERS O ON O.ORDERID=OD.ORDERID INNER JOIN CUSTOMERS C ON O.CUSTOMERID=C.CUSTOMERID GROUP BY COUNTRY GO */ SELECT * FROM V_VISTA1 WHERE VENTAS > 50000 GO

.dIeGo

Página 7

SELECT TOP 1 WITH TIES * FROM V_VISTA1 ORDER BY CANT DESC GO -----------------------------------------------------------/* LISTAR LA CANTIDAD DE PEDIDOS REALIZADOS POR CADA DIA DE LA SEMANA (DATENMAE) REALIZADOS EN EL AÑO 1997. DEBERA APARECER ORDENADO POR LOS DIAS DE SEMANA ES DECIR: LUNES, MARTES, ... DOMINGO */ --PREFIJO DE FECHA, PARTE DE FECHA, DATEPART --DAY OF WEEK DW, MONTH MM -------------------------------------------------FORMA CORRECTA SET LANGUAGE SPANISH SELECT DATENAME(MM,GETDATE()) SELECT DATENAME(DW,GETDATE()) -------------------------------------------------FORMA INCORRECTA SELECT DATENAME(YY,GETDATE()) SELECT DATENAME(DD,GETDATE()) -----------------------------------------------SELECT --DATEPART(DW,ORDERDATE), DATENAME(DW,ORDERDATE),CANT=COUNT(ORDERID) FROM ORDERS WHERE YEAR(ORDERDATE)=1997 GROUP BY DATENAME(DW,ORDERDATE), DATEPART(DW,ORDERDATE) ORDER BY DATEPART(DW,ORDERDATE)I GO ---------------------------------------------------------------------------------SUBCONSULTAS /* 1. A NIVEL DE CAMPO. SELECT , ALIAS1(SELECT UN_VALOR .....), ALIAS2=(SELECT .........) FROM TABLA_PRINCIPAL ......... 2. A NIVEL DE TABLA (TABLA DERIVADA) SELECT , FROM TABLA_PRINCIPAL T, (SELECT FROM TABLAS.........) AS X WHERE T.CAMPOCOMUN=X.CAMPOCOMUN ..................................... 3. A NIVEL DE FILTRO(WHERE) SELECT FROM TABLA_PRINCIPAL WHERE CAMPO OPERATDOR (SELECT UNA_COLUMNA) ............... OPERADOR: =,=, EL SELECT SOLO TRAE UN VALOR IN, EL SELECT PUEDE DEVOLVER MUCHOS VALORES */ --EJEMPLOS: /*

.dIeGo

Página 8

HACIENDO USO DE SUBCONSULTAS, RESOLVER: LISTAR EL NOMBRE DE LA CATEGORIA CON LA CANTIDAD DE PRODUCTOS PERTENECIENTE A CADA CATEGORIA. */ SELECT C.CATEGORYNAME, CANT=(SELECT COUNT(*) FROM PRODUCTS WHERE CATEGORYID=C.CATEGORYID) FROM CATEGORIES C --FORMA SIN SUBCONSULTA SELECT C.CATEGORYNAME, CANT=COUNT(*) FROM CATEGORIES C INNER JOIN PRODUCTS P ON C.CATEGORYID=P.CATEGORYID GROUP BY C.CATEGORYNAME GO /* LISTAR EL APELLIDO DEL EMPLEADO, LA CANTIDAD DE ORDENES REALIZADA EN 1996, Y LA CANTIDAD DE ORDENES REALIZADAS EN 1997 Y LA CANTIDAD DE ORDENES REALIZADAS EN 1998 */ SELECT LASTNAME, C_1996=(SELECT COUNT(*) FROM ORDERS EMPLOYEEID=E.EMPLOYEEID AND YEAR(ORDERDATE)=1996), C_1997=(SELECT COUNT(*) FROM ORDERS

WHERE WHERE

EMPLOYEEID=E.EMPLOYEEID AND YEAR(ORDERDATE)=1997), C_1998=(SELECT COUNT(*) FROM ORDERS EMPLOYEEID=E.EMPLOYEEID AND YEAR(ORDERDATE)=1998) FROM EMPLOYEES E GO

WHERE

/* LISTAR EL NOMBRE DEL PRODUCTO MAS VENDIDO EN EL AÑO 1997. MAS VENDIDO POR LA MAYOR CANTIDAD DE VECES QUE APARECE EN LA TABLA [ORDER DETAILS] UTILICE SUBCONSULTAS A NIVEL DE TABLA */ SELECT TOP 1 WITH TIES PRODUCTID, CANT=COUNT(D.ORDERID) FROM [ORDER DETAILS] D INNER JOIN ORDERS O ON D.ORDERID=O.ORDERID WHERE YEAR(ORDERDATE)=1997 GROUP BY PRODUCTID ORDER BY CANT DESC GO --SUBCONSULTA A NIVEL DE TABLA SELECT P.PRODUCTNAME FROM PRODUCTS P INNER JOIN ( SELECT TOP 1 WITH TIES PRODUCTID, CANT=COUNT(D.ORDERID) FROM [ORDER DETAILS] D INNER JOIN ORDERS O ON D.ORDERID=O.ORDERID WHERE YEAR(ORDERDATE)=1997 GROUP BY PRODUCTID ORDER BY CANT DESC ) AS X ON P.PRODUCTID=X.PRODUCTID GO -------------------------------------CONVERTIRLA A NIVEL DE FILTRO

.dIeGo

Página 9

SELECT PRODUCTNAME FROM PRODUCTS WHERE PRODUCTID=(SELECT TOP 1 WITH TIES PRODUCTID FROM [ORDER DETAILS] D INNER JOIN ORDERS O ON D.ORDERID=O.ORDERID WHERE YEAR(ORDERDATE)=1997 GROUP BY PRODUCTID ORDER BY COUNT(D.ORDERID) DESC) SEMANA15 --SUBCONSULTAS --1ERA PREGUNTA --LISTAR EL ULTIMO PEDIDO REALIZADO POR CADA CLIENTE EN UN AÑO DETERMINADO. --DEBERA MOSTRAR EL NUMERO DE PEDIDO, NOMBRE DEL CLIENTE Y LA FECHA --EJEMPLO AÑO 1997 SELECT O.ORDERID, C.COMPANYNAME, O.ORDERDATE FROM CUSTOMERS C INNER JOIN ORDERS O ON C.CUSTOMERID=O.CUSTOMERID WHERE O.ORDERID IN (SELECT MAX(ORDERID) FROM ORDERS WHERE YEAR(ORDERDATE)=1997 GROUP BY CUSTOMERID) --2DA PREGUNTA --LISTAR EL NOMBRE DEL CLIENTE, LA CANTIDAD DE PEDIDOS REALIZADOS EN EL AÑO: --1996,1997,1998, DE ACUERDO A UN PAIS DETERMINADO --EJEMPLO CLIENTES DE MEXICO SELECT C.COMPANYNAME, C_1996=(SELECT COUNT(*) FROM ORDERS

WHERE

CUSTOMERID=C.CUSTOMERID AND YEAR(ORDERDATE)=1996), C_1997=(SELECT COUNT(*) FROM ORDERS CUSTOMERID=C.CUSTOMERID AND YEAR(ORDERDATE)=1997), C_1998=(SELECT COUNT(*) FROM ORDERS

WHERE WHERE

CUSTOMERID=C.CUSTOMERID AND YEAR(ORDERDATE)=1998) FROM CUSTOMERS C WHERE COUNTRY='MEXICO' --1RA SOLUCION --NOTA LAS VISTAS NO OCUPAN ESPACIO FISICO CREATE VIEW VISTA1 AS SELECT C.COMPANYNAME, C_1996=(SELECT COUNT(*) FROM ORDERS

WHERE

CUSTOMERID=C.CUSTOMERID AND YEAR(ORDERDATE)=1996), C_1997=(SELECT COUNT(*) FROM ORDERS CUSTOMERID=C.CUSTOMERID AND YEAR(ORDERDATE)=1997), C_1998=(SELECT COUNT(*) FROM ORDERS

WHERE WHERE

CUSTOMERID=C.CUSTOMERID AND YEAR(ORDERDATE)=1998) FROM CUSTOMERS C WHERE COUNTRY='MEXICO' GO SELECT *,TOTAL=C_1996+C_1997+C_1998 FROM VISTA1 UNION ALL SELECT '', SUM(C_1996) 'C_1996',SUM(C_1997),SUM(C_1998),SUM(C_1996+C_1997+C_1998) FROM VISTA1

.dIeGo

Página 10

--2DA SOLUCION SELECT C.COMPANYNAME, C_1996=(SELECT COUNT(*) FROM ORDERS CUSTOMERID=C.CUSTOMERID AND YEAR(ORDERDATE)=1996), C_1997=(SELECT COUNT(*) FROM ORDERS

WHERE WHERE

CUSTOMERID=C.CUSTOMERID AND YEAR(ORDERDATE)=1997), C_1998=(SELECT COUNT(*) FROM ORDERS CUSTOMERID=C.CUSTOMERID AND YEAR(ORDERDATE)=1998)

WHERE

INTO #TEMPO1 -- TABLA TEMPORAL # DURA EL TIEMPO DE LA SESION DEL USUARIO CUANDO CIERRA SU SESION SE ELIMINE FROM CUSTOMERS C WHERE COUNTRY='MEXICO' GO SELECT *,TOTAL=C_1996+C_1997+C_1998 FROM #TEMPO1 UNION ALL SELECT '', SUM(C_1996) 'C_1996',SUM(C_1997),SUM(C_1998),SUM(C_1996+C_1997+C_1998) FROM #TEMPO1 --FORZANDO A ELIMINAR LA TABLA TEMPORAL PRIVADA DROP TABLE #TEMPO1 GO -------------------------------------------------------------USO DE VISTAS----------------------------------------------------------------/* ES UN OBJETO DE SQL SERVER QUE SOLO CONSUME RECURSOS CUANDO ES LLAMADA DENTRO DE UNA INSTRUCCION SELECT, POR EL CUAL ES DENOMINADO TABLA VIRTUAL. ESTE OBJETO SOLO PUEDE DEVOLVER UN CONJUNTO DE DATOS, NO PERMITE EL USO DE LA CLAUSSULA ORDER BY (SALVO QUE SE UTILICE TOP),COMPUTE,COMPUTE BY, SINTAXIS: CREATE VIEW NOMBRE_VISTA WITH ENCRYPTION, SHEMABINDIG --EVITA QUE BOREN LAS TABLAS O COLUMNAS QUE USE LA VISTA AS --UNICA INSTRUCCION SELECT GO NOTA: UNA VISTA SERA ACTUALIZABLE MIENTRAS NO UTILICE COLUMNA CALCULADAS, FUNCIONES DE AGREGADO O PROVENGA DE MAS DE 1 TABLA */ --LAS VISTAS TAMBIEN PERMITE QUE PODAMOS MOSTRAR PARTES DE LA INFORMACION DE NUESTRA TABLAS SELECT * INTO TMP_PROD FROM PRODUCTS GO CREATE PROC LISTAR_PROD_CAT @CAT INT AS SELECT PRODUCTID, PRODUCTNAME,UNITPRICE, UNITSINSTOCK FROM TMP_PROD WHERE CATEGORYID=@CAT GO EXEC LISTAR_PROD_CAT 7 GO --CAMBIANDO EL NOMBRE DE UNA COLUMNA SP_RENAME 'TMP_PROD.UNITPRICE','PRECIO','COLUMN'

.dIeGo

Página 11

GO EXEC LISTAR_PROD_CAT 7 GO SP_RENAME 'TMP_PROD.PRECIO','UNITPRICE','COLUMN' GO EXEC LISTAR_PROD_CAT 7 GO --UTILIZANDO SCHEMABINDING CREATE VIEW V_PRODUCTS WITH SCHEMABINDING AS SELECT PRODUCTID, PRODUCTNAME, UNITPRICE,UNITSINSTOCK FROM DBO.TMP_PROD GO --AL INTENTAR RENOMBRAR LA COLUMNA UNITPRICE NUEVAMENTE --SQL NOS IMPEDIRA POR QUE ESTA COLUMNA ESTA ENLAZA AL --ESQUEMA SP_RENAME 'TMP_PROD.UNITPRICE','PRECIO','COLUMN' DROP TABLE TMP_PROD GO --CAMBIANDO EL NOMBRE DEL PRODUCTO 5 POR 'COMPUTADORA' "VISTA ACTUALIZABLE" SELECT * FROM V_PRODUCTS WHERE PRODUCTID=5 SELECT * FROM TMP_PROD WHERE PRODUCTID=5 GO UPDATE V_PRODUCTS SET PRODUCTNAME='COMPUTADORAS' WHERE PRODUCTID=5 GO SELECT * FROM V_PRODUCTS WHERE PRODUCTID=5 SELECT * FROM TMP_PROD WHERE PRODUCTID=5 GO CREATE VIEW V_PRODUCTOS_PRECIO AS SELECT CODIGO=PRODUCTID,NOMPROD=PRODUCTNAME, PRECIO_COM=UNITPRICE, PRECIO_VTA=UNITPRICE*1.25 FROM TMP_PROD GO SELECT * FROM V_PRODUCTOS_PRECIO GO --INSERTANDO UN NUEVO PRODUCTO --ERROR POR UTILIZAR UNA COLIMNA DERIVADA O CALCULADA INSERT INTO V_PRODUCTOS_PRECIO VALUES('LAPTOP HP PAVILION TX1330LA',2500) GO --SELECT 2500*1.25 --ESTE COMANDO SI ES VALIDO UPDATE V_PRODUCTOS_PRECIO SET PRECIO_COM=2500 WHERE CODIGO=1 GO SELECT * FROM V_PRODUCTOS_PRECIO GO

.dIeGo

Página 12

------------------------CREATE VIEW V_TABLAS AS SELECT EMPLOYEEID,FIRSTNAME+', '+LASTNAME AS EMPLEADO FROM EMPLOYEES UNION ALL SELECT PRODUCTID,PRODUCTNAME FROM PRODUCTS GO ----COMPUTE SELECT CUSTOMERID,COMPANYNAME FROM CUSTOMERS COMPUTE COUNT(CUSTOMERID) --COMPUTE GO SELECT CUSTOMERID,COMPANYNAME,COUNTRY FROM CUSTOMERS ORDER BY COUNTRY COMPUTE COUNT(CUSTOMERID) BY COUNTRY --COMPUTE BY --ES OBLIGATORIO EL ORDENAMIENTO POR ESTA ULTIMA COLUMNA GO SEMANA16 CREATE DATABASE BDTRIGGERS GO USE BDTRIGGERS GO SELECT CUSTOMERID AS COD_CLI, COMPANYNAME AS NOM_CLI, PAIS=COUNTRY INTO CLIENTES FROM NORTHWIND.DBO.CUSTOMERS GO SELECT NUM_ORDEN=ORDERID, FECHA=ORDERDATE, COD_CLI=CUSTOMERID INTO ORDENES FROM NORTHWIND.DBO.ORDERS GO /* CREAR UN TRIGGER QUE NO PERMITA LA DUPLICACION DE NOMBRES DE CLIENTES AL MOMENTO DE LA INSERCION DE UN NUEVO CLIENTE. */ CREATE TRIGGER TR_01 ON CLIENTES AFTER INSERT AS SELECT 'TABLA INSERTED' AS TABLA, * FROM INSERTED SELECT 'TABLA CLIENTES' AS TABLA, * FROM CLIENTES DECLARE @NOMBRE VARCHAR(40) SELECT @NOMBRE=NOM_CLI FROM INSERTED -- AVERIGUANDO SI EL NOMBRE ESTÁ DUPLICADO DECLARE @CONTA INT SELECT @CONTA=COUNT(*) FROM CLIENTES WHERE NOM_CLI=@NOMBRE

.dIeGo

Página 13

IF @CONTA>=2 BEGIN PRINT 'EL NOMBRE: '+@NOMBRE+ ' ESTÁ DUPLICADO' ROLLBACK END

GO -- INSERTANDO UN NUEVO CLIENTE INSERT CLIENTES VALUES('ABCDE','CLIENTE ABCDE','PERU') GO -- VOLVEMOS A INSERTAR UN CLIENTE INSERT CLIENTES VALUES('XWSDE','CLIENTE ABCDE','PERU') GO /* CREAR UN TRIGGER QUE NO PERMITA LA ELIMINACION DE CLIENTES SI ES QUE EL CLIENTE TIENE ORDENES. */ CREATE TRIGGER TR_02 ON CLIENTES AFTER DELETE AS SELECT * FROM DELETED SELECT * FROM CLIENTES DECLARE @COD CHAR(5) SELECT @COD=COD_CLI FROM DELETED IF (SELECT COUNT(*) FROM ORDENES WHERE COD_CLI=@COD)>0 BEGIN PRINT 'NO SE PERMITE EL BORRADO DE CLIENTES '+ ' QUE TIENEN ORDENES' ROLLBACK END ELSE PRINT 'CLIENTE: '+@COD+ ' ELIMINADO (0) ORDENES' GO DELETE CLIENTES WHERE COD_CLI='ANTON' SELECT * FROM CLIENTES DELETE CLIENTES WHERE COD_CLI='ABCDE' SELECT * FROM CLIENTES /* DESACTIVE AL TRIGGER ANTERIOR, Y CREE UNO NUEVO QUE PERMITA ELIMINAR A UN CLIENTE Y A SUS ORDENES SOLO SI EL CLIENTE TIENE MENOS DE 5 ORDENES. */ -- DISABLE TRIGGER NOMBRE_TRIGGER ON NOMBRE_TABLA DISABLE TRIGGER TR_02 ON CLIENTES GO CREATE TRIGGER TR_03 ON CLIENTES AFTER DELETE AS DECLARE @COD CHAR(5) SELECT @COD=COD_CLI FROM DELETED DECLARE @CANT INT SELECT @CANT=COUNT(*) FROM ORDENES WHERE COD_CLI=@COD -- SI EL CLIENTE TIENE MAS DE 5 ORDENES IF @CANT>=5 BEGIN -- NO SE PERMITE LA ELIMINACION PRINT 'ERROR, EL CLIENTE: '+@COD+

.dIeGo

Página 14

' TIENE: '+STR(@CANT,3)+ ' ORDENES' ROLLBACK

END ELSE -- CASO CONTRARIO BEGIN -- ELIMINAMOS TAMBIEN SUS ORDENES PRINT 'ELIMINANDO LAS ORDENES DE: '+@COD DELETE ORDENES WHERE COD_CLI=@COD PRINT 'CLIENTE ELIMINADO' END GO -- LISTAR LOS CODIGOS DE LOS CLIENTES Y LA CANTIDAD -- DE ORDENES SELECT COD_CLI, CANTIDAD=COUNT(*) FROM ORDENES GROUP BY COD_CLI ORDER BY CANTIDAD DESC -- THEBI, ANATR --> 4 ORDENES -- VINET, FOLIG --> 5 ORDENES SELECT * FROM ORDENES WHERE COD_CLI='THEBI' SELECT * FROM CLIENTES WHERE COD_CLI='THEBI' DELETE CLIENTES WHERE COD_CLI='THEBI' GO SELECT * FROM ORDENES WHERE COD_CLI='VINET' SELECT * FROM CLIENTES WHERE COD_CLI='VINET' DELETE CLIENTES WHERE COD_CLI='VINET' GO SELECT * FROM ORDENES WHERE COD_CLI='ANATR' SELECT * FROM CLIENTES WHERE COD_CLI='ANATR' DELETE CLIENTES WHERE COD_CLI='ANATR' GO

------------------------------------------------------------------USE MASTER GO CREATE DATABASE BDTRIGGERS GO USE BDTRIGGERS GO -- PREPARARANDO LAS TABLAS SELECT COD_CLI=CUSTOMERID,NOM_CLI=COMPANYNAME, COUNTRY AS PAIS_CLI INTO CLIENTES FROM NORTHWIND..CUSTOMERS SELECT NRO_ORD=ORDERID, FECHA_ORD=ORDERDATE, COD_CLI=CUSTOMERID INTO ORDENES FROM NORTHWIND..ORDERS SELECT NRO_ORD=ORDERID, COD_PROD=PRODUCTID, CANT=QUANTITY, PRECIO=UNITPRICE INTO DETALLES FROM NORTHWIND..[ORDER DETAILS] SELECT COD_PROD=PRODUCTID, NOM_PROD=PRODUCTNAME, PRE_PROD=UNITPRICE, STK_PROD=UNITSINSTOCK INTO PRODUCTOS FROM NORTHWIND..PRODUCTS SELECT COD_CAT=CATEGORYID, NOM_CAT=CATEGORYNAME INTO CATEGORIAS FROM NORTHWIND..CATEGORIES

.dIeGo

Página 15

GO /* CREAR UN TRIGGER QUE PERMITA AUDITAR LA ELIMINACION DE REGISTROS EN LA TABLA CLIENTES. */ -- 1RO. CREAREMOS UNA TABLA QUE NOS PERMITA AUDITAR CREATE TABLE AUDITA_USUARIOS ( NRO_OPER INT IDENTITY PRIMARY KEY, FECHA DATETIME, USUARIO VARCHAR(50), COMANDO VARCHAR(50), DESCRIPCION VARCHAR(100) ) GO CREATE TRIGGER TR_AUDITA_ELIMINACION_CLIENTES ON CLIENTES AFTER DELETE AS -- DECLARAMOS LAS VARIABLES A UTILIZAR DECLARE @COD CHAR(5), @NOM VARCHAR(60) -- ALMACENAMOS EL CODIGO Y EL NOMBRE DEL CLIENTE QUE -- ESTAMOS ELIMINANDO SELECT @COD=COD_CLI, @NOM=NOM_CLI FROM DELETED -- DECLARAMOS LA VARIABLE QUE CUENTA LAS ORDENES -- DEL CLIENTE ELIMINADO DECLARE @CANT INT SELECT @CANT=COUNT(*) FROM ORDENES WHERE COD_CLI=@COD -- DECLARAMOS LA VARIABLE PARA NUESTRO MENSAJE DECLARE @MSJ VARCHAR(100) -- SI @CANT ES IGUAL A CERO IF @CANT=0 -- EL CLIENTE NO TIENE ORDENES SET @MSJ='CLIENTE ELIMINADO '+@NOM+ ' POR NO TENER ORDENES' ELSE -- SINO, EL CLIENTE TIENE ORDENES BEGIN SET @MSJ='SE INTENTÓ ELIMINAR AL CLIENTE '+@NOM+ ' QUE TIENE: '+STR(@CANT,3)+ 'ORDENES' ROLLBACK END -- INSERTAMOS LOS DATOS GUARDADOS ANTERIORMENTE PARA -- LA TABLA DE AUDITORIA INSERT INTO AUDITA_USUARIOS VALUES(GETDATE(), SYSTEM_USER, 'DELETE SOBRE CLIENTES',@MSJ) GO -- CLIENTES SIN ORDENES SELECT * FROM ORDENES WHERE COD_CLI='PARIS' SELECT * FROM CLIENTES WHERE COD_CLI='PARIS' -- ELIMINANDO AL CLIENTE PARIS DELETE CLIENTES WHERE COD_CLI='PARIS' SELECT * FROM AUDITA_USUARIOS -- CLIENTE CON ORDENES SELECT * FROM ORDENES WHERE COD_CLI='ALFKI' SELECT * FROM CLIENTES WHERE COD_CLI='ALFKI' -- ELIMINANDO AL CLIENTE ALFKI DELETE CLIENTES WHERE COD_CLI='ALFKI'

.dIeGo

Página 16

SELECT * FROM AUDITA_USUARIOS -- UTILIZANDO UN PROC. ALMACENADO CREATE PROC ELIMINA_CLIENTE @COD CHAR(5) AS DELETE CLIENTES WHERE COD_CLI=@COD GO EXEC ELIMINA_CLIENTE 'OCEAN' GO SELECT * FROM AUDITA_USUARIOS GO ------------------------------------------------------------------USE BDTRIGGERS GO /* CREAR UN TRIGGER QUE PERMITA ACTUALIZAR EL STOCK DE LOS PRODUCTOS CADA VEZ QUE SE INSERTE UN NUEVO REGISTRO EN LA TABLA DETALLE. SI EL NUEVO STOCK DEL PRODUCTO ES MENOR A CERO NO SE PUEDE REALIZAR LA VENTA. SI EL NUEVO STOCK DEL PRODUCTO ESTA ENTRE 0 (CERO) Y 10 SE DEBE GUARDAR EN UNA TABLA LLAMADA PRODUCTOS_STOCKS_MINIMOS CREATE TABLE PRODUCTOS_STOCKS_MINIMOS ( NUMERO INT IDENTITY PRIMARY KEY, CODPROD INT, NOMPROD VARCHAR(60), FECHA DATETIME ) GO */ CREATE TRIGGER TR_ACT_STOCKS ON DETALLES AFTER INSERT AS DECLARE @COD INT, @CANT INT SELECT @COD=COD_PROD, @CANT=CANT FROM INSERTED UPDATE PRODUCTOS SET STK_PROD=STK_PROD - @CANT WHERE COD_PROD=@COD DECLARE @STOCK INT, @NOM VARCHAR(50) SELECT @STOCK=STK_PROD, @NOM=NOM_PROD FROM PRODUCTOS WHERE COD_PROD=@COD IF @STOCK BETWEEN 0 AND 10 INSERT INTO PRODUCTOS_STOCKS_MINIMOS VALUES(@COD, @NOM, GETDATE())

GO

IF @STOCK < 0 BEGIN ROLLBACK DECLARE @MSJ VARCHAR(100) SET @MSJ='EL PRODUCTO %s TIENE UN STOCK NEGATIVO: %d' RAISERROR(@MSJ, 14, 1, @NOM, @STOCK) WITH LOG END

.dIeGo

Página 17

SELECT * FROM PRODUCTOS WHERE STK_PROD BETWEEN 11 AND 20 -- PRODUCTO 3, TIENE 13 DE STOCK Y 10 DE PRECIO SELECT * FROM DETALLES WHERE NRO_ORD=10248 INSERT INTO DETALLES VALUES(10248, 3, 10, 5, 0) SELECT * FROM PRODUCTOS_STOCKS_MINIMOS GO SELECT * FROM PRODUCTOS WHERE STK_PROD BETWEEN 0 AND 10 -- PRODUCTO 21 TIENE 3 DE STOCK Y 10 DE PRECIO SELECT * FROM DETALLES WHERE NRO_ORD=10248 INSERT INTO DETALLES VALUES( 10248, 21, 10, 5, 0 ) SELECT * FROM PRODUCTOS_STOCKS_MINIMOS SELECT * FROM PRODUCTOS WHERE STK_PROD BETWEEN 0 AND 10 /* CREAR UN TRIGGER QUE LISTE LOS VALORES DEL REGISTRO ORIGINAL Y DEL NUEVO REGISTRO AL MOMENTO DE ACTUALIZAR LOS DATOS DE UN PRODUCTO. */ CREATE TRIGGER TR_ACT_PRODUCTOS ON PRODUCTOS AFTER UPDATE AS -- DATOS DEL REGISTRO ORIGINAL DECLARE @COD_ANT INT, @NOM_ANT VARCHAR(50), @PRE_ANT MONEY SELECT @COD_ANT=COD_PROD, @NOM_ANT=NOM_PROD, @PRE_ANT=PRE_PROD FROM DELETED SELECT 'TABLA DELETED', D.* FROM DELETED D -- DATOS DEL NUEVO REGISTRO DECLARE @COD_NEW INT, @NOM_NEW VARCHAR(50), @PRE_NEW MONEY SELECT @COD_NEW=COD_PROD, @NOM_NEW=NOM_PROD, @PRE_NEW=PRE_PROD FROM INSERTED SELECT 'TABLA INSERTED', I.* FROM INSERTED I PRINT PRINT PRINT PRINT PRINT PRINT PRINT PRINT PRINT

'LOS DATOS ORIGINALES SON:' 'CODIGO: '+ STR(@COD_ANT) 'NOMBRE: '+ @NOM_ANT 'PRECIO: '+ STR(@PRE_ANT) '' 'LOS NUEVOS DATOS SON:' 'CODIGO: '+ STR(@COD_NEW) 'NOMBRE: '+ @NOM_NEW 'PRECIO: '+ STR(@PRE_NEW)

--SP_SEND_DBMAIL GO SELECT * FROM PRODUCTOS UPDATE PRODUCTOS SET COD_PROD=100, NOM_PROD='PRODUCTO X', PRE_PROD=50 WHERE COD_PROD=2 GO

.dIeGo

Página 18