1-laboratoriya topshirig’i

1- yechim:
SELECT billingcountry, count(invoicedate) AS Invoices
FROM Invoice GROUP BY billingcountry
ORDER BY Invoices DESC;
2- yechim:
SELECT Invoice.BillingCountry, COUNT(Invoice.BillingCountry)
FROM Invoice
GROUP BY Invoice.BillingCountry
ORDER BY COUNT(Invoice.Total) DESC;
2-savol. Biz eng ko‘p pul ishlagan shaharda reklama musiqiy festivalini o‘tkazmoqchimiz. Hisob-fakturalar summasi eng ko‘p bo‘lgan shaharni qaytaradigan so‘rov yozing. Shahar nomini va barcha hisob-fakturalar summasini qaytaring.
Ikkinchi savolga mos so’rov yozish uchun uni tahlil qilaylik va qaysi jadvaldan foydalanish kerakligini o’ylab ko’raylik. Shaharni tanlash uchun SELECTdan so’ng billingcityni yozamiz. Eng ko’p pul ishlaganlarni topish uchun MAX() ni ishlatamiz. Shahar nomini va barcha invoicelarni qaytarish uchun ikkinchi SELECTdan foydalanamiz. Natija quyidagicha bo’ladi.
1-yechim:
SELECT billingcity, max(TotalSum) AS Total FROM (
SELECT billingcity, sum(total) AS TotalSum FROM Invoice GROUP BY billingcity);
2-yechim:
SELECT Invoice.BillingCity, SUM(Invoice.Total)
FROM Invoice
GROUP BY Invoice.BillingCity
ORDER BY SUM(Invoice.Total) DESC LIMIT 1


3-savol. Eng ko‘p pul sarflagan mijoz eng yaxshi mijoz deb e’lon qilinadi. Eng ko‘p pul sarflagan kishini qaytaradigan so‘rov yozing. Men ushbu uchta ma’lumotni bog‘lash orqali yechimni topdim: bular fakturani olish uchun hisob-faktura, hisob-faktura va mijozlar jadvallari, ammo siz buni kamroq jadvallar bilan uddalashingiz mumkin!
Savolda berilganga ko’ra eng ko’p pul sarflagan eng yaxshi customerni aniqlashimiz kerak. Buning uchun barcha sarflangan pullar TotalSpentni hisoblab yig’indisini SUM() orqali topishimiz kerak. Bunda TotalSpentga asosan ORDER BY orqali tartiblab olish zarur. Tartiblangan yozuvlarning eng birinchisini olishimiz zarur, shuning uchun LIMIT dan foydalanamiz. Javob quyidagicha bo’ladi: