Oracle.txt

C:\>sqlplus SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 27 17:30:38 2018 Copyright (c) 1982, 2005, Oracle.

All rights reserved.

Enter user-name: system Enter password:manager Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

1) create table command SQL> CREATE TABLE STUDENT1(ROLLNO INT,SNAME VARCHAR(30),AGE INT); Table created. SQL> DESC STUDENT1 Name Null? ----------------------------------------- -------ROLLNO SNAME AGE

Type ---------------------------NUMBER(38) VARCHAR2(30) NUMBER(38)

2) insert data into table SQL> INSERT INTO STUDENT1(ROLLNO,SNAME,AGE)VALUES(1,'ABHISHEK',21); 1 row created. SQL> INSERT INTO STUDENT1(ROLLNO,SNAME,AGE)VALUES(2,'AMAN',22); 1 row created. SQL> INSERT INTO STUDENT1(ROLLNO,SNAME,AGE)VALUES(3,'AWADHESH',20); 1 row created. SQL> INSERT INTO STUDENT1(ROLLNO,SNAME,AGE)VALUES(4,'BHUVESH',20); 1 row created. SQL> INSERT INTO STUDENT1(ROLLNO,SNAME,AGE)VALUES(5,'DEVENDRA',20); 1 row created. SQL> INSERT INTO STUDENT1(ROLLNO,SNAME,AGE)VALUES(5,'MD TALIB',20); 1 row created. SQL> INSERT INTO STUDENT1(ROLLNO,SNAME,AGE)VALUES(7,'SAURABH',24); 1 row created. SQL> INSERT INTO STUDENT1(ROLLNO,SNAME,AGE)VALUES(8,'SHAKTI',22);

1 row created. SQL> INSERT INTO STUDENT1(ROLLNO,SNAME,AGE)VALUES(9,'SHRUTI',32); 1 row created.

3) select command SQL> SELECT * FROM STUDENT1; ROLLNO ---------1 2 3 4 5 5 7 8 9

SNAME AGE ------------------------------ ---------ABHISHEK 21 AMAN 22 AWADHESH 20 BHUVESH 20 DEVENDRA 20 MD TALIB 20 SAURABH 24 SHAKTI 22 SHRUTI 32

9 rows selected.

4) update command SQL> UPDATE STUDENT1 SET ROLLNO=6 WHERE SNAME='MD TALIB'; 1 row updated. SQL> SELECT * FROM STUDENT1; ROLLNO ---------1 2 3 4 5 6 7 8 9

SNAME AGE ------------------------------ ---------ABHISHEK 21 AMAN 22 AWADHESH 20 BHUVESH 20 DEVENDRA 20 MD TALIB 20 SAURABH 24 SHAKTI 22 SHRUTI 32

9 rows selected. SQL> UPDATE STUDENT1 SET ROLLNO=10,SNAME='SHRUTI SHAH',AGE=22 WHERE ROLLNO=9; 1 row updated. SQL> SELECT * FROM STUDENT1; ROLLNO SNAME

AGE

---------1 2 3 4 5 6 8 7 10

------------------------------ ---------ABHISHEK 21 AMAN 22 AWADHESH 20 BHUVESH 20 DEVENDRA 20 MD TALIB 20 SHAKTI 22 SAURABH 24 SHRUTI SHAH 22

9 rows selected.

5) delete command SQL> DELETE FROM STUDENT1 WHERE ROLLNO=9; 1 row deleted. SQL> SELECT * FROM STUDENT1; ROLLNO ---------1 2 3 4 5 6 7 8

SNAME AGE ------------------------------ ---------ABHISHEK 21 AMAN 22 AWADHESH 20 BHUVESH 20 DEVENDRA 20 MD TALIB 20 SAURABH 24 SHAKTI 22

8 rows selected.

