Academic Tutorials



English | French | Portugese | German | Italian
Home Advertise Payments Recommended Websites Interview Questions FAQs
News Source Codes E-Books Downloads Jobs Web Hosting
Chats

SQL Server
SQL Server Automating Administration
SQL Server Full Text Searching
SQL Server Installing
SQL Server Selecting XML Data
SQL Server XSL Stylesheets
SQL Server Stored Procedures
SQL Server XDR Schemes
SQL Server Indexed Views
SQL Server Introduction
SQL Server Data Statements
SQL Server SELECT Statement
SQL Server Modification Statements
SQL ServerTransaction Statements
SQL Server Schema Statements
SQL Server XML Introduction
SQL Server XML Templates

HTML Tutorials
HTML Tutorial
XHTML Tutorial
CSS Tutorial
TCP/IP Tutorial
CSS 1.0
CSS 2.0
HLML
XML Tutorials
XML Tutorial
XSL Tutorial
XSLT Tutorial
DTD Tutorial
Schema Tutorial
XForms Tutorial
XSL-FO Tutorial
XML DOM Tutorial
XLink Tutorial
XQuery Tutorial
XPath Tutorial
XPointer Tutorial
RDF Tutorial
SOAP Tutorial
WSDL Tutorial
RSS Tutorial
WAP Tutorial
Web Services Tutorial
Browser Scripting
JavaScript Tutorial
VBScript Tutorial
DHTML Tutorial
HTML DOM Tutorial
WMLScript Tutorial
E4X Tutorial
Server Scripting
ASP Tutorial
PERL Tutorial
SQL Tutorial
ADO Tutorial
CVS
Python
Apple Script
PL/SQL Tutorial
SQL Server
PHP
.NET (dotnet)
Microsoft.Net
ASP.Net
.Net Mobile
C# : C Sharp
ADO.NET
VB.NET
VC++
Multimedia
SVG Tutorial
Flash Tutorial
Media Tutorial
SMIL Tutorial
Photoshop Tutorial
Gimp Tutorial
Matlab
Gnuplot Programming
GIF Animation Tutorial
Scientific Visualization Tutorial
Graphics
Web Building
Web Browsers
Web Hosting
W3C Tutorial
Web Building
Web Quality
Web Semantic
Web Careers
Weblogic Tutorial
SEO
Web Site Hosting
Domain Name
Java Tutorials
Java Tutorial
JSP Tutorial
Servlets Tutorial
Struts Tutorial
EJB Tutorial
JMS Tutorial
JMX Tutorial
Eclipse
J2ME
JBOSS
Programming Langauges
C Tutorial
C++ Tutorial
Visual Basic Tutorial
Data Structures Using C
Cobol
Assembly Language
Mainframe
Forth Programming
Lisp Programming
Pascal
Delphi
Fortran
OOPs
Data Warehousing
CGI Programming
Emacs Tutorial
Gnome
ILU
Soft Skills
Communication Skills
Time Management
Project Management
Team Work
Leadership Skills
Corporate Communication
Negotiation Skills
Database Tutorials
Oracle
MySQL
Operating System
BSD
Symbian
Unix
Internet
IP-Masquerading
IPC
MIDI
Software Testing
Testing
Firewalls
SAP Module
ERP
ABAP
Business Warehousing
SAP Basis
Material Management
Sales & Distribution
Human Resource
Netweaver
Customer Relationship Management
Production and Planning
Networking Programming
Corba Tutorial
Networking Tutorial
Microsoft Office
Microsoft Word
Microsoft Outlook
Microsoft PowerPoint
Microsoft Publisher
Microsoft Excel
Microsoft Front Page
Microsoft InfoPath
Microsoft Access
Accounting
Financial Accounting
Managerial Accounting
Network Sites


SQL-Schema Statements


Previoushome Next






SQL-Schema Statements


SQL-Schema Statements provide maintenance of catalog objects for a schema -- tables, views and privileges.

A D V E R T I S E M E N T
This subset of SQL is also called the Data Definition Language for SQL (SQL DDL).

There are 6 SQL-Schema Statements:




Schema Overview


A relational database contains a catalog that describes the various elements in the system. The catalog divides the database into sub-databases known as schemas. Within each schema are database objects -- tables, views and privileges.

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.


Tables

