Oracle SQL

43  Download (0)

Full text

(1)

Oracle SQL

(2)
(3)

CONTENTS

CONTENTS

CONTENTS CONTENTS... ...2...2 INTRODUCTORY CONCEPTS INTRODUCTORY CONCEPTS... ... ...3...3 T

THEHEDDATABASEATABASEOOBJECTSBJECTS... 33

D

DATABASEATABASEOOBJECTBJECTNNAMINGAMINGRRULESULES...4...4

D

DATATYPESATATYPES...4...4

TH

TH E E DADATA DEFINITION TA DEFINITION COMMCOMMAANDSNDS FOR TABLESFOR TABLES... ... ... 88 C

CREATING AREATING ATTABLEABLE...8...8

C

CONSTRAINTS ONONSTRAINTS ONTTABLESABLES...99

D

DROPPING AROPPING ATTABLEABLE...12...12

A

ALTERLTERING ING AATTABLEABLE...13...13

S

SAMPLEAMPLETTABLESABLES...14...14

QUERYING THE

QUERYING THE TATABLESBLES... ... ...15...15 C

CONDITIONALONDITIONALRRETRIEVAL OFETRIEVAL OFRROWSOWS...15...15

S

SQLQL FFUNCTIONSUNCTIONS...17...17

G

GROUPROUP((ORORAAGGREGATEGGREGATE)) FFUNCTIONSUNCTIONS...17...17

S

SINGLEINGLERROWOW((ORORSSCALARCALAR)) FFUNCTIONSUNCTIONS...20...20

JJOINS TOOINS TOQQUERYUERYMMULTIPLEULTIPLETTABLESABLES...27...27

S

SETETOOPERATORSPERATORS...29...29

N

NESTEDESTEDQQUERIESUERIES...30...30

DATA

DATA MAMANN IPULATION IPULATION COMMACOMMANDND SS... ... ...3333 T

THEHEINSERTINSERTCOMMANDCOMMAND...3333

T

THEHEDELETEDELETE CCOMMANDOMMAND...3333

T

THEHEUPDATEUPDATE CCOMMANDOMMAND...3333

TH

TH E E TRATRANSANSACTION CTION CONTROL COMMACONTROL COMMANDND SS... ...35...35 T

THEHECOMMITCOMMIT CCOMMANDOMMAND...3535

T

THEHEROLLBACKROLLBACK CCOMMANDOMMAND...3535

W

WORKING WORKING WITH ITH OTHOTH ER SCHEER SCHE MA MA OBJOBJECTSECTS... ...37...37 S SEQUENCESEQUENCES...3737 S SYNONYMSYNONYMS...3838 V VIEWSIEWS...3939 IINDEXESNDEXES...4141 C CLUSTERSLUSTERS...4242

(4)

I

I

I

I

n

n

n

n

t

t

t

t

r

r

r

r

o

o

o

o

d

d

d

d

u

u

u

u

c

c

c

c

t

t

t

t

o

o

o

o

r

r

r

r

y

y

y

y

C

C

C

C

o

o

o

o

n

n

n

n

c

c

c

c

e

e

e

e

p

p

p

p

t

t

t

t

s

s

s

s

?

? SQL (StructSQL (Structured Query Language) is a standard RDBMS language.ured Query Language) is a standard RDBMS language.

?

? Oracle SQL iOracle SQL is the set of commands that all s the set of commands that all prpr ograms and users mograms and users must use to ust use to accessaccess

and modify the data within the Oracle Database. and modify the data within the Oracle Database. ?

? For executing SQL queries in the user schema, he/she must be granted some type of For executing SQL queries in the user schema, he/she must be granted some type of 

system pri

system privivileges by DBA.leges by DBA. ?

? The SQL statements can bThe SQL statements can be broadly cle broadly classiassifified into three categed into three categ ories:ories: o

o Data DefiData Definitinition Language (DDL) on Language (DDL) - Used to create, alter, or dro- Used to create, alter, or drop objp objects (e.g., ects (e.g., ta- ta-bles,

bles, indexes, sindexes, synonymynonyms, vis, views, clusters, etc.) ews, clusters, etc.) in in the databasethe database

o

o Data ManipulData Manipulation Language ation Language (DML) - Used to query, ins(DML) - Used to query, insertert, delete and update of , delete and update of  data stored

data stored in the database.in the database.

o

o Data Control Language (DCL) - Used for controData Control Language (DCL) - Used for controllllining data stog data stored in the datred in the dat abase.abase.

The Database Objects

The Database Objects

?

? The database objects are The database objects are entities entities storestored id in the n the database. These objects database. These objects may may be eitherbe either

schem

schema obja objectsects , or non-sch, or non-schema objema objectsects .. ?

? Schema objectsSchema objects are objects belonging to a particular schema. A schema is made up of are objects belonging to a particular schema. A schema is made up of 

a collection of logical structures of data, or schema objects. A schema is owned by a a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the s

database user and has the same naame na me as that user (e.g., scott, tme as that user (e.g., scott, trng01, etc.). Erng01, etc.). Each userach user owns a single schema.

owns a single schema. ?

? SQL is used to create and manipulate the schema objects.SQL is used to create and manipulate the schema objects.

?

? Some schema objects are listed:Some schema objects are listed: o

o ClustersClusters

o

o Database LinksDatabase Links

o

o Database TriggersDatabase Triggers

o o IndexesIndexes o o PackagesPackages o o SequencesSequences o

o Stored FunctionsStored Functions

o

o Stored ProceduresStored Procedures

o o SynonymsSynonyms o o TablesTables o o ViewsViews ?

? Other types of objOther types of objects are alsects are also stored in the database and can bo stored in the database and can be created and manie created and manipp u-

u-lated with SQL, but are not contained in a schema. These are

lated with SQL, but are not contained in a schema. These are  non-schema objects non-schema objects,, and some of them are listed blow:

and some of them are listed blow:

o o directoriesdirectories o o profilesprofiles o o rolesroles o

o rollback segmentsrollback segments

o

(5)

o

o usersusers

Database Object Naming Rules

Database Object Naming Rules

?

? MaxiMaximum mum character lencharacter length can be 30 (Except for database namgth can be 30 (Except for database names whies which are lich are li mited tomited to

8 characters). 8 characters). ?

? Must not contain a question mark.Must not contain a question mark.

?

? Case Case insensinsensitive.itive.

?

? Must begin with an alphabet.Must begin with an alphabet.

?

? May only May only contain the characters acontain the characters a -z, A-Z, 0-9, _, $ and #.-z, A-Z, 0-9, _, $ and #.

?

? Must not Must not dupliduplicate an Oracle Reserved word.cate an Oracle Reserved word.

?

? Must not Must not dupliduplicate another cate another database object beldatabase object belonginonging to g to that user.that user. Object

Object Name Name Valid/Invalid Valid/Invalid ReasonReason

employee

employee validvalid

1_test

1_test invalinvalid id Begins Begins with with a a numbernumber

check

check invinvalialid d ORACLE ORACLE reserved reserved wordword

Spp&prts

Spp&prts invinvalialid d Use Use of of ““&” &” not not alalllowedowed

Pass/fail

Pass/fail invinvalialid d Use Use of of “/” “/” not not allallowedowed

Datatypes

Datatypes

Internal

Internal Datatype Datatype DescriptionDescription

VARCHAR2(size)

VARCHAR2(size) Variable length character string having maximum lengthVariable length character string having maximum length sizesize bytes. Maximum

bytes. Maximumsizesize is 4000, and minimum is 1. Youis 4000, and minimum is 1. You mustmust

specify

specify sizesizefor a VARCHAR2.for a VARCHAR2. NUMBER(p,s)

NUMBER(p,s) Number having precisionNumber having precision  p pand scaleand scale ss.. LONG

LONG Character data of variablCharacter data of variable length up to e length up to 2 gigabytes, or2 gigabytes, or 2

23131-1 bytes.-1 bytes. DATE

DATE ValiValid date range from January 1, 4712 BC to d date range from January 1, 4712 BC to DecDec embember 31,er 31, 4712 A

4712 AD. D. Stores tiStores time me too.too. RAW(size)

RAW(size) Raw binary data of lengthRaw binary data of length sizesizebytes. Maximumbytes. Maximum sizesize is 2000is 2000 bytes. You

bytes. Youmustmustspecifyspecify sizesize for a for a RAW RAW value. value. This This datatdatatype ype isis used to

used to store binary data.store binary data. LONG RAW

(6)

ROWID Hexadecimal string representing the unique address of a row in its table. This datatype is primarily for values r e-turned by the ROWID pseudocolumn.

CHAR(size) Fixed length character data of length size bytes. Maximum

sizeis 2000 bytes. Default and minimum size is 1 byte. CLOB A character large object containing single byte characters.

Maximum size is 4 gigabytes.

BLOB A binary large object. Maximum size is 4 gigabytes.

BFILE Contains a locator to a large binary file stored ou tside the database. Enables byte stream I/O access to external LOBs residing on the database server.

Character Datatypes

? CHARandVARCHAR2are the character datatypes that can be used to store and ma-nipulate text data in a table column.

? CHARspecifies a fixed length character string (e.g., CHAR(10)) for a defined column. In case you store a string that is smaller than the column length, Oracle blank -pads the value to the column length.

? The VARCHAR2specifies the maximum number of bytes of data that it can hold in a column. Oracle stores each value in the column exactly as specified (without any blank-padding).

Number Datatype

? The NUMBERdatatype is used to store zero, positive and negative f ixed and floating point numbers.

? You can specify a fixed poi nt number with:

NUMBER(p,s)

? You specify an integer with:

NUMBER (p)

? You specify a floating point number with:

NUMBER

LONG Datatype

? LONGis used to store large text strings, up to 2GB in size. ? There are certain restrictions when you use a LONG column:

o A table cannot contain more than one LONGcolumn.

o LONGcolumns cannot appear in integrity constraints (except for NULLand NOT NULLconstraints).

o LONGcolumns cannot be indexed.

(7)

o LONGcolumns cannot appear in certain parts of SQL statements:WHERE,GROUP BY,ORDER BY, in SELECTstatements, SQL functions such as SUBSTRor

INSTR, expressions or conditions

? Due to such restrictions, it is recommended that you use CLOBdatatype to store large character data instead of the LONGdatatype.

DATE Datatype

? The DATEdatatype is used to store the date and time information. For each DATE value the following information is stored:

o century o year o month o day o hour o minute o second

? The function TO_DATEcan be used to convert a string (or numeric) value to date for-mat. This function is discusses later.

? You can get the current date and time using the function SYSDATE(e.g.,SELECT

SYSDATEfromDUAL;)

RAW and LONG RAW Datatypes

? The RAW and LONG RAW datatypes are used for raw binary data that is not to be interpreted by Oracle. Examples of such data include pictures, sound, documents, etc. ? LONG RAW data cannot be indexed, but RAW data can be indexed.

? It is recommended that you use BLOB datatype for binary data ins tead of RAW datatypes.

Large Object (LOB) Datatypes

? The Large Object (LOB) datatypes can store up to 4 GB of unstructured data such as text, image, video, documents, etc.

? BLOB, CLOB , and BFILE are the three types of LOBs.

? BLOB and CLOB are called inter nal LOBs as they store data within the database. BFILE is called external LOB as the corresponding data is stored external to the dat a-base, in the OS files.

? An Oracle-supplied package, DBMS_LOB, is used to work with the LOB data. ? Any number of columns in t able can be of LOB type (unlike the LONG or LONG

RAW types)

(8)

? ROWID(represented as a hex string) represents the address of a ro w in a table in the database. You can view this address by querying the pseudocolumn ROWIDin a table (e.g.,SELECT ROWID FROMemp;).

? An Oracle-supplied package DBMS_ROWIDlet you interpret the meaning of the hex address string of the ROWID.

(9)

T

Th

he

e D

Da

atta

a D

De

ef 

f iin

niittiio

on

n C

Co

om

mm

ma

an

nd

ds

s f 

f o

orr T

Ta

ab

blle

es

s

? Here we look at the DDL commands to work with one of the database objects – tables.

? Table can be created with the CREATE TABLE command.

? Various integrity co nstraints let you impose restrictions on the data that gets into the tables, according to the business rules.

? The structure of the table can be altered using the ALTER TABLE comm and. The same command can be used to add or drop the constraints on the table.

? You can remove a table from the database with the DROP TABLE command.

Creating a Table

Tables are created using the CREATE TABLEcommand, and owned by the user who cr e-ates them.

? Syntax

o DEFAULTspecifies a value to be assigned to the column if a row is inserted wit h-out a value for this column.

o CLUSTER includes this table in the named cluster (this is discusses later in a separate section)

Example:

CREATE TABLE table

({column datatype [DEFAULT expr ] [constraint]}

[,{column datatype [DEFAULT expr ] [constraint]}]...)

[CLUSTER cluster (column [, column] ...)] [AS query ]

CREATE TABLE products ( productno NUMBER(4),

productname VARCHAR2(20), quantityonhand NUMBER (4));

(10)

Constraints on Tables

? Constraints limit the values entered into the columns of the table , thereby ensuring the integrity of the data in the database.

? The business rules are translated as constraints on the tables (e.g., each employee number should be unique, the valid product categories can be A, B, or C only, etc.) ? Depending on the placement of the constraint, a constraint can be termed a column

constraint, or a table constraint:

o a column constraint : Constraint specified as part of the column defin ition:

o a table constraint: Constraint specified towards the end of the CREATE TABLE

statement

Types of Constraints

Constraint Description

NULL | NOT NULL Specifies if a column may or may not support NULL values .

UNIQUE Forces values of a column (or com bination of columns) to be unique. The unique key made up of a single column can contain nulls.

PRIMARY KEY Identifies a column (or combination of columns) as a primary key. A primary key value cannot appear in more than one row in the table, and it canno t be null either (it is equivalent to

UNIQUE NOT NULL). A table can have only one primary key.

FOREIGN KEY Also called a referential integrity constraint, designates a co l-umn (or combination of coll-umns) as a foreign key to a specified primary or unique ke y, called the referenced key. In this rel a-tionship, the table contai ning the foreign key is called the child table and the table co ntaining the referenced key is called the parent table.

CHECK Specifies a condition that the column (or combination of co l-umns) must satisfy for the row to exist in the table.

Example 1:NOT NULLconstraints

 Named and unnamed constraints:

? In the example above , the firstNOT NULLconstraint an unnamed constraint, where as the second NOT NULLconstraint is given a nam eNNL_pname. The CREATE TABLE products

( productno NUMBER(4) NOT NULL,

productname VARCHAR2(20) CONSTRAINT NNL_pname NOT NULL, quantityonhand NUMBER (4));

(11)

unnamed co nstraints will automatically be assigned system generated names (of  the format SYS_Cxxx where xxxis a unique number) by Oracle.

? Naming the constraints will help manage the constraints better.

? The information about the constrain ts will be stored in the data dictionary table user_constraints. To view the constraints, issue a query like the one given below:

Example 2:UNIQUEconstraints

Example 3:PRIMARY KEYconstraint

SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS

WHERE TABLE_NAME = 'PRODUCTS';

CREATE TABLE dept

(deptno NUMBER(2),

dname VARCHAR2(10) CONSTRAINT unq_dname UNIQUE, loc VARCHAR2(10)

); /* column constraint */

CREATE TABLE dept

(deptno NUMBER(2), dname VARCHAR2(10), loc VARCHAR2(10),

CONSTRAINT unq_dname UNIQUE (dname) ); /* table constraint */

CREATE TABLE dept

(deptno NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY, dname VARCHAR2(10) CONSTRAINT unq_dname UNIQUE, loc VARCHAR2(10)

); /* column constraints */

CREATE TABLE dept

(deptno NUMBER(2), dname VARCHAR2(10), loc VARCHAR2(10),

CONSTRAINT pk_dept PRIMARY KEY (deptno), CONSTRAINT unq_dname UNIQUE (dname)

(12)

Example 4:FOREIGN KEYconstraint

 Note:

? Before you define a referential integrity constraint in the child table, the refe r-enced UNIQUEorPRIMARY KEYconstraint on the parent table must already be defined . For the example above, depttable should have deptnodesignated as

PRIMARY KEYalready.

? Having a foreign key allows you to maintain referential integrity of your data. For example, having declared a foreign key deptnoin emptable, if you try to insert a row in table emp with a deptnovalue 50, this deptnoshould be present in the parent table dept already; else the insert fails.

? The ON DELETE CASCADEoption with foreign key: You cannot delete the pa r-ent table row in case there are referencing child table rows. With ON DELETE CASCADE option, Oracle permits deletions of referenced key values in the pa rent table and automatically deletes dependent rows in the child table to maintain re f-erential integrity:

CREATE TABLE emp

(empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4),

hiredate DATE DEFAULT SYSDATE, sal NUMBER(7,2),

comm NUMBER(7,2),

deptno NUMBER(2) CONSTRAINT fk_deptno REFERENCES dept(deptno),

grade CHAR(1)

); /* column constraint */

CREATE TABLE emp

(empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2), grade CHAR(1), CONSTRAINT fk_deptno

FOREIGN KEY (deptno) REFERENCES dept(deptno) ); /* table constraint */

(13)

Example 5:CHECK constraints

Dropping a Table

? DROP TABLEis used to remove a table and all its data from the database .

? When you drop a table, Oracle also drops all the indexes on the table, and invalidates other dependent objects like views, stored procedures, stored functions, packages, etc., and these invalid objects can not be used.

? Syntax:

CREATE TABLE emp

(empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2),

deptno NUMBER(2) CONSTRAINT fk_deptno

REFERENCES dept(deptno) ON DELETE CASCADE, grade CHAR(1)

);

CREATE TABLE emp

(empno NUMBER(4), ename VARCHAR2(10),

job VARCHAR2(9) CONSTRAINT chk_job

CHECK (job IN ('CLERK', 'SALESMAN','ANALYST', 'PRESIDENT')),

mgr NUMBER(4), hiredate DATE,

sal NUMBER(7,2),

comm NUMBER(7,2) CONSTRAINT chk_comm

CHECK (comm BETWEEN 100 AND 1000), deptno NUMBER(2) CONSTRAINT fk_deptno

REFERENCES dept(deptno) ON DELETE CASCADE, grade CHAR(1)

CONSTRAINT chk_totsal CHECK (sal + comm <= 6500) );

(14)

Examples:

 Note: CASCADE CONSTRAINTSallows you to drop a parent table that has referenc-ing child table. Without this option, if such referential integrity constraints exist, Oracle returns an error message and does not drop the table.

 Altering a Table

? You can alter the definition of a table for:

o adding a column

o dropping a column

o adding an integrity constraint

o dropping an integrity constraint or trigger

o modifying/ redefining a column (datatype, size, default value) ? Syntax:

 Note:

? ADDallows you to add a new column to table, or add a constraint to the table’s defin i-tion.

? DROP allows you to remove a column.

? The dropclause is used to remove an integrity constraint or trigger from the dat a-base.

? You cannot useMODIFYoption:

o To reduce the width of a column unless all the columns are empty.

o To change the data type unless the column is empty.

o To make it NOT NULLunless all rows have values in that column.

? The only type of integrity constraint that you can add to a n existing column using the

MODIFYclause with the column constraint syntax is a NOT NULLconstraint. For all other constraints use ADD clause to add the constraint.

DROP TABLE emp;

DROP TABLE dept CASCADE CONSTRAINTS;

ALTER TABLE table

{ [ADD ( { column_element | constraint } [, column_element | constraint }] ... )] [ DROP COLUMN column ]

[ MODIFY (column_element | constraint [, column_element | constraint] ... )] [ DROP drop]...

(15)

Examples:

Sample Tables

The emp and dept tables that we have used in many examples are sample tables from Oracle, and look like:

EMP Table

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP- 81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10

DEPT Table

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

ALTER TABLE emp DROP COLUMN grade; ALTER TABLE emp ADD (grade CHAR(1)); ALTER TABLE emp MODIFY (job NOT NULL); ALTER TABLE emp MODIFY (sal NUMBER(8,2));

ALTER TABLE emp ADD CONSTRAINT pk_emp PRIMARY KEY (empno); ALTER TABLE emp ADD CONSTRAINT fk_emp

FOREIGN KEY (deptno) REFERENCES dept(deptno); ALTER TABLE emp1 DROP CONSTRAINT pk_emp;

(16)

Q

Qu

ue

erry

yiin

ng

g tth

he

e T

Ta

ab

blle

es

s

? The SELECTDML command is used to query the contents of a table. ? Syntax:

? The WHEREclause is used to conditionally retrieve the rows from the table(s) ? The ORDER BYclause is used to get the data in the sorted order

? The GROUP BY and HAVING clauses are used to group together rows and get the aggregate results

Examples

To display the information of all employees SELECT * FROM emp;

To list employee names and their departments SELECT ename, deptno FROM emp;

To get the unique values of department numbers in the emptable SELECT DISTINCT deptno FROM emp;

To List different job titles available in emptable SELECT DISTINCT job FROM emp;

Conditional Retrieval of Rows

? The WHEREclause in the query is used for conditional retrieval of rows ? Operators used to specify the conditions in WHERE clause:

SELECT * | [DISTINCT] column [, column]... FROM table [, table]...

[WHERE condition]

[GROUP BY column [, column]...] [HAVING condition]

(17)

Relational (Comparison) oper ators =, >, <, >=, <=, <> , !=

Logical operators   AND , OR , NOT

Special operators IS [NOT] NULL, IN, BETWEEN, LIKE

? The IS NULL andIS NOT NULLoperators are used in comparing nulls.

? The LIKE operator is used for checking partial equality conditions with

LIKE 'pattern' – the pattern can contain the wildcard characters % (indicates any sequence of zero or more chara cters), and _ (underscore, for single character) . ? You can select expressions from a table .

? You can provide aliases for viewing data. Alias names are displayed in place of actual column names. Alias names are given to the right of a column name, enclosed within quotes. (e.g.,SELECT ename "Employee Name", sal + 100 "Increased Salary" FROM emp;)

Examples

To list the employees belonging to the department 10 SELECT * FROM emp WHERE deptno = 30;

To list the name and salary of the employees whose salary is more than 1500 SELECT ename, sal

FROM emp

WHERE sal > 1500 ORDER BY ename;

To list all the managers in department 20

SELECT * FROM emp WHERE job='MANAGER' AND deptno=20; To list employee details whose salary is between 1000 and 2000 SELECT * FROM emp

WHERE sal >= 1000 AND sal <=2000 ORDER BY sal DESC;

SELECT * FROM emp

WHERE sal BETWEEN 1000 AND 2000 ORDER BY sal DESC;

To list all the clerks in the departments 10 or 20 SELECT * FROM emp

WHERE job='CLERK' AND (deptno = 10 OR deptno = 20) SELECT * FROM emp

(18)

To list names of employees who are not salesmen SELECT ename FROM emp

WHERE job <> ‘SALESMAN’;

To list employees who are not gett ing any commission SELECT ename FROM emp

WHERE comm IS NULL;

To list employees belonging to some dept SELECT ename FROM emp

WHERE deptno IS NOT NULL;

To list the employees whose names start with ‘A’

SELECT ename FROM emp WHERE ename LIKE ‘A%’;

To list employee names having ‘I’ as the second character, and end character as 'S' SELECT ename FROM emp WHERE ename LIKE ‘_I%A’;

SQL Functions

? There are several standard functions that Oracle provides to manipulate the data and return a result.

? There are two types of SQL functions:

o Scalar (single row) functions– act on a single row at a time, returns a single result row for every row of a queried table or view ; used for ma-nipulating data values

o Group (aggregate) functions – acts on a group of rows, returns a single result row for a group of queried rows; used for summarizing data

Group (or Aggregate) Functions

? Group functions are utilized for summarizing data from a set of rows or from the en-tire table.

? The group functions produce a single value for an entire group or table. In all group functions NULLs are ignored.

(19)

Group Function Description

SUM([DISTINCT| ALL] n) returns the sum of values of n MAX([DISTINCT|ALL] n) returns the maximum value of n

  MIN([DISTINCT|ALL] n) returns the minimum value of n  AVG([DISTINCT|ALL] n) returns the average value of n

COUNT({* | [DISTINCT |ALL] n}) returns the number of rows in a query

Examples

To list the total number of employees in the emptable SELECT COUNT(*) FROM emp;

COUNT(*) ---9

To get the total salary paid to the employees SELECT SUM(sal) as Total

FROM emp; TOTAL ---23625

To list the number of jobs available in the emp table

SELECT COUNT(DISTINCT job) "tot jobs" FROM emp; tot jobs

---4

To select the maximum salary paid to all the employees SELECT MAX(sal) Maximum FROM emp;

MAXIMUM ---5000

To list the maximum salary of employee working as a clerk  SELECT MAX(sal) FROM emp WHERE job = ‘CLERK’;

MAX(SAL) ---1400

(20)

To list the average salary and number of employees working in dept 20 SELECT AVG(sal), COUNT(*) FROM emp

WHERE deptno = 20; AVG(SAL) COUNT(*)

---3518.75 4

GROUP BY Clause

? GROUP BYclause groups the rows using a specified column.

? This column induces a co ntrol break using the specified field as the control field. ? The group functions can be used on the individual groups to summarize the data. ? Conditional retrieval of rows from a grouped result is possible with the HAVING

clause. The HAVINGclause filters the rows returned by the GROUP BYclause.

Examples

To list the minimum salaries in each of the departments

SELECT deptno "Department", min(sal) "Min Sal" FROM emp GROUP BY deptno;

Department Min Sal

---10 1400

20 1100

30 1250

To list the number of employees working with each job title SELECT job "Job", COUNT(*) "Employees" FROM emp GROUP BY job; Job Employees --- ---ANALYST 2 CLERK 2 MANAGER 3 SALESMAN 2

To list the departments and the total salaries payable in each department

SELECT deptno "Department", SUM(sal) "Total Salary" FROM emp GROUP by deptno;

Department Total Salary ---

(21)

20 14075

30 5600

To list those departments which have more than 2 employees SELECT deptno "Dept", COUNT(*) "Employees"

FROM emp GROUP BY deptno HAVING COUNT(*) >2; Dept Employees ---20 4 30 3

 Note: The selected columns in the query (involving a GROUP BYclause) must form the list of columns spe cified in the GROUP BYclause or should be group functions.

Single Row ( or Scalar) Functions

N u m b e r F u n c t io n s

