Task - Aggregate Functions - hr database

[akash-coded]

Database = hr

Dump file: hr.sql

DB Schema

Queries

1. Write a query to list the number of jobs available in the employees table.

2. Write a query to get the total salaries payable to employees.

3. Write a query to get the minimum salary from the employees table.

4. Write a query to get the maximum salary of an employee working as a Programmer.

5. Write a query to get the average salary and number of employees working in department 90.

6. Write a query to get the highest, lowest, sum, and average salary of all employees

7. Write a query to get the number of employees with the same job.

8. Write a query to get the difference between the highest and lowest salaries.

9. Write a query to find the manager ID and the salary of the lowest-paid employee for that manager.

10. Write a query to get the department ID and the total salary payable in each department.

11. Write a query to get the average salary for each job ID excluding programmer.

12. Write a query to get the total salary, maximum, minimum, average salary of employees (job ID wise), for department ID 90 only.

13. Write a query to get the job ID and maximum salary of the employees where maximum salary is greater than or equal to $4000.

14. Write a query to get the average salary for all departments employing more than 10 employees.

[preeti-max]

1.SELECT COUNT(DISTINCT JOB_ID)FROM EMPLOYEES;
2.SELECT SUM(SALARY)FROM EMPLOYEES;
3.SELECT MIN(SALARY)FROM EMPLOYEES;
4.SELECT MAX(SALARY)FROM EMPLOYEES INNER JOIN JOBS USING(JOB_ID) WHERE 
JOB_TITLE='Programmer'; 
5.SELECT AVG(SALARY),COUNT(*)FROM EMPLOYEES WHERE DEPARTMENT_ID=90;
6.SELECT MAX(SALARY),MIN(SALARY),SUM(SALARY),AVG(SALARY)FROM EMPLOYEES;
7.SELECT JOB_ID,COUNT(*)FROM EMPLOYEES GROUP BY JOB_ID;
8.SELECT MAX(SALARY)-MIN(SALARY) AS DIFF FROM EMPLOYEES;
9.SELECT MANAGER_ID,MIN(SALARY) FROM EMPLOYEES GROUP BY MANAGER_ID;
10.SELECT DEPARTMENT_ID ,SUM(SALARY)FROM EMPLOYEES GROUP BY DEPARTMENT_ID;

[Gaurav-Joshi-31]

select count(distinct(JOB_ID)) as jobNumber from employees;

select sum(SALARY) as payableSalary from employees;

select min(SALARY) from employees;

select max(SALARY) from employees inner join jobs using(JOB_ID) where JOB_TITLE='Programmer'  ;

select count(*) as count,avg(SALARY) from employees where DEPARTMENT_ID=90;

select max(SALARY),min(SALARY),sum(SALARY),avg(SALARY) from employees;

select JOB_ID, count(*) as count from employees group by JOB_ID;

select (max(SALARY) - min(SALARY)) as difference from employees;

select MANAGER_ID,min(SALARY) as lowestPaidSalary from employees group by MANAGER_ID;

select DEPARTMENT_ID,sum(SALARY) as totalPayableInDepartment from employees group by DEPARTMENT_ID;

[RohitNaikade1]

1.select count(job_id) from employees;

2.select sum(salary) from employees;

3.select min(salary) from employees;

4.select max(e.salary) from employees e inner join jobs j using (job_id) where j.job_title="Programmer";

5.select count(e.department_id) as count,avg(e.salary) from employees e inner join departments d using (department_id) where e.department_id=90;

6.select max(salary) as Max_salary,min(salary) as Min_salary,sum(salary) as Sum,avg(salary) as Average from employees;

7.SELECT JOB_ID,COUNT(*)FROM EMPLOYEES GROUP BY JOB_ID;

8.select max(salary)-min(salary) as difference from employees;

9.SELECT MANAGER_ID,MIN(SALARY) FROM EMPLOYEES GROUP BY MANAGER_ID;

10.SELECT DEPARTMENT_ID ,SUM(SALARY)FROM EMPLOYEES GROUP BY DEPARTMENT_ID;

[rakeshGit419]

select count(distinct(jobTitle)) as jobsCount from employees; -- 1
select sum(salary) total from employees; -- 2
select min(salary) min_salary from employees; -- 3
select max(salary) from employees inner join jobs using(job_id) where title = 'Programmer'; -- 4
select count(*),avg(salary) from employees where department_id = 90; -- 5
select max(salary),min(salary),sum(salary),avg(salary) from employees; -- 6
select count(*),job_id from employees group by job_id; -- 7
select (max(salary)-min(salary)) as difference from employees; -- 8
select manager_id,min(salary) from employees group by manager_id; -- 9
select department_id,sum(salary) from employees group by department_id; -- 10

[Shubhi27]

1. SELECT COUNT(DISTINCT JOB_ID) FROM employees;

2. SELECT SUM(SALARY)FROM EMPLOYEES;

3. SELECT MIN(SALARY)FROM EMPLOYEES;

4. SELECT MAX(SALARY)FROM EMPLOYEES INNER JOIN JOBS USING(JOB_ID) WHERE JOB_TITLE = 'Programmer';

5. SELECT AVG(SALARY) AS AVERAGE, COUNT(*) AS TOTAL FROM EMPLOYEES WHERE DEPARTMENT_ID = '90';

