formoredetailsvisit:

python4csip.com
WORKSHEET
SQL (CS)
1 What are DDL and DML ? Give one command of each.
2 Which command is used to add new column in existing table?
3 Which clause is used to search for NULL values in any column?
4 Which command is used to see information like name of columns,datatype,size etc.?
5 Which clause is used for pattern matching? What are the 2 main characters used for
matching the pattern?
6 Which clause is used to see the output of query in ascending or descending order?
7 Which clause is used to eliminate the duplicate rows from output?
8 What is the minimum number of column required in MySQL to create table?
9 Which command is used to remove the table from database?
10 Which command is used to add new record in table?
11 Which option of ORDER BY clause is used to arrange the output in descending order?
12 Which command is used to change the existing information of table?
13 Rajisa database programmer, He has to write the query from EMPLOYEE table to search for the
employee whose name begins from letter„R‟,for this he has written the query as: SELECT*
FROM EMPLOYEE WHERE NAME=‟R%‟;
But the query is not producing the correct output, help Raj and correct the query so that he
gets the desired output.
14 Raj is a database programmer, He has to write the query from EMPLOYEE table to search
for the employee who are not getting any commission, for this he has written the query as:
SELECT * FROM EMPLOYEE WHERE commission=null;
But the query is not producing the correct output, help Raj and correct the query so that he
gets the desired output.
15 Rajisa database programmer,has to write the query from EMPLOYEE table to search for the
employee who are working in „Sales‟ or „IT‟ department, for this he has written the query as:
SELECT*FROMEMPLOYEEWHEREdepartment=‟Sales‟or„IT‟;
But the query is not producing the correct output, help Raj and correct the query so that he
gets the desired output.

Page:1
formoredetailsvisit:
python4csip.com
16 Thefollowingqueryisproducinganerror.Identifytheerrorandalsowritethecorrectquery. SELECT
* FROM EMP ORDER BY NAME WHERE SALARY>=5000;
Ans. AsperMySQL,ORDERBYmustbethelastclauseinSQLQUERY,andinthisqueryORDER BY is used
before WHERE which is wrong, the correct query will be:
SELECT*FROMEMPWHERESALARY>=5000ORDERBYNAME;
17 IfTableSalescontains5recordsandRajexecutedthefollowingqueries;findouttheoutput of both the
query.
(i) Select100+200fromdual;
(ii) Select100+200fromSales;
Ans. (i) 300
(ii) 300
300
300
300
300
18 What isthedifferencebetween Equi-Joinand NaturalJoin?
Ans. InEquijoinwecomparevalueofanycolumnfromtwotablesanditwillreturnmatching rows.InEqui-
joincommoncolumnappearstwiceinoutputbecausewefetchusing(*)notby specifying column name.
for e.g.
InEqui-joinitisnotmandatorytohavesamenameforcolumntocompareofbothtable
Innaturaljoinalsothematchingrowswillreturn.Innaturaljoincolumnwillappearonly
once in output. Then name of column must be same in both table if we are performing
natural join using the clause NATURAL JOIN.
19 ObservethegivenTableTEACHERandgivetheoutputofquestion(i)and(ii)
TEACHER_CODE TEACHER_NAME DOJ
T001 ANAND 2001-01-30
T002 AMIT 2007-09-05
T003 ANKIT 2007-09-20
T004 BALBIR 2010-02-15
T005 JASBIR 2011-01-20
T006 KULBIR 2008-07-11
(i) SELECTTEACHER_NAME,DOJFROMTEACHERWHERETEACHER_NAMELIKE„%I%‟
(ii) SELECT*FROMTEACHERWHEREDOJLIKE„%-09-%‟;
Ans (i)
TEACHER_NAME DOJ
-------------------------------------------------------
AMIT 2007-09-05
ANKIT 2007-09-20
BALBIR 2010-02-15
JASBIR 2011-01-20
KULBIR 2008-07-11

(ii)
TEACHER_CODE TEACHER_NAME DOJ
----------------------------------------------------------------------
T002 AMIT 2007-09-05
T003 ANKIT 2007-09-20
20 WhichSQLfunctionisusedtogettheaveragevalueofanycolumn?
Ans. AVG()
21 WhatisthedifferencebetweenCOUNT()andCOUNT(*)function
Ans. COUNT()functionwillcountnumberofvaluesinanycolumnexcludingtheNULLs
COUNT(*)willcountnumberofrowsinqueryoutputincludingNULLs
22 Whatis thefullformofSQL?
Ans. StructuredQueryLanguage

Page:2
formoredetailsvisit:
python4csip.com
23 Querytodeleteallrecordoftablewithoutdeletingthetable:
a. DELETETABLETABLE_NAME
b. DELETEFROMTABLE_NAME
c. DROPTABLETABLE_NAME
d. DELETETABLEFROMTABLE_NAME
Ans. b.DELETEFROMTABLE_NAME
24 IdentifythewrongstatementaboutUPDATEcommand
a. If WHEREclauseismissingalltherecordintablewillbeupdated
b. OnlyonerecordcanbeupdatedatatimeusingWHEREclause
c. MultiplerecordscanbeupdatedatatimeusingWHEREclause
d. Noneoftheabove
Ans. b.OnlyonerecordcanbeupdatedatatimeusingWHEREclause
25 Identifythecorrectstatement(s)todropacolumnfromtable
a. DELETECOLUMNCOLUMN_NAME
b. DROPCOLUMNCOLUMN_NAME
c. ALTERTABLETABLE_NAMEDROPCOLUMNCOLUMN_NAME
d. ALTERTABLETABLE_NAMEDROPCOLUMN_NAME
Ans. c. ALTERTABLETABLE_NAMEDROPCOLUMNCOLUMN_NAME
d. ALTERTABLETABLE_NAMEDROPCOLUMN_NAME
26 Suppose a table BOOK contain columns (BNO, BNAME, AUTHOR, PUBLISHER), Raj is
assigned a task to see the list of publishers, when he executed the query as:
SELECTPUBLISHERFROMBOOK;
He noticed that the same publisher name is repeated in query output. What could be possible
solution to get publisher name uniquely? Rewrite the following query to fetch unique
publisher names from table.
Ans. SolutionistouseDISTINCTclause.
CorrectQuery:SELECTDISTINCTPUBLISHERFROMBOOK;
27 HOTS
Consider a database table T containing two columns X and Y each of type integer. After the
creation of the table, one record (X=1, Y=1) is inserted in the table.
Let MX and MY denote the respective maximum values of X and Y among all records in the
table at any point in time. Using MX and MY, new records are inserted in the table 128 times
with X and Y values being MX+1, 2*MY+1 respectively. It may be noted that each time
aftertheinsertion, values of MX andMYchange. Whatwillbetheoutput of thefollowing SQL
query after the steps mentioned above are carried out?
SELECTYFROMTWHEREX=7
A.127
B.255
C.129
D.257
Ans. A.127
28 WhichSQLfunctionisusedtofindthehighestandlowestvalueof numericanddatetype
column?
Ans. MAX()andMIN()
29 WhatisthedefaultorderofsortingusingORDERBY?
Ans. Ascending
30 WhatisthedifferencebetweenCHARandVARCHAR?
Ans. CHARisfixedlengthdatatype.Forexampleifthecolumn„name‟ifofCHAR(20)thenallname
willoccupy20bytesforeachnameirrespectiveofactualdata.
VARCHARisvariablelengthdatatypei.e.itwilloccupysizeaccordingtheactuallengthof data

Page:3
formoredetailsvisit:
python4csip.com
31 WriteSQLqueriesfor(i)to(iv)andfindoutputs forSQLqueries(v)to(viii)whicharebasedontables

