/* Do not edit this part ---------------------------------------------------*/
/* ------------------------------------------------------------------------ */
:- consult('emp.pl').
:- consult('subtotal.pl').
:- consult('query.pl').
   
 %Database emp:
 %emp(Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno)
 %dept(Deptno, Dname, Loc)
 %salgrade(Grade, Losal, Hisal)
   
 /* ------------------------------------------------------------------------ */
 /* Example -----------------------------------------------------------------*/
 /* ------------------------------------------------------------------------ */
   
 /* 1. Find the number of employees who work in Dallas. */
   
 emp_dallas(E) :-
   emp(E, _, _, _, _, _, _, D),
   dept(D, _, dallas).
   
 nr_emp_dallas(N) :-
   subtotal(emp_dallas(E), [], [N = count(E)]).
   
 /* To run this query, use (in another window, after saving this file):
 'pl -s queries_emp.pl'
 and then (Prolog prompt):
 make.
 ?- q(nr_emp_dallas(N)).
 */
   
 /* ------------------------------------------------------------------------ */
 /* Write and test the following queries ----------------------------------- */
 /* ------------------------------------------------------------------------ */
   
 % 2. Find average salary of employees who work in Dallas.
 sal_dallas(I,S) :- emp(I,_,_,_,_,S,_,D), dept(D,_,dallas).
 avg_sal_dallas(A) :- subtotal(sal_dallas(_,S), [], [A = avg(S)]).
   
 % 3. For each department (including departments with no employees), find the sum of salaries of employees who work in that department.
 sal_per_dep(I,S,D) :- emp(I,_,_,_,_,S,_,DN), dept(DN,_,D).
 sal_per_dep(I,S,D) :- dept(DN,_,D), \+ ma_niekoho(DN), I is 0, S is 0.
 ma_niekoho(DN) :- emp(_,_,_,_,_,_,_,DN).
 sum_sal_dept(D,SS) :- subtotal(sal_per_dep(I,S,D), [D], [SS = sum(S)]).
         
 % 4. Find departments (deptno) with more than 3 employees.
 emp_per_dep(I,D) :- emp(I,_,_,_,_,_,_,D).
 dep_with_three(D) :- subtotal(emp_per_dep(I,D), [D], [S = count(I)]), S>3.
    
 % 5. For each department, find the number of analysts who work in that department (the result consists of tuples [D, N]).
 analysts(I,D) :- emp(I,_,analyst,_,_,_,_,D).
 count_analysts(D,N) :- subtotal(analysts(I,D), [D], [N = count(I)]).
    
 % 6. Find the job position(s) with the maximal standard deviation of salaries.
 job_sal(I,J,S) :- emp(I,_,J,_,_,S,_,_).
 job_dev(J,D) :- subtotal(job_sal(_,J,S), [J], [D = stdev(S)]).
 max_dev(M) :- subtotal(job_dev(J,D), [], [M = max(D)]).
 max_job(J,D) :- emp(_,_,J,_,_,_,_,_), job_dev(J,D), max_dev(D).
    
 % 7. Find tuples [Deptno, Job, Sum, Average] which for each [Deptno, Job] state the sum of salaries and average salary of employees who work in department Deptno and do job Job.
   
info(I,J,S,D) :- emp(I,_,J,_,_,S,_,D).
answer(D,J,Sum,Avg) :- subtotal(info(_,J,S,D), [D,J], [Sum=sum(S), Avg = avg(S)]).