<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Kailas Warade</title>
    <description>The latest articles on DEV Community by Kailas Warade (@kailas_warade_d2a15d1ef8a).</description>
    <link>https://dev.to/kailas_warade_d2a15d1ef8a</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4126500%2F91be8ee8-0234-427e-a917-f6338418cd5d.png</url>
      <title>DEV Community: Kailas Warade</title>
      <link>https://dev.to/kailas_warade_d2a15d1ef8a</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/kailas_warade_d2a15d1ef8a"/>
    <language>en</language>
    <item>
      <title>SQL Challenge : Compare Today's Sales With Yesterday's Sales Using LAG() in Oracle SQL</title>
      <dc:creator>Kailas Warade</dc:creator>
      <pubDate>Fri, 20 Nov 2026 03:08:45 +0000</pubDate>
      <link>https://dev.to/kailas_warade_d2a15d1ef8a/sql-challenge-compare-todays-sales-with-yesterdays-sales-using-lag-in-oracle-sql-jc3</link>
      <guid>https://dev.to/kailas_warade_d2a15d1ef8a/sql-challenge-compare-todays-sales-with-yesterdays-sales-using-lag-in-oracle-sql-jc3</guid>
      <description>&lt;p&gt;When working with sales data, looking at daily revenue alone doesn't always tell the complete story.&lt;/p&gt;

&lt;p&gt;Businesses often need to compare today's sales with the previous day's sales to understand whether revenue is increasing or decreasing.&lt;/p&gt;

&lt;p&gt;For example:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Yesterday's sales: 15,000&lt;/li&gt;
&lt;li&gt;Today's sales: 12,000&lt;/li&gt;
&lt;li&gt;Difference: -3,000&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;How can you calculate this comparison using SQL without joining the same table with itself?&lt;/p&gt;

&lt;p&gt;The answer is the LAG() window function.&lt;/p&gt;

&lt;p&gt;In this SQL tutorial, let's solve a practical daily sales comparison problem using Oracle SQL.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Understanding the Problem&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Suppose you have a "daily_sales" table containing sale dates and daily sales amounts.&lt;/p&gt;

&lt;p&gt;Your task is to write an SQL query that displays:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Sale date&lt;/li&gt;
&lt;li&gt;Today's sales&lt;/li&gt;
&lt;li&gt;Previous day's sales&lt;/li&gt;
&lt;li&gt;Difference between today's and previous day's sales&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;The original table should remain unchanged.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Sample Table: daily_sales&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;CREATE TABLE daily_sales (&lt;br&gt;
    sales_date DATE,&lt;br&gt;
    total_sales NUMBER(10)&lt;br&gt;
);&lt;/p&gt;

&lt;p&gt;Sample records:&lt;/p&gt;

&lt;p&gt;INSERT INTO daily_sales VALUES (DATE '2026-11-16', 10000);&lt;br&gt;
INSERT INTO daily_sales VALUES (DATE '2026-11-17', 15000);&lt;br&gt;
INSERT INTO daily_sales VALUES (DATE '2026-11-18', 12000);&lt;br&gt;
INSERT INTO daily_sales VALUES (DATE '2026-11-19', 18000);&lt;br&gt;
INSERT INTO daily_sales VALUES (DATE '2026-11-20', 22000);&lt;/p&gt;

&lt;p&gt;COMMIT;&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Your SQL Challenge&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Write an SQL query to:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Retrieve the previous day's sales using LAG().&lt;/li&gt;
&lt;li&gt;Compare today's sales with previous day's sales.&lt;/li&gt;
&lt;li&gt;Calculate the sales difference.&lt;/li&gt;
&lt;li&gt;Display records in chronological order.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Can you write the query before checking the solution?&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;SQL Solution Using LAG() (Oracle)&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;SELECT&lt;br&gt;
    sales_date,&lt;br&gt;
    total_sales AS today_sales,&lt;br&gt;
    LAG(total_sales) OVER (&lt;br&gt;
        ORDER BY sales_date&lt;br&gt;
    ) AS previous_day_sales,&lt;br&gt;
    total_sales - LAG(total_sales) OVER (&lt;br&gt;
        ORDER BY sales_date&lt;br&gt;
    ) AS sales_difference&lt;br&gt;
FROM daily_sales&lt;br&gt;
ORDER BY sales_date;&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Expected Output&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;SALES_DATE  TODAY_SALES  PREVIOUS_DAY_SALES  SALES_DIFFERENCE&lt;br&gt;
16-Nov-2026    10000          NULL                NULL&lt;br&gt;
17-Nov-2026    15000          10000               5000&lt;br&gt;
18-Nov-2026    12000          15000              -3000&lt;br&gt;
19-Nov-2026    18000          12000               6000&lt;br&gt;
20-Nov-2026    22000          18000               4000&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;How Does the LAG() Function Work?&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Let's understand the important parts of this query.&lt;/p&gt;

&lt;p&gt;LAG(total_sales)&lt;/p&gt;

&lt;p&gt;Retrieves the sales amount from the previous row.&lt;/p&gt;

&lt;p&gt;OVER()&lt;/p&gt;

&lt;p&gt;Defines the window over which the function operates.&lt;/p&gt;

&lt;p&gt;ORDER BY sales_date&lt;/p&gt;

&lt;p&gt;Arranges the rows by date and determines which row is considered previous.&lt;/p&gt;

&lt;p&gt;total_sales - LAG(total_sales)&lt;/p&gt;

&lt;p&gt;Calculates the difference between today's sales and the previous day's sales.&lt;/p&gt;

&lt;p&gt;The first record returns NULL because there is no previous row.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Understanding LAG() Syntax&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;The general syntax is:&lt;/p&gt;

&lt;p&gt;LAG(column_name, offset, default_value)&lt;br&gt;
OVER (&lt;br&gt;
    PARTITION BY column_name&lt;br&gt;
    ORDER BY column_name&lt;br&gt;
)&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;"column_name": The column whose previous value you want.&lt;/li&gt;
&lt;li&gt;"offset": Number of rows to look backward. Default is 1.&lt;/li&gt;
&lt;li&gt;"default_value": Value returned when no previous row exists.&lt;/li&gt;
&lt;li&gt;"PARTITION BY": Divides records into separate groups.&lt;/li&gt;
&lt;li&gt;"ORDER BY": Determines the order of rows.&lt;/li&gt;
&lt;/ul&gt;

&lt;ol&gt;
&lt;li&gt;One Thing to Remember&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;LAG() is different from aggregate functions.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;"SUM()": Calculates totals.&lt;/li&gt;
&lt;li&gt;"LAG()": Retrieves a value from a previous row.&lt;/li&gt;
&lt;li&gt;"LEAD()": Retrieves a value from a following row.&lt;/li&gt;
&lt;li&gt;"ROW_NUMBER()": Assigns sequential numbers to rows.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The LAG() function is particularly useful for daily sales comparisons, monthly revenue analysis, customer purchase trends and business performance reports.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Your Turn: Try This SQL Challenge&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;How would you modify the above query to calculate the percentage change in sales compared with the previous day?&lt;/p&gt;

&lt;p&gt;Hint: Use the following formula:&lt;/p&gt;

&lt;p&gt;((today_sales - previous_day_sales)&lt;br&gt;
 / previous_day_sales) * 100&lt;/p&gt;

&lt;p&gt;Try writing the query yourself before looking for the answer.&lt;/p&gt;

&lt;p&gt;Share your SQL solution in the comments!&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Frw531f2g18ew91voe4gs.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Frw531f2g18ew91voe4gs.png" alt=" " width="800" height="875"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;If you are learning SQL window functions, you can explore more practical SQL tutorials and interview challenges at "Sankalan Data Tech" (&lt;a href="https://www.sankalandtech.com/" rel="noopener noreferrer"&gt;https://www.sankalandtech.com/&lt;/a&gt;).&lt;/p&gt;

&lt;h1&gt;
  
  
  SQL #OracleSQL #SQLChallenge #WindowFunctions
&lt;/h1&gt;

</description>
      <category>lag</category>
      <category>sql</category>
      <category>database</category>
      <category>tutorial</category>
    </item>
    <item>
      <title>SQL Running Total: Calculate Cumulative Daily Sales Using SUM() OVER() in Oracle SQL</title>
      <dc:creator>Kailas Warade</dc:creator>
      <pubDate>Thu, 19 Nov 2026 04:45:28 +0000</pubDate>
      <link>https://dev.to/kailas_warade_d2a15d1ef8a/sql-running-total-calculate-cumulative-daily-sales-using-sum-over-in-oracle-sql-3e6k</link>
      <guid>https://dev.to/kailas_warade_d2a15d1ef8a/sql-running-total-calculate-cumulative-daily-sales-using-sum-over-in-oracle-sql-3e6k</guid>
      <description>&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fn4j8lda3fp37bc6hfvse.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fn4j8lda3fp37bc6hfvse.png" alt=" " width="800" height="875"&gt;&lt;/a&gt;When working with sales data, you may come across a requirement to calculate how much revenue a business has generated up to a particular date.&lt;/p&gt;

&lt;p&gt;For example, a sales manager may want to see daily sales along with the total sales accumulated from the beginning of the month.&lt;/p&gt;

