Guruh talabasi Toshpo’latov Azizbek

65 |	2019 |
4 |

| Axborot xavfsizligi1 |	30 |	2020 |	3 |
| Dasturiy injiniring1 |	50 |	2020 |	3 |
| Raqamli iqtisodiyot1 |	90 |	2020 |	3 |
| Axborot xavfsizligi |	40 |	2021 |	2 |
| Dasturiy injiniring |	60 |	2021 |	2 |
| Raqamli iqtisodiyot |	80 |	2021 |	2 |

+----------------------+--------+--------+---------+
9 rows in set (0.00 sec) GROUP BY:
mysql> select count(*), nomi,Q_yili from oq_guruxlari
-> group by Q_yili;
+----------+----------------------+--------+
| count(*) | nomi | Q_yili |
+----------+----------------------+--------+
| 3 | Axborot xavfsizligi2 | 2019 |
| 3 | Axborot xavfsizligi1 | 2020 |
| 3 | Axborot xavfsizligi | 2021 |
+----------+----------------------+--------+
3 rows in set (0.01 sec)

4. 
   Har bir talaba o’ziga berilgan predmet soha mavzusi bo’yicha HAVING operatoridan foydalanib so’rovlar yaratishi va natijasi bilan ko’rsatib o’tishi kerak.
   

mysql> select count(*) as soni, Q_yili from oq_guruxlari

-> group by Q_yili having soni=3;
+------+--------+
| soni | Q_yili |
+------+--------+
| 3 | 2019 |
| 3 | 2020 |
| 3 | 2021 |
+------+--------+
3 rows in set (0.00 sec)

5. 
   Har bir talaba o’ziga berilgan predmet soha mavzusi bo’yicha UNION, INTERSECT, MINUS operatorlaridan foydalanib so’rovlar yaratishi va natijasi bilan ko’rsatib o’tishi kerak.
   

UNION:

mysql> select*from oq_guruxlari;

+----------------------+--------+--------+---------+
| NOMI | T_soni | Q_yili | O_kursi |
+----------------------+--------+--------+---------+

| Axborot xavfsizligi |	40 |	2021 |	2 |
| Axborot xavfsizligi1 |	30 |	2020 |	3 |
| Axborot xavfsizligi2 |	45 |	2019 |	4 |
| Dasturiy injiniring |	60 |	2021 |	2 |
| Dasturiy injiniring1 |	50 |	2020 |	3 |
| Dasturiy injiniring2 |	70 |	2019 |	4 |
| Raqamli iqtisodiyot |	80 |	2021 |	2 |
| Raqamli iqtisodiyot1 |	90 |	2020 |	3 |
| Raqamli iqtisodiyot2 |	65 |	2019 |	4 |

+----------------------+--------+--------+---------+
9 rows in set (0.00 sec)

mysql> select * from oq_guruxlari2;

+---------------------+--------+--------+---------+
| Nomi | T_soni | Q_yili | O_kursi |
+---------------------+--------+--------+---------+
| Axborot xavfsizligi | 40 | 2021 | 2 |
| kasb talimi | 40 | 2019 | 4 |
| komp injiniringi | 80 | 2020 | 3 |
+---------------------+--------+--------+---------+
3 rows in set (0.00 sec)

mysql> select T_soni,O_kursi from oq_guruxlari

-> union
-> select T_soni,O_kursi from oq_guruxlari2;
+--------+---------+
| T_soni | O_kursi |
+--------+---------+

|	40 |	2 |
|	30 |	3 |
|	45 |	4 |
|	60 |	2 |
|	50 |	3 |
|	70 |	4 |
|	80 |	2 |

|	90 |	3 |
|	65 |	4 |
|	40 |	4 |
|	80 |	3 |

+--------+---------+
11 rows in set (0.00 sec) Intersect(In):
mysql> select nomi,T_soni from oq_guruxlari where nomi in
-> (select nomi from oq_guruxlari2);
+---------------------+--------+
| nomi | T_soni |
+---------------------+--------+
| Axborot xavfsizligi | 40 |
+---------------------+--------+
1 row in set (0.00 sec)

MINUS(notin):

mysql> select nomi,T_soni from oq_guruxlari where nomi not in
-> (select nomi from oq_guruxlari2);
+----------------------+--------+
| nomi | T_soni |
+----------------------+--------+

| Axborot xavfsizligi1 |	30 |
| Axborot xavfsizligi2 |	45 |
| Dasturiy injiniring |	60 |
| Dasturiy injiniring1 |	50 |
| Dasturiy injiniring2 |	70 |
| Raqamli iqtisodiyot |	80 |
| Raqamli iqtisodiyot1 |	90 |
| Raqamli iqtisodiyot2 |	65 |

+----------------------+--------+

8. 
   rows in set (0.00 sec)
   

6. 
   Har bir talaba o’ziga berilgan predmet soha mavzusi bo’yicha AVG, SUM, MIN, MAX,COUNT operatoridan foydalanib so’rovlar yaratishi va natijasi bilan ko’rsatib o’tishi kerak.
   

mysql> select * from products;

