Structure Query Language (SQL).pptx

• SQL is a language that provides an interface to a relational
database system.
•SQL is developed by IBM in 1970s and it is a defacto
standard as well as ISO & ANSI standard
•SQL was also supports DML for insert, update & delete
operations and DDL for creating and modifying tables and
other database structures.

• SQL can be used by range of users, including
those with little or no programming experience
• It is a non procedural language
• It reduces the amount of time required for
creating and maintaining system
• It is a English like language.
Features of Structure Query Language (SQL)

• SQL starts with a verb “select”. This verb may have
additional adjective “from“
• Each verb is followed by number of clauses, eg: from,
where, having
• A space separates clauses. Eg: drop
• A comma (,) separates parameter without a clauses
• A ; is used to end SQL statement
• Identifiers can contain upto 30 character and must
start with a alphabetical character.
• Character and Date literals must be enclosed with in
a single cote(‘ ’)
• Comments can be enclosed between /* and */
symbols and may be multilne.
• Single line comment may be prefixed with a - symbol
Rules for Structure Query Language (SQL)

• Delimiters are symbols are compound symbols which
have a special meaning with in a SQL statement
SQL Delimiter
+ - * / Arithmetic Operators
=, <, >, <=, >= , !=, <>
^=
Relational Operators
:= Assignment Operators
, Item separators
; Terminator

DDL
It is a set of SQL commands used to create, modify and delete database
structure but not data. This commands are normally used by DBA
DML
It is a area of SQL that allows changing data with in the database
DCL
It is a component of the SQL statement that control access to data and to
the database. DCL Statements are grouped with DML statement.
TCL
It is a component of a SQL statement that allows getting data from the
database and imposing control over the transaction.
Components of SQL

• Create: to create object in the database
• Alter: alters the structure of the database
• Drop: delete objects from the database.
• Truncate: remove all records from a table
• Comment: add comments to the data dictionary
• Grant: gives users access privileges to database
• Revoke: withdraw access privileges given with the
grant command
Commands used in DDL

Commands used in DML
 Insert: insert data into a table
 Update: updates existing data within a table
 Delete: delete all records from a table. space for the
records remain.
 Call: call a PL/SQL or Java subprogramme

• Grant: Giving privileges to the authorized user
• Revoke: : withdraw access privileges given with the
grant command
DCL
• Commit: save work done
• Savepoint: identify a point in a transaction to
which you can later roll back
• Roll back: restore database to original since the
last.
TCL

Data type in SQL
Data types come in several forms and sizes, allowing the programmer to create
tables suited to the scope of the project.
Data type Description
CHAR(size) This data type is used to store character strings values of fixed
length. The size in brackets determines the number of
character the cell can hold. It can hold between 1 and 2000
bytes for the CHAR column width. The default is 1 byte.(up to
255 character)
VARCHAR(size)
(size)/
VARCHAR2(size)
The VARCHAR2 data type stores variable-length
alphanumeric data. When you create a table with
a VARCHAR2 column, you specify a maximum string length
(in bytes or characters) between 1 and 4000 bytes for
the VARCHAR2 column.

NUMBER(P,S) The NUMBER data type stores fixed and floating-point numbers.
Numbers of virtually any magnitude can be stored and are guaranteed
portable among different systems operating Oracle Database, up to 38
digits of precision.
For numeric columns, you can specify the column as:
column_name NUMBER
Optionally, you can also specify a precision (total number of digits)
and scale (number of digits to the right of the decimal point):
column_name NUMBER (precision, scale)
If a precision is not specified, the column stores values as given. If no
scale is specified, the scale is zero.
Cont…

DATE The DATE data type stores point-in-time values (dates and times) in a
table. The DATE date type stores the year (including the century), the
month, the day, the hours, the minutes, and the seconds (after
midnight).
Database can store dates in the Julian era, ranging from January 1, 4712
BC to December 31, 9999 CE (Common Era, or 'AD’), CE date entries are
the default.
Oracle Database uses its own internal format to store dates. Date data is
stored in fixed-length fields of seven bytes each, corresponding to
century, year, month, day, hour, minute, and second.
For input and output of dates, the standard Oracle date format is DD-
MON-YY, as '13-NOV-92'
You can change this default date format for an instance with the
parameter NLS_DATE_FORMAT. You can also change it during a user
session with the ALTER SESSION statement. To enter dates that are not
in standard Oracle date format, use the TO_DATE function with a
format mask:
TO_DATE ('November 13, 1992', 'MONTH, DD, YYYY') Oracle Database
stores time in 24-hour format—HH:MI:SS.
Cont…

What is an Operator in SQL?
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.
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
•Operators used to negate conditions

SQL Arithmetic Operators:
Assume variable a holds 10 and variable b holds 20, then:
Operator Description Example
+ Addition - Adds values on either side of the operator a + b will give 30
- Subtraction - Subtracts right hand operand from left hand
operand
a - b will give -10
* 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
% Modulus - Divides left hand operand by right hand
operand and returns remainder
b % a will give 0

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

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

DDL Statements
The SQL CREATE TABLE Statement
Data Definition Language (DDL) statement are SQL statements that support the definition or
declaration of database objects (For example, Create table, drop table, and alter table). The
CREATE TABLE statement is used to create a table in a database. Tables are organized into rows
and columns; and each table must have a name.
SQL CREATE TABLE Syntax
CREATE TABLE table_name
(
column_name1 data_type(size),column_name2 data_type(size),column_name3 data_type(size),....);
The column_name parameters specify the names of the columns of the table. The data_type
parameter specifies what type of data the column can hold (e.g. varchar, integer, decimal, date,
etc.). The size parameter specifies the maximum length of the column of the table.

Example
Now we want to create a table called "Persons" that contains five columns:
PersonID, LastName, FirstName, Address, and City.
We use the following CREATE TABLE statement:
CREATE TABLE Persons
(
PersonID number(3),FirstName varchar2(20),Address varchar2(30),City
varchar2(15));
Cont…

The ALTER TABLE Statement
A table structure can be modified using the Alter Table command. We can do
the following on a table.
• Add a new column
• Modify an existing column
• Drop an existing column
• Define a default value for the new column
• Modify the existing constraints

A) Adding columns
To add a column in a table, we have to use ALTER Command
syntax:
ALTER TABLE table_nameADD (column_name datatype);
SQL ALTER TABLE Example
Look at the "Persons" table:
P_Id FirstName Address City
1 Ola Timoteivn 10 Sandnes
2 Tove Borgvn 23 Sandnes
3 Kari Storgt 20 Stavanger
Now we want to add a column named "DateOfBirth" in the "Persons" table.
We use the following SQL statement:
ALTER TABLE Persons ADD (DateOfBirth date);
The "Persons" table will now like this:
P_Id FirstName Address City DateOfBirth

B) Modify table columns
The data type, size, and default value of a column can also be modified. However, a change to
the default value affects only the subsequent insertions to the table. we want to change the data
type of the column named "DateOfBirth" in the "Persons" table.
ALTER TABLE Persons modify(DateOfBirth number(4));
Notice that the "DateOfBirth" column is now of type number and is going to hold a year in a
two-digit or four-digit format.

P_Id FirstName Address City
C) Deleting a column
Using Drop column key word the table column can be deleted. Some do’s and don’ts
with regard to dropping of columns can be summarized below:
•Columns can be dropped even if it has values
•Dropping of columns can’t be rolledback
•All columns of the table cannot be dropped
•Parent column can’t be dropped
we want to delete the column named "DateOfBirth" in the "Persons" table.
ALTER TABLE Persons DROP COLUMN DateOfBirth;
The "Persons" table will now like this:

D) Dropping Table
To drop a table completely from the database, the following command can be used
Syntax:
Drop table tablename;
Example
drop table persons;
This command drops the table “persons” completely from the database. When this
statement is executed,
•All data and data structure in the table is deleted
•Any pending transactions are committed
•All indexes are dropped
You cannot rollback this statement