TABLE:TRANSACT
TRNO ANO AMOUNT TYPE DOT
T001 101 2500 Withdraw 2017-12-21
T002 103 3000 Deposit 2017-06-01
T003 102 2000 Withdraw 2017-05-12
T004 103 1000 Deposit 2017-10-22
T005 102 12000 Deposit 2017-11-06

(i) TodisplaydetailsofalltransactionsofTYPEWithdrawfromTRANSACTtable
(ii) TodisplayANOandAMOUNTofallDepositandWithdrawalsdoneinmonthof
„May‟2017 fromtableTRANSACT
(iii) To display first dateof transaction (DOT) from tableTRANSACT for Account
havingANO as 102
(iv) To display ANO, ANAME, AMOUNT and DOT of those persons from ACCOUNT and
TRANSACT table who have done transaction less than or equal to 3000
(v) SELECTANO,ANAMEFROMACCOUNT
WHEREADDRESSNOTIN('CHENNAI','BANGALORE');
(vi) SELECTDISTINCTANOFROMTRANSACT
(vii) SELECTANO,COUNT(*),MIN(AMOUNT)FROMTRANSACT
GROUP BY ANO HAVING COUNT(*)> 1
(viii) SELECT COUNT(*), SUM(AMOUNT) FROM
TRANSACTWHERE DOT <= '2017-10-01'
Ans. (i) Select*fromTRANSACTwhereTYPE=’Withdraw’;
(ii) SelectANO,AMOUNTfromTRANSACTwhereDOTlike‘%-05-%’;
(iii) SelectMIN(DOT)fromTRANSACTwhereANO=102
(iv) SelectANO,T.ANO,ANAME,AMOUNTfromACCOUNTA,TRANSACTTwhereA.ANO=T.ANOand
AMOUNT<=3000;
(v)
ANO ANAME

103 AliReza
105 SimranKaur
(vi)
ANO

101
103
102
(vii)

(viii)

Page:4
formoredetailsvisit:
python4csip.com
32 ConsiderthefollowingtablesEMPandSALGRADE,writethequeryfor(i)to(vi)andoutput
for(vii)to(x)
TABLE:EMPLOYEE
ECODE NAME DESIG SGRADE DOJ DOB
101 Vikrant Executive S03 2003-03-23 1980-01-13
102 Ravi Head-IT S02 2010-02-12 1987-07-22
103 JohnCena Receptionist S03 2009-06-24 1983-02-24
105 AzharAnsari GM S02 2009-08-11 1984-03-03
108 PriyamSen CEO S01 2004-12-29 1982-01-19
TABLE:SALGRADE
SGRADE SALARY HRA
S01 56000 18000
S02 32000 12000
S03 24000 8000
(i) TodisplaydetailsofallemployeeindescendingorderoftheirDOJ
(ii) TodisplayNAMEANDDESIGofthoseemployeeswhosesgradeiseither„S02‟or
„S03‟
(iii) TodisplayNAME,DESIG,SGRADEofthoseemployeewhojoinedintheyear2009
(iv) To display all SGRADE, ANNUAL_SALARY from table SALGRADE [where
ANNUAL_SALARY=SALARY*12]
(v) TodisplaynumberofemployeeworkingineachSALGRADEfromtableEMPLOYEE
(vi) TodisplayNAME,DESIG,SALARY,HRAfromtablesEMPLOYEEandSALGRADE
whereSALARYislessthan50000
(vii) SelectMIN(DOJ),MAX(DOB)fromemployee;
(viii) SelectSGrade,Salary+HRAfromSalGradewhereSgrade=‟S02‟
(ix) Selectcount(distinctsgrade)fromemployee
(x) Selectsum(salary),avg(salary)fromsalgrade
Ans (i) SELECT*FROMEMPLOYEEORDERBYDOJDESC
(ii) SELECTNAME,DESIGFROMEMPLOYEEWHERESGRADEIN('S02','S03')
OR
SELECTNAME,DESIGFROMEMPLOYEEWHERESGRADE='S02'OR
SGRADE='S03'
(iii) SELECTNAME,DESIG,SGRADEFROMEMPLOYEEWHEREDOJLIKE'2009%'
(iv) SELECTSGRADE,SALARY*12ANNUAL_SALARYFROMSALGRADE
(v) SELECTSGRADE,COUNT(*)FROMEMPLOYEEGROUPBYSGRADE
(vi) SELECTNAME,DESIG,SALARY,HRA FROMEMPLOYEEE,SALGRADES WHERE
E.SGRADE=S.SGRADEANDSALARY<=50000
(vii) MIN(DOJ) MAX(DOB)

