Omat suoritukset - Ohjelmointitehtävät - Luku 1: Tehtävä 1 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Henkilot-taulun luominen Tehtävästä saadut pisteet 1 Tehtävänanto Tehtävänäsi on muodostaa SQL-lause, jolla lisätään tietokantaan uusi taulu "Henkilot". Taulun tulee sisältää alla olevan taulukon määrittelemät kentät samassa järjestyksessä kuin ne ovat taulukossa esitetty. Muista myös huomioida muut tauluun liittyvät määrittelyt. HENKILOT Kentän nimi Kentän tietotyyppi Muut määrittelyt henkilot_henkilo_id integer not null primary key henkilot_etunimi character varying(40) not null henkilot_sukunimi character varying(40) not null henkilot_email character varying(100) Ratkaisusi CREATE TABLE HENKILOT( henkilot_henkilo_id integer not null primary key, henkilot_etunimi character varying(40) not null, henkilot_sukunimi character varying(40) not null, henkilot_email character varying(100) ) Omat suoritukset - Ohjelmointitehtävät - Luku 1: Tehtävä 2 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Tietojen lisääminen Henkilot-tauluun Tehtävästä saadut pisteet 1 Tehtävänanto Edellisessä tehtävässä laadit tietokantaan taulun "Henkilot". Nyt tehtävänäsi tehdä SQL-lause, jolla saat syötettyä alla olevassa taulukkossa luetellut tiedot juuri laatimaasi tauluun. HENKILOT henkilot_henkilo_id henkilot_etunimi henkilot_sukunimi henkilot_email 1 Maija Teräväinen maija@teravainen.com Ratkaisusi INSERT INTO HENKILOT VALUES (1, 'Maija', 'Teräväinen', 'maija@teravainen.com') Omat suoritukset - Ohjelmointitehtävät - Luku 1: Tehtävä 3 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Tietojen hakeminen Henkilot-taulusta Tehtävästä saadut pisteet 1 Tehtävänanto Edellisessä tehtävässä lisäsit yhden henkilön tiedot tauluun 'Henkilot'. Samaan tauluun on lisätty tämän jälkeen lisää tietoja myös muista henkilöistä. Nyt tehtävänäsi on laatia SQL-kysely, jolla saat selville kaikkien henkilöiden kaikki tiedot taulusta 'Henkilot'. Kyselyn tulee tulostaa tiedot esimerkkitulostuksen mukaisesti. Ratkaisusi select * from henkilot; Omat suoritukset - Ohjelmointitehtävät - Luku 2: Tehtävä 1 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Tietojen hakeminen Kirjat-taulusta Tehtävästä saadut pisteet 1 Tehtävänanto Ellei toisin mainita, luvusta kaksi alkaen kaikki harjoitustehtävät liittyvät kirjatietokantaan, jonka kuvauksen voit lukea manuaalista tai tehtävien yhteydessä olevasta ""-linkistä. Kirjatietokannan tutkiminen on välttämätöntä oikeiden vastausten laatimiseksi harjoitustehtäviin. Harjoitustehtävänäsi on laatia SQL-kysely, joka hakee kaikki "Kirjat" taulun sisältämät tiedot. Ratkaisusi select * from kirjat; Omat suoritukset - Ohjelmointitehtävät - Luku 2: Tehtävä 2 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Kirjojen nimien ja hintojen hakeminen Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-kysely, jolla haetaan kaikkien kirjatietokannan () kirjojen nimet ja hinnat. Kysely on laadittava siten, että vastauksessa kullakin rivillä on ensin kirjan nimi ja tämän jälkeen kirjan hinta. Ratkaisusi select Kirjat_kirjannimi, kirjat_hinta from kirjat; Omat suoritukset - Ohjelmointitehtävät - Luku 2: Tehtävä 3 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Tiettyjen kirjojen nimien hakeminen Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-kysely, jolla haetaan niiden kirjojen nimet (), joiden hinta on alle 25 euroa. Huomaa, että itse hintaa ei haluta tulokseen, vaan ainoastaan kirjojen nimet. Ratkaisusi select kirjat_kirjannimi from kirjat where kirjat_hinta < 25; Omat suoritukset - Ohjelmointitehtävät - Luku 2: Tehtävä 4 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Kirjojen hakemien julkaisuvuoden perusteella Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-kysely, jolla haetaan niiden kirjojen nimet ja julkaisupäivämäärät (), jotka on julkaistu ennen vuotta 2000. Ratkaisusi select Kirjat_KirjanNimi,Kirjat_Julkaisupvm from kirjat where Kirjat_Julkaisupvm < '2000-01-01'; Omat suoritukset - Ohjelmointitehtävät - Luku 2: Tehtävä 5 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Varastosta loppuneet kirjat Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-kysely, jolla haetaan niiden kirjojen kirjakoodit (), jotka ovat loppuneet varastosta, toisin sanoen kirjojen lukumäärä varastossa on nolla. Ratkaisusi select varasto_kirja_id from varasto where varasto_lukumaara = 0; Omat suoritukset - Ohjelmointitehtävät - Luku 3: Tehtävä 1 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Edulliset suurteokset Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-kysely, jolla saat selville niiden kirjojen nimet, joiden hinta on alle 45 euroa ja sivumäärä on yli 450 sivua (). Ratkaisusi select kirjat_kirjannimi from kirjat where Kirjat_Hinta < 45 and Kirjat_Sivuja > 450; Omat suoritukset - Ohjelmointitehtävät - Luku 3: Tehtävä 2 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Alennushinnoiteltavat kirjat Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-kysely, jolla haetaan niiden kirjojen nimet, jotka ovat julkaistu ennen vuotta 2000 ja joiden hinta on yli 30 euroa (). Ratkaisusi select kirjat_kirjannimi from kirjat where Kirjat_Julkaisupvm < '2000-01-01' and kirjat_hinta > 30; Omat suoritukset - Ohjelmointitehtävät - Luku 3: Tehtävä 3 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Edulliset ja kalliit kirjat Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-kysely, jolla haetaan niiden kirjojen nimet, joiden hinta on alle 20 euroa tai yli 40 euroa (). Ratkaisusi select kirjat_kirjannimi from kirjat where kirjat_hinta < 20 or kirjat_hinta > 40; Omat suoritukset - Ohjelmointitehtävät - Luku 3: Tehtävä 4 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Lyhyehköt teokset Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-kysely, jolla haetaan niiden kirjojen nimet, joiden sivumäärä on 100-200 sivua (). Ratkaisusi select kirjat_kirjannimi from kirjat where Kirjat_Sivuja >= 100 and Kirjat_Sivuja < 200; Omat suoritukset - Ohjelmointitehtävät - Luku 3: Tehtävä 5 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Samalla kirjaimella alkavien kirjojen nimet Tehtävästä saadut pisteet 1 Tehtävänanto Tehtävänäsi on tehdä SQL-kysely, jolla haetaan kaikkien niiden kirjojen nimi ja hinta, jotka alkavat 'K'-kirjaimella (). Ratkaisusi select kirjat_kirjannimi, kirjat_hinta from kirjat where kirjat_kirjannimi like 'K%'; Omat suoritukset - Ohjelmointitehtävät - Luku 3: Tehtävä 6 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Kirjailijoiden nimet Tehtävästä saadut pisteet 1 Tehtävänanto Tee SQL-kysely, jolla saat selville niiden kirjailijoiden etu- ja sukunimen, joiden sukunimet sijaitsevat aakkosjärjestyksessä kirjaimen 'K' jälkeen (). Vastauksessa tulee olla jokaisella rivillä ensimmäisenä kirjailijan etunimi. Ratkaisusi select kirjailijat_etunimi, kirjailijat_sukunimi from kirjailijat where kirjailijat_sukunimi > 'K%'; Omat suoritukset - Ohjelmointitehtävät - Luku 4: Tehtävä 1 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Kirjailijoiden etu- ja sukunimet Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-kysely, jolla haetaan kirjatietokannan () kirjailijoiden etu- ja sukunimet. Kyselyn tulos järjestetään aakkosjärjestykseen sukunimen mukaan. Lisäksi saman sukunimen omaavat henkilöt järjestetään aakkosjärjestykseen etunimen mukaan. Ratkaisusi select kirjailijat_etunimi, kirjailijat_sukunimi from kirjailijat order by kirjailijat_sukunimi, kirjailijat_etunimi; Omat suoritukset - Ohjelmointitehtävät - Luku 4: Tehtävä 2 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Kaikkien kirjojen nimet ja hinnat Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-kysely, jolla haetaan kirjatietokannasta () kaikkien kirjojen nimet ja hinnat. Kyselyn tulos järjestetään hinnan perusteella siten, että kallein kirja tulostetaan tuloksen ensimmäiselle riville. Ratkaisusi select kirjat_kirjannimi, kirjat_hinta from kirjat order by Kirjat_Hinta desc; Omat suoritukset - Ohjelmointitehtävät - Luku 4: Tehtävä 3 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Kirjailijoiden etunimet Tehtävästä saadut pisteet 1 Tehtävänanto Tehtävänäsi on laatia SQL-kysely, joka hakee kaikkien kirjailijoiden etunimet (). Jokainen etunimi tulostetaan luetteloon aakkosjärjestyksessä siten, että kukin etunimi tulostetaan vain ja ainoastaan kerran. Ratkaisusi select distinct kirjailijat_etunimi from kirjailijat order by kirjailijat_etunimi; Omat suoritukset - Ohjelmointitehtävät - Luku 4: Tehtävä 4 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Yli 500-sivuiset kirjat Tehtävästä saadut pisteet 1 Tehtävänanto Tehtävänäsi on laatia SQL-kielinen lause, jolla saat selville kuinka monta yli 500-sivuista kirjaa on 'Kirjat' -taulussa (). Vastaukseen tarvitaan vain pelkkä kirjojen lukumäärä, eikä esim. listaa ko. kirjoista. Ratkaisusi select count(*) from kirjat where Kirjat_Sivuja >= 500; Omat suoritukset - Ohjelmointitehtävät - Luku 5: Tehtävä 1 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Kirjan lisääminen Kirjat-tauluun Tehtävästä saadut pisteet 1 Tehtävänanto Tehtävänäsi on laatia SQL-lause, jolla tallennetaan uusi tietue kirjatietokannan (Harjoitustietokannan kuvaus) tauluun "Kirjat". Tallennettavan tietueen tulee sisältää seuraavat tiedot: Kirjat_Kirja_ID 110 Kirjat_KirjanNimi Alavilla mailla hallan vaara Kirjat_Tekija_ID 202 Kirjat_Kustantaja_ID 303 Kirjat_Julkaisupvm 2002-12-20 Ratkaisusi insert into kirjat (Kirjat_Kirja_ID,Kirjat_KirjanNimi,Kirjat_Tekija_ID,Kirjat_Kustantaja_ID,Kirjat_Julkaisupvm) values (110,'Alavilla mailla hallan vaara',202,303,'2002-12-20'); Omat suoritukset - Ohjelmointitehtävät - Luku 5: Tehtävä 2 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Kirjan tietojen päivittäminen Tehtävästä saadut pisteet 1 Tehtävänanto Edellisessä tehtävässä lisäsit kirjan "Alavilla mailla hallan vaara" Kirjat-tauluun, minkä jälkeen taulu näytti seuraavalta: KIRJAT kirjat_kirja_id kirjat_kirjannimi kirjat_tekija_id kirjat_kustantaja_id kirjat_sivuja kirjat_hinta kirjat_julkaisupvm 101 Kolme vanhaa miestä 202 304 333 21.8 2002-12-04 102 Ilkka ja koira 201 301 310 23.1 1999-12-31 103 Poika ja huuhkaja 201 301 232 16.8 1996-03-14 104 Kevät 203 302 143 14.5 2000-01-01 105 Seitsemän pelikorttia 204 302 290 29.8 1991-04-19 106 Koko pakka 205 303 520 41.7 1990-10-10 107 Pikku-Antin seikkailut 201 301 56 10.4 2001-11-20 108 Pitkä talvi 202 304 1156 46.8 1970-12-01 109 Sinne ja tänne 203 303 814 41.2 1978-09-30 110 Alavilla mailla hallan vaara 202 303 2002-12-20 Nyt tehtävänäsi on päivittää edellisessä tehtävässä lisäämääsi tietuetta niin, että tietueen tyhjät kentät saavat alla esitetyn taulukon mukaiset arvot. Kirjat_Sivuja 351 Kirjat_Hinta 24.5 Ratkaisusi update kirjat set kirjat_sivuja = 351, kirjat_hinta = 24.5 where kirjat_kirjannimi = 'Alavilla mailla hallan vaara'; Omat suoritukset - Ohjelmointitehtävät - Luku 5: Tehtävä 3 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Kirjan poistaminen Kirjat-taulusta Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-komento, joka poistaa edellisissä tehtävissä lisäämäsi ja päivittämäsi tietueen taulusta "Kirjat". Tietue sisältää seuraavan taulukon mukaiset tiedot. Kirjat_Kirja_ID 110 Kirjat_KirjanNimi Alavilla mailla hallan vaara Kirjat_Tekija_ID 202 Kirjat_Kustantaja_ID 303 Kirjat_Sivuja 351 Kirjat_Hinta 24.5 Kirjat_Julkaisupvm 2002-12-20 Ratkaisusi delete from kirjat where Kirjat_KirjanNimi = 'Alavilla mailla hallan vaara' and Kirjat_Kirja_ID = 110; Omat suoritukset - Ohjelmointitehtävät - Luku 6: Tehtävä 1 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Eniten sivuja sisältävä kirja Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-kysely, joka hakee kirjatietokannan () "Kirjat"-taulusta suurimman tauluun tallennetun kirjan sivumäärän. Ratkaisusi select MAX(Kirjat_Sivuja) from kirjat; Omat suoritukset - Ohjelmointitehtävät - Luku 6: Tehtävä 2 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Varastossa olevat kirjat Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-kysely, joka hakee kirjatietokannan () varastossa olevien kirjojen kokonaislukumäärän. Ratkaisusi select SUM(Varasto_Lukumaara) from varasto; Omat suoritukset - Ohjelmointitehtävät - Luku 6: Tehtävä 3 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Sivumäärän keskiarvo Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-kysely, joka hakee kirjatietokannan () kirjojen keskimääräisen sivumäärän. Keskiarvosta jätetään pois sellaiset kirjat, joiden sivumäärä on alle 100. Ratkaisusi select avg(Kirjat_Sivuja) from kirjat where Kirjat_Sivuja >= 100; Omat suoritukset - Ohjelmointitehtävät - Luku 7: Tehtävä 1 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Kirjailijoiden sivumäärät Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-kysely, joka laskee yhteen kirjatietokannan () taulusta "Kirjat" kunkin kirjailijan kirjoittamien sivujen määrän. Kyselyn tuloksessa kullekin riville tulostetaan ensin kirjailijan ID-numero (Kirjat_Tekija_ID) ja tämän jälkeen kirjailijan yhteensä kirjoittamien sivujen määrä. Ratkaisusi select Kirjat_Tekija_ID, sum(Kirjat_Sivuja) from kirjat group by kirjat_tekija_id order by kirjat_tekija_id; Omat suoritukset - Ohjelmointitehtävät - Luku 7: Tehtävä 2 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Kirjailijoiden kirjojen lukumäärät Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-kysely, joka laskee yhteen kirjatietokannan () taulusta "Kirjat" kunkin kirjailijan kirjoittamien kirjojen lukumäärän. Kyselyn tuloksessa kullekin riville tulostetaan ensin kirjailijan ID-numero (KirjatTekija_ID) ja tämän jälkeen kirjailijan kirjoittamien kirjojen lukumäärä. Ratkaisusi select kirjat_tekija_id, count(*) from kirjat group by Kirjat_Tekija_ID order by kirjat_tekija_id; Omat suoritukset - Ohjelmointitehtävät - Luku 7: Tehtävä 3 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Kustantajien kustantamat kirjat Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-kysely, joka laskee yhteen eri kustantajien kustantamat kirjat (). Kyselyn tuloksessa tulostetaan kullekkin riville ensin kustantajan ID-numero (Kirjat_Kustantaja_ID) ja tämän jälkeen kustantajan kustantamien kirjojen lukumäärän. Kyselyn tulee lisäksi rajata tulos niin, että mukaan otetaan vain ne kustantajat, jotka ovat kustantaneet vähintään kolme kirjaa. Ratkaisusi select kirjat_kustantaja_id, count(*) from kirjat group by kirjat_kustantaja_id having count(*) >= 3; Omat suoritukset - Ohjelmointitehtävät - Luku 8: Tehtävä 1 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Kirjailijoiden nimet Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-kysely, jolla haetaan kaikki kirjatietokannassa () olevien kirjailijoiden etu- ja sukunimet esimerkkitulostuksen mukaisesti. Kyselyn tuloksessa kenttien alkuperäiset nimet/otsikot: "Kirjailijat_Etunimi" ja "Kirjailijat_Sukunimi" korvataan aliaksilla: "Etunimi" ja "Sukunimi". Ratkaisusi select Kirjailijat_Etunimi AS Etunimi, Kirjailijat_Sukunimi AS Sukunimi from kirjailijat; Omat suoritukset - Ohjelmointitehtävät - Luku 8: Tehtävä 2 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Kirjojen alennushinnat Tehtävästä saadut pisteet 1 Tehtävänanto Tehtävänäsi on laatia SQL-kysely, joka tulostaa kirjatietokannan () sisältämien kirjojen nimet, hinnat ja alennushinnat. Alennushinnoista poistetaan 45% kirjan alkuperäisestä hinnasta. Kyselyn tuloksen tulee olla muodoltaan esimerkkitulostuksen mukainen. Huomaa, että tuloksen hinnat on tulostettava kahden desimaalin tarkkuudella. Pyöristyksen voit tehdä ROUND-funktiolla, jonka sisään voidaan sijoittaa matemaattisia lausekkeita. Vihje: Jos lausekkeen arvo ei ole tietotyyppiä NUMERIC, täytyy tehdä tyyppimuunnos CAST-funktiolla seuraavalla tavalla: ROUND(CAST(Lampojen_Keskiarvo - Paivan_Lampotila AS NUMERIC), 2) Ratkaisusi select kirjat_kirjannimi, Kirjat_Hinta as alkuperainenhinta, round(cast(Kirjat_Hinta*0.55 AS NUMERIC), 2) as alennushinta from kirjat; Omat suoritukset - Ohjelmointitehtävät - Luku 8: Tehtävä 3 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Kavereiden sähköpostiosoitteet Tehtävästä saadut pisteet 1 Tehtävänanto Tehtävänäsi on laatia SQL-kysely, jolla haetaan kaikki tiedot alla kuvatusta taulusta "Kaverit". kaverit kaverit_etunimicharacter varying(32)tff kaverit_sukunimicharacter varying(64)tff kaverit_emailcharacter varying(100)fff Kyselyn tuloksena saatavan listauksen on oltava muodoltaan seuraavanlainen. Kyselyn tulos järjestetään aakkoselliseen järjestykseen sukunimen ja etunimen mukaan mukaan. Kaverit_email-kentässä mahdolliset NULL-arvot korvataan tekstillä "ei sähköpostia". Huomioi myös kenttien otsikoiden muoto esimerkkitulostuksesta. Ratkaisusi select kaverit_etunimi as etunimi, kaverit_sukunimi as sukunimi, COALESCE (kaverit_email, 'ei sähköpostia') as email from kaverit order by sukunimi, etunimi; Omat suoritukset - Ohjelmointitehtävät - Luku 9: Tehtävä 1 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Kirjat kustantajineen Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-kysely, jolla haetaan kirjatietokannassa () olevat kirjat ja näiden kustantajat. Kyselyn tuloksessa kullakin rivillä tulostetaan ensin kirjan nimi ja tämän jälkeen kirjan kustantaja. Ratkaisusi select Kirjat_KirjanNimi, Kustantajat_KustantajanNimi from kirjat INNER JOIN kustantajat ON kirjat.Kirjat_Kustantaja_ID = kustantajat.Kustantajat_Kustantaja_ID; Omat suoritukset - Ohjelmointitehtävät - Luku 10: Tehtävä 1 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Osoitteet-taulun luominen Tehtävästä saadut pisteet 1 Tehtävänanto Tehtävänäsi on laatia SQL-lause, joka luo alla kuvatun taulun "Osoitteet". OSOITTEET Kentän nimi Kentän tietotyyppi Muut määrittelyt osoitteet_id integer not null primary key osoitteet_katu character varying(64) default value: 'ei katuosoitetta' osoitteet_postinro character varying(5) default value: 0 osoitteet_toimipaikka character varying(32) default value: 'ei toimipaikkaa' Huomaa, että taulun kentät on sijoitettava tauluun samassa järjestyksessä kuin ne esitetty yllä olevassa taulukossa. Ratkaisusi CREATE TABLE Osoitteet( osoitteet_id integer not null primary key, osoitteet_katu character varying(64) default 'ei katuosoitetta', osoitteet_postinro character varying(5) default 0, osoitteet_toimipaikka character varying(32) default 'ei toimipaikkaa' ) Omat suoritukset - Ohjelmointitehtävät - Luku 10: Tehtävä 2 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Tietojen lisääminen Osoitteet-tauluun Tehtävästä saadut pisteet 1 Tehtävänanto Edellisessä tehtävässä laatimaasi tauluun on lisätty seuraavat tiedot: Osoitteet_ID Osoitteet_Katu Osoitteet_PostiNro Osoitteet_Toimipaikka 1 Mäkikuja 33 56000 Hanhivaara 2 Karhunpolku 5 22300 Otsola 3 Opintie 22 C 3 13200 Kumpula Tehtävänäsi on lisätä Osoitteet-tauluun seuraavat tiedot: Kentän nimi Kentän arvo Osoitteet_ID 4 Osoitteet_Katu Asfalttipolku 1 B 4 Osoitteet_PostiNro 98400 Osoitteet_Toimipaikka (kenttään ei tallenneta arvoa) Ratkaisusi insert into Osoitteet values (4,'Asfalttipolku 1 B 4',98400) Omat suoritukset - Ohjelmointitehtävät - Luku 10: Tehtävä 3 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Osoitteet-taulun tietojen päivittäminen Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-lause, joka päivittää luvun ensimmäisessä tehtävässä laatimasi taulun "Osoitteet" kentän "Osoitteet_Toimipaikka" arvolla "Takapajula". Päivitettävän kentän pääavaimen (Osoitteet_ID) arvo on 4. Ratkaisusi update Osoitteet set Osoitteet_Toimipaikka = 'Takapajula' where Osoitteet_ID=4 Omat suoritukset - Ohjelmointitehtävät - Luku 10: Tehtävä 4 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Kentän lisääminen Osoitteet-tauluun Tehtävästä saadut pisteet 1 Tehtävänanto Tehtävänäsi on päivittää luvun ensimmäisessä tehtävässä laadittua taulua siten, että tauluun lisätään uusi kenttä: "Osoitteet_Maa". Kentän tietotyypiksi määritellään VARCHAR (pituus 32 merkkiä). Ratkaisusi alter table osoitteet add column Osoitteet_Maa varchar(32) Omat suoritukset - Ohjelmointitehtävät - Luku 10: Tehtävä 5 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Maatieto Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-lause, joka päivittää laatimasi Osoitteet-taulun kentän "Osoitteet_Maa" tekstillä "ei maatietoa". Päivitys suoritetaan kaikille taulussa oleville tietueille. Ratkaisusi update osoitteet set Osoitteet_Maa = 'ei maatietoa' Omat suoritukset - Ohjelmointitehtävät - Luku 10: Tehtävä 6 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Oletusarvon lisääminen Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-lause, joka asettaa laatimasi Osoitteet-taulun "Osoitteet_Maa"-kentälle oletusarvon: "ei maatietoa". Ratkaisusi ALTER table Osoitteet alter column Osoitteet_Maa SET DEFAULT 'ei maatietoa' Omat suoritukset - Ohjelmointitehtävät - Luku 10: Tehtävä 7 Piilota tehtävänanto | Piilota ratkaisusi | Näytä malliratkaisu Osoitteet-taulun poistaminen Tehtävästä saadut pisteet 1 Tehtävänanto Laadi SQL-lause, joka poistaa äskettäin laatimasi Osoitteet-taulun tietokannasta. Ratkaisusi drop table Osoitteet 9.2 Taulujen liittäminen: INNER JOIN Edellisessä kappaleessa totesimme, että voimme syventyä taulujen välisiin suhteisiin pohtimalla seuraavaa tiedonhakua: "Hae CD-arkistosta lainattujen albumien ID-numerot, sekä niiden henkilöiden etu- ja sukunimet, jotka ovat lainanneet ko. albumit". Voidaksemme toteuttaa kyselyn, joka hakee tietoja useista tauluista, voimme käyttää SQL-kielen INNER JOIN määrettä, jolla taulut yhdistetään. INNER JOIN-määreen yleinen muoto on seuraava: [taulu1] INNER JOIN [taulu2] ON [kenttä1] = [kenttä2] Edellisessä yhdistetään taulut "taulu1" ja "taulu2" kenttien "kenttä1" ja "kenttä2" mukaan. Tyypillisesti ensimmäinen kenttä on jonkin tietyn taulun pääavain ja toinen kenttä vierasavain, joka yhdistää toisen taulun ensimmäiseen. Seuraava kuva havainnollistaa, kuinka kaksi taulua yhdistyvät INNER JOIN-määreellä: Kuten kuva osoittaa, INNER JOIN-määreen avulla laadittu kysely kohdistuu siihen joukkoon kahden taulun tietueista, joita yhdistää sama kentän arvo. Seuraava SQL-kysely havainnollistaa, kuinka INNER JOIN-määrettä hyödynnetään käytännössä: Esimerkki 9.1: Lainatut albumit ja lainaajien nimet SELECT LainaajatEtunimi, LainaajatSukunimi, LainauksetAlbumit_ID FROM Lainaajat INNER JOIN Lainaukset ON Lainaajat_ID = LainauksetLainaajat_ID Edellisen kyselyn tuloksesta voidaan nähdä, että kyselyn laatimishetkellä tietokannasta on lainattu kolme albumia, joista kaksi albumia on lainattu Kalevi Härmälle: LainaajatEtunimi LainaajatSukunimi LainauksetAlbumit_ID ---------------- ----------------- -------------------- Kalevi Härmä 1 Kalevi Härmä 2 Maija Meikalainen 3 (3 row(s) affected) Kuinka siis esimerkin 9.1 kysely toimii? Kuten tähänkin mennessä esitetyissä kyselyissä, ensin ilmaistaan kyselyn tulokseen halutut kentät, jotka tällä kertaa ovat: "LainaajatEtunimi", "LainaajatSukunimi" ja "LainauksetAlbumit_ID". Kyselyn FROM-osassa kerrotaan, mistä tauluista em. kentät löytyvät. Lisäksi FROM osassa määre INNER JOIN määrittelee liitoksen mainittujen taulujen välille. INNER JOIN-määreeseen kuuluva ON-osa ilmaisee, mitkä kentät toimivat liitoksen avaimina. Avainten perusteella tiedonhallintajärjestelmä osaa poimia tiedot lainaajien nimistä ja näille lainatuista albumeista. Kyselyn tuloksena tulostetaan ne etu- ja sukunimet, joista on tallennettu merkintä "Lainaukset" tauluun. 9.3 Useiden taulujen liittäminen SQL-kieli ei aseta rajoja sille, kuinka monta taulua voimme yhdistää samaan kyselyyn - olettaen tietenkin, että yhdistettävien taulujen suhteet ovat loogisesti eheitä. Voimme täydentää esimerkin 9.1 kyselyä siten, että kyselyssä kenttä "LainauksetAlbumit_ID" korvataan albumin nimellä: Esimerkki 9.2: Lainaajien ja lainattujen albumien nimet SELECT LainaajatEtunimi, LainaajatSukunimi, AlbumitNimi FROM (Lainaajat INNER JOIN Lainaukset ON Lainaajat_ID = LainauksetLainaajat_ID) INNER JOIN Albumit ON Albumit_ID = LainauksetAlbumit_ID Nyt tuloksena olisi esim. seuraava listaus: LainaajatEtunimi LainaajatSukunimi AlbumitNimi ---------------- ----------------- ----------- Kalevi Härmä Ride the lightning Kalevi Härmä Kill em all Maija Meikalainen Master of puppets (3 row(s) affected) Edellinen SQL-kysely voi vaikuttaa aluksi hankalalta, mutta todellisuudessa sen ymmärtäminen on suhteellisen helppoa. Esimerkkiin 9.1 verrattuna kyselyyn on lisätty uusi INNER JOIN-määre, joka yhdistää kolmannen taulun (Albumit) kyselyyn. Seuraavat kuvat havainnollistavat kolmen taulun välisiä suhteita: Mikäli tunnet epävarmuutta pyrkiessäsi ymmärtämään edellisen esimerkin toimintaa, voit käyttää mielikuvitustasi ja laatia itse vastaavia kyselyitä. Käytännön kokeileminen on usein nopea tie monimutkaisten kyselyiden sisäistämiseen. Kun koet ymmärtäneesi esimerkin, voit ryhtyä lukemaan seuraavaa lukua, jossa käsitellään lisää liitosten muodostamiseen liittyviä asioita. 10.3 Taulun rakenteen muokkaaminen Vaikka tietokanta suunniteltaisiin huolellisesti ja se täyttäisi sille asetetut vaatimukset erinomaisesti, sen rakennetta saatetaan haluta muokata ajan myötä. Tietokannan muokkaaminen tarkoittaa käytännössä taulujen lisäämistä, poistamista ja olemassa olevien taulujen muokkaamista. Tässä kappalleessa tarkastellaan, kuinka tietokannassa olevan taulun rakennetta voidaan muokata. Kun tauluun halutaan jälkikäteen lisätä uusi kenttä, voidaan käyttää ALTER-komentoa: Esimerkki 10.7: Kentän lisääminen tauluun Osoitteet ALTER TABLE Osoitteet ADD COLUMN Email VARCHAR(64) Vastaavasti taulusta voidaan poistaa kenttiä: Esimerkki 10.8: Kentän poistaminen taulusta Osoitteet ALTER TABLE Osoitteet DROP COLUMN Email RESTRICT Edellinen esimerkki poistaisi kentän "Email" taulusta "Osoitteet". Määre RESTRICT estää kentän poistamisen, mikäli kenttään on määritelty jokin viiteyhteys. Vaihtoehtoisesti voidaan käyttää CASCADE -määrettä, jolloin kenttä ja sen viite-eheyteen liittyvät määreet (esim. vierasavain) poistetaan. Kenttiä poistettaessa juuri viite-eheyteen on kiinnitettävä huomiota - koko tietokannan käyttö voi estyä kriittisen kentän poistamisen tuloksena. ALTER-komennon avulla voi luonnollisesti muokata kenttää monin eri tavoin. Seuraavassa esimerkissä havainnollistetaan, kuinka kentälle voidaan asettaa oletusarvo DEFAULT-määreellä: Esimerkki 10.9: Kentälle DEFAULT-arvo ALTER TABLE Henkilot ALTER COLUMN SyntymaPvm SET DEFAULT '0000-00-00' 10.4 Taulun poistaminen tietokannasta Tietokannasta voidaan poistaa kokonaisia tauluja SQL-kielen DROP TABLE -komennolla. Taulua poistettaessa voidaan valita kolmesta vaihtoehdosta sopivin: taulun poistaminen viite-eheysmääreistä riippumatta taulun poistaminen jos viite-eheysmääreitä ei ole taulun ja sen sidostaulujen poistaminen Kun jokin taulu halutaan poistaa riippumatta siitä, onko taulu sidoksissa muihin tauluihin, voidaan käyttää DROP TABLE -komentoa ilman määreitä: Esimerkki 10.10: Taulun poistaminen DROP TABLE Osoitteet Mikäli edelliseen lauseeseen lisätään määre "RESTRICT", taulua ei poisteta jos se on viiteyhteydessä johonkin muuhun tauluun: Esimerkki 10.11: Taulun poistaminen (RESTRICT) DROP TABLE Osoitteet RESTRICT Mikäli halutaan poistaa taulu, sekä ne taulut, jotka ovat viiteyhteydessä poistettavaan tauluun voidaan käyttää määrettä "CASCADE": Esimerkki 10.12: Taulun poistaminen (CASCADE) DROP TABLE Osoitteet CASCADE INSERT INTO Lainaajat VALUES (1, 'Esko', 'Tahvanainen', '0503787843', 'esko@email.com') UPDATE [taulu] SET [kenttä1 = uusi_arvo1, ... kenttäN = uusi_arvoN] WHERE [ehto] UPDATE-komennon havainnollistamiseksi päivitetään Esko Tahvanaisen sähköpostiosoite: Esimerkki 5.4: Eskolle uusi sähköpostiosoite UPDATE Lainaajat SET LainaajatEmail = 'esko.tahvanainen@email.com' WHERE LainaajatEtunimi= 'Esko' AND LainaajatSukunimi = 'Tahvanainen' Huomaa, että päivitettäessä yksittäisen tietueen kenttiä, on ehto-osan muodostamisessa oltava tarkkana. Mikäli edellisessä esimerkissä ei olisi ehtoa, jolla juuri tietty kenttä löydetään, tuloksena olisi kaikkien sähköpostikenttien päivittäminen samalla sähköpostiosoitteella. UPDATE-komennon yhteydessä voidaan käyttää matemaattisia operaatioita samoin kuin esimerkiksi käytettäessä SELECT-lausetta. Tämän havainnollistamiseksi otetaan esimerkki, jossa kasvatetaan kentän .Kentta1. sisältämiä arvoja: