Embed Size (px)
Citation preview
Sql Server
Creación de una Base de Datos
Create database nombre_bdon primary -- siempre va a venir la configuracion del archivo fisico( -- al archivo principal se le pone la extension "mdf"--especificaion de archivo (principal)),Filegroup nombre_filegroup -- es secundario su ventaja es de que este filegroup-- puede alojar info. de las tablas de usuario =)( -- al archivo secundario se le pone la extension "ndf"--especificaion de archivo (secundario))log on ---- siempre va a venir la configuracion del archivo logico( -- al archivo logico se le pone la extension "ldf"-- especificaion de archivo)go
--Especificaion de archivoname =nombre logicofilename= 'ruta de archivo fisico'size= tamaño inicial del archivomaxsize= maximo tamaño de crecimietnofilegrowth= valor de crecimiento
--ejemplo--
Create database bd_pruebaon primary(name=bd_prueba_data,filename = 'c:\bd_prueba_data.mdf' ,size = 5)log on(name=bd_prueba_log,filename = 'c:\bd_prueba_log.ldf' ,size = 2)go
sp_help db bd_pruebago
Use mastergo
CREATE DATABASE COMPRASON(NAME='Compra.dat',FILENAME='C:\Data\Compras.mdf',SIZE=10MB,MAXSIZE=15MB,FILEGROWTH=10%)LOG ON(NAME='Compra01',FILENAME='C:\Data\Compras01.ldf',SIZE=7MB,MAXSIZE=9MB,FILEGROWTH=1MB)GO
CREATE DATABASE LOGISTICAON(NAME=Logistica01,FILENAME='E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Logistica01.mdf',SIZE=15MB,MAXSIZE=25MB,FILEGROWTH=1MB),(NAME=Logistica02,FILENAME='E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Logistica02.ndf',SIZE=10MB,MAXSIZE=15MB,FILEGROWTH=1MB)LOG ON(NAME=Logistica03,FILENAME='E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Logistica03.Ldf',SIZE=5MB,FILEGROWTH=1MB)GO-------------------------------------------------1)TAMAÑO DE LA B.D:30 MB2)MAXIMO TAMAÑO DE LA B.D: NO TIENE LIMITE------------------------------------------------SP_HELPDB LOGISTICA-----------------------------------------------------------CREATE DATABASE FINANZASON(NAME=Finanzas01,FILENAME='C:\Data\Finanzas01.mdf',SIZE=25MB,FILEGROWTH=1MB)LOG ON(NAME=Finanzas02,FILENAME='C:\Data\Finanzas02.ldf',SIZE=10MB,FILEGROWTH=1MB),(NAME=Finanzas03,FILENAME='C:\Data\Finanzas03.ldf',SIZE=5MB,FILEGROWTH=1MB),(NAME=Finanzas04,FILENAME='C:\Data\Finanzas04.ldf',
SIZE=4MB,FILEGROWTH=1MB)GO-----------------------------------------------------------1>TAMAÑO DE LA B.D:44 2>MAXIMO TAMAÑO DE LA B.D: NO TIENE LIMITE------------------------------------------------------------------------------- Agregar archivo de registro ------------------ALTER DATABASE FINANZASADD FILE(NAME=finanzas05,FILENAME='C:\Data\Finanzas05.ndf',SIZE=20MB)------------ Agregar archivo de log de transaccion -------------ALTER DATABASE FINANZASADD LOG FILE(NAME=finanzas06,FILENAME='C:\Data\Finanzas06.ldf',SIZE=4MB)-------------------------------------------------------------ALTER DATABASE FINANZASmodify FILE(NAME=finanzas05,SIZE=30MB)----------------------------------------------------------------------- BACKUP DE LA BASE DE DATOS ------------------------BACKUP DATABASE FINANZAS TO DISK='C:\Data\finanzas.bk'
--------- RESTAURAR LA BASE DE DATOS ------------------------RESTORE DATABASE FINANZAS FROM DISK='C:\Data\finanzas.bk'--------------------------------------------------------------Drop database FINANZAS,COMPRAS
RENOMBRANDO BD :-- ALTER DATABASE -- AUMENTO Y DISMINUCION-- AUMENTANDO EL TAMAÑO DE LA BD-- SOL_01 (AUMENTAMOS TAMAÑO A UN ARCHIVO)SP_HELPDB BD3598GO
ALTER DATABASE BD3598MODIFY FILE(
NAME=CONTA_DATA,SIZE=15
)GO
SP_HELPDB BD3598GO
-- SOL_02 (ADICIONANDO UN NUEVO ARCHIVO)-- COMO SE DESEA SEPARAR LA INFORMACION DE LA-- NUEVA AREA, SE CREARA UN NUEVO FILEGROUPALTER DATABASE BD3598ADD FILEGROUP FG_PRODUCCIONGO-- LUEGO ADICIONAMOS UN NUEVO ARCHIVO DE DATOS-- DENTRO DEL FILEGROUPALTER DATABASE BD3598
ADD FILE(
NAME=PRODUCCION_DATA,FILENAME='D:\3598\SECUNDARIOS\PROD_DATA.NDF',SIZE=10
)TO FILEGROUP FG_PRODUCCIONGO
SP_HELPDB BD3598GO
USE BD3598GO-- CREANDO UNA TABLA ALMACENANDOLA DENTRO DE UN-- FILEGROUP DISTINTO DEL PRINCIPALCREATE TABLE PROD_TERMINADOS( CODIGO INT, FECHA_PROD DATETIME )ON FG_PRODUCCIONGO
SP_HELP PROD_TERMINADOSGO
----------------------------------------------- RENOMBRAR BD Y SUS ARCHIVOS----------------------------------------------- RECOMENDACION:/*
LA BD NO DEBE ENCONTRARSE ACTIVA.SE DEBE ENCONTRAR EN MODO DE UN UNICO USUARIO.
*/-- CAMBIAR EL NOMBRE DE LA BD BD3598 POR-- BD_IDATUSE MASTERGOALTER DATABASE BD3598MODIFY NAME=BD_IDATGO
SP_HELPDB BD3598GOSP_HELPDB BD_IDATGO
-- RENOMBRAR LOS NOMBRES LOGICOS DE LOS ARCHIVOS -- DE LA BDALTER DATABASE BD_IDATMODIFY FILE(
NAME=BD3598_DATA,NEWNAME=BD_IDAT_DATA
)GO
SP_HELPDB BD_IDATGO
----------------------------------ALTER DATABASE BD_IDATMODIFY FILE(
NAME=BD_IDAT_DATA,
SIZE=20)GO
SP_HELPDB BD_IDATGO-- AL TRATAR DE REDUCIR EL TAMAÑO DEL ARCHIVO-- MODIFY FILE NO LO PERMITE (NVO_TAM > TAM_ACTUAL)ALTER DATABASE BD_IDATMODIFY FILE(
NAME=BD_IDAT_DATA,SIZE=5
)GO-- ERROR
-- REDUCIENDO CORRECTAMENTE EL TAMAÑO DE UNA BD.--DBCC SHRINKDATABASE(NOM_BD, TAM_%_A_REDUCIR)--GO--DBCC SHRINKFILE(NOM_LOG_ARCH, NVO_TAM_MB)--GOUSE BD_IDATGOSP_HELPDB BD_IDATGODBCC SHRINKFILE(BD_IDAT_DATA, 5) GOSP_HELPDB BD_IDATGO
CREACION BD - CONFIGURACION-- CREAR UNA ESTRUCTURA DE CARPETAS EN UNA-- UNIDAD DE DISCO DISTINTA A C:-- UNIDAD:\3598 -- CARPETA PRINCIPAL-- |__ PRINCIPAL -- SUBCARPETA-- |__ SECUNDARIOS -- SUBCARPETA
-- CREANDO LA BD PARA LOS COMANDOS ALTER-- DATABASE.CREATE DATABASE BD3598ON PRIMARY(
NAME=BD3598_DATA,FILENAME='D:\3598\PRINCIPAL\BD3598_DATA.MDF',SIZE=5
),FILEGROUP FG_CONTA(
NAME=CONTA_DATA,FILENAME='D:\3598\SECUNDARIOS\CONTA_DATA.NDF',SIZE=10
)LOG ON(
NAME=BD3598_LOG,FILENAME='D:\3598\BD3598_LOG.LDF',SIZE=3
)GO
-- LISTANDO LA ESTRUCTURA DE LA BDSP_HELPDB BD3598GO
USE BD3598GOCREATE TABLE CLIENTES( CODIGO INT, NOMBRE VARCHAR(50))GOINSERT INTO CLIENTES VALUES(1,'MIGUEL DIAZ')GOSELECT * FROM CLIENTESGO
-- ESTABLECIENDO A SOLO LECTURA A LA BDALTER DATABASE BD3598SET READ_ONLYGO-- CAMBIA EL IDIOMA DEL SQLSET LANGUAGE SPANISHGOINSERT INTO CLIENTES VALUES(2,'KARINA TORRES')GOSELECT * FROM CLIENTESGO
-- ESTABLECIENDO A LECTURA-ESCRITURA A LA BDALTER DATABASE BD3598SET READ_WRITEGO
-- ESTABLECER EL ACCESO A UN UNICO USUARIOALTER DATABASE BD3598SET SINGLE_USERGO
SP_HELPDB BD3598GO
-- RESTABLECEMOS EL ACCESO MULTIUSUARIO A LA BDALTER DATABASE BD3598SET MULTI_USER -- RESTRICTED_USERGO
-- DEJANDO FUERA DE LINEA (DESACTIVANDO LA BD)-- A LA BDUSE MASTERGOALTER DATABASE BD3598SET OFFLINEGO
-- VOLVIENDO A ACTIVAR LA BDALTER DATABASE BD3598SET ONLINEGO
SP_HELPDB BD3598GO
-- ESTABLECIENDO EL MODELO DE RECUPERACION
-- A FULL (MODELO COMPLETO)-- RECOVERY: SIMPLE, BULK_LOGGED, FULLALTER DATABASE BD3598SET RECOVERY FULL -- SIMPLE, BULK_LOGGEDGO
CONSTRAIN
create table Persona(Codigo int, Nombre varchar(70), Telefono varchar(9), Nacimiento datetime)
ALTER Table Personaadd constraint dTelf DEFAULT '999-99-9'for telefonoInsert into Persona values(1,'juan','av.Los alamos 450','458-21-89',getdate())insert into Persona(Codigo,Nombre,Direccion,Nacimiento)values(2,'Jose','av.Los proceres 145',getdate())-----------------------------------------------------ALTER Table Personaadd constraint unom unique(nombre)Insert into Persona values(2,'claudia','av.Los alamos 450','478-71-89',getdate())insert into Persona values(3,'claudia','av.Los perros 456','477-21-89',getdate()) -----------------------------------------------------ALTER Table Personaadd constraint ctelf check(telefono like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]')Insert into Persona values(4,'Martin','av.Arequipa 789','549-87-63',getdate())insert into Persona values(5,'Fuller','av.La paz 120','A49-87-63',getdate()) -----------------------------------------------------
select * from SYSOBJECTSWHERE type='C'OR - CHECK type='PK'OR - CLAVE PRIMARIA type='F'OR - CLAVE FORANEA type='D'OR - DEFAULT type='U'OR - TABLAS type='p'or -PROCEDIMIENTOS type='K'-UNIQUE
ALTER TABLE PERSONADROP CTELF____________________________________________________________________________________________
SELECT:SELECT * FROM PRODUCTS
SELECT ProductName,QuantityPerUnit FROM PRODUCTS
SELECT PRODUCTID AS Codigo, PRODUCTNAME AS NOMBRE, UNITPRICE AS PRECIOFROM PRODUCTSWHERE UNITPRICE>50
ORDER BY PRECIO DESC____________________________________________________________________SELECT C.CATEGORYNAME, P.PRODUCTNAME, P.UNITPRICEFROM PRODUCTS P,CATEGORIES CWHERE C.CATEGORYID=P.CATEGORYID
INSERT:_____________________________________________________________________INSERT INTO GATEGORIES(CATEGORYNAME)VALUES('BEBIDAS')SELECT * FROM ALUMNOSINSERT INTO ALUMNOS VALUES('200','MARIA')
UPDATE:_____________________________________________________________________UPDATE PRODUCTSSET UNITPRICE=UNITPRICE*1.10,WHERE CATEGORYID>5SELECT * FROM PRODUCTS DELETE------------------------------DELETE FROM PRODUCTSDELETE FROM PRODUCTS WHERE UNITPRICE>50
CREATE TABLE ALUMNO(NOMBRE VARCHAR(10), NOTA1 INT, NOTA2 INT, NOTA3 INT)
INSERT INTO ALUMNO VALUES('JUAN',12,11,10)INSERT INTO ALUMNO VALUES('MANUEL',11,17,20)INSERT INTO ALUMNO VALUES('PEDRO',12,12,10)INSERT INTO ALUMNO VALUES('MARTIN',17,18,10)
SELECT * FROM ALUMNO
MOSTRAR TODAD LAS COLUMNAS DE LAS TABLAS ALUMNOMOSTRAR TODAS LAS COLUMNA DE TLA TABLA ALUMNO ORDENADO ALFABETICAMENTEMOSTRAR NOMBRE Y PROMEDIO DE LA TABLA ALUMNO
1____________________________________________SELECT * FROM ALUMNO2____________________________________________SELECT * FROM ALUMNOORDER BY NOMBRE3____________________________________________SELECT NOMBRE,((NOTA1+NOTA2+NOTA3)/3) AS PROMEDIO FROM ALUMNO4____________________________________________SELECT NOMBRE,((NOTA1+NOTA2+NOTA3)/3) AS PROMEDIO FROM ALUMNOORDER BY PROMEDIO DESC5____________________________________________SELECT NOMBRE,((NOTA1+NOTA2+NOTA3)/3) AS PROMEDIO FROM ALUMNOORDER BY NOMBRE6
___________________________________________SELECT * FROM ALUMNOWHERE NOTA3 BETWEEN 13 AND 15 ORDER BY NOMBRE
7___________________________________________
8___________________________________________SELECT NOMBRE FROM ALUMNOWHERE NOMBRE LIKE'[J]%'ORDER BY NOMBRE9__________________________________________SELECT NOMBRE FROM ALUMNOWHERE NOMBRE LIKE '%[U]%'10__________________________________________SELECT NOMBRE FROM ALUMNOWHERE NOMBRE LIKE '%[A,E,I,O,U]%'ORDER BY NOMBRE
Backup :-- COPIAS DE SEGURIDAD CREATE DATABASE BD3598GO
-- CAMBIA EL IDIOMA DEL SQLSET LANGUAGE SPANISH
-- ESTABLECER EL MODELO DE RECUPERACION -- COMPLETO ALTER DATABASE BD3598 SET RECOVERY FULL GO
USE BD3598 GO
CREATE TABLE PRODUCTOS (
COD_PROD INT,NOM_PROD VARCHAR(20)
)GO
-- INSERTANDO INSERT INTO PRODUCTOS VALUES(1,'MOUSE')INSERT INTO PRODUCTOS VALUES(2,'TECLADO')GO
SELECT * FROM PRODUCTOS
-- COPIA DE SEGURIDAD COMPLETA BACKUP DATABASE BD3598TO DISK='D:\BD3598_FULL.BACK'WITH INIT GO
-- WHIT INIT = REESTABLE EL TAMAÑO DE LA COPIA DE SEGURIDAD...
-- INSERCION DE NUEVOS PRODUCTOS INSERT INTO PRODUCTOS VALUES(100,'PRODUCTO 100')INSERT INTO PRODUCTOS VALUES(200,'PRODUCTO 200')INSERT INTO PRODUCTOS VALUES(300,'PRODUCTO 300')INSERT INTO PRODUCTOS VALUES(400,'PRODUCTO 400')INSERT INTO PRODUCTOS VALUES(500,'PRODUCTO 500')GO
-- AGREGAMOS UNA NUEVA COLUMNA ALTER TABLE PRODUCTOS ADD PRECIO MONEY GO
SELECT * FROM PRODUCTOS GO
UPDATE PRODUCTOS SET PRECIO = 100GO
-- COPIA DE SEGURIDAD DIFERENCIAL BACKUP DATABASE BD3598 TO DISK='D:\BD3598_DIFF.BAK'WITH DIFFERENTIAL, INITGO
-- INSERTAMOS NUEVOS PRODUCTOS INSERT INTO PRODUCTOS
VALUES(1000,'PRODUCTO 1000',50)INSERT INTO PRODUCTOS
VALUES(2000,'PRODUCTO 2000',80)INSERT INTO PRODUCTOS
VALUES(3000,'PRODUCTO 3000',90)INSERT INTO PRODUCTOS
VALUES(4000,'PRODUCTO 4000',50)INSERT INTO PRODUCTOS
VALUES(5000,'PRODUCTO 5000',60)GO
UPDATE PRODUCTOS SET NOM_PROD=NOM_PROD+' MODIF.'WHERE COD_PROD IN (2,200,2000,500,5000)
GO
-- GENERAMOS LA COPIA DE SEGURIDAD DEL REGISTRO -- DE TRANSACCIONES BACKUP LOG BD3598 TO DISK='D:\BD3598_LOG01.BAK'WITH INIT GO
-- SE ELIMINAN ALGUNOS PRODUCTOS SELECT * FROM PRODUCTOSDELETE PRODUCTOS WHERE COD_PROD IN (1,100,1000)GO
-- GENERAMOS UNA NUEVA COPIA DE SEGURIDAD -- DE REGISTRO DE TRANSACCIONES BACKUP LOG BD3598 TO DISK = 'D:\BD3598_LOG02.BAK'WITH INIT GO
Reduccion de Archivos :
USE BD3598 GO
CREATE TABLE CLIENTES (
COD_CLI INT PRIMARY KEY,NOM_CLI VARCHAR(80),
)GO
-- TAMAÑO DEL ARCHIVO LOGICO: BD3598_LOGSP_HELPDB BD3598GO-- INSERTANDO 500000 FILAS DE PRUEBA DECLARE @CONTA INT SELECT @CONTA=1WHILE @CONTA<=500000BEGIN
INSERT INTO CLIENTES VALUES(@CONTA, 'CLIENTES '+LTRIM(STR(@CONTA)))
SELECT @CONTA=@CONTA+1END GO
SP_HELPDB BD3598GO
-- PARA LA DISMINUCION DEL REGISTRO DE TRANSACCIONES -- REALIZAREMOS:/*
1. REALIZAMOS UN BACKUP SOBRE EL ARCHIVO LOGICO DE LA BD UTILIZANDO LA INSTRUCCION TRUNCATE_ONLY
*/BACKUP LOG BD3598 WITH TRUNCATE_ONLYGO/*
2. LUEGO REALIZAMOS UN BACKUP COMPLETO DE LA BD */BACKUP DATABASE BD3598 TO DISK='D:\BD3598_COMPLETO.BAK' WITH INIT GO/*
LUEGO INTENTAREMOS REDUCIR EL TAMAÑO DEL ARCHIVO CON LA INSTRUCCION DBCC SHRINKFILE, HASTA DONDE NOS PERMITA.
*/DBCC SHRINKFILE(BD3598_LOG, 100)GO
SP_HELPDB BD3598GO
/*SI DESPUES DE HABER REALIZADO SHRINKFILE, ESTE NO PUEDE REDUCIR AL TAMAÑO QUE SE QUIERA, SOLO NOS QUEDA POR HACER UNA SEPARACION DE LA BD.*/USE MASTER GO SP_DETACH_DB BD3598GO/*
DESPUES TENEMOS QUE MOVER EL ARCHIVO LOGICO HACIA OTRA UBICACION.*/
/*EN SQL 2005 LA INSTRUCCION CREATE DATABASE, PUEDEUTILIZAR UN SIMPLE ARCHIVO DE DATOS(MDF) Y FORZAR AL SQL A CREAR UN NUEVO ARCHIVO LOGICO DE TRANSACCIONES*/CREATE DATABASE BD3598 ON (FILENAME= 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\BD3598.MDF')FOR ATTACH_REBUILD_LOGGO
/*FINALMENTE PARA CORROBORAR QUE LA BD, NO PRESENTA ERRORES (INTEGRIDAD, CONSISTENCIA, ETC), UTILIZAMOS LA INSTRUCCION DBCC CHECKDB('NOMBRE_BD')*/
DBCC CHECKDB('BD3598')GO
BACKUP DATABASE BD3598TO DISK='D:\BD3598_COMPLETO.BAK'WITH INIT GO SP_HELPDB BD3598GO
/*PARA CREAR UN BACKUP O RESTAURAR UNA BD, SE RECOMIENDA UTILIZAR LA INSTRUCCION CHEKSUM, LA CUAL COMPROBARA LOS ERRORES QUE SE PUEDAN HABER ENCONTRADO AL CREAR EL ARCHIVO DE BACKUP O AL MOMENTO DE RESTAURAR LOS ARCHIVOS DE LA BD */BACKUP DATABASE BD3598 TO DISK='D:\BD3598_COMPLETO.BAK'WITH INIT, CHECKSUMGO
RESTORE DATABASE BD3598 TO DISK='D:\BD3598_COMPLETO.BAK'WITH INIT, CHECKSUMGO
RESTAURACION :
-- RESTORE USE MASTER GO
DROP DATABASE BD3598GO
-- RESTAURANDO LA COPIA DE SEGURIDAD COMPLETA RESTORE DATABASE BD3598FROM DISK='D:\BD3598_FULL.BAK'WITH NORECOVERY GO
USE BD3598 GO SELECT * FROM PRODUCTOS BD3598 GO
-- RESTAURANDO LA COPIA DE SEG. DIFERENCIAL RESTORE DATABASE BD3598FROM DISK='D:\BD3598_DIFF.BAK'WITH NORECOVERYGO
-- RESTAURANDO LA COPIA DE SEG. DEL REGISTRO RESTORE LOG BD3598 FROM DISK='D:\BD3598_LOG01.BAK'WITH NORECOVERYGO
-- FINALMENTE RESTAURAMOS EL ULTIMO ARCHIVO-- DE COPIA DE SEG. DEL REG. DE TRANSACCIONESRESTORE LOG BD3598 FROM DISK='D:\BD3598_LOG02.BAK'WITH NORECOVERY--WITH RECOVERYGO
--RESTAURANDO EL ULTIMO ARCHIVO DE SEGURIDADRESTORE LOG BD3598 FROM DISK='D:\BD3598_LOG02.BAK'GO
USE BD3598 GO SELECT * FROM PRODUCTOS GO
CONSULTAS :--1.-SELECT NOMBRE,AVG(NOTA) AS PROM FROM ALUMNO A,NOTA BWHERE A.ALUMNOID=B.ALUMNOID GROUP BY A.ALUMNOID,NOMBREHAVING AVG(NOTA)>10.5 ORDER BY NOMBRE--2.-SELECT NOMBRE,COUNT(NOTA)AS NUM FROM ALUMNO A,NOTA BWHERE A.ALUMNOID=B.ALUMNOID GROUP BY A.ALUMNOID,NOMBREHAVING COUNT(NOTA)>2 ORDER BY AVG(COUNT)DESC--3.-SELECT DISTINCT NOMBRE FROM ALUMNO A,NOTA BWHERE A.ALUMNOID=B.ALUMNOID AND NOTA IN(11,12)--4.-SELECT NOMBRE,COUNT(NOTA) AS DESAP FROM ALUMNO A,NOTA BWHERE A.ALUMNOID=B.ALUMNOID AND NOTA<10.5GROUP BY NOMBRE--12.-SELECT TOP(SELECT CONVERT(INT,ROUND(IDALUMNO)*33))FROM ALUMNO)NOMBRE FROM AS A,NOTA AS N --PUEDE SER TOP 30 PARENTWHERE N.IDALUMNO=A.IDALUMNO
GROUP BY N.IDALUMNOORDER BY AVG(NOTA) DESC
--COMPLISELECT NOMBRE FROM ALUMNOSWHERE ID_ALUMNO IN (SELECT ID_ALUMNO FROM NOTASWHERE NOTA=11 AND ID_ALUMNO IN (SELECT ID_ALUMNO FROM NOTAS WHERE NOTA=12))
CONSTRAINT :CREATE DATABASE BD3598GOUSE BD3598GOCREATE TABLE BD3598.DBO.ALMACEN(
COD_ALM INT,NOM_ALM VARCHAR(30)
)GO
CREATE SCHEMA CONTABILIDADGOCREATE TABLE CONTABILIDAD.CUENTAS(
COD_CTA INT,DES_CTA VARCHAR(30)
)GO
CREATE TABLE CUENTAS(
COD_CTA INT,DES_CTA VARCHAR(30)
)GO
------------------------------------------- SINTAXIS DE CREACION DE TABLAS/*Create Table [NomBD.NomSchema.]NomTabla(
NomCol Tipo_Dato [ Constraint ...])[ ON NomFileGroup]
Donde Tipo_Dato, puede ser:Tipo definido por usuarioColumna CalculadaFunción (escalar) definida por usuarioXML
*/Create Table Notas1(
codalu int,codcur int,pp int,pt int,
ex int,prom as (pp+pt+ex)/3.0
)go
insert into Notas1 values(12, 45, 12,17,11)goselect * from Notas1go
-- creamos una función escalar que devuelva-- el promediocreate function promedio(@n1 int,@n2 int,@n3 int)returns intasbegin
return (select (@n1+@n2+@n3)/3.0)endgo-- probando la funcionselect prom=dbo.promedio(14,12,15)go
create table notas2(codalu int,codcur int,pp int, pt int, ex int,prom as dbo.promedio(pp,pt,ex))go
insert into notas2 values(19,23,14,11,18)goselect * from notas2go
---------------------------------------- Campo XML en una tablacreate table Alumnos(
nro_alu int,datos_alu xml,fecha_reg datetime
)go
insert into Alumnos Values( 123,'<Alumnos>
<Nombre>Fernando Vargas</Nombre><Email>[email protected]</Email><Telefonos>
<Casa>4521896</Casa><Celular>998563028</Celular>
</Telefonos></Alumnos>',getdate())goselect * from Alumnos
go
------------------------------------------- CONSTRAINTS --
/*PRIMARY KEY (Por Defecto es CLUSTERED)Create Table NomTabla(
Col1 tipo_dato Primary Key{ "Clustered" | NonClustered}
)*/
/*UNIQUE (Por Defecto NONCLUSTERED)Create Table NomTabla(
Col1 tipo_dato Unique{ Clustered | "NonClustered"}
)*/
Create Table Vendedor(cod_ven int not null primary key,nom_ven varchar(30) unique,tlf_ven varchar(9) unique)go
insert into Vendedor values(38,'Pedro Sifuentes','4521398')goinsert into Vendedor values(9,'Roger Verastegui','7465436')goinsert into Vendedor values(81,'Ana Vargas','3324538')go
select * from Vendedorgo
insert into Vendedor values(6,'Ana Torres',null)goselect * from Vendedorgo
-------------------------------------------/*CHECK*/Create Table Cursos(
cod_cur int primary key,nom_cur varchar(30)
check(nom_cur not like '%[0-9]%'),nro_cred int
check(nro_cred between 1 and 6))go
alter table CursosAdd Precio moneygo
Alter Table CursosAdd Constraint ck_Precio
Check(Precio>=200)go
sp_helpconstraint Cursosgo
/*Para Desactivar o Activar un Constraint(Check o Foreign Key)-- DesactivamosAlter table NomTablaNOCHECK { Nombre_Constraint | All }
-- ActivamosAlter table NomTablaCHECK { Nombre_Constraint | All }
*/
/*Para Evitar la Comprobación de los valoresde las columnas al momento de crear un nuevoconstraint.
Alter Table NomTabla WITH NOCHECKADD Constraint NomConstraint
Tipo_ConstraintExpresion_Constraint
-- With NoCheck, no comprueba que los registros-- cumplan el criterio de Expresion_Constraint,-- sólo crea al Constraint.
-- With Check, (Predeterminado), antes de crear-- el constraint, SQL verificará que todos los-- registros por la columna del Constraint -- cumplen con Expresion_Constraint. */
/*FOREIGN KEY*/Sintaxis:Alter Table NomTblSecundariaAdd [ Constraint NomConstraint ]
Foreign Key(Col_Tbl_Sec)References NomTblPrincipal(Col_Tbl_Princ)[
on delete cascadeon update cascade
]
-- en cambio, al crear la tabla secundariaCreate Table Principal(
NomCol Tipo_Dato { Primary Key o UNIQUE }
)goCreate Table Secundaria(
NomCol_sec Tipo_Dato, ....
NomCol_Prin Tipo_DatoReferences NomTbl_Principal(NomCol)
)go
SEGURIDADAdministrador- Creacion de Usuarios
USE [master]GOCREATE LOGIN [USQL1] WITH PASSWORD=N'' MUST_CHANGE, DEFAULT_DATABASE=[BDSEGURIDAD], DEFAULT_LANGUAGE=[Español], CHECK_EXPIRATION=ON, CHECK_POLICY=ONGO
USE [BDPEDIDOS]GOCREATE USER [USQL1] FOR LOGIN [USQL1]GOUSE [BDSEGURIDAD]GOCREATE USER [USQL1] FOR LOGIN [USQL1]GO
/*Otorgando el derecho de leer e insertarregistros en la tabla categorias alusuario USQL1 (Permiso a Nivel de Objeto)*/use bdpedidosgogrant select, insert on dbo.categorias
to USQL1go
/*Otorgando el derecho de creación de tablasal usuario USQL1 en la base de datosBDSEGURIDAD. (Permiso a nivel de instrucción)*/use bdseguridadgogrant create table to USQL1go-- Otorgar el permiso de Alterar el Esquemagrant alter on schema::dbo to USQL1
go
/*USE [master]GOCREATE LOGIN [USQL2] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[BDSEGURIDAD], DEFAULT_LANGUAGE=[Español], CHECK_EXPIRATION=ON, CHECK_POLICY=ONGOUSE [BDSEGURIDAD]GOCREATE USER [USQL2] FOR LOGIN [USQL2]GO*/
-- Creamos un Esquemacreate schema marketinggo
-- Cambiando el esquema predeterminado al-- usuario USQL2, de dbo por marketingalter user USQL2
with default_schema=marketinggo
/*al momento de la creacion de un usuario de bdse puede establecer el nombre del esquema quequiera asignarsele. CREATE USER Nom_Usuario WITH Default_Schema=Nombre_Schema*/
/*Recuerde, para que un usuario de BD, pueda crearobjetos, es necesario a parte del permiso deCREATE TABLE el permiso de ALTER ON SCHEMA.*/
-- Dándole el permiso de creacion de tablas,-- procedimientos almacenados a un Rol de BD,-- al cual el usuario USQL2 forma parte.-- Un Rol de BD es como un grupo de usuarios.CREATE ROLE gMarketinggo
-- agregando al usuario USQL2 al Rol gMarketing-- sp_addrolemember 'Nombre_Rol','Nom_Usuario' sp_addrolemember 'gMarketing','USQL2'go
-- finalmente le otorgamos el derecho de -- creación de tablas y proc. almacenados.-- Esto permitirá que cualquier usuario -- miembro de este Rol, pueda realizar estas-- acciones.GRANT CREATE TABLE, CREATE PROCEDURE
TO gMarketinggo-- no olvidar alterar el esquemaGRANT ALTER ON SCHEMA::marketing TO gMarketing
go
-- otorgar el derecho de leer e insertar en -- todas las tablas del esquema.GRANT SELECT, INSERT ON SCHEMA::marketing
TO gMarketinggo
/*deny insert on marketing.Nom_Tabla to usuario
deny insert on schema::marketing to usuario*/
-- usuario USQL1use bdpedidosgoselect * from dbo.categoriasgoinsert into dbo.categorias values('Artefactos')goselect * from dbo.categoriasgo
--------------------------------use bdseguridadgocreate table vendedor(
cod_ven int, nom_ven varchar(50))go-- Error, falta un permiso (Alter Schema)
-- Después de otorgar el derecho de Alterar el-- Esquemacreate table vendedor(
cod_ven int, nom_ven varchar(50))go
-- si el usuario USQL1, intenta listar el -- contenido de la tabla Vendedorselect * from dbo.Vendedorgo
-- usuario usql2CREATE TABLE PRODUCTOS(
COD_PROD INT,NOM_PROD VARCHAR(50)
)GO
INSERT INTO marketing.PRODUCTOS VALUES(1,'LG Scarlet 32')go
SELECT * FROM marketing.PRODUCTOSgo
CREATE PROC LISTAR_PRODUCTOSAS SELECT * FROM marketing.PRODUCTOS
ORDER BY NOM_PRODgo
EXEC LISTAR_PRODUCTOSgo-- Error, no tiene el permiso de EXECUTE
TRANSACCIONES :-- "NUEVAS" INSTRUCCIONES T-SQLUSE BDPEDIDOSGO-- TOP(n)-- SQL 2000SELECT TOP 3 * FROM CLIENTESGODECLARE @N INTSELECT @N=5SELECT TOP @N * FROM CLIENTESGO-- SQL DINAMICODECLARE @CAD VARCHAR(500)SELECT @CAD='SELECT TOP 'DECLARE @N INTSELECT @N=5SELECT @CAD=@CAD + STR(@N,3) + ' * FROM CLIENTES'-- PRINT @CADEXEC(@CAD)GO-------------------------------------------------------------
-- VARIANTEDECLARE @N INTSELECT @N=4SET ROWCOUNT @N -- ESTABLECIENDO EL LIMITE
-- DE FILAS A MOSTRAR SELECT * FROM CLIENTESSELECT * FROM CAB_PEDIDOSET ROWCOUNT 0 -- RESTABLECE SIN RESTRICCIONGO
SELECT * FROM CLIENTESGO
-- SQL 2005DECLARE @N INTSELECT @N=4SELECT TOP(@N) * FROM CLIENTESGO
-- Se puede utilizar: INSERT, UPDATE y DELETECREATE TABLE CLIENTES_AMERICA(
COD_CLI CHAR(5) PRIMARY KEY,NOM_CLI VARCHAR(50),PAIS_CLI VARCHAR(30)
)GO
DECLARE @N INTSELECT @N=15INSERT TOP @N CLIENTES_AMERICA
SELECT COD_CLI, NOM_CLI, PAISFROM CLIENTES
WHERE PAIS IN ('MEXICO','BRAZIL','ARGENTINA')
GO
SELECT * FROM CLIENTES_AMERICAGO
/*DECLARE @N INTSELECT @N=15INSERT CLIENTES_AMERICA
SELECT TOP(@N) COD_CLI, NOM_CLI, PAISFROM CLIENTES
WHERE PAIS IN ('MEXICO','BRAZIL','ARGENTINA')
GO*/
/*SET y SELECT------------SELECT ABC='JUAN'
DECLARE @ABC VARCHAR(5)-- SET @ABC='JUAN'SELECT @ABC='JUAN'PRINT @ABC
DECLARE @CANTIDAD INTSET @CANTIDAD=(SELECT COUNT(*) FROM CLIENTES)PRINT @CANTIDADGO
DECLARE @CANTIDAD INTSELECT @CANTIDAD=COUNT(*) FROM CLIENTESPRINT @CANTIDADGO*/
---------------------------------- OUTPUT--------------------------------/*Permite acceder a las tablas internasINSERTED y DELETED, para mostrar los nuevosregistros, o los registros eliminados y/oactualizados*/insert into clientes_america
output system_user, inserted.nom_cli, inserted.pais_cli,
getdate() -- into NombreTabla
select cod_cli,nom_cli,pais from clientes
where pais='UK'go
------------------------------------ Funciones de Ranking-- ROW_NUMBER(), RANK(), DENSE_RANK() y NTILE(n)----------------------------------SELECT COD_CLI,NOM_CLI,PAIS,ROW_NUMBER() OVER(ORDER BY PAIS) AS NRO,ROW_NUMBER() OVER(PARTITION BY PAIS
ORDER BY PAIS) AS NRO_PART,RANK() OVER(ORDER BY PAIS) AS 'RANK',DENSE_RANK() OVER(ORDER BY PAIS) AS 'DENSE',NTILE(10) OVER(ORDER BY PAIS) AS 'NTILE' FROM CLIENTESGOSET ROWCOUNT 0SELECT * FROM CLIENTESSELECT * FROM EMPLEADOS----------------------------------------- TRANSACCIONES----------------------------------------- SQL 2000 y SQL 2005-- SINTAXIS:BEGIN TRAN [SACTION]
-- INSTRUCCIONES SQL-- "-- "IF @@ERROR<>0 -- HUBO UN ERROR EN ALGUNA
-- INSTRUCCIONROLLBACK TRAN [SACTION]
ELSE -- SINO HUBO ERRORCOMMIT TRAN [SACTION]
GO
-------------------------------
begin tran declare @err_eli int, @err_ins int
insert into clientes_americavalues('XYZ04','CLIENTE XYZ01','Peru')
set @err_ins=@@errorprint 'Error='+str(@err_ins)
delete clientes where cod_cli='OCEAN'set @err_eli=@@errorprint 'Error='+str(@err_eli)
-- Hubo un Error en alguna instruccionif @err_ins<>0 or @err_eli<>0
RollBack Tranelse -- Sino, No hubo Error en ninguna
-- instruccionCommit Tran
go
select * from clientes where cod_cli='FISSA'goSP_HELPCONSTRAINT CAB_PEDIDOGO-- cambiando el tipo de dato a la columna-- cod_cli de cab_pedido de nchar(5) a -- char(5)SELECT * FROM CAB_PEDIDOalter table cab_pedidoalter column cod_cli char(5) go-- creando el foreign key entre cab_pedido-- y clientesalter table cab_pedido with nocheckadd foreign key(cod_cli)
references clientes(cod_cli)go
select * from clientes_america where cod_cli='XYZ01'go
funciones def.usuario :use northwindgo
-- Funciones Definidas por el Usuario/*1. Funcion EscalarDevuelve un valor escalar (un numero, una cadena, una fecha)
Sintaxis:
CREATE FUNCTION NomFuncion (@Prm Tipo_Dato)RETURNS Tipo_Dato_DevolverAS
BEGIN-- Instrucciones SQLRETURN @Var_Tipo_Dato_Devolver
-- Podría utilizarse también la sgte. forma:-- RETURN (SELECT UnaColumna_UnaFila FROM ... )
ENDGO*/-- Ejemplo/*Crear una Función Escalar que devuelva el nombre deldia de la semana de una fecha.*/ALTER FUNCTION FN_DIA_SEMANA(@FECHA DATETIME)RETURNS VARCHAR(50)ASBEGIN
-- MOMBRE DEL DIADECLARE @NOM VARCHAR(50)SELECT @NOM=DATENAME(DW, @FECHA)-- AGREGAMOS EL DIASELECT @NOM=@NOM + ', '+STR(DAY(@FECHA),2)-- AGREGAMOS EL MES
SELECT @NOM=@NOM + ' DE '+DATENAME(MM,@FECHA)-- AGREGAMOS EL AÑOSELECT @NOM=@NOM + ' DE '+ STR(YEAR(@FECHA),4)RETURN @NOM
-- RETURN (SELECT DATENAME(DW, @FECHA))ENDGOSET LANGUAGE SPANISHGOSELECT FECHA_LARGA=DBO.FN_DIA_SEMANA(GETDATE())GO
SELECT ORDERID, CUSTOMERID, FECHA_LARGA=DBO.FN_DIA_SEMANA(ORDERDATE)FROM ORDERS WHERE MONTH(ORDERDATE)=12 AND YEAR(ORDERDATE)=1996GO
/*Crear una Función Escalar que devuelva la cantidadde ordenes por un codigo de cliente en un año específico. */CREATE FUNCTION FN_ORDENES_AÑO(@COD_CLI CHAR(5), @AÑO INT)RETURNS INTASBEGIN
RETURN (select count(*) from orders where customerid=@COD_CLI and
YEAR(orderdate)=@AÑO)
ENDGO
select top 3 companyname, cant_1996=dbo.FN_ORDENES_AÑO(customerid,1996), cant_1997=dbo.FN_ORDENES_AÑO(customerid,1997), cant_1998=dbo.FN_ORDENES_AÑO(customerid,1998)from customersgo
/*2. Función de Tipo Tabla en LíneaDevuelve el contenido de una sentencia SELECT, comouna Tabla, a diferencia de uan vista en donde tambiénse utiliza un SELECT, en la función el SELECT puedeutilizar argumentos, variables o parámetros. */create view v_productosas
select productid, productname, categoryidfrom products
goselect * from v_productosgo
create function fn_productos(@codcat int)returns tableas
return (
select productid, productname, categoryidfrom products
where categoryid=@codcat)
goselect * from dbo.fn_productos(7)go
/*Crear una función de tipo tabla que devuelva segúnun año enviado como parámetro los nombres de los 5 clientes que han comprado menos, y la cantidad deordenes que hayan realizado. Considere comprar menos a los clientes que hayan realizado pocos pedidos en eseaño.*/create function fn_clientes(@año int)returns tableasreturn(
select top 5 companyname, cant=count(*) from customers c inner join orders o
on c.customerid=o.customeridwhere year(orderdate)=@año
group by companynameorder by cant asc
)go
select * from dbo.fn_clientes(1996)go
/*3. Función Tipo Tabla de Múltiples InstruccionesDevuelve una tabla, que debe haber sido definida enla cabecera de la función. Esta tabla debe serpoblada por instrucciones INSERT INTO ... VALUES opor INSERT INTO .... SELECT .... FROM .....Este tipo de función es utilizada para devolver datos resumidos.
Sintaxis:
CREATE FUNCTION NombreFunción(@prm Tipo_Dato, .....)RETURNS @NomTabla TABLE (NomCol1 Tipo_Dato,
NomCol2 Tipo_Dato, ...)ASBEGIN
-- Múltiples instrucciones SQL-- Luego debemos poblar la tablaINSERT INTO @NomTabla VALUES( ........)
Ó
INSERT INTO @NomTablaSELECT ...... FROM ....... TABLA
RETURN -- esa instruccion devuelve la tablaENDGO*/
/*Crear una funcion de tipo Tabla que liste los nombres de los productos, el precio y el stock de los productos de acuerdo a un codigo de categoria enviadocomo parámetro.*/
create alter function fn_tabla_prod_cat(@cod_cat int)returns @tbl table (nomprod varchar(50),
precio int,stock int)
asbegin
-- adicionamos la información de los productos-- de una categoriainsert into @tbl
select productname, unitprice,unitsinstockfrom products
where categoryid=@cod_cat-- variables para capturar el promedio de los -- precios y la suma de los stockdeclare @prom_precio int,@suma_stock intselect @prom_precio=AVG(precio),
@suma_stock=SUM(stock)from @tbl
-- insertamos los datos obtenidosinsert into @tbl values(
'Resumen de la categoria '+str(@cod_cat,1), @prom_precio,@suma_stock)
insert into @tbl values( 'Promedio de Precios:',@prom_precio,0)
insert into @tbl values( 'Suma de Stocks:',0,@suma_stock)
-- devolvemos la tabla @tblreturn
endgo
select * from fn_tabla_prod_cat(7)go
PRACTICA Resuelta:/*1.-que nombres de categoria de producto fue la mas vendida el primer añoy cuanto fue el importe vendido*/
select*from productosselect*from cab_pedidoselect*from det_pedidoselect*from categorias
select top 1 c.nom_cat,importe=sum(d.cantidad*d.precio) from categorias as c inner join productos as p on c.cod_cat=p.cod_cat inner join det_pedido as d on p.cod_prod=d.cod_prod
inner join cab_pedido as ca on ca.num_pedido=d.num_pedido where year(ca.fecha)=1996group by c.nom_cat
/*2.-Listar el total facturado por el empleado mas antiguo(utilice al campo fecha de contratopor el que hara la comprobacion de la antiguedad), debera mostrar un codigo,apellido,fecha decontrato, cantidad de ordenes y el monto total genenrado por estas ordenes(cantidad*precio)*/select*from productosselect*from cab_pedidoselect*from det_pedidoselect*from categoriasselect*from empleados
select top 1 e.cod_emp,e.apellidos,e.fecha_contrato,cant_ordenes=count(ca.num_pedido),importe=sum(d.cantidad*d.precio) from empleados as e inner join cab_pedido as ca on e.cod_emp=ca.cod_empinner join det_pedido as d on ca.num_pedido=d.num_pedido group by e.cod_emp,e.apellidos,e.fecha_contrato order by year(e.fecha_contrato) asc
/*3.-Crear una funcion de tipo tabla que devuela a la lista del mejor cliente por pais en un añodeterminado, el mejor cliente por pais se considerara de acuerdo ala mayor cantidad de pedidosque ha realizado el cliente en el respectivo año.Debera devolver el nombre del pais, el nombredel cliente y la cantidad total de pedido realizadas por el respectivo cliente.*/
select*from productosselect*from cab_pedidoselect*from det_pedidoselect*from categoriasselect*from empleados
select cl.pais,cl.nom_cli,cant_pedidos=count(ca.num_pedido) from clientes as clinner join cab_pedido as ca on cl.cod_cli=ca.cod_cli where year(ca.fecha)=1997 group by cl.pais,cl.nom_cli order by cant_pedidos desc
/*4.-Crear un procedimiento almacenado que de acuerdo a un año enviado como parametro, listeel nombre del dia de la semana, cuantos pedidos se emitieron por cada uno de los dias de la semanacuantos pedidos se emitieron por cada uno de los dias de la semana, la cantidad total de pedidos en el año solicitado y su porcentaje en relacion a la cantidad total del año enviado,el porcentaje se obtiene: Porcentaje=(cantidad_pedido_semana*100)/(cantidad_total_pedidos)*/
CREATE PROCEDURE USP_PROB4@AÑO INT
ASdeclare @sum intselect @sum=count(num_pedido) from cab_pedido where year(fecha)=1996
select DIA=datename(dw,fecha),CANT_PED_DIA=count(num_pedido),CANT_TOTAL_AÑO=@sum, PORCENTAJE=round((count(num_pedido)*100)/152.00,2) from cab_pedidowhere year(fecha)=@AÑOgroup by datename(dw,fecha)GOEXEC USP_PROB4 1996
--PROCEDIMIENTOS ALMACENADOS DE LOS EJERCICIOS QUE DEJO
/*1.- CREAR UN PROC. ALMACENADO QUE DE ACUERDO A UNA OPCION DE TIPOENTERA PERMITA DEVOLVER EL APELLIDO DE LOS EMPLEADOS, SU FECHA DE NAC, SUFECHA DE CUMPLEAÑOS EN EL AÑO ACTUAL, DE QUIENES CUMPLIERON O FALTANCUMPLIR. ( 0 = CUMPLIERON, 1 = FALTAN CUMPLIR)*/
CREATE PROC USP_PROBLEMA1@OPCION INTASIF @OPCION=0
SELECT APELLIDOS,FECHA_NAC,CUMPLEANOS=STR(MONTH(FECHA_NAC),2)+'-' +STR(DAY(FECHA_NAC),2)
FROM EMPLEADOS where month(getdate())>month(fecha_nac)elseSELECT APELLIDOS,FECHA_NAC,CUMPLEANOS=STR(MONTH(FECHA_NAC),2)+'-' +STR(DAY(FECHA_NAC),2)
FROM EMPLEADOS where month(getdate())<month(fecha_nac)GOexec USP_PROBLEMA1 1exec USP_PROBLEMA1 0
/*2.- CREAR UN PROC. ALMACENADO QUE DE ACUERDO A UN AÑO ENVIADO COMOPARAMETRO, DEVUELVA EL ACUMULADO DE VENTAS REALIZADAS POR MES. SEDEBERA MOSTRAR EL NOMBRE DEL MES Y SUS VENTAS ACUMULADAS.OBS: LOS MESES DEBERAN MOSTRARSE EN SU VERDADERO ORDEN , ES DECIR, DEBERAEMPEZAR EN ENERO Y TERMINAR EN DICIEMBRE .*/
create PROC USP_PROBLEMA2@ANO INT,@MES VARCHAR(10)ASSELECT Mes=DATENAME(mm,(C.FECHA)),CANT=SUM(D.CANTIDAD*D.PRECIO) FROM CAB_PEDIDO AS CINNER JOIN DET_PEDIDO AS D ON C.NUM_PEDIDO=D.NUM_PEDIDO WHERE YEAR(C.FECHA)=@ano AND DATENAME(MM,C.FECHA)=@MESGROUP BY DATENAME(mm,(C.FECHA)) ORDER by DATENAME(mm,(C.FECHA)) desc
goexec USP_PROBLEMA2 1996,'JULIO'
/*3.- CREAR UN PROC. ALMACENADO QUE LISTE SEGUN UNA OPCION EL O LOSPRODUCTOS MAS VENDIDOS O LOS PRODUCTOS M ENOS VENDIDOS DE ACUERDO AUN CODIGO DE CATEGORIA ENVIADO TAMBIEN COMO PARAMETRO. PARADETERMINAR QUE PRODUCTOS SON LOS MAS O MENOS VENDIDOS, SE TOMARA ENCUENTA LA CANTIDAD DE VECES QUE APARECE EL PRODUCTO EN UN DETALLE DEPEDIDO, ES DECIR EL CODIGO DEL P RODUCTO QUE APARECE MENOS VECES EN LATABLA DETALLE DE PEDIDO SERA EL MENOS VENDIDO, CASO CONTRARIO ES EL MASVENDIDO.*/
CREATE PROC USP_PROBLEMA3@COD_CAT INT,@OPCION INTASIF @OPCION=1
SELECT top 1 TOTAL=COUNT(*),D.COD_PROD FROM productos AS P INNER JOINDET_PEDIDO AS D ON P.COD_PROD=D.COD_PROD group by D.COD_PROD ORDER BY COUNT(*) DESCELSE
SELECT top 1 TOTAL=COUNT(*),D.COD_PROD FROM productos AS P INNER JOINDET_PEDIDO AS D ON P.COD_PROD=D.COD_PROD group by D.COD_PROD ORDER BY COUNT(*) ASC
GO
/*4.- CREAR UN PROC. ALMACENADO QUE LISTE LOS NOMBRES DE LOS CLIENTES QUEHAYAN REALIZADO MAS PEDIDOS EN UN TRIMESTRE Y EN UN AÑO ENVIADOS COMOPARÁMETROS.NOTA: PUEDE UTILIZAR LA FUNCION DATEPAR(Q, FECHA) PARA EVALUAR LOSTRIMESTRES.*/
SELECT* FROM DET_PEDIDOSELECT*FROM PRODUCTOSSELECT*FROM CAB_PEDIDOSELECT*FROM CLIENTES
CREATE PROC USP_PROBLEMA4@COD_CLI CHAR(5),@AÑO INTASSELECT C.NOM_CLI,D.NUM_PEDIDO FROM CLIENTES AS C INNER JOIN CAB_PEDIDO AS CAON C.COD_CLI=CA.COD_CLI INNER JOIN DET_PEDIDO AS D ON D.NUM_PEDIDO=CA.NUM_PEDIDOWHERE YEAR(CA.FECHA)=1996 GROUP BY DATEPART(Q,A.FECHA)
GO
/*5.- CREAR UN PROCEDIMIENTO ALMACENADO QUE LISTE LA CANTIDAD DE ORDENESDE UN DETERMINADO CLIENTE PARA UN DETERMINADO MES Y AÑO ENVIADOSCOMO PARAMETRO, ESTE P ROCEDIMIENTO ALMACENADO DEBERA DEVOLVERCOMO VALOR DE RETORNO EL NÚMERO DE ORDENES EFECTUADOS EN ESE PERIODO.NOTA: EL MES SERA ENVIADO EN LETRAS, UTILICE LA FUNCION DATENAME*/
SELECT* FROM DET_PEDIDOSELECT*FROM CAB_PEDIDOSELECT*FROM PRODUCTOS
SELECT*FROM CLIENTES
CREATE PROC USP_PROBLEMA5@COD_CLI CHAR(5),@MES VARCHAR(10),@AÑO INTASSELECT NUM_PEDIDO,FECHA FROM CAB_PEDIDO WHERE COD_CLI=@COD_CLI AND DATENAME(MM,FECHA)=@MES ANDYEAR(FECHA)=@AÑO RETURN @@ROWCOUNTGO
EXEC USP_PROBLEMA5 'VINET' ,'NOVIEMBRE', 1997
TRIGERTS : DESENCADENADORES-- TRIGGERS (DESENCADENADORES)-- DML/* SINTAXIS:
CREATE TRIGGER NomTriggerON {NombreTabla ó NombreVista}{ FOR ó AFTER | INSTEAD OF } { INSERT y/o DELETE y/o UPDATE }[ WITH ENCRYPTION ]AS
-- Diversas instrucciones SQL-- Criterios[
if CRITERIOROLLBACK
]GO
NOTA: El orden de ejecución entreun Trigger, Constraint o Rules, es elsiguiente:
1ero, se ejecuta el RULE2do, se ejecuta el CONSTRAINT3er, se ejecuta el TRIGGER
*/
CREATE DATABASE BDTRIGGERSGOUSE BDTRIGGERSGO
CREATE TABLE PRODUCTOS(
COD_PROD INT PRIMARY KEY,NOM_PROD VARCHAR(50),PRE_PROD MONEY,STK_PROD INT,COD_CAT INT
)GO-- Poblaremos las filas de la tabla PRODUCTOS desde la tabla-- PRODUCTS de la base de datos NorthwindINSERT INTO PRODUCTOS
SELECT productid, productname,unitprice,unitsinstock,categoryidFROM Northwind.dbo.Products
go
-- Crear un Trigger que no permita la actualización de los precios-- de los productos si es que el mes actual es par.create trigger tr_no_act_precioson PRODUCTOS after updateas
-- declarar una variable que capture el nro del mes actualdeclare @nmes intselect @nmes=month(getdate())-- si el residuo de @nmes y 2 es cero, no se permite actualizarif @nmes % 2 = 0begin
raiserror('No se permite la actualización de precios en meses Pares',14,1)
rollback -- cancela todas las operaciones realizadas hasta aquí
endgo-- Raiserror('mensaje' ó Nro. de Mensaje, Numero de Gravedad, -- Bit de Comprobacion [, Valores que reemplazaran a las variables] )-- Nota: Las posibles variables a utilizar dentro del mensaje del Raiserror-- son:-- %s, para cadenas y-- %d, para un numero entero select * from PRODUCTOSUPDATE PRODUCTOS SET PRE_PROD=40 WHERE COD_PROD=1-- después de aumentar un messelect month(getdate())select * from PRODUCTOSUPDATE PRODUCTOS SET PRE_PROD=20 WHERE COD_PROD=1-- error, por el numero del mesUPDATE PRODUCTOS SET nom_prod='cambiado' WHERE COD_PROD=1-- error, incluso el nombre del producto, no se permite actualizar
sp_helptext tr_no_act_preciosgo
alter trigger tr_no_act_precios on PRODUCTOS after update as -- si se está actualizando la columna PRE_PROD if update(pre_prod) begin
-- declarar una variable que capture el nro del mes actual declare @nmes int
select @nmes=month(getdate()) -- si el residuo de @nmes y 2 es cero, no se permite actualizar if @nmes % 2 = 0 begin raiserror('No se permite la actualización de precios en meses
Pares',14,1) rollback -- cancela todas las operaciones realizadas hasta
aquí end
endgo
UPDATE PRODUCTOS SET nom_prod='cambiado' WHERE COD_PROD=1go-- okselect top 1 * from PRODUCTOSgo
-- Crear un Trigger que no permita la eliminación de los productos-- si es que el usuario no es el administrador.create trigger tr_no_eli_sino_eres_adminon PRODUCTOS after DELETEas
-- declarar una variable que almacene el nombre del usuario activo
declare @usuario varchar(25)select @usuario=system_user
if @usuario not like '%Administra%'-- if patindex('%Administra%',@usuario)=0begin
raiserror('El usuario: %s, No puede eliminar Productos',14,1,@usuario)
rollbackend
go
-- crear 1 inicio de sesion del servidorcreate login prueba1 with password='123', default_database=bdtriggersgo-- luego, crearemos un usuario de bd, que relaciones al login recientemente -- creado.use bdtriggersgocreate user prueba1go-- luego le otorgamos los permisos a nivel de objeto SELECT y DELETE sobre la-- tabla PRODUCTOS al usuario prueba1grant select, delete on PRODUCTOS to prueba1go
-- cambiamos de usuariosetuser 'prueba1'goselect system_userselect * from PRODUCTOS-- okDELETE PRODUCTOS WHERE COD_PROD=1-- volviendo al usuario Administradorsetuser
select system_user-- A5_21\AdministratorDELETE PRODUCTOS WHERE COD_PROD=1go
-- Modificar el Primer Trigger (Actualización), para que sólo permita-- actualizar los productos que correspondan su categoria con el número-- del mes actual. Para aquellos meses en donde no haya categorias, -- no se permite la actualización de los precios de ningún producto.sp_helptext tr_no_act_preciosgo
ALTER trigger tr_no_act_precios on PRODUCTOS after update as -- si se está actualizando la columna PRE_PROD if update(pre_prod) begin -- declarar una variable que capture el nro del mes actual declare @nmes int select @nmes=month(getdate()) -- declarar una variable que almacene el codigo de categoria del registro actualizado declare @codcat int select @codcat=cod_cat from deleted -- si el @nmes no es igual a @codcat, entonces no se permite actualizar if @nmes <> @codcat begin raiserror('Sólo se permite la actualización de precios según la categoria',14,1) rollback -- cancela todas las operaciones realizadas hasta aquí end end go
select * from PRODUCTOS where cod_cat=5update PRODUCTOS set pre_prod=pre_prod*1.5 where cod_prod in (22,23)goselect * from PRODUCTOS where cod_cat=5go
-- Desactivar o Activar Triggers/* Desactivar:
ALTER TABLE NombreTablaDISABLE TRIGGER NombreTrigger | ALL
Activar:ALTER TABLE NombreTabla
ENABLE TRIGGER NombreTrigger | ALL*/select * from PRODUCTOS where cod_prod = 2update PRODUCTOS set pre_prod=pre_prod*1.5 where cod_prod = 2
-- Desactivamos el TriggerALTER TABLE PRODUCTOS DISABLE TRIGGER tr_no_act_preciosGO
update PRODUCTOS set pre_prod=pre_prod*1.5 where cod_prod = 2select * from PRODUCTOS where cod_prod = 2
-- Activar el TriggerSP_HELPTRIGGER PRODUCTOSGOALTER TABLE PRODUCTOS ENABLE TRIGGER ALL --tr_no_act_preciosGO
TRIGERTS Y BD:-- TRIGGER DDL-- ***********/* sintaxis:
CREATE TRIGGER NomTriggerON { DATABASE ó ALL SERVER }AFTER { Comando_DDL } -- CREATE_TABLEAS
-- Instrucciones_SQL-- Criterios-- Rollback
GO*/
select eventdata()go
use northwindgo
-- trigger ddl, que no permite la creacion -- de nuevas tablas, en la bd activacreate trigger tr_1on database after create_tableas
select eventdata()rollback
go
create table tabla1( col1 int )go
-- desactivando el trigger ddl: tr_1disable trigger tr_1 on databasego-- si fuera de servidor se debe colocar-- disable trigger nom_trigger on all server
create table audita_ddl( num int identity primary key, datos xml)go
-- activamos el trigger ddl: tr_1enable trigger tr_1 on databasego
-- modificamos el trigger tr_1, para que-- almacene el resultado de eventdata() en la-- tabla audita_ddlalter trigger tr_1
on database after create_tableas
declare @datos xmlselect @datos=eventdata()rollbackinsert into audita_ddl values(@datos)
go
create table historial(col1 int)go
select * from audita_ddlgo
create schema producciongocreate table produccion.prod_term(col1 int)go
select * from audita_ddlgo
--select * from audita_ddl--where datos like 'historial%'--go
select nom_tabla= datos.value('(EVENT_INSTANCE/ObjectName)[1]', 'varchar(30)') from audita_ddlgo
select nom_tabla=convert(varchar(30), datos.query('data(EVENT_INSTANCE/ObjectName)'))from audita_ddlgo
-- modificar el trigger tr_1 para impedir la -- creacion de nuevas tablas sólo en el esquema-- producción en cualquier otro esquema si es-- posible. Auditar si el usuario lo intenta en-- el esquema produccion.alter trigger tr_1on database after create_tableas
declare @datos xmlselect @datos=eventdata()-- declarar una variable cadena para el nombre-- del esquemadeclare @nom_esquema varchar(20)select @nom_esquema= @datos.value('(EVENT_INSTANCE/SchemaName)[1]',
'varchar(20)')-- si el esquema es Produccion, entonces-- cancelamos la operación y auditamosif lower(@nom_esquema)='produccion'begin
rollbackinsert into audita_ddl values(@datos)
endgo
create table prod_anula( col1 int)go-- ok
create table produccion.prod_anulados(col1 int)go
select * from audita_ddlgo
/* a nivel de base de datos:tablas, vistas, procedimentos almacenados, funciones, user*//* a nivel de servidordatabase, login*/
-- crear un trigger ddl que no permita-- nuevos inicios de sesion (logins) en-- el servidor.create trigger tr_s_loginon all server after create_loginas
select eventdata()raiserror('No se permite crear logins',14,1)rollback
go
create login prueba1 with password='123'go
