Important Queries in SQL Part 4.
In my previous blogs I discussed about several sql queries which commonly used in SQL and today again also I will discuss about some important queries in SQL . Before that I suggest you to go through my SQL and PL/SQL blogs in archive May and June folder for your understanding. Also you can see my below previous blogs about Queries in SQL.
https://dheeraj60.blogspot.com/2020/07/important-queries-in-sql-part1-dotcmdb.html
1. How to Assigning sequential numbers to rows in a table
As we know in Oracle we use ROWNUM pseudo column returns a number indicating the order in which Oracle selects the row from a table.
In my SQL we use ROW_NUMBER(). Let’s understand with below statement.
The below SQL statement or queries uses the ROW_NUMBER() function to assign a sequential number to each row from the employee table:
SELECT
ROW_NUMBER() OVER (
ORDER BY emp_id
) row_num,
emp_Name,
salary
FROM
employee
order by emp_id;
In Oracle we can write this query in Simple way as
Select * from employee where ROWNUM<5;
Above query will give you sequential row number from employee table by using ROW_NUMBER function.
Below is the output of above query :
2. As we know the ROW_NUMBER() assigns each row in the result set a unique number, you can use it for pagination also .
Suppose, if you need to display a list of employee within 3 to 5. To get the records for this, we use the below query:
SELECT *
FROM
(SELECT EMP_NAME,
SALARY,
row_number()
OVER (order by SALARY) AS row_num
FROM EMPLOYEE) a
WHERE row_num BETWEEN 3 AND 5;
Note : If we put OVER (order by SALARY DESC) AS row_num the output will be different . Please go through my previous blogs Order by and Psuedo column for your better understanding.
Output of the above query is as below:
3 . How to Display last 5 records from employee table.
Here we can use ROWNUM pseudo column to get the last 5 records from employee table.
Note : Please go through my previous blog Psuedo column Topic for your better understanding.
Select * from Employee a where rownum <=5
union
select * from (Select * from Employee a order by rowid desc) where rownum <=5;
For getting the first 5 records we can use simple query as below :
Select * from Employee a where rownum <=5;
4. What is the SQL Query to find maximum salary of each department.
select dept_no,max(salary)
from department
group by dept_no;
To filter the salary we can use Having clause in above query as below :
select dept_no,max(salary)
from department
group by dept_no
Having max(salary)>600000 ;
Below is the output of above query :
5. How to find count of duplicate rows from a table .
We use aggregate function count with group by and having clause to find the duplicate counts of rows in a table. For theory you can go through my blog Aggregate function in SQL .
Query to find count of duplicate rows from a table is as below :
select emp_id , count(emp_id)
from employee
group by emp_id
having count(emp_id) >1;
Below is the output of above query where you can see the duplicate records in count column .
6 . Scenario Based :
If you writing a PL/SQL block and you need a condition if records is there in table then update the table else insert the records in a table.
In this case we can use count function to check and use if else condition in anonymous block by using variable :
Below is the piece of code you can use it :
Declare
v_count int;
Begin
Select count(*) into v_count
from employee;
If v_count > 1
then
update employee
set salary =50000
where dept_id=10;
else
insert into employee values(‘Dheeraj’,10,100,50000);
END;
This is the basic example to check and do the requirement against it . Also you can use loop or pass parameter to avoid hardcoded value .
Please go through this blog as well as my previous query blog carefully and try to write queries as much as you can as per your requirement and let me know if you have any doubt . I suggest you for theory in SQL and PL/SQL you can go through my previous blogs for your reference.
In next blog I will come up with next topic.
Thanks.
Comments
Post a Comment