ABS(value) POWER(value, exponent)

CEIL(value) ROUND(value, precision)

COS(value) SIGN(value) COSH(value) SIN(value) EXP(value) SINH(value) FLOOR(value) SQRT(value) LN(value) TAN(value) LOG(value) TANH(value)

MOD(value) TRUNC(value, precision)

Examples: Number Functions

SELECT ABS(-15) “Absolute” FROM DUAL;

Absolute ---15

SELECT CEIL(15.7) “Ceiling” FROM DUAL;

Ceiling ---16 SELECT FLOOR(15.7) “Floor”

FROM DUAL; Floor ---15

SELECT MOD(11,4) FROM DUAL; MOD(11,4)

---3 SELECT POWER(3,2) “Raised”

FROM DUAL; Raised ---9

SELECT ROUND(15.193,1) “Round” FROM DUAL;

Round ---15.2

(22)

SELECT ROUND(15.193,-1) “Round” FROM DUAL;

Round ---20

SELECT SIGN(-20) “Sign” FROM DUAL; Sign

---1 SELECT TRUNC(15.79,1) “Truncate”

FROM DUAL; Truncate ---15.7

SELECT TRUNC(15.79,-1) “Truncate” FROM DUAL; Truncate ---10 S t r i n g F u n c t io n s Function Description

String || String Concatenates two strings

ASCII(string) Returns ASCII value of the character

CHR(integer) Returns character corresponding to the ASCII integer value

CONCAT(string1, string2) Concatenates two strings

INITCAP(string) Capitalizes the first letter of the string INSTR(string,

set[,start[,occurrence]]) Returns an integer indicating the pos ition of thecharacter in string that is the first character of  this occurrence

LENGTH(string) Returns the length of the string

LOWER(string) Converts all characters to lower case

LPAD(string, n[,’set’]) Returnsstring right-padded to length n with the sequence of characters inset

LTRIM(string[,’set’]) Removes characters from the left of sring , with all the leftmost characters that appear in set re-moved

RPAD(string, n[,’set’]) Returnsstring left-padded to length nwith the sequence of characters in set

RTRIM(string[,’set’]) Removes characters from the right of sring , with all the leftmost characters that appear in set removed

SOUNDEX(string) Returns a string containing the phonetic repr e-sentation of string. This function allows you to compare words that are spelled d ifferently, but sound alike in English.

SUBSTR(string, start[, count]) Returns the count characters starting fromstart  in the string

(23)

Examples : String Functions

SELECT ename || ', ' || job FROM emp WHERE deptno=10; ENAME||','||JOB ---CLARK, MANAGER MILLER, CLERK

SELECT ASCII(‘A’), ASCII(‘B’) FROM DUAL; ASCII('A') ASCII('B') ---65 66 SELECT LOWER(ename)"Lower", UPPER(ename) "Upper", INITCAP(LOWER(ename)) "Initcap" FROM emp WHERE deptno=20;

Lower Upper Initcap ---jones JONES Jones

scott SCOTT Scott adams ADAMS Adams

ford FORD Ford

SELECT ename, LENGTH(ename) FROM emp WHERE deptno=20; ENAME LENGTH(ENAME) --- ---JONES 5 SCOTT 5 ADAMS 5 FORD 4

SELECT RPAD(ename, 20, '.'), deptno FROM emp WHERE deptno=20;

RPAD(ENAME,20,'.') DEPTNO ---JONES... 20 SCOTT... 20 ADAMS... 20 FORD... 20

SELECT LPAD(ename, 15), sal FROM emp WHERE deptno=20; LPAD(ENAME,15) SAL --- ---JONES 2975 SCOTT 5000 ADAMS 1100 FORD 5000 SELECT SUBSTR(hiredate, 4) FROM emp WHERE deptno=10; SUBSTR

---JUN-81 JAN-82

SELECT ename, INSTR(ename, 'I') "I Posn." FROM emp WHERE deptno=10;

ENAME I Posn.

---CLARK 0

MILLER 2

SELECT loc FROM dept WHERE SOUNDEX(loc)=SOUNDEX('Sidney'); --both Sidney and Sydney would match

G e n e r a l Fu n c t i o n s

Function Description

GREATEST (expr [,expr]…) Returns the greatest of the list of expre ssions. The expression can be numeric, character, or date. LEAST (expr [,expr]…) Returns the least of the list of expressions . The

ex-pression can be numeric, character, or date.

NVL (expr1, expr2) If expr1is null, returns expr2; if expr1is not null, returns expr1

DECODE (value, if1, then1

[, if2, then2]... else) If thethen1; if it isvalueisif2if1then the result isthen the result of the DECODE isthen2, and so on; if  the value equals none of the ifs, then the result is else

(24)

Examples: General Functions

SELECT LEAST ('HARRY', 'HARRIOT', 'HAROLD') "Least" FROM DUAL; Least

---HAROLD

SELECT GREATEST (23, 645, 123) "Greatest" FROM DUAL; Greatest

---645

SELECT ename, sal + comm "totsal", sal + NVL(comm, 0) "totsal" FROM emp WHERE deptno=30;

ENAME totsal totsal

---WARD 1750 1750

BLAKE 2850

TURNER 1500 1500

SELECT ename, NVL(TO_CHAR(comm), 'No Commission') "COMMISSION" FROM emp WHERE deptno = 30;

ENAME COMMISSION

---

---WARD 500

BLAKE No Commission TURNER 0

SELECT ename, DECODE(comm, null, 'NOT Eligible',

0, 'Eligible, but 0', comm) "Commission" FROM emp WHERE deptno=30;

ENAME Commission

---

---WARD 500

BLAKE NOT Eligible TURNER Eligible, but 0

SELECT ename, DECODE(deptno, 10, 'ACCOUNTING', 20, 'RESEARCH',

30, 'SALES',

40, 'OPERATION', 'NONE') "Department" FROM emp WHERE job='CLERK';

ENAME Department ---ADAMS RESEARCH MILLER ACCOUNTING

(25)

D a t e F u n c t i o n s

Function Description

ADD_MONTHS(date,n) Addsn months to the date

LAST_DAY(date) Returns the date of the last day of the month that contains date

MONTHS_BETWEEN(date1, date2) Returns number of months between dates date1 anddate2

NEXT_DAY(date, ‘day’) Returns the date of the first weekday named by day that is later than the date date

TO_CHAR(date, ‘format’) Convertsdateof DATEdatatype to a value of  VARCHAR2datatype in the format specified by the date formatformat. If you omit format,dateis converted to aVARCHAR2value in the default date format.

TO_DATE(string, ‘format’) Convertsstring of CHARor VARCHAR2datatype to a value of DATEdatatype, in the date format specified by format. If you omit format,string  must be in the default date format.

 Date Formats (With both TO_CHAR and TO_DATE)

