Important SQL Queries Part 2 #DOTC_Mdb

Important Queries in SQL Part2.

Hello Everyone,

In my previous blog we discussed about some important queries used in SQL and today again we will discuss about some important SQL queries which is useful for you and also this type of queries asked in interview. Before this I suggest you to again see my blogs related to SQL and PL/SQL which you can able to see in archive folder of May and June.

Also you can explore my previous below blog SQL part 1 for your reference.

https://dheeraj60.blogspot.com/2020/07/important-queries-in-sql-part1-dotcmdb.html

 So let’s start with Queries .

1 .Suppose I have created employee table with below data and I want to find the nth highest salary from employee data .

 

Suppose I want the 2^nd highest salary from employee table then we use below query as in table we have only 8 records . and 1 records have duplicate that is second highest salary from the table .

Query is as below :

select * from employee a where (1)

= (select count(distinct(b.salary))

from employee b

where a.salary < b.salary);

 

Below is the output of above query in image :

Here we get 2 records as 80000 is the second highest salary in employee table.

If we want the highest salary then we use 0 and similarly for third highest salary we can put value 2 and so on as INNER query will Return N-1 and for finding nth number of salary from employee table we need to put logic as for each row processed by outer query, inner query will be executed and will return the output against our requirement.

This is very good query to find the nth number of salary from any table. Also  you can refer my Sub query and co-related sub query blog for your understanding. Hope you are able to understand this query as this query most frequently asked in Interview.

 

2.  How to Display Odd rows in a table ?

 For this we need to write subquery to get odd records from a table. I am using the same table employee to find the odd rows from table .

Below is the query :

select * from employee where employee_id

IN

(select employee_id from employee where employee_id%2 <>0);

 

Here we use % that is modulus and operator <> means not equal to so it’s not divisible by 2 so it will give odd rows.

Below is the output of above query.

 

 

I same manner we can get even rows from a table by replacing <> to = .

Query will be .

select * from employee where employee_id

IN

(select employee_id from employee where employee_id%2 =0);

Below is the output of above query.

 

 

Hope you are able to understand the above queries.

3 . Suppose you want to create one table with another table without any data  only structure you need?

As we have employee table and I want to create another table emp with structure not data then we need to give false statement in where clause like 1=2 or 3=4.

Below is the query for that :

Create table emp as

Select * from employee

Where 1=2;

Here table emp created without any data but structure is same as in employee table.

Below is the output for your reference.

Here you can see emp table created but without any data , but structure is there.

 

4 . How to get distinct records from the table without using distinct keyword in SQL.

 

For this we can use ROWID as we know ROWID is Pseudo column which is unique for each rows and saved in Hexadecimal Format .

Below is the query we can use to see the distinct records from a table .

 

select * from Employee a where  rowid = (select max(rowid) from Employee b where  a.Employee_no=b.Employee_no);

Above query will give you unique records as we use  aggregate function in rowid .

5 . How to remove Duplicate Records from a table .

For this also we can use ROWID in Query as below :

Delete from employee where rowid not in (select max(rowid) from employee group by dept_no);

We can also use aggregate function MIN instead of MAX and it will also work as same . To understand more about ROWID  please refer my blog Pseudo Column in SQL for better understanding.

This all are important SQL query so practice as much as you can and also in interview they can ask you this type of query frequently.  So please go through this blogs carefully and let me know if you have any doubt. In next blog I will come up with more SQL queries which is commonly used and asked in interview.

Thanks.