|
INSERT INTO oquvchilar VALUES(44,'Yarasheva','Rano','11-sinf','A');
INSERT INTO oquvchilar VALUES(45,'Yo`ldoshev','Bobur','9-sinf','B');
INSERT INTO oquvchilar VALUES(46,'Rixsiyev','Zohid','9-sinf','A');
INSERT INTO oquvchilar VALUES(47,'Karimboyeva','Nigora','11-sinf','B');
INSERT INTO oquvchilar VALUES(48,'Qodirov','Ulug`bek','11-sinf','A');
INSERT INTO oquvchilar VALUES(49,'Matyusupov','Yo`ldoshbek','9-sinf','B');
INSERT INTO oquvchilar VALUES(50,'Sherimmetov','Davron','10-sinf','B');
insert into fanlar values (1,5,4,5,3,5,5),(2,3,4,4,3,5,4),(3,5,5,5,5,5,5),(4,3,4,4,3,3,4),(5,5,4,4,4,4,5),(6,5,4,4,5,4,5);
INSERT INTO fanlar VALUES(7,4,4,5,3,3,4);
INSERT INTO fanlar VALUES(8,4,4,5,3,3,4);
INSERT INTO fanlar VALUES(9,4,5,5,5,5,5);
INSERT INTO fanlar VALUES(10,4,3,3,3,4,4);
INSERT INTO fanlar VALUES(11,4,3,3,5,5,4);
INSERT INTO fanlar VALUES(12,4,4,5,5,4,4);
INSERT INTO fanlar VALUES(13,3,3,3,3,3,3);
INSERT INTO fanlar VALUES(14,4,3,3,5,4,3);
INSERT INTO fanlar VALUES(15,4,4,5,5,4,5);
INSERT INTO fanlar VALUES(16,3,3,5,5,4,4);
INSERT INTO fanlar VALUES(18,4,3,4,5,4,5);
INSERT INTO fanlar VALUES(19,4,4,5,4,4,4);
INSERT INTO fanlar VALUES(20,4,3,5,5,4,4);
INSERT INTO fanlar VALUES(21,3,3,3,3,3,3);
INSERT INTO fanlar VALUES(22,5,3,5,5,4,3);
INSERT INTO fanlar VALUES(23,3,3,5,5,4,4);
INSERT INTO fanlar VALUES(24,4,5,5,5,5,4);
INSERT INTO fanlar VALUES(25,4,3,4,5,4,4);
INSERT INTO fanlar VALUES(26,4,3,5,4,4,5);
INSERT INTO fanlar VALUES(27,4,5,4,5,4,4);
INSERT INTO fanlar VALUES(28,4,3,5,5,4,4);
INSERT INTO fanlar VALUES(29,5,5,5,5,5,5);
INSERT INTO fanlar VALUES(30,4,3,5,5,4,4);
INSERT INTO fanlar VALUES(31,4,3,5,5,4,4);
INSERT INTO fanlar VALUES(32,4,3,5,5,4,4);
INSERT INTO fanlar VALUES(33,4,3,3,3,3,4);
INSERT INTO fanlar VALUES(34,4,3,4,3,4,5);
INSERT INTO fanlar VALUES(35,5,4,5,5,4,5);
INSERT INTO fanlar VALUES(36,4,4,4,4,4,4);
INSERT INTO fanlar VALUES(37,5,4,5,5,4,4);
INSERT INTO fanlar VALUES(38,4,5,5,3,4,3);
INSERT INTO fanlar VALUES(39,3,3,3,4,4,3);
INSERT INTO fanlar VALUES(40,4,3,5,5,4,4);
INSERT INTO fanlar VALUES(41,5,3,3,4,3,4);
INSERT INTO fanlar VALUES(42,4,5,5,5,4,5);
INSERT INTO fanlar VALUES(43,3,4,4,5,4,4);
INSERT INTO fanlar VALUES(44,4,5,5,5,4,5);
INSERT INTO fanlar VALUES(45,4,5,5,4,4,4);
INSERT INTO fanlar VALUES(46,5,5,5,5,4,4);
INSERT INTO fanlar VALUES(47,5,4,4,5,4,4);
INSERT INTO fanlar VALUES(48,5,4,5,4,4,5);
INSERT INTO fanlar VALUES(49,3,4,5,5,4,3);
INSERT INTO fanlar VALUES(50,3,3,4,5,4,3);
select *from xodimlar;
select *from Oquvchilar;
select *from fanlar;
UPDATE xodimlar SET oyligi=3000000 WHERE xodim_ID=1;
UPDATE xodimlar SET oyligi=3500000 WHERE xodim_ID=2;
UPDATE xodimlar SET oyligi=5000000 WHERE xodim_ID=3;
UPDATE xodimlar SET oyligi=4600000 WHERE xodim_ID=4;
UPDATE xodimlar SET oyligi=7000000 WHERE xodim_ID=5;
UPDATE xodimlar SET oyligi=6600000 WHERE xodim_ID=6;
UPDATE xodimlar SET oyligi=10000000 WHERE xodim_ID=7;
UPDATE xodimlar SET oyligi=4500000 WHERE xodim_ID=8;
UPDATE xodimlar SET oyligi=3900000 WHERE xodim_ID=9;
UPDATE xodimlar SET oyligi=5800000 WHERE xodim_ID=10;
UPDATE xodimlar SET oyligi=6000000 WHERE xodim_ID=11;
UPDATE xodimlar SET oyligi=5000000 WHERE xodim_ID=12;
UPDATE xodimlar SET oyligi=5000000 WHERE xodim_ID=13;
UPDATE xodimlar SET oyligi=8200000 WHERE xodim_ID=14;
UPDATE xodimlar SET oyligi=4000000 WHERE xodim_ID=15;
UPDATE xodimlar SET oyligi=7000000 WHERE xodim_ID=16;
UPDATE xodimlar SET oyligi=6000000 WHERE xodim_ID=17;
UPDATE xodimlar SET oyligi=6000000 WHERE xodim_ID=18;
UPDATE xodimlar SET oyligi=7700000 WHERE xodim_ID=19;
UPDATE xodimlar SET oyligi=5400000 WHERE xodim_ID=20;
And funksiyasidan foydalanib oquvchilar jadvalining 10-sinf A guhida oqidigan talabalar ro`yxati. (4-rasm)
SELECT Familiya,Ism,sinf,guruh FROM oquvchilar WHERE sinf='10-sinf' AND guruh='A';
4-rasm
Like funksiyasidan foydalanib xodimlar jadvalining Ismlar qatoridagi A bn boshlangan Ismlar olindi(5-rasm)
SELECT * FROM xodimlar WHERE Ism LIKE ‘A%’;
5-rasm
Between yordamida xodimlar jadvalidagi oyligi 5000000 dan katta va 9500000 dan kichik boganlarini chiqarish.(6-rasm)
S ELECT *FROM xodimlar where oyligi BETWEEN 5000000 AND 9500000;
6-rasm
6 - LABORATORIYA ISHI Mavzu: Ma’lumotlarni saralash. Where standart so‘zidan foydalanish
Ishdan maqsad: Ma`lumotlar bazasiga so`rovlar tizimini yaratish, saralash va WHERE standart so`zidan foydalanishni o`rganish va ko`nikmaga ega bo`lish.
Masalani qo`yilishi: Loyihalanayotgan ma`lumotlar bazasiga so`rovlar tizimini tuzish va uni relyatsion sxema ko`rinishida tasvirlash. So`rovlar tizimi orqali kerakli ma`lumotlarni ajratib olish va saralash.
Where not funksiyasi yordamida fanlar jadvalidagi matematika ustinidagi baholari 3 va 4 bomaganlarini chiqarish (7-rasm)
SELECT *FROM fanlar WHERE NOT matematika=3 AND not matematika=4;
7-rasm
Sum() va min() funksiyalari ustida amallar (8-9-rasmlar)
SELECT SUM(oyligi) FROM xodimlar; SELECT MIN(oyligi) FROM xodimlar;
9-rasm
8-rasm
7 - LABORATORIYA ISHI Mavzu: Group by va Order by standart so‘zlaridan foydalanib so‘rov yaratish
Ishdan maqsad: Berilgan predmet soha ma`lumotlar bazasidan foydalanib GROUP BY va ORDER BY standart so`zlaridan foydalanishni o`rganish.
Masalani qo`yilishi: Predmet soha ma`lumotlar bazasi shakllantirilgandan so`ng undan unumli foydalanishni tashkil etish maqsadida GROUP BY va ORDER BY standart so`zlaridan foydalanib so`rovlar yaratish. Ma`lumotlar bazasidan kerakli ma`lumotlarni yuqoridagi standart so`zlar orqali ajratib olishni tashkil etish.
Group by va order by funksiyalari yordamida fanlar jadvali fizika ustunidagi
Nechta 3,4,5 borligini aniqladik (10-rasm)
SELECT COUNT(fan_ID),fizika
FROM fanlar
GROUP BY fizika
ORDER BY COUNT(fan_ID) DESC;
10-rasm
8 - LABORATORIYA ISHI
Mavzu: Having standart so‘zi orqali so‘rovlar yaratish
Ishdan maqsad: Berilgan predmet soha ma`lumotlar bazasidan foydalanib HAVING standart so`zidan foydalanishni o`rganish.
Masalani qo`yilishi: Predmet soha ma`lumotlar bazasi shakllantirilgandan so`ng undan unumli foydalanishni tashkil etish maqsadida HAVING standart so`zlaridan foydalanib so`rovlar yaratish. Ma`lumotlar bazasidan kerakli ma`lumotlarni yuqoridagi standart so`z orqali ajratib olishni tashkil etish.
Having yordamida xodimlar jadvalidagi oyligi 5000000dan katta bolganlarini chiqaradi
SELECT Familiya , oyligi FROM xodimlar
GROUP BY Familiya
HAVING oyligi>5000000;
Having yordamida fanlar jadvalidagi korsatilgan amallarni chiqaradi
SELECT *FROM fanlar
HAVING fizika>3 and ingliz_tili>4 AND kimyo>4 AND rus_tili>3 and matematika>4 AND Informatika>3;
Having yordamida oquvchilar jadvalidagi Familiyasi A bilan boshlangan va Sinfi 9-sinf bogan qatorlarni korsatadi
SELECT Familiya, Ism,sinf FROM oquvchilar
HAVING Familiya LIKE "A%" and sinf='9-sinf';
Xulosa
Laboratoriyalarni bajarish jarayonida And ,or ,not mantiqiy standard so`zlardan foydalanishni WHERE so`zidan foydalanishni GROUP BY va ORDER BY so`zlaridan foydalanishni va HAVING standard so`zidan foydalanishni o`rgandik. Bu laboratoriyalar 5-6-7-8-laboratoriyalardan tashkil topgan.
Do'stlaringiz bilan baham: |
