[ Prev ] [ Index ] [ Next ]

SQL

Created Friday 28/05/2007

This is a document of tips and examples for interactive SQL. Any implemenation-dependent idocsyncrasies that exist have a propensity to Oracle SQL and are pointed out where possible. In this document, SQL> refers to an sqlplus command prompt, however any the queries should work with interactive sql interpretor unless stated otherwise.

1. Show duplicate rows

This query selects for duplicate rows in a table. A count of the number of rows of each non-distinct type are also produced. This query uses a combination of GROUP BY and HAVING to produce control-break style output. The query is over a single columned table called numbertable.

Table schema and content:
    SQL> create table numbertable(a int);
    SQL> insert into numbertable values ( 100 );
    SQL> insert into numbertable values ( 100 );
    SQL> insert into numbertable values ( 100 );
    SQL> insert into numbertable values ( 400 );
    SQL> insert into numbertable values ( 600 );
    SQL> insert into numbertable values ( 600 );

SQL>select * from numbertable;
         A
----------
       100
       100
       100
       400
       600
       600
6 rows selected.

The data above shows that column A has 1 distinct value (the row with value A=400). The expected output is 2 rows, with A=100 (count is 3) and with A=600 (count is 2).

SQL> select count(A),A from numbertable group by A having count(A) > 1;
  COUNT(A)          A
  -------- ----------
         3        100
         2        600

2. System date

The current system date known to the DBMS can be extracted using the DUAL psuedo table, using the psuedo column sysdate.

SQL> select sysdate from dual;
SYSDATE
---------
14/FEB/08

Backlinks: :Home :sqlplus