Format Example/  Description

MM 12 RM XII MON DEC MONTH DECEMBER DDD 354 DD 23

D 6 (day of the week, where Sunday is 1)

DY FRI DAY FRIDAY YYYY 1982 SYYYY -1000 YYY 982 YY 82 Y 2 YEAR NINETEEN-FORTY-SIX Q 3 (Number of quarter) WW 46 W 3

J Julian days since Dec 31, 4713 BC 

HH 11

HH12 Same as HH 

HH24 17

MI 58

(26)

SSSS 43000 (0-83699)

/ , - : . Punctuation for TO_CHAR A.M P.M AM or PM B.C A.D BC or AD

 Date Formats (Only with TO_CHAR)

Format Example/  Description “string” “is”

fm Suppresses padding of Month or Day in format TH DdTH or DDTH gives: 24th or 24TH

SP DDSP, DdSP, or ddSP gives: THREE, Three, or three

SPTH DdSPTH gives: Third

THSP Same as SPTH 

Examples : Date Functions

To get current date

SELECT SYSDATE FROM Dual; SYSDATE

---12-DEC-02

To get a date 5 months back 

SELECT SYSDATE "Today", ADD_MONTHS(SYSDATE, -5) "Result" FROM DUAL; Today Result

---12-DEC-02 12-JUL-02

To get the date of next Friday

SELECT SYSDATE, NEXT_DAY(SYSDATE, 'THURSDAY') FROM DUAL; SYSDATE NEXT_DAY

---12-DEC-02 19-DEC-02 To get the last of the month

SELECT SYSDATE "Today", LAST_DAY(SYSDATE) "MonthEnd" FROM DUAL; Today Month End

---12-DEC-02 31-DEC-02

(27)

SELECT SYSDATE TODAY, LAST_DAY(ADD_MONTHS(SYSDATE, 6))+1"REVIEW ON" FROM DUAL;

TODAY REVIEW ON ---12-DEC-02 01-JUL-03

To get years of experience of employees in department 20 SELECT ename "Name", hiredate "Joined",

ROUND(MONTHS_BETWEEN(SYSDATE, hiredate)/12) "Experience" FROM emp WHERE deptno=20;

Name Joined Experience ---

---JONES 02-APR-81 22

SCOTT 19-APR-87 16

ADAMS 23-MAY-87 16

FORD 03-DEC-81 21

To get the dates in words

SELECT hiredate, TO_CHAR(hiredate,'DD, Month, Year' ) "In Words" FROM emp WHERE deptno=10;

HIREDATE In Words

--- ---09-JUN-81 09, June , Nineteen Eighty-One 23-JAN-82 23, January , Nineteen Eighty-Two

SELECT hiredate, TO_CHAR(hiredate, 'fmMonth, ddTH, YYYY') "In Words" FROM emp WHERE deptno=10;

HIREDATE In Words

--- ---09-JUN-81 June, 9th, 1981

23-JAN-82 January, 23rd, 1982 To get the current time

SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') "Time Now" FROM DUAL; Time Now

---12:20:08

To get the day name of a particular date SELECT TO_DATE('29-Aug-68') "Date",

TO_CHAR(TO_DATE('29-Aug-68'), 'DAY') "Day" FROM DUAL;

Date Day

---29-AUG-68 THURSDAY

(28)

Joins to Query Multiple Tables

? Joins are powerful relational operators that combine data from multiple tables into a single result table.

? There are different types of joins :

o Cartesian Joins

o Equi Joins

o Self Joins

o Outer Joins

Cartesian Joins

? In this join the tables are joined without any WHERE clause ; each row of one table matches every row of the other table.

? It is also known as Natural Join. ? Examples:

Equi Joins

? When two tables are joined together using equality of values in one or more columns they make an equi join.

? Table prefixes are used to prevent ambiguity and the where clause is used to specify which columns are to be joined.

? Example:

SELECT supp_name, po_status FROM suppliertab, orderfile;

/* List all the information about the departments and courses offered by them */

SELECT *

FROM department, course;

/* List the lecturers' name and their department name */

SELECT lecturer.c_Lect_Name, department.c_Dept_Name FROM lecturer, department

WHERE lecturer.i_Dept_No = department.i_Dept_No ORDER BY lecturer.i_Dept_No;

(29)

Outer Joins

? When two tables are joined, only those that satisfy the given criteria are retrieved by the query.

? If the user wants to check which were the rows that did not have a corresponding match and still view the data with N ULL values outer joins are made use of. ? Examples:

 Note:

? In the above example , the (+) is placed on the side of the table whose co lumns do not have a corresponding match in the other table.

? Also note that you cannot have (+) o perator for both the joined tables.

Self Joins

? When one row of a table is joined with another of the same table you perform a SELF JOIN.

? Example:

In the above example, since both the employee and his/her manager are part of the same table a self joi n is made use of.

/* List suppliers and their order status (also list the supplier who have not placed the orders */

SELECT supp_name, po_num, po_status FROM suppliertab, orderfile

WHERE orderfile.supp_code(+)= suppliertab.supp_code;

/* List the lecturers name and their department’s name (list those d e-partment also which does not have any employee */

SELECT lecturer.c_Lect_Name, department.c_Dept_Name FROM lecturer, department

WHERE lecturer.i_Dept_No =(+) department.i_Dept_No ORDER BY lecturer.i_Dept_No;

/* List the name of each employee along with his/her manager from

emptable */

SELECT worker.ename, manager.ename FROM emp worker, emp manager WHERE worker.mgr = manager.empno;

(30)

 Note: To distinguish the columns of the copy of the same table , table aliases are used. In JOINS when the table name has to be written to specify columns more than once then it is better to use aliases in place of writing big table name s.

Set Operators

? Set operators combine 2 or more queries into a single result. The data type of the corresponding columns must be the same.

? The different set operators:

o UNION

o INTERSECT o MINUS

UNION

? This operator is useful when it is required to draw info rmation from more than one table that have the same structure.

? It returns rows of the first query plus the rows of the second query after eliminating duplicate rows.

? Example:

INTERSECT

? INTERSECTreturns only those rows that are common to bo th queries. ? Example:

/* List the employees from account and research tables whose salary is greater than 2000 */

SELECT ename, sal FROM account

WHERE sal > 2000 UNION

SELECT ename , sal FROM research

(31)

 MINUS

? MINUSreturns rows that are unique to the first query. ? Example:

.

Nested Queries

? In this type of query the result of one query is dynamically substituted in the cond i-tion (WHEREclause) of another query.

? In nested queries, the sub query within the WHEREclause is evaluated first. The r eturn value is then substituted in the condition of the outer query.

? Oracle supports infinite level of nesting.

? The general syntax for a nested or sub query is :

