Give below is what you learn and can expect to be able to do after attending this tutorial…

• Brief history and overview of SQL
• Set-up environment variables to invoke sqlplus and log on to a Oracle database from a reflection session
• Execute basic SQL queries
• Get all the data in a table
• Get named columns from a table
• Get named columns from a table and order them
• Perform arithmetic on column values
• Prevent duplicate row selection
• Selecting only data that meets a criteria with the WHERE clause
• SQL operators in WHERE clauses
• Multiple condition WHERE clauses
• Group Functions
• Sub-queries
• Select data from multiple tables
• Execute basic SQL DML commands
• Insert
• Update
• Delete
• Use basic DDL commands
• create table
• alter table
• create index
• truncate table
• drop table
• Use basic access control commands
• grant
• revoke
• Modify sqlplus environment
• Write and execute small SQL scripts
 
Structured Query Language

Structured Query Language or SQL was originally developed by IBM for its System/R Project. IBM later used it in its products SQL/Data System (SQL/DS) and Database 2 (DB2). SQL today became a language of choice to access the data and structures within a relational database and is implemented and supported by most commercially available Relational Database Management System vendors.

SQL was designed to be an English like language using English phrases to manipulate the database. It is non procedural. You specify what you want done but not how to do it. The how part is taken care of by the RDBMS. Each RDBMS has an in-built query optimizer which parses your SQL statements and works out the best path to retrieve/manipulate the data.

SQL is designed to be used by a wide spectrum of users- DBA's, application developers, operators  and end users.

It provides commands to do the following …

• finding (querying) data
• inserting, updating and deleting data
• creating, modifying and deleting database objects
• controlling access to the database and database objects
• Transaction Control

The different SQL statements could be  grouped into 4 different categories as explained below…

DATA QUERY LANGUAGE (DQL) -  SQL statements  to retrieve data from the database and transform it.   SELECT statement falls into this category.

DATA MANIPULATION LANGUAGE (DML) - SQL statements to insert, change or delete data from the database. DELETE, INSERT, UPDATE   statements  fall into this category.

DATA DEFINITION LANGUAGE  (DDL) - SQL statements to define or alter database structures. DROP, TRUNCATE, CREATE, ALTER  statements  fall into this category.

Access Control - SQL statements to either grant or revoke privileges to access and manipulate  database objects. 

REVOKE, GRANT statements  fall into this category.

Transaction Control - SQL statements that commit or rollback changes to data.

COMMIT, ROLLBACK statements fall into this category.

To connect to an oracle database and execute sql commands you can use an oracle supplied tool called sqlplus.
 
Environment set-up for invoking sqlplus and connect to an Oracle  database

Add the following lines to your .profile after you login to your unix environment. After adding the following lines log out and log back in to make sure that the .profile is run.

# Replace with the path at your installation
export ORACLE_HOME=/oracle/product/8.1.7
# Replace with sid name at your installation
ORACLE_SID=xyz
export ORACLE_SID
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export LD_LIBRARY_PATH
LIBPATH=$LIBPATH:/usr/lib:$ORACLE_HOME/lib
export LIBPATH
PATH=$PATH:$ORACLE_HOME/bin
export PATH

The above lines set-up paths for oracle binaries and libraries.

The ORACLE_SID environment variable identifies the database that you would connect to when you invoke sqlplus. In our case we would be connecting to a database named stgt.

After setting up the environment you can invoke sqlplus by entering sqlplus at your unix prompt as shown below. Enter your username and password when prompted. You will get SQL prompt if you login successfully. You can then enter and execute all your sql commands at the SQL prompt.

$ sqlplus

SQL*Plus: Release 8.1.7.0.0 - Production on Mon Aug 26 11:33:41 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Enter user-name: rnemani
Enter password:

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

SQL>

If you want to terminate your sqlplus session you simply enter exit at your SQL prompt as shown below…

SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 – Production

$

DATA QUERY LANGUAGE (DQL)

The SELECT statement is the most widely used command of SQL. It allows you to retrieve the data from the database. Most SQL statements will need a semi-colon ";" to indicate the end of the statement.

Get all the data  in a table

Select * from price_list ;

Gets all the columns and all the rows in the table.

Get named columns from a table

Select  division_no, article_nbr, price_list_price, price_list_price
From price_list ;

Get named columns from a table and order them

Select  division_no, article_nbr, price_list_price
From price_list
Order by division_no, article_nbr ;

Perform arithmetic on column values

Select  division_no, article_nbr, price_list_price, price_list_price*0.05
From price_list ;

Preventing duplicate row selection

Select  distinct division_no From price_list ;

