sqlserver 26-11

USE AdventureWorks2008R2
GO

CREATE Schema Transport
GO

CREATE TABLE Transport.TBShipper
(
    idShip  CHAR(5) NOT NULL,
    nomShip VARCHAR(40) NOT NULL,
    dirShip VARCHAR(40) NOT NULL,
    CONSTRAINT PKShip PRIMARY KEY nonclustered (idShip)
)
GO

CREATE TABLE Transport.TBRoad
(
    idRoad  CHAR(5) NOT NULL,
    nomRoad VARCHAR(50) NOT NULL,
    kmRoad  NUMERIC(8,2) NOT NULL,
    CONSTRAINT PKRoad PRIMARY KEY (idRoad)
)
GO

-- Crando Indices
-- Normal
CREATE INDEX IdxKmRoad
ON Transport.TBRoad
(KmRoad ASC)
GO

-- Unico
CREATE UNIQUE INDEX IdxNomRoad
ON Transport.TBRoad (nomRoad)
GO

-- Eliminar Indices
DROP INDEX Transport.TBRoad.idxKmRoad
GO

DROP INDEX Transport.TBRoad.IdxNomRoad
GO



USE AdventureWorks2008R2
GO

SELECT @@VERSION
GO

SELECT 'Hello World, Hello People Soft'
GO

BEGIN
    DECLARE @v_Nombre VARCHAR(20) = 'JavierNT'
    SELECT @v_Nombre
END

SELECT * FROM Person.Person
GO

SELECT * FROM Production.Product
GO

-- Muestra datos usando clausula
SELECT * FROM Production.Product
WHERE ListPrice > 0
GO

---
SELECT * FROM Production.Product
WHERE ListPrice > 0
    AND Color IS NOT NULL
    AND Class IS NOT NULL
    AND SIZE IS NOT NULL
    AND Name LIKE '%Road%'
GO
---

SELECT * FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2005
GO
---

SELECT * FROM Sales.SalesOrderHeader
WHERE DATEDIFF(dd, OrderDate, ShipDate) < 30
GO
---

SELECT * FROM Person.Person
GO

/*
Muestre todos los empleados de estado civil casados
con mas de 30 años de edad
*/

SELECT * FROM HumanResources.Employee
WHERE MaritalStatus = 'M' AND DATEDIFF(YY, BirthDate, GETDATE()) > 30
GO

-- Modifique la consulta anterior para mostrar a los ingenieros
SELECT * FROM HumanResources.Employee
WHERE MaritalStatus = 'M'
    AND DATEDIFF(YY, BirthDate, GETDATE()) > 30
    AND JobTitle LIKE '%Engineer%'
GO
--

SELECT * FROM Production.Product
ORDER BY ListPrice DESC
GO

-- Usando la Clausula Order BY 02 criterios
SELECT * FROM Production.Product
ORDER BY ListPrice DESC, SIZE DESC
GO

--Usando Predicado TOP
SELECT top 5 * FROM HumanResources.Employee
WHERE MaritalStatus = 'M'
    ORDER BY BirthDate DESC
GO

--Producto mas caro de tipo

SELECT top 3 * FROM Production.Product
WHERE Name LIKE '%Mountain%' ORDER BY ListPrice DESC
GO

--
SELECT DISTINCT Color FROM Production.Product
WHERE Color IS NOT NULL
GO