SQL Practise

 1.Display the dept information from department table 

A)SELECT * FROM DEPT;

2.Display the details of all employees 

A)SELECT * FROM EMP;

3.Display the name and job for all employees 

A)SELECT ENAME,JOB FROM EMP;

4.Display employee name and annual salary for all employees 

A)SELECT ENAME,SAL,SAL*12 ANUAL_SAL FROM EMP;

5.Display the names of all employees who are working in department number 10 

A)SELECT ENAME FROM EMP WHERE DEPTNO=10;

6.Display the names of all employees working as clerks and drawing a salary more than 1000 

A) SELECT JOB,SAL FROM EMP WHERE JOB='CLERK'AND SAL>1000;

7.Display employee number and names AND COMM for employees who earn commission 

A) SELECT EMPNO,ENAME,COMM FROM EMP WHERE COMM IS NOT NULL;

8.Display the details from emp table in order of emp name 

A) SELECT ENAME FROM EMP ORDER BY ENAME;

9.Display Department numbers and total number of employees working in each Department? 

A) SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO;

10.Display the various jobs and total number of employees working in each job group? 

A) SELECT JOB,COUNT(*) FROM EMP GROUP BY JOB;

11.Display department numbers and Total Salary for each Department? 

A) SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO;

12.Display department numbers and Maximum Salary from each Department? 

A) SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO;

13.Display various jobs and Total Salary for each job? 

A) SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB;

14.Display each job along with min of salary being paid in each job group? 

A) SELECT JOB,MIN(SAL) FROM EMP GROUP BY JOB;

15.Display the department Number with more than three employees in each department? 

A) SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO HAVING COUNT(*)>3;

16.Display various jobs along with total salary for each of the job where total salary is 

greater than 3000? 

A) SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB HAVING SUM(SAL)>3000;

17.Display the various jobs along with total number of employees in each job.The 

output should contain only those jobs with more than three employees? 

A) SELECT JOB,COUNT(*) FROM EMP GROUP BY JOB HAVING COUNT(*) >3;