Nitin Agrawal
Contact -
  • Home
  • Interviews
    • Secret Receipe
    • InterviewFacts
    • Resume Thoughts
    • Daily Coding Problems
    • BigShyft
    • Companies
    • Interviews Theory
  • Programming Languages
    • Java Script >
      • Tutorials
      • Code Snippets
    • Reactive Programming >
      • Code Snippets
    • R
    • DataStructures >
      • LeetCode Problems >
        • Problem10
        • Problem300
      • AnagramsSet
    • Core Java >
      • Codility
      • Program Arguments OR VM arguments & Environment variables
      • Java Releases >
        • Java8 >
          • Performance
          • NasHorn
          • WordCount
          • Thoughts
        • Java9 >
          • ServiceLoaders
          • Lambdas
          • List Of Objects
          • Code Snippets
        • Java14 >
          • Teeing
          • Pattern
          • Semaphores
        • Java17 >
          • Switches
          • FunctionalStreams
          • Predicate
          • Consumer_Supplier
          • Collectors in Java
        • Java21 >
          • Un-named Class
          • Virtual Threads
          • Structured Concurrency
      • Threading >
        • ThreadsOrder
        • ProducerConsumer
        • Finalizer
        • RaceCondition
        • Executors
        • Future Or CompletableFuture
      • Important Points
      • Immutability
      • Dictionary
      • Sample Code Part 1 >
        • PatternLength
        • Serialization >
          • Kryo2
          • JAXB/XSD
          • XStream
        • MongoDB
        • Strings >
          • Reverse the String
          • Reverse the String in n/2 complexity
          • StringEditor
          • Reversing String
          • String Puzzle
          • Knuth Morris Pratt
          • Unique characters
          • Top N most occurring characters
          • Longest Common Subsequence
          • Longest Common Substring
        • New methods in Collections
        • MethodReferences
        • Complex Objects Comparator >
          • Performance
        • NIO >
          • NIO 2nd Sample
        • Date Converter
        • Minimum cost path
        • Find File
      • 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
        • Proxy
        • Lazy Initialization
        • CombinatorPattern
        • Singleton >
          • Singletons
        • Strategy
  • Frameworks
    • Apache Velocity
    • React Library >
      • Tutorial
    • Spring >
      • Spring Boot >
        • CustomProperties
        • ExceptionHandling
        • Custom Beans
        • 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
      • Kubernetes
  • Databases
    • MySql
    • Oracle >
      • Interview1
      • SQL Queries
    • Elastic Search
  • Random issues
    • TOAD issue
    • Architect's suggestions
  • Your Views

Imp SQL Queries

3/14/2014

1 Comment

 
​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
1 Comment
Icavallilamiaconsolazione link
10/27/2023 03:26:45 pm

Thankss great post

Reply



Leave a Reply.

    Nitin Agrawal

    Archives

    March 2014

Powered by Create your own unique website with customizable templates.