Welcome to the SQL Tutorial, presented to
you by 1Keydata.com. In this tutorial, we will go over the basics
of SQL, so you'll be able to use SQL to write simple queries to get data from a relational
database. We will first provide some background information
on how data gets stored in a relational database. Next we will go over the basic SQL commands. In particular, you will learn about: SELECT / FROM
WHERE ORDER BY
GROUP BY HAVING In a relational database, data is stored in
tables. Each table has a table name. A table consists of columns and rows. Each column is a field in a record, and there
is a column name associated with each column. Each row represents one record. Usually when we say how many records we have,
we are referring to the number of rows. With this information, now we are ready to
dive into SQL. SQL is structured very much like the English
language. At the high level, 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 the SQL statement. In fact, the syntax for SELECT FROM is pretty
straightforward: SELECT COLUMN_NAME FROM TABLE_NAME. We can select one or more columns. To select more than one column, we simply
list all the column names we want to select separated by comma. If we want to select all columns, we say SELECT
*. If we want to select unique values, we add
the keyword DISTINCT in front of the column name. Next we look at the WHERE keyword. In the previous example, we are retrieving
all records from a table. While we do want to do this sometimes, more
frequently we want to select only a subset of the data. When we want to filter our results, we use
the WHERE keyword after the FROM keywords. So the syntax becomes SELECT COLUMN_NAME FROM TABLE_NAME WHERE [Condition] Condition is where we specify how we want
the data to be filtered. For example, we may specify that only people
whose first name starts with "A" is selected. Now we turn our attention to the ORDER BY
keyword. Sometimes we want the output to be listed
in a particular order, such as in ascending order or in descending order. To achieve this, we use the OREDER BY keyword. The syntax for this will be something similar
to the following: SELECT COLUMN_NAME FROM TABLE_NAME WHERE [Condition]
ORDER BY COLUMN_NAME either ASC or DESC Notice that ORDER BY is at the end of the
statement, because we want to specify the ordering after we have specified what data
we want to get. To show results in ascending order, i.e.,
from A to Z or from smaller values to larger values, we can add ASC at the end. To show results in descending order, i.e.,
from Z to A or from larger values to smaller values, we can add DESC at the end. Usually if you do not specify this, the default
is ascending. Next we discuss the GROUP BY keyword. Before we go into details about GROUP BY,
I'd like to talk first about functions. The typical mathematical functions you see
in SQL are SUM, AVG, COUNT, MAX, and MIN. Functions allow us to operate on the values
of specific columns. For example, the MAX function allows us to
retrieve the largest value in a column. Let's say we have a table that has three columns:
Date, Store, and Sales Amount. To find out what was the highest sales amount
across all dates and all stores, we can simply use SELECT MAX(Sales Amount). On the other hand, if we want to find out
the highest sales amount for each store, then we'll need to retrieve both the Store column
and the sales amount column, and our SELECT statement becomes SELECT Store, MAX(Sales Amount) 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. Here, we will add GROUP BY Store to let SQL know that we want to calculate
the maximum sales amount for each store. So how the function is calculated is dependent
on the column or columns specified in the GROUP BY, not the column or columns specified
in the SELECT. Often the columns in the GROUP BY phrase are
same as the columns in the SELECT statement, but this is not always the case. To recap, the general syntax for GROUP BY
is SELECT ¡§COLUMN_NAME1¡¨, FUNCTION(¡§COLUMN_NAME2¡¨)
FROM ¡§TABLE_NAME¡¨ GROUP BY ¡§COLUMN_NAME1¡¨ Previously we had talked about using the WHERE
keyword to filter results. What if we want to filter based on the result
of a function? Can we also use WHERE for this? The answer is no, and the reason is that we
will need to specify the filtering condition after SQL has calculated the function, and
as a result 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. This is where the HAVING keyword comes in. The syntax for HAVING is SELECT ¡§COLUMN NAME 1¡¨, FUNCTION(¡§COLUMN
NAME 2¡¨) FROM ¡§TABLE_NAME¡¨
GROUP BY ¡§COLUMN NAME 1¡¨ HAVING (Condition based on Function) In the example we had previously mentioned,
if we only want to show results where the maximum sales amount is over 100, we will
add HAVING (sales Amount > 100) at the end of
the SQL query. In this tutorial we have introduced the SELECT
FROM, WHERE, ORDER BY, GROUP BY, and HAVING keywords. In a SQL statement, what order do we put them? The correct order is SELECT FROM, then WHERE,
then GROUP BY, then HAVING, and finally ORDER BY. That concludes this SQL tutorial. Thank you for viewing. Now you have the knowledge to write basic
SQL statements to get data out of a relational database. You can learn more about SQL by going to the
1Keydata SQL Tutorial at www.1keydata.com/sql/sql.html.
