This tutorial is aimed at giving a beginner a brief introduction to the Structured Query Language or SQL and get you started with using sqlplus to log on to Oracle and access and manipulate data.
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.
