Clase 3

[RodryGut]

create database empresa go use empresa

create table sucursales(suc_id int, suc_nombre varchar(20)) create table empleados(emp_id int, emp_nombre varchar(20), suc_id int) go

insert into sucursales values(1, 'Centro'),(2, 'Congreso'),(3, 'Caballito'),(4, 'Palermo') insert into empleados values(1, 'Juan',1),(2, 'Jose',2),(3, 'Carlos',2),(4, 'Maria',null) go

select from sucursales select from empleados

truncate table sucursales

-- INNER JOIN -- LISTAR LAS SUCURSALES Y LOS EMPLEADOS QUE EN ELLAS TRABAJAN

select s.suc_nombre as sucursales, e.emp_nombre as empleado from empleados e inner join sucursales s on e.suc_id = s.suc_id

-- variante no ANSI select s.suc_nombre as sucursales, e.emp_nombre as empleado from empleados e, sucursales s

where e.suc_id = s.suc_id

-- producto cartesiano select s.suc_nombre as sucursales, e.emp_nombre as empleado from empleados e, sucursales s

-- variante ANSI select s.suc_nombre as sucursales, e.emp_nombre as empleado from empleados e cross join sucursales s

-- outer join -- listar los empleados que no trabajan en ninguna sucursal -- tabla ppal: empleados select e.emp_nombre as empleado --,s.* from empleados e left outer join sucursales s on s.suc_id = e.suc_id where s.suc_id is null

-- listar las sucursales que no tienen empleados select s.suc_nombre as sucursales --,s. --from empleados e left outer join suacursales s from sucursales s left outer join empleados e on s.suc_id = e.suc_id where e.emp_id is null -- full join select from empleados e full join sucursales s on s.suc_id=e.suc_id

-- multiples join use pubs -- listar el nombre y apellido de los autores que escribieron libros de cocina. Informar --cuando se publicaron y que editorial lo hizo

select a.au_fname +''+ a.au_lname as autor, t.pubdate as [fecha de publicacion], p.pub_name as editorial from authors a inner join titleauthor ta on a.au_id = ta.au_id inner join titles t on ta.title_id = t.title_id inner join publishers p on p.pub_id = t.pub_id

where t. type like '%cook%'

[RodryGut]

use [AdventureWorks] --funciones de agrupacion -- informr la ultima fecha de ingreso de un empleado select max(HireDate) as 'ultima fecha de ingreso' from HumanResources.Employee

-- informar la minima cantidad de horas de vacaciones disponibles de vacaciones de un empleado select min(VacationHours) as 'ultima fecha de ingreso' from HumanResources.Employee where VacationHours >0

-- informar la cantidad de horas promedio disponibles de vacaciones de todos los aemleados select avg(VacationHours) as 'horas disponibles' from HumanResources.Employee

--informar cuando nacio el empleado mas añoso select min(BirthDate) as 'empleado mas añoso' from HumanResources.Employee

--informar cuando nacio el empleada mas añosa select min(BirthDate) as 'empleado mas añoso' from HumanResources.Employee where Gender = 'f'

-- informar la cantidad de bicis de color blanco, rojo o plateado select count(ProductID) as cantidad from Production.Product where Color in('White','red','silver')

-- informar la sumatoria de precio de todos los productos plateados select sum(ListPrice) as total from Production.Product as p where p.Color = 'silver'

-- Todas las funciones agrupadas en una unica query

select max(ListPrice) maximo, min(ListPrice) minimo, avg(ListPrice) promedio, count(ProductID) cantidad, sum(ListPrice) total from Production.Product

-- agrupaciones -- agrupar por color la cantidad de productos que esten pintados. Ordenar de mayor a menor select color, count(ProductID) cantidad from Production.Product where color is not null group by Color order by 2 desc,

-- agrupaciones -- agrupar por color la cantidad de productos que esten pintados. Ordenar de mayor a menor. -- En caso de empate de cantidad, que desempate el color en forma alfabetica select color, count(ProductID) cantidad from Production.Product where color is not null group by Color order by 2 desc,1

-- agrupaciones -- agrupar por color la cantidad de productos que esten pintados. Ordenar de mayor a menor. -- En caso de empate de cantidad, que desempate el color en forma alfabetica. Qaue la cantid -- debe ser mayor o igual a 20 select color, count(ProductID) cantidad from Production.Product where color is not null group by Color having count(ProductID) >=20 order by 2 desc,1

-- agrupaciones -- agrupar por color la cantidad de productos que esten pintados. Ordenar de mayor a menor. -- En caso de empate de cantidad, que desempate el color en forma alfabetica. Qaue la cantid -- debe ser mayor o igual a 20. Detodo lo anterior, traer los 3 primeros registros select top 4 color, count(ProductID) cantidad from Production.Product where color is not null group by Color having count(ProductID) >=20 order by 2 desc,1

[RodryGut]

-- gestion base de datos

create database prueba_editoriles; use prueba_editoriles; CREATE TABLE IF NOT EXISTS editoriales ( id_editorial INT NOT NULL AUTO_INCREMENT PRIMARY KEY, nombre_editorial VARCHAR(50), INDEX (nombre_editorial) )ENGINE=INNODB;

INSERT INTO editoriales (id_editorial, nombre_editorial) VALUES (1, 'Editorial Planeta'), (2, 'Editorial Santillana'), (3, 'Editorial Anaya'), (4, 'Editorial Alfaguara'), (5, 'Editorial SM'), (6, 'Editorial Fondo de Cultura Económica'), (7, 'Editorial Siglo XXI'), (8, 'Editorial Cátedra'), (9, 'Editorial Tecnos'), (10, 'Editorial Ariel');

