60.1K

Verified Solution

Link Copied!

SQL

Create a report that shows the facultys salary information along with an additional field that displays whether that faculty receives the highest salary among the others who have the same RANK. Your report should look like following table:

SID (PK) SNAME (NN) SEX MAJOR GPA CRSNBR (PK,FK) FK) GRADE 987 763 218 359 862 748 506 581 372 126 400 POIRIER PARKER RICHARDS PELNICK FAGIN MEGLIN LEE GAMBRELL QUICK ANDERSON MARY F F M F M M M F. F M F MGT FIN ACC FIN MGT MGT FIN MKT MGT ACC 3.2 2.7 2.4 3.6 2.2 2.8 2.7 3.8 3.5 3.7 MGT630 FIN602 MKT610 FIN601 FIN602 ACC610 ACC610 ACC661 MGT630 MGT630 MGT681 MKT610 MKT610 MKT670 ACC610 MGT630 MGT681 FIN601 987 987 987 763 763 763 218 218 218 359 359 359 862 862 862 748 748 748 A B B B B A A F B A A A B B A FACULTY Table FID(PK) FNAME(NN) EXT DEPT RANK SALARY JOIN_DATE 036 117 098 BARGES JARDIN KENNEDY SAMPLE WARD PETERS DARDEN PIERCE 075 138 219 151 113 325 MGT 212 FIN 176 ACC 171 MKT 125 MGT 220 FIN 250 ACC 205 MGT ASSO FULL ASSO ASST INST FULL ASSO INST 35000 33000 30000 25000 20000 45000 37000 22000 01-Jan-2000 01-Jan-1980 01-Mar-2000 01-Apr-1998 01-Dec-2002 15-Jan-1976 20-Jun-1999 15-Dec-2000 CRSNBR FID COURSE Table CRSNBRO PK) CNAME(NN,UN) CREDIT MAXENRL FID(FK, NN) 4 4 30 25 35 138 117 075 098 MGT630 FIN601 MKT610 ACC661 FIN602 ACC610 MGT681 MKT670 INTRO TO MANAGEMENT MANAGERIAL FINANCE MKTING FOR MANAGERS TAXATION INVESTMENT SKILLS BASIC ACCOUNTING INTERNATIONAL MGT PRODUCT MARKETING 3 3 3 4 3 3 25 25 20 20 219 098 036 075 DBS301. STUDENT DBS301. STUD_COURSE PF* CRSNBR CHAR (6 BYTE) PF* SID NUMBER (3) GRADE CHAR (1 BYTE) STU_CRS_SID_PK (CRSNBR, SID) STU_CRS_FK (CRSNBR) STU_SID_FK (SID) STU_CRS_SID_PK (CRSNBR, SID) P * SID NUMBER (3) SNAME VARCHAR2 (20 BYTE) SEX CHAR (1 BYTE) MAJOR CHAR (3 BYTE) GPA NUMBER (3,1) PHONE VARCHAR2 (10 BYTE) STUDENT_PK (SID) DBS301.COURSE P* CRSNBR CHAR (6 BYTE) CNAME VARCHAR2 (30 BYTE) CREDIT NUMBER (2) MAXENRL NUMBER (3) IF FID CHAR (3 BYTE) COURSE_PK (CRSNBR) 2 COURSE_FACULTY_FK1 (FID) DY- DBS301.FACULTY * FID CHAR (3 BYTE) * FNAME VARCHAR2 (30 BYTE) EXT NUMBER (3) DEPT CHAR (3 BYTE) RANK CHAR (4 BYTE) SALARY NUMBER (7) JOIN_DATE DATE C FACULTY_PK (FID) SID (PK) SNAME (NN) SEX MAJOR GPA CRSNBR (PK,FK) FK) GRADE 987 763 218 359 862 748 506 581 372 126 400 POIRIER PARKER RICHARDS PELNICK FAGIN MEGLIN LEE GAMBRELL QUICK ANDERSON MARY F F M F M M M F. F M F MGT FIN ACC FIN MGT MGT FIN MKT MGT ACC 3.2 2.7 2.4 3.6 2.2 2.8 2.7 3.8 3.5 3.7 MGT630 FIN602 MKT610 FIN601 FIN602 ACC610 ACC610 ACC661 MGT630 MGT630 MGT681 MKT610 MKT610 MKT670 ACC610 MGT630 MGT681 FIN601 987 987 987 763 763 763 218 218 218 359 359 359 862 862 862 748 748 748 A B B B B A A F B A A A B B A FACULTY Table FID(PK) FNAME(NN) EXT DEPT RANK SALARY JOIN_DATE 036 117 098 BARGES JARDIN KENNEDY SAMPLE WARD PETERS DARDEN PIERCE 075 138 219 151 113 325 MGT 212 FIN 176 ACC 171 MKT 125 MGT 220 FIN 250 ACC 205 MGT ASSO FULL ASSO ASST INST FULL ASSO INST 35000 33000 30000 25000 20000 45000 37000 22000 01-Jan-2000 01-Jan-1980 01-Mar-2000 01-Apr-1998 01-Dec-2002 15-Jan-1976 20-Jun-1999 15-Dec-2000 CRSNBR FID COURSE Table CRSNBRO PK) CNAME(NN,UN) CREDIT MAXENRL FID(FK, NN) 4 4 30 25 35 138 117 075 098 MGT630 FIN601 MKT610 ACC661 FIN602 ACC610 MGT681 MKT670 INTRO TO MANAGEMENT MANAGERIAL FINANCE MKTING FOR MANAGERS TAXATION INVESTMENT SKILLS BASIC ACCOUNTING INTERNATIONAL MGT PRODUCT MARKETING 3 3 3 4 3 3 25 25 20 20 219 098 036 075 DBS301. STUDENT DBS301. STUD_COURSE PF* CRSNBR CHAR (6 BYTE) PF* SID NUMBER (3) GRADE CHAR (1 BYTE) STU_CRS_SID_PK (CRSNBR, SID) STU_CRS_FK (CRSNBR) STU_SID_FK (SID) STU_CRS_SID_PK (CRSNBR, SID) P * SID NUMBER (3) SNAME VARCHAR2 (20 BYTE) SEX CHAR (1 BYTE) MAJOR CHAR (3 BYTE) GPA NUMBER (3,1) PHONE VARCHAR2 (10 BYTE) STUDENT_PK (SID) DBS301.COURSE P* CRSNBR CHAR (6 BYTE) CNAME VARCHAR2 (30 BYTE) CREDIT NUMBER (2) MAXENRL NUMBER (3) IF FID CHAR (3 BYTE) COURSE_PK (CRSNBR) 2 COURSE_FACULTY_FK1 (FID) DY- DBS301.FACULTY * FID CHAR (3 BYTE) * FNAME VARCHAR2 (30 BYTE) EXT NUMBER (3) DEPT CHAR (3 BYTE) RANK CHAR (4 BYTE) SALARY NUMBER (7) JOIN_DATE DATE C FACULTY_PK (FID)

Answer & Explanation Solved by verified expert

See Answer