CODEWITHSUNDEEP
sql
Trance339
Trance339

Reputation: 307

SQL queries for employee database

I am new to SQL and I am taking a DB class this semester. I have about 20 queries to try on this database and there are two of them that I cannot figure out... I was wondering if someone here would be able to help!

Here are my tables I created already.

create table employee(
  EID varchar(20) primary key,
  Lastname varchar(20),
  Firstname varchar(20),
  Midinitial char(1),
  gender char(1),
  street varchar(20),
  city varchar(20)
);

create table works(
  EID varchar(20) primary key,
  Lastname varchar(20),
  Firstname varchar(20),
  Midinitial char(1),
  company_name varchar(20),
  salary numeric(5,0),
  foreign key(EID) references employee,
  foreign key(company_name) references company
);

create table company(
  company_name varchar(20) primary key,
  city varchar(20)
);

create table manages(
  EID varchar(20) primary key,
  Lastname varchar(20),
  Firstname varchar(20),
  Midinitial char(1),
  ManagerLastname varchar(20),
  MFirstname varchar(20),
  MMidinitial varchar(20),
  start_date date,
  foreign key (EID) references employee
);

Find the salaries of all managers who work for First Bank and live in Omaha.

Here is what I have for this one I think its correct but I dont want to answer this until I can get the second one too....

select distinct salary
from employee, works, manages
where employee.city = 'Omaha' 
  and works.company_name = 'First Bank' 
  and manages.managerlastname = employee.Lastname
  and employee.EID = works.EID

and

Find the name and the company name of the employee who has the highest salary of all employees.

Upvotes: 3

Views: 12862

Answers (3)

Trance339
Trance339

Reputation: 307

Both of these returned exactly what I needed from the info in my tables. Thanks for the help!

Query 1

select distinct salary
from employee, works, manages
where employee.city = 'Omaha' 
  and works.company_name = 'First Bank' 
  and manages.managerlastname = employee.Lastname
  and employee.EID = works.EID

Query 2

select firstname, lastname, company_name
from works w1, (select max(salary) as max_salary
            from works) w2
where w1.salary = w2.max_salary

Upvotes: 0

Sam Grondahl
Sam Grondahl

Reputation: 2477

I'm pretty sure it should be:

SELECT w.salary FROM works w INNER JOIN employee e ON w.EID = e.EID
 WHERE e.city = 'Omaha' AND w.company_name = 'First Bank' AND EXISTS
 ( SELECT * FROM managers m WHERE m.EID = w.EID);

And for the second:

SELECT e.Firstname, e.Lastname, w.company_name FROM employee e INNER JOIN
  works w ON e.EID = w.EID ORDER BY w.salary DESC LIMIT 1;

Upvotes: 2

hjpotter92
hjpotter92

Reputation: 80653

SELECT w.salary
FROM works w
INNER JOIN employee e
    ON e.EID = w.EID
        AND e.city = 'Omaha'
WHERE w.company_name = 'First Bank';

That ought to do it.

Upvotes: 1

Related Questions