SELECT FROM editoriales; select from libros; SELECT * FROM empleados;

CREATE TABLE IF NOT EXISTS empleados ( id_empleado INT NOT NULL AUTO_INCREMENT PRIMARY KEY, nombre_empleado VARCHAR(50), id_editorial int, INDEX (nombre_empleado, id_editorial) )ENGINE=INNODB;

INSERT INTO empleados (id_empleado, nombre_empleado, id_editorial) VALUES (1, 'Juan Pérez', 1), (2, 'María Rodríguez', 1), (3, 'Pedro López', 2), (4, 'Ana Martínez', 2), (5, 'Carlos García', 3), (6, 'Laura González', 3), (7, 'Luis Fernández', 4), (8, 'Elena Sánchez', 4), (9, 'Javier Ruiz', 5), (10, 'Sofía Torres', 5);

CREATE TABLE IF NOT EXISTS libros ( id_libro INT NOT NULL AUTO_INCREMENT PRIMARY KEY, titulo_libro VARCHAR(50), id_editorial int, INDEX (titulo_libro, id_editorial) )ENGINE=INNODB;

INSERT INTO libros (id_libro, titulo_libro, id_editorial) VALUES (1, 'Cien años de soledad', 1), (2, 'Don Quijote de la Mancha', 1), (3, 'La sombra del viento', 2), (4, 'Rayuela', 2), (5, 'Crónica de una muerte anunciada', 3), (6, 'Los detectives salvajes', 3), (7, 'Ficciones', 4), (8, 'La casa de los espíritus', 4), (9, 'La ciudad y los perros', 5), (10, 'Cien años de soledad', 5);

-- 1. Eliminar una editorial: Si se elimina una editorial de la tabla editoriales, -- ¿qué sucede con los libros asociados? Escriba una consulta SQL que elimine una editorial y sus libros relacionados. DELETE FROM editoriales WHERE id_editorial = 123; -- Reemplazar con el ID de la editorial que deseas eliminar

DELETE FROM libros WHERE id_editorial = 123; -- Reemplazar con el mismo ID de la editorial

-- 2. Actualizar el nombre de una editorial: Si se actualiza el nombre de una editorial en la tabla editoriales, -- ¿qué sucede con los libros relacionados?

-- 3. Eliminar un empleado: Si se elimina un empleado de la tabla empleados, -- ¿qué sucede con los libros relacionados con esa editorial?

-- 4. Actualizar el nombre de un empleado: Si se actualiza el nombre de un empleado en la tabla empleados, -- ¿qué sucede con los libros relacionados con esa editorial?

-- 5. Eliminar un libro: Si se elimina un libro de la tabla libros, ¿qué sucede con la relación con la editorial?

-- 6. Cambiar la editorial de un libro: Si se cambia la editorial a la que está asociado un libro en la tabla libros, -- ¿qué sucede con la relación con la editorial anterior?

-- 7. Eliminar una editorial con empleados: Si se intenta eliminar una editorial que tiene empleados asociados, ¿qué sucede?

-- 8. Eliminar un empleado con libros: Si se intenta eliminar un empleado que tiene libros asociados, ¿qué sucede?

-- 9. Eliminar una editorial y sus empleados: ¿Cómo se eliminaría una editorial y todos sus empleados?

-- 10. Eliminar una editorial y transferir sus empleados a otra editorial: -- ¿Cómo se eliminaría una editorial y reasignaría a sus empleados a otra editorial?

[RodryGut]

-- >>>>>>>>>>>>>>>>>>>>>>>>administracion>>>>>>>>>>>>>>>>>>>< use [AdventureWorks]

--16. Mostrar las bicicletas de monta�a que cuestan entre $1000 y $1200 select * from Production.Product where ProductNumber like 'bk%' and ListPrice between 1000 and 1200

--31. Mostrar los productos y la cantidad total vendida de cada uno de ellos select ProductID as Producto, sum(OrderQty) as 'Cantidad vendida' from Sales.SalesOrderDetail group by ProductID order by 2 desc

-- variante con join select Name as Producto, sum(OrderQty) as 'Cantidad vendida' from Sales.SalesOrderDetail sod inner join Production.Product p on p.ProductID = sod.ProductID group by p.Name order by 2 desc

--33. Mostrar todas las facturas realizadas y el total facturado de cada una de ellas ordenado por numero de factura. select ProductID as Producto, sum(unitPrice * OrderQty) as 'Total Facturado' from Sales.SalesOrderDetail group by ProductID order by 2 desc

--34. Mostrar todas las facturas realizadas y el total facturado de cada una de ellas ordenado por nro de factura pero solo de aquellas ordenes superen un total de $10.000 select SalesOrderDetailID as [Nro de Factura], sum(unitPrice OrderQty) as 'Total Facturado' from Sales.SalesOrderDetail group by SalesOrderDetailID having sum(unitPrice OrderQty) > 10000 order by 1

--34BIS. Mostrar todas las facturas realizadas y el total facturado de cada una de ellas ordenado por nro de factura pero solo de aquellas ordenes superen un total de $10.000 select p.Name as Producto, p.ProductID as Codigo, sum(UnitPrice OrderQty) 'Total Facturado', DATEDIFF(WEEK, p.SellStartDate, p.SellEndDate) as 'Tiempo de Venta en Semanas' from Sales.SalesOrderDetail sod inner join Production.Product p on p.ProductID = sod.ProductID where DATEDIFF(WEEK, p.SellStartDate, p.SellEndDate) is not null group by p.Name, p.ProductID,DATEDIFF(WEEK, p.SellStartDate, p.SellEndDate) having sum(UnitPrice OrderQty) > 10000 order by 4 desc

