DBMS BASIC COMMANDS

Different types of commands in SQL:

A).DDL commands: - To create a database objects

B).        DML commands: - To manipulate data of a database objects

C).        DQL command: - To retrieve the data from a database.

D).        DCL/DTL commands: - To control the data of a database…

DDL commands:

1. The Create Table Command: - it defines each column of the table uniquely. Each column has minimum of three attributes, a name , data type and size.

Syntax:

Create table <table name> (<col1> <datatype>(<size>),<col2> <datatype><size>));

Ex:

    create table emp(empno number(4) primary key, ename char(10));

2. Modifying the structure of tables.

a)add new columns

Syntax:

Alter table <tablename> add(<new col><datatype(size),<new col>datatype(size));

Ex:

alter table emp add(sal number(7,2));

3. Dropping a column from a table.

Syntax:

Alter table <tablename> drop column <col>;

Ex:

alter table emp drop column sal;

4. Modifying existing columns.

Syntax:

Alter table <tablename> modify(<col><newdatatype>(<newsize>));

Ex:

alter table emp modify(ename varchar2(15));

5. Renaming the tables

Syntax:

Rename <oldtable> to <new table>;

Ex:

rename emp to emp1;

6. truncating the tables.

Syntax:

Truncate table <tablename>;

Ex:

trunc table emp1;

7. Destroying tables.

Syntax:

Drop table <tablename>;

Ex:

drop table emp;

DML commands:

8. Inserting Data into Tables: - once a table is created the most natural thing to do is load this table with data to be manipulated later.

Syntax:

insert into <tablename> (<col1>,<col2>) values(<exp>,<exp>);

9. Delete operations.

a) remove all rows

Syntax:

 delete from <tablename>;

b) removal of a specified row/s

Syntax:

 delete from <tablename> where <condition>;

10. Updating the contents of a table.

a) updating all rows

Syntax:

Update <tablename> set <col>=<exp>,<col>=<exp>;

b) updating seleted records.

Syntax:

Update <tablename> set <col>=<exp>,<col>=<exp>
where  <condition>;

11. Types of data constrains.

a) not null constraint at column level.

Syntax:

<col><datatype>(size)not null

b) unique constraint

Syntax:

Unique constraint at column level.

<col><datatype>(size)unique;

c) unique constraint at  table level:

Syntax:

Create table  tablename(col=format,col=format,unique(<col1>,<col2>);

d) primary key constraint at column level

Syntax:

<col><datatype>(size)primary key;

e) primary key constraint at table level.

Syntax:

Create table tablename(col=format,col=format

primary key(col1>,<col2>);

f) foreign key constraint at column level.

Syntax:

<col><datatype>(size>) references <tablename>[<col>];

g) foreign key constraint at table level

        Syntax:

foreign key(<col>[,<col>])references <tablename>[(<col>,<col>)

h) check constraint

check constraint  constraint at column level.

Syntax: <col><datatype>(size) check(<logical expression>)

i) check constraint  constraint at table level.

Syntax: check(<logical expression>)

DQL Commands:

12. Viewing data in the tables: - once data has been inserted into a table, the next most logical operation would be to view what has been inserted.

a) all rows and all columns

Syntax:

Select <col> to <col n> from tablename;

                 Select * from tablename;

13. Filtering table data: - while viewing data from a table, it is rare that all the data from table will be required each time. Hence, sql must give us a method of filtering out data that is not required data.

a) Selected columns and all rows:

Syntax:

select <col1>,<col2> from <tablename>;

b) selected rows and all columns:

Syntax:

select * from <tablename> where <condition>;

c) selected columns and selected rows

Syntax:

select <col1>,<col2> from <tablename> where<condition>;

14. Sorting data in a table.

Syntax:

Select * from <tablename> order by <col1>,<col2> <[sortorder]>;

DCL commands:

Oracle provides extensive feature in order to safeguard information stored in its tables from unauthoraised viewing and damage.The rights that allow the user of some or all oracle resources on the server are called privileges.

a) Grant privileges using the GRANT statement

The grant statement provides various types of access to database objects such as tables,views and sequences and so on.

Syntax:

GRANT <object privileges>

ON <objectname>

TO<username>

[WITH GRANT OPTION];

b) Reoke permissions using the REVOKE statement:

The REVOKE statement is used to deny the Grant  given on an object.

Syntax:

REVOKE<object privilege>

ON

FROM<user name>;