-- -------------------------------------------------- --
-- Lab Example: People, Departments, and Fiscal Years --
-- -------------------------------------------------- --

--
-- Departments
--
drop table Departments;
create table Departments(
  did         char(10)  not null,
  name        text      not null,
  dateFounded date,
 primary key(did)
);

insert into Departments(did, name, dateFounded) values('A', 'CS', '2010-06-30');
insert into Departments(did, name, dateFounded) values('B', 'IT', '2013-01-01');
insert into Departments(did, name, dateFounded) values('C', 'IS', '2014-07-04');

select *
from Departments;


--
-- People
--
drop table People;
create table People(
  pid       int      not null,
  firstName text     not null,
  lastName  text     not null,
  hireDate  date     not null,
  did       char(10) not null references Departments(did),
 primary key(pid)
);

insert into People(pid, firstName, lastName, hireDate, did) values(1, 'Jim',    'Kirk',   '2008-01-23', 'A');
insert into People(pid, firstName, lastName, hireDate, did) values(2, 'Mister', 'Spock',  '2006-09-28', 'A');
insert into People(pid, firstName, lastName, hireDate, did) values(3, 'Pavel',  'Chekov', '2012-04-04', 'B');
insert into People(pid, firstName, lastName, hireDate, did) values(4, 'Nyota',  'Uhura',  '2013-11-11', 'B');
insert into People(pid, firstName, lastName, hireDate, did) values(5, 'Hikaru', 'Sulu',   '2014-11-11', 'B');

select *
from People;


--
-- Fiscal Years
--
drop table FiscalYears;
create table FiscalYears(
  year      int  not null,
  startDate date not null unique,
  endDate   date not null unique check(endDate > startDate),
 primary key(year)
);

insert into FiscalYears(year, startDate, endDate) values(2012, '2011-07-01', '2012-06-30');
insert into FiscalYears(year, startDate, endDate) values(2013, '2012-07-01', '2013-06-30');
insert into FiscalYears(year, startDate, endDate) values(2014, '2013-07-01', '2014-06-30');
insert into FiscalYears(year, startDate, endDate) values(2015, '2014-07-01', '2015-06-30');

select *
from FiscalYears;


--
-- Queries
--

-- List all people, the date they were hired, and their department.
select p.firstName,
       p.lastName,
       p.hireDate,
       d.name as "Department"
from People p,
     Departments d
where d.did = p.did

-- List all people, the fiscal year in which they were hired, and their department.
select p.firstName,
       p.lastName,
       fy.year as "Fiscal Year Hired",
       d.name as "Department"
from People p,
     Departments d,
     FiscalYears fy
where d.did = p.did
  and p.hireDate >= fy.startDate
  and p.hireDate <= fy.endDate
order by "Fiscal Year Hired" ASC;

-- List the department names and the number of people in each.
select d.name,
       count(pid) as "people in department"
from Departments d left outer join People p on d.did = p.did
group by d.name
order by d.name;

-- List all the fiscal years and the number of people hired in that time span.
select fy.year, count(pid)
from FiscalYears fy,
     People p
where p.hireDate >= fy.StartDate
  and p.hireDate <= fy.EndDate
group by fy.year
order by fy.year ASC;

-- List all the fiscal years and the number of departments founded in that time span.
select fy.year, count(did)
from FiscalYears fy,
     Departments d
where d.dateFounded >= fy.StartDate
  and d.dateFounded <= fy.EndDate
group by fy.year
order by fy.year asc;

-- List all department names with no people.
select d.*
from Departments d left outer join People P on d.did = p.did
where p.pid is null;

-- List all people with no department.
select p.*
from People p
where p.did not in (select did
                    from Departments);

-- List all people hired before their department was founded.
select p.*
from People p,
     Departments d
where p.did = d.did
  and p.hireDate < d.dateFounded;