SQL

SQL (Structured Query Language) is a standard language used to operate relational databases. It includes operations such as database creation, retrieving and modifying data, granting privileges to data.

Thumbnail image for Stop Overlapping Ranges in a Table

Stop Overlapping Ranges in a Table

Overlapping Ranges Blocker Toolkit: trigger-based solution to prevent overlapping ranges of data in an Oracle master table that stores range-based data, such as income tax brackets or sales commission rates.

Read the Full Article →
Thumbnail image for LEAD/LAG Functions to Place a Value within Extents

LEAD/LAG Functions to Place a Value within Extents

Analytic functions LEAD and LAG provide access to more than one row of a table at the same time, without a self join. This feature can be used to find the position of a value within extents, where the extents are defined by the upper bound only and the lower bound is derived via comparison to the previous/next row.

Read the Full Article →
Thumbnail image for How to Delete Old Data from a Database Schema

How to Delete Old Data from a Database Schema

DELETE script or CTAS DROP-RENAME script: ways to purge 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.

Read the Full Article →
Thumbnail image for PIVOT in Oracle 11G to Select Rows As Columns

PIVOT in Oracle 11G to Select Rows As Columns

SQL to PIVOT a result set in Oracle — that is, display rows as columns. Pre-11G solution and a simple example using the 11G+ PIVOT syntax.

Read the Full Article →
Thumbnail image for INSERT ALL: Insert Multiple Rows with a Single INSERT Statement

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: And you want to create this data in the table COLORS: Hands up all who write insert statements this way:

Read the Full Article →
Thumbnail image for 15 Things You Should Know about the ORDER BY Clause

15 Things You Should Know about the ORDER BY Clause

What is the precedence of sorting in a multi-column ORDER BY? How does ORDER BY sort nulls? What does ORDER SIBLINGS BY do? Answers to these questions and more about Oracle’s ORDER BY clause.

Read the Full Article →
Thumbnail image for MERGE: Insert New Rows, Update Existing Rows in One Shot

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

MERGE in Oracle combines the power of INSERT and UPDATE into one power-packed statement

Read the Full Article →
Thumbnail image for How to Manage those Pesky NULLs when Sorting Data

How to Manage those Pesky NULLs when Sorting Data

How to position NULL values in the sort column – send them to the top or the bottom in the result set with this ORDER BY NULLs FIRST or LAST.

Read the Full Article →
Thumbnail image for DISTINCT and How NOT To Use It

DISTINCT and How NOT To Use It

Oracle keyword DISTINCT restricts the result to unique rows from a query, but DISTINCT is often used as a quick fix to bad queries. Some practices to avoid when using DISTINCT.

Read the Full Article →
Thumbnail image for LTRIM and TO_CHAR…and a “Strange” Query

LTRIM and TO_CHAR…and a “Strange” Query

When we place the output of TO_CHAR(102.3, ‘000000.0’) as the input of the LTRIM function with ‘0’, we expect to get 102.3. That’s not how it works though – the zeroes on the left do not get trimmed. Can you tell why this is so? This article gives you the answer.

Read the Full Article →
Page 3 of 6
1
2
3
4
5
6