Sql Server

Creación de una Base de Datos

Create database nombre_bd

on 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 archivo
name =nombre logico
filename= 'ruta de archivo fisico'
size= tamaño inicial del archivo
maxsize= maximo tamaño de crecimietno
filegrowth= valor de crecimiento

--ejemplo--

Create database bd_prueba

on 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_prueba
go
Use master
go

CREATE DATABASE COMPRAS

ON
(NAME='[Link]',
FILENAME='C:\Data\[Link]',
SIZE=10MB,
MAXSIZE=15MB,
FILEGROWTH=10%)
LOG ON
(NAME='Compra01',
FILENAME='C:\Data\[Link]',
SIZE=7MB,
MAXSIZE=9MB,
FILEGROWTH=1MB)
GO

CREATE DATABASE LOGISTICA

ON
(NAME=Logistica01,
FILENAME='E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\[Link]',
SIZE=15MB,
MAXSIZE=25MB,
FILEGROWTH=1MB),
(NAME=Logistica02,
FILENAME='E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\[Link]',
SIZE=10MB,
MAXSIZE=15MB,
FILEGROWTH=1MB)
LOG ON
(NAME=Logistica03,
FILENAME='E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\[Link]',
SIZE=5MB,
FILEGROWTH=1MB)
GO
-------------------------------------------------
1)TAMAÑO DE LA B.D:30 MB
2)MAXIMO TAMAÑO DE LA B.D: NO TIENE LIMITE
------------------------------------------------
SP_HELPDB LOGISTICA
-----------------------------------------------------------
CREATE DATABASE FINANZAS
ON
(NAME=Finanzas01,
FILENAME='C:\Data\[Link]',
SIZE=25MB,
FILEGROWTH=1MB)
LOG ON
(NAME=Finanzas02,
FILENAME='C:\Data\[Link]',
SIZE=10MB,
FILEGROWTH=1MB),
(NAME=Finanzas03,
FILENAME='C:\Data\[Link]',
SIZE=5MB,
FILEGROWTH=1MB),
(NAME=Finanzas04,
FILENAME='C:\Data\[Link]',
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 FINANZAS
ADD FILE
(NAME=finanzas05,
FILENAME='C:\Data\[Link]',
SIZE=20MB)
------------ Agregar archivo de log de transaccion -------------
ALTER DATABASE FINANZAS
ADD LOG FILE
(NAME=finanzas06,
FILENAME='C:\Data\[Link]',
SIZE=4MB)
-------------------------------------------------------------
ALTER DATABASE FINANZAS
modify FILE
(NAME=finanzas05,
SIZE=30MB)
--------------------------------------------------------------
--------- BACKUP DE LA BASE DE DATOS ------------------------
BACKUP DATABASE FINANZAS TO DISK='C:\Data\[Link]'

--------- RESTAURAR LA BASE DE DATOS ------------------------

RESTORE DATABASE FINANZAS FROM DISK='C:\Data\[Link]'
--------------------------------------------------------------
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 BD3598
GO

ALTER DATABASE BD3598

MODIFY FILE
(
NAME=CONTA_DATA,
SIZE=15
)
GO

SP_HELPDB BD3598
GO

-- SOL_02 (ADICIONANDO UN NUEVO ARCHIVO)

-- COMO SE DESEA SEPARAR LA INFORMACION DE LA
-- NUEVA AREA, SE CREARA UN NUEVO FILEGROUP
ALTER DATABASE BD3598
ADD FILEGROUP FG_PRODUCCION
GO
-- LUEGO ADICIONAMOS UN NUEVO ARCHIVO DE DATOS
-- DENTRO DEL FILEGROUP
ALTER DATABASE BD3598
ADD FILE
(
NAME=PRODUCCION_DATA,
FILENAME='D:\3598\SECUNDARIOS\PROD_DATA.NDF',
SIZE=10
)
TO FILEGROUP FG_PRODUCCION
GO

SP_HELPDB BD3598
GO

USE BD3598
GO
-- CREANDO UNA TABLA ALMACENANDOLA DENTRO DE UN
-- FILEGROUP DISTINTO DEL PRINCIPAL
CREATE TABLE PROD_TERMINADOS
( CODIGO INT, FECHA_PROD DATETIME )
ON FG_PRODUCCION
GO

SP_HELP PROD_TERMINADOS
GO

---------------------------------------------
-- 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_IDAT
USE MASTER
GO
ALTER DATABASE BD3598
MODIFY NAME=BD_IDAT
GO

SP_HELPDB BD3598
GO
SP_HELPDB BD_IDAT
GO

-- RENOMBRAR LOS NOMBRES LOGICOS DE LOS ARCHIVOS

-- DE LA BD
ALTER DATABASE BD_IDAT
MODIFY FILE
(
NAME=BD3598_DATA,
NEWNAME=BD_IDAT_DATA
)
GO

SP_HELPDB BD_IDAT
GO

----------------------------------
ALTER DATABASE BD_IDAT
MODIFY FILE
(
NAME=BD_IDAT_DATA,
 SIZE=20
)
GO

SP_HELPDB BD_IDAT
GO
-- AL TRATAR DE REDUCIR EL TAMAÑO DEL ARCHIVO
-- MODIFY FILE NO LO PERMITE (NVO_TAM > TAM_ACTUAL)
ALTER DATABASE BD_IDAT
MODIFY 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)
--GO
USE BD_IDAT
GO
SP_HELPDB BD_IDAT
GO
DBCC SHRINKFILE(BD_IDAT_DATA, 5)
GO
SP_HELPDB BD_IDAT
GO

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 BD3598
ON 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 BD
SP_HELPDB BD3598
GO

