Hackbright Code Challenges

SQL Managers

SQL Managers

Challenge

Medium

Concepts

SQL, Joins, Self-Joins

Download

sql-managers.zip

Solution

SQL Managers: Solution


Imagine a company with the following organization chart:

digraph mgrs { rankdir=TB Jane -> Jessica -> { Al, Bob, Jen }; Jane -> Janet -> {Nick, Nora}; Nora -> {Henri}; }

(that is, Jane manages Jessica and Janet; Jessica manages Al, Bob, and Jen, etc.)

Create a database that could hold this information, so that you can keep track of the employee names and their managers. Each employee should have a primary key that is a unique, autoincrementing integer. Each employee can have (at most) one manager.

Your Tasks

  • Design the database

  • Write the CREATE TABLE statement(s) for your design

  • Write the INSERT statements to insert this data in

  • Write a query that shows each employee and their managers name (if applicable). Make sure it shows everyone, even people without managers.

  • Write a query that lists the names of people who do not manage anyone.

Hints

You should be able to do this with one table.

You may want to learn about “self-joins”.