Unit - I

**************************************************************************

Oracle as RDBMS, SQL Commands and data types, Operators and Expressions,
Introduction to SQL * Plus, SQL Vs. SQL Plus

**************************************************************************

What is data processing Basics?

 Data are collection of facts, unorganized but able to be organized into useful
info.
 When the output of data can be used to help people make decisions, it’s called
information.
 Processing is a series of actions or operations that convert inputs into outputs.
 Data processing is defined as series of actions or operations that converts data
into useful info.

Data storage hierarchy

 The basic building block of data is a character.
 The characters are put together to form a field.
 A field is a meaningful collection of related characters which is the smallest
logical data entry that is treated as a single unit in data processing.
[Column/Attributes]
 Fields are normally grouped together to form a record.
 A record is a collection of related fields that are treated as a single unit.
[Row/Tuple]
 Records are grouped to form a file [table].
 A file is a number of related records that are treated as a unit.
 It’s customary to set up a master file of permanent data & to use transaction
files containing data of temporary nature.
 A database is a collection of integrated & related master files. It’s a collection
of logically related data elements that may be structured in various ways meet
the multiple processing & retrieval needs of organizations & individuals.
[Database is an Organized Collection of Related Information.]
 A database-management system (DBMS) is a collection of interrelated data
and a set of programs to access those data.
Example: Oracle, Sybase, dbase, FoxPro, IMS, MySQL, DB2 etc.

Gajendra Chourey Page 1

  RDBMS stands for Relational Database Management System. RDBMS is the
basis for SQL and for all modern database systems like MS SQL Server, IBM
DB2, Oracle, MySQL, and Microsoft Access.
 A Relational database management system (RDBMS) is a database management
system (DBMS) that is based on the relational model as introduced by E. F.
Codd.
 The data in RDBMS is stored in database objects called tables. The table is a
collection of related data entries and it consists of columns and rows. [Relation]

Table: Customers
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Rahul 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00

 Every table is broken up into smaller entities called fields. The fields in the
CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY. [A field is a
column in a table that is designed to maintain specific information about every
record in the table. OR A column is a vertical entity in a table that contains all
information associated with a specific field in a table.]

 A record, also called a row of data, is each individual entry that exists in a table.

 A NULL value in a table is a value in a field that appears to be blank, which means
a field with a NULL value is a field with no value. It is very important to
understand that a NULL value is different than a zero value or a field that
contains spaces. A field with a NULL value is one that has been left blank during
record creation.

Purpose of Database System:

1. The primary goal of a DBMS is to provide a way to store and retrieve database
information that is both convenient and efficient.
2. Database systems are designed to manage large bodies of information.
Management of data involves both defining structures for storage of
information and providing mechanisms for the manipulation of information. In
addition, the database system must ensure the safety of the information stored,
despite system crashes or attempts at unauthorized access. If data are to be
shared among several users, the system must avoid possible anomalous results.

Gajendra Chourey Page 2

Database System Applications
Databases are widely used. Here are some representative applications:

 Banking
 Airlines
 Railway
 Universities
 Telecommunication
 Credit card transactions
 Financial Institute

Database Systems versus File Systems

 Data redundancy and inconsistency

 Difficulty in accessing data
 Data isolation
 Integrity problems
 Atomicity problems
 Concurrent-access anomalies
 Security problems

ORACLE as RDBMS

 It is a very large and multi-user database management system. Oracle is a

relational database management system developed by 'Oracle Corporation'.

 Oracle works to efficiently manage its resource, a database of information, among

the multiple clients requesting and sending data in the network.
 It is an excellent database server choice for client/server computing. Oracle
supports all major operating systems for both clients and servers, including
MSDOS, NetWare, UnixWare, OS/2 and most UNIX flavors.
 Oracle began in 1977.
 1977 - Larry Ellison, Bob Miner and Ed Oates founded Software Development
Laboratories to undertake development work.

Features:
 Concurrency
 Read Consistency
 Locking Mechanisms

Gajendra Chourey Page 3

 Quiesce Database
 Portability
 Self-managing database
 SQL*Plus
 ASM
 Scheduler
 Resource Manager
 Data Warehousing
 Materialized views
 Bitmap indexes
 Table compression
 Parallel Execution
 Analytic SQL
 Data mining
 Partitioning

SQL
 SQL is Structured Query Language, which is a computer language for storing,
manipulating and retrieving data stored in relational database.

 SQL is Query language.

***************************************************************************
Query: -
 A query is a statement requesting the retrieval of information. The portion of a DML that involves
information retrieval is called a query language.
***************************************************************************

 SQL is the standard language for Relation Database System.

 All relational database management systems like MySQL, MS Access, Oracle,

Sybase, Informix, postgres and SQL Server use SQL as standard database
language.

 Also, they are using different dialects, such as:

o MS SQL Server using T-SQL,

o Oracle using PL/SQL,
o MS Access version of SQL is called JET SQL (native format) etc.

Why SQL?
 Allows users to access data in relational database management systems.

 Allows users to describe the data.

 Allows users to define the data in database and manipulate that data.

Gajendra Chourey Page 4

 Allows to embed within other languages using SQL modules, libraries & pre-
compilers.

 Allows users to create and drop databases and tables.

 Allows users to create view, stored procedure, functions in a database.

 Allows users to set permissions on tables, procedures and views.

History
 1970 -- Dr. E. F. "Ted" of IBM is known as the father of relational databases. He
described a relational model for databases.

 1974 -- Structured Query Language appeared.

 1978 -- IBM worked to develop Codd's ideas and released a product named
System/R.
 1986 -- IBM developed the first prototype of relational database and standardized
by ANSI. The first relational database was released by Relational Software and its
later becoming Oracle.

Benefits of SQL: -
o Non-Procedural language
o A language for all users
o Unified Language
o Common Language for all RDBMS
o Embedded SQL and Dynamic SQL
Notes:-
 All the SQL statements start with any of the keywords like SELECT, INSERT,
UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all statements end
with a semicolon (;).
 Important point to be noted is that SQL is case insensitive, which means
SELECT and select have same meaning in SQL statements, but MySQL makes
difference in table names. So if you are working with MySQL, then you need to
give table names as they exist in the database.
 SQL is followed by unique set of rules and guidelines called Syntax.

Consider the basic syntax of the SELECT statement as follows:

SELECT column1, column2, columnN FROM table_name

WHERE [CONDITION|EXPRESSION];

 An expression is a combination of one or more values, operators, and SQL

functions that evaluate to a value. SQL EXPRESSIONs are like formulas and
they are written in query language. You can also use them to query the
database for specific set of data.

 There are different types of SQL expressions:

Gajendra Chourey Page 5
  SQL -Boolean Expressions: SELECT column1, column2, columnN FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;

 SQL -Numeric Expression: SELECT numerical_expression as OPERATION_NAME

[FROM table_name WHERE CONDITION];
Example:- (1) SQL> SELECT (15 + 6) AS ADDITION from dual;
(2) SQL> SELECT COUNT(*) AS "RECORDS" FROM CUSTOMERS;

 SQL -Date Expressions: (1)SQL> SELECT CURRENT_TIMESTAMP from dual;

(2)SQL> SELECT GETDATE();

 DUAL is a table automatically created by Oracle database along with the data dictionary. Dual is in
the schema of the user SYS but is accessible by the name DUAL to all users. It has one column,
DUMMY, defined to be VARCHAR2(1), and contains on row with a value X.
**************************************************************************

SQL Commands:
 The standard SQL commands to interact with relational databases are CREATE, SELECT,
INSERT, UPDATE, DELETE and DROP.

 These commands can be classified into groups based on their nature:

Classification of SQL Command:–

DDL -Data Definition Language:

Command Description
CREATE Creates a new table, a view of a table, or other object in database
ALTER Modifies an existing database object, such as a table.
DROP Deletes an entire table, a view of a table or other object in the database.
Remove all records from a table, including all spaces allocated for the records
TRUNCATE
are removed.
COMMENT Add comments to the data dictionary.
RENAME Rename an object.

Gajendra Chourey Page 6

DML (Data Manipulation Language):–
Data manipulation is
o The retrieval of information stored in the database
o The insertion of new information into the database
o The deletion of information from the database
o The modification of information stored in the database
 Procedural DMLs require a user to specify what data are needed and how to get those
data.

 Declarative DMLs (also referred to as nonprocedural DMLs) require a user to specify

what data are needed without specifying how to get those data.

Command Description
INSERT Creates a record
UPDATE Modifies records
DELETE Deletes records
MERGE UPSERT operation (insert or update)
CALL Call a PL/SQL or Java subprogram.
EXPLAIN PLAN Explain access path to data
LOCK TABLE Control concurrency.
DQL - Data Query Language
SELECT Retrieves certain records from one or more tables

DCL (Data Control Language): -

 DCL statements are used to manage the user’s authority.

Command Description
GRANT Gives a privilege to user
REVOKE Takes back privileges granted from user

TCL (Transaction Control Language) Command: –

 SQL includes commands for specifying the beginning and ending of

transactions.
 A transaction is a unit of work that is performed against a database.

 Transactions are units or sequences of work accomplished in a logical order.

 A transaction is the propagation of one or more changes to the database.

 For example, if you are creating a record or updating a record or deleting a

record from the table, then you are performing transaction on the table. It is
important to control transactions to ensure data integrity and to handle
database errors.

Gajendra Chourey Page 7

There are following commands used to control transactions:

Command Description
COMMIT To save the changes.
ROLLBACK To rollback the changes.
SAVEPOINT Creates points within groups of transactions in which to ROLLBACK.
SET TRANSACTION Places a name on a transaction.

o Transactional control commands are only used with the DML commands
INSERT, UPDATE and DELETE only. They cannot be used while creating tables
or dropping them because these operations are automatically committed in the
database.

Properties of Transactions:

Transactions have the following four standard properties, usually referred to by the
acronym ACID:

 Atomicity: Ensures that all operations within the work unit are completed
successfully; otherwise, the transaction is aborted at the point of failure, and
previous operations are rolled back to their former state.
 Consistency: Ensures that the database properly changes states upon a
successfully committed transaction.
 Isolation: Enables transactions to operate independently of and transparent to
each other.
 Durability: Ensures that the result or effect of a committed transaction persists
in case of a system failure.

**************************************************************************
Note:
 In Oracle Documentation Type of SQL Statements section, they have referred to SELECT statement as “a
limited form of DML statement in that it can only access data in the database. It cannot manipulate data
in database, although it can operate on the accessed data before returning the results of the query. ”

 Other venders may refer to SELECT as “SQL-Data Statements”, or more appropriately Data Query
Language (DQL).

 INTO form is considered to be DML because it manipulates data.

 Manipulation means play with data which means retrieve, store, modify, delete, insert, update, copy etc.

Gajendra Chourey Page 8

Data Type:–

 Data types define the type of value that goes into a table column. Every column
has a data type.

 You would use these data types while creating your tables. You would choose a
particular data type for a table column based on your requirement.

ALPHANUMERIC DATA TYPES:

CHAR(size) - Max size 2000 characters
[If data is not the length of the column, then it is padded with spaces]
VARCHAR(size) -Max size 4000 bytes or characters.
VARCHAR2(size) - Max size 4000 characters.

 Varchar (size) and Varchar2 (size):

o VARCHAR is synonymous with the VARCHAR2 data type. Both data type is
used to store alphanumeric data. It is always a good practice to use
VARCHAR2 instead of VARCHAR to avoid behavioral changes. In case of
Varchar the size is defined for each variable at the time of variable
declaration. But in case of Varchar2 Oracle will allocate memory only after
the variable is defined, i.e., Oracle will consider only the actual length of the
string that is stored in a variable for memory allocation rather than the size
that has been given for a variable in the declaration part. So, it is always
good to use VARCHAR2 instead of CHAR data type to optimize the memory
usage. Both varchar and varchar2 can store up to 255 characters.

Example:- Address VARCHAR(10); Address VARCHAR2(10);

Gajendra Chourey Page 9

NUMERIC DATA TYPES:
Int - stores integer values
Integer(size) -stores integer values
Number(size) -stores integer values
Number(Precision, Scale) -Precision is total length, scale is digit after decimal.
Numeric(Precision, Scale) -Precision is total length, scale is digit after decimal.
DECIMAL(Precision, Scale) -Precision is total length, scale is digit after decimal.

DATE DATA TYPES:

DATE - All dates include century, year, month, date, hour, minutes and seconds
(Standard Format DD-MMM-YY)
TIMESTAMP -Takes sysdate time when you insert value

LARGE OBJECT DATA TYPES:

LONG - Character data, size 2GB
LONG RAW - Binary data storage, size 2GB
CLOB - Character data, size unlimited: 8 to 128TB
BLOB - Binary data storage, size unlimited: 8 to 128TB
BFILE -Store a link to external file (4GB size max)

Note: - Data type might have different names in different database. And even if the name is same, the
size and other details may be different!

ROWID

 ROWID data type stores information related to the disk location of table rows.
 They also uniquely identify the rows in your table.
 The ROWID data type is stored as a hexadecimal.

 Select rowid from dual;

 Select rowid from TableName;

**************************************************************************************

Operator: –

 An operator is a reserved word or a character used primarily in an SQL

statement's WHERE clause to perform operation(s), such as comparisons and
arithmetic operations.

Gajendra Chourey Page 10

  Operators are used to specify conditions in an SQL statement and to serve as
conjunctions for multiple conditions in a statement.

 Arithmetic operators

 Comparison operators

 Logical operators

a) Airthmatic Operators:-

Example
Operator Description (Assume a=10 &
b=20)
+ (Addition) -Adds values on either side of the operator a + b will give 30
Subtracts right hand operand from left hand
- (Subtraction) a - b will give -10
operand
* (Multiplication) Multiplies values on either side of the operator a * b will give 200
/ (Division) Divides left hand operand by right hand operand b / a will give 2
Divides left hand operand by right hand operand
% (Modulus) b % a will give
and returns remainder

