Index Search Add FAQ Ask Question |
---|
$Date: 07-Mar-2000 $
$Revision: 1.80 $
$Author: Frank Naudé $
Topics
In common usage SQL also encompasses DML (Data Manipulation Language), for INSERTs, UPDATEs, DELETEs and DDL (Data Definition Language), used for creating and modifying tables and other database structures.
The development of SQL is governed by standards. A major revision to the SQL standard was completed in 1992, called SQL2. SQL3 support object extensions and will be (partially?) implemented in Oracle8.
Method 1:
Method 2:
SQL> DELETE FROM table_name A WHERE ROWID > (
2 SELECT min(rowid) FROM table_name B
3 WHERE A.key_values = B.key_values);
Method 3: (thanks to Kenneth R Vanluvanee)
SQL> create table table_name2 as select distinct * from table_name1;
SQL> drop table_name1;
SQL> rename table_name2 to table_name1;
Method 4: (thanks to Dennis Gurnick)
SQL> Delete from my_table where rowid not in(
SQL> select max(rowid) from my_table
SQL> group by my_column_name );
Note: If you create an index on the joined fields in the inner loop, you for all
intensive purposes eliminate N^2 operations (no need to loop through the
entire table on each pass by a record).
SQL> delete from my_table t1
SQL> where exists (select 'x' from my_table t2
SQL> where t2.key_value1 = t1.key_value1
SQL> and t2.key_value2 = t1.key_value2
SQL> and t2.rowid > t1.rowid);
SQL> UPDATE table_name SET seqno = ROWNUM;
or use a sequences generator:
SQL> CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;
SQL> UPDATE table_name SET seqno = sequence_name.NEXTVAL;
Finally, create a unique index on this column.
select floor((date1-date2)*24*60*60)/3600)
|| ' HOURS ' ||
floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600 -
(floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))
|| ' SECS ' time_difference
from ...
select dept, sum( decode(sex,'M',1,0)) MALE, sum( decode(sex,'F',1,0)) FEMALE, count(decode(sex,'M',1,'F',1)) TOTAL from my_emp_table group by dept;
select f2, count(decode(greatest(f1,59), least(f1,100), 1, 0)) "Range 60-100", count(decode(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59", count(decode(greatest(f1,29), least(f1, 0), 1, 0)) "Range 00-29" from my_table group by f2;For equal size ranges it migth be easier to calculate it with DECODE(TRUNC(value/range), 0, rate_0, 1, rate_1, ...). Eg.
select ename "Name", sal "Salary", decode( trunc(f2/1000, 0), 0, 0.0, 1, 0.1, 2, 0.2, 3, 0.31) "Tax rate" from my_table;
SELECT f1 FROM t1 WHERE rowid = ( SELECT rowid FROM t1 WHERE rownum <= 10 MINUS SELECT rowid FROM t1 WHERE rownum < 10);Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.
SELECT * FROM tableX WHERE rowid in ( SELECT rowid FROM tableX WHERE rownum <= 7 MINUS SELECT rowid FROM tableX WHERE rownum < 5);Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.
Method 1: Using a subquery
SELECT * FROM emp WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4) FROM emp);Method 2: Use dynamic views (available from Oracle7.2):
SELECT * FROM ( SELECT rownum rn, empno, ename FROM emp ) temp WHERE MOD(temp.ROWNUM,4) = 0;Please note, there is no explicit row order in a relational database. However, these queries are quite fun and may even help in the odd situation.
SELECT * FROM (SELECT * FROM my_table ORDER BY col_name_1 DESC) WHERE ROWNUM < 10;Use this workaround with prior releases:
SELECT * FROM my_table a WHERE 10 >= (SELECT COUNT(DISTINCT maxcol) FROM my_table b WHERE b.maxcol >= a.maxcol) ORDER BY maxcol DESC;
The definitive example is in the example SCOTT/TIGER database, when looking at the EMP table (EMPNO and MGR columns). The MGR column contains the employee number of the "current" employee's boss.
You have available an extra pseudo-column, LEVEL, that says how deep in the tree you are. Oracle can handle queries with a depth up to 255.
select LEVEL, EMPNO, ENAME, MGR from EMP connect by prior EMPNO = MGR start with MGR is NULL;You can get an "indented" report by using the level number to substring or lpad a series of spaces and concatenate that to the string.
select lpad(' ', LEVEL * 2) || ENAME ........You use the start with clause to specify the start of the tree(s). More than one record can match the starting condition. One disadvantage of a "connect by prior" is that you cannot perform a join to other tables. Still, I have not managed to see anything else like the "connect by prior" in the other vendor offerings and I like trees. Even trying to doing this programmatic ally in embedded SQL is difficult as you have to do the top level query, for each of them open a cursor to look for child nodes, for each of these open a cursor .... Pretty soon you blow the cursor limit for your installation.
The way around this is to use PL/SQL, open the driving cursor with the "connect by prior" statement, and the select matching records from other tables on a row-by-row basis, inserting the results into a temporary table for later retrieval.
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 scott.emp 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
Some examples:
select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown') from employees; select a, b, decode( abs(a-b), a-b, 'a > b', 0, 'a = b', 'a < b') from tableX; select decode( GREATEST(A,B), A, 'A is greater than B', 'B is greater than A')...Note: The decode function is not ANSI SQL and are rarely implemented in other RDBMS offerings. It is one of the good things about Oracle, but use it sparingly if portability is required.
SELECT DUMP(col1)
FROM tab1
WHERE cond1 = val1;
DUMP(COL1)
-------------------------------------
Typ=96 Len=4: 65,66,67,32
For this example the type is 96, indicating CHAR, and the last byte in the column is 32, which is the ASCII code for a space.
This tells us that this column is blank-padded.
With previous releases one can use Joseph S. Testa's DROP COLUMN package that can be downloaded from http://www.oracle-dba.com/ora_scr.htm.
Other workarounds:
1. SQL> update t1 set column_to_drop = NULL; SQL> rename t1 to t1_base; SQL> create view t1 as select <specific columns> from t1_base; 2. SQL> create table t2 as select <specific columns> from t1; SQL> drop table t1; SQL> rename t2 to t1;
1. rename t1 to t1_base; create view t1 <column list with new name> as select * from t1_base; 2. create table t2 <column list with new name> as select * from t1; drop table t1; rename t2 to t1;
ALTER USER <username> IDENTIFIED BY <new_password>
/
From Oracle8 you can just type "password" from SQL*Plus, or if you need to change another user's password, type "password user_name".
You can use the above technique to prevent sequence number loss before a SHUTDOWN ABORT, or any other operation that would cause gaps in sequence values.
Note: If you run Oracle8, convert your LONGs to LOBs, as it can be replicated.
COPY TO SCOTT/TIGER@REMOTE -
CREATE IMAGE_TABLE USING -
SELECT IMAGE_NO, IMAGE -
FROM IMAGES;
General: |
Home |
Index |
Preamble |
Glossary |
OraCorp |
Papers |
Fun |
News |
Events |
Y2000 |
Books |
Links |
Forums
Products: |
SQL |
Plus |
Loader |
PL/SQL |
PreComp |
OPO |
OMO |
OO4OLE |
DBA |
PQO |
PSO |
OCO |
Net |
ODBC |
WebServer |
Des2k |
Dev2k
| Systems: |
MVS |
Unix |
Windows |
WindowsNT |
NetWare |
VMS
| |