SQL Aantekeningen 3 Maarten de Rijke
[email protected] 22 mei 2003 Samenvatting In deze aflevering: het selecteren van tuples, operaties op strings, en aggregatie functies. Verder kijken we naar iets complexere queries, en we ihb naar operaties op verzamelingen van resultaten, en het group by commando.
4
Meer Queries
4.1
Het Selecteren van Tuples
Tot nu toe hebben we ons gericht op het selecteren van (een aantal) attributen van alle tuples uit een tabel. Als we alleen tuples willen krijgen die aan bepaalde voorwaarden voldoen, dan moeten we de where clausule gebruiken. Eenvoudige vorwaarden zijn gebaseerd op vergelijkingen tussen getallen of strings. In een where clausule kunnen we complexe voorwaarden verkrijgen door eenvoudige condities te combineren met behulp van de logische connectieven and, or, en not. Voorwaarden kunnen ook pattern matching operaties bevatten, en zelfs subqueries. Voorbeeld. Geef de functie en het salaris van alle werknemers waarvan de manager nummer 7698 of 7566 heeft en die meer dan 1500 verdienen: select JOB, SAL from EMP where (MGR = 7698 or MGR = 7566) and SAL > 1500; Voor alle data typen zijn de vergelijkingsoperaties =, != of <>, <, >, <=, => toegestaan in de voorwaarden van een where clausule. Verdere vergelijkingsoperaties zijn onder andere • Voorwaarden op verzamelingen:
[not] in () select * from DEPT where DEPTNO in (20,30); 8
• Null waarden: is [not] null Met andere woorden: een tuple kan alleen geselecteerd worden als er wel/geen gedefinieerde waarde bestaat voor deze kolom. select * from EMP where MGR is not null; NB. de operaties = null en !=null zijn niet gedefinieerd! • Domein voorwaarden: [not] between and select EMPNO, ENAME, SAL from EMP where SAL between 1500 and 2500; select ENAME from EMP where HIREDATE between ’02-APR-81’ and ’08-SEP-81’;
4.2
Operaties op Strings
Om een attribuut met een string te kunnen vergelijken, moeten we de string in aanhalingstekens geven. Bijvoorbeeld: where LOCATION = ’DALLAS’. De operator like is een krachtige operator voor pattern matching. Doorgaans wordt like gebruikt met twee speciale karakters: het procentteken % (ook wel ‘wild card’), en het (ook wel ‘position marker’). Bijvoorbeeld, om alle tuples te selecteren in DEPT die twee Cs bevatten in de naam van de afdeling, gebruiken we de voorwaarde where DNAME like ’%C%C%’. Het procentteken betekent dat iedere (sub)string hier is toegestaan, zelfs de lege. Het streepje staat voor precies e´ e´ n karakter. Dus de voorwaarde where DNAME like ’%C C%’ verlangt dat er precies e´ e´ n karakter voorkomt tussen de twee Cs. Om voor ongelijkheid te testen, gebruiken we de not clausule. Verdere string operaties zijn onder andere: • upper(<string>) neemt een string en zet alle letters om in hoofdletters; • lower(<string>) zet alle letters om in kleine letters; • initcap(<string>) zet de eerste letter van ieder woord in <string> om in een hoofdletter; 9
• length(<string>) geeft, inderdaad, de lengte van de string • substr(<string>, n [, m]) knipt een m karakter stuk uit <string>, beginnend bij positie n; als m niet gespecificeerd is, wordt de rest van de string genomen. Bijvoorbeeld: substr(’DATABASE SYSTEMS’, 10, 7) geeft de string ’SYSTEMS’.
4.3
Aggregatiefuncties
Aggregatiefuncties zijn statistische functies zoals count, min, max, etc. Ze worden gebruikt om een enkele waarde te berekenen bij een verzameling van attribuut waarden van een kolom: • count—telt rijen Bijvoorbeeld: hoeveel tuples zijn er opgeslagen in de relatie EMP? select count(*) from EMP; • max—maximum waarde van een kolom • min—minimum van een kolom Bijvoorbeeld: geef het minimum en maximum salaris: select min(SAL), max(SAL) from EMP; • sum—berekent de som van waarden (alleen van toepassing voor het data type number) Bijvoorbeeld: sommeer alle salarissen van de werknemers in afdeling 30 select sum(SAL) from EMP where DEPTNO = 30; • avg—berekent het gemiddelde voor een kolom (alleen van toepassing voor het data type number). NB. avg, min en max laten tuples met een null waarde in de gespecificeerde kolom buiten beschouwing, maar count telt null waardes wel mee.
4.4
Operaties op Verzamelingen van Resultaten
Het is soms nuttig om resultaten van twee of meer queries in e´ e´ n enkel resultaat te combineren. SQL ondersteunt drie operatoren op verzamelingen, en die hebben het volgende patroon: De verzamelingsoperatoren zijn 10
• union [all] geeft een tabel bestaande uit alle rijen die ofwel in het resultaat van voorkomen ofwel in het resultaat van voorkomen. Dubbelen worden automatische verwijderd, tenzij de clausule all wordt gebruikt. • intersect geeft alle rijen die voorkomen in de resultaten van en . • minus geeft alle rijen die in het resultaat van voorkomen, maar niet in het resultaat van . Voorbeeld. Neem aan dat we een tabel EMP2 hebben die dezelfde structuur en kolommen heeft als de table EMP. • De namen en nummers van alle werknemers in beide tabellen: select EMPNO, ENAME from EMP union select EMPNO, ENAME from EMP2; • Wernemers die in zowel EMP als EMP2 voorkomen: select * from EMP intersect select * from EMP2 ; • Werknemers die alleen in EMP voorkomen: select * from EMP minus select * from EMP2; De verzamelingsoperaties verlangen dat beide tabellen hetzelfde datatype hebben voor de kolommen waarop de operatie wordt toegepast.
4.5
Grouping
We hebben eerder gezien hoe aggregatiefuncties gebruikt kunnen worden om een enkele waarde te berekenen voor een kolom. Voor toepassingen is het vaak nodig om rijen die aan bepaalde voorwaarden voldoen, te groeperen, en daarna pas een aggregatiefunctie toe te passen op e´ e´ n kolom, voor elke groep afzonderlijke. Om dit mogelijk te maken, heeft SQL de clausule 11
group by Deze clausule moet na de where clausule komen, en moet verwijzen naar kolommen of tabellen die genoemd worden in de from clausule: select from where group by [having ]; De rijen die we krijgen uit de select clausule en die dezelfde waarden hebben voor , worden ‘gegroepeerd.’ Aggregatiefuncties die gespecificeerd worden in de select clausule worden vervolgens toegepast op elke groep afzonderlijk. Let op: alleen die kolommen die voorkomen in in kunnen zonder aggregatiefunctie worden opgenomen in de select clausule. Voorbeeld.
Voor iedere afdeling willen het minimum en maximum salaris verkrijgen:
select DEPTNO, min(SAL), max(SAL) from EMP group by DEPTNO; Rijen uit de tabel EMP worden op zo’n manier gegroepeerde dat alle rijen in een groep hetzelfde afdelingsnummer hebben. De aggregatiefuncties worden vervolgens toegepast op elk van die groepen. We krijgen zo het volgende resultaat: DEPTNO 10 20 30
MIN(SAL) 1300 800 950
MAX(SAL) 5000 3000 2850
Rijen die een groep vormen kunnen beperkt worden met behulp van de where clausule. Bijvoorbeeld, als we de conditie where JOB = ’CLERK’ toevoegen, dan worden alleen de relevante kolommen in de groep opgenomen. Merk nog op dat het niet toegestaan is om een andere kolom dan DEPTNO op te nemen zonder aggregatiefunctie in de select clausule, want dit is de enige kolom die genoemd wordt in de group by clausule. Als we eenmaal groepen gevormd hebben, kunnen we ook groepen verwijderen op grond van hun eigenschappen. Bijvoorbeeld, als ene groep minder dan drie rijen heeft. Dit soort condities wordt gespecificeerd met behulp van de having clausule. Net als voor de select clausule, geldt voor de having clausule dat alleen en aggregaties gebruikt kunnen worden. 12
Voorbeeld. Bepaal het minimum- en maximumsalaris van clerks voor iedere afdelingen met meer dan drie clerks. select DEPTNO, min(SAL), max(SAL) from EMP where JOB = ’CLERK’ group by DEPTNO having count(*) > 3; Merk op dat we een subquery kunnen opgeven in de having clausule; in het voorgaande voorbeeld, kan in plaats van 3 een subquery worden gespecificeerd. Een query met een group by clausule wordt op de volgende manier verwerkt: 1. Selecteer alle rijen die voldoen aan de conditie in de where clausule. 2. Vorm groepen uit deze rijen aan de hand group by clausule. 3. Vergeet groepen die niet voldoen aan de conditie in de having clausule. 4. Pas aggregatiefuncties toe op elke groep. 5. Geef de waarden weer voor de kolommen en aggregaties die genoemd worden in de select clausule.
13
