presentation on oracle sql

8/3/2019 Presentation on Oracle SQL

1/238

SQL Plus Introduction

Copyright Jsoft Solutions Ltd


2/238

Communicating with a RDBMS Using SQL

DatabaseDatabase

SQL> SELECT loc2 FROM dept;

SQL statementSQL statementisenteredisentered

MessageissentMessageissenttothe Clienttothe Client

QueryissenttoQueryissenttoDatabaseDatabase

LocationLocation--------------------------DallasDallasNew YorkNew YorkSanta ClaraSanta Clara

DeptDept


3/238

Types Of SQL Statements

SELECT Data Retrieval

INSERT

DELETE

UPDATE

Data Manipulation Language

(DML)

CREAT

EALTER

DROP

RENAME

TRUNCATE

Data Definition Language(DDL)

COMMIT

ROLLBACK

SAVEPOINT

T

ransaction Control Language(TCL)

GRANT

REVOKE

Data Control Language

(DCL)


4/238

Objectives

Capabilities of SQL SELECT statements

Execute a basic SELECT statement

Differentiate between SQL statementsand SQL*Plus commands


5/238

Basic SELECT Statement

SELECT [DISTINCT] {*, column [alias],...}FROM table;

SELECT identifies whatcolumns

FROM identifies which table


6/238

Guidelines Writing SQL Statements

SQL statementsarenotcasesensitive.SQL statementscanbeononeorMore

lines.

Keywordscannotbeabbreviatedorsplitacross lines.

Clausesare usuallyplacedon

separate lines.Tabsandindentsare usedtoenhance

readability.


7/238

SQL> SELECT *2 FROM dept;

Selecting All Columns

DEPTNO DNAME LOC--------- -------------- -------------

10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON


8/238

Selecting Specific Columns

DEPTNO LOC--------- -------------

10 NEW YORK20 DALLAS30 CHICAGO40 BOSTON

SQL> SELECT deptno, loc2 FROM dept;


9/238

Arithmetic Expressions

Basic Arithmetic operators used in SQLs

Operator

+

-

*

/

Description

Add

Subtract

Multiply

Divide


10/238

Using Arithmetic Operators

SQL> SELECT ename, sal, sal+3002 FROM emp;

ENAME SAL SAL+300---------- --------- ---------KING 5000 5300

BLAKE 2850 3150CLARK 2450 2750JONES 2975 3275 MARTIN 1250 1550 ALLEN 1600 1900...

14 rows selected.


11/238

Operator Precedence

Multiplication and division take priorityover addition and subtraction.

Operators of the same priority areevaluated from left to right.

Override operator precedence usingparentheses

** // ++ __


12/238

Operator Precedence

SQL> SELECT ename, sal, 12*sal+1002 FROM emp;

ENAME SAL 12*SAL+100---------- --------- ----------

KING 5000 60100BLAKE 2850 34300CLARK 2450 29500JONES 2975 35800 MARTIN 1250 15100 ALLEN 1600 19300

...14 rows selected.


13/238

Using Parentheses

SQL> SELECT ename, sal, 12*(sal+100)2 FROM emp;

ENAME SAL 12*(SAL+100)

---------- --------- -----------KING 5000 61200BLAKE 2850 35400CLARK 2450 30600JONES 2975 36900 MARTIN 1250 16200



14/238

Defining a Null Value

A null is a value that is unavailable, unassigned, unknown,

or inapplicable.

A null is not the same as zero or a blank space.

SQL> SELECT ename, job, comm2 FROM emp;

ENAME JOB COMM---------- --------- ---------KING PRESIDENTBLAKE MANAGER

...TURNER SALESMAN 0



15/238

Using the DISTINCT Clause

Eliminateduplicaterowsby usingtheDISTINCTEliminateduplicaterowsby usingtheDISTINCTkeywordkeyword

SQL> SELECT DISTINCT deptno

2 FROM emp;

DEPTNO---------

102030


16/238

Objectives

Limiting the rows retrieved

Sorting the rows retrieved


17/238

Using the WHERE Clause

Restrict the rows returned by using theWHERE clause.

SELECT [DISTINCT] {*, column [alias], ...}FROM table[WHERE condition(s)];

The WHERE clause follows the FROMclause.


18/238

Using the WHERE Clause

SQL> SELECT ename, job, deptno2 FROM emp3 WHERE job='CLERK';

ENAME JOB DEPTNO---------- --------- ---------JAMES CLERK 30SMITH CLERK 20 ADAMS CLERK 20

MILLER CLERK 10


19/238

Working with Character Strings and Dates

Character strings and date values are

enclosed in single quotation marks

Character values are case-sensitive and

date values are format-sensitive

Default date format is 'DD-MON-YY'

SQL> SELECT ename, job, deptno2 FROM emp3 WHERE ename = 'JAMES';


20/238

Using the Comparison Operators

Operator

=

>

>=

SELECT ename, sal, comm2 FROM emp3 WHERE sal


22/238

Other Comparison Operators

Operator

BETWEEN

...AND...

IN(list)

LIKE

IS NULL

Meaning

Betweentwovalues(inclusive)

Matchanyofa listofvalues

Matchacharacterpattern

Isanull value


23/238

Using the BETWEEN Operator

ENAME SAL---------- --------- MARTIN 1250TURNER 1500

WARD 1250 ADAMS 1100 MILLER 1300

SQL> SELECT ename, sal2 FROM emp

3 WHERE sal BETWEEN 1000 AND 1500;

Lowerlimit

Higherlimit

Use the BETWEEN operator to display rows based on a

range of values.


24/238

Using the IN Operator

UsetheINoperatortotestforvaluesina

list.

SQL> SELECT empno, ename, sal, mgr2 FROM emp3 WHERE mgr IN (7902, 7566, 7788);

EMPNO ENAME SAL MGR--------- ---------- --------- ---------

7902 FORD 3000 75667369 SMITH 800 79027788 SCOTT 3000 75667876 ADAMS 1100 7788


25/238

Using the LIKE Operator

Use the LIKE operator to perform wildcard

searches of valid search string values.

% denotes zero or many characters

_ denotes one character

SQL> SELECT ename

2 FROM emp3 WHERE ename LIKE 'S%';


26/238

Using the LIKE Operator

You can use the ESCAPE identifier to

search for"%" or "_".

SQL> SELECT ename2 FROM emp3 WHERE ename LIKE '_A%';

ENAME----------JAMES

WARD


27/238

Using the IS NULL Operator

Usethe IS NULL operatortotestfornull

values

SQL> SELECT ename, mgr2 FROM emp3 WHERE mgr IS NULL;

ENAME MGR---------- ---------

KING


28/238

Logical Operators

Operator

AND

OR

NOT

Meaning

ReturnsTRUEifboth component

conditionsare TRUE

ReturnsTRUEifeithercomponent

conditionisTRUE

ReturnsTRUEifthefollowing

conditionis FALSE


29/238

Using the AND Operator

AND requires both conditions to be TRUE.AND requires both conditions to be TRUE.

SQL> SELECT empno, ename, job, sal2 FROM emp

3 WHERE sal>=11004 AND job='CLERK';

EMPNO ENAME JOB SAL--------- ---------- --------- ---------

7876 ADAMS CLERK 11007934 MILLER CLERK 1300


30/238

Using the OR Operator

OR requires either condition to be TRUE.OR requires either condition to be TRUE.

SQL> SELECT empno, ename, job, sal2 FROM emp3 WHERE sal>=11004 OR job='CLERK';

EMPNO ENAME JOB SAL--------- ---------- --------- ---------

7839 KING PRESIDENT 50007698 BLAKE MANAGER 28507782 CLARK MANAGER 24507566 JONES MANAGER 29757654 MARTIN SALESMAN 1250



31/238

Using the NOT Operator

SQL> SELECT ename, job2 FROM emp3 WHERE job NOT IN ('CLERK','MANAGER','ANALYST');

ENAME JOB---------- ---------KING PRESIDENT MARTIN SALESMAN ALLEN SALESMAN

TURNER SALESMAN WARD SALESMAN


32/238

Rules of Precedence

Overriderulesofprecedenceby usingparentheses.

OrderEvaluated Operator

1 All comparisonoperators

2 NOT3 AND

4 OR


33/238

Using the ORDER BY Clause

Sort rows with the ORDER BY clause

ASC: ascending order, default DESC: descending order

SQL> SELECT ename, job, deptno, hiredate2 FROM emp3 ORDER BY hiredate;

ENAME JOB DEPTNO HIREDATE---------- --------- --------- ---------SMITH CLERK 20 17-DEC-80

ALLEN SALESMAN 30 20-FEB-81...14 rows selected.


34/238

Sorting in Descending Order

SQL> SELECT ename, job, deptno, hiredate2 FROM emp3 ORDER BY hiredate DESC;

ENAME JOB DEPTNO HIREDATE---------- --------- --------- --------- ADAMS CLERK 20 12-JAN-83SCOTT ANALYST 20 09-DEC-82 MILLER CLERK 10 23-JAN-82JAMES CLERK 30 03-DEC-81FORD ANALYST 20 03-DEC-81KING PRESIDENT 10 17-NOV-81 MARTIN SALESMAN 30 28-SEP-81...14 rows selected.


35/238

Sorting the rows by Column Alias

SQL> SELECT empno, ename, sal*12 annsal2 FROM emp3 ORDER BY annsal;

EMPNO ENAME ANNSAL--------- ---------- ---------

7369 SMITH 96007900 JAMES 114007876 ADAMS 132007654 MARTIN 150007521 WARD 150007934 MILLER 15600

7844 TURNER 18000...14 rows selected.


36/238

Sorting by Multiple Columns

The order of ORDER BY list is the order of sort.

SQL> SELECT ename, deptno, sal2 FROM emp3 ORDER BY deptno, sal DESC;

ENAME DEPTNO SAL---------- --------- ---------KING 10 5000CLARK 10 2450 MILLER 10 1300FORD 20 3000...14 rows selected.


37/238

Summary

SELECT [DISTINCT] {*, column [alias], ...}FROM table[WHERE condition(s)][ORDER BY {column, expr, alias} [ASC|DESC]];


38/238

Objectives

Describe various types of functions

available in SQL

Use character, number, and date

functions in SELECT statements

Describe the use of conversion functions


39/238

Types of SQL Functions

FunctionsFunctions

SingleSingle--rowrowfunctionsfunctions

MultipleMultiple--rowrowfunctionsfunctions


40/238

Single-Row Functions

Act on each row returned

Return one result per row

Can be nested

function_name (column|expression, [arg1, arg2,...])


41/238

Single-Row Functions

ConversionConversion

CharacterCharacter

NumberNumber

DateDate

GeneralGeneral SingleSingle--rowrowfunctionsfunctions


42/238

Using Character Functions

CharacterCharacterfunctionsfunctions

LOWERLOWER

UPPERUPPER

INITCAPINITCAP

CONCATCONCAT

SUBSTRSUBSTR

LENGTHLENGTH

INSTRINSTR

LPADLPAD

CaseconversionCaseconversionfunctionsfunctions

CharactermanipulationCharactermanipulationfunctionsfunctions


43/238

Function Result

Using Case Conversion Functions

Convertcaseforcharacterstrings

LOWER('SQL Course')UPPER('SQL Course')

INITCAP('SQLCourse')

sql courseSQL COURSE

Sql Course


44/238

Using Case Conversion Functions

Displaytheemployeenumber,name,and

department

numberforemployeeBlake.SQL> SELECT empno, ename, deptno2 FROM emp3 WHERE ename = 'blake';

no rows selectedno rows selected

EMPNO ENAME DEPTNO--------- ---------- ---------

7698 BLAKE 30

SQL> SELECT empno, ename, deptno2 FROM emp3 WHERE LOWER(ename) = 'blake';


45/238

CONCAT('Good','String')SUBSTR('String',1,3)

LENGTH('String')

INSTR('String','r')

LPAD(sal,10,'*')

GoodStringStr

6

3

******5000

Function Result

Character Manipulation Functions

Manipulatecharacterstrings


46/238

Using the Character Manipulation Functions

SQL> SELECT ename, CONCAT (ename, job), LENGTH(ename),

2 INSTR(ename, 'A')

3 FROM emp

4 WHERE SUBSTR(job,1,5) = 'SALES';

ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,'A')---------- ------------------- ------------- ----------------

MARTIN MARTINSALESMAN 6 ALLEN ALLENSALESMAN 5 TURNER TURNERSALESMAN 6 0

WARD WARDSALESMAN 4


47/238

Using the Number Functions

ROUND: Rounds value to specified

decimal ROUND(45.926, 2) 45.93

TRUNC: Truncates value tospecified decimal

TRUNC(45.926, 2) 45.92

MOD: Returns remainder of

division

MOD(1600, 300) 100


48/238

Working with Dates

Oracle stores dates in an internal numeric

format: Century, year, month, day, hours,

minutes, seconds.

T

he default date format is DD-MON-YY.

SYSDATE is a function returning date and time.

DUAL is a dummy table used to view

SYSDATE.


49/238

Arithmetic with Dates

Add or subtract a number to or from a

date for a resultant date value.

Subtract two dates to find the numberof

days between those dates.

Add hours to a date by dividing the

number of hours by 24.


50/238

Using Arithmetic Operators With Dates

SQL> SELECT ename, (SYSDATE-hiredate)/7 WEEKS2 FROM emp3 WHERE deptno = 10;

ENAME WEEKS---------- ---------KING 830.93709CLARK 853.93709 MILLER 821.36566


51/238

Working with Date Functions

NumberofmonthsbetweentwodatesMONTHS_BETWEEN

ADD_MONTHS

NEXT_DAY

LAST_DAY

ROUND

TRUNC

Addcalendarmonthsto

dateNextdayofthedatespecified

Lastdayofthemonth

Rounddate

Truncatedate

FUNCTION DESCRIPTION


52/238

Using Date Functions

ROUND('25ROUND('25--JULJUL--95','MONTH') 0195','MONTH') 01--AUGAUG--9595

ROUND('25ROUND('25--JULJUL--95','YEAR')95','YEAR') 0101--JANJAN--9696

TRUNC('25TRUNC('25--JULJUL--95','MONTH')95','MONTH') 0101--JULJUL--9595

TRUNC('25TRUNC('25--JULJUL--95','YEAR')95','YEAR') 0101--JANJAN--9595


53/238

Conversion Functions

ImplicitdatatypeImplicitdatatypeconversionconversion

ExplicitdatatypeExplicitdatatypeconversionconversion

DatatypeDatatypeconversionconversion


54/238

Implicit Datatype Conversion

Forassignments,Oraclecanautomaticallyconvert

VARCHAR2orCHAR

From To

VARCHAR2orCHAR

NUMBER

DATE

NUMBER

DATE

VARCHAR2

VARCHAR2


55/238

Explicit Datatype Conversion

NUMBERNUMBER CHARACTERCHARACTER

TO_CHARTO_CHAR

TO_NUMBERTO_NUMBER

DATEDATE

TO_CHARTO_CHAR

TO_DATETO_DATE


56/238

Using the TO_CHAR Function withDates

Theformatmodel:

Mustbeenclosedinsinglequotationmarksandiscasesensitive

Canincludeanyvaliddateformatelement Hasanfm elementtoremovepadded

blanksorsuppress leading zeros

Isseparatedfromthedatevaluebyacomma

TO_CHAR(date, 'fmt')


57/238

YYYY

Date Format Model

YEAR

MM

MONTH

DY

DAY

Full yearinnumbers

Yearspelledout

2-digitvalueformonth

3-letterabbreviationoftheday

oftheweekFull nameoftheday

Full nameofthemonth


58/238

Date Format Model Elements

Timeelementsformatthetimeportionofthedate.

HH24:MI:SS AM 15:45:32PM

DD "of" MONTH 12ofOCTOBER

ddspth fourteenth

Add character strings by enclosing them in double quotation marks.

Number suffixes spell out numbers.

Using the TO CHAR Function with Numbers


59/238

Using the TO_CHAR Function with Numbers

UsetheseformatswiththeTO_CHARfunctiontodisplayanumbervalueasacharacter.

TO_CHAR(number, 'fmt')

9

0

$

L.

,

Representsanumber

Forcesa zerotobedisplayed

Placesafloatingdollarsign

Usesthefloating local currencysymbolPrintsadecimal point

Printsathousandindicator


60/238

TO_NUMBER and TO_DATE Functions

Convert a character string to a number

format using the TO_NUMBER function

TO_NUMBER(char)

Convertacharacterstringtoadateformat usingtheTO_DATEfunction

TO_DATE(char[, 'fmt'])


61/238

Windowing Technique using the RR Date Format

Current Year1995199520012001

SpecifiedDate27-OCT-9527-OCT-1727-OCT-1727-OCT-95

RR Format1995201720171995

YY Format1995191720172095

Iftwodigitsofthecurrent

yearare

0-49

0-49 50-99

50-99

Thereturndateisinthecurrentcentury.

Thereturndateisinthecenturyafterthecurrentone.

Thereturndateisinthecenturybeforethecurrentone.

Thereturndateisinthecurrentcentury.

Ifthespecifiedtwo-digityearis


62/238

SQL> SELECT ename, sal, comm, (sal*12)+NVL(comm,0)2 FROM emp;

Using the NVL Function

ENAME SAL COMM (SAL*12)+NVL(COMM,0)---------- --------- --------- --------------------KING 5000 60000BLAKE 2850 34200CLARK 2450 29400JONES 2975 35700 MARTIN 1250 1400 16400 ALLEN 1600 300 19500...14 rows selected.


63/238

Using the DECODE Function

Facilitates conditional inquiries by doing thework of a

CASE orIF-THEN-ELSE statement

DECODE(col/expression, search1, result1[, search2, result2,...,]

[, default])


64/238

Using the DECODE Function

SQL> SELECT job, sal,2 DECODE(job, 'ANALYST', SAL*1.1,3 'CLERK', SAL*1.15,4 'MANAGER', SAL*1.20,5 SAL)6 REVISED_SALARY

7 FROM emp;

JOB SAL REVISED_SALARY--------- --------- --------------PRESIDENT 5000 5000 MANAGER 2850 3420

MANAGER 2450 2940...14 rows selected.

N i F i


65/238

Nesting Functions

Single-row functions can be nested to

any level.

They follow Function of Function rule

F3(F2(F1(col,arg1),arg2),arg3)

Step 1 = Result 1

Step 2 = Result 2

Step 3 = Result 3

S


66/238

Summary

Perform calculations on data

Modify individual data items

Alter date formats for display

Convert column data types

Obj ti


67/238

Objectives

Cartesian ProductsHow to access data from more than one

table using equality and non-equality

joins

View data that generally does not meet

a join condition by using outer joins

Join a table to itself

G tti D t f M lti l T bl


68/238

EMPNO DEPTNO LOC----- ------- --------7839 10 NEW YORK7698 30 CHICAGO

7782 10 NEW YORK7566 20 DALLAS7654 30 CHICAGO7499 30 CHICAGO...14 rows selected.

Getting Data from Multiple Tables

EMPEMP DEPTDEPT

EMPNO ENAME ... DEPTNO------ ----- ... ------7839 KING ... 107698 BLAKE ... 30...7934 MILLER ... 10

DEPTNO DNAME LOC------ ---------- --------


Wh t I J i ?


69/238

What Is a Join?

Use a join to query data from more than one

table.

SELECT table1.column, table2.columnFROM table1, table2

WHERE table1.column1 = table2.column2;

Writethe joinconditionintheWHEREclause. Prefixthecolumnnamewiththetablenamewhenthe

samecolumnnameappearsinmorethanonetable.

C t i P d t


70/238

Cartesian Product

A Cartesian product is formed when:

A join condition is omitted

A join condition is invalid

All rows in the first table are

joined to all rows in the second

table

C t i P d t


71/238

Cartesian Product

ENAME DNAME------ ----------KING ACCOUNTINGBLAKE ACCOUNTING

...KING RESEARCHBLAKE RESEARCH...56 rows selected.

EMP(14rows)EMP(14rows) DEPT(4rows)DEPT(4rows)

EMPNO ENAME ... DEPTNO------ ----- ... ------7839 KING ... 107698 BLAKE ... 30...7934 MILLER ... 10

DEPTNO DNAME LOC------ ---------- --------


CartesianCartesian

product:product:14*4=56rows14*4=56rows

T pes of Joins


72/238

Types of Joins

Equijoins

Non- Equijoins

Outer Joins

Self Joins

What Is an Equijoin?


73/238

What Is an Equijoin?

EMPEMP DEPTDEPTEMPNO ENAME DEPTNO------ ------- -------7839 KING 107698 BLAKE 307782 CLARK 107566 JONES 207654 MARTIN 307499 ALLEN 307844 TURNER 307900 JAMES 307521 WARD 307902 FORD 20

7369 SMITH 20...14 rows selected.

DEPTNO DNAME LOC------- ---------- --------

10 ACCOUNTING NEW YORK30 SALES CHICAGO10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS30 SALES CHICAGO30 SALES CHICAGO30 SALES CHICAGO30 SALES CHICAGO30 SALES CHICAGO20 RESEARCH DALLAS

20 RESEARCH DALLAS...14 rows selected.

Retrieving Records with Equijoins


74/238

Retrieving Records with Equijoins

SQL> SELECT emp.empno, emp.ename, emp.deptno,2 dept.deptno, dept.loc3 FROM emp, dept4 WHERE emp.deptno=dept.deptno;

EMPNO ENAME DEPTNO DEPTNO LOC----- ------ ------ ------ ---------7839 KING 10 10 NEW YORK7698 BLAKE 30 30 CHICAGO7782 CLARK 10 10 NEW YORK7566 JONES 20 20 DALLAS...14 rows selected.

Using Table Aliases


75/238

Using Table Aliases

Simplifyqueriesby usingtablealiases.

SQL> SELECT emp.empno, emp.ename, emp.deptno,

2 dept.deptno, dept.loc

3 FROM emp, dept

4 WHERE emp.deptno=dept.deptno;

SQL> SELECT e.empno, e.ename, e.deptno,

2 d.deptno, d.loc

3 FROM emp e, dept d

4 WHERE e.deptno=d.deptno;

Joining More Than Two Tables


76/238

Joining More Than Two Tables

NAME CUSTID----------- ------JOCKSPORTS 100TKB SPORT SHOP 101VOLLYRITE 102JUST TENNIS 103K+T SPORTS 105SHAPE UP 106 WOMENS SPORTS 107... ...9 rows selected.

CUSTOMERCUSTOMER CUSTID ORDID------- -------

101 610102 611104 612106 601102 602106 604106 605


ORDORD

ORDID ITEMID------ -------

610 3611 1612 1601 1602 1


ITEMITEM

Non Equijoins


77/238

Non-Equijoins

EMPEMP SALGRADESALGRADE

salary intheEMPsalary intheEMP

tableisbetweentableisbetween

lowsalaryandhighlowsalaryandhigh

salaryinthe SALGRADEsalaryinthe SALGRADE

tabletable

EMPNO ENAME SAL------ ------- ------7839 KING 50007698 BLAKE 28507782 CLARK 2450

7566 JONES 29757654 MARTIN 12507499 ALLEN 16007844 TURNER 15007900 JAMES 950


GRADE LOSAL HISAL----- ----- ------1 700 12002 1201 14003 1401 2000

4 2001 30005 3001 9999

Retrieving Records with Non Equijoins


78/238

Retrieving Records with Non-Equijoins

ENAME SAL GRADE---------- --------- ---------JAMES 950 1SMITH 800 1 ADAMS 1100 1...

14 rows selected.

SQL> SELECT e.ename, e.sal, s.grade2 FROM emp e, salgrade s

3 WHERE e.sal

4 BETWEEN s.losal AND s.hisal;

Outer Joins


79/238

Outer Joins

EMPEMP DEPTDEPT

NoemployeeintheNoemployeeinthe

OPERATIONS departmentOPERATIONS department

ENAME DEPTNO----- ------KING 10BLAKE 30CLARK 10

JONES 20...

DEPTNO DNAME------ ----------10 ACCOUNTING30 SALES10 ACCOUNTING

20 RESEARCH...40 OPERATIONS

Outer Joins


80/238

Outer Joins

You use an outer join to also see rows thatdo not usually meet the join condition.

Outer join operator is the plus sign (+).

SELECT table.column, table.column

FROM table1, table2

WHERE table1.column(+) = table2.column;

SELECT table.column, table.column

FROM table1, table2

WHERE table1.column = table2.column(+);

Using Outer Joins


81/238

Using Outer Joins

SQL> SELECT e.ename, d.deptno, d.dname

2 FROM emp e, dept d

3 WHERE e.deptno(+) = d.deptno

4 ORDER BY e.deptno;

ENAME DEPTNO DNAME---------- --------- -------------KING 10 ACCOUNTINGCLARK 10 ACCOUNTING...

40 OPERATIONS15 rows selected.

Self Joins


82/238

Self Joins

EMP(WORKER)EMP(WORKER) EMP(MANAGER)EMP(MANAGER)

"MGRintheWORKERtableisequal toEMPNOinthe"MGRintheWORKERtableisequal toEMPNOintheMANAGERtable"MANAGERtable"

EMPNO ENAME MGR ----- ------ ----7839 KING7698 BLAKE 78397782 CLARK 7839

7566 JONES 78397654 MARTIN 76987499 ALLEN 7698

EMPNO ENAME----- --------

7839 KING7839 KING

7839 KING7698 BLAKE7698 BLAKE

Objectives


83/238

Objectives

Various group functions

Group data using the GROUP BY clause

Include or exclude grouped rows by

using the HAVING clause

What Are Group Functions?


84/238

What Are Group Functions?

Group functions operate on sets of rows to give oneresult per group.

EMPEMP

maximummaximumsalaryinsalaryin

theEMPtabletheEMPtable

DEPTNO SAL--------- ---------

10 245010 5000

10 130020 80020 110020 300020 300020 297530 160030 285030 125030 95030 150030 1250

MAX(SAL)

---------

5000

Common Group Functions


85/238

Common Group Functions

AVG

COUNT

MAX

MIN

STDDEV

SUM

VARIANCE

Using Group Functions


86/238

Using Group Functions

SELECT column, group_function(column)FROM table[WHERE condition]

[ORDER BY column];

Using the COUNT Function


87/238


COUNT(*)---------

6

SQL> SELECT COUNT(*)2 FROM emp3 WHERE deptno = 30;

COUNT(*)returnsthenumberofrowsinatable.



88/238


COUNT(expr)returnsthenumberofnonnull

rows.

SQL> SELECT COUNT(comm)2 FROM emp

3 WHERE deptno = 30;

COUNT(COMM)-----------

4

Group Functions and Null Values


89/238

Group Functions and Null Values

Groupfunctionsignorenull valuesinthe

column.

SQL> SELECT AVG(comm)2 FROM emp;

AVG(COMM)---------

550

Using the NVL Function with Group Functions