Example:- SQL> select 10+ 20 from dual;

SQL> select 10 * 20 from dual; and
SQL> select 10 / 5 from dual; so on.

b) Comparison Operator:–

Example
Operator Description
(Assume a=10 & b=20)
Checks if the values of two operands are equal or not, if yes
= (a = b) is not true.
then condition becomes true.
Checks if the values of two operands are equal or not, if values
!= (a != b) is true.
are not equal then condition becomes true.
Checks if the values of two operands are equal or not, if values
<> (a <> b) is true.
are not equal then condition becomes true.
Checks if the value of left operand is greater than the value of
> (a > b) is not true.
right operand, if yes then condition becomes true.
Checks if the value of left operand is less than the value of right
< (a < b) is true.
operand, if yes then condition becomes true.
Checks if the value of left operand is greater than or equal to
>= (a >= b) is not true.
the value of right operand, if yes then condition becomes true.
Checks if the value of left operand is less than or equal to the
<= (a <= b) is true.
value of right operand, if yes then condition becomes true.
Checks if the value of left operand is not less than the value of
!< (a !< b) is false.
right operand, if yes then condition becomes true.
Checks if the value of left operand is not greater than the value
!> (a !> b) is true.
of right operand, if yes then condition becomes true.

Example:- Consider the CUSTOMERS table having the following records:

SQL> SELECT * FROM CUSTOMERS;

Gajendra Chourey Page 11

 ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Rahul 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muskan 24 Indore 10000.00

Here are simple examples showing usage of SQL Comparison Operators:

SQL> SELECT * FROM CUSTOMERS WHERE SQL> SELECT * FROM CUSTOMERS WHERE SALARY
SALARY > 5000; = 2000;
ID NAME AGE ADDRESS SALARY
ID NAME AGE ADDRESS SALARY
4 Chaitali 25 Mumbai 6500.00
1 Ramesh 32 Ahmedabad 2000.00
5 Hardik 27 Bhopal 8500.00
3 Kaushik 23 Kota 2000.00
7 Muskan 24 Indore 10000.00

SQL> SELECT * FROM CUSTOMERS WHERE SALARY SQL> SELECT * FROM CUSTOMERS WHERE SALARY
!= 2000; <> 2000;
ID NAME AGE ADDRESS SALARY ID NAME AGE ADDRESS SALARY
2 Rahul 25 Delhi 1500.00 2 Rahul 25 Delhi 1500.00
4 Chaitali 25 Mumbai 6500.00 4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00 5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00 6 Komal 22 MP 4500.00
7 Muskan 24 Indore 10000.00 7 Muskan 24 Indore 10000.00

SQL> SELECT * FROM CUSTOMERS WHERE SALARY

>= 6500;
ID NAME AGE ADDRESS SALARY
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
7 Muskan 24 Indore 10000.00

c) Logical Operators:–
Operator Description
ALL The ALL operator is used to compare a value to all values in another value set.
The AND operator allows the existence of multiple conditions in an SQL statement's
AND
WHERE clause.
The ANY operator is used to compare a value to any applicable value in the list
ANY
according to the condition.
The BETWEEN operator is used to search for values that are within a set of values,
BETWEEN
given the minimum value and the maximum value.
The EXISTS operator is used to search for the presence of a row in a specified table
EXISTS
that meets certain criteria.
The IN operator is used to compare a value to a list of literal values that have been
IN
specified.
The LIKE operator is used to compare a value to similar values using wildcard
LIKE
operators.
The NOT operator reverses the meaning of the logical operator with which it is used.
NOT
Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.
The OR operator is used to combine multiple conditions in an SQL statement's WHERE
OR
clause.
IS NULL The NULL operator is used to compare a value with a NULL value.
The UNIQUE operator searches every row of a specified table for uniqueness (no
UNIQUE
duplicates).

Gajendra Chourey Page 12

Example:- Consider the CUSTOMERS table.
Here are simple examples showing usage of SQL Logical Operators:

SQL> SELECT * FROM CUSTOMERS WHERE AGE >= 25 SQL> SELECT * FROM CUSTOMERS WHERE
AND SALARY >= 6500; NAME LIKE 'Ko%';
ID NAME AGE ADDRESS SALARY ID NAME AGE ADDRESS SALARY
4 Chaitali 25 Mumbai 6500.00 6 Komal 22 MP 4500.00
5 Hardik 27 Bhopal 8500.00
SQL> SELECT * FROM CUSTOMERS WHERE AGE >= 25 SQL> SELECT * FROM CUSTOMERS WHERE AGE
OR SALARY >= 6500; IS NOT NULL;
ID NAME AGE ADDRESS SALARY ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00 1 Ramesh 32 Ahmedabad 2000.00
2 Rahul 25 Delhi 1500.00 2 Rahul 25 Delhi 1500.00
4 Chaitali 25 Mumbai 6500.00 3 Kaushik 23 Kota 2000.00
5 Hardik 27 Bhopal 8500.00 4 Chaitali 25 Mumbai 6500.00
7 Muskan 24 Indore 10000.00 5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muskan 24 Indore 10000.00
SQL> SELECT * FROM CUSTOMERS WHERE AGE IN SQL> SELECT * FROM CUSTOMERS WHERE AGE
(25, 27); BETWEEN 25 AND 27;
ID NAME AGE ADDRESS SALARY ID NAME AGE ADDRESS SALARY
2 Rahul 25 Delhi 1500.00 2 Rahul 25 Delhi 1500.00
4 Chaitali 25 Mumbai 6500.00 4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00 5 Hardik 27 Bhopal 8500.00

SQL> SELECT * FROM CUSTOMERS WHERE AGE > ALL SQL> SELECT AGE FROM CUSTOMERS WHERE
(SELECT AGE FROM CUSTOMERS WHERE SALARY > EXISTS (SELECT AGE FROM CUSTOMERS
6500); WHERE SALARY > 6500);
ID NAME AGE ADDRESS SALARY AGE
1 Ramesh 32 Ahmedabad 2000.00 32
25
SQL> SELECT * FROM CUSTOMERS WHERE AGE > ANY
(SELECT AGE FROM CUSTOMERS WHERE SALARY > 23
6500); 25
ID NAME AGE ADDRESS SALARY 27
22
1 Ramesh 32 Ahmedabad 2000.00
24
2 Rahul 25 Delhi 1500.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00

Gajendra Chourey Page 13

SQL*Plus
 SQL*Plus is a command-line tool that's installed with the Oracle Database.
 SQL*Plus provides access to the Oracle RDBMS. SQL*Plus enables you to:
 Enter SQL*Plus commands to configure the SQL*Plus environment
 Startup and shutdown an Oracle database
 Connect to an Oracle database
 Enter and execute SQL commands and PL/SQL blocks
 Format and print query results

 SQL*Plus is available on several platforms.

 SQL Plus understands five categories of text:
1. SQL statements
2. PL/SQL blocks
3. SQL Plus Internal commands, for example:
 Environment control commands such as SET
 Environment monitoring commands such as SHOW
4. Comments
5. External commands prefixed by the !char

History
 SQL Plus is the most basic Oracle Database utility, with a basic command line
interface, commonly used by users, administrators and programmers. The first
version of SQL Plus was called UFI (User Friendly Interface). UFI appeared in
Oracle database release up to version 4. After Oracle programmers had added new
features to UFI, its name became Advanced UFI. The name “Advanced UFI”
changed to “SQL Plus” with the release of version 5 of Oracle.

How to work with SQL*PLUS?

 To start SQL*Plus, select the Run command from the Start menu, enter "sqlplus",
and select the OK button.
 To connect to a database, enter the username and password. If necessary, you can
enter the CONNECT command to have SQL*Plus prompt you for a username and
password.
 To run a SQL statement, type it, type a semicolon, and press the Enter key.

Gajendra Chourey Page 14

 Basic SQL*Plus Commands
Database Operation SQL*Plus Command Example
List help topics available in
HELP [ INDEX | topic ] HELP Show;
SQL*Plus
Execute host commands HOST [ command ] HOST;
List column definitions for a
table, view, or synonym, or
DESCRIBE [ schema. ] object DESCRIBE employee;
specifications for a function or
procedure
List and execute commands
RUN RUN;
stored in the SQL buffer
Execute a single PL/SQL
statement or run a stored EXECUTE statement
procedure
Disconnect from a database DISCONNECT DISCONNECT;
SHUTDOWN [ ABORT |
Shut down a database SHUTDOWN;
IMMEDIATE | NORMAL ]
{ EXIT | QUIT }
QUIT;
[ SUCCESS | FAILURE |
Log out of SQL*Plus
WARNING ]
[ COMMIT | ROLLBACK ]

DIFFERENCE BETWEEN SQL & SQL * PLUS

 SQL is a language. While SQL*Plus is a tool.

 SQL is the query language used for communication with Oracle server to access
and modify the data. SQL* Plus is a command line tool with which you can send
SQL queries to the server. Also, it can help you format the query result.
 SQL is a language which is invented by IBM. SQL * Plus is a tool to use SQL
language for a database from Oracle corporation.
 SQL can be simply used to ask queries, i.e. it involves DML, DDL and DCL.
SQL * Plus is command line tool which doesn’t involve DML, DDL and DCL.
 In SQL, there is no continuation character. Whereas, in SQL * Plus there is a
continuation character.
 Keywords cannot be abbreviated in SQL. But keywords can be abbreviated in
SQL*Plus.
 SQL uses functions to manipulate the data. SQL * plus uses commands to
manipulate the data.

Gajendra Chourey Page 15

 Unit - II
**************************************************************************

Table Manipulation: Creating Tables (including Constraints), Data Manipulation

Command (Like insert, update, delete)

SELECT statement with WHERE, GROUP BY and HAVING, ORDER BY, DISTINCT

**************************************************************************

Create Table Statement:

Syntax: CREATE TABLE table_name( column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
);
Example:-
CREATE TABLE Teacher (EID number(5), Name char(20), Specialization
varchar2(25), Address varchar2(30), MobileNo number(10));

You also define Primary Key and other constraints when you create table.
Syntax: – CREATE TABLE table_name( column1 datatype NOT NULL,
column2 datatype,
column3 datatype, ..... ,
columnN datatype,
PRIMARY KEY( one or more columns)
);
Example: –
CREATE TABLE CUSTOMERS( ID NUMBER(4) NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE NUMBER(3) NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID));
Description of table:
Syntax: - DESC table_name; or Describe table;
Example: - DESC Customer; or Describe Customer;
**************************************************************************

Insert Data in Table:

 SQL INSERT INTO Statement is used to add new rows of data to a table in the database.

Syntax: – There are two basic syntaxes of INSERT INTO statement as follows:

o INSERT INTO <TableName> (FieldName1, FieldName2,………………………………,

FieldNameN) VALUES (Value1, Value2, …………, ValueN);

Gajendra Chourey Page 16

You may not need to specify the column(s) name in the SQL query if you are adding values for all the
columns of the table. But make sure the order of the values is in the same order as the columns in the
table.

o INSERT INTO <TableName> VALUES (Value1, Value2, …………, ValueN);

Example: –
INSERT INTO Teacher (EID, Name, Specialization, Address, MobileNo)
VALUES (1001, Arjun Saxena, C Programming, HNo 177 Mangla Bilaspur,
8794562189);
OR

INSERT INTO Teacher VALUES (1001, Arjun Saxena, C Programming, HNo 177
Mangla Bilaspur, 8794562189);

Use of Macro for Insertion:

Syntax: -
INSERT INTO <TableName> (FieldName1, FieldName2,………………………, FieldNameN)
VALUES (&Value1, &Value2, …………, &ValueN);

Example: -
INSERT INTO Teacher (EID, Name, Specialization, Address, MobileNo)
VALUES (&EID, &Name, &Specialization, &Address, &MobileNo);

Prompt: Enter the Value of EID : 1002

Enter the Value of Name : ‘Ramesh’
Enter the Value of Specialization : ‘C Programming’
Enter the Value of Address : ‘D-33 River View Colony’
Enter the Value of MobilNo : 8975648956
**************************************************************************

Update Table Data:

UPDATE Statement: -
 SQL UPDATE Query is used to modify the existing records in a table. You can use WHERE clause
with UPDATE query to update selected rows, otherwise all the rows would be affected.

Syntax: –
UPDATE table_name SET column1 = value1, column2 = value2....,
columnN = valueN WHERE [condition];

Example: –
UPDATE CUSTOMERS SET ADDRESS = 'Pune' WHERE ID = 6;

Gajendra Chourey Page 17

Add new column to the table/Change Data type for given column/
Delete column/Rename

ALTER TABLE Statement:

 SQL ALTER TABLE command is used to add, delete or modify columns in an existing table. You
would also use ALTER TABLE command to add and drop various constraints on an existing table.
Syntax:
o ALTER TABLE table_name {ADD|DROP|MODIFY} column_name (data_ype);
o ALTER TABLE table_name RENAME TO new_table_name;