2003-03-23 1987-07-22
(viii) SGRADESALARY+HRA

S02 44000
(ix) COUNT(*)

3
(x) SUM(SALARY) AVG(SALARY)

112000 37333.33

Page:5
formoredetailsvisit:
python4csip.com
33

(i) TodisplaydetailsofallTrainswhichstartsfromNewDelhi
(ii) To display PNR, PNAME, GENDER and AGE of all passengers whose AGE is below
50
(iii) TodisplaytotalnumbersofMALEandFEMALEpassengers
(iv) TodisplayrecordsofallpassengerstravellingintrainswhoseTNOis12015
(v) SELECT MAX(TRAVELDATE),MIN(TRAVELDATE) FROM PASSENGERS WHERE
GENDER=‟FEMALE‟;
(vi) SELECTEND,COUNT(*)FROMTRAINSGROUPBYENDHAVINGCOUNT(*)>1;
(vii) SELECTDISTINCTTRAVELDATEFROMPASSENGERS;
(viii) SELECT TNAME, PNAME FROM TRAINS T, PASSENGERS P WHERE T.TNO=P.TNO
AND AGE BETWEEN 50 AND 60

Page:6
formoredetailsvisit:
python4csip.com
Ans (i) SELECT*FROMTRAINSWHERESTART='NEWDELHI'
(ii) SELECTPNR,PNAME,GENDER,AGEFROMPASSENGERWHEREAGE<50
(iii) SELECTGENDER,COUNT(*)FROMPASSENGERSGROUPBYGENDER
(iv) SELECT*FROMPASSENGERSWHERETNO=12015
(v) MAX(TRAVELDATE) MIN(TRAVELDATE)

2018-11-10 2018-05-09
(vi) END COUNT(*)

HABIBGANJ 2
AMRITSARJUNCTION 2
NEWDELHI 4
(vii) TRAVELDATE

2018-12-25
2018-11-10
2018-10-12
2018-05-09
(viii) TNAME PNAME

AJMERSHATABDI PTIWARY
AJMERSHATABDI S TIWARY
AMRITSARMAIL RNAGRAWAL
AMRITSAR MAIL N S SINGH
SWARNA SHATABDI S K SAXENA
SWARNA SHATABDI S SAXENA
SWARNASHATABDI J K SHARMA
SWARNASHATABDI RSHARMA
34 ConsiderthetableSHOPPEandACCESSORIES,writethequeryfor(i)to(v)andoutputfor
(vi)to(x)

Page:7
formoredetailsvisit:
python4csip.com