90/238

Using the NVL Function with Group Functions

TheNVL functionforcesgroupfunctionsto

includenull values.

SQL> SELECT AVG(NVL(comm,0))2 FROM emp;

AVG(NVL(COMM,0))----------------

157.14286

Creating Groups of Data


91/238

Creating Groups of Data

EMPEMP

averageaveragesalarysalaryinEMPinEMPtabletable

foreachforeachdepartmentdepartment

2916.66672916.6667

21752175

1566.66671566.6667

DEPTNO SAL--------- ---------

10 245010 500010 130020 80020 110020 300020 300020 297530 160030 285030 125030 95030 150030 1250

DEPTNO AVG(SAL)------- ---------

10 2916.6667

20 2175

30 1566.6667

Using the GROUP BY Clause


92/238


SELECT column, group_function(column)FROM table[WHERE condition][GROUP BY group_by_expression][ORDER BY column];

DividerowsinatableintosmallergroupsbyusingtheGROUPBY clause.



93/238

g

All columnsinthe SELECT listthatarenotingroupfunctionsmustbeintheGROUPBY

clause.

SQL> SELECT deptno, AVG(sal)2 FROM emp

3 GROUP BY deptno;

DEPTNO AVG(SAL)--------- ---------

10 2916.6667

20 217530 1566.6667

Grouping by More Than One Column


94/238

p g y

EMPEMP

sumsalariesinsumsalariesintheEMPtabletheEMPtableforeach job,foreach job,groupedbygroupedbydepartmentdepartment

DEPTNO JOB SAL

--------- --------- ---------

10 MANAGER 2450

10 PRESIDENT 5000

10 CLERK 1300

20 CLERK 80020 CLERK 1100

20 ANALYST 3000

20 ANALYST 3000

20 MANAGER 2975

30 SALESMAN 1600

30 MANAGER 285030 SALESMAN 1250

30 CLERK 950

30 SALESMAN 1500

30 SALESMAN 1250

JOB SUM(SAL)

--------- ---------

CLERK 1300

MANAGER 2450

PRESIDENT 5000

ANALYST 6000

CLERK 1900

MANAGER 2975

CLERK 950

MANAGER 2850

SALESMAN 5600

DEPTNO

--------

10

10

10

20

20

20

30

30

30

Using the GROUP BY Clause on Multiple Columns


95/238

g p

SQL> SELECT deptno, job, sum(sal)

2 FROM emp3 GROUP BY deptno, job;

DEPTNO JOB SUM(SAL)

--------- --------- ---------10 CLERK 130010 MANAGER 245010 PRESIDENT 500020 ANALYST 600020 CLERK 1900

...9 rows selected.

Illegal Queries Using Group Functions


96/238

Anycolumnorexpressioninthe SELECT list

thatisnotanaggregatefunctionmustbeintheGROUPBY clause.

SQL> SELECT deptno, COUNT(ename)2 FROM emp;

SELECT deptno, COUNT(ename)*

ERROR at line 1:ORA-00937: not a single-group group function

Illegal Queries Using Group Functions


97/238

g g p

You cannot use the WHERE clause to

restrict groups.Use the HAVING clause

to restrict groups.

SQL> SELECT deptno, AVG(sal)

2 FROM emp3 WHERE AVG(sal) > 20004 GROUP BY deptno;

WHERE AVG(sal) > 2000*

ERROR at line 3:ORA-00934: group function is not allowed here

Segregating Group Results


98/238

g g g p

maximummaximumsalarysalary

perdepartmentperdepartmentgreaterthangreaterthan

$2900$2900

EMPEMP

50005000

30003000

28502850

DEPTNO SAL

--------- ---------

10 2450

10 5000

10 1300

20 800

20 1100

20 3000

20 3000

20 2975

30 1600

30 2850

30 125030 950

30 1500

30 1250

DEPTNO MAX(SAL)

--------- ---------

10 5000

20 3000

Using the HAVING Clause


99/238

g

Use the HAVING clause to restrict groups

Only the Groups matching theHAVING clause are displayed.

SELECT column, group_functionFROM table[WHERE condition]

[GROUP BY group_by_expression][HAVING group_condition][ORDER BY column];



100/238

g

SQL> SELECT deptno, max(sal)2 FROM emp3 GROUP BY deptno4 HAVING max(sal)>2900;

DEPTNO MAX(SAL)--------- ---------

10 500020 3000



101/238

SQL> SELECT job, SUM(sal) PAYROLL2 FROM emp3 WHERE job NOT LIKE 'SALES%'4 GROUP BY job5 HAVING SUM(sal)>50006 ORDER BY SUM(sal);

JOB PAYROLL--------- --------- ANALYST 6000 MANAGER 8275

Nesting Group Functions


102/238

SQL> SELECT max(avg(sal))2 FROM emp

3 GROUP BY deptno;

MAX(AVG(SAL))-------------

2916.6667

Displaythemaximumaveragesalary.

Objectives


103/238

Describe the types of problems that

subqueries can solve

Define subqueries

List the types of subqueries

Write single-row , multiple-row and

multiple column subqueries

Using a Subquery to Solve a Problem


104/238

WhohasasalarygreaterthanJoness?

WhichemployeeshaveasalarygreaterthanJonesssalary?

Main Query

??

WhatisJonesssalary???

Subquery

Subqueries


105/238

The subquery (inner query) executes

once before the main query.

The result of the subquery is used by the

main query (outer query).

SELECT select_listFROM tableWHERE expr operator

(SELECT select_listFROM table);

Using a Subquery


106/238

2975SQL> SELECT ename2 FROM emp3 WHERE sal >4 (SELECT sal5 FROM emp6 WHERE empno=7566);

ENAME----------KINGFORDSCOTT

Guidelines for Using Subqueries


107/238

Enclose subqueries in parentheses.

Place subqueries on the right side of the

comparison operator.

Do not add an ORDER BY clause to a

subquery.

Use single-row operators with single-row

subqueries.

Use multiple-row operators with multiple-

row subqueries.

Types of Subqueries


108/238

Single-row subqueryMainquery

Subqueryreturnsreturns

CLERKCLERK

Multiple-row subquery

CLERKCLERKMANAGERMANAGER

Mainquery


Multiple-column subquery

CLERK 7900CLERK 7900MANAGER 7698MANAGER 7698

Mainquery


Single-Row Subqueries


109/238

Return only one row

Use single-row comparison operators

Operator

=

>

>=

SELECT ename, job2 FROM emp3 WHERE job =4 (SELECT job5 FROM emp6 WHERE empno = 7369)

7 AND sal >8 (SELECT sal9 FROM emp10 WHERE empno = 7876);

Using Group Functions in a Subquery


111/238

800

ENAME JOB SAL---------- --------- ---------SMITH CLERK 800

SQL> SELECT ename, job, sal2 FROM emp3 WHERE sal =4 (SELECT MIN(sal)5 FROM emp);

HAVING Clause with Subqueries


112/238

The Oracle Server executes subqueries

first.

800

SQL> SELECT deptno, MIN(sal)2 FROM emp3 GROUP BY deptno4 HAVING MIN(sal) > 5 (SELECT MIN(sal)6 FROM emp

7 WHERE deptno = 20);

What Is Wrong with This Statement?


113/238

ERROR:ORA-01427: single-row subquery returns more thanone row

no rows selected

SQL> SELECT empno, ename2 FROM emp3 WHERE sal =4 (SELECT MIN(sal)5 FROM emp

6 GROUP BY deptno);

Will This Statement Work?


114/238

no rows selected

SQL> SELECT ename, job2 FROM emp3 WHERE job =4 (SELECT job5 FROM emp6 WHERE ename='SMYTHE');

Multiple-Row Subqueries


115/238

Return more than one row

Use multiple-row comparison operators

Operator

IN

ANY

ALL

Meaning

Equal toanymemberinthe list

Comparevaluetoeachvaluereturnedbythe

subquery

Comparevaluetoeveryvaluereturnedbythe

subquery

Using ANY Operator in Multiple-RowS b i


116/238

Subqueries

9508001100

1300

EMPNO ENAME JOB--------- ---------- ---------

7654 MARTIN SALESMAN7521 WARD SALESMAN

SQL> SELECT empno, ename, job

2 FROM emp3 WHERE sal < ANY4 (SELECT sal5 FROM emp6 WHERE job = 'CLERK')7 AND job 'CLERK';

Using ALL Operator in Multiple-RowS b i


117/238

Subqueries

2916.6667

2175

1566.6667

EMPNO ENAME JOB--------- ---------- ---------

7839 KING PRESIDENT7566 JONES MANAGER7902 FORD ANALYST7788 SCOTT ANALYST

SQL> SELECT empno, ename, job2 FROM emp3 WHERE sal > ALL4 (SELECT avg(sal)5 FROM emp6 GROUP BY deptno);

Multiple-Column Subqueries


118/238

MainqueryMANAGER10

Subquery

SALESMAN 30

MANAGER 10

CLERK 20

MainqueryMainquerycomparescompares

MANAGER10MANAGER10

ValuesfromamultipleValuesfromamultiple--rowandrowandmultiplemultiple--columnsubquerycolumnsubquery

SALESMANSALESMAN3030

MANAGERMANAGER 1010

CLERKCLERK 2020

toto

Using Multiple-Column Subqueries


119/238

Displaythename,departmentnumber,salary,

andcommissionofanyemployeewhosesalaryandcommissionmatchesboth the

commissionandsalaryofanyemployeein

department30.

SQL> SELECT ename, deptno, sal, comm2 FROM emp3 WHERE (sal, NVL(comm,-1)) IN4 (SELECT sal, NVL(comm,-1)5 FROM emp

6 WHERE deptno = 30);

Using a Subquery in the FROMClause


120/238

Clause

ENAME SAL DEPTNO SALAVG---------- --------- --------- ----------KING 5000 10 2916.6667JONES 2975 20 2175SCOTT 3000 20 2175...6 rows selected.

SQL> SELECT a.ename, a.sal, a.deptno, b.salavg2 FROM emp a, (SELECT deptno, avg(sal) salavg3 FROM emp4 GROUP BY deptno) b5 WHERE a.deptno = b.deptno6 AND a.sal > b.salavg;

Objectives


121/238

Insert rows into a table

Update rows in a table

Delete rows from a table

Controlling the Transactions

Data Manipulation Language


122/238

A DML statement is executed when you:

Add new rows to a table

Modify existing rows in a table

Remove existing rows from atableA transaction consists of a

collection of DML statements that

form a logical unit of work.

The INSERT Statement


123/238

Add new rows to a table by using the

INSERT statement.

INSERT INTO table [(column [, column...])]VALUES (value [, value...]);

Inserting New Rows


124/238

Insert a new row containing values foreach column.

List values in the default order of thecolumns in the table.

Optionally list the columns in theINSERT clause.

Enclose character and date valueswithin single quotation marks.

SQL> INSERT INTO dept (deptno, dname, loc)

2 VALUES (50, 'DEVELOPMENT', 'DETROIT');1 row created.1 row created.

Inserting Rows with Null Values


125/238

Implicit method: Oit the column from the

column list.

SQL> INSERT INTO dept (deptno, dname )2 VALUES (60, 'MIS');

1 row created.1 row created.

Explicit method: Specify the NULL keyword.

SQL> INSERT INTO dept2 VALUES (70, 'FINANCE', NULL);1 row created.1 row created.

Inserting Special Values

Th SYSDATE d USER f ti d th


126/238

The SYSDATE andUSERfunctionrecordsthe

currentdateandtime.

SQL> INSERT INTO emp (empno, ename, job,

2 mgr, hiredate, sal, comm,3 deptno)4 VALUES (7196, USER, 'SALESMAN',5 7782, SYSDATE, 2000, NULL,6 10);


Inserting Specific Date Values

Add a ne emplo ee


127/238

Addanewemployee.

SQL> INSERT INTO emp

2 VALUES (2296,'AROMANO','SALESMAN',7782,

3 TO_DATE('FEB 3,97', 'MON DD, YY'),

4 1300, NULL, 10);


Verify your addition.

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ------- -------- ---- --------- ---- ---- -----

2296 AROMANO SALESMAN 7782 03-FEB-97 1300 10

Inserting Values by Using Substitution (&) Variables

C t i t ti i t b i SQL*Pl


128/238

Createaninteractivescriptby using SQL*Plussubstitutionparameters.

SQL> INSERT INTO dept (deptno, dname, loc)2 VALUES (&department_id,3 '&department_name', '&location');

Enter value for department_id: 8080Enter value for department_name: EDUCATIONEDUCATIONEnter value for location:ATLANTAATLANTA

1 row created.

Creating a Script with Customized Prompts


129/238

ACCEPT stores the value into a

variable.PROMPT displays your customized text.

ACCEPT department_id PROMPT 'Please enter the -

department number:'

ACCEPT department_name PROMPT 'Please enter -

the department name:'

ACCEPT location PROMPT 'Please enter the -

location:'

INSERT INTO dept (deptno, dname, loc)

VALUES (&department_id, '&department_name',

'&location');

Copying Rows from AnotherTable


130/238

WriteyourINSERTstatementwithasub-query.

SQL> INSERT INTO managers(id, name, salary, hiredate)

2 SELECT empno, ename, sal, hiredate3 FROM emp4 WHERE job = 'MANAGER';

3 rows created.3 rows created.

Do not use the VALUES clause.Match the number of columns in the INSERT clause tothose in the subquery.

The UPDATE Statement


131/238

Modifyexistingrowswiththe

UPDATEstatement.

UPDATE table

SET column = value [, column = value][WHERE condition];

Update more than one row at a time, if required.

Updating Rows in a Table


132/238

All rowsinthetablearemodifiedifyouomittheWHEE

clause.

SQL> UPDATE employee2 SET deptno = 20;

14 rows updated.14 rows updated.

Updating Rows: Integrity ConstraintError


133/238

UPDATE emp*

ERROR at line 1:ORA-02291: integrity constraint (USR.EMP_DEPTNO_FK)violated - parent key not found

SQL> UPDATE emp2 SET deptno = 553 WHERE deptno = 10;

Error

The DELETE Statement


134/238

You canremoveexistingrowsfromatableby

usingtheDELETEstatement.

DELETE [FROM] table[WHERE condition];


135/238

Deleting Rows: Integrity ConstraintError


136/238

Error

SQL> DELETE FROM dept2 WHERE deptno = 10;

DELETE FROM dept*ERROR at line 1:ORA-02292: integrity constraint (USR.EMP_DEPTNO_FK)violated - child record found

Database Transactions


137/238

Consist of one of the following statements:

DML statements that make up one

consistent change to the data

One DDL statement

One DCL statement

Database Transactions


138/238

Begin when the first executable SQL

statement is executed

End with one of the following events:

COMMIT

or ROLLBACK DDL or DCL statement

executes (automatic commit)

User exits

System crashes

Controlling Transactions


139/238

DELETEDELETE

Transaction

SavepointASavepointA

ROLLBACKto Savepoint BROLLBACKto Savepoint B

DELETEDELETE

SavepointBSavepointBCOMMITCOMMIT

INSERTINSERTUPDATEUPDATE

ROLLBACKto SavepointAROLLBACKto SavepointA

INSERTINSERTUPDATEUPDATEINSERTINSERT

ROLLBACKROLLBACK

INSERTINSERT

State of the Data Before COMMIT or ROLLBACK

The previous state of the data can be


140/238

The previous state of the data can be

recovered. The current user can review the results of the

DML operations by using the SELECT

statement.

Other users cannotview 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


141/238

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 save points are erased.

Committing Data


142/238

SQL> UPDATE emp2 SET deptno = 103 WHERE empno = 7782;

1 row updated.1 row updated.

Makethechanges.

Commit the changes.

SQL> COMMIT;

Commit complete.Commit complete.

State of the Data After ROLLBACK

Di d ll di h b i h


143/238

Discard all pending changes by using the

ROLLBACK statement.Data changes are undone.

Previous state of the data is restored.

Locks on the affected rows are

released.

SQL> DELETE FROM employee;14 rows deleted.14 rows deleted.SQL> ROLLBACK;Rollback complete.Rollback complete.

Rolling Back Changes to a Marker


144/238

Create a marker within a currenttransaction by using the SAVEPOINT

statement.

Roll back to that marker by using the

ROLLBACK TO SAVEPOINT statement.SQL> UPDATE...SQL> SAVEPOINT update_done;Savepoint created.Savepoint created.SQL> INSERT...

SQL> ROLLBACK TO update_done;Rollback complete.Rollback complete.

Statement-Level Rollback


145/238

If a single DML statement fails during

execution, only that statement is rolled

back.

Oracle Server implements an implicit

savepoint.

All other changes are retained.

The user should terminate transactions

explicitly by executing a COMMIT or

ROLLBACK statement.

Read Consistency

R d i t t


146/238

Read consistency guarantees a

consistent view of the data at all times.

Changes made by one user do not

conflict with changes made by another

user.

Ensures that on the same data:

Readers do not wait for writers

Writers do not wait for readers

Objectives


147/238

Describe the main database objects

Create tables

Describe the datatypes that can be used

when specifying column definitionAlter table definitions

Drop, rename, and truncate tables

Database Objects


148/238

Object Description

Table Basic unitofstorage;composedofrows

andcolumns

View Logicallyrepresentssubsetsofdatafrom

oneormoretables

Sequence Generatesprimarykeyvalues

Index Improvestheperformanceofsomequeries

Synonym Givesalternativenamestoobjects

Naming Conventions


149/238

Must begin with a letter

Can be 130 characters long

Must contain only AZ, az, 09, _, $,

and #Must not duplicate the name of another

object owned by the same user

Must not be an Oracle Server reserved

word

The CREATE TABLE Statement

You must have :


150/238

You must have :

CREATE TABLE privilege A storage area

You specify:

Table name Column name, column datatype, and

column size

