/* ------------------------------------------------------------------------ */
/* Do not edit this part ---------------------------------------------------*/
/* ------------------------------------------------------------------------ */
:- consult('emp.pl').
:- consult('query.pl').

%Database emp:
%emp(Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno)
%dept(Deptno, Dname, Loc)
%salgrade(Grade, Losal, Hisal)

/* ------------------------------------------------------------------------ */
/* Example -----------------------------------------------------------------*/
/* ------------------------------------------------------------------------ */

/* Print all jobs. */
job(J) :-
  emp(_, _, J, _, _, _, _, _).

/* To run this query, use (in another window, after saving this file):
'swipl -s queries_emp.pl'
and then (Prolog prompt):
make.
?- q(job(J)). 
*/

/* ------------------------------------------------------------------------ */
/* Write and test the following queries ----------------------------------- */
/* ------------------------------------------------------------------------ */

/* Print names and jobs of employees with salary at least 2000. */
ans1(N,J) :- emp(_,N,J,_,_,S,_,_), S>=2000.

/* Print names and jobs of employees who work in department 30. */
ans2(N,J) :- emp(_,N,J,_,_,_,_,D), D=30.

/* Print the number of department in which the president works. */
ans3(D) :- emp(_,_,J,_,_,_,_,D), J=president.

/* Print names of employees who were hired between 1 September 1981 and 31 October 1981. */
ans4(N) :- emp(_,N,_,_,H,_,_,_), H>=19810901, H=<19811031 .

/* Print names and salaries of managers. */
ans5(N,S) :- emp(_,N,J,_,_,S,_,_), J=manager.

/* Print names, brutto incomes, national insurance contributions, income taxes and netto incomes of employees (subtract 13.4% for national insurance and 19% for income tax). */
ans6(N,S,I,T,L) :- emp(_,N,_,_,_,S,_,_), I is S*0.134, T is S*0.19, L is S-I-T.

/* Print name and "total salary" (total salary = salary + comm) of each employee. (Warning: the column comm may contain NULL values.) */
ans7(N,T) :- emp(_,N,_,_,_,S,C,_), \+ C=null, T is S+C.
ans7(N,T) :- emp(_,N,_,_,_,S,C,_), C=null, T is S.

/* Print jobs of employees who work in Chicago. */
ans8(J) :- emp(_,_,J,_,_,_,_,D), dept(D,_,C), C=chicago.

/* Print tuples [Name, City, Coworker] which stand for all employees, their working places and names of their co-workers (employees who work in the same department). */
ans9(N,C,W) :- emp(_,N,_,_,_,_,_,D), emp(_,W,_,_,_,_,_,D), dept(D,_,C), \+ N=W.

/* Print names, department names and salaries of all employees whose salaries are greater than the lowest salary in department 20. */
exlower(X) :- emp(_,_,_,_,_,X,_,_), emp(_,_,_,_,_,S,_,_), S<X.
minsal(X) :- emp(_,_,_,_,_,X,_,D), D=20, \+ exlower(X).
ans10(N,D,S) :- emp(_,N,_,_,_,S,_,P), dept(P,_,D), minsal(X), S>X.

/* Print names of employees together with names of their managers. */
ans11(N,M) :- emp(_,N,_,Mgr,_,_,_,_), emp(Mgr,M,_,_,_,_,_,_).

/* Which departments contain all job positions? */
contains(J,D) :- emp(_,J,_,_,_,_,_,D).
missed(D) :- emp(_,J,_,_,_,_,_,_), \+ contains(J,D).
ans12(D) :- emp(_,_,_,_,_,_,_,D), \+ missed(D).

/* Which departments are empty (have no employees)? */
has_emp(D) :- emp(_,_,_,_,_,_,_,D).
ans13(D) :- dept(D,_,_), \+ has_emp(D).

/* Which employees manage only clerks? */
ans14(N) :- emp(_,_,_,Mgr,_,_,_,_), emp(Mgr,N,_,_,_,_,_,_), \+ mgr_of_sbelse(Mgr).
mgr_of_sbelse(Mgr) :- emp(_,_,J,Mgr,_,_,_,_), \+ J=clerk.

/* Which departments employ no salesmen? */
empsalesmen(D) :- emp(_,_,J,_,_,_,_,D), J=salesman.
ans15(D) :- dept(D,_,_), \+ empsalesmen(D).

/* Find names of all employees who are subsidiaries of Blake - both direct and indirect subsidiaries. */
ans16(N) :- emp(_,N,_,ID,_,_,_,_), emp(ID,blake,_,_,_,_,_,_).
ans16(N) :- emp(_,N,_,ID,_,_,_,_), emp(ID,M,_,_,_,_,_,_), ans16(M).