Introduction to SQL

Structured Query Language

 Introduction To Oracle
• Oracle product is based on the Client-Server technology
• It involves the concept of segregating the processing on
an application between two systems. One performs all
activities related to the database(server) and other
performs activities that help the user to interact with the
application(client).
• The client or front end database application also interact
with the database by requesting and receiving information
by database server. It acts as an interface between the user
and the database. Further it also checks for validation
against the data entered by the user.
• The database server or back end is used to manage the
database tables optimally among multiple clients to
concurrently request the server for the same data.
Introduction To Oracle
 Position of Oracle

Though other RDBMS products are also present in the market

like MS-Access, SQLServer,Sybase, DB2 etc, Oracle gained the
maximum popularity because it overcame some or most of the
drawbacks of the other RDBMS.

MS-Access can store a very limited amount of data. As a result

it failed to establish itself in large organizations where the
amount of data is enormous. Oracle provides the facility of
huge data storage capacity.

Sybase became popular due to its huge data storage capacity.

But the maintenance of Sybase server was very critical from
administration point of view. Oracle is very easy to manage in
this respect.
 Position of Oracle

SQL-Server is also popular in the market but it does not

support the concept of Object-Orientation.
Oracle8 onwards supports Object-Orientation.

DB2 is very costly and as a result smaller organizations can not

afford to have this.
Oracle is comparatively cheaper and as a result more
organizations are using Oracle.
 TOOLS OF ORACLE
• SQL * Plus :SQL * Plus is a structured query language
supported by Oracle. Through SQL * Plus we can store,
retrieve, enter and run SQL commands. Using SQL * Plus
we can perform calculations, list the definition for any
table.
• PL/SQL : PL/SQL is the extension of SQL. PL/SQL block
contain any number of SQL statements integrated with the
flow of controlled statements. Thus, PL/SQL combines the
Data Manipulation power of SQL with data processing
power of procedural language.
• Forms : This is a graphical tool used for generating and
executing forms based application. Oracle form builder is
the designed component of Oracle Forms. We can build,
generate an Oracle Form application from the builder.
• Reports : It is an application development tool of Oracle for
developing, executing, displaying and printing reports.
 Introduction To SQL
• SQL stands for “ Structured Query Language”. SQL was
invented and developed by IBM
• in 1970’s. Oracle database language is SQL, which is used
for storing and retrieving information in Oracle
• A Table is primary database object of SQL that is used to
store data
• A Table holds data in the form of rows and columns.
 Introduction To SQL

SQL can be used effectively to perform the following database

operations:
Ø SQL provides command to retrieve data from a RDBMS
database.
Ø We can use the SQL commands to Insert, Update or Delete
rows from a RDBMS table.
Ø To work with a RDBMS database, the database user needs to
use some database objects like tables, views etc. Using
commands from the SQL, one can easily create,alter or remove
these database objects.
Ø While working with database we may need to modify or
upgrade the database settings. SQL includes commands to
facilitate these tasks.
 Introduction To SQL

• In a multi-user environment data security is an important

issue. SQL provides commands to maintain different
security settings of the database.
• SQL can be used to add or remove the user from the
database and also to allow or disallow the users from using
different database objects.

• The overall database consistency and integrity of its data

can also be maintained by using SQL.
 Types of SQL Statements
• SQL statements can be grouped into three general
categories :
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
 Types of SQL Statements
• Data Definition Language (DDL) : It is used for structuring the
database
• It includes the DDL statements like CREATE, ALTER, and
DROP to create new objects, alter the structure of existing
objects, or completely remove the object from the system.
 Types of SQL Statements
• Data Manipulation Language (DML): It is used for
manipulating the database and uses a data in the database. It
includes the following commands.
 Types of SQL Statements
• Data Control Language (DCL): It is used for securing the
database. It includes the following commands.
▪ Structured Query Language is a Fourth Generation
Language
• To execute the SQL statements optimally, Oracle 9i has an
internal system feature called the optimizer
• When a SQL statement is issued, the optimizer determines
one or more execution plans that it can use to execute the
statement
• After comparing the costs of execution for each plan,
Oracle uses the plan with the lowest relative cost, which
executes the fastest
CREATE TABLE Command
• The syntax for creating a table is:
create table table name
(column1 datatype (size),
column2 datatype (size));

create table branch_mstr

(branch_no varchar2(10),
name varchar2(25));
Insert command
• When inserting a single row of data into the table, the insert operation:
– Creates a new row in the database table
– Loads the values passed into the columns specified
• Syntax
– INSERT INTO table [(column [, column..])], VALUES (value [, value..]);
– INSERT INTO dept (d_id, d_name) VALUES (70, ‘marketing’);
– INSERT INTO dept VALUES (70, ‘marketing’);
• If column list is not specified, values must be listed according to the
default order of columns in the table & a value must be provided for
each column
 Inserting Multiple Rows using a
Single Insert
INSERT ALL
INTO mytable (column1, column2, column_n) VALUES (expr1, expr2,
expr_n)
INTO mytable (column1, column2, column_n) VALUES (expr1, expr2,
expr_n)
INTO mytable (column1, column2, column_n) VALUES (expr1, expr2,
expr_n)
SELECT * FROM dual;
 Select Command

▪ The SELECT command is used to retrieve rows selected from one

or more tables

All rows and all columns

SELECT * FROM table;

Selected columns and all rows

SELECT column [, column……]
FROM table;
 Select Command

Selected rows and all columns

WHERE Clause is used to filter a table to retrieve specific set of
rows
The syntax is :
SELECT * FROM table WHERE condition;
NOTE:
▪ <condition> is always quantified as <columnname = value>
▪ While specifying a condition in the WHERE Clause all standard
operators such as logical, arithmetic etc. can be used
 Select Command

Selected rows and selected columns

SELECT column [, column……]
FROM table
WHERE condition;
Ex.
SELECT last_name, job_id
FROM employees
WHERE last_name = ‘Mathur’;
▪ Can use all comparison operators like =, >, >=, <, <=, <>
▪ Character values are case sensitive and date values are format sensitive
 SQL: UPDATE Statement

To update the content of the table:

UPDATE statement syntax:

UPDATE <table name> SET <attr> = <value>

WHERE <selection condition>;

Example: UPDATE FoodCart SET sold = 349

WHERE date = ’02/25/08’ AND food = ‘pizza’;
FoodCart
date food sold date food sold
02/25/08 pizza 350 02/25/08 pizza 349
02/26/08 hotdog 500 02/26/08 hotdog 500
02/26/08 pizza 70 02/26/08 pizza 70
 SQL: DELETE Statement

To delete rows from the table:

DELETE statement syntax:
DELETE FROM <table name>
WHERE <condition>;

Example: DELETE FROM FoodCart

WHERE food = ‘hotdog’;
FoodCart

date food sold date food sold

02/25/08 pizza 349 02/25/08 pizza 349
02/26/08 hotdog 500 02/26/08 pizza 70
02/26/08 pizza 70
Note: If the WHERE clause is omitted all rows of data are deleted from the table.
Select Operation
•The select operation selects tuples that satisfy a given
predicate.
•It is denoted by sigma (σ).
• Notation: σ p(r)
Where:
σ is used for selection prediction r is used for relation
p is used as a propositional logic formula which may use
connectors like: AND OR and NOT. These relational can use as
relational operators like =, ≠, ≥, <, >, ≤.
For example: LOAN Relation
Input: σ BRANCH_NAME="perryride" (LOAN)
2. Project Operation:
•This operation shows the list of those attributes that we wish to appear in the
result. Rest of the attributes are eliminated from the table.
•It is denoted by ∏.
• Notation: ∏ A1, A2, An (r)
Where
A1, A2, A3 is used as an attribute name of relation r.
Example: CUSTOMER RELATION

Input: ∏ NAME, CITY (CUSTOMER)

3. Union Operation:
• Suppose there are two tuples R and S. The union operation contains all
the tuples that are either in R or S or both in R & S.
•It eliminates the duplicate tuples. It is denoted by ∪.
Notation: R ∪ S
A union operation must hold the following condition:
R and S must have the attribute of the same number.
Duplicate tuples are eliminated automatically.
Example:
DEPOSITOR RELATION
Input:
1.∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSITOR)

4. Set Intersection:
•Suppose there are two tuples R and S. The set intersection
operation contains all tuples that are in both R & S.
•It is denoted by intersection ∩.
1.Notation: R ∩ S
Input:
∏ CUSTOMER_NAME (BORROW) ∩ ∏ CUSTOMER_NAME (DEPOSITOR)

