This parameter can be used in this situations as minimum value specified in a sequence declaration: CREATE SEQUENCE … MINVALUE .. value … …. can be used in flashback version query: This keywords instruct Oracle to retrieve all available data versions The age of the data available is determined by the undo_retention parameter SELECT … fields … FROM … table … VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE WHERE … conditions …
This parameter can be used in this situations as maximum value specified in a sequence declaration: CREATE SEQUENCE … MAXVALUE value ….. can be used in flashback version query: This keywords instruct Oracle to retrieve all available data versions. The age of the data available is determined by the undo_retention parameter SELECT … fields … FROM … table … VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE WHERE … conditions …
The ALL comparison condition is used to compare a value to a list or subquery. It must be preceded by =, !=, >, <, <=, >= and followed by a list or subquery. When the ALL condition is followed by a list, the optimizer expands the initial condition to all elements of the list and strings them together with AND operators, as shown below. SELECT … fields … FROM … table … WHERE sal > ALL (2000, 3000, 4000);
The ANY comparison condition is used to compare a value to a list or subquery. It must be preceded by =, !=, >, <, <=, >= and followed by a list or subquery. When the ANY condition is followed by a list, the optimizer expands the initial condition to all elements of the list and strings them together with OR operators, as shown below. SELECT … fields … FROM … table … WHERE sal > ANY (2000, 3000, 4000); note: The SOME and ANY comparison conditions do exactly the same thing and are completely interchangeable.
A synonym is an alias to a database object. It can help to reduce sql complexity when written. A public synonym is visible to any users. A private synonym is contained and visible only to the creating user. Some example: DROP [PUBLIC] SYNONYM syn_table; CREATE PUBLIC SYNONYM syn_table FOR anotherschema.table; private synonym CREATE SYNONYM syn_table FOR schema.table;
In and not in are conditions. Useful for tests one or more values for membership(or not) in a list of values or subquery Some example: select * from table where col1 in (23, 45, 342) — where values of col1 match with 23, 45, 342 select * from table where col1 not in (23, 45, 342) — where values of col1 not match with 23, 45, 342 select * from table where (col1, col2) in (select val_a, val_b from testtab) — where value of col1 and col2 match with val_a and val_b of qubquery
Oracle subquery is a sql query within another sql query. Some example: select tab.name, tab.surname from (select name, surname from customer where surname like ‘S%’) tab select id from myusers where (name, surname) in (select name, surname from customer where surname like ‘S%’) tab select (select ‘1’ from dual) from table.