SQL / Systeemanalyse
Wie ben ik
Hans de Wit 44 jaar HBO BI in deeltijd gedaan Sinds 2008 werkzaam met BI / DWH med.hro.nl/wihan
SQL •
De gegevens in een database vormen de grondstof voor informatie
•
De informatie wordt opgevraagd in de taal met de naam:
Structured Query Language
SQL
SQL
SQL is een taal voor
•
invoeren
•
wijzigen
•
verwijderen
•
raadplegen
•
beveiligen
}
muteren
van gegevens
SQL •
SQL zegt alleen WAT er moet gebeuren, niet HOE het moet gebeuren
•
SQL is niet-procedureel; het zegt immers niet hoe iets moet gebeuren
•
SQL is 4GL (vierde generatie)
De rol van een dbms Database Database Management Systeem
Applicatie 3
Applicatie 1
Applicatie 2
DBMS
DATABASE
DBMS
APPLICATIE APPLICATIE APPLICATIE
Meer over SQL •
SQL is geschikt voor INTERACTIEF gebruik, dus voor rechtstreekse communicatie tussen gebruiker en DBMS
•
SQL kan in een groot aantal programmeertalen worden aangeroepen (EMBEDDED SQL)
•
De mogelijkheid om informatie op te vragen leidt tot intensief gebruik in oa Business Intelligence programma’s
Meer over SQL •
SQL is geschikt voor INTERACTIEF gebruik, dus voor rechtstreekse communicatie tussen gebruiker en DBMS
•
SQL kan in een groot aantal programmeertalen worden aangeroepen (EMBEDDED SQL)
•
De mogelijkheid om informatie op te vragen leidt tot intensief gebruik in oa Business Intelligence programma’s
Kiezen voor een DBMS •
Het maakt vor deze cursus niet zoveel uit welk DBMS we gebruiken
•
Bekende DBMS'sen zijn ORACLE, SQL Server, Informix, Sybase, Gupta, DB2, Interbase, MS JetSQL
•
Het dbms MySQL is populair maar minder geschikt voor ons doel
Enkele veelgebruikte datatypen
getal datum
tekst
Datatypen in Microsoft JetSQL
•
numeriek gegeven
26 26.33
•
tekstueel gegeven
'aap' 'broodje aap'
•
datum
Engelstalig
eigenwijs
#5-9-2003# #11 october 2007#
Manipulaties op één tabel Onze database beschikt voorlopig over één tabel die BOEKEN heet. De eenvoudigste SQL-instructie luidt: SELECT * FROM boeken;
Betekenis: laat alles uit de tabel BOEKEN zien
BOEKEN BOEKNR 2.111.1 2.111.2 2.111.4 2.111.6 2.112.0 2.112.1 2.112.3 2.112.4 2.112.5 ---
numeriek type TITEL Word in twaalf dagen WordPerfect 9 Xtal Reports de luxe Access voor dummies Haal het onderste uit Excel Brieven schrijven in Word Quicksmith 2.0 Inleiding in databases SQL en Access ---
tekstuele typen
UITGEVER MEU BB MEU BB AS AS BB MEU KLW ---
PRIJS € 22,50 € 29,50 € 32,90 € 32,90 € 39,50 € 35,00 € 79,50 € 89,90 € 72,50 ---
Projectie SELECT titel FROM boeken; titel Word in twaalf dagen WordPerfect 9 Xtal Reports de luxe Access voor dummies Haal het onderste uit Excel Brieven schrijven in Word ---
Kolomopschriften
(1)
SELECT * FROM boeken; BOEKNR
TITEL
UITGEVER
2.111.1
Word in twaalf dagen
MEU
---
---
---
SELECT titel FROM boeken; titel Word in twaalf dagen ---
PRIJS € 22,50 ---
Kolomopschriften
(2)
SELECT TiTel FROM boeken; TiTel Word in twaalf dagen ---
Meerdere kolommen selecteren SELECT boeknr, titel, prijs FROM boeken; boeknr
titel
prijs
2.111.1
Word in twaalf dagen
€ 22,50
2.111.2
WordPerfect 9
€ 29,50
2.111.4
Xtal Reports de luxe
€ 32,90
2.111.6
Access voor dummies
€ 32,90
2.112.0
Haal het onderste uit Excel
€ 39,50
---
---
---
Kolomalias SELECT boeknr, boeknr FROM boeken; Expr1000
boeknr
2.111.1
2.111.1
---
---
SELECT boeknr AS piet, boeknr FROM boeken; piet
boeknr
2.111.1
2.111.1
---
---
De naam van de kolommen is onbelangrijk; het gaat om de gegevens zelf!
Selectie
(1)
SELECT * FROM boeken WHERE uitgever = 'MEU'; BOEKNR
TITEL
UITGEVER
PRIJS
2.111.1
Word in twaalf dagen
MEU
€ 22,50
2.111.4
Xtal Reports de luxe
MEU
€ 32,90
2.112.4
Inleiding in databases
MEU
€ 89,90
2.112.6
SPSS voor schoolgebruik
MEU
€ 35,00
2.113.0
Delphi unleashed
MEU
€ 89,50
2.113.2
Visual Cobol
MEU
€ 51,40
Selectie
(2)
SELECT boeknr, prijs FROM boeken WHERE prijs = 35;
boeknr
prijs
2.112.1
€ 35,00
2.112.6
€ 35,00
Selectie
(3)
SELECT titel FROM boeken WHERE titel > 'Quiz'; titel Word in twaalf dagen WordPerfect 9
Xtal Reports de luxe SQL en Access SPSS voor schoolgebruik Visual Cobol
De operator LIKE
(1)
SELECT * FROM boeken WHERE titel LIKE 'A%';
BOEKNR
TITEL
UITGEVER
2.111.6
Access voor dummies
BB
€ 32,90
2.113.3
Alles over het Internet
BB
€ 49,00
Betekenis: eerste letter is de letter A en dan een willekeurig aantal letters (mag ook geen letters zijn)
PRIJS
De operator LIKE
(2)
SELECT titel FROM boeken WHERE titel LIKE '%s'; titel Access voor dummies Inleiding in databases SQL en Access Fractal graphics
Betekenis: willekeurig aantal letters (mag ook geen letters zijn) en als laatste letter een s
De operator LIKE
(3)
SELECT titel FROM boeken WHERE titel LIKE '%oo%'; titel Access voor dummies SPSS voor schoolgebruik Powertools voor dBase IV
Betekenis: twee aaneengesloten o-tjes
De operator LIKE
(4)
SELECT titel FROM boeken WHERE titel LIKE '%o%o%'; titel Access voor dummies SPSS voor schoolgebruik Powertools voor dBase IV Visual Cobol
Betekenis: twee o-tjes is voldoende (hoeven niet aaneengesloten te zijn maar mag wel)
De operator LIKE
(5)
SELECT * FROM boeken WHERE titel LIKE '__l%'; BOEKNR
TITEL
UITGEVER
PRIJS
2.112.4
Inleiding in databases
MEU
€ 89,90
2.112.5
SQL en Access
KLW
€ 72,50
2.113.0
Delphi unleashed
MEU
€ 89,50
2.113.3
Alles over het Internet
BB
€ 49,00
Betekenis: derde letter van de titel moet de letter l zijn
De operator LIKE
(6)
SELECT * FROM boeken WHERE titel LIKE '%e_'; BOEKNR
TITEL
UITGEVER
2.111.1
Word in twaalf dagen
MEU
€ 22,50
2.111.6
Access voor dummies
BB
€ 32,90
2.112.0
Haal het onderste uit Excel
AS
€ 39,50
2.112.4
Inleiding in databases
MEU
€ 89,90
2.113.0
Delphi unleashed
MEU
€ 89,50
2.113.3
Alles over het Internet
BB
€ 49,00
Betekenis: de op één na laatste letter moet de letter e zijn
PRIJS
Formuleren van queries
(1)
Langzaam maar zeker groeit het aantal lettertekens van de query. Vooral bij langere queries is het zinvol om de query over meerdere regels te verdelen. Dat is overzichtelijker.
Formuleren van queries
(2)
In plaats van
SELECT titel FROM boeken WHERE uitgever = 'AS'; schrijven we
SELECT titel FROM boeken WHERE uitgever = 'AS';
De layout is totaal onbelangrijk; net zo min als het gebruik van hoofd- of kleine letters.
Formuleren van queries
(3)
SELECT titel FROM boeken WHERE uitgever = 'AS'; seLECT Titel from BOEKEN WHEre uitgeVEr = 'AS'; seLECT Titel from BOEKEN WHEre uitgeVEr = 'AS';
De logische operator AND SELECT * FROM boeken WHERE uitgever = 'AS' AND prijs > 50; BOEKNR
TITEL
UITGEVER
2.112.9
Fractal graphics
AS
€ 56,90
2.113.1
Powertools voor dBase IV
AS
€ 65,90
Betekenis van AND: aan beide voorwaarden moet voldaan zijn
PRIJS
De logische operator OR SELECT * FROM boeken WHERE uitgever = 'AS' OR uitgever = 'MEU'; BOEKNR
TITEL
UITGEVER
PRIJS
2.111.1
Word in twaalf dagen
MEU
€ 22,50
2.111.4
Xtal Reports de luxe
MEU
€ 32,90
2.112.0
Haal het onderste uit Excel
AS
€ 39,50
2.112.1
Brieven schrijven in Word
AS
€ 35,00
2.112.4
Inleiding in databases
MEU
€ 89,90
2.112.6
SPSS voor schoolgebruik
MEU
€ 35,00
---
---
---
Betekenis van OR: aan minstens één van beide voorwaarden moet voldaan zijn (aan allebei mag ook)
---
De logische operator NOT SELECT * FROM boeken WHERE NOT uitgever = 'AS'; BOEKNR
TITEL
UITGEVER
PRIJS
2.111.1
Word in twaalf dagen
MEU
€ 22,50
2.111.2
WordPerfect 9
BB
€ 29,50
2.111.4
Xtal Reports de luxe
MEU
€ 32,90
2.111.6
Access voor dummies
BB
€ 32,90
2.112.3
Quicksmith 2.0
BB
€ 79,50
---
---
---
---
Betekenis van NOT: aan de geformuleerde voorwaarde moet juist níet voldaan zijn
Combinaties van operatoren SELECT * FROM boeken WHERE NOT prijs > 40 AND prijs < 60;
SELECT * FROM boeken WHERE NOT (prijs > 40 AND prijs < 60);
De queries hebben een verschillend resultaat! Gebruik in geval van twijfel altijd haakjes om de voorrang te regelen.
De operator IN SELECT * FROM boeken WHERE uitgever IN ('AS', 'BB');
Is hetzelfde als
SELECT * FROM boeken WHERE uitgever='AS' OR uitgever= 'BB'; Biedt vooral voordelen bij méér waarden in de verzameling.
De operator BETWEEN SELECT * FROM boeken WHERE prijs BETWEEN 30 AND 40;
Is hetzelfde als
SELECT * FROM boeken WHERE prijs>=30 AND prijs<=40; Formuleert alleen 'lekkerder'.
