Write SQL queries for the following prompt: The Long Haul Database belongs to a
ID: 3867921 • Letter: W
Question
Write SQL queries for the following prompt:
The Long Haul Database belongs to a company name Long Haul, which operates a number of
passenger vans. Vans are distinguished by registration code, and characterized by the number of
passenger seats. The company keep track of the cabin type of its vans. Cabin type differ in their
air conditioning capabilities. At present, there are only two types: those with air-conditioning and
those without it (yes/no). Each van is assigned a quality rank (which is equal to the number of
break-down within the last year.)
Long Haul operates various lines among the neighboring towns. All lines are express and
passenger vans never stop on a line. Each line is assign a unique number, and characterized by its
origin and destination towns. Additionally, the management keeps track of the length of distance
covered by each line (in miles).
Information is stored about drivers. For each drivers, the management needs to know ssn, first
and last names, and date of birth. If available, driver contact phone numbers are stored.
The management keeps a departure plan of lines, which schedules the departure and arrival times
of each line on each date. Carefully predicting the passenger interest, the scheduling clerk dispatches
one or more vans to each individual scheduled line. For each dispatched van, the dispatcher records
which driver has been assigned to it. Once this assignment is made, the ticket booth is free to book
tickets for each dispatched van. Each sold seat is recorded, so that no van should be booked above
its capacity.
The database is created by the following SQL statements:
create table van(
registr char(6),
seats number,
cabin char(1),
rank number,
primary key(registr)
);
create table driver(
ssn char(10),
firstname varchar(16),
lastname varchar(36),
birthdate date,
primary key(ssn)
);
create table phonebook(
person char(10),
phonenum varchar(16),
primary key(person, phonenum),
foreign key (person) references driver
);
create table line(
linenum number,
origin varchar(20),
destination varchar(20),
distance number,
primary key (linenum)
);
create table plan(
depcode char(12),
nline number,
depart date,
arrive date,
primary key (depcode),
foreign key (nline) references line
);
create table dispatch(
vehicle char(6),
depcode char(12),
soldseats number,
driverid char(10),
primary key (vehicle, depcode),
foreign key (driverid) references driver,
foreign key (depcode) references plan,
foreign key (vehicle) references van
);
Write SQL statements for the queries specified below:
1. Find the origin and destination of those vans on which at least one dispatched van has had
all its seats sold.(6 points)
2. find the largest distance and the average distance covered by the lines that depart today ( we
do not know what date is today).(5 points)
3. Find the total number of passenger seats sold (altogether) in all the vans that have been
dispatched to lines that cover a distance longer than 50 miles. (5 points)
Explanation / Answer
create table van(
registr char(6),
seats number,
cabin char(1),
rank number,
primary key(registr)
);
create table driver(
ssn char(10),
firstname varchar(16),
lastname varchar(36),
birthdate date,
primary key(ssn)
);
create table phonebook(
person char(10),
phonenum varchar(16),
primary key(person, phonenum),
foreign key (person) references driver
);
create table line(
linenum number,
origin varchar(20),
destination varchar(20),
distance number,
primary key (linenum)
);
create table plan(
depcode char(12),
nline number,
depart date,
arrive date,
primary key (depcode),
foreign key (nline) references line
);
create table dispatch(
vehicle char(6),
depcode char(12),
soldseats number,
driverid char(10),
primary key (vehicle, depcode),
foreign key (driverid) references driver,
foreign key (depcode) references plan,
foreign key (vehicle) references van
);
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.