HARAMAYA UNIVERSITY

COLLEGE OF COMPUTING AND INFORMATICS

DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

CHAPTER – III

SQL – The Relational Database Standard (Oracle)

Structured Query Language (SQL)

SQL is an acronym for Structured Query Language. It is a unified language for

defining, querying, modifying and controlling the data in a relational database.

SQL is the standard language for Relation Database System. All relational database
management systems like MySQL, MS Access, Oracle and Sybase.

1 Prepared by [Link], Professor, Dept. of Information Science, CCI,

Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

Characteristics of SQL

The characteristics of SQL are;

i) It is extremely flexible

ii) It uses a free form syntax

iii) An SQL request can be written in a variety of ways.

Data Types available in SQL

SQL supports the following data types:

(i) CHARACTER (n)

(ii) CHARACTER VARYING (n)

(iii) BIT (n)

(iv) BIT VARYING (n)

(v) NUMERIC (p,q)

(vi) DECIMAL (p,q)

(vii) INTEGER

(viii) SMALLINT

(ix) FLOAT (p)

Different categories of SQL commands

SQL commands are divided into the following categories

i) Data Definition Language (DDL)

ii) Data Definition Language (DML)

iii) Transaction Control Language (TCL)

iv) Data Control Language (DCL)

2 Prepared by [Link], Professor, Dept. of Information Science, CCI,

Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

DDL Commands

DDL is used to create, alter and delete database objects.

The commands used are…

CREATE TABLE:

Syntax:

SQL> Create table tablename (Column1 data type, Column2 data type, Column3 data
type, …………... Column n data type m),

Example:

SQL> Create table employee (Name varchar2 (15), Eid number (6),

Age number (3), City varchar2 (30), State varchar2 (20) );

ALTER TABLE:

This command is used to add or drop or modify the attributes from the existing table.

SYNTAX:

Adding an attribute:
ALTER TABLE <table_name> ADD column_name datatype;

Dropping an attribute:
ALTER TABLE <table_name> DROP COLUMN column_name;

Modifying an attribute:
ALTER TABLE <table_name> MODIFY column_name newdatatype;

Renaming an attribute:
ALTER TABLE <table_name> RENAME COLUMN <old-column_name>
<new-column_name>;

3 Prepared by [Link], Professor, Dept. of Information Science, CCI,

Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

DROP TABLE:

This command is used to remove a relation from an SQL database. This command deletes not
only the records of the table but also the entire schema of the database.

SYNTAX:

DROP TABLE < TABLE NAME >;

TRUNCATE TABLE

If there is no further use of records stored in a table and the structure has to be retained then
the records alone can be deleted.

SYNTAX:

TRUNCATE TABLE <TABLE NAME>;

RENAME TABLE

SYNTAX:

ALTER TABLE TABLENAME RENAME TO NEWTABLENAME;

DML commands

DML commands let the user insert, modify and delete the data in the database. SQL

provides three data manipulation statements, they are…

INSERT:

Insert command is used to insert the values into the table. There are three ways to insert a
record into the database.

4 Prepared by [Link], Professor, Dept. of Information Science, CCI,

Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

1. Inserting Data’s in specified columns:

Syntax:
INSERT INTO table_name(col1,col2,…….,coln)
VALUES(val1,val2,……,valn);

2. Inserting Values:

Syntax:
INSERT INTO table_name VALUES(val1,val2,……...,valn);

3. Inserting Multiple Rows:

Syntax:

INSERT INTO table_name VALUES(&col1,’&col2’,’&col3’,…….,&coln);

SELECT:

The select statement is used to query a database. This statement is used to retrieve the
information from the database. The SELECT statement can be used in many ways. They
are…

1. Selecting some columns:

To select specified number of columns from the table the following command is used.

Syntax:

SELECT column name FROM table_name;

2. Query All Columns:

To select all columns from the table * is used instead of column names.

Syntax:

SELECT * FROM table_name;

5 Prepared by [Link], Professor, Dept. of Information Science, CCI,

Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

UPDATE:

UPDATE command is used to change a value of a record in the database.

Syntax:

UPDATE table_name SET column name =new value WHERE column name=some value;

DELETE:

The DELETE statement is used to delete rows in a table.

Syntax:

DELETE FROM table_name WHERE column name=some value;

TCL commands

TCS manages all changes made by the DML commands. Some of the transaction,

control statements are…

COMMIT

Use the COMMIT statement to end your current transaction and make permanent all

changes performed in the transaction

ROLLBACK

Use the ROLLBACK statement to undo work done in the current transaction or to

manually undo the work done by an in-doubt distributed transaction.

