Transcript SQL-DBMS

Welcome you to the world of
SQL-DBMS
INDEX
LESSON -NO
Lesson 1 : Getting
TOPIC
PAGE -NO
Introduction to Database
1
Started With
Database Management System
2
Database
History of RDBMS
3
Characteristics of DBMS
4
Database Model
5
Types of Data Model
6
Logical Model
7
Hierarchical Database Model
8
Network Database Model
10
Relational Database Model
12
Relational Database Concept
1. Advantages and Disadvantages of
RDBMS
13
© 2014 PANZRA SoftTech Private Limited
I
INDEX
LESSSON-NO
TOPICS
Lesson 2 : Basic of SQL
Lesson 3 : Operators
PAGE -NO
Components of Table in RDBMS
15
Database Administration
16
Structure of DBMS
17
Introduction To SQL
19
Software System of RDBMS
20
Basic Of SQL
21
Basic Structure Of SELECT Statement
22
The SELECT Statement
23
The WHERE Clause
24 ,25
Operators In SQL
Comparison Operator
26 ,27
Concatenation Operator
28
II
© 2014 PANZRA SoftTech Private Limited
INDEX
LESSON -NO
Lesson 4: Functions
TOPICS
PAGE -NO
Logical Operator
29 , 30
Arithmetic Operator
31
ORDER BY Clause
32
Functions In SQL
33
Types Of Functions
34
I ]Single Row Function
35
1]Character Function
i)Case Conversion Function
ii)Character Manipulation Function
2]Number Function-Round, Trunc, Mod
3]Date Function
4]Conversion Function
i) TO_Char
ii) TO_Number and TO_Date
© 2014 PANZRA SoftTech Private Limited
36 ,37, 38
39, 40, 41, 42
43, 44
45, 46, 47
48, 49, 50
III
INDEX
LESSON -NO
TOPICS
PAGE -NO
Lesson 5 :
II] Multiple Row Function
51
Group Functions
Count Function
52
Distinct Keyword
53
GROUP BY Function
HAVING Clause
Lesson 6 : Joins
Lesson 7 : Sub Query
54, 55
56
Miscellaneous Functions
57, 58
Joins In SQL
59 , 65
Types Of Joins
60
Self Join And Equi Join
61
NonEqui Join And Outer Join
62
Left Join And Right Join
63
Full Outer Join and Cross Join
64
Sub Query
66
Correlated Sub Query
67
© 2014 PANZRA SoftTech Private Limited
IV
INDEX
LESSSON-NO
Lesson 8 : Set Operations
Lesson 9 : DML
Lesson 10 : Data Types
TOPICS
PAGE -NO
Exit/Not Exit Operator
68
Union , Union All Operators
69
Minus Operators
70
Data Manipulation Language
71
UPDATE And DELETE Statement
72
Transaction Control Statements
73
Data Types
74, 75
Data Integrity
76
Lesson 11 :
Database Objects – 1) Table
77
Database Object
i) Create Statement
78
ii) Alter iii) Drop Statement
79
iv) Truncate Statement
80
Table Constraints - Not Null
81
© 2014 PANZRA SoftTech Private Limited
V
INDEX
TOPICS
LESSON -NO
PAGE –NO
Unique and Primary Key Constraint
82
Foreign key and Check Constraint
83
2) View
84
3) Sequence
85, 86
4) Index
87, 88
5) Synonyms
89, 90
Other Database Objects
Lesson 12: Transaction Transaction Controlling
With COMMIT And ROLLBACK
Controlling
91
92, 93, 94 ,73
Statement
Lesson 13: DCL
Data Controlling Language - GRANT
95
REVOKE Statement
96
Privileges And roles
97
© 2014 PANZRA SoftTech Private Limited
VI
Lesson 1: Getting Started with Database
Introduction to Database
“A set of information held in a computer”
•
Oxford English Dictionary
“One or more large structured sets of persistent data, usually associated with software to update
and query the data”
•
Free On-Line Dictionary of Computing
“A collection of data arranged for ease and speed of search and retrieval”
•
Dictionary.com
Definition- Collection of related data.
Example: Database of collage will contain information about teacher, student , class rooms.
1
© 2014 PANZRA SoftTech Private Limited
Database Management System (DBMS)
 A DBMS consist of a Collection of interrelated data and a set
of programs to access that data. A set of programs that is used
to store and manipulate data. Manipulation of data includesadding new data , deleting unwanted data , changing existing
data.
 A DBMS is a software package to facilitate the creation and
maintenance of a computerized database.
 DBMS contains information about a particular enterprise
 DBMS provides an environment that is both convenient and
efficient to use.
 Some available database management systems are
DB2,Oracle,MS Access, SQL Server, Ingres, Postgre SQL,
MYSQL , Berkeley DB, Times Ten, Rdb
2
© 2014 PANZRA SoftTech Private Limited
History Of RDBMS
3
© 2014 PANZRA SoftTech Private Limited
Characteristics Of DBMS
Represents complex relationship between data
Controls data redundancy
Enforces user define rules
Ensures data sharing
It has automatic and intelligent backup and recovery procedures
It has central dictionary to store information
Pertaining to data and its manipulation
It has different interface via which user can manipulate the data
Enforce data access authorization
4
© 2014 PANZRA SoftTech Private Limited
Database Model
 Collection of logical constructs used to represent data structure and
relationship within the database.
 System designers develop models to explore ideas and to improve the
understanding of database design.
 There are three types of database models
Conceptual
Data Model
• Description of application that users can
understand
• Example : ER model entities and
relationships
Record Based
Data Model
• It is collection of table definitions
describing the data
• Example : Relational model
Physical Data
Model
• Description of how the logical data model is
represented in storage
• Example : Unifying model, Frame memory
5
© 2014 PANZRA SoftTech Private Limited
Types Of Data Model
CONCEPTUAL MODEL
• It describes the semantics of a domain (the scope) For example, it may be a model of the
interest area of an organization or of an industry.
• This consists of entity classes, representing kinds of things of significance in the domain,
and relationships assertions about associations between pairs of entity classes. A
conceptual schema specifies the kinds of facts that can be expressed using the model.
LOGICAL MODEL
• Describes the structure of some domain of information.
• This consists of descriptions of (for example) tables, columns, object-oriented classes, and
XML tags. The logical schema and conceptual schema are sometimes implemented as one
and the same.
PHYSICAL MODEL
• Describes the physical means used to store data. This is concerned with partitions,
CPUs, table spaces and the like.
• Storage technology can change without affecting the logical or the conceptual schema.
• The table/column structure can change without (necessarily) affecting the conceptual
schema.
6
© 2014 PANZRA SoftTech Private Limited
Logical Model
 Record based data model is also called as logical or
implementation model. This model is again divided
in the three models . And they are Hierarchical ,Network,
Relational model.
Implementation
Database
Models
Hierarchical
Data is stored in the form
of tree
Network
Data is stored along with
pointers which specify the
relationship between
entities
Relational
Data is stored in the form
of tables
7
© 2014 PANZRA SoftTech Private Limited
Hierarchical Database Model
 Logically represented by an upside down tree
– Each parent can have many children
– Each child has only one parent
8
© 2014 PANZRA SoftTech Private Limited
Hierarchical Database Model
Advantages
•
•
•
•
Conceptual simplicity
Database security and integrity
Data independence
Efficiency
Disadvantages
•
•
•
•
•
Complex implementation
Difficult to manage and lack of standards
Lacks structural independence
Applications programming and use complexity
Implementation limitations
9
© 2014 PANZRA SoftTech Private Limited
Network Database Model
Each record can have multiple parents
– Composed of sets
– Each set has owner record and member record
– Member may have several owners
10
© 2014 PANZRA SoftTech Private Limited
Network Database Model
Advantages
• Conceptual simplicity
• Handles more relationship types
• Data access flexibility
• Promotes database integrity
• Data independence
• Conformance to standards
Disadvantages
• System complexity
• Lack of structural independence
© 2014 PANZRA SoftTech Private Limited
11
Relational Database Model
 Relational Database Management System – a database system
