Intro: SQL (Structured Query Language)

  • Query:

    • Find FName, LName of employees in the "Research" department

  • Solution in Relational Algebra:

    Or: (written without using the join-operation (which is a short hand):

  • Solution in SQL: 

       SELECT fname, lname
   FROM   employee, department         
   WHERE  dno=dnumber
     AND  dname='Research'

SQL features

  • SQL is a query language

    • a programming language used to retrieve information from a database

  • SQL is a data definition language (DDL)

    • a programming language used to define the data model in a database

  • SQL is a data manipulation language (DML)

    • a programming language used to insert/update/delete data stored in a database

  • SQL is a view definition language (VDL)

    • a programming language used to define "views" (a.k.a. external schemas) that are used to provide logical data independence

      (Logical data independence is outside the scope of CS554)

A note on SQL output: bags (with duplicates)

  • Important note:

    • By default SQL will not remove duplicate tuples (= rows) in the output

    Example: 

       SELECT name FROM depedent

   name       
   ---------- 
   Alice      
   Theodore   
   Joy        
   Abner      
   Micheal    
   Alice      
   Elizabeth

  • Reason:

    • Detecting duplicate rows will require additional processing

    • It is more efficient to leave duplicates alone.

Extend of coverage of SQL in CS554

  • Features in SQL (programming) language:

    1. Data definition

    2. Query (SELECT)

    3. Data update (INSERT, DELETE, UPDATE)

    4. View definition

  • Features used (and reviewed) in CS554:

    • Query (SELECT)

    • View definition