Bajardi: Tilakov J. Tekshirdi: Norov M. 2023-2024-yil Mavzu

Muhammad al-Xorazmiy
nomidagi Toshkent axborot texnologiyalari universiteti Qarshi filiali AKT-11-22 guruh
2-bosqich talabasi Tilakov Javohirnig Ma’lumotlar ba’zasi fanidan yozgan
Amaliy mashg’uloti №5



Bajardi: Tilakov J.
Tekshirdi: Norov M.

2023-2024-yil
Mavzu: Tasavvur, VIEW yaratish va undan foydalanish.
Reja:

1. 
   Tasavvur, VIEW yaratish va undan foydalanish.
   
2. 
   SQL da funksiyalar yaratish. SQL da triggerlar yaratish
   
3. 
   Dasturlash tillari va ma’lumotlar bazasi yordamida oddiy interfeys yaratish
   

Mavzu: Murakkab so‘rovlar yaratish. Sql tilida index yaratish. Tasavvur, view yaratish va undan foydalanish
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)