Subquery Exercises

These are the exercises you have in your laboratory. Answers are provided.

1.) Display office city as well as number of employees assigned whose city is not the same with employee Martin Gerard and whose count of employees is not equal to the number of employees assigned to NYC office.(2 rows)

Answer: 

SELECT city,count(employeeNumber)

from employees as e, offices as o

where e.officeCode=o.officeCode

and city != (Select city from offices as o,employees as e

where e.officeCode=o.officeCode and e.lastName='Gerard' and e.firstName='Martin')

group by city

having count(employeeNumber) != (Select count(employeeNumber)

from offices as o,employees as e

where o.officeCode=e.officeCode and city='NYC'

group by city);

2.)Display the customerName and creditLimit of customers whose creditLimit is between

The highest and the lowest creditLimit of customers taken by salesRepEmployeeNumber 1166.(76 rows)

Answer: 

Select customerName,creditLimit

from customers

where creditLimit < (Select max(creditLimit)

from customers as c, employees as e

where c.salesRepEmployeeNumber=e.employeeNumber

and salesRepEmployeeNumber=1166)

and creditLimit > (Select min(creditLimit)

from customers as c, employees as e

where c.salesRepEmployeeNumber=e.employeeNumber

and salesRepEmployeeNumber=1166);