О‘zbеkistоn rеspublikаsi raqamli texnologiyalar vаzirligi muhаmmаd аl-xоrаzmiy nоmidаgi tоshkеnt аxbоrоt tеxnоlоgiyаlаri univеrsitеti

О‘ZBЕKISTОN RЕSPUBLIKАSI RAQAMLI TEXNOLOGIYALAR  VАZIRLIGI
MUHАMMАD АL-XОRАZMIY NОMIDАGI TОSHKЕNT АXBОRОT TЕXNОLОGIYАLАRI UNIVЕRSITЕTI
3-Amaliy ishi.

" Murakkab so‘rovlar yaratish. SQL tilida INDEX yaratish. Tasavvur, VIEW yaratish va undan foydalanish. SQL da funksiyalar yaratish. SQL da triggerlar yaratish

Аxborot xavfsizligi 072-21 sirtqi boʼlimi
Bajardi : Karimova M
Tekshirdi: To’xtasinov A
Toshkent 2023


Mavzu: Murakkab so‘rovlar yaratish. SQL tilida INDEX yaratish. Tasavvur, VIEW yaratish va undan foydalanish. SQL da funksiyalar yaratish. SQL da triggerlar yaratish
Ishdan maqsad: Murakkab so’rovlar yaratish bo`yicha ko`nikmaga ega bo`lish.
Masalani qo`yilishi: Berilgan predmet soha ma`lumotlar bazasidagi barcha ob`yektlarni ustida murakkab so’rovlar yaratish asosida amallarini bajarish.
Uslubiy ko`rsatmalar: Murakkab SQL so'rovlari bu oddiy so'rovlarning kombinatsiyasi. Amalga oshirilganda oddiy so'rovlar jadvallariga guruhlangan ma'lumotlar to'plamlarini qaytaradi.
mysql> select samalyot, chiqish_joyi, borar_joyi
-> from parvozlar
-> where sam_id = (select sam_id from samalyotlar
-> where yuk_hajmi = 'Kam');
+----------+--------------+------------+
| samalyot | chiqish_joyi | borar_joyi |
+----------+--------------+------------+
| AB_71 | uzbekiston | italiya |
+----------+--------------+------------+
1 row in set (0.00 sec)
mysql>
mysql> select samalyot, chiqish_joyi, borar_joyi

-> from parvozlar

-> where sam_id = some(select sam_id from samalyotlar
-> where yuk_hajmi = 'Ko`p');
+----------+--------------+------------+
| samalyot | chiqish_joyi | borar_joyi |
+----------+--------------+------------+
| AB_73 | rossiya | parij |
| AB_69 | uzbekiston | dubay |
| AZ_68 | xitoy | tatariston |
| AD_56 | qoqon | uzbekiston |
+----------+--------------+------------+
4 rows in set (0.00 sec)
mysql> select samalyot, chiqish_joyi, borar_joyi
-> from parvozlar
-> where sam_id != all(select sam_id from samalyotlar
-> where orindiqlar_soni in(140, 150));
+----------+--------------+------------+
| samalyot | chiqish_joyi | borar_joyi |
+----------+--------------+------------+
| AB_69 | uzbekiston | dubay |
| AZ_68 | xitoy | tatariston |
| AD_56 | qoqon | uzbekiston |
+----------+--------------+------------+
3 rows in set (0.00 sec)
mysql>
mysql> select samalyotlar.sam_id, samalyotlar.ishlab_chiqarish_yili, parvozlar.samalyot, parvozlar.chiqish

-> from samalyotlar

-> join parvozlar on samalyotlar.sam_id = parvozlar.sam_id;
+--------+-----------------------+----------+------------+
| sam_id | ishlab_chiqarish_yili | samalyot | chiqish |
+--------+-----------------------+----------+------------+
| 1 | 2000 | AB_73 | 01.02.2002 |
| 2 | 2000 | AB_69 | 04.03.2005 |
| 3 | 2000 | AZ_68 | 01.05.2006 |
| 4 | 2000 | AD_56 | 01.04.2007 |
| 5 | 2000 | AB_71 | 07.03.2021 |
+--------+-----------------------+----------+------------+
5 rows in set (0.00 sec)