Oracle Material 2

CONTENTS
I. Introduction
II. Creating Tables
III. Insert Table
IV. Delete Table
V. Alter Table
VI. Select Table
VII. Update Table
VIII. Aliases
IX. Where Clause
X. Operators
XI. Order by
XII. Group by
XIII. Group Functions
XIV. Having
XV. Primary Key
XVI. Foreign Key
XVII. Joins
XVIII. Veiws
XIX. Indexes
XX. Roles

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 3

I. Introduction
Oracle database is a relational database management system. It is known as Oracle database,
OracleDB or simply Oracle. It is produced and marketed by Oracle Corporation.

Oracle database is the first database designed for enterprise grid computing. The enterprise grid
computing provides the most flexible and cost effective way to manage information and
applications.

Different editions of Oracle database

Following are the four editions of the Oracle database.

Enterprise Edition: It is the most robust and secure edition. It offers all features, including
superior performance and security.

Standard Edition: It provides the base functionality for users that do not require Enterprise
Edition’s robust package.

Express Edition (XE): It is the lightweight, free and limited Windows and Linux edition.

Oracle Lite: It is designed for mobile devices.

The Oracle Corporation

Oracle Corporation is the largest software company in the field of database business. Its
relational database was the first to support SQL which has since become the industry standard.

Oracle database is one of the most trusted and widely used relational database engines. The
biggest rival of Oracle database is Microsoft’s SQL Server.

History of Oracle

Oracle was originally developed by Lawrence Ellison (Larry Ellision) and his two friends and
former co-worker in 1977. Oracle DB runs on the most major platforms like Windows, UNIX,
Linux and Mac OS.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

 Oracle Material 4
II. Creating Tables

In Oracle, CREATE TABLE statement is used to create a new table in the database.

To create a table, you have to name that table and define its columns and datatype for each
column.

Syntax:

1. CREATE TABLE table_name

2. (
3. column1 datatype [ NULL | NOT NULL ],
4. column2 datatype [ NULL | NOT NULL ],
5. …
6. column_n datatype [ NULL | NOT NULL ]
7. );

Parameters used in syntax

o table_name: It specifies the name of the table which you want to create.
o column1, column2, … column n: It specifies the columns which you want to add in the
table. Every column must have a datatype. Every column should either be defined as
“NULL” or “NOT NULL”. In the case, the value is left blank; it is treated as “NULL” as
default.

Oracle CREATE TABLE Example

Here we are creating a table named customers. This table doesn’t have any primary key.

1. CREATE TABLE customers

2. ( customer_id number(10) NOT NULL,
3. customer_name varchar2(50) NOT NULL,
4. city varchar2(50)
5. );

This table contains three columns

o customer_id: It is the first column created as a number datatype (maximum 10 digits in

length) and cannot contain null values.
o customer_name: it is the second column created as a varchar2 datatype (50 maximum
characters in length) and cannot contain null values.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

 Oracle Material 5

o city: This is the third column created as a varchar2 datatype. It can contain null values.

Oracle CREATE TABLE Example with primary key

1. CREATE TABLE customers
2. ( customer_id number(10) NOT NULL,
3. customer_name varchar2(50) NOT NULL,
4. city varchar2(50),
5. CONSTRAINT customers_pk PRIMARY KEY (customer_id)
6. );

What is Primary key

A primary key is a single field or combination of fields that contains a unique record. It must be
filled. None of the field of primary key can contain a null value. A table can have only one
primary key.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

 Oracle Material 6
III. Insert Table

In Oracle, INSERT statement is used to add a single record or multiple records into the table.

Syntax: (Inserting a single record using the Values keyword):

1. INSERT INTO table

2. (column1, column2, … column_n )
3. VALUES
4. (expression1, expression2, … expression_n );

Syntax: (Inserting multiple records using a SELECT statement):

1. INSERT INTO table

2. (column1, column2, … column_n )
3. SELECT expression1, expression2, … expression_n
4. FROM source_table
5. WHERE conditions;

Parameters:

1) table: The table to insert the records into.

2) column1, column2, … column_n:

The columns in the table to insert values.

3) expression1, expression2, … expression_n:

The values to assign to the columns in the table. So column1 would be assigned the value of
expression1, column2 would be assigned the value of expression2, and so on.

4) source_table:

The source table when inserting data from another table.

5) conditions:

The conditions that must be met for the records to be inserted.

Oracle Insert Example: By VALUE keyword

It is the simplest way to insert elements to a database by using VALUE keyword.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

 Oracle Material 7
See this example:

Consider here the already created suppliers table. Add a new row where the value of supplier_id
is 23 and supplier_name is Flipkart.

See this example:

1. INSERT INTO suppliers

2. (supplier_id, supplier_name)
3. VALUES
4. (50, ‘Flipkart’);
Output:
1 row(s) inserted.
0.02 seconds

Oracle Insert Example: By SELECT statement

This method is used for more complicated cases of insertion. In this method insertion is done by
SELECT statement. This method is used to insert multiple elements.

See this example:

In this method, we insert values to the “suppliers” table from “customers” table. Both tables are
already created with their respective columns.

Execute this query:

1. INSERT INTO suppliers

2. (supplier_id, supplier_name)
3. SELECT age, address
4. FROM customers
5. WHERE age > 20;
Output:
4 row(s) inserted.

XX)....... seconds

You can even check the number of rows that you want to insert by following statement:

1. SELECT count(*)
2. FROM customers
3. WHERE age > 20;

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

 Oracle Material 8
Output:
Count(*)
4
Oracle INSERT ALL statement

The Oracle INSERT ALL statement is used to insert multiple rows with a single INSERT
statement. You can insert the rows into one table or multiple tables by using only one SQL
command.

Syntax

1. INSERT ALL
2. INTO table_name (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
3. INTO table_name(column1, column2, column_n) VALUES (expr1, expr2, expr_n)
4. INTO table_name (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
5. SELECT * FROM dual;

Parameters

1) table_name: it specifies the table in which you want to insert your records.

2) column1, column2, column_n: this specifies the columns in the table to insert values.

3) expr1, expr2, expr_n: this specifies the values to assign to the columns in the table.

Oracle INSERT ALL Example

This example specifies how to insert multiple records in one table. Here we insert three rows into
the “suppliers” table.

1. INSERT ALL
2. INTO suppliers (supplier_id, supplier_name) VALUES (20, ‘Google’)
3. INTO suppliers (supplier_id, supplier_name) VALUES (21, ‘Microsoft’)
4. INTO suppliers (supplier_id, supplier_name) VALUES (22, ‘Apple’)
5. SELECT * FROM dual;

Output

3 row(s) inserted.
0.02 seconds

This is totally equivalent to the following three INSERT statements.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

 Oracle Material 9

1. INSERT INTO suppliers (supplier_id, supplier_name) VALUES (1000, ‘Google’);

2. INSERT INTO suppliers (supplier_id, supplier_name) VALUES (2000, ‘Microsoft’);
3. INSERT INTO suppliers (supplier_id, supplier_name) VALUES (3000, ‘Apple’);

Oracle INSERT ALL Example: (Insert into multiple tables)

The INSERT ALL statement can also be used to insert multiple rows into more than one table by
one command only.

In the following example, we are going to insert records into the both “suppliers” and
“customers” tables.

1. INSERT ALL
2. INTO suppliers (supplier_id, supplier_name) VALUES (30, ‘Google’)
3. INTO suppliers (supplier_id, supplier_name) VALUES (31, ‘Microsoft’)
4. INTO customers (age, name, address) VALUES (29, ‘Luca Warsi’, ‘New York’)
5. SELECT * FROM dual;

Output

3 row(s) inserted.
0.03 seconds

Here, total 3 rows are inserted, 2 rows are inserted into the suppliers table and one row into the
customers table.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

 Oracle Material 10
IV. Delete Table

Oracle DELETE Statement

In Oracle, DELETE statement is used to remove or delete a single record or multiple records
from a table.

Syntax

1. DELETE FROM table_name

2. WHERE conditions;

Parameters

1) table_name: It specifies the table which you want to delete.

2) conditions: It specifies the conditions that must met for the records to be deleted.

Oracle Delete Example: On one condition

1. DELETE FROM customers
2. WHERE name = ‘Sohan’;

This statement will delete all records from the customer table where name is “Sohan”.

Oracle Delete Example: On multiple conditions

1. DELETE FROM customers
2. WHERE last_name = ‘Maurya’
3. AND customer_id > 2;

This statement will delete all records from the customers table where the last_name is “Maurya”
and the customer_id is greater than 2.

Oracle TRUNCATE TABLE

In Oracle, TRUNCATE TABLE statement is used to remove all records from a table. It works
same as DELETE statement but without specifying a WHERE clause. It is generally used when
you don?t have to worry about rolling back

Once a table is truncated, it can?t be rolled back. The TRUNCATE TABLE statement does not
affect any of the table?s indexes, triggers or dependencies.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

 Oracle Material 11
Syntax

1. TRUNCATE TABLE [schema_name.]table_name

Parameters

1) schema_name: This parameter specifies the name of the schema that the table belongs to. It is
optional.

2) table_name: It specifies the table that you want to truncate.

Oracle TRUNCATE Table Example

Consider a table named “customers” and execute the following query to truncate this

1. TRUNCATE TABLE customers;

Output

Table truncated.
1.11 seconds

Now check the customers table, you will find that there is no data available in that table. It is
equally similar to DELETE TABLE statement in Oracle.

Oracle DELETE Table Example

1. DELETE TABLE customers;

TRUNCATE TABLE vs DELETE TABLE

Both the statements will remove the data from the “customers” table but the main difference is
that you can roll back the DELETE statement whereas you can’t roll back the TRUNCATE
TABLE statement.

Oracle DROP TABLE Statement

Oracle DROP TABLE statement is used to remove or delete a table from the Oracle database.

Syntax

1. DROP [schema_name].TABLE table_name

2. [ CASCADE CONSTRAINTS ]
3. [ PURGE ];

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

 Oracle Material 12
Parameters

schema_name: It specifies the name of the schema that owns the table.

Table_name: It specifies the name of the table which you want to remove from the Oracle
database.

CASCADE CONSTRAINTS: It is optional. If specified, it will drop all referential integrity

constraints as well.

PURGE: It is also optional. If specified, the table and its dependent objects are placed in the
recycle bin and can?t be recovered.

DROP TABLE Example

1. DROP TABLE customers;
This will drop the table named customers.

DROP TABLE Example with PURGE parameter

1. DROP TABLE customers PURGE

This statement will drop the table called customers and issue a PURGE so that the space
associated with the customers table is released and the customers table is not placed in recycle
bin. So, it is not possible to recover that table if required.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

 Oracle Material 13
V. Alter Table

Oracle ALTER TABLE Statement

In Oracle, ALTER TABLE statement specifies how to add, modify, drop or delete columns in a
table. It is also used to rename a table.

How to add column in a table

Syntax:

1. ALTER TABLE table_name

2. ADD column_name column-definition;

Example:

Consider that already existing table customers. Now, add a new column customer_age into the
table customers.

1. ALTER TABLE customers

2. ADD customer_age varchar2(50);

Now, a new column “customer_age” will be added in customers table.

How to add multiple columns in the existing table

Syntax:

1. ALTER TABLE table_name

2. ADD (column_1 column-definition,
3. column_2 column-definition,
4. …
5. column_n column_definition);

Example

1. ALTER TABLE customers

2. ADD (customer_type varchar2(50),
3. customer_address varchar2(50));
Now, two columns customer_type and customer_address will be added in the table customers.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

 Oracle Material 14
How to modify column of a table

Syntax:

1. ALTER TABLE table_name

2. MODIFY column_name column_type;

Example:

1. ALTER TABLE customers

2. MODIFY customer_name varchar2(100) not null;
Now the column column_name in the customers table is modified
to varchar2 (100) and forced the column to not allow null values.

How to modify multiple columns of a table

Syntax:

1. ALTER TABLE table_name

2. MODIFY (column_1 column_type,
3. column_2 column_type,
4. …
5. column_n column_type);

Example:

1. ALTER TABLE customers

2. MODIFY (customer_name varchar2(100) not null,
3. city varchar2(100));
This will modify both the customer_name and city columns in the table.

How to drop column of a table

Syntax:

1. ALTER TABLE table_name

2. DROP COLUMN column_name;

Example:

1. ALTER TABLE customers

2. DROP COLUMN customer_name;

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

 Oracle Material 15
This will drop the customer_name column from the table.

How to rename column of a table

Syntax:

1. ALTER TABLE table_name

2. RENAME COLUMN old_name to new_name;

Example:

1. ALTER TABLE customers

2. RENAME COLUMN customer_name to cname;
This will rename the column customer_name into cname.

How to rename table

Syntax:

1. ALTER TABLE table_name

2. RENAME TO new_table_name;

Example:

2. ALTER TABLE customers

3. RENAME TO retailers;
This will rename the customer table into “retailers” table.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

 Oracle Material 16
VI. Select Table

Oracle SELECT Statement

The Oracle SELECT statement is used to retrieve data from one or more than one tables, object
tables, views, object views etc.

Syntax

1. SELECT expressions
2. FROM tables
3. WHERE conditions;

Parameters

1) Expressions: It specifies the columns or calculations that you want to retrieve.

2) Tables: This parameter specifies the tables that you want to retrieve records from. There must
be at least one table within the FROM clause.

3) Conditions: It specifies the conditions that must be followed for selection.

Select Example: select all fields

Let’s take an example to select all fields from an already created table named customers

1. SELECT *
2. FROM customers;

Output

Select Example: select specific fields

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

 Oracle Material 17
Example

1. SELECT age, address, salary

2. FROM customers
3. WHERE age < 25
4. AND salary > ‘20000’
5. ORDER BY age ASC, salary DESC;

Select Example: select fields from multiple tables (JOIN)

1. SELECT [Link], [Link]
2. FROM courses
3. INNER JOIN customers
4. ON courses.course_id = course_id
5. ORDER BY name;

Output

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 18
VII. Update Table

Update statement is used to update rows in existing tables which is in your own schema or if
you have update privilege on them.