Selecting only data that meets a criteria  with the WHERE clause

Select  division_no, article_nbr, price_list_price
From price_list
Where division_no = 72 ;

SQL operators in WHERE clauses

Select  division_no, article_nbr, price_list_price
From price_list
Where division_no in  (71, 72)  ;

Select  division_no, article_nbr, price_list_price
From price_list
Where price_list_price > 100 ;

Select  division_no, article_nbr, price_list_price
From price_list
Where division_no  not in  (71, 72)  ;

select * from division
where division_desc like 'T%' ;

Multiple condition WHERE clauses

Select * from price_list
Where division_no = 93
And basis_company_id = 66 ;

Select * from price_list
Where division_no = 93
or price_list_price > 100 ;

Group Functions

Select count(*) from price_list ;

Select max(price_list_price)
From price_list ;

Select avg(price_list_price)
From price_list ;

Select division_no, count(*)
from price_list
group by division_no ;

Select division_no, max(price_list_price), min(price_list_price)
from price_list
group by division_no ;

Select division_no, max(price_list_price), min(price_list_price)
from price_list
group by division_no
having min(price_list_price) > 10 ;

Select division_no, basis_company_id, count(*)
from price_list
group by division_no, basis_company_id ;

Selecting data from multiple tables (Join)

Select division_desc, outlet_name
From division a , outlet b
Where a.division_no = b.division_no ;

Select division_desc, outlet_name
From division a, outlet b
Where a.division_no = b.division_no
And a.division_no = 51 ;

Sub-queries

Select count(*) from price_list
Where division_no in (
select division_no from division where price_group_user = 'Y'
) ;

Select count(*) from price_list
Where division_no not in (
select division_no from division where price_group_user = 'Y'
)
;

Select count(*) from price_list  a
Where exists in (
select division_no
from division b
where b.price_group_user = 'Y'
and b.division_no = a.division_no
)
;

Select count(*) from price_list  a
Where not exists in (
select division_no
from division b
where b.price_group_user = 'Y'
and b.division_no = a.division_no
)
;
DATA MANIPULATION LANGUAGE (DML)

In order for us to be able to retrieve data from tables we need to first have them populated and properly maintained so that they contain the relevant data. This is exactly what the DML commands are used for.

INSERT

Insert command is used for inserting new data into a table.

insert into division
values (98, 'This is a New Dvsn','Y','Y','N')
;

The above statement will create a new row .

insert into division
(division_no,division_desc)
values (98, 'This is a New Dvsn') ;

The above statement will create a new row but with only division_no and division_desc columns populated with values and other columns set to NULL.

Insert into new_division_table
Select * from division ;

The above statement Inserts rows from division table into new_division_table table (provided the structure of new_division_table table is identical to the structure of division table).

You have to issue a commit statement if you want the data to be saved in the table permanently. Alternatively you can issue a rollback, if you do not want your changes to be saved to the database.

A commit or a rollback command should always be issued to commit or rollback a transaction. A transaction could be made up of one or more DML commands.

A commit statement looks as follows …

Commit ;

A rollback statement looks as follows …

Rollback ;

Make sure that you issue a commit or rollback before you exit your sqlplus session. Sqlplus will issue a commit by default when you exit.

UPDATE

The UPDATE command is used for updating the column values of an existing row in a table.

Following are some examples …

update division
set division_desc = 'My new division'
where division_no = 98 ;

update price_list
set price_list_price = price_list_price * 0.05
where price_list_price < 1 ;

You can use a sub-query to update a column as shown below …

update new_division_table a
set division_desc = (select division_desc from division b
                     where b.division = a.division
                     )
;

DELETE

Delete command is used for removing data (rows) from a table.

delete from  new_division_table ;

The above statement will delete all rows from new_division_table table.

delete from  new_division_table
where division_no = 98 ;

The above statement will delete the row corresponding to the division_no 98 from the new_division_table table.

Some more examples …

delete from  new_division_table
where division_no >= 98 ;

delete from  new_division_table
where division_no in (98, 99) ;

delete from  new_division_table
where division_no != 98 ;

delete from  new_division_table
where division_no not in (98, 99) ;

DATA DEFINITION LANGUAGE (DDL)

In order for us to be able to store and retrieve data from tables we need to first have them created. This is exactly what the DDL commands are used for.

Following is an example of "create table " command in its simplest form…

create table new_division_table
( DIVISION_NO  NUMBER(2) NOT NULL,
DIVISION_DESC  VARCHAR2(30),
PRICE_GROUP_USER  VARCHAR2(1),
TRADE_GROUP_USER  VARCHAR2(1),
SALES_TYPE_USER  VARCHAR2(1)
) ;

