Notes on 1z0-007

The instr function is a string function that returns the position of a string that appears for the first time in another string.

Instr Function Format: instr ([start,] string1, string2 [, compare])

Nvl2 (A, B, C). If a is not null, B is returned. If a is null, C is returned.

 

Trunc is actually a truncate function, which literally means truncation and truncation. The function truncates numbers.
For example, tranc (1234.5678, 2) returns 1234.5600. Tranc () is not rounded in. Another example:
The result of tranc (1234.5678, 0) is 1234.0000; the result of tranc (1234.5678,-2) is 1200.0000.

Select round (number, [decimal_places]) from dual
Decimal_places)
Select round (123.456) from dual; returns limit 123
Select round (123.456, 0) from dual; returns limit 123
Select round (123.456, 1) from dual; returns limit 123.5
Select round (123.456, 2) from dual; returns limit 123.46
Select round (123.456, 3) from dual; returns limit 123.456
Select round (-123.456, 2) from dual; returns round-123.46

 

Decode (condition, value 1, translation value 1, value 2, translation value 2,... value n, translation value n, default value)

The nullif () function returns a null value when expression1 is equal to expression2. If not, returns the value of expression1.

MoD (number1, number2) describes the division of two values and returns the remaining number.

Oracle Database has a function lpad (string a, int length, string addstring). 2: Add addstring to the left of A. length is the length of the returned value.
Rpad and lpad (paste character) rpad paste character on the right of the column. lpad paste character on the left of the column

 

Like % Represents 0 or multiple characters
_ Represents a character
Escape converter % ,_
Exp: job_id like '% sa \ _ %' escape '\';
Exp: job_id like '% Sa @ _ %' escape '@';

 

Multiple tables can be written in the form of select * From Table1 left join Table2 on condition 1 left join table3 on condition 2 left join table4 on Condition 3 where Condition 4.

 

Insert into (Select... from... where... with check option) values...
You can regard the subquery as a view or table, and insert into table_name (or subquery) values ();
1. For update, with check option is available. Make sure that the data is queried by the view after update.
2. For Delete, all with check options are the same
4. With check option for insert, make sure that data is queried by the view after insert.
For views without where clauses, using with check option is redundant.

You must use with check option to define the view. This option ensures the integrity of the data being modified in the database. If conditions are violated during the insert or update operation,
An SQL error is returned.

 

The interval data type is used to store the time interval between two timestamps.
Oracle supports two inteval types: year to month and day to second.

Rollback to savepoint
Commit

 

Using
It is used to input values to bind variables, for example:
For I2 in 1 .. 1000 Loop
Execute immediate 'select * From tablex where id =: 1 'using I2;
End loop;

 

Select E. last_name, D. department_name, D. location_id
From employees e join orders ments d -- On E. department_id = D. department_id
Using (department_id );

This statement is executed 1000 times. The query conditions are different each time, but the statement is run only once by parse.

Note the differences between fmdd and fmddspth.
Select to_char (sysdate, 'fmddspth "of" month yyyy am ') todays from dual;
Select to_char (sysdate, 'ddspth "of" month yyyy am ') todays from dual;

 

Select to_char (to_date ('19-Mar-2001 ', 'dd-MON-YYYY'), 'fmddspth "of" month yyyy fmhh: MI: SS am') new_date
From dual;
If you change Mar to March, no error will be reported.
Select to_char (to_date ('19-March-2001 ', 'dd-MON-YYYY'), 'fmddspth "of" month yyyy fmhh: MI: SS am') new_date
From dual;

 

Natural join in Oracle)
Although natural join (natural join) is actually rarely used, it is actually very useful. If it can be used frequently, it is actually very convenient.

A natural join is to search for fields with the same data type and column name in the two tables, then automatically connect them, and return all results that meet the condition.

Reference ['refr quota ns]
N. Reference, source, reference
Properties ..
1. Attributes

 

Even if there are no employees in the orders and even if the orders are not yet assigned to a location.
Even if there are no employees in charge of the department and even if the Department has not been assigned to a single location.

Regarding [ri 'G encoding: di encoding]
Prep. For more information

Aggregate ['invalid grigeit]
N. Total, total, aggregation
A. Total, set, aggregated
V. Aggregation, set, total

Parentheses
Parentheses

Exclude [iks 'klu: d]
V. excluded, excluded, rejected
[Computer] exclusion

Aggregate ['invalid grigeit]
N. Total, total, aggregation
A. Total, set, aggregated
V. Aggregation, set, total

Specified by specify

Manipulate [M comment 'nipjuleit] vt. Operations; manipulate; handle skillfully; tamper

What is true about joining tables through an equijoin?

A. You can join a maximum of two tables through an equijoin.
B. You can join a maximum of two columns through an equijoin.
C. you specify an equijoin condition in the select or from clauses of a SELECT statement.
D. To join two tables through an equijoin, the columns in the join condition must be primary key and foreign
Key columns.
E. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum
Of N-1 join conditions.

Which of the following statements about equijoin is true?
A You can only perform equivalent join operations on two tables at most.
B equijoin can only connect two fields
C. You can perform equijoin in select or from statements.
D. Only the primary key or foreign key can be used for equivalent join conditions
E. Connect n tables with at least N-1 join conditions (all tables have only one primary key column)

undoubtedly, only E is correct