Laboration 2: SQL

Syfte:

Syftet med laborationen är att lära ut grundläggande databassökning via SQL.

Mål:

Efter genomgången laboration ska man kunna söka information i en relationsdatabas genom att ställa SQL-frågor.

Genomförande:

Laborationen genomföres antingen individuellt eller i grupper om två studenter. Uppgiften går ut på att formulera SQL-satser för att söka information i en databas via ett webb-baserat SQL-gränssnitt.  Man kör mot en Postgresqldatabas som klarar SQL1 med vissa tillägg för SQL92. Till sin hjälp har man utdelat material om databaser, en SQL-tutorial på http://www.sqlcourse.com, en fortsättning på http://SQLCourse2.com samt ett antal övningsfrågor.

Krav:

Maila de välstrukturerade SQL-satserna till laborationsansvarige (inge@nada.kth.se) tillsammans med resultatet som erhålls när satserna, exekveras, alternativt redovisa vid labtillfället.

---

1. Att komma igång med SQL

Gå till sidan  webbvaruhuset på datorn nestor. Du får en bild av databasens struktur och ett inmatningsfönster. I inmatningsfönstret skriver du dina frågor, t.ex.:

select namn from anställd where lön > 25000

Strax ovanför inmatningsrutan finns en knapp

Trycker man på den utförs SQL-satsen och resulatet visas på en ny HTML-sida:

namn
Svensson S
Johansson G

Satserna kan vid behov delas upp på flera rader

select namn from anställd where lön > 25000

2. Hjälpfunktioner

Det finns en hjälp-"site" för postgresql där man kan hitta information om postgresql-versionen av SQL: PostgreSQL 8.2.3 Documentation.

Dokumenten finns även på pdf-format men är väldigt stora. Eftersom ni måste vara uppkopplade mot internet för att kunna använda postgresql är det mest praktiskt att använda online-manualen.

3. Begränsningar

Av säkerhetsskäl och av praktiska administrativa skäl har användaren inte rättigheter att ändra i databasen. Det går alltså inte att lägga till i, ändra, eller ta bort ur tabeller. Det går inte heller att skapa tabeller. Kommandona UPDATE, INSERT, DELETE och CREATE är alltså inte tillgängliga under laborationen. Dock kan man skapa och kasta bort vyer. Om det behövs en vy skall denna skapas med ett tillräckligt kryptiskt namn för att undvika namnkollisioner. Alla delar namnrymd, så alla uppmanas vänligen att ta bort de vyer ni skapar efter användning.

4. Modell

5. Databasstruktur

Databasstrukturen är följande:

Tabell	Kolumner
Anställd	(namn, lön, chef, avd)
Försäljning	(avd, varunr, volym)
Lager	(avd, varunr, företag, volym)
Leverantör	(företag, adress)
Avdelning	(avd, våning)
Vara	(varunr, typ)

6. Övningsfrågor

Börja med att försöka besvara följande frågor. Titta inte på lösningsförslagen förrän du tror att du hittat en lösning, eller om du kör fast. Observera att det säkert finns andra lösningar som ger samma resultat.

1. Vad är genomsnittslönen för alla anställda?
   Se lösningsförslag
2. Vilka anställda tjänar mer än 24000?
   Se lösningsförslag
3. Hur många anställda tjänar mer än 20000?
   Se lösningsförslag
4. Vem tjänar mest, och hur mycket?
   Se lösningsförslag
5. På vilka avdelningar är medellönen större än 20000?
   Se lösningsförslag
6. Vilka varor säljs inte av någon avdelning på andra våningen?
   Se lösningsförslag
7. Vilka anställda på skoavdelningen har mer än 20000 i lön och vad heter deras närmaste chef?
   Se lösningsförslag
8. Vilka företag levererar mer än en typ av vara till någon avdelning på 3:e våningen,
   och hur många typer då?
   Se lösningsförslag
9. Vilka företag levererar pennor?
   Se lösningsförslag
   
10. På vilka våningar finns det minst två avdelningar?
    Se lösningsförslag

7. Labfrågor

Besvara följande frågor. Lösningar som baserar sig på att man antecknar delresultat, eller på att man räknar saker för hand, kommer inte att godkännas. OBS att det är formuleringen av en fråga som är intressant, inte svaret.

1. Vad är maxlönen för alla anställda?
2. Vilka anställda arbetar på leksaksavdelningen?
3. Vad är maxlönen för anställda som arbetar på andra eller tredje våningen?
4. Hur mycket tjänar Andersson L's chef?
5. Vilka varor säljs på andra våningen? Resultatet ska sorteras.
6. Vilka företag levererar inte pennor?
7. Vilken är den totala lönesumman för anställda som arbetar på avdelningar som har leveranser från Dagab?
8. Vilka anställda på varuhuset tjänar mer än alla på skoavdelningen?
9. Finns det någon vara som levereras av två eller fler olika företag?
10. Hur många anställda har de olika avdelningarna och vilken är resp. avdelnings totala lönekostnad?
11. Vilka avdelningar har en högre genomsnittslön än hela företagets genomsnitt?
12. Vilken är den totala volymen av artiklar av typ 'strumpor' som säljs på avdelningen 'herrkläder'?