This is database system concepts employeepersonname street c

This is database system concepts

employee(person_name, street, city)

works(person_name, company_name, salary)

company(company_name, city)

manages(person_name, manager_name)

1.Find the names of all employees who live in the city \"Brooklyn\".

2.Find the names and cities of residence of all employees who earn a salary of at least $50,000.

3.Find the names of all employees who work for the company \"Fedco\" and earn a salary of at least $50,000.

4.Find the names and cities of residence of all employees who work for a company located in the city \"Brooklyn\".

5.Find the number of employees managed by each manager.

6.Find the names of all employees who earn more than every employee of \"Small Bank Corporation\"

Solution

1. Select person_name from employee where city = \'Brooklyn\';

2. Select distinct e.person_name,e.city from employee e inner join works w on e.person_name = w.person_name where w.salary >=50000;

3. Select e.person_name from employee e inner join works w on e.person_name = w.person_name inner join company c on w.company_name = c.company_name where c.company_name = \'Fedco\' and w.salary >=50000;

4. Select e. person_name,e.city from employee e inner join works w on e.person_name = w.person_name inner join company c on w.company_name = c.company_name where c.city = \'Brooklyn\';

5. Select count(person_name) from manages group by manager_name;

6. Select w1.person_name from works w1 inner join works w2 on w1.person_name = w2.person_name where w1.salary > w2.salary and w2.company_name =\'Small Bank Corporation\';