6/23/2015
AdvancedSQLInterviewQuestionsandAnswers
Translate:
SelectLanguage
Search
Home
AdvancedSQLInterviewQuestionsandAnswers
Java
HerearesomecomplexSQLinterviewproblemsthatareforpeoplewhoarelookingfor
moreadvancedandchallengingquestions,alongwiththeanswersandcomplete
C/C++
Databases/SQL
SQLInterviewQuestions
[Link]
answers.
Supposewehave2tablescalledOrdersandSalespersonshownbelow:
Salesperson
[Link]
Orders
SQLKeyDefinition
ID Name Age Salary
Number order_date cust_id salesperson_id Amount
DifferencesbetweenPrimaryand
ForeignKeys
Abe
61
140000
10
8/2/96
540
Bob
34
44000
20
1/30/99
1800
SecondaryKey
Chris
34
40000
30
7/14/95
460
SimplekeyinSQL
Dan
41
52000
40
1/29/98
2400
SuperkeyExample
Ken
57
115000
50
2/3/98
600
WhatisReferentialIntegrity
11 Joe
38
38000
60
3/2/98
720
70
5/6/98
150
NaturalKeyInDatabase
[Link]
Howdodatabaseindexeswork?
Whatisaselfjoin?
NowsupposethatwewanttowriteSQLthatmustconformtotheSQLstandard.
ExampleofDISTINCTinSQL
Retrieveuniquerowswithout
DISTINCT
Wewanttoretrievethenamesofallsalespeoplethathavemorethan1order
[Link].
PracticeInterviewQuestion1
PracticeInterviewQuestion1
continued
PracticeInterviewQuestion1
continued
PracticeInterviewQuestion2
AdvancedSQLInterviewQuestions
andAnswers
AdvancedSQLInterviewQuestions
andAnswersPart2
Ifthatisthecase,thenwhat(ifanything)iswrongwiththefollowingSQL?:
SELECTName
FROMOrders,Salesperson
WHEREOrders.salesperson_id=[Link]
GROUPBYsalesperson_id
HAVINGCOUNT(salesperson_id)>1
PracticeInterviewQuestion2
continued
TheanswerandexplanationtoadvancedSQLquestion1
[Link]
ThereisdefinitelysomethingwrongwiththeSQLabove,anditisprobablysomethingthat
[Link]
saysthatwecannotselectacolumnthatisnotpartofthegroupbyclauseunlessitis
[Link]
Server,wewouldgetanerrorthatlookslikethis:
Ternary/ThreevaluedLogicinSQL
FindMaximumValueWithoutUsing
Aggregate
SQLInjectionExampleandTutorial
SQLInjectionPrevention
BlindSQLInjectionExample
ParameterizedQueriesvsPrepared
Statements
PreparedStatementExample
Differencebetweenafulljoinandan
innerjoin?
Differencebetweenaleftouterjoin
andrightouterjoin?
Column'Name'isinvalidintheselectlistbecauseitis
notcontainedineitheranaggregatefunctionor
theGROUPBYclause.
Youmightbeconfusednow,soletsexplainwhatthaterrormeansinplainEnglishand
[Link]
discussionisunderstandingexactlywhywegetthaterror,[Link]
isagoodreasonfortheerrorreadontounderstandwhy.
Differencebetweenaleftjoinanda
leftouterjoin?
YoucanseeinthebadSQLabovethattheNamecolumnisclearlynotalsoapartofthe
groupbystatement,norisitcontainedwithinanaggregatefunction(likeSUM,MAX,etc).
SQL:HavingvsGroupBy
Astheerrorabovesuggests,wecanfixtheerrorbyeitherwrappingtheNamecolumn
insideanaggregatefunctionoraddingittotheGroupByclause.
SQL:GroupBywithmultiplecolumns
SQLSelectDistinctandOrderBy
SQLOrderBydefaultsortorder
SoifwewanttowriteSQLthatcomplieswiththestandard,thenwecouldwrite
somethinglikethisbyaddingtheNamecolumntotheGroupBy:
Derivedtablevssubquery
[Link]
1/5
6/23/2015
AdvancedSQLInterviewQuestionsandAnswers
CorrelatedvsUncorrelatedSubquery
FindnthhighestsalarySQL
CardinalityinSQL
SelectivityinSQLDatabases
CardinalityversusSelectivity
SELECTName
FROMOrders,Salesperson
WHEREOrders.salesperson_id=[Link]
GROUPBYsalesperson_id,Name
weaddedthenamecolumntothegroupby,andnowitworks!
HAVINGCOUNT(salesperson_id)>1
[Link]
Pageversusblock
TheSQLabovewillrunjustfinewithoutgivinganyerror.
DatabaseLocking
LockEscalation
WecouldalsofixtheproblembyputtingtheNamecolumninanyaggregatefunction,and
[Link],wecouldjustwritethisSQL
DatabaseDeadlockExample
instead,[Link]
Whatisadatabasetransaction?
theMAXaggregatefunction,butanyotheraggregatewouldworkjustfineaswell:
SQLServerTransaction
OracleTransaction
MySQLTransaction
DB2Transaction
ConcurrentUpdateProblem
SELECTMAX(Name)putnameinanaggregatefunction
FROMOrders,Salesperson
WHEREOrders.salesperson_id=[Link]
GROUPBYsalesperson_id
HAVINGCOUNT(salesperson_id)>1
HowtoTuneDatabasePerformance
DatabaseFullTableScan
Whatiscostbasedoptimization?
HowtotuneSQLqueries
SQLIndexPerformance
AddingtheNamecolumntothegroupby,orwrappingtheNamecolumninanaggregate
willcertainlyfixtheerrorbutitsveryimportanttonotethatbothofthosethingswill
changethedatathatisreturnedtoastatethatyoumaynotwant.
Whydoestheselectedcolumnhavetobeinthegroupby
clauseorpartofanaggregatefunction?
Whatisabitmapindex?
OracleIndexesExamples
[Link]
privileges
SQLGrant
So,nowyouunderstandhowtofixtheerrorbutdoyouunderstandwhyitisa
probleminthefirstplace?Well,youshouldbecausethatisthemostimportant
thingtounderstand!So,letsexplainsomemoreaboutwhySQLgivesthaterror
shownabove.
SQLRevoke
Firstoff,[Link]
[Link],
SQLCreateUser
aggregatefunctionsareusedtoperformamathematicalfunctiononthevaluesinsidea
DatabaseRoles
givencolumn,[Link]
commonlyusedaggregatefunctions:
SQLCASEStatement
SQLSearchedCASEStatement
SQLInlineView
RANK()versusDENSE_RANK()
Javascript
PHP
AVG()Returnstheaveragevalue
COUNT()Returnsthenumberofrows
FIRST()Returnsthefirstvalue
LAST()Returnsthelastvalue
MAX()Returnsthelargestvalue
MIN()Returnsthesmallestvalue
SUM()Returnsthesum
DataStructures
DesignPatternQuestions
ExcelInterviewQuestions
ToillustratewhytheSQLstandardsaysthataselectedcolumnhastobeinthegroupby
clauseorpartofanaggregatefunction,[Link]
sometablescalledStarbucks_StoresandStarbucks_Employees.Incaseyoudontalready
know,Starbucksisapopularcoffeeshop/cafeintheUSA:
HTML5
Networking
OperatingSystems
Recursion
Starbucks_Employees
Starbucks_Stores
ID Name Age HourlyRate StoreID
store_id city
Abe
61
14
10
10
SanFrancisco
Bob
34
10
30
20
LosAngeles
Chris
34
40
30
SanFrancisco
ApacheInterviewQuestions
Dan
41
11
50
40
LosAngeles
General/Miscellaneous
Ken
57
11
60
50
SanFrancisco
11 Joe
38
13
70
60
NewYork
70
SanFrancisco
NonTechnicalQuestions
InterviewinginIndia
Now,giventhetablesaboveletssaythatwewritesomeSQLlikethis:
WorkingAsaSoftwareEngineer
FinancialAnalystQuestions
SELECTcount(*)asnum_employees,HourlyRate
FROMStarbucks_EmployeesJOINStarbucks_Stores
[Link]
2/5
6/23/2015
AdvancedSQLInterviewQuestionsandAnswers
JobAdviceForProgrammers
ONStarbucks_Employees.StoreID=Starbucks_Stores.store_id
GROUPBYcity
Puzzles
AssortmentofKnowledge
AmericanVocabulary
TechnicalVocabulary
ScienceQuestions
ItlooksliketheSQLabovewouldjustreturnthenumberofStarbucksemployeesineach
city,alongwiththeHourlyRatebecauseitwillgrouptheemployeesbasedonwhatever
citytheyworkin(thankstothegroupbycitystatement).
Subscribetoournewsletterformorefreeinterviewquestions.
Theproblemwithselectinganonaggregatecolumnthatisnot
inthegroupby
ButtherealquestionhereiswhatexactlywouldbereturnedfortheHourlyRateinthe
SQLabove?Woulditreturneveryemployeeshourlyrateseparatedbycommas?Sincewe
groupbycity,willitreturnthehighesthourlyrateforeachcity?Willitreturnthehourly
rateasadistinctlist,sothose2guysmaking11dollarsanhourwillhavethe11returned
onlyonce?
Email
Theproblemhereisthatwedonotknowwhatwillbereturnedbecausewearenot
Country
UnitedStates
specificenoughwithwhatweareaskingforintheSQL!Ifwhatweareaskingfor
isnotspecificenough,thentheSQLprocessorwillnotknowwhattoreturn.
ThisiswhyalmostalldatabaseimplementationsreturnanerrorwhentheSQLaboveis
run(withthenotableexceptionofMySQL)andthisiswhytheSQLdoesnotconformto
[Link]
showedearlier.
[Link]
ProgrammerInterview
ofoperationsinwhichthingswillhappenwiththeSQLaboveis:
Like 23,854
1.The2tablesarejoinedontheconditionthatthe
Starbucks_Employees.StoreIDcolumnvalueisequaltothe
Starbucks_Stores.store_idcolumnvalues.
[Link]
eachdistinctcitywillhaveit'sown"group".So,therewill
beatotalof3groupsoneeachforSanFrancisco,NewYork,
andLosAngeles.
[Link]
thatiscreatedinstep2.
Becauseweendupwithdifferentgroupsbasedonthecity,whenweselectacount(*),
[Link],theproblemisthat
whenweselectHourlyRate,therewillbemultiplevaluesfortheHourlyRatewithineach
[Link],forthegroupcreatedbythecityofSanFranciscotherewillbe4
differentvaluesfortheHourlyRate14,10,11,and13.
SothequestioniswhichvalueoftheHourlyRateshouldbeselectedfromeachgroup?
Well,itcouldbeanyoneofthosevalueswhichiswhythatSQLresultsinanerror.
ThisisbecausewhatweareaskingforisNOTspecificenoughhopefullythisiscrystal
clearnowtoyou.
IfthesameHourlyRatewerepartofanaggregatefunctionlikeMAXthenitwouldsimply
[Link]
functionwouldfixtheSQLerrorbecauseonlyonevaluewillbeselectedfromanygiven
group.
So,thisSQLisperfectlyfinebecausewearemorespecificinwhatweaskforbutthis
SQLwouldonlyworkforyouifyouactuallywantthehighestHourlyRateforeachcity:
SELECTcount(*)asnum_employees,MAX(HourlyRate)
FROMStarbucks_EmployeesJOINStarbucks_Stores
ONStarbucks_Employees.StoreID=Starbucks_Stores.store_id
GROUPBYcity
Fixtheerrorbyaddingcolumntothegroupclause
AnotherwaytofixtheerroristosimplyaddtheHourlyRatecolumntothegroupby
[Link]
[Link]:
[Link]
3/5
6/23/2015
AdvancedSQLInterviewQuestionsandAnswers
SELECTcount(*)asnum_employees,HourlyRate
FROMStarbucks_EmployeesJOINStarbucks_Stores
ONStarbucks_Employees.StoreID=Starbucks_Stores.store_id
GROUPBYcity,HourlyRate
Thiswouldthencreategroupsbasedontheuniquecombinationofthevaluesinthe
[Link]
HourlyRateandCitycombinationso$11,SanFranciscoand$11,LosAngeleswillbe2
[Link]:Group
ByWithMultipleColumns
WiththeSQLabove,eachgroupwillonlyhaveonevaluefortheHourlyRate,whichalso
meansthattherewillbenoambiguityorconfusionwhenselectingtheHourlyRatesince
[Link]
HourlyRatevaluecanbereturnedforeachgroup.
Addingthecolumntothegroupbyclausefixestheerrorbut
willalterthedatathatisreturned
But,oneveryimportantthingtonoteisthateventhoughaddingthecolumntothe
groupbywillfixtheerror,[Link]
[Link],the
count(*)functionwillnolongerreturnthecountofemployeesinagivencity,andwill
insteadreturnthenumberofrowsineachgroupcreatedbytheuniquecombinationof
theHourlyRateandcitycolumns.
MySQLselectingnonaggregatecolumnsnotinthegroupby
OneveryimportantthingthatyoushouldknowisthatMySQLactuallyallowsyouto
havenonaggregatedcolumnsintheselectlisteveniftheyarenotapartofthegroupby
clause(aquicksidenote:anonaggregatedcolumnissimplyacolumnthatisnot
wrappedwithinanaggregatefunction).Whatthismeansisthatyouwillnotreceivean
[Link]
MySQLisbecauseMySQLassumesthatyouknowwhatyouaredoinganditdoes
[Link],letsreferbacktotheSQLthatwe
startedwith:
SELECTName
FROMOrders,Salesperson
WHEREOrders.salesperson_id=[Link]
GROUPBYsalesperson_id
HAVINGCOUNT(salesperson_id)>1
ThereasontheoriginalSQLcode(presentedabove)worksjustfineinMySQLisbecause
thereisa1to1mappingofsalespersonnametoIDmeaningthatforeveryunique
[Link]
[Link](whichisdoneinthe
GROUPBYsalesperson_id)basedonthesalespersonID,eachgroupwillonlyhaveone
andonlyonename.
ThisSQLwillalsorunjustfineinMySQLwithoutreturninganerror:
SELECTcount(*)asnum_employees,HourlyRate
FROMStarbucks_EmployeesJOINStarbucks_Stores
ONStarbucks_Employees.StoreID=Starbucks_Stores.store_id
GROUPBYcity
But,eventhoughthecodeabovewillnotreturnanerror,theHourlyRatethatisreturned
byMySQLwillbesomearbitrary(random)[Link]
wecreateeachgroupbasedonthecity,eachgroupcanhavedifferentvaluesforthe
HourlyRate.
Inotherwords,thereisnoonetoonemappingbetweentheHourlyRateandthecitylike
[Link],becausewearenotbeing
specificastowhichHourlyRatewewant,[Link]
instance,inthegroupcreatedbythecityofSanFrancisco,MySQLcouldreturnthe
HourlyRateforanyemployeewhoworksinSanFranciscowhetheritis14,10,11,or
13wedontreallyknowsinceitisarbitrary/randominMySQL.
[Link]
[Link]
4/5
6/23/2015
AdvancedSQLInterviewQuestionsandAnswers
checkoutthenextquestionthatsapartofouradvancedSQLinterviewquestionslist.
Hiring?JobHunting?PostaJOBoryourRESUMEonourJOBBOARD>>
Follow@programmerintvw
+7 Recommend this on Google
FOLLOWVaroonSahgal,AuthorofProgrammerInterview on
Previous...
Next...
[Link]?Thenwhynottellafriendaboutus,orsimplyaddalink
tothispagefromyourwebpageusingtheHTMLbelow.
Linktothispage:
<ahref="[Link]
Pleasebookmarkwithsocialmedia,yourvotesarenoticedandappreciated:
Like 23,854peoplelikethis.
Copyright2015|ProgrammerJobBoard|IndiaJobBoardforProgrammers|About
[Link]
[Link]
5/5
