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.

Like it on Facebook, Tweet it or share this article on other bookmarking websites.

No comments