[Database] SQL (Structured Query Language)

SQL

• Initially, SEQUEL language (System R) project at IBM
• Three relational languages : QUEL, QBE, SQL
• (Later renamed) Structured Query Language (SQL)
• Currently, Standard Relational database Language
  • Users need to be less concerned to migrate to other types of relational DBMS
• Very-High-level language
  • Declarative (= Non-procedural) language
  • Users do not need to specify how (= what order of) to execute query operations.
  • Say “what to do” rather than “how to do”
  • Relational algebra is a procedural language.

• Table (= relation), row (= tuple), column (= attribute)
• A table consists of :
  • Base Table
  • View (= Virtual table)
• Basic commands;
  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Data Control Language (DCL)
• DDL is used for defining schemas, tables, and views.
  • CREATE, DROP, ALTER
• DML is used for retrieving and modifying tuples in a table.
  • SELECT (FROM WHERE)
  • INSERT, DELETE, UPDATE

CREATE TABLE

A table is defined using CREATE TABLE command:

• A relation defined by CREATE TABLE is called a “base table”.
  • The relation (and its tuples) are “physically stored”.
  • All created tables are initially “empty”.
  • Attributes are ordered as they are specified in CREATE TABLE

Constraints

• Key Integrity
  • Primary key and keys can be specified by PRIMARY KEY and UNIQUE, respectively
• Entity Integrity
  • This must be specified by NOT NULL on PRIMARY KEY
  • NOT NULL also optionally can be specified on other attributes.
    • : For example, employee names, phone may be specified by non null; What if new employee’s name is unknown?
• Referential Integrity
  • This is specified by FOREIGN KEY REFERENCES
  • SQL’s default action is to reject the operation that violates referential integrity violation
  • SQL also provides user specified trigger action

Key/Entity Constraints

Referential Constraint

Trigger

• SQL provides the following “Referential Trigger Action”;

• Total 6 trigger actions possible; For example:
  • (1) ON UPDATE CASCADE
    • : Change the value of referencing FK to the updated (new) PK value for all the referencing tuples;
  • (2) ON DELETE CASCADE
    • : Delete all the referencing tuples;
  • (3) ON UPDATE SET DEFAULT
  • (4) ON DELETE SET DEFAULT
    • : The value of FK in referencing tuples is changed to default value;
  • (5) ON UPDATE SET NULL
  • (6) ON DELETE SET NULL
    • : The value of FK in referencing tuples is changed to NULL value;

• Note : This SET NULL option is not allowed if the FK in referencing relation is a part of its own primary key.
• It is the responsibility of database designer to specify appropriate trigger actions.
• In general, CASCADE is useful for following “relationship” relations.
  • binary relationships (i.e., WORK-ON)
  • weak entity types (i.e., DEPENDENT)
  • multi-valued attributes (i.e., DEPT_LOCATIONS)

Excercise

DROP TABLE

• Used to remove a relation (base table) and its definition
• The dropped table can no longer be used in queries, updates, or any other commands since it does no longer exist.
• Two DROP options: CASCADE and RESTRICT
• Example:

DROP TABLE DEPENDENT RESTRICT

• This table is removed only if it is not referenced in any constraints or view. Otherwise, the DROP command will not be executed.
• For example, by foreign keys in another tables or views

DROP TABLE DEPENDENT CASCADE

• All constraints and views that reference this table are also removed automatically

ALTER TABLE

• Used to add or drop columns and change column definition to an existing table.

• Example:

ALTER TABLE EMP ADD COLUMN hobby CHAR(12)

• The new column ‘hobby’ will have NULLs in all tuples if no default value is specified.
• Users must enter a value for ‘hobby’ column by using UPDATE command.

ALTER TABLE EMP DROP COLUMN bdate CASCADE

• All constraints and views that ‘bdate’ column are also dropped automatically.
• If RESTRICT is used, this command is executed only if no views or constraints reference ‘bdate’ column.