E) Renaming a table
To rename a table for example “persons” to “customer”, we can use the following statement
Syntax:
Rename persons to customer;
This command can be used to rename a view, or sequence, or synonym.
F) Truncating a table
Truncating a table deletes all rows in a table permanently.
•Removes all rows from a table
•Releases the storage space used by that table
•Cannot rollback row removal?
•Using the delete statement, rows can be rolledback.
Syntax:
Truncate table tablename;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT 11/17/1981 5000 10
7698 BLAKE MANAGER 7839 05-01-1981 2850 30
7782 CLARK MANAGER 7839 06-09-1981 2450 10
7566 JONES MANAGER 7839 04-02-1981 2975 20
7788 SCOTT ANALYST 7566 12-09-1982 3000 20
7902 FORD ANALYST 7566 12-03-1981 3000 20
7369 SMITH CLERK 7902 12/17/1980 800 20
7499 ALLEN SALESMAN 7698 02/20/1981 1600 300 30
7521 WARD SALESMAN 7698 02/22/1981 1250 500 30
7654 MARTIN SALESMAN 7698 09/28/1981 1250 1400 30
7844 TURNER SALESMAN 7698 09-08-1981 1500 0 30
7876 ADAMS CLERK 7788 01-12-1983 1100 20
7900 JAMES CLERK 7698 12-03-1981 950 30
7934 MILLER CLERK 7782 01/23/1982 1300 10
Table name: emp
Queries are given based on the following three tables
Employee, department, and salary grade

DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
GRADE LOSAL HISAL
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
Department
Table name: dept
Salary grade
Table name: salgrade

ENAME SAL
KING 5000
BLAKE 2850
CLARK 2450
JONES 2975
SCOTT 3000
FORD 3000
SMITH 800
ALLEN 1600
WARD 1250
MARTIN 1250
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300
SELECT Column Example
The following SQL statement selects the "Name” and “salary" columns from the "Emp" table:
Example
SELECT ename, sal FROM emp;

7839 KING PRESIDENT 11/17/1981 5000 10
7698 BLAKE MANAGER 7839 05-01-1981 2850 30
7782 CLARK MANAGER 7839 06-09-1981 2450 10
7566 JONES MANAGER 7839 04-02-1981 2975 20
7788 SCOTT ANALYST 7566 12-09-1982 3000 20
7902 FORD ANALYST 7566 12-03-1981 3000 20
7369 SMITH CLERK 7902 12/17/1980 800 20
7499 ALLEN SALESMAN 7698 02/20/1981 1600 300 30
7521 WARD SALESMAN 7698 02/22/1981 1250 500 30
7654 MARTIN SALESMAN 7698 09/28/1981 1250 1400 30
7844 TURNER SALESMAN 7698 09-08-1981 1500 0 30
7876 ADAMS CLERK 7788 01-12-1983 1100 20
7900 JAMES CLERK 7698 12-03-1981 950 30
7934 MILLER CLERK 7782 01/23/1982 1300 10
SELECT * Example
The following SQL statement selects all the columns from the "emp" table:
Example
SELECT * FROM emp;

ENAME SAL SAL*12
KING 5000 60000
BLAKE 2850 34200
CLARK 2450 29400
JONES 2975 35700
SCOTT 3000 36000
FORD 3000 36000
SMITH 800 9600
ALLEN 1600 19200
WARD 1250 15000
MARTIN 1250 15000
TURNER 1500 18000
ADAMS 1100 13200
JAMES 950 11400
MILLER 1300 15600
Arithmetic expression
SQL allows use of arithmetic expression in select clause. The basic arithmetic operators that allowed are
+(addition), - (subtraction), *(multiplication), /(division).
Example
Display employee details with their annual salary
Select ename, sal, sal*12 from emp;

ENAME SAL annual salary
SCOTT 3000 36000
DNAME||'ISLOCATEDAT'||LOC
ACCOUNTING is located at NEW YORK
RESEARCH is located at DALLAS
SALES is located at CHICAGO
OPERATIONS is located at BOSTON
Column Aliases
Column aliases renames the column heading and is useful in arithmetic calculations. As keyword
can optionally ne used between column name and alias name.
Example
Select ename, sal, sal*12 “annual salary” from emp where ename=’SCOTT’;
Concatenation operator(||)
it is possible to concatenate two or more columns, arithmetic expression, or constant values
using || concatenation operator as shown below
Example
Select Dname || ‘is located at’ || loc from dept;

Eliminating duplicate rows (DISTINCT)
To eliminate duplicate rows, we can use the keyword DISTINCT immediately after the
SELECT keyword
Example
Select distinct job from emp;
JOB
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST

Where and order by clause
Normal select clause retrieves all rows of a table. With WHERE clause we can retrieve only
selected rows based on the specified condition. The main purpose of this statement is to filter
certain rows or display rows that are required. The syntax of SELECT with WHERE clause is
shown below
Select * or specific column name or expression from table name where condition;
Example
The condition is based on the number
Display employee details whose salary is greater than 2000
Select ename, sal from emp where sal>2000;
ENAME SAL
KING 5000
BLAKE 2850
CLARK 2450
JONES 2975
SCOTT 3000
FORD 3000

7788 SCOTT ANALYST 7566 12-09-1982 3000 20
Condition based on character
Display employee details whose name is SCOTT
Select * from emp where ename=’SCOTT’;
ENAME JOB HIREDATE SAL
BLAKE MANAGER 05/01/1981 2850
Condition based on date
Select ename, job, hiredate, sal from emp where hiredate=’05/01/1981’;

= Equal to
> Greater than
>= Greater than or equal
< Less than
<= Less than or equal
<> Not equal
BETWEEN (a) and (b) Range between (a) and (b) inclusive
IN<set> True when the number is in the set
LIKE <pattern> Matches a specified <pattern>
Relational operators and comparison condition
There are number of relational operators are allowed in SQL they are shown below
•BETWEEN---- and ---- operator
BETWEEN operator retrieves the data from the specified range and it includes the range value also
Example display the employee details whose salary is between 2000 ND 3000
Select ename, sal from empwhweresal between 2000 and 3000;
ENAME SAL
BLAKE 2850
CLARK 2450
JONES 2975
SCOTT 3000
FORD 3000

•IN operator
To test for values in a specified set of values, we can use IN condition. We shall retrieve
employee number, name, & department number working in the department number 10 or 20.
Select ename, job, deptno from emp where deptnoin(10,20);
ENAME JOB DEPTNO
KING PRESIDENT 10
CLARK MANAGER 10
JONES MANAGER 20
SCOTT ANALYST 20
FORD ANALYST 20
SMITH CLERK 20
ADAMS CLERK 20
MILLER CLERK 10
•LIKE operator
LIKE operator can be used to search a pattern string in a given table. The wildcard character ‘%’, if
used in the pattern, it matches with one or more character. To retrieve all employees whose name starts
with ‘M’ may be written as
example
Select * from emp where ename like ‘M%’;
7654 MARTIN SALESMAN 7698 09/28/1981 1250 1400 30
7934 MILLER CLERK 7782 01/23/1982 1300 - 10

ENAME JOB HIREDATE
ALLEN SALESMAN 02/20/1981
WARD SALESMAN 02/22/1981
Suppose if we wish to display all employees hired during FEB 81, the % character can be
used. As the default date format is mm/dd/yyyy and by using % we can mask dd or mm or
yyyy.
Select ename,job,hiredate from emp where hiredate like ‘02/%/1981’;
•Logical operators (AND, OR and NOT)
Like any other third generation languages, you can write one or more relational conditions combined with
logical operator to get desire true or false.
Example 1: logical operator AND
Display all employee details who are clerks and draw salary >=1000
Select * from emp where sal>=1000 and job=’Clerk’;
EMPNO ENAME JOB MGR
HIREDA
TE SAL COMM DEPTNO
7876 ADAMS CLERK 7788 01/12/1983 1100 - 20
7934 MILLER CLERK 7782 01/23/1982 1300 - 10

7369 SMITH CLERK 7902 12/17/1980 800 - 20
7499 ALLEN SALESMAN 7698 02/20/1981 1600 300 30
7876 ADAMS CLERK 7788 01/12/1983 1100 - 20
7900 JAMES CLERK 7698 12/03/1981 950 - 30
Example 2: logical operator OR
Display employee details whose name starts with A or salary <=1000
Select * from emp where enamelike’A%’ or sal<=1000;
ORDER BY clause (ASC/DESC)
The output of any SQL program is a listing of table data as it is, and no implicit order is used. We
can display data either in ascending or in descending order using the keyword ASC (default) and
DESC respectively. The general syntax is
Select * or Distinct or column name or expression from table name where condition order by
(column name, expression, alias) [ASC/DESC];

