CODEWITHSUNDEEP
sqloracle-databaseoracle11g
lukechambers91
lukechambers91

Reputation: 711

Selecting data across mulitple tables in Oracle SQL

I am new to SQL and I am trying to lists all organizations by name in ascending order with the maximum salary across all employees who belong to the organization. I have the Schema Below. Can anyone offer any help? I am also trying to lists all employees by name and if the employee is a manager of at least one organization, provide a row in the results with the name of each org he/she manages else null.

SELECT Org.name, MAX(Employee.salary) AS "Highest salary"
From  Org, Employee
GROUP BY Org.name;

The above code gets me a list of all the Org Names with the global max salary, but I am looking for the max of each Org. I think I may need to use some joins but I am not very familiar.

CREATE TABLE Employee
(
  employeeId numeric(9) not null,
  name varchar2(100) unique,
  salary numeric(9) not null,
  CONSTRAINT employeeId_pk PRIMARY KEY (employeeId)
);

CREATE TABLE Org
(
  orgId numeric(9) not null,
  name varchar2(100) not null unique,
  managerId numeric(9) not null,
  CONSTRAINT orgId_pk PRIMARY KEY (orgId),
  CONSTRAINT managerId_fk FOREIGN KEY (managerId)
    REFERENCES Employee(employeeId)
);

CREATE TABLE EmployeeOrg
(
  employeeId numeric(9) not null,
  orgId numeric(9) not null,
  CONSTRAINT employeeId_orgId_pk PRIMARY KEY (employeeId, orgId),
  CONSTRAINT employeeId_fk FOREIGN KEY (employeeId)
    REFERENCES Employee(employeeId),
  CONSTRAINT orgId_fk FOREIGN KEY (orgId)
    REFERENCES Org(orgId)
);

INSERT ALL
INTO Employee (employeeId, name, salary) VALUES (123, 'Jim', 123)
INTO Employee (employeeId, name, salary) VALUES (456, 'Bill', 1456)
INTO Employee (employeeId, name, salary) VALUES (789, 'Frank', 456)
INTO Employee (employeeId, name, salary) VALUES (987, 'Sara', 45668)
INTO Employee (employeeId, name, salary) VALUES (654, 'Liz', 4456)
INTO Employee (employeeId, name, salary) VALUES (321, 'Morgan', 4556)
SELECT * FROM dual;

INSERT ALL
INTO Org (orgId, name, managerId) VALUES (1, 'Sales', 123)
INTO Org (orgId, name, managerId) VALUES (2, 'HR', 789)
INTO Org (orgId, name, managerId) VALUES (3, 'E Suite', 987)
INTO Org (orgId, name, managerId) VALUES (4, 'Marketing', 654)
SELECT * FROM dual;

INSERT ALL
INTO EmployeeOrg (employeeId, orgId) VALUES (123, 1)
INTO EmployeeOrg (employeeId, orgId) VALUES (789, 2)
INTO EmployeeOrg (employeeId, orgId) VALUES (987, 3)
INTO EmployeeOrg (employeeId, orgId) VALUES (654, 4)
INTO EmployeeOrg (employeeId, orgId) VALUES (123, 4)
INTO EmployeeOrg (employeeId, orgId) VALUES (456, 1)
INTO EmployeeOrg (employeeId, orgId) VALUES (321, 2)
INTO EmployeeOrg (employeeId, orgId) VALUES (789, 4)
INTO EmployeeOrg (employeeId, orgId) VALUES (456, 2)
SELECT * FROM dual;

EDIT (from OP's comment)

The below code gets me a list of all the Org Names with the global max salary, but I am looking for the max of each Org. I think I may need to use some joins but I am not very familiar.

SELECT Org.name, MAX(Employee.salary) AS "Highest salary"
  From Org, Employee
  GROUP BY Org.name

Upvotes: 0

Views: 53

Answers (1)

shawnt00
shawnt00

Reputation: 17915

select o.orgId, min(o.name) as orgName, max(e.salary) as maxSalary
from Org o
    inner join EmployeeOrg eo on eo.orgId = o.orgId
    inner join Employee e on e.employeeId = eo.employeeId
group by o.orgId
order by orgName;

select e.name as empName, o.name as orgName
from Employee e left outer join Org o on o.managerId = e.employeeId
order by empName, orgName;

Upvotes: 1

Related Questions