Q- Write sql command to create a database named transport and inside it create a
table transport with the following fields.
● RTNO (INT)
● AREA_COVERED (VARCHAR(15))
● CAPACITY (INT)
● TOTALSTUDENTS (INT)
● DISTANCE (INT)
● TRANSPORT (VARCHAR(15))
● CHARGES (INT)
● SERVICES_STARTED_FROM (DATE)
● TO DISPLAY ALL INFORMATION OF STUDENT WHERE CAPACITY IS MORE
THAN THE NUMBER OF STUDENTS.
● TO DISPLAY AREA_COVERED FOR BUSES WITH DISTANCE MORE THAN 20
KM, BUT CHARGES LESS THAN 80000.
● TO DISPLAY THE AREA_COVERED BY BHALLA CO AND KISAN TOURS.
● TO DISPLAY THE NAMES OF ALL TRANSPORTERS , AREA_COVERED , THE
DAY , MONTH NAME AND YEAR OF THE SERVICES_STARTED_FROM.
● TO DISPLAY THE MINIMUM CHARGES WITH THE TRANSPORTER NAME.
● TO DISPLAY TOTAL NUMBER OF TRANSPORTER WITH NO TRANSPORTER
REPEATED.
● TO DISPLAY THE AREA WHOSE NAME ENDS WITH “R” IN DECREASING
ORDER OF THE AREA COVERED.
● TO DISPLAY RTNO , AREA_COVERED AND AVERAGE COST PER STUDENT FOR
ALL ROUTES WHERE AVERAGE COST PER STUDENT IS – CHARGES/
TOTALSTUDENTS
● TO DISPLAY THE AREAS_COVERED WHOSE CHARGES ARE NOT MORE THAN
85000
● TO DISPLAY THE NUMBER OF STUDENTS BY SPEED TRAVELS
● TO DISPLAY THE TOTAL NUMBER OF STUDENT FROM PITAMPURA WHOSE
USE SCHOOL TRANSPORT
● TO DISPLAY THE RECORDS OF ALL THE TRANSPORTERS AND
AREA_COVERED WHERE THE FIRST THREE LETTERS OF THE TRANSPORTER
‘A’,’N’, AND ‘A’.
● ADD NEW RECORDS WITH DATA : (11,”MOTI BAGH”,35,32,10,”KISAN
TOURS”,35000,’2015-04-01’).
● TO DISPLAY THE TRANSPORT WITH THE RESPECTIVE MAXIMUM CHARGES.
● GIVE THE OUTPUT CONSIDERING THE ORIGINAL RELATION AS GIVEN:
1) SELECT DISTANCE FROM TRANSPORT WHERE TRANSPORT = “YADAV
CO.”;
2) SELECT TOTALSTUDENTS FROM SCHOOLBUS WHERE DISTANCE NOT IN
(20,30);
3) SELECT CHARGES FROM TRANSPORT WHERE TRANSPORT = “ ANAND
TRAVELS ” AND CAPACITY > 80;
4) SELECT DISTINCT (TRANSPORT) FROM TRANSPORT;
5) SELECT AREA_COVERED, DISTANCE, TRANSPORT FROM TRANSPORT
WHERE TRANSPORT LIKE ‘%S’;