For example to raise the salary by Rs.500 of employee number 104. You can give the
following statement.

Update emp set sal=sal+500 where empno = 104;

In the above statement if we did not give the where condition then all employees salary will
be raised by Rs. 500. That’s why always specify proper WHERE condition if don’t want to
update all employees.

For example We want to change the name of employee no 102 from ‘Sami’ to ‘Mohd Sami’
and to raise the salary by 10%. Then the statement will be.

Update emp set name=’Mohd Sami’,

sal=sal+(sal*10/100) where empno=102;

Now we want to raise the salary of all employees by 5%.

Update emp set sal=sal+(sal*5/100);

Now to change the names of all employees to uppercase.

Update emp set name=upper(name);

Suppose We have a student table with the following structure.

Now to compute total which is sum of Maths,Phy and Chem and average.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 19

Update student set total=maths+phy+chem,

average=(maths+phy+chem)/3;

Using Sub Query in the Update Set Clause.

Suppose we added the city column in the employee table and now we want to set this column
with corresponding city column in department table which is join to employee table on
deptno.

Update emp set city=(select city from dept

where deptno= [Link]);

Example – Update multiple columns

Let’s look at an UPDATE example that shows how to update more than one column in a table.

TIP: When you update multiple columns in an UPDATE statement, you need to comma separate
the column/value pairs in the SET clause.

In this UPDATE example, we have a table called suppliers with the following data:

supplier_id supplier_name city state

100 Microsoft Redmond Washington

200 Google Mountain View California

300 Oracle Redwood City California

400 Kimberly-Clark Irving Texas

500 Tyson Foods Springdale Arkansas

600 SC Johnson Racine Wisconsin

700 Dole Food Company Westlake Village California

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 20

supplier_id supplier_name city state

800 Flowers Foods Thomasville Georgia

900 Electronic Arts Redwood City California

Now let’s demonstrate how to use the UPDATE statement to update more than one column value
at once. Enter the following UPDATE statement:

UPDATE suppliers
SET supplier_id = 150,
supplier_name = ‘Apple’,
city = ‘Cupertino’
WHERE supplier_name = ‘Google’;

There will be 1 record updated. Select the data from the suppliers table again:

SELECT * FROM suppliers;

These are the results that you should see:

supplier_id supplier_name city state

100 Microsoft Redmond Washington

150 Apple Cupertino California

300 Oracle Redwood City California

400 Kimberly-Clark Irving Texas

500 Tyson Foods Springdale Arkansas

600 SC Johnson Racine Wisconsin

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 21

supplier_id supplier_name city state

700 Dole Food Company Westlake Village California

800 Flowers Foods Thomasville Georgia

900 Electronic Arts Redwood City California

This UPDATE example would update the supplier_id to 150, the supplier_name to ‘Apple’
and city to ‘Cupertino’ where thesupplier_name is ‘Google’.

Example – Update table with data from another table

Let’s look at an UPDATE example that shows how to update a table with data from another
table.

In this UPDATE example, we have a table called products with the following data:

product_id product_name category_id

1 Pear 50

2 Banana 50

3 Orange 50

4 Apple 50

5 Bread 75

6 Sliced Ham 25

7 Kleenex NULL

And a table called summary_data with the following data:

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 22

product_id current_category

1 10

2 10

3 10

4 10

5 10

8 10

Now let’s update the summary_data table with values from the products table. Enter the
following UPDATE statement:

UPDATE summary_data
SET current_category = (SELECT category_id
FROM products
WHERE products.product_id = summary_data.product_id)
WHERE EXISTS (SELECT category_id
FROM products
WHERE products.product_id = summary_data.product_id);

There will be 5 records update. Select the data from the summary_data table again:

SELECT * FROM summary_data;

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 23

These are the results that you should see:

product_id current_category

1 50

2 50

3 50

4 50

5 75

8 10

This example would update the current_category field in the summary_data table with
the category_id from the products table where the product_id values match. The first 5 records
in the summary_data table have been updated.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 24
VIII. Aliases

SQL ALIASES can be used to create a temporary name for columns or tables.

 COLUMN ALIASES are used to make column headings in your result set easier to read.
 TABLE ALIASES are used to shorten your SQL to make it easier to read or when you
are performing a self join (ie: listing the same table more than once in the FROM clause).

Syntax

The syntax to ALIAS A COLUMN in SQL is:

column_name [AS] alias_name

OR

The syntax to ALIAS A TABLE in SQL is:

table_name [AS] alias_name

Parameters or Arguments
column_name

The original name of the column that you wish to alias.

Table_name

The original name of the table that you wish to alias.

Alias_name

The temporary name to assign.

Note

 If the alias_name contains spaces, you must enclose the alias_name in quotes.
 It is acceptable to use spaces when you are aliasing a column name. However, it is not
generally good practice to use spaces when you are aliasing a table name.
 The alias_name is only valid within the scope of the SQL statement.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 25
DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to
populate the data. Then try the examples in your own database!

Example – How to Alias a Column Name

Generally, aliases are used to make the column headings in your result set easier to read. Most
commonly, you will alias a column when using an aggregate function such as MIN, MAX, AVG,
SUM or COUNT in your query.

Let’s look at an example of how to use to alias a column name in SQL.

In this example, we have a table called employees with the following data:

employee_number last_name first_name salary dept_id

1001 Smith John 62000 500

1002 Anderson Jane 57500 500

1003 Everest Brad 71000 501

1004 Horvath Jack 42000 501

Let’s demonstrate how to alias a column. Enter the following SQL statement:

SELECT dept_id, COUNT(*) AS total

FROM employees
GROUP BY dept_id;

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 26

There will be 2 records selected. These are the results that you should see:

dept_id total

500 2

501 2

In this example, we’ve aliased the COUNT(*) field as total. As a result, total will display as the
heading for the second column when the result set is returned. Because our alias_name did not
include any spaces, we are not required to enclose thealias_name in quotes.

Now, let’s rewrite our query to include a space in the column alias:

SELECT dept_id, COUNT(*) AS “total employees”

FROM employees
GROUP BY dept_id;

There will be 2 records selected. These are the results that you should see:

dept_id total employees

500 2

501 2

In this example, we’ve aliased the COUNT(*) field as “total employees” so this will become the
heading for the second column in our result set. Since there are spaces in this column alias, “total
employees” must be enclosed in quotes in the SQL statement.

Example – How to Alias a Table Name

When you alias a table, it is either because you plan to list the same table name more than once
in the FROM clause (ie: self join), or you want to shorten the table name to make the SQL
statement shorter and easier to read.

Let’s look at an example of how to alias a table name in SQL.

In this example, we have a table called products with the following data:

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 27

product_id product_name category_id

1 Pear 50

2 Banana 50

3 Orange 50

4 Apple 50

5 Bread 75

6 Sliced Ham 25

7 Kleenex NULL

And a table called categories with the following data:

category_id category_name

25 Deli

50 Produce

75 Bakery

100 General Merchandise

125 Technology

Now let’s join these 2 tables and alias each of the table names. Enter the following SQL
statement:

SELECT p.product_name, c.category_name

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 28
FROM products AS p
INNER JOIN categories AS c
ON p.category_id = c.category_id
WHERE p.product_name <> ‘Pear’;

There will be 5 records selected. These are the results that you should see:

product_name category_name

Banana Produce

Orange Produce

Apple Produce

Bread Bakery

Sliced Ham Deli

In this example, we’ve created an alias for the products table and an alias for
the categories table. Now within this SQL statement, we can refer to the products table as p and
the categories table as c.

When creating table aliases, it is not necessary to create aliases for all of the tables listed in the
FROM clause. You can choose to create aliases on any or all of the tables.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 29
IX. Where Clause

The SQL WHERE clause is used to filter the results and apply conditions in a SELECT,
INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the WHERE clause in SQL is:

WHERE conditions;

Parameters or Arguments
conditions

The conditions that must be met for records to be selected.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to
populate the data. Then try the examples in your own database!

Example – One Condition in the WHERE Clause

It is difficult to explain the syntax for the SQL WHERE clause, so let’s start with an example
that uses the WHERE clause to apply 1 condition.

In this example, we have a table called suppliers with the following data:

supplier_id supplier_name city state

100 Microsoft Redmond Washington

200 Google Mountain View California

300 Oracle Redwood City California

400 Kimberly-Clark Irving Texas

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 30

supplier_id supplier_name city state

500 Tyson Foods Springdale Arkansas

600 SC Johnson Racine Wisconsin

700 Dole Food Company Westlake Village California

800 Flowers Foods Thomasville Georgia

900 Electronic Arts Redwood City California

Enter the following SQL statement:

SELECT *
FROM suppliers
WHERE state = ‘California’;

There will be 4 records selected. These are the results that you should see:

supplier_id supplier_name city state

200 Google Mountain View California

300 Oracle Redwood City California

700 Dole Food Company Westlake Village California

900 Electronic Arts Redwood City California

In this example, we’ve used the SQL WHERE clause to filter our results from
the suppliers table. The SQL statement above would return all rows from the suppliers table
where the state is California. Because the * is used in the select, all fields from
the suppliers table would appear in the result set.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 31
Example – Two Conditions in the WHERE Clause (AND Condition)

You can use the AND condition in the WHERE clause to specify more than 1 condition that
must be met for the record to be selected. Let’s explore how to do this.

In this example, we have a table called customers with the following data:

customer_id last_name first_name favorite_website

4000 Jackson Joe [Link]

5000 Smith Jane [Link]

6000 Ferguson Samantha [Link]

7000 Reynolds Allen [Link]

8000 Anderson Paige NULL

9000 Johnson Derek [Link]

Now enter the following SQL statement:

SELECT *
FROM customers
WHERE favorite_website = ‘[Link]’
AND customer_id > 6000;

There will be 1 record selected. These are the results that you should see:

customer_id last_name first_name favorite_website

9000 Johnson Derek [Link]

This example uses the WHERE clause to define multiple conditions. In this case, this SQL
statement uses the AND conditionto return all customers whose favorite_website is
[Link] and whose customer_id is greater than 6000.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 32
Example – Two Conditions in the WHERE Clause (OR Condition)

You can use the OR condition in the WHERE clause to test multiple conditions where the record
is returned if any one of the conditions are met.

In this example, we have a table called products with the following data:

product_id product_name category_id

1 Pear 50

2 Banana 50

3 Orange 50

4 Apple 50

5 Bread 75

6 Sliced Ham 25

7 Kleenex NULL

Now enter the following SQL statement:

SELECT *
FROM products
WHERE product_name = ‘Pear’
OR product_name = ‘Apple’;

There will be 2 records selected. These are the results that you should see:

product_id product_name category_id

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 33

product_id product_name category_id

1 Pear 50

4 Apple 50

This example uses the WHERE clause to define multiple conditions, but instead of using
the AND condition, it uses the OR condition. In this case, this SQL statement would return all
records from the products table where the product_name is either Pear or Apple.

Example – Combining AND & OR conditions

You can also combine the AND condition with the OR condition to test more complex
conditions.

Let’s use the products table again for this example.

Product_id product_name category_id

1 Pear 50

2 Banana 50

3 Orange 50

4 Apple 50

5 Bread 75

6 Sliced Ham 25

7 Kleenex NULL

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 34

Now enter the following SQL statement:

SELECT *
FROM products
WHERE (product_id > 3 AND category_id = 75)
OR (product_name = ‘Pear’);

There will be 2 records selected. These are the results that you should see:

product_id product_name category_id

1 Pear 50

5 Bread 75

This example would return all products whose product_id is greater than 3 and category_id is 75
as well as all products whose product_name is Pear.

The parentheses determine the order that the AND and OR conditions are evaluated. Just like
you learned in the order of operations in Math class!

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 35
X. Operators

Comparison operators are used in the WHERE clause to determine which records to select. Here
is a list of the comparison operators that you can use in SQL:

Comparison Operator Description

= Equal

<> Not Equal

!= Not Equal

> Greater Than

>= Greater Than or Equal

< Less Than

<= Less Than or Equal

IN ( ) Matches a value in a list

NOT Negates a condition

BETWEEN Within a range (inclusive)

IS NULL NULL value

IS NOT NULL Non-NULL value

LIKE Pattern matching with % and _

EXISTS Condition is met if subquery returns at least one row

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 36
DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to
populate the data. Then try the examples in your own database!

Example – Equality Operator

In SQL, you can use the = operator to test for equality in a query.

In this example, we have a table called suppliers with the following data:

supplier_id supplier_name city state

100 Microsoft Redmond Washington

200 Google Mountain View California

300 Oracle Redwood City California

400 Kimberly-Clark Irving Texas

500 Tyson Foods Springdale Arkansas

600 SC Johnson Racine Wisconsin

700 Dole Food Company Westlake Village California

800 Flowers Foods Thomasville Georgia

900 Electronic Arts Redwood City California

Enter the following SQL statement:

SELECT *
FROM suppliers
WHERE supplier_name = ‘Microsoft’;

There will be 1 record selected. These are the results that you should see:

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 37

supplier_id supplier_name city state

100 Microsoft Redmond Washington

In this example, the SELECT statement above would return all rows from the suppliers table
where the supplier_name is equal to Microsoft.

Example – Inequality Operator

In SQL, there are two ways to test for inequality in a query. You can use either
the <> or != operator. Both will return the same results.

Let’s use the same suppliers table as the previous example.

Supplier_id supplier_name city state

100 Microsoft Redmond Washington

200 Google Mountain View California

300 Oracle Redwood City California

400 Kimberly-Clark Irving Texas

500 Tyson Foods Springdale Arkansas

600 SC Johnson Racine Wisconsin

700 Dole Food Company Westlake Village California

800 Flowers Foods Thomasville Georgia

900 Electronic Arts Redwood City California

Enter the following SQL statement to test for inequality using the <> operator:

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 38

SELECT *
FROM suppliers
WHERE supplier_name <> ‘Microsoft’;

OR enter this next SQL statement to use the != operator:

SELECT *
FROM suppliers
WHERE supplier_name != ‘Microsoft’;

There will be 8 records selected. These are the results you should see with either one of the SQL
statements:

supplier_id supplier_name city state

200 Google Mountain View California

300 Oracle Redwood City California

400 Kimberly-Clark Irving Texas

500 Tyson Foods Springdale Arkansas

600 SC Johnson Racine Wisconsin

700 Dole Food Company Westlake Village California

800 Flowers Foods Thomasville Georgia

900 Electronic Arts Redwood City California

In the example, both SELECT statements would return all rows from the suppliers table where
the supplier_name is not equal to Microsoft.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 39
Example – Greater Than Operator

You can use the > operator in SQL to test for an expression greater than.

In this example, we have a table called customers with the following data:

customer_id last_name first_name favorite_website

4000 Jackson Joe [Link]

5000 Smith Jane [Link]

6000 Ferguson Samantha [Link]

7000 Reynolds Allen [Link]

8000 Anderson Paige NULL

9000 Johnson Derek [Link]

Enter the following SQL statement:

SELECT *
FROM customers
WHERE customer_id > 6000;

There will be 3 records selected. These are the results that you should see:

customer_id last_name first_name favorite_website

7000 Reynolds Allen [Link]

8000 Anderson Paige NULL

9000 Johnson Derek [Link]

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 40

In this example, the SELECT statement would return all rows from the customers table where
the customer_id is greater than 6000. A customer_id equal to 6000 would not be included in the
result set.

Example – Greater Than or Equal Operator

In SQL, you can use the >= operator to test for an expression greater than or equal to.

Let’s use the same customers table as the previous example.

Customer_id last_name first_name favorite_website

4000 Jackson Joe [Link]

5000 Smith Jane [Link]

6000 Ferguson Samantha [Link]

7000 Reynolds Allen [Link]

8000 Anderson Paige NULL

9000 Johnson Derek [Link]

Enter the following SQL statement:

SELECT *
FROM customers
WHERE customer_id >= 6000;

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 41

There will be 4 records selected. These are the results that you should see:

customer_id last_name first_name favorite_website

6000 Ferguson Samantha [Link]

7000 Reynolds Allen [Link]

8000 Anderson Paige NULL

9000 Johnson Derek [Link]

In this example, the SELECT statement would return all rows from the customers table where
the customer_id is greater than or equal to 6000. In this case, the supplier_id equal to 6000
would be included in the result set.

Example – Less Than Operator

You can use the < operator in SQL to test for an expression less than.

In this example, we have a table called products with the following data:

product_id product_name category_id

1 Pear 50

2 Banana 50

3 Orange 50

4 Apple 50

5 Bread 75

6 Sliced Ham 25

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 42

product_id product_name category_id

7 Kleenex NULL

Enter the following SQL statement:

SELECT *
FROM products
WHERE product_id < 5;

There will be 4 records selected. These are the results that you should see:

product_id product_name category_id

1 Pear 50

2 Banana 50

3 Orange 50

4 Apple 50

In this example, the SELECT statement would return all rows from the products table where
the product_id is less than 5. Aproduct_id equal to 5 would not be included in the result set.

Example – Less Than or Equal Operator

In SQL, you can use the <= operator to test for an expression less than or equal to.

Let’s use the same products table as the previous example.

Product_id product_name category_id

1 Pear 50

2 Banana 50

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 43

Product_id product_name category_id

3 Orange 50

4 Apple 50

5 Bread 75

6 Sliced Ham 25

7 Kleenex NULL

Enter the following SQL statement:

SELECT *
FROM products
WHERE product_id <= 5;

There will be 5 records selected. These are the results that you should see:

product_id product_name category_id

1 Pear 50

2 Banana 50

3 Orange 50

4 Apple 50

5 Bread 75

In this example, the SELECT statement would return all rows from the products table where
the product_id is less than or equal to 5. In this case, the product_id equal to 5 would be included
in the result set.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 44
Advanced Operators:

IN Condition

The SQL IN condition (sometimes called the IN operator) allows you to easily test if an
expression matches any value in a list of values. It is used to help reduce the need for
multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the IN condition in SQL is:

expression IN (value1, value2, …. value_n);

Parameters or Arguments
expression

This is a value to test.

Value1, value2 …, alue_n

These are the values to test against expression. If any of these values matches expression,
then the IN condition will evaluate to true.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to
populate the data. Then try the examples in your own database!

Example – Using the IN Condition with Character Values

The IN condition can be used with any data type in SQL. Let’s look at how to use the IN
condition with character (string) values.

In this example, we have a table called suppliers with the following data:

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 45

supplier_id supplier_name city state

100 Microsoft Redmond Washington

200 Google Mountain View California

300 Oracle Redwood City California

400 Kimberly-Clark Irving Texas

500 Tyson Foods Springdale Arkansas

600 SC Johnson Racine Wisconsin

700 Dole Food Company Westlake Village California

800 Flowers Foods Thomasville Georgia

900 Electronic Arts Redwood City California

Enter the following SQL statement:

SELECT *
FROM suppliers
WHERE supplier_name IN (‘Microsoft’, ‘Oracle’, ‘Flowers Foods’);

There will be 3 records selected. These are the results that you should see:

supplier_id supplier_name city state

100 Microsoft Redmond Washington

300 Oracle Redwood City California

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 46

supplier_id supplier_name city state

800 Flowers Foods Thomasville Georgia

This example would return all rows from the suppliers table where the supplier_name is either
Microsoft, Oracle or Flowers Foods. Because the * is used in the select, all fields from
the suppliers table would appear in the result set.

It is equivalent to the following SQL statement:

SELECT *
FROM suppliers
WHERE supplier_name = ‘Microsoft’
OR supplier_name = ‘Oracle’
OR supplier_name = ‘Flowers Foods’;

As you can see, using the IN condition makes the statement easier to read and more efficient than
using multiple OR conditions.

Example – Using the IN Condition with Numeric Values

Next, let’s look at how to use the IN condition with numeric values.

In this example, we have a table called customers with the following data:

customer_id last_name first_name favorite_website

4000 Jackson Joe [Link]

5000 Smith Jane [Link]

6000 Ferguson Samantha [Link]

7000 Reynolds Allen [Link]

8000 Anderson Paige NULL

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 47

customer_id last_name first_name favorite_website

9000 Johnson Derek [Link]

Enter the following SQL statement:

SELECT *
FROM customers
WHERE customer_id IN (5000, 7000, 8000, 9000);

There will be 4 records selected. These are the results that you should see:

customer_id last_name first_name favorite_website

5000 Smith Jane [Link]

7000 Reynolds Allen [Link]

8000 Anderson Paige NULL

9000 Johnson Derek [Link]

This example would return all records from the customers table where the customer_id is either
5000, 7000, 8000 or 9000.

It is equivalent to the following SQL statement:

SELECT *
FROM customers
WHERE customer_id = 5000
OR customer_id = 7000
OR customer_id = 8000
OR customer_id = 9000;

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 48
Example – Using the IN Condition with the NOT Operator

Finally, let’s look at how to use the IN condition with the NOT operator. The NOT operator is
used to negate a condition. When we use the NOT operator with the IN condition, we create a
NOT IN condition. This will test to see if an expression is not in a list.

In this example, we have a table called products with the following data:

product_id product_name category_id

1 Pear 50

2 Banana 50

3 Orange 50

4 Apple 50

5 Bread 75

6 Sliced Ham 25

7 Kleenex NULL

Enter the following SQL statement:

SELECT *
FROM products
WHERE product_name NOT IN (‘Pear’, ‘Banana’, ‘Bread’);

There will be 4 records selected. These are the results that you should see:

product_id product_name category_id

3 Orange 50

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 49

product_id product_name category_id

4 Apple 50

6 Sliced Ham 25

7 Kleenex NULL

This example would return all rows from the products table where the product_name is not Pear,
Banana or Bread. Sometimes, it is more efficient to list the values that you do not want, as
opposed to the values that you do want.

It is equivalent to the following SQL statement:

SELECT *
FROM products
WHERE product_name <> ‘Pear’
AND product_name <> ‘Banana’
AND product_name <> ‘Bread’;

As you can see, the equivalent statement is written using AND conditions instead of OR
conditions because the IN condition is negated.

SQL: NOT Condition

The SQL NOT condition (sometimes called the NOT Operator) is used to negate a condition in
the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the NOT condition in SQL is:

NOT condition

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 50
Parameters or Arguments
condition

This is the condition to negate. The opposite of the condition be must be met for the
record to be included in the result set.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to
populate the data. Then try the examples in your own database!

Example – Using NOT with the IN Condition

Let’s start by looking at how to use NOT with the IN condition. When we use the NOT operator
with the IN condition, we create a NOT IN condition. This will test to see if an expression is not
in a list.

In this example, we have a table called products with the following data:

product_id product_name category_id

1 Pear 50

2 Banana 50

3 Orange 50

4 Apple 50

5 Bread 75

6 Sliced Ham 25

7 Kleenex NULL

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 51

Enter the following SQL statement:

SELECT *
FROM products
WHERE product_name NOT IN (‘Pear’, ‘Banana’, ‘Bread’);

There will be 4 records selected. These are the results that you should see:

product_id product_name category_id

3 Orange 50

4 Apple 50

6 Sliced Ham 25

7 Kleenex NULL

This example would return all rows from the products table where the product_name is not Pear,
Banana or Bread. Sometimes, it is more efficient to list the values that you do not want, as
opposed to the values that you do want.

It is equivalent to the following SQL statement:

SELECT *
FROM products
WHERE product_name <> ‘Pear’
AND product_name <> ‘Banana’
AND product_name <> ‘Bread’;

Example – Using NOT with the IS NULL Condition

When you combine the NOT operator with the IS NULL condition, you create an IS NOT NULL
condition that allows you to test for a non-NULL value. This is the recommended comparison

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 52

operator to use in SQL when testing for non-NULL values. Let’s look at an example that shows
how to use the IS NOT NULL condition in a query.

Using the same products as the previous example:

product_id product_name category_id

1 Pear 50

2 Banana 50

3 Orange 50

4 Apple 50

5 Bread 75

6 Sliced Ham 25

7 Kleenex NULL

Enter the following SQL statement:

SELECT *
FROM products
WHERE category_id IS NOT NULL;

There will be 6 records selected. These are the results that you should see:

product_id product_name category_id

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 53

product_id product_name category_id

1 Pear 50

2 Banana 50

3 Orange 50

4 Apple 50

5 Bread 75

6 Sliced Ham 25

This example will return all records from the products table where the customer_id does not
contain a NULL value.

Example – Using NOT with the LIKE Condition

Next, let’s look at an example of how to use the NOT operator with the LIKE condition.

In this example, we have a table called suppliers with the following data:

supplier_id supplier_name city state

100 Microsoft Redmond Washington

200 Google Mountain View California

300 Oracle Redwood City California

400 Kimberly-Clark Irving Texas

500 Tyson Foods Springdale Arkansas

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 54

supplier_id supplier_name city state

600 SC Johnson Racine Wisconsin

700 Dole Food Company Westlake Village California

800 Flowers Foods Thomasville Georgia

900 Electronic Arts Redwood City California

Let’s look for all records in the suppliers table where the supplier_name does not contain the
letter ‘o’. Enter the following SQL statement:

SELECT *
FROM suppliers
WHERE supplier_name NOT LIKE ‘%o%’;

There will be 1 record selected. These are the results that you should see:

supplier_id supplier_name city state

400 Kimberly-Clark Irving Texas

In this example, there is only one record in the suppliers table where the supplier_name does not
contain the letter ‘o’.

Example – Using NOT with the BETWEEN Condition

The NOT operator can also be combined with the BETWEEN condition to create a NOT
BETWEEN condition. Let’s explore an example that shows how to use the NOT BETWEEN
condition in a query.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 55

In this example, we have a table called customers with the following data:

customer_id last_name first_name favorite_website

4000 Jackson Joe [Link]

5000 Smith Jane [Link]

6000 Ferguson Samantha [Link]

7000 Reynolds Allen [Link]

8000 Anderson Paige NULL

9000 Johnson Derek [Link]

Enter the following SQL statement:

SELECT *
FROM customers
WHERE customer_id NOT BETWEEN 5000 AND 8000;

There will be 2 records selected. These are the results that you should see:

customer_id last_name first_name favorite_website

4000 Jackson Joe [Link]

9000 Johnson Derek [Link]

This would return all rows where the customer_id was NOT between 5000 and 8000, inclusive.
It would be equivalent to the following SELECT statement:

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 56

SELECT *
FROM customers
WHERE customer_id < 5000
OR customer_id > 8000;

Example – Using NOT with the EXISTS Condition

Finally, the NOT condition can be combined with the EXISTS condition to create a NOT
EXISTS condition. Let’s look at an example that shows how to use the NOT EXISTS condition
in SQL.

In this example, we have a table called customers with the following data:

customer_id last_name first_name favorite_website

4000 Jackson Joe [Link]

5000 Smith Jane [Link]

6000 Ferguson Samantha [Link]

7000 Reynolds Allen [Link]

8000 Anderson Paige NULL

9000 Johnson Derek [Link]

And a table called orders with the following data:

order_id customer_id order_date

1 7000 2016/04/18

2 5000 2016/04/18

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 57

order_id customer_id order_date

3 8000 2016/04/19

4 4000 2016/04/20

5 NULL 2016/05/01

Enter the following SQL statement:

SELECT *
FROM customers
WHERE NOT EXISTS
(SELECT *
FROM orders
WHERE customers.customer_id = orders.customer_id);

There will be 2 records selected. These are the results that you should see:

customer_id last_name first_name favorite_website

6000 Ferguson Samantha [Link]

9000 Johnson Derek [Link]

This example would return all records from the customers table where there are no records in
the orders table for the givencustomer_id.

SQL: BETWEEN Condition

The SQL BETWEEN condition allows you to easily test if an expression is within a range of
values (inclusive). It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 58

Syntax

The syntax for the BETWEEN condition in SQL is:

expression BETWEEN value1 AND value2;

Parameters or Arguments
expression

A column or calculation.

Value1 and value2

These values create an inclusive range that expression is compared to.

