Some Usefull Sql Queries

1.  To separate number from text (SELECT ltrim('6372Tech', '0123456789') FROM DUAL; )
2. Translate character to 'x' (SELECT translate('1tech23', '0123456789', 'xxxxxxxxx') FROM DUAL;)
3. null if value = 10 (SELECT NULLIF(10,10) FROM DUAL;)
4. select supplier_id,
   CASE
     WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
     WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
   END
   from suppliers;
5. Find date of next month 1st TUESDAY (select next_day(trunc(add_months(sysdate,1),'MM'),'TUESDAY') from dual)
6. SELECT
  2   month, SUM(amount) AS month_amount,
  3   SUM(SUM(amount)) OVER
  4    (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  5    AS cumulative_amount
  6  FROM all_sales
  7  GROUP BY month
  8  ORDER BY month;

     MONTH MONTH_AMOUNT CUMULATIVE_AMOUNT
---------- ------------ -----------------
         1     58704.52          58704.52
         2      28289.3          86993.82
         3     20167.83         107161.65
         4      50082.9         157244.55
         5     17212.66         174457.21
         6     31128.92         205586.13
         7     78299.47          283885.6
         8     42869.64         326755.24
         9     35299.22         362054.46
        10     43028.38         405082.84
        11     26053.46          431136.3
        12     20067.28         451203.58

12 rows selected.
7.