You are currently learning SQL and looking for some practice SQL Queries for Practice for employee table. To get you going with practicing SQL in local database on your computer, we have put together list of 200 SQL Queries for Practice in your local environment.

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

51

ANS

Display the name of emp who earns highest sal.

select ename from emp where sal=(select max(sal) from emp);

52

ANS

Display the employee number and name of employee working as CLERK and earning highest salary among CLERKS.

select empno, ename from emp where job='CLERK' and sal=(select max(sal) from emp where job='CLERK');

53

ANS

Display the names of the salesman who earns a salary more than the highest salary of any clerk.

select ename from emp where job=’SALESMAN’ and sal > (select max(sal) from emp where job='CLERK');

54

ANS

Display the names of clerks who earn salary more than that of James of that of sal lesser than that of Scott

select ename from emp where job='CLERK' and sal(select sal from emp where ename='JAMES');

55

ANS

Display the names of employees who earn a Sal more than that of James or that of salary greater than that of Scott.

select ename from emp where sal (select sal from emp where ename='JAMES');

56

ANS

Display the names of the employees who earn highest salary in their respective departments.

select * from emp e where sal = (select max(sal) from emp where deptno=e.deptno)

57

ANS

Display the names of employees who earn highest salaries in their respective job groups.

select * from emp e where sal in (select max(sal) from emp group by job having e.job=job)

58

ANS

Display the employee names who are working in accounting dept.

select ename from emp where deptno = (select deptno from dept where dname=”ACCOUNTING”);

59

ANS

Display the employee names who are working in Chicago.

select ename from emp where deptno = (select deptno from dept where loc=’CHICAGO’);

60

ANS

Display the job groups having total salary greater then the maximum salary for managers.

select job, sum(sal) from emp group by job having sum(sal) > (select max(sal) from emp where job='MANAGER');

61

ANS

Display the names of employees from department number 10 with salary greater than that of any employee working in other departments.

select ename,sal,deptno from emp e where deptno=10 and sal > any(select sal from emp where e.deptno!=deptno);

62

ANS

Display the names of employee from department number 10 with salary greater then that of all employee working in other departments.

select ename, sal, deptno from emp e where deptno=10 and sal > any(select sal from emp where e.deptno != deptno);

63

ANS

Display the names of employees in Upper case.

select upper(ename) from emp;

64

ANS

Display the names of employees in lower case.

select lower(ename) from emp;

65

ANS

Display the name of employees in proper case.

select initcap(ename) from emp;



66

ANS

Find out the length of your name using appropriate function.

select length(‘India’) from dual;

67

ANS

Display the length of all employees’ names.

select sum(length(ename)) from emp;

68

ANS

Display the name of the employee concatenate with EMP no.

select ename||empno from emp;

69

ANS

Use appropriate function and extract 3 characters starting from 2 characters from the following string ‘Oracle’ i.e. the output should be ‘rac’.

select substr(‘oracle’,2,3) from dual;

70

ANS

Find the first occurrence of character a from the following string ‘computer maintenance corporation’.

select instr(‘computer maintenance corporation’,’a’,1,1) from dual;

71

ANS

Replace every occurrence of alphabet A with B in the string Allen’s (user translate function).

select replace('Allens','A','b') from dual;

72

ANS

Display the information from EMP table. Wherever job ‘manager’ is found it should be displayed as boss(replace function)

select empno, ename, replace(job, 'MANAGER', 'Boss') JOB from emp;

73

ANS

Display empno, ename, deptno from EMP table. Instead of display department numbers display the related department name (use decode function).

select e.empno, e.ename, d.dname from emp e,dept d where e.deptno = d.deptno;

74

ANS

Display your age in days.

select round(sysdate-to_date('15-aug-1947')) from dual;

75

ANS

Display your age in months.

select floor(months_between(sysdate,'15-aug-1947')) "age in months" from dual;

76

ANS

Display current date as 15th august Friday nineteen forty seven.

select to_char(sysdate,'ddth month day year') from dual;

