I denne øvelse skal du benytte sproget SQL (Structured Query Language)
til at oprette tabeller og stille avancerede forespørgsler til din
MySQL-database på maskinen mysql.itu.dk. Ugens opgavesæt
øver aggregerede forespørgsler, primær-nøgler og forskellige datatyper
(som f.eks. datetime).
Opgavesættet forudsætter at du har oprettet en database på ITU's
MySQL server mysql.itu.dk. Se kursushjemmesiden for
hvorledes dette gøres. Som for sidste opgavesæt er det en god ide at du følger MySQL-anvisningerne til kurset.
Opgaverne denne gang omhandler ikke hvorledes PHP-scripts kan forbinde til MySQL - dette omhandles i næste øvelse.
I denne opgave skal du skrive en række
SQL create table kommandoer til at skabe tabellerne
Student, Kursus, og Tilmelding
fra forelæsningen, inklusiv erklæring af primærnøgler.
Udfyld også databasen med mindst to ekstra kurser, tre ekstra studerende, samt fem ekstra tilmeldinger.
Gem dine SQL-kommandoer i en fil ps7/opg_a.txt i dit web-bibliotek
på ITU's web-server.
For at få mere interessante resultater i nedenstående delopgaver kan du tilføje
flere poster til tabellerne Student, Kursus og
Tilmelding.
Lav en forespørgsel på tabellen Kursus der viser
kursusnavn og lærer (men ikke kursusnummer) ordnet efter kursusnavn.
Gem din SQL-kommando samt resultatet af at udføre kommandoen i en fil
ps7/opg_b1.txt i dit web-bibliotek på ITU's web-server.
Lav en forespørgsel på tabellerne Kursus og
Tilmelding som giver en liste af navnene på de kurser som
der er tilmeldt studerende til.
Gem din SQL-kommando samt resultatet af at udføre kommandoen i en fil
ps7/opg_b2.txt i dit web-bibliotek på ITU's web-server.
Lav en forespørgsel på tabellerne Student,
Kursus og Tilmelding som viser studenternavn
og studienummer samt kursusnavn og lærer for alle studerende og for
alle kurser som den pågældende studerende er tilmeldt. Resultatet
skal være sorteret efter den studerendes navn.
Gem din SQL-kommando samt resultatet af at udføre kommandoen i en fil
ps7/opg_b3.txt i dit web-bibliotek på ITU's web-server.
Lav en forespørgsel på tabellerne
Student og Tilmelding som for hver
studerende (navn og studienummer) viser antal kurser den pågældende er
tilmeldt. Her skal bruges aggregering, altså count på
kursusnr og group by på snavn
og snr. Orden resultatet efter snavn.
Gem din SQL-kommando samt resultatet af at udføre kommandoen i en fil
ps7/opg_b4.txt i dit web-bibliotek på ITU's web-server.
Gentag forespørgslen fra opgave B4, men brug left join sådan at også studerende med nul kursustilmeldinger kommer med i resultatet. Udskriv resultatet.
Gem din SQL-kommando samt resultatet af at udføre kommandoen i en fil
ps7/opg_b5.txt i dit web-bibliotek på ITU's web-server.
I denne opgave bliver svarene både lange og brede, og det er derfor
nyttigt at udføre kommandoen pager less når du har startet
mysql.
Opret en tabel Weblog med felterne klient
(VARCHAR(70)), tidspunkt
(DATETIME) og webside
(VARCHAR(70)).
Importér data fra tekstfilen wwwstats.txt til tabellen
Weblog med "LOAD DATA LOCAL infile". For at
gøre dette skal du kopiere wwwstats.txt
til dit netværksdrev.
Bemærk: Filen indeholder registreringer fra en webserver i perioden 12/2 til 27/2 2000. Hver post angiver hvilken maskine (klient) der hentede en given webside, hvornår det skete, og hvilken webside der blev hentet. (NB: filen fylder over 900 KB).
Lav en forespørgsel der viser hvor mange poster der er i
Weblog i alt.
Gem din SQL-kommando i en fil
ps7/opg_c1.txt i dit web-bibliotek på ITU's web-server.
Lav en forespørgsel der viser, for hver værdi af webside,
hvor mange gange websiden har været hentet af en eller anden klient.
Der skal altså bruges COUNT og GROUP BY.
Lav den samme forespørgsel igen, men navngiv COUNT-feltet som
antal og orden resultatet efter aftagende antal.
Gem de to SQL-kommandoer i en fil ps7/opg_c2.txt i dit web-bibliotek på
ITU's web-server.
Opret en ny tabel Webside
med felter webside (VARCHAR(70)) og
antal (INT). Brug INSERT INTO Webside
... sammen med forespørgslen i delopgave C2 så resultatet
indsættes i tabellen Webside.
Gem SQL-kommandoen i en fil
ps7/opg_c3.txt i dit web-bibliotek på ITU's web-server.
Lav en ny forespørgsel, der trækker data fra tabellen
Webside oprettet i delopgave C3. Forespørgslen skal
vise, for hvert antal tilgange, hvor mange websider der blev tilgået
det antal gange.
Vink: Igen får du brug for COUNT og GROUP
BY. Eksempler fra resultatet: 389 af websiderne blev tilgået 1
gang i perioden, 446 af websiderne blev tilgået 2 gange, 149 af
websiderne blev tilgået 3 gange, osv., mens kun 1 af websiderne blev
tilgået 1171 gange.
Gem SQL-kommandoen i en fil ps7/opg_c4.txt i dit web-bibliotek på
ITU's web-server.
Martin Elsman - mael@itu.dk
Jonas Holbech - holbech@itu.dk