made up of files with data elements in two-dimensional array (rows
and columns). This database management system has the capability
to recombine data elements to form different relations resulting in
a great flexibility of data usage.
 Data and relationships are represented as tables each has a number
of columns relating to fields or attributes.
 Perceived by user as a collection of table for database. A relational
database uses two dimensional tables to store information. Tables
are a series of row/column intersections.
 Tables related by sharing common entity characteristic(s)
 There is a relationship between the two tables.
 SQL is used to manipulate the relational databases.
12
© 2014 PANZRA SoftTech Private Limited
Relational Database Concept
 The principles of the relational model were first outline by Dr.
E. F. Codd in a June 1970 paper title “ A Relational Model Of
Data For Large Shared Data Banks”. In this paper Dr . Codd
proposed the relational model for database system.
It is the basis for the relational database management
system.
 The relational model contains the following components:
1. Collection of objects or relations.
2. Set of operations to act on the relations.
3. Data integrity for accuracy and consistency.
13
© 2014 PANZRA SoftTech Private Limited
Relational Database Model
Advantages
• Structural independence
• Improved conceptual simplicity
• Easier database design, implementation, management and use
• Ad hoc query capability with SQL
• Powerful database management system
Disadvantages
• Substantial hardware and system software overhead
• Poor design and implementation is made easy
• May promote “islands of information ” problem
14
© 2014 PANZRA SoftTech Private Limited
Components Of Table In RDBMS
Tuple/Row
• Single row in the table is called as tuple.
Each row represents the data of the single
entity.
Attribute/Column
• A column stores an attribute of an entity. In
this image Roll No, Name , Phone are
attributes.
Column Name
• Each column in table is given a name. This
name is used to refer to value in the
column.
Table Name
• Each table is given a name. This is used to
refer to the table.
15
© 2014 PANZRA SoftTech Private Limited
Database Administration
 Each database requires at least one database administrator to administer it.
Because a DBMS can be large and can have many users, often this is not a one
person job. In such cases, there is a group of DBAs who share responsibility.
 A database administrator's responsibilities can include the following tasks:
1.Installing and upgrading the Oracle server and application tools
2.Allocating system storage and planning future storage requirements for the
database system
3.Creating primary database storage structures (table spaces) after application
developers have designed an application
© 2014 PANZRA SoftTech Private Limited
16
Structure Of DBMS
 A DBMS is partitioned into
modules that deal with each
of the responsibilities of the
overall system.
 The design of DBMS must
include consideration of
interface between database
system and operating
system
 Figure shows the three level
or tier architecture
proposed by ANSI/SPARC.
Front End
Used for screen development and user
interaction. Example VB, Developer2001
Back End
Used for storing and management of data.
Example MS Access , Oracle
Middleware
Used for connectivity purpose. Example
ODBC, JDBC
17
© 2014 PANZRA SoftTech Private Limited
Structure Of DBMS
 A Functional components of
DBMS can be divided into
two categories as follows
1.Query Processor
2.Storage Manager
 Query processor component
consist of DML compiler,
Embedded DML Compiler ,
DDL Interpreter, Query
evaluation engine
 Storage manager component
consist of Authorization and
integrity manager,
Transaction manager, File
manager , Buffer manager
18
© 2014 PANZRA SoftTech Private Limited
INTRODUCTION TO SQL
 SQL stands for Structured Query Language.
 Common Language For Variety of Databases. SQL uses combination of relational
algebra and calculus constructs. It can define the structure of data, modify data
and specify security constraints. Both ANSI and ISO have accepted SQL as the
standard language for relational database.
 SQL commands are divided into categories, depending upon what they do
DML-Data Manipulation Language, DDL -Data Definition Language, DCL -Data
Control Language
 The SQL language is subdivided into several language elements, including:
Clauses, which are constituent components of statements and queries.
Expressions, which can produce tables consisting of columns and rows of data
Predicates, which specify conditions that are used to limit the effects of
statements and queries, or to change program flow.
Queries, which retrieve the data based on specific criteria.
Statements can control transactions, program flow, connections and sessions. SQL
statements also include the semicolon (";") statement terminator.
© 2014 PANZRA SoftTech Private Limited
19
Software System Of DBMS
SQL Statements
DML = SELECT , INSERT ,UPDATE , DELETE , MERGE
DDL = CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMIT
DCL/Data Administration = GRANT,REVOKE
Transaction Control/Application Generation=COMMIT, ROLLBACK, SAVEPOINT
20
© 2014 PANZRA SoftTech Private Limited
Basic of SQL
Where to use SQL
• SQL*Plus ,TOAD, SQL Navigator, ODBC Supported Connections , Excel , Access , Lotus 1-2-3,
Heart of PL/SQL
Pros
• Very flexible , Universel (Oracle, Access, Paradoxe) ,
• Relatively Few Commands to Learn, Data Independence , Non Procedurality , Support
higher level language
Cons
• Requires detailed knowledge of the structure of the Database
• Can provide misleading results
SQL Statements
•
•
•
•
•
SQL statement are not case-sensitive.
Can be on one or more lines.
Keywords can not be abbreviated or split across lines.
Clauses are generally placed on separate lines.
Indent are used to enhance readability.
© 2014 PANZRA SoftTech Private Limited
21
BASIC STRUCTURE OF SELECT STATEMENT
 Basic SELECT statement
SELECT * |{ Column | Expression [alias] , ….}
FROM
table ;
In the above syntax,
SELECT

is a list of one or more column
*

selects all column
Column|Expression  selects the named column or expression
Alias

gives selected column different headings
FROM table 
specify the table containing the column
;
 defines the end of an SQL statement
22
© 2014 PANZRA SoftTech Private Limited
The SELECT statement
 SELECT statement defines WHAT is to
be returned (separated by commas)
 Database columns (From Tables or
Views)
 Constant Text Values
 Formulas
 Pre-defined Functions
 Group Functions (COUNT, SUM, MAX,
MIN, AVG)
 “*” mean all columns from all tables In
the FROM Statement
 Example: SELECT region_name, ..
 FROM statement defines the Table(s)
or View(s) used by the SELECT or
WHERE Statements
 You MUST Have a FROM statement
 Multiple Tables/Views are separated by
Commas
© 2014 PANZRA SoftTech Private Limited
23
The SELECT Statement
Column Alias
 Renames a column heading.
 Is useful with calculation.
 Immediately follows the column name.(there can also be the optional AS keyword
between the column name and alias)
 Requires double quotation marks if it contain spaces ,special character or if it is case
sensitive
SELECT first_name AS name, last_name surname, salary*12 “Annual Salary”
FROM employees;
When we run this query , what output we actually get is a ‘table’ which has column
name as NAME, SURNAME, Annual Salary
WHERE Clause
 WHERE clause is optional. When used it defines what records are to be included in the
query.
Syntax ,
SELECT column_name, column_name,…
FROM
table_name
WHERE condition(s);
24
© 2014 PANZRA SoftTech Private Limited
The WHERE Clause
WHERE  restricts the query to rows that meet a condition
Condition  is composed of column name, expression, constants and comparison
operator
Example
SELECT *
FROM Customers
WHERE Country = 'Mexico‘ ;
This query gives us a table named Customer which contain all column but shows only those
records whose country is Mexico
WHERE clause may consist of








