Oracle DB Queries

The table script and data script for employee, dept, salgarade is available at DB Script

1.Display the dept information from department table

A: Select * from dept

2.Display the details of all employees

A: Select * from emp

3.Display the name and job for all employees

A: Select ename, job from emp

4.Display name and salary for all employees

A: Select ename, sal from emp

5.Display employee number and total salary for each employee

A: Select empno, sal+nvl(comm, 0) from emp

6.Display employee name and annual salary for each employee

A: Select ename, 12*sal as annualsal from emp

7.Display the names of all employees who are working in department number 10

A: Select ename from emp where deptno=10

8.Display the names of all employees working as clerks and drawing a salary more than 1000

A: Select ename from emp where job='CLERK' and sal > 1000

9.Display employee numbers and names for employees who earn commission

A: Select empno, ename from emp where comm is not null

10.Display names of employees who don't earn any commission

A: Select ename from emp where comm is null

11.Display the names of employees who are working as clerk, salesman or analyst and drawing a salary more than 2000

A: Select ename from emp where job in ('CLERK','SALESMAN','ANALYST') and sal>2000

12.Display the names of employees who are working in the company for the past 30 years

A: Select ename from emp where (months_between(sysdate, hiredate)/12)>30

13.Display the list of employees who have joined the company before 30th June 90 or after 31st Dec 90

A: Select ename from emp where (hiredate < '30-Jun-90') or hiredate > '31-JUN-90'

14.Display current date

A: Select sysdate from dual

15.Display the names of employees working in department number 10 or 20 or 40 or employees working as clerks, salesman or analyst

A: Select ename from emp where deptno in (10,20,40) or job in('CLERK','SALESMAN','ANALYST')

16.Display the names of employees whose names start with alphabet 'S'

A: Select ename from emp where ename like 'S%'

17.Display employee names for employees whose name ends with 'S'

A: Select ename from emp where ename like '%S'

18.Display the names of employees whose names have second alphabet as 'a' in their names

A: Select ename from emp where ename like '_A%'

19.Display the names of employees whose name is exactly five characters in length

A: Select ename from emp where length(ename)=5
OR
A: Select ename from emp where ename like '_____'

20.Display the names of employees who are not working as managers

A: Select * from emp where job <> 'MANAGER'
OR
A: Select * from emp where job != 'MANAGER'

21.Display the names of employees who are not working as salesman or clerk or analyst

A: Select * from emp where job not in ( 'SALESMAN','ANALYST','CLERK')

22.Display the total number of employees working in the comany

A: Select count(*) from emp

23.Display the total salary being paid to all employees

A: Select sum(sal)+sum(nvl(comm,0)) totalSAL from emp

24.Display the maximum salary from emp table

A: Select max(sal) from emp

25.Display the minimum salary from emp table

A: Select min(sal) from emp

26.Display the average salary from emp table

A: Select avg(sal) from emp

27.Display the maximum salary being paid to clerk

A: Select max(sal) from emp where job=’CLERK’

28.Display the maximum salary being paid in dept no 20

A: Select max(sal) from emp where deptno=20

29.Display the minimum sal being paid to any salesman

A: Select min(sal) from emp where job=’SALESMAN’

30.Display the average salary drawn by managers

A: Select avg(sal) from emp where job=’MANAGER’

31.Display the total salary drawn by analyst working in dept no 20

A: Select sum(sal) from emp where deptno=20 and job='ANALYST'

32.Display the names of employees in order of salary i.e the name of the employee earning lowest salary should appear first

A: Select ename from emp order by sal

33.Display the names of employees in descending order of salary

A: Select ename from emp order by sal desc

34.Display the details from emp table in order of emp name

A: Select * from emp order by ename

35.Display empno, ename, deptno, and sal. Sort the output first based on name and within name by deptno and within deptno by sal

A: Select empno,ename,deptno,sal from emp order by ename,deptno,sal

36.Display the name of the employee along with their annual salary (sal * 12). The name of the employee

A: Select ename, (sal*12) from emp

37.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

A: Select ename,(sal*15)/100 hra,(sal*10)/100 da,(sal*5)/100 pf, (sal*(sal*0.15)*(sal*0.10))-sal*0.05 totalsalary from emp

38.Display dept numbers and tot num of employees within each group

A: Select DEPTNO,count(DEPTNO) from emp group by deptno

39.Display various jobs abs tot number of employees with each group

