ScriptsBlog

USE Tiendita

/*SELECT*/

SELECT cus.FirstName,
        cus.LastName
FROM Customer cus

SELECT *
FROM Customer

SELECT (cus.FirstName + ' ' + cus.LastName) AS FullName --"Full Name"
FROM Customer cus

---------

/*AS*/

SELECT cus.FirstName,
        cus.LastName
FROM Customer cus

SELECT (cus.FirstName + ' ' + cus.LastName) AS FullName --"Full Name"
FROM Customer cus

---------

/*WHERE*/

SELECT *
FROM [ORDER]

SELECT *
FROM [ORDER] ord
WHERE ord.CustomerId = 85 AND ord.OrderDate = '20131112'

SELECT *
FROM [ORDER] ord
WHERE ord.OrderDate = '20140101' OR ord.OrderDate = '20140130'

SELECT *
FROM [ORDER] ord
WHERE ord.CustomerId IN(85, 14, 1)

SELECT *
FROM OrderItem ordit
WHERE ordit.OrderId IN(1, 4)

SELECT *
FROM Customer cus
WHERE FirstName LIKE 'Maria'

SELECT *
FROM Customer cus
WHERE cus.FirstName LIKE 'A%'   --All names that starts with A

SELECT *
FROM Customer cus
WHERE cus.FirstName LIKE '%A'   --All names that ends with A

SELECT *
FROM Customer cus
WHERE cus.FirstName LIKE '%A%'  --All names with that pattern

SELECT *
FROM Customer cus
WHERE cus.FirstName LIKE '%Peter%'

SELECT *
FROM [ORDER] ord
WHERE ord.OrderDate BETWEEN '20130101' AND '20130228'

SELECT *
FROM [ORDER] ord
WHERE ord.OrderDate > '20140427'

---------

/*ORDER BY*/

SELECT (cus.FirstName + ' ' + cus.LastName) AS "Full Name",
        cus.City,
        cus.Country,
        cus.Phone
FROM Customer cus
ORDER BY cus.Id

----------

/*SQL Functions*/

SELECT UPPER(cus.FirstName)
FROM Customer cus

SELECT LOWER(cus.FirstName)
FROM Customer cus

SELECT LEFT(cus.FirstName, 2)
FROM Customer cus

SELECT RIGHT(cus.FirstName, 2)
FROM Customer cus

SELECT LTRIM(' Removes trailing spaces.')

SELECT RTRIM('Removes trailing spaces. ')

SELECT SUBSTRING(cus.FirstName, 2, 3)
FROM Customer cus

SELECT GETDATE()

SELECT DATEPART(yy, ord.OrderDate)
FROM [ORDER] ord

SELECT DATEADD(mm, 1, GETDATE())

SELECT MONTH('20170426')

SELECT DAY('20170426')

SELECT YEAR('20170426')

SELECT DATENAME(weekday, GETDATE())

SELECT DATEDIFF(dd, '20170426', '20170725')

SELECT DATEFROMPARTS(2017, 04, 26)

SELECT ISNULL(CONVERT(VARCHAR(MAX), ord.OrderDate), 'Sin Fecha')
FROM [ORDER] ord

SELECT NULLIF(100, 50*2)
SELECT NULLIF(2*2, 2*7)

SELECT COALESCE(sup.Phone, sup.Fax) "Contacto"
FROM Supplier sup

DECLARE @myDecimalValue FLOAT
SET @myDecimalValue = 3.1415
SELECT CAST(@myDecimalValue AS INT)

DECLARE @myDecimalValue FLOAT
SET @myDecimalValue = 4521312.531
SELECT CONVERT(INT, @myDecimalValue)

SELECT TOP 5 *
FROM Customer

SELECT RAND(12)

----------

/*JOINS*/

SELECT ord.OrderNumber,
        ord.TotalAmount,
        cus.FirstName,
        cus.LastName,
        cus.City,
        cus.Country
FROM [ORDER] ord
INNER JOIN Customer cus ON ord.CustomerId = cus.Id

SELECT ord.OrderNumber,
        ord.TotalAmount,
        cus.FirstName,
        cus.LastName,
        cus.City,
        cus.Country
FROM Customer cus
LEFT JOIN [ORDER] ord ON cus.Id = ord.CustomerId
ORDER BY ord.TotalAmount

SELECT ord.TotalAmount,
        cus.FirstName,
        cus.LastName,
        cus.City,
        cus.Country
FROM [ORDER] ord
RIGHT JOIN Customer cus ON ord.CustomerId = cus.Id
WHERE ord.TotalAmount IS NULL

----------

/*Agrupadores*/

SELECT cus.Country, COUNT(cus.City) AS Quantity
FROM Customer cus
GROUP BY cus.Country

SELECT ord.CustomerId, SUM(ord.TotalAmount) Total
FROM [ORDER] ord
GROUP BY ord.CustomerId
HAVING SUM(ord.TotalAmount) > 20000

/*Funciones matemáticas*/

SELECT TOP 10 ROUND(ord.TotalAmount, -1)
FROM [ORDER] ord

SELECT TOP 10  ord.TotalAmount
FROM [ORDER] ord

-- Be careful with decimal places. Maybe it could not be convenient use this function over monetary fields

SELECT CEILING(ord.TotalAmount)
FROM [ORDER] ord

SELECT ord.TotalAmount
FROM [ORDER] ord

SELECT FLOOR(ord.TotalAmount)
FROM [ORDER] ord

SELECT ord.TotalAmount
FROM [ORDER] ord

SELECT ABS(-12)

SELECT RAND(12)

/*Subqueries*/

SELECT cus.FirstName,
        cus.LastName,
        OrderCount = (SELECT COUNT(ord.Id) FROM [ORDER] ord WHERE ord.CustomerId = cus.Id)
FROM Customer cus

SELECT ProductName
FROM Product pro
WHERE pro.Id IN (SELECT ordit.ProductId
                    FROM OrderItem ordit
                    WHERE ordit.Quantity > 100)

----------

/*INSERT, UPDATE, DELETE*/

INSERT INTO Customer(FirstName, LastName, City, Country, Phone)
    VALUES('Pedro', 'DiBella Nava', 'Tampico', 'México', '121-45-53')

SELECT *
FROM Customer

INSERT INTO Customer (FirstName, LastName, City, Country, Phone)
    SELECT LEFT(ContactName, CHARINDEX(' ',ContactName) - 1) AS FirstName,
            SUBSTRING(ContactName, CHARINDEX(' ',ContactName) + 1, 100) AS LastName,
            City,
            Country,
            Phone
    FROM Supplier
    WHERE Country = 'Canada'

UPDATE Customer
SET LastName = 'Leal'
WHERE Id = 92

DELETE Customer
WHERE id = 92

-----------

/*Condicionales*/

IF DATENAME(weekday, GETDATE()) IN ('Saturday', 'Sunday')
    SELECT 'Weekend'
ELSE
    SELECT 'Weekday'

SELECT ord.CustomerId,
        cus.FirstName,
        cus.LastName,
        Payback =
            CASE
                WHEN SUM(ord.TotalAmount) >= 10000 AND SUM(ord.TotalAmount) < 20000
                    THEN
                        SUM(ord.TotalAmount) * 0.25
                WHEN SUM(ord.TotalAmount) >= 20000 AND SUM(ord.TotalAmount) < 30000
                    THEN
                        SUM(ord.TotalAmount) * 0.30
                WHEN SUM(ord.TotalAmount) >= 30000
                    THEN
                        SUM(ord.TotalAmount) * 0.40
                WHEN SUM(ord.TotalAmount) < 10000
                    THEN
                        0
            END
FROM [ORDER] ord
LEFT JOIN Customer cus ON ord.CustomerId = cus.Id
GROUP BY ord.CustomerId, cus.FirstName, cus.LastName

SELECT ord.CustomerId, SUM(ord.TotalAmount) Total
FROM [ORDER] ord
WHERE ord.CustomerId = 4
GROUP BY ord.CustomerId

----------

/*Ciclos*/

DECLARE @intFlag INT
SET @intFlag = 1

WHILE (@intFlag <=5)
    BEGIN
        PRINT @intFlag
        SET @intFlag = @intFlag + 1
    END
GO

DECLARE @intFlag INT
SET @intFlag = 1

WHILE (@intFlag <=5)
    BEGIN
        PRINT @intFlag
        SET @intFlag = @intFlag + 1
        IF @intFlag = 4
            BREAK;
    END
GO

DECLARE @intFlag INT
SET @intFlag = 1

WHILE (@intFlag <=5)
    BEGIN
        PRINT @intFlag
        SET @intFlag = @intFlag + 1
        CONTINUE;
        IF @intFlag = 4 -- This will never executed
            BREAK;
    END
GO

CREATE TABLE #tmpFechas(
    Fecha   DATE
)

DECLARE @fechaInicio    DATE,
        @fechaFin       DATE

SET @fechaInicio = '20170501'
SET @fechaFin = '20170531'

WHILE(@fechaInicio <= @fechaFin)
    BEGIN
        INSERT INTO #tmpFechas(Fecha)
            VALUES(@fechaInicio)
        SET @fechaInicio = DATEADD(DAY, 1, @fechaInicio)
    END

SELECT *
FROM #tmpFechas

DROP TABLE #tmpFechas

----------

/*Índices*/

CREATE NONCLUSTERED INDEX IDX_Orders ON [ORDER](OrderDate)

SELECT *
FROM [ORDER] ord
WITH (INDEX(IDX_Orders))
WHERE ord.OrderDate BETWEEN '20120828' AND '20120830'

DROP INDEX IDX_Orders ON [ORDER]

---------

/*Cursores*/

SELECT *
FROM [ORDER]

DECLARE @vFirstName     AS VARCHAR(MAX),
        @vLastName      AS VARCHAR(MAX),
        @vMoneySpent    AS MONEY

