SQL: subtilits utiles

SQL Avanc2011Witold Litwin

NQuoi & Pourquoi ?Possibilits Etendues de Manipulation de BDs relationnelles Fondamentales pour lexploration approfondie Statistiques, prvision de risques, analyse de la tendanceGestion, Actuariat, ISFN

Exemple canonSPSPNSynonymes"Diffrent de" peut tre exprim de trois manires:!= ^= Oracle, DB2 mais pas MsAccessType d'attribut peut tre exprim de plusieurs manires (SQL Oracle):CHAR(n) VARCHAR(n)FLOAT DECIMALNUMBER INTEGER SMALLINTLONG LONG VARCHARTypes de MsAccess ne sont pas ceux dANSIRevoir mon cours SQL/QBE de base

NNoms d'attributsPeuvent contenir des blancs:"Nom de fournisseur"(Oracle)Dans MSAccess: [Nom de fournisseur]Nom de fournisseur et Nom de fournisseur dans constantes, clause LIKEDans SQL Server [ ] impliquent le respect de la casseEn gnral interdits:95BilanCommence avec un chiffreSELECT, DateMots rservs[A.B] et [A!B] MsAccess Comment faire si besoin ?

NOn peut insrer de tuples dans une vue MsAccessToute vue incluant la cl primaireNotamment comme attribut de jointureY compris externeLien classe sous-classeLe rsultat peut tre linsertion simultane dans plusieurs tables sources de la vueLe tuple insr en QBE peut aussi disparatre louverture suivante de la vueInsertion dans une Vue MsAccessNLinsertion en mode QBE (feuille de donnes) dans la vue, de la cl dun tuple t existant dans une table dont la vue dpend, peut induire lapparition de tous les autres valeurs dans tLe tuple insr en mode QBE peut aussi disparatre louverture suivante de la vueInsertion dans une Vue MsAccessNA exprimenter sur la base S-P Vue SP1 : Select S.[S#], SNAME, STATUS, CITY, [P#], QTY FROM S, SP Vue SP2 : Select SP.[S#], SNAME, STATUS, CITY, [P#], QTY FROM S, SPInsertion dans une Vue MsAccessNInsertion dans une VueLinsertion QBE dun dterminant dans une vue jointure peut faire apparatre auto la valeur dtermine:Insertion de Cpostal Ville dans :SELECT P.*, Ville FROM P, C WHERE P.Cpostal = C.Cpostal ;Avec :P (P#, Nom, CPostal) et C (Cpostal, Ville) A exprimenter sur MsAccess

Insertion dans une Vue MsAccessNLinsertion QBE dans une vue avec un attribut dynamique, dune valeur de base de cet attribue, conduit lapparition auto de la valeur dynamique PrixTTC = PrixHT * 1.2Lquivalent des attributs composs sous SQL Server & autres SGBDsA exprimenter sur MsAccess 2007 Sous MsAccess2010 la table peut avoir les attributs composs directement.Insertion dans une Vue MsAccessNMAJ dune Vue MsAccessOn peut mettre jour une vueToute vue incluant la cl primaireNotamment comme attribut de jointureY compris externeLien classe sous-classeLe rsultat peut tre la MAJ simultane de plusieurs tables sources de la vueA exprimenter sur les vues SP1 et SP2

NMAJ dune Vue MsAccessLa MAJ dun dterminant dans une vue QBE jointure peut faire changer auto le dtermin:MAJ de Cpostal MAJ Ville dans lexemple avantOn peut aussi MAJ VilleMais on ne peut pas MAJ Cpostal une valeur qui ne serait pas dans CA exprimenter sur les vues prcdentes

NSuppression dans une Vue MsAccessOn peut supprimer des tuples dans une vueToute vue mono-table ou un tuple de la vue correspond un tuple de la tablePas de DISTINCT, GROUP BYSurprise ? On insre un tuple dans une vue V jointure INSERT VDELETE V ne peut pas le supprimer A exprimenter idem

NMsAccess : LgendesLa lgende a la priorit sur laliasSi la lgende de P# dans SP de notre base S-P est Product IdAlors, lalias Produit est inoprantSELECT SP.[s#], SP.[p#] AS Produit, SP.qty FROM SP;

s#Product IDqtys1p1300s1p2200s1p3400s1p4200s1p5100s1p6100s2p1300NExpressions de valeurPeuvent tre des attr. dynamiques, imbriques dans SQL de MsAccessSELECT Qty, [S#], qty1-4 AS qty2, qty2/3 AS qty3, 2*qty AS qty1 FROM SP;Mais ces atttr. ne peuvent pas tre rfrencs dans la clause WHERESELECT Qty, [S#], qty1-4 AS qty2, qty2/3 AS qty3, 2*qty AS qty1 FROM SP where qty1 > 200; pourquoi ? Une bonne question pour MicrosoftNExpressions de valeurToutefois sous QBE, l'attr. qty1 peut tre rfrencdonc la requte ci-dessus devient lgalevous avez dit bizarre ?Le signe + signifie aussi une concatnation pour les attributs du type texte, soit a = 2 et b = 3a+b 23Ce qui peu surprendre dans une requte paramtresTexte est le type par dfaut du paramtreIl faut la clause Parameters a int, b int;NPour en savoir + sur les attributs dynamiquesLitwin, W., Vigier, Ph. Dynamic attributes in the multidatabase system MRDSM, IEEE-COMPDEC, (Feb. 1986). Litwin, W., Vigier, Ph. New Functions for Dynamic Attributes in the Multidatabase System MRDSM. Honeywell Large Systems Users's Forum, HLSUA XIV, New Orleans, 1987, 467-475.Voir le site du CERIANUNION et Noms DattributsSELECT [s#] FROM SUnionSELECT [p#] FROM pQuel nom dattribut sera dans le rsultat Sous MSAccessDans SQL Server, MySQL, Oracle

NUNION et ORDER BYSELECT [s#] FROM SUnionSELECT [p#] FROM pPar consquent, o peut-on placer la (ou les) clauses ORDER BY ? Aprs le 1er Select et/ou aprs le 2me ?Quels nom(s) y employer ?Quel serait le rsultat de ORDER BY [S#] aprs le 2me SELECT ?

NORDER BY et expressions de valeurLes expressions de valeur peuvent tre dans ORDER BY clause:ORDER BY SAL - COMMExceptions : UNION, MINUS, INTERSECTCette clause peut rfrencer l'attribut par position:Select ENAME SAL 0.75 * (SAL + 500)FROM EMPORDER BY 3 ;Un must dans UNION, MINUS, INTERSECT dans Oracle Un alias dans le 1r Select est acceptable dans MsAccess

NORDER BY et expressions de valeurORDER BY clause peut aussi rfrencer un attribut et une expression qui n'est pas dans SELECT clause:Select S#, CITY FROM SORDER BY SNAME STATUS+50 ; exceptions: UNION, MINUS, INTERSECT DB2 SQL n'avait pas ces possibilits Aux dernires nouvellesORDER BY et DISTINCT peuvent tre en conflitEssayez:SELECT distinct sp.[s#]FROM spORDER BY sp.qty;

NOrdre de priorit d'oprations1. Oprateurs de comparaison logique:= != >= > y ou (2) x = y ou (3) x < y ?ex. pour valuer ORDER BY ou TOP kDB2 : oui pour (1)MsAccess Standard: soit (1) soit (3), selon implmentationNValeurs nullesEst-il vrai que:SELECT * FROM S WHERE CITY ='Paris'UNIONSELECT * FROM S WHERE NOT CITY = 'Paris' ;est toujours gal SELECT * FROM S ;Pourquoi faire simple si on peut faire compliqu" ?NValeurs nullesSELECT P_1.*FROM P AS P_1WHERE p_1.weight > all (select (py.weight) from P as py where py.color = 'blue');

SELECT P_1.*FROM p AS P_1WHERE not exists (select * from P as py where py.color = 'blue' and py.weight >= p_1.weight );

Ces requtes, sont-elles quivalentes ?Test color et weight nulsRemplace all par any et vois le rsultatNValeurs nullesFonctions scalairespeuvent sappliquer aux nuls ABS, INT, LCASE... (nul) = nulpeuvent gnrer une erreurLOG (nul) -> #ErrorA voir cas par casNFonctions Scalaires Date/TempsSELECT Now() AS now, Weekday(#30/10/06#) AS [weekday of 30/10/06], Weekday(#30/10/06#+15) AS [weekday + 15], weekdayname(2) AS [weekdaynameerror for 30/10/06], WeekdayName(weekday(datevalue(now())-1)) AS [weekdaynamecorrig for now ()]FROM S;Une erreur de calcul du nom du jour de la semaine existe en version franaise de MsAccess 2003+La semaine US de weekday commence le dimanche, celle franaise de weekdayname : le lundiDonc 2 ci-dessus doit donner lieu au lundi (la ralit pour 30/10/06)

NFonctions Scalaires Date/TempsSELECT Now() AS now, TimeValue(Now()) AS timevalue, TimeValue(Now())+TimeValue(Now()) AS [adding timevalues], hour(now()) AS [hour], month(now()) AS [month], weekday(datevalue(now())) AS datevalue, monthname(month(now())) AS monthname, weekday(day(now())-1) AS [day]FROM S;

Notez lerreur non-signale daddition de date-temps.Notez lerreur non-signale daddition de date-temps (anne 1899).NFonctions Scalaires Date/TempsDateDiff ( interval, date1, date2, [firstdayofweek], [firstweekofyear]);IntervalExplanationYyyyYearqQuartermMonthyDay of yeardDaywWeekdaywwWeekhHournMinutesSecondNFonctions Scalaires Date/TempsSELECT now() as now, #11/07/2009 09:40:09# as DateTest, DateDiff("n", now, DateTest) as DiffMin, DateDiff("h", now, DateTest) as DiffHour;Test DateDiffnowDateTestDiffMinDiffHour07/11/2009 12:03:2607/11/2009 09:40:09-143-3 Voir le Web pour les paramtres optionnels de DateDiff (DiffDate en mode cration (QBE) NFonctions Scalaires Date/Temps Clause LIKE supporte un format spcifique pour les dates DateV Like Jan/*/2009 Liste tous les tuples o DateV est de Janvier 2009 DateV Like */15/2009 Liste tous les tuples o DateV est le 15 dun mois de 2009 On peut se dbrouiller autrement Comment ? NFonction Scalaire RND Permet faire lchantillonnage Trois fournisseurs avec les fournitures au hasard (on montre RND aussi, pour lex.) SELECT TOP 3 [s#], rnd(qty) AS rankFROM SPORDER BY rnd(qty) DESC;chantillon s#rankS15,02628087997437E-02S40,518015921115875s30,75702953338623NFonction Scalaire RND Et si on crivait ? SELECT TOP 3 [s#], rnd() AS rankFROM SPORDER BY rnd(qty) DESC;OuSELECT TOP 3 [s#], rnd([S#]) AS rankFROM SPORDER BY rnd(qty) DESC; Votre commentaire ici:NFonctions FinanciresFonction DDBCalcule l'amortissement dgressif pendant une priode selon un taux spcifi.DDB(cot, VRsiduelle, VieUtile, Priode [, taux] )

NFonctions FinanciresFonction DDBCalcule l'amortissement dgressif pendant une priode selon un taux spcifi.DDB(cot, VRsiduelle, VieUtile, Priode [, taux] )Valeur rsiduelle est une valeur dsire seulementLamortissement devient zro si lon latteint NLe taux X implique la perte annuelle de X * (cout / priode) Fonction DDBinsert into DDB (cost, salvage, life, factor, amortiss, period)select 100 as cost, 70 as salvage, 5 as life, 1 as factor, DDB(cost, salvage, life, period, factor) as amortiss, period ;

Rsultat dexcutions pour les priodes = 15 Ordre de choix de valeurs na pas dimportanceComment calculer aussi la valeur rsiduelle relle la fin de chaque priode ?NFonction DDBINSERT INTO DDB ( cost, salvage, life, factor, amortiss, period )SELECT 100 AS cost, 20 AS salvage, 5 AS life, 0.5 AS factor, DDB(cost,salvage,life,period,factor) AS amortiss, period;

Rsultat dexcutions pour les priodes = 15 Ordre de choix de valeurs na pas dimportance Exprimentez avec dautres taux, notamment >= lifeNFonction PMT = Payment de SQLFonction Valeur de Payement VPM en QBE franaisDonc dans le Gnrateur dExpressionsCalcule les annuits dun emprunt dure et taux donnes.Les annuits apparaissent comme nombres ngatifsFonction PMTNFonction PMTSELECT int(Pmt([rate],[nper],[pv])) AS Annuite, rate as taux_annuel, nper as nbre_annes, pv as [valeur prsente], int(Annuite*nper) as valeur_paye, valeur_paye + pv as surprime

Fonction PMT calcul d'annuit d'empruntAnnuitetaux_annuelnbre_annesvaleur prsentevaleur_payesurprime-160490,0520200000-320980-120980NPlacement taux variableSomme et Fin sont les paramtres Expression indirecte de lagrgat PRODUCT SELECT somme*exp(sum(log(1+taux/100)))FROM [placement taux variable]WHERE [anne relative] between 1 and fin;Et les nuls que log ne supporte pas ?Anne relativeTaux1424334555Voir + dans le livre SQL Design Patterns

NGROUP BYEst une clause redondante avec le SELECT sous-requtesLa requteSELECT P#, MAX(QTY) FROM SP GROUP BY P# ;est quivalente SELECT DISTINCT P#, (SELECT MAX(QTY) FROM SP AS X WHERE X.P# = SP.P#) FROM SP ;Testez !Ca sapplique toute fonction agrgatQue faire avec les clauses WHERE et HAVINGNLISTLa requteSELECT P#, MAX(QTY), LIST(S#, QTY) FROM SP GROUP BY P# ;Donne la valeur agrge et les dtails par fournisseurComme les tabulations croisesMais en + simple LIST nexiste en standard que sur SQL Anywhere DBMSEn mono attribut (2004)En MsAccess, LIST (limit) peut tre ralis par :un formulaire avec des sous-formulaires UNION comme dans Roll Up plus loinNLISTPour en savoir +Litwin, W. Explicit and Implicit LIST Aggregate Function for Relational Databases. IASTED Intl. Conf. On Databases & Applications, 2004NGROUP BY avec WHEREClause WHERESELECT P#, MAX(QTY), MIN(QTY) FROM SP WHERE S# S1 GROUP BY P# ;est quivalente :SELECT DISTINCT P#, (SELECT MAX(QTY) FROM SP AS X WHERE X.S# S1 AND X.P# = SP.P#) AS MAXQ,(SELECT MIN(QTY) FROM SP AS X WHERE X.S# S1 AND X.P# = SP.P#) AS MINQ FROM SP WHERE S# S1 ;Peut servir pour raliser T-GROUP BY (voir plus loin)NGROUP BYLes deux formulations ne sont pas toujours quivalentesSELECT MAX(QTY)FROM SPGROUP BY P# ;nest pas (tout fait) quivalent :SELECT DISTINCT P#, (SELECT MAX(QTY) FROM SP AS X WHERE X.P# = SP.P#) FROM SP ;PourquoiNIl ny a pas de P# dans la 1re formulation, ncessaire dans la 2me ?GROUP BY avec HAVINGLa clause HAVING est galement redondanteSELECT P#FROM SPGROUP BY P# HAVING COUNT(*) > 1;est quivalent :SELECT DISTINCT P# FROM SP, WHERE (SELECT COUNT(*) FROM SP AS X WHERE X.P# = SP.P#) > 1 ;PourquoiEt si on ajoutait la clause WHERE S# S1NSELECT DISTINCT P# FROM SP WHERE S# S1 AND (SELECT COUNT(*) FROM SP AS X WHERE X.P# = SP.P# AND S# S1) > 1 ; T-GROUP BYPropos pour SQLPermettrait de faire les groupes par rapport =Le rle de -join par rapport equi-join Ainsi la requte hypothtique:SELECT P#, AVG(QTY) AS QTY1INT(AVG(QTY)) AS QTY2FROM SP T-GROUP (QT1 BY P#, QT2 BY P#)donnerait la quantit moyenne de toute pice autre que la pice P# avec la quantit moyenne de la pice P#, pour la comparaison loquente NSELECT DISTINCT P# FROM SP WHERE S# S1 AND (SELECT COUNT(*) FROM SP AS X WHERE X.P# = SP.P# AND S# S1) > 1 ; T-GROUP BYOn peut raliser la requte prcdente lheure actuelle sous MsAccess comme:SELECT DISTINCT SP.[p#] AS part, (SELECT int(avg(QTY)) FROM SP AS X WHERE X.[P#] SP.[P#]) AS avg_qty_other_parts,(SELECT avg(QTY) FROM SP AS X WHERE X.[P#] = SP.[P#]) AS part_avg_qtyFROM SP;Vrai ou Faux ? NSELECT DISTINCT P# FROM SP WHERE S# S1 AND (SELECT COUNT(*) FROM SP AS X WHERE X.P# = SP.P# AND S# S1) > 1 ; T-GROUP BY Rsultat: partavg_qty_other_partspart_avg_qtyp1250300p2262250p3245400p4260250p5260250p6272100NSELECT DISTINCT P# FROM SP WHERE S# S1 AND (SELECT COUNT(*) FROM SP AS X WHERE X.P# = SP.P# AND S# S1) > 1 ; T-GROUP BY En savoir +: Litwin, W. Galois Connections, T-CUBES, & P2P Database Mining. 3rd Intl. Workshop on Databases, Information Systems and Peer-to-Peer Computing (DBISP2P 2005), VLDB 2005Springer Verlag (publ.) NSELECT DISTINCT P# FROM SP WHERE S# S1 AND (SELECT COUNT(*) FROM SP AS X WHERE X.P# = SP.P# AND S# S1) > 1 ; Rangs Non-Denses(Non Dense Ranking)SELECT [s#], [p#], qty,(select count(*) from SP as X where X.qty > sp.qty)+1 as [non dense rank] FROM SP order by qty desc, [s#] asc

s#p#qtyND-ranks4p54001s3p24001s1p34001s4p43004s2p23004s1p13004s4p22007s1p42007s1p22007s4p12007s1p610011s1p510011NRangs Non-Denses(Graphique MsAccess)

s#p#qtyND-ranks4p54001s3p24001s1p34001s4p43004s2p23004s1p13004s4p22007s1p42007s1p22007s4p12007s1p610011s1p510011NRangs Denses(Dense Ranking)SELECT [s#], [p#], qty,(select count(qty) from (select distinct qty from SP as y) as X where X.qty > sp.qty)+1 AS [D-rank], FROM SPORDER BY qty DESC , [s#];s#p#qtyD-ranks1p34001s3p24001s4p54001s1p13002s2p23002s4p43002s1p42003s1p22003s4p22003s4p12003s1p61004s1p51004NRangs DensesGraphique MsAccesss#p#qtyD-ranks1p34001s3p24001s4p54001s1p13002s2p23002s4p43002s1p42003s1p22003s4p22003s4p12003s1p61004s1p51004

NDistribution La probabilit quune pice soit fournie par un fournisseurArrondie 3 chiffres dcimauxSELECT DISTINCT SP.[s#], round((select sum(qty) from SP X where X.[s#] = SP.[s#])/(select sum(qty) from SP as Y), 3) AS Distribution FROM SP;

NRsultats#Distributions10,419s20,097s30,129s40,355

NDistribution Cumulative La probabilit cumulative quune pice soit fournie par un fournisseurArrondie 3 chiffres dcimauxSELECT DISTINCT SP.[s#], round((select sum(qty) from SP X where X.[s#] 300 Graph 3D avec Qty en abscisses

NMoyenne GlissanteSELECT DISTINCT S.date_t, (select int(avg(prix)) from [serie] X where X.date_t between S.date_t-jours+1 and S.date_t) AS MoyenneGlisante, date_t-jours+1 as date_d, jours as kFROM [serie] SORDER BY date_t DESC;Moyenne Glissante Paramtredate_tMoyGlissdate_dk30/10/200812027/10/2008425/10/200813122/10/2008423/10/200812720/10/2008417/10/200826914/10/2008415/10/20086012/10/2008411/10/200829508/10/2008409/10/200834006/10/2008408/10/200832405/10/2008406/10/200831503/10/20084NMoyenne GlissanteSELECT DISTINCT S.date_t, (select int(avg(prix)) from [serie] X where X.date_t between S.date_t-jours+1 and S.date_t) AS MoyenneGlisante, date_t-jours+1 as date_d, jours as kFROM [serie] SORDER BY date_t DESC;

Graphique avec une info-bulleNMoyenne Glissante La construction sapplique dautres fonctions glissantes CSUM (Cumulative (Running) Sums) MAVG MSUM MDIFF Voir Teradata + loinNValue at Risk (VaR) Lanalyse de risques Voir le poly et Web Exemples pour les prts catgoriss Nombre des prts non-rembourss totalement (par cat.) / Nbre Total Ratio ( 1 X / Y) X somme des remboursements Y somme attendue des ceux-ci NValue at RiskTable Prt

NValue at RiskSELECT Prt.Cat AS Cat, Sum(Prt.MAR) AS TotalMAR, Sum(Prt.MR) AS TotalMR, Round(1-[TotalMR]/[TotalMAR],3)*100 AS Risque1, (select Count([Status]) from Prt as X where X.cat = Prt.Cat and status = -1)/count(*) AS Risque2FROM PrtGROUP BY Prt.Cat;NValue at RiskNotez lopposition mutuelle des facteurs de risque

RsultatNSries financiresA partir de valeur(s) donnes on veut gnrer une srie chronologiqueValeur dun placement Taux dintrtOn veut la valeur aprs 1,220 ansPour le taux indiqu, soit T1Pour, soit 1% de plus, soit T2On veut voir aussi le gain que T2 offrirait par rapport au T1NSries financiresSELECT capital, taux, n, int(capital*(1+taux)^n) as [val aprs n ans], round(taux+0.01,2) as taux1, int(capital*(1+taux1)^n) as [val1 aprs n ans], [val1 aprs n ans]- [val aprs n ans] as GainAbs, GainAbs/capital*100 as [GainRel%]FROM EntiersUNION SELECT capital, taux, n+10 as n1, int(capital*(1+taux)^n1) as [val aprs n ans], round(taux+0.01,2) as t, int(capital*(1+t)^n1) as x, x- [val aprs n ans] as z, z/capital*100 FROM Entiers;

Entiers est une table aux. avec la colonne de1,210NSries financirescapitaltauxn val aprs n anstaux1val1 aprs n ansGainAbsGainRel%1000,0511050,06106111000,0521100,06112221000,0531150,06119441000,0541210,06126551000,0551270,06133661000,0561340,06141771000,0571400,0615010101000,0581470,0615912121000,0591550,0616813131000,05101620,0617917171000,05111710,0618918181000,05121790,0620122221000,05131880,0621325251000,05141970,0622629291000,05152070,0623932321000,05162180,0625436361000,05172290,0626940401000,05182400,0628545451000,05192520,0630250501000,05202650,063205555NSries financires

NCration dun Portefeuille On veut acheter une collection de produits financiers de la table Produits Dans la limite L de la somme donne Jusqu n produit (n = 3 p.ex.) par ensemble On veut examiner jusqu k collections (k = 20 p.ex.) de prix (cumul) le plus proche de L En ordre descendant de prix On peut choisir un mme produit plusieurs fois pour former une collection Plusieurs actionsNParler de lachat de prts ?Cration dun Portefeuille ProduitsP#prixp3200p1200p2400p3200p4100p6100p5300p8300p7400p10200p12300p13300NCration dun Portefeuille Parameters [Investissement ?] Integer;

select TOP 20 * FROM (SELECT P.[p#] as Produit1, "" as Produit2, "" as Produit3, P.prix as PrixCumul, "" as Prix1, "" as Prix2, "" as Prix3, 1 as Nbre, [Investissement ?] as Investissement FROM Produits P where p.prix