CREATE TABLE `employee` (
  `username` varchar(45) NOT NULL,
  `password` varchar(45) DEFAULT NULL,
  `fullname` varchar(45) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `position` varchar(45) DEFAULT NULL,
  `retired` tinyint(1) DEFAULT NULL,
  `company` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`username`));

CREATE TABLE `client` (
  `longname` varchar(45) DEFAULT NULL,
  `shortname` varchar(45) DEFAULT NULL,
  `inn` double NOT NULL,
  `email` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`inn`));

CREATE TABLE `project` (
  `name` varchar(45) NOT NULL,
  `startdate` datetime DEFAULT NULL,
  `enddate` datetime DEFAULT NULL,
  `clientinn` double DEFAULT NULL,
  `companyname` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`name`));





CREATE TABLE `company` (
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`name`));

CREATE TABLE `task` (
  `id` int NOT NULL,
  `project` varchar(45) DEFAULT NULL,
  `endtime` datetime DEFAULT NULL,
  `suspended` tinyint(1) DEFAULT NULL,
  `employee` varchar(45) DEFAULT NULL,
  `eversuspended ` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`));

Exercise 2.
select username, sum(endtime - startdate) over (partition by username) from (select * from employee e inner join task t on t.employee = e.username inner join project p on t.project = p.name where e.company = 'GIVEN' and month(p.startdate) >= 12 and month(t.endtime) <= 12 and t.endtime <> NULL) sel;
Exercise 3.
select username, longname, sum(endtime - startdate) over (partition by username, inn) from (select * from employee e inner join task t on t.employee = e.username inner join project p on t.project = p.name inner join (select clnt.longname, clnt. inn from client clnt) clnt on clnt.inn = p.clientinn where e.company = 'GIVEN' and month(p.startdate) >= 12 and month(t.endtime) <= 12 and t.endtime <> NULL) s;

Exercise 5.
select * from employee e inner join company c on e.company = c.name where not exists (select * from project p where p.companyname = c.name and year(p.startdate) <= 2007 and year(p.enddate) >= 2007 and not exists (select * from task t where t.project = p.name and t.employee = e.username));
Exercise 6.
select p.clientname, p.name from project p where not exists(select * from task t where p.name = t.project and t.eversuspended = 0);