Contents
- Overview
- How can I transform a subquery involving the IN clause to a Join?
- How can I transform a statement involving an OR condition to a UNION ALL?
- How do I eliminate duplicate values in a table?
- How can I get a count of the different data values in a column?
- How can I get count/sum RANGES of data values in a column?
- How can I dynamically generate a list of comma-separated items?
- How can I get the time difference between two date columns?
- Can I retrieve only the Nth row from a table?
- Can I retrieve only rows X to Y from a table?
- Can I retrieve every Nth row from a table?
- Retrieve the TOP N Rows from a table?
- Is it possible to dump/examine the exact content of a database column?
- Can I code a MATRIX Report using SQL?
- Tree Structured (Hierarchical) Queries
- Insert Large Number of Sequence Values using UNION
Structured Query Language (SQL) is an implementation of a "data sublanguage" used in almost all relational database systems. SQL was developed by IBM in the early 1970s for use in System R, and is "de facto" standard, as well as an ISO and ANSI standard.
In common usage, SQL supports four types of SQL statements: - Queries:
This type of SQL statement implements the standard relational operations such as SELECTION, PROJECTION and JOIN. The SELECT statement forms the basis for many other SQL operations. You can use SELECT statements to query tables, create views, in subqueries, or when creating a table defined as the result set of query.
- Data Manipulation Language (DML):
DML statements are used to allow data in the database to be added, amended or deleted. DML functionality is provided by the use of INSERT, UPDATE and DELETE statements.
- Data Definition Language (DDL):
This is the language that allows for objects to be created or altered. Provided below are a few example Oracle DDL statements:
- CREATE TABLE
- DROP TABLE
- ALTER TABLE
- CREATE INDEX
- DROP INDEX
- ALTER INDEX
- CREATE VIEW
- CREATE TYPE
- Data Control Language (DCL):
This is the language that allows for transaction control. Provided below are a few example Oracle DCL statements:
- COMMIT
- SAVEPOINT
- ROLLBACK
- SET TRANSACTION
Original Statement:
SELECT e.empno, e.ename FROM emp e WHERE e.deptno IN ( SELECT d.deptno FROM dept d WHERE d.loc = 'CHICAGO );New Statement: SELECT e.empno, e.ename FROM emp e, dept d WHERE d.loc = 'CHICAGO AND d.deptno = e.deptno;Original Statement:
SELECT dname, loc FROM dept WHERE loc = 'CHICAGO OR loc = 'NEW YORK New Statement: SELECT dname, loc FROM dept WHERE loc = 'CHICAGO UNION ALL SELECT dname, loc FROM dept WHERE loc = 'NEW YORK Provided below are four methods for identifying or removing duplicate rows from a table:
Method 1: DELETE FROM emp a WHERE rowid > ( SELECT min(rowid) FROM emp b WHERE a.emp_id = b.emp_id );Method 2: CREATE TABLE emp2 AS SELECT distinct * FROM emp; DROP TABLE emp; RENAME emp2 TO emp;Method 3: DELETE FROM emp WHERE rowid NOT IN ( SELECT MIN(rowid) FROM emp GROUP BY emp_id );Method 4: DELETE FROM emp a WHERE EXISTS ( SELECT 'true' FROM emp b WHERE b.emp_id = a.emp_id AND b.rowid < a.rowid );NOTE: If you where to create an index on the joined fields in the inner loop, it may be possible to eliminate N^2 operations as there would be no need to loop through the entire table on each pass be a record. SELECT dname , sum(decode(job, 'CLERK', 1, 0)) Clerk , sum(decode(job, 'SALESMAN', 1, 0)) Salesman , sum(decode(job, 'MANAGER', 1, 0)) Manager , sum(decode(job, 'ANALYST', 1, 0)) Analyst , sum(decode(job, 'PRESIDENT', 1, 0)) President FROM emp e , dept d WHERE e.deptno (+) = d.deptno GROUP BY dname; DNAME CLERK SALESMAN MANAGER ANALYST PRESIDENT -------------- ---------- ---------- ---------- ---------- ---------- ACCOUNTING 1 0 1 0 1 OPERATIONS 0 0 0 0 0 RESEARCH 2 0 1 2 0 SALES 1 4 1 0 0A value "x" will be between values "y" and "z" if: GREATEST(x,y)=LEAST(x,z).
SELECT job , sum(decode(greatest(sal,2999), least(sal,6000), 1, 0)) "Range 3000-6000" , sum(decode(greatest(sal,1000), least(sal,2999), 1, 0)) "Range 1000-3000" , sum(decode(greatest(sal,0), least(sal,999), 1, 0)) "Range 0-1000" FROM emp GROUP BY job; JOB Range 3000-6000 Range 1000-3000 Range 0-1000 --------- --------------- --------------- ------------ ANALYST 2 0 0 CLERK 0 2 2 MANAGER 0 3 0 PRESIDENT 1 0 0 SALESMAN 0 4 0I often find myself needing to dynamically generate a list of comma-separated items (i.e. a list of database files separated by commas). The output format, for example, would be:
... Datafile1, Datafile2, Datafile3 ...Instead of thinking of the list as a linear set of items, re-write the set of items each on a separate line as follows:
... Datafile1 , Datafile2 , Datafile3...Notice that a comma is used on each line except for the first item. We can now take advantage of a popular Analytic Function (RANK) to distinguish the first line.
Consider the following CREATE CONTROLFILE example: SET HEADING OFFSET TERMOUT OFFSET FEEDBACK OFFSET VERIFY OFFSET PAGESIZE 9000SET LINESIZE 135SET TRIMSPOOL ONSET TRIMOUT ONSET ECHO OFFSET SQLBLANKLINES ON SPOOL cr_control_file_example.sql prompt CREATE CONTROLFILE SET DATABASE testdb RESETLOGS NOARCHIVELOGprompt MAXLOGFILES 16prompt MAXLOGMEMBERS 3prompt MAXDATAFILES 100prompt MAXINSTANCES 8prompt MAXLOGHISTORY 3182prompt LOGFILEprompt GROUP 1 (prompt '/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7rv7_.log',prompt '/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7sov_.log'prompt ) SIZE 50M,prompt GROUP 2 (prompt '/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v0s_.log',prompt '/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v7j_.log'prompt ) SIZE 50M,prompt GROUP 3 (prompt '/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wj5_.log',prompt '/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wqp_.log'prompt ) SIZE 50Mprompt DATAFILE SELECT CASE WHEN ROW_NUMBER() OVER(ORDER BY name) = 1 THEN ' ''' || name || '''' ELSE ' , ''' || name || '''' END AS FROM v$datafile; prompt CHARACTER SET WE8ISO8859P1 SPOOL OFF ------------------------------------------------------------ CREATE CONTROLFILE SET DATABASE testdb RESETLOGS NOARCHIVELOGMAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 3182LOGFILEGROUP 1 ('/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7rv7_.log', '/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7sov_.log') SIZE 50M,GROUP 2 ('/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v0s_.log', '/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v7j_.log') SIZE 50M,GROUP 3 ('/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wj5_.log', '/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wqp_.log') SIZE 50MDATAFILE '+ORCL_DATA1/orcl/datafile/example.263.623550121', '+ORCL_DATA1/orcl/datafile/soe.269.623559923'
, '+ORCL_DATA1/orcl/datafile/soeindex.270.623560217' , '+ORCL_DATA1/orcl/datafile/sysaux.261.623550085' , '+ORCL_DATA1/orcl/datafile/system.259.623549989' , '+ORCL_DATA1/orcl/datafile/undotbs1.260.623550053' , '+ORCL_DATA1/orcl/datafile/undotbs2.264.623550147', '+ORCL_DATA1/orcl/datafile/users.265.623550181'
CHARACTER SET WE8ISO8859P1Method 1:
SELECT ename , job , hiredate FROM emp WHERE rowid = ( SELECT rowid FROM emp WHERE rownum <= 3 MINUS SELECT rowid FROM emp WHERE rownum < 3 );Method 2: SELECT ename , job , hiredate FROM emp WHERE rownum = 1 AND rowid NOT IN ( SELECT rowid FROM emp WHERE rownum < 3 );NOTE: Always remember that there is no explicit order in a relational database. SELECT ename , job , hiredate FROM emp WHERE rowid in ( SELECT rowid FROM emp WHERE rownum <= 7 MINUS SELECT rowid FROM emp WHERE rownum < 3 );Method 1:
SELECT ename , job , hiredate FROM emp WHERE (rowid,0) in ( SELECT rowid, mod(rownum,4) FROM emp );Method 2: Using Dynamic Views: (available in Oracle7.2 and higher)
SELECT ename , job , hiredate FROM ( SELECT rownum rn, empno, ename, job, hiredate FROM emp ) d_table WHERE mod(d_table.rn,4) = 0;Method 1: Starting with Oracle8i, you can have an inner-query with an ORDER BY clause
SELECT ename , job , hiredate , sal FROM ( SELECT empno, ename, job, hiredate, sal FROM emp ORDER BY sal DESC ) WHERE rownum < 6;Method 2: The following workaround will work with prior releases SELECT ename , job , hiredate , sal FROM emp a WHERE 5 >= ( SELECT count(distinct b.sal) FROM emp b WHERE b.sal >= a.sal ) ORDER BY a.sal DESC SELECT ename, job, dump(job) FROM emp WHERE ename = 'SMITH'; ENAME JOB DUMP(JOB)---------- --------- ---------------------------SMITH CLERK Typ=1 Len=5: 67,76,69,82,75Type Code | Oracle Datatype |
1 | VARCHAR2 |
2 | NUMBER |
12 | DATE |
96 | CHAR |
SELECT * FROM ( SELECT job , sum(decode(deptno, 10, sal)) DEPT10 , sum(decode(deptno, 20, sal)) DEPT20 , sum(decode(deptno, 30, sal)) DEPT30 , sum(decode(deptno, 40, sal)) DEPT40 FROM emp e GROUP BY job ) ORDER BY 1; JOB DEPT10 DEPT20 DEPT30 DEPT40
--------- ---------- ---------- ---------- ----------ANALYST 6000
CLERK 1300 1900 950 MANAGER 2450 2975 2850 PRESIDENT 5000 SALESMAN 5600 SELECT lpad(' ', level*4) || ename FROM emp CONNECT BY PRIOR empno = mgr START WITH mgr IS NULL; Employee Name ------------------------ KING JONES JHUNTER ADAMS FORD SMITH BLAKE ALLEN WARD MARTIN TURNER JAMES CLARK MILLERNOTE: Using the "EMP" table from the SCOTT/TIGER schema makes a great test table since it has a "self-referencing" relation. (The MGR column contains the employee number of the "current" employee's boss. The LEVEL pseudo-column is an indication of how deep in the tree you are. Oracle can handle queries with a depth up to 255 levels.
The "START WITH" clause is used to specify the start of the tree. More than one record can match the starting condition. One disadvantage of having a "CONNECT BY PRIOR" clause is that you cannot perform a JOIN to other tables.
Here is an example SQL script that will insert 1,000,000 sequence values into a table using a series of UNION 's and a cartesian product.
DROP TABLE sequence_values/ CREATE TABLE sequence_values (id NUMBER)/ INSERT INTO sequence_values SELECT millions.x + hundred_thousands.x + ten_thousands.x + thousands.x + hundreds.x + tens.x + ones.x FROM (SELECT 0 x FROM dual UNION SELECT 1 FROM dual UNION SELECT 2 FROM dual UNION SELECT 3 FROM dual UNION SELECT 4 FROM dual UNION SELECT 5 FROM dual UNION SELECT 6 FROM dual UNION SELECT 7 FROM dual UNION SELECT 8 FROM dual UNION SELECT 9 FROM dual) ones, (SELECT 0 x FROM dual UNION SELECT 10 FROM dual UNION SELECT 20 FROM dual UNION SELECT 30 FROM dual UNION SELECT 40 FROM dual UNION SELECT 50 FROM dual UNION SELECT 60 FROM dual UNION SELECT 70 FROM dual UNION SELECT 80 FROM dual UNION SELECT 90 FROM dual) tens, (SELECT 0 x FROM dual UNION SELECT 100 FROM dual UNION SELECT 200 FROM dual UNION SELECT 300 FROM dual UNION SELECT 400 FROM dual UNION SELECT 500 FROM dual UNION SELECT 600 FROM dual UNION SELECT 700 FROM dual UNION SELECT 800 FROM dual UNION SELECT 900 FROM dual) hundreds, (SELECT 0 x FROM dual UNION SELECT 1000 FROM dual UNION SELECT 2000 FROM dual UNION SELECT 3000 FROM dual UNION SELECT 4000 FROM dual UNION SELECT 5000 FROM dual UNION SELECT 6000 FROM dual UNION SELECT 7000 FROM dual UNION SELECT 8000 FROM dual UNION SELECT 9000 FROM dual) thousands, (SELECT 0 x FROM dual UNION SELECT 10000 FROM dual UNION SELECT 20000 FROM dual UNION SELECT 30000 FROM dual UNION SELECT 40000 FROM dual UNION SELECT 50000 FROM dual UNION SELECT 60000 FROM dual UNION SELECT 70000 FROM dual UNION SELECT 80000 FROM dual UNION SELECT 90000 FROM dual) ten_thousands, (SELECT 0 x FROM dual UNION SELECT 100000 FROM dual UNION SELECT 200000 FROM dual UNION SELECT 300000 FROM dual UNION SELECT 400000 FROM dual UNION SELECT 500000 FROM dual UNION SELECT 600000 FROM dual UNION SELECT 700000 FROM dual UNION SELECT 800000 FROM dual UNION SELECT 900000 FROM dual) hundred_thousands, (SELECT 0 x FROM dual UNION SELECT 1000000 FROM dual UNION SELECT 2000000 FROM dual UNION SELECT 3000000 FROM dual UNION SELECT 4000000 FROM dual UNION SELECT 5000000 FROM dual UNION SELECT 6000000 FROM dual UNION SELECT 7000000 FROM dual UNION SELECT 8000000 FROM dual UNION SELECT 9000000 FROM dual) millions/Table dropped. Table created. 10000000 rows created.
No comments:
Post a Comment