&lt;p&gt;This is called a Running Total or Cumulative Sum.&lt;/p&gt;

&lt;p&gt;In SQL, we can calculate it easily using the "SUM()" window function with the "OVER()" clause.&lt;/p&gt;

&lt;p&gt;In this SQL Challenge #105, let's see how to calculate a running total using Oracle SQL.&lt;/p&gt;

&lt;p&gt;The Sales Table&lt;/p&gt;

&lt;p&gt;Assume we have a table named "sales" containing daily sales information.&lt;/p&gt;

&lt;p&gt;sale_date| daily_sales&lt;br&gt;
01-Nov-2026| 1000&lt;br&gt;
02-Nov-2026| 1500&lt;br&gt;
03-Nov-2026| 800&lt;br&gt;
04-Nov-2026| 2000&lt;br&gt;
05-Nov-2026| 1200&lt;/p&gt;

&lt;p&gt;Our requirement is to calculate the cumulative sales for each date.&lt;/p&gt;

&lt;p&gt;Your Challenge&lt;/p&gt;

&lt;p&gt;Write an SQL query to:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Display sale date, daily sales and running total.&lt;/li&gt;
&lt;li&gt;Use "SUM()" to calculate cumulative sales.&lt;/li&gt;
&lt;li&gt;Arrange records by sale date, from oldest to newest.&lt;/li&gt;
&lt;li&gt;Add each day's sales to the previous running total.&lt;/li&gt;
&lt;li&gt;Display the running total for every date.&lt;/li&gt;
&lt;li&gt;Get the result without changing the original sales table.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Can you solve it before looking at the solution?&lt;/p&gt;

&lt;p&gt;The SQL Solution (Oracle)&lt;/p&gt;

&lt;p&gt;SELECT&lt;br&gt;
    sale_date,&lt;br&gt;
    daily_sales,&lt;br&gt;
    SUM(daily_sales) OVER (&lt;br&gt;
        ORDER BY sale_date&lt;br&gt;
        ROWS BETWEEN UNBOUNDED PRECEDING&lt;br&gt;
                 AND CURRENT ROW&lt;br&gt;
    ) AS running_total&lt;br&gt;
FROM sales&lt;br&gt;
ORDER BY sale_date;&lt;/p&gt;

&lt;p&gt;Expected Output&lt;/p&gt;

&lt;p&gt;sale_date| daily_sales| running_total&lt;br&gt;
01-Nov-2026| 1000| 1000&lt;br&gt;
02-Nov-2026| 1500| 2500&lt;br&gt;
03-Nov-2026| 800| 3300&lt;br&gt;
04-Nov-2026| 2000| 5300&lt;br&gt;
05-Nov-2026| 1200| 6500&lt;/p&gt;

&lt;p&gt;Notice how the running total increases with each new day's sales.&lt;/p&gt;

&lt;p&gt;For example:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Day 1: 1000&lt;/li&gt;
&lt;li&gt;Day 2: 1000 + 1500 = 2500&lt;/li&gt;
&lt;li&gt;Day 3: 2500 + 800 = 3300&lt;/li&gt;
&lt;li&gt;Day 4: 3300 + 2000 = 5300&lt;/li&gt;
&lt;li&gt;Day 5: 5300 + 1200 = 6500&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Why Does This SQL Query Work?&lt;/p&gt;

&lt;p&gt;Let's understand the important parts of the query.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;SUM(daily_sales)&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Adds the daily sales amounts.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;OVER()&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Allows us to use SUM() as a window function without combining all records into one row.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;ORDER BY sale_date&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Processes the sales records in chronological order.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;This defines the window frame:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;"UNBOUNDED PRECEDING" starts from the first row.&lt;/li&gt;
&lt;li&gt;"CURRENT ROW" includes the current row in the calculation.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;As a result, every row displays the total sales from the first date up to that particular date.&lt;/p&gt;

&lt;p&gt;One Thing to Remember&lt;/p&gt;

&lt;p&gt;There is an important difference between a normal aggregate function and a window function.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;"SUM()": Calculates the total for a group of records.&lt;/li&gt;
&lt;li&gt;"SUM() OVER()": Calculates totals while keeping individual rows.&lt;/li&gt;
&lt;li&gt;"ORDER BY": Determines the sequence of the running calculation.&lt;/li&gt;
&lt;li&gt;"PARTITION BY": Allows separate running totals for different groups.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Window functions are especially useful when building sales reports, financial reports and business dashboards.&lt;/p&gt;

&lt;p&gt;Your Turn&lt;/p&gt;

&lt;p&gt;How would you modify this query to calculate a separate running total for each product using "PARTITION BY"?&lt;/p&gt;

&lt;p&gt;Can you solve it without using a subquery or self-join?&lt;/p&gt;

&lt;p&gt;Share your SQL query in the comments.&lt;/p&gt;

&lt;p&gt;Follow this SQL Challenge series for more practical SQL problems covering joins, subqueries, CTEs, window functions, performance tuning and real-world reporting.&lt;/p&gt;

&lt;h1&gt;
  
  
  SQL #OracleSQL #WindowFunctions #DataAnalytics
&lt;/h1&gt;

</description>
      <category>sql</category>
      <category>oracle</category>
      <category>database</category>
      <category>tutorial</category>
    </item>
    <item>
      <title>SQL Challenge: Can You Find the Second Purchase of Every Customer Using ROW_NUMBER()?</title>
      <dc:creator>Kailas Warade</dc:creator>
      <pubDate>Wed, 18 Nov 2026 03:36:53 +0000</pubDate>
      <link>https://dev.to/kailas_warade_d2a15d1ef8a/sql-challenge-can-you-find-the-second-purchase-of-every-customer-using-rownumber-2fgp</link>
      <guid>https://dev.to/kailas_warade_d2a15d1ef8a/sql-challenge-can-you-find-the-second-purchase-of-every-customer-using-rownumber-2fgp</guid>
      <description>&lt;p&gt;In real-world data analysis, businesses often need to know when customers make their second purchase. This helps them understand buying habits, track repeat customers and study customer activity.&lt;/p&gt;

&lt;p&gt;You have an "orders" table with order IDs, customer IDs and order dates.&lt;/p&gt;

&lt;p&gt;Your task is to write an SQL query to find the second purchase made by each customer using the ROW_NUMBER() window function.&lt;/p&gt;

&lt;p&gt;Your Challenge&lt;/p&gt;

&lt;p&gt;Write an SQL query to:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Display customer ID, order ID and order date.&lt;/li&gt;
&lt;li&gt;Use ROW_NUMBER() to number each customer's orders.&lt;/li&gt;
&lt;li&gt;Use PARTITION BY to start the numbering again for every customer.&lt;/li&gt;
&lt;li&gt;Arrange orders by date, from oldest to newest.&lt;/li&gt;
&lt;li&gt;Return only the second order of each customer.&lt;/li&gt;
&lt;li&gt;Get the result without changing the original orders table.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;orders&lt;/p&gt;

&lt;p&gt;order_id|customer_id |order_date&lt;br&gt;
1001    | C101      | 05-Jan-2026&lt;br&gt;
1002   | C101      | 12-Jan-2026&lt;br&gt;
1003   | C101      | 20-Jan-2026&lt;br&gt;
2001   | C102      | 03-Feb-2026&lt;br&gt;
2002   | C102      | 10-Feb-2026&lt;br&gt;
3001  | C103&lt;br&gt;&lt;br&gt;
&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fcclpg37bwo6lr89t079e.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fcclpg37bwo6lr89t079e.png" alt=" " width="800" height="875"&gt;&lt;/a&gt;| 15-Feb-2026&lt;/p&gt;

&lt;p&gt;Can you write the query before checking the solution?&lt;/p&gt;

&lt;p&gt;The SQL Solution (Oracle)&lt;/p&gt;

&lt;p&gt;SELECT&lt;br&gt;
    customer_id,&lt;br&gt;
    order_id,&lt;br&gt;
    order_date&lt;br&gt;
FROM (&lt;br&gt;
    SELECT&lt;br&gt;
        customer_id,&lt;br&gt;
        order_id,&lt;br&gt;
        order_date,&lt;br&gt;
        ROW_NUMBER() OVER (&lt;br&gt;
            PARTITION BY customer_id&lt;br&gt;
            ORDER BY order_date, order_id&lt;br&gt;
        ) AS rn&lt;br&gt;
    FROM orders&lt;br&gt;
)&lt;br&gt;
WHERE rn = 2&lt;br&gt;
ORDER BY customer_id;&lt;/p&gt;

&lt;p&gt;Expected Output&lt;/p&gt;

&lt;p&gt;customer_id |order_id |order_date&lt;br&gt;
C101       | 1002   | 12-Jan-2026&lt;br&gt;
C102      | 2002    | 10-Feb-2026&lt;/p&gt;

&lt;p&gt;Customer C103 has only one order, so there is no second purchase to display.&lt;/p&gt;

&lt;p&gt;Why Does This SQL Query Work?&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;ROW_NUMBER() gives each order a number based on its position.&lt;/li&gt;
&lt;li&gt;PARTITION BY customer_id starts the numbering again for each customer.&lt;/li&gt;
&lt;li&gt;ORDER BY order_date arranges each customer's orders from oldest to newest.&lt;/li&gt;
&lt;li&gt;order_id helps decide the order when two purchases have the same date.&lt;/li&gt;
&lt;li&gt;WHERE rn = 2 returns only the second purchase of each customer.&lt;/li&gt;
&lt;li&gt;The original orders table remains unchanged because this is a SELECT query.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;One Thing to Remember:&lt;/p&gt;

&lt;p&gt;The PARTITION BY clause makes a big difference when working with customer purchase history.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Without PARTITION BY: All orders are numbered together.&lt;/li&gt;
&lt;li&gt;With PARTITION BY customer_id: Numbering starts again for every customer.&lt;/li&gt;
&lt;li&gt;ROW_NUMBER(): Gives every order a separate number, even when two orders have the same date.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;For example, if a customer has five orders, their orders will be numbered 1, 2, 3, 4 and 5.&lt;/p&gt;

&lt;p&gt;Your Turn: How would you change this query to find the third purchase of every customer?&lt;/p&gt;

&lt;p&gt;Share your SQL query in the comments!&lt;/p&gt;

&lt;p&gt;Follow Sankalan Data Tech for more practical SQL challenges, Oracle SQL examples, window functions and database interview questions.&lt;/p&gt;

&lt;h1&gt;
  
  
  SQL #SQLChallenge #OracleSQL #ROW_NUMBER #WindowFunctions #SQLQueries #DataAnalytics #LearnSQL #SQLInterview #Database
&lt;/h1&gt;

</description>
      <category>database</category>
      <category>sql</category>
      <category>rownumber</category>
    </item>
    <item>
      <title>SQL Challenge : How to Find the Top 2 Highest-Paid Employees in Each Department Using ROW_NUMBER()</title>
      <dc:creator>Kailas Warade</dc:creator>
      <pubDate>Tue, 17 Nov 2026 03:07:30 +0000</pubDate>
      <link>https://dev.to/kailas_warade_d2a15d1ef8a/sql-challenge-how-to-find-the-top-2-highest-paid-employees-in-each-department-using-rownumber-5318</link>
      <guid>https://dev.to/kailas_warade_d2a15d1ef8a/sql-challenge-how-to-find-the-top-2-highest-paid-employees-in-each-department-using-rownumber-5318</guid>
      <description>&lt;p&gt;SQL Challenge #103: Find the Top 2 Highest-Paid Employees in Every Department Using ROW_NUMBER()&lt;/p&gt;

&lt;p&gt;Window functions are very useful in SQL interviews and real-world data analysis. They help us rank records, compare values and solve problems that are difficult to handle with basic aggregate functions.&lt;/p&gt;

&lt;p&gt;In this SQL challenge, let's solve a common business requirement: finding the two highest-paid employees in every department.&lt;/p&gt;

&lt;p&gt;The Problem&lt;/p&gt;

&lt;p&gt;Suppose you have an "employees" table containing employee IDs, employee names, department IDs and salaries.&lt;/p&gt;

&lt;p&gt;You need to prepare a report that returns the top two highest-paid employees separately for each department.&lt;/p&gt;

&lt;p&gt;For example, if the IT department has five employees, your query should return only the two employees with the highest salaries in IT. The same logic should apply to every other department.&lt;/p&gt;

&lt;p&gt;Your Challenge&lt;/p&gt;

&lt;p&gt;Write a SQL query to:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Display employee ID, employee name, department ID and salary.&lt;/li&gt;
&lt;li&gt;Use "ROW_NUMBER()" to assign a unique number to each employee within their department.&lt;/li&gt;
&lt;li&gt;Use "PARTITION BY" to restart the numbering for every department.&lt;/li&gt;
&lt;li&gt;Sort employees by salary from highest to lowest.&lt;/li&gt;
&lt;li&gt;Retrieve only the top two highest-paid employees from each department.&lt;/li&gt;
&lt;li&gt;Retrieve the results without modifying the original employees table.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Sample Data: employees&lt;/p&gt;

&lt;p&gt;emp_id| emp_name| department_id| salary&lt;br&gt;
101| Rahul| 10| 80000&lt;br&gt;
102| Amit| 10| 65000&lt;br&gt;
103| Priya| 10| 90000&lt;br&gt;
104| Sneha| 20| 75000&lt;br&gt;
105| Karan| 20| 60000&lt;br&gt;
106| Neha| 20| 85000&lt;br&gt;
107| Raj| 30| 55000&lt;br&gt;
108| Pooja| 30| 70000&lt;br&gt;
109| Anil| 30| 50000&lt;/p&gt;

&lt;p&gt;Think about how "ROW_NUMBER()" and "PARTITION BY" work before looking at the solution.&lt;/p&gt;

&lt;p&gt;The SQL Solution (Oracle)&lt;/p&gt;

&lt;p&gt;SELECT&lt;br&gt;
    emp_id,&lt;br&gt;
    emp_name,&lt;br&gt;
    department_id,&lt;br&gt;
    salary&lt;br&gt;
FROM (&lt;br&gt;
    SELECT&lt;br&gt;
        emp_id,&lt;br&gt;
        emp_name,&lt;br&gt;
        department_id,&lt;br&gt;
        salary,&lt;br&gt;
        ROW_NUMBER() OVER (&lt;br&gt;
            PARTITION BY department_id&lt;br&gt;
            ORDER BY salary DESC, emp_id&lt;br&gt;
        ) AS rn&lt;br&gt;
    FROM employees&lt;br&gt;
)&lt;br&gt;
WHERE rn &amp;lt;= 2&lt;br&gt;
ORDER BY department_id, salary DESC;&lt;/p&gt;

&lt;p&gt;Expected Output&lt;/p&gt;

&lt;p&gt;emp_id| emp_name| department_id| salary&lt;br&gt;
103| Priya| 10| 90000&lt;br&gt;
101| Rahul| 10| 80000&lt;br&gt;
106| Neha| 20| 85000&lt;br&gt;
104| Sneha| 20| 75000&lt;br&gt;
108| Pooja| 30| 70000&lt;br&gt;
107| Raj| 30| 55000&lt;/p&gt;

&lt;p&gt;Why Does This SQL Query Work?&lt;/p&gt;

&lt;p&gt;Let's understand the important parts of the query.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;ROW_NUMBER()&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;"ROW_NUMBER()" assigns a unique sequential number to every employee within their department.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;PARTITION BY department_id&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;This is the most important part.&lt;/p&gt;

&lt;p&gt;"PARTITION BY" divides employees into separate department groups and restarts the numbering from 1 for each department.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;ORDER BY salary DESC&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;This sorts employees from the highest salary to the lowest within each department.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;emp_id&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;The additional "emp_id" column provides consistent ordering when two employees have the same salary.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;WHERE rn &amp;lt;= 2&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;The outer query filters the calculated row numbers and returns only the first two employees from each department.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Original Table Remains Unchanged&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;The query only retrieves and ranks records. It does not modify the original employees table.&lt;/p&gt;

&lt;p&gt;One Thing to Remember&lt;/p&gt;

&lt;p&gt;The main purpose of "PARTITION BY" is to perform calculations separately for different groups of records.&lt;/p&gt;

&lt;p&gt;Consider the difference:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Without "PARTITION BY": Employees are ranked across the entire company.&lt;/li&gt;
&lt;li&gt;With "PARTITION BY department_id": Ranking starts again from 1 for every department.&lt;/li&gt;
&lt;li&gt;"ROW_NUMBER()": Always assigns a unique number, even when multiple employees have the same salary.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Also, remember that "ROW_NUMBER()" returns exactly two employees per department when at least two employees exist, even if multiple employees have the same salary.&lt;/p&gt;

&lt;p&gt;Your Turn: Try This SQL Problem&lt;/p&gt;

&lt;p&gt;What if the requirement changes?&lt;/p&gt;

&lt;p&gt;Instead of finding the top two employees, the business wants to find the top three salary levels in every department, including all employees who share the same salary.&lt;/p&gt;

&lt;p&gt;Would you use "ROW_NUMBER()", "RANK()" or "DENSE_RANK()"?&lt;/p&gt;

&lt;p&gt;Share your SQL query in the comments. Let's discuss your approach!&lt;/p&gt;

&lt;h1&gt;
  
  
  SQL #OracleSQL #DataAnalytics #SQLChallenge
&lt;/h1&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F6tasi3gjo8mnn3gvl9nk.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F6tasi3gjo8mnn3gvl9nk.png" alt=" " width="800" height="800"&gt;&lt;/a&gt;&lt;/p&gt;

</description>
      <category>sql</category>
      <category>database</category>
      <category>tutorial</category>
      <category>programmers</category>
    </item>
    <item>
      <title>SQL Challenge : ROW_NUMBER() vs RANK() vs DENSE_RANK() – What's the Difference?</title>
      <dc:creator>Kailas Warade</dc:creator>
      <pubDate>Mon, 16 Nov 2026 03:51:03 +0000</pubDate>
      <link>https://dev.to/kailas_warade_d2a15d1ef8a/sql-challenge-rownumber-vs-rank-vs-denserank-whats-the-difference-58m9</link>
      <guid>https://dev.to/kailas_warade_d2a15d1ef8a/sql-challenge-rownumber-vs-rank-vs-denserank-whats-the-difference-58m9</guid>
      <description>&lt;p&gt;SQL window functions are useful when working with employee rankings, sales reports and business data.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Flz80bi6h6q3034krvenu.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Flz80bi6h6q3034krvenu.png" alt=" " width="800" height="800"&gt;&lt;/a&gt;&lt;br&gt;
