Program statement: An experiment on basic SQL commands.

DDL COMMANDS

1.       CREATE The create statement is used to create database objects like tables, indexes, views, stored procedures etc in a database. Table creation syntax is as follows.

Syntax

CREATE TABLE table_name(column_name1 data_type(size),column_name2 data_type(size),column_name3 data_type(size),....);

Example

SQL>CREATE TABLE STUDENT (SID NUMBER(10),SNAME VARCHAR2(5),SADDRESS VARCHAR2(5));

Table created.

2.       Alter:  This command is used for to change the structure of a table. This command is used with following options.

ADD: This is used for to add new columns. And syntax is as follows.

Syntax: ALTER table TableName  add newcolumnname datatype(size);

Example:

SQL> ALTER table student add smobileno number(10);

SQL>Table altered.

MODIFY This option is used to modify the column data type and its size.

Syntax: Alter table tablename modify columnname newdatatype(size);

Example: ALTER Table student modify sid varchar2(10);

SQL>Table altered.

Drop: This option is used for to remove columns from Table.

Syntax: ALTER TABLE TABLENAME DROP COLUMNNAME;

EXAMPLE:

SQL> ALTER TABLE STUDENT DROP SADDRESS;

SQL>Table Altered.

Drop: This command is used for to remove objects from the database.

Syntax DROP Table tablename;

Example   SQL>DROP TABLE student;

SQL>Table dropped.

DML Commands

1.       Insert   This command is used to insert the records into a table.

Syntax: INSERT INTO TABLENAME  VALUES(VALUE1,VALUE2,VALUE3….);

To Insert multiple rows into a Table the following sysntax is used.

Syntax: Insert into tablename values(&col1,’&col2’,&col3……..);

Example: SQL>INSERT INTO STUDENT VALUES(‘125N1F0001’,’RAJA’,’KDP’,9999999999);

1 ROW CREATED.

SQL> INSERT INTO STUDENT VALUES(‘&SID’,’&SNAME’,’&SADDRESS’,&SMOBILENO);

Enter SID:125N1F0002

Enter SNAME:SEKHAR

Enter SADDRESS:HYD

Enter SMobileNo:666666666

SQL>1 row created

SQL>/

SQL>Enter SID:125N1F0003

Enter SNAME:RANI

Enter SADDRESS:CHENNAI

Enter SMobileNo:8888888888

SQL> 1 row created

SQL>SELECT * FROM STUDENT;

UPDATE: This  command IS Used to Update a column value in a Table.

Syntax: UPDATE Table name SET Col=newvalue WHERE [CONDITION LIST];

Ex: SQL> UPDATE student set sname=’ravi’ where sid=’12%N1F0001’;

SQL>1 row updated

Delete: This command is used to delete records from a Table.

Syntax: delete from tablename;

Example: SQL>delete from student;

                SQL>3 rows deleted.

DQL Commands

Select: This command is used for to display the records from a table.

Syntax: select [column list] from tablename;

Example: SQL>select Sid,Sname from student;

Rename: This command is used to change name of a table.

Syntax: RENAME oldtablename TO new tablename;

Example: SQL>RENAME student to student111;

SQL>Table Renamed.