employee table basic queries sql - SQL Queries Practice - DBMS - Edumistic

              Lab Exercise 03 For Database Management Systems

Govt. College University Faisalabad (GCUF)

Dept. of Computer Science: Database Systems-Lab

Examples:

1. Select the last name of all employees, without duplicates and order by last name. 

2. Display the list of employees whose department id lies in 20 and 70 and salary is greater than 10000. 

3. Display the data of Employees whose name is Ali. 

4. Select the data of employees where commission is unknown. 

5. Select the data of employees where commission is known and salary lies in the range 12000 and 67000. 

6. Select the list of employees that work in department 90 and 100. 

7. Select the sum of all the employees Salary. 8. How many rows are there in employees table? 

9. Find the number of all the employees’ id in salary 4800 from the employees and display the column name as Total_EMPLOYEE_ID. 

10. Display the max, min and average salary of employees as max, min and avg. 

11. Calculate 2 multiplied by 6 and display its result. 

12. Calculate 144/7 and display its result as DIV.

 

13. Calculate 9*7/5+3-2 as Cal and display its result. 

14. Combine the first name and last name of employees and display it as Employee Name.

 

15. Display the list of employees where department id is not equal to 90. (Using < > operator). 

16. Display the name of Employee who has max salary. 

17. Display all columns where job is duplicated. 

18. How to display the names of employees who earn highest salaries in their respective job groups. 

19. Select list of Employees whose salaries are more than the average salary of employees. 

20. Display the data of Employees whose name starts with ‘A’. 

21. Display the data of Employees whose JOB ends with ‘MAN’.

 

22. Display the data of Employees whose name has ‘a’ on its second position like “James, Hassan”.

 

23. Display the names of Employees who have hiring date 09-May-81. 

24. Display Sal of Employees as Salary. 

25. Display the data of Employees who have same salary.

Solutions:

1. Select the last name of all employees, without duplicates and order by last name.

SELECT distinct Elast FROM EMPLOYEE GROUP BY Elast;

2. Display the list of employees whose department id lies in 20 and 70 and salary is greater than 10000.

 

SELECT * FROM EMPLOYEE WHERE dpt_id BETWEEN 20 AND 70 AND salary>10000;

3. Display the data of Employees whose name is Ali.

  

SELECT * FROM EMPLOYEE WHERE Fname= ‘Ali’;

4. Select the data of employees where commission is unknown.

 

SELECT * FROM EMPLOYEE WHERE COMISSION is null;

 

5. Select the data of employees where commission is known and salary lies in the range 12000 and 67000.

SELECT * FROM EMPLOYEE WHERE COMISSION is not null and SALARY between 12000 and 67000;

6. Select the list of employees that work in department 90 and 100.

SELECT * FROM EMPLOYEE WHERE DPT_ID in (90,100);

7. Select the sum of all the employees Salary.

SELECT sum (SALARY) FROM EMPLOYEE;

 

8. How many rows are there in employees table?

SELECT COUNT(*) FROM EMPLOYEE;

 

9. Find the number of all the employees’ id in salary 4800 from the employees and display the column name as Total_EMPLOYEE_ID.

SELECT COUNT(*) as “Total_EMPLOYEE_ID” FROM EMPLOYEE Where SALARY = 4800;

10. Display the max, min and average salary of employees as max, min and avg.

SELECT max(SALARY) as “Maximum”, min(SALARY) as “Minimum”, avg(SALARY) as “Average” FROM EMPLOYEE;

11. Calculate 2 multiplied by 6 and display its result.

 

SELECT 2*6 FROM DUAL;

 12. Calculate 144/7 and display its result as DIV.

SELECT 144/7 As  DIV;

13. Calculate 9*7/5+3-2 as Cal and display its result.

SELECT 9*7/5+3-2 as CAL;

14. Combine the first name and last name of employees and display it as Employee Name.

SELECT EMPLOYEE.Fname, as ‘First Name’, Lname as ‘Last Name’ Order by Employee Name;

15. Display the list of employees where department id is not equal to 90. (Using < > operator).

 

SELECT* From  EMPLOYEE Where DPT_ID <> 90;

 

 

 

 

 16. Display the name of Employee who has max salary.

 

 

SELECT Ename, salary From EMPLOYEE WHERE salary in (select max(Salary) from employee);

 17. Display all columns where job is duplicated. 

SELECT job COUNT(*) From EMPLOYEE GROUP BY job having COUNT(*)>1;

18. How to display the names of employees who earn highest salaries in their respective job groups.

SELECT Ename,salary,job From EMPLOYEE WHERE salary in (select max(Salary) from employee group by job);

 

 

 

 

 19. Select list of Employees whose salaries are more than the average salary of employees.

SELECT * From EMPLOYEE WHERE SALARY > avg(SALARY) ;

20. Display the data of Employees whose name starts with ‘A’.

 

SELECT From EMPLOYEE WHERE Ename like ‘A%’;

21. Display the data of Employees whose JOB ends with ‘MAN’.

 

SELECT * From EMPLOYEE WHERE job like ‘%man’;

 

 

 

22. Display the data of Employees whose name has ‘a’ on its second position like “James, Hassan”.

 

SELECT * From EMPLOYEE WHERE Ename like ‘_A%’ ;

23. Display the names of Employees who have hiring date 09-May-81.

 

SELECT ename From EMPLOYEE WHERE Hiring date = 09-May-81 ;

24. Display Sal of Employees as Salary.

 

SELECT  EMPLOYEE.SAL As ‘salary’;

25. Display the data of Employees who have same salary.

SELECT * FROM EMPLOYEE WHERE (SALARY IN (SELECT salary FROM employee GROUP BY salary HAVING COUNT(salary)>1);