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