The SQL Relational Database Standard by shameona

VIEWS: 0 PAGES: 68

									The SQL Relational Database
        Standard
          Lecture 6
Why a Standard Query Language?

   APP1         APP2          APP3



      Standard for DBMS query language


                           My-
     Ora       DB2
                           SQL
               About SQL
• Stands for Structured Query Language
• Developed at IBM Research for System R
• Includes features of relational algebra and
  tuple relational calculus
• The standard for relational data access
• DBMS independent
• Both a Data definition language (DDL) and
  a Data manipulation language (DML)
              Terminology
Relational Algebra          SQL

    Relation                Table

      Tuple                 Row

    Attribute           Column
           Schema and Catalogs
Support for creating and maintaining schemas are provided
from SQL2. A schema is created by a CREATE SCHEMA
command. For example:

CREATE SCHEMA UNIV AUTHORIZATION dean;

Creates a schema “UNIV” owned by the user with userid “dean”.

A catalog is a named collection of schemas. Referential integrity
constraints can be defined across schemas only if they belong to
the same catalog.
                 Table Creation
A new relation can be created using the CREATE TABLE
command. The general syntax is as follows:


CREATE TABLE [schema.]table_name [{column descriptions}];

Example:

CREATE TABLE UNIV.DEPARTMENT;
 Data Types and Domains (SQL2)
Primitive data types in SQL2 include:

  Numeric (int, smallint, float, real, double precision)
  Formatted numbers (decimal(i,j), numeric(i,j))
  Character string of either fixed length (char(n)) or
    of varying length (varchar(n))
  Bit strings of either fixed length (bit(n)) or varying length
    (bit varying(n))
  Date, time and timestamp
  Text
  …
 Data Types and Domains (SQL2)
Non-standard domains may be defined using the CREATE
DOMAIN construct. For example:

CREATE DOMAIN RollNoType AS INT(6);

The domain RollNoType will be substituted by INT(6)
wherever used.
  Constraints and Default Values
• The NOT NULL constraint on an attribute disallows NULL
as a valid value

• The DEFAULT construct specifies the default value of an
attribute

• The PRIMARY KEY construct specifies one or more
attributes as the primary key

• The UNIQUE construct specifies alternate (secondary) keys

• The FOREIGN KEY construct ensures referential integrity
   Table Creation Example
CREATE TABLE EMPLOYEE (
PANNO      VARCHAR(16) NOT NULL, UNIQUE,
EMPNO        INT(6) NOT NULL,
NAME       VARCHAR (40),
GENDER       CHAR,
DOB          DATE,
ADDRESS     VARCHAR(100),
SALARY       DECIMAL(8,2),
REPORTSTO    INT(6),
PRIMARY KEY (EMPNO),
FOREIGN KEY (REPORTSTO) REFERENCES
             EMPLOYEE (EMPNO));
                    Table Deletion
Tables can dropped (deleted) using the DROP TABLE command:

DROP TABLE NAME [DEPENDENT {CASCADE | RESTRICT}];

The above command drops the table specified by NAME. If the
CASCADE option is provided, then any foreign-key constraints
and views that reference the table will also be dropped.

If RESTRICT option is specified, then the table is dropped only if
 it does not have any incoming references.
                  Schema Deletion
Schemata can dropped (deleted) using the DROP SCHEMA
command:

DROP SCHEMA NAME {CASCADE | RESTRICT};

The above command drops the schema specified by NAME. If the
CASCADE option is provided, then all tables in the schema will be
automatically dropped.

If RESTRICT option is specified, then the schema is dropped only if
 it does not have any elements.
              Modifying Tables
Table definitions can be modified using the ALTER TABLE
command. Example:

 ALTER TABLE COMPANY.EMPLOYEE
          ADD JOB VARCHAR(20);

..adds a new column to the EMPLOYEE table with the name
 job and domain VARCHAR of maximum size 20 characters.
               Modifying Tables
Consider the following example:

 ALTER TABLE COMPANY.EMPLOYEE
          ADD JOB VARCHAR(20);

Unless a DEFAULT clause is specified, the value of the JOB
attribute for all the tuples will be NULL.

If no DEFAULT clause is specified, then the NOT NULL
 constraint cannot be used.
              Modifying Tables
To drop columns, either the CASCADE or RESTRICT options
should be used:

  ALTER TABLE COMPANY.EMPLOYEE
                 DROP PANNO CASCADE;

If CASCADE option is used, then all constraints and views that
 refer to the column (PANNO in this case) are also dropped
 automatically.

If RESTRICT is used, then the column is dropped only if it does
 not have any referencing constraints or views.
               Modifying Tables
It is possible to alter a column definition by adding or dropping
DEFAULT clauses:

     ALTER TABLE COMPANY.EMPLOYEE
         ALTER REPORTSTO SET DEFAULT “007”;
or
     ALTER TABLE COMPANY.EMPLOYEE
         ALTER REPORTSTO DROP DEFAULT;

All NULL values for a column are updated whenever a new
DEFAULT is set.
      The SELECT Operation
• The basic retrieval operation in SQL
• Has no relationship with the SELECT
  operation in Relational Algebra
• SQL SELECT allows multiple occurrences
  of the same tuple. (Views the relation as a
  multi-set rather than a set)
           The SELECT Operation
The SELECT-FROM-WHERE form:

      SELECT <attribute list>
      FROM <table list>
      WHERE <condition>;

Example:

      SELECT EMPNO, NAME FROM EMPLOYEE
           WHERE REPORTSTO = „007‟;
        The SELECT Operation
The SELECT-FROM-WHERE form can act on multiple tables
as well. Consider the table DEPARTMENT shown below:

CREATE TABLE DEPARTMENT (
DNO         INT(6) NOT NULL,
NAME       VARCHAR(20),
ADDRESS     VARCHAR(60),
HEAD        INT(6),
PRIMARY KEY (DNO),
FOREIGN KEY HEAD
   REFERENCES EMPLOYEE(EMPNO));
          The SELECT Operation
Consider the following query: What is the name of the person
who heads the “SUPPLIES” department?

SELECT EMPLOYEE.NAME
FROM   EMPLOYEE, DEPARTMENT
WHERE EMPNO = HEAD
       AND
       DEPARTMENT.NAME = „SUPPLIES‟;

Note the use of the table name in order to disambiguate attributes
having the same name. Table names need not be used where there
is no ambiguity.
         The SELECT Operation
What happens when the same table has to be used multiple times?
Consider the query: What is the name of the person to whom
Arvind Kulkarni reports to?

SELECT EMPLOYEE.NAME
FROM   EMPLOYEE, EMPLOYEE
WHERE EMPLOYEE.NAME = „Arvind Kulkarni‟
       AND
       EMPLOYEE.REPORTSTO = EMPLOYEE.EMPNO;



                                    Ambiguous!
          The SELECT Operation
The solution is to use “aliasing” of table names:

SELECT BOSS.NAME
FROM   EMPLOYEE, EMPLOYEE AS BOSS
WHERE EMPLOYEE.NAME = „Arvind Kulkarni‟
       AND
       EMPLOYEE.REPORTSTO = BOSS.EMPNO;
         The SELECT Operation
When the WHERE clause is omitted, SELECT acts as the project
operator of relational algebra:

SELECT NAME, PANNO
FROM   EMPLOYEE;

Returns all rows in EMPLOYEE, but only the NAME and PANNO
columns.
          The SELECT Operation
When more than one table is specified and WHERE is omitted, the
result is equivalent to a project over the cross product of tables

SELECT EMPLOYEE.NAME, DEPARTMENT.NAME
FROM   EMPLOYEE, DEPARTMENT;

Returns rows associating all EMPLOYEE names with all
DEPARTMENT names.
          The SELECT Operation
All columns of a table can be retrieved using the „*‟ construct:

       SELECT *
       FROM   EMPLOYEE
       WHERE NAME = „Bhadriah‟;

Returns the entire row for all EMPLOYEE relations where
Name is “Bhadriah”.

       SELECT * FROM EMPLOYEE, DEPARTMENT;

Returns the cross product of EMPLOYEE and DEPARTMENT.
                  Tables as Sets
By default SQL treats relations (tables) as bags or multi-sets
rather than sets.

A tuple may appear multiple times in a table. The DISTINCT
clause can be used to make the output into a set.

       SELECT DISTINCT NAME
       FROM EMPLOYEE;
                  Tables as Sets
Set theoretic operators UNION, INTERSECTION and EXCEPT
denote operations union, intersection and set difference.
They assume relations to be sets and not multi-sets. Note the use
of DISTINCT in the following example:

               SELECT DISTINCT NAME
               FROM EMPLOYEE
               WHERE SALARY > 300000.00
               UNION
               SELECT DISTINCT NAME
               FROM EMPLOYEE
               WHERE SALARY < 24000.00;
                 Tables as Sets
When DISTINCT is not used and relations are multi-sets, the
following set-theoretic operators can be used:

              UNION ALL (for disjoint union)

              INTERSECTION ALL (for bag intersection)

              EXCEPT ALL (for bag difference)
           Substring Comparison
Partial strings can be specified by embedding them around
% symbols and compared using the LIKE comparator:

              SELECT *
              FROM EMPLOYEE
              WHERE NAME LIKE „%Arun%‟;

% symbol matches any number of characters. A single character
can be matched by the underscore (_) symbol.

       …      WHERE NAME LIKE „_arun__‟;
            Arithmetic Operators
Standard numeric operators like addition (+), subtraction (-),
multiplication (*) and division (/) can be applied to attributes
having numeric domains.

The operators may also appear as part of the output table:

               SELECT 1.1 * SALARY
               FROM EMPLOYEE
               WHERE SALARY > 300000;
                 Summary
•   Introduction to the SQL2 standard
•   Tables, attributes and values
•   Schema creation, table creation
•   Entity and foreign key constraints
•   SELECT operation and its different
    forms…
Introduction to the SQL Standard

            Lecture 7
                 Summary
•   Introduction to the SQL2 standard
•   Tables, attributes and values
•   Schema creation, table creation
•   Entity and foreign key constraints
•   SELECT operation and its different
    forms…
         The SELECT Operation
•   The SELECT-FROM-WHERE generic form
•   Disambiguating attributes
•   Aliasing
•   Selecting from multiple tables
•   Tables as multi-sets and set operations on tables
•   Substring comparison
•   Arithmetic operators
   The EMPLOYEE Table
CREATE TABLE EMPLOYEE (
PANNO      VARCHAR(16) NOT NULL, UNIQUE,
EMPNO        INT(6) NOT NULL,
NAME       VARCHAR (40),
GENDER       CHAR,
DOB          DATE,
ADDRESS     VARCHAR(100),
SALARY       DECIMAL(8,2),
REPORTSTO    INT(6),
DNO          INT(6),
PRIMARY KEY (EMPNO),
FOREIGN KEY (REPORTSTO) REFERENCES
             EMPLOYEE (EMPNO));
    The DEPARTMENT Table
CREATE TABLE DEPARTMENT (
DNUMBER     INT(6) NOT NULL,
NAME       VARCHAR(20),
ADDRESS     VARCHAR(60),
HEAD         INT(6),
PRIMARY KEY (DNO),
FOREIGN KEY HEAD
   REFERENCES EMPLOYEE(EMPNO));
             Arithmetic Operators
The BETWEEN operator can be used to check for numeric values
within a given range:

      SELECT        *
      FROM          EMPLOYEE
      WHERE         SALARY BETWEEN 300000 AND 400000;
                Sorting Results
Output of a SELECT statement can be ordered (sorted) based
on one or more attributes by using the ORDER BY clause:

       SELECT        *
       FROM          EMPLOYEE
       WHERE         SALARY > 300000
       ORDER BY      NAME, EMPNO;
                   Nested Queries
Dynamically created tables from SELECT queries can be used
within other SELECT queries:

       SELECT    NAME
       FROM      DEPARTMENT
       WHERE     DNUMBER IN
            (SELECT   DNO
            FROM      EMPLOYEE
            WHERE     SALARY > 300000);

Highlighted query called the “nested query” and the non-highlighted
query called “outer query”.
                   Nested Queries
Comparisons with an entire table of values can be performed using
the ALL clause:

       SELECT    NAME
       FROM      EMPLOYEE
       WHERE     SALARY > ALL
            (SELECT   SALARY
             FROM     EMPLOYEE
             WHERE    SUPERVISOR=„007‟);

Returns the names of employees whose salaries are more than the
salaries of all those who report to „007‟
                   Nested Queries
Attribute names and scope:
Any unqualified attribute name in a nested query applies to the
innermost block of the query:

       SELECT    E.NAME
       FROM      EMPLOYEE AS E
       WHERE     E.EMPNO IN
            (SELECT   REPORTSTO
             FROM     EMPLOYEE
             WHERE    E.NAME = NAME);

Return all employee names who have the same name as their boss.
                    Nested Queries
