SQL

iggy_fernandez's picture

An introduction to Oracle Database for beginners: What is a relational database management system?—Part II

When you start thinking in terms such as security management, availability management, continuity management, change management, incident management, problem management, configuration management, release management, and capacity management, the business of database administration begins to make coherent sense and you become a more effective database administrator. (read more)

JonathanGennick's picture

Fear Nothing

Seventh in a series of posts in response to Tim Ford's #EntryLevel Challenge.

Image credit: Black-and-white sketch by Matt Williams, colorized for this post.


Null represents the absence of a value in a database column. Null means no value at all, and in that sense null can be thought of as nothing. Should you fear the nothing? Yes, indeed! Because nulls lead to three-valued logic, which is more like a three-headed monster because of all the unintended, counterintuitive, and often just plain wrong results it can cause.

Johnjayking's picture

Oracle 12c – Invisible Columns!

Oracle 12c – Invisible Columns

Beginning with Oracle 12c columns may be marked “INVISIBLE” in CREATE and ALTER TABLE statements.

#ff0000;">What the?!?!?!?!

Suppose you have code that uses “SELECT *” (no, really this sometimes occurs) and you need to add a column or columns? Oops! Code using “SELECT *” now breaks as does code using INSERT statements without a column list. (I know, ALL of your system’s code uses explicit column name lists in SELECT and INSERT ; this does not apply to your shop… (please excuse the sarcasm))

iggy_fernandez's picture

Believe it or Not: Converting an Inner Join to an Outer Join to improve performance

The cost-based optimizer tries to merge views whenever possible but sometimes we ma y want to override this behavior; that is, we may want the optimizer to push predicates into the view instead of merging the view into the main query. If the main query performs an inner join to the view, it becomes necessary to convert the inner join to an outer join if the database version is less than 12.1.0.2.(read more)

iggy_fernandez's picture

Happy Hinting: Undocumented PUSH_PRED hint variation

There isn’t a documented method of controlling which specific predicate to push.; it appears that the decision is left to the cost-based optimizer. However there is an undocumented method. Both the PUSH_PRED and NO_PUSH_PRED hints accept an optional second parameter(read more)

JonathanGennick's picture

Cross Joins

Sixth in a series of posts in response to Tim Ford's #EntryLevel Challenge.


Cross joins give all combinations of rows from two tables. They aren't normally useful, but they can be so in the right circumstances. One use for cross joins lies in generating large amounts of data for performance testing or similar purposes.

JonathanGennick's picture

Exceptional SQL

Fifth in a series of posts in response to Tim Ford's #EntryLevel Challenge.


SQL implements a number of so-called union operators that under the right circumstances can make queries easy to write and intuitive to read and understand. One of these is the EXCEPT operator that "subtracts" one set of rows from another. 

Say for example that you're doing some work on data quality and want to investigate products that your firm has sold without ever having first purchased. What have you sold but never bought? You can answer that question easily by executing the following EXCEPT query:

iggy_fernandez's picture

The best Oracle Database idea since 1992: Putting the C into ACID (We need your vote)

Oracle Rdb (only available for the VMS platform) supports SQL-92 assertions (http://community.hpe.com/hpeb/attachments/hpeb/itrc-149/22979/1/15667.doc) so why not Oracle Database? Let’s put the “C” into “ACID.”(read more)

JonathanGennick's picture

SQL Joinery

Fourth in a series of posts in response to Tim Ford's #EntryLevel Challenge.


SQL supports three types of join operation. Most developers learn the inner join first. But there are two other join operations you should know about. These are the outer join, and the full outer join. These additional join types allow you to write in essence could be termed as optional joins

Inner Joins

The so-called inner-join is the default. It's the happy path from a theory perspective, and it's the join type most SQL developers learn first. Use it to combine related rows from two or more tables. 

For example, perhaps you want to report on all the customers in the AdventureWorks database. You might begin working that business problem by writing the following query:

To prevent automated spam submissions leave this field empty.
Syndicate content