Intro to SQL – The Basics

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

 

Leave a Reply

Your email address will not be published. Required fields are marked *