CLASE 3 SQL

use AdventureWorks2016CTP3
go

SELECT ProductID, NAME 'Nombre Producto' , MakeFlag 'FLAG' , ListPrice 'PRECIO'
FROM Production.Product
WHERE ListPrice > 0 AND ListPrice < 40
ORDER BY ListPrice DESC

SELECT ProductID, NAME 'Nombre Producto' , MakeFlag 'FLAG' , ListPrice 'PRECIO' , WeightUnitMeasureCode 'CODGIO PESO'
FROM Production.Product
WHERE (WeightUnitMeasureCode ='G'
      OR WeightUnitMeasureCode ='LB') AND (ProductID = 795 OR ProductID = 818)

SELECT P.Productid, P.ListPrice, P.COLOR
FROM Production.Product P
WHERE (P.ListPrice < 60 AND Color='Yellow') OR (p.ListPrice>231 and Color='Silver')

SELECT P.Productid, P.ListPrice, P.COLOR
FROM Production.Product P
WHERE P.Color is NOT NULL

SELECT P.ProductID, P.name,
       P.ListPrice
FROM Production.Product P
WHERE P.ListPrice BETWEEN 0 AND 10 -- WHERE P.ListPrice >= 0 and P.ListPirce <=10 SON LO MISMO
ORDER BY p.ListPrice

SELECT PE.LastName , PE.FirstName , PE.MiddleName
FROM Person.Person PE
--WHERE PE.MiddleName= 'A' OR PE.MiddleName = 'D' OR PE.MiddleName = 'R' ES LO MISMO QUE IN
WHERE PE.MiddleName IN ('A' ,'D', 'R')
ORDER BY PE.MiddleName

/* LABORATORIO RECUPERO DE DATOS */

--1 distinct
SELECT DISTINCT ProductID
FROM Sales.SalesOrderDetail

--1 union
SELECT ProductID
FROM Sales.SalesOrderDetail
UNION ALL
SELECT ProductID
FROM Production.WorkOrder
--2
SELECT ProductID
FROM Sales.SalesOrderDetail
UNION
SELECT ProductID
FROM Production.WorkOrder

--1 Case
SELECT BusinessEntityId,
 Gender = CASE WHEN Gender='M' then 'Masculino'
     WHEN Gender='F' then 'Femenino'
     END
FROM HumanResources.Employee
--2

SELECT  [BusinessEntityID]
        ,[SalariedFlag]
FROM [HumanResources].[Employee]
ORDER BY
        CASE [SalariedFlag] WHEN 1 THEN [BusinessEntityID] END DESC
        ,CASE WHEN [SalariedFlag] = 0 THEN [BusinessEntityID] END;





/* LABORATORIO OPERADORES */

--1
SELECT HR.BusinessEntityID, HR.VacationHours
FROM HumanResources.Employee HR
WHERE HR.VacationHours > 90

--2

SELECT P.Name, P.ListPrice, P.ListPrice * 1.21 as 'PrecioConIva'
FROM Production.Product P
WHERE p.ListPrice <> 0

--3

SELECT P.ProductID, P.Name, P.ListPrice
FROM Production.Product P
WHERE ProductID between 776 and 778

--4

SELECT P.FirstName, P.LastName
FROM Person.Person P
WHERE P.LastName = 'johnson'

--5

SELECT P.Name, P.ListPrice, P.Color
FROM Production.Product P
WHERE (P.ListPrice < 150 AND P.Color='RED') OR (P.ListPrice > 500 AND P.Color='Black')

--6

SELECT HR.BusinessEntityID, HR.HireDate, HR.VacationHours, YEAR(HR.HireDate) AS 'Año'
FROM HumanResources.Employee HR
WHERE YEAR(HR.HireDate) >2000

--7

SELECT P.Name, P.ProductNumber, P.ListPrice, P.ListPrice * 1.10 as 'PrecioConAumento', GETDATE() as 'Fecha'
FROM Production.Product P
WHERE P.SellStartDate < GETDATE() AND p.ListPrice > 0

-- LABORATORIO CRITERIOS DE SELECCION --

-- LIKE --

--1
SELECT P.Name, P.ListPrice, P.Color
FROM Production.Product P
WHERE P.ListPrice > 100 AND P.Name LIKE '%seat%'

--2

