Maʼlumotlar bazasidan

Rahimjonov Otabekning”Maʼlumotlar bazasidan” bajargan
2-dedline

1. Har bir talaba o’ziga berilgan predmet soha mavzusi bo’yicha AND, OR, NOT operatorlaridan foydalanib so’rovlar yaratishi va natijasi bilan ko’rsatib o’tishi kerak. mysql> select*from xaridorlar;

+----------+--------------+----------+-------------+---------+-----------+
| ism | familaya | ota_ismi | manzil | shahar | telefoni |
+----------+--------------+----------+-------------+---------+-----------+
| Aziz | Ahmedov | Akmaljon | Fergana | Quvasoy | 901429851 |
| Arslon | Asliddinov | Akbarjon | Fergana | Qoqon | 911423151 |
| Bekmurod | Bakirov | Bahodir | Qashqadaryo | Qarshi | 931449872 |
| Bahrom | Bakirov | Behzod | Qashqadaryo | Kitob | 991827319 |
| Elyor | Ergashaliyev | Bobur | Namangan | Chust | 945901298 |
+----------+--------------+----------+-------------+---------+-----------+
5 rows in set (0.00 sec)
mysql> select*from xaridorlar where familaya='Bakirov' and shahar='Kitob';
+--------+----------+----------+-------------+--------+-----------+
| ism | familaya | ota_ismi | manzil | shahar | telefoni |
+--------+----------+----------+-------------+--------+-----------+
| Bahrom | Bakirov | Behzod | Qashqadaryo | Kitob | 991827319 |
+--------+----------+----------+-------------+--------+-----------+

1. 
   row in set (0.00 sec)
   

mysql> select*from xaridorlar where familaya='Bakirov' or shahar='Kitob';
+----------+----------+----------+-------------+--------+-----------+ | ism | familaya | ota_ismi | manzil | shahar | telefoni | +----------+----------+----------+-------------+--------+-----------+
| Bekmurod | Bakirov | Bahodir | Qashqadaryo | Qarshi | 931449872 |
| Bahrom | Bakirov | Behzod | Qashqadaryo | Kitob | 991827319 |
+----------+----------+----------+-------------+--------+-----------+

2. 
   rows in set (0.00 sec)
   

