SQL Overview

What is SQL?

SQL stands for Structured Query Language. It allows you to access a database. SQL can be used to execute queries against a database, retrieve data from a database, insert new records in a database, delete records from a database and update records in a database.

SELECT Statement

SELECT is used to get data out of the database. It is returned in a table form.

Syntax :

SELECT Column_Name(s) FROM TABLE_NAME

All of the examples below will use this table.

Table : Customer
FirstName	LastName	Phone	City
Darryll	Sulymka	604-607-1440	Langley
John	Crichton	321-868-1221	Cape Canaveral
Rob	McLean	604-607-1440	Langley
Aeryn	Sun	456-345-7234	Unknown
Rygel XVI	Unknown	894-256-4235	Unknown

Select Some Columns : To Select the firstname , lastname and Location from Customer you would do some thing like this :

SELECT FirstName, LastName, City FROM Customer

FirstName	LastName	City
Darryll	Sulymka	Langley
John	Crichton	Cape Canaveral
Rob	McLean	Langley
Aeryn	Sun	Unknown
Rygel XVI	Unknown	Unknown

Distinct

DISTINCT is used to return only different values from your select statement like what were all of the different Sizes I ran last shift.

Syntax :

SELECT DISTINCT Column_Name(s) FROM TABLE_NAME

Example :

SELECT City FROM Customer

Result : SELECT City FROM Customer
Langley
Cape Canaveral
Langley
Unknown
Unknown

SELECT DISTINCT City FROM Customer

Result : SELECT DISTINCT City FROM Customer
Langley
Cape Canaveral
Unknown

WHERE Clause

WHERE is added to a Select statement to help limit down the number of records returned, based on a given condition.

Syntax :

SELECT Column_Name(s) FROM TABLE_NAME\\ WHERE COLUMN operator VALUE

Here is a list of available operators :

Operator	Description
=	Equal
<>	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between a range
LIKE	Search for data that is Like

All of the Examples Below will use this table :

Table : Customer
FirstName	LastName	Phone	City
Darryll	Sulymka	604-607-1440	Langley
John	Crichton	321-868-1221	Cape Canaveral
Rob	McLean	604-607-1440	Langley
Aeryn	Sun	456-345-7234	Unknown
Rygel XVI	Unknown	894-256-4235	Unknown

---

Examples

SELECT * FROM Customer WHERE Phone = '604-607-1440'

FirstName	LastName	Phone	City
Darryll	Sulymka	604-607-1440	Langley
Rob	McLean	604-607-1440	Langley

SELECT * FROM Customer Where Phone <> '604-607-1440'

FirstName	LastName	Phone	City
John	Crichton	321-868-1221	Cape Canaveral
Aeryn	Sun	456-345-7234	Unknown
Rygel XVI	Unknown	894-256-4235	Unknown

---

BETWEEN SQL

SELECT * FROM Customer WHERE FirstName BETWEEN 'a' AND 'f'

FirstName	LastName	Phone	City
Darryll	Sulymka	604-607-1440	Langley
Aeryn	Sun	456-345-7234	Unknown

SELECT * FROM Customer WHERE FirstName BETWEEN 'f' AND 'z'

FirstName	LastName	Phone	City
John	Crichton	321-868-1221	Cape Canaveral
Rob	McLean	604-607-1440	Langley
Rygel XVI	Unknown	894-256-4235	Unknown

---

LIKE SQL

SELECT * FROM Customer WHERE Lastname LIKE 'su%'

FirstName	LastName	Phone	City
Darryll	Sulymka	604-607-1440	Langley
Aeryn	Sun	456-345-7234	Unknown

SELECT * FROM Customer WHERE FistName LIKE '%y%'

FirstName	LastName	Phone	City
Darryll	Sulymka	604-607-1440	Langley
Aeryn	Sun	456-345-7234	Unknown
Rygel XVI	Unknown	894-256-4235	Unknown

SELECT * FROM Customer WHERE LastName LIKE '%n'

FirstName	LastName	Phone	City
John	Crichton	321-868-1221	Cape Canaveral
Rob	McLean	604-607-1440	Langley
Aeryn	Sun	456-345-7234	Unknown
Rygel XVI	Unknown	894-256-4235	Unknown

INSERT INTO Statement

INSERT INTO is used to add records to a table.

Syntax

INSERT INTO TABLE_NAME\\ VALUES (value1, value2, value3 ...)

OR

INSERT INTO TABLE_NAME(column1, column2, column3 ...) \\ VALUES (value1, value2, value3 ...)

All of the examples below will modify this table.

CustomerID	FirstName	LastName	Phone	City
42	Darryll	Sulymka	604-607-1440	Langley

---

Examples

INSERT INTO Customer
 VALUES(43, 'John', 'Crichton', '321-868-1221', 'Cape Canaveral')

