How To Use Oracle SQL

How To Use Oracle SQL: Learning SQL
Learn SQL Basics and Start Writing Queries Now
By Mini Guruswamy
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. Oracle SQL can be used to query databases and analyze data.

Before you use Oracle SQL, you need to understand what a database is. What is a database? It’s a collection of data stored in an organized fashion. The data is stored as Tables inside of a database. Data within tables are stored as rows. All data within a database is 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 data. It's important that you understand these SQL basics during SQL training.
When you're building a database, a great way to make sure that you'll be able to pull data from it easily, it's a good idea to know how to normalize it. To do this, you'll want to check out the popular and effective Database Normalization Techniques.
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.
1. SELECT allows you to choose the columns you want to see within a table. If you are not sure what columns 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
2. 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
3. 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.
4. 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.
5. 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
6. 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, learning SQL shouldn't be too difficult. With this SQL training, you can now write basic Oracle SQL queries. When you write a query, don’t forget to follow the SQL baiscs 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 writing queries and let me know what your progress is like.