SQL Basics

• Use the logical operators AND and OR to combine conditions in a WHERE clause
• AND and OR are keywords

SELECT <column1>,
       <Column2>,
       <Column3>
FROM   <table>
WHERE  <column1> = 'value' AND <column2> < 6;

• With AND, the conditions on both sides must be TRUE
• If either condition is FALSE, the row is filtered out

Select the product ID, price and name from Products where vend_id is 'DLL01' AND the product price is less than 4

-- Oracle / MySQL / SQL Server	
SELECT prod_id,
       prod_price,
       prod_name
FROM   Products
WHERE  vend_id = 'DLL01' AND prod_price < 4;

• With OR, only one of the two conditions in the WHERE clause needs to be TRUE
• Once one condition is TRUE, the other cannot change the result of the OR expression

SELECT <column1>,
       <Column2>,
       <Column3>
FROM   <table>
WHERE  <column1> = 'value' OR <column2> < 6;

Select the product name and price from Products where vend_id is 'DLL01' OR 'BRS01'

-- Oracle / MySQL / SQL Server	
SELECT prod_name,
       prod_price
FROM   Products
WHERE  vend_id = 'DLL01' OR vend_id = 'BRS01';

• AND and OR can be combined; AND takes precedence over OR
• Use parentheses ( ) to make the intended grouping clear
• Parentheses take precedence over AND and OR

SELECT <column1>,
       <Column2>,
       <Column3>
FROM   <table>
WHERE  <column1> = 'value' AND ( <column2> < 6 OR <column3> > 8 );

Select the product name and price from Products where vend_id is 'DLL01' or 'BRS01' AND prod_price is greater than or equal to 10

-- Oracle / MySQL / SQL Server	
SELECT prod_name,
       prod_price
FROM   Products
WHERE  (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;

• The IN operator checks a list of possible values
• Separate the values with commas and enclose the list in parentheses
• If the value matches any item in the list, the condition is TRUE
• The same result can also be obtained with OR

SELECT <column1>,
       <Column2>,
       <Column3>
FROM   <table>
WHERE  <column1> IN ('value1', 'value2', 'value3');	

Advantages of the IN operator:

• IN makes a long list of conditions cleaner and easier to read
• The order of evaluation is easier to manage when AND and OR are also used
• It can be evaluated efficiently
• Most importantly, IN can contain another SELECT statement (subqueries — Chapter 11)

Select the product name and price from Products where vend_id is 'DLL01' or 'BRS01', and sort the result

-- Oracle / MySQL / SQL Server
SELECT   prod_name,
         prod_price
FROM     Products
WHERE    vend_id IN ('DLL01', 'BRS01')
ORDER BY prod_name;

• Place NOT before a condition in the WHERE clause to negate that condition
• NOT is used with another condition or operator, rather than on its own

SELECT    <column1>
FROM      <table>
WHERE NOT <column1> = 'value'
ORDER BY  <column1>;	

• The NOT above negates the condition that follows
• This can also be achieved with the != operator

SELECT   <column1>
FROM     <table>
WHERE    <column1> != 'value'
ORDER BY <column1>;	

• There is no advantage to using NOT in the simple example above
• NOT is particularly useful with the IN operator

SELECT   column1
FROM     <table>
WHERE    <column1> NOT IN ('value1', 'value2', 'value3')
ORDER BY <column1>;	

Select all columns from Products where the product ID is not 'BR01', 'BR02' or 'RGAN01', and sort by product price in descending order

-- Oracle / MySQL / SQL Server	
SELECT   *
FROM     Products
WHERE    prod_id NOT IN ('BR01','BR02', 'RGAN01')
ORDER BY prod_price DESC;

16. Select all rows from Vendors where vend_country is USA and vend_state is CA (1)

17. Select all rows from Vendors where vend_country is England or France (2)

18. Select all columns from OrderItems for items priced above 10 or with at least 100 units ordered (11)

19. Select all columns from OrderItems with a prod_id of BR01, BR02 or BR03, and sort by quantity ordered (7)

20. Select all columns from Products except products from vendors BRS01 and FNG01, where the price is less than 4.00 (3)