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
Please find the links for all the presentations on SQL Basics.
http://www.mithunashok.com/2011/03/sql-basics-intro.html
http://www.mithunashok.com/2011/03/sql-basics-basic-select-statement.html
http://www.mithunashok.com/2011/03/sql-basics-restricting-and-sorting-data.html
http://www.mithunashok.com/2011/02/sql-basics-presentation.html
Regards,
Mithun
Thats a great news. CONGRATULATIONS!!!
Its all your hard work.
All the best for your future.
Best Regards,
Mithun
i got error while executing the nth maxsalary query...the query wat i executed is given below.i executed the query in sqlplus8.0....if i execute the 1st inner query its working but the 2nd one is not workin sir..find me the solution sir...
SQL> select salary from(select rownum ranking, sal salary from (select distinct(sal)
2 from emp where sal is not null order by sal desc)) where ranking = 3;
from emp where sal is not null order by sal desc)) where ranking = 3
*
ERROR at line 2:
ORA-00907: missing right parenthesis
Max salary query works fine. Let me know the db version you are trying to execute this.
Mithun
1)select length('salesman') from dual minus select length(replace('salesman','s','')) from dual
it is giving 8 as length.but it should be 2.is it not sir?my intension is to count the number of 's' es.
2)pls explain union,unionall,index,view with examples
how to use rank pls explain with an example
1. Do not use 'minus' clause to get the result that you are expecting. MINUS clause is used as one of the set operators check my presentation Link
To get the answer you need use arithmetic operator - instead of MINUS.
2. Refer to the same presentation above on UNION and other set operators.
An index is a performance-tuning method of allowing faster retrieval of records.
View in Oracle and in other database systems is simply the representation of a SQL statement that is stored in memory so that it can be easily reused.
CREATE VIEW emp2 as select ename,sal from emp;
3. RANK calculates the rank of a value in a group of values. The return type is NUMBER.
SELECT RANK(3000) WITHIN GROUP
(ORDER BY salary DESC) "Rank of 3000" FROM emp;
1. Do not use 'minus' clause to get the result that you are expecting. MINUS clause is used as one of the set operators check my presentation Link
To get the answer you need use arithmetic operator - instead of MINUS.
2. Refer to the same presentation above on UNION and other set operators.
An index is a performance-tuning method of allowing faster retrieval of records.
View in Oracle and in other database systems is simply the representation of a SQL statement that is stored in memory so that it can be easily reused.
CREATE VIEW emp2 as select ename,sal from emp;
3. RANK calculates the rank of a value in a group of values. The return type is NUMBER.
SELECT RANK(3000) WITHIN GROUP
(ORDER BY salary DESC) "Rank of 3000" FROM emp;
it is giving 8 as length.but it should be 2
sir i am not getting how to use arithmetic operators.pls write the query
You have to write the statement as shown below below,
select length('salesman') - length(replace('salesman','s','')) from dual;
Mithun