1. Query above the average price of the product name:
Select Item_name from Ebsp.product_market_price WHERE item_price > (select AVG. item_price) from ebsp.product_market_ Price
2.oracle9i The above version, you can implement the data of a table into more than one table. Cases:
Copy Code code as follows:
INSERT All
When deptno=10 THEN into dept10--the Insert Table dept10 with department Number 10
When Deptno=20 THEN into dept20
When deptno=30 THEN into Dept30
When job= ' clerk ' THEN into clerk--post for Clerk insert Table Clerk
ELSE into the other
SELECT * from EMP;
The preceding SQL statement can be changed to insert first, and when multiple table inserts are performed using the first operator, if the data already satisfies the previous condition and has been inserted into a table, the row data will not be reused in subsequent inserts.
3. Intercept the length of the string formulation.
Copy Code code as follows:
Select substr (item_name,0,2) from Ebsp.product_market_price
Select substr (' Ho scarlet Flowers ', 0,3) from dual; --print ' Ho Fresh '
Get the HireDate hire date in the EMP system table with duplicate records, which is the record of hiring more than one employee a day.
Copy Code code as follows:
Sql1:select * from Scott.emp where hiredate to (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 have Count (*) >1) t1
where t2.hiredate = T1.hiredate
If the hiredate is stored in the database, the date type has time and minutes, and can be replaced by To_char (create_date, ' yyyy-mm-dd ').
4. Modify the Oracle Database cache size to log on by system:
Copy Code code as follows:
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;