Select P.Name
FROM Production.Product p
where P.Name LIKE '%Mountain bike%'

--3

SELECT PP.LastName, PP.FirstName
FROM Person.Person PP
WHERE PP.FirstName LIKE 'Y%'

--4

SELECT PP.LastName, PP.FirstName
FROM Person.Person PP
WHERE PP.LastName LIKE '_S%'

--5

SELECT CONCAT (PP.LastName, ' ', PP.FirstName) as 'Nombre'
FROM Person.Person PP
WHERE PP.LastName like '%ez'

--6

Select *
FROM Production.Product p
WHERE P.Name LIKE '%[0-9]'

--7

SELECT *
FROM Person.Person PP
WHERE pp.FirstName like '[C-c]_[^d-g][j-w]%'

-- null --

--2

SELECT isnull(weight,0) as 'Weight' -- REMPLAZA EL NULL POR EL 0 PARA MOSTRAR, NO EN LA TABLE.
FROM Production.Product
Where Weight is null

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

SELECT distinct color
FROM Production.Product -- muestra los distintos de un campo

SELECT BusinessEntityID
FROM Sales.SalesPerson
UNION
SELECT BusinessEntityID
FROM HumanResources.Employee
ORDER BY BusinessEntityID     -------- UNION JUNTA AMBOS Y ELMINIA DUPLICADOS ------ AL USAR UNION ALL, NO ELIMINA DUPLICADOS

--CASE
SELECT  ProductLine,
        Category = CASE ProductLine
                        WHEN 'R' THEN 'Road'
                        WHEN 'M' THEN 'Mountain' -- si el productline es R M o T lo cambia por lo marcado, si no pone not for sale.
                        WHEN 'T' THEN 'Touring'
                        ELSE 'Not for sale'
                    END
FROM Production.Product;

SELECT
        CASE
            WHEN EmailPromotion=0 then 'No tiene mail'
            WHEN EmailPromotion=1 then 'Tiene mail'
            WHEN EmailPromotion=2 then 'Tiene muchos mail' --  OTRA FORMA DE HACER LO DE ARRIBA
            ELSE 'Desconocido'
        END Mail
FROM Person.Person;

SELECT   LastName
        ,TerritoryName
        ,CountryRegionName
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL
ORDER BY CASE CountryRegionName
            WHEN 'United States' THEN TerritoryName -- si es usa, te dice la region, si no, te dice el nombre del pais. EL ELSE ES SITUACIONAL
            ELSE CountryRegionName END;

--Función COUNT
SELECT COUNT(*) AS Cantidad
FROM HumanResources.EmployeeDepartmentHistory;

--Función MAX
SELECT MAX(ListPrice) AS Maximo
FROM Production.Product;

--Función MIN
SELECT MIN(ListPrice) AS Minimo
FROM Production.Product;

--Función SUM
SELECT SUM(ListPrice) AS Total
FROM Production.Product;

--Función AVG
SELECT AVG(ListPrice) AS Promedio
FROM Production.Product;

--GROUP BY
SELECT  ProductID,
        MAX(LineTotal) as Maximo
    FROM Sales.SalesOrderDetail
    WHERE   ProductID>995
GROUP BY    ProductID;

--HAVING
SELECT  ProductID
        ,MAX(LineTotal) as Maximo
FROM    Sales.SalesOrderDetail
WHERE   ProductID>995
GROUP BY    ProductID
HAVING  MAX(LineTotal)>3000;

--ROLLUP
SELECT  ProductID
        ,MAX(LineTotal) as Maximo
FROM    Sales.SalesOrderDetail
WHERE   ProductID>995
GROUP BY    ProductID WITH ROLLUP
HAVING  MAX(LineTotal)>3000;

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

USE AdventureWorks2016CTP3
GO

SELECT TOP 10 *
FROM Person.Person
SELECT TOP 10 *
FROM Person.PersonPhone

SELECT P.BusinessEntityID as 'IDPERSONA',
       P.FirstName 'NOMBRE',
       PP.PhoneNumber 'NUMERO_TELOFONO'
FROM Person.Person p
INNER JOIN Person.PersonPhone PP
   ON (PP.BusinessEntityID = p.BusinessEntityID)
WHERE p.BusinessEntityID < 10 -- INNER JOIN, SOBRE BUSINESSENTITYID, TIENEN QUE ESTAR EN LAS 2 TABLAS

