Creating and Describe Table:
SQL> create table student209401(Rollno number(5) primary key,Fname char(10),Lname char(10),adds varchar2(25),DOB date);
Table created.
SQL> desc student209401;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO NUMBER(5)
FNAME CHAR(10)
LNAME CHAR(10)
ADDS VARCHAR2(25)
DOB DATE
SQL> create table stdnt_marks(rollno references student209401(rollno),seat_no char(5),sem1 number(3),sem2 number(3));
Table created.
SQL> desc stdnt_marks;
Name Null? Type
----------------------------------------- -------- --------------------------
ROLLNO NUMBER(5)
SEAT_NO CHAR(5)
SEM1 NUMBER(3)
SEM2 NUMBER(3)
SQL> CREATE TABLE STD_FEES( Rollno references student209401(ROLLNO),FEES_PAID NUMBER(5),FEES_BALANCE NUMBER(5));
Table created.
SQL> desc STD_FEES;
Name Null? Type
----------------------------------------- -------- ---------------------
ROLLNO NUMBER(5)
FEES_PAID NUMBER(5)
FEES_BALANCE NUMBER(5)
ALTER COMMANDS:
SQL> alter table student209401 modify(rollno number(6));
Table altered.
SQL> alter table student209401 add(mobno number(10));
Table altered.
SQL> alter table student209401 modify(rollno number(6) not null);
Table altered.
SQL> desc student209401;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO NOT NULL NUMBER(6)
FNAME CHAR(10)
LNAME CHAR(10)
ADDS VARCHAR2(25)
DOB DATE
MOBNO NUMBER(10)
INSERT AND SELECT COMMAND:
SQL> insert into student209401 values(2001,'Mansur','Shaikh','Neul','29-mar-92',9002017412);
1 row created.
SQL> insert into student209401 values(2002,'Raj','patil','Thane','29-Feb-92',9967200200);
1 row created.
SQL> insert into student209401 values(2003,'Manish','Shukala','Panvel',
'19-Feb-88',8067200100);
1 row created.
SQL> insert into student209401 values(2004,'Rajesh','Shukala','Panvel',
'11-Jan-86',8080202321);
1 row created.
SQL> insert into student209401 values(2005,'Manoj','Gupta','Nerul',
'01-Jan-82',8088200232);
1 row created.
SQL> select * from student209401;
ROLLNO FNAME LNAME ADDS DOB MOBNO
---------- ---------- ---------- ------------------------- --------- ----------
2001 Mansur Shaikh Neul 29-MAR-92 9002017412
2002 Raj patil Thane 29-FEB-92 9967200200
2003 Manish Shukala Panvel 19-FEB-88 8067200100
2004 Rajesh Shukala Panvel 11-JAN-86 8080202321
2005 Manoj Gupta Nerul 01-JAN-82 8088200232
SQL> insert into stdnt_marks values(2001,'A01',56,76);
1 row created.
SQL> insert into stdnt_marks values(2002,'A02',86,66);
1 row created.
;
SQL> insert into stdnt_marks values(2003,'A03',46,56);
1 row created.
SQL> insert into stdnt_marks values(2004,'A04',86,86);
1 row created.
SQL> select * from stdnt_marks;
ROLLNO SEAT_ NO SEM1 SEM2
---------- ----- ---------- -----------------------
2001 A01 56 76
2002 A02 86 66
2003 A03 46 56
2004 A04 86 86
FORMAT COMMAND:
SQL> column ADDS Format a10;
SQL> select * from student209401;
ROLLNO FNAME LNAME ADDS DOB MOBNO
---------- ---------- ---------- ------------------------- --------- ----------
2001 Mansur Shaikh Neul 29-MAR-92 9002017412
2002 Raj patil Thane 29-FEB-92 9967200200
2003 Manish Shukala Panvel 19-FEB-88 8067200100
2004 Rajesh Shukala Panvel 11-JAN-86 8080202321
2005 Manoj Gupta Nerul 01-JAN-82 8088200232
UPDATE TABLE:
SQL> Update stdnt_marks set sem1=50
WHERE rollno='2003';
1 row updated.
SQL> select * from stdnt_marks;
ROLLNO SEAT_NO SEM1 SEM2
---------- ----- ---------- -------------------------
2001 A01 56 76
2002 A02 86 66
2003 A03 50 56
2004 A04 86 86
USE OF WHERE:
SQL> select * from student209401 where Dob='29-mar-92';
ROLLNO FNAME LNAME ADDS DOB MOBNO
---------- ---------- ---------- ------------------------- --------- -----------------
2001 Mansur Shaikh Neul 29-MAR-92 9002017412
USE OF OR and AND:
SQL> select * from stdnt_marks where sem1 > 70 OR (sem1 < 40 AND sem1 > 80);
ROLLNO SEAT_ SEM1 SEM2
---------- ----- ---------- ----------
2002 A02 86 66
2004 A04 86 86
SQL> select * from stdnt_marks where sem2 > 70 OR (sem1 < 40 AND sem2 > 80);
ROLLNO SEAT_ SEM1 SEM2
---------- ----- ---------- ----------
2001 A01 56 76
2004 A04 86 86
USE OF IN:
SQL> select * from stdnt_marks WHERE sem1 IN ('46');
ROLLNO SEAT_ SEM1 SEM2
---------- ----- ---------- -----------------------
2003 A03 46 56
USE OF BETWEEN:
SQL> select * from student209401
2 WHERE dob BETWEEN '01-MAR-90' AND '01-MAR-94';
ROLLNO FNAME LNAME ADDS DOB MOBNO
---------- ---------- ---------- ------------------------- --------- ----------
2001 Mansur Shaikh Neul 29-MAR-92 9002017412
2002 Raj patil Thane 29-FEB-92 9967200200
USE OF LIKE:
SQL> select * from student209401 where Fname LIKE 'Rajesh';
ROLLNO FNAME LNAME ADDS DOB MOBNO
---------- ---------- ---------- ------------------------- --------- ----------
2004 Rajesh Shukala Panvel 11-JAN-86 8080202321
USE OF ORDER BY:
SQL> select *from stdnt_marks ORDER BY Sem1 Desc;
ROLLNO SEAT_ SEM1 SEM2
---------- ----- ---------- -------------------------
2002 A02 86 66
2004 A04 86 86
2001 A01 56 76
2003 A03 46 56
USE OF GROUP BY:
SQL> select rollno,Sum(sem1),Sum(sem2) from stdnt_marks group by rollno;
ROLLNO SUM(SEM1) SUM(SEM2)
---------- ---------- -----------------------------
2001 56 76
2002 86 66
2003 46 56
2004 86 86
Aggregate Functions :
SQL> select AVG(sem1) AVERAGE from stdnt_marks;
AVERAGE
----------
68.5
SQL> select Count(sem1) Count from stdnt_marks;
Count
----------
4
SQL> select SUM(sem1) SUM from stdnt_marks;
SUM
----------
274
SQL> select Max(sem1) Max from stdnt_marks;
MAX
----------
86
SQL> select Min(sem1) MIN from stdnt_marks;
MIN
------
46
String Function:
Initcap:
SQL> select initcap('western college') initcap from dual;
INITCAP
---------------
Western Collage
Concat:
SQL> select concat ('Western',' college') concat from dual;
CONCAT
--------------
Western college
Replace:
SQL> select replace ('black and blue','bl','j') REPLACE from dual;
REPLACE
------------
jack and jue
Upper:
SQL> select Upper('rdbms') from dual;
UPPER
-----
RDBMS
Lower:
SQL> select Lower('WCCBM') from dual;
LOWER
-----
wccbm
String Function:
Initcap:
SQL> select initcap('western college') initcap from dual;
INITCAP
---------------
Western Collage
Concat:
SQL> select concat ('Western',' college') concat from dual;
CONCAT
--------------
Western college
Replace:
SQL> select replace ('black and blue','bl','j') REPLACE from dual;
REPLACE
------------
jack and jue
Upper:
SQL> select Upper('rdbms') from dual;
UPPER
-----
RDBMS
Lower:
SQL> select Lower('WCCBM') from dual;
LOWER
-----
wccbm
Numeric Function:
Abs:
SQL> select abs(-100) from dual;
ABS(-100)
----------
100
Acos:
SQL> select Acos(0.5) from dual;
ACOS(0.5)
----------
1.04719755
Asin:
SQL> select Asin(0.5) from dual;
ASIN(0.5)
----------
.523598776
Atan:
SQL> select Atan(0.5) from dual
ATAN(0.5)
----------
.463647609
Ceil:
SQL> select ceil(12345.678) from dual;
CEIL(12345.678)
---------------
12346
Floor :
SQL>select floor(12345.678) from dual;
FLOOR(12345.678)
----------------
12345
Exp :
SQL> select exp(2) from dual;
EXP(2)
----------
7.3890561
Greatest :
SQL> select greatest(10,11,2001) greatest from dual;
GREATEST
---------
2001
Ln :
SQL> select ln(2) from dual;
LN(2)
----------
.693147181
Log :
SQL> select log(10,100) log from dual;
LOG
----
2
Mod :
SQL> select mod(3,2) from dual;
MOD(3,2)
--------
1
Power :
SQL> select power(3,2) power from dual;
POWER
-------
9
Round :
SQL> select round(1.2001233,3) ROUND from dual;
ROUND
-------
1.2001
Sqrt:
SQL> select sqrt(3) sqrt from dual;
sqrt
----------
1.73205081
------------------------------------------------------------------------------------------------
Date Function:
Sysdate:
SQL> select sysdate from dual;
SYSDATE
---------
02-MAR-11
Add_months :
SQL> select add_months(sysdate,1) from dual;
ADD_MONTH
---------
02-APR-11
+/-:
SQL> select sysdate +1 from dual;
SYSDATE-1
---------
03-MAR-11
select sysdate -1 from dual;
SYSDATE-1
---------
01-MAR-11
Last_day:
SQL> select last_day(sysdate) from dual;
LAST_DAY
---------
31-MAR-11
Months_between:
SQL> select months_between(sysdate,'1-jan-11') from dual;
MONTHS_BETWEEN
----------------
2.05192802
Next_day:
SQL> select next_day(sysdate,'Fri') from dual;
NEXT_DAY
---------
04-MAR-11
To_char:
SQL> select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') from dual;
TO_CHAR
----------------
02-mar-2011 02:01:04
------------------------------------------------------------------------------------------------------------
CREATE VIEW:
SQL>create view student209401_view
2 As select rollno,fname,lname
3 from student209401;
View created.
DESCRIBE VIEW:
SQL> desc student209401_view;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO NOT NULL NUMBER(5)
FNAME CHAR(10)
LNAME CHAR(10)
SELECT VIEW:
SQL> select * from student209401_view;
ROLLNO FNAME LNAME
--------------------- ---------- ----------
2001 Mansur Shaikh
2002 Raj patil
2003 Manish Shukala
2004 Rajesh Shukala
2005 Manoj Gupta
UPDATE VIEW:
SQL> update student209401_view set fname='Rohan'
2 where rollno='2005';
1 row updated.
SQL> select * from student209401_view;
ROLLNO FNAME LNAME
- -- ----------- ---------- ----------
2001 Mansur Shaikh
2002 Raj patil
2003 Manish Shukala
2004 Rajesh Shukala
2005 Rohan Gupta
DROP VIEW:
SQL> drop view student209401_view ;
View dropped.
Subquery :
SQL> select sem1 from STDNT_MARKS
2 where rollno in
3 (select rollno from student209401
4 Where rollno='2003')
SEM1
-------
50
SQL> select sem1 from STDNT_MARKS
2 where rollno in
3 (select rollno from student209401
4* Where rollno='2001')
SEM1
-------
56
---------------------------------------------------------------------------------------------------------
CREATING TABLE FROM EXISTING TABLE:
SQL> create table Std209401 as select * from student209401;
Table created.
SQL> select * from std209401;
ROLLNO FNAME LNAME ADDS DOB MOBNO
---------- ---------- ---------- ------------------------- --------- ----------
2001 Mansur Shaikh Neul 29-MAR-92 9002017412
2002 Raj patil Thane 29-FEB-92 9967200200
2003 Manish Shukala Panvel 19-FEB-88 8067200100
2004 Rajesh Shukala Panvel 11-JAN-86 8080202321
2005 Manoj Gupta Nerul 01-JAN-82 8088200232
REANME TABLE:
SQL> rename Std209401 to stdunet01;
Table renamed.
SQL> select * from stdunet01;
ROLLNO FNAME LNAME ADDS DOB MOBNO
---------- ---------- ---------- ------------------------- --------- ----------
2001 Mansur Shaikh Neul 29-MAR-92 9002017412
2002 Raj patil Thane 29-FEB-92 9967200200
2003 Manish Shukala Panvel 19-FEB-88 8067200100
2004 Rajesh Shukala Panvel 11-JAN-86 8080202321
2005 Manoj Gupta Nerul 01-JAN-82 8088200232
JOINTS:
SQL> select s1.rollno,s1.fname from student209401 S1,STDNT_MARKS s2
2* where s1.rollno=s2.rollno;
ROLLNO FNAME
---------- ----------
2001 Mansur
2002 Raj
2003 Manish
2004 Rajesh
SQL> select s1.rollno,s1.fname from student209401 S1,STDNT_MARKS s2
2* where s1.rollno=s2.rollno(+);
ROLLNO FNAME
------------------ ----------
2001 Mansur
2002 Raj
2003 Manish
2004 Rajesh
2005 Rohan
--------------------------------------------------------------------------------------------------
Delete From Statement :
SQL> delete from STUDENT209401 where rollno=2005;
1 row deleted.
SQL> select * from STUDENT209401;
ROLLNO FNAME LNAME ADDS DOB MOBNO
---------- ---------- ---------- ------------------------- --------- ----------------------
2001 Mansur Shaikh Neul 29-MAR-92 9002017412
2002 Raj patil Thane 29-FEB-92 9967200200
2003 Manish Shukala Panvel 19-FEB-88 8067200100
2004 Rajesh Shukala Panvel 11-JAN-86 8080202321
DROP TABLE:
SQL> drop table STDUNET209401;
Table dropped.
SQL> commit;
Commit complete.