Home > DBA, Oracle, SQL > The 12 Days of SQL: 12 Things Every Oracle DBA and Developer Should Know About SQL (Day 1)

The 12 Days of SQL: 12 Things Every Oracle DBA and Developer Should Know About SQL (Day 1)


Inspired by Andrew Zitelli’s presentation Four Things Every DBA and Developer Should Know About Oracle at NoCOUG Conference #100 at the Computer History Museum (Conference 100 Report: Pictures at an Exhibition)

On the first day of Christmas, my true love gave to me
A partridge in a pear tree.

Day 1: SQL is based on relational calculus and relational algebra (Day 2: SQL is a non-procedural language)

As explained by IBM researcher Dr. Edgar (Ted) Codd in his seminal 1970 paper “A Relational Model of Data for Large Shared Data Banks” (reproduced in its entirety in the 100th issue of the NoCOUG Journal), the power of the relational model comes from the ability to specify data requirements by writing a “relational algebra” expression using relational operations (operations on sets of data) such as Restrict (a subset of rows is targeted), Project (a subject of columns is targeted), Union, Minus, Intersect, Cross Join, Theta Join (a combination of the Cross Join and Restrict operations), Antijoin, Semijoin, Outer Join, and Division.

Dr. Codd published a subsequent paper “Relational Completeness of Data Base Sublanguages” in which he outlined a non-procedural method of specifying data requirements using “quantifiers” such as Exists. He called this method “relational calculus” and showed that how a database engine like Oracle could convert non-procedural relational calculus expressions into equivalent relational algebra expressions. At the end of the paper he declared that “Clearly, the majority of users should not have to learn either the relational calculus or algebra in order to interact with data bases. However, requesting 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.”

