English web site
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).
- Skapa och fyll en databas enligt instruktionerna under Komma igång
med databasklienten. Undersök också databasen enligt instruktionerna där.
Denna undersökning är ett viktigt stöd i uppgifterna 7
och framåt.
På SQL-föreläsningen används samma databas.
Genomförande
Fil för redovisning
Vi rekommenderar att ni använder Notepad++ (PC-pul) eller Atom (SU-pul/Thinlinc) för att skriva er redovisningsfil. Använd åtminstone en ren texteditor, inte ordbehandlare.
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:
Uppgift nr X
Det SQL-kommando ni använde.
Det svar (den utskrift) ni fick från databasklienten.
Om uppgiften också innehåller en fråga, lägg svaret på den
här.
mysql) tillåter bara redigering av den
aktuella raden (använd piltangenterna), vilket
kan bli lite besvärligt med längre kommandon. Det kan därför vara
praktiskt att gradvis utveckla kommandot i sin redovisningsfil och
kopiera det från filen till klienten när man vill testa.
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, 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.
- Lista namnen på alla avdelningar, d.v.s attributet name för alla rader i relationen dept.
- Lista all information som finns om alla anställda, d.v.s. alla kolumner för alla rader i relationen employee.
- Lista namnen på de delar som inte finns på lager, d.v.s. qoh (Quantity On Hand) = 0 (relationen som avses är parts)
- Lista namnet och lönen på de anställda som har en lön mellan 9000 och 10000.
- Lista namnet och hur gammal varje anställd var när han eller hon började arbeta på företaget.
- 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).
- Lista alla kolumner 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.
- 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.
- Lista namnet och färgen på alla delar som är tyngre än en kortläsare. Använd dig av en nästlad fråga i huvudfrågans where-del.
- Samma fråga som ovan, fast utan nästlad fråga. Vikten på kortläsaren får inte förekomma som en konstant i frågan.
- Vad är medelvikten på svarta delar (tabellen parts)?
- För varje leverantör i 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 (använd bland annat relationen supply).
- 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. (Observera att detta går att göra på ett lätt och ett arbetsamt sätt, båda med samma grundkommando). Om du använder det lätta, undersök vad det blir för skillnad mellan item och item_cheap, utöver innehållet.
- 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 i ovanstående fråga?
- 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å?
- 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;
Ok.
2 SQL> select supplier, sum(quantity) 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 |
-----------------------------------
3 SQL>
Ett problem är att hen skulle vilja ha med även leverantörer vars varor inte blivit sålda ännu. Definiera om sale_supply så att den tar med alla leverantörer som finns i databasen även om de inte har någon försäljning. (Använd CASE för att ersätta NULL med 0).
Tips: Titta på hur tabellerna ser ut och vilka värden som finns. Tänk efter vad som händer i frågan. Läs på om hur man använder JOIN i SQL.
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: 2022-03-31