(i) TodisplayNameandPriceofalltheAccessoriesindescendingorderoftheirPrice
(ii) TodisplayIdandSnameofalltheShoppelocationin„NehruPlace‟
(iii) TodisplayName,MinimumandMaximumPriceofeachName fromACCESSORIES
table
(iv) TodisplayName,PriceofallAccessoriesandtheirrespectiveSNamefromtableSHOPPE
andACCESSORIESwherePriceis5000ormore.
(v) Todisplayalldetailsofaccessorieswherenamecontainsword„Board‟;
(vi) SELECTDISTINCTNAMEFROMACCESSORIESWHEREPRICE>5000;
(vii) SELECTAREA,COUNT(*)FROMSHOPPEGROUPBYAREA;
(viii) SELECTAVG(PRICE),MAX(PRICE)FROMACCESSORIESWHEREPRICE>=10000;
(ix) SELECTNAME,PRICE*.05DISCOUNTFROMACCESSORIESWHEREIDIN(„S02‟,‟S03‟)
(x) SELECT*FROMSHOPPES,ACCESSORIESAWHERES.ID=A.IDANDPRICE>=10000;
Ans (i) SELECTNAME,PRICEFROMACCESSORIESORDERBYPRICEDESC
(ii) SELECTID,SNAMEFROMSHOPPEWHEREAREA='NEHRUPLACE'
(iii) SELECTNAME,MIN(PRICE),MAX(PRICE)FROMACCESSORIESGROUPBYNAME
(iv) SELECT NAME,PRICE,SNAME FROM SHOPPE S, ACCESSORIES A WHERE
S.ID=A.ID AND PRICE>=5000
(v) SELECT*FROMACCESSORIESWHERENAMELIKE„%BOARD%‟
(vi) NAME

MotherBoard
LCD
(vii) AREA COUNT(*)

CP 2
GKII 1
NehruPlace 2
(viii) AVG(PRICE) MAX(PRICE)

12500 13000
(ix) NAME DISCOUNT

Keyboard 25
MotherBoard 650
Keyboard 20
HardDisk 225
(x) ID SNAME AREA NO NAME PRICE ID

S01 ABC Computronics CP A01 Mother board 12000 S01

S02AllInfotechmedia GKII A05 Motherboard 13000 S02

Page:8
formoredetailsvisit:
python4csip.com

35 a) Inadatabasetherearetwotables:WriteMYSQLqueriesfor(i)to(iii)
Table:Item
ICode IName Price Color VCode
S001 MobilePhones 30000 Silver P01
S002 Refrigerator 20000 Cherry P02
S003 TV 45000 Black P03
S004 Washing Machine 12000 White P04
S005 Air Conditioner 50000 White P05
Table : Vendor
VCode VName
P01 Rahul
P02 Mukesh
P03 Rohan
P04 Kapil
(i) TodisplayICode,INameandVNameofallthevendors,whomanufacture“Refrigerator”.
(ii) TodisplayIName,ICode,VNameandpriceofalltheproductswhoseprice>=23000
(iii) TodisplayVnameandINamemanufacturedbyvendorwhosecodeis“P04”.
Ans (i) SelectICode,IName,VNamefromItemI,VendorVwhereI.Vcode=V.VCodeand
IName='Refrigerator'
(ii) Select IName, ICode,VName from Item I,Vendor Vwhere I.Vcode=V.VCode and
Price>=23000
(iii) SelectVName,INamefromItemI,VendorVwhereI.Vcode=V.VCodeandI.VCode='P
04'
b) Whatwillbetheoutputofthefollowing-
1. SelectRound(1449.58,-2);
2. SelectRound(7.5789,3); IPONLY
3. SelectSubstr(“HelloRahul”,3,8);
4. SelectDayofmonth(“2020-10-24”);
And 1.1400
2.7.579
3. ellohRah
4. 24
36 In a database there are two tables : Write MYSQL queries for (i) to (vi)
Table : Doctors
DocID DocName Department NoofOpdDays
101 JKMishra Ortho 3
102 Maheshtripathi ENT 4
103 RaviKumar Neuro 5
104 MukeshJain Physio 3
Table: Patients
PatNo PatName Department DocId
1 Payal ENT 102
2 Naveen Ortho 101
3 Rakesh Neuro 103
4 Atul Physio 104
(i) TodisplayPatNo,PatNameandcorrespondingDocNameforeachpatient.
(ii) TodisplaythelistofalldoctorswhoseNoofOpdDaysaremorethan3
(iii) To display DocName, Department,PatName and DocId from both the tables where DocID
is either 101 or 103
(iv) To displaytotalnoof differentdepartmentsfrom Patientstable.

Page:9
formoredetailsvisit:
python4csip.com
Ans. (i) selectPatNo,PatName,DocNamefromDoctorsD,PatientsPwhereD.DocID=
P.DocID
(ii) select*fromDoctorswhereNoofOpdDays>3
(iii) SelectDocID,DocName,Department,PatNamefromDoctorD,PatientPwhere D.DocId =
P.DocId and DocId in (101,103)
(iv) selectcount(distinctDepartment)fromPatient
37 Given the Table “BANK” with records, Give the output of given queries–
NAME
SACHIN
RAMESH
DINESH
VIKAASH
RAJU
AMRITESH
i. Select*fromBANKwhereNameLike„%ES%‟;
ii. Select*fromBANKwhereNameLike„____________SH‟
Ans i. RAMESH
DINESH
AMRITESH
ii. RAMESH
DINESH
38 RajeshadatabasedeveloperatStoreIndiawantstosearchtherecordofthoseemployees
whosenamestartsfrom„R‟andtheyhavenotallottedanyproject,forthishehaswritten the following
query-
Select*fromEmployeewhereName=‘R%’andProject=Null;
Butthequeryisnotproducingthecorrectoutput.Rewritethequeryaftercorrectingthe errors

Ans Select*fromEmployeewhereNamelike„R%‟andProjectisnull
39 ConsideringtheVisitortabledata,writethequeryfor(i)to(iv)andoutputfor(v)to(viii)

VisitorID VisitorName Gender ComingFrom AmountPaid

1 Suman F Kanpur 2500
2 Indu F Lucknow 3000
3 Rachana F Haryana 2000
4 Vikram M Kanpur 4000
5 Rajesh M Kanpur 3000
6 Suresh M Allahabad 3600
7 Dinesh M Lucknow
8 Shikha F Varanasi 5000
(i) WriteaquerytodisplayVisitorName,ComingFromdetailsofFemaleVisitorswith Amount
Paid more than 3000
(ii) Writeaquerytodisplayallcomingfromlocationuniquely
(iii) Write a query to insert the following values-
7, „Shilpa‟,‟F‟,‟Lucknow‟,3000
(iv) WriteaquerytodisplayalldetailsofvisitorsinorderoftheirAmountPaidfrom highest to
lowest
(v) SelectVisitorNamefromVisitorwhereGender=‟M‟;
(vi) SelectAmountPaid+200fromVisitorwhereVisitorID=6;
(vii) SelectSum(AmountPaid)fromVisitorwherecomingFrom=‟Kanpur‟;
(viii) SelectCount(VisitorName)fromVisitorwhereAmountPaidisNULL;

Page:10
formoredetailsvisit:
python4csip.com
Ans. (i) Select VisitorName,ComingFrom from Visitor where Gender='F' and
AmountPaid>3000
(ii) SelectdistinctComingFromfromVisitor
(iii) insertintovisitorvalues(7,'Shilpa','F','Lucknow',3000)
(iv) Select*fromvisitororderbyAmountPaiddesc
(v) VisitorName

Vikram
Rajesh
Suresh
Dinesh
(vi) AmountPaid+200

3800
(vii) Sum(AmountPaid)

9500
(viii) Count(VisitorName)