USE BD3598
GO
CREATE TABLE CLIENTES
( CODIGO INT, NOMBRE VARCHAR(50))
GO
INSERT INTO CLIENTES VALUES(1,'MIGUEL DIAZ')
GO
SELECT * FROM CLIENTES
GO

-- ESTABLECIENDO A SOLO LECTURA A LA BD

ALTER DATABASE BD3598
SET READ_ONLY
GO
-- CAMBIA EL IDIOMA DEL SQL
SET LANGUAGE SPANISH
GO
INSERT INTO CLIENTES VALUES(2,'KARINA TORRES')
GO
SELECT * FROM CLIENTES
GO

-- ESTABLECIENDO A LECTURA-ESCRITURA A LA BD
ALTER DATABASE BD3598
SET READ_WRITE
GO

-- ESTABLECER EL ACCESO A UN UNICO USUARIO

ALTER DATABASE BD3598
SET SINGLE_USER
GO

SP_HELPDB BD3598
GO

-- RESTABLECEMOS EL ACCESO MULTIUSUARIO A LA BD

ALTER DATABASE BD3598
SET MULTI_USER
-- RESTRICTED_USER
GO

-- DEJANDO FUERA DE LINEA (DESACTIVANDO LA BD)

-- A LA BD
USE MASTER
GO
ALTER DATABASE BD3598
SET OFFLINE
GO

-- VOLVIENDO A ACTIVAR LA BD
ALTER DATABASE BD3598
SET ONLINE
GO

SP_HELPDB BD3598
GO

-- ESTABLECIENDO EL MODELO DE RECUPERACION

-- A FULL (MODELO COMPLETO)
-- RECOVERY: SIMPLE, BULK_LOGGED, FULL
ALTER DATABASE BD3598
SET RECOVERY FULL -- SIMPLE, BULK_LOGGED
GO
CONSTRAIN
create table Persona
(Codigo int,
Nombre varchar(70),
Telefono varchar(9),
Nacimiento datetime)

ALTER Table Persona