6) commit and rollback command FOR SAVING ANY CHANGES INTO THE DATABSE.... ------------------------------------------------------------------------SQL> COMMIT; Commit complete. FOR UNDOING THE CHANGES MADE INTO THE DATABASE..BUT WE DON'T HAVE TO USE COMMIT COMMAND OR UNTIL THE COMMIT COMMAND IS USED WE CAN JUST ROLLBACK BY USING rollback COMMAND..... ------------------------------------------------------------------------SQL>ROLLBACK; Rollback complete. ______________________________________________

7) select command using conditions SQL> SELECT SNAME FROM STUDENT1 WHERE ROLLNO=4; SNAME -----------------------------BHUVESH SQL> SELECT SNAME FROM STUDENT1 WHERE AGE=20; SNAME -----------------------------AWADHESH BHUVESH DEVENDRA MD TALIB

8) select command with relational operators WE CAN USE >,<,>= AND <= SIGN EVEN IN SQL COMMANDS...... ___________________________________________________________________________________ SQL> SELECT SNAME FROM STUDENT1 WHERE AGE>20; SNAME -----------------------------ABHISHEK AMAN SHAKTI SAURABH SHRUTI SQL> SELECT SNAME FROM STUDENT1 WHERE AGE<20; no rows selected SQL> SELECT SNAME FROM STUDENT1 WHERE AGE<=20; SNAME -----------------------------AWADHESH BHUVESH DEVENDRA MD TALIB

9) AND and OR operator we can join 2 or more than 2 conditions using AND and OR operators.....! WHEN YOU HAVE TO GIVE 2 CONDITIONS WE USE "and"

________________________________________________________________ SQL> SELECT * FROM STUDENT1 WHERE ROLLNO=7 AND SNAME='SAURABH'; ROLLNO SNAME AGE ---------- ------------------------------ ---------7 SAURABH 24 SQL> SELECT * FROM STUDENT1 WHERE ROLLNO=7 AND SNAME='SAURABH' AND AGE=24; ROLLNO SNAME AGE ---------- ------------------------------ ---------7 SAURABH 24 USING "AND"/"OR" IN SQL..... ----------------------------------------------------------------------------SQL> SELECT * FROM STUDENT1 WHERE SNAME='SAURABH' OR SNAME='ABHISHEK' OR SNAME='MD TALIB'; ROLLNO ---------1 6 7

SNAME AGE ------------------------------ ---------ABHISHEK 21 MD TALIB 20 SAURABH 24

SQL> SELECT * FROM STUDENT1 WHERE SNAME='SAURABH' AND ROLLNO=7 AND AGE=24; ROLLNO SNAME AGE ---------- ------------------------------ ---------7 SAURABH 24

10) distinct and count command distinct command shows distinct names/age/rollno present in the table count command counts the total number of distinct names/age/rollno present in the table...! SQL> SELECT DISTINCT SNAME FROM STUDENT1; SNAME -----------------------------BHUVESH SHAKTI AWADHESH SHRUTI ABHISHEK

AMAN SAURABH DEVENDRA MD TALIB 9 rows selected. SQL> SELECT COUNT(DISTINCT SNAME) FROM STUDENT1; COUNT(DISTINCTSNAME) -------------------9

11) "order by" command is used for sorting the data into the particular table "ORDER BY" IN SQL.... _______________________________________________________________

SQL> SELECT * FROM STUDENT1 ORDER BY AGE; ROLLNO ---------5 6 4 3 9 1 8 2 7

SNAME AGE ------------------------------ ---------DEVENDRA 20 MD TALIB 20 BHUVESH 20 AWADHESH 20 SHRUTI 21 ABHISHEK 21 SHAKTI 22 AMAN 22 SAURABH 24

9 rows selected.

12) min AND max COMMAND.... SQL> SELECT MAX(AGE) FROM STUDENT1 WHERE SNAME='SAURABH'; MAX(AGE) ---------24 SQL> SELECT MIN(AGE) FROM STUDENT1 WHERE SNAME='MD TALIB'; MIN(AGE) ---------20

13) count, sum, avg COMMAND.....

SQL> SELECT COUNT(AGE) FROM STUDENT1; COUNT(AGE) ---------9 SQL> SELECT SUM(AGE) FROM STUDENT1; SUM(AGE) ---------191 SQL> SELECT AVG(AGE) FROM STUDENT1; AVG(AGE) ---------21.2222222

14) if the user wants to list the items starting or ending with any specific character.... we use LIKE command.... select * from student1 where sname like 'a%'; 'a' select * from student1 where sname like '%a'; select * from student1 where sname like '%a%'; between

---> list the names starting with ---> list the names ending with 'a' ---> list the names having 'a' in

SQL> SELECT SNAME FROM STUDENT1 WHERE SNAME LIKE 'A%'; SNAME -----------------------------ABHISHEK AMAN AWADHESH SQL> SELECT SNAME FROM STUDENT1 WHERE SNAME LIKE '%I'; SNAME -----------------------------SHAKTI SQL> SELECT SNAME FROM STUDENT1 WHERE SNAME LIKE '%A%'; SNAME -----------------------------ABHISHEK AMAN

AWADHESH DEVENDRA MD TALIB SHAKTI SAURABH SHRUTI SHAH 8 rows selected.

15) in AND bewteen COMMAND.... SQL> SELECT * FROM STUDENT1 WHERE SNAME IN ('SAURABH','AMAN','BHUVESH'); ROLLNO ---------2 4 7

SNAME AGE ------------------------------ ---------AMAN 22 BHUVESH 20 SAURABH 24

SQL> SELECT * FROM STUDENT1 WHERE AGE BETWEEN 22 AND 24; ROLLNO ---------2 8 7 9

SNAME AGE ------------------------------ ---------AMAN 22 SHAKTI 22 SAURABH 24 SHRUTI SHAH 22

16) alter TABLE COMMAND SQL> ALTER TABLE STUDENT2 MODIFY CITY VARCHAR(20); Table altered. SQL> DESC STUDENT2; Name Null? ----------------------------------------- -------ROLLNO NOT NULL SNAME MARKS CITY

17) drop TABLE COMMAND

SQL> DROP TABLE STUDENT2;

Type ---------------------------NUMBER(38) VARCHAR2(30) FLOAT(4) VARCHAR2(20)

Table dropped. SQL> DESC STUDENT2; ERROR: ORA-04043: object STUDENT2 does not exist

18) create table using primary key SQL> CREATE TABLE STUDENT2(ROLLNO INT NOT NULL PRIMARY KEY,SNAME VARCHAR(30),MARKS FLOAT(4),CITY VARCHAR(20)); Table created. SQL> DESC STUDENT2; Name Null? ----------------------------------------- -------ROLLNO NOT NULL SNAME MARKS CITY

Type ---------------------------NUMBER(38) VARCHAR2(30) FLOAT(4) VARCHAR2(20)

19) rename COMMAND SQL> ALTER TABLE STUDENT1 RENAME COLUMN AGE TO SAGE; Table altered. SYNTAX: ALTER TABLE table_name RENAME COLUMN column_name_old TO column_name_new

20) JOIN COMMANDS..... INNER JOIN IN SQL....

SQL> SELECT STUDENT1.AGE,STUDENT2.MARKS,STUDENT1.SNAME FROM STUDENT1 INNER JOIN STUDENT2 ON STUDENT1.ROLLNO=STUDENT2.ROLLNO; AGE MARKS SNAME ---------- ---------- ------------------------21 470 ABHISHEK 22 430 AMAN 20 300 AWADHESH 20 350 BHUVESH 20 460 DEVENDRA 20 260 MD TALIB 22 460 SHAKTI 24 160 SAURABH 22 360 SHRUTI SHAH

19

440 PRADYUMAN

10 rows selected. SELECT table_name1.coulmn1,table_name1.column2,table_name2.coulmn1 FROM table_name1 INNER JOIN table_name2 ON table_name1.column1=table_name2.coulmn1;

21) between... SQL> select * from student1 where age between 15 and 20; ROLLNO ---------3 4 5 6 10

SNAME AGE ------------------------- ---------AWADHESH 20 BHUVESH 20 DEVENDRA 20 MD TALIB 20 PRADYUMAN 19