SQL interview question | Write the query

Question

We have two columns(Revenue and Cost Price) in a
table like below, get me the Profit column

Revenue       Cost Price        Profit
100                  Null                 100
200                  20                    180
300                  50                    250
Null                 50                     -50

solved 3
TheDataMonk 1 year 41 Answers 1846 views Grand Master 2

Answers ( 41 )

  1. select (Revenue – Cost Price) as Profit from table

  2. Select t.Revenue – COALESCE (t.’Cost Price’,0) Profit from table t

  3. Sorry it a private answer.

  4. Select revenue, costprice, (revenue – costprice) as profit from table_name

    • Select [Revenue],
      [Cost Price],
      (Coalesce ( [Revenue],0) –
      Coalesce ( [Cost Price],0) ) As
      [Profit]
      From Table

  5. Select revenue, costprice, (revenue – costprice) as profit from table

  6. SELECT *,
    CASE WHEN revenue is NULL and cost is NULL Then 0
    WHEN revenue is not NULL and cost is NULL THEN Revenue
    WHEN revenue is NULL and cost is not NULL THEN -1 * cost
    ELSE revenue – cost
    FROM Table

  7. SELECT (Revenue – Cost Price)
    AS Profit
    FROM table ;

  8. SELECT (Revenue – Cost Price) AS Profit from TABLE

  9. SELECT revenue,cost,(revenue-COALESCE(cost,0))profit

  10. Select o.Revenue – COALESCE (o.’Cost Price’,0) Profit from table o

  11. Select revenue – Coalesce (cost price, 0) as profit from table_name

  12. We can achieve the required result using NVL function OR Case statement:

    1. SELECT Revenue, Cost_Price, NVL(Revenue,0) – NVL(Cost_Price,0) as Profit
    FROM Table_Name;

    2. SELECT Revenue, Cost_Price,
    (CASE WHEN Revenue IS NULL and Cost_Price IS NULL Then 0
    WHEN Revenue IS NOT NULL and Cost_Price IS NULL THEN Revenue
    WHEN Revenue is NULL and Cost_Price IS NOT NULL THEN Cost_Price*-1
    ELSE Revenue – Cost_Price) as Profit
    FROM Table_Name

    0

    Select table.Revenue, table.Cost_Price, coalesce(table.Revenue,0) – coalesce(table.Cost_Price,0) as Profit

    0

    select revenue,costPrice,coalesce(revenue,0)-coalesce(costPrice,0) as profit from table

  13. Select revenue – Coalesce (cost price, 0) as profit from table_name

  14. SELECT
    Revenue
    ,”Cost Price”
    ,COALESCE(Revenue,0) – COALESCE(“Cost Price”,0) AS Profit
    FROM
    table

  15. select (coalesce(Revenue, 0) – coalesce(`Cost Price`, 0)) as Profit from table;

  16. Select (Coalesce(revenue,0) – Coalesce (cost price, 0))as profit
    from Table;

  17. SELECT
    Revenue, ”Cost Price”,
    COALESCE(Revenue,0) – COALESCE(“Cost Price”,0) AS Profit
    FROM
    table_name;

  18. select (nvl(revenue,0)-nvl(‘cost price’,0)) as profit from table

  19. Whatever NULL touches in an expression, it renders the result NULL. So, when you’re dealing with NULL, how can you break out of the cycle? That is, how can you display another value instead?

    — Using COALESCE

    Query:
    Let’s give a name to the table – Expenditure

    SELECT Revenue, Cost Price, (Revenue – COALESCE(Cost Price, 0)) as Profit
    FROM Expenditure;

  20. select coalesce(Revenue,0) – coalesce( Cost Price, 0) as profit
    from table_name

  21. Select revenue – COALESCE (cost price, 0) as profit from table_name

  22. Query:
    SELECT Revenue,’Cost Price’,(COALESCE(Revenue,0)-COALESCE(‘Cost Price’,0)) as Profit FROM table;

  23. Select Revenue,[Cost price], Revenue-[Cost price] as “Profit” from table

  24. select revenue, “cost price”, COALESCE(revenue, 0) – COALESCE(“cost price”, 0) as profit from tablename;

  25. SELECT
    [Revenue], [Cost Price],
    COALESCE(Revenue,0) -COALESCE( Cost Price,0)
    AS
    [Profit]
    from table

  26. SELECT (COALESCE(Revenue,0) – COALESCE(Cost Price, 0)) As Profit
    FROM Table_Name

  27. SELECT
    [Revenue], [Cost Price],
    COALESCE(Revenue,0) -COALESCE( Cost Price,0)
    AS
    [Profit]
    from table

  28. SELECT Revenue,”Cost Price”,(Revenue-“Cost Price”) as Profit FROM table

  29. Select Revenue, Cost_Price, Coalesce(Revenue,0) – Coalesce(Cost_Price) as Profit
    From Table_Name

  30. SELECT [Revenue],
    [Cost Price],
    (COALESCE ( [Revenue],0) –
    COALESCE ( [Cost Price],0) ) As
    [Profit]
    FROM Table

  31. select (ifnull(revenue,0) – ifnull(cost,0)) as Profit from table

    0

    select (Revenue – Cost Price) as Profit From table;

  32. select ifnull(Revenue, 0)-ifnull(Cost Price,0) as Profit from table table_name;

  33. select revenue,cost_price,(revenue-cost_price) as profit from table.

  34. SELECT Revenue, Cost Price, COALESCE(Revenue,0) – COALESCE(Cost Price, 0) Profit
    FROM table;

Leave an answer

Browse
Browse