DDL Commands:
Create, Alter, Drop
Create: It is used to create a new table.
Syntax:
SQL> create table table-name (column1 data-type(size), column2 data-type(size),, );
Ex:
SQL> create table ambedkar
2 (sno number(3), sname varchar2(10), doj date);
Table created.
SQL> desc ambedkar;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNO NUMBER(3)
SNAME VARCHAR2(10)
DOJ DATE
Alter: It is used to modify the specified table.
Syntax:
SQL> alter table table-name
Add / modify / drop (column-name data-type(size));
Ex:
SQL> alter table ambedkar
2 add (city varchar2(10));
Table altered.
SQL> desc ambedkar;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNO NUMBER(3)
SNAME VARCHAR2(10)
DOJ DATE
CITY VARCHAR2(10)
SQL> alter table ambedkar
2 modify (sno number(5));
Table altered.
SQL> desc ambedkar;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNO NUMBER(5)
SNAME VARCHAR2(10)
DOJ DATE
CITY VARCHAR2(10)
SQL> alter table ambedkar
2 drop(city);
Table altered.
SQL> desc ambedkar;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNO NUMBER(5)
SNAME VARCHAR2(10)
DOJ DATE
Drop: It is used to delete the table with data.
Syntax:
SQL> drop table table-name;
Ex:
SQL> drop table ambedkar;
Table dropped.
SQL> desc ambedkar;
ERROR:
ORA-04043: object ambedkar does not exist
DML Commands:
Insert, update, delete, select.
Insert: It is used to insert the records in the specified table.
Syntax1:
SQL> insert into table-name
Values(value1, value2, …);
The above command is used to insert the one record with all columns.
SQL> insert into ambi
2 values(101,'ambedkar','01-nov-24');
1 row created.
If want to see the records in the specified table.
SQL> select * from ambi;
SNO SNAME DOJ
---------- ---------- ---------
101 ambedkar 01-NOV-24
SQL> insert into ambi
2 values(101,'ambedkar');
insert into ambi
*
ERROR at line 1:
ORA-00947: not enough values
Syntax2:
SQL> insert into table-name (column-nam1, column-name2,,,)
Values(value1, value2, …);
The above command is used to insert the one record with specified columns.
Ex:
SQL> insert into ambi (sno,sname)
2 values(102,'siva');
1 row created.
SQL> select * from ambi;
SNO SNAME DOJ
---------- ---------- ---------
101 ambedkar 01-NOV-24
102 siva
Syntax3:
SQL> insert into table-name
Values(&value1, &value2, …);
The above command is used to insert the number of records with all columns.
SQL> insert into ambi
2 values (&sno,'&sname','&doj');
Enter value for sno: 103
Enter value for sname: raju
Enter value for doj: 02-oct-24
old 2: values (&sno,'&sname','&doj')
new 2: values (103,'raju','02-oct-24')
1 row created.
SQL> /
Enter value for sno: 104
Enter value for sname: anu
Enter value for doj: 03-jan-24
old 2: values (&sno,'&sname','&doj')
new 2: values (104,'anu','03-jan-24')
1 row created.
Syntax4:
SQL> insert into table-name (column-name1, column-name2,,,)
Values(&value1, &value2, …);
The above command is used to insert the number of records with specified columns.
SQL> insert into ambi(sno,sname)
2 values(&sno,'&sname');
Enter value for sno: 105
Enter value for sname: rani
old 2: values(&sno,'&sname')
new 2: values(105,'rani')
1 row created.
SQL> /
Enter value for sno: 106
Enter value for sname: jaya
old 2: values(&sno,'&sname')
new 2: values(106,'jaya')
1 row created.
SQL> select * from ambi;
SNO SNAME DOJ
---------- ---------- ---------
101 ambedkar 01-NOV-24
102 siva
103 raju 02-OCT-24
104 anu 03-JAN-24
105 rani
106 jaya
6 rows selected.
Update: It is used to modify the specified records.
Syntax1:
SQL> update table-name
Set column-name1=value, column-name2=value,,,
Where condition;
The above command is used to update the specified records only.
Ex:
SQL> update ambi
2 set doj='01-oct-24'
3 where sno=101;
1 row updated.
SQL> select * from ambi;
SNO SNAME DOJ
---------- ---------- ---------
101 ambedkar 01-OCT-24
102 siva 02-NOV-24
103 raju 02-NOV-24
104 anu 02-NOV-24
105 rani 02-NOV-24
106 jaya 02-NOV-24
6 rows selected.
Syntax2:
SQL> update table-name
Set column1=value, column2=value,,,;
The above command is update the all records.
SQL> update ambi
2 set doj='02-nov-24';
6 rows updated.
SQL> select * from ambi;
SNO SNAME DOJ
---------- ---------- ---------
101 ambedkar 02-NOV-24
102 siva 02-NOV-24
103 raju 02-NOV-24
104 anu 02-NOV-24
105 rani 02-NOV-24
106 jaya 02-NOV-24
6 rows selected.
Drop: It is used to delete the table with data.
Syntax:
SQL > drop table table-name;
SQL> drop table ambi;
Table dropped.
SQL> select * from ambi;
select * from ambi
*
ERROR at line 1:
ORA-00942: table or view does not exist