Tietokannat kurssi KSAO Datanomit kytn tuki kevt 2015

Tietokannat -kurssi KSAO, Datanomit, käytön tuki kevät 2015 Lauri Tapola

Tietokantakyselyt SQL funktiot 10. 3. 2015 Lauri Tapola

SQL-kyselyt. Laskentafunktiot • Yleisimpiä laskentafunktioita: • AVG() keskiarvo - Returns the average value • COUNT() lukumäärä - Returns the number of rows • FIRST() ensimmäinen - Returns the first value • LAST() viimeinen - Returns the last value • MAX() suurin arvo - Returns the largest value • MIN() pienin arvo - Returns the smallest value • SUM() summa - Returns the sum

SQL Laskentafunktiot, s 1 Ohjeita löytyy mm. : http: //www. w 3 schools. com/sql/ Esimerkkejä tarvittavista kyselyistä: • 1. Mikä on tuotteiden keskimääräinen ostohinta? • 2. Mikä on kallein tuote? • 3. Mikä on halvin tuote? • 4. Kuinka monta asiakasta tietokannassa on? • 5. Kuinka monta tuotetta on varastossa? • 6. Mikä on varaston arvo? Tuotteiden määrä * hinta.

SQL Laskentafunktiot, s 2 Ohjeita löytyy mm. : http: //www. w 3 schools. com/sql/ • Seuraavat tehtävät käsittelevät tietokantaa classicmodels. • Varmista oikea tietokanta komennolla: USE classicmodels • 1. Mikä on tuotteiden keskimääräinen ostohinta? • SELECT avg(buy. Price) FROM products • 2. Mikä on kallein tuote? • Kallein hinta: SELECT max(buy. Price) FROM products • 3. Mikä on halvin tuote? • Halvin hinta: SELECT min(buy. Price) FROM products • HUOM! Tuotteet hinta-järjestyksessä: SELECT productname, buy. Price FROM products ORDER BY buy. Price

SQL Laskentafunktiot, s 3 Ohjeita löytyy mm. : http: //www. w 3 schools. com/sql/ • 4. Kuinka monta asiakasta tietokannassa on? • SELECT count(*) FROM customers • 5. Kuinka monta tuotetta on varastossa? • SELECT sum(quantityinstock) FROM products • 6. Mikä on varaston kokonaisarvo? Tuotteiden määrä * hinta. • SELECT sum(quantity. In. Stock*buy. Price) as arvo FROM products • Pyöristettynä 1 desimaalilla: • SELECT round(sum(quantity. In. Stock*buy. Price), 1) as arvo FROM products

SQL Laskentafunktiot, s 4 Ohjeita löytyy mm. : http: //www. w 3 schools. com/sql/ • Lisää hyödyllisiä laskentaan liittyviä funktioita, käyttöesimerkkejä: • ROUND() pyöristys - Rounds a numeric field to the number of decimals specified • NOW() tai sysdate() aika - Returns the current system date and time • FORMAT() muotoilu - Formats how a field is to be displayed • TIME_FORMAT() päivämäärän ja ajan muotoilu

SQL Laskentafunktiot, s 5 Ohjeita löytyy mm. : http: //www. w 3 schools. com/sql/ • Lisää hyödyllisiä laskentafunktioita, käyttöesimerkkejä : • 7. Valitse tuotteen nimi ja ostohinta. Pyöristä hinta sitten yhden desimaalin tarkkuudella ja kokonaisluvuksi. • SELECT product. Name, buy. Price, round(buy. Price, 1) as hinta_1 d, round(buy. Price) as hinta_0 d FROM products • 8. Mikä on tietokannan tämänhetkinen kellonaika ja päivämäärä? • SELECT now() , sysdate(), date(now()), time(now()) • 9. Muotoile päivämäärä suomalaiseen tyyliin 10. 03. 2015 ja 10. 03. 15. • SELECT date_FORMAT(date(Now()), '%d. %m. %Y') AS Fin. Date 1, date_FORMAT(date(Now()), '%e. %m. %y') AS Fin. Date 2

SQL Laskentafunktiot, s 5 Ohjeita löytyy mm. : http: //www. w 3 schools. com/sql/ • Käytännössä laskentafunktioita hyödynnettäessä tietoja pitää ryhmitellä. • GROUP BY - komento lisätään SQL-lauseen loppuun. • 10. Kuinka monta tuotetta tuoteryhmäkohtaisesti on varastossa? • SELECT productline, sum(quantityinstock) FROM products GROUP BY productline • 11. Kuinka monta eri tuotetta on kussakin tuoteryhmässä? • SELECT productline, count(productcode) FROM products GROUP BY productline

SQL Laskentafunktiot, s 6 Ohjeita löytyy mm. : http: //www. w 3 schools. com/sql/ • 12. Mikä on tuotteiden kokonaismäärä tuoteryhmittäin ? SELECT productline, sum(quantity. In. Stock) as maara FROM products GROUP BY productline ‘sama kuin 10. • 13. Mikä on varaston kokonaisarvo tuoteryhmittäin pyöristettynä kokonaisluvuksi? SELECT productline, round(sum(quantity. In. Stock*buy. Price), 0) as arvo FROM products GROUP BY productline

Harjoitustaulu • Tablename: Orderdetails. • Columns: order. Number, product. Code, quantity. Ordered, price. Each, order. Line. Number

Oppimistehtävä 4 – kysymykset Suunnittele kyselyt seuraaviin tarpeisiin. Käytetään edellisen sivun products-taulua. Kyselyitä ei tarvitse kotona testata, kunhan ne on suunniteltu! • h 1. Laske hinnat tilausriveille. (quantity. Ordered * price. Each) • h 2. Laske hinnat tilauksille. (GROUP BY order. Number) • h 3. Mitä tuotetta on tilattu eniten kappaleina? (GROUP BY product. Code) Listaa eniten tilatut ensimmäisiksi. • h 4. Mistä tuotteesta on eniten tuloja? (GROUP BY product. Code)