• SELECT * FROM Orders a WHERE AMT >(SELECT AVG (AMT) FROM Orders b WHERE b.CNum = a.CNum);
  • SELECT ODate, SUM (AMT) FROM Orders a GROUP BY ODate HAVING SUM (AMT) > (SELECT 2000.00 + MAX (AMT) FROM Orders b WHERE a.ODate = b.ODate);
    		•

    SELECT * FROM Orders main WHERE NOT SNum




    Download 1,38 Mb.
    Pdf ko'rish
    bet46/79
    Sana30.01.2024
    Hajmi1,38 Mb.
    #148346
    1   ...   42   43   44   45   46   47   48   49   ...   79
    Bog'liq
    Mundarija kirish

    SELECT * FROM Orders main WHERE NOT SNum 
    =(SELECT 
    SNum 
    FROM Customers 
    WHERE CNum 
    = 
    main.CNum); 
    Asosiy so„rov asoslangan jadvalga asoslanuvchi mutanosib 
    so„rovdan foydalanishingiz mumkin. 
    Misol: sotib olishlar buyurtmachilari uchun o„rta qiymatdan yuqori 
    bo„lgan hamma buyurtmalarni topish.
    SELECT * FROM Orders a WHERE AMT >(SELECT AVG 
    (AMT) FROM Orders b WHERE b.CNum = a.CNum); 
    HAVING operatoridan ostki so„rovlarda foydalanilganidek 
    mutanosib ostki so„rovlarda ham foydalanigsh mumkin. 
    HAVING ifodasida mutanosib ostki so„rovdan foydalanganda 
    HAVING o„zida ishlatilishi mumkin bo„lgan pozitsiyalarga tashqi 
    ilovalarni cheklab qo„yishingiz kerak. Chunki HAVING ifodasida faqat 
    agregat SELECT ifodasida ko„rsatilgan funksiyalardan yoki GROUP 
    BY ifodasida ko„rsatilgan maydonlardan foydalanish mumkin. Ulardan 
    siz tashqi ilova sifatida foydalanishingiz mumkin. Buning sababi shuki 
    HAVING tashqi so„rovdagi satrlar uchun emas guruxlar uchun 
    baxolanadi. Shuning uchun ostki so„rov bir marta satr uchun emas gurux 
    uchun bajariladi. 
    Misol: Buyurtmalar jadvalidagi sotibolishlar summalarini sanalar 
    bo„yicha guruxlar summasini hisoblash kerak bo„lsin. Shu bilan birga 
    summa maksimal summadan kamida 2000.00 ga ko„p bo„lmagan 
    sanalarni chiqarib tashlash kerak bo„lsin: 

    67 
    SELECT ODate, SUM (AMT) FROM Orders a GROUP BY 
    ODate HAVING SUM (AMT) > (SELECT 2000.00 + MAX (AMT) 
    FROM Orders b WHERE a.ODate = b.ODate); 
    Ostki so„rov asosiy so„rovning ko„rilayotgan agregat guruxi 
    sanasiga teng sanaga ega xmma satrlar uchun MAX qiymat xisoblaydi. 
    Bu WHERE ifodasidan foydalanib bajarilishi lozim. Ostki so„rovning 
    o„zi GROUP BY yoki HAVING operatorlarini ishlatmasligi kerak. 

    Download 1,38 Mb.
    1   ...   42   43   44   45   46   47   48   49   ...   79




    Download 1,38 Mb.
    Pdf ko'rish