Consider the following relational database that Best Airlines uses to keep track

Question

Consider the following relational database that Best Airlines uses to keep track of its mechanics, their skills, and their airport locations. Mechanic number (MECHNUM), airport name (AIRNAME), and skill number are all unique fields. SIZE is an airport’s size in acres. SKILLCAT is a skill category, such as an engine skill, wing skill, tire skill, etc. YEARQUAL is the year that a mechanic first qualified in a particular skill; PROFRATE is the mechanic’s proficiency rating in a particular skill.

Write SQL SELECT commands to answer the following queries.

a. List the names and ages of all the mechanics.

b. List the airports in California that are at least
20 acres in size and have been open since 1935.
Order the results from smallest to largest airport.

c. List the airports in California that are at least 20
acres in size or have been open since 1935.

d. FindtheaveragesizeoftheairportsinCalifornia
that have been open since 1935.

e.e. How many airports have been open in California since 1935?
f. How many airports have been open in each state since 1935?
g. How many airports have been open in each state since 1935? Include in your answer only those states that have at least five such airports.
h. List the names of the mechanics who work in California.
i. Fan blade replacement is the name of a skill. List the names of the mechanics who have a proficiency rating of 4 in fan blade replacement.
j. Fan blade replacement is the name of a skill. List the names of the mechanics who work in California who have a proficiency rating of 4 in fan blade replacement.
k. List the total, combined salaries of all of the mechanics who work in each city in California.
l. Find the largest of all of the airports.

m. Find the largest airport in California

Explanation / Answer

Hi,

Below is the answer-

ANS A) SELECT MECHNAME,AGE FROM MECHANIC;
ANS B) SELECT AIRNAME,SIZE FROM AIRPORT WHERE SIZE>=20 AND STATE='CALIFORNIA' AND YEAROPENED >=1935 ORDER BY SIZE ASC;

ANS C) SELECT AIRNAME,SIZE FROM AIRPORT WHERE (SIZE>=20 OR YEAROPENED >=1935) AND STATE='CALIFORNIA';

ANS D) SELECT AVG(SIZE) FROM AIRPORT WHERE STATE='CALIFORNIA' AND YEAROPENED >=1935;

ANS e) SELECT COUNT(AIRNAME) FROM AIRPORT WHERE STATE='CALIFORNIA' AND YEAROPENED >=1935;

ANS F) SELECT COUNT(AIRNAME),STATE FROM AIRPORT WHERE YEAROPENED>=1935 GROUP BY STATE;

ANS G) SELECT COUNT(AIRNAME),STATE FROM AIRPORT WHERE YEAROPENED>=1935 GROUP BY STATE HAVING COUNT(*)>=5;

ANS H) SELECT MECHNAME FROM MECHANIC A JOIN AIRPORT B
ON A.AIRNAME=B.AIRNAME AND B.STATE='CALIFORNIA';

ANS I) SELECT MECHNAME FROM MECHANIC A
JOIN QUALIFICATION B
ON A.MECHNUM=B.MECHNUM
AND B.PROFRATE=4
JOIN SKILL C
ON B.SKILLNUM=C.SKILLNUM
AND SKILLNAME='FAN BLADE RELACEMENT';

ANS J) SELECT MECHNAME FROM MECHANIC A
JOIN QUALIFICATION B
ON A.MECHNUM=B.MECHNUM
AND B.PROFRATE=4
JOIN SKILL C
ON B.SKILLNUM=C.SKILLNUM
AND SKILLNAME='FAN BLADE RELACEMENT'
JOIN AIRPORT D
ON A.AIRNAME=D.AIRNAME
AND STATE='CALIFORNIA';

ANS K) SELECT SUM(SALARY),CITY FROM MECHANIC A
JOIN AIRPORT B
ON A.AIRNAME=B.AIRNAME
AND STATE='CALIFORNIA'
GROUP BY CITY;

ANS L) SELECT MAX(SIZE) FROM AIRPORT ;

ANS M) SELECT MAX(SIZE) FROM AIRPORT WHERE STATE='CALIFORNIA';

Thanks and Regards,

Vinay Prakash Singh

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.