6. SELECT MAX(SALARY) AS MAX ,MIN(SALARY) AS MIN ,SUM(SALARY) AS TOTAL ,AVG(SALARY) AS AVERAGE FROM EMPLOYEES;

7. SELECT JOB_ID, COUNT(*) AS TOTAL FROM EMPLOYEES GROUP BY JOB_ID;

8. SELECT (MAX(SALARY) - MIN(SALARY)) AS DIFFERENCE FROM EMPLOYEES ;

9. SELECT MANAGER_ID, MIN(SALARY) AS LOWESTSALARY FROM EMPLOYEES GROUP BY MANAGER_ID;

10. SELECT DEPARTMENT_ID, SUM(SALARY) AS TOTAL FROM EMPLOYEES GROUP BY DEPARTMENT_ID;

11. SELECT JOB_ID, AVG(SALARY) FROM EMPLOYEES INNER JOIN JOBS USING(JOB_ID) WHERE JOB_TITLE = "Programmer" GROUP BY 
      JOB_ID;

12. SELECT JOB_ID, SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = 90 GROUP 
      BY JOB_ID;

13. SELECT JOB_ID, MAX(SALARY) AS SALARY FROM EMPLOYEES WHERE SALARY >= 4000 GROUP BY JOB_ID;

14. SELECT DEPARTMENT_ID, AVG(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING COUNT(DEPARTMENT_ID) > 10

[shreiyarandive]

1) select count(distinct JOB_ID) from employees;

2) select sum(salary) from employees;

3) select min(salary) from employees;

4) select max(e.salary) from employees e inner join jobs j using (job_id) where j.job_title="Programmer";

5) select avg(salary), count(*) from employees where department_id=90;

6) select max(salary), min(salary), sum(salary), avg(salary) from employees;

7) select JOB_ID, count(*) from employees group by(JOB_ID);

8) select max(salary) - min(salary) as diff from employees;

9) select manager_id, min(salary) from employees group by manager_id;

10) select department_id, sum(salary) from employees group by department_id; 

[ratnadeepikavuddagiri]

1)select count(distinct job_id) from employees;
2)select sum(salary) from employees;
3)select min(salary) from employees;
4)select max(salary) from employees where job_id in(select job_id from jobs where job_title="programmer");
5)select avg(salary),count(employee_id) from employees where department_id=90;
6)select max(salary),min(salary),avg(salary),sum(salary) from employees;
7)select job_id,count(*) from employees group by job_id;
8)select max(salary)-min(salary) as salarydiff from employees;
9)select manager_id,min(salary) from employees group by manager_id;
10)select department_id,sum(salary) from employees group by department_id;
11)select avg(salary) from employees where job_id in(select job_id from jobs where job_title <>"Programmer") group by job_id;
12)select sum(salary),max(salary),min(salary),avg(salary) from employees where department_id=90 group by job_id;
13)select job_id,max(salary) from employees where salary>=4000 group by job_id;
14)select avg(salary) from employees group by department_id having count(employee_id)>10;

[aish955]

1. select count(distinct job_id) from employees;
2. select sum(salary) from employees;
3. select min(salary) from employees;
4. select max(salary) from employees where job_id='IT_PROG';
5. select avg(salary),count(*) from employees where department_id=90;
6. select max(salary),min(salary),sum(salary),avg(salary) from employees;
7. select job_id, count(*) from employees group by job_id;
8. select max(salary)-min(salary) from employees;
9. select manager_id,min(salary) from employees group by manager_id;
10. select department_id,sum(salary) from employees group by department_id;

[Sarfinaa]

