Huom! Seuraavat esimerkkikyselyt ovat tehty harjoitustietokantaa kohden vanhemmissa harjoituksissa. Uudemmissa tehtävissä käytetään Sakila tietokantaa.
Kuinka monta kertaa asiakkaat ovat tehneet tilauksia keskimäärin koko tilaushistorian ajalta?
SELECT customerNumber,
COUNT(*)
FROM orders
GROUP BY customerNumber;
--kuinka monta kertaa yksittäinen asiakas on tilannut
SELECT customerNumber,
COUNT(*)
FROM orders
WHERE status = "Shipped"
GROUP BY customerNumber;
--sama mutta näyttää vain ne jotka on shipped
SELECT AVG(d.c) AS keskiarvo
FROM
(SELECT customerNumber,
COUNT(*) AS c
FROM orders
WHERE status = "Shipped"
GROUP BY customerNumber)AS d;
Päivissä kerrottuna, mikä on toistuvia tilauksia tehneiden asiakkaiden keskimääräinen tilausaika? Ostaako asiakas siis viikoittain, kuukausittain vai vuosittain tuotteita?
SELECT customerNumber, (MAX(orderDate) - MIN(orderDate)) / (COUNT(*) – 1
FROM orders
GROUP BY customerNumber
HAVING COUNT(*) > 1;
Ketkä ovat asiakkaista ovat kärjessä tilauksien määrän mukaan järjestettynä koko tilaushistorian ajalta?
SELECT customerNumber,
COUNT(*)
FROM orders
GROUP BY customerNumber
ORDER BY COUNT(*) DESC
LIMIT 3;
Montako tilausriviä tilaukset keskimäärin sisältävät koko tilaushistorian ajalta?
SELECT count(orderLineNumber) / count(DISTINCT orderNumber) AS orders
FROM orderdetails;
Tilauksien kokonaissummaa euroissa tarkasteltuna, mikä on kertaostosten keskimääräinen summa, maksimi ja minimi?
SELECT MAX(total),
MIN(total),
AVG(total)
FROM
(SELECT orderNumber,
SUM(quantityOrdered * priceEach) AS total
FROM orderdetails
GROUP BY orderNumber) AS x;
Mikä on keskimääräinen myynti kuukausittain lähetettyjen myyntien osalta koko tilaushistorian ajalta?
SELECT MONTH(orderDate) AS MONTH,
YEAR(orderDate) AS YEAR,
avg(quantityOrdered * priceEach) AS AVG
FROM orders
INNER JOIN orderdetails USING (orderNumber)
WHERE status = 'shipped'
GROUP BY YEAR,
MONTH WITH ROLLUP;
Mikä on keskimääräinen toimitusaika tilauksen saapumisen ja lähettämisen välillä koko tilaushistorian ajalta?
SELECT round(avg(datediff(shippedDate, orderDate)))
FROM orders
WHERE status = 'shipped';
Ketkä asiakkaista ovat parhaiten maksavia asiakkaita, jotka ovat hoitaneet maksunsa?
SELECT contactFirstName,
contactLastName,
city,
country,
amount
FROM customers
INNER JOIN payments
ORDER BY `payments`.`amount` DESC;
Ketkä asiakkaista ovat parhaiten tilaavia eli ovat tehneet useimpia tilauksia?
SELECT contactFirstName,
contactLastName,
city,
country,
quantityOrdered
FROM customers
INNER JOIN orderdetails
ORDER BY `orderdetails`.`quantityOrdered` DESC;
Mitkä tuotteet ovat myyneet parhaiten koko tilaushistorian aikana?
SELECT productName,
sum(quantityOrdered)
FROM products
INNER JOIN orderdetails ON products.productCode = orderdetails.productCode
GROUP BY productName
ORDER BY sum(quantityOrdered) DESC
LIMIT 10;
Pienoismallin koon mukaan laskettuna, mitkä tuotteista ovat myyneet parhaiten tilaushistorian aikana?
SELECT productScale,
sum(quantityOrdered) AS "Total orders",
sum(quantityOrdered*priceEach) AS "Total sales"
FROM products
INNER JOIN orderdetails ON products.productCode = orderdetails.productCode
GROUP BY productScale
ORDER BY sum(quantityOrdered) DESC;
Keskittyen vain muutamaan parhaiten myyvään tuotteeseen niin mihin maihin kyseisiä tuotteita myydään?
SELECT country,
city,
productCode,
sum(quantityOrdered*priceEach) AS total
FROM orderdetails
INNER JOIN orders ON orderdetails.orderNumber = orders.orderNumber
INNER JOIN customers ON customers.customerNumber = orders.customerNumber
WHERE productCode IN ("S700_4002",
"S18_3232",
"S18_1342")
GROUP BY country,
city,
productCode
ORDER BY `total` DESC;
Mitkä tuoteryhmistä ovat parhaiten myyviä koko tilaushistorian aikana?
SELECT productLine,
sum(quantityOrdered*priceEach)
FROM products
INNER JOIN orderdetails ON products.productCode = orderdetails.productCode
GROUP BY productLine
ORDER BY sum(quantityOrdered*priceEach) DESC;
Ketkä ovat TOP10 huippumyyjää koko tilaushistorian ajalta?
SELECT salesRepEmployeeNumber,
firstName,
lastName,
officeCode,
SUM(quantityOrdered * priceEach) sales
FROM customers
INNER JOIN orders USING (customerNumber)
INNER JOIN orderdetails USING (ordernumber)
INNER JOIN employees ON employeeNumber = salesRepEmployeeNumber
INNER JOIN offices USING (officeCode)
GROUP BY salesRepEmployeeNumber
ORDER BY sales DESC
LIMIT 10;
Mitkä toimipaikoista ovat parhaiten myyviä niiden myynnin mukaan järjestettynä?
SELECT officeCode,
offices.city,
SUM(quantityOrdered * priceEach) sales
FROM customers
INNER JOIN orders USING (customerNumber)
INNER JOIN orderdetails USING (ordernumber)
INNER JOIN employees ON employeeNumber = salesRepEmployeeNumber
INNER JOIN offices USING (officeCode)
GROUP BY officeCode
ORDER BY sales DESC;
Mihin kaupunkeihin huippumyyjät myyvät eniten? Kun siis tiedämme ensin TOP10 huippumyyjää niin mihin kaupunkeihin he pääasiassa myyvät.
SELECT c.city,
Sum(o2.quantityordered * o2.priceeach) sales
FROM (SELECT salesrepemployeenumber,
Sum(quantityordered * priceeach) sales
FROM customers
INNER JOIN orders USING (customernumber)
INNER JOIN orderdetails USING (ordernumber)
GROUP BY salesrepemployeenumber
ORDER BY sales DESC
LIMIT 10) salestop10
INNER JOIN customers c
ON c.salesrepemployeenumber = salestop10.salesrepemployeenumber
INNER JOIN orders o1
ON o1.customernumber = c.customernumber
INNER JOIN orderdetails o2
ON o2.ordernumber = o1.ordernumber
GROUP BY c.city
ORDER BY city
