1 / 67

Hoofdstuk 8: Werken met SQL voor eindgebruikers

Hoofdstuk 8: Werken met SQL voor eindgebruikers. Overzicht. Inleiding op SQL: de select-instructie Eenvoudige queries Join queries Nested queries Queries met setoperatoren. Structured Query Language (SQL). Een datataal voor het definiëren van een relationele database (DDL)

victoria-odonnell
Télécharger la présentation

Hoofdstuk 8: Werken met SQL voor eindgebruikers

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Hoofdstuk 8: Werken met SQL voor eindgebruikers

  2. Overzicht • Inleiding op SQL: de select-instructie • Eenvoudige queries • Join queries • Nested queries • Queries met setoperatoren

  3. Structured Query Language (SQL) • Een datataal • voor het definiëren van een relationele database (DDL) • voor het behandelen van gegevens in een relationele database (DML) • raadplegen van gegevens • wijzigen van gegevens • toevoegen van gegevens • verwijderen van gegevens • Set-georïenteeerde taal • SQL-opdrachten worden uitgevoerd op tabellen die geacht worden voorstellingen te zijn van genormaliseerde wiskundige relaties • Het resultaat van een SQL instructie kan opnieuw een tabel zijn • Geen procedureel programmeren. • Theoretische onderbouwd • Relationele algebra (select, project, unie, doorsnede, verschil, product, deling, join) • Relationele calculus ( - er bestaat een element,  - voor alle elementen) • Interactief of embedded

  4. Voorbeeld: Aankoopadministratie LEVERANCIER (LEVNR, LEVNAAM, LEVADRES, LEVPLAATS, LEVSTATUS) PRODUCT (PRODNR, PRODNAAM, PRODKLEUR, HOEV_IN_VOORR) AANKOOPVOORWAARDEN (LEVNR, PRODNR, AANKOOPPRIJS, LEVERTERMIJN) AANKOOPORDER (AONR, AODATUM, LEVNR) AANKOOPORDERREGEL (AONR, PRODNR, BESTELHOEV)

  5. Voorbeeld: tabeldefinities CREATE TABLE LEVERANCIER (LEVNR CHAR(4) NOT NULL PRIMARY KEY, LEVNAAM VARCHAR(40) NOT NULL, LEVADRES VARCHAR(50), LEVPLAATS VARCHAR(20), LEVSTATUS SMALLINT); CREATE TABLE PRODUCT (PRODNR CHAR(6) NOT NULL PRIMARY KEY, PRODNAAM VARCHAR(40) NOT NULL, CONSTRAINT UC1 UNIQUE(PRODNAAM), PRODKLEUR VARCHAR(15), CONSTRAINT CC1 CHECK(PRODKLEUR IN (‘wit’, ‘groen’, ‘rood’, ‘grijs’, ‘oker’, ‘zwart’)), PRODGEWICHT DECIMAL (6,2), HOEV-IN-VOORR INTEGER);

  6. Voorbeeld: tabeldefinities (vervolg) CREATE TABLE AANKOOPVOORWAARDEN (LEVNR CHAR(4) NOT NULL, AANKOOPPRIJS DECIMAL(8,2) COMMENT ON COLUMN AANKOOPPRIJS IS ‘AANKOOPPRIJS IN EUR’, LEVERTERMIJN TIME COMMENT ON COLUMN LEVERTERMIJN IS ‘LEVERTERMIJN IN DAGEN’, PRIMARY KEY (LEVNR, PRODNR), FOREIGN KEY (LEVNR) REFERENCES LEVERANCIER (LEVNR) ON DELETE CASCADE ON UPDATE CASCADE,FOREIGN KEY (PRODNR) REFERENCES PRODUCT (PRODNR) ON DELETE CASCADE ON UPDATE CASCADE);

  7. Voorbeeld: tabeldefinities (vervolg) CREATE TABLE AANKOOPORDER (AONR CHAR(4) NOT NULL PRIMARY KEY, AODATUM DATE, LEVNR CHAR(4) NOT NULL, FOREIGN KEY (LEVNR) REFERENCES LEVERANCIER (LEVNR) ON DELETE CASCADE ON UPDATE CASCADE); CREATE TABLE AANKOOPORDERREGEL (AONR CHAR(4) NOT NULL, PRODNR CHAR(6) NOT NULL, BESTELHOEV INTEGER, PRIMARY KEY (AONR, PRODNR) FOREIGN KEY (AONR) REFERENCES AANKOOPORDER (AONR) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (PRODNR) REFERENCES PRODUCT (PRODNR) ON DELETE CASCADE ON UPDATE CASCADE);

  8. SQL select-instructie SELECT-component FROM-component [WHERE-component] [GROUP BY-component] [HAVING-component] [ORDER BY-component]

  9. Eenvoudige queries • Onder eenvoudige queries verstaan wij vragen waarmee gegevens worden geraadpleegd die in één tabel aanwezig zijn. • In de FROM component van de select-instructie is dus maar één tabelnaam vermeld. • Met de SELECT component worden kolommen geëxtraheerd. • Als expressies worden in de SELECT-component meestal namen gebruikt van kolommen die wij willen zien.

  10. Eenvoudige queries zonder WHERE component • SELECT LEVNR, LEVNAAM, LEVADRES, LEVPLAATS, LEVSTATUS FROM LEVERANCIER of • SELECT * FROM LEVERANCIER • SELECT LEVNR, LEVNAAM FROM LEVERANCIER • SELECT DISTINCT LEVNR FROM AANKOOPORDER • SELECT LEVNR, PRODNR, LEVERTERMIJN/30 AS MAAND-LEVERTERMIJN FROM AANKOOPVOORWAARDEN

  11. Eenvoudige queries met WHERE component • Wanneer in een select instructie ook een WHERE component aanwezig is, kan men met condities aangeven welke tabelrijen moeten worden geselecteerd. • In de WHERE component worden dus condities of predicaten opgelegd waaraan de op te halen rijen moeten voldoen.(equivalent met “select” operator van relationele algebra) • Voor het specificeren van condities waaraan rijen moeten voldoen, kunnen er in de WHERE component meerdere operatoren worden gebruikt: • vergelijkingsoperatoren (<, >, =, ...) • booleaanse operatoren (AND, OR, NOT) • BETWEEN operator (x BETWEEN y AND z) -> x ≥ y and x ≤ z • IN operator (IN (u,v,w)) -> x=u or x=v or x=w • LIKE operator • NULL operator

  12. Eenvoudige queries met WHERE component (voorbeelden) • SELECT LEVNR, LEVNAAMFROM LEVERANCIERWHERE LEVPLAATS = ‘ANTWERPEN’; • SELECT LEVNR, LEVNAAMFROM LEVERANCIERWHERE LEVPLAATS = ‘ANTWERPEN’ AND LEVSTATUS > 75; • SELECT LEVNR, LEVNAAMFROM LEVERANCIERWHERE LEVSTATUS BETWEEN 75 AND 85; • SELECT PRODNR, PRODNAAMFROM PRODUCTWHERE PRODKLEUR IN (‘WIT’,’GROEN’); • SELECT PRODNR, PRODNAAMFROM PRODUCTWHERE PRODNAAM LIKE ‘AP%’; • SELECT LEVNR, LEVNAAMFROM LEVERANCIERWHERE LEVSTATUS IS NULL

  13. Eenvoudige queries met setfunctie in de SELECT component • In de SELECT component kunnen er allerlei expressies voorkomen. • Deze expressies mogen ook setfuncties zijn. • Met setfuncties kunnen bewerkingen worden uitgevoerd op kolomwaarden van rijen die zich voor een bepaalde vraag kwalificeren. • De belangrijkste setfuncties: • COUNT • MIN, MAX • SUM, AVG • STDEV, VARIANCE

  14. Eenvoudige queries met setfunctie (voorbeelden) • SELECTCOUNT(*) FROM AANKOOPVOORWAARDEN WHERE PRODNR = ‘0117’; • SELECTCOUNT(AANKOOPPRIJS) FROM AANKOOPVOORWAARDEN WHERE PRODNR = ‘0117’; • SELECTCOUNT (DISTINCT AANKOOPPRIJS) FROM AANKOOPVOORWAARDEN WHERE PRODNR = ‘0117’; • SELECTSUM(BESTELHOEV) AS TOTSOMBESTELLINGEN FROM AANKOOPORDERREGEL; • SELECT PRODNR, SUM(BESTELHOEV) AS SOMBESTELLINGEN FROM AANKOOPORDERREGEL WHERE PRODNR = ‘0117’;

  15. Aankoopvoorwaarden

  16. Eenvoudige queries met setfunctie (voorbeelden) • SELECT PRODNR, AVG(AANKOOPPRIJS) AS GEWOGEN-GEMIDDELDE-PRIJS FROM AANKOOPVOORWAARDEN WHERE PRODNR = ‘0117’; • SELECT PRODNR, AVG(DISTINCT AANKOOPPRIJS) ASONGEWOGEN-GEMIDDELDE-PRIJS FROM AANKOOPVOORWAARDEN WHERE PRODNR = ‘0117’; • SELECT PRODNR, VARIANCE(AANKOOPPRIJS) ASPRIJSVARIANTIE FROM AANKOOPVOORWAARDEN WHERE PRODNR = ‘0117’; • SELECT PRODNR, MIN(AANKOOPPRIJS) AS LAAGSTE PRIJS,MAX(AANKOOPPRIJS) AS HOOGSTE-PRIJS FROM AANKOOPVOORWAARDEN WHERE PRODNR = ‘0117’;

  17. Eenvoudige queries met GROUP BY en HAVING • Met een GROUP BY component kunnen rijen worden gegroepeerd op basis van onderlinge overeenkomsten. • bv. rijen moeten eenzelfde waarde hebben voor een gespecificeerde kolom • Met de HAVING component worden groepen van rijen geselecteerd die aan bepaalde condities voldoen. • de HAVING component kan enkel voorkomen als ook de GROUP BY component is gebruikt. • terwijl expressies in de WHERE component geen setfunctie kunnen hebben, kan dat in de HAVING component wel.

  18. Eenvoudige queries met GROUP BY en HAVING (voorbeelden) • Gevraagd: geef de nummers van de producten waarvoor tenminste twee bestellingen uitstaan. • SELECT PRODNR FROM AANKOOPORDERREGEL GROUP BY PRODNR HAVING COUNT(*) >1; • Gevraagd: geef de nummers van de producten waarvoor meer dan 100 stuks in bestelling zijn. • SELECT PRODNR FROM AANKOOPORDERREGEL GROUP BY PRODNR HAVING SUM(BESTELHOEV)> 100;

  19. Eenvoudige queries met ORDER BY component • Indien men de rijen – die ontstaan als resultaat van het uitvoeren van een query – in een bepaalde logische volgorde wil tonen, moet een ORDER BY component aanwezig zijn. • Er mag op elke kolom, die in de SELECT component is gespecificeerd, worden gesorteerd en er mag op meer dan één kolom worden gesorteerd. • default is ‘ascending’. • Interpretatie van null-waarden.

  20. Eenvoudige queries met ORDER BY (voorbeelden) • Gevraagd: sorteer alle uitstaande aankooporders volgens oplopende datum, en per dag volgens dalend leveranciernummer. • SELECT AONR, AODATUM, LEVNR FROM AANKOOPORDER ORDER BY AODATUM ASC, LEVNR DESC; • Gevraagd: geef voor een bepaald product, de prijzen die leveranciers aanrekenen, in dalende volgorde. • SELECT PRODNR, LEVNR, AANKOOPPRIJS FROM AANKOOPVOORWAARDEN WHERE PRODNR = ‘0117’ ORDER BY 3 DESC;

  21. Join queries • Met join queries kunnen gegevens worden samengevoegd of gefuseerd. • Meestal gaat het om gegevens uit verschillende tabellen. • in de FROM component van de select instructie worden de namen gespecificeerd van de tabellen waarvan wij rijen willen samenvoegen • Wij moeten dan voorwaarden opleggen waaronder rijen van tabellen mogen worden samengevoegd. • in de WHERE component worden dan de condities gespecificeerd waaromheen de join moet worden voltrokken • equi-join • Voorts kunnenwij aangeven in welke kolommen uit de respectievelijke tabellen wij zijn geïnteresseerd.

  22. Join queries(vervolg) • Kolommen (vervolg) • wanneer een kolomnaam wordt gebruikt, die in meer dan één van de tabellen van de FROM component voorkomt, is het verplicht een tabelspecificatie voor de kolomnaam te vermelden. • het is evenwel de gewoonte om kolomnamen te kwalificeren met de naam van de tabel waarin de kolom voorkomt, wanneer gegevens gevraagd worden uit meerdere tabellen. • Extra condities in de WHERE component kunnen zorgen voor het fijner filteren van de rijen die voor fusie in aanmerking komen. • een SQL join query zonder WHERE component waarbij alle kolommen in het resultaat mogen verschijnen is in feite een algebraïsch product(SELECT * FROM a, b)

  23. Select instructie met inner-join (voorbeeld 1) • Gevraagd: geef nummer, naam en status van leveranciers die producten kunnen leveren, tezamen met de nummers van de producten die zij kunnen leveren en de aankoopprijs die ze hiervoor aanrekenen.Leverancier (levnr, levnaam, ..., levstatus)Aankoopvoorwaarden (levnr, prodnr, aankoopprijs, ...) • SELECT L.LEVNR, L.LEVNAAM, L.LEVSTATUS, V.PRODNR, V. AANKOOPPRIJS FROM LEVERANCIER L, AANKOOPVOORWAARDEN V WHERE L.LEVNR = V.LEVNR; of • SELECT L.LEVNR, L.LEVNAAM, L.LEVSTATUS, V.PRODNR, V.AANKOOPPRIJS FROM LEVERANCIER AS L, INNER JOIN AANKOOPVOORWAARDEN AS V ON (L.LEVNR = V. LEVNR);

  24. Select instructie met inner-join(voorbeeld 2) • Oplossing:SELECT L.LEVNR, L.LEVNAAM, AO.AONR, AO.AODATUM, P.PRODNR, P.PRODNAAM, AOR.BESTELHOEV FROM LEVERANCIER L, AANKOOPORDER AO, AANKOOPORDERREGEL AOR, PRODUCT PWHERE (L.LEVNR = AO. LEVNR) AND (AO.AONR = AOR.AONR)AND (AOR.PRODNR = P.PRODNR);

  25. Select instructie met inner-join (voorbeeld 3) • Het is mogelijk om gegevens te “joinen” die afkomstig zijn uit rijen van eenzelfde tabel. • voor het oplossen van zo’n opdracht moeten twee ‘verschijningsvormen’ van dezelfde tabel onderscheiden worden opdat het systeem de conditie kan onderzoeken waaronder respectieve rijen mogen worden samengevoegd. • Gevraagd: geef alle paren van leveranciers die dezelfde vestigingsplaats hebben.Leverancier (levnr, levnaam, levadres, levplaats, levstatus) • SELECT L1.LEVNAAM, L2.LEVNAAM, L1.LEVPLAATSFROM LEVERANCIER L1, LEVERANCIER L2WHERE L1.LEVPLAATS = L2.LEVPLAATS AND (L1.LEVNR < L2.LEVNR);

  26. Leverancier L1 Leverancier L2

  27. Select instructie met inner-join (voorbeeld 4) • Samen met de join condities mogen – uiteraard – allerlei andere condities worden opgelegd voor het uiteindelijk selecteren van de rijen. • Gevraagd: de namen van de leveranciers die een specifiek product met nummer 1174 kunnen leveren.Leverancier (levnr, levnaam, levadres, levplaats, levstatus)Aankoopvoorwaarden (levnr, prodnr, aankoopprijs, levertermijn) • SELECT L.LEVNAAMFROM LEVERANCIER L, AANKOOPVOORWAARDEN V WHERE L.LEVNR = V.LEVNR AND V.PRODNR = ‘1174’;

  28. Select instructie met inner-join (voorbeeld 5) • Eventuele overbodige dubbels kunnen steeds met een DISTINCT-optie worden geëlimineerd. • Gevraagd: de namen van de leveranciers die tenminste één product kunnen leveren met een groene kleur. • het is mogelijk dat sommige leveranciers meerdere producten met een groene kleur kunnen leveren, maar in overbodige dubbels zijn we niet geïnteresseerdLeverancier (levnr, levnaam, ...)Aankoopvoorwaarden (levnr, prodnr, ...)Product (prodnr, ..., prodkleur) • SELECTDISTINCT L.LEVNAAMFROM LEVERANCIER L, AANKOOPVOORWAARDEN V, PRODUCT PWHERE L.LEVNR = V.LEVNR AND (V.PRODNR = P.PRODNR)AND (P.PRODKLEUR = ‘groen’);

  29. Select instructie met inner-join (voorbeeld 6) • Gevraagd: geef van alle producten waarvoor bestellingen zijn geplaatst, nummer, naam en totale bestelde hoeveelheid.Product (prodnr, prodnaam, …)Aankooporderregel (aonr, prodnr, bestelhoev) • SELECT P.PRODNR, P.PRODNAAM, SUM(AOR.BESTELHOEV)FROM PRODUCT P, AANKOOPORDERREGEL AOR WHERE P.PRODNR = AOR.PRODNRGROUP BY AOR.PRODNR;

  30. Select instructie met outer-join • Met de inner-join zullen rijen die het systeem niet kan samenvoegen, omdat er voor de join-kolommen geen overeenstemmende waarden worden gevonden, niet aanwezig zijn in het eindresultaat. • Met de outer-join constructie kan dit probleem worden opgelost. • left-outer-join: elke rij uit de linkertabel wordt behouden in het eindresultaat en - zonodig – aangevuld met null-waarden voor de kolommen die uit de andere tabel komen. • right-outer-join: elke rij uit de rechtertabel wordt behouden • full-outer-join: elke rij uit beide tabellen wordt behouden

  31. Select instructie met left-outer-join (voorbeeld 1) • Gevraagd: geef nummer, naam en status van alle leveranciers, eventueel tezamen met de nummers van de producten die zij leveren en de aankoopprijs die ze hiervoor aanrekenen.Leverancier (levnr, levnaam, ..., levstatus)Aankoopvoorwaarden (levnr, prodnr, aankoopprijs, ...) • SELECT L.LEVNR, L.LEVNAAM, L.LEVSTATUS, V.PRODNR, V. AANKOOPPRIJSFROM LEVERANCIER AS L, LEFT OUTER JOINAANKOOPVOORWAARDEN AS V ON (L.LEVNR = V. LEVNR);

  32. Select instructie met left-outer-join (voorbeeld 2) • Gevraagd: geef van alle producten nummer, naam en totale bestelde hoeveelheid, ook indien er voor een product momenteel geen bestellingen uitstaan.Product (prodnr, prodnaam, …)Aankooporderregel (aonr, prodnr, bestelhoev) • SELECT P.PRODNR, P.PRODNAAM, SUM(AOR.BESTELHOEV) AS SOM FROM PRODUCT AS P, LEFT OUTER JOIN AANKOOPORDERREGEL AS AOR ON (P.PRODNR = AOR.PRODNR) GROUP BY AOR.PRODNR;

  33. Nested queries • Als er in een select-instructie geneste select-blokken voorkomen ontstaat er een zogeheten nested query met subquery’s. Het begin van een select-blok wordt met een SELECT component aangegeven. SELECT ... FROM ... WHERE ... (SELECT ... FROM ... WHERE ... ); Outer block Inner block

  34. Scalaire subqueries (voorbeeld 1) • In de where-component van een select-instructie mag als conditie worden gespecificeerd dat de waarde van een gewone expressie (bv. een kolomnaam) vergeleken moet worden met de waarde die door uitvoering van een subquery wordt afgeleverd. • Gevraagd: geef de naam van leveranciers bij wie een aankooporder met een bepaald nummer is geplaatstLeverancier (levnr, levnaam, levadres, levplaats, levstatus)Aankooporder (aonr, aodatum, levnr) • SELECT LEVNAAM FROM LEVERANCIER WHERE LEVNR = (SELECT LEVNR FROM AANKOOPORDER WHERE AONR = ‘1079’);

  35. Scalaire subqueries (voorbeeld 2) • Gevraagd: geef nummer en naam van elk product waarvan de voorraadhoeveelheid hoger is dan die van een bepaald productProduct (prodnr, prodnaam, …, hoev_in_voorr) • SELECT PRODNR, PRODNAAM FROM PRODUCT WHERE HOEV-IN-VOORR > (SELECT HOEV-IN-VOORR FROM PRODUCT WHERE PRODNR = ‘0117’);

  36. Tabel subqueries (voorbeeld 1) • Select-instructies met IN operator en tabelexpressie. • in de WHERE component van een outer select-blok van een select-instructie mag een IN operator worden gebruikt, gevolgd door een nieuw (inner) select-blok die een tabel-subquery mag zijn. • Gevraagd: geef de namen van leveranciers die een specifiek product kunnen leverenLeverancier (levnr, levnaam, ..., levstatus)Aankoopvoorwaarden (levnr, prodnr, aankoopprijs, ...) • SELECT LEVNAAM FROM LEVERANCIER WHERE LEVNR IN (SELECT LEVNR FROM AANKOOPVOORWAARDEN WHERE PRODNR = ‘0117’);

  37. Tabel subqueries (voorbeeld 2) • Gevraagd: geef de namen van leveranciers die tenminste één product kunnen leveren met een groene kleurLeverancier (levnr, levnaam, ...)Aankoopvoorwaarden (levnr, prodnr, ...)Product (prodnr, ..., prodkleur) • SELECT LEVNAAM FROM LEVERANCIER WHERE LEVNR IN (SELECT LEVNR FROM AANKOOPVOORWAARDEN WHERE PRODNR IN (SELECT PRODNR FROM PRODUCT WHERE PRODKLEUR = ‘GROEN’));

  38. Tabel subqueries (voorbeeld 3) • Gevraagd: geef de namen van de producten die zowel door de leverancier met nummer ’01’ als door de leverancier met nummer ’23’ kunnen worden geleverdAankoopvoorwaarden (levnr, prodnr, ...)Product (prodnr, prodnaam, ..., prodkleur) • SELECT PRODNAAMFROM PRODUCTWHERE PRODNR IN (SELECT PRODNRFROM AANKOOPVOORWAARDENWHERE LEVNR = ’05’)AND PRODNR IN (SELECT PRODNRFROM AANKOOPVOORWAARDEN WHERE LEVNR = ’23’);

  39. Gecorreleerde subqueries • In alle voorgaande gevallen kon de subquery (de inner-select) volledig geëvalueerd worden vooraleer met de verwerking van een outer select-blok werd gestart. • met een gecorreleerde subquery is dat niet zo. • Men spreekt van een gecorreleerde subquery wanneer een inner select-blok een kolom bevat die behoort tot een tabel die in een ander outer select-blok is gespecificeerd • de subquery moet telkens opnieuw worden geëvalueerd voor elke rij van de tabel die in het outer select-blok aanwezig is.

  40. Gecorreleerde subqueries (voorbeeld 1) • Gevraagd: geef de nummers van de producten waarvoor er tenminste twee bestellingen uitstaan.Product (prodnr, prodnaam, …)Aankooporderregel (aonr, prodnr, bestelhoev) • SELECT P.PRODNRFROM PRODUCT P WHERE 1 < (SELECT COUNT (*) FROM AANKOOPORDERREGEL AOR WHERE P.PRODNR = AOR.PRODNR);

  41. Gecorreleerde subqueries (voorbeeld 2) • Gevraagd: geef van de leveranciers die producten kunnen leveren tegen een prijs die lager ligt dan de gemiddelde prijs van dat product, nummer en naam, tezamen met nummer en naam van de betreffende producten, de prijs die door de leverancier wordt aangerekend en de levertermijn.Leverancier (levnr, levnaam, ...)Aankoopvoorwaarden (levnr, prodnr, aankoopprijs, levertermijn)Product (prodnr, prodnaam, ... ) • SELECT L.LEVNR, L.LEVNAAM, P.PRODNR, P.PRODNAAM, V1.AANKOOPPRIJS, V1.LEVERTERMIJN FROM LEVERANCIER L, AANKOOPVOORWAARDEN V1, PRODUCT PWHERE L.LEVNR = V1.LEVNR AND V1.PRODNR = P.PRODNRAND V1.AANKOOPPRIJS < (SELECT AVG(AANKOOPPRIJS)FROM AANKOOPVOORWAARDEN V2WHERE P.PRODNR = V2.PRODNR) ORDER BY L.LEVNR;

  42. Gecorreleerde subqueries (voorbeeld 3) • Gevraagd: geef de drie hoogste productnummers uit de producttabel. • Redenering: • het hoogste productnummer: er is geen enkel product met hoger nummer • het tweede hoogste productnummer: er is één product met een hoger nummer • het derde hoogste productnummer: er zijn twee producten met een hoger nummer. • Werkwijze: Voor ieder product tellen wij het aantal producten met een hoger nummer; als dit aantal kleiner is dan 3, dan behoort het nummer tot de drie hoogste nummers. • Oplossing: SELECT P1.PRODNR FROM PRODUCT P1 WHERE 3 > (SELECT COUNT (*) FROM PRODUCT P2 WHERE P1.PRODNR < P2.PRODNR);

  43. Nested queries met ALL of ANY • In de WHERE component van het (outer) select-blok van een select-instructie mag een ALL operator of een ANY operator worden gebruikt, die gevolgd wordt door een (inner) select-blok. • de ALL operator genereert een ‘waar-antwoord’ als aan de conditie is voldaan voor alle waarden die, na uitvoering van de subquery, zijn verkregen. • als de subquery geen waarde oplevert, evalueert de gehele conditie als ‘waar’. • de ANY operator genereert een ‘waar-antwoord’ als aan de conditie is voldaan voor tenminste één van de waarden die, na uitvoering van de subquery, zijn verkregen. • als de subquery geen waarde oplevert, evalueert de gehele conditie als ‘niet-waar’ • Een conditie van de vorm =ANY(…) is equivalent aan het gebruik van de IN operator.

  44. Nested queries met ALL (voorbeeld 1) • Gevraagd: geef de namen van de leveranciers die de hoogste prijs aanrekenen voor een bepaald product.Leverancier (levnr, levnaam, ...)Aankoopvoorwaarden (levnr, prodnr, aankoopprijs, levertermijn) • Oplossing:SELECT LEVNAAMFROM LEVERANCIERWHERE LEVNR IN (SELECT LEVNRFROM AANKOOPVOORWAARDENWHERE PRODNR = ‘1245’AND AANKOOPPRIJS ≥ ALL(SELECT AANKOOPPRIJSFROM AANKOOPVOORWAARDENWHERE PRODNR = ‘1245’));

More Related