SQL is shorthand for Structured Query Language. Oracle SQL is a language and not an application. It is a language that can be used to communicate with databases and analyze data.

Before you use Oracle SQL, you need to understand what a database is. It’s a collection of data stored in an organized fashion. The data is stored in tables, in either a column or a row. Vertical data is called a column, and horizontal data is called a row.

Oracle SQL is composed of keywords such as SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. The 6 keywords mentioned above allow you to query a database and analyze its information. It's important that you understand these SQL basics during training.

When you write a query, please remember to use the SQL keywords below in the order you see them. Learning SQL can be tricky but these tips will help.


SELECT allows you to choose the columns you want to see within a table. If you are not sure which you would like to see … use the asterisk to see all columns within a table.

SELECT *
FROM u.agg_ia_clickstream

Once you see the columns, you can determine which ones you want to use.

SELECT c.date, c.unique_visitors PV’s
FROM u.agg_ia_clickstream c


FROM allows you to choose the table you want data from.

SELECT c.date, c.unique_visitors PV’s
FROM u.agg_ia_clickstream c


WHERE allows you to filter data on a row level. The WHERE clause is applied before the GROUP BY clause.

SELECT c.date, c.unique_visitors PV’s
FROM u.agg_ia_clickstream c
WHERE c.date > 20070801

In this case, the WHERE clause allows you to see ony unique visitors who have PV’s greater (>) than 10.


GROUP BY - When you use aggregate functions in a SELECT statement such as sum, count, etc., place the non-aggregate functions in the GROUP BY. An aggregate function operates on table rows to calculate and return a single function. Examples of aggregate functions are: sum(), avg(), count(), max(), and min(). You can intuitively guess what the functions do. Sum() for instance will sum, whereas avg() will average, etc.

SELECT c.date, sum(c.unique_visitors) PV’s
FROM u.agg_ia_clickstream c
WHERE c.date > 20070801
GROUP BY c.date

So, whenever you use an aggregate function in a SELECT statement, make sure that the NON aggregate functions are in the GROUP BY keyword. That is what I mean by punctuation in SQL. Whenever you write a sentence, you put a period at the end of the sentence. Whenever you write a SQL query, you put your punctuation in the GROUP BY.


HAVING group level filtering is commonly used to eliminate rows. It is written after the GROUP BY clause. Aggregate functions can be used in the HAVING clause.

SELECT c.date, sum(c.unique_visitors) PV’s
FROM u.agg_ia_clickstream c
WHERE c.date > 20070801
GROUP BY c.date
HAVING sum(c.unique_visitors) > 10


ORDER BY is used to order your output. The order of rows returned by a SQL query is not guaranteed unless you use a ORDER BY clause.

SELECT c.date, sum(c.unique_visitors) PV’s
FROM u.agg_ia_clickstream c
WHERE c.date > 20070801
GROUP BY c.date
HAVING sum(c.unique_visitors) > 10
ORDER BY sum(c.unique_visitors) desc

In this query, I want to see my results sorted by PV’s.

By following the instructions above, the learning process shouldn't be too difficult. With this training, you can now write basic Oracle SQL queries. When you do, don’t forget to follow the SQL basics order I provided: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. Also consider breaking up Oracle SQL statements over multiple lines to make them easier to read and debug. Have fun!


Required Tools:Oracle SQL
Quick Tips:Break up Oracle SQL statements over multiple lines to make them easier to read and debug.
SQL statements are case-insensitive. You can use capital letters or lower case letters; it’s up to you.
Hit F9 to run your query.