Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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)

IMG_1686.jpeg

IMG_1687.jpeg


 
 

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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Concepts of Database Management

Authors: Philip J. Pratt, Mary Z. Last

8th edition

1285427106, 978-1285427102

More Books

Students also viewed these Databases questions

Question

What is multiplicity?

Answered: 1 week ago

Question

How do views relate to security?

Answered: 1 week ago

Question

What percent is $1.50 of $11.50?

Answered: 1 week ago