Different types of SQL commands

Different types of SQL commands are

·        Data retrieval retrieves data from the database, for example, SELECT.

·        Data Manipulation Language (DML) inserts new rows, changes existing rows, and removes unwanted rows, for example, CREATE, ALTER, DROP, RENAME and TRUNCTATE.

·        Transaction Control manages and changes logical transactions. Transactions are changes made to the data by DML statements grouped together for example, COMMIT, SAVE POINT and ROLLBACK.

·        Data Control Language (DCL) gives and removes rights to database objects, for example GRANT and REVOKE.

Create Table:-

  Two types of creating table:

          a) Create table

          b) Create table ... as select

a) Create Table

  This command is used to create a new relation and the corresponding syntax is:

Syntax

CREATE TABLE relation_name (col1 datatype(size), col2 datatype(size),....,coln datatype(size));

Example:-

  The Mordern book house mostly supplies books to institutions which frequently buy books from them. Various relatoins used are Customer, Sales, Book Author, and publisher. Design database scheme for the same.

  1) The customer table definition is as follows:

Create table customer (cust_no varchar(4) primery key, cust_name    varchar(25),cust_add varchar(30), cust_ph varchar(15));

 b) Create table ... as select

      This type of create command is used to crate the structure of a new table from the structure of existing table.

Syntax

CREATE TABLE relation_name1 (col1,col2,...,coln) AS SELECT col1,col2,...,coln FROM relation_name2;

Example:

  Create the structure for special customer from the structure of CUSTOMER table.

   => create table special_customer

         (cust_no,cust_name,cust_add)

         as select cust_no,cust_name,cust_add

         from customer;

Alter Table:-

  Alter table command is divided into two parts they are

  a) ALTER TABLE --.ADD ...

  b) ALTER TABLE ...MODIFY ...

a) ALTER TABLE --.ADD ...

  This is used to add some extra columns into an existing table. The generalized fornat is given below.

Syntax

ALTER TABLE relation_name ADD (new_col1 datatype(size), col2_datatype(size),...,new_coln datatype(size));

Example:-

  Add customer phone number and fax number in the customer relation.

=> ALTER TABLE customer

      ADD(cust_ph_no varchar(15),

      Cust_fax_no varchar(15));

  b) ALTER TABLE ... MODIFY

     This form is used to change the width as well as data type of existing relations. The feneralized syntax of this form is shown below.

Syntax

ALTER TABLE relation_name MODIFY (col1 new_datatype(size), col2 new_datatype(size),....,coln new_datatype(size));

Example:-

  Modify the data type of the publication year as numeric data type

  => ALTER TABLE book MODIFY (pub_year number(4));

Restriction of the Alter Table

  Using the alter table clause you cannot perform the following tasks:

·        Change the name of the table

·        Change the name of the column

·        Drop a column

·        Decrease the size of a column if table data exists

Drop Table

  This command is used to delete a table. The generalized syntax of this form is given below:

Syntax

DROP TABLE relation_name

Example:-

  Write the command for deleting special-customer relation.

  => DROP TABLE special_customer;

Renaming a Table

   You can rename a table provided you are the owner of the table.

Syntax

RENAME old_table_name TO new_table_name;

Example:-

 => RENAME test To test_info;

Truncating a Table

  Truncating a table is removing all records from the table. The structure of the table stays intact. The SQL language has a DELETE statement which can be used to remove one or more (or all) rows from a table. Truncation releases storage space occupied by the table, but deletion does not.

Syntax

TRUNCATE TABLE table_name;

Example:-

  => TRUNCATE TABLE student;