1. Learning SQL part 2

In dit tweede deel ga je leren om informatie uit de database te halen met behulp van een query. Je zult merken dat er uit zo'n kleine database nog heel wat informatie te halen valt!

 

Als het goed is heb je nu de ongeveer volgende database:

Merk op dat het invoeren bij ons een beetje slordig is gegaan. Wij hebben rechtstreeks van de website gekopieerd! Nu zitten we met hoofd en kleine letters door elkaar. Wellicht ben je zelf tegen andere problemen aangelopen (zie opdracht 5 in deel 1). Wij hebben je laten oefenen met het invoeren van gegevens in een database via phpMyAdmin.

We zullen de database ook af en toe veranderen, maar we zullen het je niet aandoen de databases telkens opnieuw te laten invoeren. In het vervolg zullen we een database invoeren met behulp van een query. Dat gaat een stuk sneller.  Om dit te oefenen gaan we de gemaakte tabel verwijderen en vervolgens gaan we de tabel opnieuw aanmaken. Op die manier werken we tevens allemaal met dezelfde tabel.

Verwijder de tabel als volgt: selecteer de database in de tab Structure. Selecteer in het drop-down menu Drop. 

Je krijgt nu de volgende vraag:

Klik op Yes and vervolgens wordt de volgende query uitgevoerd:

 

DROP TABLE `astronauts`;

 

2. Een nieuwe tabel invoeren via het query-venster

Je database heeft momenteel geen tabellen, maar daar gaan we snel wat aan doen. Hieronder krijg je de query van je nieuw in te voeren tabel. Hierbij gooien we de oude database, mocht een eerdere student of leerling deze al hebben aangemaakt, meteen weg. Voer onderstaande query nog niet uit maar lees eerst de uitleg hoe je dat moet doen.

 

DROP DATABASE IF EXISTS nasa;

CREATE DATABASE nasa;
 
