Oracle Quiz on SQL and PL/SQL See water burning Alex Nuijten

1
Oracle Quiz on SQL and PL/SQL See water
burning Alex Nuijten Lucas JellemaAMIS, The
Netherlands
2
Journey to the Mythical Heart of the Matter...

• We will look at some lesser known Oracle SQL and
  PL/SQL features
• And see some ..ahem alternative usages of well
  known features
• In the form of a Quiz, you can show off
• Multiple choice, no modifications, play fair!
• Winner gets to select any book from the bookstall

3
What is the name of Scotts cat?

1. King
2. Tiger
3. Big Red
4. Bruce

1
4
The Demo-tables The Infamous SCOTT schema
5
What has been done to have this query return
this result?

1. Data in EMP have been manipulated
2. 10g Package DBMS_ADVANCED_REWRITE has been
   (ab)used
3. Pre-parsed cursor fetch has been rolled over by
   buffer- cache checkpoint high water mark
4. 9iR2 and beyond query randomizer at work

2
6
What is the result of this query?

1. OPERATIONS
2. New York
3. 04
4. SELAS

3
7
Which query implements this question?

• For all employees, return the name and
• If they are manager
• And their salary is 2950 or up, return label
  High
• Salary under 2950, return label Low
• Else, in case they are not a manager, return
• label Low if they earn under 1000
• label Medium Odd if they have an odd salary
  over 1000
• label Medium if their salary is over 1000 and
  it is an even number

8
Which query implements this question?
B.
A.
4
C.
D.
9
CASE offers a much more attractive solution
10
DECODE vs. CASE

• Why not DECODE
• Hard to read, impossible to maintain
• Not part of the ANSI standards for SQL
• CASE expressions are a lot more powerful
• When/Where CASE
• In SQL
• select, where, having, order by
• In PL/SQL
• In Check Constraints
• In Index definitions

11
How can we implement this business rule?

• A department may not have more than one CLERK

1. Unique Constraint - on EMP (DEPTNO, JOB)
2. Check Constraint with CASE expression
3. Unique Index
4. Insert and Update trigger on EMP (column DEPTNO
   and JOB)

5
12
Function Based Unique Index

• An index can be defined as UNIQUE
• A UNIQUE index ignores NULL-clashes
• UNIQUE (deptno, job) accepts (NULL,NULL) and
  (NULL,NULL)
• A Function Based Index can be defined for an
  expression or PL/SQL function call, for example a
  CASE expression
• A department may not have more than one CLERK

13
How do we make a random selection of
approximately 10 of our Employee records?
B.
A.
6
C.
D.
14
FROM SAMPLE (8i)

• FROM lttablegt sample (percentage)
• The sample_clause lets you instruct Oracle to
  select from a random sample of rows from the
  table, rather than from the entire table.
• The sample percentage is between 0.000001 and 100
• The percentage indicates the chance for each
  record to be included in the sample

select emp. , dept.dname from emp sample
(10) -- 10 , deptwhere emp.deptno
dept.deptno
15
Whoops Management

• I perform this update
• update emp set sal (1 dbms_random.value)
  sal
• commit
• Whoops.production system Oh Oh.
• How can I best correct this situation?

1. Flashback Database and Roll Forward until just
   prior to the update
2. Select old values from Flashback Query and update
3. Restore a database backup, export table and
   import
4. Use the 10g Undo Transaction statement

7
16
Flashback Query

• select from table AS OF TIMESTAMP
• Select values as they were at a certain moment in
  time
• Depends on UNDO data
• In actual fact is every Oracle query (due to the
  read-consistency) some sort of flashback query)

17
The Top-3 Earning Employees

• What can you state about the query below with
  regard to the quest for the Top 3 earning
  employees?

1. Returns the correct answer
2. May sometimes return the correct answer
3. Okay as long as there are no duplicate salaries
4. Not correct

8
18
In-Line Views

• In line views have been around since 7.2 (and in
  7.1 an undocumented feature)

19
Can we select the Top 3 Earners PER DEPARTMENT?
A.
B.
Can not be done in a single Query
9
C.
D.
20
Analytical Functions
21
SELECT ANALYTICAL FUNCTION (8i/9i)

• Functions for advanced analysis
• OLAP inspired
• Very efficient compared to traditional SQL
• Since 8.1.7 EE and 9iR2 SE
• Typical Analytical operations
• Aggregation
• Ranking
• Deduplication
• Inter-row comparisons and calculations
• Spreadsheet-ish
• Statistical Analysis

22
Which of these queries contains an invalid Oracle
9iR2 SQL-statement
B.
A.
10
C.
D.
23
User Defined Aggregate
select avg(hiredate) from emp
ORA-00932 inconsistent datatypes expected
NUMBER got DATE

• Oracle Data Cartridge allows us to define our own
  Aggregates
• For example SUM_VARCHAR2, AVG_DATE,COUNT_CHARA
  CTERS

24
User defined sum_varchar2 aggregate

• Implement AmisVarchar2SumImpl object
• Create function sum_varchar2 as aggregate using
  object AmisVarchar2SumImpl

select deptno , sum_varchar2(ename)
employees from emp group by deptno
25
What is going on here?
Plaatje EXCEL

1. Table Function and UTL_FILE
2. External Table
3. Materialized View
4. REDO LogFile

11
26
EXTERNAL TABLE (9i)

• Data in an external text-file can be published as
  table in the database
• accessed in SQL and PL/SQL like a normal table
• Define table with ORGANIZATION EXTERNAL

CREATE TABLE emp_ext ( empcode NUMBER(4),
empname VARCHAR2(25), deptname VARCHAR2(25),
hiredate date ) ORGANIZATION EXTERNAL ...
select from where
27
Language!?
28
What (or who) is behind all this?

1. View on Table Function that generates of
   transforms values
2. NLS_RESOURCE_BUNDLE settings
3. Virtual Private Database policy function on
   table
4. View with PL/SQL Function Call in the WHERE-clause

12
29
FROM TABLE FUNCTION (9i)

• (result returned by) PL/SQL Function can be used
  as data-source in query
• As if it were a table
• Function must return a Server NESTED TABLE Type
• CREATE TYPE NUM_TABLE IS TABLE OF NUMBER
• Functions can be nested, parallelized and/or
  pipelined

... from table( function(ltparameters) )where
...
30
PieCharts in SQL

• Can you have a SQL query return apie chart?
• For example to review the salary sumsper JOB
  catagory

1. No. Are you out of your mind?
2. I would be surprised. I assume with Table
   Functions
3. Piece of cake (pun intentional) use the new 10g
   EXTRACT_PIE function
4. Yeah, you can do that, but only on Windows

13
31
PieChart in SQL
32
Final scores.

• How many correct answers did you give?

33
and the winner is
34
How often will this trigger fire for this update
statement?

1. No more than 3 times
2. Up to 5 times
3. At least three with a maximum of 6 times
4. Unlimited

35
What is happening?
Session 1
Session 2
update i1, i2 and begin with i3 run into lock
held by session 2
updates are rolled-back and a select-for update
is initiated
The lock on i3 is released now i equals 6!
36
Which Country is The Netherlands?