Untitled

mail@pastecode.io avatar
unknown
plain_text
2 months ago
42 kB
2
Indexable
Never
DATABASE MANAGEMENT SYSTEMS LAB 
	DATABASE MANAGEMENT SYSTEMS
	EXPERIMENT:1
Aim:QueriesforCreating,Dropping,andAlteringTablesandinsertrowintoatable(useconstraintswhile creatingtables)examplesusingSelectCommand.

Procedure:

1.	Creationofemp&depttableinSql:
SQL>createtable dept(
deptnonumber(2,0) primary key,dnamevarchar2(14)NOTNULL,loc varchar2(13)NOTNULL,
);
Tablecreated.

SQL>createtableemp(
empno	number(4,0),
ename			varchar2(10) NOT NULL,job	varchar2(9)NOTNULL,mgr		number(4,0),
hiredatedate,
salnumber(7,2) NOT NULL,commnumber(7,2),
eptnonumber(2,0),
constraintpk_empprimarykey(empno),
constraintfk_deptnoforeignkey(deptno)referencesdept (deptno)
);
Tablecreated.
2.	ViewStructure/schemaofemp&dept tableinsql:SQL>select*from emp;
norowsselected
SQL>select*fromdept;
norowsselected	1
	
	NIT	DEPARTMENTOFCSE	
 
	DATABASE MANAGEMENT SYSTEMS
	SQL>descemp;
Name	Null?	Type

 
EMPNO	NOTNULL	NUMBER(4)ENAME		VARCHAR2(10)
JOB	VARCHAR2(9)
MGR	NUMBER(4)
HIREDATE	DATE
SAL	NUMBER(7,2)
COMM	NUMBER(7,2)
DEPTNO	NUMBER(2)
SQL>descdept;
Name	Null?	Type

 
DEPTNO	NOT NULL NUMBER(2)DNAME		VARCHAR2(14)
LOC	VARCHAR2(13)

2. Insertthevaluesin emp&depttableinsql:
Thereareseveralwaystoinsertthevalues intheexistingtable
Querytoinsertsinglerecordintheexistingtable:
SQL>insertintodept(deptno,dname,loc)values(20,’admin’,’hyd’);
1rowcreated.
Query to insert multiple records in the existing table:SQL>insertintodeptvalues(&deptno,'&dname','&loc');Entervaluefordeptno:10
Entervaluefor dname:salesEntervalueforloc:vijayawada
old1:insertintodeptvalues(&deptno,'&dname','&loc')new1:insertinto deptvalues(10,'sales','vijayawada')
1rowcreated.	2
		
	NIT	DEPARTMENTOFCSE	
 
DATABASE MANAGEMENT SYSTEMS

SQL>/
Entervaluefordeptno:20	3
Entervaluefordname:adminEntervalueforloc: hyd
old1:insertintodeptvalues(&deptno,'&dname','&loc')1rowcreated.
SQL>/
Enter valuefordeptno:30
Entervaluefordname:marketingEntervalueforloc: vzg
old1:insertintodeptvalues(&deptno,'&dname','&loc')new1:insertintodeptvalues(30,'marketing','vzg')
1rowcreated.

4. Select Command:thiscommandisusedtoprinttherecordfromtheexistingtable.Viewallrecords indepttable:
SQL>select*fromdept;
DEPTNO	DNAME	LOC

10sales	vijayawada
20admin	hyd
30marketing	vzg
Viewrecordsbasingongivencriteriaonspecificcolumn.


1.Viewsinglecolumnfromexistingtable.
SQL>selectdnamefromdept;
DNAME

SalesAdminMarketing




NIT	DEPARTMENTOFCSE
 
	DATABASE MANAGEMENT SYSTEMS
	2.Viewspecificrecord(s)fromexistingtablebasedongivencondition.
SQL>select*fromdeptwheredname='sales';
DEPTNO	DNAME	LOC	4
		
	10sales	vijayawada


Typesof SQLCommands:


DDL:DDLCommands(DataDefinitionLanguage)
1.CREATE	2.DESC	3.ALTER4.DROP	5.TRUNCATE	6.RENAME


DMLCommands(DataManipulationLanguage)
1.SELECT	2.INSERT	3.UPDATE	4.DELETE


TCL(TransactionControlLanguage)
1.COMMIT	2.ROLLBACK	3.SAVEPOINT


DCLCommands(DataControl Language)
1.GRANT	2. REVOKE


1.CREATE:
CREATETABLE:Thisisusedtocreateanewrelationandthecorresponding


Syntax:CREATETABLErelation_name(field_1data_type(Size),field_2data_type(Size),...);
Example:
SQL>CREATETABLEStudent(idnumber,namevarchar2(10));RESULT:Tablecreated.	
	NIT	DEPARTMENTOFCSE	
 
	DATABASE MANAGEMENT SYSTEMS
	2.	DESC:Itisusedtodescribeaschemaaswellastoretrieverowsfromtableindescendingorder.
SYNTAX:DESC
EX:SQL>DESCEMP1;
NAME	NULL?	TYPE

 	 	 
EMPNO	NOTNULLNUMBER(10)ENAME		VARCHAR2(15)
JOB	CHAR(10)
DEPTNAME	VARCHAR2(10)
DEPTNO	NUMBER(9)
HIREDATE	DATE
SALARY	NUMBER(8)
EXP	NUMBER(5)


3.	ALTER:Thisisusedforadd,removeormodify thestructureoftheexisting table
(a)	ALTERTABLE...ADD...:Thisisusedtoaddsomeextrafieldsintoexistingrelation.
Syntax:ALTERTABLErelation_nameADD(newfield_1data_type(size),newfield_2data_type(size),..);

Example:SQL>ALTERTABLEemp1ADD(AddressCHAR(10));TABLEALTERED.

(b)	ALTERTABLE...MODIFY...:Thisisusedto changethewidthaswellasdatatypeoffieldsofexistingrelations.
Syntax:ALTERTABLErelation_nameMODIFY(field_1newdata_type(Size),field_2newdata_type(Size),...,field_newdata_type(Size));

Example:
SQL>ALTERTABLEemp1MODIFY(enameVARCHAR2(20),salaryNUMBER(5));
TABLEALTERED.	5
		
	NIT	DEPARTMENTOFCSE	
 
	DATABASE MANAGEMENT SYSTEMS
	SQL>DESCEMP1;

NAME	NULL?	TYPE

 	 
EMPNO	NOTNULL	NUMBER(10)
ENAME	VARCHAR2(20)
JOB	CHAR(10)
DEPTNAME	VARCHAR2(10)
DEPTNO	NUMBER(9)
HIREDATE	DATE
SALARY	NUMBER(5)
EXP	NUMBER(5)
ADDRESS	CHAR(10)

4.	DROPTABLE:Thisisusedtodeletethestructureofarelation.Itpermanentlydeletes thetable.
Syntax:DROPTABLEtablename;
Example:
SQL>DROPTABLEEMP1;
Tabledropped;
DROP:thiscommandisused toremovethedatefromtheexistingtableDROPCOLUMNINTABLE
Syntax:
ToDROPACOLUMNin an existingtable,theOracleALTERTABLEsyntaxis:ALTERTABLEtable_nameDROPCOLUMNcolumn_name;
ExamplecustomersDROPCOLUMNcustomer_name;
SQL>ALTERTABLEcustomersDROPCOLUMNcustomer_name;


