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;

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;

TAGS

http://techhowdy.com/wp-content/uploads/2018/05/List-of-200-SQL-Queries-for-Practice-before-Interview-1024x725.pnghttp://techhowdy.com/wp-content/uploads/2018/05/List-of-200-SQL-Queries-for-Practice-before-Interview-150x150.pngDemonDatabase Programming200 sql queries for practice,200 sql queries for practice in oracle,200 sql queries for practice on emp table,200 sql queries for practice online,200 sql queries for practice with answers,200 sql queries for practice with answers doc,200 sql queries for practice with answers free download,200 sql queries for practice with answers online,200 sql queries for practice with answers pdf,200 sql queries for practice with answers ppt,advanced sql queries for practice pdf,advanced sql queries practice online,basic sql queries for practice,basic sql queries practice questions,best book for sql queries practice,complex sql queries examples for practice with answers,complex sql queries for practice,complex sql queries for practice in oracle,complex sql queries for practice with answers,complex sql server queries for practice,complex sql server queries for practice with answers,db2 sql queries for practice,different sql queries for practice,difficult sql queries for practice,download sql queries practice questions,list of sql queries for practice,ms sql queries for practice with answers pdf,online sql query practice tool,oracle sql queries for practice with answers,oracle sql queries for practice with answers pdf oracle sql queries for practice pdf,oracle sql queries practice questions,oracle sql query practice exercises sql server queries examples for practice,pl sql queries for practice,pl sql queries for practice pdf,pl sql queries for practice with answers pdf,practice mysql queries,queries for practice in sql,sample sql queries for practice with answers pdf,simple sql queries for practice,solve sql queries for practice,sql command line practice,sql command practice online,sql join queries for practice,sql join queries for practice with answers,sql queries for practice in sql server,sql queries for practice pdf,sql queries online practice test,sql queries pdf for practice with answers free download,sql queries practice book,sql queries practice book pdf,sql queries practice examples,sql queries practice exercises,sql queries practice exercises pdf,sql queries practice for interview,sql queries practice questions,sql queries practice questions online,sql queries practice questions with answers,sql queries practice questions with answers pdf,sql queries practice quiz,sql queries practice set,sql queries practice test,sql queries to practice,sql queries to practice on oracle,sql query best practice,sql query best practices for performance sql queries best practices pdf,sql query examples for practice pdf,sql query good practices,sql query performance best practice,sql query practice database,sql query practice editor,sql query practice for beginners,sql query practice problems,sql query practice sites,sql query practice software,sql query practice tool,sql server 2015 queries for practice pdf,sql server 2017 queries for practice,sql server 2018 queries for practice,sql server queries for practice pdf,sql server queries for practice with answers,sql server queries for practice with answers pdf,sql server queries practice exercises,sql syntax best practice,sql tables and queries for practice,t sql queries practice,t-sql query best practicesYou 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...Latest technology news