Note

 The SQL BETWEEN Condition will return the records where expression is within the
range of value1 and value2(inclusive).

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to
populate the data. Then try the examples in your own database!

Example – Using BETWEEN Condition with Numeric Values

Let’s look at an example of how to use the BETWEEN condition to retrieve values within a
numeric range.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 59

In this example, we have a table called suppliers with the following data:

supplier_id supplier_name city state

100 Microsoft Redmond Washington

200 Google Mountain View California

300 Oracle Redwood City California

400 Kimberly-Clark Irving Texas

500 Tyson Foods Springdale Arkansas

600 SC Johnson Racine Wisconsin

700 Dole Food Company Westlake Village California

800 Flowers Foods Thomasville Georgia

900 Electronic Arts Redwood City California

Enter the following SELECT statement:

SELECT *
FROM suppliers
WHERE supplier_id BETWEEN 300 AND 600;

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 60

There will be 4 records selected. These are the results that you should see:

supplier_id supplier_name city state

300 Oracle Redwood City California

400 Kimberly-Clark Irving Texas

500 Tyson Foods Springdale Arkansas

600 SC Johnson Racine Wisconsin

This example would return all rows from the suppliers table where the supplier_id is between
300 and 600 (inclusive). It is equivalent to the following SELECT statement:

SELECT *
FROM suppliers
WHERE supplier_id >= 300
AND supplier_id <= 600;

Example – Using BETWEEN Condition with Date Values

Dates can be somewhat tricky in SQL and how you use the BETWEEN condition with dates
depends on the database you are running (ie: Oracle, SQL Server, MySQL, etc). We will show
you an example for each of the major database technologies. So let’s get started.

In this example, we have a table called orders with the following data:

order_id customer_id order_date

1 7000 2016/04/18

2 5000 2016/04/18

3 8000 2016/04/19

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 61

order_id customer_id order_date

4 4000 2016/04/20

5 NULL 2016/05/01

Enter one of the following SQL statements, depending on the database you are running.

For SQL Server, PostgreSQL and SQLite:

SELECT *
FROM orders
WHERE order_date BETWEEN ‘2016/04/19’ AND ‘2016/05/01’;

For Oracle (use the TO_DATE function):

SELECT *
FROM orders
WHERE order_date BETWEEN TO_DATE (‘2016/04/19’, ‘yyyy/mm/dd’)
AND TO_DATE (‘2016/05/01’, ‘yyyy/mm/dd’);

For MySQL and MariaDB (use the CAST function):

SELECT *
FROM orders
WHERE order_date BETWEEN CAST(‘2016/04/19’ AS DATE) AND CAST(‘2016/05/01’ AS
DATE);

There will be 3 records selected. These are the results that you should see:

order_id customer_id order_date

3 8000 2016/04/19

4 4000 2016/04/20

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 62

order_id customer_id order_date

5 NULL 2016/05/01

This example would return all records from the orders table where the order_date is between
Apr 19, 2016 and May 1, 2016 (inclusive).

Example – Using NOT Operator with the BETWEEN Condition

The BETWEEN condition can be used with the NOT operator to create a NOT BETWEEN
condition. Let’s explore an example that shows how to use the NOT BETWEEN condition in a
query.

In this example, we have a table called customers with the following data:

customer_id last_name first_name favorite_website

4000 Jackson Joe [Link]

5000 Smith Jane [Link]

6000 Ferguson Samantha [Link]

7000 Reynolds Allen [Link]

8000 Anderson Paige NULL

9000 Johnson Derek [Link]

Enter the following SQL statement:

SELECT *
FROM customers
WHERE customer_id NOT BETWEEN 5000 AND 8000;

There will be 2 records selected. These are the results that you should see:

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 63

customer_id last_name first_name favorite_website

4000 Jackson Joe [Link]

9000 Johnson Derek [Link]

This would return all rows where the customer_id was NOT between 5000 and 8000, inclusive.
It would be equivalent to the following SELECT statement:

SELECT *
FROM customers
WHERE customer_id < 5000
OR customer_id > 8000;

SQL: IS NULL Condition

The IS NULL condition is used in SQL to test for a NULL value. It returns TRUE if a NULL
value is found, otherwise it returns FALSE. It can be used in a SELECT, INSERT, UPDATE, or
DELETE statement.

Syntax

The syntax for the IS NULL condition in SQL is:

expression IS NULL

Parameters or Arguments
expression

The expression to test for a NULL value.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to
populate the data. Then try the examples in your own database!

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 64
Example – Using IS NULL with the SELECT Statement

When testing for a NULL value, IS NULL is the recommended comparison operator to use in
SQL. Let’s start by looking at an example that shows how to use the IS NULL condition in
a SELECT statement.

In this example, we have a table called customers with the following data:

customer_id last_name first_name favorite_website

4000 Jackson Joe [Link]

5000 Smith Jane [Link]

6000 Ferguson Samantha [Link]

7000 Reynolds Allen [Link]

8000 Anderson Paige NULL

9000 Johnson Derek [Link]

Enter the following SQL statement:

SELECT *
FROM customers
WHERE favorite_website IS NULL;

There will be 1 record selected. These are the results that you should see:

customer_id last_name first_name favorite_website

8000 Anderson Paige NULL

This example will return all records from the customers table where
the favorite_website contains a NULL value.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 65
Example – Using IS NULL with the UPDATE Statement

Next, let’s look at an example of how to use the IS NULL condition in an UPDATE statement.

In this example, we have a table called products with the following data:

product_id product_name category_id

1 Pear 50

2 Banana 50

3 Orange 50

4 Apple 50

5 Bread 75

6 Sliced Ham 25

7 Kleenex NULL

Enter the following UPDATE statement:

UPDATE products
SET category_id = 100
WHERE category_id IS NULL;

There will be 1 record updated. Select the data from the products table again:

SELECT * FROM products;

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 66

These are the results that you should see:

product_id product_name category_id

1 Pear 50

2 Banana 50

3 Orange 50

4 Apple 50

5 Bread 75

6 Sliced Ham 25

7 Kleenex 100

This example will update all category_id values in the products table to 100 where
the category_id contains a NULL value. As you can see, the category_id in the last row has been
updated to 100.

Example – Using IS NULL with the DELETE Statement

Next, let’s look at an example of how to use the IS NULL condition in a DELETE statement.

In this example, we have a table called orders with the following data:

order_id customer_id order_date

1 7000 2016/04/18

2 5000 2016/04/18

3 8000 2016/04/19

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 67

order_id customer_id order_date

4 4000 2016/04/20

5 NULL 2016/05/01

Enter the following DELETE statement:

DELETE FROM orders

WHERE customer_id IS NULL;

There will be 1 record deleted. Select the data from the orders table again:

SELECT * FROM orders;

These are the results that you should see:

order_id customer_id order_date

1 7000 2016/04/18

2 5000 2016/04/18

3 8000 2016/04/19

4 4000 2016/04/20

This example will delete all records from the orders table where the customer_id contains a
NULL value. As you can see, it deleted the record for order_id=5.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 68
IS NOT NULL Condition
The Oracle IS NOT NULL condition is used to test for a NOT NULL value. You can use the
Oracle IS NOT NULL condition in either a SQL statement or in a block of PLSQL code.

Syntax
The syntax for the IS NOT NULL condition in Oracle/PLSQL is:

expression IS NOT NULL

Parameters or Arguments
expression

The value to test whether it is a not null value.

Note

 If expression is NOT a NULL value, the condition evaluates to TRUE.

 If expression is a NULL value, the condition evaluates to FALSE.

Example – With SELECT Statement

Here is an example of how to use the Oracle IS NOT NULL condition in a SELECT statement:

SELECT *
FROM customers
WHERE customer_name IS NOT NULL;

This Oracle IS NOT NULL example will return all records from the customers table where
the customer_name does not contain a null value.

Example – With INSERT Statement

Here is an example of how to use the Oracle IS NOT NULL condition in an INSERT statement:

INSERT INTO suppliers

(supplier_id, supplier_name)
SELECT account_no, name

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 69
FROM customers
WHERE account_no IS NOT NULL;

This Oracle IS NOT NULL example will insert records into the suppliers table where
the account_no does not contain a null value in the customers table.

Example – With UPDATE Statement

Here is an example of how to use the Oracle IS NOT NULL condition in an UPDATE statement:

UPDATE customers
SET status = ‘Active’
WHERE customer_name IS NOT NULL;

This Oracle IS NOT NULL example will update records in the customers table where
the customer_name does not contain a null value.

Example – With DELETE Statement

Here is an example of how to use the Oracle IS NOT NULL condition in a DELETE statement:

DELETE FROM customers

WHERE status IS NOT NULL;

This Oracle IS NOT NULL example will delete all records from the customers table where
the status does not contain a null value.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 70
LIKE Condition
The Oracle LIKE condition allows wildcards to be used in the WHERE clause of
a SELECT, INSERT, UPDATE, or DELETE statement. This allows you to perform pattern
matching.

Syntax
The syntax for the LIKE condition in Oracle/PLSQL is:

expression LIKE pattern [ ESCAPE ‘escape_character’ ]

Parameters or Arguments
expression

A character expression such as a column or field.

Pattern

A character expression that contains pattern matching. The patterns that you can choose
from are:

Wildcard Explanation

% Allows you to match any string of any length (including zero length)

_ Allows you to match on a single character

escape_character

Optional. It allows you to test for literal instances of a wildcard character such as % or _.

Example – Using % wildcard (percent sign wildcard)

The first Oracle LIKE example that we will look at involves using the % wildcard (percent sign
wildcard).
Let’s explain how the % wildcard works in the Oracle LIKE condition. We want to find all of the
customers whose last_name begins with ‘Ap’.

SELECT last_name

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 71
FROM customers
WHERE last_name LIKE ‘Ap%’;

You can also using the % wildcard multiple times within the same string. For example,

SELECT last_name
FROM customers
WHERE last_name LIKE ‘%er%’;

In this Oracle LIKE condition example, we are looking for

all customers whose last_name contains the characters ‘er’.

Example – Using _ wildcard (underscore wildcard)

Next, let’s explain how the _ wildcard (underscore wildcard) works in the Oracle LIKE
condition. Remember that _ wildcard is looking for only one character.
For example:

SELECT supplier_name
FROM suppliers
WHERE supplier_name LIKE ‘Sm_th’;

This Oracle LIKE condition example would return all suppliers whose supplier_name is 5
characters long, where the first two characters is ‘Sm’ and the last two characters is ‘th’. For
example, it could return suppliers whose supplier_name is ‘Smith’, ‘Smyth’, ‘Smath’, ‘Smeth’,
etc.
Here is another example:

SELECT *
FROM suppliers
WHERE account_number LIKE ‘92314_’;

You might find that you are looking for an account number, but you only have 5 of the 6 digits.
The example above, would retrieve potentially 10 records back (where the missing value could
equal anything from 0 to 9). For example, it could return suppliers whose account numbers are:
923140, 923141, 923142, 923143, 923144, 923145, 923146, 923147, 923148, 923149

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 72
Example – Using NOT Operator
Next, let’s look at how you would use the Oracle NOT Operator with wildcards.
Let’s use the % wilcard with the NOT Operator. You could also use the Oracle LIKE condition
to find suppliers whose name does not start with ‘T’.
For example:

SELECT supplier_name
FROM suppliers
WHERE supplier_name NOT LIKE ‘W%’;

By placing the NOT Operator in front of the Oracle LIKE condition, you are able to retrieve all
suppliers whose supplier_name does not start with ‘W’.

Example – Using Escape Characters

It is important to understand how to “Escape Characters” when pattern matching. These
examples deal specifically with escaping characters in Oracle.
Let’s say you wanted to search for a % or a _ character in the Oracle LIKE condition. You can
do this using an Escape character.
Please note that you can only define an escape character as a single character (length of 1).
For example:

SELECT *
FROM suppliers
WHERE supplier_name LIKE ‘Water!%’ ESCAPE ‘!’;

This Oracle LIKE condition example identifies the ! character as an escape character. This
statement will return all suppliers whose name is Water%.
Here is another more complicated example using escape characters in the Oracle LIKE
condition.

SELECT *
FROM suppliers
WHERE supplier_name LIKE ‘H%!%’ ESCAPE ‘!’;

This Oracle LIKE condition example returns all suppliers whose name starts with H and ends in
%. For example, it would return a value such as ‘Hello%’.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 73
You can also use the escape character with the _ character in the Oracle LIKE condition.
For example:

SELECT *
FROM suppliers
WHERE supplier_name LIKE ‘H%!_’ ESCAPE ‘!’;

This Oracle LIKE condition example returns all suppliers whose name starts with H and ends in
_. For example, it would return a value such as ‘Hello_’.

Practice Exercise #1:

Based on the employees table populated with the following data, find all records
whose employee_name ends with the letter “h”.

CREATE TABLE employees