CustomerID	FirstName	LastName	Phone	City
42	Darryll	Sulymka	604-607-1440	Langley
43	John	Crichton	321-868-1221	Cape Canaveral

INSERT INTO Customer(CustomerID,FirstName,LastName,Phone) \\ VALUES(24, 'Aeryn', 'Sun', '456-345-7234')

CustomerID	FirstName	LastName	Phone	City
42	Darryll	Sulymka	604-607-1440	Langley
43	John	Crichton	321-868-1221	Cape Canaveral
44	Aeryn	Sun	456-345-7234

UPDATE Statement

UPDATE is used to update the data in a table.

Syntax :

UPDATE TABLE_NAME \\ SET column_name = new_value \\ WHERE condition

All of the Examples Below will use this table

Table : Customer
FirstName	LastName	Phone	City
Darryll	Sulymka	604-607-1440	Langley
John	Crichton	321-868-1221	Cape Canaveral
Rob	McLean	604-607-1440	Langley
Aeryn	Sun	456-345-7234	Unknown
Rygel XVI	Unknown	894-256-4235	Unknown

---

Examples :

UPDATE Customer \\ SET LastName='Smith' \\ WHERE FirstName = 'Rygel'

Table : Customer
FirstName	LastName	Phone	City
Darryll	Sulymka	604-607-1440	Langley
John	Crichton	321-868-1221	Cape Canaveral
Rob	McLean	604-607-1440	Langley
Aeryn	Sun	456-345-7234	Unknown
Rygel XVI	Smith	894-256-4235	Unknown

UPDATE Customer \\ SET City='New York' \\ WHERE City = 'Unknown'

Table : Customer
FirstName	LastName	Phone	City
Darryll	Sulymka	604-607-1440	Langley
John	Crichton	321-868-1221	Cape Canaveral
Rob	McLean	604-607-1440	Langley
Aeryn	Sun	456-345-7234	New York
Rygel XVI	Smith	894-256-4235	New York

UPDATE Customer \\ SET City='Unknown' \\ ---if no where statement is used will update every record in the table

Table : Customer
FirstName	LastName	Phone	City
Darryll	Sulymka	604-607-1440	Unknown
John	Crichton	321-868-1221	Unknown
Rob	McLean	604-607-1440	Unknown
Aeryn	Sun	456-345-7234	Unknown
Rygel XVI	Smith	894-256-4235	Unknown

DELETE Statement

DELETE allows you to remove rows from tables.

Syntax :

DELETE FROM TABLE_NAME \\ WHERE condition

All of the Examples Below will use this table :

Table : Customer
FirstName	LastName	Phone	City
Darryll	Sulymka	604-607-1440	Langley
John	Crichton	321-868-1221	Cape Canaveral
Rob	McLean	604-607-1440	Langley
Aeryn	Sun	456-345-7234	Unknown
Rygel XVI	Unknown	894-256-4235	Unknown

---

Examples :

DELETE FROM Customer \\ WHERE FirstName='Rob'

Table : Customer
FirstName	LastName	Phone	City
Darryll	Sulymka	604-607-1440	Langley
John	Crichton	321-868-1221	Cape Canaveral
Aeryn	Sun	456-345-7234	Unknown
Rygel XVI	Unknown	894-256-4235	Unknown

DELETE FROM Customer \\ WHERE City='Unknown'

Table : Customer
FirstName	LastName	Phone	City
Darryll	Sulymka	604-607-1440	Langley
John	Crichton	321-868-1221	Cape Canaveral

---

Delete all records in a table :

DELETE FROM Customer

Table : Customer
FirstName	LastName	Phone	City

ORDER BY Clause

ORDER BY is used with a select statement to order the results that are returned.

Syntax :

SELECT * FROM TABLE_NAME \\ ORDER BY column_name [ASC|DESC]

ASC - Ascending DESC - Descending If ASC or DESC are not used the query will use the database default

All of the Examples Below will use this table :

Table : Customer
FirstName	LastName	Phone	City
Darryll	Sulymka	604-607-1440	Langley
John	Crichton	321-868-1221	Cape Canaveral
Rob	McLean	604-607-1440	Langley
Aeryn	Sun	456-345-7234	Unknown
Rygel XVI	Unknown	894-256-4235	Unknown

---

Examples :

SELECT * FROM Customer \\ ORDER BY FirstName

Table : Customer
FirstName	LastName	Phone	City
Aeryn	Sun	456-345-7234	Unknown
Darryll	Sulymka	604-607-1440	Langley
John	Crichton	321-868-1221	Cape Canaveral
Rob	McLean	604-607-1440	Langley
Rygel XVI	Unknown	894-256-4235	Unknown

SELECT * FROM Customer \\ ORDER BY LastName DESC