Correlated Nested Queries:
Whenever the WHERE clause of an innermost query refers to an
attribute of the outer query, the query is said to be a correlated
nested query. (Example in the previous slide)


Understanding Correlated Queries:
In order to understand correlated queries, it should be noted that the
 query is performed once for each tuple of the outer-most block.
                   Nested Queries
The (NOT) EXISTS clause is used to check whether the result of a
SELECT operation is empty or not:

       SELECT    D.HEAD
       FROM      DEPARTMENT AS D
       WHERE     NOT EXISTS
            (SELECT   EMPNO
             FROM     EMPLOYEE
            WHERE     REPORTSTO=D.EMPNO);

Return the set of all heads of departments who do not have anyone
directly reporting to them.
                     Explicit Sets
Explicit sets of values for set theoretic operations can be specified
using parentheses:

       SELECT          NAME
       FROM            EMPLOYEE
       WHERE           REPORTSTO IN („007‟, „008‟, „009‟);

Returns the names of all employees who report to „007‟, „008‟ or
„009‟.
      Checking for NULL values
NULL values of attributes can be checked by using the
IS NULL or IS NOT NULL constructs.

       SELECT        NAME, SALARY
       FROM          EMPLOYEE AS E
       WHERE         EXISTS
              (SELECT        EMPNO
               FROM          EMPLOYEE
              WHERE          E.REPORTSTO = EMPNO
                             AND
                             REPORTSTO IS NULL);
Return the names and salaries of people who report directly to the
head of the company.
             Renaming Attributes
The AS clause can be used to rename attributes in the same way
as it can rename relations.

       SELECT          NAME AS EMPLOYEE_NAME
       FROM            EMPLOYEE;

The renamed attribute will be displayed in the output result of the
SELECT query.
                         Joins
Equijoins may be specified by equating attribute names from
two or more relations:

       SELECT        NAME, ADDRESS
       FROM          EMPLOYEE JOIN DEPARTMENT
                     ON DNO = DNUMBER
       WHERE         DEPARTMENT.NAME=„Research‟;

The DNO attribute from EMPLOYEE is equated to DNUMBER
attribute of DEPARTMENT for performing the join.
                          Joins
Natural joins may be specified using the NATURAL JOIN
construct. It automatically finds attributes having the same
names for performing the join.

Relations may be renamed to accommodate natural join using
the AS construct.

       SELECT EMPLOYEE.NAME, DEPARTMENT.NAME
       FROM EMPLOYEE NATURAL JOIN DEPARTMENT;
                         Joins
Outer joins may be specified using the following
constructs:

              LEFT OUTER JOIN
              RIGHT OUTER JOIN
              FULL OUTER JOIN
           Aggregate Functions
Aggregate functions like COUNT, SUM, AVG, MAX and MIN
operate on all tuples from the result and return a value:


       SELECT        COUNT(SALARY), MIN(SALARY),
                     MAX(SALARY), SUM(SALARY),
                     AVG(SALARY)
       FROM          EMPLOYEE;

COUNT can be used on non-numeric attributes as well.
MAX and MIN can also be used on non-numeric attributes if they
have a total ordering defined (Ex: date, char, etc.)
            Aggregate Functions
Since aggregate functions return a single value, they can be used
as part of a condition in a WHERE clause:

       SELECT         E.NAME
       FROM           EMPLOYEE AS E
       WHERE          (SELECT   COUNT(*)
                       FROM     DEPARTMENT AS D
                      WHERE     D.HEAD=E.EMPNO) >= 2;

Return the names of all employees who head two or more
departments.
             Aggregate Functions
In many cases, aggregate functions have to be applied to
 subgroups of tuples rather than all tuples. For this, the
 GROUP BY clause is useful:

       SELECT          DNO, COUNT(*), AVG(SALARY)
       FROM            EMPLOYEE
       GROUP BY        DNO;

For each department, count the number of employees and their
average salaries.
            Aggregate Functions
GROUP BY is applied after computing results of a query.

Sometimes it is required to retrieve tuples based on some aggregate
property. For this, the HAVING clause is useful:

       SELECT         DNO, COUNT(*), AVG(SALARY)
       FROM           EMPLOYEE
       GROUP BY       DNO
       HAVING         COUNT(*) > 20;

Return all departments having more than twenty employees, and
show the number of employees and their average salary.
            Aggregate Functions
HAVING versus WHERE:

WHERE conditions apply to individual tuples independently.

HAVING conditions apply to groups of tuples.
    SELECT Overall Form

       SELECT         <attribute and function list>
       FROM           <table list>
       [WHERE         <condition>]
       [GROUP BY      <grouping attribute(s)>]
       [HAVING        <group condition>]
       [ORDER BY      <attribute list>];

Constructs within box braces are optional.
        The INSERT Command
Data tuples can be added to tables using the INSERT
command. Example:

       INSERT INTO        EMPLOYEE
       VALUES      („1002‟, 002, „Bharath Kumar‟, „M‟,
            „9-5-1973‟, „Rajajinagar Bangalore 10‟,
            300000, 007, 5);

Inserts an entire EMPLOYEE record with corresponding values.
           The INSERT Command
Specific attributes can be populated by explicitly specifying
them by name:

       INSERT INTO       EMPLOYEE(NAME,ADDR,DNO)
       VALUES     („Arun K‟, „Yeshwanthpur‟, 5);

The other attributes will get either NULL values or their DEFAULT
values.

All attributes that are left out should hence not have NOT NULL
constraint or must have the DEFAULT constraint.
           The INSERT Command
The result of a SELECT query can be provided as data for
an INSERT command:

Consider the following table:

       CREATE TABLE EMD (
       EMPLOYEE INT(6),
       MANAGER INT(6),
       DNO       INTEGER);
          The INSERT Command
The following INSERT dynamically creates data from a SELECT
query for insertion:

      INSERT INTO      EMD
      SELECT     E.EMPNO AS EMPLOYEE,
                 E.REPORTSTO AS MANAGER,
                 D.DNUMBER AS DNO
      FROM       EMPLOYEE AS E JOIN
                 DEPARTMENT AS D
                 ON E.DNO = D.DNUMBER;
       The INSERT Command
• Inserts multiple instances of the same tuple if
  called multiple times as long as there is no
  violation of UNIQUE constraints
• Insertion fails if referential integrity is violated
• Multiple tuples can be inserted within a single
  INSERT command by enclosing each tuple within
  parentheses and separating tuples by a comma
              Deletion of Tuples
Tuples can be deleted from a table using the
DELETE-FROM-WHERE command:

       DELETE FROM EMPLOYEE WHERE EMPNO=007;
            (deletes one tuple)

       DELETE FROM EMPLOYEE WHERE DNO IN
            (SELECT DNUMBER FROM DEPARTMENT
             WHERE HEAD=007);
            (deletes possibly more than one tuples)

       DELETE FROM EMPLOYEE;
            (deletes all tuples)
             Updation of Tuples
Tuples can be modified using the
UPDATE-SET-WHERE command:

       UPDATE        EMPLOYEE
       SET           SALARY = SALARY*1.1
       WHERE         REPORTSTO=007;

Give a 10% raise in salary for all employees working under 007.
        Views (Virtual Tables)
• A table that physically exists in a database is
  called a base table
• A view or a virtual table is a table that is derived
  from other tables
• The other tables could either be base tables or
  other views
• Views need not be stored in the database as tables
  (but as queries)
• Update operations to a view are limited; but
  querying is not
        Specification of Views
Views are specified using the CREATE VIEW command:

      CREATE VIEW     EMD(EMPL, MGR, DEPT)
      AS
           SELECT E.NAME, M.NAME, D.NAME
           FROM EMPLOYEE AS E,
                EMPLOYEE AS M,
                DEPARTMENT AS D
           WHERE      E.REPORTSTO=M.EMPNO
                      AND
                      E.DNO=D.DNUMBER;
                  Views
• A view is always up to date
• Hence characterization of a view is not done
  during view definition time, but during
  query
• Views can be deleted using the DROP
  VIEW command
• Efficient implementation of views a tricky
  problem.
                 Summary
•   Introduction to the SQL2 standard
•   Tables, attributes and values
•   Schema creation, table creation
•   Entity and foreign key constraints
•   SELECT operation and its different
    forms…
         The SELECT Operation
•   The SELECT-FROM-WHERE generic form
•   Disambiguating attributes
•   Aliasing
•   Selecting from multiple tables
•   Tables as multi-sets and set operations on tables
•   Substring comparison
•   Arithmetic operators
      The SELECT Operation
• EXISTS and NULL checks
• Aggregation functions
• GROUP BY and HAVING constructs
• Nested queries: aliasing and scope in nested
  queries
• INSERT, DELETE and UPDATE
  operations
• Views and Virtual tables

								
To top