SELECT TOP 10 *
FROM Production.Product
SELECT TOP 10*
FROM Sales.SalesOrderDetail

SELECT P.ProductID 'PRODUCTO_P',
       S.ProductID 'PRODUCTO_S'
FROM Production.Product P
LEFT JOIN Sales.SalesOrderDetail S
    ON (S.ProductID = P.ProductID)
WHERE P.ProductID IN (897,389) -- LEFT JOIN, TODOS LOS QUE ESTAN EN LA TABLA IZQUIERDA
                               -- MAS LA INTERSECCION DE LA OTRA(RIGHT ES LO MISMO, PERO TODOS DE LA DERECHA)

------------------------------------------------ TEORIA CLASE 3 --------------------------------------------------

USE AdventureWorks2016CTP3
GO

CREATE TABLE dbo.tblA (idTablaA int not null, vchDescA varchar(10) not null)
CREATE TABLE dbo.tblB (idTablaB int not null, idTablaA int null, intValorB int not null)

insert into dbo.tblA (idTablaA, vchDescA) values (1,'A')
insert into dbo.tblA (idTablaA, vchDescA) values (2,'B')
insert into dbo.tblA (idTablaA, vchDescA) values (3,'C')
insert into dbo.tblA (idTablaA, vchDescA) values (4,'D')

insert into dbo.tblB (idTablaB, idTablaA, intValorB) values (1,1,10)
insert into dbo.tblB (idTablaB, idTablaA, intValorB) values (2,1,20)
insert into dbo.tblB (idTablaB, idTablaA, intValorB) values (3,4,40)
insert into dbo.tblB (idTablaB, idTablaA, intValorB) values (4,2,50)
insert into dbo.tblB (idTablaB, idTablaA, intValorB) values (5,NULL,100)

SELECT * FROM dbo.tblA
SELECT * FROM dbo.tblB

--INNER JOIN
SELECT A.idTablaA, A.idTablaA, b.idTablaB, b.idTablaA, B.intValorB
FROM dbo.tblA A
INNER JOIN dbo.tblB B
    ON (A.idTablaA = B.idTablaA)


--left JOIN
SELECT A.idTablaA, A.idTablaA, b.idTablaB, b.idTablaA, B.intValorB
FROM dbo.tblA A
left JOIN dbo.tblB B
    ON (A.idTablaA = B.idTablaA)

--RIGHT JOIN
    SELECT *
    FROM dbo.tblA A
    RIGHT JOIN dbo.tblB B
        ON (A.idTablaA = B.idTablaA)

--FULL JOIN
SELECT *
FROM dbo.tblA A
FULL JOIN dbo.tblB B
    ON (A.idTablaA = B.idTablaA)

--CROSS JOIN
SELECT *
FROM dbo.tblA A
CROSS JOIN dbo.tblB B -- PRODUCTO CARTESEANO


--SELF JOIN
IF OBJECT_ID('dbo.Empleados','U') IS NOT NULL
BEGIN
    DROP TABLE dbo.Empleados
END

CREATE TABLE Empleados
(
    Codigo INT,
    Nombre VARCHAR(50),
    Puesto VARCHAR(50),
    Supervisor INT
);

INSERT INTO dbo.Empleados
VALUES
 (1, 'Juan','Lider de Proyecto', 3)
,(2, 'Pedro','Desarrollador', 1)
,(3, 'Maria','Jefa', NULL)
,(4, 'Martin','Diseñador', 1);

SELECT  p2.Codigo,
        p2.Nombre,
        p2.Puesto,
        p1.Nombre AS Supervisor
FROM Empleados p1
INNER JOIN Empleados p2
    ON p1.Codigo=p2.Supervisor
WHERE p2.Supervisor IS NOT NULL;

SELECT  P1.*,P2.*
FROM Empleados p1
INNER JOIN Empleados p2
    ON p1.Codigo=p2.Supervisor
WHERE p2.Supervisor IS NOT NULL;

-- TABLAS TEMPORALES

CREATE TABLE #tblA (
                    idTablaA int not null
                    , vchDescA varchar(10) not null
                    )                                -- TABLA TEMP LOCAL -- SOLO SIRVE PARA LA SESION (QUERY) ACTUAL

CREATE TABLE ##tblB (idTablaB int not null
                     , idTablaA int null,
                     intValorB int not null
                     )                               -- TABLA TEMP GLOBAL -- SIRVE PARA CUALQUIER SESION (QUERY) ACTUAL

                     -- SI SE CIERRA LA QUERY, DESAPARECEN LAS TABLAS TEMP LOCALES Y GLOBALES.

SP_HELP 'HumanResources.Employee' -- brinda informacion de la tabla


-- LABORATORIO RELACION Y CONJUNTOS --

-- JOINS --

-- 1 --

SELECT *
FROM HumanResources.Employee HR
INNER JOIN Sales.SalesPerson S
ON (HR.BusinessEntityID = S.BusinessEntityID)

-- 2 --

SELECT HR.BusinessEntityID, P.FirstName, P.LastName
FROM HumanResources.Employee HR
INNER JOIN Person.Person P
ON (HR.BusinessEntityID = P.BusinessEntityID)
ORDER BY p.LastName, P.FirstName

-- 3 --

SELECT HR.LoginID, S.TerritoryID, S.Bonus, S.BusinessEntityID
FROM HumanResources.Employee HR
INNER JOIN Sales.SalesPerson S ON (S.BusinessEntityID = HR.BusinessEntityID)

-- 4 --

SELECT PP.Name , PP.ProductCategoryID
FROM Production.Product P
INNER JOIN Production.ProductSubcategory PP ON (P.ProductSubcategoryID = PP.ProductSubcategoryID)
WHERE PP.Name = 'Wheels'

-- 5 --

SELECT P.*, PS.Name
FROM Production.Product P
INNER JOIN Production.ProductSubcategory PS ON (PS.ProductSubcategoryID = P.ProductSubcategoryID)
WHERE P.Name NOT LIKE '%bike%'

-- 6 --

SELECT distinct PP.ProductID, pp.Name, pp.ListPrice, sd.UnitPrice
FROM Sales.SalesOrderDetail SD
INNER JOIN Production.Product PP ON (PP.ProductID = SD.ProductID)
WHERE SD.UnitPrice < pp.ListPrice
order by pp.Name

-- 7 --

SELECT p1.ProductID, p1.name, p1.ListPrice,
       p2.ProductID, p2.Name, p2.ListPrice
FROM Production.Product P1
inner join Production.Product P2 on (P2.ListPrice = P1.ListPrice)
where p1.ProductID > p2.ProductID
order by P1.ListPrice desc

-- 8 --

SELECT P.Name 'NOMBRE_PRODUCTO' , v.Name 'PROVEEDOR'
FROM Production.Product P
INNER JOIN Purchasing.ProductVendor PV ON (PV.ProductID = p.ProductID)
INNER JOIN Purchasing.Vendor V on (PV.BusinessEntityID = v.BusinessEntityID)
where p.ProductSubcategoryID = 15
order by V.Name

-- 9 --

SELECT p.FirstName, p.LastName, ISNULL(HR.LoginID, ' SIN LOGIN' )
FROM Person.Person P
left join HumanResources.Employee hr ON (hr.BusinessEntityID = p.BusinessEntityID)


-- LABORATORIO TABLAS TEMPORALES --

-- 1 --

select P.Name, P.Color, p.ListPrice
into #productos
from Production.Product P

select * from #productos

-- 2 --

SELECT BusinessEntityID, FirstName, LastName
into dbo.personas33
FROM Person.Person
where 1=2 ---- SE USA EL 1=2 PARA CREAR ALGO IMPOSIBLE, CLONANDO LA TABLA SIN NINGUN REGISTRO


--3)Eliminar si existe la tabla Productos
--tablas: Productos
IF OBJECT_ID (N'tempdb..#Productos', N'U') IS NOT NULL
    DROP TABLE #Productos;
GO

--4)Eliminar si existe la tabla Personas
--tablas: Personas
IF OBJECT_ID (N'tempdb..#Personas', N'U') IS NOT NULL
    DROP TABLE #Personas;
GO

--5)Crear una CTE con las órdenes de venta

