Basic SQL Fundamental

Data Definition Language(DDL)

Create Statement

It is used to create tables to store data by executing the SQL create table statement. To create a table, a user must have the CREATE TABLE privilege And a storage area in which to create objects. The database administrator uses data control language statements to grant privileges to users.

Syntax:  CREATE TABLE TABLE_NAME( COLUMN_NAME DATATYPE,…………., COULUMN_NAME_N DATATYPE_N);

Alter Statement

It is used to add a new column, to modify an existing column, to define a default value for the new column, or to drop a column.

Syntax:

# To add column-

ALTER TABLE table

ADD  (column datatype [DEFAULT expr]

           [, column datatype]...);

# To modify a table

ALTER TABLE table

MODIFY   (column datatype [DEFAULT expr]

                 [, column datatype]...);

# To drop a column from a table

ALTER TABLE table

DROP   (column);

Drop Statement

It is used to drop all data and structure of a table. When you issue a drop command any pending transaction will be committed. It also dropped all the constraints of the table and it cann’t be rollback again.

Syntax:

DROP TABLE table_name;

Rename Statement

It is used to change the table column name or constraint name.

Syntax:

ALTER TABLE table RENAME old_column_name to new_column_name;

ALTER TABLE table RENAME old_constraint_name to new_constraint_name;

Transaction Control Language(TCL)

Database Transactions begin when the first DML SQL statement is executed and end with one of the following statements-

# A COMMIT or ROLLBACK statement is issued

# A DDL or DCL statement executes (automatic commit)

# The user exits iSQL*Plus

# The system crashes.

Savepoint Statement

It is used to create a marker in a current transaction by using the SAVEPOINT statement.

Syntax:

Delete from table…;

SAVEPOINT ONE;

Rollback Statement

It is used to discard all pending changes. It is also used to undone data changes which restores the data the data to the previous state. It also release the locks on the affected rows.

Syntax:

DELETE FROM table;

ROLLBACK;

This undone the delete table.

ROLLBACK TO SAVEPOINT; (If savepoint has been created.)

Commit Statement

An automatic commit occurs under the following circumstances

# DDL statement is issued

# DCL statement is issued

# Normal exit from iSQL*Plus/SQL*Plus, without explicitly issuing COMMIT or ROLLBACK statements

# An automatic rollback occurs under an abnormal termination of iSQL*Plus or a system failure.

The commit statement is used to make the data change permanent in the database. The previous state of the database is permanently lost. After commit locks on the affected rows are released and all the savepoints are erased.

Data Control Language(DCL)

The data control language(DCL) is use to give and remove privileges from the user mainly by the SYS or SYSTEM user. Other users who are having the privileges can also grant and remove the privileges under certain circumstances.

GRANT Statement

It is use to give permission to the users so that the users can access the database or schema.

Syntax:

GRANT privilege_names to user_name;

REVOKE Statement

It is use to remove the privilege/s granted to the users.

Syntax:

REVOKE privilege_name from user_name

For example:

SQL> create user megha identified by megha;

User created.

SQL> grant connect, resource to megha;

Grant succeeded.

SQL> revoke connect, resource from megha;

Revoke succeeded.