add constraint dTelf DEFAULT '999-99-9'for telefono
Insert into Persona values(1,'juan','[Link] alamos 450','458-21-
89',getdate())
insert into Persona(Codigo,Nombre,Direccion,Nacimiento)
values(2,'Jose','[Link] proceres 145',getdate())
-----------------------------------------------------
ALTER Table Persona
add constraint unom unique(nombre)
Insert into Persona values(2,'claudia','[Link] alamos 450','478-71-
89',getdate())
insert into Persona values(3,'claudia','[Link] perros 456','477-21-
89',getdate())
-----------------------------------------------------
ALTER Table Persona
add 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','[Link] 789','549-87-
63',getdate())
insert into Persona values(5,'Fuller','[Link] paz 120','A49-87-
63',getdate())
-----------------------------------------------------

select * from SYSOBJECTS

WHERE 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 PERSONA

DROP CTELF
______________________________________________________________________
______________________

SELECT:
SELECT * FROM PRODUCTS

SELECT ProductName,QuantityPerUnit FROM PRODUCTS

SELECT PRODUCTID AS Codigo,

PRODUCTNAME AS NOMBRE,
UNITPRICE AS PRECIO
FROM PRODUCTS
WHERE UNITPRICE>50
ORDER BY PRECIO DESC
____________________________________________________________________
SELECT [Link],
[Link],
[Link]
FROM PRODUCTS P,CATEGORIES C
WHERE [Link]=[Link]

INSERT:
_____________________________________________________________________
INSERT INTO GATEGORIES(CATEGORYNAME)VALUES('BEBIDAS')
SELECT * FROM ALUMNOS
INSERT INTO ALUMNOS VALUES('200','MARIA')

UPDATE:
_____________________________________________________________________
UPDATE PRODUCTS
SET UNITPRICE=UNITPRICE*1.10,
WHERE CATEGORYID>5
SELECT * FROM PRODUCTS
DELETE
------------------------------
DELETE FROM PRODUCTS
DELETE 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 ALUMNO

MOSTRAR TODAS LAS COLUMNA DE TLA TABLA ALUMNO ORDENADO ALFABETICAMENTE
MOSTRAR NOMBRE Y PROMEDIO DE LA TABLA ALUMNO

1
____________________________________________
SELECT * FROM ALUMNO
2
____________________________________________
SELECT * FROM ALUMNO
ORDER BY NOMBRE
3
____________________________________________
SELECT NOMBRE,((NOTA1+NOTA2+NOTA3)/3) AS PROMEDIO FROM ALUMNO
4
____________________________________________
SELECT NOMBRE,((NOTA1+NOTA2+NOTA3)/3) AS PROMEDIO FROM ALUMNO
ORDER BY PROMEDIO DESC
5
____________________________________________
SELECT NOMBRE,((NOTA1+NOTA2+NOTA3)/3) AS PROMEDIO FROM ALUMNO
ORDER BY NOMBRE
6
___________________________________________
SELECT * FROM ALUMNO
WHERE NOTA3 BETWEEN 13 AND 15 ORDER BY NOMBRE

7
___________________________________________

8
___________________________________________
SELECT NOMBRE FROM ALUMNO
WHERE NOMBRE LIKE'[J]%'
ORDER BY NOMBRE
9
__________________________________________
SELECT NOMBRE FROM ALUMNO
WHERE NOMBRE LIKE '%[U]%'
10
__________________________________________
SELECT NOMBRE FROM ALUMNO
WHERE NOMBRE LIKE '%[A,E,I,O,U]%'
ORDER BY NOMBRE

Backup :
-- COPIAS DE SEGURIDAD
CREATE DATABASE BD3598
GO

-- CAMBIA EL IDIOMA DEL SQL

SET 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 BD3598
TO 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 = 100

GO

-- COPIA DE SEGURIDAD DIFERENCIAL

BACKUP DATABASE BD3598
TO DISK='D:\BD3598_DIFF.BAK'
WITH DIFFERENTIAL, INIT
GO

-- 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 PRODUCTOS
DELETE 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_LOG

SP_HELPDB BD3598
GO
-- INSERTANDO 500000 FILAS DE PRUEBA
DECLARE @CONTA INT
SELECT @CONTA=1
WHILE @CONTA<=500000
BEGIN
INSERT INTO CLIENTES
VALUES(@CONTA, 'CLIENTES '+LTRIM(STR(@CONTA)))
SELECT @CONTA=@CONTA+1
END
GO

SP_HELPDB BD3598
GO

-- 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_ONLY
GO
/*
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 BD3598
GO

/*
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 BD3598
GO
/*
DESPUES TENEMOS QUE MOVER EL ARCHIVO LOGICO
HACIA OTRA UBICACION.
*/

/*
EN SQL 2005 LA INSTRUCCION CREATE DATABASE, PUEDE
UTILIZAR 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\[Link]'
)
FOR ATTACH_REBUILD_LOG
GO

/*
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 BD3598

TO DISK='D:\BD3598_COMPLETO.BAK'
WITH INIT
GO
SP_HELPDB BD3598
GO

/*
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, CHECKSUM
GO

RESTORE DATABASE BD3598

TO DISK='D:\BD3598_COMPLETO.BAK'
WITH INIT, CHECKSUM
GO

RESTAURACION :
-- RESTORE
USE MASTER
GO

DROP DATABASE BD3598

GO
-- RESTAURANDO LA COPIA DE SEGURIDAD COMPLETA
RESTORE DATABASE BD3598
FROM DISK='D:\BD3598_FULL.BAK'
WITH NORECOVERY
GO

USE BD3598
GO
SELECT * FROM PRODUCTOS BD3598
GO

-- RESTAURANDO LA COPIA DE SEG. DIFERENCIAL

RESTORE DATABASE BD3598
FROM DISK='D:\BD3598_DIFF.BAK'
WITH NORECOVERY
GO

-- RESTAURANDO LA COPIA DE SEG. DEL REGISTRO

RESTORE LOG BD3598
FROM DISK='D:\BD3598_LOG01.BAK'
WITH NORECOVERY
GO

-- FINALMENTE RESTAURAMOS EL ULTIMO ARCHIVO

-- DE COPIA DE SEG. DEL REG. DE TRANSACCIONES
RESTORE LOG BD3598
FROM DISK='D:\BD3598_LOG02.BAK'
WITH NORECOVERY
--WITH RECOVERY
GO

--RESTAURANDO EL ULTIMO ARCHIVO DE SEGURIDAD

RESTORE 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 B
WHERE [Link]=[Link] GROUP BY [Link],NOMBRE
HAVING AVG(NOTA)>10.5 ORDER BY NOMBRE
--2.-
SELECT NOMBRE,COUNT(NOTA)AS NUM FROM ALUMNO A,NOTA B
WHERE [Link]=[Link] GROUP BY [Link],NOMBRE
HAVING COUNT(NOTA)>2 ORDER BY AVG(COUNT)DESC
--3.-
SELECT DISTINCT NOMBRE FROM ALUMNO A,NOTA B
WHERE [Link]=[Link] AND NOTA IN(11,12)
--4.-
SELECT NOMBRE,COUNT(NOTA) AS DESAP FROM ALUMNO A,NOTA B
WHERE [Link]=[Link] AND NOTA<10.5
GROUP BY NOMBRE
--12.-
SELECT TOP(SELECT CONVERT(INT,ROUND(IDALUMNO)*33))FROM ALUMNO)NOMBRE
FROM AS A,NOTA AS N --PUEDE SER TOP 30 PARENT
WHERE [Link]=[Link]
GROUP BY [Link]
ORDER BY AVG(NOTA) DESC

--COMPLI
SELECT NOMBRE FROM ALUMNOS
WHERE ID_ALUMNO IN (SELECT ID_ALUMNO FROM NOTAS
WHERE NOTA=11 AND ID_ALUMNO IN (SELECT ID_ALUMNO FROM NOTAS WHERE
NOTA=12))

CONSTRAINT :
CREATE DATABASE BD3598
GO
USE BD3598
GO
CREATE TABLE [Link]
(
COD_ALM INT,
NOM_ALM VARCHAR(30)
)
GO

CREATE SCHEMA CONTABILIDAD

GO
CREATE TABLE [Link]
(
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 [[Link].]NomTabla
(
NomCol Tipo_Dato [ Constraint ...]
)
[ ON NomFileGroup]

Donde Tipo_Dato, puede ser:

Tipo definido por usuario
Columna Calculada
Función (escalar) definida por usuario
XML
*/
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)

go
select * from Notas1
go

-- creamos una función escalar que devuelva

-- el promedio
create function promedio
(@n1 int,@n2 int,@n3 int)
returns int
as
begin
return (select (@n1+@n2+@n3)/3.0)
end
go
-- probando la funcion
select prom=[Link](14,12,15)
go

create table notas2

(
codalu int,codcur int,
pp int, pt int, ex int,
prom as [Link](pp,pt,ex)
)
go

insert into notas2 values(19,23,14,11,18)

go
select * from notas2
go

--------------------------------------
-- Campo XML en una tabla
create table Alumnos
(
nro_alu int,
datos_alu xml,
fecha_reg datetime
)
go

insert into Alumnos Values( 123,

'<Alumnos>
<Nombre>Fernando Vargas</Nombre>
<Email>fvargas@[Link]</Email>
<Telefonos>
<Casa>4521896</Casa>
<Celular>998563028</Celular>
</Telefonos>
</Alumnos>',
getdate()
)
go
select * 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')
go
insert into Vendedor
values(9,'Roger Verastegui','7465436')
go
insert into Vendedor
values(81,'Ana Vargas','3324538')
go

select * from Vendedor

go

insert into Vendedor

values(6,'Ana Torres',null)
go
select * from Vendedor
go

-------------------------------------------
/*
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 Cursos
Add Precio money
go

Alter Table Cursos

Add Constraint ck_Precio
Check(Precio>=200)
go

sp_helpconstraint Cursos
go

/*
Para Desactivar o Activar un Constraint
(Check o Foreign Key)
-- Desactivamos
Alter table NomTabla
NOCHECK { Nombre_Constraint | All }

-- Activamos
Alter table NomTabla
CHECK { Nombre_Constraint | All }

*/

/*
Para Evitar la Comprobación de los valores
de las columnas al momento de crear un nuevo
constraint.

Alter Table NomTabla WITH NOCHECK

ADD Constraint NomConstraint
Tipo_Constraint
Expresion_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 NomTblSecundaria
Add [ Constraint NomConstraint ]
Foreign Key(Col_Tbl_Sec)
References NomTblPrincipal(Col_Tbl_Princ)
[
on delete cascade
on update cascade
]

-- en cambio, al crear la tabla secundaria

Create Table Principal
(
NomCol Tipo_Dato { Primary Key o UNIQUE }
)
go
Create Table Secundaria
(
NomCol_sec Tipo_Dato,
....
NomCol_Prin Tipo_Dato
References NomTbl_Principal(NomCol)
)
go

SEGURIDAD
Administrador- Creacion de Usuarios
USE [master]
GO
CREATE LOGIN [USQL1]
WITH PASSWORD=N''
MUST_CHANGE,
DEFAULT_DATABASE=[BDSEGURIDAD],
DEFAULT_LANGUAGE=[Español],
CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO

USE [BDPEDIDOS]
GO
CREATE USER [USQL1] FOR LOGIN [USQL1]
GO
USE [BDSEGURIDAD]
GO
CREATE USER [USQL1] FOR LOGIN [USQL1]
GO

/*
Otorgando el derecho de leer e insertar
registros en la tabla categorias al
usuario USQL1 (Permiso a Nivel de Objeto)
*/
use bdpedidos
go
grant select, insert on [Link]
to USQL1
go

/*
Otorgando el derecho de creación de tablas
al usuario USQL1 en la base de datos
BDSEGURIDAD. (Permiso a nivel de instrucción)
*/
use bdseguridad
go
grant create table to USQL1
go
-- Otorgar el permiso de Alterar el Esquema
grant alter on schema::dbo to USQL1
go

/*
USE [master]
GO
CREATE LOGIN [USQL2]
WITH PASSWORD=N'P@ssw0rd',
DEFAULT_DATABASE=[BDSEGURIDAD],
DEFAULT_LANGUAGE=[Español],
CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
USE [BDSEGURIDAD]
GO
CREATE USER [USQL2] FOR LOGIN [USQL2]
GO
*/

-- Creamos un Esquema
create schema marketing
go

-- Cambiando el esquema predeterminado al

-- usuario USQL2, de dbo por marketing
alter user USQL2
with default_schema=marketing
go

/*
al momento de la creacion de un usuario de bd
se puede establecer el nombre del esquema que
quiera asignarsele.
CREATE USER Nom_Usuario
WITH Default_Schema=Nombre_Schema
*/

/*
Recuerde, para que un usuario de BD, pueda crear
objetos, es necesario a parte del permiso de
CREATE 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 gMarketing
go

-- 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 gMarketing
go
-- no olvidar alterar el esquema
GRANT 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 gMarketing
go

/*
deny insert on marketing.Nom_Tabla to usuario

deny insert on schema::marketing to usuario

*/

-- usuario USQL1
use bdpedidos
go
select * from [Link]
go
insert into [Link] values('Artefactos')
go
select * from [Link]
go

--------------------------------
use bdseguridad
go
create 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

-- Esquema
create table vendedor
(
cod_ven int, nom_ven varchar(50)
)
go

-- si el usuario USQL1, intenta listar el

-- contenido de la tabla Vendedor
select * from [Link]
go

-- usuario usql2
CREATE TABLE PRODUCTOS
(
COD_PROD INT,
NOM_PROD VARCHAR(50)
)
GO

INSERT INTO [Link]

VALUES(1,'LG Scarlet 32')
go
SELECT * FROM [Link]
go

CREATE PROC LISTAR_PRODUCTOS

AS
SELECT * FROM [Link]
ORDER BY NOM_PROD
go

EXEC LISTAR_PRODUCTOS
go
-- Error, no tiene el permiso de EXECUTE

TRANSACCIONES :
-- "NUEVAS" INSTRUCCIONES T-SQL
USE BDPEDIDOS
GO
-- TOP(n)
-- SQL 2000
SELECT TOP 3 * FROM CLIENTES
GO
DECLARE @N INT
SELECT @N=5
SELECT TOP @N * FROM CLIENTES
GO
-- SQL DINAMICO
DECLARE @CAD VARCHAR(500)
SELECT @CAD='SELECT TOP '
DECLARE @N INT
SELECT @N=5
SELECT @CAD=@CAD + STR(@N,3) + ' * FROM CLIENTES'
-- PRINT @CAD
EXEC(@CAD)
GO
-------------------------------------------------------------

-- VARIANTE
DECLARE @N INT
SELECT @N=4
SET ROWCOUNT @N -- ESTABLECIENDO EL LIMITE
-- DE FILAS A MOSTRAR
SELECT * FROM CLIENTES
SELECT * FROM CAB_PEDIDO
SET ROWCOUNT 0 -- RESTABLECE SIN RESTRICCION
GO

SELECT * FROM CLIENTES

GO

-- SQL 2005
DECLARE @N INT
SELECT @N=4
SELECT TOP(@N) * FROM CLIENTES
GO
-- Se puede utilizar: INSERT, UPDATE y DELETE
CREATE TABLE CLIENTES_AMERICA
(
COD_CLI CHAR(5) PRIMARY KEY,
NOM_CLI VARCHAR(50),
PAIS_CLI VARCHAR(30)
)
GO

DECLARE @N INT
SELECT @N=15
INSERT TOP @N CLIENTES_AMERICA
SELECT COD_CLI, NOM_CLI, PAIS
FROM CLIENTES
WHERE PAIS IN
('MEXICO','BRAZIL','ARGENTINA')
GO

SELECT * FROM CLIENTES_AMERICA

GO

/*
DECLARE @N INT
SELECT @N=15
INSERT CLIENTES_AMERICA
SELECT TOP(@N) COD_CLI, NOM_CLI, PAIS
FROM 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 INT

SET @CANTIDAD=(SELECT COUNT(*) FROM CLIENTES)
PRINT @CANTIDAD
GO

DECLARE @CANTIDAD INT

SELECT @CANTIDAD=COUNT(*) FROM CLIENTES
PRINT @CANTIDAD
GO
*/

--------------------------------
-- OUTPUT
--------------------------------
/*
Permite acceder a las tablas internas
INSERTED y DELETED, para mostrar los nuevos
registros, o los registros eliminados y/o
actualizados
*/
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 CLIENTES
GO
SET ROWCOUNT 0
SELECT * FROM CLIENTES
SELECT * FROM EMPLEADOS
---------------------------------------
-- TRANSACCIONES
---------------------------------------
-- SQL 2000 y SQL 2005
-- SINTAXIS:
BEGIN TRAN [SACTION]
-- INSTRUCCIONES SQL
-- "
-- "
IF @@ERROR<>0 -- HUBO UN ERROR EN ALGUNA
-- INSTRUCCION
ROLLBACK TRAN [SACTION]
ELSE -- SINO HUBO ERROR
COMMIT TRAN [SACTION]
GO

-------------------------------

begin tran
declare @err_eli int, @err_ins int

insert into clientes_america

values('XYZ04','CLIENTE XYZ01','Peru')
set @err_ins=@@error
print 'Error='+str(@err_ins)

delete clientes where cod_cli='OCEAN'

set @err_eli=@@error
print 'Error='+str(@err_eli)

-- Hubo un Error en alguna instruccion

if @err_ins<>0 or @err_eli<>0
RollBack Tran
else -- Sino, No hubo Error en ninguna
-- instruccion
Commit Tran
go
select * from clientes where cod_cli='FISSA'
go
SP_HELPCONSTRAINT CAB_PEDIDO
GO
-- cambiando el tipo de dato a la columna
-- cod_cli de cab_pedido de nchar(5) a
-- char(5)
SELECT * FROM CAB_PEDIDO
alter table cab_pedido
alter column cod_cli char(5)
go
-- creando el foreign key entre cab_pedido
-- y clientes
alter table cab_pedido with nocheck
add foreign key(cod_cli)
references clientes(cod_cli)
go

select * from clientes_america

where cod_cli='XYZ01'
go
funciones [Link] :
use northwind
go

-- Funciones Definidas por el Usuario

/*
1. Funcion Escalar
Devuelve un valor escalar (un numero, una cadena,
una fecha)

Sintaxis:

CREATE FUNCTION NomFuncion (@Prm Tipo_Dato)

RETURNS Tipo_Dato_Devolver
AS
BEGIN
-- Instrucciones SQL
RETURN @Var_Tipo_Dato_Devolver

-- Podría utilizarse también la sgte. forma:

-- RETURN (SELECT UnaColumna_UnaFila FROM ... )
END
GO
*/
-- Ejemplo
/*
Crear una Función Escalar que devuelva el nombre del
dia de la semana de una fecha.
*/
ALTER FUNCTION FN_DIA_SEMANA(@FECHA DATETIME)
RETURNS VARCHAR(50)
AS
BEGIN
-- MOMBRE DEL DIA
DECLARE @NOM VARCHAR(50)
SELECT @NOM=DATENAME(DW, @FECHA)
-- AGREGAMOS EL DIA
SELECT @NOM=@NOM + ', '+STR(DAY(@FECHA),2)
-- AGREGAMOS EL MES
 SELECT @NOM=@NOM + ' DE '+DATENAME(MM,@FECHA)
-- AGREGAMOS EL AÑO
SELECT @NOM=@NOM + ' DE '+ STR(YEAR(@FECHA),4)
RETURN @NOM

-- RETURN (SELECT DATENAME(DW, @FECHA))

END
GO
SET LANGUAGE SPANISH
GO
SELECT 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)=1996
GO

/*
Crear una Función Escalar que devuelva la cantidad
de 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 INT
AS
BEGIN
RETURN (
select count(*) from orders
where customerid=@COD_CLI and
YEAR(orderdate)=@AÑO
)
END
GO

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 customers
go

/*
2. Función de Tipo Tabla en Línea
Devuelve el contenido de una sentencia SELECT, como
una Tabla, a diferencia de uan vista en donde también
se utiliza un SELECT, en la función el SELECT puede
utilizar argumentos, variables o parámetros.
*/
create view v_productos
as
select productid, productname, categoryid
from products
go
select * from v_productos
go

create function fn_productos(@codcat int)

returns table
as
return (
 select productid, productname, categoryid
from products
where categoryid=@codcat
)
go
select * from dbo.fn_productos(7)
go

/*
Crear una función de tipo tabla que devuelva según
un año enviado como parámetro los nombres de los 5
clientes que han comprado menos, y la cantidad de
ordenes que hayan realizado. Considere comprar menos
a los clientes que hayan realizado pocos pedidos en ese
año.
*/
create function fn_clientes(@año int)
returns table
as
return(
select top 5 companyname, cant=count(*)
from customers c inner join orders o
on [Link]=[Link]
where year(orderdate)=@año
group by companyname
order by cant asc
)
go

select * from dbo.fn_clientes(1996)

go

/*
3. Función Tipo Tabla de Múltiples Instrucciones
Devuelve una tabla, que debe haber sido definida en
la cabecera de la función. Esta tabla debe ser
poblada por instrucciones INSERT INTO ... VALUES o
por 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, ...)
AS
BEGIN
-- Múltiples instrucciones SQL
-- Luego debemos poblar la tabla
INSERT INTO @NomTabla VALUES( ........)

INSERT INTO @NomTabla

SELECT ...... FROM ....... TABLA

RETURN -- esa instruccion devuelve la tabla

END
GO
*/
/*
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 enviado
como parámetro.
*/

create alter function fn_tabla_prod_cat

(@cod_cat int)
returns @tbl table (nomprod varchar(50),
precio int,
stock int)
as
begin
-- adicionamos la información de los productos
-- de una categoria
insert into @tbl
select productname, unitprice,unitsinstock
from products
where categoryid=@cod_cat
-- variables para capturar el promedio de los
-- precios y la suma de los stock
declare @prom_precio int,@suma_stock int
select @prom_precio=AVG(precio),
@suma_stock=SUM(stock)
from @tbl
-- insertamos los datos obtenidos
insert 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 @tbl

return
end
go

select * from fn_tabla_prod_cat(7)

go

PRACTICA Resuelta:
/*1.-que nombres de categoria de producto fue la mas vendida el primer
año
y cuanto fue el importe vendido
*/

select*from productos
select*from cab_pedido
select*from det_pedido
select*from categorias

select top 1 c.nom_cat,importe=sum([Link]*[Link]) 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([Link])=1996
group by c.nom_cat

/*2.-Listar el total facturado por el empleado mas antiguo(utilice al

campo fecha de contrato
por el que hara la comprobacion de la antiguedad), debera mostrar un
codigo,apellido,fecha de
contrato, cantidad de ordenes y el monto total genenrado por estas
ordenes(cantidad*precio)
*/
select*from productos
select*from cab_pedido
select*from det_pedido
select*from categorias
select*from empleados

select top 1
e.cod_emp,[Link],e.fecha_contrato,cant_ordenes=count(ca.num_pedid
o),importe=
sum([Link]*[Link]) from empleados as e inner join cab_pedido as
ca on e.cod_emp=ca.cod_emp
inner join det_pedido as d on ca.num_pedido=d.num_pedido group by
e.cod_emp,[Link],
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ño
determinado, el mejor cliente por pais se considerara de acuerdo ala
mayor cantidad de pedidos
que ha realizado el cliente en el respectivo añ[Link] devolver el
nombre del pais, el nombre
del cliente y la cantidad total de pedido realizadas por el respectivo
cliente.*/

select*from productos
select*from cab_pedido
select*from det_pedido
select*from categorias
select*from empleados

select [Link],cl.nom_cli,cant_pedidos=count(ca.num_pedido) from

clientes as cl
inner join cab_pedido as ca on cl.cod_cli=ca.cod_cli where
year([Link])=1997
group by [Link],cl.nom_cli order by cant_pedidos desc

/*4.-Crear un procedimiento almacenado que de acuerdo a un año enviado

como parametro, liste
el nombre del dia de la semana, cuantos pedidos se emitieron por cada
uno de los dias de la semana
cuantos 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
AS
declare @sum int
select @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_pedido
where year(fecha)=@AÑO
group by datename(dw,fecha)
GO
EXEC USP_PROB4 1996

--PROCEDIMIENTOS ALMACENADOS DE LOS EJERCICIOS QUE DEJO

/*1.- CREAR UN PROC. ALMACENADO QUE DE ACUERDO A UNA OPCION DE TIPO

ENTERA PERMITA DEVOLVER EL APELLIDO DE LOS EMPLEADOS, SU FECHA DE NAC,
SU
FECHA DE CUMPLEAÑOS EN EL AÑO ACTUAL, DE QUIENES CUMPLIERON O FALTAN
CUMPLIR. ( 0 = CUMPLIERON, 1 = FALTAN CUMPLIR)*/

CREATE PROC USP_PROBLEMA1

@OPCION INT
AS
IF @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)
else
SELECT APELLIDOS,FECHA_NAC,CUMPLEANOS=STR(MONTH(FECHA_NAC),2)+'-'
+STR(DAY(FECHA_NAC),2)
FROM EMPLEADOS where month(getdate())<month(fecha_nac)
GO
exec USP_PROBLEMA1 1
exec USP_PROBLEMA1 0

/*2.- CREAR UN PROC. ALMACENADO QUE DE ACUERDO A UN AÑO ENVIADO COMO

PARAMETRO, DEVUELVA EL ACUMULADO DE VENTAS REALIZADAS POR MES. SE
DEBERA MOSTRAR EL NOMBRE DEL MES Y SUS VENTAS ACUMULADAS.
OBS: LOS MESES DEBERAN MOSTRARSE EN SU VERDADERO ORDEN , ES DECIR,
DEBERA
EMPEZAR EN ENERO Y TERMINAR EN DICIEMBRE .
*/

create PROC USP_PROBLEMA2

@ANO INT,
@MES VARCHAR(10)
AS
SELECT Mes=DATENAME(mm,([Link])),CANT=SUM([Link]*[Link]) FROM
CAB_PEDIDO AS C
INNER JOIN DET_PEDIDO AS D ON C.NUM_PEDIDO=D.NUM_PEDIDO WHERE
YEAR([Link])=@ano
AND DATENAME(MM,[Link])=@MES
GROUP BY DATENAME(mm,([Link])) ORDER by DATENAME(mm,([Link])) desc

go
exec USP_PROBLEMA2 1996,'JULIO'
/*
3.- CREAR UN PROC. ALMACENADO QUE LISTE SEGUN UNA OPCION EL O LOS
PRODUCTOS MAS VENDIDOS O LOS PRODUCTOS M ENOS VENDIDOS DE ACUERDO A
UN CODIGO DE CATEGORIA ENVIADO TAMBIEN COMO PARAMETRO. PARA
DETERMINAR QUE PRODUCTOS SON LOS MAS O MENOS VENDIDOS, SE TOMARA EN
CUENTA LA CANTIDAD DE VECES QUE APARECE EL PRODUCTO EN UN DETALLE DE
PEDIDO, ES DECIR EL CODIGO DEL P RODUCTO QUE APARECE MENOS VECES EN LA
TABLA DETALLE DE PEDIDO SERA EL MENOS VENDIDO, CASO CONTRARIO ES EL
MAS
VENDIDO.*/

CREATE PROC USP_PROBLEMA3

@COD_CAT INT,
@OPCION INT
AS
IF @OPCION=1

SELECT top 1 TOTAL=COUNT(*),D.COD_PROD FROM productos AS P INNER JOIN

DET_PEDIDO AS D ON P.COD_PROD=D.COD_PROD
group by D.COD_PROD ORDER BY COUNT(*) DESC
ELSE
SELECT top 1 TOTAL=COUNT(*),D.COD_PROD FROM productos AS P
INNER JOIN
DET_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
QUE
HAYAN REALIZADO MAS PEDIDOS EN UN TRIMESTRE Y EN UN AÑO ENVIADOS COMO
PARÁMETROS.
NOTA: PUEDE UTILIZAR LA FUNCION DATEPAR(Q, FECHA) PARA EVALUAR LOS
TRIMESTRES.*/

SELECT* FROM DET_PEDIDO

SELECT*FROM PRODUCTOS
SELECT*FROM CAB_PEDIDO
SELECT*FROM CLIENTES

CREATE PROC USP_PROBLEMA4

@COD_CLI CHAR(5),
@AÑO INT
AS
SELECT C.NOM_CLI,D.NUM_PEDIDO FROM CLIENTES AS C INNER JOIN CAB_PEDIDO
AS CA
ON C.COD_CLI=CA.COD_CLI INNER JOIN DET_PEDIDO AS D ON
D.NUM_PEDIDO=CA.NUM_PEDIDO
WHERE YEAR([Link])=1996 GROUP BY DATEPART(Q,[Link])

GO

/*5.- CREAR UN PROCEDIMIENTO ALMACENADO QUE LISTE LA CANTIDAD DE

ORDENES
DE UN DETERMINADO CLIENTE PARA UN DETERMINADO MES Y AÑO ENVIADOS
COMO PARAMETRO, ESTE P ROCEDIMIENTO ALMACENADO DEBERA DEVOLVER
COMO 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_PEDIDO
SELECT*FROM CAB_PEDIDO
SELECT*FROM PRODUCTOS

SELECT*FROM CLIENTES

CREATE PROC USP_PROBLEMA5

@COD_CLI CHAR(5),
@MES VARCHAR(10),
@AÑO INT
AS
SELECT NUM_PEDIDO,FECHA FROM CAB_PEDIDO WHERE COD_CLI=@COD_CLI AND
DATENAME(MM,FECHA)=@MES AND
YEAR(FECHA)=@AÑO RETURN @@ROWCOUNT
GO

EXEC USP_PROBLEMA5 'VINET' ,'NOVIEMBRE', 1997

TRIGERTS : DESENCADENADORES
-- TRIGGERS (DESENCADENADORES)
-- DML
/* SINTAXIS:

CREATE TRIGGER NomTrigger

ON {NombreTabla ó NombreVista}
{ FOR ó AFTER | INSTEAD OF } { INSERT y/o DELETE y/o UPDATE }
[ WITH ENCRYPTION ]
AS
-- Diversas instrucciones SQL
-- Criterios
[
if CRITERIO
ROLLBACK
]
GO

NOTA: El orden de ejecución entreun Trigger, Constraint o Rules, es el

siguiente:
1ero, se ejecuta el RULE
2do, se ejecuta el CONSTRAINT
3er, se ejecuta el TRIGGER
*/

CREATE DATABASE BDTRIGGERS

GO
USE BDTRIGGERS
GO
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 Northwind
INSERT INTO PRODUCTOS
SELECT productid, productname,unitprice,unitsinstock,categoryid
FROM [Link]
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_precios
on PRODUCTOS after update
as
-- 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
go
-- 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 PRODUCTOS

UPDATE PRODUCTOS SET PRE_PROD=40 WHERE COD_PROD=1
-- después de aumentar un mes
select month(getdate())
select * from PRODUCTOS
UPDATE PRODUCTOS SET PRE_PROD=20 WHERE COD_PROD=1
-- error, por el numero del mes
UPDATE PRODUCTOS SET nom_prod='cambiado' WHERE COD_PROD=1
-- error, incluso el nombre del producto, no se permite actualizar

sp_helptext tr_no_act_precios
go

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
end
go

UPDATE PRODUCTOS SET nom_prod='cambiado' WHERE COD_PROD=1

go
-- ok
select top 1 * from PRODUCTOS
go

-- 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_admin
on PRODUCTOS after DELETE
as
-- 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)=0
begin
raiserror('El usuario: %s, No puede eliminar
Productos',14,1,@usuario)
rollback
end
go

-- crear 1 inicio de sesion del servidor

create login prueba1 with password='123', default_database=bdtriggers
go
-- luego, crearemos un usuario de bd, que relaciones al login
recientemente
-- creado.
use bdtriggers
go
create user prueba1
go
-- luego le otorgamos los permisos a nivel de objeto SELECT y DELETE
sobre la
-- tabla PRODUCTOS al usuario prueba1
grant select, delete on PRODUCTOS to prueba1
go

-- cambiamos de usuario
setuser 'prueba1'
go
select system_user
select * from PRODUCTOS
-- ok
DELETE PRODUCTOS WHERE COD_PROD=1
-- volviendo al usuario Administrador
setuser
select system_user
-- A5_21\Administrator
DELETE PRODUCTOS WHERE COD_PROD=1
go

-- 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_precios
go

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=5

update PRODUCTOS set pre_prod=pre_prod*1.5 where cod_prod in (22,23)
go
select * from PRODUCTOS where cod_cat=5
go

-- Desactivar o Activar Triggers

/* Desactivar:
ALTER TABLE NombreTabla
DISABLE TRIGGER NombreTrigger | ALL

Activar:
ALTER TABLE NombreTabla
ENABLE TRIGGER NombreTrigger | ALL
*/
select * from PRODUCTOS where cod_prod = 2
update PRODUCTOS set pre_prod=pre_prod*1.5 where cod_prod = 2

-- Desactivamos el Trigger
ALTER TABLE PRODUCTOS DISABLE TRIGGER tr_no_act_precios
GO

update PRODUCTOS set pre_prod=pre_prod*1.5 where cod_prod = 2

select * from PRODUCTOS where cod_prod = 2
-- Activar el Trigger
SP_HELPTRIGGER PRODUCTOS
GO
ALTER TABLE PRODUCTOS ENABLE TRIGGER ALL --tr_no_act_precios
GO

TRIGERTS Y BD:
-- TRIGGER DDL
-- ***********
/* sintaxis:

CREATE TRIGGER NomTrigger

ON { DATABASE ó ALL SERVER }
AFTER { Comando_DDL } -- CREATE_TABLE
AS
-- Instrucciones_SQL
-- Criterios
-- Rollback
GO
*/

select eventdata()
go

use northwind
go

-- trigger ddl, que no permite la creacion

-- de nuevas tablas, en la bd activa
create trigger tr_1
on database after create_table
as
select eventdata()
rollback
go

create table tabla1

( col1 int )
go

-- desactivando el trigger ddl: tr_1

disable trigger tr_1 on database
go
-- 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_1

enable trigger tr_1 on database
go

-- modificamos el trigger tr_1, para que

-- almacene el resultado de eventdata() en la
-- tabla audita_ddl
alter trigger tr_1
on database after create_table
as
declare @datos xml
select @datos=eventdata()
rollback
insert into audita_ddl values(@datos)
go

create table historial

(col1 int)
go

select * from audita_ddl

go

create schema produccion

go
create table produccion.prod_term
(col1 int)
go

select * from audita_ddl

go

--select * from audita_ddl

--where datos like 'historial%'
--go

select nom_tabla=
[Link]('(EVENT_INSTANCE/ObjectName)[1]',
'varchar(30)')
from audita_ddl
go

select nom_tabla=convert(varchar(30),
[Link]('data(EVENT_INSTANCE/ObjectName)'))
from audita_ddl
go

-- 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_1
on database after create_table
as
declare @datos xml
select @datos=eventdata()
-- declarar una variable cadena para el nombre
-- del esquema
declare @nom_esquema varchar(20)
select @nom_esquema=
@[Link]('(EVENT_INSTANCE/SchemaName)[1]',
'varchar(20)')
-- si el esquema es Produccion, entonces
-- cancelamos la operación y auditamos
if lower(@nom_esquema)='produccion'
begin
rollback
insert into audita_ddl values(@datos)
end
go
create table prod_anula
( col1 int)
go
-- ok

create table produccion.prod_anulados

(col1 int)
go

select * from audita_ddl

go

/* a nivel de base de datos:

tablas, vistas, procedimentos almacenados,
funciones, user
*/
/* a nivel de servidor
database, login
*/

-- crear un trigger ddl que no permita

-- nuevos inicios de sesion (logins) en
-- el servidor.
create trigger tr_s_login
on all server after create_login
as
select eventdata()
raiserror('No se permite crear logins',14,1)
rollback
go

create login prueba1 with password='123'

go