4-amaliy ish Bajardi: Ergashev Sardor Tekshirdi: Khidirova Charos Toshkent 2022

Al-Xorazmiy Nomidagi Toshkent Axborot Texnologiyalari Universiteti





SQLda dasturlash
4-amaliy ish


Bajardi: Ergashev Sardor
Tekshirdi: Khidirova Charos

Toshkent - 2022
Savollar:
1. Xodim Jon Smit bilan bir kunda ishga qabul qilingan barcha xodimlarni qaytaradigan so‘rov yozing.
2. Narxi o'rtacha narxdan past bo'lgan barcha narsalarni qaytaradigan so'rov yozing.
3. Bir necha marta sotilgan barcha tovarlarni qaytaradigan so'rov yozing.
4. Bir necha marta sotilgan tovarlar narxining 15% ga oshishini chop eting.
5. EXISTS shartidan foydalanib, kamida bir marta nimadir sotgan barcha xodimlar ro’yxatini qaytaradigan so'rov yozing.
6. Narxi 'GA' xodimi sotgan har qanday mahsulot narxidan past bo'lgan mahsulot jadvalidagi barcha mahsulotlarni qaytaradigan so'rovni yozing.
7. Narxi 'GA' kodli xodim tomonidan sotilgan kamida bitta mahsulot narxidan past bo'lgan mahsulotlar jadvalidagi barcha mahsulotlarni qaytaradigan so'rovni yozing. SOME va ANY operatorlari bir-birini almashtira olishiga ishonch hosil qiling.
8. Sana va raqamlar bilan ishlash funktsiyalaridan foydalanib, necha yoshda ekanligingizni hisoblang.
9. 'mEN fUnksIYa MatniNI biLAMaя ' qatorini katta va kichik harflarda chiqaring. Ushbu qatorning uzunligini aniqlang.
10. purchase.product_name ustuni bilan ishlagan holda quyidagilarni chiqaring:
- birinchi uchta belgi;
- to'rtinchi to'liq spektrdan boshlab qolgan barcha boshqa belgilar;
- to’liq qatorni.

Javoblar:

1. 
   SELECT * FROM kompaniya_ishchilari WHERE qabul_sanasi = (SELECT * FROM kompaniya_ishchilari WHERE ism = “Jon Smit”);
   

2. 
   SELECT * FROM Products WHERE price < (SELECT AVG(price) from Products);
   

3. 
   SELECT * FROM Products WHERE count_sold > 1;
   

4. 
   UPDATE Products SET price = (price + 15%) WHERE count_cold > 1;
   

5. 
   SELECT * FROM Staffs WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.supplierID = Supplier.supplierID);
   

6. 
   SELECT name, price FROM item_mast WHERE price < (SELECT AVG(price) FROM item_mast);
   

7. 
   SELECT DISTINCT maker FROM Product WHERE type = ‘PC’ AND NOT model = ANY (SELECT model FROM PC)
   

SELECT product_name FROM PRODUCT WHERE STAFF_ID = ‘GA’ AND NOT product_price < ANY(SELECT product_price FROM Product);

8. 
   SELECT DATEDIFF(year, ‘2022/05/9’,‘2000/09/18’) AS DateDiff;
   
9. 
   

Katta harflarda chiqarish :
SELECT UPPER(‘mEN fUnksIYa MatniNI biLAMaя ‘);
Kichik harflarda chiqarish :
SELECT LOWER(‘mEN fUnksIYa MatniNI biLAMaя ‘);
Sart uzunligini chiqarish :
SELECT LEN(‘mEN fUnksIYa MatniNI biLAMaя ‘);

10. 
    

SELECT SUBSTRING( purchase.product_name, 1, 3 ) FROM purchase;

SELECT * FROM purchase LIMIT 1;

SELECT SUBSTRING( purchase.product_name, 4,

LEN(purchase.product_name)) FROM purchase;