This starts Data Control Language (DCL) commands. See Table Creation and SQL is DML, DDL, & DCL.
While a DataBase Administrator (DBA) will normally perform the DDL and DCL functions, you need to know how they work so that you can convey your needs to the DBA.Security is a major part of the RDBMS systems. SQL makes access to data very easy, so access must be scrutinized carefully for company and corporate databases. Database objects must be entered by an individual called a user and, to do this, they must be given maintenance privileges.

Each user of an SQL database is assigned a user-ID. This is a short name that identifies this individual as a person or as a member of a group. You must have a user-ID before you can access the database. The user-ID with its associated privileges determines what the user can or can not do. In a production database, a database administrator (DBA) assigns the user-ID.

The SQL standard refers to the authorization-ID. Here we refer to the user-ID as the identification used to obtain authorization through privileges to access database objects.

> The SQL1 standard allowed the user-ID to be up to 18 characters without spaces.

> DB2 allows a user-ID of up to 8 characters and uses the MVS/TSO user name.

> Informix and Ingres use the host computer’s operating system UNIX user name.

> SQL Server and Sybase allow a user-ID of up to 30 characters.

> For portability between systems, it is suggested that the user-ID be between 1 and 8 characters.

Format:DROP SCHEMA schema_name {RESTRICT | CASCADE};
This command will remove the schema.

If RESTRICT is specified, the schema will not be deleted if all objects in the schema have not been dropped.

If CASCADE is specified, the schema will be deleted with all objects contained in the schema.
Example:

DROP SCHEMA CBT_INC CASCADE;

Format:

          CREATE SCHEMA schema_name [user_id]
                [DEFAULT CHARACTER SET char_set_name];

A schema is a set of database objects that is owned by a user called a schema owner.
The schema owner has authority to give privileges to other users.
Example:

          CREATE SCHEMA CBT_INC USER1;

> All RDBMS vendors have their own parameters for defining a schema.

When you use the CREATE TABLE statement, you are the table’s owner with SELECT, INSERT, UPDATE, DELETE, and any other privileges allowed by your RDBMS system.You can not use the CREATE VIEW statement without having at least a SELECT privilege on each of the source tables for the view, so you automatically have the SELECT privilege on the view. If you already have the INSERT, UPDATE, or DELETE privileges on the source or base table, then these privileges are automatically given to you on the view by the RDBMS system.

There are other complications that are illustrated in the examples in our course.

Format:

          DROP ALIAS alias_name;

                                     or

          DROP SYNONYM synonym_name;

This command will remove the alias or synonym.
Example:

          DROP SYNONYM PILOTS;

> Aliases and synonyms are not an SQL standard.

> DB2, Informix, and Oracle implement ALIAS and SYNONYM.

> MySQL does not implement ALIAS or SYNONYM.

Format:

          CREATE [PUBLIC | PRIVATE] ALIAS alias_name
             FOR {table_name | view_name};

or

          CREATE [PUBLIC | PRIVATE] SYNONYM synonym_name
             FOR {table_name | view_name};

An alias or synonym produces another name for a table or view.
The normal use of an alias or symonym is to avoid having to qualify another user’s table or view.
An alias or synonym simplifies the SQL statement and make it appear to be your own table.

A PRIVATE alias or synonym can only be accessed by the creator of the alias or synonym.
If unspecified, PRIVATE is the default.

A PUBLIC alias or synonym is accessible to all users of the database.
Normally only a Database Administrator or a privileged security user can create with PUBLIC.

Example:

          CREATE SYNONYM PILOTS FOR USER1.PILOT_TBL;

> Aliases and synonyms are not an SQL standard.

> DB2, Informix, and Oracle implement ALIAS and SYNONYM.

> MySQL does not implement ALIAS or SYNONYM.

DB2, Oracle, PostgreSQL Format:

          DROP INDEX index_name;

Access, MySQL Format:

          DROP INDEX index_name
            ON table_name;

SQL Server Format:

          DROP INDEX table_name.index_name;

Because an index is completely separate from its associated table, it can be deleted at any time without any effect on the base table other than performance issues. All SQL code will continue to function normally.

Most RDBMS systems have tools for measuring performance. If a table is small, or the data is infrequently referenced, you may choose to remove the index.

The SQL standard does not mention indices so vendors do their own implementation.

Format:

          CREATE {UNIQUE} INDEX index_name
              ON table_name (
                   column_name1 {ASC | DESC},
                   column_name2 {ASC | DESC},
                   ...);

A simple index contains only a single column.

A composite index contains multiple columns from a single table.
The index applies to all the columns in the index definition.
These columns do not have to be adjacent in the table.
Instead, they can be taken from any column in any order.
The order in the index definition is important and is defined left to right, going from major to minor sort order.

Your can create multiple indices on the same table.

ASC means ascending sort order, which is the default and does not need to be specified.
DESC means descending sort order and must be specified to reverse the sequence.
UNIQUE forces the combination of indexed columns to be unique.
> Access, MySQL, and SQL Server index names must be unique within a table.

> DB2, Oracle, and PostgreSQL index names must be unique within the database.

When people try to adjust a database for performance, a big part of the discussion is the creation of indices.
A basic concept of SQL databases is that the data has no order.
The advantage of this lack of order is that it allows the RDBMS to INSERT, UPDATE, and DELETE rows quickly.
The disadvantage is that finding data in rows using searching and sorting is slow (but it will work). Searching
a small database is easy, but searching a large production database with millions of rows can be slow.The RDBMS system provides the INDEX as a way of solving this problem. Basically, an index is a sorted file of values with pointers to the physical locations of the indexed data. When you request a specific value, this value is located in the sorted index and the values can be retrieved using the pointers to the rows of matching data. This way, the RDBMS system does not need to read the entire table to retrieve only those matching rows. The trade-off is that data retrieval is much faster, but INSERT, UPDATE, and DELETE are slower because the index must be updated. Each RDBMS system handles indices differently.

An index is automatically created for a PRIMARY KEY without the user’s knowledge. Automatic creation of an index for a FOREIGN KEY or constraint such as UNIQUE depends upon your RDBMS system. Once an index is created, its use is invisible to the user. No changes are made to any SELECT, INSERT, UPDATE, or DELETE statements. Creation or removal of an index only affects performance.
You may wish to create an index for:

          WHERE      Search data
          ORDER BY   Sorted data
          GROUP BY   Grouped data
          JOIN       Joined data

You use a DROP VIEW command to delete a view.
Format:

          DROP VIEW view_name;

 

> Dropping a table does not remove a view, but a view can not function without a base table.
> A table can be empty (no data) without being dropped.