--35. Mostrar la cantidad de facturas que vendieron mas de 20 unidades select ProductID as Producto, sum(OrderQty) as 'Cantidad vendida' from Sales.SalesOrderDetail group by ProductID having sum(OrderQty) > 20 order by 2 desc

--37. Mostrar todos los codigos de categorias existentes junto con la cantidad de productos y el precio de lista promediopor cada uno de aquellos productos que cuestan mas de $70 y el precio promedio es mayor a $300 select ProductSubcategoryID [Categoria de Producto], count(ProductID) Cantidad, avg(ListPrice) Promedio from Production.Product where ListPrice > 70 group by ProductSubcategoryID having avg(ListPrice) > 300 order by 1

-- JOINS --39. Mostrar los empleados que tambi�n son vendedores select e.* from HumanResources.Employee e inner join Sales.SalesPerson sp on e.BusinessEntityID = sp.BusinessEntityID

--40. Mostrar los empleados ordenados alfabeticamente por apellido y por nombre select p.FirstName + ' ' + p.LastName as Empleado
,e.* from HumanResources.Employee e inner join Person.Person p on e.BusinessEntityID = p.BusinessEntityID order by 1

--42. Mostrar los productos que sean ruedas select ps.Name as Producto ,p.* from Production.Product p inner join Production.ProductSubcategory ps on ps.ProductSubcategoryID = p.ProductSubcategoryID where ps.Name = 'Wheels' order by 1

--43. Mostrar los nombres de los productos que no son bicicletas select ps.Name as Producto ,p.* from Production.Product p inner join Production.ProductSubcategory ps on ps.ProductSubcategoryID = p.ProductSubcategoryID where ps.Name not like '%bike%' order by 1

-- 44. Mostrar los precios de venta de aquellos productos donde el precio de venta sea inferior al precio de lista recomendado para ese producto ordenados por nombre de producto select p.Name as Producto ,p.ListPrice as [Precio de Lista] ,sod.UnitPrice as [Precio Unitario] from Production.Product p inner join Sales.SalesOrderDetail sod on p.ProductID = sod.ProductID where sod.UnitPrice < p.ListPrice order by 1

--49. Mostrar los vendedores (nombre y apellido) y el territorio asignado a c/u(identificador y nombre de territorio). En los casos en que un territorio no tiene vendedores mostrar igual los datos del territorio unicamente sin datos de vendedores select p.FirstName + ' ' + p.LastName as Vendedor ,st.TerritoryID as identificador ,st.Name as Territorio from Sales.SalesTerritory st left join Sales.SalesPerson sp on st.TerritoryID = sp.TerritoryID inner join Person.Person p on p.BusinessEntityID = sp.BusinessEntityID

--49 BIS. Mostrar los vendedores (nombre y apellido) y el territorio asignado a c/u(identificador y nombre de territorio). En los casos en que un vendedor no tenga territorio mostrar igual los datos del vendedor unicamente sin datos de territorios -- tabla ppal: SalesPerson -- tabla secundaria: SalesTerritory select st.TerritoryID 'Identificador de Territorio', st.Name as 'Territorio de Venta', p.FirstName + ' ' + p.LastName as Vendedor from Person.Person p inner join Sales.SalesPerson sp on p.BusinessEntityID = sp.BusinessEntityID left join Sales.SalesTerritory st on st.TerritoryID = sp.TerritoryID where st.TerritoryID is null

--50. Mostrar el producto cartesiano ente la tabla de vendedores cuyo numero de identificacion de negocio --sea 280 y el territorio de venta sea el de francia select sp.BusinessEntityID as [numero de identificacion] ,st.Name as 'Territorio de venta' ,sp.* from Sales.SalesPerson sp cross join Sales.SalesTerritory st where st.Name = 'France' and sp.BusinessEntityID = 280

--50 BIS. Mostrar el producto cartesiano ente la tabla de vendedores cuyo numero de identificacion de negocio sea 280 y el territorio de venta sea el de francia select sp.BusinessEntityID as 'Numero de Identificacion', st.Name as 'Territorio de Venta', p.FirstName + ' ' + p.LastName as Vendedor from Sales.SalesPerson sp inner join Person.Person p on p.BusinessEntityID = sp.BusinessEntityID cross join Sales.SalesTerritory st where st.Name = 'France' and sp.BusinessEntityID = 280

use pubs -- Listar los libros que nunca fueron vendidos --tabla ppal: titles --tabla secundaria: sales select t.title as libro --,s.* from titles t left join sales s on t.title_id = s.title_id where s.stor_id is null

-- 51.listar todos las productos cuyo precio sea inferior al precio promedio de todos los productos use AdventureWorks go

SELECT * FROM Production.product WHERE ListPrice < (SELECT AVG(ListPrice) FROM Production.Product) ORDER BY ListPrice DESC

-- 53. mostrar el o los codigos del producto mas caro

SELECT * FROM Production.Product WHERE ListPrice < 3578.27-- (SELECT max(ListPrice) FROM Production.Product)

-- 54. mostrar el producto mas barato de cada subcategoría. mostrar subcaterogia, codigo de producto y el precio de lista mas barato ordenado por subcategoria