A: Select job,count(job) from emp group by job

40.Display dept numbers and tot sal for for each dept

A: Select deptno, sum(sal) totalsal from emp group by deptno

41.Display deptno and max sal for each dept

A: Select deptno, max(sal) maxsal from emp group by deptno

42.Display various jobs and tot sal for each group

A: Select job, sum(sal) totalsal from emp group by job

43.Display each job group along with with min sal being paid in each job group

A: Select job, min(sal) minsal from emp group by job

44.Display deptno with more than 3 employees in each dept

A: Select deptno, count(deptno) from emp group by deptno having count(deptno)>3

45.Display various jobs along with tot sal for each for each of jobs where tot sal is greater than 8000

A: Select job, sum(sal) totalsal from emp group by job having sum(sal)>8000

46.Display various jobs along with total number of employees in each group. The output should contain only those jobs with more than 3 employees

A: Select job, count(job) from emp group by job having count(job)>3

47.Display name of emp who earns highest sal

A: Select ename from emp where sal = (select max(sal) from emp)

48.Display employee’s number and name of employee working as ‘clerk’ and earning highest salary among ‘clerks’

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

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

A: Select empno,ename from emp where job='SALESMAN' and sal=(select max(sal) from emp where job='CLERK')

50.Display names of the clerks who earn salary more than that of ‘james ’ of that of sal and lesser than that of ‘scott’

A: Select empno,ename from emp where job='CLERK' and sal>(select sal from emp where ename='JAMES') and sal<(select sal from emp where ename='SCOTT')

51.Display names of employees who earn a sal more than that of ‘james’ or that of salary greater than that of scott

A: Select ename from emp where sal>(select sal from emp where ename='JAMES') or sal<(select sal from emp where ename='SCOTT')

52.Display names of employees who earn highest sal in their respective departments

A: Select ename from emp e where sal=(select max(sal) from emp where deptno= e.deptno)

53.Display names of employees who earn highest sal in their respective job groups

A: Select ename from emp e where sal=(select max(sal) from emp where job= e.job)

54.Display employee names who are working in accountings departments

A: Select ename from emp where deptno=(select deptno from dept where dname='ACCOUNTING')

55.Display employee names who are working in ‘Chicago’

A: Select ename from emp where deptno=(select deptno from dept where loc='CHICAGO')

56.Display job groups having total sal greater than the maximum salary of managers

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

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

A: Select ename, sal from emp WHERE deptno=10 and sal=(select max(sal) from emp)

58.Display the names of employees from department 10 with sal greater than that of all employees working in other departments

A: Select ename, sal from emp where deptno = 10 and sal > all(select sal from emp where deptno !=10)

59.Display the names of employees in upper case

A: Select upper(ename) from emp

60.Display the names of employees in lower case

A: Select lower(ename) from emp

61.Display the names of employees in proper case

A: Select initcap(ename) from emp

62.Find out the length of your name using appropriate function

A: Select length('naga') from dual

63.Display the length of all employees’ names

A: Select ename,length(ename) from emp

64.Display the name of the employee concatenate with employee no

A: Select concat(empno, ename) from emp

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

A: Select substr('oracle',2,3) from dual

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

A: Select instr('computer maintenance corporaton','a',1,1) from dual

67.Replace every occurrence of alphabet ‘a’ with ‘b’ in the string allen’s (user translate function)

A: Select replace('allen’s','a','b') FROM dual

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

A: Select ENAME, replace('BOSS','MANAGER') FROM emp where job='MANAGER'

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

