ListAgg function in Oracle SQL 12c

The LISTAGG analytic function was introduced in Oracle 11g Release 2.

the function is used to do string aggregations.

suppose I have data in employee table like this.

and we wanted display department_id and all the related employee first_name in one row , like below.

then we can use LISTAGG function to aggregate the strings.

here is the query.

select department_id,LISTAGG(first_name||',') within group (order by first_name) as Employee from EMPLOYEES

group by DEPARTMENT_ID;

Note: I have used EMPLOYEES table from HR schema in Oracle 12c.

ListAgg function in Oracle SQL 12c

The LISTAGG analytic function was introduced in Oracle 11g Release 2.

the function is used to do string aggregations.

suppose I have data in employee table like this.

and we wanted display department_id and all the related employee first_name in one row , like below.

then we can use LISTAGG function to aggregate the strings.

here is the query.

select department_id,LISTAGG(first_name||',') within group (order by first_name) as Employee from EMPLOYEES

group by DEPARTMENT_ID;

Note: I have used EMPLOYEES table from HR schema in Oracle 12c.

To find the difference between two dates in SQL Server

To find the difference between two dates.


SELECT DATEDIFF(DAY, '4/29/2019', '6/14/2019');

result : 46

SELECT DATEDIFF(DAY, '5/09/2019', '6/14/2019');

Result: 36

SELECT DATEDIFF(DAY, '4/29/2019', SYSDATETIME());

Result: 78

To find the difference between two dates in SQL Server

To find the difference between two dates.


SELECT DATEDIFF(DAY, '4/29/2019', '6/14/2019');

result : 46

SELECT DATEDIFF(DAY, '5/09/2019', '6/14/2019');

Result: 36

SELECT DATEDIFF(DAY, '4/29/2019', SYSDATETIME());

Result: 78

SQL Server DATENAME Function

DATENAME – returns a string corresponding to the datepart specified

SELECT DATENAME(YEAR, GETDATE())        AS 'Year';        
SELECT DATENAME(QUARTER, GETDATE())     AS 'Quarter';     
SELECT DATENAME(MONTH, GETDATE())       AS 'Month';       
SELECT DATENAME(DAYOFYEAR, GETDATE())   AS 'DayOfYear';   
SELECT DATENAME(DAY, GETDATE())         AS 'Day';         
SELECT DATENAME(WEEK, GETDATE())        AS 'Week';        
SELECT DATENAME(WEEKDAY, GETDATE())     AS 'WeekDay';     
SELECT DATENAME(HOUR, GETDATE())        AS 'Hour';        
SELECT DATENAME(MINUTE, GETDATE())      AS 'Minute';      
SELECT DATENAME(SECOND, GETDATE())      AS 'Second';      
SELECT DATENAME(MILLISECOND, GETDATE()) AS 'MilliSecond'; 
SELECT DATENAME(MICROSECOND, GETDATE()) AS 'MicroSecond'; 
SELECT DATENAME(NANOSECOND, GETDATE())  AS 'NanoSecond';  
SELECT DATENAME(ISO_WEEK, GETDATE())    AS 'Week';        

SQL Server DATENAME Function

DATENAME – returns a string corresponding to the datepart specified

SELECT DATENAME(YEAR, GETDATE())        AS 'Year';        
SELECT DATENAME(QUARTER, GETDATE())     AS 'Quarter';     
SELECT DATENAME(MONTH, GETDATE())       AS 'Month';       
SELECT DATENAME(DAYOFYEAR, GETDATE())   AS 'DayOfYear';   
SELECT DATENAME(DAY, GETDATE())         AS 'Day';         
SELECT DATENAME(WEEK, GETDATE())        AS 'Week';        
SELECT DATENAME(WEEKDAY, GETDATE())     AS 'WeekDay';     
SELECT DATENAME(HOUR, GETDATE())        AS 'Hour';        
SELECT DATENAME(MINUTE, GETDATE())      AS 'Minute';      
SELECT DATENAME(SECOND, GETDATE())      AS 'Second';      
SELECT DATENAME(MILLISECOND, GETDATE()) AS 'MilliSecond'; 
SELECT DATENAME(MICROSECOND, GETDATE()) AS 'MicroSecond'; 
SELECT DATENAME(NANOSECOND, GETDATE())  AS 'NanoSecond';  
SELECT DATENAME(ISO_WEEK, GETDATE())    AS 'Week';        

