· 6 years ago · Nov 30, 2019, 09:32 PM
1use AdventureWorks2016CTP3
2go
3
4SELECT ProductID, NAME 'Nombre Producto' , MakeFlag 'FLAG' , ListPrice 'PRECIO'
5FROM Production.Product
6WHERE ListPrice > 0 AND ListPrice < 40
7ORDER BY ListPrice DESC
8
9SELECT ProductID, NAME 'Nombre Producto' , MakeFlag 'FLAG' , ListPrice 'PRECIO' , WeightUnitMeasureCode 'CODGIO PESO'
10FROM Production.Product
11WHERE (WeightUnitMeasureCode ='G'
12 OR WeightUnitMeasureCode ='LB') AND (ProductID = 795 OR ProductID = 818)
13
14SELECT P.Productid, P.ListPrice, P.COLOR
15FROM Production.Product P
16WHERE (P.ListPrice < 60 AND Color='Yellow') OR (p.ListPrice>231 and Color='Silver')
17
18SELECT P.Productid, P.ListPrice, P.COLOR
19FROM Production.Product P
20WHERE P.Color is NOT NULL
21
22SELECT P.ProductID, P.name,
23 P.ListPrice
24FROM Production.Product P
25WHERE P.ListPrice BETWEEN 0 AND 10 -- WHERE P.ListPrice >= 0 and P.ListPirce <=10 SON LO MISMO
26ORDER BY p.ListPrice
27
28SELECT PE.LastName , PE.FirstName , PE.MiddleName
29FROM Person.Person PE
30--WHERE PE.MiddleName= 'A' OR PE.MiddleName = 'D' OR PE.MiddleName = 'R' ES LO MISMO QUE IN
31WHERE PE.MiddleName IN ('A' ,'D', 'R')
32ORDER BY PE.MiddleName
33
34/* LABORATORIO RECUPERO DE DATOS */
35
36--1 distinct
37SELECT DISTINCT ProductID
38FROM Sales.SalesOrderDetail
39
40--1 union
41SELECT ProductID
42FROM Sales.SalesOrderDetail
43UNION ALL
44SELECT ProductID
45FROM Production.WorkOrder
46--2
47SELECT ProductID
48FROM Sales.SalesOrderDetail
49UNION
50SELECT ProductID
51FROM Production.WorkOrder
52
53--1 Case
54SELECT BusinessEntityId,
55 Gender = CASE WHEN Gender='M' then 'Masculino'
56 WHEN Gender='F' then 'Femenino'
57 END
58FROM HumanResources.Employee
59--2
60
61SELECT [BusinessEntityID]
62 ,[SalariedFlag]
63FROM [HumanResources].[Employee]
64ORDER BY
65 CASE [SalariedFlag] WHEN 1 THEN [BusinessEntityID] END DESC
66 ,CASE WHEN [SalariedFlag] = 0 THEN [BusinessEntityID] END;
67
68
69
70
71
72/* LABORATORIO OPERADORES */
73
74--1
75SELECT HR.BusinessEntityID, HR.VacationHours
76FROM HumanResources.Employee HR
77WHERE HR.VacationHours > 90
78
79--2
80
81SELECT P.Name, P.ListPrice, P.ListPrice * 1.21 as 'PrecioConIva'
82FROM Production.Product P
83WHERE p.ListPrice <> 0
84
85--3
86
87SELECT P.ProductID, P.Name, P.ListPrice
88FROM Production.Product P
89WHERE ProductID between 776 and 778
90
91--4
92
93SELECT P.FirstName, P.LastName
94FROM Person.Person P
95WHERE P.LastName = 'johnson'
96
97--5
98
99SELECT P.Name, P.ListPrice, P.Color
100FROM Production.Product P
101WHERE (P.ListPrice < 150 AND P.Color='RED') OR (P.ListPrice > 500 AND P.Color='Black')
102
103--6
104
105SELECT HR.BusinessEntityID, HR.HireDate, HR.VacationHours, YEAR(HR.HireDate) AS 'Año'
106FROM HumanResources.Employee HR
107WHERE YEAR(HR.HireDate) >2000
108
109--7
110
111SELECT P.Name, P.ProductNumber, P.ListPrice, P.ListPrice * 1.10 as 'PrecioConAumento', GETDATE() as 'Fecha'
112FROM Production.Product P
113WHERE P.SellStartDate < GETDATE() AND p.ListPrice > 0
114
115-- LABORATORIO CRITERIOS DE SELECCION --
116
117-- LIKE --
118
119--1
120SELECT P.Name, P.ListPrice, P.Color
121FROM Production.Product P
122WHERE P.ListPrice > 100 AND P.Name LIKE '%seat%'
123
124--2
125
126Select P.Name
127FROM Production.Product p
128where P.Name LIKE '%Mountain bike%'
129
130--3
131
132SELECT PP.LastName, PP.FirstName
133FROM Person.Person PP
134WHERE PP.FirstName LIKE 'Y%'
135
136--4
137
138SELECT PP.LastName, PP.FirstName
139FROM Person.Person PP
140WHERE PP.LastName LIKE '_S%'
141
142--5
143
144SELECT CONCAT (PP.LastName, ' ', PP.FirstName) as 'Nombre'
145FROM Person.Person PP
146WHERE PP.LastName like '%ez'
147
148--6
149
150Select *
151FROM Production.Product p
152WHERE P.Name LIKE '%[0-9]'
153
154--7
155
156SELECT *
157FROM Person.Person PP
158WHERE pp.FirstName like '[C-c]_[^d-g][j-w]%'
159
160-- null --
161
162--2
163
164SELECT isnull(weight,0) as 'Weight' -- REMPLAZA EL NULL POR EL 0 PARA MOSTRAR, NO EN LA TABLE.
165FROM Production.Product
166Where Weight is null
167
168-------------------------------------------------------------------------------------------------------------------------------
169
170SELECT distinct color
171FROM Production.Product -- muestra los distintos de un campo
172
173SELECT BusinessEntityID
174FROM Sales.SalesPerson
175UNION
176SELECT BusinessEntityID
177FROM HumanResources.Employee
178ORDER BY BusinessEntityID -------- UNION JUNTA AMBOS Y ELMINIA DUPLICADOS ------ AL USAR UNION ALL, NO ELIMINA DUPLICADOS
179
180--CASE
181SELECT ProductLine,
182 Category = CASE ProductLine
183 WHEN 'R' THEN 'Road'
184 WHEN 'M' THEN 'Mountain' -- si el productline es R M o T lo cambia por lo marcado, si no pone not for sale.
185 WHEN 'T' THEN 'Touring'
186 ELSE 'Not for sale'
187 END
188FROM Production.Product;
189
190SELECT
191 CASE
192 WHEN EmailPromotion=0 then 'No tiene mail'
193 WHEN EmailPromotion=1 then 'Tiene mail'
194 WHEN EmailPromotion=2 then 'Tiene muchos mail' -- OTRA FORMA DE HACER LO DE ARRIBA
195 ELSE 'Desconocido'
196 END Mail
197FROM Person.Person;
198
199SELECT LastName
200 ,TerritoryName
201 ,CountryRegionName
202FROM Sales.vSalesPerson
203WHERE TerritoryName IS NOT NULL
204ORDER BY CASE CountryRegionName
205 WHEN 'United States' THEN TerritoryName -- si es usa, te dice la region, si no, te dice el nombre del pais. EL ELSE ES SITUACIONAL
206 ELSE CountryRegionName END;
207
208--Función COUNT
209SELECT COUNT(*) AS Cantidad
210FROM HumanResources.EmployeeDepartmentHistory;
211
212--Función MAX
213SELECT MAX(ListPrice) AS Maximo
214FROM Production.Product;
215
216--Función MIN
217SELECT MIN(ListPrice) AS Minimo
218FROM Production.Product;
219
220--Función SUM
221SELECT SUM(ListPrice) AS Total
222FROM Production.Product;
223
224--Función AVG
225SELECT AVG(ListPrice) AS Promedio
226FROM Production.Product;
227
228--GROUP BY
229SELECT ProductID,
230 MAX(LineTotal) as Maximo
231 FROM Sales.SalesOrderDetail
232 WHERE ProductID>995
233GROUP BY ProductID;
234
235--HAVING
236SELECT ProductID
237 ,MAX(LineTotal) as Maximo
238FROM Sales.SalesOrderDetail
239WHERE ProductID>995
240GROUP BY ProductID
241HAVING MAX(LineTotal)>3000;
242
243--ROLLUP
244SELECT ProductID
245 ,MAX(LineTotal) as Maximo
246FROM Sales.SalesOrderDetail
247WHERE ProductID>995
248GROUP BY ProductID WITH ROLLUP
249HAVING MAX(LineTotal)>3000;
250
251------------------------------------------------------------------------------------------------------------------------------
252
253USE AdventureWorks2016CTP3
254GO
255
256SELECT TOP 10 *
257FROM Person.Person
258SELECT TOP 10 *
259FROM Person.PersonPhone
260
261SELECT P.BusinessEntityID as 'IDPERSONA',
262 P.FirstName 'NOMBRE',
263 PP.PhoneNumber 'NUMERO_TELOFONO'
264FROM Person.Person p
265INNER JOIN Person.PersonPhone PP
266 ON (PP.BusinessEntityID = p.BusinessEntityID)
267WHERE p.BusinessEntityID < 10 -- INNER JOIN, SOBRE BUSINESSENTITYID, TIENEN QUE ESTAR EN LAS 2 TABLAS
268
269SELECT TOP 10 *
270FROM Production.Product
271SELECT TOP 10*
272FROM Sales.SalesOrderDetail
273
274SELECT P.ProductID 'PRODUCTO_P',
275 S.ProductID 'PRODUCTO_S'
276FROM Production.Product P
277LEFT JOIN Sales.SalesOrderDetail S
278 ON (S.ProductID = P.ProductID)
279WHERE P.ProductID IN (897,389) -- LEFT JOIN, TODOS LOS QUE ESTAN EN LA TABLA IZQUIERDA
280 -- MAS LA INTERSECCION DE LA OTRA(RIGHT ES LO MISMO, PERO TODOS DE LA DERECHA)
281
282------------------------------------------------ TEORIA CLASE 3 --------------------------------------------------
283
284USE AdventureWorks2016CTP3
285GO
286
287CREATE TABLE dbo.tblA (idTablaA int not null, vchDescA varchar(10) not null)
288CREATE TABLE dbo.tblB (idTablaB int not null, idTablaA int null, intValorB int not null)
289
290insert into dbo.tblA (idTablaA, vchDescA) values (1,'A')
291insert into dbo.tblA (idTablaA, vchDescA) values (2,'B')
292insert into dbo.tblA (idTablaA, vchDescA) values (3,'C')
293insert into dbo.tblA (idTablaA, vchDescA) values (4,'D')
294
295insert into dbo.tblB (idTablaB, idTablaA, intValorB) values (1,1,10)
296insert into dbo.tblB (idTablaB, idTablaA, intValorB) values (2,1,20)
297insert into dbo.tblB (idTablaB, idTablaA, intValorB) values (3,4,40)
298insert into dbo.tblB (idTablaB, idTablaA, intValorB) values (4,2,50)
299insert into dbo.tblB (idTablaB, idTablaA, intValorB) values (5,NULL,100)
300
301SELECT * FROM dbo.tblA
302SELECT * FROM dbo.tblB
303
304--INNER JOIN
305SELECT A.idTablaA, A.idTablaA, b.idTablaB, b.idTablaA, B.intValorB
306FROM dbo.tblA A
307INNER JOIN dbo.tblB B
308 ON (A.idTablaA = B.idTablaA)
309
310
311--left JOIN
312SELECT A.idTablaA, A.idTablaA, b.idTablaB, b.idTablaA, B.intValorB
313FROM dbo.tblA A
314left JOIN dbo.tblB B
315 ON (A.idTablaA = B.idTablaA)
316
317--RIGHT JOIN
318 SELECT *
319 FROM dbo.tblA A
320 RIGHT JOIN dbo.tblB B
321 ON (A.idTablaA = B.idTablaA)
322
323--FULL JOIN
324SELECT *
325FROM dbo.tblA A
326FULL JOIN dbo.tblB B
327 ON (A.idTablaA = B.idTablaA)
328
329--CROSS JOIN
330SELECT *
331FROM dbo.tblA A
332CROSS JOIN dbo.tblB B -- PRODUCTO CARTESEANO
333
334
335--SELF JOIN
336IF OBJECT_ID('dbo.Empleados','U') IS NOT NULL
337BEGIN
338 DROP TABLE dbo.Empleados
339END
340
341CREATE TABLE Empleados
342(
343 Codigo INT,
344 Nombre VARCHAR(50),
345 Puesto VARCHAR(50),
346 Supervisor INT
347);
348
349INSERT INTO dbo.Empleados
350VALUES
351 (1, 'Juan','Lider de Proyecto', 3)
352,(2, 'Pedro','Desarrollador', 1)
353,(3, 'Maria','Jefa', NULL)
354,(4, 'Martin','Diseñador', 1);
355
356SELECT p2.Codigo,
357 p2.Nombre,
358 p2.Puesto,
359 p1.Nombre AS Supervisor
360FROM Empleados p1
361INNER JOIN Empleados p2
362 ON p1.Codigo=p2.Supervisor
363WHERE p2.Supervisor IS NOT NULL;
364
365SELECT P1.*,P2.*
366FROM Empleados p1
367INNER JOIN Empleados p2
368 ON p1.Codigo=p2.Supervisor
369WHERE p2.Supervisor IS NOT NULL;
370
371-- TABLAS TEMPORALES
372
373CREATE TABLE #tblA (
374 idTablaA int not null
375 , vchDescA varchar(10) not null
376 ) -- TABLA TEMP LOCAL -- SOLO SIRVE PARA LA SESION (QUERY) ACTUAL
377
378CREATE TABLE ##tblB (idTablaB int not null
379 , idTablaA int null,
380 intValorB int not null
381 ) -- TABLA TEMP GLOBAL -- SIRVE PARA CUALQUIER SESION (QUERY) ACTUAL
382
383 -- SI SE CIERRA LA QUERY, DESAPARECEN LAS TABLAS TEMP LOCALES Y GLOBALES.
384
385SP_HELP 'HumanResources.Employee' -- brinda informacion de la tabla
386
387
388-- LABORATORIO RELACION Y CONJUNTOS --
389
390-- JOINS --
391
392-- 1 --
393
394SELECT *
395FROM HumanResources.Employee HR
396INNER JOIN Sales.SalesPerson S
397ON (HR.BusinessEntityID = S.BusinessEntityID)
398
399-- 2 --
400
401SELECT HR.BusinessEntityID, P.FirstName, P.LastName
402FROM HumanResources.Employee HR
403INNER JOIN Person.Person P
404ON (HR.BusinessEntityID = P.BusinessEntityID)
405ORDER BY p.LastName, P.FirstName
406
407-- 3 --
408
409SELECT HR.LoginID, S.TerritoryID, S.Bonus, S.BusinessEntityID
410FROM HumanResources.Employee HR
411INNER JOIN Sales.SalesPerson S ON (S.BusinessEntityID = HR.BusinessEntityID)
412
413-- 4 --
414
415SELECT PP.Name , PP.ProductCategoryID
416FROM Production.Product P
417INNER JOIN Production.ProductSubcategory PP ON (P.ProductSubcategoryID = PP.ProductSubcategoryID)
418WHERE PP.Name = 'Wheels'
419
420-- 5 --
421
422SELECT P.*, PS.Name
423FROM Production.Product P
424INNER JOIN Production.ProductSubcategory PS ON (PS.ProductSubcategoryID = P.ProductSubcategoryID)
425WHERE P.Name NOT LIKE '%bike%'
426
427-- 6 --
428
429SELECT distinct PP.ProductID, pp.Name, pp.ListPrice, sd.UnitPrice
430FROM Sales.SalesOrderDetail SD
431INNER JOIN Production.Product PP ON (PP.ProductID = SD.ProductID)
432WHERE SD.UnitPrice < pp.ListPrice
433order by pp.Name
434
435-- 7 --
436
437SELECT p1.ProductID, p1.name, p1.ListPrice,
438 p2.ProductID, p2.Name, p2.ListPrice
439FROM Production.Product P1
440inner join Production.Product P2 on (P2.ListPrice = P1.ListPrice)
441where p1.ProductID > p2.ProductID
442order by P1.ListPrice desc
443
444-- 8 --
445
446SELECT P.Name 'NOMBRE_PRODUCTO' , v.Name 'PROVEEDOR'
447FROM Production.Product P
448INNER JOIN Purchasing.ProductVendor PV ON (PV.ProductID = p.ProductID)
449INNER JOIN Purchasing.Vendor V on (PV.BusinessEntityID = v.BusinessEntityID)
450where p.ProductSubcategoryID = 15
451order by V.Name
452
453-- 9 --
454
455SELECT p.FirstName, p.LastName, ISNULL(HR.LoginID, ' SIN LOGIN' )
456FROM Person.Person P
457left join HumanResources.Employee hr ON (hr.BusinessEntityID = p.BusinessEntityID)
458
459
460-- LABORATORIO TABLAS TEMPORALES --
461
462-- 1 --
463
464select P.Name, P.Color, p.ListPrice
465into #productos
466from Production.Product P
467
468select * from #productos
469
470-- 2 --
471
472SELECT BusinessEntityID, FirstName, LastName
473into dbo.personas33
474FROM Person.Person
475where 1=2 ---- SE USA EL 1=2 PARA CREAR ALGO IMPOSIBLE, CLONANDO LA TABLA SIN NINGUN REGISTRO
476
477
478--3)Eliminar si existe la tabla Productos
479--tablas: Productos
480IF OBJECT_ID (N'tempdb..#Productos', N'U') IS NOT NULL
481 DROP TABLE #Productos;
482GO
483
484--4)Eliminar si existe la tabla Personas
485--tablas: Personas
486IF OBJECT_ID (N'tempdb..#Personas', N'U') IS NOT NULL
487 DROP TABLE #Personas;
488GO
489
490--5)Crear una CTE con las órdenes de venta
491
492--tablas: Sales.SalesOrderHeader
493--campos: SalesPersonID, SalesOrderID, OrderDate
494
495WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
496AS
497(
498 SELECT SalesPersonID
499 ,SalesOrderID
500 ,YEAR(OrderDate) AS Anio
501 FROM Sales.SalesOrderHeader
502 WHERE SalesPersonID IS NOT NULL
503)
504SELECT SalesPersonID, SalesOrderID, SalesYear
505FROM [Sales_CTE]
506
507
508--------------------------- 4------------------------------------
509
510SELECT ProductID,
511 ListPrice,
512 (SELECT AVG(ListPrice)FROM Production.Product) AS 'PRECIO-PROMEDIO'
513FROM Production.Product;
514
515
516--SUBCONSULTA FROM
517SELECT pp.ProductID,
518 pp.ListPrice,
519 x.promedio
520FROM Production.Product pp
521INNER JOIN (
522 SELECT ProductID,
523 AVG(LineTotal) promedio
524 FROM Sales.SalesOrderDetail
525 GROUP BY ProductID
526
527 ) x
528 ON pp.ProductID=x.ProductID; -- SE LE DA EL ALIAS X AL PROMEDIO DE UN PRODUCTID. LA CONSLTA MUESTRA, PRODUCTID, PRECIO DE LISTA Y PROMEDIO POR PRODUCTO
529
530
531--Subconsultas correlacionadas
532SELECT p1.ProductSubcategoryID,
533 p1.ProductID,
534 p1.ListPrice
535 FROM Production.Product p1
536 WHERE ListPrice = (
537 SELECT MIN (ListPrice)
538 FROM Production.Product p2
539 WHERE p2.ProductSubcategoryID = p1.ProductSubcategoryID
540 )
541 ORDER BY p1.ProductSubcategoryID; -- PRECIO MINIMO DE LISTA DE CADA SUBCATEGORIA Y PRODUCTID
542
543------------------------------------------------------------- CLASE 4 ---------------------------------
544
545
546-------------------------------------------------------------------------------------------------------------
547-- SUBCONSULTAS CON [ANY|SOME - ALL]
548-------------------------------------------------------------------------------------------------------------
549CREATE TABLE _A (ID INT);
550CREATE TABLE _B (ID INT);
551
552INSERT INTO _A VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
553INSERT INTO _B VALUES (1),(2),(3),(4),(5);
554
555SELECT * FROM _A
556SELECT * FROM _B
557
558---------------------------------------------- ANY|SOME --------------------------------------
559--(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
560--(1),(2),(3),(4),(5);
561
562------------------------------------------------------------------------------------------
563-- = ANY ES EQUIVALENTE A IN
564------------------------------------------------------------------------------------------
565SELECT ID
566FROM _A
567WHERE ID =ANY(SELECT ID FROM _B)
568
569SELECT ID
570FROM _A
571WHERE ID >=ANY(SELECT ID FROM _B)
572
573SELECT ID
574FROM _A
575WHERE ID >ANY(SELECT ID FROM _B)
576
577SELECT ID
578FROM _A
579WHERE ID >ANY(SELECT MAX(ID) FROM _B)
580
581SELECT ID
582FROM _B
583WHERE ID >ANY(SELECT MAX(ID) FROM _A)
584
585---------------------------------------------- ALL --------------------------------------
586--(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
587--(1),(2),(3),(4),(5);
588SELECT ID
589FROM _A
590WHERE ID =ALL(SELECT ID FROM _B)
591
592SELECT ID
593FROM _A
594WHERE ID >ALL(SELECT ID FROM _B)
595
596SELECT ID
597FROM _A
598WHERE ID <=ALL(SELECT ID FROM _B)
599
600SELECT ID
601FROM _A
602WHERE ID >ALL(SELECT MAX(ID) FROM _B)
603
604------------------------------------------------------------------------------------------
605-- <> ALL ES EQUIVALENTE A NOT IN
606------------------------------------------------------------------------------------------
607SELECT ID
608FROM _A
609WHERE ID <>ALL(SELECT ID FROM _B)
610
611--INSERT
612
613IF OBJECT_ID (N'dbo.Sectores', N'U') IS NOT NULL
614DROP TABLE dbo.Sectores;
615GO
616
617CREATE TABLE dbo.Sectores
618(
619SectorID TINYINT NOT NULL IDENTITY(1,1)
620,Gerencia VARCHAR(50)
621,Sector VARCHAR(50) DEFAULT 'Sin Sector'
622);
623
624
625INSERT INTO dbo.Sectores DEFAULT VALUES;
626GO
627
628
629INSERT INTO dbo.Sectores
630VALUES ('Finanzas', 'Contaduria' ) -- inserta salteando sectorid ya que tiene identity
631GO
632
633INSERT INTO dbo.Sectores
634VALUES ('Finanzas','Cobranzas'), -- insterta 2 valores
635('Finanzas','Ventas')
636GO
637
638INSERT INTO dbo.Sectores (Sector, Gerencia) -- inserta en distinto orden a la tabla
639VALUES ('Legales', 'Finanzas');
640GO
641
642INSERT INTO dbo.Sectores
643SELECT 'Produccion', 'Manufactura'; -- inserta valores con una consulta
644GO
645
646INSERT INTO dbo.Sectores (Gerencia, Sector)
647VALUES ('Finanzas', 'Ventas'); -- forma completa de insentar, cuando hay identity no se pone
648GO
649
650SET IDENTITY_INSERT dbo.Sectores ON;
651GO -- Permite editar el identity ID
652
653INSERT INTO dbo.Sectores (SectorID, Gerencia, Sector)
654VALUES (100, 'Recursos Humanos', DEFAULT); -- Inserta con identity id, salteandose hacia el 100
655GO
656
657SET IDENTITY_INSERT dbo.Sectores OFF;
658GO -- Vuelve el identity al normal, desde 100
659
660INSERT INTO dbo.Sectores (Gerencia, Sector)
661SELECT Name,GroupName
662FROM HumanResources.Department
663WHERE DepartmentID=1; -- Inserta datos con resultaods de una consulta
664GO
665
666SELECT * FROM dbo.Sectores
667
668--UPDATE
669IF OBJECT_ID (N'dbo.SectoresNuevo', N'U') IS NOT NULL
670DROP TABLE dbo.SectoresNuevo;
671GO
672CREATE TABLE dbo.SectoresNuevo
673(
674Sector VARCHAR(50)
675,SectorNuevo VARCHAR(50)
676);
677INSERT INTO dbo.SectoresNuevo (sector, sectornuevo)
678VALUES ('Contaduria', 'tesoreria')
679
680UPDATE s
681 SET Sector=sn.SectorNuevo
682 FROM Sectores s
683 INNER JOIN SectoresNuevo sn
684 ON S.Sector=sn.Sector
685
686SELECT * FROM dbo.Sectores;
687
688--DELETE
689SELECT * FROM DBO.SectoresNuevo
690SELECT * FROM dbo.Sectores;
691
692DELETE s
693FROM dbo.Sectores s
694INNER JOIN dbo.SectoresNuevo sn
695ON S.Sector=sn.SectorNuevo ;
696
697
698--TRUNCATE
699TRUNCATE TABLE dbo.Sectores; -- RESETEA IDENTITY SI HAY, EL DELETE NO.
700
701
702
703---------------------- LABORATORIO SUB CONSULTAS ---------------------------
704
705--1--
706
707SELECT P.Name, P.ListPrice
708FROM Production.Product P
709WHERE ListPrice < (SELECT AVG(ListPrice)
710 From Production.Product
711 )
712
713ORDER BY ListPrice DESC
714
715--2--
716
717SELECT Name,
718 ListPrice ' Precio De Lista' ,
719 (SELECT AVG(ListPrice) from Production.Product) ' Precio Promedio' ,
720 ListPrice - (Select AVG(ListPrice) FROM Production.Product) ' Diferencia De Precio'
721 FROM Production.Product
722
723-- 3 --
724
725SELECT ProductID, Name, ListPrice
726FROM Production.Product
727WHERE ListPrice = (SELECT MAX(ListPrice) From Production.Product)
728
729-- 4 --
730
731SELECT P.ProductID, P.NAME, P.ListPrice, P.ProductSubcategoryID
732FROM Production.Product P
733WHERE P.ListPrice = (SELECT MIN(P1.ListPrice)
734 FROM Production.Product P1
735 WHERE P1.ProductSubcategoryID = P.ProductSubcategoryID)
736ORDER BY P.ProductSubcategoryID, P.ListPrice
737
738------------ LABORATORIO EXISTS - NOT EXSIST -------------
739
740-- 1 --
741
742SELECT ProductID,Name, p.ProductSubcategoryID
743FROM Production.Product P
744WHERE EXISTS (SELECT *
745 FROM Production.ProductSubcategory PS
746 WHERE ps.ProductSubcategoryID = p.ProductSubcategoryID AND
747 NAME LIKE '%WHEELS%'
748 )
749
750-- 2 --
751SELECT p.ProductID, p.Name
752FROM Production.Product P
753WHERE NOT EXISTS (SELECT *
754 FROM SALES.SalesOrderDetail SD
755 WHERE SD.ProductID = p.ProductID
756 )
757order by p.ProductID
758
759SELECT P.ProductID, P.Name
760FROM Production.Product p
761LEFT JOIN sales.SalesOrderDetail sd
762 on (sd.ProductID = p.ProductID)
763WHERE SD.ProductID IS NULL
764ORDER BY P.ProductID --- FORMA DE HACERLO CON UN JOIN ENVEZ DE NOT EXIST
765
766-- 3 --
767
768SELECT *
769FROM Person.Person P
770WHERE NOT EXISTS (SELECT *
771 FROM sales.SalesPerson SP
772 WHERE SP.BusinessEntityID = P.BusinessEntityID
773 )
774
775SELECT *
776FROM Person.Person p
777LEFT JOIN Sales.SalesPerson SP
778 ON (SP.BusinessEntityID = P.BusinessEntityID)
779WHERE SP.BusinessEntityID IS NULL -- MISMA FORMA , USANDO LEFT JOIN
780
781
782-- 4 --
783
784SELECT P.BusinessEntityID,P.LastName,P.FirstName
785FROM Person.Person P
786WHERE EXISTS (SELECT *
787 FROM SALES.SalesPerson S
788 WHERE S.BusinessEntityID = P.BusinessEntityID AND
789 S.TerritoryID IS NULL)
790
791SELECT P.BusinessEntityID, P.LastName, P.FirstName
792FROM PERSON.Person P
793INNER JOIN SALES.SalesPerson SP
794 ON (SP.BusinessEntityID = P.BusinessEntityID)
795WHERE SP.TerritoryID IS NULL -- MISMA FORMA, CON INNER JOIN , (INNER JOIN TIENE MEJOR PERFORMANCE PARAL A PC)
796
797
798------------- IN / NOT IN ---------
799-- 1 --
800
801
802SELECT *
803FROM SALES.SalesOrderHeader SO
804WHERE SO.TerritoryID IN (SELECT ST.TerritoryID
805 FROM Sales.SalesTerritory ST
806 WHERE CountryRegionCode = 'US')
807
808-- 2 --
809
810SELECT *
811FROM SALES.SalesOrderHeader SO
812WHERE SO.TerritoryID IN (SELECT ST.TerritoryID
813 FROM Sales.SalesTerritory ST
814 WHERE CountryRegionCode IN ('US','FR','GB'))
815
816
817-- 3 --
818
819SELECT TOP 10 *
820FROM Production.Product
821ORDER BY ListPrice DESC
822
823-- 4 --
824
825SELECT *
826FROM Production.Product p
827WHERE P.ProductID IN (SELECT DISTINCT SO.ProductID
828 FROM SALES.SalesOrderDetail SO
829 WHERE SO.OrderQty >20
830 )
831
832--------------------------- LABORATORIO DML ---------------------
833
834-- 1 --
835
836SELECT ProductID, Name, Color, ListPrice
837INTO Productos
838FROM Production.Product
839
840-- 2 --
841
842UPDATE Productos
843set ListPrice = ListPrice * 1.20
844
845-- 3 --
846
847UPDATE P
848 SET P.ListPrice = P.ListPrice * 1.2
849 FROM Productos P
850 INNER JOIN Purchasing.ProductVendor PV
851 ON (PV.ProductID = P.ProductID)
852 WHERE pv.BusinessEntityID = 1540
853
854-- 4 --
855
856DELETE
857FROM Productos
858WHERE ListPrice = 0
859
860-- 5 --
861
862INSERT INTO Productos (Name, Color, ListPrice) VALUES
863 (
864 'Bicicleta Mountain Bike',
865 'Rojo',
866 4000
867 )
868
869-- 6 --
870SELECT Name, ListPrice
871FROM Productos
872WHERE Name like '%pedal%'
873
874UPDATE Productos
875 SET ListPrice = ListPrice * 1.15
876 WHERE Name like '%pedal%'
877
878-- 7 --
879
880SELECT *
881FROM Productos
882WHERE name like 'm%'
883
884DELETE
885FROM Productos
886WHERE Name like 'm%'
887
888-- 8 --
889
890TRUNCATE TABLE Productos
891
892-- 9 --
893
894DROP TABLE Productos
895
896
897------------- LABORATORIO MANEJO VARIABLES ------------------
898
899DECLARE @TotalVentas NUMERIC(38,6) = 0
900--SELECT @TotalVentas = 0
901--SET @TotalVentas = 0
902
903SELECT @TotalVentas = SUM(LineTotal)
904FROM Sales.SalesOrderDetail
905
906
907 SELECT @TotalVentas AS 'TOTAL VENTA'-- formas de mostrar el valor
908 PRINT @TotalVentas
909
910-- 1 --
911
912DECLARE @TotalVentas NUMERIC(38,6) = 0
913
914 SELECT @TotalVentas =SUM (SD.LINETOTAL)
915 FROM Sales.SalesOrderHeader SH
916 INNER JOIN SALES.SalesOrderDetail SD
917 ON (SH.SalesOrderID = SD.SalesOrderID)
918 WHERE year(OrderDate) = '2014'
919 PRINT @TotalVentas
920
921-- 2 --
922
923DECLARE @Promedio MONEY = 0
924
925SELECT @Promedio = AVG(LISTPRICE)
926 FROM Production.Product
927
928SELECT *
929 FROM Production.Product
930 WHERE ListPrice < @Promedio
931 ORDER BY ListPrice
932
933-- 3 --
934
935
936DECLARE @Promedio MONEY = 0
937
938SELECT @Promedio = AVG(LISTPRICE)
939 FROM Production.Product
940
941SELECT ProductID, Name, ListPrice
942INTO productos
943 FROM Production.Product
944 WHERE ListPrice < @Promedio
945 ORDER BY ListPrice
946
947UPDATE productos
948SET ListPrice = ListPrice * 1.1
949
950
951SELECT p.ProductID, p.Name , p.ListPrice, po.ListPrice
952FROM PRODUCTION.Product P
953INNER JOIN productos po
954 on (po.ProductID = p.ProductID) -- comapra las dos tablas y muestra el aumento
955
956-- 4 --
957
958DECLARE @variableTabla TABLE (
959 Categoria VARCHAR(50),
960 SubCategoria VARCHAR(50)
961 )
962INSERT INTO @variableTabla (Categoria, SubCategoria)
963 SELECT PC.Name, ps.Name
964 FROM Production.ProductSubcategory PS
965 INNER JOIN Production.ProductCategory PC
966 ON (PC.ProductCategoryID = PS.ProductCategoryID)
967
968SELECT * FROM @variableTabla
969
970-- 5 --
971
972DECLARE @Promedio MONEY = 0
973SELECT @Promedio = AVG(listprice)
974 FROM Production.Product
975
976IF (@Promedio < 500)
977BEGIN
978 PRINT 'PROMEDIO BAJO'
979END ELSE BEGIN
980 PRINT 'PROMEDIO ALTO'
981END
982
983
984
985/************************************************************************************
986* *
987* INSERTAR MODIFICAR ELIMINAR REGISTROS CON MERGE *
988* *
989************************************************************************************/
990
991IF OBJECT_ID (N'dbo.usuarios', N'U') IS NOT NULL
992 DROP TABLE dbo.usuarios;
993IF OBJECT_ID (N'dbo.UsuariosActual', N'U') IS NOT NULL
994 DROP TABLE dbo.UsuariosActual;
995
996
997CREATE TABLE Usuarios
998(
999 Codigo INT PRIMARY KEY,
1000 Nombre VARCHAR(100),
1001 Puntos INT
1002)
1003GO
1004INSERT INTO Usuarios
1005VALUES
1006 (1,'Juan Perez',5),
1007 (2,'Marco Salgado',5),
1008 (3,'Carlos Soto',5),
1009 (4,'Alberto Ruiz',5),
1010 (5,'Alejandro Castro',5)
1011GO
1012CREATE TABLE UsuariosActual
1013(
1014 Codigo INT PRIMARY KEY,
1015 Nombre VARCHAR(100),
1016 Puntos INT
1017)
1018GO
1019INSERT INTO UsuariosActual
1020VALUES
1021 (1,'Juan Perez',10),
1022 (2,'Marco Salgado',10),
1023 (4,'Alberto Ruiz',10),
1024 (5,'Alejandro Castro',10),
1025 (6,'Pablo Ramos',10)
1026
1027SELECT * FROM Usuarios
1028SELECT * FROM UsuariosActual
1029
1030
1031-- update target.codigo {1,10; 2,10; 4,10; 5,10}
1032-- insert target.codigo {6,10}
1033-- delete target.codigo {3,10}
1034
1035
1036MERGE Usuarios AS TARGET
1037USING UsuariosActual AS SOURCE
1038ON (TARGET.Codigo = SOURCE.Codigo)
1039
1040--Cuandos los registros concuerdan se actualizan los puntos si tienen alguna variación
1041WHEN MATCHED AND TARGET.Puntos <> SOURCE.Puntos THEN
1042UPDATE SET TARGET.Puntos = SOURCE.Puntos
1043
1044
1045--Cuando solo existe en source se inserta en target
1046
1047WHEN NOT MATCHED BY TARGET THEN
1048INSERT (Codigo, Nombre, Puntos)
1049VALUES (SOURCE.Codigo, SOURCE.Nombre, SOURCE.Puntos)
1050
1051
1052--Cuando solo existe en TARGET se elimina en target
1053WHEN NOT MATCHED BY source THEN
1054DELETE;
1055
1056SELECT * FROM Usuarios
1057SELECT * FROM UsuariosActual
1058
1059
1060IF OBJECT_ID (N'dbo.usuarios', N'U') IS NOT NULL
1061 DROP TABLE dbo.usuarios;
1062IF OBJECT_ID (N'dbo.UsuariosActual', N'U') IS NOT NULL
1063 DROP TABLE dbo.UsuariosActual;
1064
1065
1066--TEORIA CLASE 5
1067
1068DECLARE @indice INT;
1069SET @indice=0 ;
1070
1071WHILE (@indice<=10)
1072BEGIN
1073 PRINT CAST(@indice AS VARCHAR(10))
1074 IF @indice=7
1075 BEGIN
1076 SET @indice=@indice+1;
1077 WAITFOR DELAY '00:00:05'
1078 CONTINUE;
1079 END
1080
1081 ---- Dos formas de salir forzando el while
1082 IF @indice=4 GOTO mensaje;
1083 IF @indice=6 BREAK;
1084 ----------------------------------------
1085 SET @indice=@indice+1;
1086END
1087PRINT 'SALI DEL WHILE POR EL BREAK'
1088mensaje:
1089PRINT 'SALI DEL WHILE POR EL GOTO'
1090
1091----------------------------------- STORED PROCEDURES --------------------------
1092
1093
1094
1095CREATE PROCEDURE dbo.PA_Empleados
1096AS
1097BEGIN
1098 SET NOCOUNT ON
1099
1100
1101 SELECT LastName, FirstName, Department
1102 from HumanResources.vEmployeeDepartmentHistory -- CREAMOS UN STOCK PROCEDURE
1103END
1104GO
1105
1106EXEC dbo.PA_EMPLEADOS
1107go
1108
1109ALTER PROCEDURE dbo.PA_Empleados
1110AS
1111BEGIN
1112 SET NOCOUNT ON
1113
1114
1115 SELECT LastName, FirstName, Department as DEPARTAMENTO
1116 from HumanResources.vEmployeeDepartmentHistory -- ALTER , le ponemos un alias
1117END
1118GO
1119
1120exec dbo.PA_Empleados
1121go -- muestra lo msimo pero cambio el nombre
1122
1123ALTER PROCEDURE dbo.PA_Empleados(
1124@LastName VARCHAR(50),
1125@FirstName VARCHAR(50)
1126)
1127AS
1128BEGIN
1129 SET NOCOUNT ON
1130
1131
1132 SELECT LastName, FirstName, Department
1133 from HumanResources.vEmployeeDepartmentHistory
1134 WHERE LastName = @LastName AND
1135 FirstName = @FirstName -- LE AGREGAMOS 2 PARAMETROS DE ENTRADA
1136END
1137GO
1138
1139EXEC dbo.PA_Empleados 'Duffy' , 'Terri'
1140
1141GO -- LLAMA AL STORED PROCEDURE Y SOLO DEVUELVE LOS PARAMETROS LISTADOS
1142
1143EXEC dbo.PA_Empleados
1144@FirstName = 'Terri' ,
1145@LastName = 'Duffy'
1146 -- DEVUELVE PARAMETROS LISTADOS EN ORDEN ACLARADO
1147
1148GO
1149
1150
1151ALTER PROCEDURE dbo.PA_Empleados(
1152@LastName VARCHAR(50)= 'Altman' ,
1153@FirstName VARCHAR(50)= 'Gary'
1154)
1155AS
1156BEGIN
1157 SET NOCOUNT ON
1158
1159
1160 SELECT LastName, FirstName, Department
1161 from HumanResources.vEmployeeDepartmentHistory
1162 WHERE LastName = @LastName AND
1163 FirstName = @FirstName -- LE AGREGAMOS 2 PARAMETROS DE ENTRADA
1164 -- DONDE POR DEFAULT, LASTNAME ES ALTMAN Y FIRST ES GARY
1165END
1166GO
1167
1168ALTER PROCEDURE dbo.PA_Empleados(
1169@LastName VARCHAR(50)= 'Altman' ,
1170@FirstName VARCHAR(50)= 'Gary' ,
1171@Department VARCHAR(50) OUTPUT
1172)
1173
1174AS
1175BEGIN
1176 SET NOCOUNT ON
1177
1178
1179 SELECT @Department = Department
1180 from HumanResources.vEmployeeDepartmentHistory
1181 WHERE LastName = @LastName AND
1182 FirstName = @FirstName -- LE AGREGAMOS LA VARIABLE DEPARTMENT COMO OUTPUT, CON VALOR ASIGNADO
1183
1184END
1185GO
1186
1187DECLARE @DEPARTMENTo VARCHAR(50)
1188
1189EXEC dbo.PA_Empleados
1190@FirstName = 'Terri' ,
1191@LastName = 'Duffy' ,
1192@Department = @Departmento OUTPUT -- ME DEVUELVE EL VALOR DE @Department declarando @DEPARTAMENTO
1193
1194SELECT @DEPARTMENTo -- Para ver la consulta se nesecita el select
1195
1196
1197
1198ALTER PROCEDURE dbo.PA_Empleados(
1199@LastName VARCHAR(50)= 'Altman' ,
1200@FirstName VARCHAR(50)= 'Gary' ,
1201@Department VARCHAR(50) OUTPUT
1202)
1203
1204AS
1205BEGIN
1206 SET NOCOUNT ON
1207
1208
1209 SELECT @Department = Department
1210 from HumanResources.vEmployeeDepartmentHistory
1211 WHERE LastName = @LastName AND
1212 FirstName = @FirstName
1213
1214 IF (@@ROWCOUNT = 0)
1215 BEGIN
1216 RETURN 1
1217 END
1218
1219 IF (@Department IS NULL)
1220 BEGIN
1221 RETURN -1
1222 END ELSE BEGIN
1223 RETURN 0 -- SI EL DEPARTAMENT OESTA VACIO, MUESTRA -1 SI NO, MUESTRA 0, SI NO EXISTE MUESTRA 1
1224 END
1225END
1226GO
1227
1228DECLARE @Departamento VARCHAR(50)
1229DECLARE @error INT
1230
1231
1232EXEC @error = dbo.PA_Empleados
1233@FirstName = 'Terri' ,
1234@LastName = 'Duffy' ,
1235@Department = @Departamento OUTPUT
1236
1237SELECT @Departamento,@error -- se declara error al principio, asi se puede mostrar si lo hay
1238
1239
1240ALTER PROCEDURE DBO.PA_EMPLEADOS
1241AS
1242BEGIN
1243 SET NOCOUNT ON
1244
1245
1246 SELECT LastName, FirstName, Department
1247 FROM HumanResources.vEmployeeDepartmentHistory
1248
1249 RETURN 0
1250
1251end
1252
1253create table #Empleado (
1254Nombre VARCHAR(50) NULL,
1255Apellido VARCHAR(50) NULL,
1256Departamento VARCHAR(50) NULL
1257)
1258
1259declare @error int
1260
1261INSERT INTO #Empleado
1262exec @error = dbo.PA_EMPLEADOS
1263
1264SELECT * from #Empleado -- INSERTA TODOS LOS REGISTROS DEL STORED PROCEDURE A LA TABLA TEMPORAL #Empleado
1265
1266
1267
1268---------------------- LABORATORIO STORED PROCEDURES -------------------------------------------------------
1269
1270
1271---1---
1272
1273CREATE PROCEDURE dbo.Empleado
1274(
1275@Inicial CHAR(1)
1276)
1277AS
1278BEGIN
1279
1280 SET NOCOUNT ON
1281 SELECT BusinessEntityID, FirstName, LastName, EmailAddress -- EL LEFT DE IZQ A DERECHA LA CANTDIAD DE CARACTERES ESPEFICIADO (1 EN ESTE CASO)
1282 FROM HumanResources.vEmployee
1283 WHERE LEFT(FirstName,1) = @Inicial
1284
1285
1286 RETURN 0
1287END
1288----------------------------
1289DECLARE @ERROR INT
1290EXEC @ERROR = dbo.Empleado @inicial = 'M'
1291
1292
1293--------------------------------------------- 2-----------------------------------------
1294CREATE PROCEDURE dbo.ASDASD
1295(
1296@ProductID INT
1297
1298)
1299AS
1300BEGIN
1301 IF EXISTS (SELECT 1 FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID)
1302 BEGIN
1303 PRINT ' PRODUCTO VENDIDO '
1304 END ELSE BEGIN
1305 PRINT ' PRODUCTO NO VENDIDO '
1306 END
1307 RETURN 0
1308
1309END
1310
1311--------------------
1312
1313EXEC dbo.ASDASD @ProduCtID = 750
1314--------------------------------------------------------------------3---------------------------------------
1315
1316SELECT *
1317INTO DBO.PRODUCTO
1318FROM Production.Product -- para trabajar con otra tabla
1319
1320CREATE PROCEDURE ActualizaProducto (
1321@ProductId INT ,
1322@ListPrice MONEY
1323)
1324AS
1325BEGIN
1326 UPDATE dbo.PRODUCTO
1327 SET LISTPRICE =@ListPrice
1328 WHERE PRODUCTID=@ProductID
1329
1330 SELECT ProductID,Name,ListPrice
1331 FROM dbo.PRODUCTO
1332 WHERE ProductID = @ProductID
1333 RETURN 0
1334END
1335
1336----------------
1337
1338EXEC ActualizaProducto
1339@ProductId = 317 ,
1340@ListPrice = 1234
1341
1342
1343------------------------------------------------------ 4-------------------------
1344
1345CREATE PROCEDURE dbo.ProveedorProducto
1346(
1347@ProductID INT
1348)
1349AS
1350BEGIN
1351 select VE.Name proveedor, P.Name nombreproducto, P.ProductID
1352 from Purchasing.Vendor VE
1353 INNER JOIN Purchasing.ProductVendor pv
1354 on (pv.BusinessEntityID = VE.BusinessEntityID)
1355 INNER JOIN Production.Product P
1356 on (P.ProductID = pv.ProductID)
1357 WHERE P.ProductID = @ProductID
1358 RETURN 0
1359END
1360
1361----------------
1362EXEC dbo.ProveedorProducto
1363@ProductId = 881
1364------------------
1365
1366
1367---------------------------------------------- CLASE 5 --------------------------------------------------
1368
1369
1370
1371------------------------------------------------------ 5-----------------------------------------------
1372
1373CREATE PROCEDURE dbo.EmpleadoSector
1374(
1375@LASTNAME nvarchar(50) = '%'
1376)
1377AS
1378BEGIN
1379 SELECT FirstName,LastName,Department
1380 FROM HumanResources.vEmployeeDepartmentHistory
1381 WHERE LastName like @LASTNAME
1382END
1383
1384---------------------
1385exec dbo.EmpleadoSector 'b%'
1386
1387----------------------------------------------------------------------- fucniones --------------------------------
1388
1389CREATE FUNCTION sales.fnTotalVentas
1390(
1391@ProductId INT
1392)
1393RETURNS INT
1394AS
1395BEGIN
1396 DECLARE @totalPedidos INT = 0
1397
1398 SELECT @totalPedidos = SUM(OrderQty)
1399 FROM Sales.SalesOrderDetail
1400 WHERE ProductID = @ProductId
1401
1402 RETURN @totalPedidos
1403
1404END
1405
1406---------------
1407SELECT Sales.fnTotalVentas (771) as TOTALPEDIDOS --- Crea la funcion sales.FNTOTALVENTAS , que muestra el total de un producto vendido
1408 -- funcion escalar
1409---------
1410
1411
1412CREATE FUNCTION sales.iTotalVentas
1413(
1414@productoID INT
1415)
1416RETURNS TABLE
1417AS
1418RETURN
1419(
1420 SELECT *
1421 FROM Sales.SalesOrderDetail
1422 WHERE ProductID = @productoID
1423)
1424
1425------
1426SELECT *
1427FROM Sales.iTotalVentas (712) -- Funcion que trae todos los pedidos de el producto que le espificiamos ( FUNCION TIPO TABLA)
1428
1429
1430
1431------------
1432
1433
1434CREATE FUNCTION sales.iEmpleados
1435(
1436@formato char(1)
1437)
1438RETURNS @resultado TABLE
1439(
1440 empleadoId int, nombre varchar(100)
1441)
1442AS
1443BEGIN
1444
1445 IF (@formato = 'C')
1446 BEGIN
1447 INSERT INTO @resultado
1448 SELECT BusinessEntityID, FirstName
1449 FROM HumanResources.vEmployee
1450
1451 END ELSE BEGIN
1452 INSERT INTO @resultado
1453 SELECT BusinessEntityID, FirstName, + ' ' + LastName
1454 FROM HumanResources.vEmployee
1455
1456 END
1457 RETURN
1458END
1459
1460----------
1461select *
1462from sales.iEmpleados ('c')
1463----------
1464
1465
1466SELECT CAST(CAST (4 AS DECIMAL (6,2))/CAST(3 AS DECIMAL(6,2)) AS DECIMAL(6,2))
1467
1468
1469------------------------------------ LABORATORIO FUNCIONES ----------------------
1470
1471-------------------------------------------1----------------------------
1472
1473CREATE FUNCTION dbo.FN_PROMEDIO33()
1474RETURNS MONEY
1475AS
1476BEGIN
1477 DECLARE @promedio MONEY
1478
1479
1480 SELECT @promedio=AVG(ListPrice)
1481 FROM Production.Product
1482
1483 RETURN @promedio
1484end
1485go
1486
1487select dbo.FN_PROMEDIO33()
1488
1489------------------------------2------------------
1490
1491CREATE FUNCTION dbo.TotalVentas
1492
1493SELECT ProductID, LineTotal
1494FROM sales.SalesOrderDetail
1495
1496----------------------------------------------------- clase 5 --------------------------------
1497
1498
1499/*
1500Creacion de tablas
1501*/
1502IF OBJECT_ID ('dbo.tblPadre', 'U' ) IS NOT NULL
1503 DROP TABLE [dbo].[tblPadre]
1504GO
1505
1506CREATE TABLE tblPadre
1507 (
1508 intid int identity(1,1),
1509 vchTexto varchar (200),
1510 intEntero int,
1511 charTextoLargo char(8000)
1512 )
1513GO
1514
1515ALTER TABLE dbo.tblPadre ADD CONSTRAINT
1516 PK_tblPadre PRIMARY KEY CLUSTERED
1517 (
1518 intid
1519 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
1520GO
1521
1522/*
1523CARGA DE DATOS.
1524*/
1525DECLARE @COUNT INT=1
1526
1527WHILE @COUNT < 100
1528BEGIN
1529 INSERT INTO [dbo].[tblPadre]
1530 ([vchTexto]
1531 ,[intEntero]
1532 ,[charTextoLargo])
1533 VALUES
1534 ('TEXTO ' + CONVERT (CHAR, @COUNT)
1535 ,@COUNT
1536 ,'TEXTO ')
1537
1538 SET @COUNT=@COUNT+1
1539END
1540GO
1541
1542SELECT * FROM tblPadre
1543
1544-- bloqueos / lock -- EN DISTINTOS QUERYS
1545
1546--sesion 1, Bloqueadora-------------------------------------
1547BEGIN TRAN
1548UPDATE [dbo].[tblPadre]
1549SET [vchTexto] = 'TEXTO BLOQUEO'
1550WHERE intid = 1
1551--COMMIT
1552--ROLLBACK
1553------------------------------------------------------------
1554
1555--sesion 2, Bloqueada---------------------------------------
1556SELECT * FROM [tblPadre]
1557WHERE intid = 1
1558------------------------------------------------------------
1559
1560--sesion 3, no Bloqueada-------------------------------------
1561SELECT * FROM [tblPadre] WITH (NOLOCK) -- READ UNCOMMITED ES LO MIMSO, POR DEFAULT SQL USA READ COMMITED
1562WHERE intid = 1
1563------------------------------------------------------------
1564
1565--Trigger
1566
1567IF OBJECT_ID(N'dbo.Prueba',N'U') IS NOT NULL
1568BEGIN
1569 DROP TABLE dbo.Prueba;
1570END
1571
1572CREATE TABLE dbo.Prueba
1573(
1574Codigo INT,
1575Nombre VARCHAR(50)
1576)
1577
1578IF OBJECT_ID(N'dbo.CopiaPrueba',N'U') IS NOT NULL
1579BEGIN
1580 DROP TABLE dbo.CopiaPrueba;
1581END
1582
1583CREATE TABLE dbo.CopiaPrueba
1584(
1585Codigo INT,
1586Nombre VARCHAR(50)
1587)
1588
1589-- CREAR UN TRIGGER QUE SE DISPARA ANTE EL EVENTO INSERT
1590IF OBJECT_ID(N'dbo.TR_Prueba',N'TR') IS NOT NULL
1591BEGIN
1592 DROP TRIGGER dbo.TR_Prueba;
1593END
1594GO
1595
1596CREATE TRIGGER dbo.TR_Prueba ON dbo.Prueba
1597AFTER INSERT AS
1598BEGIN
1599 INSERT INTO dbo.CopiaPrueba
1600 SELECT * FROM inserted; -- TABLA VIRTUAL USADA POR TRIGGER " INSERTED "
1601END
1602
1603INSERT INTO dbo.Prueba
1604VALUES (1,'GABRIEL'),
1605 (2,'CARLOS'),
1606 (3,'JUAN')
1607
1608SELECT * FROM dbo.Prueba
1609SELECT * FROM dbo.CopiaPrueba -- un trigger uqe hace que todo lo insertado en dbo.prueba, se insterte en dbo.copiaprueba
1610
1611
1612--CREAR UN TRIGGER QUE SE DISPARA ANTE UN EVENTO UPDATE
1613
1614IF OBJECT_ID(N'dbo.TR_Prueba',N'TR') IS NOT NULL
1615BEGIN
1616 DROP TRIGGER dbo.TR_Prueba;
1617END
1618GO
1619
1620CREATE TRIGGER dbo.TR_Prueba ON dbo.Prueba
1621AFTER UPDATE AS
1622BEGIN
1623
1624 UPDATE P
1625 SET p.Nombre = i.nombre
1626 FROM dbo.CopiaPrueba P
1627 INNER JOIN inserted I
1628 on (p.Codigo = i.Codigo)
1629
1630END
1631
1632--SELECT * FROM dbo.Prueba
1633UPDATE dbo.Prueba
1634 SET Nombre = 'PEDRO'
1635 WHERE Codigo = 1
1636
1637SELECT * FROM dbo.Prueba
1638SELECT * FROM dbo.CopiaPrueba -- triggeer que updatea ambas tablas
1639
1640-CREAR UN TRIGGER QUE SE DISPARA ANTE UN EVENTO DELETE
1641IF OBJECT_ID(N'dbo.TR_Prueba',N'TR') IS NOT NULL
1642BEGIN
1643 DROP TRIGGER dbo.TR_Prueba;
1644END
1645GO
1646
1647CREATE TRIGGER dbo.TR_Prueba ON dbo.Prueba
1648AFTER DELETE AS
1649BEGIN
1650
1651 DELETE P
1652 FROM dbo.CopiaPrueba P
1653 INNER JOIN deleted D -- tabla temporal de trigger " DELETED "
1654 on (P.Codigo = D.Codigo)
1655END
1656
1657SELECT * FROM dbo.Prueba WHERE NOMBRE LIKE '%PEDRO%'
1658SELECT * FROM dbo.CopiaPrueba WHERE NOMBRE LIKE '%PEDRO%'
1659
1660DELETE FROM dbo.Prueba
1661WHERE NOMBRE LIKE '%PEDRO%'
1662
1663SELECT * FROM dbo.Prueba
1664SELECT * FROM dbo.CopiaPrueba -- TRIGGER QUE BORRA DE AMBAS 2 TABLAS
1665
1666------------ triger con cursor----------
1667-- Esta sería una posible estructura de la tabla original de configuración
1668CREATE TABLE [dbo].[ConfigurationTable](
1669 [KeyID] [int] IDENTITY(1,1) NOT NULL,
1670 [ParameterCode] [nvarchar](30)NOT NULL,
1671 [ParameterDescription] [nvarchar](100) NULL,
1672 [ParameterValue] [nvarchar](30) NULL
1673 PRIMARY KEY (ParameterCode) ON [PRIMARY])
1674
1675-- Nosotros querríamos saber el historial de cambios. Crearíamos otra tabla con una estrucutra similar...
1676CREATE TABLE [dbo].[ConfigurationTable_OldValues](
1677 [KeyID] [int] NOT NULL,
1678 [ParameterCode] [nvarchar](30)NOT NULL,
1679 [ParameterDescription] [nvarchar](100) NULL,
1680 [NewParameterValue] [nvarchar](30) NULL,
1681 [OldParameterValue] [nvarchar](30) NULL,
1682 [RowModifier] [nvarchar](300),
1683 [RowModifiedDate] datetime)
1684
1685
1686-- Creamos el trigger para controlar la actualización de la tabla original
1687--e insertar los datos en la tabla de valores históricos ([ConfigurationTable_OldValues])
1688
1689
1690 CREATE TRIGGER ConfigurationTable_AfterUpdate ON ConfigurationTable
1691 AFTER UPDATE
1692 AS
1693
1694 BEGIN
1695
1696 SET NOCOUNT ON
1697
1698 DECLARE @User nvarchar(30)
1699 DECLARE @SysDate datetime
1700
1701 DECLARE cInserted CURSOR FOR
1702 SELECT [KeyID],
1703 [ParameterCode],
1704 [ParameterDescription],
1705 [ParameterValue]
1706 FROM inserted
1707
1708 DECLARE @keyId int
1709DECLARE @parametercode [nvarchar](30)
1710DECLARE @parameterdescription [nvarchar](100)
1711DECLARE @parametervalue [nvarchar](30)
1712
1713SELECT @SysDate = GETDATE()
1714SELECT @User = SUSER_NAME()
1715
1716OPEN cInserted
1717FETCH NEXT FROM cInserted
1718INTO @keyId , @parametercode , @parameterdescription , @parametervalue
1719
1720WHILE @@FETCH_STATUS = 0
1721BEGIN
1722
1723 INSERT INTO [ConfigurationTable_OldValues]
1724 ([KeyID],
1725 [ParameterCode],
1726 [ParameterDescription],
1727 [NewParameterValue],
1728 [OldParameterValue] ,
1729 [RowModifier] ,
1730 [RowModifiedDate]
1731 )
1732 SELECT
1733 @keyId,
1734 @parametercode,
1735 @parameterdescription,
1736 @parametervalue,
1737 d.[ParameterValue] ,
1738 @User,
1739 @sysdate
1740 FROM deleted d
1741 WHERE d.[ParameterCode] = @parametercode
1742
1743FETCH NEXT FROM cInserted
1744INTO @keyId , @parametercode , @parameterdescription , @parametervalue
1745END
1746
1747CLOSE cInserted
1748
1749DEALLOCATE cInserted
1750
1751END
1752
1753- Hacemos un insert de prueba y un update para ver los resultados
1754
1755INSERT INTO [dbo].[ConfigurationTable]
1756 ([ParameterCode]
1757 ,[ParameterDescription]
1758 ,[ParameterValue])
1759VALUES
1760 ('Processes3'
1761 ,'Numero de procesos'
1762 ,'10'
1763 )
1764GO
1765
1766UPDATE [dbo].[ConfigurationTable]
1767SET [ParameterValue] = '30'
1768WHERE [ParameterCode] = 'Processes'
1769GO
1770
1771
1772select * from [dbo].[ConfigurationTable]
1773select * from [dbo].[ConfigurationTable_OldValues] -- trigger que muestra el update en una tabla de valores viejos
1774
1775
1776
1777---------------------------------- catch try erores------------
1778-- ejem plo 1
1779BEGIN TRY
1780 -- Generamos un error al dividir por cero
1781 SELECT 1 / 0 AS Error
1782END TRY
1783BEGIN CATCH
1784 SELECT
1785 ERROR_NUMBER() AS ErrorNumber,
1786 ERROR_STATE() AS ErrorState,
1787 ERROR_SEVERITY() AS ErrorSeverity,
1788 ERROR_PROCEDURE() AS ErrorProcedure,
1789 ERROR_LINE() AS ErrorLine,
1790 ERROR_MESSAGE() AS ErrorMessage
1791END CATCH
1792GO
1793
1794-- ejemplo 2
1795
1796IF OBJECT_ID (N'dbo.errores', N'U') IS NOT NULL
1797BEGIN
1798 DROP TABLE dbo.errores
1799END
1800
1801CREATE TABLE dbo.errores
1802 (ErrorID INT IDENTITY(1, 1),
1803 UserName VARCHAR(100),
1804 ErrorNumber INT,
1805 ErrorState INT,
1806 ErrorSeverity INT,
1807 ErrorLine INT,
1808 ErrorProcedure VARCHAR(MAX),
1809 ErrorMessage VARCHAR(MAX),
1810 ErrorDateTime DATETIME)
1811GO
1812
1813BEGIN TRY
1814 -- Generamos un error al dividir por cero
1815 SELECT 1 / 0 AS Error
1816END TRY
1817BEGIN CATCH
1818 INSERT INTO dbo.errores
1819 VALUES (
1820 SUSER_SNAME(),
1821 ERROR_NUMBER(),
1822 ERROR_STATE(),
1823 ERROR_SEVERITY(),
1824 ERROR_LINE(),
1825 ERROR_PROCEDURE(),
1826 ERROR_MESSAGE(),
1827 GETDATE()
1828 )
1829END CATCH
1830GO
1831
1832SELECT * FROM dbo.errores
1833
1834--EJEMPLO 3
1835--CREAR NUESTROS PROPIOS MENSAJES PERSONALIZADOS DE ERROR
1836BEGIN TRY
1837 -- Generamos un error al dividir por cero
1838 DECLARE @a INT = 1
1839 DECLARE @b INT = 0
1840
1841 IF (@b = 0)
1842 BEGIN
1843 RAISERROR('El denominador no puede ser cero.', 11, 1)
1844 END
1845
1846 SELECT @a / @b AS Error
1847
1848END TRY
1849BEGIN CATCH
1850 INSERT INTO dbo.errores
1851 VALUES (
1852 SUSER_SNAME(),
1853 ERROR_NUMBER(),
1854 ERROR_STATE(),
1855 ERROR_SEVERITY(),
1856 ERROR_LINE(),
1857 ERROR_PROCEDURE(),
1858 ERROR_MESSAGE(),
1859 GETDATE()
1860 )
1861
1862 DECLARE @Message varchar(MAX) = ERROR_MESSAGE(),
1863 @Severity int = ERROR_SEVERITY(),
1864 @State smallint = ERROR_STATE(),
1865 @eRRorNumber int = ERROR_NUMBER()
1866
1867
1868 BEGIN
1869 RAISERROR (@Message, @Severity, @State)
1870 --THROW @eRRorNumber ,@Message,@State
1871 END
1872END CATCH
1873GO
1874
1875--EJEMPLO4
1876--VARIABLE DE SISTEMA @@ERROR
1877--En versiones anteriores a SQL Server 2005, no estaban disponibles las instrucciones TRY CATCH.
1878--En estas versiones se controlaban los errores utilizando la variable global de sistema @@ERROR
1879
1880-- Generamos un error al dividir por cero
1881DECLARE @a INT = 1
1882DECLARE @b INT = 0
1883DECLARE @resultado INT
1884
1885-- Esta linea provoca un error de division por 0
1886SET @resultado = @a/@b
1887
1888IF @@ERROR = 0
1889BEGIN
1890 PRINT 'No hay error'
1891
1892END ELSE
1893BEGIN
1894 PRINT 'Hay error'
1895END
1896
1897
1898
1899----------- pviot unpivot ------------
1900
1901--EJERCICIO 2
1902IF OBJECT_ID (N'tempdb..#pvt', N'U') IS NOT NULL
1903 DROP TABLE #pvt;
1904
1905CREATE TABLE #pvt ( ID INT, Monto MONEY, Anio INT);
1906
1907DECLARE @Id INT;
1908DECLARE @Monto MONEY;
1909DECLARE @Cantidad INT;
1910DECLARE @Anio INT;
1911
1912DECLARE Cursor_Titulos CURSOR FOR
1913
1914 SELECT SalesOrderID
1915 ,OrderQty
1916 ,LineTotal
1917 ,YEAR(ModifiedDate)
1918 FROM Sales.SalesOrderDetail
1919
1920OPEN Cursor_Titulos
1921FETCH NEXT FROM Cursor_Titulos INTO @Id, @Cantidad, @Monto, @Anio
1922WHILE @@FETCH_STATUS = 0
1923BEGIN
1924 IF @Cantidad>10
1925 BEGIN
1926 INSERT INTO #pvt
1927 SELECT @Id, @Monto, @Anio
1928 END
1929 FETCH NEXT FROM Cursor_Titulos INTO @Id, @Cantidad, @Monto, @Anio
1930END
1931
1932CLOSE Cursor_Titulos;
1933DEALLOCATE Cursor_Titulos;
1934SELECT * FROM #pvt
1935
1936SELECT TOP 10 ID, [2010], [2011], [2012], [2013], [2014], [2015]
1937FROM
1938(
1939 SELECT [ID],[Monto],[Anio]
1940 FROM [#pvt]
1941) AS SOURCE
1942PIVOT
1943(
1944SUM([Monto])
1945FOR [Anio] IN ( [2010], [2011], [2012], [2013], [2014], [2015] )
1946) AS REP
1947
1948--UNPIVOT--
1949IF OBJECT_ID (N'dbo.prueba', N'U') IS NOT NULL
1950 DROP TABLE dbo.prueba
1951
1952CREATE TABLE dbo.prueba (id int identity (1,1) not null,
1953 columna1 tinyint not null,
1954 columna2 tinyint not null
1955 )
1956
1957INSERT INTO dbo.prueba (columna1,columna2 ) values (1,1)
1958INSERT INTO dbo.prueba (columna1,columna2 ) values (1,0)
1959INSERT INTO dbo.prueba (columna1,columna2 ) values (0,1)
1960INSERT INTO dbo.prueba (columna1,columna2 ) values (0,0)
1961
1962select * from dbo.prueba
1963
1964SELECT id, valor
1965FROM
1966 (SELECT id,ISNULL(columna1,0) AS columna1, ISNULL(columna2,0) AS columna2
1967 FROM dbo.prueba) p
1968 UNPIVOT
1969 (
1970 valor FOR columna IN (columna1,columna2)
1971 ) as u;
1972GO
1973
1974--SELECT OVER
1975
1976--FUNCIONES DE CATEGORÍA
1977--ROW_NUMBER
1978IF OBJECT_ID (N'dbo.prueba', N'U') IS NOT NULL
1979 DROP TABLE dbo.prueba
1980
1981CREATE TABLE dbo.prueba (Nombre varchar(100) not null,
1982 Edad tinyint not null,
1983 Sexo CHAR(1) not null
1984 )
1985
1986INSERT INTO dbo.prueba (Nombre,Edad,Sexo) VALUES ('Andres',26,'V')
1987INSERT INTO dbo.prueba (Nombre,Edad,Sexo) VALUES ('Eva',42,'M')
1988INSERT INTO dbo.prueba (Nombre,Edad,Sexo) VALUES ('Fabricio',18,'V')
1989INSERT INTO dbo.prueba (Nombre,Edad,Sexo) VALUES ('Carlos',54,'V')
1990INSERT INTO dbo.prueba (Nombre,Edad,Sexo) VALUES ('Maria',32,'M')
1991INSERT INTO dbo.prueba (Nombre,Edad,Sexo) VALUES ('Pedro',20,'V')
1992INSERT INTO dbo.prueba (Nombre,Edad,Sexo) VALUES ('Facundo',24,'V')
1993
1994
1995
1996--¿Cómo podríamos obtener el nombre de la segunda persona con más edad de entre ellas?
1997select * from dbo.prueba order by edad desc
1998
1999--Mediante subconsulta:
2000SELECT *
2001FROM
2002(
2003 SELECT ROW_NUMBER() OVER (ORDER BY Edad DESC) AS Orden,
2004 Nombre,
2005 Edad
2006 FROM dbo.prueba
2007) T1
2008WHERE Orden = 2
2009
2010--Mediante CTE:
2011;WITH T1 AS
2012(
2013 SELECT ROW_NUMBER() OVER (ORDER BY Edad DESC) AS Orden, Nombre, Edad
2014 FROM dbo.prueba
2015)
2016SELECT *
2017FROM T1
2018WHERE Orden = 2
2019
2020--¿Cómo podríamos obtener el nombre de la segunda persona con más edad de cada sexo?
2021select * from dbo.prueba order by edad DESC, sexo
2022
2023SELECT *
2024FROM
2025(
2026 SELECT ROW_NUMBER() OVER (PARTITION BY Sexo ORDER BY Edad DESC) AS Orden, Nombre, Edad, Sexo
2027 FROM dbo.prueba
2028) T1
2029WHERE Orden = 2
2030
2031--------------------------------------------------
2032--FUNCION DE AGRUPAMIENTO
2033---------------------------------------------------
2034IF OBJECT_ID (N'dbo.empleado', N'U') IS NOT NULL
2035BEGIN
2036 DROP TABLE dbo.empleado
2037END
2038
2039CREATE TABLE dbo. empleado (
2040empid INT NOT NULL,
2041departamento VARCHAR(100) NOT NULL,
2042salario INT NOT NULL,
2043edad TINYINT NOT NULL);
2044
2045INSERT INTO empleado (empid,departamento,salario,edad) VALUES (1,'ventas',3000,24);
2046INSERT INTO empleado (empid,departamento,salario,edad) VALUES (2,'ventas',3200,26);
2047INSERT INTO empleado (empid,departamento,salario,edad) VALUES (3,'ventas',3500,35);
2048INSERT INTO empleado (empid,departamento,salario,edad) VALUES (4,'distribucion',2000,22);
2049INSERT INTO empleado (empid,departamento,salario,edad) VALUES (5,'distribucion',2100,42);
2050INSERT INTO empleado (empid,departamento,salario,edad) VALUES (6,'distribucion',2400,40);
2051INSERT INTO empleado (empid,departamento,salario,edad) VALUES (7,'produccion',2800,41);
2052INSERT INTO empleado (empid,departamento,salario,edad) VALUES (8,'produccion',2400,29);
2053INSERT INTO empleado (empid,departamento,salario,edad) VALUES (9,'produccion',1900,19);
2054INSERT INTO empleado (empid,departamento,salario,edad) VALUES (10,'produccion',3000,45);
2055INSERT INTO empleado (empid,departamento,salario,edad) VALUES (11,'produccion',3000,40);
2056
2057SELECT empid,
2058 departamento,
2059 salario,
2060 edad
2061FROM empleado;
2062
2063--OBTENER SALARIO MEDIO DEL DEPARTAMENTO
2064SELECT empid,
2065 departamento,
2066 salario,
2067 edad,
2068 AVG(salario) OVER (PARTITION BY departamento) AS salario_medio
2069 FROM empleado ;
2070
2071--OBTENER SALARIO Y EDAD MEDIA X DEPARTAMENTO
2072SELECT empid,
2073 departamento,
2074 salario,
2075 edad,
2076 AVG(salario) OVER (PARTITION BY departamento) AS salario_medio,
2077 AVG(edad) OVER (PARTITION BY departamento) AS ed_media
2078 FROM empleado ;
2079
2080SELECT empid,
2081 departamento,
2082 salario,
2083 edad,
2084 AVG(salario) OVER (PARTITION BY departamento) AS salario_medio,
2085 AVG(edad) OVER (PARTITION BY departamento) AS ed_media
2086 FROM empleado ;
2087
2088-- FUNCION DE CATEGORIA
2089 SELECT empid,
2090 departamento,
2091 salario,
2092 edad,
2093 DENSE_RANK() OVER (PARTITION BY departamento ORDER BY SALARIO) AS salario_pos,
2094 DENSE_RANK() OVER (PARTITION BY departamento ORDER BY EDAD) AS edad_pos
2095 FROM empleado ;
2096
2097
2098SELECT TerritoryID Territorio,
2099 AVG(SalesYTD) AS Promedio
2100FROM Sales.SalesPerson
2101WHERE TerritoryID < 5
2102GROUP BY TerritoryID
2103
2104
2105SELECT TerritoryID Territorio,
2106 SalesYTD AS TotalVentas,
2107 AVG(SalesYTD) OVER ( PARTITION BY TerritoryID ORDER BY TerritoryID )AS Promedio
2108FROM Sales.SalesPerson
2109WHERE TerritoryID < 5
2110
2111--FUNCION ANALITICA LEAD
2112SELECT BusinessEntityID, YEAR(QuotaDate), SalesQuota,*
2113FROM Sales.SalesPersonQuotaHistory
2114WHERE BusinessEntityID = 275 AND
2115 YEAR(QuotaDate) IN ('2011','2012')
2116ORDER BY QuotaDate
2117
2118SELECT BusinessEntityID,
2119 YEAR(QuotaDate) AS Anio,
2120 SalesQuota AS Cuota,
2121 LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS ProximaCuota
2122FROM Sales.SalesPersonQuotaHistory
2123WHERE BusinessEntityID = 275 AND
2124 YEAR(QuotaDate) IN ('2011','2012');