O‘ZBEKISTON RESPUBLIKASI OLIY TA’LIM, FAN VA INNOVATSIYALAR VAZIRLIGI MUHAMMAD AL- XORAZMIY NOMIDAGI TOSHKENT AXBOROT TEXNOLOGIYALARI UNIVERSITETI KOMPYUTER INJIRINGI FAKULTETI
MA’LUMOTLAR BAZASI FANIDAN
4-AMALIY ISH
Gruppa: 240-21
Bajardi: Ergashev Jaxongir
Tekshirdi: Djurayev Tulkinjon
Toshkent-2023
Mavzu: Murakkab so‘rovlar yaratish. SQL tilida INDEX yaratish. Tasavvur, VIEW yaratish va undan foydalanish. SQL da triggerlar yaratish . Dasturlash tillari va ma’lumotlar bazasi yordamida oddiy interfeys yaratish .
11-amaliy ish
Ishdan maqsad: Murakkab so’rovlar yaratish
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)
| 