5.	RENAME:Itisusedtomodifythe nameoftheexisting database object.
Syntax:RENAMEold_table_nameTOnew_table_name;
Example:
SQL>RENAMEEMP1TOEMP2;
Tablerenamed.	6
		
	NIT	DEPARTMENTOFCSE	
 
	DATABASE MANAGEMENT SYSTEMS
	6.TRUNCATE:Thiscommandwillremovethedatapermanently.Butstructurewillnotberemoved.
Syntax:	TRUNCATETABLE<Tablename>
Example:
TRUNCATETABLEEMP1;	7
		
	NIT	DEPARTMENTOFCSE	
 
	DATABASE MANAGEMENT SYSTEMS
	EXPERIMENT:2
QUERIES(ALONGWITH SUBQUERIES) USINGANY,ALL,IN,EXISTS,NOTEXISTS,UNION,INTERSECT
SOLUTION:
ToCreateemployeetable:
Sql> create table employee(Fname varchar2(20),Lnamevarchar2(20),
Ssnnumber(4)primarykey,B_datedate,
Address varchar2(30),Genderchar(1),Salarynumber(7,2),
Super_ssnreferencesemployee(ssn),Dnonumber(4)
);
Tablecreated.


SQL>INSERTINTOEMPLOYEE
VALUES('SMITH',NULL,1111,'03-NOV-2016','BJD','M',2000,NULL,10)
1rowcreated.
SQL>INSERTINTOEMPLOYEE
VALUES('ALLEN',NULL,2222,'03-NOV-2016','SBC','M',3000,1111,20)
1rowcreated.
SQL>INSERTINTOEMPLOYEE
VALUES('MARTIN',NULL,3333,'03-NOV-2016','HYD','M',4000,1111,30)
1rowcreated.


Likethiswecaninsertthevalues intothetable.Toviewdatainthetablefollowing queryisused.	8
	
	NIT	DEPARTMENTOFCSE	
 
	DATABASE MANAGEMENT SYSTEMS
	
SQL>SELECT*FROMEMPLOYEE;

FNAME	LNAME	SSNBDATE	ADDRESS	G	SALARYSUPER_SSN	DNO	9
		
	SMITH	111101-JAN-06	BZA	M	2000	10
ALLEN	2222  12-DEC-04  SBC	M	3000	1111	20
MARTIN	3333  15-DEC-07	HYD	M	4000	1111	20
JONES	444428-SEP-05	TNU	M	1500	2222	10
BLAKE	555504-SEP-04	VZA	M	2500	2222	10
TURNER	666621-OCT-99	GNT	M	6000	3333	20
6rowsselected.
Insertingvalues inthedependenttableas follows
SQL>INSERTINTODEPENDENTVALUES(1111,'SMITH',’G’)
1 rowiscreated.
SQL>INSERTINTODEPENDENTVALUES(2222,'POOJA',’F’)
1 rowiscreated.
SQL>INSERTINTODEPENDENTVALUES(3333,'MARTIN',’M’)
1 rowiscreated.
SQL>INSERTINTODEPENDENTVALUES (3333,'RAJA',’M’)
1 rowiscreated.

ToCreatedependenttable:
SQL>CREATETABLEDEPENDENT(
ESSN NUMBER (4) REFERENCES EMPLOYEE (SSN),DEPENDENT_NAMEVARCHAR2(20),
GENDERCHAR(1),B_DATEDATE,
RELATIONSHIPVARCHAR2(20),
PRIMARYKEY(ESSN,DEPENDENT_NAME)
);
Tablecreated.	
	NIT	DEPARTMENTOFCSE	
 
	DATABASE MANAGEMENT SYSTEMS
	Toviewdatainthedependenttableasfollows.
SQL>SELECT*FROMDEPENDENT;
ESSNDEPENDENT_NAMEG	B_DATE	RELATIONSHIP

 	 	 

1111	SMITH	M
2222	POOJA	F
3333	MARTIN	M
3333	RAJA	M


1.ALL:
Retrievethenamesofemployeeswhosesalaryisgreaterthanthe salaryofalltheemployeesindepartment10
SQL> SELECT FNAME, LNAME FROM EMPLOYEE WHERE SALARY> ALL ( SELECTSALARYFROM EMPLOYEEWHEREDNO=10);
FNAME	LNAME	10
		
	
ALLENMARTINTURNER

2.ANY
Retrieve thenames ofemployees whosesalaryis greaterthanthe salaryofanyone oftheemployeesindepartment10
SQL>SELECTFNAME,LNAMEFROMEMPLOYEE
WHERESALARY>ANY(SELECTSALARYFROMEMPLOYEEWHERE DNO=10);

FNAME	LNAME	
	
TURNERMARTINALLENBLAKE
SMITH		
	NIT	DEPARTMENTOFCSE	
 
	DATABASE MANAGEMENT SYSTEMS
	3.IN
Retrievethenameofeachemployeewhohasadependentwiththefirstnameandsamegenderastheemployee

SQL>SELECTe.FNAME,e.LNAMEFROMEMPLOYEEeWHEREe.SSN IN(SELECTESSN FROM DEPENDENT WHERE e.GENDER=GENDER AND e.FNAME =DEPENDENT_NAME);

FNAME	LNAME	11
		
	
SMITHMARTIN

4.	EXISTS
Retrievethenameofeachemployeewhohasadependentwiththefirstnameandsamegenderastheemployee

SQL>SELECTe.FNAME,e.LNAMEFROMEMPLOYEEeWHEREEXISTS(SELECT
*FROM DEPENDENT WHERE e.SSN=ESSN AND e.GENDER=GENDER ANDe.FNAME =DEPENDENT_NAME);

FNAME	LNAME

 
SMITHMARTIN

5.	NOTEXISTS
Retrievethenamesofemployeeswhohavenodependents
SQL>SELECTFNAME, LNAMEFROMEMPLOYEEWHERENOTEXISTS(SELECT*FROMDEPENDENTWHERE SSN=ESSN);
FNAME	LNAMEALLEN	
	NIT	DEPARTMENTOFCSE	

 
	DATABASE MANAGEMENT SYSTEMS
	SQLConstraints
SQLconstraintsareused tospecifyrulesforthe datainatable.
Constraints are used to limit the type of data that can be insert into a table. This ensures the accuracyand reliability of the datain the table.If thereis any violation between the constraintand the dataaction,theactionis aborted.
Constraints can be column level or table level. Column level constraints apply to a column, and tablelevelconstraints applytothewholetable.
ThefollowingconstraintsarecommonlyusedinSQL:
•	NOTNULL-EnsuresthatacolumncannothaveaNULLvalue
Example:
SQL>createtableperson1(idint,name varchar2(10)notnull,age int);Tablecreated.
•	UNIQUE-Ensuresthatallvalues inacolumnaredifferent
Example:
SQL>createtableperson(idintunique,namevarchar2(10),ageint);
Tablecreated.
•	PRIMARYKEY-Acombination ofaNOTNULLandUNIQUE. Uniquelyidentifieseachrowinatable
Example:
SQL>createtableemp1(idnumber(10)primarykey,namevarchar2(10),salint);Tablecreated.
•	FOREIGNKEY-Uniquelyidentifiesarow/recordinanothertable
o	AFOREIGNKEYisakeyusedtolinktwotablestogether.
o	AFOREIGNKEYisafield(orcollectionoffields)inonetablethatreferstothePRIMARYKEYinanothertable.
o	Thetablecontainingtheforeignkeyiscalledthechildtable,andthetablecontaining thecandidatekeyiscalledthereferencedorparenttable.
Example:
SQL>createtableemp2(eidint,cityvarchar2(10),foreignkey(eid)referencesemp1(id));
Tablecreated.	12
		
	NIT	DEPARTMENTOFCSE	
 
	DATABASE MANAGEMENT SYSTEMS
	•	CHECK-Ensuresthatallvaluesinacolumnsatisfiesaspecificcondition
