Hello to SQL challenging excercises

1

The database schema consists of 4 tables:
Company(ID_comp, name)
Trip(trip_no, id_comp, plane, town_from, town_to, time_out, time_in)
Passenger(ID_psg, name)
Pass_in_trip(trip_no, date, ID_psg, place)
The Company table contains IDs and names of the airlines transporting passengers. The Trip table contains information on the schedule of flights: trip (flight) number, company (airline) ID, plane type, departure city, destination city, departure time, and arrival time. The Passenger table holds IDs and names of the passengers. The Pass_in_trip table contains data on flight bookings: trip number, departure date (day), passenger ID and her seat (place) designation during the flight. It should be noted that

  • scheduled flights are operated daily; the duration of any flight is less than 24 hours; town_from <> town_to;
  • all time and date values are assumed to belong to the same time zone;
  • departure and arrival times are specified with one minute precision;
  • there can be several passengers bearing the same first name and surname (for example, Bruce Willis);
  • the seat (place) designation consists of a number followed by a letter; the number stands for the row, while the letter (a – d) defines the seat position in the grid (from left to right, in alphabetical order;
  • connections and constraints are shown in the database schema below.

Exercise:
For all days between 2003-04-01 and 2003-04-07 find the number of trips from Rostov.
Result set: date, number of trips

1 Like
2

sounds like homework to me. What have you tried? Please provide DDL, sample data and expected results.

1 Like
3

hi

i have created sample data
hope it helps :slight_smile: :slight_smile:
i love feedback

drop create data ... 
create table #Company
 (ID_comp int,
  name varchar(100)
 )
 go 

create table #Trip(
trip_no int, 
id_comp int, 
plane varchar(100), 
town_from varchar(100), 
town_to varchar(100), 
time_out time, 
time_in time)
 go 

 create table #Passenger(
 ID_psg int, 
 name varchar(100)
 )
 go 

 create table #Pass_in_trip
 (trip_no int, 
 datetrip date, 
 ID_psg int, 
 place varchar(100))
 go 

insert into #Company select 1,'AirJet'
 insert into #Company select 2,'KingFisher'
 insert into #Company select 3,'AirIndia'
go 

insert into #Trip select 12,1,'Boeing','hyd','bang','10:45','15:45'
insert into #Trip select 13,2,'AirBus','kent','oh','13:00','14:34'
insert into #Trip select 14,3,'Boeing','delh','calc','15:34','16:00'
go 

insert into #Passenger select 111,'harish'
insert into #Passenger select 222,'prathima'
insert into #Passenger select 333,'Guna'
insert into #Passenger select 444,'prathima'
insert into #Passenger select 555,'prathima'
go 

insert into #Pass_in_trip select 1,'2019-07-09',111,'13A'
insert into #Pass_in_trip select 1,'2019-07-09',222,'09B'
insert into #Pass_in_trip select 1,'2019-07-09',333,'45A'
go

image
image.png626×750 8.37 KB