IT Employment

Ask something
Question Locked

I need help with oracle please

0 helpful
2 replies

by alex789

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello all,
I want to output the physican's name and the physician's salary for the following table. however I need output this information where physician's salary is less than his/her supervisor's salary

/* Physician Table */

CREATE TABLE physician(
phys_Ref_no NUMBER(3) NOT NULL,
phys_name VARCHAR2(6),
phys_salary NUMBER(7,2),
phys_supervisor_no NUMBER(3),
PRIMARY KEY (phys_Ref_no));

/* Insert Values */
INSERT INTO physician VALUES
(101,'Smith',30000.00,108);
INSERT INTO physician VALUES
(102,'Jones',37463.00,108);
INSERT INTO physician VALUES
(103,'Patel',41569.00,106);
INSERT INTO physician VALUES
(104,'Hinks',22482.00,105);
INSERT INTO physician VALUES
(105,'Moon',29750.00,106);
INSERT INTO physician VALUES
(106,'Clark',45122,103);
INSERT INTO physician VALUES
(107,'Vine',18980.00,101);
INSERT INTO physician VALUES
(108,'Sadri',46440.00,106);
INSERT INTO physician VALUES
(109,'Young',19533.00,107);

Thank you very much.

2 replies

by bng

Hi there,

this one will do it nicely :

select a.phys_ref_no, a.phys_name, a.phys_salary
from physician a, physician b
where a.phys_supervisor_no = b.phys_ref_no
and a.phys_salary < b.phys_salary;

Of course You might want put some more fancy headers on the columns, include the name of the supervisor etc. but the basic idea here is to do a self-join.

Regards
Bjornar Nying

This discussion is closed to new replies.