Axborot texnologiyalari va kommunikatsiyalarni

615.21

AXBOROT TEXNOLOGIYALARI VA KOMMUNIKATSIYALARNI


RIVOJLANTIRISH VAZIRLIGI


MUHAMMAD AL – XORAZMIY NOMIDAGI


TOSHKENT AXBOROT TEXNOLOGIYALARI UNIVERSITETI


FARG‘ONA FILIALI

“Kompyuter injinering” fakulteti

Kompyuter injinering yo‘nalishi

615.21– guruh talabasi

Bozorova Munavvarxonning

“MA’LUMOTLAR BAZASI”

fanidan tayyorlagan
AMALIY MASHG‘ULOT
ISHLARI


Topshirdi: Bozorova M
Qabul qildi: Dilshodov A.D.
Mavzu:Shahar avtomobilsozlik axborot-tizimi. (6-variant)

1.Topshiriq.

Group by va Order by standart so‘zlaridan foydalanib so‘rov yaratish.

country, COUNT(*) AS number
FROM Customers
GROUP BY country;

SELECT column1, column2, ...

FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
Select transport_nomi,count(*) as ish_vaqti
-> from avtotransport
-> group by transport_nomi;

1-rasm


2-rasm
Izoh: vtotransport nomli jadvalni (2-rasm) asc yoki desc qilmasdan oldingi holati tasvirlangan.

select * from avtotransport

-> order by ish_vaqti desc;

3-rasm


Izoh: avtotransport nomli (3-rasm) jadvalini select * from fan_jadvali order by soat_soni desc; ishlatganimizdan so’ng holati

select * from avtotransport

-> order by ish_vaqti asc ;



4-rasm
Izoh: avtotransport nomli (4-rasm) jadvalini select * from fan_jadvali order by soat_soni asc; ishlatganimizdan so’ng holati
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

select ish_vaqti

-> from avtotransport
-> group by ish_vaqti
-> having ish_vaqti >=9;




6-7-rasmlar
2.Topshiriq.
Union standart so‘zidan foydalanish.

SELECT column_name(s) FROM table1

UNION
SELECT column_name(s) FROM table2;

select garaj_nomi from garaj_fermasi

-> union all
-> select transport_nomi from avtotransport
-> order by garaj_nomi;
Izoh: garaj_fermasi va avtotransport jadvalarini ichidagi
jadvalarida (8-rasm)
select garaj_nomi from garaj_fermasi
-> union all
-> select transport_nomi from avtotransport
-> order by garaj_nomi;
ishlatganimizdan so’ng holati.

8-rasm

SELECT REPEAT(Column, 2)

FROM table_name
select REPEAT (transport_nomi,2)
-> from avtotransport;

9-rasm


Izoh: avtotransport jadvaini ichida
(9-rasm)
select REPEAT (transport_nomi,2)
-> from avtotransport
shlatganimizdan so’ng holati.

3.Topshiriq.

Standart funksiyalardan foydalanib so‘rovlar yaratish. Agregat funksiyalardan foydalanish.


10-rasm
SELECT MAX (ish_vaqti) AS h FROM avtotransport ;

SELECT MIN (ish_vaqti) AS h FROM avtotransport ;

11-rasm

Select power (4,2)

12-rasm
Izoh: Standart funksiyalardan foydalanib so‘rovlar yaratish hamda agregat funksiyalardan foydalaniilgan jarayonlar (10-11-12-rasmlarda) namoyish etilgan.

4.Topshiriq.

Murakkab so‘rovlar yaratish. Bir nechta jadvallar birlashtirish, «Join» lar bilan ishlash.
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
select transport_nomi
-> from avtotransport
-> inner join garaj_fermasi
-> on avtotransport.transport_nomi=garaj_fermasi.garaj_nomi;



13-rasm
Izoh: Murakkab so‘rovlar yaratish. Bir nechta jadvallar birlashtirish, «Join» lar bilan ishlash (13-rasm)da ko’rsatilgan.

5.Topshiriq.

SQL tilida INDEX yaratish. Tasavvur, VIEW yaratish va undan foydalanish.

CREATE INDEX index_name

ON table_name (column1, column2, ...);

create index garaj_nomi_index

-> on avtotransport (transport_nomi,ish_vaqti,kilometr,tamirlash_soni,xarakteristikalar);



14-rasm