Untitled
unknown
plain_text
a year ago
42 kB
5
Indexable
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
Editor is loading...
Leave a Comment