Given the SQL table creation script below, create a SQL query that SELF-JOINS th

Question

Given the SQL table creation script below, create a SQL query that SELF-JOINS the table fac to itself, showing the fid, fname, rank and salary of everyone with a rank of 'ASSO',or 'INST' that have a greater salary than ANY of the 'FULL' faculty members. NOTE: don't look for the average salary of either group, just if anyone in 'ASSO','INST' makes more than 'FULL' and DON'T use a GROUP BY:  

drop table fac;

CREATE TABLE fac (fid NUMBER,   
fname VARCHAR2(30),   
ext NUMBER(9),   
dept VARCHAR2(5),   
rank VARCHAR2(10),   
salary NUMBER(7,2) );
INSERT INTO fac VALUES(036, 'BARGES', 325, 'MGT','ASSO',35000);

INSERT INTO fac VALUES(117, 'JARDIN', 212, 'FIN','FULL',33000);

INSERT INTO fac VALUES(098, 'KENNEDY',176, 'ACC', 'ASSO',30000);

INSERT INTO fac VALUES(075, 'SAMPLE', 171,'MKT','ASST', 25000);

INSERT INTO fac VALUES(138, 'WARD', 125,'MGT','INST', 20000);

INSERT INTO fac VALUES(219, 'PETERS', 220,'FIN','FULL', 45000);

INSERT INTO fac VALUES(151, 'DARDIN', 250,'ACC','ASSO', 37000);

INSERT INTO fac VALUES(138, 'SAMPLE', 205,'MGT','INST', 22000);

Explanation / Answer

SELECT a.fid,a.fname,a.rank,a.salary
FROM fac a , fac b
WHERE a.rank IN ('ASSO','INST')
AND b.rank = 'FULL'
AND b.salary > a.salary;

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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