---

The SQL query pre-processor

---

---

---

 

Slideshow:

Semantic errors in a query
 
Tasks performed by the SQL pre-processor
 
Parsing an SQL query that contains a virtual relation
 
Review: a virtual relation (= table) is defined/created using a query:
 
Parsing an SQL query that contains a virtual relation -- Example
Definition of a virtual table:
 
Example query containing a virtual table:
 
The initial parse tree:

Parsing an SQL query that contains a virtual relation -- Example
 
SQL query pre-processor will replace the virtual table by its query:
 
(The query plan will then be re-written further --- discussed later)
❮ ❯



---

---

---

---

• Pre-processing an SQL query
  • Example of a query:

    SELECT fname, dno FROM employee, department WHERE dnumber = dno AND birthday LIKE '%1960'

    Query can have semantical errors:

    Does the database contain the relation employee (or department) ??? Does the attribute dno (or fname) exist in the specified relations (employee and department) ??? If it does, which relation does dno belong to ??? And so on

    ---

    ---

  • Tasks in pre-processing an SQL query:

    Check whether the relations used in the FROM clause exist in the database. Check and resolve each attributes used in the query Which relation does the attribute belong to ? Check the data types and correct usage of the attributes Can some operation be applied to the attribute ??? (E.g.: + operation requires a number type) Replace the virtual relations (views) by their corrsponding SQL query !!!

  ---

  ---

  ---

  ---

• Example: virtual relation pre-processing
  • Virtual table definition:

    CREATE VIEW Paramount_Movies AS (SELECT title, year FROM Movies WHERE StudioName = 'Paramount')

    The SELECT query is equivalent to the following logical query plan:

    ---

    ---

  • Consider a query that uses the virtual table Paramount_Movies:

    SELECT title FROM Paramount_Movies // Virtual table !! WHERE year = 1979

    The Query Processor will first parse the query and create the following logical query plan:

    Then, the SQL query preprocessor will replace the virtual table with its corresponding logical query plan: