SQL Basics

• Use the WHERE clause to filter rows in a SELECT statement
• The WHERE clause follows the table name
• Filtering in the database is preferable to filtering in the application

SELECT <column1>,
       <column2>
FROM   <table>
WHERE  <column1 = 10 >

Select the product name and price from Products where the price is 3.49

-- Oracle / MySQL / SQL Server
SELECT prod_name,
       prod_price
FROM   Products
WHERE  prod_price = 3.49;

Operator reference: MySQL / MariaDB / SQLite,   SQL Server,   PostgreSQL,   Oracle

Select the product name and price from Products where the price is less than 10

-- Oracle / MySQL / SQL Server	
SELECT prod_name,
       prod_price
FROM   Products
WHERE  prod_price < 10;

Select the vendor ID and product name from Products where the vendor ID is not DLL01

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

Select the product name and price from Products where the price is between 5 and 10

-- Oracle / MySQL / SQL Server	
SELECT prod_name,
       prod_price
FROM   Products
WHERE  prod_price BETWEEN 5 AND 10;

• Specify a date as a string, enclosed in single quotes
• Check the date format to avoid ambiguity

Select all order numbers and dates from Orders with a date later than 1 February 2012

-- Oracle, SQL Server	
SELECT	 order_num,
         order_date
FROM     Orders
WHERE    order_date > '01-FEB-2012'
ORDER BY order_date;

-- MySQL / SQL Server	
SELECT	 order_num,
         order_date
FROM     Orders
WHERE    order_date > '2012-02-01'
ORDER BY order_date;

• NULL is not the same as 0 (zero) or '' (an empty string)
• NULL represents a value that has not been assigned
• An unassigned value is treated as unknown
• Comparing a value with an unknown value (NULL) produces an unknown result (NULL)

SELECT cust_name
FROM   Customers
WHERE  cust_email = NULL -- Unwanted	

• Use the IS keyword instead of the = operator

Select the customer name and email from Customers where the email field is NULL

-- Oracle / MySQL / SQL Server	
SELECT cust_name,
       cust_email
FROM   Customers
WHERE  cust_email IS NULL;

11. Select the product IDs from OrderItems where more than 75 units were ordered (8)

12. Select all columns from Vendors where the state is unknown (2)

13. Select all columns from Products with a price from 5.00 up to and including 9.00 (2)

14. Select all columns from Orders with an order_date after 1 February 2012 (3)

15. Select all columns from Customers where cust_email has a value (3)