5. Set Difference:
•Suppose there are two tuples R and S. The set intersection
operation contains all tuples that are in R but not in S.
•It is denoted by intersection minus (-).
Notation: R - S
Input:
∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR)
6. Cartesian product
•The Cartesian product is used to combine each row in one table with each row in
the other table. It is also known as a cross product.
•It is denoted by X.
• Notation: E X D
Input:
EMPLOYEE X DEPARTMENT
 Database Objects

Object Description
Table Basic unit of storage; composed of rows
and columns

View Logically represents subsets of data from

one or more tables

Sequence Numeric value generator

Index Improves the performance of some queries
Synonym Gives alternative names to objects
 The CREATE TABLE Statement

• You must have:

– CREATE TABLE privilege
– A storage area
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);

• You specify:
– Table name
– Column name, column data type, and column size
 Creating Tables

• Create the table.

CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13));
Table created.
• Confirm table creation.
DESCRIBE dept
 Data Types
Data Type Description
VARCHAR2(size) Variable-length character data(max 4000)
VARCHAR (size) Variable-length character data(max 255)
CHAR(size) Fixed-length character data (max 255,right padded)
NUMBER(p,s) Variable-length numeric data (38 digits of precision ,‘p’
is max length and ‘s’ is nos. after decimal (default s is 0)

DATE Date (ddmmmyy, default is 1st day of current month and

time values (24 hours, by default is 12:00:00 am)
LONG Variable-length character data up to 2 GB
, only one LONG value per table allowed
CLOB Character data up to 4 GB
RAW and LONG RAW RAW (255 bytes) , LONG RAW (2GB) binary data (images)
BLOB Binary data up to 4 GB
BFILE Binary data stored in an external file; up to 4 gigabytes

“Programming In SQL” by Ivan Byross Page no. 115

• The CREATE TABLE statement for the customers table
is:

CREATE TABLE customers

(customer_id number(10),
customer_name varchar2(50)
Address varchar2(50),
City varchar2(50),
State varchar2(25),
zip_code varchar2(10),
);
 SELECT statement
• SELECT [ DISTINCT COLUMN NAME(S)/ * ] FROM TABLE;

Ø SELECT - This clause specifies the list of columns that is to be

retrieved from the table by the query.
Ø DISTINCT – This is a keyword that eliminates duplicate values
returned by the query.
Ø COLUMN NAME – The names of the columns that are to be
returned by the SELECT statement.
Ø * - This symbol is used to display all the columns from the
table that is being queried.
Ø FROM – this clause is used to specify the name of the table(s)
from where the columns are to be retrieved.
Ø TABLE – The name of the table(s) from where the columns are
to be retrieved
 Arithmetic Operators and Expressions
• When an arithmetic expression contains different kinds of
operators then it is evaluated by following a particular sequence :
• Multiplication
• Division
• Subtraction
• Addition

• SELECT EMPNO, ENAME, SAL*1.15 FROM EMP;

• SELECT ENAME, JOB, HIREDATE, 12*sal+200 FROM EMP;
• SELECT ENAME, JOB, HIREDATE, 12*(SAL+200) FROM EMP;
 NULL Values
• In any Relational or Object Relational Database
Management System a null is considered as something
which is unknown or unavailable or unassigned
• We sometimes confuse null with zero or space. But this is
not right
• A null cannot be equated with zero or space. This is
because null is neither a number (which zero is) nor it is a
character (such as space)
• Sometimes while inserting rows in a table, the values of
certain attributes in that row may be unknown. In such
cases, we assign null to those attributes
 NULL Values
• If we include a column that contains null values in an
arithmetic expression then the result will also be a null
• To solve this we should use the NVL function
• The NVL function is used to convert NULL to an actual
value
• Thus the query that we have prepared in the previous page
can be rewritten as follows:

SELECT EMPNO, ENAME, SAL, COMM, SAL+NVL (COMM, 0)

FORM EMP;
 Using Column Aliases

• SELECT ENAME NAME, JOB ROLE FROM EMP;

• SELECT ENAME “Employee”, JOB “Job Role” FROM EMP;

• SELECT ENAME “Name”, JOB “Job Role” ,SAL*12 “Annual

Salary” FROM EMP;
 Concatenation Operator

• Concatenation operators are used to join columns to other

columns, arithmetic expressions, and constant values. The
resultant expression is always a character string.

• The symbol for concatenation is “||”. In the example below,

we will join the columns ename, job and sal from the emp
table
SELECT ENAME||JOB||SAL “EMPLOYEE DETAILS”” FROM
EMP;

SELECT ENAME||’ ‘||’is working as’||’ ‘||JOB||’ ‘||’and earning a

salary of’|| ‘ ‘||SAL“Employee Details” FROM EMP;
 The ALTER TABLE Statement

Use the ALTER TABLE statement to:

• Add a new column
• Modify an existing column
• Define a default value for the new column
• Drop a column
 The ALTER TABLE Statement

Use the ALTER TABLE statement to add, modify, or

drop columns.
ALTER TABLE table
ADD (column datatype [DEFAULT expr]
[, column datatype]...);

ALTER TABLE table

MODIFY (column datatype [DEFAULT expr]
[, column datatype]...);

ALTER TABLE table

DROP (column);
 Adding a Column
New column
DEPT80

“Add a new
column to
the DEPT80
table.”

DEPT80
 Adding a Column

• You use the ADD clause to add columns.

ALTER TABLE dept80
ADD (job_id VARCHAR2(9));
Table altered.

• The new column becomes the last column.

 Modifying a Column

• You can change a column’s data type, size, and

default value.
ALTER TABLE dept80
MODIFY (last_name VARCHAR2(30));
Table altered.

• A change to the default value affects only

subsequent insertions to the table.
 Dropping a Column

Use the DROP COLUMN clause to drop columns you no

longer need from the table.

ALTER TABLE dept80

DROP COLUMN job_id;
Table altered.
 Dropping a Table

• All data in the table is deleted.

• Table structure is removed from the database
• Any pending transactions are committed.
• All indexes are dropped.
• You cannot roll back the DROP TABLE statement.

DROP TABLE dept80;

Table dropped.
 Changing the Name of an Object

• To change the name of a table, view, sequence, or

synonym, you execute the RENAME statement.
RENAME dept TO detail_dept;
Table renamed.

• You must be the owner of the object.

 Truncating a Table

• The TRUNCATE TABLE statement:

– Removes all rows from a table
– Releases the storage space used by that table
TRUNCATE TABLE detail_dept;
Table truncated.

• get rid of the data but not the table

• You cannot roll back row removal when using
TRUNCATE.
• DROP TABLE "table_name“ get rid of a table in the
database
 Difference between DELETE and
TRUNCATE Statements:

• DELETE Statement:
– This command deletes only the rows from the table
based on the condition given in the where clause or
deletes all the rows from the table if no condition is
specified.
– does not free the space containing the table.
• TRUNCATE statement:
– This command is used to delete all the rows from the
table
– Free the space containing the table.
 INSERT ALL Command
• SQL> insert all
into colors(name, category) values('yellow', 1)
into colors(name, category) values('red', 1)
into colors(name, category) values('blue', 1)
into colors(name, category) values('yellow', 2)
into colors(name, category) values('blue', 2)
select * from dual;
 INSERT ALL Command
INSERT ALL INTO Products ( ProductId, ProductName, Price )
VALUES ( 1, 'Left Handed Screwdriver', 10.50 ) INTO Products (
ProductId, ProductName, Price )
VALUES ( 2, 'Right Handed Screwdriver', 22.75 ) INTO Products (
ProductId, ProductName, Price )
VALUES ( 3, 'Bottomless Coffee Cup (4 pack)', 15.00 ) INTO Products (
ProductId, ProductName, Price )
VALUES ( 4, 'Urban Dictionary Version 2.3', 75 ) INTO Products (
ProductId, ProductName, Price )
VALUES ( 5, 'Beer Water', 15 )
SELECT 1 FROM dual;
 SQL: UPDATE Statement

To update the content of the table:

UPDATE statement syntax:

UPDATE <table name> SET <attr> = <value>

WHERE <selection condition>;

Example: UPDATE FoodCart SET sold = 349

WHERE date = ’02/25/08’ AND food = ‘pizza’;
FoodCart
date food sold date food sold
02/25/08 pizza 350 02/25/08 pizza 349
02/26/08 hotdog 500 02/26/08 hotdog 500
02/26/08 pizza 70 02/26/08 pizza 70
 SQL: DELETE Statement

To delete rows from the table:

DELETE statement syntax:
DELETE FROM <table name>
WHERE <condition>;

Example: DELETE FROM FoodCart

WHERE food = ‘hotdog’;
FoodCart

date food sold date food sold

02/25/08 pizza 349 02/25/08 pizza 349
02/26/08 hotdog 500 02/26/08 pizza 70
02/26/08 pizza 70
Note: If the WHERE clause is omitted all rows of data are deleted from the table.
 Comparison Operators
• Comparison (also known as Relational Operators) Operators can be used in
the WHERE clause to compare one expression with the other
• It provides the WHERE clause with a greater degree of flexibility
• The following are the various relational operators:
 The where Clause
• The where clause specifies conditions that the result must satisfy
– Corresponds to the selection predicate.
• To find all instructors in Comp. Sci. dept

select name
from instructor
where dept_name = ‘Comp. Sci’
 Logical operators
• AND
• OR
• NOT
• To find all instructors in Comp. Sci. dept with salary >
80000 select name
from instructor
where dept_name = ‘Comp. Sci.' and salary > 80000
• Comparison results can be combined using the logical connectives and, or,
and not.
• SELECT * FROM Customers
WHERE City='Berlin’ OR City='Mumbai';
• SELECT * FROM Customers
WHERE City='Berlin’ OR (NOT City='Mumbai‘);
• SELECT * FROM Customers
WHERE Country='Germany’ AND (City='Berlin' OR City=‘Mumbai');