Work Syntax
The basic syntax of ALTER TABLE to add a new ALTER TABLE table_name ADD
column in an existing table is as follows: column_name datatype;
The basic syntax of ALTER TABLE to DROP ALTER TABLE table_name DROP
COLUMN in an existing table is as follows: COLUMN column_name;
The basic syntax of ALTER TABLE to change the ALTER TABLE table_name MODIFY
DATA TYPE of a column in a table is as follows: column_name datatype;
The basic syntax of ALTER TABLE to change the ALTER TABLE table_name RENAME
Column Name in a table is as follows: COLUMN Old_name TO New_name;
The basic syntax of ALTER TABLE to add a NOT
ALTER TABLE table_name MODIFY
NULL constraint to a column in a table is as
column_name datatype NOT NULL;
follows:
ALTER TABLE table_name ADD
The basic syntax of ALTER TABLE to ADD
CONSTRAINT MyUniqueConstraint
UNIQUE CONSTRAINT to a table is as follows:
UNIQUE(column1, column2...);
The basic syntax of ALTER TABLE to ADD CHECK ALTER TABLE table_name ADD
CONSTRAINT to a table is as follows: CONSTRAINT MyUniqueConstraint
CHECK (CONDITION);
ALTER TABLE table_name ADD
The basic syntax of ALTER TABLE to ADD
CONSTRAINT MyPrimaryKey PRIMARY
PRIMARY KEY constraint to a table is as follows:
KEY (column1, column2...);
ALTER TABLE table_name
The basic syntax of ALTER TABLE to DROP
DROP CONSTRAINT
CONSTRAINT from a table is as follows:
MyUniqueConstraint;
ALTER TABLE table_name DROP
If you're using MySQL, the code is as follows:
INDEX MyUniqueConstraint;
The basic syntax of ALTER TABLE to DROP
ALTER TABLE table_name DROP
PRIMARY KEY constraint from a table is as
CONSTRAINT MyPrimaryKey;
follows:
ALTER TABLE table_name DROP
If you're using MySQL, the code is as follows:
PRIMARY KEY;
ALTER TABLE table_name RENAME TO
SQL ALTER TABLE Statement (Rename)
new_table_name;

Gajendra Chourey Page 18

Example:-
Consider the CUSTOMERS table having the following records:
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Ramesh 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Kaushik 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muskan 24 Indore 10000.00

Following is the example to ADD a new column in an existing table:

ALTER TABLE CUSTOMERS ADD Gender char(1);
Now, CUSTOMERS table is changed and following would be output from SELECT statement:
ID NAME AGE ADDRESS SALARY Gender
1 Ramesh 32 Ahmedabad 2000.00 NULL
2 Ramesh 25 Delhi 1500.00 NULL
3 Kaushik 23 Kota 2000.00 NULL
4 Kaushik 25 Mumbai 6500.00 NULL
5 Hardik 27 Bhopal 8500.00 NULL
6 Komal 22 MP 4500.00 NULL
7 Muskan 24 Indore 10000.00 NULL

Following is the example to DROP Gender column from existing table:

ALTER TABLE CUSTOMERS DROP Gender;
Now, CUSTOMERS table is changed and following would be output from SELECT statement:

ID NAME AGE ADDRESS SALARY

1 Ramesh 32 Ahmedabad 2000.00
2 Ramesh 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Kaushik 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muskan 24 Indore 10000.00

Rename Statement:–
Syntax: – RENAME OldName to NewName;
Example: – Rename TEACHER to FACULTY;

DELETE Statement:
 SQL DELETE Query is used to delete the existing records from a table. You can use WHERE clause
with DELETE query to delete selected rows, otherwise all the records would be deleted.
Syntax: –
DELETE FROM table_name WHERE {CONDITION};
Example: -
 Delete from TEACHER;

 Delete from TEACHER where eid=1001;

Gajendra Chourey Page 19

Drop Table Statement:–
Syntax: – DROP table tablename;
Example: - DROP Table Customer;

TRUNCATE TABLE Statement:

 SQL TRUNCATE TABLE command is used to delete complete data from an existing table.
 You can also use DROP TABLE command to delete complete table but it would remove
complete table structure form the database and you would need to re-create this table once
again if you wish you store some data.

Syntax: TRUNCATE TABLE table_name;

Example: TRUNCATE TABLE CUSTOMERS;

Note:
 Delete and truncate both commands can be delete data of the table.
 Delete is a DML command whereas truncate is a DDL command.
 Truncate can be used to delete the entire data of the table without maintaining the integrity of
the table. On the other hand, delete statement can be used for deleting specific data.

*********************************************************
Data Integrity:
The following categories of the data integrity exist with each RDBMS:
 Entity Integrity: There are no duplicate rows in a table.
 Domain Integrity: Enforces valid entries for a given column by restricting the type, the format, or
the range of values.
 Referential Integrity: Rows cannot be deleted which are used by other records.
 User-Defined Integrity: Enforces some specific business rules that do not fall into entity, domain,
or referential integrity.

*********************************************************
SQL Constraints:
 Constraints are the rules enforced on data columns on table. These are used to
limit the type of data that can go into a table. This ensures the accuracy and
reliability of the data in the database.
 Constraints could be column level or table level. Column level constraints are
applied only to one column, whereas table level constraints are applied to the
whole table.

Gajendra Chourey Page 20

Following are commonly used constraints available in SQL:
 NOT NULL Constraint: Ensures that a column cannot have NULL value.
 DEFAULT Constraint: Provides a default value for a column when none is specified.
 UNIQUE Constraint: Ensures that all values in a column are different.
 PRIMARY Key: Uniquely identified each rows/records in a database table.
 FOREIGN Key: Uniquely identified a rows/records in any another database table.
 CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy
certain conditions.
 INDEX: Use to create and retrieve data from the database very quickly.

What is NULL value?

 A NULL value in a table is a value in a field that appears to be blank, which means a field with a
NULL value is a field with no value.
 It is very important to understand that a NULL value is different than a zero value or a field that
contains spaces. A field with a NULL value is one that has been left blank during record creation.

NOT NULL Constraint:

 By default, a column can hold NULL values. If you do not want a column to have a NULL
value, then you need to define such constraint on this column specifying that NULL is now
not allowed for that column.
 A NULL is not the same as no data, rather, it represents unknown data.

Example: -
 The following SQL creates a new table called CUSTOMERS and adds five columns, three of
which, ID and NAME and AGE, specify not to accept NULLs:

CREATE TABLE CUSTOMERS( ID INT NOT NULL,

NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);

 If CUSTOMERS table has already been created, then to add a NOT NULL constraint to
SALARY column in Oracle and MySQL, you would write a statement similar to the
following:

ALTER TABLE CUSTOMERS MODIFY SALARY DECIMAL (18, 2) NOT NULL;

DEFAULT Constraint:
 The DEFAULT constraint provides a default value to a column when the INSERT INTO
statement does not provide a specific value.

Example:

Gajendra Chourey Page 21

 The following SQL creates a new table called CUSTOMERS and adds five columns. Here,
SALARY column is set to 5000.00 by default, so in case INSERT INTO statement does not
provide a value for this column. Then by default this column would be set to 5000.00.

CREATE TABLE CUSTOMERS( ID INT NOT NULL,

NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2) DEFAULT 5000.00,
PRIMARY KEY (ID)
);

 If CUSTOMERS table has already been created, then to add a DFAULT constraint to
SALARY column, you would write a statement similar to the following:

ALTER TABLE CUSTOMERS MODIFY SALARY DECIMAL (18, 2) DEFAULT 5000.00;

Drop Default Constraint:

 To drop a DEFAULT constraint, use the following SQL:

ALTER TABLE CUSTOMERS ALTER COLUMN SALARY DROP DEFAULT;

UNIQUE Constraint:

 The UNIQUE Constraint prevents two records from having identical values in a particular
column. In the CUSTOMERS table, for example, you might want to prevent two or more
people from having identical age.

Example:
 The following SQL creates a new table called CUSTOMERS and adds five columns. Here,
AGE column is set to UNIQUE, so that you cannot have two records with same age:

CREATE TABLE CUSTOMERS( ID INT NOT NULL,

NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL UNIQUE,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
 If CUSTOMERS table has already been created, then to add a UNIQUE constraint to AGE column,
you would write a statement similar to the following:
ALTER TABLE CUSTOMERS MODIFY AGE INT NOT NULL UNIQUE;

 You can also use following syntax, which supports naming the constraint in multiple columns as
well:
ALTER TABLE CUSTOMERS ADD CONSTRAINT myUniqueConstraint UNIQUE(AGE,
SALARY);

DROP UNIQUE Constraint:

 To drop a UNIQUE constraint, use the following SQL:

Gajendra Chourey Page 22

 ALTER TABLE CUSTOMERS DROP CONSTRAINT myUniqueConstraint;

 If you are using MySQL, then you can use the following syntax:
ALTER TABLE CUSTOMERS DROP INDEX myUniqueConstraint;

PRIMARY Key:
 A primary key is a field in a table which uniquely identifies each row/record in a database table.
Primary keys must contain unique values. A primary key column cannot have NULL values.
 A table can have only one primary key, which may consist of single or multiple fields. When multiple
fields are used as a primary key, they are called a composite key.
 If a table has a primary key defined on any field(s), then you cannot have two records having the
same value of that field(s).

Here is the syntax to define ID attribute as a primary key in a CUSTOMERS table.

CREATE TABLE CUSTOMERS( ID INT NOT NULL,

NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);

CREATE TABLE CUSTOMERS( ID INT PRIMARY KEY,

NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
);

To create a PRIMARY KEY constraint on the "ID" column when CUSTOMERS table already
exists, use the following SQL syntax:

ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID);

NOTE: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already
have been declared to not contain NULL values (when the table was first created).

For defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

CREATE TABLE CUSTOMERS( ID INT NOT NULL,

NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID, NAME)

Gajendra Chourey Page 23

 );

To create a PRIMARY KEY constraint on the "ID" and "NAMES" columns when CUSTOMERS table
already exists, use the following SQL syntax:
ALTER TABLE CUSTOMERS ADD CONSTRAINT PK_CUSTID PRIMARY KEY (ID, NAME);

Delete Primary Key:

 You can clear the primary key constraints from the table, Use Syntax:
ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

FOREIGN Key:
 A foreign key is a key used to link two tables together. This is sometimes called a referencing
key.
 Foreign Key is a column or a combination of columns whose values match a Primary Key in a
different table.
 The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign
Key in the second table.
 If a table has a primary key defined on any field(s), then you cannot have two records having the
same value of that field(s).

Example: Consider the structure of the two tables as follows:

CUSTOMERS table:
CREATE TABLE CUSTOMERS( ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);

ORDERS table:
CREATE TABLE ORDERS ( ID INT NOT NULL,
DATE DATETIME,
CUSTOMER_ID INT references CUSTOMERS(ID),
AMOUNT double,
PRIMARY KEY (ID)
);
If ORDERS table has already been created, and the foreign key has not yet been set, use the syntax for
specifying a foreign key by altering a table.
ALTER TABLE ORDERS ADD FOREIGN KEY (Customer_ID) REFERENCES
CUSTOMERS (ID);

DROP FOREIGN KEY Constraint:

 To drop a FOREIGN KEY constraint, use the following SQL:
ALTER TABLE ORDERS DROP FOREIGN KEY;

Gajendra Chourey Page 24

CHECK Constraint:
 The CHECK Constraint enables a condition to check the value being entered into a record. If the
condition evaluates to false, the record violates the constraint and isn’t entered into the table.

Example:
 For example, the following SQL creates a new table called CUSTOMERS and adds five columns.
Here, we add a CHECK with AGE column, so that you cannot have any CUSTOMER below 18
years:
CREATE TABLE CUSTOMERS( ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL CHECK (AGE >= 18),
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);

If CUSTOMERS table has already been created, then to add a CHECK constraint to AGE column, you
would write a statement similar to the following:
ALTER TABLE CUSTOMERS MODIFY AGE INT NOT NULL CHECK
(AGE >= 18 );

You can also use following syntax, which supports naming the constraint in multiple columns as well:

ALTER TABLE CUSTOMERS ADD CONSTRAINT myCheckConstraint

CHECK(AGE >= 18);

DROP CHECK Constraint:

 To drop a CHECK constraint, use the following SQL. This syntax does not work with MySQL:

ALTER TABLE CUSTOMERS DROP CONSTRAINT myCheckConstraint;

INDEX:
 The INDEX is used to create and retrieve data from the database very quickly.
 Index can be created by using single or group of columns in a table.
 When index is created, it is assigned a ROWID for each row before it sorts out the data.
 Proper indexes are good for performance in large databases, but you need to be careful while
creating index.
 Selection of fields depends on what you are using in your SQL queries.
Example: For example, the following SQL creates a new table called CUSTOMERS and adds five
columns:
CREATE TABLE CUSTOMERS( ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
Gajendra Chourey Page 25
 PRIMARY KEY (ID)
);
Now, you can create index on single or multiple columns using the following syntax:
CREATE INDEX index_name ON table_name ( column1,
column2,...columnN);

To create an INDEX on AGE column, to optimize the search on customers for a particular age,
following is the SQL syntax:
CREATE INDEX idx_age ON CUSTOMERS ( AGE );

DROP INDEX Statement:

Syntax: ALTER TABLE table_name DROP INDEX index_name;

Example: ALTER TABLE CUSTOMERS DROP INDEX idx_age;

 Note: By default, Oracle creates B-Tree Indexes.

*******************************************

Data Retrieval from the Table:

SELECT Statement:
o Select * FROM table_name;

o Select column1, column2, column3 FROM table_name;

WHERE Clause:-
 WHERE clause is used to specify a condition while fetching the data from single table or joining
with multiple tables.
 If the given condition is satisfied, then only it returns specific value from the table.
 You would use WHERE clause to filter the records and fetching only necessary records.
 The WHERE clause is not only used in SELECT statement, but it is also used in UPDATE, DELETE
statement, etc.

Syntax: - Select column1, column2, column3 FROM table_name WHERE

[Condition];
Example: -
1. Select ID, Name FROM Student Where age < 15;

2. SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE NAME = 'Hardik';

ID NAME AGE ADDRESS SALARY

1 Ramesh 32 Ahmedabad 2000.00
2 Rahul 25 Delhi 1500.00

Gajendra Chourey Page 26

 3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00

Operator Use Syntax/Example

