MySQL QUERIES

Table: EMP

EmpNo Ename Job Hiredate Sal DeptNo

7839 King President 2010-11-17 50000 10
7698 Blake Manager 2010-05-01 28500 30
7782 Clark Manager 2015-06-09 24500 10
7566 Jones Manager 2013-04-02 29750 20
7654 Martin Salesman 2012-09-28 12500 30
7499 Allen Salesman 2011-02-15 16000 30
7844 Turner Salesman 1012-09-08 15000 30
7900 James Clerk 2011-12-23 9500 30
7521 Ward Salesman 2011-02-22 12500 30
7902 Ford Analyst 2015-12-03 30000 NULL
7369 Smith Clerk 2010-10-07 8000 NULL
7788 Scott Analyst 2013-07-15 30000 20
7876 Adams Clerk 2012-03-01 11000 20
7934 Miller Clerk 2013-08-17 13000 NULL

1. Write a query to display Name, Sal, Empno and Job from table Emp.
SELECT ename, sal, empno, job
FROM Emp;

2. Write a query to display different jobs from table Emp.

SELECT DISTINCT job
FROM Emp;

3. Write a query to display all the employees who are getting salary more than 20000.
SELECT *
FROM Emp
WHERE sal > 20000;

4. Write a query to display all the employees who are not Managers.
SELECT *
FROM Emp
WHERE job != ‘MANAGER’;

5. Write a query to display Name, Job and Salary of all the Clerks who are getting salary
less than 20000.
SELECT ename, job, sal
FROM Emp
WHERE sal < 20000 AND job = ‘CLERK’;

1
6. Write a query to display all the employees who are either from Deptno 10 or 20.
SELECT *
FROM Emp
WHERE deptno = 10 OR deptno = 20;

7. Write a query to list Name and Salary of all the employees who are getting salary
between 20000 and 35000.
SELECT ename, sal
FROM Emp
WHERE sal BETWEEN 20000 AND 35000;

8. Write a query to display all the employees who are either Managers, Clerks, Salesman
or Analysts.
SELECT *
FROM Emp
WHERE job IN (‘SALESMAN’ , ‘CLERK’ , ‘MANAGER’ ,
‘ANALYST’);

9. Write a query to display all the employees getting salary other than 10000, 20000,
30000, 40000 and 50000.
SELECT *
FROM Emp
WHERE sal NOT IN (10000 , 20000 , 30000 , 40000 , 50000);

10. Write a query to display all the employees whose names starts with alphabet M.
SELECT ename
FROM Emp
WHERE ename LIKE “M%”;

11. Write a query to display all the employees not having alphabet ‘a’ as second character
in their name.
SELECT ename
FROM Emp
WHERE ename NOT LIKE “_a%” ;

12. Write a query to display all the employees whose having NULL value in DeptNo
column.
SELECT *
FROM Emp
WHERE DeptNo IS NULL;

13. Write a query to display all the records in the alphabetical order of name.
SELECT *
FROM Emp
ORDER BY ename ASC;

2
14. Write a query to display employee Name, Job and Salary of Managers and Clerks in
the order of their salary from highest to lowest.
SELECT ename, job, salary
FROM Emp
WHERE job = ‘MANAGER’ OR job = ‘CLERK’
ORDER BY salary DESC;

15. Write a query to display Name and New Salary of employees. New Salary is Salary +
10% of Salary.
SELECT ename, sal + sal*10/100 AS “New Salary”
FROM Emp;

16. Write a query to display names of all the employees in uppercase alphabet and
number of characters in their name.

SELECT UPPER(ename), LENGTH(ename)

FROM Emp;

17. Write a query to display names of all the employees and first four characters of their
Job.

SELECT ename, LEFT(ename , 4)

FROM Emp;

18. Write a query to display name, job and hiredate of all the employees who have joined
in year 2012.

SELECT ename, job, hiredate

FROM Emp
WHERE YEAR(hiredate) = 2012;

19. Write a query to display value 294581.763 rounded up in thousands.

SELECT ROUND ( 294581.763 , -3 ) ;

20. Write a query to display average salary of all the employees.

SELECT AVG(sal)
FROM Emp;

21. Write a query to display Minimum salary drawn by a Manager.

SELECT MIN(sal)
FROM Emp
WHERE job = ‘MANAGER’;

3
Grouping Result: (GROUP BY clause)

22. Write a query to display number of employees in each Job.

SELECT job, COUNT(job)
FROM Emp
GROUP BY job;

23. Write a query to display Total Salary paid to the employees of each department.
SELECT deptno, SUM(sal)
FROM Emp
GROUP BY deptno;

Placing Conditions on Groups (HAVING clause)

24. Write a query to display average salary of Managers.

SELECT job, AVG(sal)
FROM Emp
GROUP BY job
HAVING job = ‘MANAGER’;

25. Write a query to display all the jobs where the number of employees are less than 3.
SELECT job, COUNT(*)
FROM Emp
GROUP BY job
HAVING COUNT(*) < 3;

----- X -----