Conditional Operators [=, >, >=, <, <=, != (<>)=, (<>)]
BETWEEN x AND y
IN (list)
LIKE ‘‘%string’’
IS NULL or NOT {BETWEEN / IN / LIKE / NULL}
Multiple conditions linked with AND & OR statements
Strings contained within SINGLE QUOTES!!
An Alias can not be used in the WHERE clause.
© 2014 PANZRA SoftTech Private Limited
25
OPERATORS IN SQL
Comparison operators are used
in the conditions that compare one expression
to another expression or value.
They are used in the WHERE clause
Syntax
….WHERE expr operator value ;
Example
…WHERE last_name = ‘Joshi’;
…WHERE salary > (or)>=(or)<(or)<= 8000;
BETWEEN…AND… OPERATOR
This operator is used to display rows based
on a range of values.The range that you
specify contains a lower limit and upper
limit. And you must specify lower limit first.
Example
…WHERE salary BETWEEN 4500 AND 8500;
…WHERE name BETWEEN ‘NEHA’ AND ‘GITA’;
© 2014 PANZRA SoftTech Private Limited
26
COMPARISION OPERATOR
IN ( SET )
This operator is used to test for values in the list. The IN operator can be used with any
data type. If character or dates are used in the list, they must be enclosed in a single
quotation mark (‘ ’).
Example
…WHERE emp_id IN (100 ,101 ,201 );
The result of this query will be a table with specified columns for all the records
whose emp_id is 100 , 101 or 201
LIKE
The LIKE operator is used to perform a wildcard searches of valid search string value. You
can select rows that match a character pattern by LIKE operator. Pattern matching is
referred to as a wildcard search. Search condition can contain literal characters,
numbers. %  denotes zero or many character
_  denotes one character
Example
…WHERE first_name LIKE ‘s%’;
This query gives the table that shows all the specified record whose first name starts
with ‘s’.
© 2014 PANZRA SoftTech Private Limited
27
CONCATENATION OPERATOR
IS NULL
This operator is used to test for null. A null value means the value is unavailable,
Unassigned, unknown or inapplicable
Example
…WHERE student_marks IS NULL ;
This query gives us a table ,that shows specified column with all the records that
contain a null value for student_marks column.
Concatenation operator
Using this (||)operator we can link column to other column, arithmetic expression
or constant values to create a character expression.
Example
SELECT
FROM
last_name || job_id AS “ Employee”
employees ;
This query gives us a table having column name Employee and record shows the
concatenation of last name and job id.
28
© 2014 PANZRA SoftTech Private Limited
LOGICAL OPERATOR
LOGICAL OPERATOR
A logical operator combines the
result of two components condition
to produce a single result based on
those Conditions or it inverts the
result of single condition . A row is
return only if the overall result
of the condition is true. Three logical
operators are AND,OR ,NOT
AND
And operator requires both conditions to be true.
Example
SELECT
FROM
WHERE
AND
last_name , job_id , salary
employees
salary >= 10000
job_id LIKE ‘ %MAN% ’ ;
This query gives us a table having specified column and only those records that have salary
more than 10000 and job_id contain a string …MAN..
© 2014 PANZRA SoftTech Private Limited
29
LOGICAL OPERATOR
OR
OR operator requires either condition to be true.
Example
SELECT
FROM
WHERE
OR
last_name , job_id , salary
employees
salary >= 10000
job_id LIKE ‘ %MAN% ’ ;
In this example either condition to be true for any record to be selected. Therefore
any employee who has a job id that contain a string ‘MAN’ or earns 10000 or
more is selected.
NOT
NOT operator returns value if the condition is false. NOT operator can be used with
comparison operators e.g. NOT IN (SET) , IS NOT NULL , NOT LIKE , NOT BETWEEN..AND..
Example
…WHERE job_id NOT IN (ST_CLERK , SA_REP);
…WHERE salary NOT BETWEEN 35000 AND 6000;
…WHERE last_name NOT LIKE ‘ %A% ’ ;
30
© 2014 PANZRA SoftTech Private Limited
ARITHMETIC OPERATORS
Arithmetic Operator
Arithmetic operators are used to create
an expression with number and date data.
You may need to modify the way in which
data is displayed or may want to perform
calculations. They are possible with
addition , subtraction ,multiplication ,
division. Following are the examples for
arithmetic expression.
SELECT
FROM
last_name , salary , salary+500
employees;
In the result table we get a third column salary+500 and column contain the
added salary for every record in the table.
SELECT last_name, salary, 12*salary + 500
FROM employees;
31
© 2014 PANZRA SoftTech Private Limited
ORDER BY CLAUSE
ORDER BY CLAUSE
The order of rows that are return in a query result is undefined . The ORDER
BY clause can be used to sort the rows . If you use the ORDER BY clause , It
Must be the last clause of the SQL statement.
Syntax [ ORDER BY {column , expression , numeric _position}[ASC|DESC] ];
ORDER BY  specifies the order in which the retrieved rows are displayed
ASC
 orders the rows in ascending order(default order)
DESC
 orders the rows in descending order
Example
SELECT
FROM
ORDER BY
*
reunion
priority ASC ;
In the table ,we can see “priority”
Column is in ascending order
32
© 2014 PANZRA SoftTech Private Limited
FUNCTIONS IN SQL
 Function are a very powerful feature
of SQL and can be used to do the
following :
Perform calculations on data
Modify individual data items
Manipulate output for group of rows
Format date and number for display
Convert column data types
SQL function may accept argument
and always return a value.
 There are two distinct types of
function
 Single-row function
 Multiple-row function
33
© 2014 PANZRA SoftTech Private Limited
TYPES OF FUNCTIONS

Single-Row Function:These functions operate on
single row only and returns
one result per Row . There
are different type of single
row function. This lesson
covers the following ones.
Character
Number
Date
Conversion
 Multiple-row function:These function manipulate groups of rows to give one result per group
of rows.
34
© 2014 PANZRA SoftTech Private Limited
SINGLE ROW FUNCTION
Single row functions
It Manipulate data items , Accept arguments and return one value ,
Act on each row returned , Return one result per row , May modify
the datatype , Can be nested.
Syntax Function_name(column| expression, [arg1 , arg2 ,………])
They accept one or more argument and return one value for each row
returned by the query. An argument can be one of the following : user
supplied constant Variable value , Column name , Expression
1) Character function : Accept character input and can return both
character and number values
2) Number function : Accept numeric input and return numeric values
3) Date function : Operate on values of the date datatype (All date
function , which returns number.)
4) Conversion Function : convert a value from one datatype to another
35
© 2014 PANZRA SoftTech Private Limited
Case Conversion Function
Single-Row character Function accept
character data as input and can return
both character and number values .
Character function can be divides into
the following
– Case conversion function
– Character manipulation function
 Case Conversion Function
LOWER , UPPER ,and INITCAP are the
three case conversion function
LOWER :- convert mixed case or uppercase character string to lowercase
UPPER :-convert mixed case or lowercase character string to uppercase
INITCAP:- converts first letter of each word to uppercase and remaining
letter to lowercase
36
© 2014 PANZRA SoftTech Private Limited
USING CASE CONVERSION FUNCTION
37
© 2014 PANZRA SoftTech Private Limited
Character manipulation function
 Character Manipulation Functions
are
CONCAT , SUBSTR , LENGTH ,INSTR, LPAD and TRIM
CONCAT :- join values together (you are limited
to using two parameters with CONCAT.)
SUBSTR :- extracts a string of determined length
LENGTH:- show the length of a string as a numeric value
INSERT :- finds numeric position of named character
LPAD:- pads the character value right-justified
RPAD :character manipulation function pads the
character value left-justified
TRIM: trim heading or trailing
character from character string.
If trim _charcater or trim_source
is character literal, you must
enclose it in single quotes
In this image we can see how
character functions are used.
© 2014 PANZRA SoftTech Private Limited
38
Number Function
 Number
function accept
numeric input
and return
numeric values .
This section
describe some
of the number
function.
ROUND, TRUNC,
MOD are the
number
functions. In this
image we can
see, how
number
functions work.
39
© 2014 PANZRA SoftTech Private Limited
ROUND FUNCTION
 The ROUND function rounds the column , expression , or value to n
decimal places . if the second argument is 0 or is missing , the value is
rounded to two decimal places .If the second argument is 2,the value
is rounded to the 2 decimal places. Conversely , if the second argument
is -2 , the value is rounded to two decimal place to the left.
 The ROUND function can also be used with date function .
 The DUAL is a dummy table .the DUAL table is generally used for
SELECT statement.
40
© 2014 PANZRA SoftTech Private Limited
TRUNC FUNCTION
 The TRUNC function truncates the column , expression , or value to n