SELECT p1.ProductSubcategoryID as 'Codigo de subcategoria', p1.ProductID 'Codigo de Productos', p1.ListPrice 'precui de Lista' FROM Production.Product p1 WHERE exists ( SELECT p2.ProductSubcategoryID, MIN(p2.ListPrice) FROM Production.Product )

-- 67.listar el nombre de los productos, el nombre de la subcategoria a la que pertenece junto a su categoría de precio. La categoría de precio se calcula de la siguiente manera. --si el precio está entre 0 y 1000 la categoría es económica. --si la categoría está entre 1000 y 2000, normal --y si su valor es mayor a 2000 la categoría es cara. use [AdventureWorks]

select          p.Name as Producto,
                psc Name as Subcategoria,
                p.List_Price as Precio,
                (case
                when ListPrice < 1000 then 'economica'
                when ListPrice between 1000 and 2000 then 'normal'
                when ListPrice > 2000 then 'cara'
                else 'Sin categorizar'
                and) as 'Categoria de Precio'
from            Production.Product p
inner join      Production.ProductSubcategory psc
on              p.ProductSubcategoryID = psc.ProductSubcategoryID

-- 68.tomando el ejercicio anterior, mostrar unicamente aquellos productos cuya categoria sea "economica" select * from ( select p.Name as Producto, psc Name as Subcategoria, p.List_Price as Precio, (case when ListPrice < 1000 then 'economica' when ListPrice between 1000 and 2000 then 'normal' when ListPrice > 2000 then 'cara' else 'Sin categorizar' and) as 'Categoria de Precio' from Production.Product p inner join Production.ProductSubcategory psc on p.ProductSubcategoryID = psc.ProductSubcategoryID ) as misubconsulta where misubconsulta.categoria = 'economia' order by misubconsulta.Precio desc

[RodryGut]

gestion>>>>>>>>>>>>>> create database prueba_editoriles; use prueba_editoriles; CREATE TABLE IF NOT EXISTS editoriales ( id_editorial INT NOT NULL AUTO_INCREMENT PRIMARY KEY, nombre_editorial VARCHAR(50), INDEX (nombre_editorial) )ENGINE=INNODB;

INSERT INTO editoriales (id_editorial, nombre_editorial) VALUES (1, 'Editorial Planeta'), (2, 'Editorial Santillana'), (3, 'Editorial Anaya'), (4, 'Editorial Alfaguara'), (5, 'Editorial SM'), (6, 'Editorial Fondo de Cultura Económica'), (7, 'Editorial Siglo XXI'), (8, 'Editorial Cátedra'), (9, 'Editorial Tecnos'), (10, 'Editorial Ariel');

SELECT FROM editoriales; select from libros; SELECT * FROM empleados;

CREATE TABLE IF NOT EXISTS empleados ( id_empleado INT NOT NULL AUTO_INCREMENT PRIMARY KEY, nombre_empleado VARCHAR(50), id_editorial int, INDEX (nombre_empleado, id_editorial) )ENGINE=INNODB;

INSERT INTO empleados (id_empleado, nombre_empleado, id_editorial) VALUES (1, 'Juan Pérez', 1), (2, 'María Rodríguez', 1), (3, 'Pedro López', 2), (4, 'Ana Martínez', 2), (5, 'Carlos García', 3), (6, 'Laura González', 3), (7, 'Luis Fernández', 4), (8, 'Elena Sánchez', 4), (9, 'Javier Ruiz', 5), (10, 'Sofía Torres', 5);

CREATE TABLE IF NOT EXISTS libros ( id_libro INT NOT NULL AUTO_INCREMENT PRIMARY KEY, titulo_libro VARCHAR(50), id_editorial int, INDEX (titulo_libro, id_editorial) )ENGINE=INNODB;

INSERT INTO libros (id_libro, titulo_libro, id_editorial) VALUES (1, 'Cien años de soledad', 1), (2, 'Don Quijote de la Mancha', 1), (3, 'La sombra del viento', 2), (4, 'Rayuela', 2), (5, 'Crónica de una muerte anunciada', 3), (6, 'Los detectives salvajes', 3), (7, 'Ficciones', 4), (8, 'La casa de los espíritus', 4), (9, 'La ciudad y los perros', 5), (10, 'Cien años de soledad', 5);

-- 1. Eliminar una editorial: Si se elimina una editorial de la tabla editoriales, -- ¿qué sucede con los libros asociados? Escriba una consulta SQL que elimine una editorial y sus libros relacionados. DELETE FROM editoriales WHERE id_editorial = 123; -- Reemplazar con el ID de la editorial que deseas eliminar

DELETE FROM libros WHERE id_editorial = 123; -- Reemplazar con el mismo ID de la editorial

-- 2. Actualizar el nombre de una editorial: Si se actualiza el nombre de una editorial en la tabla editoriales, -- ¿qué sucede con los libros relacionados?

-- 3. Eliminar un empleado: Si se elimina un empleado de la tabla empleados, -- ¿qué sucede con los libros relacionados con esa editorial? -- Supongamos que tienes una tabla llamada empleados con una columna llamada id para identificar a los empleados, y una tabla llamada libros que tiene una columna llamada editorial_id para identificar la editorial a la que pertenece cada libro.

-- Eliminar un empleado específico por su ID: DELETE FROM empleados WHERE id = 1;

-- Si deseas eliminar todos los empleados de una editorial específica, puedes usar una consulta que involucre a ambas tablas: DELETE empleados, libros FROM empleados LEFT JOIN libros ON empleados.id = libros.editorial_id WHERE empleados.editorial_id = 1;

