tehtävää

demo5.sql

-- 1. Hae keveimmän punaisen osan toimittajien nimet käänteisessä
--    aakkosjärjestyksessä [Tantola, Kiirola, Karola Oy]

SELECT tnimi
FROM toimittaja
WHERE ttun IN
    (SELECT ttun  -- toimitukset joissa mukana osa
     FROM toimitus
     WHERE otun IN
         (SELECT otun  -- Osat jotka..
          FROM osa
          WHERE paino =
              (SELECT MIN(paino)  -- Minimipainoinen osa
               FROM osa
               WHERE vari = 'punainen')))
ORDER BY tnimi DESC

-- 2. Hae projekteittain tunnus sekä ko. projektille toimitettujen toimitusten
--    lukumäärä ja kappalemäärien (vrt. maara-attribuutti) keskiarvo. Koska
--    PostgreSQL-palauttaa keskiarvon turhankin monella desimaalilla, voisit
--    käyttää sopivaa funktiota desimaalien määrän rajoittamiseksi kahteen.

SELECT ptun,
       avg(maara),
       count(maara)
FROM toimitus
GROUP BY ptun
--TODO tarkkuus

-- 3. Hae toimittajittain tunnukset ja toimitusten lukumäärät niiden
--    mikkeliläisten toimittajien osalta, jotka ovat toimittaneet joskus
--    projektille, jonka tunnus on ’p111’ [t111, 4].

SELECT ttun ,
       count(maara)
FROM toimitus
WHERE ttun IN
        (SELECT ttun
         FROM toimittaja
         WHERE kaup LIKE 'Mikkeli')
    AND ttun IN
        (SELECT DISTINCT ttun
         FROM toimitus
         WHERE ptun LIKE 'p111')
GROUP BY ttun;

 -- 4.Hae tunnukset osilta, joita on toimittanut useampi kuin viisi vaasalaista
--    toimittajaa. PostgreSQL-kannassa ei ole aivan näin monta vaasalaista
--    toimittajaa, joten jos haluat testata SQL-käskysi toimintaa, muuta
--    tehtäväksianto muotoon ”...vähintään yksi vaasalainen toimittaja”
--    [o345, o111, o222, o456, o789, o323]. Huomaa kuitenkin, että ratkaisusi
--    tulee toimia pienellä muutoksella alkuperäisessä tarkoituksessa!

SELECT DISTINCT otun
FROM toimitus
WHERE 4 <
        (SELECT count(DISTINCT ttun)
         FROM toimitus
         WHERE ttun IN
                 (SELECT ttun
                  FROM toimittaja
                  WHERE kaup LIKE 'Vaasa'))

-- 5. Lisää osavalikoimaan punainen, 200 kg painava ja 30 euroa maksava osa,
--    jota kutsutaan kiristimeksi ja jonka tunnus on ‘o999’. Uuden osan
--    sijoituspaikasta ei ole vielä tehty päätöstä.

INSERT INTO osa (otun,onimi,vari,paino,hinta)
VALUES ('o999',
        'kiristin',
        'punainen',
        '200',
        '30');

-- 6. Kaikille niille projekteille, jotka ovat tulleet toimeen ilman koirrua
--    (onimi = ’koirru’), on myönnetty ylimääräinen 5 000 euron
--    budjettikorotus. Päivitä vastaavasti tietokantaa. [päivitys koskee
--    seuraavia projekteja: Tuubero, Talluke, Iklore, Tuomake, Tuma, Teklari,
--    Aboma]

UPDATE projekti
SET budjetti = budjetti + 5000
WHERE ptun NOT IN
    (SELECT ptun
     FROM toimitus
     WHERE otun NOT IN
         (SELECT otun
          FROM osa
          WHERE onimi NOT LIKE 'koirru'));

 -- 7. Poista sellaisten toimittajien tiedot, jotka ovat toimittaneet vain
--    mikkeliläisiä osia. Huom. PostgreSQL sallii myös lyhenteiden käytön
--    tarkentimina (aliaksina) [Kiirola]

--TODO pitääkö poistaa myös toimituksista?

DELETE
FROM toimittaja
WHERE ttun IN
        (SELECT ttun
         FROM toimittaja
         WHERE ttun NOT IN -- Ei ole tilannut osia muualta kuin Mikkelistä
                 (SELECT ttun
                  FROM toimitus
                  WHERE otun IN
                          (SELECT otun
                           FROM osa -- Ei-mikkeliläiset osat

                           WHERE kaup NOT LIKE 'Mikkeli'))
             AND ttun IN
                 (SELECT ttun
                  FROM toimitus
                  WHERE otun IN
                          (SELECT otun
                           FROM osa
                           WHERE kaup LIKE 'Mikkeli')));


 -- 8. Lisää määritys, jonka mukaan ptun on projekti1-relaation perusavain.
--    Kokeile lisätä relaatioon jokin sellainen rivi, jossa ptun-attribuutilla
--    on arvo, joka on jo relaatiossa.

CREATE TABLE projekti1 ( ptun (char(4));


INSERT INTO osa (ptun)
VALUES ('p111');

--Eka lisäys
 ptun
------
 p111
(1 row)

--Toka lisäys
 ptun
------
 p111
 p111
(2 rows)

--Kolmas....
 ptun
------
 p111
 p111
 p111
(3 rows)


 -- 9. Oletetaan, että edellä määriteltyyn tietokantaan halutaan luoda
--    relaatio HENKILO (htun,hnimi,kaup,palkka,ptun), jonne on tarkoitus
--    tallentaa tietoja projekteissa olevista henkilöistä. Anna SQL-käsky,
--    jolla luodaan ko. relaatio. Valitse sopivat tietotyypit. Käskyyn tulee
--    sisältyä myös ne osuudet, joilla määritetään perusavaimeksi htun ja
--    viiteavaimeksi ptun. Edelleen tulee määrittää, että projektin
--    poistamisen seurauksena myös niiden henkilöiden tiedot poistetaan, jotka
--    ovat töissä ko. projektissa. Voit halutessasi kokeilla ratkaisuasi
--    lisäämällä muutamia rivejä Henkilo-relaatioon ja poistamalla sitten
--    sen projektin (projekti1-relaatiossa), jossa he ovat töissä.


create table HENKILO (
    htun CHAR(11) PRIMARY KEY,
    hnimi VARCHAR (30),
    kaup VARCHAR (30),
    palkka INTEGER,
    ptun CHAR(4) DEFAULT SET NULL
        FOREIGN KEY (ptun) 
        REFERENCES projekti1 (ptun) 
        ON DELETE SET DEFAULT
        ON UPDATE CASCADE );