decimal place.
 The TRUNC function works with argument similar to those of the ROUND
function . if the second argument is 0 or is missing , the value is truncated
to zero decimal places.
 If the second argument is 2 ,the value is truncated to two decimal places.
 Conversely , if the second argument is 2 , the value is truncated to two
decimal places to the left
 Like the ROUND function , the TRUNC function can be used with date
function
41
© 2014 PANZRA SoftTech Private Limited
MOD FUNCTION
The MOD function finds the remainder of the first argument divided by
second argument. The MOD function is often used to determine if the
value is odd or even. See the example ,
42
© 2014 PANZRA SoftTech Private Limited
DATE FUNCTION
 In SQL the default date format is DD-MON-RR.
 It enables you to store 21st century dates in the 20th century by specifying
only the last two digits of the year.
 It enables you to store 20th century dates in the 21st century in the same
way.
 When a record with the date column is inserted into a table, the century
information is picked up from the SYSDATE function. SYSDATE is a function
that returns a date and time. Following are the date function.
43
© 2014 PANZRA SoftTech Private Limited
EXAMPLE OF DATE FUNCTION
44
© 2014 PANZRA SoftTech Private Limited
CONVERSION FUNCTIONS
 When we work with data types sometimes, we uses data of
one data type where expected data is of a different data type.
When this happens server can automatically convert the data
to the expected data type. This data type conversion can be
done implicitly by server or explicitly by the user.
 If this data conversion is
done automatically then it
is called as Implicit data
conversion.
 And if this data conversion
is done by a user then it is
called as Explicit data
conversion.
45
© 2014 PANZRA SoftTech Private Limited
Types Of Conversion Function
The assignment succeeds if the
server can convert the data type of
the value used in the assignment to
that of the assignment target.
SQL provides three functions to convert
a value from one data type to another.
TO_CHAR, TO_NUMBER, TO_DATE
are the explicit functions.
46
© 2014 PANZRA SoftTech Private Limited
TO_CHAR FUNCTION
 We use the TO_CHAR function to convert a date from default format to one
that you specify.
 Example,
47
© 2014 PANZRA SoftTech Private Limited
Date Format Model
 The images are
showing date
formats that are
used to display the
date and time
information.
48
© 2014 PANZRA SoftTech Private Limited
TO_CHAR FUNCTION WITH NUMBER
Above image shows the number format elements that you can use with TO_CHAR
function to display a number value as a character. Example,
49
© 2014 PANZRA SoftTech Private Limited
TO_NUMBER AND TO_DATE
 You may want to convert a character string to either a number or a date.
To accomplish this task, use the TO_NUMBER and TO_DATE function.
 These functions have an fx modifier. This modifier specifies the exact
matching for the character argument and date format model of a TO_DATE
function.
 Punctuation and quoted text in the character argument must exactly
match the corresponding parts of the format model.
 The character argument can not have extra blanks.
© 2014 PANZRA SoftTech Private Limited
50
MULTIPLE ROW FUNCTION
 The function that operates on the multiple row or a set of row to give one
result per group is called as Group Functions.
 These set may comprise the entire table or the table split into groups.
 Following are the group functions AVG , COUNT , MAX , MIN , STDDEV ,
SUM , VARIANCE. Group functions can’t be use in the WHERE clause.
Syntax
SELECT
[column, ] group_function (column)
FROM
table
[ WHERE condition ]
[ GROUP BY column ]
[ ORDER BY column ]
 Example using AVG,MAX , MIN , SUM functions.
SELECT
FROM
WHERE
AVG (salary), MAX (salary),
MIN (salary) , SUM ( salary)
employees
job_id LIKE ‘% REP%’ ;
51
© 2014 PANZRA SoftTech Private Limited
Count Functions
COUNT Function
 Count function has three format.
COUNT(*) , COUNT(expr) , COUNT(DISTINCT expr).
 Count(*) returns the number of rows in a table that satisfy the criteria of the
SELECT statement including duplicate rows and rows containing null values
 Count(expr) returns the number of non null values that are in the column
identified by expr
 Count(DISTINCT) returns the number of unique , non null values that are in the
column identified expr
Examples
 Find the number of tuples in the customer relation.
SELECT COUNT (*)
FROM
customer ;
 Find the number of depositors in the bank.
SELECT COUNT( DISTINCT customer_name)
FROM depositor ;
52
© 2014 PANZRA SoftTech Private Limited
DISTINCT Keyword
 The DISTINCT keyword is used to suppress the counting of any duplicate
values in the column.
 When only one expression is provided in the DISTINCT clause, the query
will return the unique values for that expression.
 When more than one expression is provided in the DISTINCT clause, the
query will retrieve unique combinations for the expressions listed.
Example
SELECT DISTINCT
FROM
WHERE
ORDER BY
city, state
customers
total_orders > 10
city ;
 This Oracle DISTINCT clause example would return each unique city and
state combination from the customers table where the total_orders is
greater than 10. The results are sorted in ascending order by city.
 In this case, the DISTINCT applies to each field listed after the DISTINCT
keyword, and therefore returns distinct combinations.
53
© 2014 PANZRA SoftTech Private Limited
GROUP BY Function
 The GROUP BY statement is used in conjunction with the aggregate
functions to group the result-set by one or more columns.
 The GROUP BY clause is used to divide the rows in the table into smaller
groups. Means It specifies how the rows should be group.
Syntax
GROUP BY expression ;
expression = it specifies columns whose values determine the basis for
grouping rows
 If you include a group function in a SELECT clause then you can’t select
individual result as well, unless the individual column appears in the
group clause.
 Using a WHERE clause , you can exclude rows before dividing them into
groups.
 You must include the column in the group by clause.
 You can’t use a column alias in the group by clause.
54
© 2014 PANZRA SoftTech Private Limited
USING GROUP BY FUNCTION
 All column in the SELECT list that are not in group functions must be in the
GROUP BY clause.
 The GROUP BY column does not have to be in the select list.
 Summary result for groups and subgroups can be return by listing more
than one GROUP By column.
 Example
SELECT
department_id, MAX (salary)
FROM
employees
GROUP BY
department_id;
--------------------------------------------------------------------------SELECT
AVG (salary)
FROM
employees
GROUP BY
department_id;
--------------------------------------------------------------------------SELECT
department_id ,dept_id, job_id,
SUM(salary)
FROM
employees
GROUP BY
department_id , job_id;
55
© 2014 PANZRA SoftTech Private Limited
HAVING CLAUSE
 Oracle GROUP BY HAVING can be used to limit the returned rows after the grouping.
 HAVING clause is used to specify which group are to be displayed , thus further
restricting the groups on the basis of aggregate information.
 The HAVING clause is applied after the GROUP BY has taken place.
 Oracle GROUP BY HAVING will group values that have a particular value. Oracle
GROUP BY HAVING can be used in conjunction with other logical functions such
as MIN, MAX, COUNT and SUM.
 The HAVING clause filters rows after the grouping with the Oracle GROUP BY clause.
Syntax
[ HAVING group_condition ] ;
group_condition restricts the group of rows returned to those groups for which
the specified condition is true.
Example
The output of this query is ,
SELECT
department_id, MAX(salary)
department id and maximum
FROM
employees
salaries for those departments
GROUP BY department_id
with a maximum salary that
HAVING
MAX (salary)>10000 ;
is greater than 10000
56
© 2014 PANZRA SoftTech Private Limited
Miscellaneous Functions
 DEFAULT() :- Return the default value for a table column
 GET_LOCK():- Get a named lock
 VALUES():- Defines the values to be used during an
INSERT
 INET_ATON():- Return the numeric value of an IP address
 IS_FREE_LOCK():-Checks whether the named lock is free
 RAND():- Return a random floating-point value
 UUID():- Return a Universal Unique Identifier (UUID)
 RELEASE_LOCK():-Releases the named lock
 SLEEP():- Sleep for a number of seconds
