Write a sql query using relation database schema of following two statements

1.A list of Officials and their highest preferred languages. Sort according to their First name followed by Last name. Display their full names and the
highest preferred languages (names and preferences). Note that there is only one highest preferred language for each official.
2. The date on which the most recent Trip(s) was (were) complete. Show the date as well as the vehicle model(s) and type(s) description involved in the trip(s)

 
 

Transcribed Image Text:

Entity LANGUAGE COUNTRY COUNTRY_LANGUAGE LOCATION LOCATION_TYPE DRIVER DRIVER LANGUAGE VEHICLE VEHICLE_TYPE MAINTENANCE_REPAIR OFFICIAL OFFICIAL_ROLE TRIP Field Name LanguageCode LanguageName CountryCode CountryName CountryCode LanguageCode Relational Database Schema PercentageSpoken LocationID LocationTypeID StreetNo SteetName City State Postcode LocTypeID LocType Description DriverLicenceNum DriverFirst Name DriverLastName Clearance Level IsAvailable LanguageCode DriverLicenceNum VIN Veh_TypeID Veh Make Veh Model Veh_Year Veh_Colour Veh_RegoNum Veh_Odometer Veh_Availability DriverLanguageProf CHAR CHAR CHAR Veh_Type_Description VIN Odometer Reading MR_Type MR_Date MR_Cost MR_Detail OfficialID Official Role CountryCode. Official First Name Official LastName RolelD Role Description BookingRefNum DriverLicenceNum OfficialID CHAR INT CHAR Veh_For_Passenger CHAR Veh_For_Cargo Veh_TypeID LanguageCode Datatype Length Description CHAR 2 VARCHAR 50 CHAR 2 VARCHAR 50 CHAR 2 PickUpLocID CHAR DronOffroaID DECIMAL (5,2) INT(AUTO) CHAR CHAR CHAR CHAR VARCHAR 5 VARCHAR 50 VARCHAR 40 3 4 2 VARCHAR 40 CHAR 12 VARCHAR 40 VARCHAR 40 1 1 2 CHAR CHAR CHAR CHAR CHAR CHAR VARCHAR CHAR INT CHAR DATE VARCHAR 30 VARCHAR 30 4 CHAR VARCHAR 20 6 CHAR CHAR CHAR 2 CHAR 2 CHAR INT 12 DECIMAL (7,2) VARCHAR 100 INT 1 17 2 1 1 1 2 40 17 1 VARCHAR 40 VARCHAR 40 CHAR 2 VARCHAR 40 INT (AUTO) CHAR 8 2 2 12 8 2 Primary Key, see examples in Ref. [1] Primary Key, see examples in Ref. [2] Primary Key, Foreign Key references COUNTRY(CountryCode) Primary Key, Foreign Key references LANGUAGE(LanguageCode) e.g., 50.25, 80 Primary Key Foreign Key references LOCATION_TYPE(LocTypeID) e.g., QLD, NSW, SA e.g., 4004, 2002, 3003 Primary Key e.g., Airport, Swimming Pool Primary Key Levels: 1, 2, 3, 4 (4 is the highest clearance) Y, N Primary Key, Foreign Key references LANGUAGE(LanguageCode) Primary Key, Foreign Key references DRIVER (DriverLicenceNum) See examples in Ref. [3] (5 is the highest) Primary Key Foreign Key references VEHICLE TYPE(Veh_TypeID) e.g., Toyota e.g., Corolla e.g., 2020 e.g., Red Y, N Y, N Y, N Primary Key e.g., Sedan Primary Key, Foreign Key references VEHICLE(VIN) Primary Key M, R Format: DD-MM-YYYY Primary key Foreign Key references OFFICIAL_ROLE(RoleID) Foreign key references COUNTRY(CountryCode) Primary key e.g., Judge, Coach Primary Key Foreign Key References DRIVER (DriverLicenceNum) Foreign key references OFFICIAL_LANGUAGE(OfficialID) Foreign Key references OFFICIAL LANGUAGE(Language Code) Foreign key references LOCATION (LocationID) Foreign kou refer Entity LANGUAGE COUNTRY COUNTRY_LANGUAGE LOCATION LOCATION_TYPE DRIVER DRIVER LANGUAGE VEHICLE VEHICLE_TYPE MAINTENANCE_REPAIR OFFICIAL OFFICIAL_ROLE TRIP Field Name LanguageCode LanguageName CountryCode CountryName CountryCode LanguageCode Relational Database Schema PercentageSpoken LocationID LocationTypeID StreetNo SteetName City State Postcode LocTypeID LocType Description DriverLicenceNum DriverFirst Name DriverLastName Clearance Level IsAvailable LanguageCode DriverLicenceNum VIN Veh_TypeID Veh Make Veh Model Veh_Year Veh_Colour Veh_RegoNum Veh_Odometer Veh_Availability DriverLanguageProf CHAR CHAR CHAR Veh_Type_Description VIN Odometer Reading MR_Type MR_Date MR_Cost MR_Detail OfficialID Official Role CountryCode. Official First Name Official LastName RolelD Role Description BookingRefNum DriverLicenceNum OfficialID CHAR INT CHAR Veh_For_Passenger CHAR Veh_For_Cargo Veh_TypeID LanguageCode Datatype Length Description CHAR 2 VARCHAR 50 CHAR 2 VARCHAR 50 CHAR 2 PickUpLocID CHAR DronOffroaID DECIMAL (5,2) INT(AUTO) CHAR CHAR CHAR CHAR VARCHAR 5 VARCHAR 50 VARCHAR 40 3 4 2 VARCHAR 40 CHAR 12 VARCHAR 40 VARCHAR 40 1 1 2 CHAR CHAR CHAR CHAR CHAR CHAR VARCHAR CHAR INT CHAR DATE VARCHAR 30 VARCHAR 30 4 CHAR VARCHAR 20 6 CHAR CHAR CHAR 2 CHAR 2 CHAR INT 12 DECIMAL (7,2) VARCHAR 100 INT 1 17 2 1 1 1 2 40 17 1 VARCHAR 40 VARCHAR 40 CHAR 2 VARCHAR 40 INT (AUTO) CHAR 8 2 2 12 8 2 Primary Key, see examples in Ref. [1] Primary Key, see examples in Ref. [2] Primary Key, Foreign Key references COUNTRY(CountryCode) Primary Key, Foreign Key references LANGUAGE(LanguageCode) e.g., 50.25, 80 Primary Key Foreign Key references LOCATION_TYPE(LocTypeID) e.g., QLD, NSW, SA e.g., 4004, 2002, 3003 Primary Key e.g., Airport, Swimming Pool Primary Key Levels: 1, 2, 3, 4 (4 is the highest clearance) Y, N Primary Key, Foreign Key references LANGUAGE(LanguageCode) Primary Key, Foreign Key references DRIVER (DriverLicenceNum) See examples in Ref. [3] (5 is the highest) Primary Key Foreign Key references VEHICLE TYPE(Veh_TypeID) e.g., Toyota e.g., Corolla e.g., 2020 e.g., Red Y, N Y, N Y, N Primary Key e.g., Sedan Primary Key, Foreign Key references VEHICLE(VIN) Primary Key M, R Format: DD-MM-YYYY Primary key Foreign Key references OFFICIAL_ROLE(RoleID) Foreign key references COUNTRY(CountryCode) Primary key e.g., Judge, Coach Primary Key Foreign Key References DRIVER (DriverLicenceNum) Foreign key references OFFICIAL_LANGUAGE(OfficialID) Foreign Key references OFFICIAL LANGUAGE(Language Code) Foreign key references LOCATION (LocationID) Foreign kou refer