There was an error with one of our application, and I needed to find table rows with duplicate values. The table column was not defined to have a unique index, but we weren’t anticipating duplicate values. I needed an easier way to identify duplicate values in a table with simple SQL statement.

Consider the following “employee” table:

Assuming that we have an “employee” table with above values, and looking to find records with duplicate “Alias”. How do we retrieve them? With the following SQL statement with HAVING clause, we can easily accomplish that.


SELECT alias, count(alias) as count
FROM employee
GROUP BY alias
HAVING (count(alias) > 1)
ORDER BY alias


The above SQL statement will retrieve:

*NOTE: The HAVING clause allows SQL to use with aggregate functions with a condition whereas WHERE clause does not offer that functionality. For example, the HAVING clause can be used to retrieve SUM(x) > 100 or COUNT(y) > 1.

I need to grab last 100 rows of a MySQL table, and loop them through in reverse order. The array_reverse() PHP function won’t reverse the “resource” or “array” data types, so it isn’t as easy to reverse the array of “mixed” data type. The best way to achieve this is by using a SQL statement as shown below.

SELECT * FROM (SELECT * FROM mytable ORDER BY id DESC limit 100) AS foo ORDER BY id ASC;

You may also retrieve the MySQL result set in descending order, and traverse the set in reverse order.

/* fetch MySQL result set in reverse order */
for ($i = mysql_num_rows($resultset) – 1; $i >= 0; $i–) {
    mysql_data_seek($resultset, $i);
    $row = mysql_fetch_assoc($result);
    echo $row['abc'] . ‘ ‘ . $row['xyz'] . “\n”;
}

SQL*Plus is a command-line application that allows you to manage virtually every facet of the Oracle database. This article may be viewed as FAQs on SQL*Plus.

Before using SQL*Plus, there are a number of environment setting that you may want to configure. To toggle any of those settings, use the SET command shown below.

% sqlplus username
Enter Password: *****
SQL> SHOW ALL
SQL> SET {setting} ON/OFF

To turn on SQL query results to the terminal, set the following environment variable.

SQL> SET SERVEROUTPUT ON

If you expect your query to return a lot of data, use ‘SET PAUSE ON’ to scroll results one page at a time.

SQL > SET PAUSE ON
SQL > SET PAUSE “MORE…”

SQL*Plus stores last statement in a buffer, which may be edited or modified with the following commands:

– List command buffer
SQL> list
– Change old value with new to a buffered SQL statement
SQL> c/old/new
– Append text to a buffered SQL statement
SQL> a/more text …
– Execute the statement stored in a buffer
SQL> /
– To save buffered SQL statement in a file
SQL> SAVE FILE {filename}
– To execute commands from a file
SQL> START {filename}
OR
SQL> @{filename}

The Oracle export (exp) and import (imp) utilities are used to perform logical database backup and recovery, which allow you to write data into an operating system file and read back into the Oracle database. They are also used to move Oracle schema and data from one machine to another, or one database to another.

The exp/imp utilities use an Oracle proprietary binary file format and can thus only be used between Oracle databases. One cannot export data and expect to import it into a non-Oracle database.

Different versions of the import utility is upwards compatible. This means that one can take an export file created from an old export version, and import it using a later version of the import utility. Oracle also ships some previous catexpX.sql (X=Oracle Version) scripts that can be executed as user SYS enabling older imp/exp versions to work (for backwards compatibility). For example, one can run $ORACLE_HOME/rdbms/admin/catexp7.sql on an Oracle 8 database to allow the Oracle 7.3 exp/imp utilities to run against an Oracle 8 database.

EXPORT
Exporting an ORACLE database object is controlled by command-line parameters. To get familiar with EXPORT parameters, type the following in the SQL*Plus prompt:
exp help=y

Oracle EXPORT utility may be used in three ways:

• Interactive Mode: Simply type exp in command-line and answere the questions.
• With command-line parameters: exp <userid /password> <parameter>=<value>,<parameter>=<value>,…
• With parameters passed from a file: exp <userid /password> parfile=<filename>

IMPORT
Like EXPORT, the IMPORT utility is controlled by parameters. To get help with these parameters type: imp help=y

Using IMPORT utility is similar to using EXPORT utility as described above.

Local Variable Declaration

DECLARE @X INT
DECLARE @A INT, @B INT, @C CHAR(10)

A local variable is initially assigned a NULL value. A value can be assigned to a local variable by using the SET or SELECT statement.

DECLARE @X INT
SET @X = 1
SELECT @X = COUNT(*) FROM db.dbo.books

IF … ELSE statement

DECLARE @X INT
SET @X = 1
IF @X = 1 PRINT ‘X is 1′
ELSE PRINT ‘X is NOT 1′

use Northwind
IF db_name() = ‘Northwind’
BEGIN
PRINT ‘Using Northwind Database’
PRINT ‘Second Statement’
END

WHILE Statement

DECLARE @C INT
SET @C = 0
WHILE @C < 2
BEGIN
PRINT ‘C is ‘ + cast(@C as CHAR)
SET @C = @C + 1
END

BREAK and CONTINUE statements
The BREAK statement exits out of the inner most WHILE loop, and the CONTINUE statement skips executing the rest of the statements between the CONTINUE and the END statement of the current loop.

WHILE TRUE
BEGIN
BREAK
END

WHILE (@I <10)
BEGIN
PRINT ‘I is ‘ + @I
IF (@I >3) CONTINUE
END

GOTO Statement

WHILE TRUE
BEGIN
GOTO ONE
END

ONE:
PRINT ‘Out of the Loop’

CASE Statement
The CASE statement allows you to replace a column value with a different value based on the original value. For example, a table column named gender may contain a value 0 representing female, and 1 representing male. The CASE statement allows you to translate value 0 to ‘Female’ and 1 to ‘Male’.

SELECT Name,
CASE
when gender = 0 then ‘Female’
when gender = 1 then ‘Male’
else ‘Unknown’
END as “Gender”
from db.dbo.Customer

Create Table/ Drop Table

create table MYTABLE (id int, name varchar(10), value varchar(100))
drop table MYTABLE