List of 200 SQL Queries for Practice Before Interview
You are currently learning SQL and looking for some practice SQL Queries for Practice Before Interview. To get you going with practicing SQL in local database on your computer, we have put together list of 200 SQL and PLSQL Queries for Practice.
Since we don’t want to make each post on SQL Queries for Practice very length, we will be dividing this list of SQL Question and Answers for Practice in 4 different Parts.
Each part will contain list of 50 SQL Queries for Practice. Answers for these Practice SQL Queries will be provided, but my suggestion is try it on your own first and then see the answer for SQL questions. If you do not have any sample data to practice SQL Queries, you can read this post by me on How to Sample Oracle Database Tables for PLSQL Queries Practice
1
ANS
Display the dept information from department table.
select * from dept;
2
ANS
Display the details of all employees.
select * from emp;
3
ANS
Display the name and job for all employees.
select ename, job from emp;
4
ANS
Display name and salary for all employees
select ename, sal from emp;
5
ANS
Display employee number and total salary for each employee
select empno, sal+comm from emp;
ALSO READ
6
ANS
Display employee name and annual salary for all employees.
select empno, empname, 12*sal+nvl(comm,0) annualsal from emp;
7
ANS
Display the names of all employees who are working in department number 10
select ename from emp where deptno=10;
8
ANS
Display the names of all employees working as clerks and drawing a salarymore than 3000.
select ename from emp where job=’CLERK’ and sal>3000;
9
ANS
Display employee number and names for employees who earn commission.
select empno, ename from emp where comm is not null and comm>0;
10
ANS
Display names of employees who do not earn any commission
Select empno, ename from emp where comm is null and comm=0;
11
ANS
Display the names of employees who are working as clerk, salesman or analystand drawing a salary more than 3000
select ename from emp where (job=’CLERK’ or job=’SALESMAN’ or job=’ANALYST’) and sal>3000;
12
ANS
Display the names of employees who are working in the company for the past 5 years.
select ename from emp where sysdate-hiredate>5*365;
13
ANS
Display the list of employees who have joined the company before 30 th June 90or after 31 st dec 90
select * from emp where hiredate between ‘30-jun-1990’ and ‘31-dec-1990’;
14
ANS
Display current date
select sysdate from dual;
15
ANS
Display the list of users in your database (using log table)
select * from dba_users;
16
ANS
Display the names of all tables from the current user
select * from tab;
17
ANS
Display the name of the current user.
show user;
18
ANS
Display the names of employees working in department number 10 or 20 or 40 or employees working as clerks, salesman or analyst.
select ename from emp where deptno in (10,20,40) or job in(‘CLERK’,’SALESMAN’,’ANALYST’);
19
ANS
Display the names of employees whose name starts with alphabet S.
select ename from emp where ename like ‘S%’;
20
ANS
Display employee names for employees whose name ends with alphabet.
select ename from emp where ename like ‘%S’;
21
ANS
Display the names of employees whose names have second alphabet A in their names.
select ename from emp where ename like ‘_S%’;
22
ANS
Display the names of employees whose name is exactly five characters in length
select ename from emp where length(ename)=5;
23
ANS
Display the names of employees who are not working as managers
select * from emp minus (select * from emp where empno in (selectmgr from emp));
24
ANS
Display the names of employees who are not working as SALESMAN or CLERK or ANALYST
select ename from emp where job not in(‘CLERK’,’SALESMAN’,’ANALYST’);
25
ANS
Display all rows from EMP table. The system should wait after every screen full of information
set pause on;
26
ANS
Display the total number of employees working in the company
select count(*) from emp;
27
ANS
Display the total salary being paid to all employees
select sum(sal)+sum(nvl(comm,0)) from emp;
28
ANS
Display the maximum salary from emp table
select max(sal) from emp;
29
ANS
Display the minimum salary from emp table
select min(sal) from emp;
30
ANS
Display the average salary from emp table
select avg(sal) from emp;
31
ANS
Display the maximum salary being paid to CLERK
select max(sal) from emp where job=’CLERK’;
32
ANS
Display the maximum salary being paid in dept no 20
select max(sal) from emp where deptno=20;
33
ANS
Display the min Sal being paid to any SALESMAN
select min(sal) from emp where job=’SALESMAN’;
34
ANS
Display the average salary drawn by managers
select avg(sal) from emp where job=’MANAGER’;
35
ANS
Display the total salary drawn by analyst working in dept no 40
select sum(sal)+sum(nvl(comm,0)) from emp where deptno=40;
36
ANS
Display the names of employees in order of salary i.e. the name of the employee earning lowest salary should appear first
select ename from emp order by sal;
37
ANS
Display the names of employees in descending order of salary
select ename from emp order by sal desc;
38
ANS
Display the details from emp table in order of emp name
select ename from emp order by ename;
39
ANS
Display empno, ename, deptno, and sal. Sort the output first based on nameand within name by deptno and within deptno by Sal
select * from emp order by ename,deptno,sal;
40
ANS
Display the name of the employee along with their annual salary (Sal * 12).The name of the employee earning highest annual salary should appear first
select ename, 12*(sal+nvl(comm,0)) Annual from emp order by12*(sal+nvl(comm,0)) desc;
41
ANS
Display name, Sal, hra, pf, da, total Sal for each employee. The output should be in the order of total Sal, hra 15% of Sal, da 10% of sal, pf 5% of sal total salary will be (sal*hra*da)-pf
select ename,sal,sal*15/100 HRA, sal*5/100 PF, sal*10/100DA,sal+sal*15/100-sal*5/100+sal*10/100 TOTAL_SALARY from emp
42
ANS
Display dept numbers and total number of employees within each group
select deptno,count(*) from emp group by deptno;
43
ANS
Display the various jobs and total number of employees with each job group
select job, count(*) from emp group by job;
44
ANS
Display department numbers and total salary for each department
select deptno, sum(sal) from emp group by deptno;
45
ANS
Display department numbers and maximum salary for each department
select deptno, max(sal),min(sal) from emp group by deptno;
46
ANS
Display the various jobs and total salary for each job
select job, sum(sal) from emp group by job;
47
ANS
Display each job along with minimum sal being paid in each job group
select job, min(sal) from emp group by job;
48
ANS
Display the department numbers with more than three employees in each dept
select deptno, count(*) from emp group by deptno having count(*)>3;
49
ANS
Display the various jobs along with total sal for each of the jobs where total salis greater than 40000
select job, sum(sal) from emp group by job having sum(sal)>40000;
50
ANS
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
select job, count(*) from emp group by job having count(*)>3;
ALSO READ
List of 200 SQL Queries for Practice Before Interview Part -1
TAGS
