SQL-kyselyt - Demo 3
Näissä demoissa opetellaan SQL-kyselyjen perusteita.
Alkuvalmistelut
- Käynnistä Microsoft Access ja luo uusi tietokanta c:\mytemp\-kansioon nimelle reseptit.mdb. Älä käytä aiemmissa demoissa luomaasi reseptitietokantaa!
- Tarkista, että Accessin optioissa on valittuna suomenkielinen järjestys eli Tools | Options | General | New database sort order | Swedish/Finnish. Uuden Office 2007:n Accessissa sama asetus löytyy Office-napin kautta kohdasta Access options | Popular | New database sort order.
- Sulje Microsoft Access.
- Käynnistä Erkka-ohjelma (Start | Programs | Erkka)
- Siirry Query-välilehdelle ja avaa yhteys edellä luomaasi Access-tietokantaan
- Kopioi leikepöydälle resepti.sql-tiedoston sql-lauseet
- Liitä SQL-lauseet Erkan Query-välilehdelle SQL-statement-laatikkoon.
- Suorita SQL-lauseet (Execute)
- Poista kaikki SQL-lauseet
Käytössäsi on nyt seuraavan kuvan esittämä tietokanta:
SQL-kyselyt
Kirjoita seuraavat SQL-kyselyt. Voit kirjoittaa kaikki kyselyt samaan tiedostoon kunhan erotat ne puolipisteellä (;) toisistaan. Tallenna kyselyt U:-asemalle demo3.sql-nimelle. Muista tallentaa tarpeeksi usein. Vinkkejä kyselyjen tekemiseen löytyy luento 5 - ja luento 6 -sivuilta ja kurssimonisteesta.
- Hae kaikki tiedot reseptitaulusta. Kyselyn tuloksena pitäisi tulla seuraavat rivit:
nimi kuvaus henkilomaara reseptiid ruokalajiid Ananas Con Carne Tulinen 4 1 2 Tiramisu Hieno herkku 6 2 7 Banaanicurry Mainio kasvisruoka 2 3 2 Porkkanapiirakka Suuri herkku 8 4 7 Rommikakku Hyytelökakku, ei tarvi paistaa 6 5 7 Kauralastut Helppo ja nopea 4 6 7 - Hae ruokalajien nimet. Kyselyn tuloksena pitäisi tulla seuraavat rivit:
nimi Alkuruoka Pääruoka Lisäkeruoka Väli - tai iltapala Jälkiruoka Suolainen leivonnainen Makea leivonnainen - Hae yksiköiden lyhenteet ja nimet. Kyselyn tuloksena pitäisi tulla seuraavat rivit:
lyhenne nimi kpl Kappale dl Desilitra l Litra g Gramma kg Kilogramma tl Teelusikka rkl Ruokalusikka - Hae kaikkien niiden reseptien nimet ja henkilömäärät, jotka on tarkoitettu neljälle henkilölle. Kyselyn tuloksena pitäisi tulla seuraavat rivit:
nimi henkilomaara Ananas Con Carne 4 Kauralastut 4 - Hae kaikkien niiden reseptien nimet ja henkilömäärät, jotka ovat vähintään neljälle henkilölle.
Kyselyn tuloksena pitäisi tulla seuraavat rivit:
nimi henkilomaara Ananas Con Carne 4 Tiramisu 6 Porkkanapiirakka 8 Rommikakku 6 Kauralastut 4 - Hae kaikkien niiden reseptien nimet ja henkilömäärät, jotka ovat kahdelle, kolmelle tai neljälle henkilölle käytä IN-lausetta. Kyselyn tuloksena pitäisi tulla seuraavat rivit:
nimi henkilomaara Ananas Con Carne 4 Banaanicurry 2 Kauralastut 4 - Tee edellinen myös käyttäen BETWEEN-lausetta.
- Hae kaikkien niiden reseptien nimet ja henkilömäärät joita ei ole tarkoitettu neljälle henkilölle. Kyselyn tuloksena pitäisi tulla seuraavat rivit:
nimi henkilomaara Tiramisu 6 Banaanicurry 2 Porkkanapiirakka 8 Rommikakku 6 - Hae kaikkien niiden reseptien nimet, joiden nimi alkaa P-kirjaimella. Kyselyn tuloksena pitäisi tulla seuraava rivi:
nimi Porkkanapiirakka - Hae kaikkien niiden reseptien nimet, joiden nimi loppuu u-kirjaimeen. Kyselyn tuloksena pitäisi tulla seuraavat rivit:
nimi Tiramisu Rommikakku - Hae kaikkien niiden reseptien nimet, henkilomaara ja ruokalajiid, joiden ruokalajiid on 7 ja jotka on tarkoitettu yli kuudelle henkilölle.
Kyselyn tuloksena pitäisi tulla seuraava rivi:
nimi henkilomaara ruokalajiid Porkkanapiirakka 8 7 - Hae kaikkien niiden reseptien nimet ja henkilömäärät, jotka ovat kahdelle, kolmelle tai neljälle henkilölle. Alä käytä IN- tai BETWEEN-lausetta! Kyselyn tuloksena pitäisi tulla seuraavat rivit:
nimi henkilomaara Ananas Con Carne 4 Banaanicurry 2 Kauralastut 4 - Hae kaikkien niiden reseptien nimet, joiden ruokalajiid on 7 ja jotka on tarkoitettu neljälle henkilölle. Kyselyn tuloksena pitäisi tulla seuraava rivi:
nimi henkilomaara ruokalajiid Kauralastut 4 7 - Hae kaikkien niiden reseptien nimet joiden ruokalajiid on 2 TAI jotka on tarkoitettu neljälle henkilölle. Kyselyn tuloksena pitäisi tulla seuraavat rivit:
nimi henkilomaara ruokalajiid Ananas Con Carne 4 2 Banaanicurry 2 2 Kauralastut 4 7 - Hae reseptien nimet ja järjestä ne nousevaan aakkosjärjestykseen. Kyselyn tuloksena pitäisi tulla seuraavat rivit seuraavassa järjestyksessä:
nimi Ananas Con Carne Banaanicurry Kauralastut Porkkanapiirakka Rommikakku Tiramisu - Hae reseptien nimet ja henkilömäärät ja järjestä ne ensisijaisesti henkilömäärien mukaan nousevaan järjestykseen ja edelleen nimien mukaan nousevaan aakkosjärjestykseen.
Kyselyn tuloksena pitäisi tulla seuraavat rivit:
nimi henkilomaara Banaanicurry 2 Ananas Con Carne 4 Kauralastut 4 Rommikakku 6 Tiramisu 6 Porkkanapiirakka 8 - Hae reseptin nimi ja siihen liittyvän ruokalajin nimi. Joudut käyttämään kahden taulun välistä liitosta kyselyssä. Liitosta tehdessäsi kannattaa katsoa demojen alussa olevaa taulukaaviota! Kyselyn tuloksena pitäisi tulla seuraavat rivit:
nimi ruokalaji Ananas Con Carne Pääruoka Banaanicurry Pääruoka Tiramisu Makea leivonnainen Porkkanapiirakka Makea leivonnainen Rommikakku Makea leivonnainen Kauralastut Makea leivonnainen - Hae reseptin nimi ja reseptiin kuuluvat ohjeet. Järjestä hakutulos reseptien nimien
mukaan aakkosjärjestykseen ja ohjeet numerojärjestykseen.
Huomaa, että Erkka saattaa levittää tuloslistauksen näyttöä leveämmäksi.
Kyselyn tuloksena pitäisi tulla seuraavat rivit:
nimi vaihenro ohjeteksti Ananas Con Carne 1 Kuumenna öljy padassa ja ruskista lihaa ja sipulia kovalla lämmöllä noin 10-15 minuuttia koko ajan sekoittaen, kunnes neste on haihtunut. Ananas Con Carne 2 Mausta suolalla ja chilillä (kannattaa olla varovainen;) ja puserra pataan valkosipulia. Kaada nesteeksi tomaatit liemineen javähennä lämpöä. Sekoita pataa silloin tällöin. Kaada sekaan 1.5 dl vettä ja lisää lihaliemikuutio. Ananas Con Carne 3 nostele pataan paprika, purjo ja selleri. Kaada joukkoon ananasmurska ja pavut. Jos jätät ananasmurskan pois niin tuloksena on tavallinen chili con carne. Ananas Con Carne 4 Tarjoa keitetyn riisin kera. Banaanicurry 1 Silppua sipuli ja murskaa valkosipulinkynsi Banaanicurry 2 Kuumenna pannussa öljyä ja kuullota sipulisilppu Banaanicurry 3 Lisää curryjauhe, valkosipuli, mangochutney ja suola Banaanicurry 4 Kiehuta miedolla lämmöllä viisi minuuttia, lisää kerma ja kiehuta viela viisi minuuttia. Banaanicurry 5 Lisää ruuanvalmistusjugurtti. Lämmitä mutta älä keitä koska jugurtti juoksettuu helposti. Banaanicurry 6 Lohko banaanit viipaleiksi ja sekoita joukkoon. Banaanicurry 7 Tarjoaa persiljariisin kera. Kauralastut 1 Sekoita kaikki aineet sulaneeseen rasvaan. Kauralastut 2 Nosta taikinasta pellille leivinpaperille pieniä nokareita kahdella lusikalla. Jätä reilut välit. Kauralastut 3 Paista kauralastuja n. 5 minuuttia 200 asteessa. Irrota leivinpaperista hieman jäähtyneinä. Porkkanapiirakka 1 Munat ja sokeri vaahdoksi. Porkkanapiirakka 2 Jauhot sekaisin, lisataan vaahtoon. Porkkanapiirakka 3 Sulatettua voita 175g ja raaste lisätään varovasti sekoittaen. Porkkanapiirakka 4 Sulata loppu voi (ei ruskisteta) pilko juusto sekaan, miedolla lämmöllä kunnes juusto sulaa. Porkkanapiirakka 5 Nosta levyltä, lisää sokerit. Levitä heti leivoslevyn päälle Rommikakku 1 Vatkaa keltuaiset ja sokeri vahdoksi. Rommikakku 2 Lisää vatkattu kerma ja rommi vaahtoon Rommikakku 3 Liota lehtiä kulmassa vedessä, liuota sitten kiehuvaan veteen, anna hieman jäähtyä ennen vaahtoon lisäämistä. Rommikakku 4 Anna hyytyä jääkaapissa ennen tarjoamista n. 2 h. Mieluummin yön yli. Tiramisu 1 Vatkaa munat ja sokeri vaahdoksi. Sekoita muut aineet keskenään ja seulo vaahtoon Tiramisu 2 Kaada taikina leivinpaperilla vuorattuun uunipannuun. Paista 200 asteessa n.12min (silmät tarkkana ;) Tiramisu 3 ripota leivinpaperille sokeria, kumoa levy sille ja poista leivinpaperi. Anna jäähtyä. Tiramisu 4 Liota liivatelehdet kuumaan kahviin. Anna jäähtyä. Tiramisu 5 Vatkaa keltuaiset ja sokeri vaahdoksi, lisää vaahtoon juusto, ranskankerma, mantelilikööri ja liivateseos. Vaahdota kerma. Vatkaa valkuaiset kovaksi vaahdoksi. Lisää molemmat varovasti seokseen. Tiramisu 6 Leikkaa kakkulevy kahtia, kostuta levyt kahvi-likööriseoksella. Pane toinen puolikas sopivan kokoiseen vuokaan. Levitä puolet täytteestä levylle, nosta toinen levy ja levitä päällimmäiseksi loput täytteestä. Tasoita ja ripota pinnalle kaakaojauhetta. Tiramisu 7 Anna jähmettyä jääkaapissa. - Hae reseptin nimi ja siihen liittyvien aineiden nimet, ainemäärät ja ainemäärän yksiköt reseptin nimen ja aineiden nimien mukaan järjestettynä. Kyselyn tuloksena pitäisi tulla seuraavat rivit:
nimi maara yksikko_lyhenne aine Ananas Con Carne 450.00 g Ananasmurska Ananas Con Carne 2.00 tl Cayennepippuri Ananas Con Carne 400.00 g Jauheliha Ananas Con Carne 2.00 kpl Lehtiselleri Ananas Con Carne 1.00 kpl Lihaliemikuutio Ananas Con Carne 1.00 kpl Purjo Ananas Con Carne 4.00 kpl Sipuli Ananas Con Carne 1.00 tl Suola Ananas Con Carne 400.00 g Tomaattimurska Ananas Con Carne 400.00 g Valkoiset pavut tomaattikastikkeessa Ananas Con Carne 3.00 kpl Valkosipulinkynsi Ananas Con Carne 5.00 rkl Öljy Banaanicurry 2.00 kpl Banaani Banaanicurry 2.00 rkl Curry Banaanicurry 3.00 rkl Mangochutney Banaanicurry 2.00 dl Ruuanvalmistusjugurtti Banaanicurry 1.00 kpl Sipuli Banaanicurry 1.00 tl Suola Banaanicurry 3.00 kpl Valkosipulinkynsi Banaanicurry 1.00 rkl Öljy Kauralastut 1.00 kpl Kananmuna Kauralastut 1.50 dl Kaurahiutale Kauralastut 1.00 tl Leivinjauhe Kauralastut 50.00 g Margariini Kauralastut 1.00 dl Sokeri Kauralastut 1.00 tl Vaniljasokeri Kauralastut 2.00 rkl Vehnäjauho Porkkanapiirakka 4.00 kpl Kananmuna Porkkanapiirakka 2.00 tl Leivinjauhe Porkkanapiirakka 200.00 g Maustamaton koskenlaskijajuusto Porkkanapiirakka 6.00 dl Porkkanaraaste Porkkanapiirakka 3.00 dl Sokeri Porkkanapiirakka 3.00 tl Sooda Porkkanapiirakka 3.00 dl Tomusokeri Porkkanapiirakka 3.00 tl Vaniljasokeri Porkkanapiirakka 4.00 dl Vehnäjauho Porkkanapiirakka 250.00 g Voi Rommikakku 200.00 g Digestivekeksi Rommikakku 4.00 kpl Keltuainen Rommikakku 4.00 dl Kuohukerma Rommikakku 5.00 kpl Liivatelehti Rommikakku 100.00 g Margariini Rommikakku 3.00 rkl Rommi Rommikakku 1.50 dl Sokeri Rommikakku 1.00 rkl Sokeri Tiramisu 2.00 dl Kahvi Tiramisu 4.00 kpl Kananmuna Tiramisu 3.00 kpl Keltuainen Tiramisu 2.00 dl Kuohukerma Tiramisu 1.00 tl Leivinjauhe Tiramisu 4.00 kpl Liivatelehti Tiramisu 6.00 rkl Mantelilikööri Tiramisu 300.00 g Maustamaton tuorejuusto Tiramisu 0.75 dl Perunajauho Tiramisu 1.00 dl Ranskankerma Tiramisu 1.50 dl Sokeri Tiramisu 2.00 dl Tomusokeri Tiramisu 3.00 kpl Valkuainen Tiramisu 1.00 dl Vehnäjauho - Hae Porkkanapiirakkareseptiin kuuluvien valmistusaineiden nimet ja niitä tarvittavat määrät yksiköineen. Järjestä tiedot valmistusaineiden mukaan aakkosjärjestykseen. Kyselyn tuloksena pitäisi tulla seuraavat rivit:
nimi maara yksikko_lyhenne aine Porkkanapiirakka 4.00 kpl Kananmuna Porkkanapiirakka 2.00 tl Leivinjauhe Porkkanapiirakka 200.00 g Maustamaton koskenlaskijajuusto Porkkanapiirakka 6.00 dl Porkkanaraaste Porkkanapiirakka 3.00 dl Sokeri Porkkanapiirakka 3.00 tl Sooda Porkkanapiirakka 3.00 dl Tomusokeri Porkkanapiirakka 3.00 tl Vaniljasokeri Porkkanapiirakka 4.00 dl Vehnäjauho Porkkanapiirakka 250.00 g Voi - Laske montako eri ainetta tietokannassa on listattu.
Tarvitset avuksesi yhteenvetofunktiota.
Kyselyn tuloksena pitäisi tulla seuraava rivi:
Aineita 40 - Laske paljonko kaikkiin resepteihin yhteensä tarvitaan kananmunia. Kyselyn tuloksena pitäisi tulla seuraava rivi:
Kananmuna 9.00 - Laske montako reseptiä liittyy kuhunkin ruokalajiin. Kyselyssä joudut käyttämään ryhmittelyä!. Ryhmittelyn perusongelmista on oma vinkkisivu. Kyselyn tuloksena pitäisi tulla seuraavat rivit:
ruokalajiid nimi lukumaara 2 Pääruoka 2 7 Makea leivonnainen 4 - Laske montako valmistusvaihetta liittyy kuhunkin reseptiin. Kyselyn tuloksena pitäisi tulla seuraavat rivit:
nimi lukumaara Ananas Con Carne 4 Tiramisu 7 Banaanicurry 7 Porkkanapiirakka 5 Rommikakku 4 Kauralastut 3 - Laske paljonko kutakin ainetta tarvitaan kaikissa resepteissä yhteensä. Varmista, että lasket yhteen vain samassa yksikössä ilmoitetut ainemäärät. Järjestä aineet aakkosjärjestykseen aineen nimen ja käytetyn yksikön mukaan. Kyselyn tuloksena pitäisi tulla seuraavat rivit:
nimi lukumaara yksikko_lyhenne Ananasmurska 450.00 g Banaani 2.00 kpl Cayennepippuri 2.00 tl Curry 2.00 rkl Digestivekeksi 200.00 g Jauheliha 400.00 g Kahvi 2.00 dl Kananmuna 9.00 kpl Kaurahiutale 2.50 dl Keltuainen 7.00 kpl Kuohukerma 6.00 dl Lehtiselleri 2.00 kpl Leivinjauhe 4.00 tl Lihaliemikuutio 1.00 kpl Liivatelehti 9.00 kpl Mangochutney 3.00 rkl Mantelilikööri 6.00 rkl Margariini 150.00 g Maustamaton koskenlaskijajuusto 200.00 g Maustamaton tuorejuusto 300.00 g Perunajauho 0.75 dl Porkkanaraaste 6.00 dl Purjo 1.00 kpl Ranskankerma 1.00 dl Rommi 3.00 rkl Ruuanvalmistusjugurtti 2.00 dl Sipuli 5.00 kpl Sokeri 7.00 dl Sokeri 1.00 rkl Sooda 3.00 tl Suola 2.00 tl Tomaattimurska 400.00 g Tomusokeri 5.00 dl Valkoiset pavut tomaattikastikkeessa 400.00 g Valkosipulinkynsi 6.00 kpl Valkuainen 3.00 kpl Vaniljasokeri 4.00 tl Vehnäjauho 5.00 dl Vehnäjauho 2.00 rkl Voi 250.00 g Öljy 6.00 rkl - Listaa aakkosjärjestyksessä niiden reseptien nimet, joihin liittyy yli neljä valmistusvaihetta. Listaa myös valmistusvaiheiden lukumäärä.
Kyselyn tuloksena pitäisi tulla seuraavat rivit:
nimi count Banaanicurry 7 Porkkanapiirakka 5 Tiramisu 7 -
Listaa aakkosjärjestyksessä niiden reseptien nimet, jotka on tarkoitettu
vähintään kuudelle henkilölle ja joihin liittyy yli viisi valmistusvaihetta. Listaa myös valmistusvaiheiden lukumäärä ja henkilömäärä.
Kyselyn tuloksena pitäisi tulla seuraava rivi:
nimi henkilomaara vaiheet Tiramisu 6 7
Käyttäjien kommentit
;-) Mikäli tehtävän 22. esimerkkivastauksen perusteella pitäisi keittiöön hakea tarvikkeet IT-Tiedekunnan syysjuhlan tarjoiluja vasten, niin jäisi kattaus vajaaksi. Vielä kaupasta ei voi ostaa erikseen keltuaista/valkuaista, joita osassa resepteissä tarvitaan. Siis kananmunia tarvitaan yhteensä 16 kpl, mikäli halutaan saada kaikki reseptit valmistettua ohjeen mukaisesti. Tehtävään 25 lisäämällä oheisen rivin loppuun saadaan HAVING Aine.Nimi='Kananmuna' OR Aine.Nimi='Keltuainen' Tämäkään ei vielä ole täydellinen ja ei toimi silloin kun reseptissä on ainoastaan valkuaista.