Understanding the basics of Oracle SQL

Lets learn a little on how to interact with a database using a language called SQL. If you are already familiar with SQL, then this post is NOT for you. This blog post is tailored for beginners with interest in the query language.

SQL means Structured Query Language. Simply put, it is used to communicate with a database in order to define, access, or manipulate it's data.

Aside Oracle, there are other databases that accept these communications through SQL.

They are in no special order:

·         Sybase by SAP,

·         SQL Server by Microsoft,

·         MySql by Oracle

·         DB2 by IBM

·         PostgreSQL (Opensource) 

and the list goes on and on.

To know more about these database available out there click here

It should be noted that though they all share similar query syntax, all have some proprietary commands/queries that are unique to them.

Oracle calls its own type of SQL,  SQLPLUS which will be my focus for this blog post

So what is a relational database management system?

Before we write our first query, it’s important that we understand what a relational database management system is.

A relational Database management system (RDBMS) is a type of database management system (DBMS) that stores data in the form of related tables. It was invented by Edgar.F. Codd while working with IBM. This database management type allows data to be extracted in a less complex manner.

Relational tables are the sole reason why the use of SQL is possible. This will be clearer as you read on.

Let’s look at the basic structure of a TABLE

What you need for this practical

·         Oracle database with sample schemas enabled. Click here to see this post Installing Oracle Database 11g on Windows OS in simple steps to learn how to install Oracle 11g.

·         Access to HR schema.

·         An SQL work environment (SQLplus command prompt tool, SQL Navigator). Please note that there are several third party SQL work environments out there. My focus will be on these two.

·         If you do NOT have access to all that is stated above, then you can use Oracle’s online SQL environment called Live SQL. Click here to see my post on Oracle Live SQL: An online database scripting tool for the smart to learn more about it 

How to start an SQL work space.

For our practical we will be using the HR schema (which is just another way of saying HR user)

We will also be using SQLplus command prompt tool.

However if you desire to use other types of sql work space environments, I have a few options for you to pick from below:

SQL Navigator

• Click here to see a tutorial using SQL Navigator. Note that this will require you to have a locally installed database. Click here for a tutorial on how to install an oracle database.

Live SQL

• Click here to see tutorial using Live SQL. This is highly recommended if you do not have a locally installed database. You can start querying on the fly with this.

Now back to our main gist.

To use SQLplus you will need the following:

• Open a command prompt: Simply search for cmd the click on command prompt after the search
• We will be connecting to the database using the HR user account. Please note that the password of my HR user account is 'HR' (yeah very simple). This will be my reference password for this user account throughout the blog post.
• In the command prompt, type the following command:

         sqlplus hr/hr

What you have just typed is explained as follows:

• Sqlplus: Command to initiate Sql environment
• hr: Requested account for connection
• hr: Password for hr account (note that the password is in plain text even in the command prompt bar at the top of the window)

An alternative and more secured way to log in is to simply type the sqlplus command in the command prompt and hit enter.
You will be required to enter the username and the password. Notice that in this method, the password is not visible to anyone. See the screen shot below

Congratulations. 
You have now created a session for your queries.
If you feel comfortable using command prompt screen to execute SQL just like me, then there are a few things you must note:

The following commands should be executed in order to have the output of your queries well formatted:

SQL> SET LINESIZE 300

The LINESIZE value is the total number of output columns in your resulting query. The value I have selected is just my suggestion. You can tinker with several values till you get the one that suits you.
Please note that setting of linesize is not required on SQL Navigator or LiveSQL online environment. They are automatically well formatted.

Like said earlier, this practical will be done using SQLplus in command prompt.

Lets begin..

SELECT statement

The SELECT statement in SQL is used to fetch specific information from the database. It comes in the following  format:

Select [column1,column2]

from [object name];

The object name can be a table while the columns are the desired columns in the selected tables.

An asterix    *   can be used as a shortcut to fetch all column in a specified table instead.

A semi colon is used in SQL to end the query. Please note this. Your query will not run because of this.

Let’s try a simple example.

We will be fetching all the columns in a table called EMPLOYEES

Select * from employees;

We can however decide to select only the following instead:

·         Employee_id

·         First_name

·         Last_name

·         salary

So let’s write this query

Select employee_id,first_name,last_name,salary  from employees;

What if you do not know the column that are in your table of interest and would like to see the column to query against. There is a way to achieve this, using the DESCRIBE command as seen below:

I will use the describe command to show the column in the EMPLOYEES table

DESCRIBE EMPLOYEES

A shortcut for the DESCRIBE command is DESC. It can be used as  follows:

DESC EMPLOYEES

  

Using Select Command with Arithmetic operators

Arithmetic operators can be used with the select statement to create new expressions. We can use the following operators below:

Let us see an example with the Multiply Operator below:

Supposed you are required to give a report of the annual salary of all staff with the following data:

·         Staff last name

·         Salary

·         Upgraded salary

The script will be written as below:

select last_name, salary, salary*12 from employees;

Aliasing

You can rename the column title of your table to any desired name. As you can see from our last example we had one of the column titles as SALARY*12. This can be changed to a more acceptable form. Let us see this in the next example.

You have been asked to represent your previous report with legible column titles.

The SALARY*12 was requested to be changed to ANNUAL SALARY.

Here is the script below:

select last_name, salary, salary*12  as "ANNUAL SALARY"  from employees;

Limiting rows though the use of WHERE clause

In real life using * to fetch all data could be a bad idea as real world data are always very huge. Now supposing you don’t what the whole data but instead need just a few specific data. Then this command is for you.

The WHERE clause.

It should be noted that the WHERE clause cannot be used without the following Comparison conditions seen below:

Comparison conditions

We shall look at some of the operators

Equal to (=)

Suppose you are required to prepare a report of staff earning exactly $2,500 with the following data:

·         Staff id

·         Staff last name

·         Salary

The script will be written as below:

select employee_id,last_name,salary from employees where salary='2500';

Greater than (>)

You are required to prepare a report of staff earning over $5,000 with the following data:

·         Staff id

·         Staff last name

·         Salary

The script will be written as below:

select employee_id,last_name,salary from employees where salary > '5000';

Greater than or equal to (>=)

You are required to prepare a report of staff earning $5,000 or more with the following data:

·         Staff id

·         Staff last name

·         Salary

The script will be written as below:

select employee_id,last_name,salary from employees where salary >= '5000';

BETWEEN...AND... (with Integers)

You are required to prepare a report of staff earning between $5,000 and $10,000 with the following data:

·         Staff id

·         Staff last name

·         Salary

The script will be written as below:

select employee_id,last_name,salary from employees where salary between  '5000' and '10000';

BETWEEN...AND... (With Dates)

You are required to prepare a report of staff employed from 2006 up until 2008 with the following data:

·         Staff id

·         Staff last name

·         Salary

·         Date staff was employed

The script will be written as below:

select employee_id,last_name,salary,hire_date from employees

where hire_date between '1-jan-2006' and '31-dec-2008';

IN

You are required to prepare a report of staff employed that earn exactly $3,000, $5,000 and $10,000 with the following data:

·         Staff id

·         Staff last name

·         Salary

·         Date staff was employed

The script will be written as below:

select employee_id,last_name,salary,hire_date from employees

where salary IN ('3000','5000','10000');

Logical Conditions

The WHERE clause can also be used with what is called Logical conditions. These allow us to add more to our selection criteria. The logical conditions available are seen below:

Let’s look at queries using this conditions

  

AND

You are required to prepare a report of staff employed that earn exactly $3,000, $5,000 and $10,000 and where employed within the period of 2006 through 2007 with the following data:

·         Staff id

·         Staff last name

·         Salary

·         Date staff was employed

The script will be written as below:

select employee_id,last_name,salary,hire_date from employees

where salary IN ('3000','5000','10000')

AND hire_date between '1-Jan-2006' and '31-Dec-2007';

Please note that AND operator is a very strict logical condition. It ensures all conditions starting from the WHERE clause are true or else the query output returns nothing. Let’s  try and introduce a false condition. For our script let us use a future date for our hire date say year 3000 as seen below

select employee_id,last_name,salary,hire_date from employees

where salary IN ('3000','5000','10000')

AND hire_date between '1-Jan-3000' and '31-Dec-3000';

As you can see below, that the query did not return any value even though the WHERE condition was true

OR

You are required to prepare a report of staff employed that earn exactly $3,000, $5,000 and $10,000 or where employed within the period of 2006 through 2007 with the following data:

·         Staff id

·         Staff last name

·         Salary

·         Date staff was employed

The script will be written as below:

select employee_id,last_name,salary,hire_date from employees

where salary IN ('3000','5000','10000')

OR hire_date between '1-Jan-2006' and '31-Dec-2007';

Now OR is not a strict logical condition thus if any of the conditions starting from the WHERE clause is true, the query output will always return values. Let’s retry our false condition again where we used a future date for our hire date say year 3000 as seen below.

select employee_id,last_name,salary,hire_date from employees

where salary IN ('3000','5000','10000')

OR hire_date between '1-Jan-3000' and '31-Dec-3000';

The query worked because the first condition (in the WHERE clause) was true.

NOT

This is just the negation of any requirement in the query. Let’s have a look at an example:

You are required to prepare a report of staff employed that do NOT earn exactly $3,000, $5,000 and $10,000 with the following data:

·         Staff id

·         Staff last name

·         Salary

·         Date staff was employed

The script will be written as below:

