Northern California Oracle Users Group

Home » SQL Challenge » SQL Mini-challenge

SQL Mini-challenge

Advertisements

As published in the August 2014 issue of the NoCOUG Journal

Note: If you would like to take part in the challenge, please send an email request to sqlchallenge@nocoug.org and we will make you a guest contributor to the NoCOUG blog so that you can post your entry.

The inventor of the relational model, Dr. Edgar Codd, was of the opinion that “[r]equesting data by its properties is far more natural than devising a particular algorithm or sequence of operations for its retrieval. Thus, a calculus-oriented language provides a good target language for a more user-oriented source language” (Relational Completeness of Data Base Sublanguages). Therefore, with the exception of the Union operation, the original version of SQL was based on relational calculus, although, over time, other elements of relational algebra like difference (minus), intersection, and outer join crept in.

Testing of existence in a set using subqueries is the hallmark of relational calculus. The following example has nested subqueries. It lists the locations containing a department that either contains an employee named Steven King or an employee who holds the title of President or or employee who has previously held the title of President.

SELECT l.location_id, l.city
FROM locations l
WHERE EXISTS (
  SELECT * FROM departments d, employees e, jobs j
  WHERE d.location_id = l.location_id
  AND e.department_id = d.department_id
  AND j.job_id = e.job_id
  AND (
    (e.first_name = 'Steven' AND e.last_name = 'King')
    OR j.job_title  = 'President'
    OR EXISTS (
      SELECT * FROM job_history jh, jobs j2
      WHERE jh.employee_id = e.employee_id
      AND j2.job_id = jh.job_id
      AND j2.job_title = 'President'
    )
  )
);

The challenge is to rewrite the above query without subqueries and in the most efficient way possible, as measured by the number of consistent gets; that is, the Buffers column in the query execution plan. Answers must be posted on the NoCOUG blog https://nocoug.wordpress.com. Answers must be tested in the HR sample schema to prove that they actually work and must be accompanied by a query execution plan showing the number of consistent gets used by the rewritten query. The winner will receive a $75 Amazon gift certificate or a prize of equal value. The contest will close when a sufficient number of entries have been received. NoCOUG’s decisions are final. ▲

Update (August 13, 2014): There was a little ambiguity in the problem statement. To avoid ambiguity, the wording should have been “the challenge is to rewrite the above query without testing of existence in a set using subqueries” because the preamble indicated that the goal was to avoid existence testing using techniques such as EXISTS, IN, ALL, ANY, ALL, and SOME. Inline views are therefore permissible. Anything not explicitly disallowed by the problem statement is also permissible.

Advertisements

5 Comments

  1. […] An entry for the SQL Mini-challenge of NoCOUG August 2014: […]

    Like

  2. Vlado Barun says:

    Is DDL allowed, ie. to add indexes and/or change physical attributes of the tables?

    Like

  3. […] SQL challenge was posted recently on the blog of the Northern California Oracle user group, SQL Mini-challenge. A query was given against Oracle's demo HR schema, with […]

    Like

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

Next NoCOUG meeting
August 20 at Chevron, San Ramon

Follow me on Twitter

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 280 other followers

%d bloggers like this: