Given the following database of a Bus company The underlined

Given the following database of a Bus company. The underlined attributes represent the primary key The bus capacity represents the maximum number of passengers of the bus. The Schedule table contains the scheduling of buses and drivers on the several routes. Add the color field to the Bus table. Add the following constraint to the Bus table: \"Possible values of Bus type are: A, B, C D, F and E\". For all buses of the type \'A\', assign the color \'blue\'. Give the total bus capacity the number of drivers. For each city, calculate the number of drivers. Give the numbers and names of the drivers who are scheduled today on a bus with a capacity of more than 50 Give the numbers and names of the drivers who have not been scheduled to a bus of type \'A\'. Give the numbers and names of the drivers who drive one of buses which was driving by the driver \"Smith\". Give the bus (or buses) number that has the second highest capacity Give the numbers and names of the drivers who drive only, buses of type \'A\' Give the numbers and names of the drivers who arc scheduled two times, at least, on the same bus Give the numbers and names of the drivers who are travelling on all routes with more than 10 stops. Remove all drivers that have not been scheduled and remove all buses that have not been not scheduled after 1/1/2005.

Solution

(1) Adding the color field to the Bus Table :

(2) Adding the constraint for possible values of Bus Types :

(3) Assigning the color Blue to all Buses of type A :

UPDATE Bus
SET color=\'Blue\'
WHERE type=\'A\';

(4) Total Bus Capacity of the company :

SELECT SUM(capacity) AS Total_Bus_Capacity FROM Bus;

(5) The numbers of drivers in each city :

SELECT city, COUNT(city) AS number_of_drivers

FROM Driver

GROUP BY city;

(6) Numbers and names of the drivers who are scheduled today on a bus with a capacity of more than 50 :

SELECT d.driver-no, d.name

FROM Driver d

WHERE d.driver-no IN

(SELECT s.driver-no from SCHEDULE s WHERE s.leaving-date =

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

AND

(SELECT b.capacity from Bus b WHERE b.capacity > 50));

(7) Numbers and names of the drivers who have not been scheduled to a bus of type \'A\' :

SELECT d.driver-no, d.name

FROM Driver d

WHERE (SELECT b.type from Bus b WHERE b.type != \'A\';

(9) Bus Number with the second-highest capacity of passsengers :

SELECT bus-no FROM Bus

WHERE capacity = (SELECT MAX(capacity) FROM Bus

WHERE capacity < (SELECT MAX(capacity) FROM Bus));

(6) Numbers and names of the drivers who are scheduled today on a bus with a capacity of more than 50 :

SELECT d.driver-no, d.name

FROM Driver d

WHERE d.driver-no IN

(SELECT s.driver-no from SCHEDULE s WHERE s.LEAVING-DATE =

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

AND

(SELECT b.capacity from Bus b WHERE bcapacity > 50));

(13) Removing all drivers that have not been scheduled :

DELETE FROM Driver WHERE driver-no IN (SELECT s.driver-no FROM Schedule s WHERE s.leaving-date IS NULL);

Removing all buses that have not been scheduled after 1/1/2005 :

DELETE FROM Bus WHERE driver-no IN (SELECT s.driver-no FROM Schedule s WHERE s.leaving-date < \'1/1/2005\');