SQL Server Queries

Consider the following relational schemas:

• employee (person_name, street, city)
• company (company_name, city)
• works (person_name, company_name, salary)
• manages (person_name, manager_name)
• Write the following queries in SQL:
• Find the names, cities of employees who work in at least one company.
• Display the names of all employees who work in at least one company and the city of the company in an ascending order of employee names.
• Find the names, cities of employees who work for exactly one company.
• Find the names of all employees who earn more than EVERY employee of Small Bank Corporation. Find the names of all employees who earn more than SOME employee of Small Bank Corporation.

Q1) Find the names, cities of employees who work in at least one company.

select e.person_name, e.city from employee e where exists (select * from works w

where w.person_name = e.person_name)

Alternative solution:

select distinct e.person_name, e.city from employee e, works w where

w.person_name = e.person_name

Q2) Display the names of all employees who work in at least one company and the city of the company in an ascending order of employee names.

select distinct w.person_name, c.city from works w, company c

where c.company_name = w.company_name order by w.person_name asc

Q3) Find the names, cities of employees who work for exactly one company.

select e.person_name. e.city from employee e where 1 = (select count(*)

from works w where w.person_name = e.person_name)

Q4) Find the names of all employees who earn more than EVERY employee of Small Bank Corporation.

select w1.person_name from works w1 where w1.salary > all (select w2.salary

from works w2 where w2.company_name = “Small Bank Corporation”)

Alternative solution:

select w1.person_name from works w1 where w1.salary > select max

(w2.salary) from works w2 where w2.company_name = “Small Bank Corporation”)

Q5) Find the names of all employees who earn more than SOME employee of Small Bank Corporation.

select w1.person_name from works w1 where w1.salary > some (select w2.salary

(w2.salary) from works w2 where w2.company_name = “Small Bank Corporation”)

Alternative solution:

select w1.person_name from works w1where exists (select * from works w2

where w2.company_name = “Small Bank Corporation” and w1.salary > w2.salary)

Consider the following relational schemas:

• employee (person_name, street, city)
• company (company_name, city)
• works (person_name, company_name, salary)
• manages (person_name, manager_name)
• Write the following queries in SQL:
• Find the names of the employees who are not a manager.
• Find all cities where employees live or where companies are located.
• Find the name of the company located in Hong Kong that has the largest number of employees.
• Find the names of those companies whose employees earn a higher salary, on average, than the average salary at First Bank Corporation.
• Find the name

Q6) Find the names of the employees who are not a
manager.

(select person_name from employee) except (select manager_name

from manages)

Alternative solutions :

select person_name from employee where not exists (select *

from manages where employee.person_name = manages.manager_name)

select person_name from employee where person_name not in (select

manager_name from manages)

Q7) Find all cities where employees live or where
companies are located.

(select distinct city from employee) union (select distinct city

from company)

Q8) Find the name of the company located in Hong Kong that has the largest number of employees.

select temp.company_name from (select w.company_name, count(distinct

w.person_name) as CNT from works w, company c where w.company_name =

c.company_name and c.city = “Hong Kong” group by w.company_name) as

temp where temp.CNT = (select max(CNT) from temp)

Q9) Find the names of those companies whose employees
earn a higher salary, on average, than the average salary at First Bank Corporation.

select company_name from works group by company_name having avg(salary)

> (select avg(salary) from works where company_name = “First Bank Corporation”)

Q10) Find the name of the company that has the
smallest payroll.

select company_name from works group by company_name having sum

(salary) <= all (select sum(salary) from works group by company_name)

Alternative solution:

select temp.company_name from (select company_name, sum(salary) as

payroll from works group by company_name) as temp where temp.payroll =

(select min (payroll) from temp)

Retrieve the tuple with the largest value in an attribute
Using the aggregate function max

Consider the following relational schema:
account(account_id, balance)

q Write an SQL query to retrieve the ids of those accounts that have the second largest balance.

Example:
1.(a1, 100), (a2, 400), (a3, 500), (a4, 300), (a5, 200), (a6, 400), (a7,100), (a8, 100),

(a9, 500), (a10, 200)

Answer 1:

select a1.account_id from account a1 where a1.balance <> (select max(balance)

from account) and not exists (select * from account a2 where a1.balance <>

a2.balance <> (select max(balance) from account))

Answer 2:

select account_id from account where balance <> (select max(balance) from

account) and balance not in (select distinct a1.balance from account a1, account

a2, account a3 where a1.balance <> a3.balance)

Answer 3:

select account_id from account where balance = (select max(balance) from

account where balance <> (select max(balance) from account))

Answer 4:

select a1.account_id from account a1, account a2 where a1.balance <>

a1.account_id having count(distinct a2.balance) = 1