Transcript PowerPoint-presentatie

Querytraining
Eppo Drenth, CFB SIS HvA | Mark van der Molen, CFB SIS UvA
7 maart 2013
Agenda
09.30 Voorstelrondje
09.45 Introductie query’s en querytool
10.10 Zelfstandig opdrachten doen (opdrachten 1 en 2)
10.50 Uitleg datamodel
11.00 Zelfstandig opdrachten doen (opdrachten 3 t/m 6)
12.00 Lunch
12.45 Queryontwerp en prompts
13.30 Zelfstandig opdrachten doen (opdrachten 7 en 8)
14.00 Outer joins
14.15 Zelfstandig opdrachten doen (opdracht 11)
14.30 Expressies
14.45 Zelfstandig opdrachten doen (opdrachten 9 en 10)
16.00 Einde
Querytraining | 7 maart 2013
2
Databases
 SIS maakt gebruik van een relationele database (Oracle).
 De gegevens zijn opgeslagen in tabellen met daarin rijen,
in SIS heten tabellen Records.
 Een rij in de tabel bevat altijd een unieke sleutel (primary key),
dit kan een enkel veld zijn maar ook een combinatie van velden.
 Tabellen kunnen onderling gekoppeld zijn door middel van relaties.
 Relaties lopen over de zgn. verwijzende sleutel (foreign key).
 Een veld is van een bepaald datatype (teken, tekst, numeriek, datum, etc).
 Sommige datatypes hebben een maximale lengte.
Querytraining | 7 maart 2013
3
Datatypes
 Teken (Oracle: VARCHAR2):
normale tekstvelden
max. aantal karakters is bepaald (van 1 tot 4000)
 Tekst (CLOB/BLOB):
lange tekstvelden, HTML, plaatjes
in principe onbeperkte lengte
 Getal (NUMERIC):
getallen, met of zonder teken, met of zonder decimalen
Getal 3.2: positief getal, 3 posities voor, 2 na komma, totale lengte is dus 5
GmT 3.2: positief of negatief getal
 Datum (DATE):
datum en/of tijd
Querytraining | 7 maart 2013
4
SQL

Structured Query Language.

DDL: Data Definition Language, aanmaken en wijzigen van o.a. tabelstrukturen.

DAL: Data Access Language, selecteren en manipuleren van gegevens in tabellen.

In principe database onafhankelijk, maar er zijn database specifieke
verschillen, voornamelijk bij het gebruik van functies.

De meeste keywords zijn dus bijvoorbeeld ook te gebruiken in Access, etc.

Verschillende statements:
SELECT: voor het selecteren van gegevens
UPDATE: voor het bijwerken van gegevens
INSERT: voor het toevoegen van gegevens
DELETE: voor het verwijderen van gegevens

In SIS is alleen het SELECT commando te gebruiken
Querytraining | 7 maart 2013
5
Opbouw van een query: Basis

Meest simpele vorm:
SELECT <kolom>
FROM <record>

Voorbeeld:
SELECT A.INSTITUTION, A.DESCR
FROM PS_INSTITUTION_TBL A

Alle records beginnen met PS of PS_. In de user interface zie je deze prefix niet.
Voorbeeld: het record INSTITUTION heet in de database PS_INSTITUTION.
Record aliassen: elk record dat in een query gebruikt wordt krijgt een alias,
dit is een letter (A t/m Z), we zijn dus beperkt tot maximaal 26 tabellen in één query.
Deze aliassen is om aan te geven uit welke tabel een kolom komt in het geval van
dezelfde kolomnamen in meerdere tabellen.
Hoofdletters: hoewel SQL niet case-sensitive is werken we in principe altijd met
hoofdletters. De query editor doet dit voor de meeste dingen al standaard voor je.


Querytraining | 7 maart 2013
6
Opbouw van een query: Criteria

SELECT <kolom>
FROM <record>
WHERE <voorwaarde>

Voorbeeld:
SELECT A.EMPLID , A.FIRST_NAME, A.NAME_ROYAL_PREFIX, A.LAST_NAME
FROM PS_PERSONAL_DATA A
WHERE A.EMPLID = ‘500132190'



