Nitin Agrawal
Contact -
  • Home
  • Interviews
    • Secret Receipe
    • InterviewFacts
    • Resume Thoughts
    • Daily Coding Problems
    • BigShyft
    • CompanyInterviews >
      • InvestmentBanks >
        • ECS
        • Bank Of America
        • WesternUnion
        • WellsFargo
      • ProductBasedCompanies >
        • CA Technologies
        • Model N India
        • Verizon Media
        • Oracle & GoJek
        • IVY Computec
        • Nvidia
        • ClearWaterAnalytics
        • ADP
        • ServiceNow
        • Pubmatic
        • Expedia
        • Amphora
        • CDK Global
        • CDK Global
        • Epic
        • Sincro-Pune
        • Whiz.AI
        • ChargePoint
      • ServiceBasedCompanies >
        • Altimetrik
        • ASG World Wide Pvt Ltd
        • Paraxel International & Pramati Technologies Pvt Ltd
        • MitraTech
        • Intelizest Coding Round
        • EPAM
    • Interviews Theory
  • Programming Languages
    • Java Script >
      • Tutorials
      • Code Snippets
    • Reactive Programming >
      • Code Snippets
    • R
    • DataStructures >
      • LeetCode Problems
      • AnagramsSet
    • Core Java >
      • Codility
      • Program Arguments OR VM arguments & Environment variables
      • Java Releases
      • Threading >
        • ThreadsOrder
        • ProducerConsumer
        • Finalizer
        • RaceCondition
        • Executors
        • Future Or CompletableFuture
      • Important Points
      • Immutability
      • Dictionary
      • URL Validator
    • Julia
    • Python >
      • Decorators
      • String Formatting
      • Generators_Threads
      • JustLikeThat
    • Go >
      • Tutorial
      • CodeSnippet
      • Go Routine_Channel
      • Suggestions
    • Methodologies & Design Patterns >
      • Design Principles
      • Design Patterns >
        • TemplatePattern
        • Adapter Design Pattern
        • Decorator
        • Proxy
        • Lazy Initialization
        • CombinatorPattern
        • RequestChaining
        • Singleton >
          • Singletons
  • Frameworks
    • Apache Velocity
    • Spring >
      • Spring Boot >
        • CustomProperties
        • ExceptionHandling
        • Issues
      • Quick View
    • Rest WebServices >
      • Interviews
      • Swagger
    • Cloudera BigData >
      • Ques_Ans
      • Hive
      • Apache Spark >
        • ApacheSpark Installation
        • SparkCode
        • Sample1
        • DataFrames
        • RDDs
        • SparkStreaming
        • SparkFiles
    • Integration >
      • Apache Camel
    • Testing Frameworks >
      • JUnit >
        • JUnit Runners
      • EasyMock
      • Mockito >
        • Page 2
      • TestNG
    • Blockchain >
      • Ethereum Smart Contract
      • Blockchain Java Example
    • Microservices >
      • Messaging Formats
      • Design Patterns
    • AWS >
      • Honeycode
    • Dockers >
      • GitBash
      • Issues
  • Databases
    • MySql
    • Oracle >
      • Interview1
      • SQL Queries
    • Elastic Search
  • Random issues
    • TOAD issue
    • Architect's suggestions
  • Your Views

Imp SQL Queries

3/14/2014

0 Comments

 
​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;

Picture
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 -
Picture
=======================================================================
​*                                                                               
**                                                                              
***

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-
Picture
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
0 Comments

    Nitin Agrawal

    Archives

    March 2014

Powered by Create your own unique website with customizable templates.