Three commonly used ranking functions are "ROW_NUMBER()", "RANK()" and "DENSE_RANK()". Although they look similar, they produce different results when multiple records have the same value.&lt;/p&gt;

&lt;p&gt;If you want to understand SQL window functions in more detail, including "PARTITION BY", "LAG()", "LEAD()", running totals and interview questions, you can also explore this "SQL Window Functions and Analytic Functions Guide" (&lt;a href="https://www.sankalandtech.com/Tutorials/sql-plsql-faq-interview/window-functions-analytic-functions-sql-faq.html" rel="noopener noreferrer"&gt;https://www.sankalandtech.com/Tutorials/sql-plsql-faq-interview/window-functions-analytic-functions-sql-faq.html&lt;/a&gt;).&lt;/p&gt;

&lt;p&gt;The Problem&lt;/p&gt;

&lt;p&gt;Suppose you have an employees table containing employee IDs, employee names, departments and salaries.&lt;/p&gt;

&lt;p&gt;emp_id| emp_name| department| salary&lt;br&gt;
101| Amit| IT| 90000&lt;br&gt;
102| Rahul| IT| 80000&lt;br&gt;
103| Priya| HR| 80000&lt;br&gt;
104| Neha| HR| 70000&lt;br&gt;
105| Karan| IT| 80000&lt;br&gt;
106| Sneha| HR| 65000&lt;/p&gt;

&lt;p&gt;You need to compare the results of three SQL ranking functions when employees have equal salaries.&lt;/p&gt;

&lt;p&gt;SQL Solution (Oracle)&lt;/p&gt;

&lt;p&gt;SELECT&lt;br&gt;
    emp_id,&lt;br&gt;
    emp_name,&lt;br&gt;
    department,&lt;br&gt;
    salary,&lt;br&gt;
    ROW_NUMBER() OVER (&lt;br&gt;
        ORDER BY salary DESC, emp_id&lt;br&gt;
    ) AS row_num,&lt;br&gt;
    RANK() OVER (&lt;br&gt;
        ORDER BY salary DESC&lt;br&gt;
    ) AS salary_rank,&lt;br&gt;
    DENSE_RANK() OVER (&lt;br&gt;
        ORDER BY salary DESC&lt;br&gt;
    ) AS dense_rank&lt;br&gt;
FROM employees&lt;br&gt;
ORDER BY salary DESC, emp_id;&lt;/p&gt;

&lt;p&gt;Expected Output&lt;/p&gt;

&lt;p&gt;emp_id| emp_name| salary| row_num| salary_rank| dense_rank&lt;br&gt;
101| Amit| 90000| 1| 1| 1&lt;br&gt;
102| Rahul| 80000| 2| 2| 2&lt;br&gt;
103| Priya| 80000| 3| 2| 2&lt;br&gt;
105| Karan| 80000| 4| 2| 2&lt;br&gt;
104| Neha| 70000| 5| 5| 3&lt;br&gt;
106| Sneha| 65000| 6| 6| 4&lt;/p&gt;

&lt;p&gt;Understanding the Difference&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;"ROW_NUMBER()" assigns a unique sequential number to every row.&lt;/li&gt;
&lt;li&gt;"RANK()" assigns the same rank to equal salaries and skips subsequent rank numbers.&lt;/li&gt;
&lt;li&gt;"DENSE_RANK()" assigns the same rank to equal salaries without skipping rank numbers.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Your Turn&lt;/p&gt;

&lt;p&gt;Suppose you want to find the top three distinct salary levels in each department, including all employees who share the same salary.&lt;/p&gt;

&lt;p&gt;Which SQL ranking function would you use?&lt;/p&gt;

&lt;p&gt;Share your answer in the comments!&lt;/p&gt;

&lt;p&gt;For more practical SQL examples and interview preparation, explore the SQL window functions guide linked above.&lt;/p&gt;

&lt;h1&gt;
  
  
  sql #database #dataanalytics #programming
&lt;/h1&gt;

</description>
      <category>database</category>
      <category>sql</category>
      <category>tutorial</category>
      <category>windowfunction</category>
    </item>
    <item>
      <title>How to Rank Employees Within Each Department Using ROW_NUMBER()</title>
      <dc:creator>Kailas Warade</dc:creator>
      <pubDate>Fri, 13 Nov 2026 04:46:22 +0000</pubDate>
      <link>https://dev.to/kailas_warade_d2a15d1ef8a/how-to-rank-employees-within-each-department-using-rownumber-437j</link>
      <guid>https://dev.to/kailas_warade_d2a15d1ef8a/how-to-rank-employees-within-each-department-using-rownumber-437j</guid>
      <description>&lt;p&gt;Introduction&lt;/p&gt;

&lt;p&gt;When working with employee data, you may need to rank employees based on their salaries within each department.&lt;/p&gt;

&lt;p&gt;For example, the HR team may want to identify the highest-paid employee in every department.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fjbijicc4kwdzd7wybll0.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fjbijicc4kwdzd7wybll0.png" alt=" " width="800" height="800"&gt;&lt;/a&gt;&lt;br&gt;
SQL window functions make this task easier. In this challenge, we will use "ROW_NUMBER()" with "PARTITION BY" and "ORDER BY" to generate department-wise salary rankings.&lt;/p&gt;

&lt;p&gt;The Problem&lt;/p&gt;

&lt;p&gt;You have an "employees" table containing employee IDs, employee names, departments and salaries.&lt;/p&gt;

&lt;p&gt;Your task is to rank employees based on their salaries within each department, starting with the highest-paid employee.&lt;/p&gt;

&lt;p&gt;employees&lt;/p&gt;

&lt;p&gt;emp_id| emp_name| department| salary&lt;br&gt;
101| Amit| IT| 90000&lt;br&gt;
102| Rahul| IT| 75000&lt;br&gt;
103| Priya| HR| 80000&lt;br&gt;
104| Neha| HR| 70000&lt;br&gt;
105| Karan| IT| 85000&lt;br&gt;
106| Sneha| HR| 65000&lt;/p&gt;

&lt;p&gt;Your Challenge&lt;/p&gt;

&lt;p&gt;Write a SQL query to:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Display employee name, department and salary.&lt;/li&gt;
&lt;li&gt;Rank employees based on salary within each department.&lt;/li&gt;
&lt;li&gt;Assign rank 1 to the highest-paid employee.&lt;/li&gt;
&lt;li&gt;Restart the ranking for each department.&lt;/li&gt;
&lt;li&gt;Keep the original table unchanged.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Think about the solution before looking below!&lt;/p&gt;

&lt;p&gt;SQL Solution (Oracle)&lt;/p&gt;

&lt;p&gt;SELECT&lt;br&gt;
    emp_id,&lt;br&gt;
    emp_name,&lt;br&gt;
    department,&lt;br&gt;
    salary,&lt;br&gt;
    ROW_NUMBER() OVER (&lt;br&gt;
        PARTITION BY department&lt;br&gt;
        ORDER BY salary DESC&lt;br&gt;
    ) AS salary_rank&lt;br&gt;
FROM employees&lt;br&gt;
ORDER BY department, salary_rank;&lt;/p&gt;

&lt;p&gt;Expected Output&lt;/p&gt;

&lt;p&gt;emp_id| emp_name| department| salary| salary_rank&lt;br&gt;
103| Priya| HR| 80000| 1&lt;br&gt;
104| Neha| HR| 70000| 2&lt;br&gt;
106| Sneha| HR| 65000| 3&lt;br&gt;
101| Amit| IT| 90000| 1&lt;br&gt;
105| Karan| IT| 85000| 2&lt;br&gt;
102| Rahul| IT| 75000| 3&lt;/p&gt;

&lt;p&gt;How Does This Query Work?&lt;/p&gt;

&lt;p&gt;Let's understand the important parts.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;ROW_NUMBER()&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;ROW_NUMBER()&lt;/p&gt;

&lt;p&gt;It assigns a unique sequential number to each row, starting from 1.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;PARTITION BY department&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;PARTITION BY department&lt;/p&gt;

&lt;p&gt;This divides employees into separate department groups.&lt;/p&gt;

&lt;p&gt;The numbering starts again from 1 for every department.&lt;/p&gt;

&lt;p&gt;For example:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;HR employees get ranks 1, 2 and 3.&lt;/li&gt;
&lt;li&gt;IT employees also get ranks 1, 2 and 3.&lt;/li&gt;
&lt;/ul&gt;

&lt;ol&gt;
&lt;li&gt;ORDER BY salary DESC&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;ORDER BY salary DESC&lt;/p&gt;

&lt;p&gt;This sorts employees from the highest salary to the lowest salary within each department.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;AS salary_rank&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;This gives a meaningful name to the calculated ranking column.&lt;/p&gt;

&lt;p&gt;One Thing to Remember&lt;/p&gt;

&lt;p&gt;"ROW_NUMBER()" assigns a different number to every row, even when two employees have the same salary.&lt;/p&gt;

&lt;p&gt;If you want employees with equal salaries to receive the same rank, you should explore "RANK()" and "DENSE_RANK()".&lt;/p&gt;

&lt;p&gt;Also, if multiple employees have the same salary and you want consistent row numbering, you can add "emp_id" as a secondary sorting column.&lt;/p&gt;

&lt;p&gt;For example:&lt;/p&gt;

&lt;p&gt;ORDER BY salary DESC, emp_id&lt;/p&gt;

&lt;p&gt;ROW_NUMBER() vs RANK() vs DENSE_RANK()&lt;/p&gt;

&lt;p&gt;These three window functions are commonly used for ranking data.&lt;/p&gt;

&lt;p&gt;Function| Behaviour&lt;br&gt;
ROW_NUMBER()| Assigns a unique number to every row&lt;br&gt;
RANK()| Gives the same rank to tied values and skips the next rank&lt;br&gt;
DENSE_RANK()| Gives the same rank to tied values without skipping ranks&lt;/p&gt;

&lt;p&gt;Understanding the difference is useful when preparing salary reports, sales rankings and interview queries.&lt;/p&gt;

&lt;p&gt;Your Turn&lt;/p&gt;

&lt;p&gt;What would happen if we replaced "ROW_NUMBER()" with "RANK()" or "DENSE_RANK()"?&lt;/p&gt;

&lt;p&gt;Can you modify this query to find the top two highest-paid employees in each department?&lt;/p&gt;

&lt;p&gt;Share your SQL query in the comments.&lt;/p&gt;

&lt;p&gt;Follow Sankalan Data Tech for more practical SQL challenges, examples and database learning resources.&lt;/p&gt;

&lt;h1&gt;
  
  
  SQL #OracleSQL #DataAnalytics #Programming
&lt;/h1&gt;

</description>
      <category>sql</category>
      <category>dataengineering</category>
      <category>datascience</category>
      <category>dataanalytics</category>
    </item>
    <item>
      <title>SQL Challenge: Can You Build a Customer Report Using JOIN, GROUP BY and CASE WHEN?</title>
      <dc:creator>Kailas Warade</dc:creator>
      <pubDate>Thu, 12 Nov 2026 10:22:25 +0000</pubDate>
      <link>https://dev.to/kailas_warade_d2a15d1ef8a/sql-challenge-can-you-build-a-customer-report-using-join-group-by-and-case-when-2pp4</link>
      <guid>https://dev.to/kailas_warade_d2a15d1ef8a/sql-challenge-can-you-build-a-customer-report-using-join-group-by-and-case-when-2pp4</guid>
      <description>&lt;p&gt;Here is a practical problem you might face as a data analyst.&lt;/p&gt;

&lt;p&gt;You have two tables: customers and orders.&lt;/p&gt;

&lt;p&gt;The business team wants a report showing each customer's order count, total spending, latest order date and customer category.&lt;/p&gt;

&lt;p&gt;Customer categories:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;High Value: ₹1,00,000 or above&lt;/li&gt;
&lt;li&gt;Medium Value: ₹50,000 to below ₹1,00,000&lt;/li&gt;
&lt;li&gt;Low Value: Below ₹50,000&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Ftg1dpkt7y2h5coqt2f0u.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Ftg1dpkt7y2h5coqt2f0u.png" alt=" " width="800" height="800"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;customers&lt;/p&gt;

&lt;p&gt;customer_id | customer_name&lt;br&gt;
101 | Rahul&lt;br&gt;
102 | Priya&lt;br&gt;
103 | Amit&lt;br&gt;
104 | Neha&lt;/p&gt;

&lt;p&gt;orders&lt;/p&gt;

&lt;p&gt;order_id | customer_id | order_date | amount&lt;br&gt;
1 | 101 | 2026-01-10 | 40000&lt;br&gt;
2 | 101 | 2026-02-15 | 35000&lt;br&gt;
3 | 101 | 2026-03-20 | 30000&lt;br&gt;
4 | 102 | 2026-01-18 | 25000&lt;br&gt;
5 | 102 | 2026-03-12 | 30000&lt;br&gt;
6 | 103 | 2026-02-05 | 20000&lt;/p&gt;

&lt;p&gt;Your Challenge&lt;/p&gt;

&lt;p&gt;Write a SQL query to:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Display all customers, including those without orders.&lt;/li&gt;
&lt;li&gt;Calculate total orders and revenue.&lt;/li&gt;
&lt;li&gt;Find the latest order date.&lt;/li&gt;
&lt;li&gt;Assign customer categories using CASE WHEN.&lt;/li&gt;
&lt;li&gt;Keep the original tables unchanged.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Think before you scroll!&lt;/p&gt;

&lt;p&gt;The SQL Solution (Oracle)&lt;/p&gt;

&lt;p&gt;SELECT&lt;br&gt;
    c.customer_id,&lt;br&gt;
    c.customer_name,&lt;br&gt;
    COUNT(o.order_id) AS total_orders,&lt;br&gt;
    NVL(SUM(o.amount), 0) AS total_revenue,&lt;br&gt;
    MAX(o.order_date) AS latest_order_date,&lt;br&gt;
    CASE&lt;br&gt;
        WHEN NVL(SUM(o.amount), 0) &amp;gt;= 100000&lt;br&gt;
            THEN 'High Value'&lt;br&gt;
        WHEN NVL(SUM(o.amount), 0) &amp;gt;= 50000&lt;br&gt;
            THEN 'Medium Value'&lt;br&gt;
        ELSE 'Low Value'&lt;br&gt;
    END AS customer_category&lt;br&gt;
FROM customers c&lt;br&gt;
LEFT JOIN orders o&lt;br&gt;
    ON c.customer_id = o.customer_id&lt;br&gt;
GROUP BY c.customer_id, c.customer_name&lt;br&gt;
ORDER BY c.customer_id;&lt;/p&gt;

&lt;p&gt;Expected Output&lt;/p&gt;

&lt;p&gt;customer_id | customer_name | orders | revenue | latest_date | category&lt;br&gt;
101 | Rahul | 3 | 105000 | 2026-03-20 | High Value&lt;br&gt;
102 | Priya | 2 | 55000 | 2026-03-12 | Medium Value&lt;br&gt;
103 | Amit | 1 | 20000 | 2026-02-05 | Low Value&lt;br&gt;
104 | Neha | 0 | 0 | NULL | Low Value&lt;/p&gt;

&lt;p&gt;Why Does This Work?&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;LEFT JOIN keeps customers without orders.&lt;/li&gt;
&lt;li&gt;COUNT(o.order_id) counts actual orders.&lt;/li&gt;
&lt;li&gt;SUM() calculates total customer spending.&lt;/li&gt;
&lt;li&gt;NVL() replaces NULL revenue with 0.&lt;/li&gt;
&lt;li&gt;MAX() finds the latest order date.&lt;/li&gt;
&lt;li&gt;CASE WHEN assigns customer categories.&lt;/li&gt;
&lt;li&gt;GROUP BY creates one report row per customer.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;One Thing to Remember:&lt;/p&gt;

&lt;p&gt;With LEFT JOIN, COUNT(*) can return 1 for a customer without orders. Use COUNT(o.order_id) to get the correct count of 0.&lt;/p&gt;

&lt;p&gt;Your Turn&lt;/p&gt;

&lt;p&gt;How would you add average order value to this report?&lt;/p&gt;

&lt;p&gt;Share your SQL query in the comments!&lt;/p&gt;

&lt;h1&gt;
  
  
  SQL #SQLChallenge #OracleSQL #DataAnalytics #LearnSQL
&lt;/h1&gt;

</description>
      <category>database</category>
      <category>sql</category>
      <category>tutorial</category>
    </item>
    <item>
      <title>SQL Challenge: Can You Group Customers by Income Using CASE WHEN?</title>
      <dc:creator>Kailas Warade</dc:creator>
      <pubDate>Wed, 11 Nov 2026 03:31:53 +0000</pubDate>
      <link>https://dev.to/kailas_warade_d2a15d1ef8a/sql-challenge-can-you-group-customers-by-income-using-case-when-4cei</link>
      <guid>https://dev.to/kailas_warade_d2a15d1ef8a/sql-challenge-can-you-group-customers-by-income-using-case-when-4cei</guid>
      <description>&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/..." class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/..." alt="Uploading image" width="800" height="400"&gt;&lt;/a&gt;&lt;br&gt;
Here is a practical problem you might face while preparing a customer analysis report.&lt;/p&gt;

&lt;p&gt;You have a customers table containing customer IDs, names and annual income.&lt;/p&gt;

&lt;p&gt;The business team wants to understand how many customers fall into each income category.&lt;/p&gt;

&lt;p&gt;The categories are:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Low Income: Below ₹3,00,000&lt;/li&gt;
&lt;li&gt;Middle Income: ₹3,00,000 to ₹7,00,000&lt;/li&gt;
&lt;li&gt;High Income: Above ₹7,00,000&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;How would you create this report using SQL?&lt;/p&gt;

&lt;p&gt;customers&lt;/p&gt;

&lt;p&gt;customer_id | name  | annual_income&lt;br&gt;
101         | Rahul | 250000&lt;br&gt;
102         | Priya | 450000&lt;br&gt;
103         | Amit  | 850000&lt;br&gt;
104         | Sneha | 300000&lt;br&gt;
105         | Karan | 650000&lt;br&gt;
106         | Neha  | 950000&lt;br&gt;
107         | Rohan | 200000&lt;/p&gt;

&lt;p&gt;Your Challenge&lt;/p&gt;

&lt;p&gt;Write a query to:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Categorize customers using CASE WHEN.&lt;/li&gt;
&lt;li&gt;Count the number of customers in each category.&lt;/li&gt;
&lt;li&gt;Display the income category along with the total customer count.&lt;/li&gt;
&lt;li&gt;Do not change the original table data.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Think before you scroll!&lt;/p&gt;

&lt;p&gt;The SQL Solution (Oracle)&lt;/p&gt;

&lt;p&gt;SELECT&lt;br&gt;
    CASE&lt;br&gt;
        WHEN annual_income &amp;lt; 300000&lt;br&gt;
            THEN 'Low Income'&lt;br&gt;
        WHEN annual_income &amp;lt;= 700000&lt;br&gt;
            THEN 'Middle Income'&lt;br&gt;
        ELSE 'High Income'&lt;br&gt;
    END AS income_category,&lt;br&gt;
    COUNT(*) AS total_customers&lt;br&gt;
FROM customers&lt;br&gt;
GROUP BY&lt;br&gt;
    CASE&lt;br&gt;
        WHEN annual_income &amp;lt; 300000&lt;br&gt;
            THEN 'Low Income'&lt;br&gt;
        WHEN annual_income &amp;lt;= 700000&lt;br&gt;
            THEN 'Middle Income'&lt;br&gt;
        ELSE 'High Income'&lt;br&gt;
    END;&lt;/p&gt;

&lt;p&gt;Expected Output&lt;/p&gt;

&lt;p&gt;income_category | total_customers&lt;br&gt;
Low Income      | 2&lt;br&gt;
Middle Income   | 3&lt;br&gt;
High Income     | 2&lt;/p&gt;

&lt;p&gt;Why Does This Work?&lt;/p&gt;

&lt;p&gt;CASE WHEN checks the annual income of each customer and assigns an income category.&lt;/p&gt;

&lt;p&gt;Customers earning below ₹3,00,000 are placed in Low Income.&lt;/p&gt;

&lt;p&gt;Customers earning from ₹3,00,000 to ₹7,00,000 are placed in Middle Income.&lt;/p&gt;

&lt;p&gt;All remaining customers are placed in High Income.&lt;/p&gt;

&lt;p&gt;GROUP BY combines customers belonging to the same category.&lt;/p&gt;

&lt;p&gt;COUNT(*) calculates how many customers belong to each group.&lt;/p&gt;

&lt;p&gt;That's it. CASE WHEN and GROUP BY help convert individual customer records into a useful business report.&lt;/p&gt;

&lt;p&gt;One Thing to Remember:&lt;/p&gt;

&lt;p&gt;The order of conditions in CASE WHEN matters.&lt;/p&gt;

&lt;p&gt;Once a condition is satisfied, SQL returns its corresponding result without checking the remaining conditions.&lt;/p&gt;

&lt;p&gt;Your Turn&lt;/p&gt;

&lt;p&gt;Suppose you want to display the actual customer names along with their income categories instead of just counting customers.&lt;/p&gt;

&lt;p&gt;How would you modify this query?&lt;/p&gt;

&lt;p&gt;Share your SQL query in the comments!&lt;/p&gt;

&lt;h1&gt;
  
  
  SQL #SQLChallenge #OracleSQL #DataAnalytics #LearnSQL
&lt;/h1&gt;

</description>
      <category>database</category>
      <category>tutorial</category>
      <category>sql</category>
      <category>oracle</category>
    </item>
    <item>
      <title>Extract Email Domains Using INSTR() and SUBSTR() in Oracle SQL</title>
      <dc:creator>Kailas Warade</dc:creator>
      <pubDate>Tue, 10 Nov 2026 00:56:55 +0000</pubDate>
      <link>https://dev.to/kailas_warade_d2a15d1ef8a/extract-email-domains-using-instr-and-substr-in-oracle-sql-e0o</link>
      <guid>https://dev.to/kailas_warade_d2a15d1ef8a/extract-email-domains-using-instr-and-substr-in-oracle-sql-e0o</guid>
      <description>&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F8lp3okwo4cv7m74o9p4g.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F8lp3okwo4cv7m74o9p4g.png" alt=" " width="800" height="800"&gt;&lt;/a&gt;&lt;br&gt;
SQL Challenge #98: How to Extract an Email Domain Using INSTR() and SUBSTR() in Oracle SQL&lt;/p&gt;

&lt;p&gt;Sometimes, while working with employee or customer records, you may need to get the domain name separately from an email address.&lt;/p&gt;

&lt;p&gt;For example:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;a href="mailto:kailas@gmail.com"&gt;kailas@gmail.com&lt;/a&gt; → gmail.com&lt;/li&gt;
&lt;li&gt;
&lt;a href="mailto:rahul@yahoo.com"&gt;rahul@yahoo.com&lt;/a&gt; → yahoo.com&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Let's see how to do this using two simple Oracle SQL functions.&lt;/p&gt;

&lt;p&gt;The Problem&lt;/p&gt;

&lt;p&gt;You have an "employees" table containing email addresses.&lt;/p&gt;

&lt;p&gt;You need to write a query that extracts everything after the "@" symbol and displays it in a separate column.&lt;/p&gt;

&lt;p&gt;Input: employees&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;emp_id&lt;/th&gt;
&lt;th&gt;email&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;101&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:kailas@gmail.com"&gt;kailas@gmail.com&lt;/a&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;102&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:rahul@yahoo.com"&gt;rahul@yahoo.com&lt;/a&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;103&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:priya@outlook.com"&gt;priya@outlook.com&lt;/a&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;104&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:amit@company.in"&gt;amit@company.in&lt;/a&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;105&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:sneha@gmail.com"&gt;sneha@gmail.com&lt;/a&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Your Challenge&lt;/p&gt;

&lt;p&gt;Write a query to:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Find the position of "@" in each email.&lt;/li&gt;
&lt;li&gt;Extract everything after "@".&lt;/li&gt;
&lt;li&gt;Display the email and its domain.&lt;/li&gt;
&lt;li&gt;Keep the original table data unchanged.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Think about the query before looking at the solution!&lt;/p&gt;

&lt;p&gt;Oracle SQL Solution&lt;/p&gt;

&lt;p&gt;SELECT emp_id,&lt;br&gt;
       email,&lt;br&gt;
       SUBSTR(email, INSTR(email, '@') + 1) AS domain&lt;br&gt;
FROM employees;&lt;/p&gt;

&lt;p&gt;Expected Output&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;emp_id&lt;/th&gt;
&lt;th&gt;email&lt;/th&gt;
&lt;th&gt;domain&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;101&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:kailas@gmail.com"&gt;kailas@gmail.com&lt;/a&gt;&lt;/td&gt;
&lt;td&gt;gmail.com&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;102&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:rahul@yahoo.com"&gt;rahul@yahoo.com&lt;/a&gt;&lt;/td&gt;
&lt;td&gt;yahoo.com&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;103&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:priya@outlook.com"&gt;priya@outlook.com&lt;/a&gt;&lt;/td&gt;
&lt;td&gt;outlook.com&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;104&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:amit@company.in"&gt;amit@company.in&lt;/a&gt;&lt;/td&gt;
&lt;td&gt;company.in&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;105&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:sneha@gmail.com"&gt;sneha@gmail.com&lt;/a&gt;&lt;/td&gt;
&lt;td&gt;gmail.com&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;How Does It Work?&lt;/p&gt;

&lt;p&gt;Let's understand the two functions used in this query.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;INSTR()&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;"INSTR()" finds the position of a character in a string.&lt;/p&gt;

&lt;p&gt;SELECT INSTR('&lt;a href="mailto:kailas@gmail.com"&gt;kailas@gmail.com&lt;/a&gt;', '@')&lt;br&gt;
FROM dual;&lt;/p&gt;

&lt;p&gt;Output:&lt;/p&gt;

&lt;p&gt;7&lt;/p&gt;

&lt;p&gt;The "@" symbol is at position 7.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;SUBSTR()&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;"SUBSTR()" extracts characters from a given position.&lt;/p&gt;

&lt;p&gt;SELECT SUBSTR('&lt;a href="mailto:kailas@gmail.com"&gt;kailas@gmail.com&lt;/a&gt;', 8)&lt;br&gt;
FROM dual;&lt;/p&gt;

&lt;p&gt;Output:&lt;/p&gt;

&lt;p&gt;gmail.com&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Using Both Functions Together&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;SUBSTR(email, INSTR(email, '@') + 1)&lt;/p&gt;

&lt;p&gt;First, "INSTR()" finds the position of "@".&lt;/p&gt;

&lt;p&gt;Then, we add 1 to start from the next character.&lt;/p&gt;

&lt;p&gt;Finally, "SUBSTR()" extracts the remaining characters.&lt;/p&gt;

&lt;p&gt;That's how we get the email domain in a single query.&lt;/p&gt;

&lt;p&gt;Note: This query assumes that the email address contains an "@" symbol. Invalid email addresses should be checked separately.&lt;/p&gt;

&lt;p&gt;Your Turn&lt;/p&gt;

&lt;p&gt;Now try the opposite.&lt;/p&gt;

&lt;p&gt;Suppose you have:&lt;/p&gt;

&lt;p&gt;&lt;a href="mailto:kailas@gmail.com"&gt;kailas@gmail.com&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;You want to extract only:&lt;/p&gt;

&lt;p&gt;kailas&lt;/p&gt;

&lt;p&gt;Which SQL function or combination of functions would you use?&lt;/p&gt;

&lt;p&gt;Write your query in the comments. Let's see how you solve it!&lt;/p&gt;

&lt;p&gt;Follow for more practical SQL questions and solutions.&lt;/p&gt;

&lt;h1&gt;
  
  
  SQL #OracleSQL #SQLChallenge #DataAnalytics #LearnSQL #SQLFunctions #Database
&lt;/h1&gt;

</description>
      <category>datascience</category>
      <category>tutorial</category>
      <category>sql</category>
      <category>oracle</category>
    </item>
    <item>
      <title>How to Clean Messy Employee Names in Oracle SQL Using TRIM() and LOWER()</title>
      <dc:creator>Kailas Warade</dc:creator>
      <pubDate>Mon, 09 Nov 2026 03:52:00 +0000</pubDate>
      <link>https://dev.to/kailas_warade_d2a15d1ef8a/how-to-clean-messy-employee-names-in-oracle-sql-using-trim-and-lower-4mdm</link>
      <guid>https://dev.to/kailas_warade_d2a15d1ef8a/how-to-clean-messy-employee-names-in-oracle-sql-using-trim-and-lower-4mdm</guid>
      <description>&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fzoerx34ssae4huelw6ap.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fzoerx34ssae4huelw6ap.png" alt=" " width="800" height="800"&gt;&lt;/a&gt;&lt;br&gt;
How to Clean Messy Employee Names in Oracle SQL Using TRIM() and LOWER()&lt;/p&gt;

&lt;p&gt;Have you ever worked with an employee table where names are stored in different formats?&lt;/p&gt;

&lt;p&gt;For example, some names are in uppercase, others use mixed case, and a few have unwanted spaces around them.&lt;/p&gt;

&lt;p&gt;Consider this sample employee data:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;emp_id&lt;/th&gt;
&lt;th&gt;employee_name&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;101&lt;/td&gt;
&lt;td&gt;JOHN&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;102&lt;/td&gt;
&lt;td&gt;Mary&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;103&lt;/td&gt;
&lt;td&gt;DAVID&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;104&lt;/td&gt;
&lt;td&gt;SARAH&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;105&lt;/td&gt;
&lt;td&gt;Robert&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The names themselves are correct, but their formats are inconsistent.&lt;/p&gt;

&lt;p&gt;This can become a problem when comparing records, preparing reports, or working with data from multiple sources.&lt;/p&gt;

&lt;p&gt;Let's clean these values using Oracle SQL.&lt;/p&gt;

&lt;p&gt;The requirement&lt;/p&gt;

&lt;p&gt;We want to:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Remove leading and trailing spaces.&lt;/li&gt;
&lt;li&gt;Convert all names into lowercase.&lt;/li&gt;
&lt;li&gt;Display the cleaned values without updating the original table.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Oracle SQL query&lt;/p&gt;

&lt;p&gt;SELECT emp_id,&lt;br&gt;
       LOWER(TRIM(employee_name)) AS cleaned_name&lt;br&gt;
FROM employees;&lt;/p&gt;

&lt;p&gt;Expected output:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;emp_id&lt;/th&gt;
&lt;th&gt;cleaned_name&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;101&lt;/td&gt;
&lt;td&gt;john&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;102&lt;/td&gt;
&lt;td&gt;mary&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;103&lt;/td&gt;
&lt;td&gt;david&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;104&lt;/td&gt;
&lt;td&gt;sarah&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;105&lt;/td&gt;
&lt;td&gt;robert&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The result is now consistent across all employee records.&lt;/p&gt;

&lt;p&gt;Understanding the two functions&lt;/p&gt;

&lt;p&gt;Let's look at each function separately.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;TRIM()&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;"TRIM()" removes spaces from the beginning and end of a string.&lt;/p&gt;

&lt;p&gt;SELECT TRIM('   JOHN   ') AS name&lt;br&gt;
FROM dual;&lt;/p&gt;

&lt;p&gt;Output:&lt;/p&gt;

&lt;p&gt;JOHN&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;LOWER()&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;"LOWER()" converts alphabetic characters into lowercase.&lt;/p&gt;

&lt;p&gt;SELECT LOWER('JOHN') AS name&lt;br&gt;
FROM dual;&lt;/p&gt;

&lt;p&gt;Output:&lt;/p&gt;

&lt;p&gt;john&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Combining TRIM() and LOWER()&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;In our original query:&lt;/p&gt;

&lt;p&gt;LOWER(TRIM(employee_name))&lt;/p&gt;

&lt;p&gt;Oracle evaluates the inner function first.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;"TRIM()" removes unwanted spaces.&lt;/li&gt;
&lt;li&gt;"LOWER()" converts the remaining text into lowercase.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;For example:&lt;/p&gt;

&lt;p&gt;'   JOHN   '&lt;br&gt;
      |&lt;br&gt;
    TRIM()&lt;br&gt;
      |&lt;br&gt;
    'JOHN'&lt;br&gt;
      |&lt;br&gt;
    LOWER()&lt;br&gt;
      |&lt;br&gt;
    'john'&lt;/p&gt;

&lt;p&gt;One important point: this SELECT statement only displays the transformed values. It does not modify the original employee records.&lt;/p&gt;

&lt;p&gt;What if there are extra spaces between words?&lt;/p&gt;

&lt;p&gt;Here is something worth testing.&lt;/p&gt;

&lt;p&gt;SELECT TRIM('  JOHN  SMITH  ') AS name&lt;br&gt;
FROM dual;&lt;/p&gt;

&lt;p&gt;Output:&lt;/p&gt;

&lt;p&gt;JOHN  SMITH&lt;/p&gt;

&lt;p&gt;Notice that the spaces between "JOHN" and "SMITH" are still present.&lt;/p&gt;

&lt;p&gt;If you want to display names in title case, Oracle provides another function called "INITCAP()".&lt;/p&gt;

&lt;p&gt;SELECT INITCAP(TRIM(employee_name)) AS formatted_name&lt;br&gt;
FROM employees;&lt;/p&gt;

&lt;p&gt;For example:&lt;/p&gt;

&lt;p&gt;john smith&lt;/p&gt;

&lt;p&gt;Becomes:&lt;/p&gt;

&lt;p&gt;John Smith&lt;/p&gt;

&lt;p&gt;Your turn: Test your Oracle SQL knowledge&lt;/p&gt;

&lt;p&gt;Which Oracle SQL function converts:&lt;/p&gt;

&lt;p&gt;"john smith"&lt;/p&gt;

&lt;p&gt;Into:&lt;/p&gt;

&lt;p&gt;"John Smith"&lt;/p&gt;

&lt;p&gt;A. "UPPER()"&lt;/p&gt;

&lt;p&gt;B. "LOWER()"&lt;/p&gt;

&lt;p&gt;C. "INITCAP()"&lt;/p&gt;

&lt;p&gt;D. "TRIM()"&lt;/p&gt;

&lt;p&gt;What is your answer? Share it in the comments.&lt;/p&gt;

&lt;p&gt;A quick reference&lt;/p&gt;

&lt;p&gt;Function| What it does&lt;br&gt;
"TRIM()"| Removes leading and trailing spaces&lt;br&gt;
"LOWER()"| Converts text to lowercase&lt;br&gt;
"UPPER()"| Converts text to uppercase&lt;br&gt;
"INITCAP()"| Capitalises the first letter of each word&lt;/p&gt;

&lt;p&gt;These functions are simple, but they are useful when cleaning text data in everyday SQL work.&lt;/p&gt;

&lt;p&gt;The next time you receive inconsistent employee names, try combining "TRIM()" and "LOWER()" before using the data in your analysis.&lt;/p&gt;

</description>
      <category>database</category>
      <category>sql</category>
      <category>oracle</category>
      <category>dataanalytics</category>
    </item>
    <item>
      <title>Can You Find Employees Who Joined This Month Using SQL? (Oracle TRUNC &amp; ADD_MONTHS)</title>
      <dc:creator>Kailas Warade</dc:creator>
      <pubDate>Sun, 08 Nov 2026 02:28:13 +0000</pubDate>
      <link>https://dev.to/kailas_warade_d2a15d1ef8a/can-you-find-employees-who-joined-this-month-using-sql-oracle-trunc-addmonths-518c</link>
      <guid>https://dev.to/kailas_warade_d2a15d1ef8a/can-you-find-employees-who-joined-this-month-using-sql-oracle-trunc-addmonths-518c</guid>
      <description>&lt;p&gt;Your HR manager asks you to find all employees who joined during the current month.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F2re4e1qv9jm0dn79nlce.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F2re4e1qv9jm0dn79nlce.png" alt=" " width="800" height="800"&gt;&lt;/a&gt;&lt;br&gt;
You could filter for November 2026, but would you have to change the query when December starts?&lt;/p&gt;

&lt;p&gt;Can you make it dynamic?&lt;/p&gt;

&lt;p&gt;employees&lt;/p&gt;

&lt;p&gt;emp_id|employee_name|joining_date&lt;br&gt;
101    | Amit        | 2026-11-02&lt;br&gt;
102    | Neha        | 2026-10-25&lt;br&gt;
103    | Rahul       | 2026-11-05&lt;br&gt;
104    | Priya       | 2026-09-18&lt;br&gt;
105    | Sneha       | 2026-11-08&lt;br&gt;
106    | Karan       | 2026-12-01&lt;/p&gt;

&lt;p&gt;Your Challenge&lt;/p&gt;

&lt;p&gt;Write a query to:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Find employees who joined this month.&lt;/li&gt;
&lt;li&gt;Exclude previous and future months.&lt;/li&gt;
&lt;li&gt;Avoid hardcoding dates.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Think before you scroll!&lt;/p&gt;

&lt;p&gt;The SQL Solution (Oracle)&lt;/p&gt;

&lt;p&gt;SELECT emp_id, employee_name, joining_date&lt;br&gt;
FROM employees&lt;br&gt;
WHERE joining_date &amp;gt;= TRUNC(SYSDATE, 'MM')&lt;br&gt;
  AND joining_date &amp;lt; ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1);&lt;/p&gt;