mysql> select*from xaridorlar where not (familaya='Bakirov' or shahar='Kitob'); +--------+--------------+----------+----------+---------+----------+ | ism | familaya | ota_ismi | manzil | shahar | telefoni | +--------+---
-----------+----------+----------+---------+-----------+
| Aziz | Ahmedov | Akmaljon | Fergana | Quvasoy | 901429851 |
| Arslon | Asliddinov | Akbarjon | Fergana | Qoqon | 911423151 |
| Elyor | Ergashaliyev | Bobur | Namangan | Chust | 945901298 | +--------+--------------+----------+----------+---------+-----------+ 3 rows in set (0.00 sec)
2.Har bir talaba o’ziga berilgan predmet soha mavzusi bo’yicha WHERE, IN, BETWEEN, LIKE operatorlaridan foydalanib so’rovlar yaratishi va natijasi bilan ko’rsatib o’tishi kerak. mysql> select*from sotish where soni
in(30,21); +------+-------------------+--------+----------+----------+
| soni | dori | miqdor | retsept | sotuvchi |
+------+-------------------+--------+----------+----------+
| 30 | Bolnol relaks gel | 25-40 | 2 mahal | Diyor |
| 21 | Nol gripp | 25-40 | 1 mahal | Elyor |
+------+-------------------+--------+----------+----------+
2 rows in set (0.00 sec)
mysql> select*from xaridorlar where telefoni between
-> '901429851' and '945901298';
+----------+--------------+----------+-------------+---------+-----------+
| ism | familaya | ota_ismi | manzil | shahar | telefoni |
+----------+--------------+----------+-------------+---------+-----------+
| Aziz | Ahmedov | Akmaljon | Fergana | Quvasoy | 901429851 |
| Arslon | Asliddinov | Akbarjon | Fergana | Qoqon | 911423151 |
| Bekmurod | Bakirov | Bahodir | Qashqadaryo | Qarshi | 931449872 |
| Elyor | Ergashaliyev | Bobur | Namangan | Chust | 945901298 |
+----------+--------------+----------+-------------+---------+-----------+ 4 rows in set (0.00 sec)
mysql> select*from sotish where dori like '%nol%';
+------+-------------------+--------+----------+----------+
| soni | dori | miqdor | retsept | sotuvchi |
+------+-------------------+--------+----------+----------+
| 30 | Bolnol relaks gel | 25-40 | 2 mahal | Diyor |
| 21 | Nol gripp | 25-40 | 1 mahal | Elyor |
+------+-------------------+--------+----------+----------+
2 rows in set (0.00 sec)
3.Har bir talaba o’ziga berilgan predmet soha mavzusi bo’yicha Order by, Group by operatorlaridan foydalanib so’rovlar yaratishi va natijasi bilan ko’rsatib o’tishi kerak. mysql> select*from sotish where dori like '%nol%' order by soni;
+------+-------------------+--------+----------+----------+
| soni | dori | miqdor | retsept | sotuvchi |
+------+-------------------+--------+----------+----------+
| 21 | Nol gripp | 25-40 | 1 mahal | Elyor |
| 30 | Bolnol relaks gel | 25-40 | 2 mahal | Diyor |
+------+-------------------+--------+----------+----------+
2 rows in set (0.00 sec)
mysql> select*from xaridorlar order by telefoni;
+----------+--------------+----------+-------------+---------+-----------+
| ism | familaya | ota_ismi | manzil | shahar | telefoni |
+----------+--------------+----------+-------------+---------+-----------+
| Aziz | Ahmedov | Akmaljon | Fergana | Quvasoy | 901429851 |
| Arslon | Asliddinov | Akbarjon | Fergana | Qoqon | 911423151 |
| Bekmurod | Bakirov | Bahodir | Qashqadaryo | Qarshi | 931449872 |
| Elyor | Ergashaliyev | Bobur | Namangan | Chust | 945901298 |
| Bahrom | Bakirov | Behzod | Qashqadaryo | Kitob | 991827319 | +----------+--------------+----------+-------------+---------+-----------+ 5 rows in set (0.00 sec)
mysql> select*from xaridorlar order by telefoni desc;
+----------+--------------+----------+-------------+---------+-----------+
| ism | familaya | ota_ismi | manzil | shahar | telefoni |
+----------+--------------+----------+-------------+---------+-----------+
| Bahrom | Bakirov | Behzod | Qashqadaryo | Kitob | 991827319 |
| Elyor | Ergashaliyev | Bobur | Namangan | Chust | 945901298 |
| Bekmurod | Bakirov | Bahodir | Qashqadaryo | Qarshi | 931449872 |
| Arslon | Asliddinov | Akbarjon | Fergana | Qoqon | 911423151 |
| Aziz | Ahmedov | Akmaljon | Fergana | Quvasoy | 901429851 | +----------+--------------+----------+-------------+---------+-----------+ 5 rows in set (0.00 sec)
mysql> select count(*),ism,familaya from xaridorlar
-> group by shahar;
+----------+----------+--------------+
| count(*) | ism | familaya |
+----------+----------+--------------+
| 1 | Elyor | Ergashaliyev |
| 1 | Bahrom | Bakirov |
| 1 | Bekmurod | Bakirov |
| 1 | Arslon | Asliddinov |
| 1 | Aziz | Ahmedov |
+----------+----------+--------------+
5 rows in set (0.00 sec)
mysql> select count(*),ism,shahar from xaridorlar
-> group by familaya;
+----------+----------+---------+
| count(*) | ism | shahar |
+----------+----------+---------+
| 1 | Aziz | Quvasoy |
| 1 | Arslon | Qoqon |
| 2 | Bekmurod | Qarshi |
| 1 | Elyor | Chust |
+----------+----------+---------+
4 rows in set (0.00 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(*),ism from xodim > group by familyasi having 'soni'=1; Empty set, 1 warning (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. mysql> select*from xaridorlar; +----------+--------------+----------+-------------+---------+-----------+
| ism | familaya | ota_ismi | manzil | shahar | telefoni |
+----------+--------------+----------+-------------+---------+-----------+
| Aziz | Ahmedov | Akmaljon | Fergana | Quvasoy | 901429851 |
| Arslon | Asliddinov | Akbarjon | Fergana | Qoqon | 911423151 |
| Bekmurod | Bakirov | Bahodir | Qashqadaryo | Qarshi | 931449872 |
| Bahrom | Bakirov | Behzod | Qashqadaryo | Kitob | 991827319 |
| Elyor | Ergashaliyev | Bobur | Namangan | Chust | 945901298 | +----------+--------------+----------+-------------+---------+-----------+ 5 rows in set (0.00 sec)
mysql> select*from sotuvchilar;
+-------------+---------------+----------------+----------------------+-----------------+
| familyasi | ismi | otasining_ismi | kelib_tushgan_sanasi | tugilgan_sanasi
|
+-------------+---------------+----------------+----------------------+-----------------+
| Karimov | Muhammaddiyor | Akmaljon | 2021-04-21 | 2004 |
| Ahmedov | Oybek | Akram | 2020-08-19 | 2003 |
| Ahmedov | Akmal | Akbar | 2020-03-20 | 2003 |
| Asliddinov | Kamol | Davlatjon | 2021-09-18 | 2000 |
| Abdukarimov | Asliddin | Akbarali | 2022-04-30 | 2004 | +-------------+---------------+----------------+----------------------+-----------------+ 5 rows in set (0.00 sec)
mysql> select ismi,familyasi,tugilgan_sanasi from sotuvchilar
-> union
-> select ism,familaya,manzil from xaridorlar;
+---------------+--------------+-----------------+
| ismi | familyasi | tugilgan_sanasi |
+---------------+--------------+-----------------+
| Muhammaddiyor | Karimov | 2004 |
| Oybek | Ahmedov | 2003 |
| Akmal | Ahmedov | 2003 |
| Kamol | Asliddinov | 2000 |
| Asliddin | Abdukarimov | 2004 |
| Aziz | Ahmedov | Fergana |
| Arslon | Asliddinov | Fergana |
| Bekmurod | Bakirov | Qashqadaryo |
| Bahrom | Bakirov | Qashqadaryo |
| Elyor | Ergashaliyev | Namangan |
+---------------+--------------+-----------------+
10 rows in set (0.00 sec)
mysql> select ism from xaridorlar where ism not in
-> (select ism from xaridorlar);
Empty 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
mahsulot;
+----+---------------+------------+---------------+--------+
| id | mahsulot_nomi | brend_nomi | mahsulot_soni | narxi |
+----+---------------+------------+---------------+--------+
| 1 | Iphone X | Apple | 3 | 76000 |
| 2 | Iphone 8 | Apple | 2 | 51000 |
| 3 | Galaxy S9 | Samsung | 2 | 85000 |
| 4 | Honor | Huawei | 10 | 850100 |
| 5 | Redmi 10 | Redmi | 15 | 1000 |
+----+---------------+------------+---------------+--------+
5 rows in set (0.00 sec)
mysql> select count(*) as soni > , sum(mahsulot_soni*narxi), -> max(narxi) as qimmat,
-> min(narxi) as arzon,
-> avg(narxi) as orta from mahsulot;
+------+--------------------------+--------+-------+-------------+
| soni | sum(mahsulot_soni*narxi) | qimmat | arzon | orta |
+------+--------------------------+--------+-------+-------------+ |
5 | 9016000 | 850100 | 1000 | 212620.0000 | +-----+--------------------------+--------+-------+-------------+ 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*from sotuvchilar;
+-------------+---------------+----------------+----------------------+----------------+ | familyasi | ismi | otasining_ismi | kelib_tushgan_sanasi | tugilgan_sanasi |
+-------------+---------------+----------------+----------------------+----------------+ | Karimov | Muhammaddiyor | Akmaljon | 2021-04-21 | 2004
|
| Ahmedov | Oybek | Akram | 2020-08-19 | 2003 |
| Ahmedov | Akmal | Akbar | 2020-03-20 | 2003 |
| Asliddinov | Kamol | Davlatjon | 2021-09-18 | 2000 | |
Abdukarimov | Asliddin | Akbarali | 2022-04-30 | 2004
| +-------------+---------------+----------------+----------------------+----------------+ 5 rows in set (0.00 sec)
mysql> select MONTHNAME(kelib_tushgan_sanasi) from sotuvchilar;
+---------------------------------+
| MONTHNAME(kelib_tushgan_sanasi) |
+---------------------------------+
| April |
| August |
| March |
| September |
| April |
+---------------------------------+
5 rows in set (0.00 sec)
mysql> select DAYNAME(kelib_tushgan_sanasi) from sotuvchilar;
+-------------------------------+
| DAYNAME(kelib_tushgan_sanasi) |
+-------------------------------+
| Wednesday |
| Wednesday |
| Friday |
| Saturday |
| Saturday |
+-------------------------------+
5 rows in set (0.00 sec)
mysql> select
MONTHNAME(kelib_tushgan_sanasi),DAYNAME(kelib_tushgan_sanasi
) from sotuvchilar;
+---------------------------------+-------------------------------+
| MONTHNAME(kelib_tushgan_sanasi) |
DAYNAME(kelib_tushgan_sanasi) | +---------------------------------+-------------------------------+
| April | Wednesday |
| August | Wednesday |
| March | Friday |
| September | Saturday |
| April | Saturday |
+---------------------------------+-------------------------------+
5 rows in set (0.00 sec)
mysql> select CONCAT(MONTHNAME(kelib_tushgan_sanasi),'
',DAYNAME(kelib_tushgan_sanasi)) from sotuvchilar;
+---------------------------------------------------------------------------+
| CONCAT(MONTHNAME(kelib_tushgan_sanasi),'
',DAYNAME(kelib_tushgan_sanasi)) |
+---------------------------------------------------------------------------+
| April Wednesday |
| August Wednesday |
| March Friday |
| September Saturday |
| April Saturday |
+---------------------------------------------------------------------------+ 5 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.
mysql> select*from xodim where id= ANY
-> (select id from jarayon);
+----+--------------+---------------+----------------+-----------+
| id | familyasi | ism | otasining_ismi | telefon |
+----+--------------+---------------+----------------+-----------+
| 1 | Bakiorv | Bekmurod | Botirjon | 994672150 |
| 2 | Ergashaliyev | Elyor | Toxirjon | 932709421 |
| 3 | Bakirov |Bahrom| Dilshodjon | 911447793 |
4 rows in set (0.00 sec)
mysql> select*from xodim where id= ANY
-> (select id mijoz);
+----+--------------+---------------+----------------+-----------+
| id | familyasi | ism | otasining_ismi | telefon |
+----+--------------+---------------+----------------+-----------+
| 1 | Bakiorv | Bekmurod | Botirjon | 994672150 |
| 2 | Ergashaliyev | Elyor | Toxirjon | 932709421 |
| 3 | Bakirov |Bahrom| Dilshodjon | 911447793 |
+----+--------------+---------------+----------------+-----------+
4 rows in set (0.00 sec)
mysql> select*from xodim where id = ANY
-> (select id from mijoz);
+----+--------------+---------------+----------------+-----------+
| id | familyasi | ism | otasining_ismi | telefon |
+----+--------------+---------------+----------------+-----------+
| 1 | Bakiorv | Bekmurod | Botirjon | 994672150 |
| 2 | Ergashaliyev | Elyor | Toxirjon | 932709421 |
| 3 | Bakirov |Bahrom| Dilshodjon | 911447793 |
|
+----+--------------+---------------+----------------+-----------+
4 rows in set (0.00 sec)
mysql> select*from xodim where id != ALL
-> (select id from jarayon);
Empty set (0.00 sec)
mysql> select*from xodim where id != ALL
-> (select id from mijoz); Empty set (0.00 sec)
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 X.ism,J.sana from XODIM X
-> INNER JOIN JARAYON J
-> ON x.id=j.id;
+---------------+------------+
| ism | sana |
+---------------+------------+
| Elyor | 2020-11-20 |
| Bekmurod | 2020-12-20 |
| Akbar | 2020-06-20 |
| Aziz | 2020-09-20 |
+---------------+------------+
4 rows in set (0.00 sec)
mysql> select X.ism,J.sana from XODIM X
-> LEFT JOIN JARAYON J
-> ON x.id=j.id;
+---------------+------------+
| ism | sana |
+---------------+------------+
| Elyor | 2020-11-20 |
| Bekmurod | 2020-12-20 |
| Akbar | 2020-06-20 |
| Aziz | 2020-09-20 |
+---------------+------------+
4 rows in set (0.00 sec)
mysql> select X.ism,J.sana from XODIM X
-> left JOIN JARAYON J
-> ON x.id=j.id;
+---------------+------------+
| ism | sana |
+---------------+------------+
| Elyor | 2020-11-20 |
| Bekmurod | 2020-12-20 |
| Akbar | 2020-06-20 |
| Aziz | 2020-09-20 |
+---------------+------------+
4 rows in set (0.00 sec)