This SQL tutorial will teach you the basics of how to use SQL to access and manipulate data in
CASJobs.

1. Intro to SQL

SQL is a (ANSI) defined standard language for accessing and manipulating databases.

SQL stands for Structured Query Language

2. Syntax for SQL

A database most often contains one or more tables. Each table is identified by a name (e.g. "Employees"). 

Below is an example of a table called "Employees".

EmpID	Lname		Fname		Address		City
-------	---------------	---------------	---------------	------
1	Hampton		Lisa		2300 lilac ln	Sidney
2	Smith		Tom		5678 cuba rd	Sidney
3	Paulsen		Karen		8901 Samson ct	Seattle

The table above contains three records (one for each employee) and 
five columns (EmpID, Lname, Fname, Address, and City).

3. SELECT Statement

The SELECT statement is used to select data from a table in a database.

Use the following SELECT statement to select the "lname,fname" columns from the table above.

SELECT Lname,Fname FROM Employees

Lname		Fname	
---------------	---------
Hampton		Lisa
Smith		Tom
Paulsen		Karen

Use the following SELECT statement to select all the columns "*" without having to 
name each one individually.

SELECT * FROM Employees

EmpID	Lname		Fname		Address		City
-------	---------------	---------------	---------------	------
1	Hampton		Lisa		2300 lilac ln	Sidney
2	Smith		Tom		5678 cuba rd	Sidney
3	Paulsen		Karen		8901 Samson ct	Seattle

4.  SELECT DISTINCT Statement

In a table, some columns may contain duplicate values. 

The DISTINCT keyword can be used to return only different values.

The "Employees" table.

EmpID	Lname		Fname		Address		City
-------	---------------	---------------	---------------	------
1	Hampton		Lisa		2300 lilac ln	Sidney
2	Smith		Tom		5678 cuba rd	Sidney
3	Paulsen		Karen		8901 Samson ct	Seattle


Use the following SELECT statement to select only the distinct values for the "City" column.

SELECT DISTINCT City FROM Employees

City
---------
Sidney
Seattle


5.  The WHERE Clause

The WHERE clause is used to filter records.

The "Employees" table.

EmpID	Lname		Fname		Address		City
-------	---------------	---------------	---------------	------
1	Hampton		Lisa		2300 lilac ln	Sidney
2	Smith		Tom		5678 cuba rd	Sidney
3	Paulsen		Karen		8901 Samson ct	Seattle

Now we want to select only the Employees living in the city "Sidney" from the table above.

Use the following SELECT statement.

SELECT * FROM Employees
WHERE City='Sidney'

EmpID	Lname		Fname		Address		City
-------	---------------	---------------	---------------	------
1	Hampton		Lisa		2300 lilac ln	Sidney
2	Smith		Tom		5678 cuba rd	Sidney

SQL uses single quotes around character values.

This is correct.

SELECT * FROM Employees WHERE Fname='Tom'

This is incorrect.

SELECT * FROM Employees WHERE Fname=Tom

Quotes aren't required for numeric values.

This is correct.

SELECT * FROM Employees WHERE Year=1960

This is incorrect.

SELECT * FROM Employees WHERE Year='1960'

When using the WHERE clause, the following operators can be utilized.

Operator	description
=		Equal
<>,!=		Not equal
<		Less than
>		Greater than
<=		Less than or equal
>=		Greater than or equal
BETWEEN		Between an inclusive range
LIKE		Search for a pattern
IN		If you know the exact value you want to return for at least one of the columns


6.  The AND & OR Operators

The AND & OR operators are used to filter records based on more than one conditions.

The AND operator displays a record if both the first condition and the second condition is true.

The OR operator displays a record if either the first condition or the second condition is true.

The "Employees" table.

EmpID	Lname		Fname		Address		City
-------	---------------	---------------	---------------	------
1	Hampton		Lisa		2300 lilac ln	Sidney
2	Smith		Tom		5678 cuba rd	Sidney
3	Paulsen		Karen		8901 Samson ct	Seattle

Select only the Employees with the first name equal to "Tom" AND the last name equal to "Smith".

Use the following SELECT statement.

SELECT * FROM Employees
WHERE Fname='Tom'
AND Lname='Smith	'

EmpID	Lname		Fname		Address		City
-------	---------------	---------------	---------------	------
2	Smith		Tom		5678 cuba rd	Sidney

OR Operator Example

Now we want to select only the Employees with the first name equal to "Tom" OR the first name equal to "Lisa".

Use the following SELECT statement.

SELECT * FROM Employees
WHERE Fname='Tom'
OR Fname='Lisa'

EmpID	Lname		Fname		Address		City
-------	---------------	---------------	---------------	------
1	Hampton		Lisa		2300 lilac ln	Sidney
2	Smith		Tom		5678 cuba rd	Sidney

AND and OR can be combined, use parentheses to form complex expressions.

Now we want to select only the Employees with the last name equal to "Smith" AND the first name equal to "Tom" OR to "Lisa".

Use the following SELECT statement.

SELECT * FROM Employees WHERE
Lname='Smith'
AND (Fname='Tom' OR Fname='Lisa')

EmpID	Lname		Fname		Address		City
-------	---------------	---------------	---------------	------
2	Smith		Tom		5678 cuba rd	Sidney


7.  ORDER BY Keyword

The ORDER BY keyword is used to sort the results.

The ORDER BY keyword sort the records in ascending order by default.

If you want to sort the records in a descending order, you can use the desc keyword.

The "Employees" table.

EmpID	Lname		Fname		Address		City
-------	---------------	---------------	---------------	------
1	Hampton		Lisa		2300 lilac ln	Sidney
2	Smith		Tom		5678 cuba rd	Sidney
3	Paulsen		Karen		8901 Samson ct	Seattle
4	Niegoda		Tom		1234 yeoho rd	Seattle

select all the Employees from the table above, and sort the Employees by their last name.

Use the following SELECT statement.

SELECT * FROM Employees
ORDER BY Lname	

EmpID	Lname		Fname		Address		City
-------	---------------	---------------	---------------	------
1	Hampton		Lisa		2300 lilac ln	Sidney
4	Niegoda		Tom		1234 yeoho rd	Seattle
3	Paulsen		Karen		8901 Samson ct	Seattle
2	Smith		Tom		5678 cuba rd	Sidney

select all the Employees from the table above, and sort the Employees by their last name in descending order.

Use the following SELECT statement.

SELECT * FROM Employees
ORDER BY Lname desc

EmpID	Lname		Fname		Address		City
-------	---------------	---------------	---------------	------
2	Smith		Tom		5678 cuba rd	Sidney
3	Paulsen		Karen		8901 Samson ct	Seattle
4	Niegoda		Tom		1234 yeoho rd	Seattle
1	Hampton		Lisa		2300 lilac ln	Sidney