Tällä viikolla Kotitehtävien läpikäynti SQL-harjoituksia, osa 1 Jatketaan ”Pelifirman” tietovaraston suunnittelua: tietotyyppien kertaus, taulun luonti ER-kaavioon, taulun luonti kaavion avulla tietokantaan, kaavion luonti tietokannasta SQL-harjoituksia, osa 2 Seuraavat tehtävät koskevat tietokantaa classicmodels. Eli ennen kyselyiden tekoa aktivoi tuplaklikkaamalla MySQL:sta ensin tietokanta classicmodels. Vastaukset tulee olla SQL-kyselyitä. Sulkeissa on joitain vinkkejä kyselyyn. 1. Saat selville yrityksen "president"-tittelin omaavan työntekijän? SELECT * FROM employees WHERE jobTitle = 'President'; 2. Haet työntekijöiden tiedot etunimen mukaan nousevasti järjestettyinä. (ORDER BY) SELECT * FROM employees ORDER BY firstName ASC; 3. Haet työntekijöiden tiedot tittelin mukaan laskevasti järjestettyinä. (ORDER BY) SELECT * FROM employees ORDER BY jobTitle DESC; 4. Haet kaikki toimistot jotka eivät sijaitse USAssa tai Ranskassa. SELECT * FROM offices WHERE country <> 'USA' AND country <> 'FRANCE'; 5. Haet kaikki tuotteet, joiden ostohinta on 90 ja 100 välillä. (BETWEEN) SELECT * FROM products WHERE buyPrice BETWEEN 90 AND 100; Seuraavat tehtävät koskevat tietokantaa classicmodels. Eli ennen kyselyiden tekoa aktivoi tuplaklikkaamalla MySQL:sta ensin tietokanta classicmodels. Vastaukset tulee olla SQL-kyselyitä. Sulkeissa on joitain vinkkejä kyselyyn. 6. Haet kaikkien asiakkaiden tiedot joille ei ole määritelty osavaltio-tietoa. (NULL) SELECT * FROM customers WHERE ISNULL(state); 7. Selvität mikä on kaikki tuotteiden yhteenlaskettu ostohinta (kenttä buyPrice)? SELECT SUM(buyPrice) FROM products; 8. Selvität mikä on kaikkien tuotteiden ostohintojen keskiarvo? SELECT AVG(buyPrice) FROM products; 9. Haet suoritettujen maksujen summat asiakkaittain (GROUP BY, SUM) SELECT customerNumber, SUM(amount) FROM payments GROUP BY customerNumber; 10. Haet tilausten tuotteiden yhteismäärät per tuote (GROUP BY, SUM) SELECT productCode, SUM(quantityOrdered) FROM orderdetails GROUP BY productCode; Kyselyiden yhdistäminen, UNION / UNION ALL Sisäkyselyt Tekstinkäsittelyfunktioita: UPPER, LOWER, CONCAT, SUBSTRING, RIGHT, LEFT, LENGTH Kyselyiden yhdistäminen, UNION / UNION ALL: SELECT city FROM offices UNION SELECT city FROM customers Sisäkyselyt: Kyselyä voi käyttää toisen kyselyn lähteenä tai vaikka WHEREehdossa. WHERE-ehdossa eksakti vertailu (=) yhteen arvoon tai joukkoon (IN): SELECT COUNT(*) FROM (SELECT city FROM offices UNION ALL SELECT city FROM customers) AS temp SELECT * FROM customers WHERE city IN (SELECT city FROM offices WHERE country='UK') Yhdistä UNION-kyselyllä kaksi SELECT-kyselyä, joissa haetaan kaikki kentät tuotetaululle kun 1) productLine='Planes' tai 2) productLine='Ships' SELECT * FROM products WHERE productLine='Planes„ UNION SELECT * FROM productsWHERE productLine='Ships„ Miten saat samat vastausrivit kuin edellä yhdellä kyselyllä (ilman UNIONia)? SELECT * FROM products WHERE productLine='Ships„ OR productLine='Planes„ Montako eri maata on tauluissa customers ja offices? SELECT COUNT(*) FROM (SELECT country FROM offices UNION SELECT country FROM customers) AS temp Hae tilaukset asiakkaalle, jonka nimi on 'Royale Belge„: SELECT * FROM orders WHERE customerNumber= (SELECT customerNumber FROM customers WHERE customerName='Royale Belge') Hae nimi (customerName) niille asiakkaille, joilla on tilauksia statuksella 'Cancelled'. Käytä sisäkyselyä. SELECT customerNameFROM customersWHERE customerNumber IN (SELECT customerNumber FROM orders WHERE status='Cancelled') - Yleisimmin käytetyt tietotyypit: VARCHAR(nn) – muuttuvanmittainen merkkijono CHAR(n) – kiinteänmittainen merkkijono INT – kokonaisluku DATE – päivämäärä Käytetään yo. Tietotyyppejä P12tietovaraston tauluissa - - Parityöskentelynä luodaan ER-malliin taulut. Kukin pari mallintaa listalta yhden tai kaksi taulua. Taulujen pääavaimet (PK) on määrätty ja lisäksi listalla on joitain viiteavaimia (FK) malliksi. Yritys (PK yritys_id) Osasto (PK osasto_id) (FK yritys_id) Henkilo (PK henkilo_id) (FK osasto_id) Koulutus (PK koulutus_id) Koulutus_linkki (FK koulutus_id, henkilo_id) Kokemus (PK kokemus_id) Kokemus_linkki (FK kokemus_id, henkilo_id) Pelit (PK peli_id) (FK osasto_id) Asiakas (PK asiakas_id) Toimittaja (PK toimittaja_id) Osto (PK osto_id) (FK toimittaja_id, osasto_id) Myynti (PK myynti_id) (FK asiakas_id, peli_id, osasto_id) Kalenteri (PK pvm_id) Tekstinkäsittelyfunktioita: UPPER - muuttaa merkit isoiksi (uppercase) SELECT UPPER(customerName) FROM customers LOWER - muuttaa merkit pieniksi (lowercase) SELECT LOWER(customerName) FROM customers SELECT 'Teksti' - palauttaa kirjoitetun tekstin CONCAT - yhdistää tekstimuotoisia kenttiä tai kirjoitettua tekstiä SELECT CONCAT(firstName, ' ', lastName) FROM employees SUBSTRING - merkkijonon haku SELECT SUBSTRING(productname, 5, 8) FROM products RIGHT - hakee kentän oikeanpuoleisia merkkejä n kappaletta SELECT RIGHT(email,4) FROM employees LEFT - hakee kentän vasemmanpuoleisia merkkejä n kappaletta SELECT LEFT(productName,4) FROM products LENGTH - kentän pituus = merkkien määrä SELECT LENGTH(firstName) FROM employees - Hae products-taulusta kaikki Ferrarit - Hae products-taulusta kaksi kenttää, joissa ensimmäisessä lukee 'Ferrari' ja toisessa 'Classic car'. Hyödynnä edellistä kyselyä. Tulokseen tulee kaksi riviä. - Edellisen tehtävän tulokseen tuli kaksi riviä. Millä yhdellä määreellä saat karsittua tuplarivin pois? - Muodosta yhteen kenttään teksti, jossa ensin tuotteen nimi, sitten kaksoispiste ja lopuksi tuotekuvaus - Sama kuin edellä, mutta muuta tuotenimi isoiksi kirjaimiksi ja tuotekuvaus pieniksi kirjaimiksi - Hae työntekijöiden etunimi ja toiseen kenttään etunimen pituus - Tee kahden kentän kysely, jossa ensimmäisessä lukee "Etunimi" toisessa on työntekijän etunimi - Tee sama kuin edellä, mutta tiedot yhteen kenttään ja välilyönti ennen nimeä - Muodosta "ohjelause peliin": "Valitse ajoneuvo:" ja sitten lista ajoneuvoista. (UNION-kysely) - Hae eri kenttiin työntekijöiden etunimen alkukirjain ja sukunimen alkukirjain - Hae työntekijöiden nimikirjaimet muodossa (esim.) "E.S." eli etunimen alkukirjain ja sukunimen alkukirjain - Hae tuotteen S10_2016 tuotekuvauksesta sana "logos“ 1.) Lukutehtävä. Kertaa edellisen viikon kotitehtävät ja tämän viikon uudet SQL-asiat tästä kalvosarjasta. Tehtävän suoritusta ei tarvitse merkitä mihinkään. 2.) Laita tekemästäsi omasta SQL-oppaasta kopio GoogleDriveen opettajalle jakoon Ensi viikolla jatkamme kesken jääneitä teksti-funktioharjoituksia tunnilla.
© Copyright 2024