SQL89 Standard

SQL89

---

 

---

<script></script>

ANSI SQL Standard

Overview of SQL

Schema Definition Language

Data Definition

Primary and Foreign Keys

Data Manipulation

Cursor Operations

Noncursor Operations

View

Update Operations on Views

Union

Functions

Data Control

Miscellaneous

Search Conditions

ANSI-SQL Keywords

ANSI SQL-2

Future Extensions

---

ANSI SQL Standard

◇ Advantages

•  Reduced Training Costs

•  Application Portability

•  Application Longevity

•  Cross-system Communication

•  Customer Choice

•  A standard can Stifle Creativity

  ◇ Problems

•  Not Quite Relational

•  Some Deficiencies

  • incomplete in table definition management

  • lack of implementation standard

  瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw  standard.htm,  Thu Jun 14 08:06:50 CST 2001

  Overview of SQL

  ◇ Reference Book

•  C. J. Date, "A Guide to The SQL Standard", 2nd Ed., Addison-Wesley, 1989

  Invoke SQL	Aggregate Functions
  Data Manipulation Language
  CURSOR Operation	VIEWS
  Data Control	ANSI-SQL Keywords

  00 Invoke SQL

•  Data manipulation functions can be invoked either from interactively or from an application program

•  Data Definition functions can only be invoked interactively

  礶

  ---

  00 Data Manipulation Language

  ◇ SELECT, INSERT, UPDATE, DELETE

  INSERT INTO SP (SNO, PNO, QTY) VALUES ( 'S5', 'P1', 100)

  UPDATE S SET STATUS = 2 * STATUS WHERE S.CITY = 'LONDON'

  DELETE FROM P WHERE P.WEIGHT > 15

  ◇ Options

•  DISTINCT, ALL

•  ORDER BY (DESC, ASC)

  ◇ Comments:

•  single-row SELECT only (singleton SELECT)

  • need to use CURSOR for multiple-row SELECT

  • UPDATE and DELETE can work on multiple-rows

•  possible confusion on the word "update" used in generic meaning, and the "UPDATE" operation in SQL. "update" may means INSERT, UPDATE, and DELETE.

  礶

  ---

  00 Aggregate Functions

  ◇ COUNT, SUM, AVG, MAX, MIN, COUNT(*).

  ---

  00 CURSOR Operation

  • A cursor is a SQL object that is associated with a specific SELECT operation.

  ◇ OPEN

  • open the cursor, which causes the SELECT to be executed

  ◇ FETCH

  • fetch rows repeatly on the opened cursor

  ◇ CLOSE

  • close the cursor

  Cursor Operation Details
  礶

  ---

  00 Views

  CREATE VIEW LS (SNO, SNAME, STATUS) AS SELECT S.SNO, S.SNAME, S.STATUS FROM S WHERE S.CITY = 'London'

•  View definition must not contain subqueries

•  Full SELECTION operation

•  Restricted update operations

  • applicable only if the target is a simple row-and-column subset of a single underlying base table.

  Detailed View Description
  礶

  ---

  00 Data Control

  ◇ Security

  ◇ Integrity

  ◇ Transaction Processing

  Detailed Data Control Description
  礶

  ---

  00 ANSI-SQL Keywords

  ANSI-SQL Keywords Details
  

  瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw  overview.htm,  Thu Jun 14 08:06:52 CST 2001

  Schema Definition Language

  ◇ Define a database

  CREATE SCHEMA AUTHORIZATION user schema-element-list

  ◇ Schema Elements

  tables	views	grand-operations

•  Comments: "SCHEMA" is confusion, may be better to use "DATABASE"

  ◇ Data Types

  CHARACTER	NUMERIC	INTEGER	SMALLINT
  FLOAT	DOUBLE	REAL	DECIMAL

•  Comments: Need other data types:

  DATE	TIMES	MONEY VALUES	BINARIES
  BIT STRINGS	BOOLEAN VALUES	etc.

  ◇ Constraints

  .h2

  Primary Key	Foreign Key	Candidate Key	UNIQUE (column-commalist)

  • each column must be defined as "NOT NULL"

•  Check Condition

  CREATE TABLE S ( SNO .... STATUS .... CHECK ( S.STATUS BETWEEN 10 and 100 )) ^ |---------------(search-condition)

  • Check conditions is a "restricted predicate"

  •  

    • a condition that can be evaluated for a given row

  ◇ Privileges

•  CREATE, SELECT, INSERT, UPDATE, DELETE

•  no special privilege is needed to create a schema

•  to create a base table, no special privilege is needed

  • (unless it references another table)

•  to create a view V, the SELECT privilege is needed on every table mentioned in the definition of V

•  for other operations, corresponding privilege is needed

•  need SELECT privilege on all tables mentioned in WHERE clauses

  ◇ Grant

•  GRANT privileges ON table TO grantee-commalist

  • [ WITH GRANT OPTION ]

  • Privilege can be either "ALL" or a commalist of specific operations.

  • grantee is either an authorization identifier or "PUBLIC"

  ◇ USER

  •  a system variable specifying the user's identifier

    瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw  schema.htm,  Thu Jun 14 08:06:54 CST 2001

    Data Definition

    ◇ Can Define Base Tables and Views

  •  CREATE TABLE

  •  CREATE VIEW

    • Comment: only avbiable to DBA in interactive mode

    ◇ AUTHORIZATION name

    ◇ Examples

    CREATE SCHEMA AUTHORIZATION TED

    CREATE TABLE S ( SNO CHAR(5), NOT NULL, SNAME CHAR(20), STATUS DECIMAL(3), CITY CHAR(15), PRIMARY KEY (SNO))

    CREATE TABLE P ( PNO CHAR(6), NOT NULL, PNAME CHAR(20), COLOR CHAR(6), WEIGHT DECIMAL(3), CITY CHAR(15), PRIMARY KEY (PNO))

    CREATE TABLE SP ( SNO CHAR(5), NOT NULL, PNO CHAR(6), NOT NULL, QTY DECIMAL(3), PRIMARY KEY ( SNO, PNO )) FOREIGN KEY ( SNO ) REFERENCE S, FOREIGN KEY ( PNO ) REFERENCE P )

    CREATE VIEW LS ( SNO, SNAME, STATUS) AS SELECT S.SNO, S.SNAME, S.STATUS FROM S WHERE S.CITY = 'London'

    Relation: S (Supplier)
    SNO	SNAME	STATUS	CITY
    char(5)	char(20)	decimal(3)	char(15)
    NOT NULL
    primary key

    Relation: P (Parts)
    PNO	PNAME	COLOR	WEIGHT	CITY
    char(6)	char(20)	char(6)	decimal(3)	char(15)
    NOT NULL
    primary key

    Relation: SP (Shipment)
    SNO	PNO	QTY
    char(5)	char(6)	decimal(3)
    primary Key
    foreign key(S)	foreign key(P)

    瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw  datadef.htm,  Thu Jun 14 08:06:56 CST 2001

    Primary and Foreign Keys

    ◇ Primary Key

    PRIMARY KEY (ColumnList)

  •  Semantically identical to

    UNIQUE (ColumnList)

  •  each table can have at most one primary key

  •  primary key receives special treatment in any corresponding foreign key definitions

  •  Comments

    • primary key is optional for compatibility,

    • but is strongly recommended

    ◇ Foreign Keys

    FOREIGN KEY (ColumnList) REFERENCES BaseTable [(ColumnList)]

    • a column (or combination of columns) in one base table T2 whose values are required to match values of the primary key (or cadidate key: in ANSI standard) in some base table T1

    • Comment: better to reference to primary keys only

    • T1 : referenced table, T2 : referencing table

    • A foreign key and its matching candidate key must have the same number of columns and the data types of corresponding columns must be identical

    • T1 and T2 can be the same table

    CHILDOF (CHILD, MOTHER)

  •  null values are allowed in a foreign key

  •  a table definition is invalid if any referenced table does not exist

    • (permit the definition, but not usable)

  •  DBMS will reject any operation that violates any referential constraint

    • Comment: it is better to provide cascading delete, instead of reject it.

  •  need REFERENCE privilege on every component columns of the matching candidate key.

  •  Comment: not clear how cycle referential integrity is treated

    瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw  pkey.htm,  Thu Jun 14 08:06:58 CST 2001

    Data Manipulation

    ◇ Cursor Operations

    OPEN	FETCH	CLOSE
    UPDATE .. CURRENT (positioned UPDATE)
    DELETE .. CURRENT (positioned DELETE)

    ◇ NonCursor Operations

    SELECT	INSERT
    UPDATE (searched UPDATE)
    DELETE (searched DELETE)

    ◇ Transaction termination operations

    COMMIT WORK	ROLLBACK WORK

    瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw  manipu.htm,  Thu Jun 14 08:07:00 CST 2001

    Cursor Operations

    OPEN FETCH UPDATE .. CURRENT (positioned UPDATE) DELETE .. CURRENT (positioned DELETE) CLOSE

    .fi

    • a pointer that can be used to run thru an ordered collection of rows, pointing to each of the rows in that collection in turn

    ◇ Cursor Definition

    SYNTAX	EXAMPLE
    DECLARE cursor CURSOR	DECLARE X CURSOR
    FOR query-expression	FOR SELECT SP.SNO, SP.QTY
    	FROM SP
    	WHERE SP.PNO = PNO_PARAM
    [order-by-clause]	ORDER BY SP.SNO

  •  define a set of row for cursor to access

  •  conditions for update:

    • does not involve UNION

    • if it is a view, must be an updatable view

    • cursor definition does not include ORDER BY clause

  •  will be executed when X is OPENed

    • parameter PNO_PARAM must be specified

    • parameter values are fixed at the OPEN time

  •  ORDER BY clause

    ORDER BY ordering-spec-list [ASC|DESC]

    • column name can be replaced by column position

    ◇ OPEN/CLOSE

    OPEN/CLOSE cursor

    ◇ Positions

  •  on /fRa row/fP,/h'1'before /fRa row/fP,/h'1'after /fRthe last row

    • OPEN put the cursor before the first row

    ◇ FETCH (/s-2 fetch next/s0)

    SYNTAX	EXAMPLE
    FETCH cursor	FETCH X
    INTO target-list	INTO SNO_P, QTY_P INDICATOR QTY_I

    • will put the cursor on next row or after the last row

    ◇ UPDATE (positioned)

    SYNTAX	EXAMPLE
    UPDATE table	UPDATE SP
    SET assignment-commalist	SET QTY=SP.QTY+INCR_PARAM
    WHERE CURRENT OF cursor	WHERE CURRENT OF X

    • Cursor X must be currently open and be positioned on a row

    • row (table) must be updatable

    • must not reference to any of the aggregate builtin function

    • Comments: /s-4 don't know what happen if the update operation changed the position of the updated row w.r.t. to order

    ◇ DELETE (positioned)

    SYNTAX	EXAMPLE
    DELETE	DELETE
    FROM table	FROM SP
    WHERE CURRENT OF cursor	WHERE CURRENT OF X

    • Cursor X will be positioned before next row or after the last row if no next row

    瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw  cursor.htm,  Thu Jun 14 08:07:01 CST 2001

    Noncursor Operations

    ◇ SELECT

  •  Singleton SELECT, not a full SELECT

  •  retrieve at most one row

    • it's an error if multiple rows are selected

    SYNTAX	EXAMPLE
    SELECT [ALL|DISTINCT] selection	SELECT P.WEIGHT, P.COLOR
    INTO target-list	WEIGHT_PARAM, COLOR_PARAM
    table-expression	FROM P
    	WHERE P.PNO = 'P4'

  •  "selection" can be either a commalist of scalar expression or a "*", but not both.

  •  it can be both in DB2

  •  at most one DISTINCT can appear in SELECT such as:

    • illegal: SELECT SUM(DISTINCT QTY), AVG(DISTINCT QTY)

    ◇ INSERT

    SYNTAX	EXAMPLE
    INSERT	INSERT
    INTO table [(column-list)]	INTO TEMP (SNO, CITY)
    source	SELECT S.NO, S.CITY
    	FROM S
    	WHERE S.STATUS > STATUS_PARAM

  •  "source" can be a query or a VALUE caluse

    • VALUES clause must not be an operational expression such as X+1

    ◇ SEARCHED UPDATE

    SYNTAX	EXAMPLE
    UPDATE table	UPDATE table
    SET assignment-commalist	SET STATUS = 2 * STATUS
    WHERE search-condition	WHERE S.CITY = CITY_PARAM

    ◇ SEARCHED DELETE

    SYNTAX	EXAMPLE
    DELETE	DELETE
    FROM table	FROM P
    [WHERE search-condition]	WHERE P.WEIGHT > WEIGHT_PARAM

    ◇ Limitation of INSERT, UPDATE, DELETE

  •  If use a query specification, the target table must not be referred in any FROM clause, such as:

    INSERT INTO T SELECT * FROM T

    瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw  noncursr.htm,  Thu Jun 14 08:07:03 CST 2001

    View

    ◇ View Definition

  •  Syntax

    CREATE VIEW [(column-commalist)] AS query-specification [WITH CHECK OPTION] .Code  Example CREATE VIEW GOODSUPPS (SNO, STATUS, CITY) AS SELECT S.SNO, S.STATUS, S.CITY FROM S WHERE S.STATUS > 15 .Code  cannot include any parameter since it is at schema level ◇ View Processing ORIGINAL QUERY TRANSLATED QUERY SELECT * SELECT S.SNO, S.STATUS, S.CITY FROM GOODSUPPS FROM S WHERE CITY <> 'London' WHERE S.STATUS > 15   AND S.CITY <> 'London'  no specification on how view should be processed  materialization of view is not specified Retrieval Operations on Views ◇ almost no restriction (except some surprises) ◇ Anomalies  grouped views, DISTINCT, derived columns A FROM clause that references a grouped view cannot have an associated WHERE clause, GROUP BY clause, or HAVING clause.  Example CREATE VIEW PQ ( PNO, TOTQTY ) AS SELECT SP.PNO, SUM (SP.QTY) FROM SP GROUP BY SP.SNO .Code ORIGINAL QUERY TRANSLATED QUERY SELECT PQ.PNO SELECT SP.PNO FROM PQ FROM SP WHERE TOTQTY > 5 WHERE SUM (SP.QTY) > 5   GROUP BY SP.PNO     Correct Translation     SELECT SP.PNO   FROM SP   GROUP BY SP.PNO   HAVING SUM ( SP.QTY ) > 5 ORIGINAL QUERY TRANSLATED QUERY SELECT AVE(PQ.TOTQTY) SELECT AVE(SUM(SP.QTY)) FROM PQ FROM SP   GROUP BY SP.PNO  Rule of Thumb: The translated query must be a valid SQL SELECT-expression 瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw  view.htm,  Thu Jun 14 08:07:05 CST 2001 Update Operations on Views ◇ Limited Update CREATE VIEW CITYPAIRS (SCITY, PCITY) AS SELECT S.CITY, P.CITY FROM S, SP, P WHERE S.SNO = SP.SNO AND SP.PNO = P.PNO .Code ◇ Conditions of Updatable View Definition does not include the key word DISTINCT every item in the SELECT clause is a simple reference to a column of the underlying table not a constant, an operational expression, or a function FORM clause identifies exactly one updatable table WHERE clause does not include a subquery no GROUP BY clause no HAVING clause  Comments these conditions are too restrictive in DB2, if SELECTION contains operational exp. (e.g. X+1), DELETE is allowable on X UPDATE is allowable on other columns ◇ Check Option  what if an update make a tuple disqualified?  add WITH CHECK OPTION to the view definition all INSERTs and UPDATEs against that view will be checked to ensure that the newly INSERTed or UPDATEd row does satisfy the view-definition condition  Comments in DB2, check option can only be specified for updatable view check option is not inherentable 瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw  vwupdate.htm,  Thu Jun 14 08:07:07 CST 2001 Union ◇ Limitations  can only be used in cursor definition (query exp.)  a binary operation illegal example: x UNION y UNION z  Syntax A UNION [ ALL ] B redundant duplicate rows are retained iff ALL is used for all i, the ith column of A and the ith column of B have exactly the same description (the same data type and length) too restrict no name inherent rule -- resulting table is unnamed 瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw  union.htm,  Thu Jun 14 08:07:09 CST 2001 Functions ◇ COUNT, SUM, MAX, MIN argument of SUM and AVG must be numeric DISTINCT or ALL can be applied on the argument must specify DISTINCT when COUNT is used if DISTINCT is used, argument must be a simple cloumn if not used, argument can be an operational expression no nested function reference nulls in argument are eliminated before a function is applied (except COUNT) if argument is empty, COUNT returns a zero; others return null unless a GROUP BY is involved, a SELECT clause must consists of function references only, no mixture is allowed 瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw  function.htm,  Thu Jun 14 08:07:10 CST 2001 Data Control ◇ Security  View (use view to hide data)  GRANT operation SELECT, UPDATE, DELETE, INSERT, REFERENCE privileges. table creator has the full privilege any user holding a privilege "with the grant option" can grant the privilege to another user with or without grant option Examples GRANT INSERT, DELETE, UPDATE ON SP TO JOE GRANT SELECT ON SP TO ALICE WITH GRANT OPTION GRANT UPDATE (STATUS) ON S TO JUDY GRANT DELETE ON SP TO SANDY, CLYDE ◇ Integrity  NOT NULL  CHECK check for any column or combination of columns (In DB2, non-updatable views can not have CHECK)  UNIQUE no duplicated row is allowed must have NOT NULL constraint  PRIMARY KEY (a special case of UNIQUE)  FOREIGN KEY (an alternative way to specify a foreign key)  REFERENCES  Comments Need to support wider range of integrity constraints ◇ Transaction Processing  transaction: a sequence of SQL operations that is guaranteed to be atomic  no nested transaction  terminated by COMMIT WORK (normal termination) ROLLBACK WORK (rollback termination) No specification what to do on failure a database update by a transaction is not visible by other transactions before termination  comments: no cascade rollback is possible, but concurrency is lower guaranteed serialization 瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw  control.htm,  Thu Jun 14 08:07:12 CST 2001 Miscellaneous ◇ Range Variables SELECT FIRST.SNO, SECOND.SNO FROM S FIRST, S SECOND WHERE FIRST.CITY = SECOND.CITY   FIRST and SECOND are range variables ◇ GROUP BY rearrange the table into min. number of groups such that within each group all rows have the same value for the GROUP BY column ◇ HAVING a WHERE clause for groups expression in a HAVING clause must be single-valued per group must be used with GROUP BY together SELECT PNO, SUM (SP.QTY) FROM SP GROUP BY SP.PNO HAVING COUNT(*) > 1 .Code ◇ Search Conditions used in WHERE and HAVING to qualify or disqualify specific rows each row is evaluated to true false unknown  Boolean Operators: AND OR NOT  comparison predicate: = <> < > <= >=  BETWEEN ScalarExp [ NOT ] BETWEEN ScalarExp AND ScalarExp  LIKE  test for null  IN  All-or-Any  test for existence 瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw  misc.htm,  Thu Jun 14 08:07:14 CST 2001 Search Conditions ◇ Comparison Predicate: = <> < > <= >= one argument can be a subquery that returns a single scalar value if no value is returned, the predicate will evaluated to unknown the subquery must be at RHS of the comparison operator cannot compare two subqueries cannot include GROUP BY or HAVING the FROM clause in the subquery must not identify a grouped view correctness of a program depends on data ◇ BETWEEN ScalarExp [ NOT ] BETWEEN ScalarExp AND ScalarExp y BETWEEN x AND z x <= y AND y <= z ◇ LIKE  used for simple pattern-matching  Column-ref [ NOT ] LIKE atom [ ESCAPE atom ] SELECT P.PNO, P.PNAME FROM P WHERE P.PNAME LIKE 'C%' .Code "part number and names for parts whose names begin with the letter C" Symbol meaning "_" any single character "%" any sequence of n characters Example meaning X LIKE %Berkeley% X contains the string "Berkeley" X LIKE S__ X is char(2) and begins with an S X LIKE _% X begins with a '_' ◇ Test for Existence EXISTS subquery Search Conditions ◇ Test for Null  Null means unknown  Null compared with any value --> unknown NOT     AND T ? F   OR T ? F T F   T T ? F   T T T T ? ?   ? ? ? F   ? T ? ? F T   F F F F   F T ? T SELECT S.SNO FROM S WHERE S.CITY IS NULL ◇ IN ScalarExp [ NOT ] IN ( atom-commalist ) ScalarExp [ NOT ] IN subquery SELECT P.PNO FROM P WHERE P.WEIGHT IN (12, 16, 17 ) ◇ All-or-Any ScalarExp quantified-comparison subquery quantified-comparison is the scalar comparison (=, <>, <, > <=, >=) followed by ALL, ANY, or SOME ANY is the same as SOME SELECT DISTINCT SP.SNO FROM SP WHERE SP.QTY >ALL (SELECT SP.QTY FROM SP WHERE SP.SNO = 'S1' )  Comments: All-or-any predicates are error-prone because "any" may be used in place of "every" in natural English language. ">ANY" may be used, instead of ">ALL". ANY is not necessary since it can be replaced by EXISTS 瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw  search.htm,  Thu Jun 14 08:07:16 CST 2001 ANSI-SQL Keywords .h2 ALL DELETE INTO REFERENCES AND DESC IS ROLLBACK ANY DISTINCT KEY SCHEMA AS DOUBLE LANGUAGE SECTION ASC END LIKE SELECT AUTHORIZATION ESCAPE MAX SET AVG EXEC MIN SMALLINT BEGIN EXISTS MODULE SOME BETWEEN FETCH NOT SQL BY FLOAT NULL SQLCODE CHAR FOR NUMERIC SQLERROR CHARACTER FOREIGN OF SUM CHECK FORTRAN ON TABLE CLOSE FOUND OPEN TO COBOL FROM OPTION UNION COMMIT GO OR UNIQUE CONTINUE GOTO ORDER UPDATE COUNT GRANT PASCAL USER CREATE GROUP PLI VALUES CURRENT HAVING PRECISION VIEW CURSOR IN PRIMARY WHENEVER DEC INDICATOR PRIVILEGES WHERE DECIMAL INSERT PROCEDURE WITH DECLARE INT PUBLIC WORK DEFAULT INTEGER REAL 瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw  keyword.htm,  Thu Jun 14 08:07:18 CST 2001 ANSI SQL-2 ◇ Extended Database Language SQL ◇ Proposed to replace SQL-1 (SQL-1989)  SQL-1 refer to ISO 9075:1989 and ANS X3.135-1989 Contact: American National Standard Institute, Attn: BSR Center 1430 Broadway, NY, NY 10018 ◇ Extended Features:  schema manipulation  dynamic SQL  exception handling with diagnostics  data administration and data manipulation  New Data types DATE, TIME, TIMESTAMP, and BITSTRING  Domain Definition  New Operators string manipulation, case expression, cast expressions for converting data types outer join, union join, set operations, derived table in the FROM clause  New Data Definition Capabilities variable length strings, character set collation cascaded effects for referential integrity recognition of new international character string requirements 瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw  sql2.htm,  Thu Jun 14 08:07:19 CST 2001 Future Extensions Future Definitional Extensions Future Manipulative Extensions Future Extensions: Orthogonality 00 Future Definitional Extensions ◇ CREATE TABLE from an application program ◇ Support domains create and drop domains columns can be defined on domains domain integrity (constrain all columns of the same domain) limited "strong type checking" on comparisons (join) limited ability to defined valid operations for specific domain and to define the domain of the result of a scalar expression ◇ New builtin data types VARCHAR (varying length strings) NCHAR (national strings) DATETIME (absolute dates and times) INTERVAL (date and time intervals) ◇ New schema manipulations DROP SCHEMA DROP TABLE DROP VIEW REVOKE [= drop authorization ] ALTER TABLE [add/drop column or constraints, modify defaults ] CREATE DOMAIN, DROP DOMAIN CREATE ASSERTION, DROP ASSERTION ◇ Extended Referential Integrity Constraints CASCADE, SET NULL, SET DEFAULT delete and update rules ◇ General Integrity Constraints with arbitrary complexity, involving any number of tables Comments: induce redundancy NOT NULL is a special case of single-column CHECK single-column CHECK is a special case of table-level CHECK table-level check is a special case of general integrity constraints 礶 00 Future Manipulative Extensions ◇ Special New Scalar Operators comparison and component extraction operators for date and times string concatenation operator || substring operator SUBSTRING substring index operator INDEX string length operator LENGTH ◇ String Pattern-Matching Comparison SIMILAR TO ◇ Extensive Error Messages ◇ Schema Information Tables ACCESSIBLE_TABLE TABLE_PRIVILEGES ACCESSIBLE_COLUMNS COLUMN_PRIVILEGES CONSTRAINT_DEFS CONSTRAINT_COLUMNS DOMAIN_DEFINITIONS ◇ Naming Derived Columns SELECT X + Y AS Z , ... SELECT T.Z + 1, ... FROM (SELECT X + Y FROM ...) AS T ( Z ) .Code ◇ Scroll Cursors FETCH NEXT FETCH PRIOR FETCH FIRST FETCH LAST FETCH ABSOLUTE scalar-expression FETCH RELATIVE scalar-expression ◇ Various Joined Tables table [ NATURAL ] [ INNER | LEFT | RIGHT | FULL ] JOIN table [ ON search-condition ] ◇ Intersect and Difference INTERSET EXCEPT ◇ Deactivation of Integrity Constraints SET CHECK47, CHECK82 CONSTRAINTS OFF ◇ New Transaction Management Language ◇ Temporary Tables and Views ◇ Dynamic SQL application program generates query on-the-fly 礶 00 Future Extensions: Orthogonality ◇ Orthogonality means Independence ◇ Query Expression will be accepted in the followins   (Cursor definition only)  CREATE VIEW  subquery  INSERT ... SELECT  components of join  UNION  FROM ◇ DISTINCT  can applied to scalar expression  more than one DISTINCT will be allowed in a given expression ◇ GROUPED VIEW  the restriction in FROM clause is dropped 瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw  future.htm,  Thu Jun 14 08:07:22 CST 2001