DDL:(DATA DEFINATION LANGUAGE)
1.CREATE( TO CREATE NEW DATABASE)
2.ALTER(FOR MODIFYING ON EXISTING OBJ)
3.TRUNCATE(TO REMOVE ALL CONTENTS FROM TABLES)
4.DROP(REMOVE AN ENTIRE TABLE OR OTHER OBJ FROM DATABASE)
1.HOW TO CREATE TABLE:
SQL> create table student
2 (sno number(3), name varchar2(30), Dob date, grade varchar2(3));
Table created.
2.HOW TO CHECK OUR DESCRIBED TABLE
SQL> desc student;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNO NUMBER(3)
NAME VARCHAR2(30)
DOB DATE
GRADE VARCHAR2(3)
3. HOW TO MODIFY EXISTING OBJ:(ALTER)
A)RENAME COLUMN:
Alter table building
Rename column email_address to email;
B)Write a query to add new column "description" of type varchar(255) add not null constraint to building table.
alter table building
add(description varchar(255));
C)Write a query to remove the column 'address' from the 'building' table.
alter table building
Drop column address;
D)Change column type in building table:
alter table building
modify(owner_name varchar(500));
DML(DATA MANIPULATING LANGUAGE):
1.INSERT(INSERT A NEW ROW)
2.UPDATE(UPDATE EXISTING ROW)
3.SELECT(RETRIVE RECORDS FROM ONE OR MORE TAB.
1.HOW TO INSERT MULTIPLE ROWS:
SQL> insert into student values (101, 'Vardhan','17-nov-22','9');
1 row created.
SQL> insert into student values (102, 'Krupa','18-feb-22','9.5');
1 row created.
SQL> insert into student values (103, 'Maha','19-mar-22','8.6');
1 row created.
SQL> insert into student values (104, 'Loki','20-apr-22','9.6');
1 row created.
SQL> insert into student values (105, 'Inusha','23-oct-22','9.8');
1 row created.
2.HOW TO SEE OUR INSERTED TABLE:
SQL> select * from student;
SNO NAME DOB GRA
---------- ------------------------------ --------- ---
101 Vardhan 17-NOV-22 9
102 Krupa 18-FEB-22 9.5
103 Maha 19-MAR-22 8.6
104 Loki 20-APR-22 9.6
105 Inusha 23-OCT-22 9.8
106 Manaswitha 12-DEC-22 9.9
6 rows selected.
3.HOW TO UPDATE EXISTING ROW:
SQL> update student set grade=9.7 where sno=103;
1 row updated.
RESULT AFTER UPDATING:
select * from student;
SNO NAME DOB GRA
---------- ------------------------------ --------- ---
101 Vardhan 17-NOV-22 9
102 Krupa 18-FEB-22 9.5
103 Maha 19-MAR-22 9.7
104 Loki 20-APR-22 9.6
105 Inusha 23-OCT-22 9.8
106 Manaswitha 12-DEC-22 9.9
6 rows selected.
4.HOW TO DELETE PARTICULAR ROW:
SQL> delete from student where sno=101;
1 row deleted.
RESULT AFTER DELETING:
SQL> select * from student;
SNO NAME DOB GRA
---------- ------------------------------ --------- ---
102 Krupa 18-FEB-22 9.5
103 Maha 19-MAR-22 9.7
104 Loki2 20-APR-22 9
105 Inusha 23-OCT-22 9.8
106 Manaswitha 12-DEC-22 9.9
5.HOW TO DROP CONSTRAINTS:
ALTER TABLE Persons
DROP CONSTRAINT PK_Person;
DROP CONSTRAINT PK_Person;
ALTER TABLE Persons
DROP PRIMARY KEY;
DROP PRIMARY KEY;
