1 / 36

Oracle Quiz on SQL and PL/SQL - Journey to the Mythical Heart of the Matter

Join us for a quiz on lesser-known Oracle SQL and PL/SQL features, where you can showcase your skills and learn alternative usages of well-known features. The winner gets to choose a book from the bookstall.

abarrier
Download Presentation

Oracle Quiz on SQL and PL/SQL - Journey to the Mythical Heart of the Matter

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Quiz on SQL and PL/SQLSee 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 Scott’s cat? • King • Tiger • Big Red • Bruce 1

  4. The Demo-tables – The Infamous SCOTT schema

  5. What has been done to have this query return this result? • Data in EMP have been manipulated • 10g Package DBMS_ADVANCED_REWRITE has been (ab)used • Pre-parsed cursor fetch has been rolled over by buffer- cache checkpoint high water mark • 9iR2 and beyond query randomizer at work 2

  6. What is the result of this query? • OPERATIONS • New York • 04 • 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 • Unique Constraint - on EMP (DEPTNO, JOB) • Check Constraint with CASE expression • Unique Index • 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 <table> 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? • Flashback Database and Roll Forward until just prior to the update • Select old values from Flashback Query and update • Restore a database backup, export table and import • 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”? • Returns the correct answer • May sometimes return the correct answer • Okay as long as there are no duplicate salaries • 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. Can not be done in a single Query B. 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 • Oracle Data Cartridge allows us to define our own Aggregates • For example: SUM_VARCHAR2, AVG_DATE,COUNT_CHARACTERS select avg(hiredate) from emp ORA-00932: inconsistent datatypes: expected NUMBER got DATE

  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 • Table Function and UTL_FILE • External Table • Materialized View • REDO LogFile 11

  26. ExternalTables 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? • View on Table Function that generates of transforms values • NLS_RESOURCE_BUNDLE settings • Virtual Private Database – policy function on table • 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(<parameters) )where ...

  30. PieCharts in SQL • Can you have a SQL query return apie chart? • For example to review the salary sumsper JOB catagory • No. Are you out of your mind? • I would be surprised. I assume with Table Functions… • Piece of cake (pun intentional) : use the new 10g EXTRACT_PIE function • Yeah, you can do that, but only on Windows 13

  31. PieChart in SQL

  32. B A 1 10 B B or A 2 11 D A, C or D 3 12 B A 4 13 C or D 5 B or D 6 B (a or c) 7 B or D 8 D (c) 9 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? • No more than 3 times • Up to 5 times • At least three with a maximum of 6 times • Unlimited

  35. What is happening? Session 1 Session 2 update i=1, i=2 and begin with i=3; run into lock held by session 2 updates are rolled-back and a select-for update is initiated The lock on i=3 is released now; i equals 6!

  36. Which Country is The Netherlands?

More Related