A: Select empno,ename,decode(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES') from emp

70.Display your age in days

A: Select months_between(sysdate,'15-june-1985')*30 from dual

71.Display your age in months

A: Select months_between(sysdate,'15-june-1985') from dual

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

A: Select replace('15-aug-1947',sysdate) from dual

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

A: Select ename || 'has joined the company on' || to_char(hiredate,'ddth month day year') FROM emp

74.Find the date of nearest Saturday after current day

A: Select next_day(sysdate,'saturday') from dual

75.Display the current time

A: Select to_char(sysdate,'hh:mi:ss') from dual

76.Display the date three months before the current date

A: Select sysdate-90 FROM dual

77.Display the common jobs from department number 10 and 20

A: Select job from emp where deptno=20 INTERSECT select job from emp where deptno=10

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

A: Select DISTINCT job from emp where deptno in(10,20)

79.Display the jobs, which are unique to department number 10

A: Select job FROM emp where job not in (select job from emp WHERE deptno in (20,30,40))
OR
A: Select job FROM emp WHERE deptno=10 and job not in (SELECT job FROM emp where deptno <> 10)

80.Display the details of those who don’t have any person working under them

A: Select * from emp where empno not in (select empno FROM emp where empno in (select mgr FROM emp))
OR
A: Select * from emp where empno not in (Select DISTINCT mgr from emp where mgr is not null)

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

A: Select ename,dname,grade from emp,dept,salgrade where emp.deptno=dept.deptno and dname='SALES' and sal between losal and hisal and grade=3

82.Display those who are not managers and who are managers to anyone

A: Select* from emp where empno in (select mgr from emp)) INTERSECT (select * from emp where empno not in (select empno from emp where empno in (select mgr from emp))

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

A: Select * FROM emp where length(ename) > 4

84.Display those departments whose name start with‘s’ while location name end with ‘o’

A: Select * FROM dept where dname like 'S%' and loc like '%O'

85.Display those employees whose manager name is ‘jones’

A: Select * from emp where mgr in (select empno from emp WHERE ename='JONES')

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

A: Select ename from emp where sal+(sal*0.2) > 3000

87.Display all employees with their dept name

A: Select e.ename, d.dname from emp e, dept d where e.deptno= d.deptno

88.Display ename who are working in sales dept

A: Select e.ename from emp e, dept d where e.deptno= d.deptno and d.dname='SALES'

89.D89) Display employee name, deptname, salary and comm. For those sal in between 2000 to 5000 while location in Chicago

A: Select e.ename, d.dname,e.sal,e.comm from emp e, dept d where e.deptno= d.deptno and d.loc='CHICAGO' and e.sal between 2000 and 5000

90.Display those employees whose salary greater than his manager salary

A: Select * from emp e1 where e1.sal>(select e2.sal from emp e2 where e1.mgr= e2.empno)

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

A: Select * from emp e1 where e1.deptno=(select e2.deptno from emp e2 where e1.mgr= e2.empno)

92.Display those employees who are not working under any manager

A: Select ename from emp where mgr is null

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

A: Select grade,ename from emp, salgrade where grade!=4 and deptno in (10,30) and hiredate < to_date('31-dec-82') and sal BETWEEN losal and hisal

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

A: Update emp set sal=sal+sal*0.1 where comm is null

95.Display those employees who joined the company before 31-dec-82 while there dept location is ‘new york’ or ‘chicago’

A: Select * from emp e, dept d where e.hiredate<'31-dec-82' and e.deptno= d.deptno and (d.loc='NEWYORK' or d.loc='CHICAGO')

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

A: Select e.ename,e.job,d.dname,d.loc from emp e,dept d where e.deptno= d.deptno and e.job='MANAGER'

97.Display those employees whose manager’s name is jones, and also display their manager name

A: Select e.ename, e.mgr MGRNO, e1.ename MGRNAME from emp e, emp e1 where e.mgr=e1.empno and e1.ename='JONES'

98.Display name and salary of ford if his sal is equal to high sal of his grade

A: Select ename, sal, grade, hisal from emp, salgrade where ename='FORD' and sal=hisal and sal between losal and hisal

99.Display employee name, his job, his dept name, his manager name, his grade and make out of an under department wise

A: Select e.ename,e.job,d.dname, grade, e1.ename from emp e,emp e1,dept d, salgrade where e.deptno= d.deptno and e.mgr=e1.empno and e.sal between losal and hisal

100.List out all the employees name, job and salary grade and department name for everyone in the company except ‘clerk’. Sort on salary display the highest salary

A: Select ename,job,sal,grade from emp,dept,salgrade where emp.deptno= dept.deptno and sal between losal and hisal and job<>'CLERK' order by sal desc

101.Display employee name, his job and his manager. Display also employees who are without manager

A: Select e.ename,e.job,e1.ename MGRNAME from emp e, emp e1 where e.mgr=e1.empno(+)

102.Find out the top 5 earner of company

A: Select e.ename from emp e where 5>(select count(*) from emp e1 where e.sal>e1.sal)

103.Display the name of those employees who are getting highest salary

A: Select ename from emp where sal=(select max(sal) from emp)

Tags : Sample DB queries for practice. Oracle queries, employee, dept, salgrade tables, table scripts

