% SQL ---------------------------------------------
 
% ----- query 1 -----;
% Retrieve the name and address of all employees who work for the 
% 'Research' department.

sql1 := select fname,lname,address
        from employee, department
        where dname = 'Research' and
              dnumber = dno;

% ----- query 2 -----;
% For every project located in 'Stafford', list the project number, the
% controlling department number, and the department manager's last name,
% address, and birthdate.

sql2 := select pnumber,dnum,lname,address,bdate
        from projects,department,employee
        where dnum = dnumber and
              mgrssn = ssn and
              plocation = 'Stafford';

% ----- query 3 -----;
% Find the names of employees who work on all the projects controlled
% by department number 5.

sql3 := select lname,fname 
        from employee E
        where not exists (select *
                          from projects P
                          where P.dnum = 5 and not exists (select *
                                                           from works_on W
                                                           where W.essn = E.ssn and
                                                           W.pno = P.pnumber));
                                                           

% ----- query 4 -----;
% Make a list of project numbers for projects that involve an employee
% whose last name is 'Smith', either as a worker or as a manager of the
% department that controls the project.

sql4 := (select pnumber 
         from   projects, department, employee
         where  dnum = dnumber and
                mgrssn = ssn and
                lname = 'Smith')
        union
        (select pnumber
         from   projects,works_on, employee
         where  pnumber = pno and
                essn = ssn and
                lname = 'Smith');

% ----- query 5 -----;
% List the names of all employees with two or more dependents.

dep_num(ssn,depent) := select ssn, count(*)
                       from employee,dependent
                       where ssn = essn
                       group by ssn;

sql5 := select lname,fname 
        from   employee e,dep_num d
        where  e.ssn = d.ssn and
               depent >= 2;

% ----- query 6 -----;
% Retrieve the names of employees who have no dependents.

sql6 := select lname,fname
        from   employee
        where  not exists (select *
                           from dependent
                           where ssn = essn);

% ----- query 7 -----;
% List the names of managers who have at least one dependent.

sql7 := select lname,fname 
        from   employee
        where  exists (select *
                       from   dependent
                       where  ssn = essn) and
               exists (select *
                       from   department
                       where  ssn = mgrssn);
