SQL - Query to display average as either "longer than" or "shorter than"
- by user1840801
Here are the tables I've created:
CREATE TABLE Plane_new (Pnum char(3), Feature varchar2(20), Ptype varchar2(15),
primary key (Pnum));
CREATE TABLE Employee_new (eid char(3), ename varchar(10), salary number(7,2), mid char(3),
PRIMARY KEY (eid), FOREIGN KEY (mid) REFERENCES Employee_new);
CREATE TABLE Pilot_new (eid char(3), Licence char(9), primary key (eid),
foreign key (eid) references Employee_new on delete cascade);
CREATE TABLE FlightI_new (Fnum char(4), Fdate date, Duration number(2), Pid char(3),
Pnum char(3), primary key (Fnum), foreign key (Pid) references Pilot_new (eid),
foreign key (Pnum) references Plane_new);
And here is the query I must complete:
For each flight, display its number, the name of the pilot who implemented the flight
and the words ‘Longer than average’ if the flight duration was longer than average or
the words ‘Shorter than average’ if the flight duration was shorter than or equal to the
average. For the column holding the words ‘Longer than average’ or ‘Shorter than average’
make a header Length.
Here is what I've come up with - with no luck!
SELECT F.Fnum, E.ename,
CASE Length
WHEN F.Duration>(SELECT AVG(F.Duration) FROM FlightI_new F)
THEN "Longer than average"
WHEN F.Duration<=(SELECT AVG(F.Duration) FROM FlightI_new F)
THEN 'Shorter than average' END
FROM FlightI_new F LEFT OUTER JOIN Employee_new E ON F.Pid=E.eid
GROUP BY F.Fnum, E.ename;
Where am I going wrong?
