[ 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: sqlplus Home