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 All Columns
The * Symbol will give you all the columns for a given Select statement.

SELECT * FROM Customer

Result : SELECT * FROM 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

Result : 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 - Desending
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

Table of Contents

SQL Overview

SELECT Statement

WHERE Clause

INSERT INTO Statement

UPDATE Statement

DELETE Statement

ORDER BY Clause

SQL Functions

GROUP BY Clause

SQL Quick Reference