SQL Tutorial - Basic Commands

1
SQL Tutorial
Basic SQL Commands
2013 1keydata.com All Rights Reserved
2
Agenda

• Database Basics
• SQL Commands
• SELECT FROM
• WHERE
• ORDER BY
• GROUP BY
• HAVING

2013 1keydata.com All Rights Reserved
3
Database Basics
In a relational database, data is stored in
tables.
Database
2013 1keydata.com All Rights Reserved
4
Database Basics
Each table consists of columns and rows. Each
column is a field in a record, and there is a
column name associated with each column.
Database
2013 1keydata.com All Rights Reserved
5
Database Basics
Each row represents one record. When we say how
many records we have, we are referring to the
number of rows.
Database
2013 1keydata.com All Rights Reserved
6
SELECT FROM
SQL is structured similar to the English
language. The basic command for retrieving data
from a database table is to SELECT data FROM a
table. Not surprisingly, the keywords "SELECT"
and "FROM" make up the core of a SQL statement.
The syntax for SELECT FROM is

• SELECT COLUMN_NAME
• FROM TABLE_NAME

2013 1keydata.com All Rights Reserved
7
SELECT FROM

• Different ways of selecting data

Select more than 1 column SELECT
COLUMN_NAME_1, COLUMN_NAME_2 FROM TABLE_NAME
Select all columns SELECT FROM TABLE_NAME
Select unique values SELECT DISTINCT
Column_Name FROM TABLE_NAME
2013 1keydata.com All Rights Reserved
8
WHERE
Sometimes we want to retrieve only a subset of
the data. In those cases, we use the WHERE
keyword. The syntax for WHERE is

• SELECT COLUMN_NAME
• FROM TABLE_NAME
• WHERE CONDITION

CONDITION represents how we want the data to be
filtered.
2013 1keydata.com All Rights Reserved
9
ORDER BY
When we want to list the results in a particular
order (ascending or descending), we use the ORDER
BY keyword at the end of the SQL statement. The
syntax for ORDER BY is

• SELECT COLUMN_NAME
• FROM TABLE_NAME
• WHERE CONDITION
• ORDER BY COLUMN_NAME ASC DESC

2013 1keydata.com All Rights Reserved
10
MATHEMATICAL FUNCTIONS

• SQL has built-in mathematical functions to allow
  us to perform mathematical operations on the
  data. Common mathematical functions include
• SUM
• AVG
• COUNT
• MAX
• MIN

2013 1keydata.com All Rights Reserved
11
GROUP BY
To find the highest Sales_Amount across all
stores, we use the MAX( ) function in the
following SQL
SALES_HISTORY
SELECT MAX(Sales_Amount) FROM SALES_HISTORY
Date Store Sales_Amount


2013 1keydata.com All Rights Reserved
12
GROUP BY
To find the highest Sales_Amount for each store,
we change the SELECT portion to include Store
SALES_HISTORY
SELECT Store, MAX(Sales_Amount) FROM
SALES_HISTORY
Date Store Sales_Amount


2013 1keydata.com All Rights Reserved
13
GROUP BY
However, this SELECT statement by itself is not
enough. To allow SQL to correctly calculate what
we want, we need to use the GROUP BY keyword. In
the following example, the Store column after
GROUP BY tells SQL to apply the MAX function for
each Store.
SALES_HISTORY
SELECT Store, MAX(Sales_Amount) FROM
SALES_HISTORY GROUP BY Store
Date Store Sales_Amount


2013 1keydata.com All Rights Reserved
14
GROUP BY
To summarize, the syntax for GROUP BY is as
follows

• SELECT COLUMN_NAME_1, FUNCTION(COLUMN_NAME_2)
• FROM TABLE_NAME
• WHERE CONDITION
• GROUP BY COLUMN_NAME_1

2013 1keydata.com All Rights Reserved
15
HAVING
Previously we had talked about using the WHERE
keyword to filter results. We cannot use WHERE
to filter based on the result of a function,
because we need to specify the filtering
condition after SQL has calculated the function,
and consequently any filtering condition based on
the function needs to be specified after the
GROUP BY phrase. So we cannot use the WHERE
keyword because it is always used before GROUP
BY. HAVING is used to filter based on the
result of a function.
2013 1keydata.com All Rights Reserved
16
HAVING
The syntax for HAVING is as follows

• SELECT COLUMN_NAME_1, FUNCTION(COLUMN_NAME_2)
• FROM TABLE_NAME
• GROUP BY COLUMN_NAME_1
• HAVING (CONDITION based on FUNCTION)

2013 1keydata.com All Rights Reserved
17
HAVING
Using the SALES_HISTORY table we had earlier. If
we want to sum the sales amount for each store,
but only want to see results for stores with
total sales amount greater than 100, we use the
following SQL
SALES_HISTORY
SELECT Store, SUM(Sales_Amount) FROM
SALES_HISTORY GROUP BY Store HAVING
SUM(Sales_Amount) gt 100
Date Store Sales_Amount


2013 1keydata.com All Rights Reserved
18
Order of SQL Commands

• A SELECT statement has the following order
• SELECT FROM
• WHERE
• GROUP BY
• HAVING
• ORDER BY

2013 1keydata.com All Rights Reserved
19

• 1Keydata SQL Tutorial
• http//www.1keydata.com/sql/sql.html

2013 1keydata.com All Rights Reserved