•Sorting in ascending order
Ascending order is default ASC keyword is not require to display data in ascending order.
Example
Display employee details whose job is salesman in ascending order based on their hiredate
Select * from emp where job=’SALESMAN’ order by hiredate;
EMPNO ENAME JOB MGR
HIREDAT
E SAL COMM DEPTNO
7499 ALLEN SALESMAN 7698 02/20/1981 1600 300 30
7521 WARD SALESMAN 7698 02/22/1981 1250 500 30
7844 TURNER SALESMAN 7698 09/08/1981 1500 0 30
7654 MARTIN SALESMAN 7698 09/28/1981 1250 1400 30
•Sorting in descending order
When sorting data in descending order, we have to use DESC keyword.
Example
Display employee details in descending order based on their names whose job is manager
Select ename, job from emp where job=’MANAGER’ order by ename DESC;
ENAME JOB
JONES MANAGER
CLARK MANAGER
BLAKE MANAGER

ENAME JOB SAL ANNUAL_SALARY
SMITH CLERK 800 9600
JAMES CLERK 950 11400
ADAMS CLERK 1100 13200
MILLER CLERK 1300 15600
•Sorting based on Alias
We can sort the data based on their alias name as shown below
Display employees annual salary whose job is clerk and sort the data based on annual salary
Select ename, job, sal, sal*12 “Annual salary” from emp where job=’CLERK’ order by Annual
salary;
•Sorting on multiple columns
When ORDER BY clause contains more than one column the ordering is done in the same order
as specified in the query.
Example
Display employee name deptno and salary in sorted order based on deptno in ascending and
salary in descending order
Select ename, deptno, sal from emp order by deptno, sal desc;
ENAME DEPTNO SAL
KING 10 5000
CLARK 10 2450
MILLER 10 1300
SCOTT 20 3000
FORD 20 3000
JONES 20 2975

SQL Functions
SQL functions are built into Oracle Database and are available for use in various appropriate SQL statements.
If you call a SQL function with an argument of a data type other than the data type expected by the SQL
function, then Oracle attempts to convert the argument to the expected data type before performing the SQL
function. If you call a SQL function with a null argument, then the SQL function automatically returns null.
Numeric Functions
Numeric functions accept numeric input and return numeric values. Most numeric functions that
return NUMBER values that are accurate to 38 decimal digits.
Function
name
Description Example
ABS ABS returns the absolute value of n. SELECT ABS(-15) "Absolute" FROM
DUAL; Absolute---------- 15
ACOS ACOS returns the arc cosine of n. The
argument n must be in the range of -1 to 1, and the
function returns a value in the range of 0 to pi,
expressed in radians.
SELECT ACOS(.3)"Arc_Cosine"
FROM DUAL;Arc_Cosine----------
1.26610367
ASIN ASIN returns the arc sine of n. The argument n must
be in the range of -1 to 1, and the function returns a
value in the range of -pi/2 to pi/2, expressed in
radians.
SELECT ASIN(.3) "Arc_Sine" FROM
DUAL;Arc_Sine----------.304692654
ATAN ATAN returns the arc tangent of n. The
argument n can be in an unbounded range and
returns a value in the range of -pi/2 to pi/2,
expressed in radians.
SELECT ATAN(.3) "Arc_Tangent"
FROM DUAL;Arc_Tangent----------
.291456794

Function name Description Example
COS COS returns the cosine of n (an angle expressed in
radians).
SELECT COS(180 * 3.1415/180)"Cosine
of 180 degrees" FROM UAL;Cosine of 180
degrees--------------------- -1
SIN SIN returns the sine of n (an angle expressed in radians). SELECT SIN(30 * 3.1415/180) "Sine of 30
degrees" FROM DUAL;Sine of 30 degrees-
----------------- .5
TAN TAN returns the tangent of n (an angle expressed in
radians).
SELECT TAN(135 * 3.1415/180)"Tangent
of 135 degrees" FROM DUAL;Tangent of
135 degrees---------------------- -
1
POWER POWER returns n2 raised to the n1 power. The
base n2 and the exponent n1 can be any numbers, but
if n2 is negative, then n1 must be an integer.
SELECT POWER(3,2) "Raised" FROM
DUAL; Raised---------- 9
SQRT SQRT returns the square root of n. SELECT SQRT(26) "Square root" FROM
DUAL;Square root-----------5.09901951
ROUND(numb
er)
ROUND returns n rounded to integer places to the right
of the decimal point. If you omit integer, then n is
rounded to 0 places. The argument integer can be
negative to round off digits left of the decimal point.
SELECT ROUND(15.193,1) "Round"
FROM DUAL; Round----------
15.2SELECT ROUND(15.193,-1) "Round"
FROM DUAL; Round---------- 20
SELECT ROUND(1.5), ROUND(2.5)
FROM DUAL;ROUND(1.5)
ROUND(2.5)---------- ---------- 2
3SELECT ROUND(1.5f), ROUND(2.5f)
FROM DUAL;ROUND(1.5F)
ROUND(2.5F)----------- -----------
2.0E+000 2.0E+000

