Göm menyn

729G28 Webbprogrammering och databaser

SQL-övning


Meningen med övningarna

Här ska ni öva på att skriva SQL-frågor, från de allra enklaste upp till en ganska avancerad nivå. Se till att utnyttja den handledda tiden väl. Förberedelser krävs, se instruktioner. Kontoinformation för databashanteraren MySQL ska ni ha fått via epost (troligen för länge sedan, t.ex. i augusti). Rubriken på brevet är "MySQL database created". Om ni har läst någon kurs tidigare, på IDA, och då fått databaskonto ska samma konto användas (då får ni inget nytt brev).

Redovisning

övningen redovisas genom att ni lämnar in dokumentation av era svar sparade i en textfil som  lämpligen kompletteras med svar på förekommande frågor också. Visa både vad ni skrev för uttryck för att få fram svaret, och vad svaret blev. Glöm inte bort att också förklara varför det blev som det blev, på de uppgifter där det efterfrågas. (På uppgift 1 kan ni rita pilar för hand på pappersutskriften, de behöver inte finnas i filen.)

Uppgifterna

  1. Undersök databasen med hjälp av kommandona show tables, describe , show create table . Rita upp ett relationsschema med alla relationer (tabeller) och attribut, markera referensattribut (foreign key) med pilar mellan relationerna. Observera att du behöver denna information för de senare uppgifterna.
  2. Lista namnen på alla avdelningar, d.v.s attributet name för alla rader i tabellen jbdept.
  3. Lista all information som finns om alla anställda, d.v.s. alla kolumner för alla rader i tabellen jbemployee.
  4. Vilka delar finns inte på lager, d.v.s. qoh (Quantity On Hand) = 0 ?  (tabellen som avses är jbparts)
  5. Vilka anställda har en lön som är mellan 9000 och 10000?
  6. Lista hur gammal varje anställd var när han eller hon började arbeta på företaget. Tips: Kolla kapitel 9 i databasboken.
  7. Vilka anställda har ett "son"-namn? (Dvs efternamnet slutar på "son"). (Ja, du får anta att strukturen är Efternamn, Förnamn för att bli av med Jason). Se samma kap som ovan.
  8. Vilka saker (i jbitem) har levererats av leverantören med namnet "Fisher-Price"? Använd dig av en kapslad fråga (subquery). ID-numret för leverantören Fisher-Price får inte förekomma som konstant i frågan.
  9. Ställ samma fråga som ovan, fast utan kapsling (subquery). Id-numret får inte heller här förekomma som konstant.
  10. Ta reda på namnet och färgen på alla delar som är tyngre än en kortläsare. Använd dig av en kapslad fråga. Vikten på kortläsaren får inte förekomma som en konstant i frågan.
  11. Samma fråga som ovan, fast utan kapsling (subquery). Vikten får fortfarande inte finnas som konstant. Tips: kolla kapitel 7.17 i boken och/eller föreläsningen om SQL.
  12. Vad är medelvikten på svarta delar? Tips: det du behöver är "Aggregeringsfunktioner".
  13. 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. Tips: levererade delar finns i tabellen jbsupply.
  14. Skapa en helt ny tabell med samma utseende som jbitem  och fyll den med alla saker (ur jbitem) 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, kolla SQLmanualen om du vill ha det lätta).
  15. Skapa en vy över alla saker i jbitem som kostar mindre än medelpriset. Vad är skillnaden mellan denna vy och tabellen i ovanstående fråga? (Vad händer vid uppdatering av jbItem?)
  16. Oops! Jordbävning! Radera alla leverantörer i Los Angeles från tabellen jbsuppliers. Vad händer när du försöker göra det? Varför? Vad skulle du behöva göra för att kunna genomföra kommandot?
  17. En databasadministratör i företaget har försökt ta reda på hur bra olika leverantörers saker säljer. Han 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:
1 SQL> create view sale_supply(supplier, item, quantity) as
         select jbsupplier.name, jbitem.name, jbsale.quantity
         from jbsupplier, jbitem, jbsale
         where jbsupplier.id=jbitem.supplier and
               jbsale.item=jbitem.id;
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 han skulle vilja ha med även leverantörer vars varor inte blivit sålda. Hjälp honom! Definiera om sale_supply så att den också tar med leverantörer som inte har någon försäljning.

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 sedan om Join som beskrivs i kap. 8.7-8.11.

Sidansvarig: Eva L. Ragnemalm
Senast uppdaterad: 2011-12-02