Imp SQL Queries

​Below query gives the number of sessions the database was configured to allow
SELECT name, value FROM v$parameter WHERE name = 'sessions'

Below query gives the number of sessions currently active
SELECT COUNT(*) FROM v$session

select * from v$resource_limit;

1. Log on again in the SQL console and put the following commands to increase the process number to 200 by example.

SQL> connect AS system/"defaultpassword" AS SYSDBA;
SQL> ALTER system SET processes = 200 scope=spfile;
2. Restart the Oracle server

Optional Tip: If you want to modify the sessions you can do this with following command:

SQL> connect AS system/"defaultpassword" AS SYSDBA;
SQL> ALTER system SET sessions= 200 scope=spfile;

select * from dba_users;
=======================================================================
/* It will provide the rank to fetch any order value */
SELECT empno,
       deptno,
       sal,
       RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM   emp;

​Above will give different consecutive ranks for the same values & this way we can loose actual other ranked value.
​To avoid this, we can use DENSE_RANK() to give the same rank to equal values.

​SELECT empno,
       deptno,
       sal,
       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM   emp;

//To get the random numbers-
SELECT floor(dbms_random.value(1000, 4000)) rnd FROM dual;
​=======================================================================
3. Get the details of the employees earning more than their managers -
​
select e.empno, e.ename, e.mgr, e.sal, m.sal "Manager Salary" from emp e, emp m where e.sal > m.sal and E.MGR = m.empno;
=======================================================================
​4. To create the temporary table with one column for the given list of values like,
​     To create a table with one column which contains 1,2,3,4 numbers -
       select column_value from table(sys.dbms_debug_vc2coll(1,2,3,4));
​=======================================================================
5. To concatenate the values of a column across multiple rows -
​select job, listagg(ename, ' ') within group (order by ename) names from emp group by job;

Below is the example of case in Oracle to decide on the quality of salary & count the number of employees in that quality -
​select Quality, count(ename) from (select ename, sal,
case
when sal <= 1000 then 'Poor'
when sal > 1000 and sal <= 1500 then 'Need Improvement'
when sal > 1500 and sal <= 2000 then 'Average'
when sal > 2000 and sal <= 2800 then 'Good'
else 'Great'
end as Quality
from emp) group by Quality;

=======================================================================
 Lets have more information about above results -

select Quality, count(ename), listagg(ename, ', ') within group(order by ename) Employees, listagg(sal, ', ') within group(order by ename) Salaries
from (select ename, sal,
case
when sal <= 1000 then 'Poor'
when sal > 1000 and sal <= 1500 then 'Need Improvement'
when sal > 1500 and sal <= 2000 then 'Average'
when sal > 2000 and sal <= 2800 then 'Good'
else 'Great'
end as Quality
from emp) group by Quality;

This will give result like shown below -

=======================================================================
​*                                                                               
**                                                                              
***
Print above output.

Solution : SELECT lpad ('*', ROWNUM,'*') FROM emp
   Constraint : Using this query, you can print the above triangle of maximum number of
   rows/records available in the table used here. Otherwise you have to write some                 
   procedure/function.

=======================================================================

Suppose you want to get the data with the name of manager & list of employees under him/her like shown below-

Below is one kind of solution can be fine for small table or for your interview otherwise take a look of its execution plan, this query is not light one-
​
select manager, employees from (
select l1, mgr, manager, listagg(ename, ', ') within group(order by ename) employees from (
select level l1, e.mgr, (select inn.ename from emp inn where inn.empno = e.mgr) manager, e.ename from emp e
connect by e.mgr=prior e.empno
start with e.mgr is null)
where mgr is not null
group by manager, mgr, l1)
order by l1, manager