+----+-------------------+---------+--------------+---------------+
| ID | Maxsulotnomi | Brendi | Maxsulotsoni | Maxsulotnarxi |
+----+-------------------+---------+--------------+---------------+

|	1 | IPhone 13 pro MAX | Apple	|	8 |	1200 |
|	2 | IPhone 12 pro MAX | Apple	|	7 |	900 |
|	3 | IPhone 11 pro MAX | Apple	|	12 |	800 |
|	4 | Galaxy S21 Ultra | Samsung |		10 |	1000 |
|	5 | Honor 7 plus | Honor |		18 |	500 |
|	6 | Redmi Note 9 Pro | Xiaomi |		16 |	450 |
|	7 | Huawei P 30 Lite | Huawei |		13 |	300 |
|	8 | Redmi 10 T | Xiaomi |		0 |	457 |
|	9 | Miui 12 Ultra | Xiaomi |		0 |	412 |

+----+-------------------+---------+--------------+---------------+

8. 
   rows in set (0.00 sec)
   

mysql> select count(*) as soni,

-> sum(Maxsulotsoni*Maxsulotnarxi) as narxi,
-> max(Maxsulotnarxi) as qimmat,
-> min(Maxsulotnarxi) as arzon,
-> avg(Maxsulotnarxi) as orta from products;
+------+-------+--------+-------+------------------+
| soni | narxi | qimmat | arzon | orta |
+------+-------+--------+-------+------------------+
| 9 | 55600 | 1200 | 300 | 668.777777777778 |
+------+-------+--------+-------+------------------+
1 row in set (0.00 sec)

7. 
   Har bir talaba o’ziga berilgan predmet soha mavzusi bo’yicha STANDART FUNKSIYALARDAN foydalanib so’rovlar yaratishi va natijasi bilan ko’rsatib o’tishi kerak.
   

mysql> select CONCAT(Maxsulotnomi,'- ',SUBSTR(UPPER(Brendi),1,2))as UMUMIY from products;
+----------------------+
| UMUMIY |
+----------------------+
| IPhone 13 pro MAX-AP |
| IPhone 12 pro MAX-AP |
| IPhone 11 pro MAX-AP |
| Galaxy S21 Ultra-SA |
| Honor 7 plus-HO |
| Redmi Note 9 Pro-XI |
| Huawei P 30 Lite-HU |
| Redmi 10 T-XI |
| Miui 12 Ultra -XI |
+----------------------+
9 rows in set (0.01 sec)

POWER VA SQRT:

mysql> select POW(Maxsulotsoni,2) from products;
+---------------------+
| POW(Maxsulotsoni,2) |
+---------------------+

|	64 |
|	49 |
|	144 |
|	100 |
|	324 |
|	256 |
|	169 |
|	0 |
|	0 |

+---------------------+
9 rows in set (0.01 sec)

mysql> select sqrt(Maxsulotsoni) from products;

+--------------------+
| sqrt(Maxsulotsoni) |
+--------------------+

|	2.82842712474619 |
|	2.64575131106459 |
|	3.46410161513775 |
|	3.16227766016838 |
|	4.24264068711928 |
|	4 |

| 3.60555127546399 |
| 0 |
| 0 |
+--------------------+
9 rows in set (0.00 sec)

8. 
   Har bir talaba o’ziga berilgan predmet soha mavzusi bo’yicha murakkab so’rovlar yaratishi va natijasi bilan ko’rsatib o’tishi kerak.
   

ANY:
mysql> select * from oq_guruxlari where nomi = Any
-> (select nomi from Oq_guruxlari2);
+ + + + +
| NOMI | T_soni | Q_yili | O_kursi |
+ + + + +
| Axborot xavfsizligi | 40 | 2021 | 2 |
+ + + + + 1 row in set (0.00 sec)
ALL:
select * from oq_guruxlari where nomi != ALL
-> -> (select nomi from Oq_guruxlari2);

9. 
   Har bir talaba o’ziga berilgan predmet soha mavzusi bo’yicha JOIN operatorilaridan foydalanib so’rovlar yaratishi va natijasi bilan ko’rsatib o’tishi kerak.
   

mysql> select * from talaba;
+------+------------+----------+--------+
| T_Id | name | Fam | Guruh |
+------+------------+----------+--------+
| 1 | Shuhrat | Imomov | 650_21 |
| 2 | Nurshodbek | Shokirov | 650_21 |
| 3 | Javohir | Turgunov | 650_21 |
+------+------------+----------+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM Fanlar ;

+------+-------------+------+
| T_Id | nomi | Baho |
+------+-------------+------+
| 1 | MB | 2 |
| 2 | DISKRET | 3 |
| 3 | ELEKTRONICA | 5 |
+------+-------------+------+
3 rows in set (0.00 sec)

mysql> SELECT T.Fam, T.name,F.nomi,F.Baho from Talaba T inner join Fanlar F on T.T_Id=F.T_Id

-> where Baho=5;
+----------+---------+-------------+------+
| Fam | name | nomi | Baho |
+----------+---------+-------------+------+
| Turgunov | Javohir | ELEKTRONICA | 5 |
+----------+---------+-------------+------+
1 row in set (0.00 sec)