Introductory Level Test Oracle 9i PL/SQL

Which of the following queries can you use to search for employees with the pattern 'A_B' in their names?
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 '\';
Refer to the SQL codes below: SELECT manager_id, last_name, hire_date, salary, AVG (salary) OVER (PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS C_mavg FROM employees; What has been achieved?
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
Which of the following is not true if you use the alter tables pace statement and specify the TEMPORARY clause (Choose all that apply)?
The offline files may require media recovery before you bring the table space online
Oracle performs a checkpoint for all online data files in the table space
Oracle does not ensure that all files are written
Oracle no longer perform any checkpoint for the online data files in the tables pace
Which of the following has been achieved by the following SQL codes? SELECT employee_id FROM employees WHERE commission_pct = .5 OR salary > 23000;
it returns employees who have a 50% of a salary greater than $23,000:
it returns employees who have a0.5%Commission rate & salary > $23,000:
runtime error
it returns employees who have a 50% of a salary less than $23,000:
View the image below to examine the structures of the EMPLOYEES and TAX tables. You need to find the percentage tax applicable for each employee. Which SQL statement would you use?
SELECT employee_id, salary, tax_percent FROM employees e, tax t WHERE e.salary BETWEEN t.min_salary AND t.max_salary;
SELECT employee_id, salary, tax_percent FROM employees e, tax t WHERE e.salary > t.min_salary AND < t.max_salary;
SELECT employee_id, salary, tax_percent FROM employees e, tax t WHERE MIN(e.salary) = t.min_salary AND MAX(e.salary) = t.max_salary;
You cannot find the information because there is no common column between the two tables
View the image below to examine the structures of the EMPLOYEES and TAX tables. You need to find the percentage tax applicable for each employee. Which SQL statement would you use?
SELECT employee_id, salary, tax_percent FROM employees e, tax t WHERE e.salary BETWEEN t.min_salary AND t.max_salary;
SELECT employee_id, salary, tax_percent FROM employees e, tax t WHERE e.salary > t.min_salary AND < t.max_salary;
SELECT employee_id, salary, tax_percent FROM employees e, tax t WHERE MIN(e.salary) = t.min_salary AND MAX(e.salary) = t.max_salary;
You cannot find the information because there is no common column between the two tables
Which is an iSQL*Plus command?
INSERT
UPDATE
SELECT
DESCRIBE
/
The query returns 3 rows.
The query returns from employees where emp_id=3
The query returns no rows.
The query fails because the outer query is retrieving more than one column.
The PRODUCTS table has these columns: PRODUCT_ID NUMBER (4) PRODUCT_NAME VARCHAR2 (45) PRICE NUMBER (8, 2) Evaluate this SQL statement: SELECT * FROM PRODUCTS ORDER BY price, product_name; What is true about the SQL statement?
The results are not sorted.
The results are sorted numerically.
The results are sorted alphabetically.
The results are sorted numerically and then alphabetically.

INSERT INTO employees VALUES (emp_id_seq. NEXTVAL, '&ename', '&job_Id', 2000, NULL, &did);
INSERT INTO employees VALUES (emp_id_seq. NEXTVAL, '&ename', '&job_Id', 2000, NULL, &did);
INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50)) VALUES (emp_id_seq. NEXTVAL, '&ename', '&job_id', 2000, NULL, &did);
INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50) WITH CHECK OPTION)VALUES (emp_id_seq. NEXTVAL, '&ename', '&job_id', 2000, NULL, &did);
/
The SQL statement displays the desired results.
The column in the WHERE clause should be changed to display the desired results.
Display the desired results.
The WHERE clause should be changed to use an outer join to display the desired results.
Evaluate this SQL statement: SELECT e.EMPLOYEE_ID, e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_NAME FROM EMP e, DEPARTMENT d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID; In the statement, which capabilities of a SELECT statement are performed?
Selection, projection, join
Difference, projection, join
Selection, intersection, join
Intersection, projection, join
From SQL*Plus, you issue this SELECT statement: SELECT * FROM orders; You use this statement to retrieve data from a database table for _______________.
updating
viewing
deleting
truncating
Which statement is in correctly describe functions that are available in SQL
DECODE translates an expression after comparing it to each search value
NVL2 compares two expressions and returns null if they are equal, or the first expression if they are not equal.
NVL compares two expressions and returns null if they are equal, or the first expression if they are not equal.
NULLIF compares two expressions and returns null if they are equal, or the first expression if they are not equal.
Description:

This test is for anyone who is interested in learning more about Oracle9i SQL and in taking the Oracle 9i OCA exam
Disclaimer: Content, such as images used in the questions (if any), have been picked up from various places for the sole purpose of Instruction.

Discussion

Sen

this is funny. some questions have incorrect answers.

1233 days 6 hours 52 minutes ago

nagajyothi

the answer for 14th question is 4th option. nvl2 returns expression 1 if it is null, other wise it returns expression2.

3221 days 8 hours 18 minutes ago

satish

The test is not proper. wrong answers are given as correct ones

3230 days 22 hours 38 minutes ago

naveen bandla

hi, question no.8 , answer is wrong please verify that

3321 days 9 hours 12 minutes ago

kshitij

i think the third option should be true in the above question,as from the inner or sub query the comm of employee_id =3 is selected then the outer query will find those employees whose comm is equal to comm returned from the inner query.............
plz be sure n check the ans before giving it.......

3426 days 14 hours 25 minutes ago

arpit jain

So many Ans are incorrect , Pls correct them .

3503 days 54 minutes ago

Krunal

in this question, never "Display the desire results" which is right ans but the query has a problem because instead of department_id = null, the query should be "departid_id is null"

3527 days 3 hours 56 minutes ago

j p suman

if u provide more tessts on each topic then its nice

3550 days 1 hours 13 minutes ago

Tara

It will not display the desired result. For displaying it, where clause should be written as WHERE DEPARTMENT_ID IS NULL

3554 days 3 hours 54 minutes ago

manoj

in this question actually second option is right since it will return the rows of emp_id '3' only.since the inner querry will return the comm values for emp_id '3' den the outer querry will return matching rows for inner querry comm value. the third answer would be right only when the table doesnot contain rows of emp_id '3'.

3560 days 1 hours 10 minutes ago

Your Facebook Friends on WizIQ

More Tests By Author

ASP Server Controls
10 Questions | 667 Attempts

ASP.NET, Namespaces ASP.NET
10 Questions | 2028 Attempts

An ASP.NET Application
10 Questions | 437 Attempts

Explore Similar Courses