1-mavzu. Ma’lumotlar bazasi obyektlarini yaratish

Guruhlarni filterlash. HAVING
HAVING bandi chiqishga qaysi guruhlar ko`rinishini aniqlaydi, ya'ni guruhlarni filtrlaydi.
HAVING-dan foydalanish WHERE-ga o'xshashdir. Faqat qatorlarni filtrlash uchun WHERE qo`llanilgan bo’lib, undan keyin HAVING guruhlarni filtrlash uchun ishlatiladi.
Masalan, ishlab chiqaruvchilar bo'yicha tovarlarning barcha guruhlarini topaylik, unda tovar guruhlari uchun hech bo’lmaganda bitta modelga ega:
SELECT Manufacturer, COUNT(*) AS ModelsCount
FROM Products
GROUP BY Manufacturer
HAVING COUNT(*) > 1

Bundan tashqari, bitta ifodada WHERE va HAVING bandlaridan foydalanish mumkin:
SELECT Manufacturer, COUNT(*) AS ModelsCount
FROM Products
WHERE Price * ProductCount > 80000
GROUP BY Manufacturer
HAVING COUNT(*) > 1
Ya'ni, bu holda, avval qatorlar filterlanadi: umumiy qiymati 80000 dan yuqori bo’lgan tovarlar tanlanadi. Shundan so'ng tanlangan tovarlar ishlab chiqaruvchilar bo'yicha guruhlanadi. Undan keyin guruhlarning o`zi filtrlanadi - 1 dan ortiq modelni o`z ichiga olgan guruhlar tanlanadi.
Agar tartiblash talab qilinsa, ORDER BY bandi HAVING bandidan keyin keladi:
SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units
FROM Products
WHERE Price * ProductCount > 80000
GROUP BY Manufacturer
HAVING SUM(ProductCount) > 2
ORDER BY Units DESC

Bunday holda, guruhlash ishlab chiqaruvchilar bo'yicha amalga oshiriladi va har bir ishlab chiqaruvchi uchun modellar soni (Models) va ushbu modellar uchun barcha mahsulotlarning umumiy soni (Units) ham tanlanadi. Oxir-oqibat, guruhlar mahsulot soniga qarab kamayish tartibida saralanadi.

INNER/OUTER JOIN ifodalarida guruhlashdan ham foydalanish mumkin. Masalan, har bir foydalanuvchi uchun qancha buyurtma berilganligini aniqlaylik:

SELECT FirstName, COUNT(Orders.Id)
FROM Customers JOIN Orders
ON Orders.CustomerId = Customers.Id
GROUP BY Customers.Id, Customers.FirstName;

Guruhlash mezonlari Id va xaridorning ismidir. SELECT buyrug'i Orders jadvalidagi Id ustuni yordamida mijozning ismini va buyurtmalar sonini tanlaydi.
INNER JOIN bo'lgani uchun, guruhlarga faqat buyurtma bergan mijozlar kiradi.
Agar buyurtmaga ega bo'lmagan xaridorlarni ham olish zarur bo'lsa, unda OUTER JOIN ishlatiladi:
SELECT FirstName, COUNT(Orders.Id)
FROM Customers LEFT JOIN Orders
ON Orders.CustomerId = Customers.Id
GROUP BY Customers.Id, Customers.FirstName;

Yoki buyurtmalarning umumiy miqdori bilan mahsulotlarni o`qib olamiz:
SELECT Products.ProductName, Products.Manufacturer,
SUM(Orders.ProductCount * Orders.Price) AS Units
FROM Products LEFT JOIN Orders
ON Orders.ProductId = Products.Id
GROUP BY Products.Id, Products.ProductName, Products.Manufacturer