Oracle Quiz-2

SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\\';

SELECT last_name FROM employees WHERE last_name LIKE '%A_B%' ESCAPE;

SELECT last_name FROM employees WHERE last_name LIKE 'A_B%' ESCAPE '%';

SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\';

Because of a syntax problem, no row will be returned

It calculates, for each employee in the employees table, the average salary of the employees reporting to his/her respective manager

It calculates, for each employee in the employees table, the average salary of the employees reporting to his/her respective manager who were hired just before the employee

It calculates, for each employee in the employees table, the average salary of the employees reporting to the same manager who were hired in the range just before through just after the employee

It calculates, for each employee in the employees table, the average salary of the employees reporting to his/her respective manager who were hired just after the employee

A. SELECT TRUNC(TO_DATE(12-Feb-99,DD-MON-YY, 'YEAR')) "Date " FROM DUAL;

B. TRUNC = TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR', "Date " FROM DUAL;

C. SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;

D. date = TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL

A. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n';

B. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ,-1,1) FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n';

C. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, 1, 1) = 'n';

D. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, -1, 1) = 'n';

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.

A. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;

B. ALTER TABLE customers MODIFY CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;

C. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL;

D. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn IS NOT NULL;

E. ALTER TABLE customers MODIFY name CONSTRAINT cust_name_nn NOT NULL;

F. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name NOT NULL;

ALTER TABLE students ADD PRIMARY KEY student_id;

B. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);

C. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;

D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

E. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

A. SELECT city_address, COUNT(*) FROM customers WHERE city_address IN ('Los Angeles', 'San Francisco');

B. SELECT city_address, COUNT(*) FROM customers WHERE city_address IN ('Los Angeles', 'San Francisco') GROUP BY city_address;

C. SELECT city_address, COUNT(customer_id) FROM customers WHERE city_address IN ('Los Angeles', 'San Francisco') GROUP BY city_address, customer_id;

D. SELECT city_address, COUNT(customer_id) FROM customers GROUP BY city_address IN ('Los Angeles', 'San Francisco');

Creates a view with constraints

B. creates a view even if the underlying parent table has constraints

C. creates a view in another schema even if you don't have privileges

D. creates a view in another schema even if you don't have privileges

UNIQUE

NOT NULL

CHECK

PRIMARY KEY

FOREIGN KEY

CREATE TABLE EMP9$# AS (emp_id number(2));

CREATE TABLE EMP*123 AS (emp_id number(2));

CREATE TABLE PACKAGE AS (pack_id number(2));

CREATE TABLE 1EMP_TEST AS (emp_id number(2));

Difference, projection, join

Selection, projection, join

Selection, intersection, join

Intersection, projection, join

Difference, projection, product

Index-by table

Nested table

VARRAY

Nested table and VARRAY

A named server exception

A programmer-defined exception

An unnamed server exception

The RAISE statement is never required for an exception

Type team_rec is record like teams;

Team_rec teams%tabletype;

Team_rec teams%type;

Team_rec teams%rowtype;

E. You must list all the column in the TYPE statement that defines the record.

Header

Declaration

Executable

Exception

X is greater

Y is greater

Both X is greater and Y is greater

Neither X is greater nor Y is greater

NULL

NOT NULL

Function NVL2 is not defined

None of the above

%rowtype, %toomanyrows, %found

%found, %notfound, %rowcount

%rowtype, %rowcount, %notfound

None of the above

0 and .5

5 and 4.5

0 and 2

This will raise an exception, since MOD can only operate on integer values

Select Dname From Department, Employee Where Employee.Dnumber = Department.Dnumber AND Budget < Salary;

Select Dname From Department Where Budget < (Select sum(Salary) From Employee);

Select Dname From Department;

Select Dname From Department Where Budget < (Select sum(Salary) From Employee Where Employee.Dnumber = Department.Dnumber);

Locking the rows before update

Locking the row before update

No lock just update

Lock table

Statement 1 is true and Statement 2 is false

Statement 2 is true and Statement 1 is false

Both Statements are false

Both Statements are true