CS407PC:DATABASEMANAGEMENTSYSTEMSLAB
[Link]. LTPC
0031.5
Co-requisites:
Co-requisiteofcourse“DatabaseManagementSystems”
CourseObjectives:
IntroduceERdatamodel,databasedesignandnormalization
LearnSQLbasicsfordatadefinitionanddatamanipulation
CourseOutcomes:
Design database schema for a given application and apply normalization
Acquireskillsin using SQLcommandsfor datadefinition and datamanipulation.
Developsolutionsfordatabaseapplicationsusingprocedures,cursorsandtriggers
LISTOFEXPERIMENTS:
1. ConceptdesignwithE-RModel
2. RelationalModel
3. Normalization
4. PracticingDDLcommands
5. PracticingDMLcommands
6. Querying(usingANY,ALL,IN,Exists,NOTEXISTS,UNION,INTERSECT,Constraintsetc.)
7. QueriesusingAggregatefunctions, GROUP BY, HAVINGandCreationanddroppingof
Views.
8. Triggers(Creationofinserttrigger,deletetrigger,updatetrigger)
9. Procedures
10. UsageofCursors
WEEK:1
ConceptDesignwith ERModel
Conceptdesignwith E–R model
Relate the entities appropriately. Apply cardinalities for each relationship. Identify strongentities
and weak entities (if any). Indicate the type of relationship (total/partial). Try to incorporate
generalization, aggregation, specialization etc wherever required.
Definitions:
Thecardinalityratio: -forabinaryrelationshipspecifiesthe maximumnumberofrelationships that
an entity can participate in.
Relationship:- itisdefinedasanassociationamongtwoormore entities.
Weak and strong entity:- anentityset maynothave sufficient attributesto formaprimarykey. Such
an entity set is termed a weak entity set. An entity set that has primary key is termed a strong
entity set.
Totalparticipation:-
Ex: - if a travel agency states that every passenger must make reservation then every passenger
travels in bus. Than a passengers entity can exist only if it participates in atleast one travels
relationship instances. Thus the participation of passenger in travel is called total participation
meaning that every entity in the “total set” passenger entities must be related to bus via travels
relationship.
Allpassengerstravelinonebussoitistotalparticipation
Partialparticipation:aparticipationthatisnottotaliscalledaspartialparticipation.
Relationshipbetweendifferententities:
RelationshipbetweenBus andTicketentities
1:Mbinaryrelationship
RelationshipbetweenPassengerandBusentities
M:1binaryrelationship
RelationshipbetweenPassengerandTicketentities
M:Nbinaryrelationship
1. DrawtheERDiagramfor UNIVERSITY
2. DrawtheERDiagramforHospitalmanagementSystems.
3. DrawtheERdiagramforHRmanagementsystems 4
4. DrawtheERdiagramforHRmanagementsystems
WEEK:2
RelationalModel
Relationalmodel
Represent all entities (strong, week) in tabular fashion. Represent relationships in a tabular
fashion. There are different ways of representing as tables based on the cardinality. Represent
attributes as columns in the tables or as tables based on the requirement. Different types of
attributes (composite, multivalued and derived).
Definitions:
Composite attributes: can be divided into smaller sub parts which represent more basic
attributes with independent meaning.
Multivalued attributes: for exthe attribute inthe BusentityBustypecanhavedifferent typesof
buses according that the Bustype attribute contains the values as Garuda, Luxury, Express, and
Ordinary. This type of attribute is called multivalued attribute and may have lower and upper
bounds to constrain the number of values allowed for each individual entity.
Derived attributes:
In some cases, two or more attribute values are related. With the help of one attribute we get the
value of another attribute. Age and DOB attributes. With the DOB we get the age of the personto
the current date.
RelationalModelconcept
[Link] column has a
name or attribute.
Domain:Itcontainsasetofatomicvaluesthatanattributecan take.
Attribute:[Link],dom(Ai)
RelationalModelconcept
Relational modelcanrepresent asatablewithcolumns androws. Each rowis knownas atuple. Eachtableofthe column has a
name or attribute.
Domain:Itcontainsa setofatomic valuesthatanattributecantake.
Attribute:Itcontainsthenameofacolumnina [Link] domain,dom(Ai)
Relationalinstance: Intherelationaldatabasesystem, therelational instanceisrepresentedbya finiteset oftuples. Relation
instances do not have duplicate tuples.
Relationalschema: Arelationalschema containsthenameoftherelationandnameofallcolumnsor attributes. Relational
key: Intherelational key, eachrow has one or moreattributes. It canidentifytherow intherelation uniquely.
Example:STUDENTRelation
NAME ROLL_NO PHONE_NO ADDRESS AGE
Ram 14795 7305758992 Noida 24
Shyam 12839 9026288936 Delhi 35
Laxman 33289 8583287182 Gurugram 20
Mahesh 27857 7086819134 Ghaziabad 27
Ganesh 17282 90289i3988 Delhi 40
o Inthegiventable,NAME, ROLL_NO,PHONE_NO,ADDRESS,andAGEaretheattributes.
o TheinstanceofschemaSTUDENThas5tuples.
o t3=<Laxman,33289,8583287182,Gurugram,20>
Integrityconstraints
NOTNULL
UNIQUE
DEFAULT
CHECK
KeyConstraints–PRIMARYKEY,FOREIGNKEY
Domainconstraints
NOTNULL:
NOT NULLconstraintmakessurethata columndoesnotholdNULLvalue. Whenwedon’tprovidevaluefora particular
column while inserting a record into a table, it takes NULL value bydefault. Byspecifying NULL constraint, we can
be sure that a particular column(s) cannot have NULL values.
UNIQUE:
UNIQUE Constraintenforces a column orset [Link], it
means that particular column cannot have duplicate values in a table.
DEFAULT:
TheDEFAULT constraintprovidesa defaultvaluetoacolumn when thereisnovalueprovided whileinsertinga record
into a table.
CHECK:
Thisconstraintisusedfor specifyingrangeofvaluesfora particular columnofa table. Whenthisconstraintisbeing set on a
column, it ensures that the specified column musthave the value falling in the specified range.
Keyconstraints:
PRIMARY KEY:
Primarykeyuniquelyidentifies each recordin atable. Itmusthaveuniquevaluesandcannot [Link] the below
example the ROLL_NO field is marked as primarykey, that means the ROLL_NO field cannot have duplicate and
null values.
FOREIGNKEY:
Foreign keys arethecolumns ofa tablethatpointstotheprimarykeyofanother table. Theyact asa cross-reference
between tables.
Readmoreaboutithere.
Domainconstraints:
Eachtablehascertain setofcolumnsandeachcolumnallowsa sametype ofdata,basedonitsdatatype. The column does not
accept values of any other data type.
Example:create table student(sid varchar(30)primary key, sname
char(20)unique,ageint(20)check(age>16),cityvarchar(20)default
‘hyderabad’)
Create table department(did varchar(20) primary key,dname
char(20),sidvarchar(30),Foreignkey(sid)referencesstudent(sid));
Companydatabase
CREATE TABLEemployee(
emp_id INTPRIMARYKEY,
first_nameVARCHAR(40),
last_nameVARCHAR(40),
birth_dayDATE,
sexVARCHAR(10),
salaryINTcheck(salary>0),
super_idINT,
branch_idINT);
CREATE TABLEbranch(
branch_id INTPRIMARYKEY,
branch_nameVARCHAR(40),
mgr_idINT,
mgr_start_dateDATE,
FOREIGNKEY(mgr_id)REFERENCESemployee(emp_id));
CREATE TABLE client(
client_id INTPRIMARYKEY,
client_nameVARCHAR(40),
branch_idINT,
FOREIGNKEY(branch_id)REFERENCESbranch(branch_id));
WEEK:3
Normalization
Database normalization is a technique for designing relational database tables to minimize
duplication of information and, in doing so , to safeguard the database against certain types
of logical or structural problems namely data anomalies.
Thenormalizationformsare:
1) FirstNormalForm:1NFrequiresthatthevaluesineachcolumnofatable are
atomic. Byatomicwemeanthatthereare no setsofvalueswithina column.
2) SecondNormalForm:wherethe1NFdealswithatomicityofdata,the2NFdeals with
relationships between composite key columns and non-key columns. To achieve
2NF the tables should be in 1NF. The 2NF any non-key columns must depend on
the entire primary key.
3) Third Normal Form: 3NF requires that all columns depend directly on the primary
key. Tables violate the third normal form when one column depends ananother
column, which in turn depends on the primary key(transitive dependency).
One way to identify transitive dependency is to look at your tables and see if any
columnswouldrequireupdatingifanothercolumninthetable [Link] a
column exists, it probably violates 3NF.
1NF
RelationEMPLOYEEisnotin1NFbecauseofmulti-valuedattribute EMP_PHONE.
Relationcanbeconvertedinto1NFasfollows
Employee_details
2NF
o Inthe2NF,relationalmustbein1NF.
o Inthesecondnormalform,allnon-keyattributesare fullyfunctionaldependentontheprimarykey
Relationnotin2nf
Converttheaboverelationinto2NFasfollows
Createtheteacher_detailtablewithteacher_idasprimarykey
Createteacher_subjecttablewithteacher_idasforeignkey
3NF
o Arelationwillbein3NFif itisin2NFand notcontainanytransitivepartial dependency.
o 3NFisused [Link] toachievethedata integrity.
o Ifthereisnotransitivedependencyfornon-primeattributes,thentherelationmustbeinthirdnormal form.
Arelationisinthirdnormalformifitholdsatleastoneofthefollowingconditionsforeverynon-trivialfunction dependency X →
Y.
1. X isasuperkey.
2. Yisaprimeattribute,i.e.,eachelementofYispartofsomecandidatekey.
The above table can be converted into 3NF as follows
Createtheemployeetablewithemp_idasprimarykey
Createemployee_ziptablewithemp_zipasprimarykey
WEEK:4
PracticingDDL commands
DataDefinitionLanguage(DDL)
o DDLchangesthestructureofthetablelikecreating atable,deleting atable,altering atable,etc.
o AllthecommandofDDLareauto-committedthatmeansitpermanentlysaveallthechangesinthe database.
HerearesomecommandsthatcomeunderDDL:
o CREATE
o ALTER
o DROP
o TRUNCATE
a. CREATEItisused tocreateanewtable inthe database.
Syntax:
CREATETABLETABLE_NAME(COLUMN_NAMEDATATYPES[,........ ]);
Example:
CREATETABLEEMPLOYEE(NameVARCHAR2(20),EmailVARCHAR2(100),DOBDATE);
b. DROP:Itisused todeleteboththestructureand recordstored inthetable.
Syntax
DROPTABLE;
Example
DROPTABLE EMPLOYEE;
c. ALTER: Itisusedtoalterthestructureof [Link]
characteristics of an existing attribute or probably to add a new attribute.
Syntax:
Toaddanewcolumninthe table
ALTERTABLEtable_nameADDcolumn_nameCOLUMN-definition; To
modify existing column in the table:
ALTERTABLEMODIFY(COLUMNDEFINITION....... );
EXAMPLE
ALTERTABLESTU_DETAILSADD(ADDRESSVARCHAR2(20));
ALTERTABLESTU_DETAILSMODIFY(NAMEVARCHAR2(20));
d. TRUNCATE:Itisused todeletealltherowsfromthetableandfreethespacecontaining thetable.
Syntax:
TRUNCATETABLEtable_name;
Example:
TRUNCATETABLEEMPLOYEE;
WEEK:5
PracticingDMLcommands
DataManipulationLanguage
o DMLcommandsareused [Link].
o ThecommandofDMLisnotauto-committedthatmeansitcan'tpermanentlysaveallthechangesinthe database.
They can be rollback.
HerearesomecommandsthatcomeunderDML:
o INSERT
o UPDATE
o DELETE
a. INSERT:[Link] toinsertdataintotherowof a table.
Syntax:
INSERTINTOTABLE_NAME(col1,col2,col3,....colN)VALUES(value1,value2, value3, .................. valueN);
Or
INSERTINTOTABLE_NAMEVALUES (value1,value2,value3, ............ valueN);
Forexample:
INSERTINTOjavatpoint(Author,Subject)VALUES("Sonoo","DBMS");
b. UPDATE:Thiscommand isusedtoupdateormodify thevalueofacolumninthetable.
Syntax:
UPDATEtable_nameSET[column_name1=value1,.column_nameN=valueN][WHERECONDITION]
Forexample:
UPDATEstudents
SETUser_Name='Sonoo'WHERE
Student_Id = '3'
c. DELETE:Itisused toremoveone ormorerowfromatable.
Syntax:
DELETEFROMtable_name[WHEREcondition];
Forexample:
DELETEFROMjavatpointWHERE Author="Sonoo";
WEEK:6
Querying(usingANY,IN,NOTINUNIONUNIONALL.)
IN, NOT IN operators in SQL are used with SELECT, UPDATE and
DELETEstatements/queries to select, update and delete only particular records in a table those
meet thecondition given in WHERE clause and conditions given in IN, NOT IN operators. I.e. it
filtersrecords from a table as per the condition. Syntax for SQL IN & NOT IN operators are
givenbelow.
UNION
TheUNIONcommandcombinestheresult setoftwoormoreSELECTstatements(onlydistinct values)
The followingSQLstatement returnsthecities(onlydistinct values) fromboththe"Customers" and
the "Suppliers" table:
Example
SELECTCityFROMCustomers
UNION
SELECTCityFROMSuppliers
ORDER BY City;
UNIONALL
TheUNIONALLcommandcombinestheresult setoftwoormoreSELECTstatements(allows
duplicate values).
The followingSQLstatement returnsthecities(duplicatevaluesalso)fromboththe "Customers" and
the "Suppliers" table:
Example
SELECTCityFROMCustomers
UNION ALL
SELECTCityFROMSuppliers
ORDER BY City;
mysql>createdatabasenested; mysql>
use nested;
Databasechanged
mysql>select*fromemployee;
+ + + + +
|eid|ename|dept |salary|
+ + + + +
|1|rahul|hr |10000|
|2|ramesh|mrkt |20000|
|3|rajat|hr |30000|
|4|rakesh|marketing|40000|
|5 |rharshit|it |50000|
|6 |himesh| marketing|60000|
+ + + + +
6 rows in set (0.00 sec)
mysql>select*fromproject;
+ + + + +
|eid|pid|pname |plocation|
+ + + + +
|1|p1|iot |hyd |
|5|p2|android|pune |
|4| p3|networking|bengalore|
|4| p3|database|mangalore|
|3| p4|database|mangalore|
+ + + + +
5rowsinset(0.00 sec)
1) IN
TheINoperatorisusedwhenyouwanttoretrieveacolumnthathasentriesinthetableor referencing table.
Syntax
expressionIN(value1,value2, ........ value_n);
query
1) select*fromemployeewhereenameIn('ramesh','mahesh','suresh');
2) selectenamefromemployeewhereeidIn(selecteidfromprojectwhere
[Link]=[Link]);
3) selectename,salaryfromemployeewhereeid In(selecteidfromprojectwhere
[Link]=[Link]);
2) NOTIN
TheNOTINoperatorisusedwhenyouwanttoretrieveacolumnthathasnoentriesinthetableor referencing
table.
select*fromemployeewhereenameNotIn('ramesh','mahesh','suresh');
3) ANY
The ANYoperatorreturnstrueifanyofthesubquery valuesmeetthecondition.
Syntax
SELECTcolumn_name(s)
FROMtable_name
WHEREcolumn_nameoperatorANY
(SELECT column_nameFROMtable_nameWHEREcondition);
select*fromemployeewhereeid=ANY([Link]=[Link]);
4) All
TheALLoperatorreturnstrueifallofthe subqueryvalues meet the condition.
ALLSyntax
SELECTcolumn_name(s)
FROMtable_name
WHEREcolumn_nameoperator ALL
(SELECT column_nameFROMtable_nameWHEREcondition);
UNION
TheUNIONoperatoris usedtocombine theresult-set oftwoormore SELECT statements.
EachSELECTstatementwithinUNIONmusthavethesamenumberof columns
Thecolumnsmust alsohave similardatatypes
The columnsineachSELECTstatementmust alsobeinthesameorder
UNIONSyntax
SELECTcolumn_name(s)FROM table1
UNION
SELECTcolumn_name(s)FROMtable2;
Example
Createtableemp1(eidint,enamevarchar(20),addressvarchar(20),salaryint(20));
Createtableemp2(eidint,enamevarchar(20),addressvarchar(20),salaryint(20));
mysql> select * from emp1;
+++ ++
| eid|ename |address |salary|
+ + + + +
| 1|rahul |pune |10000|
| 2|rajat |hyd |20000|
| 3|rakesh|mangalore|30000|
| 4|harshit|umerga |40000|
| 5|harshit|mangalore|30000|
| 6|somesh|solapur |50000|
| 1|saket |aaa |10000|
| 2|sanket|bbb |20000|
+ + + + +
mysql>select*fromemp2;
+ + + + +
|eid|ename|address|salary|
+ + + + +
| 1|saket|aaa |10000|
| 2|sanket|bbb |20000|
| 3|rishi|ccc |40000|
| 4|sanket|ddd |50000|
| 1|saket|aaa |10000|
| 2|sanket|bbb |20000|
+ + + + +
mysql> select eid,ename from emp1 union select eid,ename from emp2;
mysql>selecteid,enamefromemp1unionallselecteid,enamefromemp2;
WEEK:7
Aggregatefunctions,GROUPBY,HAVINGandCreationanddroppingof Views.
Createthetables inthedatabasewithdifferent attributesinserttherecordsinthetablesand perform the
execution of the following functions
SQLAggregate Functions
o SQLaggregationfunctionisusedto performthecalculations onmultiplerowsofasingle
column of a table. It returns a single value.
o Itisalsousedtosummarizethedata.
TypesofSQLAggregationFunction
1. COUNT FUNCTION
o [Link] bothnumericand non-
numeric data types.
o COUNTfunctionusestheCOUNT(*)[Link](*) considers
duplicate and Null.
Syntax
COUNT(*)
or
COUNT([ALL|DISTINCT]expression)
Examples
1) SELECT COUNT(*)
FROMPRODUCT_MAST;
2) SELECT COUNT(*)
FROMPRODUCT_MAST;
WHERE RATE>=20;
Example:COUNT()withDISTINCT
SELECT COUNT(DISTINCT COMPANY)
FROM PRODUCT_MAST;
2. SUMFunction
Sumfunctionisused tocalculatethesumofallselected [Link].
Syntax
SUM()
or
SUM([ALL|DISTINCT]expression)
Example: SUM()
SELECT SUM(COST)
FROMPRODUCT_MAST;
Example:SUM()with WHERE
SELECT SUM(COST)
FROMPRODUCT_MAST
WHERE QTY>3;
3. AVGfunction
[Link] all non-Null
values.
Syntax
AVG()
Example:
SELECT AVG(COST)
FROMPRODUCT_MAST;
4. MAXFunction
MAXfunctionisusedtofindthe [Link] all selected
values of a column.
Syntax
MAX()
Example:
SELECT MAX(RATE)
FROMPRODUCT_MAST;
5. MINFunction
MINfunctionisusedtofindtheminimumvalueofacertain column. Thisfunctiondeterminesthesmallestvalueof all selected
values of a column.
Syntax
MIN()
SELECT MIN(RATE)
FROMPRODUCT_MAST;
GroupBy
TheMYSQLGROUPBYClauseisusedtocollectdatafrommultiplerecordsandgrouptheresultbyoneor more column. It is
generally used in a SELECT statement.
Syntax:
SELECTexpression1,expression2,...expression_n,
aggregate_function (expression)
FROM tables
[WHEREconditions]
GROUPBYexpression1,expression2,... expression_n;
Examples
SELECTaddress,COUNT(*)
FROMofficers
GROUPBYaddress;
SELECTemp_name,SUM(working_hours)AS"Totalworking hours"
FROMemployees
GROUPBYemp_name;
MySQLHAVINGClause
MySQLHAVING [Link].
Syntax:
SELECTexpression1,expression2,...expression_n,
aggregate_function (expression)
FROM tables
[WHEREconditions]
GROUPBYexpression1,expression2,... expression_n
HAVINGcondition;
example
SELECTemp_name,SUM(working_hours)AS"Totalworking hours"
FROMemployees
GROUPBYemp_name
HAVING SUM(working_hours)>5;
CREATE VIEW Statement
InSQL,aviewis avirtualtablebasedonthe result-set ofanSQL statement.
Aview contains rowsandcolumns,justlike [Link] fieldsinaview arefields fromone or more
real tables in the database.
CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECTcolumn1,column2,...
FROM table_name
WHEREcondition;
Example
CREATEVIEW[BrazilCustomers]AS
SELECT CustomerName, ContactName
FROM Customers
WHERECountry="Brazil";
SELECT*FROM[Brazil Customers];
SQLDroppingaView
A viewisdeletedwiththeDROPVIEW command.
SQL DROPVIEWSyntax
DROPVIEWview_name;
WEEK:8
Triggers
Triggers
Triggersarestoredprograms,whichareautomaticallyexecutedorfiredwhensomeeventoccurs.
Syntaxforcreating trigger:
CREATE[ORREPLACE]TRIGGER trigger_name
{BEFORE|AFTER|INSTEADOF}
{INSERT[OR]|UPDATE[OR]|DELETE}
[OFcol_name]
ONtable_name
[REFERENCINGOLDASoNEWASn] [FOR
EACH ROW]
WHEN(condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
mysql>use triggers;
Databasechanged
mysql>createtablestudent(sidint(20)primarykey,snamevarchar(20),addressvarchar(20),marks int(20));
QueryOK,0rows affected(0.17sec)
mysql>createtriggersample_insertbeforeinsertonstudentforeachrowsetnew.marks=[Link]+5; Query
OK, 0 rows affected (0.01 sec)
mysql>insertintostudentvalues(1,'aaa','pune',76,'patil');
Query OK, 1 row affected (0.06 sec)
mysql>insertintostudentvalues(2,'bbb','hyd',70,'rao');
Query OK, 1 row affected (0.06 sec)
mysql>insertintostudentvalues(3,'ccc','chd',36,'balude');
Query OK, 1 row affected (0.07 sec)
mysql>insertintostudentvalues(4,'ddd','delhi',35,'malude');
Query OK, 1 row affected (0.06 sec)
mysql>select*fromstudent;
+ + + + + +
|sid|sname|address| marks|lname|
+ + + + + +
|1 |aaa| pune|81 |patil|
|2|bbb|hyd |75|rao|
|3 |ccc|chd |41|balude|
|4|ddd|delhi|40|malude|
+ + + + + +
4rowsinset(0.00 sec)
mysql>createtabledoctor(didint(30)primarykey,dnamevarchar(20),addressvarchar(20),age int(20),city
varchar(20));
QueryOK,0rows affected(0.10sec)
mysql>insertintodoctorvalues(1,'ramesh','himayatnagar',34,'hyderabad');
Query OK, 1 row affected (0.07 sec)
mysql>insertintodoctorvalues(2,'jogit','dilsukhnagar',35,'hyderabad'); Query
OK, 1 row affected (0.37 sec)
mysql>insertintodoctorvalues(3,'gupta','mehandipatnam',37,'hyderabad');
Query OK, 1 row affected (0.06 sec)
mysql>insertintodoctorvalues(4,'atmaram','begumpet',38,'hyderabad');
QueryOK,1rowaffected(0.07sec)
mysql>select*fromdoctor;
+ + + + + +
|did|dname|address |age|city |
+ + + + + +
|1 |ramesh| himayatnagar|32|hyderabad|
|2|jogit|dilsukhnagar|33 |hyderabad|
|3|gupta|mehandipatnam|35|hyderabad|
|4|atmaram| begumpet |36|hyderabad|
+ + + + + +
4rowsinset(0.00 sec)
WEEK9
PROCEDURES
ThePL/SQLstoredprocedureorsimplyaprocedureisaPL/SQLblockwhichperformsoneormorespecifictasks. It is just like
procedures in other programming languages.
Syntaxforcreatingprocedures
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN|OUT|INOUT]type[,...])]
{IS|AS} BEGIN
<procedure_body>END
procedure_name;
Mysql>Createtabledoctor(didint(20),dnamevarchar(20),addressvarchar(20),ageint(20),city varchar(20));
mysql>delimiter/
mysql>createproceduredisp_doctor()
->begin
->select*from doctor;
->end;
->/
QueryOK,0rowsaffected(0.14sec)
mysql> call disp_doctor()/
+ + + + + +
|did|dname|address |age|city |
+ + + + + +
|1 |ramesh| himayatnagar|32|hyderabad|
|2 |jogit|dilsukhnagar|33| hyderabad|
|3|gupta|mehandipatnam|35|hyderabad|
|4|atmaram| begumpet |36|hyderabad|
+ + + + + +
4rowsinset(0.00 sec)
QueryOK,0rows affected(0.03sec)
mysql>createproceduredoctor_details()
->begin
-> selectdid,dnamefromdoctor;
->end;
->/
QueryOK,0rowsaffected(0.00sec)
mysql> call doctor_details()/
+ + +
|did|dname|
+ + +
|1 |ramesh|
|2|jogit|
|3 |gupta|
|4|atmaram|
+ + +
4rowsinset(0.00 sec)
QueryOK,0rowsaffected(0.02sec)
QueryOK,0rows affected(0.03sec)
mysql>createprocedurefind_doctorid(inidint)
->begin
->select*fromdoctor wheredid=id;
->end;
->/
QueryOK,0rowsaffected(0.00sec)
mysql> call find_doctorid(2)/
+ + + + + +
|did|dname|address |age|city |
+ + + + + +
|2 |jogit| dilsukhnagar|33 |hyderabad|
+ + + + + +
1rowinset(0.10 sec)
QueryOK,0rowsaffected(0.11sec)
mysql> call find_doctorid(4)/
+ + + + + +
|did|dname|address|age|city |
+ + + + + +
|4 |atmaram|begumpet|36| hyderabad|
+ + + + + +
1rowinset(0.01 sec)
mysql>createproceduredoctor_info()
->begin
->selectdid,dname,cityfromdoctor;
->end;
->/
QueryOK,0rows affected(0.01sec)
mysql>call doctor_info()/
+ + + +
|did|dname|city |
+ + + +
|1 |ramesh| hyderabad|
|2 |jogit|hyderabad |
|3 |gupta| hyderabad|
|4 |atmaram|hyderabad|
+ + + +
4rowsinset(0.00 sec)
QueryOK,0rows affected(0.01sec)
WEEK 10
USAGEOFCURSORS
CursorsInMySQL,[Link] isusedfor
theresult setandreturned fromaquery. Byusing acursor, youcaniterate,orbystepthroughthe results
of a queryand perform certain operations on each row. The cursor allows you to iterate
throughthe result set and thenperformthe additionalprocessing onlyonthe rowsthat require it. In a
cursor contains the data in a loop. Cursors may be different fromSQL commands that operate on
all the rows in the returned by a query at one time.
Therearesomestepswehaveto follow, givenbelow:
Declareacursor
Openacursorstatement
Fetchthecursor
Closethecursor
mysql>select*fromstudents;
+ + + + +
|sid|sname|age|marks|
+ + + + +
|1|ravi|15| 25|
|2| ramu|20|30 |
|2|rahul|18| 26|
|5|kiran|19|28|
|6|varun|21|32|
|8|ramesh|22|33|
|8| rohit|10|20|
+ + + + +
7rowsinset(0.00 sec)
mysql>delimiter$$
mysql>createprocedurep1(in_customer_idint)
->begin
-> declarev_idint;
->declarev_name varchar(20);
->declarev_finishedintegerdefault 0;
->declarec1cursorforselectsid,snamefromstudentswheresid=in_customer_id;
-> declarecontinuehandlerforNOTFOUNDSETv_finished=1;
-> open c1;
->std:LOOP
-> fetchc1intov_id,v_name;
-> ifv_finished=1then
->leave std;
->end if;
->selectconcat(v_id,v_name);
->endloop std;
-> closec1;
->end;
->$$
QueryOK,0rows affected(0.10sec)
mysql>call p1(2);$$
+ +
|concat(v_id,v_name)|
+ +
|2ramu |
+ +
1rowinset(0.00 sec)
+ +
|concat(v_id,v_name)|
+ +
|2rahul |
+ +
1rowinset(0.01 sec)
QueryOK,0rows affected(0.03sec)
mysql>callp1(1) $$
+ +
|concat(v_id,v_name)|
+ +
|1ravi |
+ +
1rowinset(0.00 sec)
QueryOK,0rows affected(0.01sec)
mysql>callp1(5) $$
+ +
|concat(v_id,v_name)|
+ +
|5kiran |
+ +
1rowinset(0.00 sec)
QueryOK,0rows affected(0.00sec)
mysql>call p1(6);$$
+ +
|concat(v_id,v_name)|
+ +
|6varun |
+ +
1rowinset(0.00 sec)
QueryOK,0rows affected(0.00sec)