experiment no 1 .1 (1)
TRANSCRIPT
-
8/8/2019 Experiment No 1 .1 (1)
1/44
DATABASE MANAGEMENT SYSTEM
LAB
(Subject Code: ETCS-357)
FACULTY NAME: Mrs. Neetu Gupta STUDENT NAME : AshishChaudhary
ROLL NO :0361482808
SEMESTER :V
BATCH :ECE 123
MAHARAJA AGRESEN INSTECEUTE OF
TECHNOLOGY
PSP AREA,SECTOR-22,
ROHINI,NEW DELHI-110085
-
8/8/2019 Experiment No 1 .1 (1)
2/44
Experiment no- 1
Table creation-
SQL> connect
Enter user-name: system
Enter password:
Connected.
SQL> create table student ( name varchar2(10),roll_no number(3),branch varchar2(10));
Table created.
Adding data into tables-
Add data to all attributes in the order mentioned during table
creation:add a record in student table-
SQL> insert into student values('ashish','101','ECE');
1 row created.
Add data to all the attributes in the different order:add a record in
student table
SQL> insert into student(roll_no,branch,name) values('102',' ECE','vinay');
1 row created.
Add data to few attributes:add a record to student table (only to
roll_no and name)
SQL> insert into student(roll_no,name) values(103,'mayank');
1 row created.
-
8/8/2019 Experiment No 1 .1 (1)
3/44
Adding multiple rows using one query-
SQL> insert into student values('&name','&roll','&branch');
Enter value for name: smith
Enter value for roll: 104
Enter value for branch: ECE
old 1: insert into student values('&n ame','&roll','&branch')
new 1: insert into student values('smith','104',' ECE')
1 row created.
SQL> /
Enter value for name: siya
Enter value for roll: 201
Enter value for branch: CSE
old 1: insert into student values('&name','&roll','&branch')
new 1: insert into student values('siya','201','CSE')
1 row created.
SQL> /
Enter value for name: amit
Enter value for roll: 202
Enter value for branch: CSE
old 1: insert into student values('&name','&roll','&branch')
new 1: insert into student values('amit','202','CSE')
-
8/8/2019 Experiment No 1 .1 (1)
4/44
1 row created.
SQL> /
Enter value for name: tripti
Enter value for roll: 203
Enter value for branch: CSE
old 1: insert into student values('&name','&roll','&branch')
new 1: insert into student values('tripti','203','CSE')
1 row created.
SQL> /
Enter value for name: tripti
Enter value for roll: 203
Enter value for branch: CSE
old 1: insert into student values('&name','&roll','&branch')
new 1: insert into student values('tripti','203','CSE')
1 row created.
Viewing data of a table-
All columns and all rows - to view all the details of all the students
from student table
SQL> select * from student;
-
8/8/2019 Experiment No 1 .1 (1)
5/44
NAME ROLL_NO BRANCH
------------------------------
ashish 101 ECE
vinay 102 ECE
mayank 103
smith 104 ECE
siya 201 CSE
amit 202 CSE
tripti 203 CSE
tripti 203 CSE
8 rowsselected.
All columns and selected rows - to view all the details of all the
students in ECE branch from student table-
SQL>select*fromstudentwherebranch='ECE';
NAME ROLL_NO BRANCH
------------------------------
ashish 101 ECE
vinay 102 ECE
smith 104 ECE
selected columns and all rows to view roll_no and name of all the
students from student table-
SQL>selectroll_no,namefromstudent;
-
8/8/2019 Experiment No 1 .1 (1)
6/44
ROLL_NO NAME
--------------------
101 ashish
102 vinay
103 mayank
104 smith
201 siya
202 amit
203 tripti
203 tripti
8 rowsselected.
Selected columns and selected rows to view roll number and
name of all the students in ECE branch from student table
SQL>selectroll_no,namefromstudentwherebranch='ECE';
ROLL_NO NAME
--------------------
101 ashish
102 vinay
104 smith
Eliminatingduplicaterowswhenusingselectstatement:
To view the distinct branch names from student table
SQL>selectdistinct(branch)fromstudent;
-
8/8/2019 Experiment No 1 .1 (1)
7/44
BRANCH
----------
ECE
CSE
Toviewthedatainsortedform-
Sorted in ascending order to view the details of all the students
order by name in ascending order:
SQL>select*fromstudentorderbyname;
NAME ROLL_NO BRANCH
------------------------------
amit 202 CSE
ashish 101 ECE
mayank 103
vinay 102 ECE
siya 201 CSE
smith 104 ECE
tripti 203 CSE
tripti 203 CSE
8 rowsselected.
OR
SQL>select*fromstudentorderbynameasc;
-
8/8/2019 Experiment No 1 .1 (1)
8/44
NAME ROLL_NO BRANCH
------------------------------
amit 202 CSE
ashish 101 ECE
mayank 103
vinay 102 ECE
siya 201 CSE
smith 104 ECE
tripti 203 CSE
tripti 203 CSE
8 rowsselected.
Sorted in descending order to view the details of all the students
order by name in ascending order:
SQL>select*fromstudentorderbynamedesc;
NAME ROLL_NO BRANCH
------------------------------
tripti 203 CSE
tripti 203 CSE
smith 104 ECE
siya 201 CSE
vinay
102
ECE
mayank 103
ashish 101 ECE
amit 202 CSE
8 rowsselected.
-
8/8/2019 Experiment No 1 .1 (1)
9/44
Creatingtablefromanexistingtable:
SQL>create
table
student1(sname,sroll,sbranch)
as
select
name,roll_no,branch
fromstudent;
Tablecreated.
Addingdataintoatablefromanexistingtable:
SQL>createtablestudent2(s_namevarchar2(10),s_roll number(3),s_branch
varchar2(10));
Tablecreated.
Query to add data from existing table:
SQL>insertintostudent2selectname,roll_no,branch fromstudent;
8 rowscreated.
-
8/8/2019 Experiment No 1 .1 (1)
10/44
Experiment No 2
Deletingrecordsorrowsfromatable
Deleting few rows:
Status before deleting
SQL>select*fromstudent2;
S_NAME S_ROLL S_BRANCH
---------- ---------- ----------
ashish 101 ECE
vinay 102 ECE
mayank 103
smith 104 ECE
siya 201 CSE
amit 202 CSE
tripti 203 CSE
tripti 203 CSE
8 rowsselected.
Delete all the details of roll number 101 from student2 table:
SQL>deletefromstudent2wheres_roll=101;
1rowdeleted.
-
8/8/2019 Experiment No 1 .1 (1)
11/44
Status after deletion
SQL>select*fromstudent2;
S_NAME S_ROLL S_BRANCH
---------- ---------- ----------
vinay 102 ECE
mayank 103
smith 104 ECE
siya 201 CSE
amit 202 CSE
tripti 203 CSE
tripti 203 CSE
7 rowsselected.
Deleting all the rows:
Status before deletion
SQL>select*fromstudent2;
S_NAME S_ROLL S_BRANCH
------------------------------
vinay 102 ECE
mayank 103
smith 104 ECE
siya 201 CSE
amit
202
CSE
tripti 203 CSE
tripti 203 CSE
7 rowsselected.
-
8/8/2019 Experiment No 1 .1 (1)
12/44
To delete all the records of student2 table
SQL>deletefromstudent2;
7 rowsdeleted.
Status after deletion
SQL>select*fromstudent2;
norowsselected
updatingthecontentsofatable:
updation using where clause:update the name of a student from
Riya to Siya in student table:
status of student table before updation:
SQL>select*fromstudent;
NAME ROLL_NO BRANCH
---------- ---------- ----------
ashish 101 ECE
vinay 102 ECE
mayank 103
smith 104 ECE
siya 201 CSE
amit 202 CSE
tripti 203 CSE
tripti 203 CSE
8 rowsselected.
Update query
SQL>updatestudentsetname='riya'wherename='siya';
-
8/8/2019 Experiment No 1 .1 (1)
13/44
1rowupdated.
Status of student table after updation
SQL>select*fromstudent;
NAME ROLL_NO BRANCH
------------------------------
ashish 101 ECE
vinay 102 ECE
mayank 103
smith 104 ECE
riya 201 CSE
amit 202 CSE
tripti 203 CSE
tripti 203 CSE
8 rowsselected.
Update without where clause: add 100 to roll number of all the
studemts in student table:
SQL>updatestudentsetroll_no=roll_no+100;
8 rowsupdated.
Status of student table after updation:
SQL>select*fromstudent;
NAME ROLL_NO BRANCH
---------- ---------- ----------
ashish 201 ECE
vinay 202 ECE
mayank 203
-
8/8/2019 Experiment No 1 .1 (1)
14/44
smith 204 ECE
riya 301 CSE
amit 302 CSE
tripti 303 CSE
tripti 303 CSE
8 rowsselected.
Modifyingstructureofatable
Adding new columns to a table: add contact (numeric size-10) to
student table
SQL>altertablestudentadd(contactnumber(10));
Tablealtered.
Status of student table after adding new column:
SQL>select*fromstudent;
NAME ROLL_NO BRANCH CONTACT
---------- ---------- ---------- ----------
ashish 201 ECE
vinay 202 ECE
mayank 203
smith 204 ECE
riya 301 CSE
amit 302 CSE
tripti 303 CSE
tripti 303 CSE
8 rowsselected.
-
8/8/2019 Experiment No 1 .1 (1)
15/44
Deleting existence column of a table : delete the contact
attribute from student table
SQL>altertablestudentdropcolumncontact;
Tablealtered.
Status of student table after droping contact attribute:
SQL>select*fromstudent;
NAME ROLL_NO BRANCH
---------- ---------- ----------
ashish 201 ECE
vinay 202 ECE
mayank 203
smith 204 ECE
riya 301 CSE
amit 302 CSE
tripti 303 CSE
tripti 303 CSE
8 rowsselected.
-
8/8/2019 Experiment No 1 .1 (1)
16/44
Modifying the column datatype or size: change the branch attribute
datatype from varchar2 to char and size from 10 to 12
SQL>altertablestudentmodify(branchchar(12));
Tablealtered.
Renamingatable
Change the name of the table student1 to s1:
SQL>renamestudent1tos1;
Tablerenamed.
Truncatingatable:
Status of s1 before truncate command
SQL>select*froms1;
SNAME SROLL SBRANCH
---------- ---------- ----------
ashish 101 ECE
vinay 102 ECE
mayank 103
smith 104 ECE
siya 201 CSE
amit 202 CSE
tripti 203 CSE
-
8/8/2019 Experiment No 1 .1 (1)
17/44
tripti 203 CSE
8 rowsselected.
Query:
SQL>truncatetables1;
Tabletruncated.
Status of s1 after truncate command
SQL>select*froms1;
norowsselected
destroyingtable:destroys1table:
SQL>droptables1;
Tabledropped.
Findingoutthetable/screatedbyauser
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
STUDENT TABLE
STUDENT2 TABLE
SQLPLUS_PRODUCT_PROFILE TABLE
PRODUCT_PRIVS VIEW
PRODUCT_USER_PROFILE SYNONYM
HELP TABLE
-
8/8/2019 Experiment No 1 .1 (1)
18/44
6 rowsselected.
Displaying thetablestructure:
SQL>descstudent;
Name Null? Type
----------------------------------------- ------------------------------------
NAME VARCHAR2(10)
ROLL_NO NUMBER(3)
BRANCH CHAR(12)
OR
SQL>describestudent;
Name Null? Type
----------------------------------------- ------------------------------------
NAME VARCHAR2(10)
ROLL_NO NUMBER(3)
BRANCH CHAR(12)
-
8/8/2019 Experiment No 1 .1 (1)
19/44
Experimentno-3
Primarykey
Primary key declaration at column level
SQL>createtablestudent(roll_nonumber(3)primarykey,namevarchar2(10),branch
varchar2(10));
Tablecreated.
Primary key declaration at table level
SQL>createtablestudent1(roll_nonumber(3),namevarchar2(10),branchvarchar2(1
0),primarykey(roll_no));
Tablecreated.
Foreignkey
Foreign key declaration at column level
SQL>createtableresult1(roll_no number(3)referencesstudent(roll_no),mathsnum
ber(3),sciencenumber(3),english number(3));
Tablecreated.
Foreign key declaration at the table level
SQL>createtableresult(roll_nonumber(3),mathsnumber(3),sciencenumber(3),eng
lishnumber(3),foreignkey(roll_no)referencesstudent1(roll_no));
Tablecreated.
Uniqueconstraint
Unique constraint declaration at column level
SQL>createtableemp(e_idvarchar2(4)primarykey,e_namevarchar2(10),e_contact
number(10)unique);
Tablecreated.
-
8/8/2019 Experiment No 1 .1 (1)
20/44
Unique constraint declaration at table level
SQL>createtableemp1(e_idvarchar2(4)primarykey,e_namevarchar2(10),e_contact
number(10),unique(e_contact));
Table
created.
Notnullconstraint(can only be declared at column level)
SQL>createtableemp2(e_idvarchar2(4)primarykey,e_namevarchar2(10)not
null,e_contactnumber(10));
Tablecreated.
Checkconstraint
Check constraint declaration at column level
SQL>createtableemp3(e_idvarchar2(4)primarykeycheck(e_idlike'E%'),e_name
varchar2(10)not nullcheck(e_name=upper(e_name)),e_depttvarchar2(3)check(e_deptt
in('ECE','CSE','EEE','ECE','MAE')));
Tablecreated.
Check constraint declaration at table level
SQL>createtableemp4(e_idvarchar2(4)primarykey,e_namevarchar2(10)not
null,e_depttvarchar2(3),check(e_id like
'E%'),check(e_name=upper(e_name)),check(e_depttin('ECE','CSE','EEE','ECE','MAE')));
Tablecreated.
Naming theconstraintsduringtablelevelcreation
Assigning names to primary key,notnull,unique,check at column
level
-
8/8/2019 Experiment No 1 .1 (1)
21/44
SQL>createtablestudents(rollnumber(3)constraintpkprimarykey,namevarchar2(10)
constraintckcheck(name=initcap(name)),branchvarchar2(10)constraintnnnot
null,contact number(10)constraintuunique);
Tablecreated.
Assigning names to primary key,unique,check at table level
SQL>createtablestudents1(rollnumber(3),namevarchar2(10),branch
varchar2(10),contactnumber(10),constraint pk1primarykey(roll),constraint ck1 check
(name=initcap(name)),constraint u1unique(contact));
Tablecreated.
Assigning name to foreign key at column level
SQL>createtableresult(rollnumber(3)constraintfkreferencesstudent(roll_no),math
number(3),sciencenumber(3),englishnumber(3));
Tablecreated.
Assigning name to foreign key at table level
SQL>createtableresult1(rollnumber(3),mathsnumber(3),sciencenumber(3),english
number(3),constraint fk1foreignkey(roll)referencesstudent(roll_no));
Tablecreated.
Settingconstraintsusingaltercommand
First of all tables student,student1,result,result1 are created as
follows
SQL>createtablestudent(rollnumber(3),name varchar2(10),branchvarchar2(10),contact
number(10));
-
8/8/2019 Experiment No 1 .1 (1)
22/44
Tablecreated.
SQL>createtablestudent1(rollnumber(3),namevarchar2(10),branch
varchar2(10),contactnumber(10));
Tablecreated.
Query to create result table:
SQL>createtableresult(rollnumber(3),mathsnumber(3),sciencenumber(3),english
number(3));
Tablecreated.
SQL>createtableresult1(rollnumber(3),mathsnumber(3),sciencenumber(3),english
number(3));
Tablecreated.
Primary key,unique and check constraints
SQL>altertablestudentadd(primary
key(roll),check(name=upper(name)),unique(contact));
Tablealtered.
Adding constraints with name
SQL>altertablestudent1add(constraintpk2primarykey(roll),constraint ck2
check(name=upper(name)),constraint u2unique(contact));
Tablealtered.
-
8/8/2019 Experiment No 1 .1 (1)
23/44
Notnull
Adding null constraint without name
SQL>altertablestudentmodify(branchnotnull);
Tablealtered.
Adding null constraint with name
SQL>altertablestudent1modify(branchconstraintnn1notnull);
Tablealtered.
Foreignkey
Adding foreign key constraint without name
SQL>altertableresultaddforeignkey(roll)referencesstudent(roll);
Tablealtered.
Adding foreign key constraint with name
SQL>altertableresult1addconstraintfk2foreignkey(roll)referencesstudent(roll);
Tablealtered.
Droping
constraints
Foreignkey
Dropping foreign key using constraint name
SQL>altertableresult1dropconstraintfk2;
-
8/8/2019 Experiment No 1 .1 (1)
24/44
Tablealtered
Experimentno-4
Computation done on table data:
Created a table employee and add data to it as follows:
SQL>createtableemployee(e_name varchar2(10),e_idvarchar2(4)primarykey,e_deptt
varchar2(3),e_salarynumber(10));
Tablecreated
SQL>insertintoemployeevalues('&e_name','&e_id','&e_deptt','&salary');
Entervaluefore_name:amar
Entervaluefore_id:E101
Entervaluefore_deptt:ECE
Entervalueforsalary:20000
old 1:insertintoemployeevalues('&e_name','& e_id','&e_deptt','&salary')
new 1:insertintoemployeevalues('amar','E101',' ECE','20000')
1rowcreated.
SQL>/
Entervaluefore_name:ashish
Entervaluefore_id:E102
Entervaluefore_deptt:ECE
Entervalueforsalary:30000
old 1:insertinto employeevalues('&e_name','&e_id','&e_deptt','&salary')
-
8/8/2019 Experiment No 1 .1 (1)
25/44
new 1:insertintoemployeevalues('ashish','E102','ECE','30000')
1rowcreated.
SQL>/
Entervaluefore_name:sonali
Entervaluefore_id:E103
Entervaluefore_deptt:ECE
Entervalueforsalary:25000
old 1:insertintoemployeevalues('&e_name','&e_id','&e_deptt','&salary')
new 1:insertintoemployeevalues('sonali','E103',' ECE','25000')
1rowcreated.
SQL>/
Entervalue
for
e_name:
dinesh
Entervaluefore_id:E104
Entervaluefore_deptt:ECE
Entervalueforsalary:28000
old 1:insertintoemployeevalues('&e_name','&e_id','&e_deptt','&salary')
new 1:insertintoemployeevalues('dinesh','E104',' ECE','28000')
1rowcreated.
SQL>/
Entervaluefore_name:neha
-
8/8/2019 Experiment No 1 .1 (1)
26/44
Entervaluefore_id:E105
Entervaluefore_deptt:ECE
Entervalueforsalary:45000
old 1:insertintoemployeevalues('&e_name','&e_id','&e_deptt','&salary')
new 1:insertintoemployeevalues('neha','E105',' ECE','45000')
1rowcreated.
SQL>/
Entervaluefore_name:kanika
Entervaluefore_id:E106
Entervaluefore_deptt:CSE
Entervalueforsalary:30000
old 1:insertintoemployeevalues('&e_name','&e_id','&e_deptt','&salary')
new 1:insertintoemployeevalues('kanika','E106','CSE','30000')
1rowcreated.
SQL>/
Entervaluefore_name:mohit
Entervaluefore_id:E107
Enter
value
for
e_deptt:
CSE
Entervalueforsalary:19000
old 1:insertintoemployeevalues('&e_name','&e_id','&e_deptt','&salary')
new 1:insertintoemployeevalues('mohit','E10 7','CSE','19000')
-
8/8/2019 Experiment No 1 .1 (1)
27/44
1rowcreated.
SQL>/
Entervaluefore_name:smith
Entervaluefore_id:E108
Entervaluefore_deptt:CSE
Entervalueforsalary:45000
old 1:insertintoemployeevalues('&e_name','&e_id','&e_deptt','&salary')
new 1:insertinto employeevalues('smith','E108','CSE','45000')
1rowcreated.
SQL>/
Entervaluefore_name:mayank
Entervaluefore_id:E109
Entervalue
for
e_deptt:
CSE
Entervalueforsalary:20000
old 1:insertintoemployeevalues('&e_name','&e_id','&e_deptt','&s alary')
new 1:insertintoemployeevalues('mayank','E109','CSE','20000')
1rowcreated.
SQL>/
Entervaluefore_name:ramesh
Entervaluefore_id:E110
Entervaluefore_deptt:CSE
-
8/8/2019 Experiment No 1 .1 (1)
28/44
Entervalueforsalary:33000
old 1:insertintoemployeevalues('& e_name','&e_id','&e_deptt','&salary')
new 1:insertintoemployeevalues('ramesh','E110','CSE','33000')
1rowcreated.
SQL>select*fromemployee;
E_NAME E_ID E_D E_SALARY
---------- ---- --- ----------
amar E101 ECE 20000
ashish E102 ECE 30000
sonali E103 ECE 25000
dinesh E104 ECE 28000
neha E105 ECE 45000
kanika
E106
CSE
30000
mohit E107 CS E 19000
smith E108 CSE 45000
mayank E109 CSE 20000
ramesh E110 CSE 33000
10
rows
selected.
Arithmaticoperators:
To find annual income of all the employees with employee name
from employee table
SQL>selecte_name,e_salary*12 "annual" fromemployee;
-
8/8/2019 Experiment No 1 .1 (1)
29/44
E_NAME annual
---------- ----------
amar 240000
ashish 360000
sonali 300000
dinesh 336000
neha 540000
kanika 360000
mohit 228000
smith 540000
mayank 240000
ramesh 396000
10rows
selected.
OR
SQL>selecte_name,e_salary*12asannualfromemployee;
E_NAME annual
---------- ----------
amar 240000
ashish 360000
sonali 300000
dinesh 336000
neha 540000
kanika 360000
-
8/8/2019 Experiment No 1 .1 (1)
30/44
mohit 228000
smith 540000
mayank 240000
ramesh 396000
10rowsselected.
Logicaloperators
To display the details of employee having salary ranging between
20000 to 35000
SQL>select*fromemployeewheree_salary>=20000ande_salaryselect*fromemployeewheree_salary>25000ande_deptt=' ECE';
-
8/8/2019 Experiment No 1 .1 (1)
31/44
E_NAME E_ID E_D E_SALARY
---------- ---- --- ----------
ashish E102 ECE 30000
dinesh E104 ECE 28000
neha E105 ECE 45000
To display details of employees having salary >=30000
department=CSE
SQL>select*fromemployeewheree_salary>=30000ore_deptt='CSE';
E_NAME E_ID E_D E_SALARY
---------- ---- --- ----------
ashish E102 ECE 30000
neha E105 ECE 45000
kanika E106 CSE 30000
mohit E107 CSE 19000
smith E108 CSE 45000
mayank E109 CSE 20000
ramesh E110 CSE 33000
7rows
selected.
To display details of employees where salary is not ranging
between 20000 to 35000
SQL>select*fromemployeewhere NOT(e_salary>=2000ande_salary
-
8/8/2019 Experiment No 1 .1 (1)
32/44
E_NAME E_ID E_D E_SALARY
---------- ---- --- ---------
neha E105 ECE 45000
smith E108 CSE 45000
Patternmatching
To find details of all the employee where name begins with A
character
SQL>select*fromemployeewheree_namelike'a%';
E_NAME E_ID E_D E_SALARY
---------- ---- --- ----------
amar E101 ECE 20000
ashish E102 ECE 30000
To find details of all the employees where second character of the
name is o
SQL>select*fromemployeewheree_namelike'_o%';
E_NAME E_ID E_D E_SALARY
---------- ---- --- ----------
sonali E103 ECE 25000
mohit E107 CSE 19000
mayank E109 CSE 20000
To find the details of all the employees where third character of the
name is h:
-
8/8/2019 Experiment No 1 .1 (1)
33/44
SQL>select*fromemployeewheree_namelike'__h%';
E_NAME E_ID E_D E_SALARY
---------- ---- --- ----------
neha E105 ECE 45000
mohit E107 CSE 19000
mayank E109 CSE 20000
To find details of all the employees where name ends with t
character:
SQL>select*fromemployeewheree_namelike'%t';
E_NAME E_ID E_D E_SALARY
---------- ---- --- ----------
mohit E107 CSE 19000
To find detailos of all the employees where second character of the
name is o or n:
SQL>select*fromemployeewheree_namelike'_o%'ore_namelike'_n%';
E_NAME E_ID E_D E_SALARY
---------- ---- --- ----------
ashish E102 ECE 30000
sonali E103 ECE 25000
mohit E107 CSE 19000
mayank E109 CSE 20000
To find details of all the employees where second character of the
name is o and name is of total four characters:
-
8/8/2019 Experiment No 1 .1 (1)
34/44
SQL>select*fromemployeewheree_namelike'_o__';
E_NAME E_ID E_D E_SALARY
---------- ---- --- ----------
mayank E109 CSE 20000
inandnotin
To find na,e and salary of employees with enployee
id=E101,E103,E105,E106(using in):
SQL>selecte_name,e_salaryfromemployeewheree_idin('E101','E103','E105','E
106');
E_NAME E_SALARY
---------- ----------
amar 20000
sonali 25000
neha 45000
kanika 30000
To find id and salary of employees named ashish,smith,ramsh(using
in):
SQL>selecte_id,e_salaryfromemployeewheree_namein(' ashish','smith','ramesh');
E_ID E_SALARY
---- ----------
E102 30000
E108 45000
E110 33000
-
8/8/2019 Experiment No 1 .1 (1)
35/44
To find id and salary of employees other than ashish,
smith,ramesh(using not in):
SQL>selecte_id,e_salaryfromemployeewheree_namenotin(' ashish','smith','r
amesh');
E_ID E_SALARY
---- ----------
E101 20000
E103 25000
E104 28000
E105 45000
E106 30000
E107 19000
E109 20000
7 rowsselected.
Aggregatefunctions:
AVG (averege)
To find average of salary of all employees in employee table:
SQL>selectavg(e_salary)fromemployee;
AVG(E_SALARY)
-------------
29500
SUM (Sumation)
-
8/8/2019 Experiment No 1 .1 (1)
36/44
To find sum of salary of all employees in employee table:
SQL>selectsum(e_salary)fromemployee;
SUM(E_SALARY)
-------------
295000
To display maximum salary from employee table:
SQL>selectmax(e_salary)fromemployee;
MAX(E_SALARY)
-------------
45000
MIN (Minimum)
To display minimum salary from employee table:
SQL>selectmin(e_salary)fromemployee;
MIN(E_SALARY)
-------------
19000
COUNT
To find the number of rows in table employee
SQL>selectcount(*)fromemployee;
COUNT(*)
----------
-
8/8/2019 Experiment No 1 .1 (1)
37/44
10
To find the number of employees in table employee having salary
greater than 25000
SQL>selectcount(*)fromemployeewheree_salary>25000;
COUNT(*)
----------
6
Oraclefunctionsusingdualtable:
ABS(n):returns absolute of n
SQL>SELECTABS(-15)"Absolute"FROM DUAL;
Absolute
----------
15
POWER(m,n):returnsmraisedtothenthpower
SQL>SELECTPOWER(3,2)"Raised"FROM DUAL;
Raised
----------
9
ROUND(n,m):Returnsn,roundedtomplaces
SQL>SELECT ROUND(15.19,1)"Round"FROM DUAL;
-
8/8/2019 Experiment No 1 .1 (1)
38/44
Round
----------
15.2
SQL>select ROUND(10/3,1)"Round"FROM DUAL;
Round
----------
3.3
SORT(n):Returnssquarerootofn
SQL>SELECTSQRT(25)"Square Root"FROM DUAL;
Square Root
-----------
5
SQL>SELECTSQRT(3)"Squareroot"FROM DUAL;
Squareroot
-----------
1.73205081
EXP(n): Returnseraisedtonthpower(wheree=2.71828183)
SQL>SELECTEXP(5)"Exponent" FROM DUAL;
Exponent
----------
148.413159
-
8/8/2019 Experiment No 1 .1 (1)
39/44
GREATEST(expr1,exp2,expr3,.....expr n):Tofindthegreatestof
givenexpressionsornumbers.
SQL>SELECTGREATEST(4,5,17)"Num" FROM DUAL;
Num
----------
17
SQL>SELECTGREATEST(10/4,10+5,20*17)"Num" FROM DUAL;
Num
----------
340
LEAST(exp1,exp2,exp3.....expn):Tofindthesmallestofgiven
expressionsornumbers
SQL>SELECTLEAST(10/4,10+5,20*1 7)"Num"FROM DUAL;
Num
----------
2.5
SQL>SELECTLEAST(4,5,17)"Num" FROM DUAL;
Num
----------
4
MOD(m,n):Returnsremainderofm/n
SQL>SELECTMOD(15,7)"Mod1",MOD(15.7,7)"Mod2" FROM DUAL;
-
8/8/2019 Experiment No 1 .1 (1)
40/44
Mod1 Mod2
---------- ----------
1 1.7FLOOR(n): Returnsthelargestintegervaluethatisequaltpless
thananumber
SQL>SELECT FLOOR(24.8)"Flrl",FLOOR(13.15)"FLr2"FROM DUAL;
Flrl FLr2
--------------------
24 13
CEIL(n): Returnsthesmallestintegervaluethatisgreaterthan
orequaltoanumber
SQL>SELECTCEIL(24.8)"Ceil",CEIL(13.15)"Ceil" FROM DUAL;
Ceil Ceil
--------------------
25 14
LOWER(char):Returnschar,withallthelettersinlowercase
SQL>SELECTLOWER('DBMSlAB')"Lower"FROM DUAL;
Lower
--------
dbmslab
INECECAP(char): Returnschar,withthefirstletterofeachword
inuppercase
-
8/8/2019 Experiment No 1 .1 (1)
41/44
SQL>SELECT INECECAP('DBMSLAB')"initcap"FROM DUAL;
initcap
--------
DbmsLab
UPPER(char): Returnschar,withallthelettersinuppercase
SQL>SELECTupper('dbmslab')"upper"FROM DUAL;
upper
--------
DBMSLAB
SUBSTR(string,start_posiyion,length);Returnsaportionof
characters,beginningatcharacterm,andgoinguptocharactern
SQL>SELECTSUBSTR('thisisatest',6,2)"Extracted"FROM DUAL;
Ex
--
is
SQL>SELECTSUBSTR('thisisatest',6,5)"Extracted" FROM DUAL;
Extra
-----
isa
SQL>SELECTSUBSTR('thisisatest',6,7)"Extracted"FROM DUAL;
-
8/8/2019 Experiment No 1 .1 (1)
42/44
Extract
-------
isate
ASCII(single-character):ReturnsASCII valueofasinglecharacter
SQL>SELECTASCII('A')"ASCII"fromdual;
ASCII
----------
65
SQL>SELECTASCII('a')"ASCII"fromdual;
ASCII
----------
97
SQL>SELECTASCII('a')"ASCII_a",ASCII('z')"ASCII_z"fromdual;
ASCII_aASCII_z
--------------------
97 122
LENGTH(word): Returnsthelengthofaword
SQL>SELECTLENGTH('HELLO WORLD')"length"FROM DUAL;
length
----------
11
LTRIM(char,set): Removesfromleftofcharwithinitial
charactersremoveduptothefirstcharacternotinset
SQL>SELECTLTRIM('NISHA','N')"Length"FROM DUAL;
-
8/8/2019 Experiment No 1 .1 (1)
43/44
Leng
----
ISHA
SQL>SELECTLTRIM('NISHA','NI')"Length"FROM DUAL;
Len
---
SHA
SQL>SELECTLTRIM('NISHA','NIS')"Length"FROM DUAL;
Le
--
HA
SQL>SELECTLTRIM('NISHA','NISH')"Length"FROM DUAL;
L
-
A
SQL>SELECT
LT
RIM('
NISHA','
NISHA')
"Length
"FROM
DUAL;
L
-
RTRIM(char,set):Returnschar,withfinalcharactersremoved
afterthelastcharacternotinset
SQL>SELECT
RT
RIM('
NISHA','
NISHA')
"Length
"FROM
DUAL;
L
-
-
8/8/2019 Experiment No 1 .1 (1)
44/44
SQL>SELECT RTRIM('NISHA','ISHA')"Length"FROM DUAL;
L
-
N
SQL>SELECT RTRIM('NISHA','SHA')"Length"FROM DUAL;
Le
--
NI
SQL>SELECT RTRIM('NISHA','HA')"Length"FROM DUAL;
Len
---
NIS
SQL>SELECT RTRIM('NISHA','A')"Length"FROM DUAL;
Leng
----
NISH