Tutoriel SQL
SQL signifie langage de requête structuré . Syntaxe SQL . ... des systèmes de bases de données modernes comme MS SQL Server IBM DB2
Injection SQL avancée
I HACK. • I CURSE. • I DRINK (Rum & Coke). How I Throw Down Page 4. Identify – How to find SQLI. Attack Methodology – The process and syntax I use.
SQL & Advanced SQL
05?/05?/2012 Hierarchical QUERIES. What is the hierarchy of management in my enterprise? ADVANCED SQL QUERIES. Oracle Tutorials. 5th of May 2012. Page 23 ...
Chapter 5: Advanced SQL
Accessing SQL From a Programming Language. ? Functions and Procedural Constructs. ? Triggers. ? Recursive Queries. ? Advanced Aggregation Features.
Advanced SQL and Functions
17?/09?/2014 Adv. SQL - Window Functions CTEs
Advanced Programming Techniques with PROC SQL - Kirk Paul
The SQL procedure is a wonderful tool for querying and subsetting data; restructuring data by constructing case expressions; constructing and using virtual
Advanced SQL Injection In SQL Server Applications
The typical unit of execution of SQL is the 'query' which is a collection of statements that typically return a single 'result set'. SQL statements can modify
Lecture 4: Advanced SQL – Part II
Aggregates inside nested queries. Remember SQL is compositional. 2. Hint 1: Break down query description to steps (subproblems). 3. Hint 2: Whenever in doubt
Logical SQL Reference Guide for Oracle Business Intelligence
Si une analyse contient des colonnes hiérarchiques des sélections ou des groupes
Amusez-vous avec la procédure SQl - Un didacticiel avancé
La procédure SQL est une implémentation de la norme ANSI. Langage de requête structuré ( SQL ) qui facilite l'extraction de données à partir de plusieurs sources avec un simple
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_dbs22.pdfusesText_28[PDF] advanced sql server queries interview questions
[PDF] advanced sql server tutorial
[PDF] advanced sql server tutorial pdf
[PDF] advanced sql server tutorial point
[PDF] advanced sql tuning burleson pdf
[PDF] advanced sql tuning tips and techniques pdf
[PDF] advanced stored procedure examples in oracle
[PDF] advanced stored procedure examples in sql server pdf
[PDF] advanced t sql books
[PDF] advanced t sql querying and programming pdf
[PDF] advanced test in c and embedded system programming pdf free download
[PDF] advanced transition words for college essays
[PDF] advanced video editing app for android
[PDF] advanced vocabulary exercises with answers
