Skip to toolbar

Basic SQL Queries Updated (2020)

Here is the list of Basic SQL Queries for the beginners.

Simple Queries:

1. List all the employee details from the employee table

 Answer:  Select * from employee;

2. List all the department details from the department table

Answer: Select * from department;

3. List all job details from job table

Answer: Select * from job;

4. List all the locations from the location table

Answer: Select * from location;

5. List out first name, last name, salary, commission for all employees from the employee table

Answer Select first_name , last_name , salary, commission from employee;

6. List out employee, last name, department id for all employees and rename employee id as “ID of the employee”, last name as “Name of the employee”, department id as “department ID”

Answer: Select employee_id as empid last_name “L_name”, department id as “dept_Id” from employee;

7. List out the employee’s annual salary with their names only.

Answer: Select last_name, salary*12 “annual salary” from employee

Where Conditions:

8. List the details about “JOHN”

Select * from employee where last_name=’JOHN’;

9. List out the employees who are working in department 100

Select * from employee where department_id=100

10. List out the employees who are earning salary between 2000 and 3500

Select * from employee where salary between 2000 and 3500

11. List out the employees who are working in department 40 or 50

Select * from employee where department_id in (40,50)

12. Find out the employees who are not working in department 10 or 20

Select last_name, salary, commission, department_id from employee where department_id not in (10,20)

13. List out the employees whose name starts with “Z”

Select * from employee where last_name like ‘Z%’

14. List out the employees whose name start with “R” and end with “P”

Select * from employee where last_name like ‘R%P’

15. List out the employees whose name length is 4 and start with “B”

Select * from employee where last_name like ‘B___’

16. List out the employees who are working in department 50 and draw the salaries more than 30000

Select * from employee where department_id=50 and salary>30000

17. list out the employees from the employee table who are not receiving any commission.

Select * from employee where commission is Null

Order By Clause:

18. List out the employee id, employee last name in ascending order based on their ID.

Select employee_id, last_name from employee order by employee_id

19. List out the employee id, employee name in descending order based on salary

Select employee_id, last_name, salary from employee order by salary desc

20. list out the employee information based on their last_name in ascending order and salaries in descending order

Select employee_id, last_name, salary from employee order by last_name, salary desc

21. list out the employee information according to their last_name in ascending order and then on department_id in descending order.

Select employee_id, last_name, salary from employee order by last_name, department_id desc

Group By & Having Clause:

 22. List out the employees who are working in different departments wise in the organization

Select department_id, count(*), from employee group by department_id

23. Capture the department wise maximum salary, minimum salary, average salary of the employees from the employee table.

Select department_id, count(*), max(salary), min(salary), avg(salary) from employee group by department_id

24. List out the job wise maximum salary, minimum salary, average salaries of the employees from the employee table.

Select job_id, count(*), max(salary) As Max_Salary, min(salary) as Min_Salary, avg(salary) as AVG_Salary from employee group by job_id

25. List out the number of employees joined in every month in ascending order using employee table.

Select to_char(hire_date,’month’)month, count(*) from employee group by to_char(hire_date,’month’) order by month

26. List out the number of employees joined for each month and year, in the ascending order based on the year, month.

Select to_char(hire_date,’yyyy’) Year, to_char(hire_date,’mon’) Month, count(*) “No. of employees” from employee group by to_char(hire_date,’yyyy’), to_char(hire_date,’mon’)

27. List out the number of departments having at least five employees in that department .

Select department_id, count(*) from employee group by department_id having count(*)>=5

28. How many employees are joined in the month of March

Select to_char(hire_date,’mon’) month, count(*) from employee group by to_char(hire_date,’mon’) having to_char(hire_date,’mon’)=’Mar

29. Find out how many employees are joined in February or August month.

Select to_char(hire_date,’mon’) month, count(*) from employee group by to_char(hire_date,’mon’) having to_char(hire_date,’mon’) in (‘fed’,’aug’)

30. Find out how many employees are joined in the year 1995.

Select To_char(hire_date,’yyyy’) Year, count(*) from employee group by To_char(hire_date,’yyyy’) having To_char(hire_date,’yyyy’)=1995

Leave a comment