select employee_id,last_name,salary,hire_date from employees

where salary NOT in ('3000','5000','10000');

Sorting your data using the ORDER BY clause

Data can be sorted either in ascending or descending order using the order by clause

The options to be used with the order by clause are

ASC  - To arranged in ascending order

DESC  - To arranged in descending order

You can order with any column you desire.

Let us now look at an example where we used the SALARY column for ordering:

You are required to prepare a report of staff employed with the following data:

·         Staff id

·         Staff last name

·         Salary

·         Date staff was employed

The report is to have the staff with the lowest salary at the top.

The script will be written as below:

select employee_id,last_name,salary,hire_date from employees

ORDER BY salary ASC;

LOWER and UPPER Functions

In general terms, function takes an argument, processes it and returns a value. These Lower and Upper functions are called case manipulation functions

Oracle search criteria is a little strict as your input for the search is case sensitive. What I mean is that you will need to know the exact case used for the data before you can successfully extract it.

Case manipulation functions helps you to bypass these issues. It also helps to greatly reduce error in reporting when some data are not included in the report because of wrong use of letter case.

Let’s take a few examples to look at how they work

LOWER and UPPER function test

You were asked to generate a report to display staff with the last name of “KING” with the following data:

·         Staff id

·         Staff last name

·         Salary

·         Date staff was employed

Let us write our query as seen below:

select employee_id,last_name,salary,hire_date from employees

where last_name='KING';

As you can see we searched using upper case but the result was “no data” 

Now let us try lower case instead:

select employee_id,last_name,salary,hire_date from employees

where last_name='king';

Still no data. Hmmmmmmmm

Now because we get no data does not mean the data with a last name of KING does not exist.

Let us try another method. This time let's use our case manipulation function.

select employee_id,last_name,salary,hire_date from employees

where LOWER(last_name)='king';

Voila!!! Now we have data..

So what really happened?

All I did was tell the database that I was typing in LOWER CASE and it should bypass the case sensitivity constraints.

  

Now supposed I used  LOWER case function but then typed in upper case. 

Let’s see the result.

select employee_id,last_name,salary,hire_date from employees

where LOWER(last_name)='KING';

No data.
This means once you declare your function as LOWER case then you should only type in lower case.

Now let’s correct the error by changing the function to UPPER instead.

select employee_id,last_name,salary,hire_date from employees

where UPPER(last_name)='KING';

And it works like a charm.

I hope you now understand the case functions (LOWER and UPPER) very well now.

Table Join

Recall that the queries we have been running since where extracted from a particular table called EMPLOYEES.

Now what if you were required to generate reports that included other tables, how would you achieve this?  This is what this topic is all about.

Joining table to generate a report requires identification of a relationship tying the tables together.

Let us look at a few examples:

The tables we will be using in our tutorial are:

EMPLOYEES

DEPARTMENTS

LOCATION

 Below is a matrix of the columns in all the tables listed above and how they relate to each other:

To find this columns simply type the following command

DESC <table name>

Let’s get busy and write our first query

Supposing we were asked to generate a report of staff employed with the following data:

·         Staff id

·         Staff last name

·         Salary

·         Department of staff

·         Address of staff location

Notice that not all the columns come from one table. They are spread across several tables

Our script will thus look like this:

select employee_id,last_name,salary,department_name,street_address

from employees;

Oop! So this didn’t work. What went wrong………….

Recall I mentioned that the columns are from different tables and not only extracted from the employees table. That was why the query was wrong.

Lets fix this query

select employee_id,last_name,salary,department_name,street_address

from employees, departments,locations;

This works but it gives us a never ending query thus this is not the real solution.

The query you just ran is called a CARTESIAN PRODUCT error.

We can avoid this error by expressing the relationship of the table in the query we are running

Here we go…

select employee_id, last_name,salary, department_name, street_address

from employees E, departments D,locations L

where E.department_id=D. department_id

and

D.location_id=L.location_id;

IT WORKS!!!!!

So you can now see the importance of table relationship.

This is the greatest advantage of Relational Tables in a Relational Database.

 To also be on a safer side the query can also be written like this to avoid possible errors.

select E.employee_id,E.last_name,E.salary,D.department_name,L.street_address

from employees E, departments D, locations L

where E.department_id=D. department_id

and

D.location_id=L.location_id;

What I have done here is to assign every column to be displayed with their respective table codes defined in the “from” query.

Let’s run the query

Also NOTE that there are several other methods of writing a table JOIN query. This is just one of the methods.

And that’s all about this post.

There are loads and loads of more commands and queries you can learn on SQLPLUS, however these are the basics needed to startup.

If you are interested in furthering your SQL knowledge, I would recommend the following sites below:

·         sqlzoo

·         beginner-sql-tutorial

·         sql-plus

·         sqlcourse