/*
CIS 324

CHAPTER 7

SELECT II

SELECT with conditional restrictions pp. 237

WHERE condition. The WHERE condition filters rows
string values are placed in single quotes ' ' as are
dates (more on dates later...)

BETWEEN allows us to specify endpoints on a range.

WHERE condition(s)

*/

USE NorthWind

SELECT OrderID, CustomerID, *
FROM Orders

SELECT *
FROM Orders
WHERE ShipCity ='Reims'

--Test promotional effectiveness
SELECT *
FROM Orders
WHERE ShipCity ='Reims' AND OrderDate ='1996-09-02 00:00:00.000'

/*
TT = T
TF = F
FT = F
FF = F
*/
SELECT *
FROM Orders
WHERE ShipCity ='Reims' AND OrderDate ='September 02, 1996'

--Do a date range

SELECT *
FROM Orders
WHERE OrderDate >= 'September 1, 1997' AND 
      Orderdate <= 'September 30, 1997'
ORDER BY OrderDate ASC

--Numeric comparison
SELECT *
FROM Orders
WHERE OrderDate >= 'September 1, 1997' AND 
      Orderdate <= 'September 30, 1997' AND
      Freight > 50.0
ORDER BY OrderDate ASC

--This is not very readable because the endpoints of the dates
--are obscured by the Freight predicate
SELECT *
FROM Orders
WHERE OrderDate >= 'September 1, 1997' AND 
      Freight > 50.0 AND
      Orderdate <= 'September 30, 1997'
ORDER BY OrderDate ASC

SELECT *
FROM Orders
WHERE OrderDate BETWEEN 'September 1, 1997' AND 
                        'September 30, 1997' AND 
      Freight > 50.0
ORDER BY OrderDate ASC

SELECT *
FROM Orders
WHERE NOT(OrderDate BETWEEN 'September 1, 1997' AND 'September 30, 1997') AND 
      Freight > 50.0
ORDER BY OrderDate ASC

--These two expressions are equivalent 
SELECT *
FROM Orders
WHERE NOT(OrderDate BETWEEN 'September 1, 1997' AND 'September 30, 1997')

SELECT *
FROM Orders
WHERE OrderDate < 'September 1, 1997' OR OrderDate > 'September 30, 1997'


/*
Mathematical functions:
ROUND()
See list in Query Analyzer help system
*/

--specifying a range with WHERE


/*
The BETWEEN special operator pp. 243
The Between operator makes the query a little more readable

test_expression [ NOT ] BETWEEN begin_expression AND end_expression

*/