732G16 Databaser: design och programmering

Laboration - Använda språket SQL

Syftet med denna labb är att öva på att skriva SQL-frågor, från de allra enklaste upp till en ganska avancerad nivå. Detta är en förberedelse för att använda SQL till de senare uppgifterna i projektet samt tentamen. Titta speciellt på de s.k. aggregeringsfunktionerna och vyerna.

Förberedelser

• Läs på om hur man använder SQL för att skriva frågor till en databas (se kursboken och/eller W3School´s tutorial, se Litteratur-sidan).
• Skapa och fyll en databas enligt instruktionerna under Komma igång med databasklienten. Om du närvarade vid SQL-föreläsningen, och skapade och fyllde databasen då, behöver du bara göra om detta om du gjorde ändringar i den (med kommandona insert, delete, update, create table, alter table eller drop table).
• Undersök sedan hela databasen enligt instruktionerna under "Komma igång med databasklienten" (se "Förberedelse för SQL-laborationen"). Denna undersökning är ett viktigt stöd i uppgifterna 7 och framåt.

Genomförande

Fil för redovisning

Vi rekommenderar att ni använder Notepad, Atom eller liknande för att skriva er redovisningsfil. Använd åtminstone en ren texteditor, inte ordbehandlare (sådana, t.ex. MsWord och LibreOffice lägger till font/styling-tecken) .

Skapa en textfil med namnet SQLlabb_GRUPPNR.txt där ni ersätter GRUPPNR med ert gruppnummer i Webreg. Högst upp i filen skriver ni namn och LiuID för de två som arbetat tillsammans med labben.

För varje uppgift i listan nedan, redovisa ert svar så här (i filen):

Tips: Kopiera gärna både kommando och utmatning från klienten det sista ni gör så att ni redovisar exakt det kommando ni faktiskt körde. MEN observera att ctrl-C avslutar klienten i vissa typer av terminal/command-fönster, så kopiera med musen.

Redovisningsfilen lämnas sedan in på Lisam. För deadline, se schemat. För att bli godkänd på labben ska alla uppgifter nedan lösas.

Uppgifter

Alla nedanstående uppgifter ska lösas med ett enda sql-kommando utom nr 13. Notera att i den sista uppgiften ska ert kommando skapa vyn och ni ska redovisa både innehållet i vyn och resultatet av det select-kommando som finns i uppgiften.

1. Lista namnen på alla avdelningar, d.v.s attributet name för alla rader i relationen dept.
2. Lista all information som finns om alla anställda, d.v.s. alla kolumner för alla rader i relationen employee.
3. Lista namnen på de delar (relationen parts) som inte finns på lager, d.v.s. qoh (Quantity On Hand) = 0  
4. Lista namnet och lönen på de anställda (employee) som har en lön mellan 9000 och 10000.
5. Lista namnet och hur gammal varje anställd var när han eller hon började arbeta på företaget.
6. Lista namnet på alla anställda som har ett "son"-namn. (Dvs efternamnet slutar på "son"). (Du kan anta att strukturen är "Efternamn, Förnamn").
7. Lista id-värdet (pk_item) och namnet för de saker (items) som har levererats av leverantören med namnet "Fisher-Price"? Använd dig av en nästlad fråga (select inuti select, se avsnitt 7.8 i boken) i huvudfrågans where-del.
8. Ställ samma fråga som ovan, fast med hjälp av JOIN. Fisher-Prices pk_supplier-värde får inte förekomma som en konstant i frågan.
9. Lista namnet och färgen på alla delar (parts) som är tyngre än en kortläsare. Använd dig av en nästlad fråga i huvudfrågans where-del.
10. Samma fråga som ovan, fast med hjälp av JOIN. Vikten på kortläsaren får inte förekomma som en konstant i frågan.
11. Vad är medelvikten på svarta delar (parts)?
12. För varje leverantör (supplier) som finns i staten Massachusetts (Mass), ta reda på totalvikten av allt de levererat. Svaret ska vara en lista med namnen på leverantörerna och deras totala levererade vikt (vikten per del gånger antalet levererade delar. Använd bland annat relationerna supply och parts).
13. Skapa en helt ny tabell (kalla den item_cheap) med samma utseende som item  och fyll den med alla saker (ur item) som kostar mindre än medelpriset. Detta går att göra med ett eller flera kommandon. Om du använder ett så blir det skillnad mellan item och item_cheap, utöver själva innehållet. Vilken?
14. Skapa en vy över alla saker i item (kalla den item_cheap_view) som kostar mindre än medelpriset. Vad är skillnaden mellan denna vy och tabellen du skapade i ovanstående fråga?
15. Det blev jordbävning i Los Angeles! Radera alla leverantörer i Los Angeles från tabellen supplier. Vad händer när du försöker göra det? Varför? Vad kan du göra för att det ska gå?
16. 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 hjälpvy och kan med hjälp av den få reda på hur många saker som sålts från varje leverantör:

mysql> 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;
Query OK, 0 rows affected (0.01 sec)
mysql> select supplier, sum(quantity) from sale_supply
         group by supplier;
-----------------------------------
|      SUPPLIER      |     SUM    |
-----------------------------------
|       Cannon       |      6     |
|       Koret        |      1     |
|    Levi-Strauss    |      1     |
|     Playskool      |      2     |
|     White Stag     |      4     |
|     Whitman's      |      2     |
-----------------------------------
6 rows in set (0.01 sec)
mysql>

Redovisning

Lämna in filen SQLlabb_GRUPPNR.txt enligt instruktionerna på Översikts-sidan.

Deadline

Se schemat.

Övrigt

Tänk på att jobba metodiskt. Skriv inte någon SQL kod tills dess ni har en ide om hur ni vill lösa den. Det svåra är inte att skriva koden, utan att komma på vad det är man vill göra.

Sidansvarig: Eva Ragnemalm
Senast uppdaterad: 2025-03-27