1. Sotish miqdori kamroq bo'lgan mahsulotlarni olib tashlang

Uyga vazifa
1. Sotish miqdori kamroq bo'lgan mahsulotlarni olib tashlang
buyurtma tafsilotlaridagi mahsulotlarning eng kichik o'rtacha soni
(product_id bo'yicha guruhlash). Olingan jadvalda product_name va units_in_stock ustunlari
bo'lishi kerak.
Answer:
SELECT product_name,units_in_stock FROM
products
WHERE EXISTS(
SELECT product_id FROM order_details
WHERE product_id=products.product_id
GROUP BY product_id
HAVING COUNT(product_id)>(SELECT AVG(product_id)
FROM order_details)
)
2. Buyurtma yuklarining umumiy miqdorini ko'rsatadigan so'rov yozing
buyurtmachi kompaniyalar uchun yuk narxi ko'proq bo'lgan buyurtmalar uchun
yoki barcha buyurtmalar yukining o'rtacha qiymatiga, shuningdek sanaga teng
buyurtma 1996 yil iyul oyining ikkinchi yarmida jo'natilishi kerak.
Olingan jadvalda customer_id va freight_sum ustunlari bo'lishi kerak,
ularning satrlari buyurtma yuklari miqdori bo'yicha saralanishi kerak.
ANSWER:
2.SELECT customer_id, freight as freight_sum,order_date
FROM orders
WHERE freight>(SELECT AVG(freight) FROM orders)
AND shipped_date='1996-07-15'
ORDER BY freight DESC
3. 1997-yil 1-sentabrdan keyin yaratilgan va Janubiy Amerika mamlakatlariga yetkazib berilgan eng
yuqori qiymatga ega 3 ta buyurtmani ko'rsatadigan so'rov yozing. Umumiy qiymat chegirmani
hisobga olgan holda buyurtma qismlarining qiymati yig'indisi sifatida hisoblanadi. Olingan jadvalda
customer_id, ship_country va order_price ustunlari bo'lishi kerak, ularning satrlari buyurtma
qiymati bo'yicha teskari tartibda saralanishi kerak.
4. To'liq 10 dona buyurtma qilingan barcha mahsulotlarni (noyob mahsulot nomlari) olib keling
(albatta, buni pastki so'rovsiz hal qilish mumkin).
Answer:
.SELECT product_name, products.unit_price,COUNT(products.product_id) FROM products
JOIN order_details ON order_details.product_id=products.product_id
GROUP BY products.product_id
HAVING COUNT(products.product_id)<10
///
SELECT product_name, unit_price FROM products
WHERE EXISTS(
SELECT product_id,COUNT(order_id) FROM order_details
WHERE order_details.product_id=products.product_id
GROUP BY product_id
HAVING COUNT(order_id)>10
)
5. Quyidagi ustunlarni chiqaradigan ko'rinishni yarating:
order_date, required_date, shipped_date, zip_postal_code, company_name, contact_name, phone,
last_name, first_name, title buyurtmalar, mijozlar va xodimlar jadvallaridan.
Yaratilgan ko'rinishga Select qiling, bu erda order_date 1997 yil 1 yanvardan katta bo'lgan barcha
yozuvlarni ko'rsatadi.
Answer:
.SELECT order_date, required_date, shipped_date, ship_postal_code, company_name, contact_name, phone,
last_name, first_name, title FROM orders,customers,employees
WHERE orders.order_date>'1997-01-01