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;