Question

1 to this PC Picture Tools ew Help Acrobat Storyboarding FormatTell me what you want to de Picture Bonde A Bring Send Selection Tent Pictu楩Layout . | | Forward. Backward . Pane Figure 3.6 One possibie database state for the Figure 3.6 One possible database state for the COMPANY relational database schema EMPLOYEE Fname Minit Lname Ssn Bdate Sex Salary Super ssn Do Address John B Smih 123456780 1985 01.00 731 Fondren Houton TX M 30000 34558 Fondren, Houston, TX M 30000 333445555 5 Franklin T Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 888665555 5 Alicia Zelaya 999887777 1968-01-19 3321 Castle Jennifer S Wallace 987654321 1941-06-20 291 Bery. Beaire, TX F 43000 888665555 Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 5 Joyce A English 453453453 1972-07 31 5631 Rice, Houston, TX F 25000 333445555 5 Spring TX F 25000 987654321 Ahn adl v 19690329| 980 Dallas, Houston TX |M ↑25000|987664321| 4 Jabbar 987987987 James E Borg 888665555 1937-11-10 450 Stone. Houston, TXM 55000 NULL DEPARTMENT DEPT LOCATIONS Dname Research Administration Headquarters Dnumber 33445555 987654321 888665555 Mgr start date 1988-06-22 1995-01-01 981-06-19 Houston Stafford Sugarland Houston 72 rs an einent department numbers of the company. It also defines a set of vald department runters that the elumnDn0offMOL。EE can take to be consistent with vailues of Dnsmbet in DEPARTMENT relation, by condistent we mean for each Ono value in EMPLOVEE, we should fed a corresponding t

Oracle SQL Assignment 4

- List the name of supervisor who has more supervisee than other supervisors.

The supervisor would be the Mgr_ssn from the Department table. Each employee from the Employee table has a Super_ssn which would be their supervisor.

Answer 1

NOTE: I have completed answers for your question along with explanation. Please check and let me know if you have any questions. I will revert back within 24 hours. Thanks for your patience.

Query:
select
Fname
from
(
select
super_ssn
from
employee
group by
super_ssn
order by
count(*) desc
limit 1
) t1
inner join
t1.super_ssn = employee.ssn;

Explanation:
1) The inner query retrieves the super_ssn of employee which occurred most number of times. This way we are trying to find the super_ssn which is available for most of the employees.
2) After the above step we will have the super_ssn which occurred most of times.
3) We have given alias to the inner query as t1 and then did inner join on super_ssn column of inner table and the actual table employee column's ssn.
4) The result is employee who has highest number of supervisors.