CREATE TABLE IF NOT EXISTS nasa.astronauts (
  id int(4) NOT NULL AUTO_INCREMENT,
  firstname varchar(75) NOT NULL,
  lastname varchar(75) NOT NULL,
  nationality varchar(50) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;


INSERT INTO nasa.astronauts (id, firstname, lastname, nationality) VALUES
(1, 'Joseph', 'Acaba', 'United States'),
(2, 'Michael', 'Barratt', 'United States'),
(3, 'Christopher', 'Cassidy', 'United States'),
(4, 'Michael', 'Fossum', 'United States'),
(5, 'Scott', 'Kelly', 'United States'),
(6, 'Anne', 'McClain', 'United States'),
(7, 'Rex', 'Walheim', 'United States'),
(8, 'Frank', 'Winne, de', 'Belgium'),
(9, 'André', 'Kuipers', 'Netherlands'),
(10, 'Luca', 'Parmitano', 'Italy'),
(11, 'Sergei', 'Krikalev', 'Russia'),
(12, 'Elena', 'Serova', 'Russia'),
(13, 'Yurchikhin', 'Nikolayevich', 'Russia');

Het eerste deel van de query noemen we een constraint. Hier blijven we even bij stilstaan en we zullen de regel één voor een uitleggen in de navolgende afbeelding.

 

We gaan hier opnieuw de database mee maken.

Op de eerste plaats ga je werken met het queryvenster. Wij kiezen het pop-up queryvenster.

 Voer de constraint in, in het queryvenster.

 Je kunt zien dat de query is uitgevoerd.

 Op dezelfde wijze kun je nu ook de astronauten opnieuw invoeren, met de juiste data.

Controleer nu eerst of de database weer terug is gezet.

 

3. Het SELECT statement

Er zijn verschillende manieren om uit de gemaakte kleine tabel informatie tevoorschijn te halen. Hiervoor wordt het SELECT statement gebruikt. De syntax van het SELECT statement is als volgt:

SELECT ,
FROM ;

 

 Op de sql_select pagina van W3schools wordt het SELECT statement verder uitgelegd en kun je het uitproberen. In de les daarna wordt het DISTINCT statement uitgelegd. Dit statement houdt in dat ieder record met dezelfde waarde  in dezelfde kolom waarvoor het DISTINCT statement geldt maar één keer wordt getoond.

4. De informatie filteren met WHERE

Met de WHERE clause kun je een voorwaarde stellen aan je SELECT statement. De syntax is als volgt:

SELECT ,
FROM
WHERE  ;

Op de sql_where pagina van W3Schools vind je voorbeelden om uit te proberen.

 

Operators

Op dezelfde pagina vind je een lijst van operators. In de opdracht ga je niet alleen de = operator uitproberen, maar ook de groter-of-gelijk-dan operator.

 

 

5. AND & OR Operators

Het verschil tussen de AND en de OR operator is als volgt. Bij de AND operator moeten beide condities waar zijn. Bij de OR operator moet één van beide waar zijn (beide operators mag ook).

 

Op de sql_and_or pagina van W3Schools vind je voorbeelden om uit te proberen. Bij opdracht 4 kun je kijken naar het combineren van AND en OR.

6. De informatie ordenen

Als je met behulp van het SELECT statement de informatie kunt ophalen is ook vaak het ordenen van de informatie van belang. Dit kan op verschillende manieren. Je kunt bijvoorbeeld een lijst op alfabet beginnen al of niet op- of aflopend. Je kunt ook eerst op de eerste kolom  ordenen en daarna kolom 2. In opdracht 6.1 ga je dat concreet doen door het sleutelwoord ORDER BY toe te voegen aan je query.

De syntax van ORDER BY is als volgt.

SELECT ,
FROM

ORDER BY ASC|DESC, ASC|DESC;

Op de sql_orderby van W3Schools vind je voorbeelden om uit te proberen. Merk op dat het mogelijk is om twee kolommen te gebruiken om te sorteren. 

7. Een zoekfunctie maken

Met het SELECT statement en de LIKE operator is het  mogelijk om te zoeken.  De syntax is als volgt:

SELECT
FROM
WHERE LIKE <pattern>;

 

Op de sql_like pagina van W3Schools vind je voorbeelden om uit te proberen. Je kunt ook wildcards gebruiken zoals je kunt zien op de sql_wildcards pagina.

 

8. Handige functies

Als laatste laten we nog een handige functie zien. Op W3Schools vind je allerlei voorbeelden en oplossing. Je kunt ook zoeken op het internet om een bepaalde oplosslng te vinden. Probeer nu de drie vragen van de opdracht op te lossen.

9. Oefenvragen op de fietsdatabase

Toegang tot de database staat in de volgende les. De vragen die je hierop kunt loslaten zijn:

1. Vindt het aantal gestolen fietsen uit Rotterdam waar een A in het merk voorkomt. Antwoord 879
2. Geef de query die een lijst van unieke straten van Capelle aan den IJssel weergeeft. Antwoord: 69.
3. Hoeveel bromfietsen zijn er gestolen tussen april en juni (inclusief). Antwoord: 233
4. Hoeveel fietsen zijn er in de hele regio gestolen tussen 18.00 en 22.00 uur? Antwoord: 209
5. In welke maand zijn de meeste scooters gestolen. Antwoord In juni zijn er 11 scooters gestolen.
6. Geef een overzicht van fietsdiefstallen per weekdag.
7. Welk voorval heeft het grootste voorval nummer. Antwoord: 2013191793-1
8. Hoeveel scooters zijn er gestolen in de periode januari tot en met april? Antwoord: 25, met de opmerking dat 12 er ook bij kan zitten als je de query goed hebt. Dat komt omdat alles in een VARCHAR staat.
9. Fietsen worden op iedere dag gestolen, van zondag t/m zaterdag. Bepaal op welke weekdag de meeste fietsen in Rotterdam zijn gestolen en geef van die dag het aantal. Je houdt hiervoor de kolom Begin_dagsoort aan. Antwoord 213
10. Geef de query die het aantal unieke plaatsen telt waar bromfietsen zijn gestolen. Antwoord: 21
11. Door GROUP BY  en COUNT in je query te gebruiken kun je het aantal fietsdiefstallen per plaats zien in één overzicht. Welke plaats staat op de zesde positie als het gaat om het aantal fietsdiefstallen? Antwoord: RIDDERKERK
12. In welke straat van alle plaatsen worden de meeste diefstallen gepleegd (dus fietsen, bromfietsen, scooters etc.)? Antwoord: STATIONSWEG
13. Geef de query waarbij je de merken vindt met zowel een a als een b in de naam. Antwoord: 20 merken.
14. Geef het kleinste voorvalnummer. Antwoord 2013000499-1
15. Geef alle diefstallen van de volgende wijken: 87 FEIJENOORD, 01 KOELE NACHT, 01 RIDDERKERK-CENTRUM. Gebruik een slimme oplossing (zie W3Schools). Antwoord 52.

10. Toegang database

De database is te bereiken via hoekschlyceum-server.nl/phpmyadmin. Nadat je de link hebt aangeklikt verschijnt er een pop-up waarin je de volgende gegevens plaatst:Username: admin_fiets Password: tB3RTkFz.

Het examen vind je hier.

11. antwoorden op de oefenvragen

Dit onderdeel is uitgeschakeld door de docent.