&lt;p&gt;Expected Output&lt;/p&gt;

&lt;p&gt;emp_id|employee_name|joining_date&lt;br&gt;
101    | Amit        | 2026-11-02&lt;br&gt;
103    | Rahul       | 2026-11-05&lt;br&gt;
105    | Sneha       | 2026-11-08&lt;/p&gt;

&lt;p&gt;Why Does This Work?&lt;/p&gt;

&lt;p&gt;"TRUNC(SYSDATE, 'MM')" returns the first day of the current month.&lt;/p&gt;

&lt;p&gt;"ADD_MONTHS(..., 1)" returns the first day of the next month.&lt;/p&gt;

&lt;p&gt;So the query retrieves everything from the beginning of this month up to, but not including, the next month.&lt;/p&gt;

&lt;p&gt;One important point:&lt;/p&gt;

&lt;p&gt;Using BETWEEN with the first day of the next month as the ending date can cause timestamp-related issues.&lt;/p&gt;

&lt;p&gt;This pattern is safer:&lt;/p&gt;

&lt;p&gt;date_column &amp;gt;= start_date&lt;br&gt;
AND date_column &amp;lt; next_period_start&lt;/p&gt;

&lt;p&gt;You can use it for daily and monthly reports too.&lt;/p&gt;

&lt;p&gt;Learn More&lt;/p&gt;