DECLARE @CustomerMoneySpent AS CURSOR

SET @CustomerMoneySpent = CURSOR FOR
SELECT cus.FirstName,
        cus.LastName,
        SUM(ord.TotalAmount)
FROM Customer cus
LEFT JOIN [ORDER] ord ON cus.Id = ord.CustomerId
GROUP BY cus.FirstName, cus.LastName

OPEN @CustomerMoneySpent;
FETCH NEXT FROM @CustomerMoneySpent INTO @vFirstName, @vLastName, @vMoneySpent;

WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @vFirstName + ' ' + @vLastName + ' ' + CAST(@vMoneySpent AS VARCHAR(10));
        FETCH NEXT FROM @CustomerMoneySpent INTO @vFirstName, @vLastName, @vMoneySpent;
    END

CLOSE @CustomerMoneySpent;
DEALLOCATE @CustomerMoneySpent;

----------

/*Triggrs*/

/*IF NOT EXISTS (SELECT * from sys.databases where name = 'db_test')
BEGIN
    CREATE DATABASE db_test;
END

USE db_test;*/

IF NOT EXISTS(SELECT * FROM sys.sysobjects WHERE name='Expedientes' AND xtype='U')
CREATE TABLE expedientes (
    Code                VARCHAR(15) NOT NULL,
    State               VARCHAR(20) DEFAULT 'INICIO',
    StateChangedDate    DATETIME,
    CONSTRAINT          PK_Expedientes PRIMARY KEY(code)
)

DELETE FROM Expedientes WHERE Code IN ('exp1','exp2', 'exp3');
INSERT INTO Expedientes(Code) VALUES('exp1');
INSERT INTO Expedientes(Code) VALUES('exp2');
INSERT INTO Expedientes(Code) VALUES('exp3');

IF NOT EXISTS (SELECT * FROM sys.sysobjects WHERE name='ExpStatusHistory' AND xtype='U')
CREATE TABLE ExpStatusHistory (
    Id          INT IDENTITY,
    Code        VARCHAR(15) NOT NULL,
    State       VARCHAR(20) NOT NULL,
    DATE        DATETIME DEFAULT GETDATE(),
    CONSTRAINT  PK_ExpStatusHistory PRIMARY KEY(id)
);

--IF EXISTS(SELECT 1 FROM sys.triggers WHERE name = 'StatusChangeDateTrigger')
--  DROP TRIGGER StatusChangeDateTrigger
--GO
IF OBJECT_ID('StatusChangeDateTrigger', 'TR') IS NOT NULL
BEGIN
    DROP TRIGGER StatusChangeDateTrigger;
END
GO

CREATE TRIGGER StatusChangeDateTrigger ON Expedientes
AFTER UPDATE AS
    IF UPDATE(state)
        BEGIN
            UPDATE expedientes
            SET stateChangedDate = GETDATE()
            WHERE code = (SELECT code FROM inserted);

            INSERT INTO ExpStatusHistory(Code, State)
            SELECT Code, State
            FROM deleted
            WHERE Code = deleted.Code
        END
GO

SELECT *
FROM Expedientes

UPDATE Expedientes
SET State = 'PENDIENTE_COBRO'
WHERE Code = 'exp1'

SELECT *
FROM ExpStatusHistory

----------

/*Stored Procedure*/

IF EXISTS(SELECT 1 FROM sys.procedures WHERE name = 'spCustomerIns')
    DROP PROC spCustomerIns
GO
CREATE PROC spCustomerIns(
    @pId        INT OUT,
    @pFirstName NVARCHAR(MAX),
    @pLastName  NVARCHAR(MAX),
    @pCity      NVARCHAR(MAX),
    @pCountry   NVARCHAR(MAX),
    @pPhone     NVARCHAR(MAX)
)
AS
    BEGIN
        SELECT @pId = ISNULL(MAX(cus.Id), 0) + 1  FROM Customer cus

        INSERT INTO Customer(Id, FirstName, LastName, City, Country, Phone)
            VALUES(@pId, @pFirstName, @pLastName, @pCity, @pCountry, @pPhone)
    END
GO

EXEC spCustomerIns 95, 'Pascual', 'DiBella Nava', 'Tampico', 'México', '212-98-43'

SELECT *
FROM Customer

----------

/*Functions*/

IF EXISTS(SELECT 1 FROM sys.objects WHERE name = 'StripWWWandCom')
    DROP PROC StripWWWandCom
GO

CREATE FUNCTION StripWWWandCom(
    @pInput     VARCHAR(250)
)
RETURNS VARCHAR(250)
AS
    BEGIN
        DECLARE @WORK VARCHAR(250)

        SET @WORK = @pInput
        SET @WORK = REPLACE(@WORK, 'www.', '')
        SET @WORK = REPLACE(@WORK, '.com', '')

        RETURN @WORK
    END

DECLARE @vWebSite VARCHAR(MAX)
SET @vWebSite = 'www.google.com'
SELECT dbo.StripWWWandCom(@vWebSite)