This video covers the following SQL keywords:
SELECT ... FROM,
DISTINCT,
WHERE,
ORDER BY,
GROUP BY, and
HAVING. After you finish watching this video, you'll be able to write basic SQL queries to retrieve data from a relational database.
Embed this video using the code below
Video Transcription
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.