&lt;p&gt;Explore SQL date functions like TRUNC(), EXTRACT() and TO_CHAR():&lt;/p&gt;

&lt;p&gt;&lt;a href="https://www.sankalandtech.com/Tutorials/sql-plsql-faq-interview/conversion-functions-conditional-expressions-sql-faq.html" rel="noopener noreferrer"&gt;https://www.sankalandtech.com/Tutorials/sql-plsql-faq-interview/conversion-functions-conditional-expressions-sql-faq.html&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Your Turn&lt;/p&gt;

&lt;p&gt;How would you find employees who joined during the previous month?&lt;/p&gt;

&lt;p&gt;Share your query in the comments!&lt;/p&gt;

&lt;h1&gt;
  
  
  SQL #SQLChallenge #OracleSQL #DataAnalytics #LearnSQL
&lt;/h1&gt;

</description>
      <category>sql</category>
      <category>database</category>
      <category>dataengineering</category>
      <category>datascience</category>
    </item>
    <item>
      <title>SQL Challenge #95: BETWEEN With Dates — Can It Miss Records?</title>
      <dc:creator>Kailas Warade</dc:creator>
      <pubDate>Sat, 07 Nov 2026 03:09:11 +0000</pubDate>
      <link>https://dev.to/kailas_warade_d2a15d1ef8a/sql-challenge-95-between-with-dates-can-it-miss-records-11nd</link>
      <guid>https://dev.to/kailas_warade_d2a15d1ef8a/sql-challenge-95-between-with-dates-can-it-miss-records-11nd</guid>
      <description>&lt;p&gt;Have you ever missed records while filtering dates in SQL?&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fryi95v77lho5xnlpluyq.jpg" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fryi95v77lho5xnlpluyq.jpg" alt=" " width="800" height="763"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Imagine your manager asks you to prepare a report showing all orders placed on 7 November 2026.&lt;/p&gt;

&lt;p&gt;Your "orders" table contains both dates and timestamps, such as "2026-11-07 14:30:00".&lt;/p&gt;

&lt;p&gt;You decide to use BETWEEN to filter the records.&lt;/p&gt;

&lt;p&gt;But will your query return all orders placed on that day?&lt;/p&gt;

&lt;p&gt;Let's look at the data.&lt;/p&gt;

&lt;p&gt;orders&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;order_id&lt;/th&gt;
&lt;th&gt;order_date&lt;/th&gt;
&lt;th&gt;amount&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;101&lt;/td&gt;
&lt;td&gt;2026-11-07 09:30:00&lt;/td&gt;
&lt;td&gt;5000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;102&lt;/td&gt;
&lt;td&gt;2026-11-07 14:45:00&lt;/td&gt;
&lt;td&gt;3000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;103&lt;/td&gt;
&lt;td&gt;2026-11-07 22:15:00&lt;/td&gt;
&lt;td&gt;7000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;104&lt;/td&gt;
&lt;td&gt;2026-11-08 08:00:00&lt;/td&gt;
&lt;td&gt;4000&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Your Challenge&lt;/p&gt;

&lt;p&gt;Write an SQL query to:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Retrieve all orders placed on 7 November 2026.&lt;/li&gt;
&lt;li&gt;Include orders placed at any time during that day.&lt;/li&gt;
&lt;li&gt;Exclude orders placed on 8 November 2026.&lt;/li&gt;
&lt;li&gt;Use a date range condition to filter the records.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Think before you scroll!&lt;/p&gt;