• Comparisons can be applied to results of arithmetic expressions.

Quiz : Which of these clauses is optional in an

 Precedence of Logical Operator
• In Oracle, whenever in a SQL statement logical operators are
combined they are executed in a particular order. The rules of
precedence with regard to logical operators are as follows:
• NOT
• AND
• OR

If parentheses are not used in the above illustration, then the condition following the
AND operator would have been evaluated before the OR operator resulting in a
misleading output.
 Between Operator
• For example, if you wanted to list all employees who earn in the
range of 1500 to 3000:
• SELECT ENAME,JOB,SAL FROM EMP WHERE SAL>1500 AND
SAL<3000;

• SELECT ENAME,JOB,SAL FROM EMP WHERE SAL >=1500 AND

SAL<=3000;

• SELECT ENAME,JOB,SAL FROM EMP WHERE SAL BETWEEN 1500

AND 3000;
•
String Operations
SQL includes a string-matching operator for comparisons on character
strings. The operator “like” uses patterns that are described using two
special characters:
– percent (%). The % character matches any substring.
– underscore (_). The _ character matches any character.
• Find the names of all instructors whose name includes the substring “dar”.
select name
from instructor
where name like '%dar%'
• Match the string “100 %”
like ‘100 \%' escape '\'
• SQL supports a variety of string operations such as
– concatenation (using “||”) eg. select fname || “ “ || lname “customers”
• SQL Wildcards
String Operations
• SQL wildcards can substitute for one or more characters when searching
for data in a database. SQL wildcards must be used with the SQL LIKE
operator. With SQL, the following wildcards can be used:
• Wildcard Description
• % A substitute for zero or more characters
• _ A substitute for exactly one character
• [charlist] Any single character in charlist
• [^charlist] or [!charlist] Any single character not in charlist
• SELECT * FROM Persons WHERE LastName LIKE '[bsp]%'
• SELECT * FROM Persons WHERE LastName LIKE '[!bsp]%'
• SELECT * FROM Persons WHERE LastName LIKE 'S_end_on'
• SELECT * FROM Persons WHERE FirstName LIKE '_la'
• SELECT * FROM Persons WHERE City LIKE '%nes%'
• IN
Range
– SELECT * FROM Customers
WHERE City IN ('Paris','London');
• NOT IN
– SELECT * FROM Customers
WHERE City NOT IN ('Paris','London');
• BETWEEN
– SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
• SELECT EMPNO,ENAME,JOB,DEPTNO FROM EMP WHERE JOB
NOT IN (‘PRESIDENT’,’MANAGER’,’ANALYST’);
 round function
• round( number, [ decimal_places ] )
function Output
round(125.315) would return 125
round(125.315, 0) would return 125
round(125.315, 1) would return 125.3
• Select Name,ROUND( ((sub1+sub2)*100)/200) ,2)
“Percentage”
• SELECT ProductName, ROUND(Price,0) AS “RoundedPrice”
FROM Products;
 Arranging Output
• The output of a SELECT statement can be displayed in sorted order
with the use of ORDER BY clause. The output can be ordered on
multiple columns as well. The default order is ascending but you can
use DESC clause to sort in descending order
SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL;
SELECT EMPNO, ENAME, SAL FROM EMP ORDER BY 3;
SELECT EMPNO, ENAME, SAL FROM EMP ORDER BY ENAME DESC;
SELECT * FROM EMP ORDER BY DEPTNO, JOB;
SELECT ENAME,JOB,SAL*12 ANNSAL FROM EMP WHERE DEPTNO=10
ORDER BY ANNSAL;
 Aggregate Functions
• Aggregate functions are used to produce summarized information after analyzing
the table data. They operate on a set of rows and can return results based on the
groups of rows. By default all rows fetched by the query is taken as one group.
We use aggregate functions like SUM, AVG, MAX, MIN, and COUNT
• SUM () - Returns the total of a particular column provided that the datatype of
that column is numeric.
• AVG () - Returns the average of a given numeric column.
• MAX () - Returns the largest value in a given column. In case of char and varchar
datatype, it compares the ASCII values and returns the largest value.
• MIN () – Returns the lowest value in a given column. In case of char and varchar
datatype, it compares the ASCII values and returns the minimum value.
 Aggregate Functions
• COUNT (*) - Returns the number of rows satisfying a particular condition
including duplicate rows and rows containing NULL values.
• COUNT (expr) - Returns the number of rows not containing null values in the
column identified by expr.
• All the aggregate functions barring COUNT(*) ignores NULL values.

SELECT COUNT (*) FROM EMP WHERE DEPTNO=50;

SELECT SUM (SAL) FROM EMP WHERE DEPTNO=50;
SELECT SUM(SAL),AVG(SAL) FROM EMP;
SELECT MIN (SAL) FROM EMP WHERE JOB=’MANAGER’;
SELECT COUNT (COMM) FROM EMP WHERE JOB =’MANAGER’;
 Aggregate Functions (Cont.)
• Find the average salary of instructors in the Computer Science
department
– select avg (salary)
from instructor
where dept_name= ’Comp. Sci.’;
• Find the total number of instructors who teach a course in the Spring
2010 semester
– select count (distinct ID)
from teaches
where semester = ’Spring’ and year = 2010
• Find the number of tuples in the course relation
– select count (*)
from course;
 Aggregate Functions (Cont.)
• SELECT MIN(salary) as "Lowest salary" FROM
employees;
• SELECT MAX(salary) as "Highest salary" FROM
employees;
• SELECT ABS(-10) FROM DUAL;
– 10
• SELECT SQRT(36) FROM dual;
–6
 DUAL
• The DUAL table is a special one-column table present by default
in all Oracle database installations.
• It is suitable for use in selecting a pseudo column such as
SYSDATE or USER.
• The table has a single VARCHAR2(1) column called DUMMY that
has a value of 'X'.
• SELECT * from DUAL
• DUAL is a dummy table to facilitate arithmetic calculations
where no table is referenced.
• Select 2*2 from DUAL
– Output : 4
• Select SYSDATE from DUAL
– Output : system date
• SELECT UID FROM DUAL;
– UID returns an integer that uniquely identifies the
session user
• SELECT USER FROM DUAL;
– USER returns the name of the session user
• least function returns the smallest value in a list of expressions.
• Select least(2, 5, 12, 3) from DUAL
– 2
• Select greatest(2, 5, 12, 3) from DUAL
– 12
• SELECT MOD(11,4) "Modulus" FROM DUAL;
– Modulus
3
• SELECT FLOOR(15.7) "Floor" FROM DUAL;
– Floor ---------- 15
• SELECT order_total, CEIL(order_total) FROM orders
WHERE order_id = 2434;
– ORDER_TOTAL CEIL(ORDER_TOTAL)
– 268651.8 268652
• CEIL (VALUE): Returns the smallest integer larger than or equal to value.
CEIL (3) = 3 CEIL (2.4) = 3
• FLOOR (VALUE) : Returns the largest integer smaller than or equal to value .
FLOOR (3) = 3 FLOOR (2.4) = 2
• COS(VALUE) : Returns the COSine of value.
COS (1) = . 54030231
• COSH (VALUE) : Returns the Hyperbolic COSine of value.
COSH (1) = 1.5430806
• EXP (VALUE): Returns e raised to value EXPonent
EXP (2) = 7.3890561
• ABS (): This function returns the absolute value of the number specified.
ABS (123) = 123 ABS (-123) = 123
 Arithmetic Functions