Table : Customer
FirstName	LastName	Phone	City
Rygel XVI	Unknown	894-256-4235	Unknown
Aeryn	Sun	456-345-7234	Unknown
Darryll	Sulymka	604-607-1440	Langley
Rob	McLean	604-607-1440	Langley
John	Crichton	321-868-1221	Cape Canaveral

SELECT * FROM Customer \\ ORDER BY City ASC, FirstName DESC

Table : Customer
FirstName	LastName	Phone	City
John	Crichton	321-868-1221	Cape Canaveral
Rob	McLean	604-607-1440	Langley
Darryll	Sulymka	604-607-1440	Langley
Rygel XVI	Unknown	894-256-4235	Unknown
Aeryn	Sun	456-345-7234	Unknown

SQL Functions

Functions are used to operate against a collection of values, but return a single value.

Syntax :

SELECT FUNCTION(COLUMN) FROM TABLE_NAME

Function	Description
AVG(column)	Returns the average value of a column
COUNT(column)	Returns the number of rows (without a NULL value) of a column
COUNT(*)	Returns the number of selected rows
COUNT(DISTINCT column)	Returns the number of distinct results
MAX(column)	Returns the highest value of a column
MIN(column)	Returns the lowest value of a column
SUM(column)	Returns the total sum of a column

This table will be used in all of the examples below :

Table : Stats
ID	DATA
1	42
2	24
3	72
4	14
5	50
6	35
7	99
8	66
9	34
10	80

---

AVG() SQL

SELECT AVG(DATA) FROM Stats

Result
51.6

---

COUNT() SQL

SELECT COUNT(DATA) FROM Stats

Result
10

---

MAX() SQL

SELECT MAX(DATA) FROM Stats

Result
99

---

MIN() SQL

SELECT MIN(DATA) FROM Stats

Result
14

---

SUM() SQL

SELECT SUM(DATA) FROM Stats

Result
516

GROUP BY Clause

GROUP BY divides a table into groups.

Syntax:

SELECT column1, SUM(column2) FROM tablename GROUP BY column1

This table will be used in the example below :

TABLE : Payroll
Name	Amount
John	1000
Bob	1500
Dan	5000
Frank	3000
John	1000
Bob	1500
Dan	5000
Frank	3000

---

Example :

SELECT Name, SUM(Amount) FROM Payroll GROUP BY Name

TABLE : Payroll
Name	Amount
John	2000
Bob	3000
Dan	10000
Frank	6000

SQL Quick Reference

Statement	Syntax
AND / OR	SELECT column_name(s) FROM table_name WHERE condition AND/OR condition
ALTER TABLE (add column)	ALTER TABLE table_name ADD column_name datatype
ALTER TABLE (drop column)	ALTER TABLE table_name DROP COLUMN column_name
AS (alias for column)	SELECT column_name AS column_alias FROM table_name
AS (alias for table)	SELECT column_name FROM table_name AS table_alias
BETWEEN	SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
CREATE DATABASE	CREATE DATABASE database_name
CREATE INDEX	CREATE INDEX index_name ON table_name (column_name)
CREATE TABLE	CREATE TABLE table_name (column_name1 data_type, column_name2 data_type, …….)
CREATE UNIQUE INDEX	CREATE UNIQUE INDEX index_name ON table_name (column_name)
CREATE VIEW	CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
DECLARE	DECLARE @Temp Datatype SET @Temp = Value
DELETE FROM	DELETE FROM table_name (Note: Deletes the entire table!!) or DELETE FROM table_name WHERE condition
DROP DATABASE	DROP DATABASE database_name
DROP INDEX	DROP INDEX table_name.index_name
DROP TABLE	DROP TABLE table_name
GROUP BY	SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1
HAVING	SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1 HAVING SUM(column_name2) condition value
IN	SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..)
INSERT INTO	INSERT INTO table_name VALUES (value1, value2,….) or INSERT INTO table_name (column_name1, column_name2,…) VALUES (value1, value2,….)
LIKE	SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern
ORDER BY	SELECT column_name(s) FROM table_name ORDER BY column_name [ASC/DESC]
SELECT	SELECT column_name(s) FROM table_name
SELECT *	SELECT * FROM table_name
SELECT DISTINCT	SELECT DISTINCT column_name(s) FROM table_name
SELECT INTO (used to create backup copies of tables)	SELECT * INTO new_table_name FROM original_table_name or SELECT column_name(s) INTO new_table_name FROM original_table_name
TRUNCATE TABLE (deletes only the data inside the table)	TRUNCATE TABLE table_name
UPDATE	UPDATE table_name SET column_name=new_value [,column_name=new_value] WHERE column_name=some_value
WHERE	SELECT column_name(s) FROM table_name WHERE condition