1-laboratoriya topshirig’i

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:
1-yechim:
SELECT distinct email,firstname,lastname,e.name
FROM Customer a,Invoice b,InvoiceLine c,Track d,Genre e
WHERE a.Customerid=b.Customerid AND b.Invoiceid=c.Invoiceid
AND c.Trackid=d.Trackid AND d.Genreid=e.Genreid AND e.name = 'Rock' ORDER BY email;
2-yechim:
SELECT Customer.Email, Customer.FirstName, Customer.LastName, Genre.Name
FROM Customer
INNER JOIN Invoice ON Customer.CustomerId=Invoice.CustomerId
INNER JOIN InvoiceLine ON Invoice.InvoiceId=InvoiceLine.InvoiceId
INNER JOIN Track ON InvoiceLine.TrackId=Track.TrackId
INNER JOIN Genre ON Genre.GenreId=Track.GenreId
WHERE Genre.Name="Rock"
GROUP BY Customer.CustomerId
ORDER BY Customer.Email