9i sql slide
TRANSCRIPT
-
8/8/2019 9i SQL Slide
1/141
-
8/8/2019 9i SQL Slide
2/141
22-Dec-10 2:51 PM 2 Copyright Argam, 2006. All rights reserved.
SQL
COALESCE
CASE enhanced JOINS syntax FULL OUTER JOIN FLASHBACK AS OF DEFAULT keyword INSERT Multi-table Direct Path
MERGE Date Data types
TIMESTAMP,TIMESTAMP WITH TIME ZONE,TIMESTAMP WITH LOCAL TIME ZONE
DATETIME functionsTZ_OFFSET, CURRENT_DATE,LOCALTIMESTAMP
Multi-Table InsertUn-conditional, Conditional ALL, FIRST
Create Index with create table statement
Partitioned Outer Join
MEDIAN Function FLASH BACK VERSIONS MODEL clause
Functions CV() ,PRESENTV(),PRESENTNNV(),
ITERATION_NUMBER MERGE DELETE clause
-
8/8/2019 9i SQL Slide
3/141
22-Dec-10 2:51 PM 3 Copyright Argam, 2006. All rights reserved.
SQL
Introduction
Basic SELECT statements
Restricting and Sorting Data
Single Row Function
Displaying Data from Multiple Tables
Aggregating Data using Group Functions
Sub-Queries
Manipulating DataCreating and Managing Tables
Constraints
Views
Sequence Index Synonym
Controlling User Access
Using SET operators
Enhancement to the GROUP BY Clause
Advanced Sub-queries
Hierarchical Retrieval
Extensions to DML
-
8/8/2019 9i SQL Slide
4/141
22-Dec-10 2:51 PM 4 Copyright Argam, 2006. All rights reserved.
SQL
Introduction
-
8/8/2019 9i SQL Slide
5/141
22-Dec-10 2:51 PM 5 Copyright Argam, 2006. All rights reserved.
SQL
Database
Every Organization had some information needs.
Organizations can store pieces of information calledDATA. Data stored in different formats, hard copy document, electronic spread
sheet or in database.
A database is an organized collection of information.
DBMS a program that stores retrieves and modifies data in the databaseon request.
Types of Database Hierarchical, Network, Relational, Object Relational.
-
8/8/2019 9i SQL Slide
6/141
22-Dec-10 2:51 PM 6 Copyright Argam, 2006. All rights reserved.
SQL
History of SQL
1970s, Dr. E. F. Codd an IBM researcher Codd's work led tothe definition of the relational data model and a languagecalled DSL/Alpha for manipulating data in a relationaldatabase.
IBM built a project called System/R based on Codd's work.
System/R developed a simplified version of DSL calledSQUARE, which was later renamed SEQUEL, and finallyrenamed SQL.
American National Standards Institute (ANSI), released itsfirst SQL standard in 1986, followed by updates in 1989,1992, and 1999.
-
8/8/2019 9i SQL Slide
7/141
22-Dec-10 2:51 PM 7 Copyright Argam, 2006. All rights reserved.
SQL
Relational Database Concept
Dr. EF. Codd proposed the relational model for database systems in 1970,A Relational Model of data for Large Shared Data Banks.
It is the basis for RDBMS
The Relational model consists of the following :
- Collection of objects or relations.- Set of operators to act on the relations.- Data Integrity for accuracy and consistency.
Definition :A relational database is a collection of relations ortwo-dimensional tables (rows & columns).
-
8/8/2019 9i SQL Slide
8/141
22-Dec-10 2:51 PM 8 Copyright Argam, 2006. All rights reserved.
SQL
Entity Relationship Model
Entity / Table : A thing of significance about which information needs to beknown.
e.g. Employees, Departments, Students, and Orders.
Attribute / Column : Something that qualifies an entity. eg. eno, ename, ordno.,
Relationship : Association between entities showing cardinality / degree.
EMPLOYEES
numbername
job title
DEPARTMENTS
numbername
location
Scenario:- assign one or more employees to a department- some departments do not yet have assigned employees
-
8/8/2019 9i SQL Slide
9/141
22-Dec-10 2:51 PM 9 Copyright Argam, 2006. All rights reserved.
SQL
Entity Relationship Modeling Conventions
Entity - TableSoft boxSingular, unique nameUppercaseSynonym in parentheses
EMPLOYEE
# * number* nameo job title
DEPARTMENT
# * number* nameo location
assigned to
composed of
Attribute - ColumnSingular nameLowercaseMandatory marked with *Optional marked with o
Unique Identifier UIDPrimary marked with #Secondary marked with (#)
RelationshipsDashed Line Optional may beSolid line Mandatory must beCrows foot Degree element one or moreSingle line one and only one
-
8/8/2019 9i SQL Slide
10/141
22-Dec-10 2:51 PM 10 Copyright Argam, 2006. All rights reserved.
SQL
Relational Database Terminology
ENO ENAME SALARY COMM_PCT DNO
100 King 24000 90
101 Kochhar 17000 90
102 Rey 6000 60
103 DeCarl 3500 50
104 Abel 11000 .3 80
105 Grant 7000 .15 80106 Reyarndo 8300 110
Row / Record
Column Non-key valuePrimary Key
null
Foreign Key
field
-
8/8/2019 9i SQL Slide
11/141
22-Dec-10 2:51 PM 11 Copyright Argam, 2006. All rights reserved.
SQL
Data Models
Model of systemin clients mind
Entity model ofclients model
Table model ofentity model
Tables on disk
Oracle Server
-
8/8/2019 9i SQL Slide
12/141
22-Dec-10 2:51 PM 12 Copyright Argam, 2006. All rights reserved.
SQL
ORDBMS - Object and Relational
DataBase Management System
Relational model and Object Relational model.
User-defined data types and objects. Fully compatible with relational database.
High-quality database server features.
-
8/8/2019 9i SQL Slide
13/141
22-Dec-10 2:51 PM 13 Copyright Argam, 2006. All rights reserved.
SQL
Oracle ProductSQL, PL/SQL, Developer, Forms ...
Architecture Oracle 8i
Non-Oracle ProductJava, XML, HTML ...
Components
Database
Data Dictionary
-
8/8/2019 9i SQL Slide
14/141
-
8/8/2019 9i SQL Slide
15/141
22-Dec-10 2:51 PM 15 Copyright Argam, 2006. All rights reserved.
SQL
X
Server Architecture
Database
Dedicated Server
A
Database
Shared Server
B C
-
8/8/2019 9i SQL Slide
16/141
22-Dec-10 2:51 PM 16 Copyright Argam, 2006. All rights reserved.
SQL
Communicating with a RDBMS using SQL
SQL Statementis entered
SELECT last_nameFROM employees;
Oracle Server
LAST_NAME
King
Kochhar
Reynardo
DeCarl
Abel
Grant
Rey
Statement is sentto Oracle Server
-
8/8/2019 9i SQL Slide
17/141
22-Dec-10 2:51 PM 17 Copyright Argam, 2006. All rights reserved.
SQL
SQL StatementsData Retrieval
SELECT
Data Manipulation Language (DML)
CALL DELETE EXPLAIN PLAN
INSERT LOCK TABLE MERGE
UPDATE
Data
Definition Language (
DDL)CREATE ALTER DROP RENAME TRUNCATE
Transaction Control Language (TCL)
COMMIT ROLLBACK SAVEPOINT SET TRANSACTION
Data Control Language (DCL)
GRANT REVOKE
Session Control Statements System Control Statements
ALTER SESSION ALTER SYSTEM
-
8/8/2019 9i SQL Slide
18/141
22-Dec-10 2:51 PM 18 Copyright Argam, 2006. All rights reserved.
SQL
Improved performance and functionality ofOn-Line Transaction Processing OLTP
Operating within the Network Computing Architecture (NCA)frame work, supportsClient-Server (TCP-IP) andWeb-based applications (HTTP)
10,000 concurrent users, support upto 512 Peta-bytes of data
( 1 Peta-byte is 1,000 Tera-bytes)
Support of Multimedia and Large OBjects
-
8/8/2019 9i SQL Slide
19/141
22-Dec-10 2:51 PM 19 Copyright Argam, 2006. All rights reserved.
SQL
Summary
Relational databases are composed of relations, managed byrelational operations, and governed by data integrity
constraints.
The Oracle is the database for Internet Computing andbased on the ORDBMS.
Oracle Server supports ANSI standard SQL and containsextensions.
SQL is the language used to communicate with the server toaccess, manipulate, and control data.
-
8/8/2019 9i SQL Slide
20/141
22-Dec-10 2:51 PM 20 Copyright Argam, 2006. All rights reserved.
SQL
Writing Basic SQLSELECT Statements
-
8/8/2019 9i SQL Slide
21/141
22-Dec-10 2:51 PM 21 Copyright Argam, 2006. All rights reserved.
SQL
Capabilities of SQLSELECT Statements
Projection Selection
Joins
Table A Table B
Table A Table B
-
8/8/2019 9i SQL Slide
22/141
22-Dec-10 2:51 PM 22 Copyright Argam, 2006. All rights reserved.
SQL
Basic SELECT Statement
SELECT *|{[DISTINCT] column|expression [ alias],}FROM table;
SELECT identifies what column(s)
FROM identifies which table
SELECT *FROM departments;
Selecting All Columns
-
8/8/2019 9i SQL Slide
23/141
22-Dec-10 2:51 PM 23 Copyright Argam, 2006. All rights reserved.
SQL
SELECT department_id, location_idFROM departments;
Selecting Specific Columns
SQL Statements are not case sensitive.
SQL statements can be on one or more lines.
Key words cannot be abbreviated or split across lines.
Clauses are usually placed on separate lines.
Writing SQL Statements
-
8/8/2019 9i SQL Slide
24/141
22-Dec-10 2:51 PM 24 Copyright Argam, 2006. All rights reserved.
SQL
Arithmetic Expressions
Create expressions with number and date data by using arithmeticoperators.
SELECT last_name, salary, salary+300FROM employees;
SELECT last_name,salary,12*salary+300,12+salary*300FROM employees;
Operator Precedence
Override the rule by usingParentheses
12*(salary+300)
-
8/8/2019 9i SQL Slide
25/141
22-Dec-10 2:51 PM 25 Copyright Argam, 2006. All rights reserved.
SQL
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.
Arithmetic expressions containing a null value evaluate to null.
ENO ENAME SALARY COMM DNO
100 King 24000 .3 1.3 90
101 Kochhar 17000 90
102 Ernst 6000 60
103 Rajs 3500 50
104 Abel 11000 .5 1.5 80
105 Grant 7000 .15 1.15 40
106 Gietz 8300 110
COMM+1
-
8/8/2019 9i SQL Slide
26/141
22-Dec-10 2:51 PM 26 Copyright Argam, 2006. All rights reserved.
SQL
SELECT last_nameAS name, commission_pct comm
FROM employees;
SELECT last_name AS Name, salary*12 Annual SalaryFROM employees;
Defining a Column Alias
Renames a column heading.
Is useful with calculations.
Immediately follows the column name,AS keyword can beoptional between the column name and alias.
Requires double quotation marks if it contains spaces orspecial characters or is case sensitive.
-
8/8/2019 9i SQL Slide
27/141
22-Dec-10 2:51 PM 27 Copyright Argam, 2006. All rights reserved.
SQL
Concatenation Operator
SELECT last_name||job_id AS Employees
FROM employees;
Concatenates columns or character strings to other columns.
Is represented by two vertical bars ||
SELECT last_name|| is a ||job_id AS EmployeesFROM employees;
Date and character literal values must be enclosed withinsingle quotation marks.
Each character string is output once for each row returned.
-
8/8/2019 9i SQL Slide
28/141
22-Dec-10 2:51 PM 28 Copyright Argam, 2006. All rights reserved.
SQL
Duplicate Rows
SELECT department_idFROM employees;
The default display of queries is all rows, including duplicate rows
SELECT DISTINCT department_idFROM employees;
Eliminate duplicate rows by using the DISTINCT or UNIQUE
keyword in the SELECT clause.
Display Table StructureDESC[RIBE] tablename;
DESCRIBE employees; DESC employees;
You cannot specifyDISTINCT if the
select_list containsLOB columns.
-
8/8/2019 9i SQL Slide
29/141
22-Dec-10 2:51 PM 29 Copyright Argam, 2006. All rights reserved.
SQL
Summary
SELECT statement
- Returns all rows and columns from a table
- Returns specified columns from a table
- Uses column aliases to give descriptive column headings
To write, save, execute the SQL statements
use the i*SQLPlus environment, SQL WorkSheet.
SELECT *|{[DISTINCT] column|expression [ alias],}FROM table;
-
8/8/2019 9i SQL Slide
30/141
22-Dec-10 2:51 PM 30 Copyright Argam, 2006. All rights reserved.
SQL
Restricting and Sorting Data
-
8/8/2019 9i SQL Slide
31/141
22-Dec-10 2:51 PM 31 Copyright Argam, 2006. All rights reserved.
SQL
Limiting Rows using a Selection
retrieve all employees in department 90 .
SELECT *|{[DISTINCT] column|expression [ alias],}FROM table[ WHERE condition(s) ];
TheWHERE clause follows the FROMclause
SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id = 90;
-
8/8/2019 9i SQL Slide
32/141
22-Dec-10 2:51 PM 32 Copyright Argam, 2006. All rights reserved.
SQL
Character Strings and Dates
Character strings and date values are enclosed in singlequotation marks.
Character values are case sensitive, andDate values are format sensitive.
The default date format is DD-MON-RR.
SELECT employee_id, last_name, department_id
FROM employeesWHERE last_name = Adam;
-
8/8/2019 9i SQL Slide
33/141
22-Dec-10 2:51 PM 33 Copyright Argam, 2006. All rights reserved.
SQL
Comparison Conditions
Used in conditions that compare one expression to anothervalue or expression.
SELECT employee_id, last_name, department_id
FROM employees WHERE last_name = Adam;
WHERE hire_date = 01-Jan-95 ;
WHERE salary >= 6000 ;WHERE department_id 80 ; != ^=
-
8/8/2019 9i SQL Slide
34/141
22-Dec-10 2:51 PM 34 Copyright Argam, 2006. All rights reserved.
SQL
BETWEEN Condition
Use the BETWEEN condition to display rows based on arange of values.
SELECT employee_id, last_name, salaryFROM employees
WHERE salaryBETWEEN 2500 AND 3500 ;
Lower limit Upper limit
IN Condition
Use the IN membership condition to test for values in a list.
SELECT employee_id, last_name, salary, manager_idFROM employees
WHERE manager_id IN (100, 101, 201) ;
-
8/8/2019 9i SQL Slide
35/141
22-Dec-10 2:51 PM 35 Copyright Argam, 2006. All rights reserved.
SQL
LIKE Condition
LIKE condition to perform wildcard searches of valid searchstring values.
Can contain either literal character or numbers
% denotes zero or many characters._ denotes any single character.
SELECT employee_id, last_nameFROM employees
WHERE last_name LIKE S%;
Use theES
C
APEidentifier to search for the
actual % and _ symbols
SELECT employee_id, last_name, job_idFROM employees
WHERE job_id LIKE SA\_% ESCAPE \;
-
8/8/2019 9i SQL Slide
36/141
22-Dec-10 2:51 PM 36 Copyright Argam, 2006. All rights reserved.
SQL
Using theNULL Conditions
Test for nulls with the IS NULL operator.
SELECT employee_id, last_name, manager_idFROM employees
WHERE manager_id IS NULL;
Logical Conditions
AND Returns TRUE ifboth component conditions are true
OR Returns TRUE ifeithercomponent condition are true
NOT Returns TRUE if the following condition is false
-
8/8/2019 9i SQL Slide
37/141
-
8/8/2019 9i SQL Slide
38/141
22-Dec-10 2:51 PM 38 Copyright Argam, 2006. All rights reserved.
SQL
Using theNOT Operator
SELECT employee_id, last_name, job_id
FROM employees
WHERE job_id NOT IN (IT_PROG, ST_CLERK);
Rules of Precedence
1 Arithmetic operators2 Concatenation operators3 Comparison Conditions4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN6 NOT logical condition7 AND logical condition8 ORlogical condition
Override rules of precedence by using parentheses
-
8/8/2019 9i SQL Slide
39/141
22-Dec-10 2:51 PM 39 Copyright Argam, 2006. All rights reserved.
SQL
SELECT last_name, job_id, salaryFROM employees WHERE job_id = SA_REPOR job_id = AD_PRES
AND salary > 15000;
Rule of Precedence
SELECT last_name, job_id, salaryFROM employees
WHERE (job_id = SA_REPOR job_id = AD_PRES) AND salary > 15000;
Select the row if an employee is AD_PRES and earns more than15000, or if the employee is a SA_REP
Select the row if an employee is AD_PRES or SA_REP , and if theemployee earns more than 15000.
-
8/8/2019 9i SQL Slide
40/141
22-Dec-10 2:51 PM 40 Copyright Argam, 2006. All rights reserved.
SQL
ORDERBY Clause
Sort rows with the ORDERBY clause
-ASC ascending order, default- DESC descending order
The ORDERBY clause comes last in the SELECT statement
SELECT employee_id, last_name, job_id, salaryFROM employees
ORDERBY hire_date;
ORDERBY salary DESC ;
-
8/8/2019 9i SQL Slide
41/141
22-Dec-10 2:51 PM 41 Copyright Argam, 2006. All rights reserved.
SQL
Sorting by Column Alias
SELECT employee_id, last_name, salary*12 annsal
FROM employeesORDERBY annsal ;
Sorting by Multiple Columns
SELECT last_name, department_id, salary
FROM employeesORDERBY department_id, salary DESC ;
You can sort by a column that is not in the SELECT list
Sorting by Column position
SELECT employee_id, last_name, salary*12 annsal
FROM employeesORDERBY 3 ;
SQL
-
8/8/2019 9i SQL Slide
42/141
22-Dec-10 2:51 PM 42 Copyright Argam, 2006. All rights reserved.
SQL
Summary UseWHERE clause to restrict rows of output
- Use the comparison conditions
- Use the BETWEEN, IN, LIKE, and NULL conditions
- Apply the logical AND, OR, and NOT operators
Use the ORDERBY clause to sort rows of output
SELEC
T *|{[DISTINC
T] column|expression [ alias],}FROM table
[ WHERE condition(s) ]
[ORDERBY {column, expr, alias} [ASC|DESC]];
SQL
-
8/8/2019 9i SQL Slide
43/141
22-Dec-10 2:51 PM 43 Copyright Argam, 2006. All rights reserved.
SQL
Single-Row Functions
SQL
-
8/8/2019 9i SQL Slide
44/141
22-Dec-10 2:51 PM 44 Copyright Argam, 2006. All rights reserved.
SQL
SQL Functions
Function
Function returnsa valuearg 1
arg 2arg n
Input
Output
Result value
Function
Single-rowfunctions
Multiple-rowfunctions
Input Output Input Output
SQL
-
8/8/2019 9i SQL Slide
45/141
22-Dec-10 2:51 PM 45 Copyright Argam, 2006. All rights reserved.
SQL
Single-Row Functions
Manipulate data items
Accepts arguments and returns one value
Act on each row returned
Return one result per row
Displays the input/output requested data format
Can be nested
Accept arguments which can be a column or an expression
function_name [(arg1, arg2,)];
SQL
-
8/8/2019 9i SQL Slide
46/141
22-Dec-10 2:51 PM 46 Copyright Argam, 2006. All rights reserved.
SQL
Character Functions
Case-manipulation functions
LOWER(Hello Bangalore) hello bangaloreUPPER(Hello Bangalore) HELLO BANGALOREINITCAP(HeLlo BangAlore) Hello Bangalore
SELECT employee_id, last_name, department_id
FROM employeesWHERE lower(last_name) = abel;
SQL
-
8/8/2019 9i SQL Slide
47/141
22-Dec-10 2:51 PM 47 Copyright Argam, 2006. All rights reserved.
SQL
Character - manipulation functions
CONCAT(Hello, World) HelloWorld
SUBSTR(HelloWorld,1,5) Hello
LENGTH(HelloWorld) 10
INSTR(HelloWorld,o) 5
LPAD(salary,10,*) *****24000
RPAD(salary,8,#) 9900####
WHERE SOUNDEX(city) = SOUNDEX(Sidny)
RTRIM(AMERIC
A, A) AMERIC
LTRIM(AMERICA, A) MERI CA
TRIM(H FROM HelloWorld) elloWorldTRIM(A FROM AMERICA) MERIC
Character Functions
SQL
-
8/8/2019 9i SQL Slide
48/141
22-Dec-10 2:51 PM 48 Copyright Argam, 2006. All rights reserved.
SQL
ABS(-6) 6ABS(6) 6
CEIL(1.4) 2
FLOOR(1.6) 1
SIGN(-25) -1SIGN(25) 1
SQRT(36) 6
VSIZE(city) The storage size in bytes
For any datatype :GREATEST(10,45,100,1) 100
LEAST(10,45,100,1) 1
Number Functions
SQL
-
8/8/2019 9i SQL Slide
49/141
22-Dec-10 2:51 PM 49 Copyright Argam, 2006. All rights reserved.
SQL
Number Functions
ROUND :Rounds value to specified decimalSELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROM DUAL;
45.92 46 50
DUAL is a dummy table you can use to view results from functions and calculations
TRUNC :Truncates value to specified decimal
45.92 45 0
SELECT TRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-2)FROM DUAL;
MOD :Returns remainder of division
SELECT MOD(10,3), MOD(10,7)FROM DUAL; 1 3
SQL
-
8/8/2019 9i SQL Slide
50/141
22-Dec-10 2:51 PM 50 Copyright Argam, 2006. All rights reserved.
SQL
Working with Dates
Oracle database stores Dates in an internal numeric format :Century, Year, Month, Day, Hours, Minutes, SecondsEg. 15-Jan-2005 2:00 PM
C Y M D H M S20 05 01 15 14 00 15
Default Display format DD-MON-RR.
Valid Oracle Dates betweenJanuary 1, 4712 BC and December 31, 9999 AD
SYSDATE function returns current database serverDate and Time
SELECT SYSDATEFROM DUAL;
SQL
-
8/8/2019 9i SQL Slide
51/141
22-Dec-10 2:51 PM 51 Copyright Argam, 2006. All rights reserved.
SQL
Arithmetic with Dates
Date + Number Date Adds a number of days
Date Number Date Subtracts a number of days
Date Date no. of days Subtracts one date from
another
Date + Number/24 Date Adds a number of hours toa date
SELECT last_name, (SYSDATE-hire_date)/7 as WEEKSFROM employees
WHERE department-id = 90;
SQL
-
8/8/2019 9i SQL Slide
52/141
22-Dec-10 2:51 PM 52 Copyright Argam, 2006. All rights reserved.
SQL
Date functions
MONTHS_B
ETWEEN(15-NOV-05,01-SEP-05) -> 1.5 ADD_MONTHS(11-JAN-95,6) -> 11-JUL-9
NEXT_DAY(01-JUN-05,FRIDAY) -> 03-JUN-05
LAST_DAY(18-FEB-08) -> 29-FEB-08
Assume SYSDATE = 25-JUL- 05;
ROUND(SYSDATE, MONTH) -> 01-Aug-05
ROUND(SYSDATE, YEAR) -> 01-Jan-06
TRUNC(SYSDATE, MONTH) -> 01-Jul-05
TRUNC(SYSDATE, YEAR) -> 01-Jan-05
-
8/8/2019 9i SQL Slide
53/141
SQL
-
8/8/2019 9i SQL Slide
54/141
22-Dec-10 2:51 PM 54 Copyright Argam, 2006. All rights reserved.
SQL
Elements of the Date Format ModelYYYY Full year in number
YEAR Year spelled out
MM Two-digit value of month
MONTH Full name of the month
MON Three-letter abbreviation of the monthDY Three-letter abbreviation of the day of the week
DAY Full name of the day of the week
DD Numeric day of the monthWW W Week of year or month
RM Roman Number
JJulian day; the number of days since 31Dec 4713 BC
SQL
-
8/8/2019 9i SQL Slide
55/141
22-Dec-10 2:51 PM 55 Copyright Argam, 2006. All rights reserved.
SQL
Elements of the Date Format Model
SELECT to_char(sysdate,DD-Mon-YYYY HH24:MI:SS AM)FROM dual;
DD of MONTH 4 of OctoberDDth 4thDDsp FOUR Ddspth Fourth
SELECT last_name,to_char(hire_date,fmDD Mon YYYY) Hiredate
FROM employees;
-
8/8/2019 9i SQL Slide
56/141
SQL
-
8/8/2019 9i SQL Slide
57/141
22-Dec-10 2:51 PM 57 Copyright Argam, 2006. All rights reserved.
Q
General Functions
These function work with any data type and pertain to using nulls.
Converts a null to an actual value- Data type must match
NVL(commission_pct,0)NVL(hire_date,01-Jan-05)NVL(job_id,No Job Yet)
NVL function
NVL2(expr1, expr2, expr3)- examine the expr1, if expr1 is null returns expr3,
if expr1 is not null returns expr2
NVL2(commission_pct,SAL+COMM, SAL)
NVL2 function
SQL
-
8/8/2019 9i SQL Slide
58/141
22-Dec-10 2:51 PM 58 Copyright Argam, 2006. All rights reserved.
Q
NULLIF function
NULLIF(expr1, expr2)
- expr1 compares to expr2, if both are equal returns nullif both are not equal expr1 is
returned
NULLIF(length(first_name), length(last_name))
COALESCE function
COALESCE(expr1, expr2, expr3, .. exprn)
- returns the first non null expression
COALESCE(first_name, last_name, No Name)
NVL(first_name, (NVL(last_name,No Name)))
-
8/8/2019 9i SQL Slide
59/141
SQL
-
8/8/2019 9i SQL Slide
60/141
22-Dec-10 2:51 PM 60 Copyright Argam, 2006. All rights reserved.
RR Date Format
Current Year Specified date RR Format YY Format . 1995 27-Oct-95 1995 1995 . 1995 27-Oct-17 2017 1917 . 2001 27-Oct-17 2017 2017 . 2001 27-Oct-95 1995 2095
If the specifiedtwo-digit year is0-49 50-99
If two digitsof thecurrentyearare
0-49 the return date is The return date is in thein the current century century before the
current one
50-99 the return date is The return date is in thein the century after current century the current one
SQL
-
8/8/2019 9i SQL Slide
61/141
22-Dec-10 2:51 PM 61 Copyright Argam, 2006. All rights reserved.
Example ofRRDate Format
To find employees hired prior to 1990, use the RRformat, which produces the same results whether thecommand is run in 1999 or now:
SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY')FROM employees
WHERE hire_date < TO_DATE('01-Jan-90', 'DD-Mon-RR');
The following command results in no rows being selected
because the YY format interprets the year portion of the date in
the current century (2090).SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-yyyy')FROM employeesWHERE TO_DATE(hire_date, 'DD-Mon-yy') < '01-Jan-90';
no rows selected
SQL
-
8/8/2019 9i SQL Slide
62/141
22-Dec-10 2:51 PM 62 Copyright Argam, 2006. All rights reserved.
Summary
Perform calculations on data using functions
Modify individual data items using functions
Manipulate output for groups of rows using functions
Alter date format for display using functions
Use NVL, NVL2, NULLIF, COALESCE
Use IF-THEN-ELSE logic DECODE - CASE
SQL
-
8/8/2019 9i SQL Slide
63/141
22-Dec-10 2:51 PM 63 Copyright Argam, 2006. All rights reserved.
Displaying data from Multiple Tables
-
8/8/2019 9i SQL Slide
64/141
SQL
-
8/8/2019 9i SQL Slide
65/141
22-Dec-10 2:51 PM 65 Copyright Argam, 2006. All rights reserved.
Natural / Inner Joins - EQUI
SELECT department_id, department_name,location_id, city
FROM departmentsNATURAL JOIN locations;
Joins for same Column name and Data type.
Returns two tables that have equal values in all matched columns.
If there is no common columns, it generates Cartesian Product.
SQL USING Clause
-
8/8/2019 9i SQL Slide
66/141
22-Dec-10 2:51 PM 66 Copyright Argam, 2006. All rights reserved.
SELECT e.employee_id, e.last_name, d.location_idFROM employees e JOIN departments dUSING (department_id);
USING Clause
Matches only one column when more than one column matches.
Do not use a table name or alias in the referenced columns
SELECT e.employee_id, e.last_name, d.location_idFROM employees e, departments d
WHERE e.department_id = d.department_id;
SELECT e.eno, e.ename, s.gradeFROM emp e, salgrade s
WHERE e.salBETWEEN s.losal AND s.hisal;
(Non-equi Join)
SQL
-
8/8/2019 9i SQL Slide
67/141
22-Dec-10 2:51 PM 67 Copyright Argam, 2006. All rights reserved.
SELECT e.employee_id, e.last_name, e.manager_id,
m.last_nameFROM employees e JOIN employees m
ON (e.manager_id = m.employee_id);
ON Clause (Self join) The join condition for the natural join is equi-join,
Use this clause to specify a join condition.
SELECT e.employee_id, e.last_name, e.manager_id,m.last_name
FROM employees e , employees m
WHERE e.manager_id = m.employee_id ;
Findthe nameofthe Managerforeachemployee.
SQL
-
8/8/2019 9i SQL Slide
68/141
22-Dec-10 2:51 PM 68 Copyright Argam, 2006. All rights reserved.
Creating Three-way joins with the ON Clause
SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;
SELECT employee_id, city, department_nameFROM employees e, departments d, locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id;
To join N tables, minimum N-1join condition required
SQL
-
8/8/2019 9i SQL Slide
69/141
22-Dec-10 2:51 PM 69 Copyright Argam, 2006. All rights reserved.
LEFT OUTER JOIN
SELECT e.last_name,e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);
SELECT e.last_name,e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+) ;
This retrieves all rows in the Employees, which is the left tableeven if there is no match in the departments table.
SQL
-
8/8/2019 9i SQL Slide
70/141
22-Dec-10 2:51 PM 70 Copyright Argam, 2006. All rights reserved.
RIGHT OUTER JOIN
SELECT e.last_name,e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);
SELECT e.last_name,e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;
This retrieves all rows in the Departments, which is the righttable even if there is no match in the Employees table.
SQL
-
8/8/2019 9i SQL Slide
71/141
22-Dec-10 2:51 PM 71 Copyright Argam, 2006. All rights reserved.
FULL OUTER JOIN
SELECT e.last_name,e.department_id, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);
This retrieves all rows in the Employees , even if there is nomatch in the Departments table.
It also retrieves all rows in the Departments table, even if thereis no match in the Employees table.
SQL
-
8/8/2019 9i SQL Slide
72/141
22-Dec-10 2:51 PM 72 Copyright Argam, 2006. All rights reserved.
CROSS JOIN
NATURAL JOIN
USING
ON
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
Summary
SQL
-
8/8/2019 9i SQL Slide
73/141
22-Dec-10 2:51 PM 73 Copyright Argam, 2006. All rights reserved.
Aggregating Data
Using Group Functions
SQL
-
8/8/2019 9i SQL Slide
74/141
22-Dec-10 2:51 PM 74 Copyright Argam, 2006. All rights reserved.
What are Group Functions ?
Group functions operates on set of rows to giveone result per group.
SELECT AVG(salary), SUM(salary),
MIN(salary), MAX(salary)
FROM employees;
Themaximum salaryin the Employeestable
SELECT MIN(hire_date), MAX(hire_date),
MIN(last_name), MAX(last_name)
FROM employees;
Multiple-rowfunctions
Input Output
SQL
-
8/8/2019 9i SQL Slide
75/141
22-Dec-10 2:51 PM 75 Copyright Argam, 2006. All rights reserved.
Using the COUNT Function COUNT(*) returns the number of rows in a table
SELECT COUNT(*)
FROM employees;
COUNT(expr) returns the number of rows with non-null valuesfor the expr
SELEC
TC
OUNT(commission_pct)FROM employees
WHERE department_id = 80;
SQL
-
8/8/2019 9i SQL Slide
76/141
22-Dec-10 2:51 PM 76 Copyright Argam, 2006. All rights reserved.
Using the DISTINCT Keyword
COUNT(DISTINCT expr)returns the number of distinct non-null values of the expr
SELECT COUNT(DISTINCT department_id)
FROM employees;
Group Functions and Null Values
Group functions ignore null values in the column
SELECT AVG(commission_pct)
FROM employees;
SQL
-
8/8/2019 9i SQL Slide
77/141
22-Dec-10 2:51 PM 77 Copyright Argam, 2006. All rights reserved.
Using NVL Function with Group Functions
The NVL function forces group functions to include null values
SELECT AVG(NVL(commission_pct,0))
FROM employees;
SQL
-
8/8/2019 9i SQL Slide
78/141
22-Dec-10 2:51 PM 78 Copyright Argam, 2006. All rights reserved.
Creating Groups of Data
Divide rows in table into smaller groups by using
GROUP BY Clause
Theaverage salaryin the Employeestableforeach department
SELECT *|{[DISTIN
CT] column|expression [ alias],}
FROM table
[ WHERE condition(s) ]
[ GROUP BY group_by_expression ]
[ORDERBY {column, expr, alias} [ASC|DESC]];
SQL
-
8/8/2019 9i SQL Slide
79/141
22-Dec-10 2:51 PM 79 Copyright Argam, 2006. All rights reserved.
Using GROUP BY Clause
All column(s) in the SELEC
T list that are not group functionsmustbe in the GROUP BY clause.
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
The GROUP BY column(s) does nothave to be in the SELECT list
SELECT AVG(salary)
FROM employees
GROUP BY department_id;
SQL
-
8/8/2019 9i SQL Slide
80/141
22-Dec-10 2:51 PM 80 Copyright Argam, 2006. All rights reserved.
Using the GROUP BY Clause on Multiple Columns
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id;
Excluding Group Results
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;
Themaximum salaryperdepartmentwhereitis greaterthan 10000
SQL
-
8/8/2019 9i SQL Slide
81/141
22-Dec-10 2:51 PM 81 Copyright Argam, 2006. All rights reserved.
Nested Group Functions
SELECT MAX(AVG(SALARY))
FROM employees
GROUP BY department_id;
Displaythe maximum averagesalary
Group Function can be nestedto a depthofTwo
SQL
-
8/8/2019 9i SQL Slide
82/141
22-Dec-10 2:51 PM 82 Copyright Argam, 2006. All rights reserved.
Summary
Use the group functions
Write queries that use the GROUP BY clause
Write queries that use the HAVING clause
SELECT *|{[DISTINCT] column|expression [ alias],}FROM table[ WHERE condition(s) ][ GROUP BY group_by_expression ][ HAVING group_condition ][ORDERBY {column, expr, alias} [ASC|DESC]];
SQL
-
8/8/2019 9i SQL Slide
83/141
22-Dec-10 2:51 PM 83 Copyright Argam, 2006. All rights reserved.
Sub-Queries
SQL
-
8/8/2019 9i SQL Slide
84/141
22-Dec-10 2:51 PM 84 Copyright Argam, 2006. All rights reserved.
Using a Subquery to Solve a Problem
Which employees have salaries greater than Abels salary ?
You need two queries :First, find what Abel earns, and
Second, find who earns more than that amount.
Combine two queries, placing one query inside the other query.i.e. Query within a query.
What is the Abels Salary
2
1
SQL
-
8/8/2019 9i SQL Slide
85/141
22-Dec-10 2:51 PM 85 Copyright Argam, 2006. All rights reserved.
Sub-querySELECT select_listFROM table
WHERE expr operator ( SELECT select_listFROM table );
The subquery (inner query) executes once before the main query.
The result of the subquery is used by the main query(outer query).
SELECT last_name
FROM employeesWHERE salary > ( SELECT salary FROM employees
WHERE last_name =Abel );
SQL
-
8/8/2019 9i SQL Slide
86/141
22-Dec-10 2:51 PM 86 Copyright Argam, 2006. All rights reserved.
Types of Subqueries
Single-row subquery
Multiple-row subquery
ST_CLERK
ST_CLERKSA_MAN
SELECT select_listFROM table
WHERE expr operator ( SELECT select_listFROM table );
-
8/8/2019 9i SQL Slide
87/141
SQL
-
8/8/2019 9i SQL Slide
88/141
22-Dec-10 2:51 PM 88 Copyright Argam, 2006. All rights reserved.
Using Group Functions in a Subquery
SELECT last_name, job_id, salaryFROM employees
WHERE salary = ( SELECT MIN(salary) FROM employees);
The HAVING clause with Subqueries
SELECT department_id, MIN(salary)FROM employees
GROUPB
Y department_idHAVING MIN(salary) > ( SELECT MIN(salary)FROM employees
WHERE department_id = 50);
SQL
Will this statement Return Rows ?
-
8/8/2019 9i SQL Slide
89/141
22-Dec-10 2:51 PM 89 Copyright Argam, 2006. All rights reserved.
Null values in the Subquery
SELECT last_name, job_idFROM employees
WHERE job_id = ( SELECT job_id FROM employees WHERE last_name = Haas);
If subquery returns null values, main query returns null result
Will this statement Return Rows ?
SELECT last_name, job_id
FROM employeesWHERE job_id = ( SELECT job_id FROM employees
WHERE last_name = Xyz);
If subquery returns no rows, main query returns null result
SQL
What is Wrong in this Statement
-
8/8/2019 9i SQL Slide
90/141
22-Dec-10 2:51 PM 90 Copyright Argam, 2006. All rights reserved.
What is Wrong in this Statement
SELECT last_name, job_id
FROM employeesWHERE salary = ( SELECT MIN(salary) FROM employees
GROUP BY department_id);
Single row operator with multiple-row subquery
Using the IN Operator
SELECT last_name, salary, department_idFROM employees
WHERE salary IN ( SELECT MIN(salary) FROM employeesGROUP BY department_id);
Equal to any member in the list
SQL
U i th O t
-
8/8/2019 9i SQL Slide
91/141
22-Dec-10 2:51 PM 91 Copyright Argam, 2006. All rights reserved.
Using theANY Operator
SELECT last_name, salary, department_idFROM employees
WHERE salary < ANY ( SELECT salary FROM employeesWHERE job_id = IT_PROG);
Compare value to each values returned by the subquery
Using theALL Operator
SELECT last_name, salary, department_idFROM employees
WHERE salary < ALL ( SELECT salary FROM employeesWHERE job_id = IT_PROG);
Compare value to every values returned by the subquery
SQL
-
8/8/2019 9i SQL Slide
92/141
22-Dec-10 2:51 PM 92 Copyright Argam, 2006. All rights reserved.
Summary
Write subqueries when a query is based on unknown values
SELECT select_listFROM table
WHERE expr operator ( SELECT select_listFROM table );
SQL
-
8/8/2019 9i SQL Slide
93/141
22-Dec-10 2:51 PM 93 Copyright Argam, 2006. All rights reserved.
SQL*Plus Formating Elements
SQL
C l H di
-
8/8/2019 9i SQL Slide
94/141
22-Dec-10 2:51 PM 94 Copyright Argam, 2006. All rights reserved.
Column Headings
COLUMN employee_name HEADING Employee NameEmployee Name-------------
COLUMN employee_name HEADING Employee | NameEmployee
Name--------
COLUMN employee_name HEADING Employee | Name JUSTIFY RIGHTCOLUMN employee_name HEADING Employee | Name JUSTIFY CENTER
SET UNDERLINE @
SET LINESIZE 60 SET PAGESIZE 50 Page Width Page Length
SQL
-
8/8/2019 9i SQL Slide
95/141
22-Dec-10 2:51 PM 95 Copyright Argam, 2006. All rights reserved.
COLUMN department_name FORMAT A15 Heading DNAMECOLUMN department_id FORMAT 9999 Heading JOB
SELECT department_name,department_id
FROM departments d;
SQL
-
8/8/2019 9i SQL Slide
96/141
22-Dec-10 2:51 PM 96 Copyright Argam, 2006. All rights reserved.
Value Format Result123 9999 1231234.01 9,999.99 1,234.0123456 $999,999.99 $23,456.001 0999 00011 99099 001-1000.01 9,999.9mi 1,000.01-1001 S9,999 +1,001-1001 9,999PR
1001 9,999PR 1,001
SQL
Defining a Title
-
8/8/2019 9i SQL Slide
97/141
22-Dec-10 2:51 PM 97 Copyright Argam, 2006. All rights reserved.
Defining a Title
TTITLE LEFT My CompanyCENTER CurrentRIGHT Page FORMAT 999 SQL.PNO SKIP 1CENTER Employee Listing SKIP 4
My Company Current Page 1
Employee Listing
BTITLE
COLUMN curdate NEW_VALUE report_dateSELECT to_char(sysdate,ddmon-yyyy) curdate
FROM dual;
BTITLE LEFT Report Date : report_date
Getting the Date into a title
SQL.RELEASESQL.USER
SQL
Report Breaks
-
8/8/2019 9i SQL Slide
98/141
22-Dec-10 2:51 PM 98 Copyright Argam, 2006. All rights reserved.
BREAK ON owner
SELECT owner, table_nameFROM all_tablesORDERBY owner,table_name;
OWNER TABLE_NAME ---------------------- --------------------------CTYSYS DR$CLASS
DR$DELETEDR$INDEX
SCOTT CUSTOMER DEPARTMENTEMPLOYEE
BREAK ON owner SKIP 1
BREAK ON owner SKIP PAGE
BREAK ON owner SKIP PAGEON object_type SKIP 1
SELECT owner, object_type,object_nameFROM dba_objectsORDERBY owner,object_type, object_name;
The below above example causes apage break to occur whenever anowner changes, and a blank line tobe printed whenever an ownerchanges, and a blank line to beprinted whenever the object typechanges :
SQL
-
8/8/2019 9i SQL Slide
99/141
22-Dec-10 2:51 PM 99 Copyright Argam, 2006. All rights reserved.
Creating and Managing Tables
SQL
Table
-
8/8/2019 9i SQL Slide
100/141
22-Dec-10 2:51 P M 100 Copyright Argam, 2006. All rights reserved.
Table
Basic unit of storage ; composed of rows and columns
TABLE name and COLUMN name
Must begin with a letter
Must between 1-30 characters long
Must contain only A-Z, a-z, 0-9, _ , $ , #
Must not be an Oracle server reserved word
CREATE TABLE [schema.]table(column datatype [DEFAULT expr] [,..]);
SQL
Creating Table
-
8/8/2019 9i SQL Slide
101/141
22-Dec-10 2:51 P M 101 Copyright Argam, 2006. All rights reserved.
Creating TableCREATE TABLE student(sno number(2),
sname varchar2(15),dob date,age number(2),tno number(2));
CREATE TABLE dept80AS SELECT employee_id, last_name, hire_date,
department_idFROM employees
WHERE department_id = 80;
Creating a table by using a Subquery
CREATE TABLE teacher(tno number(2),tname varchar2(15),dept varchar2(15) DEFAULT no dept);
SQL
The ALTER TABLE statement
-
8/8/2019 9i SQL Slide
102/141
22-Dec-10 2:51 P M 102 Copyright Argam, 2006. All rights reserved.
TheALTER TABLE statement
UseALTER TABLE statement to add, modify or drop columns
ALTER TABLE dept80
ADD (job_id varchar2(9));
ALTER TABLE dept80
MODIFY (last_name varchar2(30));
ALTER TABLE dept80
DROP COLUMN job_id;
ALTER TABLE dept80
SET UNUSED (last_name);
ALTER TABLE dept80
SET UNUSED COLUMN job_id;
ALTER TABLE dept80
DROP UNUSED COLUMNS ;
SQL
Dropping a Table
-
8/8/2019 9i SQL Slide
103/141
22-Dec-10 2:51 P M 103 Copyright Argam, 2006. All rights reserved.
DROP TABLE dept80;
Changing the Name of an Object / Column
RENAME dept TO detail_dept;
Truncating a table
TRUNCATE TA
BLE detail_dept;
Removes all the rows from a Table
Releases storage space used by that table
HWM
ALTER TABLE emp RENAME COLUMN dept TO depart;
-
8/8/2019 9i SQL Slide
104/141
SQL
-
8/8/2019 9i SQL Slide
105/141
22-Dec-10 2:51 P M 105 Copyright Argam, 2006. All rights reserved.
Constraints
Constraints enforce rules at the Table level
NOT NULL value must exist
UNIQUE no duplicatesPRIMARY KEY NOT NULL & UNIQUE
FOREIGN KEY relationship between twocolumns
CHECK condition must be true
-
8/8/2019 9i SQL Slide
106/141
SQL
Constraints
-
8/8/2019 9i SQL Slide
107/141
22-Dec-10 2:51 P M 107 Copyright Argam, 2006. All rights reserved.
Constraints
ALTER TABLE teach
ADD CONSTRAINT emp_mgr_uk UNIQUE (tname);
ALTER TABLE teach
DROP CONSTRAINT emp_mgr_uk;
ALTER TABLE teach
DISABLE CONSTRAINT emp_mgr_uk;
ALTER TABLE teach
ENABLE CONSTRAINT emp_mgr_uk;
user_constraintsuser_cons_columns
SQL
-
8/8/2019 9i SQL Slide
108/141
22-Dec-10 2:51 P M 108 Copyright Argam, 2006. All rights reserved.
Manipulating Data
SQL
SQL Statements
-
8/8/2019 9i SQL Slide
109/141
22-Dec-10 2:51 P M 109 Copyright Argam, 2006. All rights reserved.
SQL Statements
SELECT Data retrieval
INSERT DML Data Manipulation LanguageUPDATEDELETEMERGE
CREATE DDL Data Definition LanguageALTERDROPRENAMETRUNCATE
COMMIT Transaction Control ROLLBACKSAVEPOINT
GRANT DCL Data Control LanguageREVOKE
SQL
Data Manipulation Language
-
8/8/2019 9i SQL Slide
110/141
22-Dec-10 2:51 P M 110 Copyright Argam, 2006. All rights reserved.
Data Manipulation Language
A DML statement is executed when
Add new rows to a table.
Modify existing rows in a table.
Remove existing rows from a table.
The INSERT statement
Add new rows to a table by using the INSERT statement
Only one row is inserted at a time with this syntax
INSERT INTO table [(column [, column])]VALUES (value [, value]);
SQL
I i N R
-
8/8/2019 9i SQL Slide
111/141
22-Dec-10 2:51 P M 111 Copyright Argam, 2006. All rights reserved.
Inserting New Rows
INSERT INTO departments(department_id,department_name, manager_id, location_id)VALUES (9, Database Admin, 100, 1700);
INSERT INTO departments
VALUES (9, Database Admin,100,1700);
INSERT INTO departments(department_id,location_id)
VALUES (9, 1700);
INSERT INTO departments(department_id,department_name, manager_id, location_id)
VALUES (9, USER , null ,);
SQL
Inserting New Rows
-
8/8/2019 9i SQL Slide
112/141
22-Dec-10 2:51 P M 112 Copyright Argam, 2006. All rights reserved.
INSERT INTO employees
VALUES (18,Reyarndo,DeCarl,RDECARL, 515.17.61,TO_DATE(FEB 3, 2005,MON DD, YYYY), DBA, 11000,
NULL, 100, 30);
Copying Rows from Another TableINSERT INTO empp
SELECT * FROM employees WHERE deptno = 30;
Inserting using & && (Substitution variables)INSERT INTO departments
VALUES (&c1, &c2 , &&c3);
SQL
The UPDATE statement
-
8/8/2019 9i SQL Slide
113/141
22-Dec-10 2:51 P M 113 Copyright Argam, 2006. All rights reserved.
The UPDATE statement
Modify existing rows with the UPDATE statement
Update more than one row at a time, if required
UPDATE tableSET column = value [, column = value,][WHERE condition];
UPDATE employeesSET department_id = 70
WHERE employee_id = 113;
UPDATE emppSET department_id = 70;
Updates all rows, ifWHERE clause is omitted
-
8/8/2019 9i SQL Slide
114/141
SQL
The DELETE statement
-
8/8/2019 9i SQL Slide
115/141
22-Dec-10 2:51 P M 115 Copyright Argam, 2006. All rights reserved.
Remove existing rows with the DELETE statement
Delete more than one row at a time, if required
DELETE [FROM] table[WHERE condition];
DELETE FROM departments WHERE department_name = Finance;
DELETE FROM empp;
DELETE employeesWHERE department_id = (SELECT department_id
FROM departmentsWHERE department_nameLIKE %Public%);
-
8/8/2019 9i SQL Slide
116/141
SQL
Multi-table INSERT
-
8/8/2019 9i SQL Slide
117/141
22-Dec-10 2:51 P M 117 Copyright Argam, 2006. All rights reserved.
UnconditionalINSERT ALL
Select the EMPLOYEE_ID, HIRE_DATE, SALARY, andMANAGER_ID values from the EMPLOYEES table for thoseemployees whose EMPLOYEE_ID is greater than 200.
Insert these values into the SAL_HISTORY and
MGR_HISTORYtables using a multi-table
INSERT All the table must be valid, otherwise rollback.
INSERT ALLINTO sal_history VALUES(empid, hiredate, sal)INTO mgr_history VALUES(empid, mgr, sal)SELECT employee_id EMPID, hire_date HIREDATE,
salary SAL, manager_id MGRFROM employees
WHERE employee_id > 200;
SQL
Multi-table INSERT
-
8/8/2019 9i SQL Slide
118/141
22-Dec-10 2:51 P M 118 Copyright Argam, 2006. All rights reserved.
ConditionalINSERT ALL
Select the EMPLOYEE_ID, HIRE_DATE, SALARY, andMANAGER_IDvalues from the EMPLOYEES table for those employees whoseEMPLOYEE_ID is greater than 200.
-- If the SALARY is greater than $10,000, insert these values into theSAL_HISTORY table
-- If theMANAGER_ID is greater than 200, insert these values into the
MGR_HISTORY table.
INSERT ALLWHEN sal > 10000 THEN
INTO sal_history VALUES(empid, hiredate, sal)WHEN mgr > 200 THEN
INTO mgr_history VALUES(empid, mgr, sal)SELECT employee_id EMPID, hire_date HIREDATE,
salary SAL, manager_id MGRFROM employees
WHERE employee_id > 200;
SQL
Multi-table INSERT
-
8/8/2019 9i SQL Slide
119/141
22-Dec-10 2:51 P M 119 Copyright Argam, 2006. All rights reserved.
Conditional FIRST INSERT
Select the DEPARTMENT_ID, SUM(salary) and MAX(hire_date)
from the EMPLOYEES table-- If the SUM(salary)is greater than $18,000, then insert these values into theSPECIAL_SAL.
-- If the firstWHEN clause evaluates to true, the subsequentWHEN clauses for
this rows should be skipped.-- For the rows that do not satisfy the first WHEN condition, insert into the
HIREDATE_HISTORY_00, or HIREDATE_HISTORY_99 or HIREDATE_HISTORY tables,
based on the value in the HIRE_DATE column
INSERT FIRSTWHEN sal > 18000 THEN
INTO special_sal VALUES(deptid, sal)WHEN hiredate like (%00%) THEN
INTO hiredate_history_00 VALUES(deptid, hiredate)
WHEN hiredate like (%99%) THENINTO hiredate_history_99 VALUES(deptid,hiredate)
ELSE INTO hiredate_history VALUES(deptid, hiredate)SELECT department_id DEPTID, SUM(salary) SAL,
MAX(hire_date) HIREDATE FROM employeesGROUP BY department_id;
SQL
Transaction
-
8/8/2019 9i SQL Slide
120/141
22-Dec-10 2:51 P M 120 Copyright Argam, 2006. All rights reserved.
Transaction
Transaction consists of the collection of DML statements thatform a logical unit of work
DML statements which constitute one consistent change to the data.
One DDL statement
One DCL statement
Begin when the first DML statement is executed
End with one of the following events :
- A COMMIT orROLLBACK statement is issued
- A DDL or DCL statement executed
- The user exits SQL*Plus / Worksheet
SQL
C t lli T ti
-
8/8/2019 9i SQL Slide
121/141
22-Dec-10 2:51 P M 121 Copyright Argam, 2006. All rights reserved.
Controlling Transactions
COMMIT
Transaction
DELETE
SAVEPOINT A
INSERT
UPDATE
SAVEPOINT B
INSERT
ROLLBACK TOSAVEPOINT B
ROLLBACK TOSAVEPOINT A
ROLLBACK
SQL
Index
-
8/8/2019 9i SQL Slide
122/141
22-Dec-10 2:51 P M 122 Copyright Argam, 2006. All rights reserved.
Used by the Server to speed up the retrieval rows by using pointer
Automatically - PRIMARY KEY orUNIQUE constraintManually - create non-unique index on column
CREATE INDEX emp_lname_idxON employees(last_name);
user_indexesuser_ind_columns
DROP INDEX emp_lname_idx;
CREATE INDEX upper_emp_job_idx
ON employees( UPPER (job_id));
A Function-based index is an index based on expressions
SQL
Sequence
-
8/8/2019 9i SQL Slide
123/141
22-Dec-10 2:51 P M 123 Copyright Argam, 2006. All rights reserved.
Generates unique numbers sequentially
Used to create a primary key value
CREATE SEQUENCE d_did_seqSTART WITH 120INCREMENT BY 10
MAXVALUE 9999 | NOMAXVALUEC
YC
LE | NOC
YC
LECACHE 10 | NOCACHE;ORDER | NOORDER;
INSERT INTO departmentsVALUES (d_did_seq.NEXTVAL , Support, 2500);
NEXTVAL returns the next available sequenceCURRVAL obtains the current sequence value
SELECT d_did_seq.CURRVAL FROM dual;
SQL
Sequence
-
8/8/2019 9i SQL Slide
124/141
22-Dec-10 2:51 P M 124 Copyright Argam, 2006. All rights reserved.
ALTER SEQUENCE is used to modify
ALTER SEQUENCE d_did_seqINCREMENT BY 10
MAXVALUE 8888
NOCYCLE;
START WITH cannot be modified always
user_sequences
DROP SEQUENCE d_did_seq;
Sequence
SQL
Synonyms
-
8/8/2019 9i SQL Slide
125/141
22-Dec-10 2:51 P M 125 Copyright Argam, 2006. All rights reserved.
Shorten lengthy objects names
CREATE SYNONYM d_sumFOR department_sum_vu;
DROP SYNONYM d_sum;
y y
SQL
Summary
-
8/8/2019 9i SQL Slide
126/141
22-Dec-10 2:51 P M 126 Copyright Argam, 2006. All rights reserved.
yINSERT DML Data Manipulation Language
UPDATEDELETEMERGE
COMMIT Transaction Control
ROLLBACKSAVEPOINTMulti-table INSERT
Unconditional INSERT ALL
Conditional INSERT ALLConditional FIRST INSERT
INDEX SEQUENCE - SYNONYM
SQL
-
8/8/2019 9i SQL Slide
127/141
22-Dec-10 2:51 P M 127 Copyright Argam, 2006. All rights reserved.
Controlling User Access
SQL
Privileges
-
8/8/2019 9i SQL Slide
128/141
22-Dec-10 2:51 P M 128 Copyright Argam, 2006. All rights reserved.
Privileges are the right to execute particular SQL statements
System Privileges : Gaining access to the database
CREATE TABLE, CREATE USER ..
Object Privileges : Manipulating the content of the db objects SELECT, UPDATE, REFERENCES...
CREATE USER reynardoIDENTIFIED BY carl;
GRANT create user, create table, create viewTO hr;
ALTER USER reynardoIDENTIFIED BY rey;
SQL
Role
-
8/8/2019 9i SQL Slide
129/141
22-Dec-10 2:51 P M 129 Copyright Argam, 2006. All rights reserved.
Role is a named group of related privileges that can be grantedto the user
A User can have several roles, and several users can beassigned the same role
CREATE ROLE manager;
GRANT create table, create userTO manager;
GRANT managerTO dehann, kochhar;
Create a Role
Grant privileges to aRole
Grant a role to users SET ROLE gardener IDENTIFIED BYmarigolds;
SET ROLE ALL;SET ROLE ALL EXCEPT dw_manager;SET ROLE NONE; -- includes DEFAULT role
SQL
* Object privileges is access to on object i e table viewPrivileges
-
8/8/2019 9i SQL Slide
130/141
22-Dec-10 2:51 P M 130 Copyright Argam, 2006. All rights reserved.
* Object privileges is access to on object i.e. table, view* An owner has all privileges on the object which has been
created* An owner can give specific privileges on his object to other userGRANT select, updateON employeesTO scott;
SELECT *FROM hr.employees;
Connect to SCOTT to retrieve from HR
GRANT update (department_id, location_id)ON employeesTO scott, rey ;
GRANT select, updateON employeesTO scott
WITH GRANT OPTION ;
GRANT selectON rey.studentTO PUBLIC;
references
SQL
Revoke
-
8/8/2019 9i SQL Slide
131/141
22-Dec-10 2:51 P M 131 Copyright Argam, 2006. All rights reserved.
REVOKE statement used to revoke privileges granted to other users
Privileges granted to others throughWITH GRANT OPTION alsorevoked
REVOKE select, updateON employeesFROM scott;
WITHGRANTOPTION
Grant on X object
X Y Z
Z can access from X
REVOKE the PRIVILEGE
Grant on X object
X Y Z
Y and Z cannot access from X
SQL
Summary
-
8/8/2019 9i SQL Slide
132/141
22-Dec-10 2:51 P M 132 Copyright Argam, 2006. All rights reserved.
Summary
Privilege - rights to execute particular SQL statementsSystem CREATE TABLE, CREATE USER ..Object SELECT, UPDATE ..
Role is a named group of related privileges
GrantRevoke
SQL
-
8/8/2019 9i SQL Slide
133/141
22-Dec-10 2:51 P M 133 Copyright Argam, 2006. All rights reserved.
Views
SQL
Creating View
-
8/8/2019 9i SQL Slide
134/141
22-Dec-10 2:51 P M 134 Copyright Argam, 2006. All rights reserved.
Logically represents subsets of data from one or more tables.
Logical table based on a table or another view.
A View contains no data of its own but is like a windowthrough which data from tables can be view or changed.
View is stored as a SELECT statement in the data dictionary.
CREATE [OR REPLACE] [FORCE|NO FORCE] VIEWviewname[(alias[, alias] ..)]
AS subquery[WITH CHECK OPTION [constraint cname]][WITH READ ONLY [constraint cname]] ;
SQL
Creating View
-
8/8/2019 9i SQL Slide
135/141
22-Dec-10 2:51 P M 135 Copyright Argam, 2006. All rights reserved.
Create a viewEMPVU80, that contains details of the employees
in department 80CREATE OR REPLACE VIEWempvu80
AS SELECT employee_id, last_name, salaryFROM employees WHERE department_id = 80;
CREATE OR REPLACE VIEWempvu50AS SELECT employee_idEID, last_nameNAME,
salary*12ANN_SALFROM employees WHERE department_id = 50;
CREATE OR REPLACE VIEWempvu50(EID,NAME,ANN_SAL)AS SELECT employee_id, last_name, salary
FROM employees WHERE department_id = 50WITH READ ONLY ;
SQL
Complex View
-
8/8/2019 9i SQL Slide
136/141
22-Dec-10 2:51 P M 136 Copyright Argam, 2006. All rights reserved.
Create a Complex view that contains group functions to displayvalues from two tables
CREATE OR REPLACE VIEWdept_sum_vu
( name, minsal, maxsal, avgsal)AS SELECT d.department_name, min(e.salary),
max(salary), avg(salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;
Complex View
SQL
WITH CHECK OPTION
-
8/8/2019 9i SQL Slide
137/141
22-Dec-10 2:51 P M 137 Copyright Argam, 2006. All rights reserved.
Any attempt to change the department number for a rowin the view fails, because it violatesWITH CHECK OPTION
CREATE OR REPLACE VIEWempvu20
AS SELECT employee_id, last_name, salary,
department_id FROM employees
WHERE department_id = 20
WITH CHECK OPTION ;
DROP VIEW view_name;
WITH CHECK OPTION CONSTRAINT empvu20_ck;
SQL
I li Vi / TOP N
-
8/8/2019 9i SQL Slide
138/141
22-Dec-10 2:51 P M 138 Copyright Argam, 2006. All rights reserved.
A named subquery in the FROMclause of the main query.
Who are the top TEN earners ?Who are the least FIVE earners ?
SELECT ROWNUM as RANK, last_name, salaryFROM ( SELECT last_name, salary FROM employees
ORDERBY salary DESC )WHERE ROWNUM < = 10 ;
WHERE must use < or
-
8/8/2019 9i SQL Slide
139/141
22-Dec-10 2:51 P M 139 Copyright Argam, 2006. All rights reserved.
To restrict data access
To make complex queries easy
To present different view of the same data table.
Simple View vs Complex View
Feature Simple View Complex View
No of tables One One or More
Contains group functions No Yes
DML operations Yes Not alwaysthrough a view
SQL
S
-
8/8/2019 9i SQL Slide
140/141
22-Dec-10 2:51 P M 140 Copyright Argam, 2006. All rights reserved.
Summary
Create View Simple / Complex
WITH CHECK OPTION
INLINE / TOP-N
SQL
-
8/8/2019 9i SQL Slide
141/141