1
40 WriteaMySQLquerytocreatethegiventable(MEMBER)
Columnname Datatype Size
ID Char 6
Name Varchar 30
Fee Int 10
DOJ Date
Ans. createtablemember(idchar(6),namevarchar(30),feeint(10),dojdate)
41 WhatistheDifferencebetweenALTERTablecommandandUPDATEcommand?
Ans. ALTER is DDL command and is used for modifying the schema of table like adding new
column, modifying column definition, dropping column. UPDATE is DML command and
isusedformodifyingtheexistingdataoftablelikechangingthemobilenumber,changingthe
salaryetc.
42 (i) Sanjaywasdeletingtherecordofempno=1234,butatthetimeofexecutionofcommandhe
forgottoaddconditionempno=1234,whatwillbetheeffectofdeletecommandinthiscase?
(ii) Sameerisexecutingthequerytofetchtherecordsofemployeewhoaregettingsalary between
4000 to 8000, he executed the query as -
Select * from employee where salary between 4000 to 8000;
Butheisnotgettingthecorrectoutput,Rewritethecorrectquery.
Ans. (i) Ifwhere clauseismissingwithDELETEthenitwilldeletealltherecordoftable.
(ii) Select*fromemployeewheresalarybetween40000and80000
43 WriteMYSQL command toseethelistoftables in currentdatabase
Ans. Showtables
44 Sunil decides to delete a PhoneNo column from a MySQL Table (student) after insert the data
into the table. Write the command to delete that particular column in student table.
Ans. ALTERTABLEstudentdropPhoneNo
45 AtableEmployeecontains5Rowsand4ColumnsandanothertablePROJECTcontains5
Rowsand3Columns.HowmanyrowsandcolumnswillbethereifweobtainCartesian
productofthesetwotables?
Ans. Rows=5x5=25
Columns=4+3=7
46 Ranjeetcreatedatablenamedstudent,Hewantstoseethosestudentswhosenameending with p. He
wrote a query- SELECT * FROM student WHERE name=”p%”;
Butthequeryisnotproducingthedesiredoutput,HelpRanjeettorunthequerybyremoving the errors
from the query and rewriting it.

Page:11
formoredetailsvisit:
python4csip.com
Ans SELECT*FROMstudentWHEREnameLIE”p%”;
47 ConsiderthefollowingEMPLOYEEtablewriteMYSQLcommandfor(i)to(iv)andOutputsfor
(v)to(viii)
EMPNO ENAME DEPT SALARY COMM
1 ANKIT HR 20000 1200
2 SUJEET ACCOUNTS 24000
3 VIJAY HR 28000 2000
4 NITIN SALES 18000 3000
5 VIKRAM SALES 22000 1700

(i) Todisplaythenameofemployeesstartingfrom„V‟inascendingorderoftheirsalary
(ii) TodisplaythedetailsofallSALESdeptemployeewhoareearningsalarymorethan 20000
(iii) Tocountdistinctdepartmentfromthetable
(iv) ChangethesalaryofNITINfrom18000to20000
(v) ToinsertanewrowinthetableEmployee
„6‟,„SUMIT‟,‟HR‟,40000,2000
(vi) SelectAVG(COMM)fromEmployee
(vii) SelectENAME,DEPTfromEmployeewhereDeptin(„HR‟,‟ACCOUNTS‟)
(viii) SelectENAME,SALARY+100NEWSALfromEmployee

Ans. (i) selectenamefromemployeewhereenamelike'V%'orderbysalary;

(ii) Select*fromemployeewheredept='Sales'andsalary>20000;
(iii) selectcount(distinctdept)fromemployee;
(iv) updateemployeesetsalary=20000whereename='NITIN';
(v) insertintoemployeevalues(6,'SUMIT','HR',40000,2000)
(vi) 1980 (includingrecordinsertedin(v))
(vii) ENAME DEPT

ANKIT HRSUJEET
ACCOUNTS
VIJAY HR
(viii) ENAME NEWSAL

ANKIT 20100
SUJEET 24100
VIJAY 28100
NITIN 20100
VIKRAM 22100
SUMIT 40100
48 WriteMYSQLcommandtocreatethe tableENQUIRYincludingits constraints
Table: ENQUIRY
Nameofcolumn Type Size Constraints
visitorID Decimal 4 Primarykey
visitorName Varchar 20
visitorMobile Char 10 Notnull
visitorAddress Varchar 40
Ans. createtableENQUIRY(visitorIDdecimal(4)primarykey,visitorNamevarchar(20)visitorMobile
char(10)notnull,visitorAddressvarchar(40))
49 Inadatabase thereare twotables:
Table: Doctor
DocID DocName Specialist
D001 VimalJha Cardio
D002 SunilBawra Ortho
D003 MukulBarman Surgeon
D004 NiteshSolanki Skin