SQL AND and OR operators are used to combine multiple conditions to narrow data in an SQL
statement. These two operators are called conjunctive operators. These operators provide a
means to make multiple comparisons with different operators in the same SQL statement.
The AND operator Syntax:-
allows the SELECT column1, column2, columnN
existence of FROM table_name
AND multiple conditions WHERE [condition1] AND [condition2]...AND
in an SQL [conditionN];
statement's Example:-
WHERE clause. SELECT ID, NAME, SALARY FROM CUSTOMERS
WHERE SALARY > 2000 AND age < 25;
The OR operator is Syntax:-
used to combine SELECT column1, column2, column
multiple conditions FROM table_name
in an SQL WHERE [condition1] OR [condition2]...OR
OR
statement's [conditionN];
WHERE clause. Example:-
SQL> SELECT ID, NAME, SALARY FROM CUSTOMERS
WHERE SALARY > 2000 OR age < 25;
NOT operator Syntax:-
display a record if SELECT column1, column2, column
the condition(s) is FROM table_name
not TRUE. WHERE NOT [condition];
NOT
Example:-
SQL> SELECT ID, NAME, SALARY FROM CUSTOMERS
WHERE NOT SALARY = 2000 ;

Distinct Keyword:
 DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the
duplicate records and fetching only unique records.
 There may be a situation when you have multiple duplicate records in a table. While
fetching such records, it makes more sense to fetch only unique records instead of fetching
duplicate records.

 In a table, some of the columns may contain duplicate values. This is not a problem,
however, sometimes you will want to list only the different (distinct) values in a table.

The DISTINCT keyword can be used to return only distinct (different) values.

Gajendra Chourey Page 27

Syntax: - SELECT DISTINCT column1, column2,....., columnN FROM
table_name WHERE [condition];

Example: - 1. SELECT SALARY FROM CUSTOMERS ORDER BY SALARY;

2. SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY;

TOP:
 TOP clause is used to fetch a TOP N number or X percent records from a table.
Syntax: SELECT TOP number|percent column_name(s) FROM table_name WHERE
[condition];

Example: SELECT TOP 3 * FROM CUSTOMERS;

 All the databases do not support TOP clause. For example MySQL supports LIMIT clause to fetch
limited number of records and Oracle uses ROWNUM to fetch limited number of records.

 SELECT column_name(s) FROM table_name LIMIT number;

SELECT * FROM CUSTOMERS LIMIT 5;

 SELECT column_name(s) FROM table_name WHERE ROWNUM <=

number;

SELECT * FROM CUSTOMERS WHERE ROWNUM <= 3;

Return starting 3 record from table.

ORDER BY Clause: -
 Order by clause is used with Select statement for arranging retrieved data in sorted order.
 The Order by clause by default sort data in ascending order. Some database sorts query results
in ascending order by default.
Syntax: -
1. SELECT column1, column2....columnN FROM table_name
WHERE CONDITION ORDER BY column_name {ASC|DESC};

2. You can use more than one column in the ORDER BY clause.
SELECT column-list FROM table_name [WHERE condition] [ORDER BY
column1, column2, .. columnN] [ASC | DESC];

Example: - 1. SELECT * from Customer order by salary;

2. SELECT * from Customer order by CustomerName;

3. SELECT * FROM CUSTOMERS ORDER BY NAME, SALARY;

4. SELECT * FROM CUSTOMERS ORDER BY NAME DESC;

To fetch the rows with own preferred order, the SELECT query would be as follows:
SELECT * FROM CUSTOMERS ID NAME AGE ADDRESS SALARY
Gajendra Chourey Page 28
ORDER BY (CASE ADDRESS 2 Ramesh 25 Delhi 1500.00
WHEN 'DELHI' THEN 1 5 Hardik 27 Bhopal 8500.00
WHEN 'BHOPAL' THEN 2 3 Kaushik 23 Kota 2000.00
WHEN 'KOTA' THEN 3 6 Komal 22 MP 4500.00
WHEN 'AHMADABAD' THEN 4 4 Chaitali 25 Mumbai 6500.00
7 Muskan 24 Indore 10000.00
WHEN 'MP' THEN 5
1 Ramesh 32 Ahmedabad 2000.00
ELSE 100 END) ASC, ADDRESS DESC;

Aggregate Functions: -
 Aggregate functions take a collection of values and return a single value as a result.

Following are aggregate functions:

 COUNT Function
o The SQL COUNT aggregate function is used to count the number of rows in a database
table.
Example:-
Employee
Id Name Work_date Daily_typing_pages
1 John 2019-01-24 250
2 Ram 2019-05-27 220
3 Jack 2019-05-06 170
3 Jack 2019-04-06 100
4 Jill 2019-04-06 220
5 Zara 2019-06-06 300
5 Zara 2019-02-06 350

Suppose based on the Employee table you want to count total number of rows in this table, then
you can do it as follows:
SELECT COUNT (*) FROM Employee;

Output: - COUNT (*)

7
1 row in set

Similarly, if you want to count the number of records for Zara, then it can be done as follows:
SELECT COUNT(*) FROM Employee WHERE name="Zara";
Output: - COUNT (*)
2
1 row in set
NOTE: All the SQL queries are case insensitive, so it does not make any difference if you give
ZARA or Zara in WHERE CONDITION.

 MAX Function
o The MAX aggregate function allows us to select the highest (maximum) value for a
certain column.

Gajendra Chourey Page 29

 Example:- Suppose based on the above table you want to fetch maximum value of
daily_typing_pages, then you can do so simply using the following command:

SELECT MAX(daily_typing_pages)FROM Employee;

Output: - MAX(daily_typing_pages)
350
You can find all the records with maxmimum value for each name using GROUP BY clause as
follows:

SELECT id, name, MAX(daily_typing_pages)FROM Employee GROUP BY

name;

Id Name MAX(daily_typing_pages)
3 Jack 170
4 Jill 220

 MIN Function:
o The MIN aggregate function allows us to select the lowest (minimum) value for a certain
column.

Example:- Suppose based on the above table you want to fetch minimum value of
daily_typing_pages, then you can do so simply using the following command:

SELECT MIN(daily_typing_pages)FROM Employee;

Output: - MIN(daily_typing_pages)
100
You can find all the records with minimum value for each name using GROUP BY clause as
follows:
SELECT id, name, work_date, MIN(daily_typing_pages) FROM Employee GROUP
BY name;

Output: -
Id Name MIN(daily_typing_pages)
3 Jack 100
4 Jill 220
1 John 250
2 Ram 220
5 Zara 300

You can use MIN Function along with MAX function to find out minimum value as well.

SELECT MIN(daily_typing_pages) least,MAX(daily_typing_pages) max FROM

Employee;

Output: -

Gajendra Chourey Page 30

 least max
100 350

 AVG Function
o The AVG aggregate function selects the average value for certain table column or SQL
AVG function is used to find out the average of a field in various records.

Example:- Suppose based on the above table you want to calculate average of all the
dialy_typing_pages, then you can do so by using the following command:

SELECT AVG(daily_typing_pages)FROM Employee;

Output: - AVG(daily_typing_pages)
230.0000

You can take average of various records set using GROUP BY clause. Following example will take
average all the records related to a single person and you will have average typed pages by every
person.

SELECT name, AVG(daily_typing_pages)FROM Employee GROUP BY name;

Output: -
Name AVG(daily_typing_pages
Jack 135.0000
Jill 220.0000
John 250.0000
Ram 220.0000
Zara 325.0000

 SUM Function
o The SUM aggregate function allows selecting the total for a numeric column or sum
takes a collection of values and returns the sum of the values.
Example: - Suppose based on the above table you want to calculate total of all the
dialy_typing_pages, then you can do so by using the following command:

SELECT SUM(daily_typing_pages)FROM Employee;

Output: - SUM(daily_typing_pages)
1610

You can take sum of various records set using GROUP BY clause. Following example will sum up all
the records related to a single person and you will have total typed pages by every person.

SELECT name, SUM(daily_typing_pages)FROM employee_tbl GROUP BY

name;

Output: -
Name SUM(daily_typing_pages)
Jack 270
Jill 220
John 250

Gajendra Chourey Page 31

 Ram 220
Zara 650

Note: SQL is based on relational algebra, and QBE and Datalog are based on domain relational
calculus.

Gajendra Chourey Page 32

GROUP BY Clause: -
 The group by clause is a SQL command that is used to group rows that have the same
values. The group by clause is used in the select statement. Optionally it is used with
aggregate functions to produce summary report from database.

 Group by clause is used to group the results of a SELECT query based on one or more
columns.

 GROUP BY clause is used in collaboration with the SELECT statement to arrange identical
data into groups.

Syntax: -
SELECT column_name, function(column_name) FROM table_name
WHERE condition GROUP BY column_name;

 The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the
ORDER BY clause. The GROUP BY clause must follow the conditions in the WHERE clause and
must precede the ORDER BY clause if one is used.

Syntax: - SELECT column1, column2 FROM table_name WHERE [conditions]

GROUP BY column1, column2 ORDER BY column1, column2;

CUSTOMERS CUSTOMERS2
ID NAME AGE ADDRESS SALARY ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00 1 Ramesh 32 Ahmedabad 2000.00
2 Suresh 25 Delhi 1500.00 2 Ramesh 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00 3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00 4 Kaushik 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00 5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00 6 Komal 22 MP 4500.00
7 Muskan 24 Indore 10000.00 7 Muskan 24 Indore 10000.00
If you want to know the total amount of salary If you want to know the total amount of salary
on each customer, then GROUP BY query would on each customer, then GROUP BY query would
be as follows: be as follows:

SELECT NAME, SUM(SALARY) FROM SELECT NAME, SUM(SALARY) FROM

CUSTOMERS GROUP BY NAME; CUSTOMERS2 GROUP BY NAME;

NAME SUM(SALARY) NAME SUM(SALARY

Chaitali 6500.00 Hardik 8500.00
Hardik 8500.00 kaushik 8500.00
kaushik 2000.00 Komal 4500.00
Suresh 1500.00 Muskan 10000.00
Komal 4500.00 Ramesh 3500.00
Muskan 10000.00
Ramesh 2000.00

Gajendra Chourey Page 33

HAVING Clause: -
 HAVING clause enables you to specify conditions that filter which group results appear in the
final results.
 The WHERE clause places conditions on the selected columns, whereas the HAVING clause
places conditions on groups created by the GROUP BY clause.
 Having clause is used with SQL Queries to give more precise condition for a statement. It is used
to mention condition in Group based SQL functions, just like WHERE clause.

Syntax:
The following is the position of the HAVING clause in a query:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
Syntax: -
Select column_name, function(column_name) FROM table_name
WHERE column_name condition GROUP BY column_name HAVING
function(column_name) condition;

The HAVING clause must follow the GROUP BY clause in a query and must also precede the
ORDER BY clause if used. The following is the syntax of the SELECT statement, including the
HAVING clause:

Syntax:
SELECT column1, column2 FROM table1, table2
WHERE [conditions] GROUP BY column1, column2
HAVING [conditions] ORDER BY column1, column2;

Example: -
Following is the example, which would display record for which similar age count would
be more than or equal to 2:

SELECT AGE FROM CUSTOMERS GROUP BY age HAVING COUNT(age) >= 2;

This would produce the following result:

AGE
25

Gajendra Chourey Page 34

 Unit - III
Operators: Special Operator (IN, ANY, ALL, BETWEEN, EXISTS, LIKE), Join, Built in
Functions;

**************************************************************************

Table: Persons
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Operators:

BETWEEN:
The BETWEEN operator is used in a WHERE clause to select a range of data between two values.
The values can be numbers, text, or dates.

Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2;

Example:
BETWEEN
SELECT * FROM Persons
WHERE LastName
BETWEEN 'Hansen' AND 'Pettersen';

Output:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

BETWEEN
SELECT * FROM Persons WHERE LastName
BETWEEN 'Hansen' AND 'Svendson';

Output
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

BETWEEN
SELECT * FROM Persons WHERE P_ID
BETWEEN 1 AND 3;

Gajendra Chourey Page 35

 Output
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

NOT BETWEEN:

The NOT BETWEEN operator is used in a WHERE clause to select a range of data not between
two values. The values can be numbers, text, or dates.

Example:
NOT BETWEEN
SELECT * FROM Persons
WHERE LastName
NOT BETWEEN 'Hansen' AND 'Pettersen';

Output:
P_Id LastName FirstName Address City
2 Svendson Tove Borgvn 23 Sandnes

NOT BETWEEN
SELECT * FROM Persons
WHERE LastName
NOT BETWEEN 'Hansen' AND 'Svendson';

Output: No Data Found

NOT BETWEEN
SELECT * FROM Persons WHERE P_ID NOT
BETWEEN 1 AND 2 ;

Output:
P_Id LastName FirstName Address City
3 Pettersen Kari Storgt 20 Stavanger

Note: The BETWEEN operator is treated differently in different databases.

**************************************************************************
IN
 The IN operator allows you to specify multiple values in a WHERE clause.
 IN allows you to easily test if an expression matches any value in a list of values.
 It is used to reduce the need for multiple OR conditions in a SELECT, INSERT, UPDATE, OR DELETE
statement.
Syntax:
SELECT column_name(s) FROM table_name
WHERE column_name IN(value1,value2,...);

Example:
IN

Gajendra Chourey Page 36

 SELECT * FROM Persons
WHERE LastName IN ('Hansen','Pettersen');

Output
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

IN
SELECT * FROM Persons
WHERE P_ID IN (1,3);

Output
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

NOT IN
IN
SELECT * FROM Persons
WHERE P_ID NOT IN (1,3);

Output:
P_Id LastName FirstName Address City
2 Svendson Tove Borgvn 23 Sandnes

**************************************************************************
IS NULL
A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is
one that has been left blank during record creation.

Syntax:
SELECT Column_name(s) FROM table_name
WHERE column_name IS NULL;

Example:
SELECT CustomerName FROM Customers
WHERE Address IS NULL;

IS NOT NULL
Syntax:
SELECT Column_name(s) FROM table_name
WHERE column_name IS NOT NULL;
Example:
SELECT CustomerName FROM Customers
WHERE Address IS NOT NULL;

Gajendra Chourey Page 37

SQL Wildcards or Wildcard Characters:
 A wildcard character is used to substitute one or more characters in a string.
 Wildcard characters are used with the LIKE operator. The LIKE operator is used
in a WHERE clause to search for a specified pattern in a column.
 Example:
% Represents zero or more characters.
_ Represents a single character.

LIKE
 SQL LIKE clause is used to compare a value to similar values using wildcard characters. There are
two wildcards used in conjunction with the LIKE operator:
 The percent sign (%)
 The underscore (_)
 The percent sign represents zero, one, or multiple characters. The underscore represents a single
number or character. The symbols can be used in combinations.

Syntax:
SELECT * FROM table_name
WHERE column LIKE 'XXXX%';
or
SELECT * FROM table_name
WHERE column LIKE '%XXXX%;
or
SELECT * FROM table_name
WHERE column LIKE 'XXXX_';
or
SELECT * FROM table_name
WHERE column LIKE '_XXXX';
or
SELECT * FROM table_name
WHERE column LIKE '_XXXX_';

 You can combine N number of conditions using AND or OR operators.

 Here, XXXX could be any numeric or string value.

Example: Here are number of examples showing WHERE part having different LIKE clause with
'%' and '_' operators:

Statement Description
WHERE SALARY LIKE Finds any values that start with 200
'200%'
WHERE SALARY LIKE Finds any values that have 200 in any position
'%200%'

Gajendra Chourey Page 38

 WHERE SALARY LIKE Finds any values that have 00 in the second and third positions
'_00%'
WHERE SALARY LIKE Finds any values that start with 2 and are at least 3 characters
'2_%_%' in length
WHERE SALARY LIKE '%2' Finds any values that end with 2
WHERE SALARY LIKE Finds any values that have a 2 in the second position and end
'_2%3' with a 3
WHERE SALARY LIKE Finds any values in a five-digit number that start with 2 and end
'2___3' with 3

 SQL allows the specification of an escape character. The escape character is used immediately
before a special pattern character to indicate that the special pattern character is to be treated like
a normal character. We define the escape character for a like comparison using the escape
keyword.
like ’ab\%cd%’ escape ’\’ matches all strings beginning with “ab%cd”.
like ’ab\\cd%’ escape ’\’ matches all strings beginning with “ab\cd”.

**************************************************************************

SOUNDEX()

The SOUNDEX() function returns a string that contains the phonetic representation of a string.

Example 1:

SELECT SOUNDEX('see') see, SOUNDEX('sea') sea FROM Dual;

Example 2: Table: EMP21

Query 1: SELECT * FROM emp21 WHERE SOUNDEX(ename) = SOUNDEX('virendra');

Query 2: SELECT * FROM emp21 WHERE SOUNDEX(ename) LIKE SOUNDEX('virendra');

Output:

Gajendra Chourey Page 39

Simple and Complex Query: -
Simple query is searches using one parameter value.
Whereas, a complex query is a parameter query that searches using more than one parameter value,
that is two or more criteria.

Subqueries or Nested Queries and Correlated nested Queries: -

 Subquery or Inner query or Nested query is a query within another SQL query and embedded
within the WHERE clause.
 A subquery is used to return data that will be used in the main query as a condition to further
restrict the data to be retrieved.
 Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with
the operators like =, <, >, >=, <=, IN, BETWEEN etc.

There are a few rules that subqueries must follow:

 Subqueries must be enclosed within parentheses.

 A subquery can have only one column in the SELECT clause, unless multiple columns are in the
main query for the subquery to compare its selected columns.

 An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The
GROUP BY can be used to perform the same function as the ORDER BY in a subquery.

 Subqueries that return more than one row can only be used with multiple value operators, such
as the IN operator.

 The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB,
or NCLOB.

 A subquery cannot be immediately enclosed in a set function.

 The BETWEEN operator cannot be used with a subquery; however, the BETWEEN operator can
be used within the subquery.

Subqueries with the SELECT Statement: -

Subqueries are most frequently used with the SELECT statement.
The basic syntax is as follows:
SELECT column_name [, column_name]
FROM table1 [, table2]
WHERE column_name OPERATOR
(SELECT column_name [, column_name]
FROM table1 [, table2]
[WHERE]);
Example: -
Consider the CUSTOMERS table having the following records:

Gajendra Chourey Page 40

 CUSTOMERS
ID NAME AGE ADDRESS SALARY
1 Ramesh 35 Ahmedabad 2000.00
2 Suresh 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muskan 24 Indore 10000.00

Now, let us check the following subquery with SELECT statement:

SELECT *
FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS
WHERE SALARY > 4500);

This would produce the following result:

ID NAME AGE ADDRESS SALARY
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
7 Muskan 24 Indore 10000.00

Subqueries with the INSERT Statement:

 Subqueries also can be used with INSERT statements.
 The INSERT statement uses the data returned from the subquery to insert into another table. The
selected data in the subquery can be modified with any of the character, date or number functions.

The basic syntax is as follows:

INSERT INTO table_name [ (column1 [, column2 ])]
SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ];
Example: -
Consider a table CUSTOMERS_BKP with similar structure as CUSTOMERS table. Now to copy complete
CUSTOMERS table into CUSTOMERS_BKP:
INSERT INTO CUSTOMERS_BKP
SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS);