Example:
SQL>CREATETABLEperson1(IDint,Ageint,Cityvarchar(10),CONSTRAINTchkCHECK(Age>=18ANDCity='vja');
Tablecreated.
•	DEFAULT-Setsadefaultvalueforacolumnwhennovalueisspecified.
o	TheDEFAULTconstraintisusedtoprovideadefaultvalueforacolumn.
o	ThedefaultvaluewillbeaddedtoallnewrecordsIFnoother valueisspecified.
SQLDEFAULTonCREATETABLE
SQL>createtableemp(idnumber(10),namevarchar2(10),cityvarchar2(10)default'vja');Tablecreated.	13
		
	NIT	DEPARTMENTOFCSE	
 
	DATABASE MANAGEMENT SYSTEMS
	EXPERIMENT:3
QUERIESUSINGAGGREGATEFUNCTIONS(COUNT,SUM,AVG,MAXANDMIN)	14
	
	GROUPBY,HAVINGand Creationanddropping ofViews.	
	SOLUTION:	
	1.COUNT:Calculatethenumberofemployeesindept20.	
	SQL>SELECTCOUNT(*)NO_EMPFROMEMPWHERE DEPTNO=20;	
	NO_EMP	
	 
5	
	2.SUM:Calculatethe totalsalariesforeach dept	
	SQL>SELECTDEPTNO,SUM(SAL)FROMEMPGROUPBYDEPTNO	
	DEPTNOSUM(SAL)	
	30	9400	
	20	10875	
	10	8750	
	
3.AVG:Calculatetheaveragesalariesforeachdept	
	SQL>SELECTDEPT_NO,AVG(SAL)FROMEMPGROUPBYDEPT_NO;	
	DEPT_NOAVG(SAL)	
	----------	----------	
	30	1566.66667	
	20	2175	
	10	2916.66667	
	4.MAX:Calculatethemaximumsalaryforeachdept	
	SQL>SELECTDEPTNO,MAX(SAL)FROMEMPGROUPBYDEPTNO;	
	DEPTNOMAX(SAL)	
	 	 
30	2850	
	20	3000	
	10	5000	
	NIT	DEPARTMENTOFCSE	
 
	DATABASE MANAGEMENT SYSTEMS
	5.	MIN
Calculatetheminimumsalaryforeachdept
SQL>SELECTDEPTNO, MIN(SAL)FROMEMPGROUPBYDEPTNO
DEPTNOMIN(SAL)

 	 
30	950
20	800
10	1300

6.	GROUPBY:
TheGROUPBYclauseisaSQLcommandthatisusedtogrouprowsthathavethesamevalues.The GROUPBY clauseis usedin the SELECT statement.Optionallyitis usedin conjunction withaggregatefunctions toproducesummaryreportsfromthedatabase.
GROUPBY Syntax
SELECTstatements…GROUPBYcolumn_name1[column_name2,…];
GroupingusingaSingleColumn:
Createatablecalleddatawithgendercolumnand valuesasmaleandfemale.
SQL>select* fromdata;
GENDER

 
malefemalefemalefemalefemalefemalemalemalemalefemalemalemalefemalemalemalefemale

16rowsselected.	15
		
	NIT	DEPARTMENTOFCSE	
 
DATABASE MANAGEMENT SYSTEMS

SQL>selectgenderfromdataGROUPBYgender;
16
GENDER

malefemale

SQL>selectcount (gender),genderfrom dataGROUPBYgender;
COUNT(GENDER)GENDER

8	male
8	female

GroupingusingMultipleColumnsSyntax
SELECTColumn1, Column2,AGGREGATE_FUNCTION(Column3)FROMTABLE1GROUPBY
Column1,Column2
Examples:
SQL>select *fromemp;
IDNAME	DEPT	SAL
 
--------------------
 
----------
 
----------
 

1	a	cse	1000
2	b	ece	2000
3	c	eee	3000
4	d	cse	4000
1	z	cse	5000
5	a	ece	6000
6	e	ece	7000
2	b	eee	9000
8rowsselected.
SQL> select id, name from emp GROUP BY id, name;IDNAME
3	c
4	d
1	a
2	b
5	a
1	z
6	e
7rowsselected.
NIT	DEPARTMENTOFCSE
 
	DATABASE MANAGEMENT SYSTEMS

7.HAVING
•	TheHAVINGclausewasaddedtoSQLbecausetheWHEREkeywordcouldnotbeusedwith	17
	aggregatefunctions.
•	TheWHERE clauseplacesconditionsontheselectedcolumns,whereastheHAVINGclauseplacesconditions ongroups createdbytheGROUPBYclause.
•	TheHAVINGclausemustfollowtheGROUPBYclauseinaqueryandmustalsoprecedetheORDERBYclauseifused
HAVINGSyntax
SELECTcolumn_name(s)FROMtable_nameWHEREconditionGROUPBYcolumn_name(s)
HAVINGcondition


SQL>select *fromemp;


ID	NAMEDEPT	SAL
	1	a	cse	1000
2	b	ece	2000
3	c	eee	3000
4	d	cse	4000
5	e	ece	5000

SQL>selectcount(id),deptfromempGROUPBYdepthavingcount(id)>1;COUNT(ID)DEPT
-------------------------
2	cse
2	ece

SQL>select *fromemp;
IDNAME	DEPT	SAL
--------------------	----------	----------
1	a	cse	1000
2	b	ece	2000
3	c	eee	3000
4	d	cse	4000
5	e	ece	5000	
	NIT	DEPARTMENTOFCSE	

 
	DATABASE MANAGEMENT SYSTEMS
	SQL>selectmax(sal),dept fromempGROUPBYdept;
MAX(SAL)DEPT
----------	----------
4000	cse
3000	eee
5000	ece
SQL>selectmax(sal),deptfromempGROUPBYdepthavingmax(sal)>3000;MAX(SAL)DEPT
----------	----------
4000	cse
5000	ece

8.	View:
o	ViewsinSQLareconsideredasavirtualtable.Aviewalsocontainsrowsandcolumns.
o	Tocreatetheview,wecanselectthefieldsfromoneormoretablespresentinthedatabase.
o	Aviewcaneitherhavespecificrowsbasedoncertainconditionoralltherowsof atable.


SQL>select*fromemp;


ENOENAME	SALARYLOC

 	 

101	ali	15000vja
102	haji	`	20000hyd
103	mohammad	42000vja
104	ravi	23000gnt
105	irfath	50000hyd
SQL>createVIEWhydasselect*fromempwhereloc='hyd';
Viewcreated.


SQL>select*fromhyd;
ENOENAME	SALARYLOC	18
		
	102	haji
105	irfath	20000	hyd
50000	hyd	
	NIT	DEPARTMENTOFCSE	
 
	DATABASE MANAGEMENT SYSTEMS
	SQL>dropVIEWhyd;
Viewdropped.


SQL>select*fromhyd;
select*fromhyd
*ERRORatline1:
ORA-00942:tableor viewdoesnot exist	19
		
	NIT	DEPARTMENTOFCSE	
 
	DATABASE MANAGEMENT SYSTEMS
	EXPERIMENT:4
20
QUERIESUSINGCONVERSIONFUNCTIONS(TO_CHAR,TO_NUMBERANDTO_DATE),STRING FUNCTIONS (CONCATENATION, LPAD, RPAD, LTRIM, RTRIM, LOWER, UPPER,INITCAP,LENGTH,SUBSTRANDINSTR),DATEFUNCTIONS(SYSDATE,NEXT_DAY,ADD_MONTHS,LAST_DAY,MONTHS_BETWEEN,LEAST,
GREATEST,TRUNC,ROUND,TO_CHAR)

SQL>select*fromemp;
ENOENAME	SALARYLOC
	101	ali	15000	vja
102	haji	20000	hyd
103	mohammad	42000	vja
104	ravi	23000	gnt
105	irfath	50000	hyd

a)ConversionFunctions:
1.to_char:to_charisusedtoconverttheattributevaluestochar.
SQL>selectto_char(salary,'$99999.99')fromemp;

TO_CHAR(SALARY)

 
$15000.00
$20000.00
$42000.00
$23000.00
$50000.00
SQL>SELECTTO_CHAR(123.4567, '99999.9')FROMDUAL;TO_CHAR(
	123.5
SQL>SELECTTO_CHAR(123.4567,'99999.99')FROMDUAL;TO_CHAR(1
	123.46

SQL>SELECTTO_CHAR(1234.56789,'9,999.00')FROMDUAL;	
	NIT	DEPARTMENTOFCSE	

 
DATABASE MANAGEMENT SYSTEMS
TO_CHAR(1	21

 
1,234.57	
SQL>SELECTTO_CHAR(SYSDATE,'YYYY/MM/DD') FROMDUAL;	
TO_CHAR(SY	

 
2021/07/09	
SQL>SELECTTO_CHAR(SYSDATE,'DD/MM/YYYY')FROMDUAL;	
TO_CHAR(SY	

 
09/07/2021	
SQL>SELECTTO_CHAR(23,'000099') FROMDUAL;	
TO_CHAR	

 
000023	
SQL>SELECTTO_CHAR(23,'0000999')FROMDUAL;	
TO_CHAR(	

 
0000023	
SQL>SELECTTO_CHAR(23,'00009')FROMDUAL;	
TO_CHA	

 
00023	
SQL>SELECTTO_CHAR(23,'00000') FROMDUAL;	
TO_CHA	

 
00023	
SQL>SELECTTO_CHAR(234.5678,'00.00') FROMDUAL;	
TO_CHA	

 
######	
NIT	DEPARTMENTOFCSE	

 
	DATABASE MANAGEMENT SYSTEMS
	SQL>SELECTTO_CHAR(234.5678,'000.000')FROMDUAL;

TO_CHAR(	22
		
	234.568

SQL>SELECTTO_CHAR(2345.234566,'1,23.000')FROMDUAL;SELECTTO_CHAR(2345.234566,'1,23.000')FROMDUAL
*ERRORatline1:
ORA-01481:invalidnumberformatmodel
SQL>SELECTTO_CHAR(2345.2345,'9,000.00')FROMDUAL;TO_CHAR(2	
	2,345.23
SQL>SELECTTO_CHAR(2345.2345,'$9,000.00')FROMDUAL;TO_CHAR(23	
	$2,345.23


2.	to_number:to_numberisusedtoconverttheattributevaluetonumber.
SQL>SELECTTO_NUMBER('1210.73', '9999.99')FROMDUAL;
TO_NUMBER('1210.73','9999.99')1210.73
3.	to_date:to_dateisusedforconvertanddisplaytheattributevaluesasdate.SQL>selectto_date('01-01-2020','MM-DD-YYYY')fromdual;
TO_DATE('	
	01-JAN-20	
	NIT	DEPARTMENTOFCSE	

 
DATABASE MANAGEMENT SYSTEMS


b)Stringfunctions:
1.	Concatenation:CONCATisusedtoaddtwo attributevaluessuchasstring.
SQL>select concat(eno,loc)fromemp;
CONCAT(ENO,LOC)

 
101vja102hyd103vja104gnt105hyd

2.	lpad:LPAD()functionisusedtopaddingtheleftsideofastringwithaspecificsetofcharacters.
SQL>selectlpad(ename,10,'*')fromemp;

LPAD(ENAME,10,'*')

 
*******ali
******haji
**mohammad
******ravi
****irfath

3.	rpad:RPAD()functionisusedtopaddingtherightsideofastringwithaspecificsetofcharacters.
SQL>selectrpad(ename,10,'*')fromemp;

RPAD(ENAME,10,'*')

 
ali*******haji******mohammad**ravi******irfath****




4.	ltrim:LTRIM()functionisusedtoremoveallspecifiedcharactersfromtheleftendsideofastring
NIT	DEPARTMENTOFCSE	23
	
 
DATABASE MANAGEMENT SYSTEMS
SQL>selectltrim('******hi********','*')fromdual;
LTRIM('***hi********
5.	rtrim:RTRIM()functionisusedtoremoveallspecifiedcharactersfromtheleftendsideofastring
SQL>select rtrim('******hi********','*')fromdual;
RTRIM('*

 
******hi
6.	lower:lower()functionisusedtoconverttheattributevalueintolowercase.
SQL>selectlower(ename)fromemp;

LOWER(ENAM

 
alihaji
mohammadravi
irfath

7.	upper:upper()functionisusedtoconverttheattributevaluesintouppercase.
SQL>selectupper(ename)fromemp;
UPPER(ENAMALI
HAJI
MOHAMMADRAVI
IRFATH






8.	initcap:initcap() isusedtoconverttheattributevaluesfirstcharacterincapitalletter.
SQL>select initcap(ename) fromemp;	24
	
	NIT	DEPARTMENTOFCSE	

 
DATABASE MANAGEMENT SYSTEMS

INITCAP(EN

 
AliHaji
MohammadRavi
Irfath


9.	length:length() functionisused tocalculatethelengthofthegivenattribute.
SQL>selectename,length(ename)fromemp;
ENAME	LENGTH(ENAME)
ali	3
haji	4
mohammad	8
ravi	4
irfath	6

10.	substr:substr()functionisusedtofindthesubstringofthegivenattributevalue.Itretunssize-1ofthe givenstring/attributeasasubstring.
SQL>selectename,substr(ename,4)fromemp;

ENAME	SUBSTR(ENAME,4)

 
ali
haji	i
mohammad	ammadravi		i
irfath	ath







11.	instr:instr()functionreturnthe locationofstartingpassionofthesubstringintheexistingvalue.

	SQL>selectinstr('welcometoCRRCOE','to') fromdual;		25
	
	NIT	DEPARTMENTOFCSE	

 
DATABASE MANAGEMENT SYSTEMS

INSTR('WELCOMETO CRRCOE','TO')9
c)Datefunctions:

1.	Sysdate():sysdate() functionreturnsthecurrent systemdate.
SQL>selectsysdatefromdual;

SYSDATE28-APR-21
2.	next_day();itreurnsthedateofnextcoming day.
SQL>selectnext_day(sysdate,'sunday')fromdual;

NEXT_DAY(02-MAY-21
3.	add_months():itreturnsthenextdateafteraddingnumberofmonthsintheorguments.
SQL>selectadd_months(sysdate,5)fromdual;

ADD_MONTH28-SEP-21
4.	last_day():TheLAST_DAY()functiontakesadatevalueasargumentandreturnsthelastdayofmonthinthatdate
SQL>selectlast_day(sysdate) fromdual;
LAST_DAY(30-APR-21
SQL>select last_day('02-FEB-2020')fromdual;
LAST_DAY(	26
	
	NIT	DEPARTMENTOFCSE	

 
DATABASE MANAGEMENT SYSTEMS
29-FEB-20

5.	months_between():itreturnsthenumbersofmonthsbetweengiventwodates.
SQL>selectmonths_between('02-feb-2021','02-feb-2020')fromdual;


MONTHS_BETWEEN('02-FEB-2021','02-FEB-2020')12
SQL>selectmonths_between(sysdate,'02-feb-2020')fromdual;


MONTHS_BETWEEN(SYSDATE,'02-FEB-2020')

 
14.8600769

6.	least():itretuns leastvaluefromthegivenargumentorattributes.
SQL>selectleast(300,450,100,440)fromdual;

LEAST(300,450,100,440)100
7.	greatest():itreturnsmaximumvaluesfromthegivenargumentsorattributesintherelation.
SQL>selectgreatest(300,450,100,440)fromdual;
GREATEST(300,450,100,440)450
8.	trunc():TheTRUNC()functionreturnsaDATEvaluetruncatedtoaspecifiedunit.
SQL>selecttrunc(sysdate,'mm')fromdual;

TRUNC(SYS01-APR-21
SQL>selecttrunc(sysdate,'yyyy')fromdual;
TRUNC(SYS	27
	
	NIT	DEPARTMENTOFCSE	

 
DATABASE MANAGEMENT SYSTEMS
01-JAN-21

9.	round():Roundfunctionroundanumbertoaspecifiedlengthorprecision.
SQL>selectround(12.49,0)fromdual;

ROUND(12.49,0)12
SQL>selectround(12.51,0)fromdual;

ROUND(12.51,0)13
10.	to_char():itconvertthegivendatetypeattributevaluestotextandreturnthedateinthespecificformat.
SQL>selectto_char(sysdate,'yyyy-mm-dd')fromdual;

TO_CHAR(SY2021-04-28














EXPERIMENT:5	28
	
	NIT	DEPARTMENTOFCSE	

 
DATABASE MANAGEMENT SYSTEMS

i.	CreateasimplePL/SQLprogramwhichincludesdeclarationsection,executablesectionandexception–Handlingsection(Ex.Studentmarkscanbeselectedfromthetableandprintedforthose	29
whosecuredfirstclassandanexceptioncanberaisedifnorecordswerefound).
ii.	Insert data into student table and use COMMIT, ROLLBACK and SAVEPOINT in PL/SQL block..i).Wehavetocreate the studenttable andinserttherecords intothetableasfollows:
SQL>createtablestudent(sidnumber(10),snamevarchar2(20),rankvarchar(10));
Tablecreated.


SQL>insertintostudentvalues(501,'Ravi','second');
1rowcreated.


SQL>insertintostudentvalues(502,'Raju','third');
1rowcreated.


SQL>insertintostudentvalues(503,'Ramu','');
1rowcreated.


SQL>select*fromstudent;

SIDSNAME	RANK

501Ravi	second
502Raju	third
503Ramu









PL/SQLCODE:
NIT	DEPARTMENTOFCSE
 
DATABASE MANAGEMENT SYSTEMS
SQL>ed 5a
Enterthefollowingcodeintothetexteditorandsavethefilewith.sqlformat	30

setserveroutputon;declare
temp1 number(10);temp2varchar2(10);

begin
select sid,sname into temp1,temp2 from student where rank='first';dbms_output.put_line('StudentNo:'||temp1||'Name:'||temp2||'gotfirstrank');
exception
when no_data_found thendbms_output.put_line('********************************************');dbms_output.put_line('# Error: there is no student got first rank');end;
/

SQL>@5a;
********************************************# Error:thereisnostudentgotfirstrank

PL/SQLproceduresuccessfullycompleted.

SQL>updatestudentsetrank='first'wheresid=503;1rowupdated.

SQL>select*fromstudent;

SIDSNAME	RANK

 
501Ravi	second
502Raju	third
503Ramu	first

SQL>@5a
StudentNo:503Name:RamugotfirstrankPL/SQLproceduresuccessfullycompleted.



ii)
SQL>select*fromstudent;
 
NIT	DEPARTMENTOFCSE

 
	DATABASE MANAGEMENT SYSTEMS
	SIDSNAME	RANK	31
	501Ravi	second
502Raju	third
503Ramu	first

PL/SQLCODE:

SQL>ed 5b

Enterthefollowing codeintothetexteditorandsavethefilewith.sqlformatsetserveroutputon;
DECLARE
snostudent.sid%type;namestudent.sname%type;srankstudent.rank%type;

BEGIN
sno:= &sno;name := '&name';srank:='&srank';
INSERT into student values(sno,name,srank);dbms_output.put_line('One record inserted');COMMIT;
-- adding savepointSAVEPOINTs1;
--secondtimeaskinguserforinputsno:=&sno;
name := '&name';srank:='&srank';
INSERT into student values(sno,name,srank);dbms_output.put_line('One record inserted');ROLLBACKTOSAVEPOINTs1;
END;
/




SQL>@5b;	
	NIT	DEPARTMENTOFCSE	
 
	DATABASE MANAGEMENT SYSTEMS
	SQL>@5b
Enter value for sno: 504old  7:	sno:=&sno;
new7:	sno:=504;
Entervalueforname:ali
old  8:	name:='&name';new8:		name := 'ali';Entervalueforsrank:firstold  9:	srank:='&srank';new9:		srank := 'first';	32
		
	Enter valuefor sno:505old16:	sno := &sno;new16:		sno:=505;
Entervalue forname:haji
old17:	name:='&name';new17:		name := 'haji';Entervalueforsrank:thirdold18:	srank := '&srank';new18:		srank := 'third';Onerecordinserted
Onerecordinserted	
	PL/SQLproceduresuccessfullycompleted.	
	SQL>select*fromstudent;	
	SIDSNAME	RANK	
	501Ravi	second
502Raju	third
503Ramu	first
504 ali	first		
		



EXPERIMENT:6	
	NIT	DEPARTMENTOFCSE	
 
	DATABASE MANAGEMENT SYSTEMS
	Developaprogram thatincludesthefeaturesNESTEDIF,CASEandCASEexpression.Theprogram
canbeextendedusing theNULLIFandCOALESCEfunctions.
A.NESTEDIF:
Anestedif-thenisanifstatementthatisthetargetofanotherifstatement.Nestedif-thenstatementsmeananifstatementinsideanotherifstatement
Syntax:-
if(condition1)then
--Executeswhencondition1istrueif(condition2)then
--Executeswhencondition2istrueendif;
endif;


PL/SQLCODE:PL/SQLProgramtofindbiggestofthreenumberusingnestedif.SQL>ed6a
Enterthefollowing codeintothetexteditorandsavethefilewith.sqlformat

declare
a number:=10;b number:=12;cnumber:=5;
begin
dbms_output.put_line('a='||a||'b='||b||'c='||c);ifa>bANDa>cthen
dbms_output.put_line('aisgreatest');
else
ifb>aANDb>cthen
dbms_output.put_line('bisgreatest');
else
dbms_output.put_line('cisgreatest');
end if;
end if;
end;
/




SQL>@6a	33
		
	NIT	DEPARTMENTOFCSE	

 
	DATABASE MANAGEMENT SYSTEMS
	a=10b=12c=5
bisgreatest
PL/SQLproceduresuccessfullycompleted.


B.CASEandCASEExpression:CASEstatementselectsonesequenceofstatementstoexecute.However,toselectthesequence,theCASEstatementusesaselectorratherthanmultipleBooleanexpressions. A selector is an expression, the value of which is used to select one of several alternatives.Syntax
CASEselector
WHEN 'value1' THEN S1;WHEN 'value2' THEN S2;WHEN'value3'THENS3;
...
ELSESn;--defaultcaseEND CASE;
SQL>createtableemp(enonumber(5),enamevarchar2(10),locvarchar(10),salarynumber(10,2));
Tablecreated.
SQL>insertintoempvalues(101,'ali','vja',15000);1rowcreated.
SQL>insertintoempvalues(102,'ravi','hyd',25000);1rowcreated.
SQL>insertintoempvalues(103,'raju','gnt',35000););1rowcreated.
SQL>insertintoempvalues(104,'rakesh','vja',45000);1rowcreated.
SQL>select*fromemp;
ENOENAME	LOC	SALARY
101	ali	vja	15000
102	ravi	hyd	25000
103	raju	gnt	35000
104	rakeshvja	45000

ExampleofCASEExpression:	34
		
	NIT	DEPARTMENTOFCSE	

 
	DATABASE MANAGEMENT SYSTEMS
	SQL>selectloc, case(loc)when'vja'thensalary+2000when'hyd'thensalary+1000elsesalary
end"rev_salary"fromemp;LOC	rev_salary	35
		
	vja	17000
hyd	26000
gnt	35000
vja	47000

PL/SQL CODE:PL/SQL CODE to demonstrate CASESQL>ed6b
setserveroutputon;declare
grade char(1);begingrade:='&grade';case
whengrade='a'then
dbms_output.put_line('Excellent');whengrade='b'then
dbms_output.put_line('verygood');whengrade='c'then
dbms_output.put_line('good');whengrade='d'then
dbms_output.put_line('fair');whengrade='f'then
dbms_output.put_line('poor');
else
dbms_output.put_line('Nosuchgrade');
endcase;end;
/

SQL>@6b
Enter value for grade: cold4:grade:='&grade';
new4:grade:='c';good

PL/SQLproceduresuccessfullycompleted.SQL> @6b
Enter value for grade: gold4:grade:='&grade';	
	NIT	DEPARTMENTOFCSE	

 
	DATABASE MANAGEMENT SYSTEMS
	new4:grade:='g';
No suchgrade

PL/SQLproceduresuccessfullycompleted.


C.NULLIF:Takestwoarguments.Ifthetwoargumentsareequal,thenNULLisreturned.otherwisethe firstargumentisreturned.
Syntax:selectcolumn_name,NULLIF(argument1,arguement2)fromtable_name;
Example:
SQL>selectename,nullif('ali','ali1')fromemp;
ENAME	NUL	36
		
	ali	ali
ravi	ali
raju	ali
rakesh	ali

SQL>selectename,nullif('ali','ali')fromemp;
ENAME	NUL	
	aliravirajurakesh

D.COALESCE:COALESCE()functionacceptsalistofargumentsandreturnsthefirstonethatevaluatestoa non-nullvalue.
Syntax:coalesce("expression1","expression2",...);
Example:
SQL>selectcoalesce(NULL,'CRRCOE','IT')fromdual;
COALECRRCOE
EXPERIMENT:7	
	NIT	DEPARTMENTOFCSE	

 
	DATABASE MANAGEMENT SYSTEMS
	ProgramdevelopmentusingWHILELOOPS,numericFORLOOPS,nestedloopsusingERROR
Handling,BUILT–INExceptions,USEdefinedExceptions,RAISEAPPLICATIONERROR.


A.	WHILELOOP:AWHILELOOPstatementinPL/SQLprogramminglanguagerepeatedlyexecutesatargetstatementas longas agivenconditionis true.
Syntax:
WHILE condition LOOPsequence_of_statements
ENDLOOP;
PL/SQL Code: A PL/SQL Program to find sum of ODD number upto given number using While loopSQL>ed7a

setserveroutputon;declare
inval number;endvalnumber;
snumber default0;
begin
inval:=1;endval:=&endval;
whileinval<endvalloops:=s+inval;inval:=inval+2;
endloop;
dbms_output.put_line('sumofodd numbersbetween1 and'||endval||' is'||s);end;
/

SQL>@7a
Entervalueforendval:100old  7:	endval:=&endval;
new7:	endval:=100;
sumofoddnumbersbetween1 and100is2500PL/SQLproceduresuccessfullycompleted.
B.	FORLoop:AFORLOOPisarepetitioncontrolstructure thatallowsustoefficientlywritealoop	37
		
	NIT	DEPARTMENTOFCSE	

 
	DATABASE MANAGEMENT SYSTEMS
	thatneedstoexecuteaspecificnumberoftimes.
Syntax
FORcounterINinitial_value..final_valueLOOPsequence_of_statements;
END LOOP;
PL/SQLCODE:APL/SQLcodetoprintmultiplicationtableusingforloop
SQL>ed 7b

set serveroutput on;DECLARE
VAR1 NUMBER;VAR2NUMBER;
BEGIN
dbms_output.put_line('Enternumbertoprintmultiplicationtable');VAR1:=&VAR1;
FORVAR2IN1..10LOOPDBMS_OUTPUT.PUT_LINE(VAR1||'X'||VAR2||'='||VAR1*VAR2);
END LOOP;END;
/
SQL>@7b
Entervalueforvar1:2old6:VAR1:=&VAR1;
new6:VAR1:=2;
Enternumertoprintmultiplicationtable2X1=2
2X2=4
2X3=6
2X4=8
2X5=10
2X6=12
2X7=14
2X8=16
2X9=18
2X10=20

PL/SQLproceduresuccessfullycompleted.	38
		
	NIT	DEPARTMENTOFCSE	

 
	DATABASE MANAGEMENT SYSTEMS
	C.NESTEDLOOP:PL/SQLallowsusingoneloopinsideanotherloop.Itmaybeeither
basic,whileorforloop.
Syntax:
WHILE condition1 LOOPsequence_of_statements1
WHILE condition2 LOOPsequence_of_statements2
END LOOP;END LOOP;
PL/SQLCODE:APL/SQLprogramtoprintnprimenumberusingnestedloop.SQL>ed7c
DECLARE
i number(3);jnumber(3);
BEGINi:=2;LOOP
j:= 2;LOOP
exitWHEN ((mod(i,j)=0)or (j =i));j:=j+1;
ENDLOOP;IF(j=i)THEN
dbms_output.put_line(i||'isprime');ENDIF;
i:=i+1;
exitWHENi=50;ENDLOOP;
END;
/
SQL>@7c	39
		
	NIT	DEPARTMENTOFCSE	

 
	DATABASE MANAGEMENT SYSTEMS
	2	isprime
3	isprime
5isprime
7isprime
11isprime
13isprime
17isprime
19isprime
23isprime
29isprime
31isprime
37isprime
41isprime
43isprime
47isprime


PL/SQLproceduresuccessfullycompleted.




















EXPERIMENT:8	40
		
	NIT	DEPARTMENTOFCSE	
 
	DATABASE MANAGEMENT SYSTEMS
	Programs	development	using	creation  of  procedures,  passing	parameters  IN  and	OUT  of
PROCEDURES.
SQL>createtableenquiry(enqno1number(3),fnamevarchar2(30));
Tablecreated.
SQL>insertintoenquiryvalues(111,'sai');
1rowcreated.
SQL>insertintoenquiryvalues(112,'sindhu');
1rowcreated.


PL/SQLCODEtocreateprocedure
SQL>edfindname

createprocedurefindname(enquiryno1INnumber,fname1OUTvarchar2)isfname2varchar2(30);
begin
selectfnameinto fname2fromenquirywhereenqno1=enquiryno1;fname1:=fname2;
exceptionwhenno_data_found then
raise_application_error(-20100,'Thegivennumberisnotpresent');end;
/

SQL>@findnameProcedurecreated.











PL/SQLCode forcallingprocedureinprogram	41
		
	NIT	DEPARTMENTOFCSE	

 
	DATABASE MANAGEMENT SYSTEMS
	SQL>ed pro8

setserveroutputon;declare
enqno2 number(5);fname2 varchar2(30);beginenqno2:=&enqno2;
findname(enqno2,fname2);
dbms_output.put_line('Personnameofequiryid'||enqno2||'is'||fname2);end;
/

SQL>@pro8
Entervalueforenqno2:114old5:enqno2:=&enqno2;
new5:enqno2:=114;declare
*
ERRORatline1:
ORA-20100: The given number is not presentORA-06512:at"SYSTEM.FINDNAME", line7
ORA-06512:atline6

SQL>@pro8
Entervalueforenqno2:112old5:enqno2:=&enqno2;
new5:enqno2:=112;

Person name of equiry id 112 is sindhuPL/SQLproceduresuccessfullycompleted.




EXPERIMENT:9	42
		
	NIT	DEPARTMENTOFCSE	

 
	DATABASE MANAGEMENT SYSTEMS
	Programdevelopmentusingcreationofstoredfunctions,invokefunctionsinSQLstatementsandwrite
complex functions.Sol:
SQL>createtabledept(deptnoint,dnamevarchar(10));
Tablecreated.
SQL>insertintodeptvalues(1219,'sai');
1rowcreated.


PL/SQLCODEtocreateuserdefinefunction

createorreplacefunctiongetname(dnonumber)returnvarchar2as
fname1 varchar2(30);begin
selectdnameintofname1 fromdeptwheredeptno=dno;return(fname1);
exception
whenno_data_found then
raise_application_error(-20100,'YourenteredDepartmentnumberisnotexists');end;
/


SQL> @getnameFunctioncreated.









PL/SQLCodeforcallingfunctioninprogram	43
		
	NIT	DEPARTMENTOFCSE	

 
	DATABASE MANAGEMENT SYSTEMS
	
SQL>ed pro9

set serveroutputon;declare
fname2 varchar2(30);deptno2 number(5);begindeptno2:=&deptno;
fname2:=getname(deptno2);dbms_output.put_line(fname2||'isindeptno'||deptno2);end;
/

SQL>@pro9
Enter value for deptno: 1219old5: deptno2:=&deptno;
new5:deptno2:=1219;


saiisindeptno1219
PL/SQLproceduresuccessfullycompleted.SQL> @pro9
Enter value for deptno: 1001old5: deptno2:=&deptno;
new5:deptno2:=1001;declare
*
ERRORatline1:
ORA-20100:YourenteredDepartmentnumberisnotexistsORA-06512:at"SYSTEM.GETNAME",line 9
ORA-06512:atline6


EXPERIMENT:10	44
		
	NIT	DEPARTMENTOFCSE	

 
DATABASE MANAGEMENT SYSTEMS

Developprogramsusingfeaturesparametersin aCURSOR,FORUPDATECURSOR,WHERECURRENTofclauseandCURSORvariables.	45

Sol:
SQL>createtablecustomers(idnumber(3),namevarchar2(10),agenumber(3),addressvarchar2(10),salarynumber(10,2));
Tablecreated.


SQL>insertintocustomersvalues(1,'ramesh',32,'ahmedabad',2000);
1rowcreated.
SQL>insertintocustomersvalues(2,'khilan',25,'Delhi',1500);
1rowcreated.
SQL>insert intocustomersvalues(3,'kaushik',23,'Kota',2000);
1rowcreated.
SQL>insertintocustomersvalues(4,'chitali',25,'Mumbai',6500);
1rowcreated.
SQL>select*fromcustomers;
IDNAME	AGEADDRESS	SALARY
	-			
1 ramesh	32ahmedabad	2000
2khilan	25Delhi	1500
3kaushik	23Kota	2000
4chitali	25Mumbai	6500

4rowsselected.










SQL>ed pro10

DECLARE
S
c_idcustomers.id%type;
c_namecustomers.name%type;
 
c_addrcustomers.address%type;
 
	DATABASE MANAGEMENT SYSTEMS
	



















SQL>@pro10
1	rameshahmedabad
2	khilanDelhi
3	kaushikKota
4	chitaliMumbai
PL/SQLproceduresuccessfullycompleted.













EXPERIMENT:11
Developprogramsusingbeforeandaftertriggers,rowandstatementtriggersandinsteadoftriggers.	46
		
	NIT	DEPARTMENTOFCSE	
 
	DATABASE MANAGEMENT SYSTEMS

Sol:
SQL>createtablecustomers(idnumber(3),namevarchar2(10),agenumber(3),address	47
	varchar2(10),salarynumber(10,2));
Tablecreated.


SQL>insertintocustomersvalues(1,'ramesh',32,'ahmedabad',2000);
1rowcreated.
SQL>insertintocustomersvalues(2,'khilan',25,'Delhi',1500);
1rowcreated.
SQL>insert intocustomersvalues(3,'kaushik',23,'Kota',2000);
1rowcreated.
SQL>insertintocustomersvalues(4,'chitali',25,'Mumbai',6500);
1rowcreated.
SQL>select*fromcustomers;
IDNAME	AGEADDRESS	SALARY
		-				 1 ramesh	32ahmedabad		2000
2	khilan	25Delhi	1500
3	kaushik	23Kota	2000
4	chitali	25Mumbai	6500


4rowsselected.











PL/SQLCodeforcreationoftriggerwhileinsert/updaterecordsintoatable.SQL>edpro11	
	NIT	DEPARTMENTOFCSE	

 
	DATABASE MANAGEMENT SYSTEMS
	CREATE OR REPLACE TRIGGER display_salary_changesBEFOREDELETEORINSERTORUPDATEONcustomersFOREACHROW
WHEN(NEW.ID>0)DECLARE
sal_diffnumber;BEGIN
sal_diff := :NEW.salary - :OLD.salary;dbms_output.put_line('Old salary: ' || :OLD.salary);dbms_output.put_line('New salary: ' || :NEW.salary);dbms_output.put_line('Salarydifference:'||sal_diff);
END;
/

SQL> @pro11Triggercreated.

SQL>insertintocustomersvalues(5,'Hardik',27,'Mumbai',5500);Oldsalary:
Newsalary:5500Salarydifference:
1rowcreated.


SQL>updatecustomerssetsalary=salary+500whereid=2;Oldsalary:1500
Newsalary:2000
Salarydifference:500
1rowupdated.









EXPERIMENT:12
Createatableandperformthesearchoperationontableusingindexing and non-indexingtechniques.	48
		
	NIT	DEPARTMENTOFCSE	

 
	DATABASE MANAGEMENT SYSTEMS
	Sol:
	SQL>CREATETABLETEACHER(STAFF_IDVARCHAR2(4)PRIMARYKEY,STAFF_NAME
	VARCHAR2(30),QUALIFICATIONVARCHAR2(10),HIREDATEDATE,JOBVARCHAR2(30),	
	ADDRESSVARCHAR2(15),PH_NUMNUMBER(10),SALARYNUMBER(7,2),DEPARTMENT	
	VARCHAR2(10));	
	
Tablecreated.	
	
SQL>insertintoteachervalues('T101','SUNITHA','MCA','29-JUN-06','ASSOCIATE	
	PROFESSOR','VIJAYAWADA',9985061308,23000,'MCA');	
	1rowcreated.	
	
SQL>insertintoteachervalues('T102','FREDSMITH','MTECH','07-MAR-03','ASSOCIATE	
	PROFESSOR','GUNTUR',9985063445,36000,'MBA');	
	1rowcreated.	
	
SQL>insertintoteachervalues('T103','JACKBARNES','BTECH','27-JUN-	
	07','PROFESSOR','TENALI',9985012345,27000,'MTECH');	
	1rowcreated.	
	
SQL>insertintoteachervalues('T104','JANEDOE','MCA','04-JUL-06','ASSISTANT	
	PROFESSOR','VIJAYAWADA',9985045678,29000,'BTECH');	
	1rowcreated.	
	
SQL>insertintoteachervalues('T105','JOESHMOE','MBA','16-AUG-08','ASSOCIATE	
	PROFESSOR','ELURU',9987651308,36000,'MCA');	
	1rowcreated.	
	
SQL>insert intoteachervalues('T106','JONBAKER','MSC(COM)','12-JAN-	
	03','PROFESSOR','HYDERABAD',8876561308,46000,'MCA');	
	1rowcreated.	
	NIT	DEPARTMENTOFCSE	
 
	DATABASE MANAGEMENT SYSTEMS
	SQL>insertintoteachervalues('T107','JOHNDOE','MSC(PHY)','06-FEB-04','ASSISTANT
PROFESSOR','VIJAYAWADA',8345661308,31000,'MBA');
1rowcreated.

SQL>insert into teacher values('T108','KIM SMITH','MCA','10-MAR-08','ASSISTANTPROFESSOR','VIZAG',8374561308,26000,'MTECH');
1rowcreated.

SQL>insertintoteachervalues('T109','MARYPARKER','MTECH','02-APR-09','PROFESSOR','NELLORE',7893427649,52000,'MBA');
1rowcreated.

SQL>insertintoteachervalues('T110','SAMUELJOHN','BTECH','19-MAY-05','ASSISTANTPROFESSOR','ELURU',9982222208,26000,'MBA');
1rowcreated.


SQL>insertintoteachervalues('T111','FRANKLINWONG','MBA','20-AUG-06','ASSOCIATEPROFESSOR','VIZAG',9985033333,20000,'MTECH');
1rowcreated.


SQL>insertintoteachervalues('T112','SLICIAZELAYA','MCA','16-SEP-04','ASSISTANTPROFESSOR','VIJAYAWADA',9985202020,33000,'BTECH');
1rowcreated.


SQL>insert intoteachervalues('T113','JENNIFERWALLACE','MSC(MATHS)','25-OCT-03','PROFESSOR','HYDERABAD',9902192033,54000,'MCA');
1rowcreated.


SQL>insertintoteachervalues('T114','RAMESHNARAYANA','MCA','24-NOV-04','ASSOCIATEPROFESSOR','NARASARAOPET',9988776655,34000,'MBA');
1rowcreated.	50
		
	NIT	DEPARTMENTOFCSE	
 
DATABASE MANAGEMENT SYSTEMS
SQL>insertintoteachervalues('T115','JOYCEENGLISH','MBA','22-DEC-06','ASSISTANT
PROFESSOR','VIJAYAWADA',9998765443,45000,'MBA');	51
1rowcreated.


Toshowthequeryexecutiontimingusethe followingquerySQL> Settimingon;

Retrievedetailsofteacherbeforecreation ofindex.


SQL>select*fromteacher;


STAFSTAFF_NAMEQUALIFICATHIREDATEJOBADDRESSPH_NUM  SALARYDEPARTMENT

 

T101SUNITHAMCA29-06-06ASSOCIATE PROFESSORVIJAYAWADA998506130823000MCAT102 FRED SMITHMTECH07-MAR-03ASSOCIATE PROFESSORGUNTUR998506344536000MBAT103JACKBARNESBTECH27-JUN-07PROFESSORTENALI998501234527000MTECH
T104 JANE DOE MCA 04-JUL-06ASSISTANT PROFESSORVIJAYAWADA 998504567829000BTECHT105JOESHMOEMBA16-AUG-08ASSOCIATEPROFESSORELURU998765130836000MCA
T106JONBAKERMSC(COM)12-JAN-03PROFESSORHYDERABAD  887656130846000MCA
T107JOHN DOEMSC(PHY)06-FEB-04 ASSISTANT PROFESSOR VIJAYAWADA 834566130831000MBAT108KIMSMITHMCA10-MAR-08ASSISTANTPROFESSORVIZAG837456130826000MTECH
T109MARYPARKERMTECH02-APR-09PROFESSORNELLORE789342764952000MBA
T110SAMUEL JOHNBTECH19-MAY-05ASSISTANT PROFESSORELURU9982222208   26000   MBAT111FRANKLIN WONGMBA 20-AUG-06ASSOCIATE PROFESSORVIZAG998503333320000MTECHT112SLICIAZELAYAMCA16-SEP-04ASSISTANTPROFESSORVIJAYAWADA998520202033000BTECHT113JENNIFERWALLACEMSC(MATHS)25-OCT-03PROFESSORHYDERABAD990219203354000MCA
T114RAMESH NARAYANA MCA 24-NOV-04 ASSOCIATE PROFESSORNARASARAOPET998877665534000MBAT115JOYCEENGLISHMBA22-DEC-06ASSISTANTPROFESSORVIJAYAWADA999876544345000MBA

15rowsselected.
Elapsed:00:00:00.24



Indexcreation:


 
NIT	DEPARTMENTOFCSE
 
DATABASE MANAGEMENT SYSTEMS
SQL>createindexteacher_job_indonteacher(job);
Indexcreated.	52
Elapsed:00:00:00.00


Retrievedetailsofteacheraftercreationofindex.


SQL>select*fromteacher;


STAFSTAFF_NAMEQUALIFICATHIREDATEJOBADDRESSPH_NUM  SALARYDEPARTMENT

 

T101SUNITHAMCA29-06-06ASSOCIATE PROFESSORVIJAYAWADA998506130823000MCAT102 FRED SMITHMTECH07-MAR-03ASSOCIATE PROFESSORGUNTUR998506344536000MBAT103JACKBARNESBTECH27-JUN-07PROFESSORTENALI998501234527000MTECH
T104 JANE DOE MCA 04-JUL-06ASSISTANT PROFESSORVIJAYAWADA 998504567829000BTECHT105JOESHMOEMBA16-AUG-08ASSOCIATEPROFESSORELURU998765130836000MCA
T106JONBAKERMSC(COM)12-JAN-03PROFESSORHYDERABAD  887656130846000MCA
T107JOHN DOEMSC(PHY)06-FEB-04 ASSISTANT PROFESSOR VIJAYAWADA 834566130831000MBAT108KIMSMITHMCA10-MAR-08ASSISTANTPROFESSORVIZAG837456130826000MTECH
T109MARYPARKERMTECH02-APR-09PROFESSORNELLORE789342764952000MBA
T110SAMUEL JOHNBTECH19-MAY-05ASSISTANT PROFESSORELURU9982222208   26000   MBAT111FRANKLIN WONGMBA 20-AUG-06ASSOCIATE PROFESSORVIZAG998503333320000MTECHT112SLICIAZELAYAMCA16-SEP-04ASSISTANTPROFESSORVIJAYAWADA998520202033000BTECHT113JENNIFERWALLACEMSC(MATHS)25-OCT-03PROFESSORHYDERABAD990219203354000MCA
T114RAMESH NARAYANA MCA 24-NOV-04 ASSOCIATE PROFESSORNARASARAOPET998877665534000MBAT115JOYCEENGLISHMBA22-DEC-06ASSISTANTPROFESSORVIJAYAWADA999876544345000MBA

15rowsselected.
Elapsed:00:00:00.13













 
NIT	DEPARTMENTOFCSE
 

Leave a Comment