( employee_number number(10) not null,
employee_name varchar2(50) not null,
salary number(6),
CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

INSERT INTO employees (employee_number, employee_name, salary)

VALUES (1001, ‘John Smith’, 62000);

INSERT INTO employees (employee_number, employee_name, salary)

VALUES (1002, ‘Jane Anderson’, 57500);

INSERT INTO employees (employee_number, employee_name, salary)

VALUES (1003, ‘Brad Everest’, 71000);

INSERT INTO employees (employee_number, employee_name, salary)

VALUES (1004, ‘Jack Horvath’, 42000);

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 74
Solution for Practice Exercise #1:
The following SELECT statement uses the Oracle LIKE condition to return the records
whose employee_name ends with the letter “h”.

SELECT *
FROM employees
WHERE employee_name LIKE ‘%h’;

It would return the following result set:

EMPLOYEE_NUMBER EMPLOYEE_NAME SALARY

1001 John Smith 62000

1004 Jack Horvath 42000

Practice Exercise #2:

Based on the employees table populated with the following data, find all records
whose employee_name contains the letter “s”.

CREATE TABLE employees

( employee_number number(10) not null,
employee_name varchar2(50) not null,
salary number(6),
CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

INSERT INTO employees (employee_number, employee_name, salary)

VALUES (1001, ‘John Smith’, 62000);

INSERT INTO employees (employee_number, employee_name, salary)

VALUES (1002, ‘Jane Anderson’, 57500);

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 75
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1003, ‘Brad Everest’, 71000);

INSERT INTO employees (employee_number, employee_name, salary)

VALUES (1004, ‘Jack Horvath’, 42000);

Solution for Practice Exercise #2:

The following Oracle SELECT statement would use the Oracle LIKE condition to return the
records whose employee_name contains the letter “s”.

SELECT *
FROM employees
WHERE employee_name LIKE ‘%s%’;

It would return the following result set:

EMPLOYEE_NUMBER EMPLOYEE_NAME SALARY

1002 Jane Anderson 57500

1003 Brad Everest 71000

Practice Exercise #3:

Based on the suppliers table populated with the following data, find all records
whose supplier_id is 4 digits and starts with “500”.

CREATE TABLE suppliers

( supplier_id varchar2(10) not null,
supplier_name varchar2(50) not null,
city varchar2(50),
CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 76
INSERT INTO suppliers(supplier_id, supplier_name, city)
VALUES (‘5008’, ‘Microsoft’, ‘New York’);

INSERT INTO suppliers (supplier_id, supplier_name, city)

VALUES (‘5009’, ‘IBM’, ‘Chicago’);

INSERT INTO suppliers (supplier_id, supplier_name, city)

VALUES (‘5010’, ‘Red Hat’, ‘Detroit’);

INSERT INTO suppliers (supplier_id, supplier_name, city)

VALUES (‘5011’, ‘NVIDIA’, ‘New York’);

Solution for Practice Exercise #3:

The following Oracle SELECT statement would use the Oracle LIKE condition to return the
records whose supplier_id is 4 digits and starts with “500”.

SELECT *
FROM suppliers
WHERE supplier_id LIKE ‘500_’;

It would return the following result set:

SUPPLIER_ID SUPPLIER_NAME CITY

5008 Microsoft New York

5009 IBM Chicago

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 77

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 78
XI. Order by

ORDER BY Clause
The SQL ORDER BY clause is used to sort the records in the result set for a SELECT statement.

Syntax
The syntax for the ORDER BY clause in SQL is:

SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ ASC | DESC ];

Parameters or Arguments
expressions

The columns or calculations that you wish to retrieve.

Tables

The tables that you wish to retrieve records from. There must be at least one table listed
in the FROM clause.

WHERE conditions

Optional. Conditions that must be met for the records to be selected.

ASC

Optional. ASC sorts the result set in ascending order by expression. This is the default
behavior, if no modifier is provider.

DESC

Optional. DESC sorts the result set in descending order by expression.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 79
Note

 If the ASC or DESC modifier is not provided in the ORDER BY clause, the results will
be sorted by expression in ascending order. This is equivalent to ORDER
BY expression ASC.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to
populate the data. Then try the examples in your own database!

Example – Sorting Results in Ascending Order

To sort your results in ascending order, you can specify the ASC attribute. If no value (ASC or
DESC) is provided after a field in the ORDER BY clause, the sort order will default to ascending
order. Let’s explore this further.
In this example, we have a table called customers with the following data:

customer_id last_name first_name favorite_website

4000 Jackson Joe [Link]

5000 Smith Jane [Link]

6000 Ferguson Samantha [Link]

7000 Reynolds Allen [Link]

8000 Anderson Paige NULL

9000 Johnson Derek [Link]

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 80
Enter the following SQL statement:

SELECT *
FROM customers
ORDER BY last_name;

There will be 6 records selected. These are the results that you should see:

customer_id last_name first_name favorite_website

8000 Anderson Paige NULL

6000 Ferguson Samantha [Link]

4000 Jackson Joe [Link]

9000 Johnson Derek [Link]

7000 Reynolds Allen [Link]

5000 Smith Jane [Link]

This example would return all records from the customers sorted by the last_name field in
ascending order and would be equivalent to the following SQL ORDER BY clause:

SELECT *
FROM customers
ORDER BY last_name ASC;

Most programmers omit the ASC attribute if sorting in ascending order.

Example – Sorting Results in descending order

When sorting your result set in descending order, you use the DESC attribute in your ORDER
BY clause. Let’s take a closer look.
In this example, we have a table called suppliers with the following data:

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 81

supplier_id supplier_name city state

100 Microsoft Redmond Washington

200 Google Mountain View California

300 Oracle Redwood City California

400 Kimberly-Clark Irving Texas

500 Tyson Foods Springdale Arkansas

600 SC Johnson Racine Wisconsin

700 Dole Food Company Westlake Village California

800 Flowers Foods Thomasville Georgia

900 Electronic Arts Redwood City California

Enter the following SQL statement:

SELECT *
FROM suppliers
WHERE supplier_id > 400
ORDER BY supplier_id DESC;

There will be 5 records selected. These are the results that you should see:

supplier_id supplier_name city state

900 Electronic Arts Redwood City California

800 Flowers Foods Thomasville Georgia

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 82

supplier_id supplier_name city state

700 Dole Food Company Westlake Village California

600 SC Johnson Racine Wisconsin

500 Tyson Foods Springdale Arkansas

This example would sort the result set by the supplier_id field in descending order.

Example – Sorting Results by relative position

You can also use the SQL ORDER BY clause to sort by relative position in the result set, where
the first field in the result set is 1, the second field is 2, the third field is 3, and so on.
In this example, we have a table called products with the following data:

product_id product_name category_id

1 Pear 50

2 Banana 50

3 Orange 50

4 Apple 50

5 Bread 75

6 Sliced Ham 25

7 Kleenex NULL

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 83
Now enter the following SQL statement:

SELECT product_id, product_name

FROM products
WHERE product_name <> ‘Bread’
ORDER BY 1 DESC;

There will be 6 records selected. These are the results that you should see:

product_id product_name

7 Kleenex

6 Sliced Ham

4 Apple

3 Orange

2 Banana

1 Pear

This example would sort the results by the product_id field in descending order, since
the product_id field is in position #1 in the result set and would be equivalent to the following
SQL ORDER BY clause:

SELECT product_id, product_name

FROM products
WHERE product_name <> ‘Bread’
ORDER BY product_id DESC;

Example – Using both ASC and DESC attributes

When sorting your result set using the SQL ORDER BY clause, you can use the ASC and DESC
attributes in a single SELECT statement.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 84
In this example, let’s use the same products table as the previous example:

product_id product_name category_id

1 Pear 50

2 Banana 50

3 Orange 50

4 Apple 50

5 Bread 75

6 Sliced Ham 25

7 Kleenex NULL

Now enter the following SQL statement:

SELECT *
FROM products
WHERE product_id <> 7
ORDER BY category_id DESC, product_name ASC;

There will be 6 records selected. These are the results that you should see:

product_id product_name category_id

5 Bread 75

4 Apple 50

2 Banana 50

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 85

product_id product_name category_id

3 Orange 50

1 Pear 50

6 Sliced Ham 25

This example would return the records sorted by the category_id field in descending order, with
a secondary sort by product_name in ascending order.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 86

XII. Group by

SQL: GROUP BY Clause

The SQL GROUP BY clause can be used in a SELECT statement to collect data across multiple
records and group the results by one or more columns.

Syntax
The syntax for the GROUP BY clause in SQL is:

SELECT expression1, expression2, … expression_n,

aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, … expression_n
[ORDER BY expression [ ASC | DESC ]];

Parameters or Arguments
expression1, expression2, … expression_n

Expressions that are not encapsulated within an aggregate function and must be included
in the GROUP BY Clause at the end of the SQL statement.

Aggregate_function

This is an aggregate function such as the SUM, COUNT, MIN, MAX, or AVG functions.

Aggregate_expression

This is the column or expression that the aggregate_function will be used on.

Tables

The tables that you wish to retrieve records from. There must be at least one table listed
in the FROM clause.

WHERE conditions

Optional. These are conditions that must be met for the records to be selected.

ORDER BY expression

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 87
Optional. The expression used to sort the records in the result set. If more than one
expression is provided, the values should be comma separated.

ASC

Optional. ASC sorts the result set in ascending order by expression. This is the default
behavior, if no modifier is provider.

DESC

Optional. DESC sorts the result set in descending order by expression.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to
populate the data. Then try the examples in your own database!

Example – Using GROUP BY with the SUM Function

Let’s look at how to use the GROUP BY clause with the SUM function in SQL.
In this example, we have a table called employees with the following data:
employee_number last_name first_name salary dept_id

1001 Smith John 62000 500

1002 Anderson Jane 57500 500

1003 Everest Brad 71000 501

1004 Horvath Jack 42000 501

Enter the following SQL statement:

SELECT dept_id, SUM(salary) AS total_salaries

FROM employees
GROUP BY dept_id;

There will be 2 records selected. These are the results that you should see:

dept_id total_salaries

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 88

dept_id total_salaries

500 119500

501 113000

In this example, we’ve used the SUM function to add up all of the salaries for each dept_id and
we’ve aliased the results of the SUM function as total_salaries. Because the dept_id is not
encapsulated in the SUM function, it must be listed in the GROUP BY clause.

Example – Using GROUP BY with the COUNT function

Let’s look at how to use the GROUP BY clause with the COUNT function in SQL.
In this example, we have a table called products with the following data:
product_id product_name category_id

1 Pear 50

2 Banana 50

3 Orange 50

4 Apple 50

5 Bread 75

6 Sliced Ham 25

7 Kleenex NULL

Enter the following SQL statement:

SELECT category_id, COUNT(*) AS total_products

FROM products
WHERE category_id IS NOT NULL
GROUP BY category_id
ORDER BY category_id;

There will be 3 records selected. These are the results that you should see:

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 89

category_id total_products

25 1

50 4

75 1

In this example, we’ve used the COUNT function to calculate the number of products for
each category_id and we’ve aliased the results of the COUNT function as total_products. We’ve
excluded any category_id values that are NULL by filtering them out in the WHERE clause.
Because the category_id is not encapsulated in the COUNT function, it must be listed in the
GROUP BY clause.

Example – Using GROUP BY with the MIN function

Let’s next look at how to use the GROUP BY clause with the MIN function in SQL.
In this example, we will use the employees table again that is populated the following data:
employee_number last_name first_name salary dept_id

1001 Smith John 62000 500

1002 Anderson Jane 57500 500

1003 Everest Brad 71000 501

1004 Horvath Jack 42000 501

Enter the following SQL statement:

SELECT dept_id, MIN(salary) AS lowest_salary

FROM employees
GROUP BY dept_id;

There will be 2 records selected. These are the results that you should see:

dept_id lowest_salary

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 90

dept_id lowest_salary

500 57500

501 42000

In this example, we’ve used the MIN function to return the lowest salary for each dept_id and
we’ve aliased the results of the MIN function as lowest_salary. Because the dept_id is not
encapsulated in the MIN function, it must be listed in the GROUP BY clause.

Example – Using GROUP BY with the MAX function

Finally, let’s look at how to use the GROUP BY clause with the MAX function.
Let’s use the employees table again, but this time find the highest salary for each dept_id:
employee_number last_name first_name salary dept_id

1001 Smith John 62000 500

1002 Anderson Jane 57500 500

1003 Everest Brad 71000 501

1004 Horvath Jack 42000 501

Enter the following SQL statement:

SELECT dept_id, MAX(salary) AS highest_salary

FROM employees
GROUP BY dept_id;

There will be 2 records selected. These are the results that you should see:
dept_id highest_salary

500 62000

501 71000

In this example, we’ve used the MAX function to return the highest salary for each dept_id and
we’ve aliased the results of the MAX function as highest_salary. The dept_id column must be
listed in the GROUP BY clause because it is not encapsulated in the MAX function.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 91
XIII. Group Functions

SQL: AVG Function

The SQL AVG function is used to return the average of an expression in a SELECT statement.

Syntax
The syntax for the AVG function in SQL is:

SELECT AVG(aggregate_expression)
FROM tables
[WHERE conditions];

OR the syntax for the AVG function when grouping the results by one or more columns is:

SELECT expression1, expression2, … expression_n,

AVG(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, … expression_n;

Parameters or Arguments
expression1, expression2, … expression_n

Expressions that are not encapsulated within the AVG function and must be included in
the GROUP BY clause at the end of the SQL statement.

Aggregate_expression

This is the column or expression that will be averaged.

Tables

The tables that you wish to retrieve records from. There must be at least one table listed
in the FROM clause.

WHERE conditions

Optional. These are conditions that must be met for the records to be selected.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 92
Example – With Single Expression
For example, you might wish to know how the average cost of all products that are in the
Clothing category.

SELECT AVG(cost) AS “Average Cost”

FROM products
WHERE category = ‘Clothing’;

In this SQL AVG Function example, we’ve aliased the AVG(cost) expression as “Average
Cost”. As a result, “Average Cost” will display as the field name when the result set is returned.

Example – Using SQL DISTINCT

You can use the SQL DISTINCT clause within the AVG function. For example, the SELECT
statement below returns the combined average cost of unique cost values where the category is
Clothing.

SELECT AVG(DISTINCT cost) AS “Average Cost”

FROM products
WHERE category = ‘Clothing’;

If there were two cost values of $25, only one of these values would be used in the AVG
function calculation.

Example – Using Formula

The expression contained within the AVG function does not need to be a single field. You could
also use a formula. For example, you might want the average profit for a product. Average profit
is calculated as sale_price less cost.

SELECT AVG(sale_price – cost) AS “Average Profit”

FROM products;

You might also want to perform a mathematical operation within the AVG function. For
example, you might determine the average commission as 10% of sale_price.

SELECT AVG(sale_price * 0.10) AS “Average Commission”

FROM products;

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 93

Example – Using SQL GROUP BY

In some cases, you will be required to use the SQL GROUP BY clause with the AVG function.
For example, you could also use the AVG function to return the name of the department and the
average sales (in the associated department).

SELECT department, AVG(sales) AS “Average Sales”

FROM order_details
WHERE department > 10
GROUP BY department;

Because you have listed one column in your SELECT statement that is not encapsulated in the
AVG function, you must use the GROUP BY clause. The department field must, therefore, be
listed in the GROUP BY section.

SQL: COUNT Function

The SQL COUNT function is used to count the number of rows returned in a SELECT
statement.

Syntax
The syntax for the COUNT function in SQL is:

SELECT COUNT(aggregate_expression)
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];

OR the syntax for the COUNT function when grouping the results by one or more columns is:

SELECT expression1, expression2, … expression_n,

COUNT(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, … expression_n
[ORDER BY expression [ ASC | DESC ]];

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 94
Parameters or Arguments
expression1, expression2, … expression_n

Expressions that are not encapsulated within the COUNT function and must be included
in the GROUP BY clause at the end of the SQL statement.

Aggregate_expression

This is the column or expression whose non-null values will be counted.

Tables

The tables that you wish to retrieve records from. There must be at least one table listed
in the FROM clause.

WHERE conditions

Optional. These are conditions that must be met for the records to be selected.

ORDER BY expression

Optional. The expression used to sort the records in the result set. If more than one
expression is provided, the values should be comma separated.

ASC

Optional. ASC sorts the result set in ascending order by expression. This is the default
behavior, if no modifier is provider.

DESC

Optional. DESC sorts the result set in descending order by expression.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to
populate the data. Then try the examples in your own database!

Example – COUNT Function only includes NOT NULL Values

Not everyone realizes this, but the COUNT function will only count the records where
the expression is NOT NULL in COUNT(expression). When the expression is a NULL value, it
is not included in the COUNT calculations. Let’s explore this further.
In this example, we have a table called customers with the following data:

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 95

customer_id last_name first_name favorite_website

4000 Jackson Joe [Link]

5000 Smith Jane [Link]

6000 Ferguson Samantha [Link]

7000 Reynolds Allen [Link]

8000 Anderson Paige NULL

9000 Johnson Derek [Link]

Enter the following SELECT statement that uses the COUNT function:

SELECT COUNT(customer_id)
FROM customers;

There will be 1 record selected. These are the results that you should see:
COUNT(customer_id)

In this example, the query will return 6 since there are 6 records in the customers table and
all customer_id values are NOT NULL (ie: customer_id is the primary key for the table).
But what happens when we encounter a NULL value with the COUNT function? Let’s enter this
next SELECT statement that counts the favorite_website column which can contain NULL
values:

SELECT COUNT(favorite_website)
FROM customers;

There will be 1 record selected. These are the results that you should see:
COUNT(favorite_website)

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 96
This second example will return 5. Because one of the favorite_website values is NULL, it
would be excluded from the COUNT function calculation. As a result, the query will return 5
instead of 6.
TIP: Use the primary key in the COUNT function or COUNT(*) if you want to be certain that
records aren’t excluded in the calculations.

Example – Using a Single Expression in the COUNT Function

Let’s look at an example that shows how to use the COUNT function with a single expression in
a query.
In this example, we have a table called employees with the following data:
employee_number last_name first_name salary dept_id

1001 Smith John 62000 500

1002 Anderson Jane 57500 500

1003 Everest Brad 71000 501

1004 Horvath Jack 42000 501

Enter the following SQL statement:

SELECT COUNT(*) AS total

FROM employees
WHERE salary > 50000;

There will be 1 record selected. These are the results that you should see:
total

In this example, we will return the number of employees who have a salary above $50,000.
We’ve aliased the COUNT(*) as total to make our query results more readable. Now, total will
display as the column heading when the result set is returned.

Example – Using GROUP BY with the COUNT Function

In some cases, you will be required to use the GROUP BY clause with the COUNT function.
This happens when you have columns listed in the SELECT statement that are not part of the
COUNT function. Let’s explore this further.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 97
Again, using the employees table populated with the following data:
employee_number last_name first_name salary dept_id

1001 Smith John 62000 500

1002 Anderson Jane 57500 500

1003 Everest Brad 71000 501

1004 Horvath Jack 42000 501

Enter the following SQL statement:

SELECT dept_id, COUNT(*) AS total

FROM employees
WHERE salary > 50000
GROUP BY dept_id;

There will be 2 records selected. These are the results that you should see:
dept_id total

500 2

501 1

In this example, the COUNT function will return the number of employees that make over
$50,000 for each dept_id. Because the dept_id column is not included in the COUNT function, it
must be listed in the GROUP BY clause.

Example – Using DISTINCT with the COUNT Function

Did you know that you can use the DISTINCT clause within the COUNT function? This allows
you to count only the unique values.
Using the same employees table as the previous example:
employee_number last_name first_name salary dept_id

1001 Smith John 62000 500

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 98

employee_number last_name first_name salary dept_id

1002 Anderson Jane 57500 500

1003 Everest Brad 71000 501

1004 Horvath Jack 42000 501

Enter the following SQL statement:

SELECT COUNT(DISTINCT dept_id) AS total

FROM employees
WHERE salary > 50000;

There will be 1 record selected. These are the results that you should see:
total

In this example, the COUNT function will return the unique number of dept_id values that have
at least one employee that makes over $50,000.

TIP: Performance Tuning with the COUNT Function

Since the COUNT function will return the same results regardless of what NOT NULL field(s)
you include as the COUNT function parameters (ie: within the parentheses), you can
use COUNT(1) to get better performance. Now the database engine will not have to fetch any
data fields, instead it will just retrieve the integer value of 1.
For example, instead of entering this statement:

SELECT dept_id, COUNT(*) AS total

FROM employees
WHERE salary > 50000
GROUP BY dept_id;

You could replace COUNT(*) with COUNT(1) to get better performance:

SELECT dept_id, COUNT(1) AS total

FROM employees
WHERE salary > 50000

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 99
GROUP BY dept_id;

Now, the COUNT function does not need to retrieve all fields from the employees table as it had
to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each
record that meets your criteria.

SQL: MAX Function

The SQL MAX function is used to return the maximum value of an expression in a SELECT
statement.

Syntax
The syntax for the MAX function in SQL is:

SELECT MAX(aggregate_expression)
FROM tables
[WHERE conditions];

OR the syntax for the MAX function when grouping the results by one or more columns is:

SELECT expression1, expression2, … expression_n,

MAX(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, … expression_n;

Parameters or Arguments
expression1, expression2, … expression_n

Expressions that are not encapsulated within the MAX function and must be included in
the GROUP BY clause at the end of the SQL statement.

Aggregate_expression

This is the column or expression from which the maximum value will be returned.

Tables

The tables that you wish to retrieve records from. There must be at least one table listed
in the FROM clause.

WHERE conditions

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 100
Optional. These are conditions that must be met for the records to be selected.

Example – With Single Expression

The simplest way to use the SQL MAX function would be to return a single field that calculates
the MAX value.
For example, you might wish to know the maximum salary of all employees.

SELECT MAX(salary) AS “Highest salary”

FROM employees;

In this SQL MAX function example, we’ve aliased the MAX(salary) field as “Highest salary”.
As a result, “Highest salary” will display as the field name when the result set is returned.

Example – Using SQL GROUP BY Clause

In some cases, you will be required to use the SQL GROUP BY clause with the SQL MAX
function.
For example, you could also use the SQL MAX function to return the name of each department
and the maximum salary in the department.

SELECT department, MAX(salary) AS “Highest salary”

FROM employees
GROUP BY department;

Because you have listed one column in your SQL SELECT statement that is not encapsulated in
the MAX function, you must use the SQL GROUP BY clause. The department field must,
therefore, be listed in the GROUP BY section.

SQL: MIN Function

The SQL MIN function is used to return the minimum value of an expression in a SELECT
statement.

Syntax
The syntax for the MIN function in SQL is:

SELECT MIN(aggregate_expression)
FROM tables
[WHERE conditions];

OR the syntax for the MIN function when grouping the results by one or more columns is:

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 101

SELECT expression1, expression2, … expression_n,

MIN(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, … expression_n;

Parameters or Arguments
expression1, expression2, … expression_n

Expressions that are not encapsulated within the MIN function and must be included in
the GROUP BY clause at the end of the SQL statement.

Aggregate_expression

This is the column or expression from which the minimum value will be returned.

Tables

The tables that you wish to retrieve records from. There must be at least one table listed
in the FROM clause.

WHERE conditions

Optional. These are conditions that must be met for the records to be selected.

Example – With Single Expression

The simplest way to use the SQL MIN function would be to return a single field that calculates
the MIN value.
For example, you might wish to know the minimum salary of all employees.

SELECT MIN(salary) AS “Lowest salary”

FROM employees;

In this SQL MIN function example, we’ve aliased the MIN(salary) field as “Lowest salary”. As a
result, “Lowest salary” will display as the field name when the result set is returned.

Example – Using SQL GROUP BY

In some cases, you will be required to use the SQL GROUP BY clause with the SQL MIN
function.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 102
For example, you could also use the SQL MIN function to return the name of each department
and the minimum salary in the department.

SELECT department, MIN(salary) AS “Lowest salary”

FROM employees
GROUP BY department;

Because you have listed one column in your SQL SELECT statement that is not encapsulated in
the SQL MIN function, you must use the SQL GROUP BY clause. The department field must,
therefore, be listed in the GROUP BY section.

SQL: SUM Function

The SQL SUM function is used to return the sum of an expression in a SELECT statement.

Syntax
The syntax for the SUM function in SQL is:

SELECT SUM(aggregate_expression)
FROM tables
[WHERE conditions];

OR the syntax for the SUM function when grouping the results by one or more columns is:

SELECT expression1, expression2, … expression_n,

SUM(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, … expression_n;

Parameters or Arguments
expression1, expression2, … expression_n

Expressions that are not encapsulated within the SUM function and must be included in
the GROUP BY clause at the end of the SQL statement.

Aggregate_expression

This is the column or expression that will be summed.

Tables

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 103
The tables that you wish to retrieve records from. There must be at least one table listed
in the FROM clause.

WHERE conditions

Optional. These are conditions that must be met for the records to be selected.

Example – With Single Expression

For example, you might wish to know how the combined total salary of all employees whose
salary is above $25,000 / year.

SELECT SUM(salary) AS “Total Salary”

FROM employees
WHERE salary > 25000;

In this SQL SUM Function example, we’ve aliased the SUM(salary) expression as “Total
Salary”. As a result, “Total Salary” will display as the field name when the result set is returned.

Example – Using SQL DISTINCT

You can use the SQL DISTINCT clause within the SQL SUM function. For example, the SQL
SELECT statement below returns the combined total salary of unique salary values where the
salary is above $25,000 / year.

SELECT SUM(DISTINCT salary) AS “Total Salary”

FROM employees
WHERE salary > 25000;

If there were two salaries of $30,000/year, only one of these values would be used in the SQL
SUM function.

Example – Using Formula

The expression contained within the SQL SUM function does not need to be a single field. You
could also use a formula. For example, you might want the net income for a business. Net
Income is calculated as total income less total expenses.

SELECT SUM(income – expenses) AS “Net Income”

FROM gl_transactions;

You might also want to perform a mathematical operation within the SQL SUM function. For
example, you might determine total commission as 10% of total sales.

SELECT SUM(sales * 0.10) AS “Commission”

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 104
FROM order_details;

Example – Using SQL GROUP BY

In some cases, you will be required to use the SQL GROUP BY clause with the SQL SUM
function.
For example, you could also use the SQL SUM function to return the name of the department
and the total sales (in the associated department).

SELECT department, SUM(sales) AS “Total sales”

FROM order_details
GROUP BY department;

Because you have listed one column in your SQL SELECT statement that is not encapsulated in
the SQL SUM function, you must use the SQL GROUP BY clause. The department field must,
therefore, be listed in the SQL GROUP BY section.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 105
XIV. Having

SQL: HAVING Clause

This SQL tutorial explains how to use the SQL HAVING clause with syntax and examples.

Description
The SQL HAVING clause is used in combination with the GROUP BY clause to restrict the
groups of returned rows to only those whose the condition is TRUE.

Syntax
The syntax for the HAVING clause in SQL is:

SELECT expression1, expression2, … expression_n,

aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, … expression_n
HAVING condition;

Parameters or Arguments
expression1, expression2, … expression_n

Expressions that are not encapsulated within an aggregate function and must be included
in the GROUP BY Clause near the end of the SQL statement.

Aggregate_function

This is an aggregate function such as the SUM, COUNT, MIN, MAX, or AVG functions.

Aggregate_expression

This is the column or expression that the aggregate_function will be used on.

Tables

The tables that you wish to retrieve records from. There must be at least one table listed
in the FROM clause.

WHERE conditions

Optional. These are the conditions for the records to be selected.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 106
HAVING condition

This is a further condition applied only to the aggregated results to restrict the groups of
returned rows. Only those groups whose condition evaluates to TRUE will be included in
the result set.

Example – Using SUM function

Let’s look at a SQL HAVING clause example that uses the SQL SUM function.
You could also use the SQL SUM function to return the name of the department and the total
sales (in the associated department). The SQL HAVING clause will filter the results so that only
departments with sales greater than $1000 will be returned.

SELECT department, SUM(sales) AS “Total sales”

FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;

Example – Using COUNT function

Let’s look at how we could use the HAVING clause with the SQL COUNT function.
You could use the SQL COUNT function to return the name of the department and the number
of employees (in the associated department) that make over $25,000 / year. The SQL HAVING
clause will filter the results so that only departments with more than 10 employees will be
returned.

SELECT department, COUNT(*) AS “Number of employees”

FROM employees
WHERE salary > 25000
GROUP BY department
HAVING COUNT(*) > 10;

Example – Using MIN function

Let’s next look at how we could use the HAVING clause with the SQL MIN function.
You could also use the SQL MIN function to return the name of each department and the
minimum salary in the department. The SQL HAVING clause will return only those departments
where the minimum salary is greater than $35,000.

SELECT department, MIN(salary) AS “Lowest salary”

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 107
FROM employees
GROUP BY department
HAVING MIN(salary) > 35000;

Example – Using MAX function

Finally, let’s look at how we could use the HAVING clause with the SQL MAX function.

For example, you could also use the SQL MAX function to return the name of each department
and the maximum salary in the department. The SQL HAVING clause will return only those
departments whose maximum salary is less than $50,000.

SELECT department, MAX(salary) AS “Highest salary”

FROM employees
GROUP BY department
HAVING MAX(salary) < 50000;

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 108
XV. Primary Key

SQL: Primary Keys

What is a primary key in SQL?

In SQL, a primary key is a single field or combination of fields that uniquely defines a record.
None of the fields that are part of the primary key can contain a NULL value. A table can have
only one primary key.
You use either the CREATE TABLE statement or the ALTER TABLE statement to create a
primary key in SQL.

Create Primary Key (CREATE TABLE statement)

A primary key can be created when you execute a CREATE TABLE statement in SQL.

Syntax
The syntax to create a primary key using the CREATE TABLE statement in SQL is:

CREATE TABLE table_name

(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
…

CONSTRAINT constraint_name PRIMARY KEY (pk_col1, pk_col2, … pk_col_n)

);

OR

CREATE TABLE table_name

(
column1 datatype CONSTRAINT constraint_name PRIMARY KEY,
column2 datatype [ NULL | NOT NULL ],
…
);

table_name

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 109
The name of the table that you wish to create.

Column1, column2

The columns that you wish to create in the table.

Constraint_name

The name of the primary key.

Pk_col1, pk_col2, … pk_col_n

The columns that make up the primary key.

Example
Let’s look at an example of how to create a primary key using the CREATE TABLE statement
in SQL. We will start with a very simple one where our primary key consists of just one column.
For example:

CREATE TABLE suppliers

( supplier_id int NOT NULL,
supplier_name char(50) NOT NULL,
contact_name char(50),
CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

In this example, we’ve created a primary key on the suppliers table called suppliers_pk. It
consists of only one column – the supplier_id column.
We could have used the alternate syntax and created this same primary key as follows:

CREATE TABLE suppliers

( supplier_id int CONSTRAINT suppliers_pk PRIMARY KEY,
supplier_name char(50) NOT NULL,
contact_name char(50)
);

Both of these syntaxes are valid when creating a primary key with only one field.
If you create a primary key that is made up of 2 or more columns, you are limited to using only
the first syntax where the primary key is defined at the end of the CREATE TABLE statement.
For example:

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 110

CREATE TABLE contacts

( last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(25) NOT NULL,
birthday DATE,
CONSTRAINT contacts_pk PRIMARY KEY (last_name, first_name)
);

This example creates a primary key called contacts_pk that is made up of a combination of
the last_name and first_name columns. So each combination of last_name and first_name must
be unique in the contacts table.

Create Primary Key (ALTER TABLE statement)

If your table already exists and you wish to add a primary key later, you can use the ALTER
TABLE statement to create a primary key.

Syntax
The syntax to create a primary key using the ALTER TABLE statement in SQL is:

ALTER TABLE table_name

ADD CONSTRAINT constraint_name
PRIMARY KEY (column1, column2, … column_n);

table_name

The name of the table to modify. This is the table that you wish to add a primary key to.

Constraint_name

The name of the primary key.

Column1, column2, … column_n

The columns that make up the primary key.

Example
Let’s look at an example of how to create a primary key using the ALTER TABLE statement in
SQL. So say, we already have a suppliers table created in our database. We could add a primary
to the suppliers table with the following ALTER TABLE statement:

ALTER TABLE suppliers

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 111
ADD CONSTRAINT suppliers_pk
PRIMARY KEY (supplier_id);

In this example, we’ve created a primary key on the existing suppliers table called suppliers_pk.
It consists of the supplier_id column.
We could also create a primary key with more than one field as in the example below:

ALTER TABLE suppliers

ADD CONSTRAINT suppliers_pk
PRIMARY KEY (supplier_id, supplier_name);

This example would created a primary key called suppliers_pk that is made up of a combination
of the supplier_id and supplier_name columns.

Drop Primary Key

In SQL, you can drop a primary key using the ALTER TABLE statement.

Syntax
The syntax to drop a primary key in SQL is:

ALTER TABLE table_name

DROP PRIMARY KEY;

table_name

The name of the table to modify. This is the table whose primary key you wish to drop.

Example
Let’s look at an example of how to drop a primary key using the ALTER TABLE statement in
SQL.

ALTER TABLE suppliers

DROP PRIMARY KEY;

In this example, we’ve dropped the primary key on the suppliers table. We do not need to
specify the name of the primary key as there can only be one primary key on a table.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 112
XVI. Foreign Key

Foreign Keys

What is a foreign key in Oracle?

A foreign key is a way to enforce referential integrity within your Oracle database. A foreign key
means that values in one table must also appear in another table.
The referenced table is called the parent table while the table with the foreign key is called
the child table. The foreign key in the child table will generally reference a primary key in the
parent table.
A foreign key can be defined in either a CREATE TABLE statement or an ALTER TABLE
statement.

Using a CREATE TABLE statement

Syntax
The syntax for creating a foreign key using a CREATE TABLE statement is:

CREATE TABLE table_name

(
column1 datatype null/not null,
column2 datatype null/not null,
…

CONSTRAINT fk_column
FOREIGN KEY (column1, column2, … column_n)
REFERENCES parent_table (column1, column2, … column_n)
);

Example

CREATE TABLE supplier

( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 113
);

CREATE TABLE products

( product_id numeric(10) not null,
supplier_id numeric(10) not null,
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
);

In this example, we’ve created a primary key on the supplier table called supplier_pk. It consists
of only one field – the supplier_id field. Then we’ve created a foreign key called fk_supplier on
the products table that references the supplier table based on the supplier_id field.
We could also create a foreign key with more than one field as in the example below:

CREATE TABLE supplier

( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

CREATE TABLE products

( product_id numeric(10) not null,
supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
CONSTRAINT fk_supplier_comp
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name)
);

In this example, our foreign key called fk_foreign_comp references the supplier table based on
two fields – the supplier_id and supplier_name fields.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 114
Using an ALTER TABLE statement

Syntax
The syntax for creating a foreign key in an ALTER TABLE statement is:

ALTER TABLE table_name

ADD CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, … column_n)
REFERENCES parent_table (column1, column2, … column_n);

Example

ALTER TABLE products

ADD CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id);

In this example, we’ve created a foreign key called fk_supplier that references the supplier table
based on the supplier_id field.
We could also create a foreign key with more than one field as in the example below:

ALTER TABLE products

ADD CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name);

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 115
XVII. Joins

SQL: JOINS
SQL JOINS are used to retrieve data from multiple tables. A SQL JOIN is performed whenever
two or more tables are listed in a SQL statement.
There are 4 different types of SQL joins:

 SQL INNER JOIN (sometimes called simple join)

 SQL LEFT OUTER JOIN (sometimes called LEFT JOIN)
 SQL RIGHT OUTER JOIN (sometimes called RIGHT JOIN)
 SQL FULL OUTER JOIN (sometimes called FULL JOIN)

So let’s discuss SQL JOIN syntax, look at visual illustrations of SQL JOINS and explore some
examples.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to
populate the data. Then try the examples in your own database!

SQL INNER JOIN (simple join)

Chances are, you’ve already written a SQL statement that uses an SQL INNER JOIN. It is the
most common type of SQL join. SQL INNER JOINS return all rows from multiple tables where
the join condition is met.

Syntax
The syntax for the INNER JOIN in SQL is:

SELECT columns
FROM table1
INNER JOIN table2
ON [Link] = [Link];

Visual Illustration
In this visual diagram, the SQL INNER JOIN returns the shaded area:

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 116

The SQL INNER JOIN would return the records where table1 and table2 intersect.

Example
Let’s look at an example of how to use the INNER JOIN in a query.
In this example, we have a table called customers with the following data:
customer_id last_name first_name favorite_website

4000 Jackson Joe [Link]

5000 Smith Jane [Link]

6000 Ferguson Samantha [Link]

7000 Reynolds Allen [Link]

8000 Anderson Paige NULL

9000 Johnson Derek [Link]

And a table called orders with the following data:

order_id customer_id order_date

1 7000 2016/04/18

2 5000 2016/04/18

3 8000 2016/04/19

4 4000 2016/04/20

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 117

order_id customer_id order_date

5 NULL 2016/05/01

Enter the following SQL statement:

SELECT customers.customer_id, orders.order_id, orders.order_date

FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;

There will be 4 records selected. These are the results that you should see:
customer_id order_id order_date

4000 4 2016/04/20

5000 2 2016/04/18

7000 1 2016/04/18

8000 3 2016/04/19

This example would return all rows from the customers and orders tables where there is a
matching customer_id value in both the customers and orders tables.
The rows where customer_id is equal to 6000 and 9000 in the customers table would be omitted,
since they do not exist in both tables. The row where the order_id is 5 from the orders table
would be omitted, since the customer_id of NULL does not exist in the customers table.

Old Syntax
As a final note, it is worth mentioning that the INNER JOIN example above could be rewritten
using the older implicit syntax as follows (but we still recommend using the INNER JOIN
keyword syntax):

SELECT customers.customer_id, orders.order_id, orders.order_date

FROM customers, orders
WHERE customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 118
SQL LEFT OUTER JOIN
Another type of join is called a LEFT OUTER JOIN. This type of join returns all rows from the
LEFT-hand table specified in the ON condition and only those rows from the other table where
the joined fields are equal (join condition is met).

Syntax
The syntax for the LEFT OUTER JOIN in SQL is:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON [Link] = [Link];

In some databases, the OUTER keyword is omitted and written simply as LEFT JOIN.

Visual Illustration
In this visual diagram, the SQL LEFT OUTER JOIN returns the shaded area:

The SQL LEFT OUTER JOIN would return the all records from table1 and only those records
from table2 that intersect with table1.

Example
Now let’s look at an example that shows how to use the LEFT OUTER JOIN in a SELECT
statement.
Using the same customers table as the previous example:
customer_id last_name first_name favorite_website

4000 Jackson Joe [Link]

5000 Smith Jane [Link]

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 119

customer_id last_name first_name favorite_website

6000 Ferguson Samantha [Link]

7000 Reynolds Allen [Link]

8000 Anderson Paige NULL

9000 Johnson Derek [Link]

And the orders table with the following data:

order_id customer_id order_date

1 7000 2016/04/18

2 5000 2016/04/18

3 8000 2016/04/19

4 4000 2016/04/20

5 NULL 2016/05/01

Enter the following SQL statement:

SELECT customers.customer_id, orders.order_id, orders.order_date

FROM customers
LEFT OUTER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;

There will be 6 records selected. These are the results that you should see:
customer_id order_id order_date

4000 4 2016/04/20

5000 2 2016/04/18

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 120

customer_id order_id order_date

6000 NULL NULL

7000 1 2016/04/18

8000 3 2016/04/19

9000 NULL NULL

This LEFT OUTER JOIN example would return all rows from the customers table and only
those rows from the orders table where the joined fields are equal.
If a customer_id value in the customers table does not exist in the orders table, all fields in
the orders table will display as NULL in the result set. As you can see, the rows
where customer_id is 6000 and 9000 would be included with a LEFT OUTER JOIN but
the order_id and order_date fields display NULL.

SQL RIGHT OUTER JOIN

Another type of join is called a SQL RIGHT OUTER JOIN. This type of join returns all rows
from the RIGHT-hand table specified in the ON condition and only those rows from the other
table where the joined fields are equal (join condition is met).

Syntax
The syntax for the RIGHT OUTER JOIN in SQL is:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON [Link] = [Link];

In some databases, the OUTER keyword is omitted and written simply as RIGHT JOIN.

Visual Illustration
In this visual diagram, the SQL RIGHT OUTER JOIN returns the shaded area:

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 121

The SQL RIGHT OUTER JOIN would return the all records from table2 and only those records
from table1 that intersect with table2.

Example
Now let’s look at an example that shows how to use the RIGHT OUTER JOIN in a SELECT
statement.
Using the same customers table as the previous example:
customer_id last_name first_name favorite_website

4000 Jackson Joe [Link]

5000 Smith Jane [Link]

6000 Ferguson Samantha [Link]

7000 Reynolds Allen [Link]

8000 Anderson Paige NULL

9000 Johnson Derek [Link]

And the orders table with the following data:

order_id customer_id order_date

1 7000 2016/04/18

2 5000 2016/04/18

3 8000 2016/04/19

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 122

order_id customer_id order_date

4 4000 2016/04/20

5 NULL 2016/05/01

Enter the following SQL statement:

SELECT customers.customer_id, orders.order_id, orders.order_date

FROM customers
RIGHT OUTER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;

There will be 5 records selected. These are the results that you should see:
customer_id order_id order_date

NULL 5 2016/05/01

4000 4 2016/04/20

5000 2 2016/04/18

7000 1 2016/04/18

8000 3 2016/04/19

This RIGHT OUTER JOIN example would return all rows from the orders table and only those
rows from the customers table where the joined fields are equal.
If a customer_id value in the orders table does not exist in the customers table, all fields in
the customers table will display as NULL in the result set. As you can see, the row
where order_id is 5 would be included with a RIGHT OUTER JOIN but the customer_id field
displays NULL.

SQL FULL OUTER JOIN

Another type of join is called a SQL FULL OUTER JOIN. This type of join returns all rows
from the LEFT-hand table and RIGHT-hand table with NULL values in place where the join
condition is not met.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 123
Syntax
The syntax for the SQL FULL OUTER JOIN is:

SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON [Link] = [Link];

In some databases, the OUTER keyword is omitted and written simply as FULL JOIN.

Visual Illustration
In this visual diagram, the SQL FULL OUTER JOIN returns the shaded area:

The SQL FULL OUTER JOIN would return the all records from both table1 and table2.

Example
Let’s look at an example that shows how to use the FULL OUTER JOIN in a SELECT
statement.
Using the same customers table as the previous example:
customer_id last_name first_name favorite_website

4000 Jackson Joe [Link]

5000 Smith Jane [Link]

6000 Ferguson Samantha [Link]

7000 Reynolds Allen [Link]

8000 Anderson Paige NULL

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 124

customer_id last_name first_name favorite_website

9000 Johnson Derek [Link]

And the orders table with the following data:

order_id customer_id order_date

1 7000 2016/04/18

2 5000 2016/04/18

3 8000 2016/04/19

4 4000 2016/04/20

5 NULL 2016/05/01

Enter the following SQL statement:

SELECT customers.customer_id, orders.order_id, orders.order_date

FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;

There will be 7 records selected. These are the results that you should see:
customer_id order_id order_date

NULL 5 2016/05/01

4000 4 2016/04/20

5000 2 2016/04/18

6000 NULL NULL

7000 1 2016/04/18

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 125

customer_id order_id order_date

8000 3 2016/04/19

9000 NULL NULL

This FULL OUTER JOIN example would return all rows from the orders table and all rows
from the customers table. Whenever the joined condition is not met, a NULL value would be
extended to those fields in the result set. This means that if a customer_id value in
the customers table does not exist in the orders table, all fields in the orders table will display as
NULL in the result set. Also, if a customer_id value in the orders table does not exist in
the customers table, all fields in the customers table will display as NULL in the result set.
As you can see, the rows where the customer_id is 6000 and 9000 would be included but
the order_id and order_date fields for those records contains a NULL value. The row where
the order_id is 5 would be also included but the customer_id field for that record has a NULL
value.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 126
XVIII. Views

SQL: VIEW
The SQL VIEW is, in essence, a virtual table that does not physically exist. Rather, it is created
by a SQL statement that joins one or more tables.

Create SQL VIEW

Syntax
The syntax for the CREATE VIEW statement in SQL is:

CREATE VIEW view_name AS

SELECT columns
FROM tables
[WHERE conditions];

view_name

The name of the SQL VIEW that you wish to create.

WHERE conditions

Optional. The conditions that must be met for the records to be included in the VIEW.

Example
Here is an example of how to use the SQL CREATE VIEW:

CREATE VIEW sup_orders AS

SELECT suppliers.supplier_id, [Link], [Link]
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
WHERE suppliers.supplier_name = ‘IBM’;

This SQL CREATE VIEW example would create a virtual table based on the result set of the
select statement. You can now query the SQL VIEW as follows:

SELECT *
FROM sup_orders;

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 127
Update SQL VIEW
You can modify the definition of a SQL VIEW without dropping it by using the SQL CREATE
OR REPLACE VIEW Statement.

Syntax
The syntax for the SQL CREATE OR REPLACE VIEW Statement is:

CREATE OR REPLACE VIEW view_name AS

SELECT columns
FROM table
[WHERE conditions];

Example
Here is an example of how you would use the SQL CREATE OR REPLACE VIEW Statement:

CREATE or REPLACE VIEW sup_orders AS

SELECT suppliers.supplier_id, [Link], [Link]
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
WHERE suppliers.supplier_name = ‘Microsoft’;

This SQL CREATE OR REPLACE VIEW example would update the definition of the SQL
VIEW called sup_orders without dropping it. If the SQL VIEW did not yet exist, the SQL VIEW
would merely be created for the first time.

Drop SQL VIEW

Once a SQL VIEW has been created, you can drop it with the SQL DROP VIEW Statement.

Syntax
The syntax for the SQL DROP VIEW Statement is:

DROP VIEW view_name;

view_name

The name of the view that you wish to drop.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 128
Example
Here is an example of how to use the SQL DROP VIEW Statement:

DROP VIEW sup_orders;

This SQL DROP VIEW example would drop/delete the SQL VIEW called sup_orders.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 129
XIX. Indexes

SQL: Indexes

What is an Index in SQL?

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. Each index name must be
unique in the database.

Create an Index
You can create an index in SQL using the CREATE INDEX statement.

Syntax
The syntax to create an index in SQL is:

CREATE [UNIQUE] INDEX index_name

ON table_name (column1, column2, … column_n);

UNIQUE

The UNIQUE modifier 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.

Example
Let’s look at an example of how to create an index in SQL.
For example:

CREATE INDEX websites_idx

ON websites (site_name);

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 130
In this example, we’ve created an index on the websites table called websites_idx. It consists of
only one field – the site_name field.
We could also create an index with more than one field as in the example below:

CREATE INDEX websites_idx

ON websites (site_name, server);

This would create an index called websites_idx that is made up of two columns –
site_name and server.
Unique Index
Similar to a primary key, a unique key allows you to choose one column or combination of
columns that must be unique for each record. Although you can only have one primary key on a
table, you can create as many unique indexes on a table as you need.
To create a unique index on a table, you need to specify the UNIQUE keyword in the CREATE
INDEX statement.
For example:

CREATE UNIQUE INDEX websites_idx

ON websites (site_name);

This example would create a unique index on the site_name field so that this field must always
contains a unique value with no duplicates. This is a great way to enforce integrity within your
database if you require unique values in columns that are not part of your primary key.

Drop an Index
You can drop an index in SQL using the DROP INDEX statement.

Syntax
The syntax to drop an index in SQL is:
For Oracle and PostgreSQL:

DROP INDEX index_name;

For MySQL and MariaDB:

DROP INDEX index_name

ON table_name;

For SQL Server:

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 131

DROP INDEX table_name.index_name;

index_name

The name of the index to drop.

Table_name

The name of the table that the index belongs to.

Example
Let’s look at an example of how to drop an index called websites_idx from the websites table.
For Oracle:

DROP INDEX websites_idx;

Because each index name must be unique within the database, we do not have to specify
the websites table in the DROP INDEX statement in Oracle.
For MySQL and MariaDB:

DROP INDEX websites_idx

ON websites;

For SQL Server:

DROP INDEX websites.websites_idx;

As you can see, each database has unique differences in their syntax. Be sure you use the correct
DROP INDEX command.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 132

XX. Roles
Roles
A role is a set or group of privileges that can be granted to users or another role. This is a great
way for database administrators to save time and effort.

Create Role
You may wish to create a role so that you can logically group the users' permissions. Please note
that to create a role, you must have CREATE ROLE system privileges.

Syntax
The syntax for creating a role in Oracle is:

CREATE ROLE role_name

[ NOT IDENTIFIED |
IDENTIFIED {BY password | USING [schema.] package | EXTERNALLY | GLOBALLY } ;

role_name

The name of the new role that you are creating. This is how you will refer to the grouping
of privileges.

NOT IDENTIFIED

It means that the role is immediately enabled. No password is required to enable the role.

IDENTIFIED

It means that a user must be authorized by a specified method before the role is enabled.

BY password

It means that a user must supply a password to enable the role.

USING package

It means that you are creating an application role - a role that is enabled only by
applications using an authorized package.

EXTERNALLY

It means that a user must be authorized by an external service to enable the role. An
external service can be an operating system or third-party service.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 133
GLOBALLY

It means that a user must be authorized by the enterprise directory service to enable the
role.

Note

 If both NOT IDENTIFIED and IDENTIFIED are omitted in the CREATE ROLE
statement, the role will be created as a NOT IDENTIFIED role.

Example
Let's look at an example of how to create a role in Oracle.
For example:

CREATE ROLE test_role;

This first example creates a role called test_role.

CREATE ROLE test_role

IDENTIFIED BY test123;

This second example creates the same role called test_role, but now it is password protected with
the password of test123.

Grant TABLE Privileges to Role

Once you have created the role in Oracle, your next step is to grant privileges to that role.
Just as you granted privileges to users, you can grant privileges to a role. Let's start with granting
table privileges to a role. Table privileges can be any combination of SELECT, INSERT,
UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.

Syntax
The syntax for granting table privileges to a role in Oracle is:

GRANT privileges ON object TO role_name

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 134
privileges

The privileges to assign to the role. It can be any of the following values:
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.

REFERENCES Ability to create a constraint that refers to the table.

Ability to perform ALTER TABLE statements to change the table

ALTER
definition.

INDEX Ability to create an index on the table with the create index statement.

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.

role_name

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

Example
Let's look at some examples of how to grant table privileges to a role in Oracle.
For example, if you wanted to grant SELECT, INSERT, UPDATE, and DELETE privileges on a
table called suppliers to a role named test_role, you would run the following GRANT statement:

GRANT select, insert, update, delete ON suppliers TO test_role;

You can also use the ALL keyword to indicate that you wish all permissions to be granted. For
example:

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 135

GRANT all ON suppliers TO test_role;

Revoke Table Privileges from Role

Once you have granted table privileges to a role, you may need to revoke some or all of these
privileges. To do this, you can execute a revoke command. You can revoke any combination of
SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.

Syntax
The syntax for revoking table privileges from a role in Oracle is:

REVOKE privileges ON object FROM role_name;

privileges

The privileges to revoke from the role. It can be any of the following values:
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.

REFERENCES Ability to create a constraint that refers to the table.

Ability to perform ALTER TABLE statements to change the table

ALTER
definition.

INDEX Ability to create an index on the table with the create index statement.

ALL All privileges on table.

object

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.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 136
role_name

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

Example
Let's look at some examples of how to revoke table privileges from a role in Oracle.
For example, if you wanted to revoke DELETE privileges on a table called suppliers from a role
named test_role, you would run the following REVOKE statement:

REVOKE delete ON suppliers FROM test_role;

If you wanted to revoke ALL privileges on the table called suppliers from a role
named test_role, you could use the ALL keyword. For example:

REVOKE all ON suppliers FROM test_role;

Grant Function/Procedure Privileges to Role

When dealing with functions and procedures, you can grant a role the ability to EXECUTE these
functions and procedures.

Syntax
The syntax for granting EXECUTE privileges on a function/procedure to a role in Oracle is:

GRANT EXECUTE ON object TO role_name;

EXECUTE

The ability to compile the function/procedure and the ability to execute the
function/procedure directly.

object

The name of the database object that you are granting privileges for. In the case of
granting EXECUTE privileges on a function or procedure, this would be the function
name or the procedure name.

role_name

The name of the role that will be granted the EXECUTE privileges.

Example

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 137
Let's look at an example of how to grant EXECUTE privileges on a function or procedure to a
role in Oracle.
For example, if you had a function called Find_Value and you wanted to grant EXECUTE access
to the role named test_role, you would run the following GRANT statement:

GRANT execute ON Find_Value TO test_role;

Revoke Function/Procedure Privileges from Role

Once you have granted EXECUTE privileges on a function or procedure to a role, you may need
to revoke these privileges from that role. To do this, you can execute a REVOKE command.

Syntax
The syntax for the revoking privileges on a function or procedure from a role in Oracle is:

REVOKE execute ON object FROM role_name;

EXECUTE

Revoking the ability to compile the function/procedure and the ability to execute the
function/procedure directly.

object

The name of the database object that you are revoking privileges for. In the case of
revoking EXECUTE privileges on a function or procedure, this would be the function
name or the procedure name.

role_name

The name of the role that will have the EXECUTE privileges revoked.

Example
Let's look at an example of how to grant EXECUTE privileges on a function or procedure to a
role in Oracle.
If you wanted to revoke EXECUTE privileges on a function called Find_Value from a role
named test_role, you would run the following REVOKE statement:

REVOKE execute ON Find_Value FROM test_role;

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 138
Grant Role to User
Now, that you've created the role and assigned the privileges to the role, you'll need to grant the
role to specific users.

Syntax
The syntax to grant a role to a user in Oracle is:

GRANT role_name TO user_name;

role_name

The name of the role that you wish to grant.

user_name

The name of the user that will be granted the role.

Example
Let's look at an example of how to grant a role to a user in Oracle:

GRANT test_role TO smithj;

This example would grant the role called test_role to the user named smithj.

Enable/Disable Role (Set Role Statement)

To enable or disable a role for a current session, you can use the SET ROLE statement.
When a user logs into Oracle, all default roles are enabled, but non-default roles must be enabled
with the SET ROLE statement.

Syntax
The syntax for the SET ROLE statement in Oracle is:

SET ROLE
( role_name [ IDENTIFIED BY password ] | ALL [EXCEPT role1, role2, ... ] | NONE );

role_name

The name of the role that you wish to enable.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 139
IDENTIFIED BY password

The password for the role to enable it. If the role does not have a password, this phrase
can be omitted.

ALL

It means that all roles should be enabled for this current session, except those listed
in EXCEPT.

NONE

Disables all roles for the current session (including all default roles).

Example
Let's look at an example of how to enable a role in Oracle.
For example:

SET ROLE test_role IDENTIFIED BY test123;

This example would enable the role called test_role with a password of test123.

Set role as DEFAULT Role

A default role means that the role is always enabled for the current session at logon. It is not
necessary to issue the SET ROLE statement. To set a role as a DEFAULT ROLE, you need to
issue the ALTER USER statement.

Syntax
The syntax for setting a role as a DEFAULT ROLE in Oracle is:

ALTER USER user_name

DEFAULT ROLE
( role_name | ALL [EXCEPT role1, role2, ... ] | NONE );

user_name

The name of the user whose role you are setting as DEFAULT.

role_name

The name of the role that you wish to set as DEFAULT.

ALL

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 140
It means that all roles should be enabled as DEFAULT, except those listed in EXCEPT.

NONE

Disables all roles as DEFAULT.

Example
Let's look at an example of how to set a role as a DEFAULT ROLE in Oracle.
For example:

ALTER USER smithj

DEFAULT ROLE
test_role;

This example would set the role called test_role as a DEFAULT role for the user named smithj.

ALTER USER smithj

DEFAULT ROLE
ALL;

This example would set all roles assigned to smithj as DEFAULT.

ALTER USER smithj

DEFAULT ROLE
ALL EXCEPT test_role;

This example would set all roles assigned to smithj as DEFAULT, except for the role
called test_role.

Drop Role
Once a role has been created in Oracle, you might at some point need to drop the role.

Syntax
The syntax to drop a role in Oracle is:

DROP ROLE role_name;

role_name

The name of the role that is to be dropped.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922

Oracle Material 141
Example
Let's look at an example of how to drop a role in Oracle.
For example:

DROP ROLE test_role;

This DROP statement would drop the role called test_role that we defined earlier.

Maitry Infotech, Nagiri Street, Srikalahasti – 517644 [Link].08578-221922