The document describes a database assignment involving analysis of a products table using aggregate functions and group by clauses. It includes creating the table, inserting sample data, and running 16 queries to count, sum, average, find maximum/minimum values, calculate variance/standard deviation, and group by various columns. The results of each query are displayed.
The document describes a database assignment involving analysis of a products table using aggregate functions and group by clauses. It includes creating the table, inserting sample data, and running 16 queries to count, sum, average, find maximum/minimum values, calculate variance/standard deviation, and group by various columns. The results of each query are displayed.
The document describes a database assignment involving analysis of a products table using aggregate functions and group by clauses. It includes creating the table, inserting sample data, and running 16 queries to count, sum, average, find maximum/minimum values, calculate variance/standard deviation, and group by various columns. The results of each query are displayed.
The document describes a database assignment involving analysis of a products table using aggregate functions and group by clauses. It includes creating the table, inserting sample data, and running 16 queries to count, sum, average, find maximum/minimum values, calculate variance/standard deviation, and group by various columns. The results of each query are displayed.
15. Calculate average price group by product_type_id and having average price greater than 1500. SQL> select product_type_id,avg(price) as AveragePrice from products group by product_type_id having avg(price)>1500;
16. Calculate average price of the products whose price is less that Rs.2000 and group by product_type_id and having average price greater than 1500. SQL> select product_type_id,avg(price) as AveragePrice from products where price<2000 group by product_type_id having avg(price)>1500; no rows selected
TASK 2: 1. Find the highest sal of EMP table SQL> select max(sal) as highest_sal from emp; HIGHEST_SAL ----------- 5000
2. Find details of highest paid employee.
SQL> select * from emp where sal=(select max(sal) from emp); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7839 KING PRESIDENT 17-NOV-81 5000 10
3. Find the highest paid employee of sales department
SQL> select * from emp where job='SALESMAN' and sal=(select max(sal) from emp where job='SALESMAN'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
4. Find the total sal given to the MGR
SQL> select sum(sal) as total_sal from emp where mgr is not null; TOTAL_SAL ---------- 24025
5. Find the total annual sal to distribute job wise in the year 81. SQL> select job,sum(sal*12) as annual_sal from emp where hiredate between '1-jan-1981' and '31-dec-1981' group by job; JOB ANNUAL_SAL --------- ---------- SALESMAN 67200 CLERK 11400 PRESIDENT 60000 MANAGER 99300 ANALYST 36000
6. Display the average salaries of all the clerks.
SQL> select avg(sal) from emp where job='CLERK';
AVG(SAL) ---------- 1037.5
7. List the employeein dept 20 whose sal is >the average sal 0f dept 10 emps SQL> select * from emp where deptno=20 and sal>(select avg(sal) from emp where deptno=10);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7566 JONES MANAGER 7839 02-APR-81 2975 20 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20
8. Display the number of employee for each job group deptno wise SQL> select deptno,job,count(*) as num_emp from emp group by deptno,job order by deptno,job;
9. List the manage rno and the number of employees working for those mgrs in the ascending Mgrno. SQL> select mgr,count(*) as num_emp from emp group by mgr order by mgr asc;
SQL> select deptno,count(*) from emp group by deptno; DEPTNO COUNT(*) ---------- ---------- 30 6 20 5 10 3
11. List the department, details where at least two emps are working SQL> select * from (select deptno,count(*) as num_emp from emp group by deptno) where num_emp>=2;
12. List the names of the emps who are ge ng the highest sal dept wise. SQL> select e1.ename,e1.deptno,e1.sal from emp e1 where e1.sal=(select max(e2.sal) from emp e2 where e2.deptno=e1.deptno );
ENAME DEPTNO SAL
---------- ---------- ---------- BLAKE 30 2850 SCOTT 20 3000 KING 10 5000 FORD 20 3000 13. List the emps whose sal is greater than or equal to the average of max and minimum SQL> select * from emp where sal>=(select (max(sal)+min(sal))/2 from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7566 JONES MANAGER 7839 02-APR-81 2975 20 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7902 FORD ANALYST 7566 03-DEC-81 3000 20
14. List the no. of emps in each department where the no. is more than 3. SQL> select deptno,count(*) from emp group by deptno having count(*)>3;
DEPTNO COUNT(*) ---------- ---------- 30 6 20 5
15. Find out how may Managers are their in the company. SQL> select count(job) from emp where job='MANAGER';
COUNT(JOB) ---------- 3
16. Check whether all the emps numbers are indeed unique SQL> select empno from emp group by empno having count(empno)>1;
no rows selected 17. Find all the emps who earn the minimum Salary for each job wise in ascending order. SQL> select job,sal,ename from emp where (job,sal) in (select job,min(sal) as min_sal from emp group by job) order by job asc;
JOB SAL ENAME
--------- ---------- ---------- ANALYST 3000 SCOTT ANALYST 3000 FORD CLERK 800 SMITH MANAGER 2450 CLARK PRESIDENT 5000 KING SALESMAN 1250 WARD SALESMAN 1250 MARTIN 7 rows selected.
18. Find out all the emps who earn highest salary in each job type. Sort in descending salary order. SQL> select job,sal,ename from emp where (job,sal) in (select job,max(sal) as max_sal from emp group by job) order by sal desc;
JOB SAL ENAME
--------- ---------- ---------- PRESIDENT 5000 KING ANALYST 3000 SCOTT ANALYST 3000 FORD MANAGER 2975 JONES SALESMAN 1600 ALLEN CLERK 1300 MILLER 6 rows selected.
19. List the Deptno where there are no emps.
SQL> select d.deptno from dept d le join emp e on d.deptno=e.deptno where e.deptno is null;
DEPTNO ---------- 40
20. List the No.of emp’s and Avg salary within each department for each job. SQL> select d.deptno,e.job,count(*) as emp_count,avg(e.sal) as avg_sal from emp e inner join dept d on e.deptno=d.deptno group by d.deptno, e.job;
21. Find the maximum average salary drawn for each job except for ‘President’. SQL> select job,max(avg_sal) as max_avg_sal from (select job,avg(sal) as avg_sal from emp where job <> 'PRESIDENT' group by job) group by job;
24. Find the count of employee, average salary and sum of the salary SQL> select count(*) as emp_count,avg(sal) as avg_sal, sum(sal) as total_sal from emp;
EMP_COUNT AVG_SAL TOTAL_SAL
---------- ---------- ---------- 14 2073.21429 29025 25. Find the count of employee, average salary and sum of the salary and group by department number wise in the ascending order SQL> select deptno,count(*) as emp_count,avg(sal) as avg_sal, sum(sal) as total_sal from emp group by deptno order by deptno asc;
26. Find the count of employee, average salary and sum of the salary and group by department number wise and job wise and in the ascending order of dept number and Job. SQL> select deptno,job,count(*) as emp_count,avg(sal) as avg_sal, sum(sal) as total_sal from emp group by deptno,job order by deptno asc,job asc;