Two of Codd’s IBM colleagues, Donald Chamberlin and Raymond Boyce, then created a language called SEQUEL (Structured English Query Language) using the ideas of relational calculus (http://faculty.cs.tamu.edu/yurttas/PL/DBL/docs/sequel-1974.pdf). The acronym was shortened to SQL when it was discovered that SEQUEL was someone else’s registered trademark. SQL was not intended to be a rigorous implementation of relational calculus—it was originally intended for the use of “accountants, engineers, architects, and urban planners” who, “while they are not computer specialists, would be willing to learn to interact with a computer in a reasonably high-level, non-procedural query language”—and had flaws which persist to this day; for example, it permitted duplicates. Also, it used the Union, Minus, and Intersect operations which are relational operations. Over time, explicit support for other relational operations such as Cross Join, Theta Join, and Outer Join was added. SQL is therefore a redundant hybrid of relational calculus and relational algebra.

Here are some examples from the Human Resources sample schema (ER diagram)

Employees who earn more than $10,000 (the relational calculus version)

select
  d.department_id, d.department_name,
  e.employee_id, e.first_name, e.last_name, e.salary
from employees e, departments d
where e.salary > 10000
and e.department_id = d.department_id

The above expression does not explicitly mention the need to join the employee and department tables; it is the task of the database engine to find a sequence of relational operations that can produce the required data.

Employees who earn more than $10,000 (the relational algebra version)

select
  d.department_id, d.department_name, -- Project operation on the Departments table
  e.employee_id, e.first_name, e.last_name, e.salary -- Project operation on the Employees table
from departments d join employees e on (e.department_id = d.department_id) -- Theta Join operation on the Employees and Departments tables
where e.salary > 10000 -- Restriction operation on the Employees table

The above expression contains two Project operations (one each on the Employees and Departments tables), one Theta Join operation, and one Restrict operation.

Managers who earn less than one of their employees (the relational calculus version)

select m.employee_id, m.first_name, m.last_name
from employees m
where exists (
  select * from employees e
  where m.employee_id = e.manager_id
  and m.salary < e.salary
)

Notice the use of the Exists quantifier in the above expression.

Managers who earn less than one of their employees (the relational algebra version)

select distinct m.employee_id, m.first_name, m.last_name
from employees m join employees e on (m.employee_id = e.manager_id)
where m.salary < e.salary

The above expression implicitly requires at least two Project operations; can you find them?

Employees who have worked in every department (the relational calculus version)

select e.employee_id, e.first_name, e.last_name
from employees e
where not exists (
  select * from departments d
  where (d.department_id != e.department_id and e.department_id is not null)
  and not exists (
    select * from job_history j
    Where j.employee_id = e.employee_id
    and j.department_id = d.department_id
  )
)

Employees who have worked in every department (the relational algebra version)

Left as an exercise🙂 Rewrite without using the Exists quantifier.

A rigorous treatment of relational algebra and relational calculus can be found in Database System Concepts by Korth and Silberschatz. As Leonardo da Vinci said in The Discourse On Painting “Those who are in love with practice without knowledge are like the sailor who gets into a ship without rudder or compass and who never can be certain whether he is going. Practice must always be founded on sound theory.”

Day 2: SQL is a non-procedural language

Categories: DBA, Oracle, SQL
  1. December 2, 2011 at 9:07 am

    Is there a null issue conflict in your exercise example? Employees with null department_id have been working in all departments???

    insert into employees(department_id,employee_id,first_name,last_name,email,hire_date,job_id)
    values (null,(select max(employee_id)+1 from employees),’John’,’Doe’,’john.doe@email.address’,sysdate,(select job_id from employees where rownum=1))
    ;

    select e.employee_id, e.first_name, e.last_name
    from employees e
    where not exists (
    select * from departments d
    where (d.department_id != e.department_id and e.department_id is not null)
    and not exists (
    select * from job_history j
    Where j.employee_id = e.employee_id
    and j.department_id = d.department_id
    )
    )
    ;

    rollback;

    The homework for the query…

    select e.employee_id, e.first_name, e.last_name, e.department_id
    from employees e
    left outer join departments d
    on d.department_id != e.department_id
    and e.department_id is not null
    left outer join job_history j
    on j.employee_id = e.employee_id
    and j.department_id = d.department_id
    where j.employee_id is null
    and d.department_id is null
    ;

  2. Iggy Fernandez
    December 2, 2011 at 5:15 pm

    Timo Raitalaakso :

    Is there a null issue conflict in your exercise example? Employees with null department_id have been working in all departments???

    The example is from the Human Resources sample schema. Here are the descriptions of the employees and job_history tables.

    SQL> describe employees
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     EMPLOYEE_ID                               NOT NULL NUMBER(6)
     FIRST_NAME                                         VARCHAR2(20)
     LAST_NAME                                 NOT NULL VARCHAR2(25)
     EMAIL                                     NOT NULL VARCHAR2(25)
     PHONE_NUMBER                                       VARCHAR2(20)
     HIRE_DATE                                 NOT NULL DATE
     JOB_ID                                    NOT NULL VARCHAR2(10)
     SALARY                                             NUMBER(8,2)
     COMMISSION_PCT                                     NUMBER(2,2)
     MANAGER_ID                                         NUMBER(6)
     DEPARTMENT_ID                                      NUMBER(4)
    
    SQL> describe job_history
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     EMPLOYEE_ID                               NOT NULL NUMBER(6)
     START_DATE                                NOT NULL DATE
     END_DATE                                  NOT NULL DATE
     JOB_ID                                    NOT NULL VARCHAR2(10)
     DEPARTMENT_ID                                      NUMBER(4)
    

    Since the start_date and end_date columns in the job_history table are non-null, an entry in the job_history table indicates that an employee has previously worked under that job title in that department. It is the job_id and department_id columns in the Employees table which hold the current job title and department of the employee. However, the department_id column of the employees table is allowed to be null.

  3. Iggy Fernandez
    December 2, 2011 at 6:34 pm

    Timo Raitalaakso :

    Is there a null issue conflict in your exercise example? Employees with null department_id have been working in all departments???

    Here is the comment stored with the table definition.

    SQL> select comments from user_tab_comments where table_name='JOB_HISTORY';
    
    COMMENTS
    --------------------------------------------------------------------------------
    Table that stores job history of the employees. If an employee
    changes departments within the job or changes jobs within the department,
    new rows get inserted into this table with old job information of the
    employee. Contains a complex primary key: employee_id+start_date.
    Contains 25 rows. References with jobs, employees, and departments tables.
    
  1. December 14, 2015 at 9:57 pm
  2. December 27, 2015 at 8:28 pm
  3. December 29, 2015 at 9:00 am

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

%d bloggers like this: