Homework4 solution

Author

Dr. Bartosiak

Coding and Database Managment

SQL - Homework 4 - solution

Warning

You have to run these queries in Workbench (or Python program).

The following queries use the classicmodels schema.

Who is at the top of organization?

SELECT  DISTINCT boss.employeeNumber, boss.lastName, boss.firstName
 FROM employees as emp JOIN employees boss
  ON emp.reportsTo = boss.employeeNumber
 WHERE boss.reportsTo IS NULL;

Who reports to directly to William Patterson?

SELECT emp.lastName, emp.firstName 
 FROM employees as emp JOIN employees boss
  ON emp.reportsTo = boss.employeeNumber
 WHERE boss.lastName = 'Patterson'
 AND boss.firstName = 'William';

List the employees who report to those employees who report to Diane Murphy.

SELECT emp.lastName, emp.firstName , emp.employeeNumber, emp.reportsTo
 FROM employees as emp JOIN employees as boss JOIN employees as bigboss
  ON emp.reportsTo = boss.employeeNumber
  AND boss.reportsTo = bigboss.employeeNumber
 WHERE bigboss.lastName = 'Murphy'
 AND bigboss.firstName = 'Diane';

How many employees report to the Sales Manager that is employee named ‘Bow Anthony’? Rename the column as “Mr. Bow’s Direct Reports”

SELECT  count(emp.reportsTo) AS 'Mr. Bows Direct Reports'
 FROM employees as emp JOIN employees boss
  ON emp.reportsTo = boss.employeeNumber
 WHERE boss.employeeNumber IN (SELECT employeeNumber 
                                FROM employees 
                                WHERE firstName= 'Anthony' AND lastName= 'Bow');

Find the pair of customer that have the same credit limit. Omit the customers having no credit limit (That is creditlimit = 0): Report the customer name and credit limit for each pair and be sure to avoid unnecessary duplication.

SELECT a.customerName, b.customerName, a.creditLimit, b.creditLimit
 FROM customers AS a JOIN customers AS b
  ON a.customerName> b.customerName
 WHERE a.creditLimit = b.creditLimit
 AND b.creditLimit !=0;

Enabled by Dr. Marcin Bartosiak