SQL

Stop Overlapping Ranges in a Table

Income tax brackets. Sales commission rates. Loan pre-payment penalty charges. Those are types of master data that get defined as range-based matrices. An important validation for such data is that the slabs should have no overlapping ranges: any input value should match only a single row in the master range definition.

When range-based matrices are stored in a database table, how can overlapping ranges be prevented? Read on to find out the solution for an Oracle database.

[click to continue…]

{ 0 comments }

LEAD/LAG Functions to Place a Value within Extents

LEAD/LAG are analytic functions that provide access to more than one row of a table at the same time, without a self join. Let’s see how.

Take a table that stores the master list of exam grades, mapped to the upper limit up to which the grade applies. The table (GRADE_MASTER) has two columns: {GRADE_CODE, SCORE_UPTO}. For exam scores in the range 0-100, GRADE_MASTER specifies the A-F.

SQL> desc grade_master
 Name              Null?    Type
 ----------------- -------- -----------
 GRADE_CODE        NOT NULL VARCHAR2(2)
 SCORE_UPTO        NOT NULL NUMBER(3)

SQL> select * from grade_master;

GR SCORE_UPTO
-- ----------
F          59
D          69
C          79
B          89
A         100

The above data means that grade F applies to scores 0-59, D applies to scores 60-69, C to scores 70-79, and so on.

To find the grade for a given the examination score, the SQL needs to compare the EXTENT values across *two* rows. Comparing a column’s value across more than one row can be tricky to implement – unless you turn to Oracle functions LEAD/LAG.

[click to continue…]

{ 0 comments }

How to Delete Old Data from a Database Schema

How would you delete old data from a database schema so that it retains only the current year’s rows in all the tables in the schema, and the rest of the data is erased?

[click to continue…]

{ 0 comments }

PIVOT in Oracle 11G to Select Rows As Columns

A frequent requirement in SQL is to "pivot" a result set — that is, display rows as columns. Before 11G, the developer had to do the equivalent of jumping through hoops to pivot the data. In 11G, with the introduction of the new PIVOT syntax, the task of transposing rows to columns has become a lot more intuitive.

This post shows the use of PIVOT with an example and sample scripts.

[click to continue…]

{ 7 comments }

INSERT ALL: Insert Multiple Rows with a Single INSERT Statement

A quick tip to get rid of the plodding way of inserting data into a table with multiple INSERT statements. Know what I mean? Say you have a table COLORS with this structure:

 Name             Type
 ---------------- ------------
 NAME             VARCHAR2(30)
 CATEGORY         VARCHAR2(10)

And you want to create this data in the table COLORS:

NAME                           CATEGORY
------------------------------ --------
yellow                         1
red                            1
blue                           1
yellow                         2
blue                           2

Hands up all who write insert statements this way:

insert into colors (name, category)
values ('yellow', 1);

insert into colors (name, category)
values ('red', 1);

insert into colors (name, category)
values ('blue', 1);

insert into colors (name, category)
values ('yellow', 2);

insert into colors (name, category)
values ('blue', 2);

[click to continue…]

{ 9 comments }

15 Things You Should Know about the ORDER BY Clause

When multiple columns/expressions are specified in the ORDER BY clause, the precedence of sorting is left to right.
The ORDER BY clause can order in ascending (ASC) or descending (DESC) sequence, or a mix of both. If ASC or DESC is not explicitly stated, then ASC is the default.
ORDER BY ASC places NULL values at the end of the query results. ORDER BY DESC places null values at the start of the query results.

[click to continue…]

{ 5 comments }

MERGE: Insert New Rows, Update Existing Rows in One Shot

Oracle’s MERGE statement is tailor-made for situations when you want to do an "upsert" i.e. update existing rows in a table or insert new rows depending on a match condition. This is typically the case when you have to synchronize a table periodically with data from another source (table/view/query). In place of 3 separate unwieldy INSERT, UPDATE and DELETE statements with conditional sub-queries, the all-in-one MERGE does the job in one shot.

[click to continue…]

{ 18 comments }

How to Manage those Pesky NULLs when Sorting Data

A typical query scenario: you want to sort data in descending order, say students arranged by their GMAT scores. Given a table student (id, name, score), what can be simpler than adding an ‘ORDER BY score DESC’ to the query?

If that’s what you thought, here’s a complication. There are some students who did not take the GMAT at all. Their scores in the table are not zero, they are NULL. Oracle’s ORDER BY..DESC in this situation could give you a nasty surprise with the result.

Here’s how the result will look with the ORDER BY…DESC clause:

[click to continue…]

{ 3 comments }