I often use the EMP and DEPT tables for test and demonstration purposes. Both these tables are owned by the SCOTT user, together with two less frequently used tables: BONUS and SALGRADE. Execute the below code snippets to create and seed the EMP and DEPT tables in your own schema. The BONUS and SALGRADE tables are included as well, but are commented out. The DDL (data definition language) part creates the tables, the DML (data manipulation language) part inserts the data.
DDL
create table dept( deptno number(2,0), dname varchar2(14), loc varchar2(13), constraint pk_dept primary key (deptno) ); create table emp( empno number(4,0), ename varchar2(10), job varchar2(9), mgr number(4,0), hiredate date, sal number(7,2), comm number(7,2), deptno number(2,0), constraint pk_emp primary key (empno), constraint fk_deptno foreign key (deptno) references dept (deptno) ); /* create table bonus( ename varchar2(10), job varchar2(9), sal number, comm number ); create table salgrade( grade number, losal number, hisal number ); */
DML
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');
insert into emp
values(
7839, 'KING', 'PRESIDENT', null,
to_date('17-11-1981','dd-mm-yyyy'),
5000, null, 10
);
insert into emp
values(
7698, 'BLAKE', 'MANAGER', 7839,
to_date('1-5-1981','dd-mm-yyyy'),
2850, null, 30
);
insert into emp
values(
7782, 'CLARK', 'MANAGER', 7839,
to_date('9-6-1981','dd-mm-yyyy'),
2450, null, 10
);
insert into emp
values(
7566, 'JONES', 'MANAGER', 7839,
to_date('2-4-1981','dd-mm-yyyy'),
2975, null, 20
);
insert into emp
values(
7788, 'SCOTT', 'ANALYST', 7566,
to_date('13-JUL-87','dd-mm-rr') - 85,
3000, null, 20
);
insert into emp
values(
7902, 'FORD', 'ANALYST', 7566,
to_date('3-12-1981','dd-mm-yyyy'),
3000, null, 20
);
insert into emp
values(
7369, 'SMITH', 'CLERK', 7902,
to_date('17-12-1980','dd-mm-yyyy'),
800, null, 20
);
insert into emp
values(
7499, 'ALLEN', 'SALESMAN', 7698,
to_date('20-2-1981','dd-mm-yyyy'),
1600, 300, 30
);
insert into emp
values(
7521, 'WARD', 'SALESMAN', 7698,
to_date('22-2-1981','dd-mm-yyyy'),
1250, 500, 30
);
insert into emp
values(
7654, 'MARTIN', 'SALESMAN', 7698,
to_date('28-9-1981','dd-mm-yyyy'),
1250, 1400, 30
);
insert into emp
values(
7844, 'TURNER', 'SALESMAN', 7698,
to_date('8-9-1981','dd-mm-yyyy'),
1500, 0, 30
);
insert into emp
values(
7876, 'ADAMS', 'CLERK', 7788,
to_date('13-JUL-87', 'dd-mm-rr') - 51,
1100, null, 20
);
insert into emp
values(
7900, 'JAMES', 'CLERK', 7698,
to_date('3-12-1981','dd-mm-yyyy'),
950, null, 30
);
insert into emp
values(
7934, 'MILLER', 'CLERK', 7782,
to_date('23-1-1982','dd-mm-yyyy'),
1300, null, 10
);
/*
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;
APEXPLAINED 
Might also be required, so the tables are associated to the right user:
DROP USER SCOTT CASCADE;
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger;
LikeLike
thats so nice
LikeLiked by 1 person
Help please
Q1.Use subquery to find employees in Department 10 of the same positions as in
department 30.
Q2. Use subquery to find employees in the department 10 that not have same positions as them in
department 30.
LikeLike
I feel like i’m doing your homework… that’s cool. Anyway, these are exercises on correlated subqueries. This is how I would solve it:
select emp1.* from emp emp1 where emp1.deptno = 10 and exists (select empno from emp emp2 where emp2.deptno = 30 and emp1.job = emp2.job); select emp1.* from emp emp1 where emp1.deptno = 10 and not exists (select empno from emp emp2 where emp2.deptno = 30 and emp1.job = emp2.job);LikeLiked by 1 person
Thanks man
LikeLike
Can u plz help me…
LikeLike
Sure, fire away.
LikeLike
1) select * from emp s where s.deptno=10 and exists (select job from emp m where m.deptno=30 and m.job=s.job);
2)select * from emp s where s.deptno=10 and not exists (select deptno from emp m where m.deptno=30 and m.job=s.job);
LikeLike
Thank’s for the tables..
LikeLike
Nick, thanks buddy….that was so nice of you to upload such a useful thing…..
LikeLike
select * from emp where deptno =10 and job in (select job from emp where deptno=30);
and
select * from emp where deptno =10 and job not in ( select job from emp where deptno =30);
Is it right way to write the Queary?
LikeLike
The right way to write the query? Depends of course on what’s being asked of you, but the queries are syntactically correct.
LikeLike
Both the queries are right (with “in” and “Exists”).
But the one with EXISTS is more optimized. Avoid using “in” for the sub-query.
LikeLike
Both queries are right (with “in” and “exists”)
but it is always better to use it with EXISTS as it is one of the good ways to optimize the output and one of the basic concepts of SQL tuning.
LikeLike
yes suraj your query is right and executed in less then 0.02 sec.
LikeLike
Thanks, Nick.
Actually, since the mgr column in EMP table points to the employee’s manager, who is also an employee, I would specify this reference in another FK constraint too:
constraint fk_mgr foreign key (mgr) references emp (empno)
LikeLike
1. List all the employees who have at least one person reporting to them
LikeLike
Thanks Nick.. :) for sharing such useful info..!!:) it helped me
LikeLike
bonus table data??
LikeLike
The BONUS table doesn’t contain any records initially.
LikeLike
Thank you soo much :-)
LikeLike
SUPERB YARRRRRR……THANK YOU SO MUCH….SUCH A BEAUTIFUL INFORMATION…
LikeLike
Thank u so much… :) :)
LikeLike
On the above example i want maximum and minimum salary of a employee in each department along with the name
LikeLike
select d.dname as department, min(sal) as minimum_salary, max(sal) as maximum_salary from emp e join dept d on e.deptno = d.deptno group by d.deptno, d.dname;LikeLike
i Have a table and need answers for those can any help me
Emp_Id Emp_Name Emp_Project Emp_Manager Designation
1112252 John Martin Wal-Mart Stacy Mangle Store Manager
1112253 David Hoff Whirlpool Victor Maze Finance Analyst
1112254 Stanley Lawson Papa Jones Gloria McFaul Logistics’ Analyst
1112255 Nancy Bridge Whirlpool Tara Wood Sr. Financial Analyst
1112256 Tom Herald Papa Jones Michael Bell Client Manager
1112257 Nicholas Jr. Lexmark Bill Faber Field Services Manager
1112258 Bryan Nickson Wal-Mart Ben Bernanke Billing Head
1.How do we retrieve the Employee Names from the Emp_Master Table?
Select * From Emp_Master:
Select Emp_Name IN Emp_Master:
Select Emp_Name from Emp_Master
Select All Emp_Name from Emp_Master
2.Get all the Employee data from the Whirlpool Project?
Select * IN Emp_Master WHERE Emp_Project = Whirlpool
Select All from Emp_Master WHERE Emp_Project = Whhirlpool
Select * from Emp_Master WHERE Emp_project = Whirlpool
Select * from Emp_Master WHERE Emp_Project Whirlpool
3.Retrieve All Manager Names from the Emp_Master Tables
Select Emp_Manager from Emp_Master WHERE Designation Like %Manager%
Select Emp_Manager WHERE Designation = “Manager”
Select Emp_Manager WHERE Designation IS Manager
Select Emp_Manager WHERE Designation = ?Manager’:
4.How many records are there in the Emp_master table with Wal-Mart Project?
Select SUM(Emp_Project) from Emp_Master WHERE Emp_Project = Wal-Mart
Select Count (Emp_Project) from Emp_Master WHERE Emp_Project LIKE-Wal-Mart
Select CountIF (Emp_Project) =Wal-Mart from Emp_Master
Select Count (Emp_Project) From Emp_Master WHERE Emp_Project ONLY ‘Wal-Mart’.
5. Retrieve the Project Names from Emp_Master Table With no Duplicate Values.
Select ONLY Emp_Project from Emp_Master
Select DISTINCT Emp_Project from Emp_Master
Select UNIQUE Emp_Project from Emp_Master
Select DISTINCTIVE Emp_Project from Emp_Master.
Can any1 help me in giving those answers please help me out my mail is ashwin.parab@gmail.com pls send the answers on my mail pls…..
LikeLiked by 1 person
display the list of all users in your database
LikeLike
display the names of all tables from current user;
LikeLike
thnx man
LikeLike
There are two table.EMP and dept
My requirement is one more column ‘deptname’ execute in following query .
How i can execute..give me feedback
“Select deptno,count(*) from EMP group by deptno;”
I need Output like :
Deptname. Deptno count(*)
——-–——— ————— ——————-
operation. 40. 0
Sales. 30 6
Research 20. 5
Accounting. 10. 3
LikeLike
Using a correlated subquery:
select dept.deptno, dept.dname, (select count(*) from emp where emp.deptno = dept.deptno) as employee_count from dept group by dept.deptno, dept.dname order by employee_count;LikeLike
thanks buddy to share valuable info.. keep share more tiips
LikeLike
thank u very much Nick
LikeLike
query: display department wise total salaries
LikeLike
LikeLike
good work..thank you!!!
LikeLike
Thanks for solve my problem….
LikeLike
display ename,dname,deptno from table
LikeLike
display ename,dname,deptno from table;
And display deptwise total number of emp;
LikeLike
select emp.empno,emp.ename,dept.dname,dept.deptno from emp left join dept on dept.deptno = emp.deptno;
LikeLike
probably iam missing the point but i dont see anything special about this approach?
LikeLike
i like all the commands too much
LikeLike
nice helpful our programs
LikeLike
Hai si i want sql and plsql questions sir plss send me
LikeLike
hai sir i want sql plsql interview questions sir plss send me
LikeLike
can we make relationship the table emp and Bonus in the scott chema?
LikeLike
may i know how to prints the even records in the table.
LikeLike
You can do something like this:
select * from ( select emp.*, rownum as row_id from emp order by empno ) where mod(row_id, 2) = 0;LikeLiked by 1 person
Thank you Nick, this is very helpful :-)
LikeLiked by 1 person
thankq so much soo helpful
LikeLike
Thanks Buddy for tables :)
LikeLike
can you post artical on unit testing ? please reply me to my message .
thank you
LikeLike
There you go:
I wrote that article years ago…
LikeLike
Superb dear Nick..! I got very useful information about SQL here… please share more…
LikeLike