dbms.txt

/* employee name ad job title name */

SELECT e.FIRST_NAME, j.JOB_ID

FROM employees as e

JOIN 

jobs as j

ON e.JOB_ID = j.JOB_ID


/* employee name and job title name and how much less salary than the max slary */

SELECT e.FIRST_NAME, j.JOB_TITLE, j.MAX_SALARY- e.SALARY as sal_diff

FROM employees as e

JOIN 

jobs as j

ON e.JOB_ID = j.JOB_ID


/* region name, location,postal code */

SELECT l.POSTAL_CODE, r.REGION_NAME

FROM locations as l

JOIN

countries as c 
ON l.COUNTRY_ID = c.COUNTRY_ID

JOIN
regions as r 
ON r.REGION_ID=c.REGION_ID

/* employee name and manager name*/

SELECT emp.FIRST_NAME,man.FIRST_NAME

FROM employees as emp

JOIN employees as man

ON emp.MANAGER_ID = man.EMPLOYEE_ID

/* employee name and manager name, manager of manager name*/

SELECT emp.FIRST_NAME,man.FIRST_NAME,manman.FIRST_NAME

FROM employees as emp

JOIN employees as man

JOIN employees as manman

ON emp.MANAGER_ID = manman.EMPLOYEE_ID

/* employee name , his senior collegue id */

SELECT myself.EMPLOYEE_ID,myself.HIRE_DATE,colg.EMPLOYEE_ID,colg.HIRE_DATE

FROM employees as myself

JOIN

employees as colg

ON myself.HIRE_DATE>colg.HIRE_DATE

order by myself.EMPLOYEE_ID desc

/* employee id , number of senior employees than him */

SELECT myself.EMPLOYEE_ID, COUNT(colg.EMPLOYEE_ID)

FROM employees as myself

JOIN

employees as colg

ON myself.HIRE_DATE>colg.HIRE_DATE

GROUP by myself.EMPLOYEE_ID

order by myself.EMPLOYEE_ID desc

/* employee id not a member of dept no. 50/60/70 , number of senior employees than him */

SELECT myself.EMPLOYEE_ID, COUNT(colg.EMPLOYEE_ID)

FROM employees as myself

JOIN

employees as colg

ON myself.HIRE_DATE>colg.HIRE_DATE

WHERE myself.DEPARTMENT_ID NOT IN (50,60,70)

GROUP by myself.EMPLOYEE_ID

order by myself.EMPLOYEE_ID desc

/* employee id not a member of dept no. 50/60/70 , number of senior employees than him where there exists at least 100 senior employees */

SELECT myself.EMPLOYEE_ID, COUNT(colg.EMPLOYEE_ID) 

FROM employees as myself

JOIN

employees as colg

ON myself.HIRE_DATE>colg.HIRE_DATE

WHERE myself.DEPARTMENT_ID NOT IN (50,60,70)

GROUP by myself.EMPLOYEE_ID

HAVING COUNT(colg.EMPLOYEE_ID)>=100

order by myself.EMPLOYEE_ID desc

/* manager id, number of employees he manages */

SELECT MANAGER_ID, COUNT(EMPLOYEE_ID)

FROM employees 

GROUP by MANAGER_ID


/* main question: manager name, number of employees he manages */

/* employee name and manager name*/

SELECT man.FIRST_NAME, COUNT(emp.EMPLOYEE_ID)

FROM employees as emp

JOIN employees as man

ON emp.MANAGER_ID = man.EMPLOYEE_ID


GROUP BY man.EMPLOYEE_ID, man.FIRST_NAME

/* main question: manager name, number of employees receives higher salary than the manager */

/* employee name and manager name*/

SELECT man.FIRST_NAME, COUNT(emp.EMPLOYEE_ID)

FROM employees as emp

JOIN employees as man

ON emp.MANAGER_ID = man.EMPLOYEE_ID

where emp.SALARY > man.SALARY


GROUP BY man.EMPLOYEE_ID, man.FIRST_NAME