Write a sql query using relation database schema of following two statements 1.A list of Officials and
Fantastic news! We've Found the answer you've been seeking!
Question:
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
Expert Answer:
Related Book For
Concepts of Database Management
ISBN: 978-1285427102
8th edition
Authors: Philip J. Pratt, Mary Z. Last
Posted Date:
Students also viewed these databases questions
-
In this project you are to choose a publicly traded company based on Canada / U.S. The company must be in the retail sector. You will have to access the company's financial statements for 2020, 2021...
-
Consider the following relational schema: Staff ( staffNo , name, dept, skillCode) Skill ( skillCode , description, chargeOutRate) Project ( projectNo ,...
-
In Data 2.1 on page 46, we introduce a study in which people were asked whether they agreed or disagreed with the statement that there is only one true love for each person. Is the level of a persons...
-
A three-dimensional elasticity problem of a uniform bar stretched under its own weight gives the following strain field: where A and B are constants. Integrate the strainedisplacement relations to...
-
In Figure 28-43 the two speakers emit sound that is 180 out of phase and of a single frequency, . (a) Does Larry hear a sound intensity that is a maximum or a minimum? Does your answer depend on the...
-
Both drawings show the same square, each of which has a side of length L = 0.75 m. An observer O is stationed at one corner of each square. Two loudspeakers are located at corners of the square, as...
-
The following information is available for Virginia Company: Calculate the following ratios for 2019: a. Gross profit percentage b. Return on sales c. Asset turnover d. Return on assets e. Return on...
-
Which of the following are characteristic of principalagent conflicts that often exist in a firm? (Note: The entire statement must be true in order to be a correct answer.) a. Managers do not always...
-
Describe Wells Fargo Bank's organizational structure, financial position, scope of operations, and business model (this material was part of the Module 4 Milestone assignment and can be used and or...
-
Ray Flagg took out a 60-month fixed installment loan of $12,000 to open a new pet store. He paid no money down and began making monthly payments of $232. Ray's business does better than expected and...
-
Ethics is something that a paralegal will encounter every day in the profession. There may be times when a paralegal may feel compelled to overstep their role in assisting the client, often while not...
-
1. Devise a way to determine the height of a building using only a stopwatch. 2. From the point of view of physics, is there a basis to the law banning the use of cell phone while driving?
-
The concentration of a drug in the body decreases exponentially after a dosage is given. In one clinical study, adult subjects averaged 12 micrograms/milliliter (mcg/mL) of the drug in their blood...
-
The extremes of the X-ray portion of the electromagnetic spectrum range from approximately 1.0x108 m to 1.0x10-13 m. a. Find the minimum accelerating voltages required to produce wavelengths at these...
-
Given 6 Lf(x) dx = 7 and [f(x) dx = -3 evaluate each definite integral. 6 (a) 1 f(x) dx = [ 3 f(x) dx = (b) [ f(x) (c) 3 f(x) dx = = LRON- Lf(x) 3 (d) 6 -10f(x) dx = [ 3
-
Discuss the impact of disruptive technologies on business management practices.
-
A 120 V rms power source is connected to a 10:1 step down transformer. The secondary winding of the transformer is connected to a 100 ohm load resistor. What is the rms current through load resistor?
-
Big Jim Company sponsored a picnic for employees and purchased a propane grill equipped with a standard-sized propane tank for the picnic. To make sure there was enough propane for all the cooking...
-
What is multiplicity?
-
The requirements shown in Question 11 have changed. The dentists office would like to add the date of the patients last payment. In which relation (table) would you place this attribute? Why? Data...
-
How do views relate to security?
-
Which of the following flowcharts illustrates the flow of information among areas of responsibility in an organization? a. program flowchart c. system flowchart b. computer configuration chart d....
-
Which of the following is not one of the recommended guidelines for making flowcharts more readable, clear, concise, consistent, and understandable? a. Divide the flowchart into columns with labels....
-
Data sources and destinations are represented in a data flow diagram as: a. a square d. two parallel lines b. a curved arrow e. none of the above c. a circle
Study smarter with the SolutionInn App