-- 4. Actualizar el nombre de un empleado: Si se actualiza el nombre de un empleado en la tabla empleados, -- ¿qué sucede con los libros relacionados con esa editorial?

UPDATE empleados SET nombre = 'Nuevo nombre' WHERE id = 1;

-- 5. Eliminar un libro: Si se elimina un libro de la tabla libros, ¿qué sucede con la relación con la editorial? -- Supongamos que deseas eliminar un libro con ID 1: DELETE FROM libros WHERE id = 1;

-- 6. Cambiar la editorial de un libro: Si se cambia la editorial a la que está asociado un libro en la tabla libros, -- ¿qué sucede con la relación con la editorial anterior? -- Supongamos que deseas cambiar la editorial del libro con ID 1 a una nueva editorial con ID 2: UPDATE libros SET editorial_id = 2 WHERE id = 1;

-- 7. Eliminar una editorial con empleados: Si se intenta eliminar una editorial que tiene empleados asociados, ¿qué sucede? -- Supongamos que deseas eliminar una editorial con ID 1, pero tiene empleados asociados: DELETE FROM editoriales WHERE id = 1;

-- Si hay empleados asociados, esta operación podría fallar dependiendo de las restricciones de integridad referencial configuradas.

-- 8. Eliminar un empleado con libros: Si se intenta eliminar un empleado que tiene libros asociados, ¿qué sucede? -- Supongamos que deseas eliminar un empleado con ID 1, pero tiene libros asociados: DELETE FROM empleados WHERE id = 1; -- Si hay libros asociados, esta operación podría fallar dependiendo de las restricciones de integridad referencial configuradas.

-- 9. Eliminar una editorial y sus empleados: ¿Cómo se eliminaría una editorial y todos sus empleados?

DELETE FROM editoriales WHERE id = 1; -- 10. Eliminar una editorial y transferir sus empleados a otra editorial: -- ¿Cómo se eliminaría una editorial y reasignaría a sus empleados a otra editorial? -- Supongamos que deseas eliminar la editorial con ID 1 y transferir a sus empleados a la editorial con ID 2:

-- Paso 1: Actualizar los empleados UPDATE empleados SET editorial_id = 2 WHERE editorial_id = 1;

-- Paso 2: Eliminar la editorial DELETE FROM editoriales WHERE id = 1;

[RodryGut]

-- Clase 12 parte 1-- Variables

/ declaracion, inicializacion e impresion / declare @nombre as nvarchar(20) set @nombre = 'Juan' --select @nombre /muestra como resultado de query/ print @nombre/muestra como mensaje/

/ condicional if / declare @nombre as varchar(20) set @nombre = 'Juan' if (@nombre = 'Juan') begin print 'Es Juan' end else begin print 'no es juan' end

/ bucle while / declare @valor as int set @valor = 1 while(@valor<=10) begin print @valor set @valor=@valor+1 end

/ variable que almacena query / use pubs go

declare @maximo as int declare @minimo as int

select @maximo = max(price),/ por ser mezcla entre query y variable debe ir select NO set/ @minimo = min (price) from titles

select @maximo select @minimo

/ variables y funciones del sistema / select @@servername /variables del sistema/

select @@max_connections

select getdate()/funciones del sistema/

/ Ejecuci�n de sentencias SQL: � Din�micos � Batch � Transacci�n � Scripts /

--Din�micos: son generadas durante la ejecuci�n de un script. --por ejemplo se puede generar un store procedure con variables para construir una sentencia SELECT que incorpore esas variables

DECLARE @tabla varchar(20), @bd varchar(20) SET @tabla = 'authors' SET @bd = 'pubs' EXECUTE ('USE '+ @bd + ' SELECT * FROM ' + @tabla )

--Ejemplo:

DECLARE @tabla varchar(20), @bd varchar(20),@campo varchar(20),@funcion varchar (20) set @funcion='avg(price)' set @campo='type' SET @tabla = 'sales' SET @bd = 'pubs'

if(@tabla = 'titles') begin EXECUTE ('USE '+ @bd + ' SELECT '+ @campo + @funcion +'promedio ' +' FROM ' + @tabla+' GROUP BY '+@campo ) end

else begin set @funcion='sum(qty)' set @campo='stor_id' SET @tabla = 'sales' EXECUTE ('USE '+ @bd + ' SELECT '+ @campo+' tienda, '+@funcion +'ventas ' +' FROM ' + @tabla+' GROUP BY '+@campo ) end

--Batch: ejecuci�n de varias sentencias juntas. --mejoran el performance de SQL Server debido a que compila y ejecuta todo junto

SELECT MAX(price) AS 'M�ximo precio' FROM titles PRINT '' SELECT MIN(price) AS 'Menor precio' FROM titles PRINT '' SELECT AVG(price) AS 'Precio promedio' FROM titles GO

--Transacciones: se ejecutan como un bloque --si alguna sentencia falla, no se ejecuta nada del bloque

BEGIN TRANSACTION update clientes set categoria=categoria+1 where nombre='carlos' COMMIT TRANSACTION --rollback transacction deshace la operacion

--Clausulas try catch:permite manejar de modo seguro transacciones

BEGIN TRY PRINT 'Continuo OK'; END TRY BEGIN CATCH RAISERROR('mensaje de error',16,1) --Eleva un error a la aplicaci�n o batch que lo llamo --RAISERROR ( { msg_id | msg_str } { , severidad , estado } ] --msg_id: N�mero de error en la tabla sysmessages PRINT 'fallo el proceso' END CATCH

--try catch con transacciones

create database dml go use dml

create table clientes( codigo int identity(1,1), nombre varchar(40) not null, dni int not null unique, sexo char(1) not null default 'F', categoria int not null check (categoria between 1 and 10)

                    );

select * from clientes

insert into clientes (nombre,dni,sexo,categoria) values ('carlos',25765981,'M',6)

insert into clientes (nombre,dni,sexo,categoria) values ('jose',24578965,'M',3)

insert into clientes (nombre,dni,categoria) values ('maria',19653827,8)

insert into clientes (nombre,dni,categoria) values ('mariana',20123456,5)

BEGIN TRY BEGIN TRAN update clientes set categoria=categoria+1 where nombre='carlos' COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN PRINT 'fallo el proceso' END CATCH

--Funciones --funciones escalares

-- devolver todos los libros cuyo precio sea mayor al promedio

use pubs go

SELECT * FROM titles WHERE price > (SELECT avg(price) FROM titles)

CREATE FUNCTION promedio() returns money as BEGIN declare @promedio money select @promedio=avg(price) from titles return @promedio END

select * from titles where price >dbo.promedio()

drop function promedio

--funcion escalar con pasaje por parametros

CREATE FUNCTION promedio_2(@categoria varchar(30)) returns money as BEGIN

    declare @promedio money
    select @promedio=avg(price) from titles where type=@categoria
    return @promedio

END

select * from titles where price > dbo.promedio_2(type)

drop function promedio_2

-- funciones de tabla en linea

CREATE FUNCTION autoresLibros(@cat varchar(30)) returns table as return (SELECT a.au_id, a.au_fname, a.au_lname, a.state, t.title_id, t.type, t.price, t.pub_id

        FROM        authors a
        INNER JOIN  titleauthor ta
        ON          a.au_id=ta.au_id
        INNER JOIN  titles t
        ON          ta.title_id=t.title_id)

SELECT * FROM autoresLibros('business')

drop function autoresLibros

--variables de tipo tabla

declare @t table(codigo int,nombre varchar(200))

insert into @t select 1,'juan'

insert into @t select 2,'pepe'

select * from @t

--funciones de multisentencia

CREATE FUNCTION fnMultisentencia() returns @t table(codigo int,nombre varchar(200)) as BEGIN

    insert into @t
    select 1,'juan'

    insert into @t
    select 2,'pepe'

    insert into @t
    select 3,'martin'

    return

END

select * from dbo.fnMultisentencia()

drop function fnMultisentencia

--otro ejemplo: totaliza y promedia el precio de una determinada categoria

CREATE FUNCTION fnMultisentencia2(@cat varchar (30)) returns @t table(titulo varchar(200), precio money)

BEGIN 
       insert into @t
       select title, price
       from titles 
       where type = @cat

       insert into @t
       select 'Promedio', avg(precio) from @t

       insert into @t 
       select 'Total', sum(precio) from @t
       return          
END 

select * from dbo.fnMultisentencia2('business')

drop function fnMultisentencia2

--Procedimientos Almacenados

CREATE PROCEDURE listarLibros as BEGIN select * from titles END

exec listarLibros

drop procedure listarLibros

--otro use AdventureWorks2008R2 go

--El siguiente ejemplo muestra como se puede crear un procedimiento que devuelve un --conjunto de registros de todos los productos que llevan m�s de un d�a de fabricaci�n. CREATE PROC Production.LongLeadProducts AS SELECT Name, ProductNumber, DaysToManufacture FROM Production.Product WHERE DaysToManufacture >= 1 ORDER BY DaysToManufacture DESC, Name GO

--procedimiento con parametros

--El siguiente c�digo agrega un parametro @MinimumLength al procedimiento --LongLeadProducts Esto permite que la c�usula WHERE sea m�s flexible, permitiendo a la --aplicaci�n llamante, definir el tiempo m�nimo de fabricaci�n apropiado.

ALTER PROC Production.LongLeadProducts @MinimumLength int = 1 -- valor por defecto AS IF (@MinimumLength < 0) -- validaci�n BEGIN RAISERROR('Invalid lead time.', 14, 1) RETURN END

SELECT Name, ProductNumber, DaysToManufacture FROM Production.Product WHERE DaysToManufacture >= @MinimumLength ORDER BY DaysToManufacture DESC, Name

/Metadatos/

/para ver el diccionario de datos de la base/ select * from sys.tables

/muestra todos los procedimientos generados en la BD/ select * from sys.procedures

/muestra las bases/ select * from sys.databases

/muestra todos los mensajes de la base/ select * from sys.messages

/muestra todos los objetos de la base/ select * from sys.objects

[RodryGut]

-->>>>>>>>>>>>>>>>>><<Clase 12 -- 69:aumentar un 20% el precio de lista de todos los productos
UPDATE Production.Product SET ListPrice = ListPrice * 1.2

    use [AdventureWorks]            
    go

-- 70:aumentar un 20% el precio de lista de los productos del proveedor 1540 UPDATE Production.Product SET ListPrice = ListPrice * 1.2 FROM Production.Product p INNER JOIN Purchasing.ProductVendor v ON p.ProductID = v.ProductID WHERE v.BusinessEntityID = 1540

SELECT ListPrice as Precio FROM Production.Product p INNER JOIN Purchasing.ProductVendor v ON p.ProductID = v.ProductID WHERE v.BusinessEntityID = 1540