Tussen enkele aanhalingstekens, dit doet SIS vaak zelf voor je.
Hoofdlettergevoelig
Datatype gevoelig, dit regelt SIS meestal wel voor je.
Querytraining | 7 maart 2013
7
Opbouw van een query: Criteria Operators
Simpel
gelijk aan
groter dan
in lijst
niet in lijst
kleiner dan
niet gelijk aan
niet groter dan
niet kleiner dan
tussen
niet tussen
zoals
niet zoals
= [waarde]
> [waarde]
IN (‘[waarde 1]’,’[waarde 2]’, ‘[etc]’)
NOT IN (‘[waarde 1]’,’[waarde 2]’, ..)
< [waarde]
<> [waarde]
<= [waarde]
>= [waarde]
BETWEEN [waarde 1] AND [waarde 2]
NOT BETWEEN [waarde 1] AND [waarde 2]
LIKE [waarde]
NOT LIKE [waarde]
T,G,D,X
T *,G,D
T,G,D
T,G,D
T*,G,D
T*,G,D
T*,G,D
T*,G,D
T*,G,D
T*,G,D
T
T
Geavanceerd
bestaat
bestaat niet
in boom
niet in boom
is null
is niet null
Bij ‘zoals’ en ‘niet zoals’ kan je de wildcards % en _ gebruiken.
%: meerdere tekens, _ exact één teken.
Datatypes: T teken, G getal, D datum, X tekst, bij * kan je onverwachte resultaten
krijgen.
Querytraining | 7 maart 2013
8
Opbouw van een query: Criteria
Meerdere criteria
•
EN (AND)
•
OF (OR)
•
EN NIET (AND NOT)
•
OF NIET (OR NOT)
•
Gebruik haakjes als je de volgorde wilt beïnvloeden
•
Voorbeeld:
SELECT A.EMPLID, A.BIRTHDATE, A.LAST_NAME
FROM PS_PERSONAL_DATA A
WHERE A.BIRTHDATE > TO_DATE('1980-01-01','YYYY-MM-DD‘)
AND A.LAST_NAME LIKE 'Jans%'
Querytraining | 7 maart 2013
9
Opbouw van een query: Criteria
Voorbeelden:
•
SELECT A.EMPLID , A.FIRST_NAME, A.NAME_ROYAL_PREFIX, A.LAST_NAME
FROM PS_PERSONAL_DATA A
WHERE A.EMPLID = ' 500132190 '

SELECT A.EMPLID , A.FIRST_NAME, A.NAME_ROYAL_PREFIX, A.LAST_NAME
FROM PS_PERSONAL_DATA A
WHERE A.LAST_NAME LIKE ‘Jans%'

SELECT A.EMPLID, A.BIRTHDATE
FROM PS_PERSONAL_DATA A
WHERE A.BIRTHDATE = TO_DATE('1980-01-01','YYYY-MM-DD')
OR A.LAST_NAME LIKE 'Jans%'
Querytraining | 7 maart 2013
10
Opbouw van een query
Sorteren
•
ORDER BY <kolomnaam1>, <kolomnaam2>, etc.
•
Standaard sortering is oplopend
•
Wil je aflopend sorteren gebruik dan DESC achter de kolomnaam
•
Voorbeeld:
SELECT A.EMPLID, A.BIRTHDATE, A.LAST_NAME
FROM PS_PERSONAL_DATA A
WHERE A.BIRTHDATE > TO_DATE('1980-01-01','YYYY-MM-DD')
AND A.LAST_NAME LIKE 'Jans%‘
ORDER BY A.LAST_NAME, A.FIRST_NAME
Querytraining | 7 maart 2013
11
En nu in SIS
Link S020TRA: http://tra-ws01.s020.sans-hosting.nl:8010/
Gebruiker-ID: QUERYCURSUS
Wachtwoord: query4hva
Rapportagehulpmiddelen > Query > Querybeheer
Querytraining | 7 maart 2013
12
Opdracht 1
• Maak een query die persoonsgegevens laat zien van alle studenten van
wie de achternaam met ‘Jans’ begint.
Opdracht 2
• Breid de query uit opdracht 1 uit met de naam van het land voluit.
Querytraining | 7 maart 2013
13
Het datamodel
 SIS bestaat uit heel erg veel records (honderden), de meeste records zijn
onderling verbonden met relaties.
Querytraining | 7 maart 2013
14
Opdracht 3
• Breid de query uit opdracht 2 uit met de studieprogramma’s van de
studenten.
Opdracht 4
• Breid de query uit opdracht 3 uit met de omschrijvingen van de
studieprogramma’s.
Opdracht 5
• Breid de query uit opdracht 4 uit met de omschrijvingen van de loopbaan.
Querytraining | 7 maart 2013
15
Opdracht 6
• Maak een nieuwe query met daarin een overzicht van de studenten die
ingeschreven staan op een studieactiviteit.
Querytraining | 7 maart 2013
16
Queryontwerp
 Een overzicht van de diplomeringsregels van de studenten van een
cohortgroep.
Querytraining | 7 maart 2013
17
Prompts
 Prompts zijn variabelen die bij het uitvoeren van de query door de
gebruiker ingevuld kunnen worden. Hiermee kan je criteria in een query
maken zodat een query algemeen gebruikt kan worden.
 Er zijn vier soorten prompts
1. Verplicht met tabelvalidatie: dit is een vrij invulveld met een lookuppopup en validatie.
2. Niet verplicht met of zonder tabellookup: idem maar dan zonder
validatie en optionele lookup-popup.
3. Ja/Nee: dit levert een checkbox op.
4. Xlat-tabel: alleen voor velden met een Xlat waarde, dit levert een
dropdown selectbox op, altijd verplicht.
Querytraining | 7 maart 2013
18
Opdracht 7
• Breid de query uit opdracht 6 uit met een prompt op de studieactiviteit.
Opdracht 8
• Breid de query uit opdracht 6 uit met lookup prompts op institution,
periode en studieactiviteit.
Querytraining | 7 maart 2013
19
Outer joins
 Tot nu toe zijn alle joins normale joins (zgn. inner joins). Deze geven
alleen resultaat terug als in beide tabellen waardes voorkomen.
 Het is echter mogelijk dat dit niet het geval is, bijvoorbeeld als van
een student geen land bekend is zal hij met een inner join tussen
PERSONAL_DATA en COUNTRY_TBL wegvallen uit de resultaten.
 De oplossing hiervoor is een outer join.
Querytraining | 7 maart 2013
20
Opdracht 11
• Maak een query die naam en het land van de studenten ‘500132190’ en
‘500647684’ ophaalt en toon hier de naam van het land voluit.
Querytraining | 7 maart 2013
21
Expressies
Functies in de query: ORACLE functies
• Meeste standaard Oracle functies worden ondersteund
• Bijvoorbeeld: UPPER, TO_CHAR, SUBSTR
http:// www.oradev.com/
http://www.techonthenet.com/oracle/functions/index.php
Afhankelijke logica (als-dan/if-then-else): ORACLE programmeerlogica
• Velden aan elkaar plakken, berekeningen, enz.
• Bijvoorbeeld: CONCAT, NVL, DECODE, CASE
Querytraining | 7 maart 2013
22
Expressies – Oracle functies
 TO_CHAR: datum/nummer formatteren
TO_CHAR(A.GRADE_DT,’DD-MONTH-YY’) geeft 01-May-12
 UPPER: hoofdletters
UPPER(‘Jansen’) geeft ‘JANSEN’
 LOWER: kleine letters
LOWER(‘Jansen’) geeft ‘jansen’
 REPLACE: vervangen binnen een tekstveld
REPLACE(A.NAME,’a,’o’). Als A.NAME = ‘Jansen’ geeft ‘Jonsen’
Querytraining | 7 maart 2013
23
Expressies – Oracle functies
 SUBSTR: teksten uit elkaar peuteren
SUBSTR(<veld>,<start>,[<lengte>])
A.NAME = ‘Jansen’
SUBSTR(A.NAME,1,1) geeft ‘J’
SUBSTR(A.NAME,2) geeft ‘ansen’
SUBSTR(A.NAME,-1,1) geeft ‘n’
 INSTR: de positie van een woord of karakter binnen een tekst
INSTR(<veld>,<zoektekst>,[<positie>],[<hoeveelste>])
INSTR('ab ab ab','ab') = 1
INSTR('ab ab ab','ab',1,2) = 4
INSTR('abcabcabcdef','de') = 7
Querytraining | 7 maart 2013
24
Expressies – Oracle functies
 Teksten aan elkaar plakken (concateneren)
Met de CONCAT functie: omslachtig, niet doen
Met de dubbele pipe: ||
A.VOORNAAM || A.ACHTERNAAM: PeterJansen
A.VOORNAAM || ‘ ’ || A.ACHTERNAAM: Peter Jansen
 Je kan functies ook nesten
REPLACE(REPLACE(TO_CHAR(TO_DATE('01-JAN-12','DD-MON-YY'),'DDMonth-YYYY','nls_date_language = Dutch'),' ',''),'-',' ')
Querytraining | 7 maart 2013
25
Expressies – Logica
 Logica met functies
NVL, DECODE
 Logica door code
CASE
Querytraining | 7 maart 2013
26
Expressies – Logica
 NVL: Vervangt een NULL of lege waarde door de opgegeven default
NVL(<veld>, <default>)
NVL(A.VOLGNR, 1): als A.VOLGNR leeg is wordt 1 gebruikt
 DECODE: Vertalen van een waarde in een andere waarde
DECODE(<veld>, <waarde>, <vertaalde waarde> [,…], [<default>])
DECODE(A.ENRL_ACTION_REASON, ’E’, ’Enrolled’, ’D’,’Dropped’, ’Waitlist’)
Querytraining | 7 maart 2013
27
Expressies – Logica
 CASE: als-dan logica
CASE
WHEN <voorwaarde> THEN <doe iets>
[…]
[ELSE]
END
 CASE
WHEN A.ENRL_STATUS_REASON = ‘D’ THEN ‘Dropped’
WHEN A.ENRL_STATUS_REASON = ‘W’ THEN ‘Waitlist’
WHEN A.ENRL_STATUS_REASON = ‘E’ THEN ‘Enrolled’
ELSE ‘Unknown status ’ || A.ENRL_STATUS_REASON
END
Querytraining | 7 maart 2013
28
Opdracht 9
• Maak een query die de voornaam en achternaam van de student voluit in
één veld geeft, voor alle studenten van wie de naam begint met ‘Berg’.
Opdracht 10
• Pas de query uit opdracht 9 aan zodat ook de tussenvoegsels correct in
het ene naamveld komen te staan.
Opdracht 11
• Pas de query uit opdracht 10 aan zodat ook de tussenvoegsels correct in
het ene naamveld komen te staan.
Querytraining | 7 maart 2013
29