Some useful sql statements

1. Search for above average prices:
SELECT item_name FROM ebsp.product_market_price WHERE item_price > (SELECT AVG(item_price) FROM ebsp.product_market_price
2. Versions above oracle9i, which can insert data from one table into multiple tables at the same time. Ex. :

 
INSERT ALL 
WHEN deptno=10 THEN INTO dept10 -- Department Number: 10 Insert table dept10 In the  
WHEN deptno=20 THEN INTO dept20 
WHEN deptno=30 THEN INTO dept30 
WHEN job='CLERK' THEN INTO clerk -- Jobs for CLERK Insert table clerk  In the  
ELSE INTO other 
SELECT * FROM emp; 

The previous sql statement can be changed to INSERT FIRST, and when performing multiple table inserts using the First operator, if the data has already met the previous conditions and has been inserted into a table, that data will not be used again in subsequent inserts.
3. Truncate the specified length of the string.

 
select substr(item_name,0,2) from ebsp.product_market_price 
select substr( ' ho Bright red flowers  ',0,3) from dual; --print  ' ho fresh ' 

The hiredate employment date is obtained from the emp system table, and there is a duplicate record, that is, the record of hiring more than one employee in one day.

 
SQL1: select * from scott.emp where hiredate in (select hiredate mycount from scott.emp group by hiredate having count(*) >1) 
SQL2:select t2.* from scott.emp t2 , 
(select t.hiredate,count(*) mycount from scott.emp t group by t.hiredate having count(*) >1) t1 
where t2.hiredate = t1.hiredate 

When hiredate is stored in the database, the date type with minutes and seconds can be replaced by to_char(CREATE_DATE, 'ES28en-ES29en-ES30en ')
4. Modify the cache size of oracle database to log in as system:

 
alter system set db_cache_size = 700m scope = spfile; 
alter system set shared_pool_size = 200m scope=spfile; 
alter system set pga_aggregate_target = 100m scope=spfile;