Friday, 15 July 2011

Where Clause

/*
SELECT *
  FROM data source(s)
  WHERE condition
*/

-- a boolean condition
SELECT *  FROM dbo.Tour  WHERE 1 = 1;

-- Comparison Operator
SELECT *  FROM dbo.Tour  WHERE TourID <= 3;

-- Between
SELECT *  FROM dbo.Tour  WHERE TourID BETWEEN 2 AND 4;

SELECT *  FROM dbo.Tour  WHERE TourID >= 2    AND TourID <= 4;

-- In Condition
SELECT *  FROM dbo.Tour  WHERE TourID IN (1, 3);

UPDATE dbo.BaseCamp SET Region = 'NC' WHERE Name = 'Ashville';
UPDATE dbo.BaseCamp SET Region = 'NC' WHERE Name = 'Cape Hatteras';
UPDATE dbo.BaseCamp SET Region = 'BA' WHERE Name = 'Freeport';
UPDATE dbo.BaseCamp SET Region = 'FL' WHERE Name = 'Ft Lauderdale';
UPDATE dbo.BaseCamp SET Region = 'WV' WHERE Name = 'West Virginia';

SELECT *  FROM dbo.BaseCamp  WHERE Region IN ('NC', 'WV');

-- Like Condition
/*
multiple characters    %
single character        _
match in range        [ ]
match not in range    [^ ]
*/

SELECT *  FROM dbo.BaseCamp  WHERE Name LIKE 'F%';

SELECT *  FROM dbo.Customer  WHERE LastName LIKE '%son';

SELECT *  FROM dbo.Customer  WHERE FirstName LIKE '[ABC]%';

SELECT *  FROM dbo.Customer  WHERE FirstName LIKE '[^ABC]%';

-- Multiple Conditions
SELECT *  FROM dbo.Customer  WHERE   CustomerTypeID = 1     OR        FirstName LIKE  'Dav%'     AND        CustomerID = 14;

SELECT *  FROM dbo.Customer  WHERE       (CustomerTypeID = 1         OR        FirstName LIKE 'Dav%')      AND        CustomerID = 14;