O’zbekiston milliy universiteti amaliy matematika va intellektual texnologiyalar fakulteti axborot tizimlarining matematik va dasturiy ta’minoti yo’nalishi III kurs 19-01 guruh talabasining Ma’lumotlar bazasini boshqarish tizimlari fanidan kurs ishi

TRAVEL jadvali ma`lumotlari:

TRAIN jadvali ma`lumotlari:

GROUP_OF_WAGON jadvali ma`lumotlari

Ma’lumotlar ustida so’rovlar

--1)”К” turidagi vagonlarni topish:

SELECT ID_WAGON, SERIAL_NUMBER, YEAR_OF_PRODUCTION
FROM WAGON
WHERE TYPE = 'К';



--2)1977-1981-yillar oralig`ida ishlab chiqarilgan vagonlarni topish:
SELECT ID_WAGON, SERIAL_NUMBER, YEAR_OF_PRODUCTION
FROM WAGON
WHERE (YEAR_OF_PRODUCTION BETWEEN 1977 AND 1981)
AND NOT YEAR_OF_PRODUCTION IN (1977, 1981);



--3)O`rindiqlari soni 12 va 26 orasida bo`lgan vagonlarni topish:
SELECT ID_WAGON, SERIAL_NUMBER, NUMBER_OF_SEATS
FROM WAGON
WHERE NUMBER_OF_SEATS IN (26, 12);



--4)Locomotive nomi va undan nechta borligini chiqaruvchi so`rov:
SELECT MANUFACTURER, COUNT(ID_LOCOMOTIVE)
FROM LOCOMOTIVE
GROUP BY MANUFACTURER;



--5)Stansiyalar nomi va ushbu stansiyalar uchun reyslar sonini topish:
SELECT STATIONS.NAME_STATION, COUNT (TRAVEL.ID_START_STATION)
FROM STATIONS, TRAVEL
WHERE STATIONS.ID_STATION = TRAVEL.ID_START_STATION
GROUP BY NAME_STATION;



--6)Joriy oyda 2 martadan ko’p reyslarni amalga oshirgan poezdlarni topish:
SELECT TRAIN.ID_LOCOMOTIVE, count (TRAVEL.ID_START_STATION)
FROM TRAVEL,TRAIN
WHERE TRAVEL.ID_TRAIN = TRAIN.ID_TRAIN
AND extract(month from TRAVEL.DATE_OF_ARRIVAL) = extract(month from sysdate)
GROUP BY ID_LOCOMOTIVE
HAVING COUNT(*)>2;



--7)Eng ko’p borilgan stansiya:
SELECT STATIONS.NAME_STATION
FROM STATIONS,
(SELECT ID_STATION, SUM(TRAVEL.ID_FINISH_STATION) MAX_FINISH
FROM STATIONS, TRAVEL
WHERE STATIONS.ID_STATION = TRAVEL.ID_FINISH_STATION
GROUP BY ID_STATION) t
WHERE t.ID_STATION=STATIONS.ID_STATION
AND MAX_FINISH=(SELECT MAX(MAX_FINISH)
FROM (
SELECT ID_STATION,SUM(TRAVEL.ID_FINISH_STATION) AS MAX_FINISH FROM STATIONS, TRAVEL
WHERE STATIONS.ID_STATION = TRAVEL.ID_FINISH_STATION
GROUP BY ID_STATION))



--8)Hech qanday poezdning bir qismi bo’lmagan lokomotivni topish:
SELECT SERIAL_NUMBER FROM LOCOMOTIVE
MINUS
SELECT SERIAL_NUMBER FROM LOCOMOTIVE
WHERE ID_LOCOMOTIVE IN
(SELECT DISTINCT ID_LOCOMOTIVE
FROM TRAIN)


--9)Hech bo’lmasa bitta vagonga bog’langan stansiyani topish:
SELECT STATIONS.NAME_STATION, count(WAGON.ID_WAGON)
FROM STATIONS,WAGON
WHERE STATIONS.ID_STATION = WAGON.ID_STATION
GROUP BY NAME_STATION
HAVING COUNT(*)>=1;