Subqueries with the UPDATE Statement:

The subquery can be used in conjunction with the UPDATE statement. Either single or multiple
columns in a table can be updated when using a subquery with the UPDATE statement.
The basic syntax is as follows:
UPDATE table_name
SET column_name = new_value
[WHERE OPERATOR [ VALUE]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ];

Gajendra Chourey Page 41

Example: -
Assuming, we have CUSTOMERS_BKP table available which is backup of CUSTOMERS table.
Following example updates SALARY by 0.25 times in CUSTOMERS table for all the customers
whose AGE is greater than or equal to 27:

UPDATE CUSTOMERS
SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 27);
This would impact two rows and finally CUSTOMERS table would have the following records:
ID NAME AGE ADDRESS SALARY
1 Ramesh 35 Ahmedabad 125.00
2 Suresh 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 2125.00
6 Komal 22 MP 4500.00
7 Muskan 24 Indore 10000.00

Subqueries with the DELETE Statement:

The subquery can be used in conjunction with the DELETE statement like with any other
statements mentioned above.
The basic syntax is as follows:
DELETE FROM TABLE_NAME [WHERE OPERATOR [VALUE]
(SELECT COLUMN_NAME FROM TABLE_NAME) [WHERE)];
Example: -
Assuming, we have CUSTOMERS_BKP table available which is backup of CUSTOMERS table.
Following example deletes records from CUSTOMERS table for all the customers whose AGE is
greater than or equal to 27:

DELETE FROM CUSTOMERS

WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 27 );
This would impact two rows and finally CUSTOMERS table would have the following records:
ID NAME AGE ADDRESS SALARY
2 Suresh 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
6 Komal 22 MP 4500.00
7 Muskan 24 Indore 10000.00

Gajendra Chourey Page 42

Operators used in Nested Queries: -

Operator Description Syntax Example

ALL operator is SELECT column_name(s) SELECT * FROM CUSTOMERS
used with WHERE FROM table_name WHERE AGE > ALL (SELECT
or HAVING Clause. WHERE column_name AGE FROM CUSTOMERS WHERE
The ALL operator
ALL operator ALL (SELECT SALARY > 6500);
returns true if all of
the subquery
column_name
values meet the FROM table_name
condition. WHERE condition);
ANY operator is SELECT column_name(s) SELECT * FROM CUSTOMERS
used with WHERE FROM table_name WHERE AGE > ANY (SELECT
or HAVING Clause. WHERE column_name AGE FROM CUSTOMERS WHERE
The ANY operator
ANY operator ANY (SELECT SALARY > 6500);
returns true if any
of the subquery
column_name
values meet the FROM table_name
condition. WHERE condition);
TRUE if any of the SELECT * FROM CUSTOMERS WHERE AGE > SOME
SOME subquery values (SELECT AGE FROM CUSTOMERS WHERE SALARY >
meet the condition 6500);
SELECT column_name(s) SELECT * FROM CUSTOMERS
EXISTS operator FROM table_name WHERE EXIST (SELECT AGE
is used to test for WHERE EXISTS (SELECT FROM CUSTOMERS WHERE
the existence of column_name SALARY > 6500);
any record in a FROM table_name ***********************
EXISTS subquery.
WHERE condition); SELECT * FROM CUSTOMERS
& ******************** WHERE NOT EXISTS (SELECT
NOT NOT EXISTS SELECT column_name(s) AGE FROM CUSTOMERS WHERE
EXISTS operator is used FROM table_name SALARY > 6500);
to test for the WHERE NOT EXISTS ***********************
non existence of SELECT * FROM CUSTOMERS
(SELECT column_name
any record in a WHERE NOT EXISTS (SELECT
FROM table_name
subquery. AGE FROM CUSTOMERS WHERE
WHERE condition); AGE > 35);

Gajendra Chourey Page 43

AS Keyword (Alias):
 The AS command is used to rename a column or table with an alias.

 An alias only exists for the duration of the query.

 You can rename a table or a column temporarily by giving another name known as alias. The
use of table aliases means to rename a table in a particular SQL statement. The renaming is a
temporary change and the actual table name does not change in the database.

 The column aliases are used to rename a table's columns for the purpose of a particular SQL
query.

Syntax: - The basic syntax of table alias is as follows:

SELECT column1, column2....

FROM table_name AS alias_name
WHERE [condition];

The basic syntax of column alias is as follows:

SELECT column_name AS alias_name

FROM table_name WHERE [condition];

Now, following is the usage of table alias:

SELECT [Link], [Link], [Link], [Link]

FROM CUSTOMERS AS C, ORDERS AS O
WHERE [Link] = O.CUSTOMER_ID;

Oracle:
SELECT [Link], [Link], [Link], [Link] FROM CUSTOMERS C, ORDERS O WHERE [Link] =
[Link];

This would produce the following result:

ID NAME AGE AMOUNT
3 Kaushik 23 3000
3 Kaushik 23 1500
2 Suresh 25 1560
4 Chaitali 25 2060

Following is the usage of column alias:

SELECT ID AS CUSTOMER_ID, NAME AS CUSTOMER_NAME

FROM CUSTOMERS
WHERE SALARY IS NOT NULL;

This would produce the following result:

CUSTOMER_ID CUSTOMER_NAME

Gajendra Chourey Page 44

 1 Ramesh
2 Suresh
3 kaushik
4 Chaitali
5 Hardik
6 Komal
7 Muskan

 Alias for Columns: - The following SQL statement creates two aliases, one for the CustomerID
column and one for the CustomerName column:

Example: - SELECT CustomerID AS ID, CustomerName AS Customer FROM

Customers;

 The following SQL statement creates two aliases. Notice that it requires double quotation marks or
square brackets if the alias name contains spaces:

Example: - SELECT CustomerName AS Customer, ContactName AS [Contact

Person] FROM Customers;

 The following SQL statement creates an alias named "Address" that combine four columns
(Address, PostalCode, City and Country):

Example: - SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City
+ ', ' + Country AS Address FROM Customers;

Example (Oracle): - SELECT Name||' '|| Age ||' '|| Salary AS Customer_table
FROM Customers;

 Note: To get the SQL statement above to work in MySQL use the following:

Example: - SELECT CustomerName, CONCAT(Address,', ',PostalCode,',

',City,', ',Country) AS Address FROM Customers;

Example (Oracle): - SELECT CONCAT(Name, Age) AS Customer_table FROM

Customers;

 Alias for Tables: - The following SQL statement selects all the orders from the customer with
CustomerID=4 (Around the Horn). We use the "Customers" and "Orders" tables, and give them the
table aliases of "c" and "o" respectively (Here we use aliases to make the SQL shorter):

Example: - SELECT [Link], [Link], [Link] FROM Customers

AS c, Orders AS o WHERE [Link]="Around the Horn" AND
[Link]=[Link];

Gajendra Chourey Page 45

JOIN
SQL Joins clause is used to combine records from two or more tables in a database.
A JOIN is a means for combining fields from two tables by using values common to each.

Consider the following two tables

(a) CUSTOMERS table is as follows: (b) Another table is ORDERS as follows:
ID NAME AGE ADDRESS SALARY OID DATE CUSTOMER_ID AMOUNT
1 Ramesh 32 Ahmedabad 2000.00 102 2009-10-08 3 3000
2 Suresh 25 Delhi 1500.00 100 2009-10-08 3 1500
3 Kaushik 23 Kota 2000.00 101 2009-11-20 2 1560
4 Chaitali 25 Mumbai 6500.00 103 2008-05-20 4 2060
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muskan 24 Indore 10000.00

Let us join these two tables in our SELECT statement as follows:

SELECT ID, NAME, AGE, AMOUNT

FROM CUSTOMERS, ORDERS
WHERE [Link] = ORDERS.CUSTOMER_ID;

This would produce the following result:

ID NAME AGE AMOUNT

3 Kaushik 23 3000
3 Kaushik 23 1500
2 Suresh 25 1560
4 Chaitali 25 2060

INNER JOIN
The most frequently used and important of the joins is the INNER JOIN. They are also referred to
as an EQUIJOIN.
The INNER JOIN creates a new result table by combining column values of two tables (table1
and table2) based upon the join-predicate. The query compares each row of table1 with each
row of table2 to find all pairs of rows which satisfy the join- predicate. When the join-predicate is
satisfied, column values for each matched pair of rows of A and B are combined into a result
row.

Syntax:

SELECT table1.column1, table2.column2...

FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;

Example:

Gajendra Chourey Page 46

 SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
INNER JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID;

ID NAME AMOUNT DATE

3 Kaushik 3000 2009-10-08
3 Kaushik 1500 2009-10-08
2 Khilan 1560 2009-11-20
4 Chaitali 2060 2008-05-20

LEFT JOIN
The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right
table. This means that if the ON clause matches 0 (zero) records in right table, the join will still
return a row in the result, but with NULL in each column from right table.
This means that a left join returns all the values from the left table, plus matched values from
the right table or NULL in case of no matching join predicate.

Syntax: -

SELECT table1.column1, table2.column2...

FROM table1 LEFT JOIN table2
ON table1.common_filed = table2.common_field;

Example: -

SELECT ID, NAME, AMOUNT, DATE

FROM CUSTOMERS LEFT JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID;

ID NAME AMOUNT DATE

1 Ramesh NULL NULL
2 Khilan 1560 2009-11-20
3 Kaushik 3000 2009-10-08
3 Kaushik 1500 2009-10-08
4 Chaitali 2060 2008-05-20
5 Hardik NULL NULL
6 Komal NULL NULL
7 Muskan NULL NULL

RIGHT JOIN
The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the
left table. This means that if the ON clause matches 0 (zero) records in left table, the join will still
return a row in the result, but with NULL in each column from left table.
This means that a right join returns all the values from the right table, plus matched values from
the left table or NULL in case of no matching join predicate.

Gajendra Chourey Page 47

 Syntax: -

SELECT table1.column1, table2.column2...

FROM table1
RIGHT JOIN table2
ON table1.common_filed = table2.common_field;
Example: -
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID;

ID NAME AMOUNT DATE

3 Kaushik 3000 2009-10-08 [Link]
3 Kaushik 1500 2009-10-08 [Link]
2 Khilan 1560 2009-11-20 [Link]
4 Chaitali 2060 2008-05-20 [Link]

FULL JOIN
The SQL FULL JOIN combines the results of both left and right outer joins.
The joined table will contain all records from both tables, and fill in NULLs for missing matches
on either side.

Syntax: -
SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_filed = table2.common_field;
Example: -
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
FULL JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID;

ID NAME AMOUNT DATE

1 Ramesh NULL NULL
2 Khilan 1560 2009-11-20
3 Kaushik 3000 2009-10-08
3 Kaushik 1500 2009-10-08
4 Chaitali 2060 2008-05-20
5 Hardik NULL NULL
6 Komal NULL NULL
7 Muskan NULL NULL
3 Kaushik 3000 2009-10-08
3 Kaushik 1500 2009-10-08
2 Khilan 1560 2009-11-20
4 Chaitali 2060 2008-05-20

Gajendra Chourey Page 48

 If your Database does not support FULL JOIN like MySQL does not support
FULL JOIN, then you can use UNION ALL clause to combine two JOINS as
follows:

SELECT ID, NAME, AMOUNT, DATE

FROM CUSTOMERS
LEFT JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID
UNION ALL
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID;

SELF JOIN
The SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily
renaming at least one table in the SQL statement.

Syntax: -
SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_filed = b.common_field;

Example: -
SELECT [Link], [Link], [Link]
FROM CUSTOMERS a, CUSTOMERS b
WHERE [Link] < [Link];
Result:

Gajendra Chourey Page 49

CARTESIAN or CROSS JOIN:
The CARTESIAN JOIN or CROSS JOIN returns the cartesian product of the sets of records from
the two or more joined tables. Thus, it equates to an inner join where the join-condition always
evaluates to True or where the join-condition is absent from the statement.

Syntax: -
SELECT table1.column1, table2.column2...
FROM table1, table2 [, table3 ];

Example: -
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS, ORDERS;

Result: -
ID NAME AMOUNT DATE
1 Ramesh 3000 2009-10-08
1 Ramesh 1500 2009-10-08
1 Ramesh 1560 2009-11-20
1 Ramesh 2060 2008-05-20
2 Khilan 3000 2009-10-08
2 Khilan 1500 2009-10-08
2 Khilan 1560 2009-11-20

Gajendra Chourey Page 50

 2 Khilan 2060 2008-05-20
3 Kaushik 3000 2009-10-08
3 Kaushik 1500 2009-10-08
3 Kaushik 1560 2009-11-20
3 Kaushik 2060 2008-05-20
4 Chaitali 3000 2009-10-08
4 Chaitali 1500 2009-10-08
4 Chaitali 1560 2009-11-20
4 Chaitali 2060 2008-05-20
5 Hardik 3000 2009-10-08
5 Hardik 1500 2009-10-08
5 Hardik 1560 2009-11-20
5 Hardik 2060 2008-05-20
6 Komal 3000 2009-10-08
6 Komal 1500 2009-10-08
6 Komal 1560 2009-11-20
6 Komal 2060 2008-05-20
7 Muskan 3000 2009-10-08
7 Muskan 1500 2009-10-08
7 Muskan 1560 2009-11-20
7 Muskan 2060 2008-05-20

**************************************************************************

Union Clause
SQL UNION clause/operator is used to combine the results of two or more SELECT statements
without returning any duplicate rows.
To use UNION, each SELECT must have the same number of columns selected, the same number
of column expressions, the same data type, and have them in the same order, but they do not
have to be the same length.

Syntax:-

SELECT column1 [, column2 ]

FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition];
Example: -

SELECT ID, NAME, AMOUNT, DATE

FROM CUSTOMERS
LEFT JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID
UNION
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS

Gajendra Chourey Page 51

 ON [Link] = ORDERS.CUSTOMER_ID;

ID NAME AMOUNT DATE

1 Ramesh NULL NULL
2 Khilan 1560 2009-11-20
3 Kaushik 3000 2009-10-08
3 Kaushik 1500 2009-10-08
4 Chaitali 2060 2008-05-20
5 Hardik NULL NULL
6 Komal NULL NULL
7 Muskan NULL NULL

UNION ALL Clause

The UNION ALL operator is used to combine the results of two SELECT statements including
duplicate rows.
The same rules that apply to UNION apply to the UNION ALL operator.

Syntax:-
SELECT column1 [, column2 ] FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ] FROM table1 [, table2 ]
[WHERE condition];

Example:-
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
LEFT JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID UNION ALL
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
RIGHT JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID;

ID NAME AMOUNT DATE

1 Ramesh NULL NULL
2 Khilan 1560 2009-11-20
3 Kaushik 3000 2009-10-08
3 Kaushik 1500 2009-10-08
4 Chaitali 2060 2008-05-20
5 Hardik NULL NULL
6 Komal NULL NULL
7 Muskan NULL NULL
3 Kaushik 3000 2009-10-08
3 Kaushik 1500 2009-10-08
2 Khilan 1560 2009-11-20
4 Chaitali 2060 2008-05-20

INTERSECT Clause

Gajendra Chourey Page 52

 The SQL INTERSECT clause/operator is used to combine two SELECT statements, but returns
rows only from the first SELECT statement that are identical to a row in the second SELECT
statement. This means INTERSECT returns only common rows returned by the two SELECT
statements.
Just as with the UNION operator, the same rules apply when using the INTERSECT operator.
MySQL does not support INTERSECT operator.

Syntax: -
SELECT column1 [, column2 ]
FROM table1 [, table2 ] [WHERE condition]
INTERSECT
SELECT column1 [, column2 ]
FROM table1 [, table2 ] [WHERE condition];
Example: -

SELECT ID, NAME, AMOUNT, DATE

FROM CUSTOMERS LEFT JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID
INTERSECT
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS RIGHT JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID;

ID NAME AMOUNT DATE

3 Kaushik 3000 2009-10-08
3 Kaushik 1500 2009-10-08
2 Ramesh 1560 2009-11-20
4 Kaushik 2060 2008-05-20

EXCEPT Clause
The SQL EXCEPT clause/operator is used to combine two SELECT statements and returns rows
from the first SELECT statement that are not returned by the second SELECT statement. This
means EXCEPT returns only rows, which are not available in second SELECT statement.
Just as with the UNION operator, the same rules apply when using the EXCEPT operator.
MySQL does not support EXCEPT operator.

Syntax: -
SELECT column1 [, column2 ]
FROM table1 [, table2 ][WHERE
condition]
EXCEPT
SELECT column1 [, column2 ]

Gajendra Chourey Page 53

 FROM table1 [, table2 ][WHERE
condition];
Example: -

SELECT ID, NAME, AMOUNT, DATE

FROM CUSTOMERS LEFT JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID
EXCEPT
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS RIGHT JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID;

ID NAME AMOUNT DATE

1 Ramesh NULL NULL
5 Hardik NULL NULL
6 Komal NULL NULL
7 Muskan NULL NULL

Gajendra Chourey Page 54

************************************************************************

Query to display the Current Date.

Query:

o Select SYSDATE From dual;

o Select TO_CHAR(SYSDATE,'YYYY-MM-DD') From dual;
o Select TO_CHAR(SYSDATE,'DL') From dual;
o Select TO_CHAR(SYSDATE,'DL','NLS_DATE_LANGUAGE = HINDI')
From dual;
Note:-
 The Oracle To_CHAR() function converts a DATE or INTERVAL value to a string in
a specified date format. The Oracle To_CHAR() is very useful for formatting the
internal date data returned by a query in a specific date format.
 In Oracle, To_CHAR function converts a datetime value(DATE,TIMESTAMP
datatype i.e) to a string using the specified format. In SQL Server, you can uyse
CONVERT or CAST functions to covert a datetime value(DATE,TIMESTAMP
datatype i.e) to a string.
 To_CHAR(n) converts n to a value of VARCHAR2 datatype. A VARCHAR2 value
exactly long enough to hold its significant digits.

************************************************************************

 You can change the default Date format of oracle from “DD-MM-YYYY” to
something you like by issuing the following command:

Syntax: Alter session set NLS_DATE_FORMAT=<’my_format’>;

Example: Alter session set NLS_DATE_FORMAT="YYYY/MM/DD";

This change applied for current session only.

************************************************************************

Gajendra Chourey Page 55

Function in SQL

Important String Functions in SQL

 SQL string functions are used primarily for string manipulation.

Initcap()

 The initcap() function converts the first letter of each word in a string to upper case, and
converts any remaining characters in each word to lowercase.

Query: SELECT initcap(ename) FROM emp21;

Output:

UPPER(str)/UCASE(str)

 UCASE () is a synonym for UPPER ().

 Returns the string str with all characters changed to uppercase according to the current
character set mapping.

Example 1: SELECT UPPER('abvu') from dual;

Example 2: SELECT UPPER(ename) FROM emp21;

Output:

LOWER(str)/ LCASE(str)

 LCASE () is a synonym for LOWER ().

 Returns the string str with all characters changed to lowercase according to the current
character set mapping.
Gajendra Chourey Page 56
 Example 1: SELECT LOWER('QUADRATICALLY') from dual;

LOWER('QUADRATICALLY')
quadratically

Example21: SELECT lower(ename) from employee where eno='E33';

LENGTH(str)

 Returns the length of the string str measured in bytes. A multi-byte character counts as
multiple bytes. This means that for a string containing five two-byte characters,
LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

Example 1: SELECT LENGTH(‘text’) from dual;

LENGTH(‘text’)
4
Example 2:
SELECT LENGTH(ename) from employee where eno='E33';

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)/ TRIM([remstr FROM] str)

 Returns the string str with all remstr prefixes or suffixes removed. If none of the
specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional
and, if not specified, spaces are removed.

Example: SELECT TRIM(' bar ') from dual;

TRIM(' bar ')

bar

Example: SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx') from dual;

TRIM(LEADING 'x' FROM 'xxxbarxxx')

barxxx

Example: SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx') from dual;

TRIM(BOTH 'x' FROM 'xxxbarxxx')

bar

Example: SELECT TRIM(TRAILING 'z' FROM 'barxxyz');

TRIM(TRAILING 'z' FROM 'barxxyz')

barxxy

RTRIM(str)

 Returns the string str with trailing space characters removed.

Example: SELECT RTRIM('barbar ') from dual;

Gajendra Chourey Page 57

 RTRIM('barbar ')
barbar

LTRIM(str)

 Returns the string str with leading space characters removed.

Example: SELECT LTRIM(' barbar') from dual;

LTRIM(' barbar')
barbar

LPAD(str,len,padstr)
 Returns the string str, left-padded with the string pad str to a length of len characters. If
str is longer than len, the return value is shortened to len characters.

Example: SELECT LPAD('hi',4,'??') from dual;

LPAD('hi',4,'??')
??hi

RPAD(str,len,padstr)
 Returns the string str, right-padded with the string pad str to a length of len characters.
If str is longer than len, the return value is shortened to len characters.

Example: SELECT RPAD('hi',5,'?') from dual;

RPAD('hi',5,'?')
hi???

CONCAT (str1,str2)
 Returns the string that results from concatenating the arguments. May have one or more
arguments. If all arguments are non-binary strings, the result is a non-binary string. If
the arguments include any binary strings, the result is a binary string. A numeric
argument is converted to its equivalent binary string form; if you want to avoid that, you
can use an explicit type cast, as in this:

Example 1: SELECT CONCAT('S', 'QL') from dual;

CONCAT('S', 'QL')
MySQL

Example 2: SELECT CONCAT(ename, hire_date) FROM employee;

REPLACE(str,from_str,to_str)
Returns the string str with all occurrences of the string from_str replaced by the
string to_str. REPLACE() performs a case-sensitive match when searching for
from_str.

Gajendra Chourey Page 58

 Example: SELECT REPLACE('[Link]', 'w', 'Ww') from dual;

REPLACE('[Link]', 'w','Ww')
[Link]

REVERSE(str)
 Returns the string str with the order of the characters reversed.

Example: SELECT REVERSE('abcd') from dual;

REVERSE('abcd')
dcba

ASCII(str)
Returns the numeric value of the leftmost character of the string str. Returns 0 if str is
the empty string. Returns NULL if str is NULL. ASCII() works for characters with
numeric values from 0 to 255.

Example: SELECT ASCII('2') FROM DUAL;

ASCII('2')
50

************************************************************************

SQL Numeric Functions:

 SQL numeric functions are used primarily for numeric manipulation and/or
mathematical calculations.

ABS(X)
The ABS() function returns the absolute value of X. Consider the following

Example: SELECT ABS(-2) from dual;

Example: SELECT ABS(7.56) from dual;

SQRT(X)
This function returns the non-negative square root of X. Consider the following
example:

SELECT SQRT(49) from dual;

