Ma’lumotlar tahlili yo‘nalishi bo‘yicha o‘qituvchilar uchun qo‘llanma

1-yechim:

SELECT c.CustomerId, c.FirstName, c.LastName, sum(i.Total) as TotalSpent

FROM Customer c, Invoice i

WHERE c.CustomerId=i.CustomerId

GROUP BY (i.CustomerId)

ORDER BY TotalSpent desc

LIMIT 1;

2-yechim:

SELECT Customer.CustomerID, Customer.FirstName, Customer.LastName, SUM(Invoice.Total) AS Invoices

FROM Customer

INNER JOIN Invoice ON Customer.CustomerID=Invoice.CustomerID

GROUP BY Customer.CustomerID

ORDER BY SUM(Invoice.Total) DESC LIMIT 1

4-savol. Chinook guruhi Rok musiqasini tinglaydigan barcha mijozlarni aniqlamoqchi. Barcha Rok musiqa tinglovchilarining elektron pochta manzilini, ismini, familiysini va janrini qaytarish uchun so‘rov yozing. Elektron pochta manzili bo‘yicha ’A’ bilan boshlanadigan alifbo tartibida ro‘yxat qaytaring.

Izoh: Mijozlarning pochtasi, ismi, familiyasini qaytarish uchun SELECT dan so’ng yoziladigan maydonlar nomlarini bilib olamiz. Undan so’ng esa so’ralgan topshiriqqa asosan qaysi jadvaldan ekanligini ko’rsatamiz, bu yerda Customer jadvalidan bo’ladi. So’ngra WHERE buyrug’idan keyin shartni yozishda so’rovga mos tarzda Rock janrini tinglovchilarni tanlaymiz va elektron pochtasini ORDER BY buyrug’i orqali tartiblaymiz. Ushbu savolga javob SQL so’rovi quyidagicha bo’ladi: