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);

Given the fac table description above create a SQL query with a SUBQUERY that does the same thing as the previous query. That is, use a subquery to return the highest salary for a FULL professor and then compare that to anyone who is an associate professor or instructor.

Given the fac table in the previous question and the SQL table creation script below, create a SQL query that will use an INNER JOIN to show the faculty ID, faculty name, course and year/term for all the course sections that the professors are teaching in fall 2017.

CREATE TABLE crs_sections(fid NUMBER(9),

dept varchar2(5),

course varchar2(10),

yrtrm varchar2(10),

enrl NUMBER(9));

insert into crs_sections values(36,'MGT','MGT102','2017F',30);

insert into crs_sections values(117,'FIN','FIN101','2017F',30);

insert into crs_sections values(98,'ACC','ACC311','2017F',30);

insert into crs_sections values(75,'MKT','MKT411','2017F',30);

insert into crs_sections values(138,'MGT','MGT298','2017F',30);

insert into crs_sections values(219,'FIN','FIN400','2017F',30);

insert into crs_sections values(36,'MGT','MGT202','2017F',30);