There are 6 SQL-Schema Statements:
The catalog itself is a set of tables with its own schema name - definition_schema. Tables in the catalog cannot be modified directly. They are modified indirectly with SQL-Schema statements.
C o l u m n s | |||||||||||||||||||||
R |
|
||||||||||||||||||||
o | |||||||||||||||||||||
w | |||||||||||||||||||||
s |
A table has a fixed set of columns. The columns in a base table are not accessed positionally but by name, which must be unique among the columns of the table. Each column has a defined data type, and the value for the column in each row must be from the defined data type or null. The columns of a table are accessed and identified by name.
A table has 0 or more rows. A row in a base table has a value or null for each column in the table. The rows in a table have no defined ordering and are not accessed positionally. A table row is accessed and identified by the values in its columns.
In SQL92, base tables can have duplicate rows (rows where each column has the same value or null). However, the relational model does not recognize tables with duplicate rows as valid base tables (relations). The relational model requires that each base table have a unique identifier, known as the Primary Key. The primary key for a table is a designated set of columns which have a unique value for each table row. For a discussion of Primary Keys, see Entity Integrity under CREATE TABLE below.
A base table is defined using the CREATE TABLE Statement. This statement places the table description in the catalog and initializes an internal entity for the actual representation of the base table.
Example base table - s:
sno | name | city |
---|---|---|
S1 | Pierre | Paris |
S2 | John | London |
S3 | Mario | Rome |
Other types of tables in the system are derived tables. SQL-Data statements use internally derived tables in computing results. A query is in fact a derived table. For instance, the query operator - Union, combines two derived tables to produce a third one. Much of the power of SQL comes from the fact that its higher level operations are performed on tables and produce a table as their result.
Derived tables are less constrained than base tables. Column names are not required and need not be unique. Derived tables may have duplicate rows. Views are a type of derived table that are cataloged in the database. See Views below.
Once defined in the catalog, a view can substitute for a table in SQL-Data statements. A view name can be used instead of a base table name in the FROM clause of a SELECT statement. Views can also be the subject of a modification statement with some restrictions.
A SQL Modification Statement can operate on a view if it is an updatable view. An updatable view has the following restrictions on its defining query:
A DBMS user may access database objects (tables, columns, views) as allowed by the privileges assigned to that specific authorization identifier. Access privileges may be granted by the system (automatic) or by other users.
System granted privileges include:
CREATE TABLE table-name ({column-descr|constraint} [,{column-descr|constraint}]...)table-name is the new name for the table. column-descr is a column declaration. constraint is a table constraint.
The column declaration can include optional column constraints. The declaration has the following general format:
column-name data-type [column-constraints]column-name is the name of the column and must be unique among the columns of the table. data-type declares the type of the column. Data types are described below. column-constraints is an optional list of column constraints with no separators.
The check predicate must evaluate to not False (that is, the result must be True or Unknown) before a modification or addition of a row takes place. The check is effectively made on the contents of the table after the modification. For INSERT Statements, the predicate is evaluated as if the INSERT row were added to the table. For UPDATE Statements, the predicate is evaluated as if the row were updated. For DELETE Statements, the predicate is evaluated as if the row were deleted (Note: A check predicate is only useful for DELETE if a self-referencing subquery is used.)
length specifies the number of characters for fixed size strings (CHAR, CHARACTER); spaces are supplied for shorter strings. If length is missing for fixed size strings, the default length is 1. For variable size strings (VARCHAR, CHARACTER VARYING), length is the maximum size of the string. Strings exceeding length are truncated on the right.
Numeric
The integer types have default binary precision -- 15 for SMALLINT and 31 for INT, INTEGER.
NUMERIC ( precision [, scale] )
DECIMAL ( precision [, scale] )
Fixed point types have a decimal precision (total number of digits) and scale (which cannot exceed the precision). The default scale is 0. NUMERIC scales must be represented exactly. DECIMAL values can be stored internally with a larger scale (implementation defined).
FLOAT [(precision)]
REAL
DOUBLE
The floating point types have a binary precision (maximum significant binary digits). Precision values are implementation dependent for REAL and DOUBLE, although the standard states that the default precision for DOUBLE must be larger than for REAL. FLOAT also uses an implementation defined default for precision (commonly this is the same as for REAL), but the binary precision for FLOAT can be explicit.
Datetime
TIME and TIMESTAMP allow an optional seconds fraction (scale). The default scale for TIME is 0, for TIMESTAMP 6. The optional WITH TIME ZONE specifier indicates that the timezone adjustment is stored with the value; if omitted, the current system timezone is assumed.
INTERVAL interval-qualifier
See below for a description of the interval-qualifier.
Intervals are divided into sub-types -- year-month intervals and day-time intervals. Year-month intervals can only contain the sub-fields - year and month. Day-time intervals can contain day, hour, minute, second. The interval qualifier has the following formats:
YEAR [(precision)] [ TO MONTH ] MONTH [(precision)] {DAY|HOUR|MINUTE} [(precision)] [ TO SECOND [(scale)] ] DAY [(precision)] [ TO {HOUR|MINUTE} ] HOUR [(precision)] [ TO MINUTE ] SECOND [ (precision [, scale]) ]The default precision is 2. The default scale is 6.
A primary key is a constraint on the contents of a table. In relational terms, the primary key maintains Entity Integrity for the table. It constrains the table as follows,
Entity Integrity (Primary Keys) is enforced by the DBMS and ensures that every row has a proper unique identifier. The contents of any column in the table with Entity Integrity can be uniquely accessed with 3 pieces of information:
The primary key constraint in the CREATE STATEMENT has two forms. When the primary key consists of a single column, it can be declared as a column constraint, simply - PRIMARY KEY, attached to the column descriptor. For example:
sno VARCHAR(5) NOT NULL PRIMARY KEYAs a table constraint, it has the following format:
PRIMARY KEY ( column-1 [, column-2] ...)column-1 and column-2 are the names of the columns of the primary key. For example,
PRIMARY KEY (sno, pno)The order of columns in the primary key is not significant, except as the default order for the FOREIGN KEY table constraint, See Referential Integrity, below.
Referential Integrity requires that:
Like other constraints, the referential integrity constraint restricts the contents of the referencing table, but it also may in effect restrict the contents of the referenced table. When a row in a table is referenced (through its primary key) by a foreign key in a row in another table, operations that affect its primary key columns have side-effects and may restrict the operation. Changing the primary key of or deleting a row which has referencing foreign keys would violate the referential integrity constraints on the referencing table if allowed to proceed. This is handled in two ways,
column-descr REFERENCES references-specificationAs a table constraint, it has the following format:
FOREIGN KEY (column-list) REFERENCES references-specificationcolumn-list is the referencing table columns that comprise the foreign key. Commas separate column names in the list. Their order must match the explicit or implicit column list in the references-specification.
The references-specification has the following format:
table-2 [ ( referenced-columns ) ] [ ON UPDATE { CASCADE | SET NULL | NO ACTION }] [ ON DELETE { CASCADE | SET NULL | NO ACTION }]The order of the ON UPDATE and ON DELETE clauses may be reversed. These clauses declare the effect action when the referenced primary key is updated or deleted. The default for ON UPDATE and ON DELETE is NO ACTION.
table-2 is the referenced table name (primary key table). The optional referenced-columns list the columns of the referenced primary key. Commas separate column names in the list. The default is the primary key list in declaration order.
Contrary to the relational model, SQL92 allows foreign keys to reference any set of columns declared with the UNIQUE constraint in the referenced table (even when the table has a primary key). In this case, the referenced-columns list is required.
Example table constraint for referential integrity (for the sp table):
FOREIGN KEY (sno) REFERENCES s(sno) ON DELETE NO ACTION ON UPDATE CASCADE
CREATE TABLE s (sno VARCHAR(5) NOT NULL PRIMARY KEY, name VARCHAR(16), city VARCHAR(16) ) CREATE TABLE p (pno VARCHAR(5) NOT NULL PRIMARY KEY, descr VARCHAR(16), color VARCHAR(8) ) CREATE TABLE sp (sno VARCHAR(5) NOT NULL REFERENCES s, pno VARCHAR(5) NOT NULL REFERENCES p, qty INT, PRIMARY KEY (sno, pno) )Create for sp with a constraint that the qty column can't be negative:
CREATE TABLE sp (sno VARCHAR(5) NOT NULL REFERENCES s, pno VARCHAR(5) NOT NULL REFERENCES p, qty INT CHECK (qty >= 0), PRIMARY KEY (sno, pno) )
CREATE VIEW view-name [ ( column-list ) ] AS query-1 [ WITH [CASCADED|LOCAL] CHECK OPTION ]view-name is the name for the new view. column-list is an optional list of names for the columns of the view, comma separated. query-1 is any SELECT statement without an ORDER BY clause. The optional WITH CHECK OPTION clause is a constraint on updatable views.
column-list must have the same number of columns as the select list in query-1. If column-list is omitted, all items in the select list of query-1 must be named. In either case, duplicate column names are not allowed for a view.
The optional WITH CHECK OPTION clause only applies to updatable views. It affects SQL INSERT and UPDATE statements. If WITH CHECK OPTION is specified, the WHERE predicate for query-1 must evaluate to true for the added row or the changed row.
The CASCADED and LOCAL specifiers apply when the underlying table for query-1 is another view. CASCADED requests that WITH CHECK OPTION apply to all underlying views (to any level.) LOCAL requests that the current WITH CHECK OPTION apply only to this view. LOCAL is the default.
CREATE VIEW supplied_parts AS SELECT * FROM p WHERE pno IN (SELECT pno FROM sp) WITH CHECK OPTIONAccess example:
SELECT * FROM supplied_parts
pno | descr | color |
---|---|---|
P1 | Widget | Red |
P2 | Widget | Blue |
CREATE VIEW part_locations (part, quantity, location) AS SELECT pno, qty, city FROM sp, s WHERE sp.sno = s.snoAccess examples:
SELECT * FROM part_locations
part | quantity | location |
---|---|---|
P1 | NULL | Paris |
P1 | 200 | London |
P1 | 1000 | Rome |
P2 | 200 | Rome |
SELECT part, quantity FROM part_locations WHERE location = 'Rome'
part | quantity |
---|---|
P1 | 1000 |
P2 | 200 |
DROP TABLE table-name {CASCADE|RESTRICT}table-name is the name of an existing base table in the current schema. The CASCADE and RESTRICT specifiers define the disposition of other objects dependent on the table. A base table may have two types of dependencies:
CASCADE specifies that any dependencies are removed before the drop is performed:
DROP VIEW view-name {CASCADE|RESTRICT}view-name is the name of an existing view in the current schema. The CASCADE and RESTRICT specifiers define the disposition of other objects dependent on the view. A view may have two types of dependencies:
CASCADE specifies that any dependencies are removed before the drop is performed:
GRANT privilege-list ON [TABLE] object-list TO user-listprivilege-list is either ALL PRIVILEGES or a comma-separated list of properties: SELECT, INSERT, UPDATE, DELETE. object-list is a comma-separated list of table and view names. user-list is either PUBLIC or a comma-separated list of user names.
The GRANT statement grants each privilege in privilege-list for each object (table) in object-list to each user in user-list. In general, the access privileges apply to all columns in the table or view, but it is possible to specify a column list with the UPDATE privilege specifier:
UPDATE [ ( column-1 [, column-2] ... ) ]If the optional column list is specified, UPDATE privileges are granted for those columns only.
The user-list may specify PUBLIC. This is a general grant, applying to all users (and future users) in the catalog.
Privileges granted are revoked with the REVOKE Statement.
The optional specificier WITH GRANT OPTION may follow user-list in the GRANT statement. WITH GRANT OPTION specifies that, in addition to access privileges, the privilege to grant those privileges to other users is granted.
GRANT SELECT ON s,sp TO PUBLIC GRANT SELECT,INSERT,UPDATE(color) ON p TO art,nan GRANT SELECT ON supplied_parts TO sam WITH GRANT OPTION
REVOKE privilege-list ON [TABLE] object-list FROM user-listThe REVOKE Statement revokes each privilege in privilege-list for each object (table) in object-list from each user in user-list. All privileges must have been previously granted.
The user-list may specify PUBLIC. This must apply to a previous GRANT TO PUBLIC.
REVOKE SELECT ON s,sp FROM PUBLIC REVOKE SELECT,INSERT,UPDATE(color) ON p FROM art,nan REVOKE SELECT ON supplied_parts FROM sam