Aggregates inside nested queries Remember SQL is compositional 2 Hint 1: Break down query description to steps (subproblems) 3 Hint 2: Whenever in doubt
Previous PDF | Next PDF |
[PDF] Advanced SQL Database Programmers Handbook
31 déc 2020 · internals of Oracle programming problems and presents challenging and complex time-based SQL queries, managing set operations in SQL, and The most common examples are timestamps, user identifiers, and other
[PDF] Chapter 8 Advanced SQL
In this chapter, the student will learn: ▫ How to use the advanced SQL JOIN operator syntax ▫ About the different types of subqueries and correlated queries
[PDF] SQL & Advanced SQL - CERN Indico
5 mai 2012 · Explain several selected advanced SQL features ➢ Outline Flashback Transaction query (not part of this tutorial) ➢ For error recovery
[PDF] SQL Tutorial
SQL tutorial gives unique learning on Structured Query Language and it helps modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and
[PDF] Advanced SQL and Functions - Joe Conway
17 sept 2014 · Queries Syntax Overview- VALUES, TABLE Last, but not least, the most http:// www postgresql org/docs/9 4/interactive/tutorial-window html
[PDF] Advanced SQL
Advanced SQL Solutions to Practice Exercises 4 1 Query: create table loan using SQL alone or using only a general-purpose programming language
[PDF] Advanced SQL - Subqueries and Complex Joins
Advanced Queries: o Sub-queries: one way to nest or a cascade query is to stick a query in the 'where' clause: e g , find parcels owned by XXX take advantage of the fact that any SQL query returns a table - SQL examples using zoning
[PDF] Lecture 4: Advanced SQL – Part II - GitHub Pages
Aggregates inside nested queries Remember SQL is compositional 2 Hint 1: Break down query description to steps (subproblems) 3 Hint 2: Whenever in doubt
[PDF] Structured Query Language (SQL)
SQL (Structured Query Language) is a database computer language designed for managing In this Tutorial, we will focus on Microsoft SQL Server
[PDF] advanced transition words for college essays
[PDF] advanced video editing app for android
[PDF] advanced vocabulary exercises with answers
[PDF] advanced web programming book pdf
[PDF] advanced web programming bsc it pdf
[PDF] advanced web programming mcq
[PDF] advanced web programming notes pdf
[PDF] advanced web programming practical pdf
[PDF] advanced web programming sanfoundry
[PDF] advanced web programming syllabus
[PDF] advanced web programming tutorial
[PDF] advanced web programming tutorial pdf
[PDF] advanced web programming w3schools
[PDF] advanced web technologies
Lecture4:AdvancedSQL-PartII
Lecture4:AdvancedSQL-PartIILecture4
1.Aggregation&GROUPBY5Lecture4>Section1
7AggregationSELECTCOUNT(*)FROMProductWHEREyear > 1995ExceptCOUNT,allaggregationsapplytoasingleattributeSELECTAVG(price)FROMProductWHERE maker = "Toyota"•SQLsupportsseveralaggregationoperations:•SUM,COUNT,MIN,MAX,AVGLecture4>Section1>Aggregation
8•COUNTappliestoduplicates,unlessotherwisestatedSELECTCOUNT(category) FROMProductWHEREyear > 1995Note:SameasCOUNT(*).Why?Weprobablywant:SELECTCOUNT(DISTINCTcategory)FROMProductWHEREyear > 1995Aggregation:COUNTLecture4>Section1>Aggregation
9Purchase(product, date, price, quantity)MoreExamplesSELECTSUM(price * quantity)FROMPurchaseSELECTSUM(price * quantity)FROMPurchaseWHERE product = 'bagel'Whatdothesemean?Lecture4>Section1>Aggregation
10SimpleAggregationsPurchaseProductDatePriceQuantitybagel10/21120banana10/30.510banana10/10110bagel10/251.5020SELECTSUM(price * quantity)FROMPurchaseWHERE product = 'bagel'50(=1*20+1.50*20)Lecture4>Section1>Aggregation
11GroupingandAggregationSELECT product,SUM(price * quantity) AS TotalSalesFROMPurchaseWHEREdate > '10/1/2005'GROUP BYproductLet'sseewhatthismeans...Findtotalsalesafter10/1/2005perproduct.Lecture4>Section1>GROUPBYPurchase(product, date, price, quantity)
131.ComputetheFROMandWHEREclausesProductDatePriceQuantityBagel10/21120Bagel10/251.5020Banana10/30.510Banana10/10110SELECT product, SUM(price*quantity) AS TotalSalesFROMPurchaseWHEREdate > '10/1/2005'GROUP BY productLecture4>Section1>GROUPBYFROM
ProductDatePriceQuantityBagel10/21120Bagel10/251.5020Banana10/30.510Banana10/10110142.GroupbytheattributesintheGROUPBYSELECT product, SUM(price*quantity) AS TotalSalesFROM PurchaseWHERE date > '10/1/2005'GROUP BY productLecture4>Section1>GROUPBYGROUP BY ProductDatePriceQuantityBagel10/2112010/251.5020Banana10/30.51010/10110
153.ComputetheSELECTclause:groupedattributesandaggregatesSELECTproduct, SUM(price*quantity) AS TotalSalesFROM PurchaseWHERE date > '10/1/2005'GROUP BY productProductTotalSalesBagel50Banana15SELECTProductDatePriceQuantityBagel10/2112010/251.5020Banana10/30.51010/10110Lecture4>Section1>GROUPBY
16GROUPBYv.s.NestedQuereisSELECT product, Sum(price*quantity) ASTotalSalesFROMPurchaseWHEREdate > '10/1/2005'GROUP BYproductSELECT DISTINCTx.product, (SELECTSum(y.price*y.quantity)FROMPurchase yWHEREx.product= y.productAND y.date> '10/1/2005') ASTotalSalesFROMPurchase xWHEREx.date> '10/1/2005'Lecture3>Section2>GROUPBY
17HAVINGClauseSamequeryasbefore,exceptthatweconsideronlyproductsthathavemorethan100buyersHAVINGclausescontainsconditionsonaggregatesSELECT product, SUM(price*quantity)FROMPurchaseWHEREdate > '10/1/2005'GROUP BY productHAVINGSUM(quantity) > 100WhereasWHEREclausesconditiononindividualtuples...Lecture4>Section1>GROUPBY
18GeneralformofGroupingandAggregation•S=CanONLYcontainattributesa1,...,akand/oraggregatesoverotherattributes•C1=isanyconditionontheattributesinR1,...,Rn•C2=isanyconditionontheaggregateexpressionsSELECTSFROMR1,...,RnWHEREC1GROUP BYa1,...,akHAVINGC2Why?Lecture4>Section1>GROUPBY
19GeneralformofGroupingandAggregationSELECTSFROMR1,...,RnWHEREC1GROUP BYa1,...,akHAVINGC2Evaluationsteps:1.EvaluateFROM-WHERE:applyconditionC1ontheattributesinR1,...,Rn2.GROUPBYtheattributesa1,...,ak3.ApplyconditionC2toeachgroup(mayhaveaggregates)4.ComputeaggregatesinSandreturntheresultLecture4>Section1>GROUPBY
20Group-byv.s.NestedQuery•Findauthorswhowrote³10documents:•Attempt1:withnestedqueriesSELECTDISTINCTAuthor.nameFROM AuthorWHERE COUNT(SELECTWrote.urlFROMWroteWHEREAuthor.login= Wrote.login)> 10Author(login, name)Wrote(login, url)ThisisSQLbyanoviceLecture4>Section1>GROUPBY
21Group-byv.s.NestedQuery•Findallauthorswhowroteatleast10documents:•Attempt2:SQLstyle(withGROUPBY)SELECTAuthor.nameFROM Author, WroteWHEREAuthor.login= Wrote.loginGROUP BYAuthor.nameHAVING COUNT(Wrote.url) > 10NoneedforDISTINCT:automaticallyfromGROUPBYThisisSQLbyanexpertLecture4>Section1>GROUPBY
moreefficient!Lecture4>Section1>GROUPBY3.AdvancedSQL-izing24Lecture4>Section2
26QuantifiersProduct(name, price, company)Company(name, city)Findallcompaniesthatmakesomeproductswithprice<100SELECT DISTINCTCompany.cnameFROMCompany, ProductWHERECompany.name= Product.companyAND Product.price< 100Existential:easy!JLecture4>Section2>QuantifiersAnexistentialquantifierisalogicalquantifier(roughly)oftheform"thereexists"
27QuantifiersProduct(name, price, company)Company(name, city)Findallcompanieswithproductsallhavingprice<100SELECT DISTINCTCompany.cnameFROMCompanyWHERECompany.nameNOT IN(SELECT Product.companyFROM Product.price>= 100)Auniversalquantifierisoftheform"forall"Universal:hard!LFindallcompaniesthatmakeonlyproductswithprice<100EquivalentLecture4>Section2>Quantifiers
30NullValues•C1ANDC2=min(C1,C2)•C1ORC2=max(C1,C2)•NOTC1=1-C1SELECT*FROMPersonWHERE(age < 25) AND (height > 6 AND weight > 190)Won'treturne.g.(age=20height=NULLweight=200)!RuleinSQL:includeonlytuplesthatyieldTRUE(1.0)Lecture4>Section2>NULLs
31NullValuesUnexpectedbehavior:SELECT*FROMPersonWHEREage < 25 OR age >= 25SomePersonsarenotincluded!Lecture4>Section2>NULLs
32NullValuesCantestforNULLexplicitly:•xISNULL•xISNOTNULLSELECT*FROMPersonWHEREage < 25 OR age >= 25 OR age IS NULLNowitincludesallPersons!Lecture4>Section2>NULLs
33RECAP:InnerJoinsBydefault,joinsinSQLare"innerjoins":SELECTProduct.name, Purchase.storeFROMProduct JOIN Purchase ONProduct.name= Purchase.prodNameSELECTProduct.name, Purchase.storeFROMProduct, PurchaseWHEREProduct.name= Purchase.prodNameProduct(name, category)Purchase(prodName, store)Bothequivalent:BothINNERJOINS!Lecture4>Section2>NULLs
34InnerJoins+NULLS=Lostdata?Bydefault,joinsinSQLare"innerjoins":However:Productsthatneversold(withnoPurchasetuple)willbelost!SELECTProduct.name, Purchase.storeFROMProduct JOIN Purchase ONProduct.name= Purchase.prodNameSELECTProduct.name, Purchase.storeFROMProduct, PurchaseWHEREProduct.name= Purchase.prodNameProduct(name, category)Purchase(prodName, store)Lecture4>Section2>NULLs
35OuterJoins•Anouterjoinreturnstuplesfromthejoinedrelationsthatdon'thaveacorrespondingtupleintheotherrelations•I.e.IfwejoinrelationsAandBona.X=b.X,andthereisanentryinAwithX=5,butnoneinBwithX=5...•ALEFTOUTERJOINwillreturnatuple(a,NULL)!•LeftouterjoinsinSQL:Lecture4>Section2>OuterJoinsSELECTProduct.name, Purchase.storeFROMProduct LEFT OUTER JOIN Purchase ONProduct.name= Purchase.prodNameNowwe'llgetproductseveniftheydidn'tsell
36namecategoryGizmogadgetCameraPhotoOneClickPhotoprodNamestoreGizmoWizCameraRitzCameraWiznamestoreGizmoWizCameraRitzCameraWizProductPurchaseINNERJOIN:SELECTProduct.name, Purchase.storeFROMProduct INNER JOIN Purchase ONProduct.name= Purchase.prodNameNote:anotherequivalentwaytowriteanINNERJOIN!Lecture4>Section2>OuterJoins
37namecategoryGizmogadgetCameraPhotoOneClickPhotoprodNamestoreGizmoWizCameraRitzCameraWiznamestoreGizmoWizCameraRitzCameraWizOneClickNULLProductPurchaseLEFTOUTERJOIN:SELECTProduct.name, Purchase.storeFROMProduct LEFT OUTER JOIN Purchase ONProduct.name= Purchase.prodNameLecture4>Section2>OuterJoins
LinearalgebrainSQL1.Simplejoinswithaggregations2.Hint1:UsingaliasesleadstocleanSQL3.Hint2:SQLsupportsmanyoperationsovernumericattributes(intheSELECTpartofanSFWquery)43Lecture4>ProblemSet#1SELECT MAX(A.val*B.val)FROMA, BWHEREA.i= B.iANDA.j= B.j
PrecipitationdataandnestedqueriesExample:"UsingasingleSQLquery,findallofthestationsthathadthehighestdailyprecipitation(acrossallstations)onanygivenday."45Lecture4>ProblemSet#1SELECT station_id, dayFROMprecipitation,(SELECTday ASmaxd, MAX(precipitation)AS maxpFROMprecipitationGROUP BY day)WHEREday = maxdANDprecipitation = maxpPrecipitation
Example:"Findallpathsofsizetwoinadirectedgraph"47Lecture4>ProblemSet#1SELECT e1.src, e1.trg, e2.trgFROMedges ASe1, edges ASe2, WHEREe1.trg = e2.srcThetravelingSQLsalesman:GraphtraversalsinSQLedge_idsrctrg1AB2BC3CDEdgesSomemoreexamples:https://www.fusionbox.com/blog/detail/graph-algorithms-in-a-database-recursive-ctes-and-topological-sort-with-postgres/620/
quotesdbs_dbs6.pdfusesText_11