CREATE TABLE [schema.]table(column datatype [DEFAULT expr];

Referencing Another Users Tables


151/238

Tables belonging to other users are not

in the users schema.

You should use the owners name as aprefix to the table.

The DEFAULT Option

Specify a default value for a column


152/238

Specify a default value for a column

during an insert.

hiredate DATE DEFAULT SYSDATE,

Legal values are literal value, expression, or SQL function.

Illegal values are another columns name or pseudocolumn.

The default datatype must match the column datatype.

Creating Tables

Create the table.


153/238

SQL> CREATE TABLE dept2 (deptno NUMBER(2),3 dname VARCHAR2(14),4 loc VARCHAR2(13));

Table created.Table created.

Confirmtablecreation.

SQL> DESCRIBE dept

Name Null? Type--------------------------- -------- ---------

DEPTNO NOT NULL NUMBER(2)DNAME VARCHAR2(14)LOC VARCHAR2(13)

Querying the Data Dictionary

Describe tables owned by the user


154/238

Describe tables owned by the user.

Viewdistinctobjecttypesownedbythe user.

Viewtables,views,synonyms,andsequencesownedbythe user.

SQL> SELECT *2 FROM user_tables;

SQL> SELECT DISTINCT object_type2 FROM user_objects;

SQL> SELECT *2 FROM user_catalog;

Datatypes


155/238

Datatype Description

VARCHAR2(size) Variable-lengthcharacterdata

CHAR(size) Fixed-lengthcharacterdata

NUMBER(p,s) Variable-lengthnumericdata

DATE Dateandtimevalues

LONG Variable-lengthcharacterdataupto2gigabytes

CLOB Single-bytecharacterdata upto4gigabytes

RAWand LONGRAW Rawbinarydata

BLOB Binarydata upto4gigabytes

BFILE Binarydatastoredinanexternalfile; upto4gigabytes

Creating a Table Using a Subquery

C


156/238

Create a table and insert rows by combining

the CREATETABLE statement and AS subqueryoption.CREATE TABLE table

[column(, column...)]AS subquery;

Creating a Table Using a Subquery


157/238

SQL> CREATE TABLE dept302 AS3 SELECT empno,ename,sal*12 ANNSAL,hiredate4 FROM emp5 WHERE deptno = 30;

Table created.Table created.

Name Null? Type---------------------------- -------- -----

EMPNO NOT NULL NUMBER(4)ENAME VARCHAR2(10) ANNSAL HIREDATE DATE

SQL> DESCRIBE dept30

The ALTER TABLE Statement

Use the ALTER TABLE statement to:


158/238

Add a new column

Modify an existing column

Define a default value for the newcolumn

ALTER TABLE tableADD (column datatype [DEFAULT expr]

[, column datatype]...);

ALTER TABLE table

MODIFY (column datatype [DEFAULT expr][, column datatype]...);

Adding a Column

dd dd


159/238

DEPT30DEPT30

EMPNO ENAME ANNSAL HIREDATE------ ---------- --------7698 BLAKE 34200 01-MAY-817654 MARTIN 15000 28-SEP-817499 ALLEN 19200 20-FEB-817844 TURNER 18000 08-SEP-81

...

addaadda

newnewcolumncolumnintointoDEPT30DEPT30tabletable

DEPT30DEPT30

EMPNO ENAME ANNSAL HIREDATE------ ---------- --------7698 BLAKE 34200 01-MAY-81

7654 MARTIN 15000 28-SEP-817499 ALLEN 19200 20-FEB-817844 TURNER 18000 08-SEP-81

...

JOB

JOB

NewcolumnNewcolumn

Adding a Column

You use the ADD clause to add columns


160/238

You use the ADD clause to add columns.

EMPNO ENAME ANNSAL HIREDATE JOB--------- ---------- --------- --------- ----

7698 BLAKE 34200 01-MAY-817654 MARTIN 15000 28-SEP-81

7499 ALLEN 19200 20-FEB-817844 TURNER 18000 08-SEP-81

...6 rows selected.

SQL> ALTER TABLE dept302 ADD (job VARCHAR2(9));

Table altered.Table altered.

Thenewcolumnbecomesthe lastcolumn.

Modifying a Column


161/238

You can change a column's datatype,

size, and default value.

A change to the default value affects onlysubsequent insertions to the table.

ALTER TABLE dept30MODIFY (ename VARCHAR2(15));


Dropping a Table

All data and structure in the table is deleted


162/238

All data and structure in the table is deleted.

Any pending transactions are committed.

All indexes are dropped.

You cannotroll back this statement.

SQL> DROP TABLE dept30;Table dropped.Table dropped.

Changing the Name of an Object

To change the name of a table view


163/238

To change the name of a table, view,

sequence, or synonym, you executethe RENAME statement.

You must be the owner of the object.

SQL> RENAME dept TO department;Table renamed.Table renamed.

Truncating a Table

The TRUNCATE TABLE statement:


164/238

The TRUNCATE TABLE statement:

Removes all rows from a table Releases the storage space used

by that table

Cannot roll back row removal whenusing TRUNCATE

Alternatively, remove rows by using theDELETE statement

SQL> TRUNCATE TABLE department;Table truncated.Table truncated.

What Are Constraints?

Constraints enforce rules at the table


165/238

level.Constraints prevent the deletion ofa table if there are dependencies.

The following constraint types are valid

in Oracle:

NOT NULL

UNIQUE Key

PRIMARY KEY

FOREIGN KEY

CHECK

Constraint Guidelines

Name a constraint or the Oracle Server


166/238

will generate a name by using theSYS_Cn format.

Create a constraint:

At the same time as the table iscreated

After the table has been created

Define a constraint at the column ortable level.

View a constraint in the data dictionary.

Defining Constraints


167/238

CREATE TABLE [schema.]table(column datatype [DEFAULT expr][column_constraint],

[table_constraint]);

CREATE TABLE emp(empno NUMBER(4),ename VARCHAR2(10),

deptno NUMBER(7,2) NOT NULL,

CONSTRAINT emp_empno_pkPRIMARY KEY (EMPNO));


168/238

The NOT NULL Constraint

Ensuresthatnull valuesarenotpermittedfor the column


169/238

forthecolumn

EMPEMP

EMPNO ENAME JOB ... COMM DEPTNO

7839 KING PRESIDENT 107698 BLAKE MANAGER 307782 CLARK MANAGER 107566 JONES MANAGER 20...

NOTNULL constraintNOTNULL constraint(norowmaycontain(norowmaycontainanull valueforanull valueforthiscolumn)thiscolumn)

AbsenceofNOTNULLAbsenceofNOTNULLconstraintconstraint(anyrowcancontain(anyrowcancontainnull forthiscolumn)null forthiscolumn)

NOTNULL constraintNOTNULL constraint


170/238


171/238

The UNIQUE Key Constraint


172/238

Definedateitherthetable level orthecolumnlevel

SQL> CREATE TABLE dept(2 deptno NUMBER(2),3 dname VARCHAR2(14),4 loc VARCHAR2(13),5 CONSTRAINT dept_dname_uk UNIQUE(dname));

The PRIMARY KEY Constraint


173/238

DEPTDEPT

DEPTNO DNAME LOC------ ---------- --------


PRIMARY KEYPRIMARY KEY

InsertintoInsertinto

20 MARKETING DALLAS

FINANCE NEW YORK

NotallowedNotallowed(DEPTNO(DEPTNO20already20alreadyexists)exists)

NotallowedNotallowed(DEPTNOisnull)(DEPTNOisnull)

The PRIMARY KEY Constraint


174/238

Definedateitherthetable level orthecolumnlevel

SQL> CREATE TABLE dept(2 deptno NUMBER(2),

3 dname VARCHAR2(14),4 loc VARCHAR2(13),5 CONSTRAINT dept_dname_uk UNIQUE (dname),6 CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));

The FOREIGN KEY Constraint

DEPTDEPT


175/238

DEPTDEPT

DEPTNO DNAME LOC------ ---------- --------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS

...

PRIMARYPRIMARYKEYKEY

EMPEMP

EMPNO ENAME JOB ... COMM DEPTNO

7839 KING PRESIDENT 107698 BLAKE MANAGER 30...

FOREIGNFOREIGNKEYKEY

7571 FORD MANAGER ... 200 97571 FORD MANAGER ... 200

InsertintoInsertinto

