 | |||
|
|
||
| |||
| |||
| |
|
Home > Oracle Database / Applications Tips > Oracle Database Administrator > Fun with SQL |
| |
|
|
|
| Oracle Tips: |
|
|
|
|
|
|
|
|
||||
|
ORACLE DATABASE ADMINISTRATOR Fun with SQLJeff Hunter 06.28.2004 Rating: -4.56- (out of 5)
| |||||
TABLE OF CONTENTS
[IMAGE] Overview
[IMAGE] How can I transform a subquery involving the IN clause to a Join?
[IMAGE] How can I transform a statement involving an OR condition to a UNION ALL?
[IMAGE] How do I eliminate duplicate values in a table?
[IMAGE] How can I get a count of the different data values in a column?
[IMAGE] How can I get count/sum RANGES of data values in a column?
[IMAGE] How can I get the time difference between two date columns?
[IMAGE] Can I retrieve only the Nth row from a table?
[IMAGE] Can I retrieve only rows X to Y from a table?
[IMAGE] Can I retrieve every Nth row from a table?
[IMAGE] Retrieve the TOP N Rows from a table?
[IMAGE] Is it possible to dump/examine the exact content of a database column?
[IMAGE] Can I code a MATRIX Report using SQL?
[IMAGE] Tree-structured (hierarchical) queries
Overview
[ Return to Table of Contents ]
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, cre
Digg This!
 StumbleUpon
 Del.icio.us
|
 |
 |
| |
RELATED GLOSSARY TERMS
|
||||
|
|
| |
RELATED RESOURCES
|
ate 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:
Data Control Language (DCL): This is the language that allows for transaction control. Provided below are a few example Oracle DCL statements:
The development of SQL is governed by standards. A major revision of the SQL standard was completed in 1992, called SQL2. SQL3 is a newer revision that supports object extensions and will be partially implemented starting with Oracle8.
How can I transform a subquery involving the IN clause to a Join?
[ Return to Table of Contents ]
Original statement:
New statement: SELECT e.empno, e.ename FROM emp e, dept d WHERE d.loc = 'CHICAGO' AND d.deptno = e.deptno;
How can I transform a statement involving an OR condition to a UNION ALL?
[ Return to Table of Contents ]
Original statement:
New Statement:
How do I eliminate duplicate values in a table?
[ Return to Table of Contents ]
Provided below are four methods for identifying or removing duplicate rows from a table:
Method 1:
Method 2:
Method 3:
Method 4:
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.
How can I get a count of the different data values in a column?
[ Return to Table of Contents ]
How can I get count/sum RANGES of data values in a column?
[ Return to Table of Contents ]
A value "x" will be between values "y" and "z" if: GREATEST(x,y)=LEAST(x,z).
How can I get the time difference between two date columns?
[ Return to Table of Contents ]
Method 1: (Very long-winded. A more efficient version is indicated in Method 2.)
Method 2: (Thanks to Chris Hunt, http://www.extracon.com/, for providing me with this much easier and efficient SQL query to produce the same results!)
Can I retrieve only the Nth row from a table?
[ Return to Table of Contents ]
Method 1:
Method 2:
NOTE: Always remember that there is no explicit order in a relational database.
Can I retrieve only rows X to Y from a table?
[ Return to Table of Contents ]
Can I retrieve EVERY Nth row from a table?
[ Return to Table of Contents ]
Method 1:
Method 2: Using Dynamic Views: (available in Oracle7.2 and higher)
Retrieve the TOP N Rows from a table?
[ Return to Table of Contents ]
Method 1: Starting with Oracle8i, you can have an inner-query with an ORDER BY clause
Method 2: The following workaround will work with prior releases.
Is it possible to dump/examine the exact content of a database column?
[ Return to Table of Contents ]
[TABLE]
Oracle type codes for column dumps
Can I code a MATRIX Report using SQL?
[ Return to Table of Contents ]
Tree-structured (hierarchical) queries
[ Return to Table of Contents ]
NOTE: 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 hwo 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.
|
DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk. |
|
|||
