dense rank in SQL
Posted inMY SQL Oracle PL/SQL SQL

DENSE_RANK SQL

Problem We can easily use the MAX() function in SQL Server to find the maximum value in a table. However, there are situations when the second-highest or third-highest record is needed from the table. SQL Server has no direct SQL functions to fetch the second or third highest from the table, so…
SQL rank function
Posted inSQL MY SQL Oracle PL/SQL

RANK() Function in SQL

The RANK() function in SQL is a powerful window function in SQL Server used to assign a rank to each row within a result set. It is particularly useful when we need to assign a rank to a group of rows based on some sorting criteria and want to differentiate between rows…
SQL LEAD()
Posted inMY SQL Oracle PL/SQL SQL

SQL LEAD()

The LEAD() window function takes a column and an integer offset as arguments and returns the value of the cell in that column that is the specified number of rows after the current row. A third argument can be added to fill cells that do not have a corresponding row. Syntax LEAD(column1_name,…
How to Use ROW_NUMBER OVER() in SQL
Posted inSQL MY SQL Oracle PL/SQL

How to Use ROW_NUMBER OVER() in SQL

Sometimes you need to know the position of rows in a result set. Learn how using ROW_NUMBER and OVER can make it happen! Have you ever needed to add a sequential number to the records returned by an SQL query? Or perhaps you need to create a ‘top n’ report based…
sql subquery
Posted inOracle PL/SQL MY SQL SQL

SQL Subquery

Summary: In this tutorial, you’ll learn how to use SQL subqueries to form flexible queries for retrieving data from the database. Introduction to SQL subquery  A subquery is an SQL query nested inside another query. The query that contains a subquery is known as an outer query. To write a subquery,…
left join in sql
Posted inMY SQL Oracle PL/SQL SQL

SQL LEFT JOIN

Summary: in this tutorial, you’ll learn how to use the SQL LEFT JOIN clause to merge rows from two tables. Introduction to SQL LEFT JOIN clause  The LEFT JOIN clause is an optional clause of the SELECT statement. The LEFT JOIN clause allows you to merge rows from two tables. Here’s the syntax of LEFT JOIN clause: SELECT column1, column2…
Posted inMY SQL SQL

SQL HAVING COUNT

In SQL, the HAVING clause is used in combination with the COUNT function to filter the results of a query based on the count of rows returned by a particular condition. This clause is typically used in conjunction with the GROUP BY clause to aggregate data and then filter the aggregated results. Here’s an…
Posted inMY SQL Oracle PL/SQL SQL

SQL remove duplicates

Removing duplicates from a SQL database is a common task that can help improve data quality and query performance. Duplicates occur when there are multiple rows in a table with identical values in one or more columns. You can eliminate these duplicates using various SQL techniques and clauses. In this…
Posted inSQL MY SQL Oracle PL/SQL

SQL MERGE

The SQL MERGE statement stands out as a versatile tool for consolidating data from multiple sources and maintaining data integrity. It serves as a powerful alternative to the more traditional approach of using individual INSERT, UPDATE, and DELETE statements to manage data changes. The MERGE statement’s ability to handle these operations within a single statement offers…
Posted inMY SQL Oracle PL/SQL SQL

SQL TCL statements

Transaction Control Language (TCL) in SQL is a subset of SQL commands used to manage database transactions. Transactions are a fundamental concept in database systems, ensuring data integrity and consistency by grouping a set of SQL statements into a single logical unit of work. TCL commands allow developers to control when changes…