Character Functions Returning Character Values
Character functions that return character values return values of the following datatypes unless otherwise
documented:
•If the input argument is CHAR or VARCHAR2, then the value returned is VARCHAR2.
The length of the value returned by the function is limited by the maximum length of the datatype returned.
•For functions that return CHAR or VARCHAR2, if the length of the return value exceeds the limit, then
Oracle Database truncates it and returns the result without an error message.
The character functions that return character values are:
Function
name
Description Example
LOWER LOWER returns char, with all letters lowercase. SELECT LOWER('SCOTT
MCMILLAN') "Lowercase" FROM
DUAL;Lowercase--------------------
scottmcmillan
UPPER UPPER returns char, with all letters uppercase SELECT LOWER(“scottmcmillan”)
"Uppercase" FROM
DUAL;Uppercase--------------------------
---SCOTT MCMILLAN
INITCAP INITCAP returns char, with the first letter of each word
in uppercase, all other letters in lowercase. Words are
delimited by white space or characters that are not
alphanumeric.
SELECT INITCAP('the soap')
"Capitals" FROM DUAL; Capitals------
---The Soap
CONCAT CONCAT returns char1 concatenated with char2.
Both char1 and char2 can be any of the
datatypes CHAR, VARCHAR2. The string returned is
in the same character set as char1. Its datatype depends
on the datatypes of the arguments.
SELECT CONCAT(“Hello”,”World”)
“Concatinated String” FROM DUAL
Concatinated String------------------------
------Hello World

Function
name
Description Example
SUBSTR The SUBSTR functions return a portion of char,
beginning at
character position, substring_length characters
long. SUBSTR calculates lengths using characters as
defined by the input character set. SUBSTRB uses
bytes instead of characters. SUBSTRC uses Unicode
complete characters. SUBSTR2 uses UCS2 code
points. SUBSTR4 uses UCS4 code points.
 If position is 0, then it is treated as 1.
 If position is positive, then Oracle Database
counts from the beginning of char to find the
first character.
 If position is negative, then Oracle counts
backward from the end of char.
 If substring_length is omitted, then Oracle
returns all characters to the end of char.
If substring_length is less than 1, then Oracle
returns null.
SELECT SUBSTR('ABCDEFG',3,4)
"Substring" FROM
DUAL;Substring---------
CDEFSELECT
SUBSTR('ABCDEFG',-5,4)
"Substring" FROM
DUAL;Substring---------CDEF
REPLACE REPLACE returns char with every occurrence
of search_string replaced with replacement_string.
If replacement_string is omitted or null, then all
occurrences of search_string are removed.
If search_string is null, then char is returned.
SELECT REPLACE('JACK and
JUE','J','BL') "Changes" FROM
DUAL;Changes--------------BLACK
and BLUE

Function
name
Description Example
LPAD LPAD returns expr1, left-padded to length n characters with
the sequence of characters in expr2. This function is useful for
formatting the output of a query.
If you do not specify expr2, then the default is a single blank.
If expr1 is longer than n, then this function returns the portion
of expr1 that fits in n.
The argument n is the total length of the return value as it is
displayed on your terminal screen. In most character sets, this
is also the number of characters in the return value. However,
in some multibyte character sets, the display length of a
character string can differ from the number of characters in
the string.
SELECT LPAD('Page 1',15,'*.') "LPAD
example" FROM DUAL;LPAD example--
-------------*.*.*.*.*Page 1
RPAD RPAD returns expr1, right-padded to
length n characters with expr2, replicated as many
times as necessary. This function is useful for
formatting the output of a query
expr1 cannot be null. If you do not specify expr2, then
it defaults to a single blank. If expr1 is longer than n,
then this function returns the portion of expr1that fits
in n.
The argument n is the total length of the return value
as it is displayed on your terminal screen. In most
character sets, this is also the number of characters in
the return value. However, in some multibyte
character sets, the display length of a character string
can differ from the number of characters in the string.
SELECT last_name, RPAD(' ',
salary/1000/1, '*') "Salary" FROM
employees WHERE department_id =
80 ORDER BY last_name;
LAST_NAME Salary
-------------------------
Abel **********
Ande *****
Banda *****
Bates ******

Function
name
Description Example
LTRIM LTRIM removes from the left end of char all of the
characters contained in set. If you do not specify set, it
defaults to a single blank. If char is a character literal,
then you must enclose it in single quotes. Oracle
Database begins scanning char from its first character
and removes all characters that appear in setuntil
reaching a character not in set and then returns the
result.
SELECT product_name,
LTRIM(product_name, 'Monitor ')
"Short Name" FROM products
WHERE product_name LIKE
'Monitor%';
PRODUCT_NAME Sort Name
-------------------- ---------
Monitor 17/HR 17/HR
Monitor 17/HR/F 17/HR/F
Monitor 17/SD 17/SD

Function
name
Description Example
RTRIM TRIM enables you to trim leading or trailing characters
(or both) from a character string.
If trim_character or trim_source is a character literal,
then you must enclose it in single quotes.
 If you specify LEADING, then Oracle Database
removes any leading characters equal
to trim_character.
 If you specify TRAILING, then Oracle removes
any trailing characters equal to trim_character.
 If you specify BOTH or none of the three, then
Oracle removes leading and trailing characters
equal to trim_character.
 If you do not specify trim_character, then the
default value is a blank space.
 If you specify only trim_source, then Oracle
removes leading and trailing blank spaces.
 The function returns a value with
datatype VARCHAR2. The maximum length of the
value is the length of trim_source.
 If either trim_source or trim_character is null, then
the TRIM function returns null.
SELECT employee_id,
TO_CHAR(TRIM(LEADING 0
FROM hire_date)) FROM
employees WHERE
department_id = 60;
EMPLOYEE_ID TO_CHAR(T
----------- ---------
103 3-JAN-90
104 21-MAY-91
105 25-JUN-97
06 5-FEB-98
107 7-FEB-99

Group Functions
In SQL, the aggregate functions are generally applied to group. A set row is called as a group. The output
consists of one result per group. Grouping is done based on a particular column(s).
The group clause can be used in a SELECT statement to collect data across multiple records and group
the results by one or more columns.
Different Grouping functions are SUM, COUNT, MAX, MIN, AVG

The general syntax of SELECT Clause with GROUP BY clause is shown below:
SELECT [col, ] group_function(col),
FROM Table
[WHERE condition]
[GROUP BY column]
[HAVING condition]
The rules to be followed while using GROUP BY clause are given below:
•Non-group functions or columns are not allowed in SELECT clause.
•Group Functions ignore nulls.
•By default, the result of GROUP BY clause sorts data in ascending order
Examples
No of Employees
14
Select count(* ) as “No of Employees” from emp;
Select count(job) as " No of Employees" from emp where job='MANAGER';
No of Employees
3

Creating Groups of Data
As given already, the GROUP BY clause arranges the rows into smaller groups as shown in
below figure:
DEPTNO SAL
_ _ _ _ _ _ _ _ _ _ _ _ _ _
10 2450
10 5000
10 1300
_ _ _ _ _ _ _ _ _ _ _ _ _ _
20 800
20 1100
20 3000
20 3000
20 2975
_ _ _ _ _ _ _ _ _ _ _ _ _ _
30 1600
30 2850
30 1250
30 950
30 1500
30 1250
DEPTNO AVG (SAL)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _
10 2916.66667
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _
20 2175
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _
30 1566.66667
Average Salary
for each
Department
2916.6666
2175
1566.6667

To retrieve the average salary for each department in EMP table, the query is given below may be
used.
selectdeptno, avg(sal) from emp group by deptno;
DEPTNO AVG(SAL)
30 1566.66666666666666666666666666666666667
20 2175
10 2916.66666666666666666666666666666666667
selectdeptno, count(*), sum(sal) from emp group by deptno;
DEPTNO COUNT(*) SUM(SAL)
30 6 9400
20 5 10875
10 3 8750

Grouping by Multiline Columns
selectdeptno, job, sum(sal) from emp group by deptno,job;
DEPTNO JOB SUM(SAL)
20 MANAGER 2975
20 CLERK 1900
30 SALESMAN 5600
30 CLERK 950
10 PRESIDENT 5000
30 MANAGER 2850
10 CLERK 1300
10 MANAGER 2450
20 ANALYST 6000

HAVING clause
To exclude certain rows in a table, we use WHERE clause with an appropriate condition.
Similarly, to exclude certain groups in a GROUP BY clause we must use HAVING clause.
selectdeptno, max(sal) from emp group by deptno having max(sal)>2900;
DEPTNO MAX(SAL)
20 3000
10 5000
Note: grouping function is normally used in HAVING clause but not in WHERE clause.

Using all WHERE, GROUP BY, HAVING, ORDER BY Clauses
The below figure demonstrates the query with all clauses.
Find the sum of the salary of each job and sum of salary should greater than 5000 order based on
sum of salary
select job, sum(sal) "pay" from emp group by job having sum(sal)>5000 order by sum(sal);
JOB pay
SALESMAN 5600
ANALYST 6000
MANAGER 8275
Query with all clauses
•First all rows matching the WHERE condition are retrieved.
•These rows are grouped using the column(s) in GROUP BY clause
•Groups matching the HAVING clause condition are retained.
•Finally, the result is ordered (ascending) on the column SUM (Sal).

Nesting of Group functions
You can nest the group functions in the SELECT clause as shown in below figure
select max(avg(sal)) from emp group by deptno;
MAX(AVG(SAL))
2916.66666666666666666666666666666666667
DEPTNO AVG (SAL)
10 2916.66667
20 2175
30 1566.66667
2916.66667
Max(Sal)
This query finds the average salary of EMP table for each department first and then picks the
highest in that group. You can’t specify the DeptNo column in SELECT clause as it is not a single
group function. This means the query yields a single value.

DML Statements
A data manipulation language (DML) consists of SQL statements which are used to insert, delete
and update the records in a table. Adding records to a newly created table is called as populating
the table.
INSERT statement
To add new rows to a table INSERT statement is used and its syntax is
Syntax
It is possible to write the INSERT INTO statement in two forms. The first form does not
specify the column names where the data will be inserted, only their values:
INSERT INTO table_nameVALUES (value1,value2,value3,...);
The second form specifies both the column names and the values to be inserted:
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
INSERT INTO statement inserts only one row at a time. To insert more than one row then several
times INSERT statement must be executed.
Example
INSERT INTO emp values(7839, ‘king’, ‘president’, ‘-‘, ‘11/17/1981’, 5000, ‘-‘, 10);
After inserting several records the table look like below

DELETE Statement
To remove row from a table, use DELETE Statement. The syntax for this statement is given
below
DELETE [FROM] table [WHERE Condition];
The DELETE statement deletes only one row at a time if the WHERE condition contains a
primary key. Be careful that if WHERE is not present in the query, all the rows in the table are
deleted. To delete OPERATIONS department in DEPT table, we use the SQL statement as shown
in below figure
delete from dept where dname='OPERATIONS';
1 row(s) deleted.
An error message may be displayed during a row deletion, if it has a reference in some other
table.
For example, an attempt to delete the row with DeptNo = 10, Oracle SQL displays an integrity
constraint error.
delete from dept where deptno=10;
Error Message: ORA-02292: integrity constraint (SCOTT.SYS_C00810) violated – child record
found
This is because, there are several employees working for department number 10 and these records
appear in EMP table. Hence, unless all these records are deleted, it is not possible to delete the
department

given below:
UPDATE table
SET column-1 = value-1 [, column-2 = value-2 ………]
[WHERE condition];
UPDATE a Single Row
To update a single row we must use WHERE clause with primary key. Assume that we wish to
promote the employee FORD (EmpNo = 7902) from the job of ANALYST to MANAGER. The
query shown in below figure does this task.
updateemp set job='MANAGER' where empno=7902;
1 row(s) updated.selectempno,ename, job from emp where empno=7902;
EMPNO ENAME JOB
7902 FORD MANAGER

Integrity Constraint Error
If the record being updated has a reference in the form of foreign key, similar to DELETE
statement, you will be issued an error. See below figure
Update emp set deptno=60 where empno=7902;
ORA-02291: integrity constraint (NALINA.SYS_C006991) violated - parent key not found
Fig: Integrity Constraint Error
The error is due to the fact that DeptNo 60 does not in DEPT table.
Example: A very common and popular example of UPDATE statement is to hike the salary of
employees in terms of percentage. Assume that the company hikes the salary of all SALESMAN by
10%
updateemp set sal=sal*0.10 where job='SALESMAN';
4 row(s) updated.
select ename,job,sal from emp where job='SALESMAN';
ENAME JOB SAL
ALLEN SALESMAN 1760
WARD SALESMAN 1375
MARTIN SALESMAN 1375
TURNER SALESMAN 1650

Nested queries or sub queries
A WHERE clause generally contains a condition, but it can also contain an SQL query with
another SELECT statement. The query within a WHERE clause is called inner query or sub
query and the query which encloses the inner one is called as outer query or main query. It is
also possible to place the inner query within a FROM or HAVING clause. Using nested
queries it is possible to build powerful SQL programs.
Execution of Nested Queries
The general syntax
SELECT <column(s)> outer query
FROM table
WHERE <condition> operator
inner query
(SELECT <column> from Table
The operator mentioned in the outer query can be any one of >, =, or IN
The comparison condition may be a Single row operator like >, )=, <, <>) or
multiple row operators like IN, ANY, ALL.

•Enclose sub queries in parentheses
•Place sub queries on the right side of the comparison operator
•DO not add an ORDER BY clause to a sub query
•Use single-row operators with single-row sub queries
•Use multiple-row operators with multiple-row sub queries
Guidelines to write sub queries:
Types of SubQueries
We can classify the subqueries based upon how many tows the inner query returns. We have four
types:
•Single-row Subquery
•Multiple-row Subqucry
•Multiple-column Subquery
Single-Row Subquery
The single-row subquery returns just one row as a result. For these types of single-row
subqueries, use only the following operators for comparison:
>, =, >=, <, <=, <>
The example quay shown in below figure prints all employees who draw more than the
employee 7876 (S1100). Note that both the inner queries return one row only because of the
primary key used in WHERE clause.

selectename, job from emp where ename = (select ename from emp where
job='CLERK' and sal> (select sal from emp where empno = 7876));
ENAME JOB
MILLER CLERK
Group Functions in Sub queries
Suppose we wish to display the employees who draw salary more than the average
salary of all employees.
selectename, job, sal from emp where sal>= (select avg(sal) from emp);
ENAME JOB SAL
KING PRESIDENT 5000
BLAKE MANAGER 2850
CLARK MANAGER 2450
JONES MANAGER 2975
SCOTT ANALYST 3000
FORD ANALYST 3000

HAVING in Sub queries
We can use HAVIIIG clause in a subquery, but the constraints of the single-row queries must
be satisfied. An example quay is given in below figure.
selectdeptno,min(sal) from emp group by deptno having min(sal)>(select min(sal) from emp
where deptno=20);
DEPTNO MIN(SAL)
30 950
10 1300
Multiple-Row Sub query
These types of queries returns more than one row because of the execution of the inner query.
Use the comparison operators shown in the below table for multiple-row subqueries. The
operators in. ANY, and ALL are used in the multiple-row sub queries.

IN Equal to any member in the list
ANY Compare value to each value returned by the
sub query
ALL Compare value to all the values returned by
the sub query
Operator Meaning Example
<ANY Less than the maximum E<ANY (5,3,8): e is less than any single item
in the list (5,3,8). Even 7 qualifies, because
7<8
>ANY More than the minimum E>ANY (5,3,8): e is greater than any single
item in the list (5,3,8). Even 4 qualifies,
because 4>3
=ANY Same as IN E=ANY (5,3,8). All the values in the list
qualify
<ALL Less than the maximum E<ALL (5,3,8). Anything below 3 qualifies
>ALL More than the minimum E>ALL (5,3,8). Anything greater than 8
qualifies
!=ALL Not equal to anything E!= (5,3,8). Any thing other than 5, 3, and 8
qualifies

Display the details of the employee whose salary is less than the salary of all the clerks
Select empno,ename,job from emp where sal<ANY(select sal from emp where job='CLERK')
and job<>'CLERK';
EMPNO ENAME JOB
7521 WARD SALESMAN
7654 MARTIN SALESMAN
Display the details of the employee whose salary is greater than the average salary of each
department
Select empno,ename,job from emp where sal>ALL(select avg(sal) from emp group by
deptno);
EMPNO ENAME JOB
7566 JONES MANAGER
7902 FORD ANALYST
7788 SCOTT ANALYST
7839 KING PRESIDENT

Multiple-Column Subquery
When the subquery returns multiple rows with multiple colunms, the Outer query should
also have appropriate columns in the WHERE clause. An example query is shown in below
figure
Replace the commission column with 0 for all the employees of deparment no 30
ENAME DEPTNO SAL COMM
BLAKE 30 2850 0
ALLEN 30 1600 300
WARD 30 1250 500
MARTIN 30 1250 1400
TURNER 30 1500 0
JAMES 30 950 0
select ename,deptno,sal,comm from emp where (sal, nvl(comm,0)) IN (select sal, nvl(comm,0)
from emp where deptno=30);

Database Transaction Statements
A transaction is one which consists of a set of SQL statement to accomplish a specific task.
This set or block may consist of DDL, DML and DCL statements.
Two important statements that are needed for the database transaction are COMMIT and
ROLLBACK. The state of the database before COMMIT or ROLLBACK is:
•The previous state of data can be recovered
•The current user can review the results of the DML operations by using the SELECT
statement
•Other users cannot view the results of the DML statements modifies by the current user
•The affected rows are locked; other users cannot change data within the affected rows

COMMIT Statement
After the COMMIT statement is issued, then
•Data changes are made permanent in the database
•The previous state of data is permanently lost
•All users can view the results
•Locks on the affected rows are released; those rows are available for other users to manipulate
•All savepoints are erased
ROLLBACK Statement
After the ROLLBACK statement is executed,
•Discard all pending changes by using the ROLLBACK statement
•Data changes are undone
•Previous state of data is restored
•Locks on the affected rows are released
Example:
delete from emp;
14 row(s) deleted.
Rollback;
Rollback complete
ROLLBACK restores the changes made to the Database

SAVEPOINT Statement
The SAVEPOINT statement creates a marker or firewall within a transaction which signifies
that prior to this point all the modifications has been committed and safe. We can rollback the
modifications up to any user defined save point(s) to avoid long rollbacks.
SET OPERATORS
•Union
•Intersection
•Minus
Considering A and B are the two relations involved in the set operations, we can write,
Union Operator
A UNION B
The result is all rows selected by A and B
Intersection Operator
A INTRSECTION B
All distinct rows selected by both the queries
Minus Operator
A MINUS B
All distinct rows selected by A that are not selected by B
The precedence of these set operators are all same and the evaluation proceeds from left to right
if no parentheses are used. The tables used for set operations are given in below figure

FName LName Age
Susan Yao 18
Ramesh Arvind 20
Joseph Antony 19
Jennifer Amy 30
Andy perumal 21
FName LName Age
Jennifer Amy 30
Scott Tommy 40
Ramesh Arvind 20
Student Teacher
UNION Operator
The important rule to be followed while using UNION operator is that the two queries must be union
compatible. Union operator basically eliminates the duplicates.
Query 1: Retrieve both students teachers
SELECT FName, LName
FROM Student
UNION
SELECT FName, LName
FROM Teacher;
What does this query do? It gives the details of both Students and Teachers eliminating the duplicates
Output: The output of Query1 is shown below and we notice that Jennifer and Ramesh are common in both
the relations. Hence 6 rows are retrieved.

FName LName
Andy Perumal
Jennifer Amy
Joseph Antony
Scott Tommy
Ramesh Arvind
Susan Yao
Output Table will be
To add duplicate rows also in the result, we can use UNION ALL as shown in the next example.
Query 2: Demonstration of UNION ALL
SELECT FName, LName
FROM Student
UNION ALL
SELECT FName, LName
FROM Teacher;
Output: Result of executing the Query2 is shown below:
FName LName
Susan Yao
Ramesh Arvind
Joseph Antony
Jennifer Amy
Andy Perumal
Jennifer Amy
Scott Tommy
Ramesh Arvind

INTERSECT Operator
The INTERSECT set operator returns the rows common to both the queries. For intersection
operations, there should be compatibility between the two queries. Let us find out who are
teachers as well as students.
Query 3: Teachers as well as Students
SELECT FName, LName
FROM Student
INTERSECT
SELECT FName, LName
FROM Teacher;
Output: Notice that intersection also outputs the rows in the ascending order just like union
operations
FName LName
Jennifer Amy
Ramesh Arvind

MINUS Operator
The MINUS operation will compare each record in table1 to a record in table2. The result
returned will be records in table1 that are not in table2. We can reserve the MINUS order to
get records in table2 that are not in table1. Let us find out the names of students who are not
teachers.
Query 4: Students who are not teachers.
SELECT FName, LName
FROM Student
MINUS
SELECT FName, LName
FROM Teacher;
Output: The output of Students - Teacher is shown below:
FName LName
Andy Perumal
Joseph Antony
Susan Yao

CONSTRAINTS
Basically constraints enforce certain rules on table or column level. For example, primary key,
foreign key, etc, are some of the table level constraints that can be specified. SQL allows you to
define constraints on columns and tables. Constraints give us as much control over data in tables
as needed. If a user attempts to store data in a column that would violate a constraint, an error is
raised. This applies even if the value came from the default value definition.
Oracle 10g supports the following types of constraints:
NOT NULL
UNIQUE KEY
PRIMARY KEY
FOREIGN KEY
CHECK
Syntax for creating a Constraints
CREATE TABLE [schema.] table (column data type [DEFAULT expr] [column_constraint],
………….[CONSTRAINT constraint_name] constraint_type (column, …..),);
This syntax gives the method to add constraints using the keyword CONSTRAINT. The
column_constraint shown in the second line with bold letters specifies the column level
constraint which takes the same syntax as table level constraint.

NOT NULL and PRIMARY KEY constraints
Create a Student table with column and table level constraints.
Create table student ( RegNo number(5) primary key, StdName varchar2(20) NOT
NULL);
Column level NOT NULL constraint for Name column and table level constraint of RegNo as
primary key. When a column(s) is declared as primary key there is no need to add the not null
constraint. The primary key constraint can be specified in the column level as: RegNo
NUMBER PRIMARY KEY, also and Oracle will create a constraint name for this.
UNIQUE Constraint
The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE and
PRIMARY KEY constraint both provide a guarantee for uniqueness for a column or set of
columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
Note that we can have many UNIQUE constraints per table, but only one PRIMARY KEY
constraint per table.
Example: Consider a table called Invoice with three columns InvNo, ItemNo, and InvDate. The
column InvNo depends on ItemNo, because an invoice generally consists of items. Hence InvNo
and ItemNo can be considered as that they should be unique.

FOREIGN KEY Constraint
A Foreign Key is a combination of columns with values based on the primary key values from
another table. For example, the DeptNo column in EMP table is a foreign key that references
DeptNo column in DEPT table.
With this constraint enabled, no employee record can be inserted into EMP table with an invalid
DNo (i.e., not in DEPT table)..
ON DELETE CASCADE
The ON DELETE CASCADE option helps to specify whether you want rows deleted in a
child table when corresponding rows are deleted in the parent table. If cascading delete option
is not specified, the default behaviour of the SQL prevents deleting data in a table of other
tables reference it.
The syntax for this constraint is given below:
CREATE TABLE ..............
...........................
...........................
CONSTRAINT INV_FK FOREIGN KEY (ItemNo)
REFERENCES Items(ItemNo) ON DELETE CASCADE);
Now with this modification, when row is deleted in Items table all the rows in the Invoice
table with this ItemNo will automatically get deleted.

ItemNo ItemName Price
12 Paste 45
13 Rice 20
InvNo ItemNo InvDate
111 12 30-OCT-05
111 13 30-OCT-05
112 12 01-NOV-05
Items Invoice
In the above figure, if row with ItemNo 12 is deleted in Items table, two rows in Invoice table
with InvNo 111 and 112 would also deleted.
CHECK Constraint
The columns must have values that are within certain range or that satisfy certain conditions
(domain of the column). With CHECK constraint specified in the form an expression must be
true for each row in the table. An example for this could be to add column level check
constraint for marks in the Student table. The example query appears below:
CREATE TABLE Student(
.................
Marks NUMBER (3) CHECK (Marks BETWEEN 0 AND 100);
.................
A column-level CHECK constraint cannot
•refer to values in other rows
•use SYSDATE , UID, UserEnv, CurrVal, NextVal, Level, or ROWNUM

Constraint Manipulations
The constraints can be added, dropped, enabled, or disabled provided appropriate constraints
have been defined while creating the table. A constraint cant’t be modified directly, but follow
a two step process. First, drop the existing constraint, and then add a new constraint with
intended modifications.
Adding a Constraint
To add a constraint, use the following syntax:
ALTER TABLE table_name
ADD [CONSTRAINT constraint] type (column);
Look at the below example,
ALTER TABLE EMP
ADD CONSTRIANT EMP_FK
FOREIGN KEY(Mgr) REFERENCES EMP(EmpNo);
This example adds a foreign key constraint to EMP table, but in general you can add any type
of constraint.

Dropping a Constraint
To remove the manager constraint from the EMP table created in the preceding section, the following
query can be executed:
ALTER TABLE EMP
DROP CONSTRAINT EMP_FK;
To remove the PRIMARY KEY constraint of DEPT table, we drop the associated FOREIGN KEY
constraint on the EMP.DEPTNO column. This is illustrated below:
ALTER TABLE DEPT
DROP PRIMARY KEY CASCADE;
Disabling a Constraint
We must execute the DISABLE clause of the ALTER TABLE statement to deactivate an integrity
constraint. Similar to the previous example, the CASCADE option is applied to disable dependent
integrity constraints
ALTER TABLE EMP
DISABLE CONSTRAINT EMP_PK CASCADE;
Enabling a Constraint
To activate an integrity constraint currently disabled in the table definition by using the ENABLE clause.
A UNIQUE or PRIMARY KEY index is automatically created if you enable a UNIQUE key or PRIMARY
KEY constraint.
ALTER TABLE EMP
ENABLE CONTRAINT EMP_PK CASCADE;

JOINS
So far the queries that we have discussed were containing only on table in the FROM clause.
There are many occasions in the database applications where we need to retrieve data from
more than one table.
What is a Join?
It basically involves with more than one table. This section addresses several types of joins as
shown below:
The general syntax of join is shown below:
SELECT table1.column, table2.column FROM table1, table2
WHERE table1.column1 = table2.column2;

Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=,
BETWEEN, LIKE, and NOT; they can all be used to join tables. However, the
most common operator is the equal to symbol.
There are different types of joins available in SQL −
INNER JOIN − returns rows when there is a match in both tables.
LEFT JOIN − returns all rows from the left table, even if there are no matches
in the right table.
RIGHT JOIN − returns all rows from the right table, even if there are no
matches in the left table.
FULL JOIN − returns rows when there is a match in one of the tables.
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.
CARTESIAN JOIN − returns the Cartesian product of the sets of records from
the two or more joined tables.

Inner or Equi Join
When the joining condition in the WHERE clause involves = sign, then such a join is called as
equi join. When two tables are joined together for equi join, you must follow guidelines as
indicated below:
•Table names FROM clause must be separated with commas
•Use appropriate joining condition. That is, table1.column = table2.column
•Do not use ambiguous column names
Syntax:
SELECT table1.column1, table2.column2... FROM table1 INNER JOIN
table2 ON table1.common_field = table2.common_field;

To demonstrate the working of equi join, let us display the employee names and department
names from EMP and DEPT tables (see below figure).
Select ename, dname from emp,dept where emp.deptno=dept.deptno;
ENAME DNAME
CLARK ACCOUNTING
MILLER ACCOUNTING
KING ACCOUNTING
FORD RESEARCH
SCOTT RESEARCH
JONES RESEARCH
SMITH RESEARCH
ADAMS RESEARCH
WARD SALES
MARTIN SALES

Table Aliases
Instead of using the table names in the WHERE condition (or in SELECT clause), you may
optionally use aliases for table names as shown in below table
Select ename, e.deptno,dname, d.deptno from emp e,dept d where e.deptno=d.deptno;
ENAME DEPTNO DNAME DEPTNO
CLARK 10 ACCOUNTING 10
MILLER 10 ACCOUNTING 10
KING 10 ACCOUNTING 10
FORD 20 RESEARCH 20
SCOTT 20 RESEARCH 20
JONES 20 RESEARCH 20
SMITH 20 RESEARCH 20
ADAMS 20 RESEARCH 20
WARD 30 SALES 30
MARTIN 30 SALES 30

Joining more than Two Tables
When two or more tables are joined, the WHERE clause must have joining attributes for table1
and table2, joining attributes for table2 and table3. For example, if three tables Employees,
Departments, and Projects are joined using the joining attributes DNo, it should use the following
syntax:
Employees.DNo = Departments.DNo AND Departemnts.DNo = Projects.Dno
The result of number of tuples is equal to the product of the number of tuples of each table.

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 the right table; the join will still return a row in the result, but with
NULL in each column from the 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_field = table2.common_field;

Order Table:
+-----+---------------------+-------------+--------+
| OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
Cutomer Table:
+----+----------+-----+-----------+----------+
| 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 | Muffy | 24 | Indore | 10000.00|
+----+----------+-----+-----------+----------+

Example:
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS LEFT JOIN ORDERS ON
CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
This would produce the following result −
+----+----------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+----+----------+--------+---------------------+
| 1 | Ramesh | NULL | NULL |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 4 | Chaitali| 2060 | 2008-05-20 00:00:00 |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
| 7 | Muffy | 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 the left table;
the join will still return a row in the result, but with NULL in each column from the 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.
Example:
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS RIGHT JOIN ORDERS ON
+-----+---------------------+-------------+--------+
| OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+

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 the 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_field = table2.common_field;

Example:
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS FULL JOIN ORDERS ON
+------+----------+--------+---------------------+
+------+----------+--------+---------------------+
| 1 | Ramesh | NULL | NULL |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
| 7 | Muffy | NULL | NULL |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+

If your Database does not support FULL JOIN (MySQL does not support FULL
JOIN), then you can use UNION ALL clause to combine these two JOINS
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS LEFT JOIN ORDERS ON
CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION ALL
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS RIGHT JOIN ORDERS ON
CUSTOMERS.ID = 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:
The basic syntax of SELF JOIN is as follows −
SELECT a.column_name, b.column_name... FROM table1 a, table1
b WHERE a.common_field = b.common_field;

Cross Join:
The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of
records from two or more joined tables. Thus, it equates to an inner join where the
join-condition always evaluates to either True or where the join-condition is absent
from the statement.
Syntax:
The basic syntax of the CARTESIAN JOIN or the CROSS JOIN is as follows −
SELECT table1.column1, table2.column2... FROM table1,
table2 [, table3 ]

Example:
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS, ORDERS;
+----+----------+--------+---------------------+
+----+----------+--------+---------------------+
| 1 | Ramesh | 3000 | 2009-10-08 00:00:00 |
| 1 | Ramesh | 1500 | 2009-10-08 00:00:00 |
| 1 | Ramesh | 1560 | 2009-11-20 00:00:00 |
| 1 | Ramesh | 2060 | 2008-05-20 00:00:00 |
| 2 | Khilan | 3000 | 2009-10-08 00:00:00 |
| 2 | Khilan | 1500 | 2009-10-08 00:00:00 |
| 2 | Khilan | 2060 | 2008-05-20 00:00:00 |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1560 | 2009-11-20 00:00:00 |
| 3 | kaushik | 2060 | 2008-05-20 00:00:00 |
| 4 | Chaitali | 3000 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 1500 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 1560 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
| 5 | Hardik | 3000 | 2009-10-08 00:00:00 |
| 5 | Hardik | 1500 | 2009-10-08 00:00:00 |
| 5 | Hardik | 1560 | 2009-11-20 00:00:00 |
| 5 | Hardik | 2060 | 2008-05-20 00:00:00 |
| 6 | Komal | 3000 | 2009-10-08 00:00:00 |
| 6 | Komal | 1500 | 2009-10-08 00:00:00 |
| 6 | Komal | 1560 | 2009-11-20 00:00:00 |
| 6 | Komal | 2060 | 2008-05-20 00:00:00 |
| 7 | Muffy | 3000 | 2009-10-08 00:00:00 |
| 7 | Muffy | 1500 | 2009-10-08 00:00:00 |
| 7 | Muffy | 1560 | 2009-11-20 00:00:00 |
| 7 | Muffy | 2060 | 2008-05-20 00:00:00 |
+----+----------+--------+---------------------+

PL/SQL:
PL/SQL is a combination of SQL along with the procedural features of programming
languages. It was developed by Oracle Corporation in the early 90's to enhance the
capabilities of SQL.
Features of PL/SQL
PL/SQL has the following features −
•PL/SQL is tightly integrated with SQL.
•It offers extensive error checking.
•It offers numerous data types.
•It offers a variety of programming structures.
•It supports structured programming through functions and procedures.
•It supports object-oriented programming.
•It supports the development of web applications and server pages.

Advantages of PL/SQL
PL/SQL has the following advantages −
•SQL is the standard database language and PL/SQL is strongly integrated with SQL.
PL/SQL supports both static and dynamic SQL. Static SQL supports DML operations
and transaction control from PL/SQL block. In Dynamic SQL, SQL allows embedding
DDL statements in PL/SQL blocks.
•PL/SQL allows sending an entire block of statements to the database at one time. This
reduces network traffic and provides high performance for the applications.
•PL/SQL gives high productivity to programmers as it can query, transform, and
update data in a database.
•PL/SQL saves time on design and debugging by strong features, such as exception
handling, encapsulation, data hiding, and object-oriented data types.
•Applications written in PL/SQL are fully portable.
•PL/SQL provides high security level.
•PL/SQL provides access to predefined SQL packages.
•PL/SQL provides support for Object-Oriented Programming.
•PL/SQL provides support for developing Web Applications and Server Pages.

S.No Sections & Description
1
Declarations
This section starts with the keyword DECLARE. It is an optional section
and defines all variables, cursors, subprograms, and other elements to be
used in the program.
2
Begin
This section is enclosed between the keywords BEGIN and END and it is a
mandatory section. It consists of the executable PL/SQL statements of the
program. It should have at least one executable line of code, which may be
just a NULL command to indicate that nothing should be executed.
3
Exception Handling
This section starts with the keyword EXCEPTION. This optional section
contains exception(s) that handle errors in the program.
PL/SQL which is a block-structured language; this means that the PL/SQL
programs are divided and written in logical blocks of code. Each block consists
of three sub-parts −

Every PL/SQL statement ends with a semicolon (;). PL/SQL blocks can be nested
within other PL/SQL blocks using BEGIN and END. Following is the basic structure
of a PL/SQL block −
DECLARE <declarations section>
BEGIN <executable command(s)>
EXCEPTION <exception handling>
END;
The 'Hello World' Example
DECLARE
message varchar2(20):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END; /

PL/SQL Character Set
The basic character set includes:
Upper case alphabets A-Z
Lower case alpabets a-z
Numerals 0-9
Symbola (),+.-,*,&,%,@,# etx

S.No Date Type & Description
1
Numeric
Numeric values on which arithmetic operations are
performed.
2
Character
Alphanumeric values that represent single characters
or strings of characters.
3
Boolean
Logical values on which logical operations are
performed.
4
Datetime
Dates and times.
PL/SQL Scalar Data Types
PL/SQL Scalar Data Types come under the following categories −

Variable Declaration in PL/SQL
PL/SQL variables must be declared in the declaration section or in a package as a
global variable. When you declare a variable, PL/SQL allocates memory for the
variable's value and the storage location is identified by the variable name.
The syntax for declaring a variable is −
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]
Where, variable_name is a valid identifier in PL/SQL, datatype must be a valid
PL/SQL data type. Some valid variable declarations along with their definition
are shown below −
sales number(10, 2);
pi CONSTANT double precision := 3.1415;
name varchar2(25);
address varchar2(100);

It is a good programming practice to initialize variables properly otherwise,
sometimes programs would produce unexpected results.
Example:
DECLARE a integer := 10;
b integer := 20;
c integer;
f real;
BEGIN c := a + b;
dbms_output.put_line('Value of c: ' || c);
f := 70.0/3.0;
dbms_output.put_line('Value of f: ' || f);
END;

Variable Scope in PL/SQL
PL/SQL allows the nesting of blocks, i.e., each program block may contain another
inner block. If a variable is declared within an inner block, it is not accessible to
the outer block. However, if a variable is declared and accessible to an outer block,
it is also accessible to all nested inner blocks.
There are two types of variable scope −
•Local variables − Variables declared in an inner block and not accessible to outer
blocks.
•Global variables − Variables declared in the outermost block or a package.

Following example shows the usage of Local and Global variables in its
simple form −
DECLARE -- Global variables
num1 number := 95;
num2 number := 85;
BEGIN
dbms_output.put_line('Outer Variable num1: ' || num1);
dbms_output.put_line('Outer Variable num2: ' || num2);
DECLARE -- Local variables
num1 number := 195;
num2 number := 185;
BEGIN
dbms_output.put_line('Inner Variable num1: ' || num1);
dbms_output.put_line('Inner Variable num2: ' || num2);
END;
END;
When the above code is executed, it produces the following result −
Outer Variable num1: 95
Outer Variable num2: 85
Inner Variable num1: 195
Inner Variable num2: 185

Constant:
A constant holds a value that once declared, does not change in the
program
Declaring a Constant:
A constant is declared using the CONSTANT keyword. It requires an initial
value and does not allow that value to be changed.
For example −
PI CONSTANT NUMBER := 3.141592654;

The PL/SQL Literals
A literal is an explicit numeric, character, string, or Boolean value not represented by
an identifier. For example, TRUE, 786, NULL, 'tutorialspoint' are all literals of type
Boolean, number, or string. PL/SQL, literals are case-sensitive.
PL/SQL supports the following kinds of literals −
•Numeric Literals
•Character Literals
•String Literals
•BOOLEAN Literals
•Date and Time Literals
The following table provides examples from all these categories of literal values.

S.N
o
Literal Type & Example
1
Numeric Literals
050 78 -14 0 +32767
6.6667 0.0 -12.0 3.14159 +7800.00
6E5 1.0E-8 3.14159e0 -1E38 -9.5e-3
2
Character Literals
'A' '%' '9' ' ' 'z' '('
3
String Literals
'Hello, world!'
'Tutorials Point'
'19-NOV-12'
4
BOOLEAN Literals
TRUE, FALSE, and NULL.
5
Date and Time Literals
DATE '1978-12-25';
TIMESTAMP '2012-10-29 12:01:01';

Control Structure:
Decision-making structures require that the programmer specify one or more
conditions to be evaluated or tested by the program, along with a statement or
statements to be executed if the condition is determined to be true, and optionally,
other statements to be executed if the condition is determined to be false.
Types of IF Statement
Simple IF
IF then Else
IF then ElseIf

Simple IF
It is the simplest form of the IF control statement, frequently used in decision-
making and changing the control flow of the program execution.
The IF statement associates a condition with a sequence of statements enclosed
by the keywords THEN and END IF. If the condition is TRUE, the statements get
executed, and if the condition is FALSE or NULL, then the IF statement does
nothing.
Syntax
Syntax for IF-THEN statement is −
IF condition THEN
S;
END IF;
Where condition is a Boolean or relational condition and S is a simple or
compound statement. Following is an example of the IF-THEN statement −
IF (a <= 20)THEN
c:= c+1;
END IF;

If Then Else:
A sequence of IF-THEN statements can be followed by an optional sequence
of ELSE statements, which execute when the condition is FALSE.
Syntax
Syntax for the IF-THEN-ELSE statement is −
IF condition THEN
S1;
ELSE S2;
END IF;
Where, S1 and S2 are different sequence of statements. In the IF-THEN-ELSE
statements, when the test condition is TRUE, the statement S1 is executed and S2 is
skipped; when the test condition is FALSE, then S1 is bypassed and statement S2 is
executed.
For example −
IF color = red THEN
dbms_output.put_line('You have chosen a red car')
ELSE
dbms_output.put_line('Please choose a color for your car');
END IF;

IF-THEN-ELSIF
This statement allows you to choose between several alternatives. An IF-
THEN statement can be followed by an optional ELSIF...ELSE statement.
The ELSIF clause lets you add additional conditions.
When using IF-THEN-ELSIF statements there are a few points to keep in mind.
•An IF-THEN statement can have zero or one ELSE's and it must come after any
ELSIF's.
•An IF-THEN statement can have zero to many ELSIF's and they must come
before the ELSE.
•Once an ELSIF succeeds, none of the remaining ELSIF's or ELSE's will be tested.
Syntax

The syntax of an IF-THEN-ELSIF Statement in PL/SQL programming
language is −
IF(boolean_expression 1)THEN
S1; -- Executes when the boolean expression 1 is true
ELSIF( boolean_expression 2) THEN
ELSIF( boolean_expression 3) THEN
ELSE
S4; -- executes when the none of the above condition is true
END IF;

Iterative Statements:
Programming languages provide various control structures that allow for more
complicated execution paths.
A loop statement allows us to execute a statement or group of statements
multiple times
Types of Looping Statement
•Basic Loop
•While loop
•For loop

Basic Loop:
Basic loop structure encloses sequence of statements in between
the LOOP and END LOOP statements. With each iteration, the sequence of
statements is executed and then control resumes at the top of the loop.
Syntax
The syntax of a basic loop in PL/SQL programming language is −
LOOP
Sequence of statements;
END LOOP;

Example
DECLARE x number := 10;
BEGIN LOOP
dbms_output.put_line(x);
x := x + 10;
IF x > 50 THEN
exit;
END IF;
END LOOP; -- after exit, control resumes here
dbms_output.put_line('After Exit x is: ' || x); END;
When the above code is executed at the SQL prompt, it produces the following result −
10
20
30
40
50
After Exit x is: 60

AWHILE LOOP
While loop statement in PL/SQL programming language repeatedly executes a
target statement as long as a given condition is true.
Syntax:
WHILE condition LOOP
sequence_of_statements
END LOOP;
Example:
DECLARE a number(2) := 10;
BEGIN
WHILE a < 20 LOOP
dbms_output.put_line('value of a: ' || a);
a := a + 1;
END LOOP;
END;

When the above code is executed at the SQL prompt, it produces the following result −
value of a: 10
value of a: 11
value of a: 12
value of a: 13
value of a: 14
value of a: 15
value of a: 16
value of a: 17
value of a: 18
value of a: 19

FOR LOOP
For loop is a repetition control structure that allows you to efficiently write a
loop that needs to execute a specific number of times.
Syntax
FOR counter IN initial_value .. final_value LOOP
sequence_of_statements;
END LOOP;

Example
DECLARE a number(2);
BEGIN
FOR a in 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
When the above code is executed at the SQL prompt, it produces the following
result −
value of a: 10
value of a: 11
value of a: 12
value of a: 13
value of a: 14
value of a: 15
value of a: 16
value of a: 17
value of a: 18
value of a: 19
value of a: 20

Reverse FOR LOOP Statement
By default, iteration proceeds from the initial value to the final value, generally
upward from the lower bound to the higher bound. You can reverse this order by
using the REVERSE keyword. In such case, iteration proceeds the other way.
After each iteration, the loop counter is decremented.
However, you must write the range bounds in ascending (not descending) order.
Syntax
FOR counter IN reverese initial_value .. final_value LOOP
sequence_of_statements;
END LOOP;

The following program illustrates this −
DECLARE a number(2) ;
BEGIN FOR a IN REVERSE 10 .. 20 LOOP dbms_output.put_line('value of a: ' ||
a);
END LOOP;
END;
When the above code is executed at the SQL prompt, it produces the following
result −
value of a: 20
value of a: 19
value of a: 18
value of a: 17
value of a: 16
value of a: 15
value of a: 14
value of a: 13
value of a: 12
value of a: 11
value of a: 10

Structure Query Language (SQL).pptx

Recommended

More Related Content

Similar to Structure Query Language (SQL).pptx (20)

Recently uploaded (20)

Structure Query Language (SQL).pptx