MySql Employee Dept Scripts

Employee Table Script

CREATE TABLE emp (
empno decimal(4,0) NOT NULL,
ename varchar(10) default NULL,
job varchar(9) default NULL,
mgr decimal(4,0) default NULL,
hiredate date default NULL,
sal decimal(7,2) default NULL,
comm decimal(7,2) default NULL,
deptno decimal(2,0) default NULL
);


Employee Insert Scripts


INSERT INTO emp VALUES ('7369','SMITH','CLERK','7902','1980-12-17','800.00',NULL,'20');
INSERT INTO emp VALUES ('7499','ALLEN','SALESMAN','7698','1981-02-20','1600.00','300.00','30');
INSERT INTO emp VALUES ('7521','WARD','SALESMAN','7698','1981-02-22','1250.00','500.00','30');
INSERT INTO emp VALUES ('7566','JONES','MANAGER','7839','1981-04-02','2975.00',NULL,'20');
INSERT INTO emp VALUES ('7654','MARTIN','SALESMAN','7698','1981-09-28','1250.00','1400.00','30');
INSERT INTO emp VALUES ('7698','BLAKE','MANAGER','7839','1981-05-01','2850.00',NULL,'30');
INSERT INTO emp VALUES ('7782','CLARK','MANAGER','7839','1981-06-09','2450.00',NULL,'10');
INSERT INTO emp VALUES ('7788','SCOTT','ANALYST','7566','1982-12-09','3000.00',NULL,'20');
INSERT INTO emp VALUES ('7839','KING','PRESIDENT',NULL,'1981-11-17','5000.00',NULL,'10');
INSERT INTO emp VALUES ('7844','TURNER','SALESMAN','7698','1981-09-08','1500.00','0.00','30');
INSERT INTO emp VALUES ('7876','ADAMS','CLERK','7788','1983-01-12','1100.00',NULL,'20');
INSERT INTO emp VALUES ('7900','JAMES','CLERK','7698','1981-12-03','950.00',NULL,'30');
INSERT INTO emp VALUES ('7902','FORD','ANALYST','7566','1981-12-03','3000.00',NULL,'20');
INSERT INTO emp VALUES ('7934','MILLER','CLERK','7782','1982-01-23','1300.00',NULL,'10');
commit;


Department Table Script

CREATE TABLE dept (
deptno decimal(2,0) default NULL,
dname varchar(14) default NULL,
loc varchar(13) default NULL
);


Department Insert Scripts


INSERT INTO dept VALUES ('10','ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES ('20','RESEARCH','DALLAS');
INSERT INTO dept VALUES ('30','SALES','CHICAGO');
INSERT INTO dept VALUES ('40','OPERATIONS','BOSTON');
commit;


Tags:MySql Employee table,insert scripts, MySql Department table,insert scripts, MySql Salgrade table,insert scripts, MySql employee,dept create table scripts, MySql Employee,Dept insert scripts

Oracle Employee Dept Scripts

Employee Table Script

CREATE TABLE EMP
(EMPNO NUMERIC(4) NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR NUMERIC(4),
HIREDATE DATETIME,
SAL NUMERIC(7, 2),
COMM NUMERIC(7, 2),
DEPTNO NUMERIC(2));


Employee Insert Scripts


INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902, '17-DEC-1980', 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-1981', 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698, '22-FEB-1981', 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839, '2-APR-1981', 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-1981', 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839, '1-MAY-1981', 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839, '9-JUN-1981', 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL, '17-NOV-1981', 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698, '8-SEP-1981', 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788, '12-JAN-1983', 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698, '3-DEC-1981', 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566, '3-DEC-1981', 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782, '23-JAN-1982', 1300, NULL, 10);
commit;





Department Table Script

CREATE TABLE DEPT
(DEPTNO NUMERIC(2),
DNAME VARCHAR(14),
LOC VARCHAR(13) );


Department Insert Scripts


INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
commit;





Salgrade Table Script

CREATE TABLE SALGRADE
(GRADE NUMERIC,
LOSAL NUMERIC,
HISAL NUMERIC);


Salgrade Insert Scripts


INSERT INTO SALGRADE VALUES (1, 700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);
commit;



Tags:Oracle Employee table,insert scripts, Oracle Department table,insert scripts, Oracle Salgrade table,insert scripts, Oracle employee,dept create table scripts, Oracle Employee,Dept insert scripts