• LN (VALUE) : Returns the natural logarithm of value .
LN (1) = 0 LN (2) = . 69314718
• LOG (BASE, VALUE) : Returns base 10 LOGarithm of value .
LOG (10,5) = . 69897
• MOD (VALUE, DIVISOR): Returns the remainder
MOD (14,3) = 2 MOD (21,7) = 0
• POWER (VALUE, EXPONENT): Returns the value raised to an exponent.
POWER (2,3) = 8 POWER (2, -3) = . 125
 String function
• SELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase"
FROM DUAL;
– Lowercase --------- mr. scott mcmillan
• SELECT INITCAP('the soap') "Capitals" FROM DUAL;
– Capitals --------- The Soap
• SELECT UPPER(last_name) "Uppercase" FROM
employees;
• SELECT SUBSTR('ABCDEFG',3,4) "Substring" FROM DUAL;
– Substring --------- CDEF
 Character Functions
• LPAD (VALUE, N, STRING): Pads the left-hand side of the value with
‘STRING’, the total width being N including the width of the VALUE.
LPAD (‘ORACLE’, 10,’#’) = ####ORACLE
SQL> SELECT LPAD (ENAME, 15,'*') FROM EMP
WHERE JOB='MANAGER';
LPAD (ENAME, 15,'
---------------
**********JONES
**********BLAKE
**********CLARK
 Character Functions
• RPAD (VALUE, N, STRING): Pads the right hand side of the value with
‘STRING’, the total width being N including the width of the VALUE.
• RPAD (‘ORACLE’, 10,’#’) = ORACLE####
• SQL> SELECT RPAD (ENAME, 15,'*') FROM EMP WHERE JOB='MANAGER';
RPAD (ENAME, 15,'
---------------
JONES**********
BLAKE**********
CLARK**********
• CONCAT (CHAR1, CHAR2): Concats two character values .
CONCAT (‘MS’,’OFFICE’) = MSOFFICE
 Character Functions
• INSTR (VALUE,STRING): Returns the character position of the first
occurrence of the STRING in the value
• SELECT INSTR ('COMPLETE','E') FROM DUAL;
• INSTR (VALUE, STRING [STARTPOS, OCCURENCE]): Returns the position of
the next OCCURRENCE of the STRING from the STARTing POSition .
INSTR('COMPLETE','E',1,2) = 8
• LTRIM (VALUE,CHARACTER(S)) : Removes the leading occurrences of the
CHARACTER(s) from the value .
LTRIM ('OORACLE','O') = RACLE
LTRIM ('BISWAJIT','BISW')= AJIT
 Character Functions
• RTRIM (VALUE, CHARACTER(S)) : Removes the trailing occurrences of
the CHARACTER(s) from the value .
RTRIM ('ORACLEE','E') = ORACL
RTRIM ('BISWAJIT','JIT') = BISWA
• LENGTH (STRING) : Returns the length of the string .
LENGTH (‘ROBERT’)= 6
SQL> SELECT ENAME, LENGTH (ENAME) FROM EMP WHERE JOB='CLERK';
• ASCII returns the decimal representation in the database
character set of the first character of char.
• SELECT last_name FROM employees WHERE
ASCII(SUBSTR(last_name, 1, 1,)) = 76;
LAST_NAME
-------------------------
Ladwig
Landry
Lee
Livingston
 Conversion Functions
• These functions are used to convert one data type into other.
TO_CHAR (VALUE)
• This function is used to convert DATE or NUMBER data type to character
string.
SELECT SAL, TO_CHAR (SAL) FROM EMP WHERE JOB=’PRESIDENT’;
TO_NUMBER (STRING)
• Convert a CHAR or VARCHAR2 datatype into NUMBER datatype.
SELECT TO_NUMBER (‘888’) FROM DUAL;
• TO_DATE (string, format)
• Convert a NUMBER, CHAR or VARCHR2 datatype into DATE datatype.
SELECT TO_DATE(‘24-AUG-99’,’DD/MM/YY’) FROM DUAL;
 TO_CHAR()
• The TO_CHAR function is typically used to format output data.
• Convert a numeric or date expression to a character String according
to the format mask.
• Select to_char(sysdate, 'FMMonth DD, YYYY') FROM dual;
– 'June 9, 2005'
• select to_char(sysdate,'HH24:MI:SS') "Time Now" from dual;
– Time Now '14:35:56‘
• Select to_char(1.234, '9999.9') FROM dual;
– '1.2'
• Select to_char(1000.25, '9,999.99') FROM dual;
– '1,000.25‘
• Select to_char(1000.25, '$9,999.00') FROM dual;
– '$1,000.25'
• select to_char(sysdate,’MON’) “Month " from dual;
– Month JUL
• Select to_char(25, '000099') FROM dual;
– '000025'
• Select to_char(sysdate, 'FMMonth DDTH, YYYY') FROM dual;

– July 24TH, 2013

• SELECT TO_CHAR (TO_DATE ('01-JAN-2000'),'DAY')
“DATE” FROM DUAL;
Date Format
The various date formatting characters are specified below and also in the subsequent
pages:
Ø DDD or DD or D: Day of the YEAR, MONTH or WEEK (e.g. 288th day of the year )
Ø DY: Three letter abbreviation of DAY (e.g. MON, TUE)
Ø DAY: DAY name padded with blanks (e.g. MONDAY, TUESDAY)
Ø YYYY: Four digit YEAR (1999)
Using all the above specified format character let’s display the different information from a date.
SELECT TO_CHAR (TO_DATE (‘07-JUL-2000’), ‘DDD|DD|D|DY|DAY|YYYY’) FROM DUAL;
• select to_char(sysdate,’MON’) “Month " from dual;
– Month JUL
• Select to_char(25, '000099') FROM dual;
– '000025'
• Select to_char(sysdate, 'FMMonth DDTH, YYYY') FROM dual;

– July 24TH, 2013

• FM Fill mode. Oracle uses trailing blank characters and leading zeroes to fill
format elements to a constant width. The width is equal to the display width of
the largest element for the relevant format model

• Numeric elements are padded with leading zeros to the width of the maximum
value allowed for the element. For example, the YYYY element is padded to
four digits (the length of '9999'), HH24 to two digits (the length of '23'), and
DDD to three digits (the length of '366')

• The character elements MONTH, MON, DAY, and DY are padded with trailing
blanks to the width of the longest full month name, the longest abbreviated
month name, the longest full date name, or the longest abbreviated day name,
respectively
 Format Model Examples

The following statement uses a date format model to return a character expression:
SELECT TO_CHAR(SYSDATE, 'fmDDTH') || ' of ' || TO_CHAR(SYSDATE,
'fmMonth') || ', ' || TO_CHAR(SYSDATE, 'YYYY') "Ides" FROM DUAL;
Ides
------------------
3RD of April, 2008
The preceding statement also uses the FM modifier. If FM is omitted, then the month is
blank-padded to nine characters:

SELECT TO_CHAR(SYSDATE, 'DDTH') || ' of ' || TO_CHAR(SYSDATE,

'Month') || ', ' || TO_CHAR(SYSDATE, 'YYYY') "Ides" FROM DUAL;
Ides
-----------------------
03RD of April , 2008
The following statement places a single quotation mark in the return value by using a
date format model that includes two consecutive single quotation marks:
SELECT TO_CHAR(SYSDATE, 'fmDay') || '''s Special' "Menu" FROM DUAL;
Menu
Tuesday's Special
 TO_DATE
• The oracle to_date function converts a string in
a specified format to an Oracle date format
– to_date('01-JAN-2006');
• (returns a date using the default oracle date format)
– insert into cust_master (Cust_no, DOB ) values ( ‘C1’,
to_date('01-01-04','DD-MM-YY'));
• Add_months()
– Select add_months(sysdate,4) from dual
– Select DOB, add_months(DOB,4) from cust_mstr

• Last_day()
– Select sysdate,last_day(sysdate) from dual

• Months_between(d1,d2)
– Select months_between (to_date ('01-12-13','dd-mm-
yy'),to_date ('01-07-13','dd-mm-yy') ) from dual
 TO_DATE
• SELECT TO_CHAR (TO_DATE ('01-JAN-2000'),'DAY') “DATE” FROM
DUAL;

• select to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') from dual;

• Select next_day ('24-JULY-13','saturday') from dual

27-jul-13
 Null Values and Aggregates
• Total all salaries
select sum (salary )
from instructor
– Above statement ignores null amounts
– Result is null if there is no non-null amount
• All aggregate operations except count(*) ignore tuples with null values
on the aggregated attributes
• What if collection has only null values?
– count returns 0
– all other aggregates return null
 Null Values
• It is possible for tuples to have a null value, denoted by null, for some
of their attributes
• null signifies an unknown value or that a value does not exist.
• The result of any arithmetic expression involving null is null
– Example: 5 + null returns null
• The predicate is null can be used to check for null values.
– Example: Find all instructors whose salary is
null.
select name
from instructor
where salary is null
• Any comparison with null returns unknown
– Returning false could cause problems:
• Consider r.A < 10 vs. not (r.A >= 10)
 Null Values and Three Valued Logic
• Any comparison with null returns unknown
– Example: 5 < null or null <> null or null = null
• Three-valued logic using the truth value unknown:
– OR: (unknown or true) = true,
(unknown or false) = unknown
(unknown or unknown) = unknown
– AND: (true and unknown) = unknown,
(false and unknown) = false,
(unknown and unknown) = unknown
– NOT: (not unknown) = unknown
– “P is unknown” evaluates to true if predicate P evaluates to
unknown
• Result of where clause predicate is treated as false if it evaluates to
unknown
 Ordering the Display of Tuples
• List in alphabetic order the names of all instructors
select distinct name
from instructor
order by name
• We may specify desc for descending order or asc for ascending order,
for each attribute; ascending order is the default.
– Example: order by name desc
• Can sort on multiple attributes
– Example: order by dept_name, name
• Select * from cust_mstr order by fname
• Select * from cust_mstr order by
to_char(DOB_INC,'MM')
 Grouping of Rows
• When a query is required to produce one summary row for all selected rows
that have identical values in one or more columns or expression specified, we
use GROUP BY clause
• The example below displays the total salary for each department.
SELECT DEPTNO, SUM (SAL) FROM EMP GROUP BY DEPTNO;

• Column names should always be specified with the GROUP BY clause

• We cannot use column aliases along with the GROUP BY clause.
 GROUP BY Statement
• The GROUP BY statement is used in
conjunction with the aggregate functions to
group the result-set by one or more columns.
• SELECT column_name,
aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
• The power of aggregate functions is greater when
combined with the GROUP BY clause.
• In fact, the GROUP BY clause is rarely used without
an aggregate function.
• It is possible to use the GROUP BY clause without
aggregates, but such a construction has very limited
functionality, and could lead to a result table that is
confusing or misleading.
• The following query displays how many
employees work for each department?
emp_dpt_n ----
umber
SELECT emp_dpt_number "Department",
COUNT(*) "Department Count" 1 ---
FROM employee 3 ---
GROUP BY emp_dpt_number; 7 ---
3 ---
Department Department Count 7
---------- ---------------- 3
1 1
7
3 3
7
7 4
 The column name used in a GROUP BY does not have to be listed in the
SELECT clause; however, it must be a column name from one of the tables
listed in the FROM clause.
• We could rewrite the last query without specifying the emp_dpt_number
column as part of the result table, but as you can see below, the results
are rather cryptic without the emp_dpt_number column to identify the
meaning of the aggregate count.
SELECT COUNT(*) "Department Count"
FROM employee
GROUP BY emp_dpt_number;
Department Count
----------------
1
3
4
 Example
However, the reverse is NOT true!

SELECT emp_dpt_number, COUNT(*) "Department

emp_dpt_ ----
number
Count"
1 ---
FROM employee; 3 ---
7 ---
3 ---
SELECT emp_dpt_number, COUNT(*) "Department7
Count" 3
* 7
7
ERROR at line 1:
ORA-00937: not a single-group group function
Bordoloi and Bock
 GROUP BY Clause
• To keep it simple, just remember the following:
1. If you have column name(s) AND Aggr.
Function(s) in the SELECT clause, then you
MUST also have a GROUP BY clause.

2. The column name(s) in the SELECT clause

MUST match column name(s) listed in the
GROUP BY clause.
 Example
SELECT emp_dpt_number "Department",
emp_gender "Gender",
COUNT(*) "Department Count"
FROM employee
GROUP BY emp_dpt_number;

ERROR at line 2:
ORA-00979: not a GROUP BY expression
 Example
SELECT emp_dpt_number "Department",
emp_gender "G",
COUNT(*) “Employee Count"
FROM employee
GROUP BY emp_dpt_number, emp_gender;
Department G Employee Count
---------- - --------------
1 M 1
3 F 2
3 M 1
7 F 1
7 M 3
 Usage of HAVING
• A HAVING clause with GROUP BY can be used to find which group to be included in
the output
• A WHERE clause on the other hand, determines which rows are to be included in
the groups
• HAVING clause act on groups whereas WHILE clause works on individual rows
• Suppose we want to display the department number and maximum salary in each
department if it is 3000 or more :
SELECT DEPTNO, MAX (SAL) FROM EMP GROUP BY DEPTNO HAVING MAX (SAL)
>=3000;

• Unlike WHERE clause HAVING clause is mainly used with the columns used with
aggregate functions
 The HAVING Clause
• The HAVING clause was added to SQL because
the WHERE keyword could not be used with
aggregate functions.
• It can be used in an SQL SELECT statement to
filter the records that a SQL GROUP BY returns
SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;
 The HAVING Clause
• SELECT department, COUNT(*) as "Number of employees“
FROM employees
WHERE salary > 25000
GROUP BY department
HAVING COUNT(*) > 10;
• SELECT department, MIN(salary) as "Lowest salary“
FROM employees
GROUP BY department
HAVING MIN(salary) = 35000;
• We want to display the total number of employee in departments 10,20 and 30
where total number of employee in a department is more than 4, sorted by
number of employees
SELECT DEPTNO, COUNT (*) FROM EMP WHERE DEPTNO IN (10,20,30)
GROUP BY DEPTNO HAVING COUNT (*) >4 ORDER BY 2;
 The HAVING Clause
• The above statement will work in the following way:
• Firstly the rows will be selected based on the column deptno which
is having values of 10 or 20 or 30
• It will group the selected rows based on group by
• It will count the total number of records for each group
• It will compare the total number of records for each group with a
value more than 4
• Then it will display the output after sorting the records based on
the second column mentioned in the select statement
 JOINS
• One of the most powerful features of SQL is its capability to
handle and manipulate data stored in different tables
• Without this feature we would have to store all the data
elements necessary for each application in one table. SQL
provides a way to solve this problem which could have
generated many difficulties related to data management and
storage space problem.
SELECT * FROM TABLE 1, TABLE 2;
 Reserves sid bid day
Example
22 101 10/10/96
Instances 95 103 11/12/96
Sailors sid sname rating age
22 Dustin 7 45.0
31 Lubber 8 55.5
95 Bob 3 63.5
Boats bid bname color
101 Interlake blue
102 Interlake red
103 Clipper green
104 Marine red
 Find the names of the sailors
who have been reserved..
• Equi join : Equi Join can be defined as a join in which more than
one tables are joined together with the help of a common
column that exists in both the tables
• In this type of join the relationship between the tables are
specified in the where clause, by using an equal (=) symbol

SELECT s.sname,s.sid,r.day
FROM sailors s, Reserves r
WHERE s.sid=r.sid
 Find the number of reservations
for each red boat.
SELECT B.bid, COUNT(*) AS numbres
FROM Boats B, Reserves R
WHERE R.bid=B.bid
AND B.color=‘red’
GROUP BY B.bid

• Grouping over a join of two relations.

 SELECT B.bid, COUNT (*) AS scount
FROM Boats B, Reserves R
WHERE R.bid=B.bid AND B.color=‘red’
GROUP BY B.bid
b.bid b.color r.bid b.bid b.color r.bid
101 blue 101
102 red 101
103 green 101
104 red 101
101 blue 102
102 red 102 102 red 102
103 green 102
104 red 102

1 2
b.bid scount
answer
102 1
 Joins
SELECT (column_list)
FROM table_name
[INNER | {LEFT |RIGHT | FULL } OUTER] JOIN table_name
ON qualification_list
WHERE …

Explicit join semantics needed unless it is an INNER

join
(INNER is default)
 Natural join
• A natural join is a join statement that compares the common columns of both
tables with each other. One should check whether common columns exist in
both tables before doing a natural join.
• Note: Natural joins may cause problems if columns are added or renamed. It is
highly recommended to not use them.

SELECT dname, ename FROM dept NATURAL JOIN emp;

SELECT department_name, first_name||' '||last_name FROM departments

NATURAL JOIN employees;
 Inner Join
Only the rows that match the search conditions are returned.

SELECT s.sid, s.name, r.bid

FROM Sailors s INNER JOIN Reserves r
ON s.sid = r.sid
Returns only those sailors who have reserved boats
SQL-92 also allows:

SELECT s.sid, s.name, r.bid

FROM Sailors s NATURAL JOIN Reserves r
“NATURAL” means equi-join for each pair of attributes with the same name
 SELECT s.sid, s.name, r.bid
FROM Sailors s INNER JOIN Reserves r
ON s.sid = r.sid

sid sname rating age sid bid day

22 Dustin 7 45.0 22 101 10/10/96
31 Lubber 8 55.5 95 103 11/12/96
95 Bob 3 63.5

s.sid s.name r.bid

22 Dustin 101
95 Bob 103
 Outer join
• An outer join is a join similar to the equi join, but Oracle will also return non
matched rows from the table.
• Oracle 9i introduced the ANSI/ISO 1999 standard syntax for specifying outer
joins with the LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN
clauses. The legacy syntax is to use a (+) in the query's WHERE clause, similar
to SQL Server's (*) syntax
• A situation may occur where we need to display all the Department details
though some of the Departments don’t have any employee working under
them
• In that case the Outer join is needed. An Outer join is a method for forcefully
retrieving selected rows from one table for which no matching rows are
present in the other table
• In case of EQUI JOIN non-matched rows are not selected
 Outer join
• Our intention is to display all employee details with their corresponding
department name and number along with those department numbers and
names where no employees are present
• Notice the plus sign (+) in the statement. This symbol enforces the query to
retrieve all the rows from the DEPT table for which no matching rows are
available in the EMP table

SELECT DEPT.DEPTNO, DNAME, EMPNO, ENAME FROM DEPT, EMP

WHERE DEPT.DEPTNO=EMP.DEPTNO(+) ;
 Left Outer Join

Left Outer Join returns all matched rows, plus all

unmatched rows from the table on the left of the join
clause
(use nulls in fields of non-matching tuples)

SELECT s.sid, s.name, r.bid

FROM Sailors s LEFT OUTER JOIN Reserves r
ON s.sid = r.sid

Returns all sailors & information on whether they have

reserved boats
 SELECT s.sid, s.name, r.bid
FROM Sailors s LEFT OUTER JOIN Reserves r
ON s.sid = r.sid
SAILORS RESERVES
sid sname rating age sid bid day
22 Dustin 7 45.0 22 101 10/10/96
31 Lubber 8 55.5 95 103 11/12/96
95 Bob 3 63.5

s.sid s.name r.bid

22 Dustin 101
95 Bob 103
31 Lubber
 Right Outer Join

Right Outer Join returns all matched rows, plus all unmatched
rows from the table on the right of the join clause
SELECT r.sid, b.bid, b.name
FROM Reserves r RIGHT OUTER JOIN Boats b
ON r.bid = b.bid

Returns all boats & information on which ones are reserved.

 SELECT r.sid, b.bid, b.name
FROM Reserves r RIGHT OUTER JOIN Boats b
ON r.bid = b.bid

RESERVES BOATS
bid bname color
sid bid day 101 Interlake blue
22 101 10/10/96 102 Interlake red
95 103 11/12/96 103 Clipper green
104 Marine red
r.sid b.bid b.name
22 101 Interlake
102 Interlake
95 103 Clipper
104 Marine
 Full Outer Join

Full Outer Join returns all (matched or unmatched) rows from

the tables on both sides of the join clause

SELECT r.sid, b.bid, b.name

FROM Reserves r FULL OUTER JOIN Boats b
ON r.bid = b.bid

Returns all boats & all information on reservations

 SELECT r.sid, b.bid, b.name
FROM Reserves r FULL OUTER JOIN Boats b
RESERVES ON r.bid = b.bid BOATS
sid bid day bid bname color
101 Interlake blue
22 101 10/10/96 102 Interlake red
95 103 11/12/96 103 Clipper green
104 Marine red

r.sid b.bid b.name

22 101 Interlake
102 Interlake
95 103 Clipper
104 Marine
Will return rows which have match for at least one of
 Nonequi join

• An nonequi (or theta) join is an inner join statement that uses an unequal
operation (i.e: <>, >, <, !=, BETWEEN, etc.) to match rows from different
tables. The converse of an nonequi join is a equi join operation.

SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal

BETWEEN s.losal AND s.hisal;

• Using ANSI join syntax for the same query:

SELECT e.ename, e.sal, s.grade FROM emp e INNER JOIN salgrade s ON
e.sal BETWEEN s.losal AND s.hisal;
 Cartesian join
• A Cartesian join or Cartesian product is a join of every row of one table to
every row of another table. This normally happens when no matching join
columns are specified. For example, if table A with 100 rows is joined with
table B with 1000 rows, a Cartesian join will return 100,000 rows.
• Note: A Cartesian product may indicate a missing join condition. A query must
have at least (N-1) join conditions to prevent a Cartesian product, where N is
the number of tables in the query. However a Cartesian product may be
something valid; for instance, in a star schema, a Cartesian join between
dimension tables is not unusual.
• Using Oracle join syntax:
SELECT * FROM emp, dept;
SELECT * FROM emp, dept WHERE dept.deptno = 10 AND emp.sal > 10000;
• Using ANSI join syntax:
SELECT * FROM emp CROSS JOIN dept;
Objectives
In this lesson, you will learn to:
• Use subqueries
• Use subqueries with the IN clause
• Use subqueries with the EXISTS clause
• Use nested subqueries
• Use correlated subqueries
• Use the SELECT INTO statement
• Use the UNION operator
 Subqueries
• A Sub Query can be defined as a SELECT statement that is included in the
clause of another SELECT statement
• Sub Queries are very useful when there is a necessity to retrieve rows from a
table based on a condition that is dependent upon the data in the
same table
• Sub Query is also known as nested SELECT, SUB-SELECT or inner
SELECT statement
• The SQL first executes the inner (child) query and the output of the inner query is
not displayed but passed to the outer (parent) SQL statement for its use
• Here it is displaying the employee number, name and salary for all those
employees from EMP table who are getting maximum salary

SELECT EMPNO, ENAME, SAL

FROM EMP
WHERE SAL=(SELECT MAX (SAL) FROM EMP);
 Subqueries
• Sub queries can be divided into the following two categories:
✓ Single-Row Sub Query: A single-row sub query returns only one row from the
inner (child) query. Thus the sub-query example given above is an example of a
single-row subquery
 Subqueries
• Multiple-Row Sub Query: A Multiple-Row Sub Query returns more than one
row from the inner (child) query. In this case, we use the following comparison
operators:

Ø IN: Compares for equality with every member in the list.

Ø ANY: Compares for equality with each value returned from the Sub Query.
Ø ALL: Compares a value to every value returned by the Sub Query

SELECT DEPTNO,DNAME FROM DEPT

WHERE DEPTNO IN (SELECT DISTINCT DEPTNO FROM EMP);
 Subqueries
• The following points should be noted with regard to ALL and ANY operators:

• q <ANY less than the maximum

• q >ANY means greater than the minimum.
• q =ANY works in the same manner as the IN operator.
• q >ALL more than the maximum
• q <ALL less than the minimum.
Subqueries
• A subquery can be defined as a SELECT query that returns a
single value
• Subqueries are nested within a SELECT, INSERT, UPDATE, or
DELETE statement
• Subqueries can be used to retrieve data from multiple tables
and can be used as an alternative to a join
• Subqueries can also be used inside the WHERE or HAVING
clause of the SELECT, INSERT, UPDATE, and DELETE statements
More on Subqueries
• Subqueries with IN
– A subquery introduced with IN returns zero or
more values
– Example
SELECT Au_Id
FROM TitleAuthor
WHERE Title_Id IN
(SELECT Title_Id FROM Sales)
• Subqueries with EXISTS
– A subquery, when used with the EXISTS clause,
always returns data in terms of a TRUE or FALSE
value
– Example
SELECT Pub_Name
FROM Publishers
WHERE EXISTS (SELECT * FROM Titles
WHERE Type = 'business')
• Subqueries with Aggregate Functions
– Aggregate functions can also be used in subqueries
– Example
SELECT Title
FROM Titles
WHERE Advance > (SELECT
AVG(Advance)
FROM Titles
WHERE Type = 'business')
 Subqueries
• ANY Operator : ANY works in the same way as the IN operator
• But it used only in the sub query. It compares a value to each value returned
from a Sub Query
• The sub query used here can return any datatype. The list, which will be
generated by the sub query, should take values from a single column. Multiple
columns are not allowed.
• Here we are displaying the details of all those employees who are
drawing more salary than any of the employees from department 10
• Here ,the inner query is returning the salaries of employees whose DEPTNO =
10 and the outer query is displaying the name, number and salary of those
employees whose salary is greater than any of the salary returned by the inner
query.
SELECT EMPNO, ENAME, SAL FROM EMP
WHERE SAL> ANY (SELECT SAL FROM EMP WHERE DEPTNO = 10);
SELECT DEPTNO,DNAME FROM DEPT WHERE DEPTNO IN
(SELECT DISTINCT DEPTNO FROM EMP);
 Subqueries
• Exists Operator
• EXISTS returns a value of true if the sub query that follows it return at least
one row.
• We are trying to display the details of all employees from EMP table if and only
if the total number of employees in department 30 is more than 5
• Note : the use of EXISTS operator. Here this operator will return true if the
inner query is successful and then only the outer query will return the
employee details of all employees.

SELECT EMPNO, ENAME, DEPTNO FROM EMP WHERE EXISTS

(SELECT COUNT(*) FROM EMP WHERE DEPTNO=30
GROUP BY DEPTNO
HAVING COUNT(*) > 5)
ORDER BY DEPTNO;
 Subqueries
• ALL Operator
• ALL operator compares a value with every value returned from the Sub Query.
• In the example the details of those employees who earn more than
the maximum salary from department 10

SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL>ALL

(SELECT SAL FROM EMP WHERE DEPTNO = 10);
• Subqueries Restrictions
– SQL Server restricts the use of certain
methods and techniques, and forces the
implementation of certain standards while
using subqueries. The restrictions imposed
are:
• The column list of the SELECT statement of a
subquery introduced with a comparison operator
can include only one column
• The column used in the WHERE clause of the outer
query should be compatible with the column used
in the SELECT list of the inner query
• Nested Subqueries
– A subquery can itself contain one or more
subqueries
– Example
SELECT 'Author Name' = SUBSTRING
(Au_Fname, 1, 1) + '. '+ Au_Lname
FROM Authors
WHERE Au_Id IN (SELECT Au_Id
FROM TitleAuthor
WHERE Title_Id =(SELECT
Title_Id FROM Titles
WHERE Title = 'Net
Etiquette'))
 Derived Tables
• A table can also be created based upon another table
• In such a case, the new table is created on the basis of a select statement on
an existing table. This phenomenon is known as DERIVED TABLE
• When a new table is created in such a fashion it inherits the column
characteristics from the existing table
• To put it simply, the newly created table has the same column name, datatype
and size as the table from which it was created.
• The example given below will illustrate this principle.
CREATE TABLE T1 AS
SELECT DEPTNO, MAX (SAL) HSAL, MIN (SAL) LSAL
FROM EMP
GROUP BY DEPTNO;
 Derived Tables
• Ex 1
CREATE TABLE T1(ENO, NAME, SALARY, HRA, DT)
AS
SELECT EMPNO, ENAME, SAL, SAL*.5, SYSDATE
FROM EMP;
• • Ex 2
CREATE TABLE T1
AS
SELECT * FROM EMP;
• Correlated Subqueries
– Can be defined as queries that depend on the
outer query for its evaluation
– Example
SELECT Title, Type, Advance
FROM Titles t1
WHERE t1.Advance > (SELECT
AVG(t2.Advance)
FROM Titles t2 WHERE
t1.Type =
t2.Type)
 Views
 A view is a virtual table with no data, but can be operated like any
other table. It is like a window through which you can view the data
of another table, which is called the base table
 This window is called a view and is given a name and can be
operated with some restrictions. Views are defined to satisfy the
following purposes:
 To restrict database access as views presents only a selected
portion of the database.
 Facilitates the user in making complex queries easier. For
example, a user who cannot query from multiple tables by joining
them can easily retrieve the required data with the help of a view.
 Provides data independence. For example a view can be used to
retrieve data from multiple tables which can be utilized by the user
without having to access the tables. Thus database security is not
sacrificed.
VIEWS
 A database view is a logical or virtual table based on a
query.
 It is useful to think of a view as a stored query.
 Views are created through use of a CREATE VIEW
command that incorporates use of the SELECT
statement.
 Views are queried just like tables.
 CREATING A VIEW
 CREATE VIEW Syntax

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW

<view name> [(column alias name….)] AS <query>
[WITH [CHECK OPTION] [READ ONLY]
[CONSTRAINT]];
 The OR REPLACE option is used to create a view that already
exists.
 If you attempt to create a view that already exists without using
the OR REPLACE option, Oracle will return the ORA-00955:
name is already used by an existing object error message and
the CREATE VIEW command will fail.
 VIEWS
CREATE VIEW employee_parking
(parking_space, last_name,
first_name, ssn) AS
SELECT emp_parking_space, emp_last_name,
emp_first_name, emp_ssn
FROM employee;

CREATE VIEW KEY_VIEW AS

SELECT DEPT, DEPTNO, DNAME, EMPNO, SAL
FROM DEPT, EMP
WHERE DEPT.DEPTNO=EMP.DEPTNO;
VIEWS
SELECT *
FROM employee_parking;

PARKING_SPACE LAST_NAME FIRST_NAME SSN

------------- ---------- ----------- --------
1 Bordoloi Bijoy 999666666
3 Joyner Suzanne 999555555
32 Zhu Waiman 999444444
more rows are displayed…

 Notice that the only columns in the query are

those defined as part of the view.
 CREATING A VIEW
 The FORCE option allows a view to be created even if a base
table that the view references does not already exist.
 This option is used to create a view prior to the actual
creation of the base tables and accompanying data. Before
such a view can be queried, the base tables must be created
and data must be loaded into the tables. This option can
also be used if a system user does not currently have the
privilege to create a view.
 The NOFORCE option is the opposite of FORCE and allows
a system user to create a view if they have the required
permissions to create a view, and if the tables from which
the view is created already exist. This is the default option.
 CREATING A VIEW
 The WITH READ ONLY option allows creation of a view
that is read-only. You cannot use the DELETE, INSERT,
or UPDATE commands to modify data for the view.
ORDER
 Specify ORDER to guarantee that sequence numbers are
generated in order of request. This clause is useful if you
are using the sequence numbers as timestamps.
Guaranteeing order is usually not important for
sequences used to generate primary keys.
Example
CREATE VIEW empview7 AS
SELECT emp_ssn, emp_first_name, emp_last_name
FROM employee
WHERE emp_dpt_number=7;
View created.

• A simple query of the empview7 shows the following data.

SELECT *
FROM empview7;
EMP_SSN EMP_FIRST_NAME EMP_LAST_NAME
--------- ------------------------- -------------------------
999444444 Waiman Zhu
999111111 Douglas Bock
999333333 Dinesh Joshi
999888888 Sherri Prescott
Example
• It is also possible to create a view that has exactly
the same structure as an existing database table.
• The view named dept_view shown next has
exactly the same structure as department table.

CREATE VIEW dept_view AS

SELECT *
FROM department;
View created.
Example
• We can recreate the view by using the OR REPLACE
clause to create a view that is read-only by specifying a
WITH READ ONLY clause.
• The new version of dept_view will restrict data
manipulation language operations on the view to the use
of the SELECT command.

CREATE OR REPLACE VIEW dept_view AS

SELECT *
FROM department WITH READ ONLY CONSTRAINT
vw_dept_view_read_only;
View created.
FUNCTIONS AND VIEWS – A JOIN VIEW

• In addition to specifying columns from existing

tables, you can use single row functions
consisting of number, character, date, and group
functions as well as expressions to create
additional columns in views.
• This can be extremely useful because the system
user will have access to data without having to
understand how to use the underlying functions.
Example
CREATE OR REPLACE VIEW dept_salary
(name, min_salary, max_salary, avg_salary) AS
SELECT d.dpt_name, MIN(e.emp_salary),
MAX(e.emp_salary), AVG(e.emp_salary)
FROM employee e, department d
WHERE e.emp_dpt_number=d.dpt_no
GROUP BY d.dpt_name;
View created.
SELECT *
FROM dept_salary;
NAME MIN_SALARY MAX_SALARY AVG_SALARY
------------------------- ------------------ ------------------ ------------------
Admin and Records 25000 43000 31000
Headquarters 55000 55000 55000
Production 25000 43000 34000
 INSERTING , UPDATING, AND DELETING TABLE ROWS
THROUGH VIEWS

• You can insert a row if the view in use is one that

is updateable (not read-only).
• A view is updateable if the INSERT command
does not violate any constraints on the underlying
tables.
• This rule concerning constraint violations also
applies to UPDATE and DELETE commands.
Example
CREATE OR REPLACE VIEW dept_view AS
SELECT dpt_no, dpt_name
FROM department;

INSERT INTO dept_view VALUES (18, 'Department 18');

INSERT INTO dept_view VALUES (19, 'Department 20');

SELECT *
FROM dept_view;
DPT_NO DPT_NAME
------------ --------------------
7 Production
3 Admin and Records
1 Headquarters
18 Department 18
19 Department 20
Example
UPDATE dept_view SET dpt_name = 'Department 19‘
WHERE dpt_no = 19;
1 row updated.

SELECT *
FROM department
WHERE dpt_no >= 5;
DPT_NO DPT_NAME DPT_MGRSS DPT_MGR_S
----------- -------------------- ------------------ -----------------
7 Production 999444444 22-MAY-98
18 Department 18
19 Department 19
more rows are displayed…
More Examples
DELETE dept_view
WHERE dpt_no = 18 OR dpt_no = 19;
2 rows deleted.

SELECT *
FROM department;
DPT_NO DPT_NAME DPT_MGRSS DPT_MGR_S
------------ ------------------------ ------------------ ----------------
7 Production 999444444 22-MAY-98
3 Admin and Records 999555555 01-JAN-01
1 Headquarters 999666666 19-JUN-81
DROPPING VIEW
• A DBA or view owner can drop a view with the
DROP VIEW command. The following
command drops a view named dept_view.

DROP VIEW dept_view;

View dropped.
Updateable views
 Single table
 Aggregate function ,group by and having clause
should not be present
 No subqueries
 INSERT possible if PRIMARY KEY and all NOT NULL
columns are included
 UPDATE ,DELETE records even if PRIMARY key value
and NOT NULL columns are excluded from view
definition
SEQUENCES
• Oracle provides the capability to generate
sequences of unique numbers, and they are
called sequences.
• Just like tables, views, indexes, and synonyms, a
sequence is a type of database object.
• Sequences are used to generate unique,
sequential integer values that are used as
primary key values in database tables.
• The sequence of numbers can be generated in
either ascending or descending order.
Creating Sequences
• The syntax of the CREATE SEQUENCE command
is fairly complex because it has numerous
optional clauses.
CREATE SEQUENCE <sequence name>
[INCREMENT BY <number>]
[START WITH <start value number>]
[MAXVALUE <MAXIMUM VALUE NUMBER>]
[NOMAXVALUE]
[MINVALUE <minimum value number>]
[CYCLE]
[NOCYCLE]
[CACHE <number of sequence value to cache>]
[NOCACHE]
[ORDER]
[NOORDER];
 Example

CREATE SEQUENCE order_number_sequence

INCREMENT BY 1
START WITH 1
MAXVALUE 100000000
MINVALUE 1
CYCLE
CACHE 10;
Sequence created.
SELECT order_number_sequence.nextval FROM DUAL
 Accessing Sequence Values
• Sequence values are generated through the use of
two pseudocolumns named currval and nextval.
• A pseudocolumn behaves like a table column, but
psuedocolumns are not actually stored in a table.
• We can select values from pseudocolumns but
cannot perform manipulations on their values.
• The first time you select the nextval pseudocolumn,
the initial value in the sequence is returned.
• Subsequent selections of the nextval
pseudocolumn will cause the sequence to
increment as specified by the INCREMENT BY
clause and will return the newly generated
sequence value.
 Accessing Sequence Values
 The NOCYCLE option indicates that the sequence
cannot generate more values after reaching its
maximum or minimum value.
 The CACHE clause preallocates a set of sequence
numbers and keeps them in memory so that sequence
numbers can be accessed faster. When the last of the
sequence numbers in the cache has been used, the
database reads another set of numbers into the cache.
Sequence
CACHE
Specify how many values of the sequence the
database preallocates and keeps in memory for
faster access

NOCACHE
Specify NOCACHE to indicate that values of the
sequence are not preallocated. If you omit both
CACHE and NOCACHE, the database caches
20 sequence numbers by default
 Sequence
ORDER
It is necessary only to guarantee ordered generation if you
are using Oracle Database with Real Application
Clusters. If you are using exclusive mode, sequence
numbers are always generated in order.
NOORDER

Specify NOORDER if you do not want to guarantee

sequence numbers are generated in order of request.
This is the default.
 Accessing Sequence Values
• The currval pseudocolumn returns the current
value of the sequence, which is the value returned
by the last reference to nextval.

• Example

CREATE TABLE sales_order (

order_number NUMBER(9)PRIMARY KEY,
order_amount NUMBER(9,2));
 Accessing Sequence Values
• The INSERT commands shown below insert three rows into
the sales_order table. The INSERT commands reference the
order_number_sequence.nextval pseudocolumn.

INSERT INTO sales_order

VALUES(order_number_sequence.nextval, 155.59
);

INSERT INTO sales_order

VALUES(order_number_sequence.nextval, 450.00
);

INSERT INTO sales_order

VALUES(order_number_sequence.nextval, 16.95);
Accessing Sequence Values
SELECT *
FROM sales_order;

ORDER_NUMBER ORDER_AMOUNT
------------ ------------
1 155.59
2 450
3 16.95
 Accessing Sequence Values
• Use of currval.

CREATE TABLE order_details (

order_number NUMBER(9),
order_row NUMBER(3),
product_desc VARCHAR2(15),
quantity_ordered NUMBER(3),
product_price NUMBER(9,2),
PRIMARY KEY (order_number, order_row),
FOREIGN KEY (order_number) REFERENCES sales_order);
 Accessing Sequence Values
• The order_details table has a FOREIGN KEY reference to the
sales_order table through the order_number column.
INSERT INTO sales_order
VALUES ( order_number_sequence.nextval, 200.00 );

INSERT INTO order_details

VALUES ( order_number_sequence.currval, 1, 'End Table',1, 100.00);

INSERT INTO order_details

VALUES ( order_number_sequence.currval, 2, 'Table Lamp',2, 50.00);
 Accessing Sequence Values
SELECT *
FROM sales_order;

ORDER_NUMBER ORDER_AMOUNT
------------ ------------
5 200

SELECT *
FROM order_details;

ORDER_NUMBER ORDER_ROW PRODUCT_DESC QUANTITY_ORDERED PRODUCT_PRICE

--------- -------- ---------- ------------- ----------
5 1 End Table 1 100
5 2 Table Lamp 2 50
Altering a Sequence
• A sequence is usually altered when it is
desirable to set or eliminate the values of the
MINVALUE or MAXVALUE parameters, or to
change the INCREMENT BY value, or to
change the number of cached sequence
numbers.
• The ALTER SEQUENCE command shown here
changes the MAXVALUE of the
order_number_sequence to 200,000,000.
ALTER SEQUENCE order_number_sequence
MAXVALUE 200000000;
Sequence altered.
Altering a Sequence
• When specifying a MINVALUE clause, the
specified value should be less than the
MAXVALUE where a sequence generates
ascending numbers.
• In the case of a descending sequence, the
MAXVALUE should be less than the
MINVALUE.
 Viewing Sequence Properties
• You may need to review the names and properties of
your sequences.
• You can do this by querying the USER_SEQUENCES
system view with a SELECT command.This view is
part of the database's data dictionary.

SELECT * FROM USER_SEQUENCES;

SEQUENCE_NAME MIN_VAL MAX_VALUE INCRE C O CACHE_SIZE Last_N

---------------- ------ ---------- ----- -- -- -------- ----
-
ORDER_NUMBER_SEQUENCE 1 200000000 1 Y N 10 6
 Dropping a Sequence
• DROP SEQUENCE command is used to drop
sequences that need to be recreated or are no
longer needed.
• The general format is shown here along with an
example that drops the order_number_sequence
object.
DROP SEQUENCE <sequence name>;
DROP SEQUENCE order_number_sequence;
Sequence dropped.
 INDEX
 Indexes are created to speed up the SQL statement execution on a table as
Index directly points to the location of the rows containing the value
 These are created implicitly when we declare a column as PRIMARY or
UNIQUE KEY
 We can create an Index explicitly by using the CREATE INDEX
command.
 Indexes are used in an SQL database for two primary reasons:
1. To provide the ordering of data based on the index’s field or fields
2. To optimize the execution speed of enquiry
 The example given below creates a normal Index.
CREATE INDEX NEW_INDEX ON EMP (EMPNO);
CRETAE UNIQUE INDEXON EMP (EMPNO);

Note: A unique Index is automatically created when a PRIMARY KEY or

UNIQUE KEY constraint is defined with regard to a table.
 INDEX
Composite Indexes
 An index created on multiple columns of a table is called
Composite Index.
Example:
CREATE INDEX I1 ON EMP (DEPTNO,JOB);

DROP INDEX

Alter table <table name> drop index <index name>;

Transactions, Roles & Privileges
ACID: Data Control Statements (DCL)
 DCL statements are:
 The SAVEPOINT command, that sets a marker that
enables undoing transactions only to a save point.
 The COMMIT command, which makes permanent the
data change.
 The ROLLBACK command, which can undo everything
since:
 The last COMMIT command.
 A specific SAVEPOINT command.
Transactions, Roles & Privileges
ACID: ROLLBACK command

ROLLBACK [TO <savepoint_name>];

Transactions, Roles & Privileges
ACID: SAVEPOINT command

SAVEPOINT <savepoint_name>;
Transactions, Roles & Privileges
ACID: COMMIT command

SAVEPOINT <savepoint_name>;
END