The database table is the root structure in the relational model and in SQL. A table (called a relation in relational) consists of rows and columns. In relational, rows are called tuples and columns are called attributes. Tables are often displayed in a flat format, with columns arrayed horizontally and rows vertically:

      C o l u m n s
    R
                           
                           
                           
                           
                           
    o
    w
    s

Database tables are a logical structure with no implied physical characteristics. Primary among the various logical tables is the base table. A base table is persistent and self contained, that is, all data is part of the table itself with no information dynamically derived from other tables.

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

The s table records suppliers. It has 3 defined columns:

  • sno -- supplier number, an unique identifier that is the primary key
  • name -- the name of the supplier
  • city -- the city where the supplier is located

At the current time, there are 3 rows.

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.


Views

A view is a derived table registered in the catalog. A view is defined using a SQL query. The view is dynamically derived, that is, its contents are materialized for each use. Views are added to the catalog with the CREATE VIEW Statement.

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:

  • The query FROM clause can reference a single table (or view)
  • The single table in the FROM clause must be:
    • a base table,
    • a view that is also an updatable view, or
    • a nested query that is updatable, that is, it follows the rules for an updatable view query.

  • The query must be a basic query, not a:

  • The select list cannot contain:
    • the DISTINCT specifier,
    • an Expression, or
    • duplicate column references

Subqueries are acceptable in updatable views but cannot reference the underlying base table for the view's FROM clause.

Privileges

SQL92 defines a SQL-agent as an implementation-dependent entity that causes the execution of SQL statements. Prior to execution of SQL statements, the SQL-agent must establish an authorization identifier for database access. An authorization identifier is commonly called a user name.

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:

  • All privileges on a table to the user that created the table. This includes the privilege to grant privileges on the table to other users.
  • SELECT (readonly) privilege on the catalog (the tables in the schema - definition_schema). This is granted to all users.

User granted privileges cover privileges to access and modify tables and their columns. Privileges can be granted for specific SQL-Data Statements -- SELECT, INSERT, UPDATE, DELETE.



CREATE TABLE Statement


The CREATE TABLE Statement creates a new base table. It adds the table description to the catalog. A base table is a logical entity with persistence. The logical description of a base table consists of:

  • Schema -- the logical database schema the table resides in
  • Table Name -- a name unique among tables and views in the Schema
  • Column List -- an ordered list of column declarations (name, data type)
  • Constraints -- a list of constraints on the contents of the table

The CREATE TABLE Statement has the following general format:

    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 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.

Constraints

Constraint specifications add additional restrictions on the contents of the table. They are automatically enforced by the DBMS. The column constraints are:

  • NOT NULL -- specifies that the column can't be set to null. If this constraint is not specified, the column is nullable, that is, it can be set to null. Normally, primary key columns are declared as NOT NULL.

     

  • PRIMARY KEY -- specifies that this column is the only column in the primary key. There can be only one primary key declaration in a CREATE TABLE. For primary keys with multiple columns, use the PRIMARY KEY table constraint. See Entity Integrity below for a detailed description of primary keys.

     

  • UNIQUE -- specifies that this column has a unique value or null for all rows of the table.

     

  • REFERENCES -- specifies that this column is the only column in a foreign key. For foreign keys with multiple columns, use the FOREIGN KEY table constraint. See Referential Integrity below for a detailed description of primary keys.

     

  • CHECK -- specifies a user defined constraint on the table. See the table constraint - CHECK, below.

The table constraints are:

  • PRIMARY KEY -- specifies the set of columns that comprise the primary key. There can be only one primary key declaration in a CREATE TABLE Statement. See Entity Integrity below for a detailed description of primary keys.

     

  • UNIQUE -- specifies that a set of columns have unique values (or nulls) for all rows in the table. The UNIQUE specifier is followed by a parenthesized list of column names, separated by commas.

     

  • FOREIGN KEY -- specifies the set of columns in a foreign key. See Referential Integrity below for a detailed description of foreign keys.

     

  • CHECK -- specifies a user defined constraint, known as a check condition. The CHECK specifier is followed by a predicate enclosed in parentheses. For Intermediate Level SQL92, the CHECK predicate can only reference columns from the current table row, with no subqueries. Many DBMSs support subqueries in the check predicate.

    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.)


Data Type

