Demo 6
Tietokannan tietojen lisääminen, muuttaminen ja poistaminen SQL:llä

Sinulla on seuraavien määrityksien mukaan luotu tietokanta:

CREATE TABLE Nimike (
NimikeID INTEGER     NOT NULL,
Nimike   VARCHAR(64) NOT NULL,
CONSTRAINT Nimike_PK
   PRIMARY KEY (NimikeID)
);
CREATE TABLE Tehtava (
TehtavaID   INTEGER     NOT NULL,
TehtavaNimi VARCHAR(64) NOT NULL,
CONSTRAINT Tehtava_PK
   PRIMARY KEY (TehtavaID)
);
CREATE TABLE Projekti (
ProjektiID   INTEGER     NOT NULL,
ProjektiNimi VARCHAR(64) NOT NULL,
Alkamispvm   DATE        NOT NULL,
Paattymispvm DATE        NOT NULL,
CONSTRAINT Projekti_PK
   PRIMARY KEY (ProjektiID)
);
CREATE TABLE Henkilo (
Email     VARCHAR(64) NOT NULL, 
Etunimi   VARCHAR(32) NOT NULL,
Sukunimi  VARCHAR(32) NOT NULL, 
PuhNro    VARCHAR(32) DEFAULT '-',
Tyonimike INTEGER     NOT NULL, 
CONSTRAINT Henkilo_PK
   PRIMARY KEY (Email),
CONSTRAINT Henkilo_FK_T
   FOREIGN KEY (Tyonimike)
   REFERENCES Nimike (NimikeID)
      ON UPDATE NO ACTION
      ON DELETE NO ACTION
);
CREATE TABLE Tekee (
Henkilo  VARCHAR(64) NOT NULL, 
Tehtava  INTEGER     NOT NULL,
Projekti INTEGER     NOT NULL,
CONSTRAINT Tekee_PK
   PRIMARY KEY (Henkilo, Tehtava, Projekti),
CONSTRAINT Tekee_FK_H
   FOREIGN KEY (Henkilo)
   REFERENCES Henkilo (Email)
      ON UPDATE CASCADE
      ON DELETE NO ACTION,
CONSTRAINT Tekee_FK_T
   FOREIGN KEY (Tehtava)
   REFERENCES Tehtava (TehtavaID)
      ON UPDATE CASCADE
      ON DELETE NO ACTION,
CONSTRAINT Tekee_FK_P
   FOREIGN KEY (Projekti)
   REFERENCES Projekti (ProjektiID)
      ON UPDATE CASCADE
      ON DELETE CASCADE
);

Kopioi itsellesi edellä lueteltujen SQL-lauseiden perusteella luotu Access-tietokanta (Access 97).

Kirjoita SQL-kielellä järjestyksessä seuraavat lisäysoperaatiot. Suorita jokainen operaatio ja tallenna omaksi kyselykseen (Lisays1 ... Lisays19)

Kaikki lisäysoperaatiot eivät välttämättä onnistu ilman virheitä. Jos saat virheilmoituksen niin varmista, että ymmärrät miksi operaatio ei onnistu. Perusavain- ja viite-eheysmääritykset vaikuttavat siihen onnistuuko lisääminen vai ei.

1. Lisää Nimike-tauluun

   NimikeID	Nimike
   1	Ohjelmoija

2. Lisää Nimike-tauluun

   NimikeID	Nimike
   2	Suunnittelija

3. Lisää Tehtava-tauluun

   TehtavaID	TehtavaNimi
   1	Ohjelmoi

4. Lisää Tehtava-tauluun

   TehtavaID	TehtavaNimi
   2	Projektipäällikkö

5. Lisää Projekti-tauluun

   ProjektiID	ProjektiNimi	Alkamispvm	Paattymispvm
   1	WWW	1.2.2000	1.6.2000

6. Lisää Projekti-tauluun

   ProjektiID	ProjektiNimi	Alkamispvm	Paattymispvm
   2	Makro	1.3.2000	1.5.2000

7. Lisää Projekti-tauluun

   ProjektiID	ProjektiNimi	Alkamispvm	Paattymispvm
   3	WWW	2.3.2000	2.6.2000

8. Lisää Henkilo-tauluun

   Email	Etunimi	Sukunimi	Puhnro	Tyonimike
   tommi.j.lahtonen@jyu.fi	Tommi	Lahtonen	2602746	2