1. SELECT COUNT(DISTINCT(JOB_ID))FROM EMPLOYEES;
2. SELECT SUM(SALARY)FROM EMPLOYEES;
3. SELECT MIN(SALARY)FROM EMPLOYEES;
4. SELECT MAX(SALARY)FROM EMPLOYEES INNER JOIN JOBS USING(JOB_ID) WHERE JOB_TITLE='Programmer'; 
5. SELECT AVG(SALARY),COUNT(*)FROM EMPLOYEES WHERE DEPARTMENT_ID=90;
6. SELECT MAX(SALARY),MIN(SALARY),SUM(SALARY),AVG(SALARY)FROM EMPLOYEES;
7. SELECT JOB_ID,COUNT(*)FROM EMPLOYEES GROUP BY JOB_ID;
8. SELECT MAX(SALARY)-MIN(SALARY) AS DIFF FROM EMPLOYEES;
9. SELECT MANAGER_ID,MIN(SALARY) FROM EMPLOYEES GROUP BY MANAGER_ID;
10. SELECT DEPARTMENT_ID ,SUM(SALARY)FROM EMPLOYEES GROUP BY DEPARTMENT_ID;
11. SELECT JOB_ID,AVG(SALARY) FROM EMPLOYEES WHERE JOB_ID IN (SELECT JOB_ID FROM JOBS WHERE JOB_TITLE <> "Programmer") GROUP BY JOB_ID;
12. SELECT JOB_ID, SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = 90 GROUP BY JOB_ID;
13. SELECT JOB_ID, MAX(SALARY) AS SALARY FROM EMPLOYEES WHERE SALARY >= 4000 GROUP BY JOB_ID;
14. SELECT DEPARTMENT_ID, AVG(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING COUNT(DEPARTMENT_ID)>10;

[Batchusatyasri]

1. SELECT count(DISTINCT JOB_ID) as jobcount FROM employees;
2. SELECT SUM(salary) as totalsalary from employees;
3. SELECT MIN(salary) from employees;
4. SELECT MAX(salary) from employees inner join jobs using(job_id)
5. where job_title='Programmer';
6. select Floor(avg(salary)) as averagesalary,count(*) from employees where DEPARTMENT_ID=90;
7. select max(salary),min(salary),sum(salary),avg(salary) from employees;
8. SELECT job_id,count(job_id) as samejob from employees group by job_id;
9. select max(salary) - min(salary) as difference from employees;
10. SELECT manager_id,min(salary) as leastpaid from employees group by manager_id;
11. select department_id,sum(salary) as totalsalary from employees group by department_id;

[SrideviYamala]

1. select count(distinct job_id) from employees;

2. select sum(salary) from employees;

3. select min(salary) from employees;

4. select max(salary) from employees where job_id in 
(select job_id from jobs where job_title="Programmer");

5. select avg(salary),count(*) from employees
where DEPARTMENT_ID=90;

6. select max(salary),min(salary),sum(salary),avg(salary) from employees;

7. select job_id,count(*) from employees group by job_id;

8. select max(salary)-min(salary) as difference from employees;

9. select MANAGER_ID,min(SALARY) from employees group by manager_id;

10. select DEPARTMENT_ID,sum(salary) FROM employees group by DEPARTMENT_ID;

11. select job_id,avg(SALARY) from employees where job_id not in 
(select job_id from jobs where job_title="Programmer") group by job_id;

12. select job_id,min(salary),max(salary),avg(SALARY) from employees
where job_id in (select job_id from employees where
department_id=90) group by job_id;

13. select job_id,max(salary) as max_sal from employees group by job_id
having max_sal>=4000 ORDER BY JOB_ID;

14. select avg(salary) from employees group by department_id
having count(employee_id)>10;

[Snehil6197]

1--SELECT COUNT(DISTINCT JOB_ID) FROM employees;

2--SELECT SUM(SALARY) AS TOTAL_SALARY FROM employees;

3--SELECT MIN(SALARY) AS MIN_SALARY FROM employees;

4--SELECT MAX(SALARY) FROM employees WHERE JOB_ID IN (SELECT JOB_ID FROM jobs WHERE JOB_TITLE='Programmer');

5--SELECT AVG(SALARY),COUNT(*) FROM employees WHERE DEPARTMENT_ID=90;

6--SELECT MIN(SALARY),MAX(SALARY),AVG(SALARY),SUM(SALARY) FROM employees;

7--SELECT COUNT(JOB_ID),JOB_ID FROM employees GROUP BY JOB_ID;

8--SELECT (MAX(SALARY)-MIN(SALARY)) AS DIFFERENCE FROM employees;

9--SELECT MANAGER_ID,MIN(SALARY) AS LOWEST_PAID FROM employees GROUP BY MANAGER_ID;

10--SELECT DEPARTMENT_ID ,SUM(SALARY)FROM EMPLOYEES GROUP BY DEPARTMENT_ID;

[Khyati-tripathi]

1. select count(distinct job_id) from employees;
2. select sum(salary) from employees;
3. select min(salary) from employees;
4. select max(salary) from employees where job_id in(select job_id from jobs where job_title="programmer");
5. select avg(salary),count(employee_id) from employees where department_id=90;
6. select max(salary),min(salary),avg(salary),sum(salary) from employees;
7. select job_id,count(*) from employees group by job_id;
8. select max(salary)-min(salary) as salarydiffernece from employees;
9. select manager_id,min(salary) from employees group by manager_id;
10. select department_id,sum(salary) from employees group by department_id;

[Ashritha-sd]

1. SELECT COUNT(DISTINCT JOB_ID) FROM employees;
2. SELECT SUM(SALARY) FROM employees;
3. SELECT MIN(SALARY) FROM employees;
4. SELECT MAX(SALARY) FROM employees INNER JOIN jobs USING(JOB_ID) WHERE JOB_TITLE="Programmer";
5. SELECT AVG(SALARY),COUNT(EMPLOYEE_ID) FROM employees WHERE DEPARTMENT_ID=90;
6. SELECT MAX(SALARY),MIN(SALARY),SUM(SALARY),AVG(SALARY)FROM employees;
7. SELECT JOB_ID,COUNT(*)FROM employees GROUP BY JOB_ID;
8. SELECT MAX(SALARY)-MIN(SALARY) FROM employees;
9. SELECT MANAGER_ID,MIN(SALARY) FROM employees GROUP BY MANAGER_ID;
10. SELECT DEPARTMENT_ID,SUM(SALARY) FROM employees GROUP BY DEPARTMENT_ID;

[ujjwal18bit1167]

--1
SELECT COUNT(distinct JOB_ID) FROM EMPLOYEES;

--2
select sum(salary) from employees;

--3
SELECT MIN(SALARY)FROM EMPLOYEES;

--4
select MAX(SALARY) from employees inner join jobs using(JOB_ID) where JOB_TITLE="Programmer";

--5
select avg(salary),count(employee_id) from employees where DEPARTMENT_ID = 90; 
 
 --6
 select MAX(salary) , MIN(salary), SUM(salary), AVG(salary) from employees;

 --7
 select job_id,count(employee_id) from employees group by job_id;

 --8
select MAX(salary) - MIN(salary) from employees;

--9
SELECT MANAGER_ID,MIN(SALARY) FROM EMPLOYEES GROUP BY MANAGER_ID;

--10
SELECT DEPARTMENT_ID ,SUM(SALARY)FROM EMPLOYEES GROUP BY DEPARTMENT_ID;

[Narutoram]

1.select count(job_id) from employees;
2.select sum(salary) from employees;
3.select min(salary) from employees;
4.select max(e.salary) from employees e inner join jobs j using (job_id) where j.job_title="Programmer";
5.select count(),avg(salary) from employees where department_id = 90;
6.select max(salary),min(salary),sum(salary),avg(salary) from employees;
7.select count(),job_id from employees group by job_id;
8.select max(salary)-min(salary) as difference from employees;
9.select manager_id,min(salary) from employees group by manager_id;
10.select department_id,sum(salary) from employees group by department_id;``

[GouthamGunji]

1.select count(job_id) from employees;

2.select sum(salary) from employees;

3.select min(salary) from employees;

4.select max(e.salary) from employees e inner join jobs j using (job_id) where j.job_title="Programmer";

5.select count(e.department_id) as count,avg(e.salary) from employees e inner join departments d using (department_id) where e.department_id=90;

6.select max(salary) as Max_salary,min(salary) as Min_salary,sum(salary) as Sum,avg(salary) as Average from employees;

7.SELECT JOB_ID,COUNT(*)FROM EMPLOYEES GROUP BY JOB_ID;

8.select max(salary)-min(salary) as difference from employees;

9.SELECT MANAGER_ID,MIN(SALARY) FROM EMPLOYEES GROUP BY MANAGER_ID;

10.SELECT DEPARTMENT_ID ,SUM(SALARY)FROM EMPLOYEES GROUP BY DEPARTMENT_ID;

[vamsi1234567891]

1.select count(distinct job_id) from employees;
2.select sum(salary) from employees;
3.select min(salary) from employees;
4.select max(salary) from employees where job_id = 'IT_PROG';
5.select AVG(salary),count(*) FROM employees WHERE department_id = 90;
6.select MAX(salary),MIN(salary),SUM(salary),AVG(salary) from employees;
7.select job_id, count(*) from employees GROUP BY job_id;
8.select max(salary)-min(salary) as deffernce from employees;
9.select manager_id, MIN(salary)FROM employees GROUP BY manager_id;
10.select department_id, SUM(salary) from employees GROUP BY department_id;

[meghvi-raj]

--Q1
select count(distinct job_id)
from employees;

--Q2
select sum(salary)
from employees;

--Q3
select min(salary)
from employees;

--Q4
select max(salary)
from employees
where job_id like '%PROG%';

--Q5
select avg(salary),
    count(employee_id)
from employees
where department_id = 90;

--Q6
select max(salary),
    min(salary),
    sum(salary),
    avg(salary)
from employees;

--Q7
select count(employee_id),
    job_id
from employees
group by job_id;

--Q8
select max(salary) - min(salary) as `Difference Between Max & Min Salary`
from employees;

--Q9
select min(salary),
    manager_id
from employees
group by MANAGER_ID;

--Q10
select sum(salary),
    department_id
from employees
group by DEPARTMENT_ID;

--Q11
select avg(salary),
    job_id
from employees
where job_id <> 'IT_PROG'
group by job_ID;

--Q12
select sum(salary),
    max(salary),
    min(salary),
    avg(salary),
    job_id
from employees
where DEPARTMENT_ID = 90
group by job_id;

--Q13
select job_id,
    max(salary)
from employees
group by JOB_ID
having max(salary) >= 4000;

--Q14
select avg(salary),
    department_id
from employees
group by department_id
having count(EMPLOYEE_ID) > 10;

[uzma23]

-- DISCUSSION 9 
--#1 Write a query to list the number of jobs available in the employees table.
SELECT COUNT(DISTINCT JOB_ID) FROM employees;

-- #2 Write a query to get the total salaries payable to employees.
SELECT SUM(SALARY) FROM employees

-- #3 Write a query to get the minimum salary from the employees table.
SELECT MIN(SALARY) FROM employees

-- #4 Write a query to get the maximum salary of an employee working as a Programmer.
SELECT MAX(SALARY) FROM employees
WHERE JOB_id LIKE "%prog%"

-- #5 Write a query to get the average salary and number of employees working in department 90.
SELECT AVG(SALARY), COUNT(EMPLOYEE_id)
FROM employees
WHERE DEPARTMENT_ID=90
-- #6 Write a query to get the highest, lowest, sum, and average salary of all employees
SELECT MAX(SALARY), MIN(SALARY),AVG(SALARY)
FROM employees

-- #7Write a query to get the number of employees with the same job.
SELECT COUNT(JOB_ID), JOB_ID
from employees
GROUP BY JOB_ID

-- #8Write a query to get the difference between the highest and lowest salaries.
SELECT MAX(salary)-MIN(SALARY)
from employees

-- #9Write a query to find the manager ID and the salary of the lowest-paid employee for that manager.
SELECT MANAGER_ID, SALARY
FROM employees
GROUP BY MANAGER_ID
HAVING SALARY=MIN(salary)


-- #10 Write a query to get the department ID and the total salary payable in each department.
SELECT DEPARTMENT_ID, SUM(salary)
from employees
GROUP BY DEPARTMENT_ID
-- #11 Write a query to get the average salary for each job ID excluding programmer.
SELECT AVG(salary), JOB_ID
FROM employees
WHERE JOB_ID not like"%prog"
GROUP BY JOB_ID
-- ##12 Write a query to get the total salary, maximum, minimum, average salary of employees (job ID wise), for department ID 90 only.
SELECT sum(SALARY), min(SALARY), MAX(salary),AVG(salary)
from employees
where DEPARTMENT_ID<>90
group by JOB_ID
-- #13 Write a query to get the job ID and maximum salary of the employees where maximum salary is greater than or equal to $4000.
select JOB_ID, max(SALARY)
FROM employees
GROUP BY JOB_ID
HAVING MAX(SALARY)>4000
-- #14Write a query to get the average salary for all departments employing more than 10 employees.  
SELECT AVG(SALARY),DEPARTMENT_ID FROM employees
GROUP BY JOB_ID
HAVING COUNT(JOB_ID)>10

[ghost]

1.SELECT COUNT(DISTINCT JOB_ID)FROM EMPLOYEES;

2.SELECT SUM(SALARY)FROM EMPLOYEES;

3.SELECT MIN(SALARY)FROM EMPLOYEES;

4.SELECT MAX(SALARY)FROM EMPLOYEES INNER JOIN JOBS USING(JOB_ID) WHERE JOB_TITLE='Programmer'; 

5.SELECT AVG(SALARY),COUNT(*)FROM EMPLOYEES WHERE DEPARTMENT_ID=90;

6.SELECT MAX(SALARY) AS MAX_SALARY,MIN(SALARY) AS MIN_SALARY,SUM(SALARY) AS SUM,AVG(SALARY) AS AVERAGE FROM EMPLOYEES;

7.SELECT JOB_ID,COUNT(*)FROM EMPLOYEES GROUP BY JOB_ID;

8.SELECT MAX(SALARY)-MIN(SALARY) FROM EMPLOYEES;

9.SELECT MANAGER_ID,MIN(SALARY) FROM EMPLOYEES GROUP BY MANAGER_ID;

10.SELECT DEPARTMENT_ID ,SUM(SALARY)FROM EMPLOYEES GROUP BY DEPARTMENT_ID;

11.SELECT JOB_ID, AVG(SALARY) FROM EMPLOYEES INNER JOIN JOBS USING(JOB_ID) WHERE JOB_TITLE = "Programmer" GROUP BY JOB_ID;

12.SELECT JOB_ID, SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID <> 90 GROUP BY JOB_ID;

13.SELECT JOB_ID, MAX(SALARY) AS SALARY FROM EMPLOYEES WHERE SALARY >= 4000 GROUP BY JOB_ID;

14.SELECT DEPARTMENT_ID, AVG(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING COUNT(DEPARTMENT_ID) > 10 ;

[ghost]

1. select count(distinct(job_id)) as distinct_jobid
   from employees;

2. select min(salary) as min_salary
   from employees;

3. select max(salary) as MAX_SALARY_OF_PROGRAMMER
   from employees
   inner join jobs using(JOB_ID)
   where job_title = 'Programmer';

4. select avg(salary) as average_salary,
   count(*) as no_of_employees
   from employees
   group by DEPARTMENT_ID
   having DEPARTMENT_ID = 90;

5. SELECT MAX(salary) as highest_salary,
   MIN(salary) as lowest_salary,
   sum(salary) as sum_of_salary,
   AVG(salary)
   from employees;

6. SELECT JOB_ID,
   COUNT(*)
   FROM EMPLOYEES
   GROUP BY JOB_ID;
   SELECT max(salary) - min(salary)
   from employees;

7. SELECT MANAGER_ID,
   MIN(SALARY)
   FROM EMPLOYEES
   GROUP BY MANAGER_ID;

8. SELECT DEPARTMENT_ID,
   SUM(SALARY)
   FROM EMPLOYEES
   GROUP BY DEPARTMENT_ID;

9. SELECT JOB_ID,
   AVG(SALARY)
   FROM EMPLOYEES
   INNER JOIN JOBS USING(JOB_ID)
   WHERE JOB_TITLE != "Programmer"
   GROUP BY JOB_ID;

10. SELECT JOB_ID,
    SUM(SALARY),
    MAX(SALARY),
    MIN(SALARY),
    AVG(SALARY)
    FROM EMPLOYEES
    WHERE DEPARTMENT_ID = 90
    GROUP BY JOB_ID;

11. SELECT JOB_ID,
    MAX(SALARY) AS SALARY
    FROM employees
    WHERE SALARY >= 4000
    GROUP BY JOB_ID;

12. select DEPARTMENT_ID,avg(salary)
    from employees
    group by department_id
    having count(employee_id) > 10;

[Kuntapaliwal]

--1.  Write a query to list the number of jobs available in the employees table.
select * from employees GROUP BY JOB_ID;

--2.  Write a query to get the total salaries payable to employees.
select SUM(salary) from employees;

--3.  Write a query to get the minimum salary from the employees table.
select MIN(salary)from employees;

--4. Write a query to get the maximum salary of an employee working as a Programmer.
select MAX(salary) from employees where JOB_ID LIKE 'IT_PROG';

--5. Write a query to get the average salary and number of employees working in department 90.
SELECT AVG(salary) as avg_salary,count(*) from employees where DEPARTMENT_ID=90;

--6. Write a query to get the highest, lowest, sum, and average salary of all employees
select max(salary),MIN(salary),sum(salary),AVG(salary)from employees;

--7. Write a query to get the number of employees with the same job.
SELECT COUNT(*) from employees GROUP BY job_id;

--8.  Write a query to get the difference between the highest and lowest salaries.
select MAX(salary)-MIN(salary) from employees;

--9. Write a query to find the manager ID and the salary of the lowest-paid employee for that manager.
SELECT manager_id,
    MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
ORDER BY MIN(salary) DESC;

--10. Write a query to get the department ID and the total salary payable in each department.
SELECT department_id,sum(salary) from employees GROUP BY department_id;

--11. Write a query to get the average salary for each job ID excluding programmer.
SELECT job_id,
    AVG(salary)
FROM employees
WHERE job_id <> 'IT_PROG'
GROUP BY job_id;
--12. Write a query to get the total salary, maximum, minimum, average salary of employees (job ID wise), for department ID 90 only.
SELECT job_id,
    SUM(salary),
    AVG(salary),
    MAX(salary),
    MIN(salary)
FROM employees
WHERE department_id = '90'
GROUP BY job_id;
--13. Write a query to get the job ID and maximum salary of the employees where maximum salary is greater than or equal to $4000.
SELECT job_id,
    MAX(salary)
FROM employees
GROUP BY job_id
HAVING MAX(salary) >= 4000;
--14. Write a query to get the average salary for all departments employing more than 10 employees.
SELECT department_id,
    AVG(salary),
    COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;

[ApoorvaSinha]

1.select count(distinct(JOB_ID)) as jobNumber from employees;

2.select sum(SALARY) as payableSalary from employees;

3.select min(SALARY) from employees;

4.select max(SALARY) from employees inner join jobs using(JOB_ID) where JOB_TITLE='Programmer'  ;

5.select count(*) as count,avg(SALARY) from employees where DEPARTMENT_ID=90;

6.select max(SALARY),min(SALARY),sum(SALARY),avg(SALARY) from employees;

7.select JOB_ID, count(*) as count from employees group by JOB_ID;

8.select (max(SALARY) - min(SALARY)) as difference from employees;

9.select MANAGER_ID,min(SALARY) as lowestPaidSalary from employees group by MANAGER_ID;

10.select DEPARTMENT_ID,sum(SALARY) as totalPayableInDepartment from employees group by DEPARTMENT_ID;

[vshvandana]

Select count(DISTINCT job_id) from employees;
Select sum(salary) from employees;
Select min(salary) from employees;
SELECT MAX(SALARY)FROM EMPLOYEES INNER JOIN JOBS USING(JOB_ID) WHERE 
JOB_TITLE='Programmer'; 
SELECT AVG(SALARY),COUNT(*)FROM EMPLOYEES WHERE DEPARTMENT_ID=90;
SELECT MAX(SALARY),MIN(SALARY),SUM(SALARY),AVG(SALARY)FROM EMPLOYEES;
SELECT JOB_ID,COUNT(*)FROM EMPLOYEES GROUP BY JOB_ID;
SELECT MAX(SALARY)-MIN(SALARY) AS DIFF FROM EMPLOYEES;
SELECT MANAGER_ID,MIN(SALARY) FROM EMPLOYEES GROUP BY MANAGER_ID;
SELECT DEPARTMENT_ID ,SUM(SALARY)FROM EMPLOYEES GROUP BY DEPARTMENT_ID;

[ashumore1998]

--1. Write a query to list the number of jobs available in the employees table.

select count(distinct e.JOB_ID) as number_of_jobs
FROM employees e;

--2. Write a query to get the total salaries payable to employees.

select sum(SALARY) as total_salaries FROM employees ;

--3. Write a query to get the minimum salary from the employees table.

select min(SALARY) as minimum_salary FROM employees ;

--4. Write a query to get the maximum salary of an employee working as a Programmer.

select max(SALARY) as maximum_salary FROM employees ;

--5. Write a query to get the average salary and number of employees working in department 90.

select avg(SALARY) as avg_salary FROM employees where DEPARTMENT_ID=90;

--6. Write a query to get the highest, lowest, sum, and average salary of all employees

select min(SALARY) as minimum_salary,sum(SALARY) as total_salaries,max(SALARY) as maximum_salary,avg(SALARY) as avg_salary FROM employees;

--7. Write a query to get the number of employees with the same job.

select JOB_ID,count(*) from employees group by JOB_ID;

--8. Write a query to get the difference between the highest and lowest salaries.

select (max(SALARY)-min(SALARY)) as salary_difference FROM employees;

--9. Write a query to find the manager ID and the salary of the lowest-paid employee for that manager.

select MANAGER_ID,min(SALARY) as lowest_salary
from employees e
group by MANAGER_ID;

--10. Write a query to get the department ID and the total salary payable in each department.

select DEPARTMENT_ID,sum(SALARY) as total_salary
from employees e
group by DEPARTMENT_ID;

--11. Write a query to get the average salary for each job ID excluding programmer.

select j.JOB_ID,avg(e.SALARY) as avg_salary
FROM employees e
JOIN jobs j
on e.JOB_ID = j.JOB_ID
where j.JOB_TITLE not in ('Programmer')
group by j.JOB_ID
;

--12. Write a query to get the total salary, maximum, minimum, average salary of employees (job ID wise), for department ID 90 only.

select JOB_ID,min(SALARY) as minimum_salary,sum(SALARY) as total_salaries,max(SALARY) as maximum_salary,avg(SALARY) as avg_salary
FROM employees
where DEPARTMENT_ID=90
group by JOB_ID;

--13. Write a query to get the job ID and maximum salary of the employees where maximum salary is greater than or equal to $4000.

select JOB_ID,max(SALARY) as maximum_salary
from employees
group by JOB_ID
having max(SALARY)>=4000;

--14. Write a query to get the average salary for all departments employing more than 10 employees.

select e.DEPARTMENT_ID,d.DEPARTMENT_NAME,avg(e.SALARY) as avg_salary,count(e.DEPARTMENT_ID) as employee_count
from employees e
JOIN departments d
on d.DEPARTMENT_ID = e.DEPARTMENT_ID
group by e.DEPARTMENT_ID,d.DEPARTMENT_NAME
having count(e.DEPARTMENT_ID)>10;

[dhanyaMeruva9]

use hr;
--1
select count(distinct job_id) from employees;

--2
select sum(salary) as 'Total Salary' from employees;

--3
select min(salary) as 'Minimum Salary' from employees;

--4
select max(salary) as 'Maximum Salary' from employees join jobs USING(job_id) where job_title='Programmer';

--5
select avg(salary) as 'Average salary', count(*) from employees where department_id=90;

--6
SELECT MAX(SALARY) as 'Maximum salary',MIN(SALARY) as 'Minimum salary',SUM(SALARY) as 'Total Salary',AVG(SALARY) as 'Average Salary' FROM EMPLOYEES;

--7
SELECT JOB_ID,COUNT(*)FROM EMPLOYEES GROUP BY JOB_ID;

--8
SELECT MAX(SALARY)-MIN(SALARY) AS DIFF FROM EMPLOYEES;

--9
SELECT MANAGER_ID,MIN(SALARY) as minimum FROM EMPLOYEES GROUP BY MANAGER_ID;

--10
SELECT DEPARTMENT_ID ,SUM(SALARY) as total_sal FROM EMPLOYEES GROUP BY DEPARTMENT_ID;

--11
SELECT j.job_title,j.job_id, AVG(e.SALARY) as avg_sal FROM EMPLOYEES e join jobs j USING(job_id)
where j.job_title <>'Programmer' GROUP BY j.job_id,j.job_title;

--12
SELECT department_id,MAX(SALARY) as 'Maximum salary',MIN(SALARY) as 'Minimum salary',SUM(SALARY) as 'Total Salary',AVG(SALARY) as 'Average Salary' FROM EMPLOYEES
join departments d using(department_id) where department_id=90 group by job_id;

--13
select job_id, max(salary) as max_sal from employees where salary>=4000 group by job_id;

--14
select avg(e.salary) as avg_sal, d.department_name from employees e join departments d
using(department_id)
group by e.department_id
having count() >10;
select count() from employees group by department_id;

[var5]

1. Write a query to list the number of jobs available in the employees table.
   select count(distinct(job_id)) from employees;

2. Write a query to get the total salaries payable to employees.
   SELECT round(SUM(salary),2) AS total_salaries_payable
   FROM employees;

3. Write a query to get the minimum salary from the employees table.
   SELECT round(min(salary),2) AS total_salaries_payable
   FROM employees;

4. Write a query to get the maximum salary of an employee working as a Programmer.
   SELECT round(max(salary),2) AS total_salaries_payable
   FROM employees
   where job_id in(select job_id
   from jobs
   where job_title like '%programmer');

5. Write a query to get the average salary and number of employees working in department

select round(avg(salary),2) as AVG_salary, count(salary) as
No_of_employees
from employees
-- group by department_id;
where department_id = 90;

6. Write a query to get the highest, lowest, sum, and average salary of all employees
   select min(salary) as min_salary , max(salary) as max_salry,
   round(avg(salary), 2) as avg_salary, sum(salary) as total_salary
   from employees;

7. Write a query to get the number of employees with the same job.
   select job_id, count(job_id) as emp_with_same_job
   from employees group by job_id;

8. Write a query to get the difference between the highest and lowest salaries.
   select max(salary) - min(salary) as salry_diff
   from employees;

9. Write a query to find the manager ID and the salary of the lowest-paid employee for that
   manager.
   select manager_id, min(salary) as lowest_paid_salary
   from employees
   where manager_id > 0
   group by manager_id;

10. Write a query to get the department ID and the total salary payable in each department.
    select department_id as department_id , sum(salary) as
    total_salary_payable
    from employees
    group by department_id;

11. Write a query to get the average salary for each job ID excluding programmer.
    select job_id, round(avg(salary),2) as avg_salry
    from employees
    where job_id not in (
    select job_id from jobs
    where job_title like '%programmer')
    group by job_id;

12. Write a query to get the total salary, maximum, minimum, average salary of employees (job
    ID wise), for department ID 90 only.
    select job_id, sum(salary) as total_salary, min(salary)as
    min_salary, max(salary) as max_salary, round(avg(salary),2) as
    avg_saalry
    from employees
    where department_id = 90
    group by job_id;

13. Write a query to get the job ID and maximum salary of the employees where maximum
    salary is greater than or equal to $4000.
    select job_id, salary
    from employees
    where salary >= 4000;

14. Write a query to get the average salary for all departments employing more than 10
    employee
    select department_id, round(avg(salary), 2) as avg_salary
    from employees
    group by department_id having count(*)>10;

[NANDU-R-N]

#1.Write a query to list the number of jobs available in the employees table.

SELECT job_title FROM jobs
WHERE job_id IN(SELECT DISTINCT(job_id) FROM employees);

SELECT COUNT(DISTINCT(job_id)) FROM employees;

#2. Write a query to get the total salaries payable to employees.

SELECT SUM(salary) FROM employees;

#3. Write a query to get the minimum salary from the employees table.

SELECT MIN(salary) FROM employees;

#4. Write a query to get the maximum salary of an employee working as a Programmer.

SELECT MAX(salary) FROM employees WHERE job_id = 'IT_PROG';

#5. Write a query to get the average salary and number of employees working in department 90.

SELECT AVG(salary),COUNT(employee_id) FROM employees WHERE department_id = 90;

#6. Write a query to get the highest, lowest, sum, and average salary of all employees
SELECT MIN(salary),MAX(salary),SUM(salary),AVG(salary) FROM employees;

#7. Write a query to get the number of employees with the same job.

SELECT DISTINCT(job_id),COUNT(employee_id) FROM employees
GROUP BY job_id;

#8.Write a query to get the difference between the highest and lowest salaries.

SELECT MAX(salary)-MIN(salary) AS difference FROM employees;

#9.Write a query to find the manager ID and the salary of the lowest-paid employee for that manager.
SELECT manager_id,MIN(salary) FROM employees GROUP BY manager_id;

#SELECT manager_id,salary FROM employees;

#10. Write a query to get the department ID and the total salary payable in each department.
SELECT department_id,SUM(salary) FROM employees GROUP BY department_id;

#11.Write a query to get the average salary for each job ID excluding programmer.
SELECT job_id,AVG(salary) FROM employees
WHERE job_id != 'IT_PROG'
GROUP BY job_id;

#12. Write a query to get the total salary, maximum, minimum,
-- average salary of employees (job ID wise), for department ID 90 only.
USE hr;
SELECT job_id,SUM(salary),MAX(salary),MIN(salary) FROM employees WHERE department_id = 90 GROUP BY job_id;

#13 .Write a query to get the job ID and maximum
-- salary of the employees where maximum salary is greater than or equal to $4000.
SELECT job_id,SUM(salary),MAX(salary) FROM employees WHERE salary >= 4000 GROUP BY job_id;

SELECT * FROM employees WHERE job_id = 'AC_ACCOUNT';

#14. Write a query to get the average salary for all departments employing more than 10 employees.

SELECT department_id,AVG(salary) FROM employees GROUP BY department_id HAVING COUNT(department_id) >10;

[TABASSUM-12345]

#1
SELECT COUNT(DISTINCT JOB_ID)FROM hr.EMPLOYEES;

#2
SELECT SUM(SALARY)FROM hr.EMPLOYEES;

#3
SELECT MIN(SALARY)FROM hr.EMPLOYEES;

#4
SELECT MAX(SALARY)FROM hr.EMPLOYEES INNER JOIN hr.JOBS USING(JOB_ID) WHERE JOB_TITLE='Programmer';

#5
SELECT AVG(SALARY),COUNT(*)FROM hr.EMPLOYEES WHERE DEPARTMENT_ID=90;

#6
SELECT MAX(SALARY),MIN(SALARY),SUM(SALARY),AVG(SALARY)FROM hr.EMPLOYEES;

#7
SELECT JOB_ID,COUNT(*)FROM hr.EMPLOYEES GROUP BY JOB_ID;

#8
SELECT MAX(SALARY)-MIN(SALARY) AS DIFF FROM hr.EMPLOYEES;

#9
SELECT MANAGER_ID,MIN(SALARY) FROM hr.EMPLOYEES GROUP BY MANAGER_ID;

#10
SELECT DEPARTMENT_ID ,SUM(SALARY)FROM hr.EMPLOYEES GROUP BY DEPARTMENT_ID;

#11
SELECT JOB_ID, AVG(SALARY) FROM hr.EMPLOYEES INNER JOIN hr.JOBS USING(JOB_ID) WHERE JOB_TITLE = "Programmer" GROUP BY
JOB_ID;

#12
SELECT JOB_ID, SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM hr.EMPLOYEES WHERE DEPARTMENT_ID = 90 GROUP
BY JOB_ID;

#13
SELECT JOB_ID, MAX(SALARY) AS SALARY FROM hr.EMPLOYEES WHERE SALARY >= 4000 GROUP BY JOB_ID;

#14
SELECT DEPARTMENT_ID, AVG(SALARY) FROM hr.EMPLOYEES GROUP BY DEPARTMENT_ID HAVING COUNT(DEPARTMENT_ID) > 10;