Some practical SQL statements

Some practical SQL statements. For more information, see.

Some practical SQL statements. For more information, see.

1. query the names of products with prices higher than the average price:
SELECT item_name FROM ebsp. product_market_price WHERE item_price> (select avg (item_price) FROM ebsp. product_market_price
2. oracle9i or a later version allows you to insert data from a table to multiple tables at the same time. Example:
The Code is as follows:
INSERT ALL
WHEN deptno = 10 then into dept10 -- insert Table dept10 with Department Number 10
WHEN deptno = 20 then into dept20
WHEN deptno = 30 then into dept30
WHEN job = 'cler' then into clerk -- the job is in the clerk Insert table.
Else into other
SELECT * FROM emp;

You can change the preceding SQL statement to the one after INSERT FIRST. When the First operator is used to INSERT multiple tables, if the data already meets the preceding conditions and has been inserted into a table, this row of data will not be used again in subsequent inserts.
3. Extract the length specified by the string.
The Code is as follows:
Select substr (item_name, 0, 2) from ebsp. product_market_price
Select substr ('ho fresh flowers ',) from dual; -- print 'ho fresh'

Obtain the hiredate employment date in the emp system table, which has repeated records, that is, the records of hiring multiple employees in a day.
The Code is as follows:
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

If hiredate is stored in the database with the date type with the time, you can use to_char (CREATE_DATE, 'yyyy-MM-DD ') to replace the above
4. Modify the cache size of the oracle database and log on to the system:
The Code is as follows:
Alter system set db_cache_size = 700 m scope = spfile;
Alter system set shared_pool_size = 200 m scope = spfile;
Alter system set pga_aggregate_target = 100 m scope = spfile;