Skip to main content

Important Queries in SQL Part 4 #DOTC_Mdb

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

https://dheeraj60.blogspot.com/2020/07/important-sql-queries-part-2-dotcmdb.html

https://dheeraj60.blogspot.com/2020/07/important-queries-in-sql-part3-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

Popular posts from this blog

SQL and Classification of SQL in Oracle Database #Diksha Oracle Training Centre

  SQL and Classification of SQL in Oracle Database.   SQL is Structured Query Language , which is used for storing, manipulating and retrieving data stored in a relational database .SQL is the standard language for RDBMS. All the Relational Database Management Systems (RDMS) like Oracle, MySQL, Sybase, Informix, IBM DB2 and Microsoft SQL Server use SQL as their standard database language. Oracle is one of the more secured database as compared to other databases. Importance of   SQL : SQL and PL/SQL is a backend process where all data is stored and retrieved in GUI which created either by any programming languages like Java, C++, PHP etc. so we need to have very secure database so that there will be no impact for users. SQL allows users to access data in the relational database management systems. SQL is used to communicate with a database.SQL and PL/SQL allows users to create and drop databases tables , views , stored procedures , functions , packages , trigger et...

Basic SQL Commands in Oracle Database #Diksha Oracle Training Centre

  Basic SQL Commands in Oracle Database.   Hello Everyone, In my previous blog I discussed about SQL and Classification of SQL . Today I will discuss about SQL basic commands which widely used in RDBMS. The Topics of SQL command which I am going to cover in this blog are mainly divided into four Categories: ·         DDL:   DDL consists of commands which are used to define and design the database. ·         DML:   DML consists of commands which are mainly used to manipulate the data in the database. ·        DCL: DCL Consists of commands which deal with the user permissions/access and controls in the database. ·       TCL:   TCL Consist of commands which deal with the transaction in the database. If you want to explore theory part please follow my below blog as in today blog I will discuss about how to use the commands. h...

Pseudo Column in Oracle #Diksha Oracle Training Centre

  Pseudo Column in Oracle. Hello Everyone, Today I will discuss about Pseudo column in Oracle. This topic is very important so before going to read this blog, I suggest you to please see my previous blogs which is in archive folder for your reference so that it’s easy for you to understand this topic. Let’s discuss about Pseudo Column in Oracle. Pseudo column: A pseudo-column is dynamic in Oracle as it behaves like a table column but is not stored in the table. Pseudo column behaves like a table column, but is not actually stored in the table. You can select from pseudo columns, but you cannot insert, update, or delete their values. A pseudo column is also similar to a function without arguments. In   Simple word we can say Pseudo column is a column that yields a value when selected, but which is not an actual column of the table. An example is RowID or SysDate. It can be use in combination with the DUAL table. Below are the Pseudo Column commonly used in Oracle Dat...