732G16 Databaser: design och programmering

Uppgift 2 - Använda språket SQL

Motivation & Syfte

Här ska ni öva på att skriva SQL-frågor, från de allra enklaste upp till en ganska avancerad nivå. Detta är en bra övning för att kunna 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. Fundera på varje uppgift och skapa er en idé om hur ni tänkt lösa uppgiften innan ni börjar skriva SQL kod.

Starta MySQL klienten

För att starta MySQL klienten (dvs det program där man skriver SQL frågor till en databas) så dubbelklickar man på filen "StartMySQL" i kodskelettet. Då ska en svart ruta öppnas där man får skriva in sitt lösenord för databasen.

Skapa databasschema och lägg in data

I kodskelettet finns filerna "Z:\73G16\uppg2\CompanySchema.txt" och "Z:\73G16\uppg2\CompanyData.txt". Dessa innehåller SQL kod för att skapa tabeller i en databas samt fylla dessa tabeller med data. Titta gärna i dessa om ni är intresserade av hur detta görs, men ändra inte något i de.

För att ni ska ha någon data att öva på så ska ni skapa tabeller och importera data. Detta gör ni genom att starta MySQL klienten och exekvera dessa två kommandon, ett i taget:

source Z:\732G16\uppg2\CompanySchema.txt

source Z:\732G16\uppg2\CompanyData.txt

Genomförande

Fil för lösningar

Vi rekommenderar att ni använder Notepad++ för att skriva er egen SQL kod.

I kodskelettet finns filen "Z:\732G16\uppg2\Solutions.txt". Det är i denna fil som ni skriver alla era lösningar. Ta inte bort något från filen, utan ändra endast längst upp där det står "Förnamn Efternamn (liu-id)". Lämna kvar alla "#" tecken, de är kommentarer i SQL och kommer inte köras.

Filen "Solutions.txt" följer ett visst format, först kommer en kommentar t ex "#Uppgift 1", sedan en SELECT , t ex "SELECT Uppgift 1", och sedan skall ni skriva er kod. Där ni ombeds att förklara något fenomen ska ni använda kommentarer, t ex:

# Detta är en kommentar
# som är på flera rader.
# På så vis kan man svara på frågor i texten...

När ni fyllt i er lösning för en uppgift och vill testa den så öppnar ni MySQL klienten och skriver på prompten:

source Z:\732G16\uppg2\Solutions.txt

Då körs all den SQL kod ni skrivit i filen, och ni kan läsa resultatet direk i terminalen (ni kanske måste skrolla upp något när ni kör de första uppgifterna).

Uppgifter

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 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 får anta att strukturen är Efternamn, Förnamn). Se samma kap som ovan.
Lista alla kolumner för de saker (items) som har levererats av leverantören med namnet "Fisher-Price"? Använd dig av en subquery i huvudfrågans where-del.
Ställ samma fråga som ovan, fast utan subquery.
Lista namnet och färgen på alla delar som är tyngre än en kortläsare. Använd dig av en subquery i huvudfrågans where-del. Vikten på kortläsaren får inte förekomma som en konstant i frågan.
Samma fråga som ovan, fast utan subquery. Vikten får fortfarande inte finnas som konstant.
Vad är medelvikten på svarta delar?
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 city).
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, kolla SQLmanualen om du vill ha det lätta).
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?
En databasadministratör i företaget har försökt ta reda på hur bra olika leverantörers saker säljer. Hon 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 hon skulle även vilja ha med leverantörer vars varor inte blivit sålda. 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 på om hur man använder JOIN i SQL.

Redovisning

Mejla filen Solutions.txt till er labbassistent. I ämnesraden skriver ni "732G16 - Uppgift 2 - Redovisning".

Deadline

2013-05-03

Övrigt

Tänk på att jobba metodiskt. Skriv inte någon SQL kod tills dess ni har en idé 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: infomaster
Senast uppdaterad: 2013-03-28

Institutionen för datavetenskap