Oracle

Question 0

SQL

Answer 0

Structured query language

Question 1

Relational db

Answer 1

Uses relations or two-dimensional tables to store information

Question 2

ANSI

Answer 2

American national standards institute

Question 3

Data retrieval operator

Answer 3

Select

Question 4

DML

Answer 4

Data manipulation language

Question 5

DML operators

Answer 5

Insert
Update
Delete
Merge

Question 6

DDL

Answer 6

Data definition language

Question 7

DDL operators

Answer 7

Create
Alter
Drop
Rename
Truncate

Question 8

Transactional control operators

Answer 8

Commit
Rollback
Savepoint

Question 9

DCL

Answer 9

Data control language

Question 10

DCL operators

Answer 10

Grant

Revoke

Question 11

Suppress duplicates keyword

Answer 11

Distinct

Question 12

Arithmetic expressions containing null evaluate to …

Answer 12

null

Question 13

Oracle SQL concatenation operator

Answer 13

||

Question 14

Literal value is .., … or …

Answer 14

character, number or date

Question 15

Display strucutre of table in SQL*Plus

Answer 15

Desc[ribe]

Question 16

‘Like’ condition wildcard for one character

Answer 16

_

Question 17

‘Like’ condition wildcard for zero or many character

Answer 17

%

Question 18

Rules of precedence

Answer 18

Arithmetic
Concatenation
Comparison
LIKE, IS NULL, IN
BETWEEN
NOT
AND
OR

Question 19

Order by default order

Answer 19

Ascending

Question 20

Reverse order by order with

Answer 20

desc

Question 21

Oracle functions can be

Answer 21

Single-row and multiple-row

Question 22

Single-row functions hierarchy

Answer 22

Character
Number
Date
Conversion
General (nvl, nullif, case, decode)

Question 23

Character functions can be

Answer 23

Case-manipulation and character-manipulation

Question 24

Case-manipulation functions

Answer 24

Lower Upper Initcap

Question 25

Character-manipulation

Answer 25

``` Concat Substr Length Instr Lpad rpad Trim Replace ```

Question 26

Number functions

Answer 26

Round Trunc Mod

Question 27

Default date format

Answer 27

DD-MON-RR

Question 28

Translate Explicit

Answer 28

Явный

Question 29

Coalesce

Answer 29

Returns the first non-null expression in the expression list

Question 30

Nullif

Answer 30

Returns null if expressions are equal, returns 1 expression if not

Question 31

SQL Conditional expressions

Answer 31

Case | Decode

Question 32

When join is omitted or invalid we recieve

Answer 32

Cartesian product

Question 33

Equijoin Simple join Inner join

Answer 33

Values on both tables must be equal

Question 34

Nonequijoin

Answer 34

Condition contains other than an equality operator

Question 35

When outer join is used

Answer 35

To see rows that do not meet the join condition

Question 36

Where does the + resides (outer join)?

Answer 36

Table with missing information

Question 37

Cross join is equal to

Answer 37

Cartesian product

Question 38

Natural join is based on

Answer 38

all columns in 2 tables that have the same name. | Columns should be of the same type

Question 39

Natural join with the using clause

Answer 39

can be used as equijoin

Question 40

Group founction operate on ... to give ...

Answer 40

sets of rows to give one result per group

Question 41

All group functions ... null values

Answer 41

ignore

Question 42

Clause to restrict groups

Answer 42

Having

Question 43

Group functions can be nested to a depth ...

Answer 43

of two

Question 44

Multiple-row comparison operators

Answer 44

IN Any All

Question 45

SQL placeholder for var name

Answer 45

&

Question 46

WITH CHECK OPTION

Answer 46

progibits from changing rows that are not in the subquery

Question 47

Merge statement provides the ability

Answer 47

to conditionally update or insert data | Performs update if the row exists and insert otherwise

Question 48

Merge syntax

Answer 48

``` merge into table_name as table_alias using (table|view|sub_query) as alias on (join condition) when matched then update set col1 = col1_val, col2 = col2_val when not matched then insert (column_list) values (column_values) ```

Question 49

Transaction ends when

Answer 49

Commit or rollback is issued DDL or DCL executes (auto commit) User exits System crashes

Question 50

If a snigle DML fails what is rolled back?

Answer 50

Only that statement. Oracle implements an implicit savepoint. Other changes are retained and should be terminated explicitly.

Question 51

Уровни изолированности транзакций

Answer 51

Read uncommited Read commited Repeatable read Serializable

Question 52

Как в oracle включить уровень изолированности serializable?

Answer 52

alter session set isolation_level = serializable

Question 53

Oracle lock modes:

Answer 53

Exclusive | Share

Question 54

Oracle table naming rules

Answer 54

``` Begin with a letter 1 to 30 characters long Contain A-Z, a-z, 0-9, _, $, # Unique Not reserved word ```

Question 55

Default keyword can be used

Answer 55

When creating tables | In insert statement

Question 56

Tables in Oracle are divided into 2 groups:

Answer 56

User | Data dictionary

Question 57

Oracle datatypes

Answer 57

``` Varchar2(n) Char[(n)] Date Long Lob, clob, blob Raw, long raw Bfile Rowid ```

Question 58

Rowid is

Answer 58

Hexadecimal string representing the unique address of a row in table

Question 59

Bfile is

Answer 59

Binary data stored in an external file

Question 60

Clob is

Answer 60

Character data up to 4 Gb

Question 61

How to mark column as unused in Oracle?

Answer 61

Set unused

Question 62

Truncate table statement ...

Answer 62

Removes all rows | Releases the storage space

Question 63

Can truncate be rollbacked?

Answer 63

No

Question 64

Table DDL statements

Answer 64

``` Create Alter Drop Rename Truncate Comment ```

Question 65

Constraints are

Answer 65

rules at table level

Question 66

FK constraint keywords

Answer 66

Foreign key References On delete cascade On delete set null

Question 67

Constraint types

Answer 67

``` Not null Unique Primary key Foreign key Check ```

Question 68

Force keyword when creating view means

Answer 68

view will be created regardles of whether or not the base table exists

Question 69

With check option when creating view

Answer 69

only rows accessible to the view can be inserted or updated

Question 70

With read only when creating view

Answer 70

no DML can be performed on the view

Question 71

Cannot add data through view if view includes:

Answer 71

``` Group functions Group by Distinct Rownum Columns defined by expressions Not null columns in tables not selected by view ```

Question 72

Inline view is

Answer 72

a subquery with an alias

Question 73

Top-n analysis SQL structure

Answer 73

Select column_list, rownum from (select column_list from t order by 1) where rownum <= N

Question 74

What is a sequence?

Answer 74

Sharable DB object for generating integers

Question 75

Sequence pseudocolumns

Answer 75

Nextval and currval

Question 76

When Nextval and Currval cant be used?

Answer 76

``` Select list of view Select with distinct Select with group by, having, order by Subquery in select, delete or update Default in create table or alter table ```

Question 77

What is and index?

Answer 77

Schema object that cna speed up the retrival of rows by using a pointer.

Question 78

When not to create index?

Answer 78

``` Small table Columns are not used in query condition Most queries retrieve > 4% data Table is updated frequently Indexed columns are referenced as part of expression ```

Question 79

Set operatiors

Answer 79

Union/Union all Intersect Minus

Question 80

What is the difference between union and union all?

Answer 80

Union eliminates duplicates

Question 81

Rollup operator

Answer 81

Used with group by to generate subtotals

Question 82

Cube operator

Answer 82

Used with group by to generate subtotals and produces all possible combination of groups

Question 83

Scalar subquery

Answer 83

returns exactly one column value from one row

Question 84

Correlated subquery

Answer 84

executes for each row

Question 85

Exists operator

Answer 85

tests for existence of rows in th results set of the subquery

Question 86

With clause

Answer 86

is used for reusing sql block