This subsection describes data type specifications. The data type categories are:

  • Character (String) -- fixed or variable length character strings. The character set is implementation defined but often defaults to ASCII.

     

  • Numeric -- values representing numeric quantities. Numeric values are divided into these two broad categories:
    • Exact (also known as fixed-point) -- Exact numeric values have a fixed number of digits to the left of the decimal point and a fixed number of digits to the right (the scale). The total number of digits on both sides of the decimal are the precision. A special subset of exact numeric types with a scale of 0 is called integer.
    • Approximate (also known as floating-point) -- Approximate numeric values that have a fixed precision (number of digits) but a floating decimal point.

    All numeric types are signed.

     

  • Datetime -- Datetime values include calendar and clock values (Date, Time, Timestamp) and intervals. The datetime types are:
    • Date -- calendar date with year, month and day
    • Time -- clock time with hour, minute, second and fraction of second, plus a timezone component (adjustment in hours, minutes)
    • Timestamp -- combination calendar date and clock time with year, month, day, hour, minute, second and fraction of second, plus a timezone component (adjustment in hours, minutes)
    • Interval -- intervals represent time and date intervals. They are signed. An interval value can contain a subset of the interval fields, for example - hour to minute, year, day to second. Interval types are subdivided into:
      • year-month intervals -- may contain years, months or combination years/months value.
      • day-time intervals -- days, hours, minutes, seconds, fractions of second.

Data type declarations have the following general format:

    Character (String)

     

      CHAR [(length)]
      CHARACTER [(length)]
      VARCHAR (length)
      CHARACTER VARYING (length)

      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

     

      SMALLINT
      INT
      INTEGER

      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

     

      DATE
      TIME [(scale)] [WITH TIME ZONE]
      TIMESTAMP [(scale)] [WITH TIME ZONE]

      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.


Interval Qualifier

An interval qualifier defines the specific type of an interval value. The qualifier for an interval type declares the sub-fields that comprise the interval, the precision of the highest (left-most) sub-field and the scale of the SECOND sub-field (if any).

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.

Entity Integrity

As mentioned earlier, the relational model requires that each base table have a Primary Key. SQL92, on the other hand, allows a table to created without a primary key. The advice here is to create all tables with primary keys.

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,

  • For a given row, the set of values for the primary key columns must be unique from all other rows in the table,
  • No primary key column can contain a null, and
  • A table can have only one primary key (set of primary key columns).

Note: SQL92 does not require the second restriction on nulls in the primary key. However, it is required for a relational system.

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:

  • table identifier
  • primary key value
  • column name

This capability is crucial to a relational system. Having a clear, consistent identifier for table rows (and their columns) distinguishes relational systems from all others. It allows the establishment of relationships between tables, also crucial to relational systems. This is discussed below under Referential Integrity.

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 KEY
    

As 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

Foreign keys provide relationships between tables in the database. In relational, a foreign key in a table is a set of columns that reference the primary key of another table. For each row in the referencing table, the foreign key must match an existing primary key in the referenced table. The enforcement of this constraint is known as Referential Integrity.

Referential Integrity requires that:

  • The columns of a foreign key must match in number and type the columns of the primary key in the referenced table.
  • The values of the foreign key columns in each row of the referencing table must match the values of the corresponding primary key columns for a row in the referenced table.

The one exception to the second restriction is when the foreign key columns for a row contain nulls. Since primary keys should not contain nulls, a foreign key with nulls cannot match any row in the referenced table. However, a row with a foreign key where any foreign key column contains null is allowed in the referencing table. No corresponding primary key value in the referenced table is required when any one (or more) of the foreign key columns is null. Other columns in the foreign key may be null or non-null. Such a foreign key is a null reference, because it does not reference any row in the referenced table.

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,

  • The referenced table is restricted from making the change (and violating referential integrity in the referencing table), or
  • Rows in the referencing table are modified so the referential integrity constraint is maintained.

These actions are controlled by the referential integrity effects declarations, called referential triggers by SQL92. The referential integrity effect actions defined for SQL are:

  • NO ACTION -- the change to the referenced (primary key) table is not performed. This is the default.
  • CASCADE -- the change to the referenced table is propagated to the referencing (foreign key) table.
  • SET NULL -- the foreign key columns in the referencing table are set to null.

Update and delete have separate action declarations. For CASCADE, update and delete also operate differently:

  • For update (the primary key column values have been modified), the corresponding foreign key columns for referencing rows are set to the new values.
  • For delete (the primary key row is deleted), the referencing rows are deleted.

A referential integrity constraint in the CREATE STATEMENT has two forms. When the foreign key consists of a single column, it can be declared as a column constraint, like:

    column-descr REFERENCES references-specification
    

As a table constraint, it has the following format:

    FOREIGN KEY (column-list) REFERENCES references-specification
    

column-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 Examples

Creating the example tables:

    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 Statement


The CREATE VIEW statement creates a new database view. A view is effectively a SQL query stored in the catalog. The CREATE VIEW has the following general format:

    
    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 Examples

Parts with suppliers:

    CREATE VIEW supplied_parts AS
                SELECT *
                FROM p
                WHERE pno IN (SELECT pno FROM sp)
           WITH CHECK OPTION
    

Access example:

    SELECT * FROM supplied_parts
    

    pno descr color
    P1 Widget Red
    P2 Widget Blue

Joined view:

    CREATE VIEW part_locations (part, quantity, location) AS
                SELECT pno, qty, city
                FROM sp, s
                WHERE sp.sno = s.sno
    

Access 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 Statement


The DROP TABLE Statement removes a previously created table and its description from the catalog. It has the following general format:

    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:

  • A view whose query specification references the drop table.
  • Another base table that references the drop table in a constraint - a CHECK constraint or REFERENCES constraint.

RESTRICT specifies that the table not be dropped if any dependencies exist. If dependencies are found, an error is returned and the table isn't dropped.

CASCADE specifies that any dependencies are removed before the drop is performed:

  • Views that reference the base table are dropped, and the sequence is repeated for their dependencies.
  • Constraints in other tables that reference this table are dropped; the constraint is dropped but the table retained.




DROP VIEW Statement


The DROP VIEW Statement removes a previously created view and its description from the catalog. It has the following general format:

    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:

  • A view whose query specification references the drop view.
  • A base table that references the drop view in a constraint - a CHECK constraint.

RESTRICT specifies that the view not be dropped if any dependencies exist. If dependencies are found, an error is returned and the view isn't dropped.

CASCADE specifies that any dependencies are removed before the drop is performed:

  • Views that reference the drop view are dropped, and the sequence is repeated for their dependencies.
  • Constraints in base tables that reference this view are dropped; the constraint is dropped but the table retained.




GRANT Statement


The GRANT Statement grants access privileges for database objects to other users. It has the following general format:

    GRANT privilege-list ON [TABLE] object-list TO user-list
    

privilege-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 Statement Examples

    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 Statement


The REVOKE Statement revokes access privileges for database objects previously granted to other users. It has the following general format:

    REVOKE privilege-list ON [TABLE] object-list FROM user-list
    

The 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 Statement Examples

    REVOKE SELECT ON s,sp FROM PUBLIC
    
    REVOKE SELECT,INSERT,UPDATE(color) ON p FROM art,nan
    
    REVOKE SELECT ON supplied_parts FROM sam
    



Be the first one to comment on this page.




  SQL Server eBooks

No eBooks on SQL Server could be found as of now.

 
 SQL Server FAQs
More Links » »
 
 SQL Server Interview Questions
More Links » »
 
 SQL Server Articles
More Links » »
 
 SQL Server News
More Links » »
 
 SQL Server Jobs
More Links » »

Share And Enjoy:These icons link to social bookmarking sites where readers can share and discover new web pages.
  • blinkbits
  • BlinkList
  • blogmarks
  • co.mments
  • connotea
  • del.icio.us
  • De.lirio.us
  • digg
  • Fark
  • feedmelinks
  • Furl
  • LinkaGoGo
  • Ma.gnolia
  • NewsVine
  • Netvouz
  • RawSugar
  • Reddit
  • scuttle
  • Shadows
  • Simpy
  • Smarking
  • Spurl
  • TailRank
  • Wists
  • YahooMyWeb

Previoushome Next

Keywords: SQL-Schema Statements, SQL Server, SQL Server, SQL Server Tutorial, SQL Server tutorial pdf, history of SQL Server, SQL Server Administration, learn SQL Server

