sql oracle case statement in where clause

(hiredate, ' mm ') = ' 02 '; 　　--23, for each employee, shows the number of days they joined the company 　　Selectename,sysdate-hiredate from EMP 　　--24, displays the names of all employees that contain "A" in the Name field 　　Selectename from EMP where ename like '%a% '; 　　Selectename from EMP where InStr (ename, 'A ', 1) >0; 　　--25, year, month and day show the service life of all employees 　　Selectmonths_between (sysdate,hiredate)/12as "year", Months_between (sysdate,hiredate) as "month", sy

Methods of identifying statements that occupy more resources (4 methods) 1. Feedback from the test group and end users on issues related to slow response. 2. The implementation details are provided using the V_$sqlarea view. (Number of times to perform, read, and read buffers) #8226; Data columns Executions: Number of executions Disk_reads: Read the number of disk Command_type: Command Type (3:select,2:insert;6:update;7delete;47:pl/sql program

SQL statement for querying by page in Oracle Database Query SQL statements by page in the oracle database tutorialOracleSelect *From (select rownum r, t1. * from table name t1 where rownum Where t2.r> = m For example, to retrieve 10 or 20 records from the table sys_option (p

In oracle, csv files are generated using SQL scripts. many friends want to implement this function. Therefore, this article is necessary. do not miss it if you are interested, hope this article will help you In oracle, csv files are generated using SQL scripts. many friends want to implement this function. Therefore,

1. copy only the SQL statements of the Table Structure Create Table tablename2 as select * From tablename1 where 1 2. SQL statement used to copy the table structure and data in the table Create Table tablename2 as select * From tablename1 3. SQL statement used to crea

----------------------------------------------------------------------------- Order Number Group varchar2 (10) SQL> insert into T1 (Order, group) values (1, test ); Insert into T1 (Order, group) values (1, 'test ') * Error at line 1: ORA-00928: Missing select keyword SQL> insert into T1 values (1, 'test '); 1 row created. SQL> insert into

Mysql oracle SQL MYSQL Java code drop TABLE if EXISTS tmp_t; create TABLE tmp_t select min (id) id, 'name' from comp_info group by 'name' having count ('name')> 1; delete. * from comp_info a, tmp_t B where B. 'name' =. 'name' and. id> B. id; truncate table tmp_t; drop TABLE if EXISTS tmp_t; Oracle Java code delete from gqh_sch_info where sch_name in (select sch_n

Check the SQL statement of the deadlock 1. Select Sid, decode (Block, 0, 'No', 'yes') blocker, decode (request, 0, 'No', 'yes') WaiterFrom v $ lockWhere request> 0 or block> 0Order by block DESC; 2. Select BS. username "blocking user", BS. username "DB user ",WS. username "Waiting user", BS. Sid "Sid", WS. Sid "wsid ",BS. Serial # "serial #", BS. SQL _ad

SYSDBA--with SYS user must add as SYSDBA clause sqlplus scott/[emailprotected]-- Non-admin user login desc v$database; --Query the table structure of the V$database database--Execute SQL script in Sqlplus, the following two ways can start file_name@file_name--determine whether the table exists, if present, delete declare num number; Begin select COUNT (1) into NUM from all_tables where table_name = '

OracleHow to display all current user tables Show all tables of a user (for example, Scott, uppercase is required) Select table_name from all_tables where owner = 'Scott '; Show all current user tables Select * From user_tables Display All Tables of the current database Select * From tab; Displays information about all columns in a table. Select * From user_tab_columns where table_name in ('order _ status2 '); Display the space name of the current data table You can use sys User Login Select tab

String sql= "SELECT * FROM (select E.*,rownum r from (SELECT * to emp ORDER BY sal Desc) e) where r between? and? ";String sql= "SELECT * FROM (select E.*,rownum r from (SELECT * to emp ORDER BY sal Desc) e) where r between 6 and 10";StringBuilder vs = new StringBuilder ();Vs.append ("SELECT * FROM (");Vs.append ("Select E.*, RowNum RNO from (SELECT * from emp ORDER BY sal Desc) e");Vs.append (")");Vs.appen

Functions of several views, from official documentsV $ sqltextV $ SQLTEXTDisplays the text of SQL statements belonging to shared SQL cursors in the SGA.V $ SESSIONV $ SESSIONDisplays session information for each current session.V $ wait_chainsV $ WAIT_CHAINSDisplays information about blocked sessions. A wait chain is comprised of sessions that are blocked by one another. each row represents a blocked and bl

Tags: on as SQL type Oracle table text class ROMQuery all columns and their properties for a table:Select From User_tab_columns t,user_col_comments Cwhere t.table_name = c.table_name and T.column_name = c.column_name and t.table_name = women;To find the primary key for a table:Find the foreign key of the table (including the name, the table name of the reference table, and the corresponding key name, whi

Copy codeThe Code is as follows:-- Oracle reads SQL statements from last Monday to weekend-- The day of the week starts on Sunday.Select to_char (to_date ('201312', 'yyyymmdd'), 'D') from dual;-- Result: Note 6: the sixth day of the week is Friday.Select to_char (sysdate + (2-to_char (sysdate, 'D')-7, 'yyyymmdd') from dual; --- last MondaySelect to_char (sysdate + (2-to_char (sysdate, 'D')-1, 'yyyymmdd') fr

SQL statements for Oracle management and query Tablespace and capacity: Select tablespace_name, sum (bytes)/1024/1024/1024 from dba_data_files group by tablespace_name; Free space of the tablespace: Select tablespace_name, sum (bytes) from dba_free_space group by tablespace_name; System tablespace: Select tablespace_name from dba_tablespaces; System data file: Select file_name from dba_data_files;

Oraclexe 11.0.2.0Output to FileSql>spool/* Full path */;Sql>spool off;Multi-Table Natural linksSelect Spj.sno from SPJ join P on P.pno=spj.pnoJoin S on Spj.sno=s.snoWhere city!= ' Tianjin ' or color!= ' red ';Good trouble, put this assignment up.Blog in the garden to pass files like only you can see??About logical algebra and the translation of the SQL language (Logic ÷ part of Baidu Library although the

, 'month'), trunc (sysdate, 'month') from table_name; /* use the null function (when expr1 is null, take expr2/When expr1 is null, take expr2, otherwise take expr3/When expr1 = expr2 returns NULL) */select nvl (expr1, expr2), nvl2 (expr1, expr2, expr3), nullif (expr1, expr2) from table_name; select column1, column2, column3, case column2 when '50' then column2 * 1.1 When '30' then column2 * 2.1 When '10' then column3/20 else column3end as tttfrom tabl

' ); Insert into EMP (Emp_id, manager_id ) Values ('002', '123' ); Insert into EMP (Emp_id, manager_id ) Values ('003 ', '123' ); Insert into EMP (Emp_id, manager_id ) Values ('20160301', '20160301' ); Insert into EMP (Emp_id, manager_id ) Values ('20160301', '20160301' ); Insert into EMP (Emp_id, manager_id ) Values ('20160301', '20160301' ); Commit; Someone gave the following answer:Select emp_id, manager_id FROM (SELECT CONNECT_BY_ROOT (emp_id) emp_id, manager_id, CONNECT_BY_ISLEAF v_isleaf F

You can use the following SQL statement to determine whether a custom object exists in Oracle:Select * from user_objects where object_name = 'yourobobject ';In this way, all information of the object can be obtained. Note that:1. The object name in the query condition must be in uppercase.2. The object name may be repeated. Therefore, you need to specify another OBJECT_TYPE condition to make a more accurate

The original statement is as follows:Select sum (sl0000) from xstfxps2 whereDhao00 in (Select dhao00 from xstfxps1 where trunc (ywrq00) = trunc (sysdate)And khdm00 = '000000 ');Time in use: 00: 02: 49.04　　 Execution Plan----------------------------------------------------------0 select statement Optimizer = CHOOSE1 0 SORT (AGGREGATE)2 1 NESTED LOOPS3 2 table access (FULL) OF 'xstfxps2'4 2 table access (by i