SYNTAX
CREATE TABLE
EXAMPLE
SQL> create table studentlak(regno number(10),
2 name varchar(15),
3 dob date,
4 address varchar(50),
5 primary key(regno));
Table created.
DESCRIPTION
SYNTAX
DESC
EXAMPLE
SQL> DESC STUDENTLAK;
Name Null? Type
REGNO NOT NULL NUMBER(10)
NAME VARCHAR2(15)
DOB DATE
ADDRESS VARCHAR2(50)
INSERT VALUES
SYNTAX
INSERT INTO table_name(column1,column2….)VALUES (value1,value2..);
EXAMPLE
SQL> insert into studentlak values(®no,'&name','&dob','&address');
Enter value for regno: 100
Enter value for name: LAKSHMI
Enter value for dob: 01-MAY-1988
Enter value for address: NO.32 PANDIAN ST, R.M.K NAGAR CHENNAI-48
old 1: insert into studentlak values(®no,'&name','&dob','&address')
new 1: insert into studentlak values(100,'LAKSHMI','01-MAY-1988','NO.32 PANDIAN ST, R.M.K NAGAR CH
1 row created.
SQL> /
Enter value for regno: 101
Enter value for name: KUMAR
Enter value for dob: 13-MAR-1990
Enter value for address: NO.50 RANIPET, VELLORE
old 1: insert into studentlak values(®no,'&name','&dob','&address')
new 1: insert into studentlak values(101,'KUMAR','13-MAR-1990','NO.50 RANIPET, VELLORE')
1 row created.
SQL> /
Enter value for regno: 103
Enter value for name: SAHANA
Enter value for dob: 26-MAR-1990
Enter value for address: NEW MAIN ROAD VIRUDHUNAGAR
old 1: insert into studentlak values(®no,'&name','&dob','&address')
new 1: insert into studentlak values(103,'SAHANA','26-MAR-1990','NEW MAIN ROAD VIRUDHUNAGAR')
1 row created.
SELECT TABLE
SYNTAX
1. SELECT * FROM
2. SELECT
EXAMPLE
SQL> SELECT * FROM STUDENTLAK;
REGNO NAME DOB ADDRESS
100 LAKSHMI 01-MAY-88 NO.32 PANDIAN ST, R.M.K NAGAR CHENNAI-48
101 KUMAR 13-MAR-90 NO.50 RANIPET, VELLORE
103 SAHANA 26-MAR-90 NEW MAIN ROAD VIRUDHUNAGAR
SQL> SELECT regno, name FROM STUDENTLAK;
REGNO NAME
100 LAKSHMI
101 KUMAR
103 SAHANA
ALTER TABLE
ADD Column
SYNTAX
ALTER TABLE
EXAMPLE
SQL> alter table studentlak add(phno number(10),phno1 number(10));
Table altered.
SQL> desc studentlak;
Name Null? Type
REGNO NOT NULL NUMBER(10)
NAME VARCHAR2(15)
DOB DATE
ADDRESS VARCHAR2(50)
PHNO NUMBER(10)
PHNO1 NUMBER(10)
RENAME A TABLE
SYNTAX
ALTER TABLE
EXAMPLE
SQL> alter table studentlak rename to studentlakshmi;
Table altered.
SQL> select * from studentlakshmi;
REGNO NAME DOB ADDRESS
100 LAKSHMI 01-MAY-88 NO.32 PANDIAN ST, R.M.K NAGAR CHENNAI-48
101 KUMAR 13-MAR-90 NO.50 RANIPET, VELLORE
103 SAHANA 26-MAR-90 NEW MAIN ROAD VIRUDHUNAGAR
UPDATE Column Values
SYNTAX
Update
EXAMPLE
SQL> alter table studentlakshmi add(phno1 NUMBER(10));
Table altered.
SQL> UPDATE STUDENTLAKSHMI SET PHNO='9440001250' WHERE REGNO=100;
1 row updated.
SQL> UPDATE STUDENTLAKSHMI SET PHNO='125469871' WHERE REGNO=101;
1 row updated.
SQL> UPDATE STUDENTLAKSHMI SET PHNO='9440001233' WHERE REGNO=103;
· DROP Column
SYNTAX
Alter table
EXAMPLE
SQL> ALTER TABLE STUDENTLAKSHMI DROP COLUMN ADDRESS;
Table altered.
SQL> SELECT * FROM STUDENTLAKSHMI;
REGNO NAME DOB PHNO PHNO1
100 LAKSHMI 01-MAY-88 9440001250 9785649870
101 KUMAR 13-MAR-90 125469871 9785333000
103 SAHANA 26-MAR-90 9440001233 9785322111
TRUNCATE TABLE
SQL>Create table emp1(eno number(3),ename varchar2(25));
Table Created
SQL> TRUNCATE TABLE EMP1;
Table truncated.
SQL> SELECT * FROM EMP1;
no rows selected
DROP TABLE
SQL> DROP TABLE EMP1;
Table dropped.
SQL> DESC EMP1;
ERROR:
ORA-04043: object EMP1 does not exist
TO CREATE A VIEW
SYNTAX
CREATE OR REPLACE view< tablename>as SELECT coulumn1=”” where {condition};
EXAMPLE
SQL> create or replace view new as select * from STUDENTLAKSHMI;
View created.
SQL> SELECT * FROM NEW;
REGNO NAME DOB PHNO PHNO1
100 LAKSHMI 01-MAY-88 9440001250 9785649870
101 KUMAR 13-MAR-90 125469871 9785333000
103 SAHANA 26-MAR-90 9440001233 9785322111
SQL> SELECT * FROM STUDENTLAKSHMI;
REGNO NAME DOB PHNO PHNO1
100 LAKSHMI 01-MAY-88 9440001250 9785649870
101 KUMAR 13-MAR-90 125469871 9785333000
103 SAHANA 26-MAR-90 9440001233 9785322111
INSERT THE VALUES INTO VIEW (new) TABLE
SQL> insert into new values(®no,'&name','&dob',&phno,&phno1);
Enter value for regno: 3060
Enter value for name: hema
Enter value for dob: 25-oct-1980
Enter value for phno: 9874563214
Enter value for phno1: 9874563218
old 1: insert into new values(®no,'&name','&dob',&phno,&phno1)
new 1: insert into new values(3060,'hema','25-oct-1980',9874563214,9874563218)
1 row created.
SQL> /
Enter value for regno: 3061
Enter value for name: hemalatha
Enter value for dob: 22-feb-1977
Enter value for phno: 9825647584
Enter value for phno1: 1258963478
old 1: insert into new values(®no,'&name','&dob',&phno,&phno1)
new 1: insert into new values(3061,'hemalatha','22-feb-1977',9825647584,1258963478)
1 row created.
SQL> select * from new;
REGNO NAME DOB PHNO PHNO1
---------- --------------- --------- ---------- ----------
100 LAKSHMI 01-MAY-88 9440001250 9785649870
101 KUMAR 13-MAR-90 125469871 9785333000
103 SAHANA 26-MAR-90 9440001233 9785322111
3060 hema 25-OCT-80 9874563214 9874563218
3061 hemalatha 22-FEB-77 9825647584 1258963478
SQL> create or replace view new1 as select * from studentlakshmi with read only;
View created.
SQL> insert into new1 values(®no,'&name','&dob',&phno,&phno1);
Enter value for regno: 300
Enter value for name: lak
Enter value for dob: 30-oct-1985
Enter value for phno: 2589631478
Enter value for phno1: 9632587415
old 1: insert into new1 values(®no,'&name','&dob',&phno,&phno1)
new 1: insert into new1 values(300,'lak','30-oct-1985',2589631478,9632587415)
insert into new1 values(300,'lak','30-oct-1985',2589631478,9632587415)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
SQL> drop view new1;
View dropped.
