---

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)

  • 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 (The logical data independence is not discussed in CS554) Miscelaneous features: Manage users (accounts, privilages/authorizations) Create and delete indices of files (= tables)

  ---

  ---

  ---

  ---

  ---

  ---

  ---

  ---

• 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))

  ---

  ---

  ---

  ---

  ---

  ---

  ---

  ---

• What do we cover in SQL

  • Features available in the SQL (programming) language:

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

    ---

    ---

  • Coverage in CS554:

    Query (SELECT)         View definition