SQL server date functions

To find current date and time : select SYSDATETIME();

To display only date : select convert(date,SYSDATETIME());

To display only time : select convert(time,SYSDATETIME());

Here is the query to display all this above.

select SYSDATETIME() as Timestamp,convert(date,SYSDATETIME()) as Date,CONVERT(time,SYSDATETIME()) as Time;

SQL server date functions

To find current date and time : select SYSDATETIME();

To display only date : select convert(date,SYSDATETIME());

To display only time : select convert(time,SYSDATETIME());

Here is the query to display all this above.

select SYSDATETIME() as Timestamp,convert(date,SYSDATETIME()) as Date,CONVERT(time,SYSDATETIME()) as Time;

Find nth Highest salary

As all of you aware, in most of the interview, regardless you are fresher or the experienced, in SQL section there is all time favorite question that is how to find nth highest salary.

most of us fail because, our brain wont work when they ask this type of question.

here I am going to solve the problem with steps you don't forget.

now think about about the columns required to calculate highest salary.

we need to have empno, ename and salary columns , right ?

ok, now think about the logic to get rank of salary.

now  shall we use rank() or dense_rank ().

always remember, it is best to use dense_rank function.

now our initial query looks like this,

select e.empno,e.ename,sal,dense_rank() over (order by sal desc) as rnk from scott.emp e ;

which returns values like this.

now, the RNK column has the rank values for which we can apply filter and get the highest salary details.

for example, if you want to see 3rd highest salary, apply filter on RNK column like this.

select * from (select e.empno,e.ename,sal,dense_rank() over (order by sal desc) as rnk from scott.emp e

) where rnk=3;

the output will be

Thanks and happy learning :)

Find nth Highest salary

As all of you aware, in most of the interview, regardless you are fresher or the experienced, in SQL section there is all time favorite question that is how to find nth highest salary.

most of us fail because, our brain wont work when they ask this type of question.

here I am going to solve the problem with steps you don't forget.

now think about about the columns required to calculate highest salary.

we need to have empno, ename and salary columns , right ?

ok, now think about the logic to get rank of salary.

now  shall we use rank() or dense_rank ().

always remember, it is best to use dense_rank function.

now our initial query looks like this,

select e.empno,e.ename,sal,dense_rank() over (order by sal desc) as rnk from scott.emp e ;

which returns values like this.

now, the RNK column has the rank values for which we can apply filter and get the highest salary details.

for example, if you want to see 3rd highest salary, apply filter on RNK column like this.

select * from (select e.empno,e.ename,sal,dense_rank() over (order by sal desc) as rnk from scott.emp e

) where rnk=3;

the output will be

Thanks and happy learning :)

Simple Python Program to find Biggest number

a=int(input('Enter value for a :'))
b=int(input('Enter value for b :'))

op=str(input('Enter the operator :'))
if op in ["'addition","+", "sum"]:
    result=a+b
    print('Result is: %d' %result)
else:
    print('Invalid operation')

Simple Python Program to find Biggest number

a=int(input('Enter value for a :'))
b=int(input('Enter value for b :'))

op=str(input('Enter the operator :'))
if op in ["'addition","+", "sum"]:
    result=a+b
    print('Result is: %d' %result)
else:
    print('Invalid operation')

OBIEE Server procedure call to view the current cache contents

We can use call NQS_GetAllCacheEntries(); command to get all the cache entries available in server.

steps to use:

1. Login to analytics as user with admin role or weblogic.

2. Go to administration page.

3. Click on issue sql link.

4. call the command in issue sql box: call NQS_GetAllCacheEntries();