--tablas: Sales.SalesOrderHeader
--campos: SalesPersonID, SalesOrderID, OrderDate

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
(
    SELECT  SalesPersonID
            ,SalesOrderID
            ,YEAR(OrderDate) AS Anio
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
SELECT SalesPersonID, SalesOrderID, SalesYear
FROM [Sales_CTE]


--------------------------- 4------------------------------------

SELECT  ProductID,
        ListPrice,
        (SELECT AVG(ListPrice)FROM Production.Product) AS 'PRECIO-PROMEDIO'
FROM Production.Product;


--SUBCONSULTA FROM
SELECT  pp.ProductID,
        pp.ListPrice,
        x.promedio
FROM Production.Product pp
INNER JOIN (
            SELECT ProductID,
                    AVG(LineTotal) promedio
                    FROM Sales.SalesOrderDetail
                    GROUP BY ProductID

            ) x
    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


--Subconsultas correlacionadas
SELECT  p1.ProductSubcategoryID,
        p1.ProductID,
        p1.ListPrice
    FROM Production.Product p1
    WHERE ListPrice =   (
                        SELECT MIN (ListPrice)
                            FROM Production.Product p2
                            WHERE p2.ProductSubcategoryID = p1.ProductSubcategoryID
                        )
    ORDER BY p1.ProductSubcategoryID; -- PRECIO MINIMO DE LISTA DE CADA SUBCATEGORIA Y PRODUCTID

------------------------------------------------------------- CLASE 4 ---------------------------------


-------------------------------------------------------------------------------------------------------------
--                                      SUBCONSULTAS CON [ANY|SOME - ALL]
-------------------------------------------------------------------------------------------------------------
CREATE TABLE _A (ID INT);
CREATE TABLE _B (ID INT);

INSERT INTO _A VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
INSERT INTO _B VALUES (1),(2),(3),(4),(5);

SELECT * FROM _A
SELECT * FROM _B

---------------------------------------------- ANY|SOME --------------------------------------
--(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
--(1),(2),(3),(4),(5);

------------------------------------------------------------------------------------------
--  = ANY ES EQUIVALENTE A IN
------------------------------------------------------------------------------------------
SELECT ID
FROM _A
WHERE ID =ANY(SELECT ID FROM _B)

SELECT ID
FROM _A
WHERE ID >=ANY(SELECT ID FROM _B)

SELECT ID
FROM _A
WHERE ID >ANY(SELECT ID FROM _B)

SELECT ID
FROM _A
WHERE ID >ANY(SELECT MAX(ID) FROM _B)

SELECT ID
FROM _B
WHERE ID >ANY(SELECT MAX(ID) FROM _A)

---------------------------------------------- ALL --------------------------------------
--(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
--(1),(2),(3),(4),(5);
SELECT ID
FROM _A
WHERE ID =ALL(SELECT ID FROM _B)

SELECT ID
FROM _A
WHERE ID >ALL(SELECT ID FROM _B)

SELECT ID
FROM _A
WHERE ID <=ALL(SELECT ID FROM _B)

SELECT ID
FROM _A
WHERE ID >ALL(SELECT MAX(ID) FROM _B)

------------------------------------------------------------------------------------------
--  <> ALL ES EQUIVALENTE A NOT IN
------------------------------------------------------------------------------------------
SELECT ID
FROM _A
WHERE ID <>ALL(SELECT ID FROM _B)

--INSERT

IF OBJECT_ID (N'dbo.Sectores', N'U') IS NOT NULL
DROP TABLE dbo.Sectores;
GO

CREATE TABLE dbo.Sectores
(
SectorID TINYINT NOT NULL IDENTITY(1,1)
,Gerencia VARCHAR(50)
,Sector VARCHAR(50) DEFAULT 'Sin Sector'
);


INSERT INTO dbo.Sectores DEFAULT VALUES;
GO


INSERT INTO dbo.Sectores
VALUES ('Finanzas', 'Contaduria' )  -- inserta salteando sectorid ya que tiene identity
GO

INSERT INTO dbo.Sectores
VALUES ('Finanzas','Cobranzas'), -- insterta 2 valores
('Finanzas','Ventas')
GO

INSERT INTO dbo.Sectores (Sector, Gerencia) -- inserta en distinto orden a la tabla
VALUES ('Legales', 'Finanzas');
GO

INSERT INTO dbo.Sectores
SELECT 'Produccion', 'Manufactura'; -- inserta valores con una consulta
GO

INSERT INTO dbo.Sectores (Gerencia, Sector)
VALUES ('Finanzas', 'Ventas'); -- forma completa de insentar, cuando hay identity no se pone
GO

SET IDENTITY_INSERT dbo.Sectores ON;
GO                                    -- Permite editar el identity ID

INSERT INTO dbo.Sectores (SectorID, Gerencia, Sector)
VALUES (100, 'Recursos Humanos', DEFAULT); -- Inserta con identity id, salteandose hacia el 100
GO

SET IDENTITY_INSERT dbo.Sectores OFF;
GO                                      -- Vuelve el identity al normal, desde 100

INSERT INTO dbo.Sectores (Gerencia, Sector)
SELECT Name,GroupName
FROM HumanResources.Department
WHERE DepartmentID=1; -- Inserta datos con resultaods de una consulta
GO

SELECT * FROM dbo.Sectores

--UPDATE
IF OBJECT_ID (N'dbo.SectoresNuevo', N'U') IS NOT NULL
DROP TABLE dbo.SectoresNuevo;
GO
CREATE TABLE dbo.SectoresNuevo
(
Sector VARCHAR(50)
,SectorNuevo VARCHAR(50)
);
INSERT INTO dbo.SectoresNuevo (sector, sectornuevo)
VALUES ('Contaduria', 'tesoreria')

UPDATE s
    SET Sector=sn.SectorNuevo
    FROM Sectores s
    INNER JOIN SectoresNuevo sn
        ON S.Sector=sn.Sector

SELECT * FROM dbo.Sectores;

--DELETE
SELECT * FROM DBO.SectoresNuevo
SELECT * FROM dbo.Sectores;

DELETE s
FROM dbo.Sectores s
INNER JOIN dbo.SectoresNuevo sn
ON S.Sector=sn.SectorNuevo ;


--TRUNCATE
TRUNCATE TABLE dbo.Sectores; -- RESETEA IDENTITY SI HAY, EL DELETE NO.



---------------------- LABORATORIO SUB CONSULTAS ---------------------------

--1--

SELECT P.Name, P.ListPrice
FROM Production.Product P
WHERE ListPrice < (SELECT AVG(ListPrice)
                    From Production.Product
                    )

ORDER BY ListPrice DESC

--2--

SELECT Name,
        ListPrice ' Precio De Lista' ,
        (SELECT AVG(ListPrice) from Production.Product) ' Precio Promedio' ,
        ListPrice - (Select AVG(ListPrice) FROM Production.Product) ' Diferencia De Precio'
        FROM Production.Product

-- 3  --

SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ListPrice = (SELECT MAX(ListPrice) From Production.Product)

-- 4 --

SELECT P.ProductID, P.NAME, P.ListPrice, P.ProductSubcategoryID
FROM Production.Product P
WHERE P.ListPrice = (SELECT MIN(P1.ListPrice)
                    FROM Production.Product P1
                    WHERE P1.ProductSubcategoryID = P.ProductSubcategoryID)
ORDER BY P.ProductSubcategoryID, P.ListPrice

------------ LABORATORIO EXISTS  - NOT EXSIST -------------

-- 1 --

SELECT ProductID,Name, p.ProductSubcategoryID
FROM Production.Product P
WHERE EXISTS (SELECT *
                FROM Production.ProductSubcategory PS
                WHERE ps.ProductSubcategoryID = p.ProductSubcategoryID AND
                NAME LIKE '%WHEELS%'
            )

-- 2 --
SELECT p.ProductID, p.Name
FROM Production.Product P
WHERE NOT EXISTS (SELECT *
                    FROM SALES.SalesOrderDetail SD
                    WHERE SD.ProductID = p.ProductID
                    )
order by p.ProductID

SELECT P.ProductID, P.Name
FROM Production.Product p
LEFT JOIN sales.SalesOrderDetail sd
        on (sd.ProductID = p.ProductID)
WHERE SD.ProductID IS NULL
ORDER BY P.ProductID --- FORMA DE HACERLO CON UN JOIN ENVEZ DE NOT EXIST

-- 3 --

SELECT *
FROM Person.Person P
WHERE NOT EXISTS (SELECT *
                FROM sales.SalesPerson SP
                WHERE SP.BusinessEntityID = P.BusinessEntityID
                )

SELECT *
FROM Person.Person p
LEFT JOIN Sales.SalesPerson SP
        ON (SP.BusinessEntityID = P.BusinessEntityID)
WHERE SP.BusinessEntityID IS NULL -- MISMA FORMA , USANDO LEFT JOIN


-- 4 --

SELECT P.BusinessEntityID,P.LastName,P.FirstName
FROM Person.Person P
WHERE EXISTS (SELECT *
                FROM SALES.SalesPerson S
                WHERE S.BusinessEntityID = P.BusinessEntityID AND
                    S.TerritoryID IS NULL)

SELECT P.BusinessEntityID, P.LastName, P.FirstName
FROM PERSON.Person P
INNER JOIN SALES.SalesPerson SP
    ON (SP.BusinessEntityID = P.BusinessEntityID)
WHERE SP.TerritoryID IS NULL -- MISMA FORMA, CON INNER JOIN , (INNER JOIN TIENE MEJOR PERFORMANCE PARAL A PC)


------------- IN / NOT IN ---------
-- 1 --


SELECT *
FROM SALES.SalesOrderHeader SO
WHERE SO.TerritoryID IN (SELECT ST.TerritoryID
                        FROM Sales.SalesTerritory ST
                        WHERE CountryRegionCode = 'US')

-- 2 --

SELECT *
FROM SALES.SalesOrderHeader SO
WHERE SO.TerritoryID IN (SELECT ST.TerritoryID
                        FROM Sales.SalesTerritory ST
                        WHERE CountryRegionCode IN ('US','FR','GB'))


-- 3 --

SELECT TOP 10 *
FROM Production.Product
ORDER BY ListPrice DESC

-- 4 --

SELECT *
FROM Production.Product p
WHERE P.ProductID IN (SELECT DISTINCT SO.ProductID
                    FROM SALES.SalesOrderDetail SO
                    WHERE SO.OrderQty >20
                    )

--------------------------- LABORATORIO DML ---------------------

-- 1 --

SELECT ProductID, Name, Color, ListPrice
INTO Productos
FROM Production.Product

-- 2 --

UPDATE Productos
set ListPrice = ListPrice * 1.20

-- 3 --

UPDATE P
        SET P.ListPrice = P.ListPrice * 1.2
        FROM Productos P
        INNER JOIN Purchasing.ProductVendor PV
        ON (PV.ProductID = P.ProductID)
        WHERE pv.BusinessEntityID = 1540

-- 4 --

DELETE
FROM Productos
WHERE ListPrice = 0

-- 5 --

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

-- 6 --
SELECT Name, ListPrice
FROM Productos
WHERE Name like '%pedal%'

UPDATE Productos
        SET ListPrice = ListPrice * 1.15
        WHERE Name like '%pedal%'

-- 7 --

SELECT *
FROM Productos
WHERE name like 'm%'

DELETE
FROM Productos
WHERE Name like 'm%'

-- 8 --

TRUNCATE TABLE Productos

-- 9 --

DROP TABLE Productos


------------- LABORATORIO MANEJO VARIABLES ------------------

DECLARE @TotalVentas NUMERIC(38,6) = 0
--SELECT @TotalVentas = 0
--SET @TotalVentas = 0

SELECT @TotalVentas = SUM(LineTotal)
FROM Sales.SalesOrderDetail


    SELECT @TotalVentas AS 'TOTAL VENTA'-- formas de mostrar el valor
    PRINT @TotalVentas

-- 1 --

DECLARE @TotalVentas NUMERIC(38,6) = 0

        SELECT @TotalVentas =SUM (SD.LINETOTAL)
            FROM Sales.SalesOrderHeader SH
            INNER JOIN SALES.SalesOrderDetail SD
                ON (SH.SalesOrderID = SD.SalesOrderID)
         WHERE year(OrderDate) = '2014'
         PRINT @TotalVentas

-- 2 --

DECLARE @Promedio MONEY = 0

SELECT @Promedio = AVG(LISTPRICE)
    FROM Production.Product

SELECT *
    FROM Production.Product
    WHERE ListPrice < @Promedio
    ORDER BY ListPrice

-- 3 --


DECLARE @Promedio MONEY = 0

SELECT @Promedio = AVG(LISTPRICE)
    FROM Production.Product

SELECT ProductID, Name, ListPrice
INTO productos
    FROM Production.Product
    WHERE ListPrice < @Promedio
    ORDER BY ListPrice

UPDATE productos
SET ListPrice = ListPrice * 1.1


SELECT p.ProductID, p.Name , p.ListPrice, po.ListPrice
FROM PRODUCTION.Product P
INNER JOIN productos po
    on (po.ProductID = p.ProductID) -- comapra las dos tablas y muestra el aumento

-- 4 --

DECLARE @variableTabla TABLE (
                            Categoria VARCHAR(50),
                            SubCategoria VARCHAR(50)
                            )
INSERT INTO @variableTabla (Categoria, SubCategoria)
        SELECT PC.Name, ps.Name
        FROM Production.ProductSubcategory PS
        INNER JOIN Production.ProductCategory PC
            ON (PC.ProductCategoryID = PS.ProductCategoryID)

SELECT * FROM @variableTabla

-- 5 --

DECLARE @Promedio MONEY = 0
SELECT @Promedio = AVG(listprice)
        FROM Production.Product

IF (@Promedio < 500)
BEGIN
    PRINT 'PROMEDIO BAJO'
END ELSE BEGIN
    PRINT 'PROMEDIO ALTO'
END



/************************************************************************************
*                                                                                   *
*                   INSERTAR MODIFICAR ELIMINAR REGISTROS CON MERGE                 *
*                                                                                   *
************************************************************************************/

IF OBJECT_ID (N'dbo.usuarios', N'U') IS NOT NULL
    DROP TABLE dbo.usuarios;
IF OBJECT_ID (N'dbo.UsuariosActual', N'U') IS NOT NULL
    DROP TABLE dbo.UsuariosActual;


CREATE TABLE Usuarios
(
    Codigo INT PRIMARY KEY,
    Nombre VARCHAR(100),
    Puntos INT
)
GO
INSERT INTO Usuarios
VALUES
        (1,'Juan Perez',5),
        (2,'Marco Salgado',5),
        (3,'Carlos Soto',5),
        (4,'Alberto Ruiz',5),
        (5,'Alejandro Castro',5)
GO
CREATE TABLE UsuariosActual
(
        Codigo INT PRIMARY KEY,
        Nombre VARCHAR(100),
        Puntos INT
)
GO
INSERT INTO UsuariosActual
VALUES
        (1,'Juan Perez',10),
        (2,'Marco Salgado',10),
        (4,'Alberto Ruiz',10),
        (5,'Alejandro Castro',10),
        (6,'Pablo Ramos',10)

SELECT * FROM Usuarios
SELECT * FROM UsuariosActual


-- update target.codigo {1,10; 2,10; 4,10; 5,10}
-- insert target.codigo {6,10}
-- delete target.codigo {3,10}


MERGE Usuarios AS TARGET
USING UsuariosActual AS SOURCE
ON (TARGET.Codigo = SOURCE.Codigo)

--Cuandos los registros concuerdan se actualizan los puntos si tienen alguna variación
WHEN MATCHED AND TARGET.Puntos <> SOURCE.Puntos THEN
UPDATE SET TARGET.Puntos = SOURCE.Puntos


--Cuando solo existe en source se inserta en target

WHEN NOT MATCHED BY TARGET THEN
INSERT (Codigo, Nombre, Puntos)
VALUES (SOURCE.Codigo, SOURCE.Nombre, SOURCE.Puntos)


--Cuando solo existe en TARGET se elimina en target
WHEN NOT MATCHED BY source THEN
DELETE;

SELECT * FROM Usuarios
SELECT * FROM UsuariosActual


IF OBJECT_ID (N'dbo.usuarios', N'U') IS NOT NULL
    DROP TABLE dbo.usuarios;
IF OBJECT_ID (N'dbo.UsuariosActual', N'U') IS NOT NULL
    DROP TABLE dbo.UsuariosActual;


--TEORIA CLASE 5

DECLARE @indice INT;
SET @indice=0 ;

WHILE (@indice<=10)
BEGIN
    PRINT CAST(@indice AS VARCHAR(10))
    IF @indice=7
    BEGIN
        SET @indice=@indice+1;
        WAITFOR DELAY '00:00:05'
        CONTINUE;
    END

    ---- Dos formas de salir forzando el while
    IF @indice=4 GOTO mensaje;
    IF @indice=6 BREAK;
    ----------------------------------------
    SET @indice=@indice+1;
END
PRINT 'SALI DEL WHILE POR EL BREAK'
mensaje:
PRINT 'SALI DEL WHILE POR EL GOTO'