/* List the jobs that are common in research, accounts and sales d e-partment */

SELECT job FROM research INTERSECT

SELECT job FROM accounts INTERSECT

SELECT job FROM sales;

/* List the jobs which are done in accounts only and not in sales */ SELECT job FROM account

MINUS

SELECT job FROM sales;

SELECT col1 [, col2]... FROM table1

WHERE column operator (SELECT column FROM table2

(32)

? Examples:

 Note: When using relational operators with subqueries, ensure that the sub query returns a single row output. For comparison with a set of rows returned by the subquery, use the

INoperator.

Correlated Subqueries

? SQL sub queries that perform the subquery over and over again, once for each row of  the outer query are called correlated subqueries.

? Example:

/* To list the names of employees drawing the highest salary */  SELECT ename, sal FROM emp

WHERE sal = (SELECT MAX (sal) FROM emp);

ENAME SAL

---SCOTT 5000

FORD 5000

/* List all items whos e quantity is greater than the average transaction qua ntity for that item */ 

SELECT item_code, trans_qty FROM trans_file tfile

WHERE trans_qty > ( SELECT AVG(trans_qty) FROM trans_file

WHERE tfile.item_code = trans_file.item_code) ORDER BY item_code;

/* To list all the managers in the emp table who have more than 1 person reporting to him/her */ 

SELECT empno FROM emp WHERE empno IN

(SELECT mgr FROM emp

GROUP BY mgr HAVING COUNT(*) > 1); EMPNO

---7566 7698

(33)

 Note: While referring to the outer query , alias is mandatory in the inner query.

Using theEXISTSoperator in subqueries

? EXISTS is a special operator that returns TRUE if a subquery returns at least one row.

? Examples:

 /* To list the employee details only if more than 3 employees are present in dept 20 */ 

SELECT * FROM emp

WHERE deptno = 20 AND EXISTS (SELECT COUNT(*)

FROM emp WHERE deptno = 20 GROUP BY deptno

HAVING COUNT (*) > 3 );

 /* To list out the department details in which there are any employees */  SELECT dname, deptno FROM dept

WHERE EXISTS

(SELECT * from emp

WHERE dept.deptno = emp.deptno);

DNAME DEPTNO

---Accounting 10

Research 20

(34)

D

Da

atta

a M

Ma

an

niip

pu

ulla

attiio

on

n C

Co

om

mm

ma

an

nd

ds

s

The DML commands INSERT,UPDATE, and DELETEare discussed in this section.

The

INSERT

command

? INSERTstatement is used to insert values into the table. ? Syntax:

? The INSERTwith aVALUESclause inserts a single row into the table

? The INSERTwith aquery is used to add all the rows returned by a query at once. ? All data types to be included in quote s(' ') , except numeric data types.

? Examples:

The

DELETE

Command

? Using the DELETE statement row (s) can be deleted from a table. ? Syntax:

? Examples:

The

UPDATE

Command

? The UPDATEstatement is used to change existing values in a specified table. INSERT INTO table [ ( column [, column ]… ) ]

{ VALUES ( expression [, expression ]… ) | query }

INSERT INTO dept VALUES (60, 'Education', ' Boston'); INSERT INTO emp (empno, ename, deptno)

VALUES (8000, 'John', 20);

--rest of the columns will take a null or default value

DELETE FROM table [WHERE condition];

DELETE FROM emp; -- deletes all rows DELETE FROM emp WHERE empno=8000;

(35)

? Syntax:

 Note: Update without aWHEREclause will update all the rows in a table. ? Example:

UPDATE table SET

{column = expression [, column = expression]... | ( column [, column]…) = (subquery ) } [WHERE condition];

To modify the salary and dep artment number of SMITH UPDATE emp

SET sal = sal + 100, deptno = 30 WHERE ename = 'SMITH';

(36)

T

Th

he

e T

Trra

an

ns

sa

ac

cttiio

on

n C

Co

on

nttrro

oll C

Co

om

mm

ma

an

nd

ds

s

? A transaction(or a logical unit of work) is a sequence of SQL statements that Oracle treats as a single unit.

? The Transaction Controlcommands manage changes made by insert, update or d e-lete commands.

? The transaction control commands are:

o COMMIT o ROLLBACK

? A transaction begins with the first executable SQL statement after a COMMIT,

ROLLBACKor connection to the database. A transaction ends with a COMMIT,

ROLLBACKor disconnection (intentional or unintentional) from the d atabase.

The

COMMIT

Command

? COMMITis used to end the current transaction and make permanent all changes done in the transaction.

? This comma nd also releases the transaction's locks. ? Example:

Upon commit, the inserted row resides permanently in the database .

The

ROLLBACK

Command

? AROLLBACKundoes all the changes in the current transaction, and ends the transa c-tion. It also releases the transaction's locks.

? Example:

ROLLBACKwithSAVEPOINT

? Using the ROLLBACKcommand with the TO SAVEPOINTclause performs the fo l-lowing o perations:

o Rolls back just the portion of the transaction after the savepoint.

o Erases all savepoints created after that savepoint.

o Releases all the locks obtained since the savepoint.

INSERT INTO dept VALUES (60, 'Education', 'London'); COMMIT;

DELETE FROM emp; ROLLBACK;

(37)

? Example :

In the above example, only the DELETEstatement will be rolled back. The UPDATEis neither committed nor rolled back at this point.

UPDATE emp SET sal = sal + 100 WHERE deptno = 10; SAVEPOINT s1;

DELETE FROM emp WHERE empno = 8000; ROLLBACK TO SAVEPOINT s1;

(38)

W

Wo

orrk

kiin

ng

g w

wiitth

h O

Otth

he

err S

Sc

ch

he

em

ma

a O

Ob

b j

 je

ec

ctts

s

? Some of the database schema objects that we will work with are:

o Sequences o Synonyms o Views o Indexes o Clusters

Sequences

? Sequences are used to generate sequential numbers, helpful in multi -user environ-ments to generate and return unique se quential numbers, and to generate unique int e-gers for use as primary keys.

? Sequence generators are objects that are stored as part of the database. ? Syntax:

 Note:

? The default INCREMENT BYis 1. A positive number will cause incrementing up and a negative number incrementing down.

? The default for MINVALUEfor ascending sequences is 1. ? The default for MAXVALUEfor descending sequences is -1.

? The default START WITHisMAXVALUEfor descending sequences and MINVALUE for ascending; use START WITHto override this default.

? To restart a sequence, specify CYCLE.

? CACHEallows a preallocated set of sequence numbers to be kept in memory. The d e-fault is 20.

? To use the contents of a sequence use seq_name.CURRVALor

seq_name.NEXTVAL.

? seqname.CURRVALreturns the current value of the sequence.

? seqname.NEXTVALreturns the next value of the sequence. Also increments the value.

CREATE SEQUENCE seqname [INCREMENT BY n]

[START WITH m]

[MAXVALUE integer | NOMAXVALUE ] [MINVALUE integer | NOMINVALUE ] [CYCLE | NOCYCLE ]

(39)

Examples:

To use the sequence:

 DUAL is Oracle supplied table that is made up of one column and one row. This table is available to all users by default.

Synonyms

? A synonym is just another name for a database object.

? Synonyms do not require additional storage other than its definition in the data di c-tionary.

? Synonyms are used:

o For security reasons to mask the owner and name of the object

o To simplify SQL statements.

o To provide transparency to user from remote databases. ? Whenever a user execute the query for example,

SELECT *

FROM table_name; Oracle interprets it as SELECT *

FROM owner.table_name; CREATE SEQUENCE dept_seq

START WITH 1 INCREMENT BY 1;

INSERT INTO dept (deptno, dname, loc)

VALUES (dept_seq.NEXTVAL, 'Education', 'Fremont');

SELECT dept_seq.CURRVAL FROM DUAL; SELECT dept_seq.NEXTVAL FROM DUAL;

(40)

If the user is not the owner of the table then he has to use

owner.table_name. In order to hide the actual owner as well as ta-ble_namesynonyms can be used as fo llows:

CREATE PUBLIC SYNONYM syn_name FOR owner.table_name;

Example:Creation and use of Synonyms

The PUBLICoption indicates that any user can access the synonym.

 Views

? Aviewis a logical table. It does not contain any data itself; it draws data from other tables and views.

? The tables on which a view is based are called  base tables.

? Views are constructed with queries. A view takes the output of a query and treats it as a table; hence, a view can also be thought of as a “stored query” or a “virtual table”. ? You can use the normal querying on the views as though views were tables the

m-selves.

? Insert, update, and delete to the base tables through the view is subject to certain r e-strictions.

? All operations performed on a view actually affect data in the base tables of the view. ? Views can used to for various reasons like:

o Store complex queries

o Provide security to base tables by exposing only certain portions of table data through views

o Isolate applications from changes in definitions of base tables

Creating Views

The CREATE VIEW DDLcommand is used to create a view, that mentions a query based on which the view is created (in the absence of  aliases, the view column names are same as that mentioned in the query) :

CREATE PUBLIC SYNONYM pucust

FOR scott.customer; SELECT *

FROM pucust;

INSERT INTO pucust(...) VALUES(...)

CREATE [OR REPLACE] VIEW

view  [ ( alias [, alias ]… ) ] AS query 

[WITH CHECK OPTION] [WITH READ ONLY]

(41)

WITH READ ONLYoption specifies that no deletes, inserts, or updates to the base table can be pe rformed through the view.

WITH CHECK OPTIONspecifies that insertsandupdatesperformed through the view must result in rows that the view query can select

Examples of Views

Suppose you would like to expose only part of the emptable. You can do so by creating a view that selects certain columns from the table, and using (or granting privileges on, to other users) this view, as shown below.

Table EMP

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7329 SMITH CLERK 7902 17-DEC-87 300.00 80 20 7499 ALLEN SALESMAN 7698 20-FEB-88 300.00 600 30 7521 WARD SALESMAN 7698 22-FEB-88 5.00 250 30 7566 JONES MANAGER 7839 02-APR-88 975 20

EMPNO ENAME JOB MGR DEPTNO 7329 SMITH CLERK 7902 20 7499 ALLEN SALESMAN 7698 30 7521 WARD SALESMAN 7698 30 7566 JONES MANAGER 7839 20

The above view is created with the DDL:

You can then use the view as a normal table:

If you did not want any insert or update to the base table through this view , create the view with WITH READ ONLYoption:

 View STAFF

CREATE VIEW staff AS

SELECT empno, ename, job, manager, deptno FROM emp;

CREATE VIEW staff AS

SELECT empno, ename, job, manager, deptno FROM emp

WITH READ ONLY;

SELECT * FROM Staff;

(42)

If you want to create a view that exposes only the clerks in the emp table, create a view as follows:

The WITH CHECK OPTIONspecifies that inserts and updates performed through the view must result in rows that the view query can select. In this example any row other than the one having job='CLERK'cannot be inserted into the table.

 Note: You cannot perform inserts, updates or deletes for a view if the view query co n-tains:

o  joins

o set operators

o group functions

o GROUP BYclause

o the DISTINCToperator

Indexes

? The data in the tables are not sto red in any particular order of any column. Hence, when you access the table data, it is sequential access. This can pose a performance problem if the table is huge.

? You can use  Indexes to provide random access to data, there by enhancing perfor m-ance due to faster access to the data.

? Indexes are stored separately from the actual data. The index is made up of the co l-umn on which you are indexing, and the physical address ( ROWID) of the row with that column value.

? Indexes are referred to whenever the indexed c olumns are referenced in the WHERE clause.

o E.g., if the query

SELECT ename, sal FROM emp WHERE ename = 'SMITH'

is issued, and if there is an index on enamealready, the index will be first searched to find the location of the row with specified enamevalue, and then the row is fetched from the table.

? With every data manipulation, the appropriate indexes are automatically updated.

o E.g., if there is an insert into the table, the corresponding indexes are also updated to reflect the new rows.

? Indexes are created using the CREATE INDEXDDL command:

CREATE VIEW clerk (id_number, person, department, position) AS SELECT empno, ename, deptno,job

FROM emp WHERE job = 'CLERK' WITH CHECK OPTION;

(43)

Example

When you create an index:

? Oracle gets and sorts the columns to be indexed,

? Stores theROWIDalong with the index value for each row , and ? Loads the index from the bottom up.

 Note:

? Oracle, by default, creates indexes for columns defined as PRIMARYkey orUNIQUE key, at the time of table creation .

? Internally, Oracle uses B*Trees to manage the indexes.

Composite Indexes

A Composite index is one that is created on multiple columns in a table. It is also known as concatenated index.

Example

Clusters

? A Cluster is a group of tables that share the same data blocks, because they share the same columns and are often used together.

? If multiple tables are clustered together, they should have commo n column(s), called the cluster key. E.g., emp and dept tables can be clustered together based on the cluster key deptnothat is common to both the tables.

? Advantages of using clusters:

o Disk I/O is reduced and access time improves for joins of clustered ta bles.

o Each cluster key is stored only once in the cluster and the cluster index, no matter how many rows of different tables contain the value.

Creating a Cluster

? To create a cluster:

o First create the cluster using the CREATE CLUSTERcommand CREATE INDEX idx_ename ON emp(ename);

CREATE INDEX index_name ON table_name( col1, col2, col3);

CREATE INDEX ordcust

Figure

Updating...

References

Related subjects :