SUB Queries:
1. List the employees working in research department 2. List employees who are located in New York and Chicago
3. Display the department name in which ANALYSTS are working
4. Display employees who are reporting to JONES
5. Display all the employees who are reporting to Jones Manager
6. Display all the managers in SALES and ACCOUNTING department
7. Display all the employee names in Research and Sales Department who are having at least 1 person reporting to them
8. Display all employees who do not have any reportees
9. List employees who are having at least 2 reporting
10. List the department names which are having more than 5 employees
11. List department name having at-least 3 salesman
12. List employees from research and accounting having at-least 2 reporting
13. Display second max salary
14. Display 4th max salary
15. Display 5th max salary -- Answer for nth Max Salary
Co-Related Subqueries:
16. Write a query to get 4th max salary from EMP table
17. Write a query to get 2nd…
1. List the employees working in research department 2. List employees who are located in New York and Chicago
3. Display the department name in which ANALYSTS are working
4. Display employees who are reporting to JONES
5. Display all the employees who are reporting to Jones Manager
6. Display all the managers in SALES and ACCOUNTING department
7. Display all the employee names in Research and Sales Department who are having at least 1 person reporting to them
8. Display all employees who do not have any reportees
9. List employees who are having at least 2 reporting
10. List the department names which are having more than 5 employees
11. List department name having at-least 3 salesman
12. List employees from research and accounting having at-least 2 reporting
13. Display second max salary
14. Display 4th max salary
15. Display 5th max salary -- Answer for nth Max Salary
Co-Related Subqueries:
16. Write a query to get 4th max salary from EMP table
17. Write a query to get 2nd…
Comments
Answers posted.
Regards,
Mithun
can you please explain thin ans. bec'z me getting confused by this Ans For the same Q.
19. List emp working as managers and clerks with Salary atleast 2000 except in dept 10 and 20
SQL> SELECT * FROM emp WHERE job = 'MANAGER' OR job = 'CLERK' AND sal >=2000 AND deptno NOT IN (10,20);
When I say working as manager and clerk, does not mean that an employee can work as both manager and clerk, my question is..
Listing employees who are either managers or clerks and whose salary is greater than or equal to 2000 and should not belong to department 20 or 30.
Hope this helps. Let me know incase if still have issues understanding this question.
Regards,
Mithun
Q. display all rows from emp table,the system should not wait after every screen full of information.
What should be the asswer of this Question?
I guess the question should be,
Q. Display all rows from emp table,the system should wait after every screen full of information.
You can make SQL*PLUS screen wait once the screen is full until you press enter key.
Try this,
SELECT * FROM USER_TABLES;
This will display the result within few seconds, here the display did not stop until you reached SQL command prompt.
Now try this,
SET PAUSE ON
SELECT * FROM USER_TABLES;
Now the screen gets paused everytime it gets full.
SET PAUSE OFF to return to default setting. SET PAUSE OFF is an SQL*PLUS command.
Hope this helps.
Thank you it's working for me. n thatks again now i got the sub quaries...
Answers to Sub queries posted.
Mithun
display dept_wise total salaries for all the managers and analysts,only if the average salaries for the same is greater than or equal to 3000.
deptno job sal
20 manager 2975
20 analyst 3000
20 analyst 3000
30 manager 2850
10 manager 2450
select deptno,sum(sal)
from emp
where job in ('MANAGER','ANALYST')
group by deptno,sal
having avg(sal)>=3000;
output is
deptno sum(sal)
20 6000
Actually i should not get output.pls correct the query
Here is the query,
select deptno,job, sum(sal)
from emp
where job in ('MANAGER','ANALYST')
group by job, deptno
having avg(sal) >=3000;
Mithun
using subquery:
select * from emp where sal>(
select avg(sal) from emp;
how to write the query using self joins.pls tell me solution.
pls send me answers for qtns which i have posted on 4th aug.on saturday i am attending mock.pls answer my qtns.
Sorry for the delayed response. It was quite hectic for last 1 week.
Am not sure if this is possible using joins nevertheless am trying to get an answer for your questions. Will post this as soon as I form an answer for this.
Regards,
Mithun
Thanks for ur message and the songs.... It feels good... :-)
Regards,
Mithun
Mithun
wat is d differnce between nvl and nvl2?
wat is d differnc between nvl d nvl2?m getting confused...
SI FIRST_NAME LASTNAME
1 hello how
2 how hello
then i m trying to insert the values
using this query
insert into customerr
(&SI,&LASTNAME,&FIRST_NAME)
VALUES('A113','MUDASEER','AHMED');
it displayed output as
Enter value for si:
Enter value for lastname:
Enter value for first_name:
it gives error can u plz tel me how
to insert into the table in such a way that i need not type insert into command again and again is it possible
update command?
select sal
from (select rownum as "hhh",sal
from (select sal from emp order by sal desc)
where rownum<=6)
order by sal desc
/
Refer to the link below for difference between NVL and NVL2.
NVLandNVL2
Regards,
Mithun
1. Your statement below is wrong,
insert into customerr
(&SI,&LASTNAME,&FIRST_NAME)
VALUES('A113','MUDASEER','AHMED');
Write it this way.
insert into customerr
(SI,LASTNAME,FIRST_NAME)
VALUES('&SI','&LASTNAME','&FIRST_NAME');
2. As far as I know you cannot use update statement to delete a row from a table.
3. In the query below,
select sal
from (select rownum as "hhh",sal
from (select sal from emp order by sal desc)
where rownum<=6)
order by sal desc
/
You are trying to use subqueries inside from. Consider each subquery as a table. This statement lists salaries in an order.
Regards,
Mithun
Your website gives us lots of info and questions to go thru wit.. Pls keep updating as we would be following it up very closely.
Thank You.
This is varada(back Up faculty QSP). Sir i have following doubts
1)How to create DB and User
2)How to give access to the User to create the table or a view
3)Write query to display employees who are getting salary more than there respective manager
from emp a,emp b
WHERE a.mgr=b.empno
AND a.sal>b.sal;
your website is awesome..............
Increase the salaries of all the employees by 10% for the departments that are common in both of these tables.
Table1
Deptno
Empno
Sal
10
1
1000
10
2
3000
20
3
2000
30
4
6000
20
5
1000
60
6
9000
10
7
1000
Table2
Deptno
10
40
i am not able to execute mutli-row funs in online database
its giving invalid character.
plz help me how to execute.
Sir my question is
Q)I want to retrieve only 100th no row from a table?
(i have 2 tables
1:student_info in college database
2:placement_info in placement database
regno is primary key in both the tables)
http://www.mithunashok.com/2014/07/finding-leap-year-using-sql.html
Please explain me steps to install oracle database 12c on my ubuntu 14.04
please sir plz help me
I want to know how to create the new connection in SQL Developer. I am getting some error messages while creating the new connection like 'IO error : The network adapter could not establish the connection'. Please help me out.