Gajendra Chourey Page 59

POW(X,Y) / POWER(X,Y)
 These two functions return the value of X raised to the power of Y.
Example: SELECT POWER(3,2) from dual;

CEIL(X) / CEILING(X)
These functions return the smallest integer value that is not smaller than X. Consider
the following

Example: SELECT CEIL(3.46) from dual;

Example: SELECT CEIL(-3.46) from dual;

FLOOR(X)
This function returns the largest integer value that is not greater than X.

Example: SELECT FLOOR(3.46) FROM DUAL;

Example: SELECT FLOOR(-3.46) FROM DUAL;

ROUND(X) / ROUND(X, D)
 This function returns X rounded to the nearest integer. If a second argument, D, is
supplied, then the function returns X rounded to D decimal places. D must be positive or
all digits to the right of the decimal point will be removed. Consider the following
example:
SELECT ROUND(3.46796) from dual;

SELECT ROUND(3.6796) from dual;

Gajendra Chourey Page 60

 SELECT ROUND(3.46796,2) from dual;

SIGN(X)
 This function returns the sign of X (negative, zero, or positive) as -1, 0, or 1.
SELECT SIGN(-3.46) from dual;

SELECT SIGN(0.46) from dual;

SELECT SIGN(0) from dual;

STD(expression) / STDDEV(expression)
 The STD() function is used to return the standard deviation of expression. This is
equivalent to taking the square root of the VARIANCE() of expression. The following
example computes the standard deviation of the PRICE column in our CARS table:

SQL> SELECT STDDEV(PRICE) STD_DEVIATION FROM CARS;

GREATEST(n1,n2,n3,..........)
 The GREATEST() function returns the greatest value in the set of input parameters (n1,
n2, n3, a nd so on). The following example uses the GREATEST() function to return the
largest number from a set of numeric values:

Example: SELECT GREATEST(3,5,1,8,33,99,34,55,67,43) FROM DUAL;

LEAST(N1,N2,N3,N4,......)
The LEAST() function is the opposite of the GREATEST() function. Its purpose is to
return the least-valued item from the value list (N1, N2, N3, and so on). The following
example shows the proper usage and output for the LEAST() function:

Gajendra Chourey Page 61

 Example: SELECT LEAST(3,5,1,8,33,99,34,55,67,43) FROM DUAL;

EXP(X)
 This function returns the value of e (the base of the natural logarithm) raised to the
power of X.

Example: SELECT EXP(3) FROM DUAL;

LOG(B,X)
 The single argument version of the function will return the natural logarithm of X. If it is
called with two arguments, it returns the logarithm of X for an arbitrary base B. Consider
the following example:
SELECT LOG(2,45) FROM DUAL;

MOD(N,M)
This function returns the remainder of N divided by M. Consider the following
example:
SELECT MOD(29,3) FROM DUAL;

************************************************************************

Important Date & Time Functions in SQL

SYSDATE

 Return the current system date and time of the operating system where the Oracle
Database resides.

Example: SELECT SYSDATE FROM DUAL;

CURRENT_DATE
 Return the current date and time in the session time zone.

Example: SELECT CURRENT_DATE FROM dual;

CURRENT_TIMESTAMP
Return the current date and time with time zone in the session time zone.

Example: SELECT CURRENT_TIMESTAMP FROM dual;

Gajendra Chourey Page 62

DBTIMEZONE
 Get the current database time zone.

Example: SELECT DBTIMEZONE FROM dual;

ADD_MONTHS
 Add a number of months (n) to a date and return the same day which is n of months
away.

Example: SELECT ADD_MONTHS( DATE '2021-06-30', 1) FROM DUAL;

Result:

EXTRACT
 Extract a value of a date time field e.g., YEAR, MONTH, DAY, … from a date time value.

Example: EXTRACT(YEAR FROM SYSDATE)

FROM_TZ
 Convert a timestamp and a time zone to a TIMESTAMP WITH TIME ZONE value.

Example: SELECT FROM_TZ(TIMESTAMP '2021-08-08 [Link]', '-09:00') FROM DUAL;

Result:

LAST_DAY
 Gets the last day of the month of a specified date.

Example: SELECT LAST_DAY(DATE '2021-02-01') FROM DUAL;

Result:

LOCALTIMESTAMP
 Return a TIMESTAMP value that represents the current date and time in the session time
zone.

Example: SELECT LOCALTIMESTAMP FROM dual;

MONTHS_BETWEEN

Gajendra Chourey Page 63

 Return the number of months between two dates.

Example:
SELECT MONTHS_BETWEEN( DATE '2021-07-01', DATE '2021-01-01' )FROM DUAL;
Result:

NEW_TIME
 Convert a date in one time zone to another.

Example: SELECT NEW_TIME(TO_DATE( '08-07-2021 [Link]', 'MM-DD-YYYY

HH24:MI:SS' ), 'AST', 'PST') FROM DUAL;
Result:

NEXT_DAY
 Get the first weekday that is later than a specified date.

Example: NEXT_DAY( DATE '2021-01-01', 'SUNDAY' )

Result:

ROUND
 Return a date rounded to a specific unit of measure.

Example: SELECT ROUND(DATE '2021- 07-16', 'MM') FROM DUAL;

Result:

SESSIONTIMEZONE
 Get the session time zone.

Example: SELECT SESSIONTIMEZONE FROM dual;

SYSTIMESTAMP
 Return the system date and time that includes fractional seconds and time zone.

Example: SELECT SYSTIMESTAMP FROM dual;

TO_CHAR
 Convert a DATE or an INTERVAL value to a character string in a specified format.

Gajendra Chourey Page 64

 Example: SELECT TO_CHAR( DATE'2021-01-01', 'DL' ) FROM DUAL;
Result:

TO_DATE
 Convert a date which is in the character string to a DATE value.

Example: SELECT TO_DATE( '01 Jan 2021', 'DD MON YYYY' ) FROM DUAL;
Result:

TRUNC
 Return a date truncated to a specific unit of measure.
Example: SELECT TRUNC(DATE '2017-07-16', 'MM') FROM DUAL;
Result:

TZ_OFFSET
 Get time zone offset of a time zone name from UTC.
Syntax: TZ_OFFSET('timezone')

Example: SELECT TZ_OFFSET('Europe/London') FROM DUAL;

 To find more time zone values, you can run the following query:
SELECT DISTINCT tzname FROM V$TIMEZONE_NAMES;

************************************************************************

Gajendra Chourey Page 65

SQL general functions:
These functions work with any data type and pertain to the use of null values in the
expression list. These are all single row function i.e. provide one result per row.

1. NVL(expr1, expr2):
o In SQL, NVL() converts a null value to an actual value.
o Data types that can be used are date, character and number.
o Data type must match with each other i.e. expr1 and expr2 must of same
data type.

Syntax – NVL (expr1, expr2)

expr1 is the source value or expression that may contain a null.

expr2 is the target value for converting the null.

Example: SELECT salary, NVL(commission, 0), (salary*12) +

(salary*12*NVL(commission, 0)) annual_salary FROM employee;

2. NVL2 (expr1, expr2, expr3):

o The NVL2 function examines the first expression. If the first expression is
not null, then the NVL2 function returns the second expression. If the first
expression is null, then the third expression is returned i.e., If expr1 is not
null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. The
argument expr1 can have any data type.

Syntax – NVL2 (expr1, expr2, expr3)

Gajendra Chourey Page 66

 expr1 is the source value or expression that may contain null
expr2 is the value returned if expr1 is not null
expr3 is the value returned if expr1 is null

Example: SELECT Ename, salary, commission, NVL2(COMMISSION, 'SALARY +

COMMISSION', 'SALARY') income FROM employee;

3. DECODE():
o Facilitates conditional inquiries by doing the work of a CASE or IF-THEN-
ELSE statement.
o The DECODE function decodes an expression in a way similar to the IF-
THEN-ELSE logic used in various languages.
o The DECODE function decodes expression after comparing it to each search
value. If the expression is the same as search, result is returned.
o If the default value is omitted, a null value is returned where a search
value does not match any of the result values.

Syntax: DECODE(col|expression, search1, result1 [, search2,

result2,...,][, default]);

Example: SELECT Ename, job_type, salary, DECODE(job_type, 'Manager',

1.10*salary, 'Analyst', 1.15*salary, 'clerk', 1.20*salary,
salary) REVISED_SALARY FROM employee;

Gajendra Chourey Page 67

4. COALESCE():
o The COALESCE() function examines the first expression, if the first
expression is not null, it returns that expression; Otherwise, it does a
COALESCE of the remaining expressions.
o The advantage of the COALESCE() function over the NVL() function is that
the COALESCE function can take multiple alternate values. In simple words
COALESCE() function returns the first non-null expression in the list.

Syntax: COALESCE (expr_1, expr_2, ... expr_n);

Examples: SELECT ename, COALESCE(commission, salary, 10) comm FROM

employee ORDER BY commission;

Gajendra Chourey Page 68

5. NULLIF() :
o The NULLIF function compares two expressions. If they are equal, the
function returns null. If they are not equal, the function returns the first
expression. You cannot specify the literal NULL for first expression.

Syntax: NULLIF (expr_1, expr_2);

Examples: SELECT LENGTH(ename) "expr1", LENGTH(job_type) "expr2",

NULLIF(LENGTH(ename),LENGTH(job_type)) result FROM
employee;

6. LNNVL():
o LNNVL evaluate a condition when one or both operands of the condition
may be null.
o The function can be used only in the WHERE clause of a query. It takes as
an argument a condition and returns TRUE if the condition is FALSE or
UNKNOWN and FALSE if the condition is TRUE.

Syntax: LNNVL( condition(s) );

Examples: SELECT COUNT(*) FROM employees WHERE commission < 500.00;

o Now the above examples does not considered those employees who have no
commission at all.
o To include them as well we use LNNVL()
Gajendra Chourey Page 69
 SELECT COUNT(*) FROM employee WHERE LNNVL(commission >= 500.00);

7. NANVL() :
o The NANVL function is useful only for floating-point numbers of type
BINARY_FLOAT or BINARY_DOUBLE.
o It instructs the Database to return an alternative value n2 if the input
value n1 is NaN (not a number). If n1 is not NaN, then database returns n1.
This function is useful for mapping NaN values to NULL.

Syntax: NANVL( n1 , n2 );

Consider the following table named nanvl_demo :

Example:
SELECT bin_float, NANVL(bin_float,0) FROM nanvl_demo;
Output:

Gajendra Chourey Page 70

Database Anomalies:
Anomalies are problems that can occur in poorly planned, un-normalised databases where all
the data is stored in one table.

Cause of Anomalies:
Anomalies are caused when there is too much redundancy in the database’s information.
Anomalies can often be caused when the tables that make up the database suffer from poor
construction.

 Insertion Anomaly:
o An insertion anomaly is the inability to add data to the database due to absence of other
data.
o An insertion/Insert Anomaly occurs when certain attributes cannot be inserted into the
database without the presence of other attributes.
o For example, assume in Student table, Enrolment_Number column is defined as Not
NULL. If a new student is admitted but not immediately assigned enrolment number
then this student could not be entered into the database.

 Deletion Anomaly:
o A deletion anomaly occurs when you delete record that may contain attributes that
shouldn’t be deleted.
o A deletion anomaly is the unintended loss of data due to deletion of other data.

 Update Anomaly:
o An update anomaly is a data inconsistency that results from data redundancy and a
partial update.

**************************************************************************************

Gajendra Chourey Page 71

 Unit - IV
**************************************************************************
Database Objects: View, Synonyms, Index; Transaction Control Statements: Commit,
Rollback, Savepoint.
**************************************************************************

View Definition and use: -

 View is nothing more than a SQL statement that is stored in the database with an associated name.
A view is actually a composition of a table in the form of a predefined SQL query.
 A view can contain all rows of a table or select rows from a table. A view can be created from one or
many tables which depend on the written SQL query to create a view.

Views, which are kind of virtual tables, allow users to do the following:
 Structure data in a way that users or classes of users find natural or intuitive.
 Restrict access to the data such that a user can see and (sometimes) modify exactly what they need
and no more.
 Summarize data from various tables which can be used to generate reports.

Creating View: -
Database views are created using the CREATE VIEW statement. Views can be
created from a single table, multiple tables, or another view.
To create a view, a user must have the appropriate system privilege according to the specific
implementation.

The basic CREATE VIEW syntax is as follows:

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

You can include multiple tables in your SELECT statement in very similar way as you use them in normal SQL
SELECT query.
Example: -
CUSTOMERS
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muskan 24 Indore 10000.00

Now, following is the example to create a view from CUSTOMERS table. This view would be used to
have customer name and age from CUSTOMERS table:
Example:
CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age FROM CUSTOMERS;

Gajendra Chourey Page 83

Now, you can query CUSTOMERS_VIEW in similar way as you query an actual table. Following is the
Example:
SELECT * FROM CUSTOMERS_VIEW;

This would produce the following result:

ID NAME
1 Ramesh
2 Khilan
3 Kaushik
4 Chaitali
5 Hardik
6 Komal
7 Muskan

The WITH CHECK OPTION: -

 The WITH CHECK OPTION is a CREATE VIEW statement option. The purpose of the WITH CHECK
OPTION is to ensure that all UPDATE and INSERTs satisfy the condition(s) in the view definition.
 If they do not satisfy the condition(s), the UPDATE or INSERT returns an error.

The following is an example of creating same view CUSTOMERS_VIEW with the WITH CHECK OPTION:
CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM CUSTOMERS
WHERE age IS NOT NULL
WITH CHECK OPTION;

The WITH CHECK OPTION in this case should deny the entry of any NULL values in the view's AGE
column, because the view is defined by data that does not have a NULL value in the AGE column.

Updating a View: A view can be updated under certain conditions:

 The SELECT clause may not contain the keyword DISTINCT.
 The SELECT clause may not contain summary (aggregate) functions.
 The SELECT clause may not contain set functions.
 The SELECT clause may not contain set operators.
 The SELECT clause may not contain an ORDER BY clause.
 The FROM clause may not contain multiple tables.
 The WHERE clause may not contain sub-queries.
 The query may not contain GROUP BY or HAVING.
 Calculated columns may not be updated.
 All NOT NULL columns from the base table must be included in the view in
order for the INSERT query to function.
So if a view satisfies all the above mentioned rules then you can update a view.

Following is an example to update the age of Ramesh:

UPDATE CUSTOMERS_VIEW SET AGE = 35 WHERE name='Ramesh';

Gajendra Chourey Page 84

This would ultimately update the base table CUSTOMERS and same would reflect in the view itself.
Now, try to query base table, and SELECT statement would produce the following result:

CUSTOMERS
ID NAME AGE ADDRESS SALARY
1 Ramesh 35 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muskan 24 Indore 10000.00

Inserting Rows into a View:

Rows of data can be inserted into a view. The same rules that apply to the UPDATE command
also apply to the INSERT command.
Here, we cannot insert rows in CUSTOMERS_VIEW because we have not included all the NOT
NULL columns in this view, otherwise you can insert rows in a view in similar way as you insert
them in a table.

Deleting Rows into a View:

Rows of data can be deleted from a view. The same rules that apply to the UPDATE and INSERT
commands apply to the DELETE command.

Following is an example to delete a record having AGE= 22.

DELETE FROM CUSTOMERS_VIEW WHERE age = 22;

This would ultimately delete a row from the base table CUSTOMERS and same would reflect in
the view itself. Now, try to query base table, and SELECT statement would produce the
following:

Result:
CUSTOMERS
ID NAME AGE ADDRESS SALARY
1 Ramesh 35 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
7 Muskan 24 Indore 10000.00
Dropping Views:
Obviously, where you have a view, you need a way to drop the view if it is no longer
needed. The syntax is very simple as given below:

DROP VIEW view_name;

Following is an example to drop CUSTOMERS_VIEW from CUSTOMERS table:

DROP VIEW CUSTOMERS_VIEW;

*********************************************************************************

Gajendra Chourey Page 85

Synonyms
 Oracle SQL / PLSQL uses synonym as an alias for any database object such as tables, views,
sequences, stored procedures, and other database object. In other words we can say that in
Oracle SQL / PLSQL a synonym is an alternative name for database objects.
 A synonym provides you with many benefits if you use it properly.

Oracle SQL / PLSQL syntax to create or replace a synonym is:

CREATE [OR REPLACE] [PUBLIC] SYNONYM [SCHEMA.] synonym_name

FOR [SCHEMA.] object_name;

 The OR REPLACE phrase in the PL-SQL create synonym syntax above allows us to replace or
recreate any PL-SQL synonym (if already existing) for the same database object and without
using the drop command.

 The PUBLIC phrase in the above PL-SQL create synonym syntax means that the synonym will be
accessible for all the valid users, but the user must have the sufficient privileges for the object to
use its synonym.

 The SCHEMA phrase in the PL-SQL create synonym is the name of the schema where the
synonym will reside. It is an optional phrase, if omitted; oracle PL-SQL creates the synonym in
the current schema.

 The SYNONYM_NAME in the above PL-SQL create synonym syntax is the name of the synonym.

 The OBJECT_NAME in the above PL-SQL create synonym syntax is the name of the database
object for which the synonym is to be created.

Example: Create a synonym for ‘employee’ table

CREATE PUBLIC SYNONYM employee_syn FOR employee;

Once we run the above PL-SQL CREATE SYNONYM command we can fetch the records of the
employee table as:

SELECT * FROM employee_syn;

If the ‘employee_syn’ synonym already existed we can replace the synonym using the CREATE
OR REPLACE SYNONYM as:

CREATE OR REPLACE PUBLIC SYNONYM employee_syn FOR employee;

Drop a PL-SQL synonym

 Oracle SQL / PL-SQL allow us to drop a synonym that we have created earlier.

Gajendra Chourey Page 86

 The syntax to drop a PL-SQL synonym is:

DROP [PUBLIC] SYNONYM [SCHEMA.] synonym_name [FORCE];

 The PUBLIC phrase in the above oracle PL-SQL drop synonym syntax allows us to drop a
public synonym, if we have specified public then we don’t have to specify a schema for the
PL-SQL synonym.
 The FORCE phrase in the above oracle PL-SQL drop synonym syntax will drop the synonym
even if there are dependencies on it.

Example:

DROP PUBLIC SYNONYM employee_syn;

The above PL-SQL drop synonym command will drop the synonym ‘employee_syn’ from
the database.

*********************************************************************************

Index
 Indexes are special lookup tables that the database search engine can use to speed up data
retrieval. Simply put, an index is a pointer to data in a table.
 An index in a database is very similar to an index in the back of a book. For example, if
you want to reference all pages in a book that discuss a certain topic, you first refer to the
index, which lists all topics alphabetically and are then referred to one or more specific
page numbers.
 An index helps speed up SELECT queries and WHERE clauses, but it slows down data
input, with UPDATE and INSERT statements.
 Indexes can be created or dropped with no effect on the data.
 Creating an index involves the CREATE INDEX statement, which allows you to name the
index, to specify the table and which column or columns to index, and to indicate whether
the index is in ascending or descending order.
 Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents
duplicate entries in the column or combination of columns on which there's an index.

When should indexes be avoided?

Although indexes are intended to enhance a database's performance, there are times
when they should be avoided.
The following guidelines indicate when the use of an index should be reconsidered:

 Indexes should not be used on small tables.

Gajendra Chourey Page 87

  Tables that have frequent, large batch update or insert operations.

 Indexes should not be used on columns that contain a high number of NULL
values.

 Columns that are frequently manipulated should not be indexed.

CREATE & DROP INDEX

The basic syntax of CREATE INDEX is as follows:

CREATE INDEX index_name ON table_name;

 Single-Column Indexes:
 A single-column index is one that is created based on only one table column.
The basic syntax is as follows:

CREATE INDEX index_name ON table_name (column_name);

 Unique Indexes:
 Unique indexes are used not only for performance, but also for data integrity. A
unique index does not allow any duplicate values to be inserted into the table.
The basic syntax is as follows:

CREATE INDEX index_name on table_name (column_name);

 Composite Indexes:
 A composite index is an index on two or more columns of a table.
The basic syntax is as follows:

CREATE INDEX index_name on table_name (column1, column2);

 Whether to create a single-column index or a composite index, take into consideration the
column(s) that you may use very frequently in a query's WHERE clause as filter
conditions.
 Should there be only one column used, a single-column index should be the choice. Should
there be two or more columns that are frequently used in the WHERE clause as filters, the
composite index would be the best choice.

 Implicit Indexes:
 Implicit indexes are indexes that are automatically created by the database
server when an object is created.

Gajendra Chourey Page 88

  Indexes are automatically created for primary key constraints and unique
constraints.

 The DROP INDEX Command:

 An index can be dropped using SQL DROP command.
 Care should be taken when dropping an index because performance may be
slowed or improved.
The basic syntax is as follows:
DROP INDEX index_name;

*********************************************************************************

Transaction Control Statements:

COMMIT Command:
 The COMMIT command is the transactional command used to save changes invoked by a
transaction to the database.
 The COMMIT command saves all transactions to the database since the last COMMIT or
ROLLBACK command.

Syntax: - COMMIT;

Example: - Following is the example, which would delete records from the table having age = 25 and
then COMMIT the changes in the database.

SQL> DELETE FROM CUSTOMERS WHERE AGE = 25;

SQL> COMMIT;
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
3 Kaushik 23 Kota 2000.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muskan 24 Indore 10000.00

ROLLBACK Command:
 The ROLLBACK command is the transactional command used to undo transactions that have not
already been saved to the database.
 The ROLLBACK command can only be used to undo transactions since the last COMMIT or
ROLLBACK command was issued.

Syntax: - ROLLBACK;

Example: - Following is the example, which would delete records from the table having age = 25
and then ROLLBACK the changes in the database.

Gajendra Chourey Page 89

 SQL> DELETE FROM CUSTOMERS WHERE AGE = 25;
SQL> ROLLBACK;
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Ramesh 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Kaushik 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muskan 24 Indore 10000.00

SAVEPOINT Command: -
 A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point
without rolling back the entire transaction.
Syntax: - SAVEPOINT SAVEPOINT_NAME;
 This command serves only in the creation of a SAVEPOINT among transactional statements. The
ROLLBACK command is used to undo a group of transactions.

Syntax: - ROLLBACK TO SAVEPOINT_NAME;

Example: - Following is an example where you plan to delete the three different records from
the CUSTOMERS table. You want to create a SAVEPOINT before each delete, so that you can
ROLLBACK to any SAVEPOINT at any time to return the appropriate data to its original state:

SQL> SAVEPOINT SP1;

Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=1;
1 row deleted.
SQL> SAVEPOINT SP2;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=2;
1 row deleted.
SQL> SAVEPOINT SP3;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=3;
1 row deleted.

Now that the three deletions have taken place, say you have changed your mind and decided
to ROLLBACK to the SAVEPOINT that you identified as SP2. Because SP2 was created after the
first deletion, the last two deletions are undone:

SQL> ROLLBACK TO SP2;

Rollback complete.
ID NAME AGE ADDRESS SALARY
2 Ramesh 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Kaushik 25 Mumbai 6500.00

Gajendra Chourey Page 90

 5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muskan 24 Indore 10000.00

RELEASE SAVEPOINT Command:

 The RELEASE SAVEPOINT command is used to remove a SAVEPOINT that you have created.

Syntax: - RELEASE SAVEPOINT SAVEPOINT_NAME;

 Once a SAVEPOINT has been released, you can no longer use the ROLLBACK command to undo
transactions performed since the SAVEPOINT.
*********************************************************
Creating a User

 The SYSTEM account is one of a handful of predefined administrative accounts generated

automatically when Oracle is installed.
 SYSTEM is capable of most administrative tasks, but the task we’re particularly interested in is
account management.
 Once connected as SYSTEM, simply issue the CREATE USER command to generate a new account.

Example:

CREATE USER books_admin IDENTIFIED BY MyPassword;

 Here we’re simply creating a books_admin account that is IDENTIFIED or authenticated by the
specified password.

GRANT Statement: -

 With our new books_admin account created, we can now begin adding privileges to the account using
the GRANT statement.
 GRANT is a very powerful statement with many possible options, but the core functionality is to
manage the privileges of both users and roles throughout the database.

 Providing Roles
 Typically, you’ll first want to assign privileges to the user through attaching the account to
various roles, starting with the CONNECT role:

GRANT CONNECT TO books_admin;

 In some cases to create a more powerful user, you may also consider adding
the RESOURCE role (allowing the user to create named types for custom schemas) or even
Gajendra Chourey Page 91
 the DBA role, which allows the user to not only create custom named types but alter and
destroy them as well.

GRANT CONNECT, RESOURCE, DBA TO books_admin;

 Assigning Privileges:

 Next you’ll want to ensure the user has privileges to actually connect to the database and
create a session using GRANT CREATE SESSION.

GRANT CREATE SESSION TO books_admin;

 We also need to ensure our new user has disk space allocated in the system to actually
create or modify tables and data, so we’ll GRANT TABLESPACE like so:

GRANT UNLIMITED TABLESPACE TO books_admin;

 Table Privileges:
 While not typically necessary in newer versions of Oracle, some older installations may
require that you manually specify the access rights the new user has to a specific schema
and database tables.
 For example, if we want our books_admin user to have the ability to perform SELECT,
UPDATE, INSERT, and DELETE capabilities on the books table, we might execute the
following GRANT statement:

Syntax: - GRANT <objectprivilege> | [ALL]

ON <objectname> TO <username> [WITH GRANT OPTION];

Here,
<objectprivilege>: ALTER, DELETE, INSERT, SELECT, UPDATE and ALL
<objectname>: Database Table name

Example: - GRANT SELECT, INSERT, UPDATE, DELETE

ON [Link]
TO books_admin;

 This ensures that books_admin can perform the four basic statements for the books table
that is part of the schema schema.

REVOKE: -

 The REVOKE command removes user access rights or privileges to the database objects.

Syntax: - REVOKE <objectprivilege> | [ALL]

ON <objectname> FROM <username>;

Gajendra Chourey Page 92

 Example: -
REVOKE SELECT ON Customers TO Ravi;

 This command will REVOKE a SELECT privilege on employee table from Ravi.

DROP USER: -

 Use the DROP USER statement to remove a database user.

DROP USER books_admin;

SET TRANSACTION Command:

 The SET TRANSACTION command can be used to initiate a database transaction. This command
is used to specify characteristics for the transaction that follows.
For example, you can specify a transaction to be read only or read write.
Syntax: -
SET TRANSACTION [ READ WRITE | READ ONLY ];

Example:
 The following statements could be run at midnight of the last day of every
month to count the products and quantities on hand in the Toronto warehouse
in the sample Order Entry (oe) schema. This report would not be affected by any
other user who might be adding or removing inventory to a different
warehouse.
COMMIT;

SET TRANSACTION READ ONLY NAME 'Toronto';

SELECT product_id, quantity_on_hand FROM inventories

WHERE warehouse_id = 5
ORDER BY product_id;

COMMIT;

Gajendra Chourey Page 93

 The first COMMIT statement ensures that SET TRANSACTION is the first
statement in the transaction. The last COMMIT statement does not actually
make permanent any changes to the database. It simply ends the read-only
transaction.

Note:-
 DELETE is used to remove existing records from the database. DELETE command is a DML statement so that it can
be rolled back.
 DROP is used to delete the whole table, including its structure. DROP is a DDL command that lost the data
permanently, and it cannot be rolled back.
 TRUNCATE is used to delete the whole records, but it preserves the table's schema or structure. TRUNCATE is a
DDL command, so it cannot be rolled back.

Gajendra Chourey Page 94