77

ANS

Display the following output for each row from EMP table as ‘scott has joined the company on Wednesday 13th august nineteen ninety’.

select ename||' has joined the company on '||to_char(hiredate,'day ddth month year') from emp;

78

ANS

Find the date of nearest Saturday after current day.

select next_day(sysdate, 'SATURDAY') from dual;

79

ANS

Display current time.

select to_char(sysdate,'hh:mi:ss') Time from dual;

80

ANS

80. Display the date three months before the current date

select add_months(sysdate,-3) from dual;

81

ANS

Display the common jobs from department number 10 and 20.

select job from emp where deptno=10 and job in(select job from emp where deptno=20);

82

ANS

Display the jobs found in department number 10 and 20 eliminate duplicate jobs.

select distinct(job) from emp where deptno=10 and job in(select job from emp where deptno=20);

83

ANS

Display the jobs which are unique to dept no 10.

select job from emp where deptno=10 minus select job from emp where deptno!=10;

84

ANS

Display the details of those who do not have any person working under them.

select empno from emp where empno not in (select mgr from emp where mgr is not null);

85

ANS

Display the details of employees who are in sales dept and grade is 3.

select * from emp where sal>=(select losal from salgrade where grade=3) and sal

86

ANS

Display those who are not managers and who are managers any one.

select * from emp where empno in(select mgr from emp) union select * from emp where empno not in(select mgr from emp where mgr is not null);

87

ANS

Display those employees whose name contains not less than 4 chars.

Select * from emp where length(ename)>4;

88

ANS

Display those departments whose name start with ‘S’ while location name end with ‘O’.

select * from dept where dname like 'S%' and loc like '%O';

89

ANS

Display those employees whose manager name is JONES.

select * from emp where mgr=(select empno from emp where ename='JONES');

90

ANS

Display those employees whose salary is more than 3000 after giving 20% increment.

select * from emp where sal+sal*20/100 > 3000;

91

ANS

Display all employees with there dept name.

select ename, dname from emp e, dept d where e.deptno = d.deptno;

92

ANS

Display ename who are working in sales dept.

select empno, ename from emp where deptno=(select deptno from dept where dname='SALES');

93

ANS

Display employee name, deptname, salary and comm. for those Sal in between 2000 to 5000 while location is Chicago.

select empno,ename,deptno from emp where deptno=(select deptno from dept where loc='CHICAGO') and sal between 2000 and 5000;

94

ANS

Display those employees whose salary greater than his manager salary.

select * from emp e where sal>(select sal from emp where empno=e.mgr);

95

ANS

Display those employees who are working in the same dept where his manager is working.

select * from emp e where deptno = (select deptno from emp where empno=e.mgr);

96

ANS

Display those employees who are not working under any manger.

select * from emp where mgr is null or empno=mgr;

97

ANS

Display grade and employees name for the dept no 10 or 30 but grade is not 4, while joined the company before 31-dec-82.

select empno,ename,sal,deptno,hiredate,grade from emp e,salgrade s where e.sal>=s.losal and e.sal4 and hiredate

98

ANS

Update the salary of each employee by 10% increments that are not eligible for commission.

update emp set sal=sal+(sal*10/100) where comm is null;

99

ANS

Delete those employees who joined the company before 31-dec-82 while there dept location is ‘NEW YORK’ or ‘CHICAGO’.

delete from emp where hiredate

100

ANS

Display employee name, job, deptname, location for all who are working as managers.

select ename,job,dname,loc from emp e, dept d where e.deptno=d.deptno and empno in (select mgr from emp);

TAGS

http://techhowdy.com/wp-content/uploads/2018/05/200-SQL-Queries-for-Practice-before-Interview-2-1024x725.pnghttp://techhowdy.com/wp-content/uploads/2018/05/200-SQL-Queries-for-Practice-before-Interview-2-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 for employee table. To get you going with practicing SQL in local database on your computer, we have put together list of 200 SQL Queries for Practice in your local environment.Since we don’t want to make...Latest technology news