729G28 Webbprogrammering och databaser

Laboration 1. SQL

Innehåll

Syfte
Redovisning
Förberedelser
Uppgifter

Syfte (översikt)

SQL är både DDL (Data Definition Language) och DML (Data Manipulation Language). DDL betyder att språket kan användas för att skapa, ändra och ta bort databaser, tabeller och vyer. DML betyder att man kan lägga in, ändra och ta bort data i tabellerna samt göra mer eller mindre komplexa sökningingar över data. Det krävs en del förberedelser (se nedan).

Redovisning

Denna labb redovisas genom att lämna in en två filer i Lisam. Det är viktigt att de namnges med ert gruppnummer ur Webreg enligt mönstret: gN_uppgifter.txt respektive gN_relationer.jpg där N är ert gruppnummer. Formatet på den sista får också vara pdf. Deadline finns angiven i TimeEdit.

Förberedelser

Läs kapitel 7-9 i kursboken. Speciellt sammanfattningen av SQL-kommandon i kapitel 9.1-9.7 kan vara nyttig. Ta även del av den SQL-tutorial som W3Schools erbjuder. Se schemasidan för exakt vilka delar som ingår i kursen.

OBS: Nedanstående instruktioner gäller för att göra labbarna i Linux-sal (SU-pul) eller via Thinlinc. Instruktioner för att labba på egen databasserver med XAMPP finns här.

Konto på databas-servern:
LiU tillhandahåller en databas-server som heter MariaDB (som är av samma typ som MySQL) där du som går kursen får ett databaskonto och en databas. De har båda samma namn som ditt LiU-ID men databaskontot har ett eget lösenord.

Kontrollera om du fått e-post med kontoinformation (lösenordet) för databashanteraren. Brevet har rubriken "MySQL database created" och skickas ut någon vecka in på terminen till alla som är anmälda till kursen. Om du läst en IDA-kurs där du använt en databas tidigare får du inget nytt brev utan ska använda samma.
Om du inte hittar något brev men vet att du är anmäld till kursen, eller har glömt lösenordet till ett gammalt databaskonto kan du begära ett nytt lösenord här. Det nya lösenordet skickas till din LiU-mail.

Starta databasklienten:
För att genomföra uppgifterna i denna labb ska du använda den textbaserade databasklienten "mysql" för att hantera databasen på servern.

Du startar den textbaserade databasklienten genom att först starta ett terminalfönster. (Se instruktionera för XAMPP ovan för hur du startar mysql-klienten i XAMPP på egen dator).
I terminalen använder du följande kommando:
mysql -h mariadb.edu.liu.se -u <LiU-ID> -p
Där du byter <LiU-ID> mot ditt eget LiU-ID.
Kommandot startar databasklienten mysql, kopplar upp den mot IDA:s databasserver för undervisningsbruk (mariadb.edu.liu.se) och startar inloggning på den med ditt kontonamn. Du kommer därefter att bli ombedd att om att ange ditt databaslösenord:
Enter password
Observera att det inte syns något alls i terminalen när du anger lösenordet, inte ens svarta prickar och cursorn rör sig inte. Om inloggningen lyckas får du följande utskrift:
Welcome to the MySQL monitor. Commands end with ; or \g. ... Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Det betyder att databasklienten mysql är redo att ta emot dina kommandon.
Du behöver börja med att ange vilken databas du vill arbeta med. Kom ihåg att du bara har en, den har samma namn som ditt LiU-ID:
mysql> use <LiU-ID>

Därefter kan du mata in dina SQL-kommandon. Ett tips är att ha en textfil (lämpligen den som ska redovisas) öppen i en textredigerare där man redigerar kommandon som sedan kopieras in i mysql-klienten. Notera att Ctrl-C avslutar mysql-klienten i vissa typer av terminalfönster. Kopiera därför med hjälpa av musen när du vill kopiera från klienten till textfilen (i vissa terminalfönster verkar ctrl-shift-c fungera som kopiering. Notera också att alla kommandon ska avslutas med ett semikolon. Om du hamnar i att den skriver:
->
beror det på att den väntar på ett semikolon. Om du vill använda autocomplete, dvs att <tab> gör att ett påbörjat kommando eller namn fylls i (om det är unikt), på samma sätt som i när du skriver linux/unix-kommandon, kan du prova att ge mysql-kommandot REHASH;.

Installera en databas att arbeta med:
Kommandot SOURCE filnamn gör att man läser in SQL-kommandon som är lagrade på en fil som heter filnamn. Om filnamn inte innehåller en sökväg antas filen ligga på i den mapp man startade mysql-klienten från. Kommandot SOURCE gör att de kommandon som står i filen genomförs och eventuella utskrifter från dem skrivs ut i terminalfönstret.

Filen Johnson_Brothers.zip innehåller två filer, company_schema.sql och company_data.sql, som innehåller definitioner och data för The Johnsson Brothers Database.

Ladda ner och packa upp filerna (kom ihåg vilken mapp du lagrar dem i) och inspektera dem med ett textredigeringsprogram. Vad innehåller de?
Installera The Johnson Brothers databas med SOURCE-kommandot enligt ovan. Tänk på vilken ordning filerna måste användas. Om allt går bra skrivs en massa utskrifter ut av typen:
Query OK, 0 rows affected (0.01 sec) Records: O, Duplicates: 0 Warnings: 0 Om det något blir fel får du felutskrifter så titta noga igenom de uskrifterna för att kolla att det inte blev några fel. Om du får flera felutskrifter kan filerna ha blivit skadade vid nedladdningen, testa att ladda ned databasfilerna igen.
Undersök databasen med hjälp av kommandona SHOW TABLES och SHOW CREATE TABLE tabellnamn. Rita upp ett relationsschema med alla relationer (tabeller) och deras attribut. Markera främmandenycklar (referensattribut) som pilar mellan relationerna. Du kan rita för hand och fota av till redovisningen eller använda ritverktyg som t.ex. diagrams.net. Döp filen till "gN_relationer.jpg" eller "gN_relationer.pdf". Observera att denna fil ska lämnas in i Lisam. Det är viktigt att ha en bra uppfattning om hur databasen ser ut för att kunna genomföra uppgifterna nedan.

Uppgifter

För varje uppgift nedan ska ni i redovisningsfilen "gN_uppgifter.txt" ange det SQL-kommando som används för att utföra uppgiften och kopiera in den tabell som blev resultatet. Markera tydligt gränsen mellan uppgifter. Vissa uppgifter innehåller också en fråga som ska besvaras, skriv svaret efter frågans tabell.

Alla uppgifter utom 13 ska lösas med ett enda SQL-kommando (kommandon som är nästlade, som en SELECT inuti en SELECT, räknas som ett). Kapitelhänvisningarna nedan gäller båda utgåvorna av boken.

Lista namnen på alla avdelningar (kolumnen "name") för alla rader i tabellen "dept".
Lista all information som finns om alla anställda, dvs alla kolumner för alla rader i tabellen "employee".
Vilka delar finns inte på lager i tabellen "parts"? Dvs för vilka delar är "qoh" (Quantity On Hand) noll?.
Vilka anställda ("employee") har en lön som är mellan 9000 och 10000?
Beräkna hur gammal varje anställd var när han/hon började arbeta på företaget.
Tips: enkla matematiska beräkningar kan skrivas direkt i listan av kolumner som ska visas (efter SELECT) och utförs då för varje rad.
Vilka anställda har ett "son-namn", dvs ett efternamn som slutar på "-son"?
Tips: Du kan anta att "name" har formen "efternamn, förnamn".
Konstruera en nästlad fråga (fråga inuti fråga, se 7.8 i boken) för att ta reda på vilka saker (tabellen "item") som har levererats av leverantören med namnet "Fisher-Price". ID-numret för leverantören får inte förekomma som konstant i frågan. Resultatlistan ska innehålla ID och namn för sakerna.
Konstruera nu en icke-nästlad fråga för samma resultatlista som ovanstående. ID-numret får fortfarande inte förekomma som konstant.
Ta reda på namn och färg på alla delar ("parts") som är tyngre än en kortläsare ("Card reader"). Använd en nästlad fråga. Vikten på kortläsaren får inte förekomma som en konstant i frågan.
Ställ samma fråga som ovan utan att nästla. Vikten får fortfarande inte förekomma som konstant.
Tips: läs 8.13 i boken (8.14 i boken).
Vad är medelvikten på svarta delar (i tabellen "parts")?
Tips: Det du behöver kallas "aggregatfunktion".
För varje leverantör i Massachusetts ("Mass"), ta reda på totalvikten av allt som leverantören levererat. Svaret ska vara en lista med namnen på leverantörerna och den totala levererade vikten för respektive leverantör.
Tips: Antal levererade delar finns i "supply". Denna fråga blir väldigt besvärlig om man försöker nästla.
Skapa en helt ny tabell med samma utseende som "item" och fyll den med alla saker (ur "item") som kostar mindre än medelpriset. Här får du använda mer än ett kommando om du vill, men det finns ett som räcker. Om du använder det, vilken skillnad blir det i schemat för den gamla "item" och den nya som du skapar?
Skapa en vy över alla saker i "item" som kostar mindre än medelpriset. Vad är skillnaden mellan denna vy och tabellen i ovanstående fråga?
Tips: Hur mycket plats tar de? Vad händer med den nya tabellen och med vyn när "item" uppdateras?
Oops, det blev jordbävning! Alla leverantörer i Los Angeles försvann. Ta bort dem ur tabellen "Supplier". Vad händer? Varför?
Tips: Ta istället bort alla leverantörer i Springfield. Varför blev det skillnad?
En databasadministratör i företaget har försökt ta reda på hur bra olika leverantörers saker säljer. Hen har skapat en vy och kan med hjälp av den få reda på hur många varor som sålts från varje leverantör. Vyn skapades med nedanstående kommando:
create view sale_supply (supplier, item, quantity) as select supplier.name, item.name, sale.quantity from supplier, item, sale where supplier.pk_supplier=item.supplier and sale.item=item.pk_item
Hen använder sedan nedanstående fråga för att summera:
mysql> select supplier, sum(quantity) as sum from sale_supply group by supplier; 6 rows selected ------------------------- | SUPPLIER | SUM | ------------------------- | Cannon | 6 | | Koret | 1 | | Levi-Strauss | 1 | | Playskool | 2 | | White Stag | 4 | | Whitman's | 2 | -------------------------- Ett problem är att hen skulle vilja ha med alla leverantörer, även de vars varor inte blivit sålda ännu eller ens levererat varor. Hjälp hen genom att definiera om vyn "sale_supply" så att den också tar med leverantörer som inte sålt några varor.
Tips: Titta på tabellerna och vilka värden som finns. Tänk efter vad som händer i frågan. Läs sedan om Join i kapitel 8.7-8.11. Sista exemplet i 8.9 visar hur vi får nollor istället för NULL. Observera att vyn du skapar ska vara underlag för SELECT-frågan ovan.

Sidansvarig: Eva Ragnemalm
Senast uppdaterad: 2023-11-27

IDA - Institutionen för datavetenskap