· 6 years ago · Nov 16, 2019, 09:58 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'