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
00 Invoke SQLInvoke SQL Aggregate Functions Data Manipulation Language CURSOR Operation VIEWS Data Control ANSI-SQL Keywords -
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
00 ViewsCREATE 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.
00 Data Control◇ Security
◇ Integrity
◇ Transaction Processing
Detailed Data Control Description
礶
00 ANSI-SQL Keywords瓣ミ現獀厩戈癟厩╰ 硈模玭 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
.h2Primary 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
.fiOPEN FETCH UPDATE .. CURRENT (positioned UPDATE) DELETE .. CURRENT (positioned DELETE) CLOSE
-
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
.h2ANSI-SQL Keywords 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
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-