57
© 2014 PANZRA SoftTech Private Limited
Examples of Miscellaneous functions
This function can be used to implement application locks or to simulate record
locks. Names are locked on a server-wide basis. If a name has been locked by
one client, GET_LOCK() blocks any request by another client for a lock with the
same name. This enables clients that agree on a given lock name to use the
name to perform cooperative advisory locking. But be aware that it also enables
a client that is not among the set of cooperating clients to lock a name, either
inadvertently or deliberately, and thus prevent any of the cooperating clients
from locking that name. One way to reduce the likelihood of this is to use lock
names that are database-specific or application-specific. For example, use lock
names of the form db_name.str or app_name.str.
1. SELECT GET_LOCK ( 'lock1‘ , 10 ) ;
2. SELECT IS_FREE_LOCK ( 'lock2‘ ) ;
3. SELECT GET_LOCK ( ‘ lock2 ’ , 10 ) ;
4. SELECT RELEASE_LOCK ( ‘ lock2 ’ ) ;
5. SELECT RELEASE_LOCK (‘ lock1 ’ ) ;
6. SELECT INET_ATON ( ‘ 10.0.5.9 ’ ) ;
-> 167773449 For this example, the return value is calculated as 10×2563 + 0×2562 +
5×256 + 9. etc…
58
© 2014 PANZRA SoftTech Private Limited
JOINS
 A join is used to view information from multiple tables. Therefore
you can join tables together to view information from more than
one table.
Syntax
SELECT
column1, column2, collumn3...
FROM
WHERE
table_name1, table_name2
table_name1 . column2 = table_name2 . Column1 ;
 A Join condition is a part of the SQL query that retrieves rows from
two or more tables.
 A Join condition is used in the WHERE Clause of select, update,
delete statements.
 There are different types of joins to display data from multiple
tables. And they are Equijoins, Nonequijoins, Outer joins , Self
joins ,Cross joins , Natural joins, Full outer joins
59
© 2014 PANZRA SoftTech Private Limited
Types Of Join
Natural joins
We can join tables based on columns in the two tables that have the matching
data types and names. And this can be done using the keyword ‘ NATURAL JOIN’.
If the column have the same name but different data types , then the NATURAL
JOIN syntax causes an error. Example,
SELECT
FROM
NATURAL JOIN
department_id , department_name , location_id , city
departments
locations;
In this example, the LOCATIONS table is joined to the DEPARTMENT table by the
location_id column, which is the only column of the same name in both tables.
 USING Clause
If the column have the same names but the data type do not match NATURAL
JOIN can be applied by using the USING clause to specify the column that should
be used for an equijoin. Use the USING clause to match only one column when
more than one column matches. Do not use a table name or alias in the referenced
column. The NATURAL JOIN and USING clauses are mutually exclusive.
60
© 2014 PANZRA SoftTech Private Limited
Self Join And EquiJoin
 ON CLAUSE
Use the ON clause to specify arbitrary conditions or specify columns to join. The join
condition is separated from other search conditions. The ON clause makes code easy
to understand. ON clause can be used to join columns that have different names.
SELF JOIN
Sometimes we need to join tables to itself. For example, to find the name of each
employees manager, you need to join employees table to itself or perform a self
join. To find out , you need to see the table two times . Example,
SELECT
FROM
ON
e.last_name emp , m.last_name mgr
employees e JOIN employees m
(e.manager_id = m.employee_id) ;
EQUI JOIN
It is a simple join condition which uses the equal sign as the comparison operator.
Equi joins are also called as Inner join or simple join. Frequently, this type of join
involves primary and foreign key complements . Syntax is,
….WHERE table1.column = table2.column
61
© 2014 PANZRA SoftTech Private Limited
Non EquiJoin And Outer Join
NON EQUI JOIN
It is a join condition which makes use of some comparison operator other
than the equal sign like >, <, >=, <=… Example of non equi join,
.…WHERE table1.column IN (table2.column) ;
….WHERE table1.column
BETWEEN table2.columnA AND table2.columnB;
OUTER JOIN
A join between two tables that returns the result of the inner join as
well as the unmatched rows from the left(or right) table is called as a
left(or right)outer join. An outer join returns all rows that satisfy the
join condition and also returns some or all of those rows from one
table for which no rows from the other table satisfy the join condition.
There are 3 types of outer joins LEFT OUTER, RIGHT OUTER,FULL OUTER
62
© 2014 PANZRA SoftTech Private Limited
Left And Right Outer Join
LEFT OUTER JOIN
The LEFT JOIN keyword returns all rows from the left table (table1), with the
matching rows in the right table (table2). The result is NULL in the right side
when there is no match.
Syntax
SELECT
FROM
LEFT OUTER JOIN
ON
column_name(s)
table1
table2
table1.column_name = table2.column_name ;
RIGHT OUTER JOIN
The RIGHT JOIN keyword returns all rows from the right table (table2), with
the matching rows in the left table (table1). The result is NULL in the left
side when there is no match.
Syntax
SELECT
FROM
RIGHT OUTER JOIN
ON
column_name(s)
table1
table2
table1.column_name = table2.column_name ;
© 2014 PANZRA SoftTech Private Limited
64
Full Outer And Cross Join
FULL OUTER JOIN
A join between two tables that returns the result of the inner join as well as
the result of the left and right join is a full outer join. The FULL OUTER JOIN
keyword returns all rows from the left table (table1) and from the right table
(table2). The FULL OUTER JOIN keyword combines the result of both LEFT
and RIGHT joins
SELECT
column_name(s)
FROM
table1
FULL OUTER JOIN table2
ON
table1.column_name = table2.column_name;
Syntax
CARTESIAN PRODUCT
It is also called as cross join. If a join condition is omitted or if it is invalid or
all rows in the first table are join to all rows in the second table then the join
operation will result in a Cartesian product. The Cartesian product returns a
number of rows equal to the product of all rows in all the tables being joined.
Syntax
SELECT
FROM
CROSS JOIN
column_name(s)
table1
table2;
© 2014 PANZRA SoftTech Private Limited
64
All Joins With Syntax
65
© 2014 PANZRA SoftTech Private Limited
SUB QUERY
 It is possible to embed a SQL statement within another. When this is done on the
WHERE or the HAVING statements, we have a subquery construct.
syntax
SELECT "column_name1"
FROM "table_name1"
WHERE "column_name2" [Comparison Operator]
( SELECT "column_name3"
FROM "table_name2"
WHERE "condition“ ) ;
 Comparison operators such as =, >, <, >=, <= can used. It can also be a text
operator such as "LIKE". The portion in red is considered as the "inner query.
while the portion in green is considered as the "outer query".
 The sub query(inner query) executes once before the main query. The result of
the sub query is used by the main query.
 Enclose sub query in a parentheses. Place sub query on the right side of the
comparison condition.
 Sub queries are an alternate way of returning data from multiple tables.
 Sub queries can be used with the SELECT ,INSERT , UPDATE ,DELETE statements
along with the comparison operators.
© 2014 PANZRA SoftTech Private Limited
66
Correlated Sub Query
 Sub query is of a two types. Single row subquery and Multiple row subquery.
 A single row sub query is one that returns one row from the inner SELECT
statement. It uses a single row operator.
 Sub queries that return more than one row are called multiple row subquery.
It uses multiple row comparison operator like IN, ANY ,ALL.
CORRELATED SUB QUERY
 A correlated sub query is a sub query that uses values from the outer query,
requiring the inner query to execute once for each outer query .
 A query is called correlated sub query when both the inner query and the
outer query are interdependent. For every row processed by the inner query,
the outer query is processed as well. The inner query depends on the outer
query before it can be processed.
EXAMPLE
SELECT
FROM
WHERE
p.product_name
product p
p.product_id = ( SELECT o.product_id
FROM
order_items o
WHERE o.product_id = p.product_id ) ;
67
© 2014 PANZRA SoftTech Private Limited
Exist/Not Exist Operator
 EXISTS/NOT EXISTS Operator
The EXISTS checks the existence of a result of a subquery. The EXISTS subquery tests whether a
subquery fetches at least one row. When no data is returned then this operator returns 'FALSE'.
Example,
SELECT
FROM
WHERE EXISTS
ORDER BY
agent_code , agent_name , working_area , commission
agents
( SELECT * FROM customer
WHERE grade=3
AND
agents.agent_code = customer.agent_code )
commission ;
SELECT
FROM
WHERE NOT EXISTS

agent_code, ord_num, ord_amount, cust_code
orders
( SELECT agent_code
FROM
customer
WHERE payment_amt = 1400 ) ;
CONNECT BY and START WITH clauses
There are two mandatory keywords to build a hierarchy, CONNECT BY and prior. A hierarchy is
built when one row is the parent of another row. START WITH defines the first ancestor.
Example,
SELECT
ENAME
FROM
CONNECT BY PRIOR
START WITH
EMP
EMPNO = MGR
ENAME = 'JONES‘ ;
© 2014 PANZRA SoftTech Private Limited
68
SET OPERATORS
UNION
Returns all distinct rows selected by either query. The UNION operators
returns results from both queries after eliminating duplicate rows.
SELECT employee_id , job_id
Example,
FROM
UNION
SELECT
FROM
employees
employee_id , job_id
job_history;
UNION ALL
Returns all rows selected by either query, including all duplicate rows .
Example,
SELECT employee_id , job_id
FROM employees
UNION ALL
SELECT employee_id , job_id
FROM job_history;
INTERSECT and INTERSECT ALL
Returns all distinct rows selected by both queries. Means returns rows that
are common to both queries.
69
© 2014 PANZRA SoftTech Private Limited
MINUS SET OPERATORS
Example
SELECT employee_id , job_id
FROM
employees
INTERSECT
SELECT employee_id , job_id
FROM
job_history;
MINUS
The operator, which returns only unique rows returned by the first query
that are not present in the second query.
SELECT
FROM
MINUS
SELECT
FROM
employee_id , job_id
employees
employee_id , job_id
job_history;
70
© 2014 PANZRA SoftTech Private Limited
Data Manipulation Language
We insert ,delete ,modify the data from database, the command used for this task
is known as DML statements.
Adding data – add new rows to the table.
Removing data – remove existing rows from table.
Modifying data -- modify existing rows from the table.
Data Manipulation Language is a core part of SQL. A collection of DML Statement
that form a logical unit of work is called a transaction.
INSERT
INSERT statement is used to add a new row to a table.
Syntax INSERT INTO table_name [( column1 ,column2,…)]
VALUES
(value1,value2,..)
In this syntax value is a corresponding value for the column. This statement with a
Value clause add only one row at a time to a table. Example ,
INSERT INTO
VALUES
department ( dept_id , dept_name, mgr_id, location_id)
(70, ‘Public Relation’ , 100 ,1700);
71
© 2014 PANZRA SoftTech Private Limited
UPDATE AND DELETE
UPDATE
Modify existing rows with the UPDATE statement.
Syntax
UPDATE
table
SET
column = value [ , column= value ,..]
[WHERE condition] ;
We can update more than one row at a time if required. In this example if you
omit the WHERE clause, all rows
UPDATE
employees
SET
dept_id = 70
in the table are modified.
WHERE
emp_id = 104 ;
DELETE
We can remove the existing rows from the table by using this statement.
Syntax DELETE FROM
WHERE
table
condition;
Specific row is deleted if we use
The WHERE clause. Otherwise
all the rows in table are deleted.
DELETE FROM
WHERE
departments
dept_name = ‘Finance’;
72
© 2014 PANZRA SoftTech Private Limited
TRANSACTION CONTROL STATEMENTS
Commit
 Ends the transaction by making all pending data changes permanent.
 When we want to save transactions permanently. After using commit
statement the previous state of the data is permanently lost.
Syntax- COMMIT;
Rollback
 Discards all pending data changes. Data changes are undone.
 And then previous state of the data is restored.
Syntax – ROLLBACK ;
Savepoint
 It is a pointer which points location . It is used to roll back to the savepoint
marker.
 Syntax- SAVEPOINT s1 ; // and when want rollback then
ROLLBACK TO s1;
73
© 2014 PANZRA SoftTech Private Limited
DATA TYPES
 CHAR It stores fixed-length character strings. Maximum size is 2000 bytes.
 VARCHAR2 and VARCHAR datatype stores variable- length character strings
specify a maximum string length (in bytes or characters) between 1 and
4000 bytes.
 NCHAR and NCHAR2 The maximum length of an NCHAR column is 2000 bytes
 CLOB The LOB datatypes for character data are CLOB and NCLOB. They can
store up to 4GB of character data (CLOB) or national character set data
(NCLOB).
 BLOB A binary large object. Maximum size is (4 gigabytes - 1) *
 LONG Columns defined as LONG can store variable-length character data
containing up to 2 gigabytes of information. LONG data is text data that is to
be appropriately converted when moving among different systems.
 NUMBER (p , s) stores floating- point numbers. Number having precision p
and scale s. The precision p can range from 1 to 38. The scale s can range
from - 84 to 127. Both precision and scale are in decimal digits. A NUMBER
value requires from 1 to 22 bytes.
74
© 2014 PANZRA SoftTech Private Limited
Data types
 DATE Valid date range from January 1, 4712 BC, to December 31, 9999
AD. The size is fixed at 7 bytes. This data type contains the date time fields
YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have
fractional seconds or a time zone.
 BFILE Contains a locator to a large binary file stored outside the database.
Enables byte stream I/O access to external LOBs residing on the database
server. Maximum size is 4 gigabytes.
 ROWID Base 64 string representing the unique address of a row in its
table. This is primarily for values returned by the ROWID pseudocolumn.
 TIMESTAMP The size is 7 or 11 bytes, depending on the precision. This
data type contains the datetime fields YEAR, MONTH, DAY, HOUR,
MINUTE, and SECOND.
 INTERVAL YEAR TO MONTH Stores a period of time in years and months.
The size is fixed at 5 bytes.
 INTERVAL DAY TO SECOND Stores a period of time in days, hours, minutes,
and seconds. The size is fixed at 11 bytes.
© 2014 PANZRA SoftTech Private Limited
75
Data Integrity
 Business rules specify conditions and relationships that must always be true
or must always be false. For example, each company defines its own
policies about salaries, employee numbers, inventory tracking, and so on. It
is important that data maintain data integrity, which is adherence to these
rules as determined by the database administrator or application developer.
 NOT NULL - specifies that the column can not contain a null value.
 Primary key - It is a set of column used to uniquely identify rows of a table.
eliminate duplicate entry.
 Check - specifies a condition that must be true.
 Unique key - specifies a column or combination of columns whose values
must be unique for all rows in the table . Enter only unique record.
 Foreign key - It is a key which references to primary key of another table.
 All values in primary key must be not null and unique. Each entity that is
stored in the table must be uniquely identified. A foreign key must be either
null or must have a value that is derived from corresponding parent key.
This rules should be followed in order to maintain a data integrity.
© 2014 PANZRA SoftTech Private Limited
76
DATABASE OBJECT
 A database can contain multiple data structures. Each structure should be
outlined in the database design so that it can be created during the build
stage of database development. There are different types of database
object like table, view , sequence , index ,synonym.
 Table – basic unit of storage, composed of rows.
 View – logically represents subsets of data from one or more tables.
 Sequence – generates numeric values.
 Index –improves the performance of some queries.
 Synonym –gives alternative names to objects.
TABLE
Tables can be created at any time , even while users are using the database.
You do not need to specify the size of table. To create table you must have
the create table privilege and a storage area. And you specify the table name,
column name , column data type and column size.
Table names and column names
It must begin with a letter. It must be 1 to 30 character long.
© 2014 PANZRA SoftTech Private Limited
77
TABLE
 Table /column name must contain only A-Z , a-z , 0-9 , _ , $,#.
 Table/column name must not be a reserved word.
 Must not duplicate the name of another object owned by the same user.
Create Table
CREATE statement is a Data Definition Language (DDL).
CREATE TABLE table_name
(column_name datatype[default expression]);
In this syntax , datatype is a columns datatype and length. And default
expression specifies a default value, if a value is omitted in the insert
statement.
Syntax
Example
CREATE TABLE stud (
name
gr_no
address
phone_no
varcha2(20),
number(2) ,
varchar2(22),
number(20) ) ;
Here we create the table stud where we can add the column . A table can be
created by using a subquery.
© 2014 PANZRA SoftTech Private Limited
78
ALTER AND DROP
ALTER TABLE
 After creating a table you may need to change the table structure and that can
be done with the ALTER statement. ALTER is a DDL statement.
 This statement is used to add new column, modify an existing column , define a
default value for the new column , drop a column.
Syntax
ALTER TABLE table_name
Example
 To rename the table from t1 to t2 - ALTER TABLE t1 RENAME t2;
 To add a new timestamp column named d - ALTER TABLE t2 ADD d TIMESTAMP;
 To remove column c - ALTER TABLE t2 DROP COLUMN c;
 To modify value - ALTER TABLE tab_name SET col_name=‘ ’ WHERE condition ;
DROP TABLE
 The DROP TABLE statement removes the definition of a table. When you drop
a table , the database loses all the data in the table and all the indexes
associated with it. DROP is a DDL statement.
Syntax
DROP TABLE table_name ;
© 2014 PANZRA SoftTech Private Limited
79
TRUNCATE
Example
DROP TABLE dept40 ;
Drop command can also be used for other data objects.
 DROP INDEX
index_name;
 DROP SEQUENCE seq_name;
 DROP SYNONYM sy_name;
 DROP VIEW
view_name ;
TRUNCATE
 TRUNCATE statement is a Data Definition Language (DDL) rather than a
DML statement. It removes all rows from a table , leaving the table empty
and table structure intact.
Syntax TRUNCATE TABLE table_name ;
Example TRUNCATE TABLE copy_emp;
 Removing rows with a TRUNCATE statement is faster than DELETE
statement
© 2014 PANZRA SoftTech Private Limited
80
TABLE CONSTRAINTS
 Constraints are used to prevent invalid data entry into the table. It enforce rules at the
table level. Constraint prevent the deletion of table if there are dependencies.
Constraints type are unique, not null , primary key , foreign key ,check. Constraint can
be created at the same time as table is created or after the table has been created.
Syntax
CREATE TABLE tablename
( col_name datatype [default expression]
[column constraint]
………
[table constraint][…] );
Column level constraint syntax,
column [CONSTRAINT constraint_name] constraint_type,
Table level constraint syntax ,
column ,….
[CONSTRAINT constraint_name] constraint_type(column,..)
;
NOT NULL
 Ensures that null values are not permitted for the column. NOT NULL constraint must be
define at column level. Column without NOT NULL constraint can contain null values by
default.
CREATE TABLE employees
(empid NUMBER(6), ename VARCHAR(25)
email VARCHAR(25) CONSTRAINT emp_email_nn NOT NULL
jobid VARCHAR(10) ) ;
© 2014 PANZRA SoftTech Private Limited
81
Unique And Primary Key
UNIQUE CONSTRAINT
It requires that every value in the column or set of columns be unique.
CREATE TABLE
employees
(empid NUMBER(6), ename VARCHAR(25)
email VARCHAR(25) CONSTRAINT emp_email_uk UNIQUE
jobid VARCHAR(10) ) ;
-----------------------------------------------------------------------------------------------------OR (table level) (….., email VARCHAR(25) , ……..
CONSTRAINT emp_email_uk UNIQUE (email) ) ;
PRIMARY KEY CONSTRAINT
Create a primary key for a table. Only one key can be created for each table.
CREATE TABLE
employees
(empid NUMBER(6), ename VARCHAR(25)
email VARCHAR(25) CONSTRAINT emp_email_pk PRIMARY KEY
jobid VARCHAR(10) ) ;
----------------------------------------------------------------------------------------------------------OR (table level) (….., email VARCHAR(25) , ……..
CONSTRAINT emp_email_pk PRIMARY KEY(email) ) ;
82
© 2014 PANZRA SoftTech Private Limited
FOREIGN AND CHECK CONSTRAINT
FOREIGN KEY CONSTRAINT
It designates a column
CREATE TABLE employees
as a foreign key and
(empid NUMBER(6), ename VARCHAR(25)
email VARCHAR(25)CONSTRAINT emp_email_fk FOREIGN KEY
establishes relationship
jobid VARCHAR(10) ) ;
Between a primary key ----------------------------------------------------------------------------------------------------------or unique key in the
OR (table level) (….., email VARCHAR(25) , ……..
CONSTRAINT emp_email_fk FOREIGN KEY(email) ) ;
Same table or in the
Different table.
CHECK CONSTRAINT
Defines a condition that
each row must satisfy. CREATE TABLE employees
(empid NUMBER(6), ename VARCHAR(25)
A single column can
salary NUMBER(2)CONSTRAINT emp_salary_ck CHECK (salary > 0)
jobid VARCHAR(10) ) ;
have multiple CHECK
constraints that refer ----------------------------------------------------------------------------------------------------------OR (table level) (….., salary NUMBER(2) , ……..
to the column. There
CONSTRAINT emp_salary_ck CHECK ( salary > 0 ) ) ;
is no limit to the no
of CHECK constraint.
© 2014 PANZRA SoftTech Private Limited
83
VIEW
 A view is a logical table based on a table or another view.
 A view contains no data of it’s own but is like a window through which data
from tables can be viewed or changed. Display the data from one or more
table or different tables.
 The tables on which a view is based are called base tables. The view is stored
as a SELECT statement in the data dictionary.
 View displays a data in logical form but not store in physical form on disk.
Advantages of view
 To present different views of the same data.
 View restricts access to the data
 To make complex query easy
 To provide data independence
CREATE VIEW empuv40
AS SELECT
emp_id, last_name , salary
Syntax and Example
CREATE VIEW view_name
AS SELECT column_name FROM table_name
FROM
WHERE
employees
dept_id =80 ;
84
© 2014 PANZRA SoftTech Private Limited
SEQUENCE
 A sequence is a database object that creates integer value. We can create
sequences and then use them to generate numbers.
 A sequences can automatically generate unique numbers. It is a sharable
object. It can be used to create a primary key value. It replaces application
code. It speed up the efficiency of accessing sequence values when cached
in memory.
 A field in oracle can be kept as auto incremented by using sequence. it can
be used to create a number sequence.
 Syntax
Example
CREATE SEQUENCE sequence_name
[ INCREMENT BY n]
[ START WITH n ]
[MAXVALUE n|NOMAXVALUE n]
[MINVALUE n |NOMINVALUE n]
[ CYCLE| NOCYCLE ]
[CACHE n | NOCACHE];
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCYCLE
NOCACHE ;
85
© 2014 PANZRA SoftTech Private Limited
SEQUENCE
In the above syntax,
INCREMENT BY n specifies interval between sequence number where n is
an integer. If this clause is omitted, the sequence incremented by 1.
START WITH n specifies the first sequence number to be generated. If this
clause is omitted , the sequence starts with 1.
MAXVALUE specifies the maximum value the sequence can generate.
NOMAXVALUE specifies a maximum value of 10^27 for an ascending
sequence and -1 for a descending sequence. This is the default option.
MINVALUE specifies the minimum sequence value.
NOMINVALUE specifies a minimum value of 1 for an ascending sequence
and –(10^26) for descending sequence. This is the default option.
CYCLE|NOCYCLE specifies whether the sequence continues to generate
values after reaching its max or min value. NOCYCLE is default option.
CACHE|NOCACHE specifies how many values the oracle server
preallocates and keep in memory. By default server caches 20 value.
86
© 2014 PANZRA SoftTech Private Limited
INDEXES
 Indexes are database objects that you can create to improve the
performance of some queries.
 Indexes can also be created automatically by the server when you create a
primary key or unique constraints.
 Index in sql is created on existing tables to retrieve the rows quickly.
When there are thousands of records in a table, retrieving information will
take a long time. Therefore indexes are created on columns which are
accessed frequently, so that the information can be retrieved quickly.
Indexes can be created on a single column or a group of columns. When a
index is created, it first sorts the data, then it assigns ROWID for each row.
 Indexes can be created or dropped at any time and have no effect on the
base tables or other indexes.
 When you drop a table , corresponding indexes are also dropped.
 Two types of indexes can be created unique and nonunique.
 Unique Index - they are created when a column is explicitly defined with
PRIMARY KEY, UNIQUE KEY Constraint.
© 2014 PANZRA SoftTech Private Limited
87
INDEXES
 Nonunique Indexes - They are created using the "create index.. " syntax.
 In oracle database you can define up to sixteen (16) columns in an INDEX.
Syntax CREATE INDEX index_name
ON table_name ( col1, col2 ,…. );
Example Improve the query access speed to the last_name column in the
employees table.
CREATE INDEX
ON
emp_last_name_idx
employees(last_name);
When to create an index
 When the column contains a wide range of values
 The column contains a large number of null values
 One or more columns are frequently used together in where clause.
Indexes can be dropped by using DROP command
Syntax
DROP INDEX index_name ;
Example DROP INDEX emp_last_name_idx ;
© 2014 PANZRA SoftTech Private Limited
88
SYNONYMS
 Synonyms are database objects that enables you to call a table by another
name. You can create synonyms to give an alternative name to a table.
 Use the CREATE SYNONYM statement to create a synonym, which is an
alternative name for a table, view, sequence, procedure, stored function,
package, materialized view, Java class schema object, user-defined object
type, or another synonym.
 synonyms are not a substitute for privileges on database objects.
Appropriate privileges must be granted to a user before the user can use
the synonym.
 Create an easier reference to a table that is owned by another user.
 Shorten lengthy object names.
 can refer to synonyms in the following DML statements: [ SELECT, INSERT,
UPDATE, DELETE, FLASHBACK TABLE, EXPLAIN PLAN, and LOCK TABLE.]
 can refer to synonyms in the following DDL statements AUDIT, NOAUDIT ,
GRANT, REVOKE, and COMMENT.
89
© 2014 PANZRA SoftTech Private Limited
SYNONYMS
Syntax CREATE [PUBLIC] SYNONYM synonym_name
FOR
object;
In the syntax , PUBLIC create a synonym that is accessible to all users . Object
Identifies the object for which the synonym is created.
Notes – The object can not be contained in a package. A private synonym name must
be distinct from all other objects that are owned by the same user.
Examples of synonyms
 To define the synonym offices for the table locations in the schema hr, issue
the following statement
CREATE SYNONYM offices
FOR
hr.locations;
 To create a PUBLIC synonym for the employees table in the schema hr on the
remote database, you could issue the following statement
CREATE PUBLIC SYNONYM emp_table
FOR
[email protected];
© 2014 PANZRA SoftTech Private Limited
90
Other Database Objects
 Oracle Database recognizes objects that are associated with a particular schema and
objects that are not associated with a particular schema.
 Within a namespace, no two objects can have the same name.
 The following schema objects share one namespace:
Tables, Views, Sequences, Private synonyms, Stand-alone procedures, Stand-alone stored
functions, Packages, Materialized views, User-defined types, User-defined, operators
 Each of the following schema objects has its own namespace:
Indexes, Constraints, Clusters, Database triggers, Private database links, Dimensions
 Because tables and views are in the same namespace, a table and a view in the same
schema cannot have the same name. However, tables and indexes are in different
namespaces. Therefore, a table and an index in the same schema can have the same name.
 Each schema in the database has its own namespaces for the objects it contains. Example,
that 2 tables in different schemas are in different namespaces and can have the same name
 Each of the following nonschema objects also has its own namespace:
User roles, Public synonyms, Public database links, Tablespaces, Profiles, Editions
Parameter files (PFILEs) and server parameter files (SPFILEs)
 Because the objects in these namespaces are not contained in schemas, these namespaces
span the entire database.
91
© 2014 PANZRA SoftTech Private Limited
TRANSACTION CONTROLLING
 A transaction is a logical unit of work that comprises one or more SQL
statements executed by a single user.
A database transaction consists
of one of the following:
 DML statements which
constitute one consistent
Change to the data
 One DDL statement
 One DCL statement
A transaction begins with the first executable SQL statement.
A transaction ends with one of the following events:
 A COMMIT or ROLLBACK statement is issued
 A DDL or DCL statement executes (automatic commit)
 The user exits I SQL*Plus
 The system crashes
92
© 2014 PANZRA SoftTech Private Limited
TRANSACTION CONTROLLING
With COMMIT and ROLLBACK
statements, you can:
 Ensure data consistency
 Preview data changes
before making changes
permanent
 Group logically related
operations
State of the Data Before
COMMIT or ROLLBACK
 The previous state of the
data can be recovered.
93
© 2014 PANZRA SoftTech Private Limited
TRANSACTION CONTROLLING
 The current user can review the results of the DML operations by using the SELECT
statement.
 Other users can not view the results of the DML statements by the current user.
 The affected rows are locked
 Other users cannot change the data within the affected rows.
State of the Data after COMMIT
 Data changes are made permanent in the database.
 The previous state of the 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.
State of Data after ROLLBACK
 Data changes are undone
 Previous state of the data is restored
 Locks on affected rows are released
94
© 2014 PANZRA SoftTech Private Limited
DATA CONTROLLING LANGUAGE
 DCL commands are used to enforce database security in a multiple user
database environment. Two types of DCL commands are GRANT and
REVOKE. Only Database Administrator's or owner's of the database object
can provide/remove privileges on a database object.
GRANT
 SQL GRANT is a command used to provide access or privileges on the
database objects to the users. You can grant users various privileges to
tables. These privileges can be any combination of SELECT, INSERT,
UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.
 Give the permission to the user for accessing the particular data.
Syntax
GRANT
privilege_name
ON
object_name
TO
{user_name |PUBLIC |role_name}
[WITH GRANT OPTION];
 EX.2- GRANT SELECT /INSERT/UPDATE/ DELETE ON suppliers TO smith;
© 2014 PANZRA SoftTech Private Limited
95
REVOKE
 privilege_name is the access right or privilege granted to the user. Some of the
access rights are ALL, EXECUTE, and SELECT.
 object_name is the name of an database object like TABLE, VIEW, STORED PROC
and SEQUENCE.
 user_name is the name of the user to whom an access right is being granted.
 PUBLIC is used to grant access rights to all users.
 ROLES are a set of privileges grouped together.
 WITH GRANT OPTION - allows a user to grant access rights to other users.
REVOKE

The REVOKE command removes user access rights or privileges to the database
objects.
Syntax
REVOKE
ON
FROM
privilege_name
object_name
{user_name |PUBLIC |role_name} ;
 EX:- 1 . REVOKE ALL ON suppliers FROM public ;
96
© 2014 PANZRA SoftTech Private Limited
PRIVILEGES AND ROLES
Privileges
 Privileges: Privileges defines the access rights provided to a user on a database object.
There are two types of privileges.
 1) System privileges - Gaining access to the database. This allows the user to CREATE,
ALTER, or DROP database objects.
2) Object privileges - Manipulating the content of the database object. This allows the
user to EXECUTE, SELECT, INSERT, UPDATE, or DELETE data from database objects to
which the privileges apply.
Roles
 Roles are a collection of privileges or access rights. When there are many users in a
database it becomes difficult to grant or revoke privileges to users. Therefore, if you
define roles, you can grant or revoke privileges to users, thereby automatically granting
or revoking privileges. You can either create Roles or use the system roles pre-defined
by oracle.
 It's easier to GRANT or REVOKE privileges to the users through a role rather than
assigning a privilege directly to every user. If a role is identified by a password, then,
when you GRANT or REVOKE privileges to the role, you definitely have to identify it with
the password.
97
© 2014 PANZRA SoftTech Private Limited