Create Table Departments (
    did number(3) primary key,
    dname varchar(30) not null,
    managerId number(4)
);

Create Table Emps (
    eid number(4) primary key,
    firstname varchar(30),
    lastname varchar(30) not null,
    salary number(9, 2) check (salary > 0),
    workdept number(3) references Departments,
    hiredate date
);

Alter Table Departments
    modify managerId number(4) references Emps;

Create Table Projects (
    pno number(5) primary key,
    title varchar(80) not null,
    respEid number(4) references Emps,
    respDid number(3) references Departments
);

Create Table WorksOn (
    eid number(4) references Emps,
    pno number(5) references Projects,
    sdate date,
    edate date,
    primary key (eid, pno, sdate),
    check (edate >= sdate)
);

Insert into Departments (did, dname) Values (101, 'Executives');
Insert into Departments (did, dname) Values (102, 'IT');
Insert into Departments (did, dname) Values (103, 'Marketing');

Insert into Emps Values (1001, 'Anne', 'Apland', 75000, 101,
        to_date('2008/09/10', 'yyyy/mm/dd'));
Insert into Emps Values (1002, 'Ben', 'Bentan', 75000, 102,
        to_date('2010/09/10', 'yyyy/mm/dd'));
Insert into Emps Values (1003, 'Cathy', 'Carren', 65000, 103,
        to_date('2008/11/12', 'yyyy/mm/dd'));
Insert into Emps Values (1004, 'Dave', 'Denn', 70000, 101,
        to_date('2009/03/20', 'yyyy/mm/dd'));
Insert into Emps Values (1005, 'Ethan', 'Edin', 60000, 102,
        to_date('2012/04/15', 'yyyy/mm/dd'));
Insert into Emps Values (1006, 'Faith', 'Fern', 50000, 103,
        to_date('2013/07/01', 'yyyy/mm/dd'));

Update Departments set managerId = 1001 where did = 101;
Update Departments set managerId = 1002 where did = 102;
Update Departments set managerId = 1003 where did = 103;

Insert into Projects values (10001, 'what ever project', 1002, 103);
Insert into Projects values (10002, 'development planning', 1001, 101);
Insert into Projects values (10003, 'not so important project', 1002, 102);
Insert into Projects values (10004, 'important project', 1003, 103);

Insert into WorksOn values (1001, 10001,
   to_date('2015/03/01', 'yyyy/mm/dd'), to_date('2016/04/30', 'yyyy/mm/dd'));
Insert into WorksOn values (1002, 10001,
   to_date('2016/01/01', 'yyyy/mm/dd'), to_date('2016/09/30', 'yyyy/mm/dd'));
Insert into WorksOn values (1003, 10003,
   to_date('2015/03/01', 'yyyy/mm/dd'), to_date('2016/04/30', 'yyyy/mm/dd'));
Insert into WorksOn values (1004, 10002,
   to_date('2015/03/01', 'yyyy/mm/dd'), to_date('2016/04/30', 'yyyy/mm/dd'));
Insert into WorksOn values (1001, 10003,
   to_date('2015/03/01', 'yyyy/mm/dd'), to_date('2016/04/30', 'yyyy/mm/dd'));
Insert into WorksOn values (1002, 10003,
   to_date('2015/03/01', 'yyyy/mm/dd'), to_date('2016/04/30', 'yyyy/mm/dd'));

-- The following insert query should be rejected by Oracle
-- because the edate is earlier than the sdate
Insert into WorksOn values (1006, 10003,
   to_date('2017/03/01', 'yyyy/mm/dd'), to_date('2016/04/30', 'yyyy/mm/dd'));

Select firstname, lastname, hiredate
From Emps
Where salary >= 60000;

Select sysdate From Dual;

-- function in select list and renaming the column
Select to_char(sysdate, 'Month dd, yyyy, Day, hh:mm:ss') as currentDate
From Dual;

-- Oracle's to_char and to_date functions
-- and string concatenation
Select lastname || ', ' || firstname as name, salary,
       to_char(hiredate, 'Mon dd, yyyy, Day') as hiredate
From Emps
Where hiredate >= to_date('2009-07-01', 'yyyy-mm-dd')
  and salary > 50000;

-- try the following query with and without distinct respectively
Select distinct eid
From WorksOn;

-- cross product
Select eid, workdept, did, dname
From Emps, Departments;

-- join condition expressed as where condition
Select eid, workdept, did, dname
From Emps, Departments
Where workdept = did;

-- join condition expressed directly in from list
Select eid, workdept, did, dname
From Emps join Departments on workdept = did;

-- rename relation and multiple table variables of the same table
Select E1.lastname, D.dname as DeptName, E2.lastname as managerName
From Emps E1, Departments D, Emps E2
Where E1.workdept = D.did and D.managerId = E2.eid
  and E1.hiredate >= to_date('2009/Jul/01', 'yyyy/Mon/dd');

Select E1.lastname, dname as DeptName, E2.lastname as managerName
From Emps E1 join Departments on workdept = did
     join Emps E2 on managerId = E2.eid
Where E1.hiredate >= to_date('2009/Jul/01', 'yyyy/Mon/dd');

-- natural join example
select firstname, lastname, title
from emps natural join workson natural join projects;

select did
from departments
where dname = 'I';

select did
from departments
where dname like 'I_';

-- full sub-query in from list
Select firstname || ' ' || lastname as name
From (select did From Departments Where dname like '%t%'), Emps
Where workdept = did;

Select firstname || ' ' || lastname as name
From (select did as workdept From Departments Where dname like '%t%')
     natural join Emps;

select D.did
from departments D join emps E on D.did = E.workdept;