---

SQL: Structured Query Language

---

• Introduction.
  • So now that you have learned how to formulate queries in a mathematical language (relational algebra), it is time to put it into practice

  • The most commonly used language to interface with a DB system is the Structured Query Language (SQL)

  • SQL is now an international standard x

    ---

  • SQL is closedly related to relational algebra (and relational calculus)

  • A "case-in-point" Example: Find FName, LName of employees in the "Research" department

    Relational Algebra:

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

    SQL:

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

    ---

    ---

  • There is clearly a correlation between Relational Algebra and SQL:

    The FROM clause specifies a cartesian product The WHERE clause specifies a selection operation The SELECT clause specifies a projection operation

  ---

  ---

  ---

  ---

  ---

• Features of SQL.
  • SQL is not only a query language - i.e., a language used to retrieve information from a database

    ---

    ---

  • SQL also contains these features:

    a Data Definition Language Data Definition Language = a (programming) language that is used to define the data model of a database a Data Manipulation Language Data Manipulation Language = a (programming) language that is used to insert update/delete data into the data model of the database a View Definition Language, View Definition Language = a (programing) language where users can define views (= external schemas) that are used to provide logical data independence (For details on logical data independence, see: click here ) Miscelaneous features: Manage users (accounts, privilages/authorizations) Create and delete indices of files (= tables)

  ---

  ---

  ---

  ---

• How is SQL used.
  • Stand-alone
    user enters SQL commands - (command line or in a GUI)

  • Embedded in a host langauge
    SQL commands are embedded (written inside) an "ordinary" program in a high level language (e.g., C, C++, Cobol, Java, etc)

  • Library-based
    SQL commands are made available through library functions (e.g., Java)

  • Web-based
    Various languages has SQL extensions that allows webpages to access a database server

  ---

  ---

  ---

  ---

  ---

  ---

  ---

  ---

• Duplicate tuples in SQL outputs
  • Note:

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

    Example:

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

    ---

  • Reason:

    Detecting duplicate rows will require additional processing It is more efficient to leave duplicates alone.

    ---

  • Comment:

    You will (later) learn an SQL command to request the removal of duplicate rows (tuples))

  ---

  ---

  ---

  ---

  ---

  ---

  ---

  ---

• Order of Discussion.

  Data definition Query (SELECT) Data update (INSERT, DELETE, UPDATE) View definition

  (In CS544: mainly as a quety language and skip the delete/update commands)