9. Lisää Henkilo-tauluun

   Email	Etunimi	Sukunimi	Puhnro	Tyonimike
   peheinon@mit.jyu.fi	Petri	Heinonen	2602746	1

10. Lisää Henkilo-tauluun

    Email	Etunimi	Sukunimi	Puhnro	Tyonimike
    eskheis@mit.jyu.fi	Esko	Heiskanen	2600000	0

11. Lisää Tekee-tauluun

    Henkilo	Tehtava	Projekti
    tommi.j.lahtonen@jyu.fi	1	2

12. Lisää Tekee-tauluun

    Henkilo	Tehtava	Projekti
    peheinon@mit.jyu.fi	1	1

13. Lisää Tekee-tauluun

    Henkilo	Tehtava	Projekti
    eskheis@mit.jyu.fi	1	1

14. Lisää Tekee-tauluun

    Henkilo	Tehtava	Projekti
    tommi.j.lahtonen@jyu.fi	2	1

15. Lisää Tekee-tauluun

    Henkilo	Tehtava	Projekti
    tommi.j.lahtonen@jyu.fi	2	2

16. Lisää Tekee-tauluun

    Henkilo	Tehtava	Projekti
    peheinon@mit.jyu.fi	2	1

17. Lisää Tekee-tauluun

    Henkilo	Tehtava	Projekti
    tommi.j.lahtonen@jyu.fi	1	3

18. Lisää Tekee-tauluun

    Henkilo	Tehtava	Projekti
    tommi.j.lahtonen@jyu.fi	3	3

19. Lisää Tekee-tauluun

    Henkilo	Tehtava	Projekti
    peheinon@mit.jyu.fi	2	3

Kirjoita SQL-kielellä järjestyksessä seuraavat päivitysoperaatiot. Suorita jokainen operaatio ja tallenna omaksi kyselykseen (Paivitys1 ... Paivitys6)

Kaikki päivitysoperaatiot eivät välttämättä onnistu ilman virheitä. Jos saat virheilmoituksen niin varmista, että ymmärrät miksi operaatio ei onnistu. Perusavain- ja viite-eheysmääritykset vaikuttavat siihen onnistuuko päivittyminen vai ei.

Osa päivityksistä voi vaikuttaa myös useampaan tauluun samanaikaisesti (viite-eheys). Tarkista jokaisen päivityksen jälkeen mihin kaikkiin tauluihin on tullut muutoksia. Varmista, että ymmärrät miksi päivitys on vaikuttanut useampaan tauluun.

1. Päivitä Projekti-taulun projekti 1:lle (projektiID = 1) nimeksi Webbiprojekti 1 ja uudeksi projektiID:ksi 4.
2. Päivitä Nimike-taulussa nimike 1:en (nimikeID = 1) nimeksi Koodinvääntäjä.
3. Päivitä Henkilo-taulussa kaikkien henkiloiden työnimikkeeksi 1.
4. Päivitä Tekee-taulussa tehtäväksi 2 kaikille niille jotka toimivat projektissa 2 ja joiden sähköpostiosoite on tommi.j.lahtonen@jyu.fi.
5. Päivitä Nimike-taulussa NimikeID 1:en NimikeID:ksi 3.
6. Päivitä Nimike-taulussa NimikeID 2:en NimikeID:ksi 4.

Kirjoita SQL-kielellä järjestyksessä seuraavat poisto-operaatiot. Suorita jokainen operaatio ja tallenna omaksi kyselykseen (Poisto1 ... Poisto3)

Kaikki poisto-operaatiot eivät välttämättä onnistu ilman virheitä. Jos saat virheilmoituksen niin varmista, että ymmärrät miksi operaatio ei onnistu. Viite-eheysmääritykset vaikuttavat siihen onnistuuko poistaminen vai ei.

Osa poistoista voi vaikuttaa myös useampaan tauluun samanaikaisesti (viite-eheys). Tarkista jokaisen poiston jälkeen mihin kaikkiin tauluihin on tullut muutoksia. Varmista, että ymmärrät miksi poisto on vaikuttanut useampaan tauluun.

1. Poista Projekti-taulusta kaikki Projekti joiden nimi on WWW.
2. Poista Tehtava-taulusta kaikki ne tehtävät joiden TehtavaID on 1.
3. Poista Nimike-taulusta kaikki ne Nimike joiden NimikeID on suurempi kuin 2.