Oracle/SQL Server – On the fly data using DUAL for simple SQL tests – 100 level Basics

For SQL Server

Although these examples are to be run on Oracle, for SQL Server, simply remove the “FROM DUAL” and ROWNUM references, then replace || (for string concatenation) with “+” and you should be all set!

For Oracle

While this is an extremely basic tip, it illustrates a few things

  • A basic way to create data on the fly to test your SQL concepts without needing any permissions other than “CONNECT”
  • Built in ROWNUM column used to number rows
  • There is a built in ROWID column in every table which uniquely identifies each row but it cannot be used with “on the fly” data like this
  • The behavior of COUNT when dealing with NULL values and how numbers and character columns behave
  • Using ” (two single quotes) for a character column value will result in a NULL value for the column

Simply uncomment the SELECT’s at the bottom one at a time to observe the results in your favorite query editor after connecting to any Oracle database. At a later date, we will build on this concept to do more complex things. There are other ways to generate a lot of test data but this is customizable enough to test basic stuff quickly before doing the actual complex business implementation of the simple test!

The base data looks like this

EMPID EMPNAME ADDRESS ANNUALSALARY
1 Mike 61211
2 Ed 123 Main Street
3 Will 45 E.Lock Street 80394
4 Brad 1 32nd Street 45321
5 Alex 601 Broadway 76890
6 Sally
7 Tom 100234
WITH Data
AS
(
    SELECT 1 AS EmpId, 'Mike' AS EmpName, NULL AS Address, 61211 AS AnnualSalary
    FROM DUAL
    UNION ALL
    SELECT 2 AS EmpId, 'Ed' AS EmpName, '123 Main Street' AS Address, NULL AS AnnualSalary
    FROM DUAL
    UNION ALL
    SELECT 3 AS EmpId, 'Will' AS EmpName, '45 E.Lock Street' AS Address, 80394 AS AnnualSalary
    FROM DUAL
    UNION ALL
    SELECT 4 AS EmpId, 'Brad' AS EmpName, '1 32nd Street' AS Address, 45321 AS AnnualSalary
    FROM DUAL
    UNION ALL
    SELECT 5 AS EmpId, 'Alex' AS EmpName, '601 Broadway' AS Address, 76890 AS AnnualSalary
    FROM DUAL
    UNION ALL
    SELECT 6 AS EmpId, 'Sally' AS EmpName, NULL AS Address, NULL AS AnnualSalary
    FROM DUAL
    UNION ALL
    SELECT 7 AS EmpId, 'Tom' AS EmpName, '' AS Address, 100234 AS AnnualSalary
    FROM DUAL
)
SELECT rownum, a.*
FROM Data a

Now, we could do basic tests (for example some windowing function test cases) using the data by replacing the highlighted line above with our test SQL

Are you really a SQL expert? Think again!

Most of us consider ourselves as SQL experts. Are we really sure? Before running all these queries write down what you think will be the results and run the queries. See how wrong you may be because of NULL behavior when grouping and counting data (not to mention again that ” is NULL in Oracle and is NOT NULL in SQL Server). Also, see if the results are different between Oracle and SQL Server if you happen work on both!

--<<WITH Clause goes here>>
SELECT COUNT(1) FROM Data

--<<WITH Clause goes here>>
SELECT COUNT(*) FROM Data

--<<WITH Clause goes here>>
SELECT COUNT(EmpId) FROM Data

--<<WITH Clause goes here>>
SELECT COUNT(Address) FROM Data

--<<WITH Clause goes here>>
SELECT COUNT(1) FROM Data WHERE Address IS NULL

--<<WITH Clause goes here>>
SELECT COUNT(TO_CHAR(AnnualSalary)) FROM Data

--<<WITH Clause goes here>>
SELECT COUNT(EmpId ||TO_CHAR(AnnualSalary)) FROM Data

--<<WITH Clause goes here>>
SELECT COUNT(EmpId + AnnualSalary) FROM Data

--<<WITH Clause goes here>>
SELECT COUNT(Address||TO_CHAR(AnnualSalary)) FROM Data

--<<WITH Clause goes here>>
SELECT COUNT(DISTINCT Address) FROM Data

--<<WITH Clause goes here>>
SELECT COUNT(DISTINCT AnnualSalary) FROM Data

If you were wrong in guessing one or more results, then at least you have test data to quickly test what the result might be for a given scenario!

You are encouraged to comment on this post with interesting things you found.

Advertisements

6 thoughts on “Oracle/SQL Server – On the fly data using DUAL for simple SQL tests – 100 level Basics

  1. Really nice article,

    With Oracle was aware that NULL or Empty String ” are treated as NULL and but was not sure it behaviour differ in SQL Server.

    Oracle
    ‘A’||’1’ ‘A’||” ‘A’||NULL
    ——– ——- ———
    a1 a a

    SQL Server
    a1 a NULL

    Empty string is not treated as NULL.!
    Thanks for sharing!

  2. Oracle
    ‘A’||’1’ ‘A’||” ‘A’||NULL
    ——– ——- ———
    a1 a a
    SQL Server
    a1 a NULL
    Empty string is not treated as NULL.
    Same also differ in sql server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s