SAVEPOINT

SAVEPOINT command is used to temporarily save a transaction so that you can

ROLLBACK to that point whenever necessary.

Following is SAVEPOINT command's syntax,

SAVEPOINT savepoint-name;

6 Prepared by [Link], Professor, Dept. of Information Science, CCI,

Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

DCL commands

DCL commands manages permanent change and restores the changes with the

following commands they are…

GRANT and

REVOKE

GRANT Privileges on Table

The GRANT command is used to give various privileges to tables for users. These

privileges can be any combination of SELECT, INSERT, UPDATE, DELETE, or ALL.

Syntax

GRANT privileges ON object TO user;

Privilege Description

SELECT Ability to perform SELECT statements on the table.

INSERT Ability to perform INSERT statements on the table.

UPDATE Ability to perform UPDATE statements on the table.

DELETE Ability to perform DELETE statements on the table.

ALL All privileges on table.

Object

The name of the database object that you are granting privileges for. In the case of

granting privileges on a table, this would be the table name.

User

The name of the user that will be granted these privileges.

7 Prepared by [Link], Professor, Dept. of Information Science, CCI,

Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

Example

Let's look at some examples of how to grant privileges on tables in Oracle.

For example, if you wanted to grant SELECT, INSERT, UPDATE, and DELETE

privileges on a table called SUPPLIERS to a user name smith, you would run the

following GRANT statement:

GRANT SELECT, INSERT, UPDATE, DELETE ON SUPPLIERS TO smith;

You can also use the ALL keyword to indicate that you wish ALL permissions to be

granted for a user named smith.

For example:

GRANT ALL ON suppliers TO smith;

REVOKE Privileges on Table

Once you have granted privileges, you may need to revoke some or all of these

privileges. To do this, you can run a revoke command. You can revoke any

combination of SELECT, INSERT, UPDATE, DELETE, ALL.

Syntax

REVOKE privileges ON object FROM user;

Privilege Description

SELECT Ability to perform SELECT statements on the table.

INSERT Ability to perform INSERT statements on the table.

UPDATE Ability to perform UPDATE statements on the table.

DELETE Ability to perform DELETE statements on the table.

ALL All privileges on table.

object

8 Prepared by [Link], Professor, Dept. of Information Science, CCI,

Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

The name of the database object that you are revoking privileges for. In the case of

revoking privileges on a table, this would be the table name.

user

The name of the user that will have these privileges revoked.

Example

For example, if you wanted to revoke DELETE privileges on a TABLE called

suppliers from a user named anderson, you would run the following REVOKE

statement:

REVOKE DELETE ON SUPPLIERS FROM anderson;

If you wanted to revoke ALL privileges on a table for a user named anderson, you

could use the ALL keyword as follows:

REVOKE ALL ON suppliers FROM anderson;

NOTE:

CREATE USER <user-name> IDENTIFIED BY <password>;

GRANT CREATE SESSION TO <user-name>;

Example:

Create user SARA identified by SARAN;

Grant create session to SARA;

To display all users in DBA

SELECT * FROM ALL_USERS;

To display current user in DBA

Show user;
9 Prepared by [Link], Professor, Dept. of Information Science, CCI,
Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

Constraints and Integrity

Relational data model includes several types of constraints whose purpose is to

maintain accuracy and integrity of the data in the database. The major types of

integrity constraints are:

i) Domain constraints

ii) Entity integrity constraints

iii) Referential integrity constraints

Domain constraints
A domain is a set of values that may be assigned to attribute. All values that appear in a
column of a relation (table) must be taken from the same domain.
Entity integrity constraints
The entity integrity constraint states that no primary key value can be null. This is because
the primary key value is used to identify individual tuples in a relation; having null values for
the primary key implies that we cannot identify some tuples.
Referential integrity constraints
A referential integrity constraint is a rule that maintaining consistency among the rows of two
table (relations). The rule states that if there is a foreign key in one relation, either each
foreign key value must match a primary key value in the other table or else the foreign key
value must be null.

10 Prepared by [Link], Professor, Dept. of Information Science, CCI,

Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

SQL Built-in Functions

SQL has Built-in functions like avg(), sum(), count(), etc., to perform what is known as
aggregate data calculations against a table or a specific table column.
 Date Function
 Numeric Function
 Character Function
 Conversion Function
 Miscellaneous Function