-- 71:agregar un dia de vacaciones a los 10 empleados con mayor antiguedad. UPDATE HumanResources.Employee SET VacationHours = VacationHours + 24 FROM (SELECT TOP 10 BusinessEntityID FROM HumanResources.Employee ORDER BY HireDate ASC) AS miSubconsulta WHERE HumanResources.Employee.BusinessEntityID = miSubconsulta.BusinessEntityID;

--verificacion SELECT TOP 10 VacationHours,* FROM HumanResources.Employee e ORDER BY HireDate asc

-- 72: eliminar los detalles de compra (purchaseorderdetail) cuyas fechas de -- VENCIMIENTO PERTENEZCAN AL TERCER TRIMESTRE DEL A�O 2006 DELETE
FROM Purchasing.PurchaseOrderDetail WHERE MONTH(DueDate) between 7 and 9 and YEAR(DueDate)=2006;

-- 73:quitar registros de la tabla salespersonquotahistory cuando las ventas del a�o hasta la fecha --almacenadas en la tabla salesperson supere el valor de 2500000

DELETE FROM Sales.SalesPersonQuotaHistory FROM Sales.SalesPersonQuotaHistory AS spqh INNER JOIN Sales.SalesPerson AS sp ON spqh.BusinessEntityID = sp.BusinessEntityID WHERE sp.SalesYTD > 2500000.00;

-- bulk copy

-- 74: clonar estructura y datos de los campos nombre ,color y precio de lista de la tabla production.product en una tabla llamada productos

SELECT Color,Name,ListPrice INTO productos FROM Production.Product

SELECT * from productos

-- 75: clonar solo estructura de los campos identificador ,nombre y apellido de la tabla person.person en una tabla llamada personas

SELECT BusinessEntityID,FirstName,LastName INTO personas FROM Person.Person WHERE 1=2

--drop table dbo.personas --drop table dbo.productos

-- 76:insertar un producto dentro de la tabla productos.tener en cuenta los siguientes --datos: el color de producto debe ser rojo, el nombre debe ser "bicicleta mountain bike" y el precio de lista debe ser de 4000 pesos.

INSERT INTO productos(Color,Name,ListPrice) VALUES ('Rojo','Bicicleta Mountain Bike',4000)

select * from productos

-- 77: copiar los registros de la tabla person.person a la tabla personas cuyo identificador este entre 100 y 200

INSERT INTO personas SELECT BusinessEntityID,FirstName,LastName FROM Person.Person WHERE BusinessEntityID BETWEEN 100 AND 200

select * from personas

-- 78: aumentar en un 15% el precio de los pedales de bicicleta

UPDATE productos SET ListPrice=ListPrice*1.15 WHERE name like'%pedal%'

-- verificacion
SELECT * FROM productos WHERE name like'%pedal%'

-- 79: eliminar de las personas cuyo nombre empiecen con la letra m

DELETE FROM personas WHERE firstname like 'm%'

--verificacion select * FROM personas WHERE FirstName like 'm%'

-- 80: borrar todo el contenido de la tabla productos

DELETE
FROM productos

--verificacion SELECT * FROM productos

-- 81: borrar todo el contenido de la tabla personas sin utilizar la instrucci�n delete.

TRUNCATE TABLE personas

drop table productos

-- Variables

/ declaracion, inicializacion e impresion / declare @nombre as nvarchar(20) set @nombre = 'Carla' --select @nombre /muestra como resultado de query/ print @nombre/muestra como mensaje/

/ condicional if / declare @nombre as nvarchar(20) set @nombre = 'Maria' if (@nombre = 'Juan') begin print 'Es Juan' end else begin print 'no es juan' end

/ bucle while / declare @valor as int set @valor = 1 while(@valor<=10) begin print @valor set @valor=@valor+1 end

/ variable que almacena query / use pubs go

declare @maximo as int declare @minimo as int

select @maximo = max(price),/ por ser mezcla entre query y variable debe ir select NO set/ @minimo = min (price) from titles

select @maximo select @minimo

/ variables y funciones del sistema / select @@servername /variables del sistema/

select @@max_connections

select getdate()/funciones del sistema/

/ Ejecuci�n de sentencias SQL: � Din�micos � Batch � Transacci�n � Scripts /

--Din�micos: son generadas durante la ejecuci�n de un script. --por ejemplo se puede generar un store procedure con variables para construir una sentencia SELECT que incorpore esas variables

DECLARE @tabla varchar(20), @bd varchar(20) SET @tabla = 'authors' SET @bd = 'pubs' EXECUTE ('USE '+ @bd + ' SELECT * FROM ' + @tabla )

--Ejemplo:

DECLARE @tabla varchar(20), @bd varchar(20),@campo varchar(20),@funcion varchar (20) set @funcion='avg(price)' set @campo='type' SET @tabla = 'sales' SET @bd = 'pubs'

if(@tabla = 'titles') begin EXECUTE ('USE '+ @bd + ' SELECT '+ @campo + @funcion +'promedio ' +' FROM ' + @tabla+' GROUP BY '+@campo ) end

else begin set @funcion='sum(qty)' set @campo='stor_id' SET @tabla = 'sales' EXECUTE ('USE '+ @bd + ' SELECT '+ @campo+' tienda, '+@funcion +'ventas ' +' FROM ' + @tabla+' GROUP BY '+@campo ) end

--Batch: ejecuci�n de varias sentencias juntas. --mejoran el performance de SQL Server debido a que compila y ejecuta todo junto