Page:12
formoredetailsvisit:
python4csip.com

Table: Patient
PatID PatName DateAdm DocID
P001 Kapil 2013-10-10 D002
P002 Susheel 2013-09-01 D001
P003 Wasim 2013-10-15 D002
P004 Sanjay 2013-10-12 D003
P005 Jai 2013-10-17 D003

WritetheMySQLqueriesforthefollowing:
(i) TodisplayPatID,PatName,andcorrespondingDocNameof„Cardio‟and„Ortho‟ patient
(ii) TodisplayDocName,PatNameofthosepatientwhoareadmittedbefore15-Oct- 2013

Ans. (i) select PatID, PatName,DocName from Doctor D, Patient P where D.DocID = P.DocID
and specialist in ('Cardio','Orto');
(ii) selectDocName,PatNamefromDoctorD,patientPwhereD.DocID=P.DocIDand
DateAdm<'2013-10-15'
50 WhatwillbeoutputoffollowingMysqlQueries–
(i) SelectRound(55.698,2)
(ii)
(iii)
Selectmid(„examination‟,4,4)
SelectRound(4562.778,-2) IPONLY
(iv) Selectlength(trim(„exam„))

Ans. (i) 55.70

(ii) mina
(iii) 4600
(iv) 4
51 1.WriteQueryforthefollowingrequirements– (STUDENT)
Id NAME STIPEND SUBJECT AVERAGE DIV
1 KARAN 400 PHYSICS 68 1
2 DIVAKAR 450 COMPSC 68 1
3 DIVYA 300 CHEMISTRY 62 2
4 ARUN 350 PHYSICS 63 1
5 SABINA 500 MATHS 70 1
6 JOHN 400 CHEMISTRY 55 2
7 ROBERT 250 PHYSICS 64 1
8 RUBINA 450 MATHS NULL NULL
9 VIKAS 500 COMPSC 62 1
10 MOHAN 300 MATHS 57 2
GUIDE
SUBJECT ADVISOR
PHYSICS ALOK
COMPSC RAJAN
CHEMISTRY MANJU
MATHS SMITA
HISTORY KISHORE
1. TODISPLAYTHENAMEOFSTUDENT,SUBJECTANDADVISORNAME
2. TODISPLAYTHESTUDENTNAMEANDADVISORALLTHESTUDENTSWHOAREOFFERING
EITHER PHYSICS OR CHEMISTRY

Page:13
formoredetailsvisit:
python4csip.com
Ans. 1. SelectName,Subject,AdvisorfromStudentS,GuideGwhereS.subject=G.subject;
2. SelectName,AdvisorfromStudentS,GuideGwhereS.subject=G.subjectand S.subject in
('Physics','Chemistry')
52 DIFFERENCEBETWEEN
1. HAVINGANDWHERE
2. %AND_
3. CHARANDVARCHAR
Ans. (1)
HAVING–thisclauseisusedwithGROUPBYtofilterthegroupofrecords.Wecanuse aggregate functions
with HAVING.
WHERE–thisclauseisusedtoapplyconditiononalltherowsoftable.Wecannotuse aggregate functions
with WHERE.

(2) %isawildcardcharacterusedwithLIKEanditisusedforsubstitutingmultiple characters while

matching the pattern. Matching text can be of any length
_(underscore)isalsoawildcardcharacterusedwithLIKEbutitsubstituteonlysingle character at
given position while matching the pattern. Length will be fixed.

(3) RefertoAnswerno.30
53 OUTPUT–
a. SelectSubstring(„mysqlapplication‟,3,3)

b. Selectinstr(„mysqlapplication‟,‟p‟);
c. Selectround(7756.452,1); IPONLY
d. Selectround(59999.99,-2);
e. Selectright(„mysqlapplication‟,3);
Ans. a. sql
b. 8
c. 7756.5
d. 60000
e. Ion

Page:14
formoredetailsvisit:
python4csip.com

Page:15