Date Function
They operate on date values and produce outputs, which also belong to date data type
except for months, between, date function returns a number.
1. Add_month
This function returns a date after adding a specified date with specified number of months.
Syntax: Add_months(d,n); where d-date n-number of months
Example: Select add_months(sysdate,2) from dual;
2. last_day
It displays the last date of that month.
Syntax: last_day (d); where d-date
Example: Select last_day (‘1-jun-2009’) from dual;
3. Months_between
It gives the difference in number of months between d1 & d2.
Syntax: month_between (d1,d2); where d1 & d2 -dates
4. next_day
It returns a day followed the specified date.
Syntax: next_day (d,day);
Example: Select next_day (sysdate,’wednesday’) from dual

11 Prepared by [Link], Professor, Dept. of Information Science, CCI,

Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

Numerical Functions

Character Functions

12 Prepared by [Link], Professor, Dept. of Information Science, CCI,

Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

Group Functions
A group function returns a result based on group of rows.
1. avg
Example: select avg (total) from student;
[Link]
Example: select max (percentagel) from student;
[Link]
Example: select min (marksl) from student;
4. sum
Example: select sum(price) from product;

13 Prepared by [Link], Professor, Dept. of Information Science, CCI,

Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

Joins and Sub Queries

Sub Queries

The query within another is known as a sub query. A statement containing sub query is called
parent statement. The rows returned by sub query are used by the parent statement.
The following operators can be used for Sub Queries
 in
 not in
 all

EXAMPLE FOR SUB QUERIES

SQL> Select * from emp1

EID ENAME ESALARY

--------- ---------- ---------
4 guna 60000
8 bala 70000
7 mohan 80000
-----------------------------------------------
SQL> Select * from emp2;

EID AID EADD

--------- --------- -------
4 5123 Harar
8 1253 Jegol
-------------------------------------------------

SQL> Select * from emp1 where eid in(select eid from emp2);

EID ENAME ESALARY

--------- --------- ---------
4 guna 60000
8 bala 70000
---------------------------------------------------

14 Prepared by [Link], Professor, Dept. of Information Science, CCI,

Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

SQL> Select * from emp1 where eid not in(select eid from emp2 where aid>60);

EID ENAME ESALARY

--------- --------- ---------
7 mohan 80000
---------------------------------------------

SQL> Select * from emp1 where eid>=all(select eid from emp2);

EID ENAME ESALARY

--------- ---------- ---------
8 bala 70000
-----------------------------------------------

SQL> Select * from emp1 where eid<=all(select eid from emp2);

EID ENAME ESALARY

--------- ---------- ---------
4 guna 60000
-----------------------------------------------

JOINS

Join operations take two relations and return another relation.

Joins Types
 INNER JOIN
 OUTER JOIN
INNER JOIN
The Inner Join is a join operation that joins two tables by their common column. This
operation is similar to the setting relation of two tables
SELECT [Link], a.col1, b.col2, expr1, expr2 FROM table1 a, table2 b WHERE [Link]
= [Link]

Example1: Make a list of students and the instruments they learn.

15 Prepared by [Link], Professor, Dept. of Information Science, CCI,

Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

SELECT [Link], [Link], [Link], [Link] FROM student s, music m WHERE [Link]=[Link] ORDER
BY class, name
Example2:
SQL> Select [Link],[Link],[Link], [Link] from emp1 a, emp2 b where [Link] =[Link];

EID ENAME ESALARY EADD

--------- ---------- --------- ---------
4 guna 60000 Harar
8 bala 70000 Jegol
---------------------------------------------------------------
OUTER JOIN
An Outer Join is a join operation that includes rows that have a match, plus rows that do not
have a match in the other table. The symbol (+) represents outer join.
SELECT col1, col2, col3 FROM table1, table2 WHERE table1.col1(+) != table2.col1;
Example1:

SELECT ename, job, dname FROM emp, dept where [Link] (+) != [Link];

Example2:

SQL> Select [Link],[Link],[Link] from emp1 a, emp2 b where [Link](+) !=[Link];

EID ENAME ESALARY

--------- ---------- ---------
4 guna 60000
8 bala 70000
7 mohan 80000
-----------------------------------------------
16 Prepared by [Link], Professor, Dept. of Information Science, CCI,
Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

Oracle Objects
VIEWS
Views are used to view the selected columns as per the user need.
Views are four types, they are…
CREATE
UPDATE
ALTER
SELECT

NOTE:
To ACCESS a VIEW ----need to give permission in user login……

GRANT CREATE VIEW TO <USERNAME>

Example:

GRANT CREATE VIEW TO SARA;

CREATE VIEW:

This command is used to create database view in Oracle.

SYNTAX:

CREATE VIEW <view_name> AS (SELECT <col1, col2, col2… coln> FROM <table-name>);

UPDATE:

UPDATE command is used to change a value of a record in the database.

SYNTAX:

UPDATE view_name SET column name =new value WHERE column name=some value;

ALTER:

ALTER view <viewname> AS SELECT <column-name> FROM <table-name>;

17 Prepared by [Link], Professor, Dept. of Information Science, CCI,

Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

SELECT:

SYNTAX:

Select *from viewname;

SQL> desc emp1;

Name Null? Type

-------------------- -------- ----
EMPNO NOT NULL NUMBER (10)
EMPNAME VARCHAR2 (15)
EMPID VARCHAR2 (10)
SAL NUMBER (38)
JOB VARCHAR2 (10)
DOB DATE
ADDRESS VARCHAR2 (20)
CITY VARCHAR2 (10)

CREATING A VIEW

SQL> create view emp2 as (select empname, empno from emp1);

View created.

SQL> desc emp2;

Name Null? Type
------------ ---------------- ----
EMPNO NOT NULL NUMBER (10)
EMPNAME VARCHAR2 (15)
EMPID VARCHAR2 (10)
SAL NUMBER (38)
JOB VARCHAR2 (10)
DOB DATE
ADDRESS VARCHAR2 (20)
CITY VARCHAR2 (10)

18 Prepared by [Link], Professor, Dept. of Information Science, CCI,

Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

SQL> select * from emp2;

EMPNAME EMPNO
--------------- ---------
Tamil 1
Selvi 2
Siva 3
Raja 4
Josh 5

ALTERING A VIEW

SQL> alter view emp2 as select empname, empno from emp1;

View altered.

UPDATING A VIEW

SQL> update emp2 set empname='kamala’ where empno=2;

1 row updated.

DISPLAYING A VIEW

SQL> select *from emp2;

EMPNAME EMPNO
--------------- ---------
Tamil 1
Kamala 2
Siva 3
Raja 4
Josh 5

19 Prepared by [Link], Professor, Dept. of Information Science, CCI,

Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

SEQUENCES

A sequence is a database objet that generates numbers in sequential order.

Applications most often use these numbers when they require a unique value in a

table such as primary key values.

CREATE SEQUENCE <sequence_name> [START WITH star_num]

[INCREMENT BY increment_num] [{MAXVALUE maximum_num]

Example:

SQL > CREATE SEQUENCE ANU START WITH 1 INCREMENT BY 1 MAXVALUE

100;

Sequence Created.

On execution, Oracle will create a sequence 'ANU'. Its start with value is 1, incrementing the
sequence number by 1. Maximum value that it can generate is 100.

Example:

Create sequence anu start with 1 increment by 1 maxvalue 100;

Create table Student (Sno number3), Sname varchar2(20));

Insert into Student Values (&Sno, ‘&Sname’);

Enter value for sno: [Link]

Enter value for sname: suresh

1 row created.

20 Prepared by [Link], Professor, Dept. of Information Science, CCI,

Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

INDEXES
An index is a performance-tuning method of allowing faster retrieval of records. An index
creates an entry for each value that appears in the indexed columns.

Create an Index
Syntax
The syntax for creating an index in Oracle/PLSQL is:

CREATE [UNIQUE] INDEX index_name

ON table_name (column1, column2, ... column_n)
[ COMPUTE STATISTICS ];

UNIQUE
It indicates that the combination of values in the indexed columns must be unique.
index_name
The name to assign to the index.
table_name
The name of the table in which to create the index.
column1, column2, ... column_n
The columns to use in the index.
COMPUTE STATISTICS
It tells Oracle to collect statistics during the creation of the index. The statistics are
then used by the optimizer to choose a "plan of execution" when SQL statements are
executed.
Example
Let's look at an example of how to create an index in Oracle/PLSQL.

CREATE TABLE Example_tab (Column_a INTEGER, Column_b INTEGER);

Table created.

21 Prepared by [Link], Professor, Dept. of Information Science, CCI,

Haramaya University.
 HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE
INSC 1064 - ADVANCED DATABASE

SELECT * FROM Example_tab ORDER BY Column_a;

COLUMN_A COLUMN_B
------------------- ----------------
1 2
2 4
3 6
4 8
5 10

5 rows selected.

CREATE INDEX Idx ON Example_tab(Column_a + Column_b);

Index created.

SELECT * FROM Example_tab WHERE Column_a + Column_b < 10 ORDER BY

Column_a;

COLUMN_A COLUMN_B
------------------- ----------------
1 2
2 4
3 6

3 rows selected.

22 Prepared by [Link], Professor, Dept. of Information Science, CCI,

Haramaya University.