SELECT MAX(price) AS 'M�ximo precio' FROM titles PRINT '' SELECT MIN(price) AS 'Menor precio' FROM titles PRINT '' SELECT AVG(price) AS 'Precio promedio' FROM titles GO

--Transacciones: se ejecutan como un bloque --si alguna sentencia falla, no se ejecuta nada del bloque

BEGIN TRAN update clientes set categoria=categoria+1 where nombre='carlos' COMMIT TRANSACTION --rollback transacction deshace la operacion

--Clausulas try catch:permite manejar de modo seguro transacciones

BEGIN TRY PRINT 'Continuo OK'; END TRY BEGIN CATCH RAISERROR('mensaje de error',16,1) --Eleva un error a la aplicaci�n o batch que lo llamo --RAISERROR ( { msg_id | msg_str } { , severidad , estado } ] --msg_id: N�mero de error en la tabla sysmessages PRINT 'fallo el proceso' END CATCH

--try catch con transacciones

create database dml go use dml

create table clientes( codigo int identity(1,1), nombre varchar(40) not null, dni int not null unique, sexo char(1) not null default 'F', categoria int not null check (categoria between 1 and 10)

                    );

select * from clientes

insert into clientes (nombre,dni,sexo,categoria) values ('carlos',25765981,'M',6)

insert into clientes (nombre,dni,sexo,categoria) values ('jose',24578965,'M',3)

insert into clientes (nombre,dni,categoria) values ('maria',19653827,8)

insert into clientes (nombre,dni,categoria) values ('mariana',20123456,5)

BEGIN TRY BEGIN TRAN update clientes set categoria=categoria+1 where nombre='carlos' COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN PRINT 'fallo el proceso' END CATCH

select * from clientes

--Funciones --funciones escalares

-- devolver todos los libros cuyo precio sea mayor al promedio

use pubs go

SELECT * FROM titles WHERE price > (SELECT avg(price) FROM titles)

CREATE FUNCTION promedio() returns money as BEGIN declare @promedio money select @promedio=avg(price) from titles return @promedio END

select * from titles where price >dbo.promedio()

drop function promedio

--funcion escalar con pasaje por parametros

CREATE FUNCTION promedio_2(@categoria varchar(30)) returns money as BEGIN

    declare @promedio money
    select @promedio=avg(price) from titles where type=@categoria
    return @promedio

END

select * from titles where price > dbo.promedio_2(type)

drop function promedio_2

-- funciones de tabla en linea

alter FUNCTION autoresLibros(@cat varchar(30)) returns table as return (SELECT a.au_id, a.au_fname, a.au_lname, a.state, t.title_id, t.type, t.price, t.pub_id

        FROM        authors a
        INNER JOIN  titleauthor ta
        ON          a.au_id=ta.au_id
        INNER JOIN  titles t
        ON          ta.title_id=t.title_id
        WHERE       t.type = @cat)

SELECT * FROM autoresLibros('business')

drop function autoresLibros

--variables de tipo tabla

declare @t table(codigo int,nombre varchar(200))

insert into @t select 1,'juan'

insert into @t select 2,'pepe'

select * from @t

--funciones de multisentencia

CREATE FUNCTION fnMultisentencia() returns @t table(codigo int,nombre varchar(200)) as BEGIN

    insert into @t
    select 1,'juan'

    insert into @t
    select 2,'pepe'

    insert into @t
    select 3,'martin'

    return

END

select * from dbo.fnMultisentencia()

drop function fnMultisentencia

--otro ejemplo: totaliza y promedia el precio de una determinada categoria

CREATE FUNCTION fnMultisentencia2(@cat varchar (30)) returns @t table(titulo varchar(200), precio money)

BEGIN 
       insert into @t
       select title, price
       from titles 
       where type = @cat

       insert into @t
       select 'Promedio', avg(precio) from @t

       insert into @t 
       select 'Total', sum(precio) from @t
       return          
END 

select * from dbo.fnMultisentencia2('business')

drop function fnMultisentencia2

--Procedimientos Almacenados

CREATE PROCEDURE listarLibros as BEGIN select * from titles END

exec listarLibros

drop procedure listarLibros

--otro use AdventureWorks2008R2 go

--El siguiente ejemplo muestra como se puede crear un procedimiento que devuelve un --conjunto de registros de todos los productos que llevan m�s de un d�a de fabricaci�n. CREATE PROC Production.LongLeadProducts AS SELECT Name, ProductNumber, DaysToManufacture FROM Production.Product WHERE DaysToManufacture >= 1 ORDER BY DaysToManufacture DESC, Name GO

--procedimiento con parametros

--El siguiente c�digo agrega un parametro @MinimumLength al procedimiento --LongLeadProducts Esto permite que la c�usula WHERE sea m�s flexible, permitiendo a la --aplicaci�n llamante, definir el tiempo m�nimo de fabricaci�n apropiado.

ALTER PROC Production.LongLeadProducts @MinimumLength int = 1 -- valor por defecto AS IF (@MinimumLength < 0) -- validaci�n BEGIN RAISERROR('Invalid lead time.', 14, 1) RETURN END

SELECT Name, ProductNumber, DaysToManufacture FROM Production.Product WHERE DaysToManufacture >= @MinimumLength ORDER BY DaysToManufacture DESC, Name

/Metadatos/

/para ver el diccionario de datos de la base/ select * from sys.tables

/muestra todos los procedimientos generados en la BD/ select * from sys.procedures

/muestra las bases/ select * from sys.databases

/muestra todos los mensajes de la base/ select * from sys.messages

/muestra todos los objetos de la base/ select * from sys.objects