Collection of common Oracle statements

Simple case example

For each customer in the sampleOe. MERsTable, the following statement lists the credit limit as "low" if it equals $100, "high" if it equals $5000, and "medium" if it equals anything else.

Select distinct, Case credit_limit when 100 then 'low' when 5000 then 'high 'else' medium 'end from MERs MERS; cust_last_name casecr accept ------... Bogart mediumnolte mediumloren mediumguenmediey Medium





 Simple functionexample 





Create function f (cur sys_refcursor, mgr_hiredate date) return number isEmp_hiredate date;   Before number: = 0;   After number: = 0;  Begin   Loop   Fetch cur into emp_hiredate;   Exit when cur % notfound;   If emp_hiredate> mgr_hiredate then   After: = after + 1;   Else   Before: = before + 1;   End if;   End loop;   Close cur;   If before> after then   Return 1;   Else   Return 0;  End if;  End ; Simple date example
 
The following example converts the datetime value of one time zone to another time zone:
 
Select from_tz (cast (to_date ('2017-12-01 11:00:00 ', 'yyyy-MM-DD hh: MI: ss') as timestamp), 'America/new_york ') at time zone 'America/los_angeles '"West Coast Time" from dual; West Coast Time -------------------------------------------------- 01-dec-99 08.00.00.000000 am America/los_angeles
 SimpleGroup ByClause, you can use either the upper or lower form of expression list: 
 
Select department_id, min (salary), max (salary) from employees group by department_id, salary; select department_id, min (salary), max (salary) from employees group by (department_id, salary );
 
 
 
 in  rollup ,  cube , and  grouping   sets  clses of  group   by  clses, you can combine individual expressions with sets of expressions in the same expression list. the following example shows several valid grouping sets expression lists in one SQL statement: 
 select prod_category, prod_subcategory, country_id, cust_city, count (*) from products, sales, customers where sales. prod_id = products. prod_id and sales. cust_id = MERs. cust_id and sales. time_id = '01-oct-00' and MERs. cust_year_of_birth between 1960 and 1970 group by grouping sets ( prod_category, prod_subcategory, country_id, cust_city  ), ( prod_category, prod_subcategory, country_id ), ( prod_category, prod_subcategory  ),  country_id  );