NotallowedNotallowed(DEPTNO(DEPTNO99

doesnotexistdoesnotexistintheDEPTintheDEPTtabletableAllowedAllowed

The FOREIGN KEY Constraint

Definedateitherthetable level orthe


176/238

column level

SQL> CREATE TABLE emp(2 empno NUMBER(4),3 ename VARCHAR2(10) NOT NULL,4 job VARCHAR2(9),5 mgr NUMBER(4),6 hiredate DATE,7 sal NUMBER(7,2),8 comm NUMBER(7,2),9 deptno NUMBER(7,2) NOT NULL,10 CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)11 REFERENCES dept (deptno));

FOREIGN KEY Constraint Keywords

FOREIGN KEY


177/238

Defines the column in the child table at

the table constraint level

REFERENCES

Identifies the table and column in the parent

table

ON DELETE CASCADE

Allows deletion in the parent table and deletion

of the dependent rows in the child table

The CHECK Constraint

Defines a condition that each row must satisfy


178/238

Expressions that are not allowed:

References to pseudocolumns CURRVAL,NEXTVAL, LEVEL, and ROWNUM

Calls to SYSDATE, UID, USER, and

USERENV functions

Queries that refer to other values in otherrows

..., deptno NUMBER(2),CONSTRAINT emp_deptno_ck

CHECK (DEPTNO BETWEEN 10 AND 99),...

Adding a Constraint


179/238

Add or drop, but not modify, a constraint

Enable or disable constraintsAdd a NOT NULL constraint by using the

MODIFY clause

ALTER TABLE tableADD [CONSTRAINT constraint] type (column);

Adding a Constraint


180/238

Adda FOREIGNKEY constrainttotheEMPtableindicatingthatamanagermustalreadyexistasavalidemployeeintheEMPtable.

SQL> ALTER TABLE emp2 ADD CONSTRAINT emp_mgr_fk

3 FOREIGN KEY(mgr) REFERENCES emp(empno);Table altered.Table altered.

Dropping a Constraint

Remove the manager constraint fromthe EMP table


181/238

the EMP table.

SQL> ALTER TABLE emp2 DROP CONSTRAINT emp_mgr_fk;


RemovethePRIMARY KEY constraintontheDEPTtableanddroptheassociated FOREIGNKEY constraintontheEMP.DEPTNOcolumn.

SQL> ALTER TABLE dept2 DROP PRIMARY KEY CASCADE;


Disabling Constraints

Execute the DISABLE clause of the


182/238

ALTER TABLE statement to deactivatean integrity constraint.

Apply the CASCADE option to disabledependent integrity constraints.

SQL> ALTER TABLE emp2 DISABLE CONSTRAINT emp_empno_pk CASCADE;


Enabling Constraints

Activate an integrity constraint currentlydisabled in the table definition by using the


183/238

disabled in the table definition by using the

ENABLE clause.

A UNIQUE or PRIMARY KEY index isautomatically created if you enable aUNIQUE key or PRIMARY KEY constraint.

SQL> ALTER TABLE emp2 ENABLE CONSTRAINT emp_empno_pk;


Viewing Constraints


184/238

QuerytheUSER_CONSTRAINTS tabletoview

all constraintdefinitionsandnames.

CONSTRAINT_NAME C SEARCH_CONDITION------------------------ - -------------------------SYS_C00674 C EMPNO IS NOT NULL

SYS_C00675 C DEPTNO IS NOT NULLEMP_EMPNO_PK P...

SQL> SELECT constraint_name, constraint_type,2 search_condition

3 FROM user_constraints4 WHERE table_name = 'EMP';

Viewing the Columns Associated with Constraints


185/238

CONSTRAINT_NAME COLUMN_NAME------------------------- ----------------------EMP_DEPTNO_FK DEPTNO

EMP_EMPNO_PK EMPNOEMP_MGR_FK MGRSYS_C00674 EMPNOSYS_C00675 DEPTNO

SQL> SELECT constraint_name, column_name

2 FROM user_cons_columns3 WHERE table_name = 'EMP';

Viewthecolumnsassociatedwiththeconstraint

namesintheUSER_CONS_COLUMNS view


186/238

Database Objects


187/238

Description

Basic unitofstorage;composedofrows

andcolumns

Logicallyrepresentssubsetsofdatafrom

oneormoretables

Generatesprimarykeyvalues

Improvestheperformanceofsomequeries

Alternativenameforanobject

Object

Table

View

Sequence

Index

Synonym

Why Use Views?

To restrict database access


188/238

To make complex queries easy

To allow data independence

To present different views of the same

data

Simple Views and Complex Views


189/238

Feature Simple View Complex View

Number of tables One One or More

Contain Functions No Yes

Contain Groupsof data

No Yes

DML via View Yes Not Always

Creating a View

You embed a subquery within the CREATE

VIEW statement


190/238

VIEW statement.

The subquery can contain complex SELECT

syntax.

The subquery cannot contain an ORDER BY

clause.

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEWview[(alias[, alias]...)]

AS subquery[WITH CHECK OPTION [CONSTRAINT constraint]][WITH READ ONLY]

Creating a View

Create a view, EMPVU10, that containsdetails of employees in department 10.


191/238

details of employees in department 10.

Describethestructureoftheviewby usingthe SQL*PlusDESCRIBEcommand.

SQL> DESCRIBE empvu10

SQL> CREATE VIEW empvu102 AS SELECT empno, ename, job3 FROM emp4 WHERE deptno = 10;

View created.View created.

Creating a View

Create a view by using column


192/238

aliases in the subquery.

Select the columns from this view by

the given alias names.

SQL> CREATE VIEW salvu302 AS SELECT empno EMPLOYEE_NUMBER, ename NAME,3 sal SALARY

4 FROM emp5 WHERE deptno = 30;View created.View created.

Retrieving Data from a View


193/238

EMPLOYEE_NUMBER NAME SALARY--------------- ---------- ---------

7698 BLAKE 2850

7654 MARTIN 12507499 ALLEN 16007844 TURNER 15007900 JAMES 9507521 WARD 1250

6 rows selected.

SQL> SELECT *2 FROM salvu30;

Querying a View


194/238

USER_VIEWSUSER_VIEWSEMPVU10EMPVU10

SELECT empno,ename, jobFROM empWHERE deptno=10;

USER_VIEWSUSER_VIEWSEMPVU10EMPVU10

SELECT empno,ename, jobFROM empWHERE deptno=10;

SQL*PlusSQL*Plus

SELECT *FROM empvu10;

EMP

7839 KING PRESIDENT7782 CLARK MANAGER7934 MILLER CLERK

Modifying a View Modify the EMPVU10 view by using CREATE OR

REPLACE VIEW clause. Add an alias for each column


195/238

name.

Column aliases in the CREATE VIEW clause are listed

in the same order as the columns in the subquery.

SQL> CREATE OR REPLACE VIEW empvu102 (employee_number, employee_name, job_title)3 AS SELECT empno, ename, job4 FROM emp5 WHERE deptno = 10;


Creating a Complex View

Create a comple ie that contains gro p


196/238

Createacomplexviewthatcontainsgroupfunctionstodisplayvaluesfromtwotables.

SQL> CREATE VIEW dept_sum_vu2 (name, minsal, maxsal, avgsal)3 AS SELECT d.dname, MIN(e.sal), MAX(e.sal),

4 AVG(e.sal)5 FROM emp e, dept d 6 WHERE e.deptno = d.deptno7 GROUP BY d.dname;


Rules for Performing DML Operations on a View

You can perform DML operations on


197/238

simple views.

You cannot remove a row if the view

contains the following:

Group functions

A GROUP BY clause

The DISTINCT keyword

Rules for Performing DML Operations on a View

You cannot modify data in a view if it contains:

An of the conditions mentioned in the


198/238

Any of the conditions mentioned in the

previous slide

Columns defined by expressions

The ROWNUM pseudocolumn

You cannot add data if: The view contains any of the conditions

mentioned above or in the previous slide

There are NOT NULL columns in the base

tables that are not selected by the view

Using the WITH CHECK OPTION Clause

You can ensure that DML on the viewstays


199/238

y

within the domain of the view by usingthe WITH CHECK OPTION.

Any attempt to change the department number for any row

in the view will fail because it violates the WITH CHECKOPTION constraint.

SQL> CREATE OR REPLACE VIEW empvu202 AS SELECT *

3 FROM emp4 WHERE deptno = 205 WITH CHECK OPTION CONSTRAINT empvu20_ck;


Denying DML Operations

You can ensure that no DMLoperations occur by adding the WITH


200/238

operations occur by adding the WITHREAD ONLY option to your viewdefinition.

SQL> CREATE OR REPLACE VIEW empvu102 (employee_number, employee_name, job_title)

3 AS SELECT empno, ename, job4 FROM emp5 WHERE deptno = 106 WITH READ ONLY;


Any attempt to perform a DML on any row in the viewwill result in Oracle Server error ORA-01752.

Removing a View

Removeaviewwithout losingdatabecausea


201/238

viewisbasedon underlyingtablesinthedatabase.

SQL> DROP VIEW empvu10;View dropped.View dropped.

DROP VIEWview;

Objectives


202/238

Describe some database objects andtheir uses

Create, maintain, and use sequences

Create and maintain indexes

Create private and public synonyms

Database Objects


203/238

Description

Basic unitofstorage;composedofrows

andcolumns

Logicallyrepresentssubsetsofdatafrom

oneormoretables

Generatesprimarykeyvalues

Improvestheperformanceofsomequeries

Alternativenameforanobject

Object

Table

View

Sequence

Index

Synonym

What Is a Sequence?

Automatically generates unique


204/238

numbers

Is a sharable object

Is typically used to create a primary

key value

Replaces application code

Speeds up the efficiency of accessing

sequence values when cached in

memory

The CREATE SEQUENCE Statement

Defineasequencetogeneratesequentialnumbers automatically


205/238

numbersautomatically

CREATE SEQUENCE sequence[INCREMENT BY n][START WITH n][{MAXVALUE n | NOMAXVALUE}][{MINVALUE n | NOMINVALUE}][{CYCLE | NOCYCLE}][{CACHE n | NOCACHE}];

Creating a Sequence

Create a sequence namedDEPT DEPTNO to be used for the


206/238

_

primary key of theDEPT table.

Do not use the CYCLE option.

SQL> CREATE SEQUENCE dept_deptno2 INCREMENT BY 13 START WITH 914 MAXVALUE 1005 NOCACHE6 NOCYCLE;

Sequence created.Sequence created.

Confirming Sequences

Verify your sequence values in theUSER SEQUENCES data dictionary


207/238

USER_SEQUENCES data dictionarytable.

T

he LAST_NUMBER column displaysthe next available sequence number.

SQL> SELECT sequence_name, min_value, max_value,

2 increment_by, last_number3 FROM user_sequences;

NEXTVAL and CURRVALPseudocolumns

NEXTVAL returns the next available


208/238

sequence value.

It returns a unique value every time it

is referenced, even for different users.

CURRVAL obtains the current

sequence value.

NEXTVAL must be issued for that

sequence before CURRVAL contains a

value.

Using a Sequence

Insert a new department named


209/238

MARKETING in San Diego.

View the current value for theDEPT_DEPTNO sequence.

SQL> INSERT INTO dept(deptno, dname, loc)2 VALUES (dept_deptno.NEXTVAL,3 'MARKETING', 'SAN DIEGO');


SQL> SELECT dept_deptno.CURRVAL2 FROM dual;

Using a Sequence

Caching sequence values in memory allows

faster access to those values.


210/238

faster access to those values.

Gaps in sequence values can occur when:

A rollback occurs

The system crashes

A sequence is used in another table View the next available sequence, if it was

created with NOCACHE, by querying the

USER_SEQUENCES table.

Modifying a Sequence

Changetheincrementvalue,maximum

value,minimumvalue,cycleoption,orcache


211/238

option.

SQL> ALTER SEQUENCE dept_deptno

2 INCREMENT BY 13 MAXVALUE 9999994 NOCACHE5 NOCYCLE;

Sequence altered.Sequence altered.

Removing a Sequence

Remove a sequence from the data


212/238

dictionary by using the DROPSEQUENCE statement.

Once removed, the sequence can no

longer be referenced.

SQL> DROP SEQUENCE dept_deptno;Sequence dropped.Sequence dropped.

What Is an Index?

Schemaobject


213/238

UsedbytheOracle Servertospeeduptheretrieval ofrowsby usingapointer

ReducesdiskI/Oby usingrapidpath

accessmethodto locatethedataquickly

Independentofthetableitindexes

Automatically usedandmaintainedbytheOracle Server

How Are Indexes Created?

Automatically


214/238

A uniqueindexiscreatedautomaticallywhenyou defineaPRIMARY KEY orUNIQUEkeyconstraintinatabledefinition.

Manually

Userscancreatenonunique

indexesoncolumnstospeedupaccesstimetotherows.

Creating an Index

Create an index on one or more columns


215/238

Improve the speed of query access on

the ENAME column in the EMP table

SQL> CREATE INDEX emp_ename_idx2 ON emp(ename);

Index created.Index created.

CREATE INDEX indexON table (column[, column]...);

Create an index on one or more columns

Confirming Indexes

The USER INDEXES data dictionary view


216/238

TheUSER_INDEXES datadictionaryview

containsthenameoftheindexandits

uniqueness.

TheUSER_IND_COLUMNS viewcontainstheindexname,thetablename,andthe

columnname.SQL> SELECT ic.index_name, ic.column_name,2 ic.column_position col_pos,ix.uniqueness3 FROM user_indexes ix, user_ind_columns ic

4 WHERE ic.index_name = ix.index_name5 AND ic.table_name = 'EMP';

Removing an Index

Removeanindexfromthedatadictionary.


217/238

y

RemovetheEMP_ENAME_IDXindexfrom

thedatadictionary.

Todropanindex,you mustbetheowneroftheindexorhavetheDROPANY INDEXprivilege.

SQL> DROP INDEX emp_ename_idx;Index dropped.Index dropped.

SQL> DROP INDEX index;

Synonyms

Simplifyaccesstoobjectsbycreatinga

synonym(anothernameforanobject).


218/238

Refertoatableownedbyanother

user.

Shorten lengthyobjectnames.

CREATE [PUBLIC] SYNONYMsynonymFOR object;

Creating and Removing Synonyms

Create a shortened name for theDEPT_SUM_VU view.


219/238

SQL> CREATE SYNONYM d_sum2 FOR dept_sum_vu;

Synonym Created.Synonym Created.

SQL> DROP SYNONYM d_sum;

Synonym dropped.Synonym dropped.

Drop a synonym.


220/238

DCL Statements

Objectives

Create users


221/238

Create users

Create roles to ease setup and

maintenance of the security model

GRANT

and REVOKE object privileges

Controlling User Access


222/238

DatabaseDatabaseadministratoradministrator

UsersUsers

Usernameandpasswordprivileges

Privileges

Database security


223/238

System security

Data securitySystem privileges: Gain access to the

databaseObject privileges: Manipulate the content of

the database objects

Schema: Collection of objects, such as

tables, views, and sequences

System Privileges

More than 80 privileges are available.


224/238

The DBA has high-level systemprivileges.

Create new users

Remove users

Remove tables

Backup tables

Creating Users

TheDBAcreates usersby usingthe CREATE


225/238

USERstatement.

SQL> CREATE USER scott2 IDENTIFIED BY tiger;

User created.User created.

CREATE USER userIDENTIFIED BY password;

User System Privileges

Once a user is created, the DBA can grant specific systemprivileges to a user.


226/238

GRANTprivilege [,privilege...]TO user[, user...];

An application developer may have the following systemprivileges:

CREATE SESSION

CREATE TABLE

CREATE SEQUENCE

CREATE VIEW

CREATE PROCEDURE

Granting System Privileges

TheDBAcangranta userspecificsystemprivileges.


227/238

p g

SQL> GRANT create table, create sequence, create view2 TO scott;

Grant succeeded.Grant succeeded.

What Is a Role?


228/238

AllocatingprivilegesAllocatingprivileges

withoutarolewithoutarole

AllocatingprivilegesAllocatingprivileges

witharolewitharole

PrivilegesPrivileges

UsersUsers

ManagerManager

Creating and Granting Privileges to a Role


229/238

SQL> CREATE ROLE manager;Role created.Role created.

SQL> GRANT create table, create view

2 to manager;Grant succeeded.Grant succeeded.

SQL> GRANT manager to BLAKE, CLARK;


Changing Your Password

Whenthe useraccountiscreated,a


230/238

passwordisinitialized.

Userscanchangetheirpasswordby

usingtheALTERUSERstatement.

SQL> ALTER USER scott2 IDENTIFIED BY lion;

User altered.User altered.

ObjectPrivilege Table View Sequence Procedure

Object Privileges


231/238

ALTER

DELETE

EXECUTE

INDEX

INSERT

REFERENCES

SELECT

UPDATE


232/238

Granting Object Privileges

GrantqueryprivilegesontheEMPtable.


233/238

SQL> GRANT select2 ON emp3 TO sue, rich;


SQL> GRANT update (dname, loc)2 ON dept3 TO scott, manager;


Grant privileges to update specific columns to users and roles.

Using WITH GRANT OPTION and PUBLIC Keywords

Give a user authority to pass along the privileges.


234/238

Allowall usersonthesystemtoquerydatafromAlicesDEPTtable.

SQL> GRANT select, insert2 ON dept3 TO scott4 WITH GRANT OPTION;


SQL> GRANT select2 ON alice.dept

3 TO PUBLIC;Grant succeeded.Grant succeeded.

Confirming Privileges Granted

DataDictionaryTable Description


235/238

ROLE_SYS_PRIVS Systemprivilegesgrantedtoroles

ROLE_TAB_PRIVS Tableprivilegesgrantedtoroles

USER_ROLE_PRIVS Rolesaccessiblebythe user

USER_TAB_PRIVS_MADE Objectprivilegesgrantedonthe

user'sobjectsUSER_TAB_PRIVS_RECD Objectprivilegesgrantedtothe

user

USER_COL_PRIVS_MADE Objectprivilegesgrantedonthecolumnsofthe user'sobjects

USER_COL_PRIVS_RECD Objectprivilegesgrantedtotheuseronspecificcolumns

How to Revoke Object Privileges

You use the REVOKE statement to


236/238

revoke privileges granted to otherusers.

Privileges granted to others through

the WIT

H GRANT

OPT

ION will alsobe revoked.

REVOKE {privilege [, privilege...]|ALL}ON objectFROM {user[, user...]|role|PUBLIC}

[CASCADE CONSTRAINTS];

Revoking Object Privileges

As userAlice,revokethe SELECTand

INSERTprivilegesgivento userScottonthe


237/238

DEPTtable.

SQL> REVOKE select, insert2 ON dept3 FROM scott;

Revoke succeeded.Revoke succeeded.


238/238

Thank you

presentation on oracle sql

Documents