The above command creates a table called new_division_table. It will be empty. The NOT NULL clause  for division_no column is to make sure that division_no column must always have a value whenever data is inserted into this table and it can not be updated to a NULL value.

You can also create a table from an existing table as shown below…

create table new_division_table as
select * from division ;

The above command creates a table called new_division_table and populates it with data from division table.

create index x1_new_division_table
on new_division_table (division_no) ;

The above command creates an index called x1_new_division_table.

Alter table  new_division_table
modify               DIVISION_DESC  VARCHAR2(40) ;

The above command alters the width of division_desc column to 40 from 30.

truncate table new_division_table ;

The above command deletes all data in the table

drop table new_division_table ;

The above command drops the table from the database. All the data within the table is permanently lost. So do exercise caution when using this command. I have one suggestion to avoid mistakes with DROP command – DO NOT USE IT in the first place.

Note : DDL commands do not need a commit because they issue an implicit commit. This means that we can not rollback any DDL once it is executed.

There are a number of other DDL commands which I think are the beyond the scope of this brief introduction to SQL.
 
Access Control Commands

When a user creates a database object such as a table, it gets created under the ownership (schema) of the user creating it. The user who created the table owns the table.  In order for others to be able to access or store data in this table the owner has to grant appropriate privileges to other users.

Following are some examples but first the disclaimer…

"Following examples are based on fictitious situations. Any resemblance to any names of any persons or projects or places is purely coincidental. " 

Example 1.

Tom and Harry work in the IT department for a major bottling company and are working on a new project (actually cleaning up an old project) which requires them to work on an Oracle database and deal with tables etc...

During the course working on this project, Tom creates a table called new_division_table and he wants Harry to be able to select data from this table. So he executes the following command…

grant select on new_division_table to Harry ;

Now Harry can access data from this table as follows..

select *  from Tom.new_division_table ;

Note that the username of the owner of the table has to be prepended to the table name, if the table is not owned by the user trying to access the table.

Example 2.

Harry tells Tom that he wants to be able to do more than mere selecting of data from the table.

Tom then executes the following GRANT command to give Harry more privileges…

grant insert, update, delete on new_division_table  to Harry ;

Harry then tests his newly granted privileges on the table as follows…

delete from  Tom.new_division_table ;
commit ;

Example 3.

Now Tom is mad and wants to take away all but SELECT privilege from Harry on the new_division_table. He executes the following command …

revoke   insert,update, delete on new_division_table from Harry ;

I Hope the above examples clarify the use of Access control commands.

Changing sqlplus environment

You can change the default behaviour of how the results are displayed within sqlplus. You can do this by using the set command of sqlplus.

set pagesize 80

The above command makes sure columns headings are repeated  after every 80 lines of the results.

set pagesize 0

The above command supresses printing of headings.

set linesize  70

Above command sets the line size to 70.

spool /home/rnemani/division.list
select * from division ;
spool off

The above commands create a text file called division.lst in /home/patuser directory. This file would contain the output of division table.

If you want see your current sqlplus environment settings give the following command

show all

There are a number of useful set commands that you could use and would use as you start using sqlplus regularly.

Using SQL scripts

You can save regularly used sql commands in a sql script using a text editor such as vi.

For example you can save the following commands in a  script file called get_div.sql  using vi.

spool /home/rnemani/division.list
select * from division ;
spool off

You can then start up a sql session and execute the script at the sql prompt as shown below.

SQL> @get_div.sql

The above command would execute all the commands in the script as if they were entered at SQL prompt.

You can know the structure (column names, data types etc…) of any table, by using the following command

DESC table_name

for example…

SQL> desc division
 Name                                Null?    Type
 ----------------------------------- -------- --------------------
 DIVISION_NO                         NOT NULL NUMBER(2)
 DIVISION_DESC                                VARCHAR2(30)
 PRICE_GROUP_USER                             VARCHAR2(1)
 TRADE_GROUP_USER                             VARCHAR2(1)
 SALES_TYPE_USER                              VARCHAR2(1)

SQL>

Oracle returns errors when you execute your sql statements, if there is a syntax error(s) or if it is unable to find the objects that are referred to in the SQL statement. Given below is an example…

SQL> select count(*) from rkn_branch_extension ;
select count(*) from rkn_branch_extension
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

If you want know more about this error then you can issue the following command at your UNIX prompt.

$ oerr ORA 942

Conclusion

As you can see SQL is very easy to use and intuitive. One other good thing about SQL is that it is portable for the most part across databases that support it. I hope this tutorial gave you enough information to get started with SQL.