Gagnasafnsfræði, haust 2011

Verkefni 2 - Venslalíkanið og grunnatriði í SQL - Sýnislausn

Skiladæmi:

Sýnið SQL skipanir til að búa til eftirfarandi gagnasafn (skilgreinið einnig aðallykla og ytri lykla):
Movie(id: integer, title: string, year: integer, director: integer)
Director(id: integer, name: string)
Actor(id: integer, name: string)
Casting(id: integer, movie: integer, actor: integer, ord: integer)
Trivia(id: integer, movie: integer, trivia: text)
Dálkurinn Movie.director er ytri lykill og vísar í Director.id
Dálkurinn Casting.movie er ytri lykill og vísar í Movie.id
Dálkurinn Casting.actor er ytri lykill og vísar í Actor.id
Dálkurinn Trivia.movie er ytri lykill og vísar í Movie.id
Lausn:
```
CREATE TABLE Director (
    id SERIAL PRIMARY KEY,
    name VARCHAR(64) NOT NULL
);

CREATE TABLE Movie (
    id SERIAL PRIMARY KEY,
    title VARCHAR(64) NOT NULL,
    year INTEGER NOT NULL,
    director INTEGER REFERENCES Director (id)
);

CREATE TABLE Actor (
    id SERIAL PRIMARY KEY,
    name VARCHAR(64) NOT NULL
);

CREATE TABLE Casting (
    id SERIAL PRIMARY KEY,
    movie INTEGER REFERENCES Movie (id),
    actor INTEGER REFERENCES Actor (id),
    ord INTEGER
);

CREATE TABLE Trivia (
    id SERIAL PRIMARY KEY,
    movie INTEGER REFERENCES Movie (id),
    trivia TEXT NOT NULL
);
```

Sýnið SQL skipanir til að búa til eftirfarandi gagnasafn (skilgreinið einnig aðallykla og ytri lykla):

Lausn:

CREATE TABLE Department (
    did SERIAL PRIMARY KEY,
    dname VARCHAR(64) NOT NULL
);

CREATE TABLE Employee (
    ssn CHAR(10) PRIMARY KEY,
    name VARCHAR(64) NOT NULL,
    did INTEGER REFERENCES Department (did),
    since DATE NOT NULL,
    supervisor CHAR(10) REFERENCES Employee (ssn)
);

Sýnið SQL skipun til að sækja lista yfir öll mismunandi ártöl (Movie.year) sem eru skráð í Movie töfluna í movies.sql (ábending: SELECT DISTINCT)

Lausn:

SELECT DISTINCT year FROM movie ORDER BY year ASC;

Sýnið SQL skipun til að sækja nafn, ártal og einkunn fyrir allar kvikmyndir með einkunn > 8.5.
Dæmi um úttak:

                                title                                 | year | score
----------------------------------------------------------------------+------+-------
 Star Wars                                                            | 1977 |   8.8
 Shawshank Redemption, The                                            | 1994 |     9
 Pulp Fiction                                                         | 1994 |   8.6
 Star Wars: Episode V - The Empire Strikes Back                       | 1980 |   8.6
 Usual Suspects, The                                                  | 1995 |   8.7

Lausn:

SELECT title, year, score FROM movie WHERE score > 8.5;

Sýnið SQL skipun til að sækja lista yfir nafn leikara og meðaleinkunn allra mynda sem hann hefur leikið í (ábending: þarft að tengja Movie, Casting, Actor og nota GROUP BY ásamt AVG)
Dæmi um úttak:

              name              |       avg
--------------------------------+------------------
 Benny Hill                     |              6.1
 Courtney B. Vance              |            6.875
 Clare Kramer                   |              6.5
 John Brady (II)                |                7
 Louis Calhern                  |              8.3

Lausn:

SELECT a.name, AVG(m.score)
FROM actor a, casting c, movie m
WHERE a.id=c.actorid 
  AND c.movieid=m.id
GROUP BY a.name
ORDER BY AVG DESC;