HTML Quizzes
HTML Quiz
XHTML Quiz
CSS Quiz
TCP/IP Quiz
CSS 1.0 Quiz
CSS 2.0 Quiz
HLML Quiz
XML Quizzes
XML Quiz
XSL Quiz
XSLT Quiz
DTD Quiz
Schema Quiz
XForms Quiz
XSL-FO Quiz
XML DOM Quiz
XLink Quiz
XQuery Quiz
XPath Quiz
XPointer Quiz
RDF Quiz
SOAP Quiz
WSDL Quiz
RSS Quiz
WAP Quiz
Web Services Quiz
Browser Scripting Quizzes
JavaScript Quiz
VBScript Quiz
DHTML Quiz
HTML DOM Quiz
WMLScript Quiz
E4X Quiz
Server Scripting Quizzes
ASP Quiz
PERL Quiz
SQL Quiz
ADO Quiz
CVS Quiz
Python Quiz
Apple Script Quiz
PL/SQL Quiz
SQL Server Quiz
PHP Quiz
.NET (dotnet) Quizzes
Microsoft.Net Quiz
ASP.Net Quiz
.Net Mobile Quiz
C# : C Sharp Quiz
ADO.NET Quiz
VB.NET Quiz
VC++ Quiz
Multimedia Quizzes
SVG Quiz
Flash Quiz
Media Quiz
SMIL Quiz
Photoshop Quiz
Gimp Quiz
Matlab Quiz
Gnuplot Programming Quiz
GIF Animation Quiz
Scientific Visualization Quiz
Graphics Quiz
Web Building Quizzes
Web Browsers Quiz
Web Hosting Quiz
W3C Quiz
Web Building Quiz
Web Quality Quiz
Web Semantic Quiz
Web Careers Quiz
Weblogic Quiz
SEO Quiz
Web Site Hosting Quiz
Domain Name Quiz
Java Quizzes
Java Quiz
JSP Quiz
Servlets Quiz
Struts Quiz
EJB Quiz
JMS Quiz
JMX Quiz
Eclipse Quiz
J2ME Quiz
JBOSS Quiz
Programming Langauges Quizzes
C Quiz
C++ Quiz
Visual Basic Quiz
Data Structures Using C Quiz
Cobol Quiz
Assembly Language Quiz
Mainframe Quiz
Forth Programming Quiz
Lisp Programming Quiz
Pascal Quiz
Delphi Quiz
Fortran Quiz
OOPs Quiz
Data Warehousing Quiz
CGI Programming Quiz
Emacs Quiz
Gnome Quiz
ILU Quiz
Soft Skills Quizzes
Communication Skills Quiz
Time Management Quiz
Project Management Quiz
Team Work Quiz
Leadership Skills Quiz
Corporate Communication Quiz
Negotiation Skills Quiz
Database Quizzes
Oracle Quiz
MySQL Quiz
Operating System Quizzes
BSD Quiz
Symbian Quiz
Unix Quiz
Internet Quiz
IP-Masquerading Quiz
IPC Quiz
MIDI Quiz
Software Testing Quizzes
Testing Quiz
Firewalls Quiz
SAP Module Quizzes
ERP Quiz
ABAP Quiz
Business Warehousing Quiz
SAP Basis Quiz
Material Management Quiz
Sales & Distribution Quiz
Human Resource Quiz
Netweaver Quiz
Customer Relationship Management Quiz
Production and Planning Quiz
Networking Programming Quizzes
Corba Quiz
Networking Quiz
Microsoft Office Quizzes
Microsoft Word Quiz
Microsoft Outlook Quiz
Microsoft PowerPoint Quiz
Microsoft Publisher Quiz
Microsoft Excel Quiz
Microsoft Front Page Quiz
Microsoft InfoPath Quiz
Microsoft Access Quiz
Accounting Quizzes
Financial Accounting Quiz
Managerial Accounting Quiz
Testimonials | Contact Us | Link to Us | Site Map
Copyright 2008. Academic Tutorials.com. All rights reserved Privacy Policies | About Us
Our Portals : Academic Tutorials | Best eBooksworld | Beyond Stats | City Details | Interview Questions | Discussions World | Excellent Mobiles | Free Bangalore | Give Me The Code | Gog Logo | Indian Free Ads | Jobs Assist | New Interview Questions | One Stop FAQs | One Stop GATE | One Stop GRE | One Stop IAS | One Stop MBA | One Stop SAP | One Stop Testing | Webhosting in India | Dedicated Server in India | Sirf Dosti | Source Codes World | Tasty Food | Tech Archive | Testing Interview Questions | Tests World | The Galz | Top Masala | Vyom | Vyom eBooks | Vyom International | Vyom Links | Vyoms | Vyom World
Copyright 2003-2017 Vyom Technosoft Pvt. Ltd., All Rights Reserved.