&lt;p&gt;The SQL Solution&lt;/p&gt;

&lt;p&gt;SELECT&lt;br&gt;
    order_id,&lt;br&gt;
    order_date,&lt;br&gt;
    amount&lt;br&gt;
FROM orders&lt;br&gt;
WHERE order_date &amp;gt;= '2026-11-07'&lt;br&gt;
  AND order_date &amp;lt; '2026-11-08';&lt;/p&gt;

&lt;p&gt;Expected Output&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;order_id&lt;/th&gt;
&lt;th&gt;order_date&lt;/th&gt;
&lt;th&gt;amount&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;101&lt;/td&gt;
&lt;td&gt;2026-11-07 09:30:00&lt;/td&gt;
&lt;td&gt;5000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;102&lt;/td&gt;
&lt;td&gt;2026-11-07 14:45:00&lt;/td&gt;
&lt;td&gt;3000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;103&lt;/td&gt;
&lt;td&gt;2026-11-07 22:15:00&lt;/td&gt;
&lt;td&gt;7000&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Why is this useful?&lt;/p&gt;

&lt;p&gt;Date filtering is common in real-world SQL projects.&lt;/p&gt;

&lt;p&gt;You can use this approach to find:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Daily sales transactions&lt;/li&gt;
&lt;li&gt;Customer registrations&lt;/li&gt;
&lt;li&gt;Employee attendance records&lt;/li&gt;
&lt;li&gt;Orders placed during a specific date&lt;/li&gt;
&lt;li&gt;Daily revenue for business reports&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;One Important Thing to Remember&lt;/p&gt;

&lt;p&gt;BETWEEN includes both boundary values.&lt;/p&gt;

&lt;p&gt;When you write:&lt;/p&gt;

&lt;p&gt;WHERE order_date BETWEEN '2026-11-07'&lt;br&gt;
                     AND '2026-11-07'&lt;/p&gt;

&lt;p&gt;Many SQL databases interpret the ending date as midnight at the start of that day when comparing against a timestamp.&lt;/p&gt;

&lt;p&gt;As a result, orders placed later that day may be missed.&lt;/p&gt;

&lt;p&gt;Using "&amp;gt;= start_date AND &amp;lt; next_date" is a reliable approach for timestamp-based date filtering.&lt;/p&gt;

&lt;p&gt;Your Turn: Can you solve the same problem using BETWEEN without missing any orders?&lt;/p&gt;

&lt;p&gt;Share your SQL query in the comments. Let's learn together!&lt;/p&gt;

&lt;h1&gt;
  
  
  SQL #SQLChallenge #MySQL #DataAnalytics #LearnSQL
&lt;/h1&gt;

</description>
      <category>sql</category>
      <category>database</category>
      <category>programming</category>
      <category>tutorial</category>
    </item>
  </channel>
</rss>
