Part - 6 Oracle Interview

Question: 51. (C)

Examine the structures of the PATIENT, PHYSICIAN, and ADMISSION tables.
PATIENT Table



Which SQL statement will produce a list of all patients who have more than one physician?

A. SELECT p.patient_id
FROM patient p
WHERE p.patient_id IN (SELECT patient_id
FROM admission
GROUP BY patient_id
HAVING COUNT(*) > 1);

B. SELECT DISTINCT a.patient_id
FROM admission a, admission a2
WHERE a.patient_id = a2.patient_id
AND a.physician_id <> a2.physician_id;

C. SELECT patient_id
FROM admission
WHERE COUNT(physician_id) > 1;

D. SELECT patient_id
FROM patient FULL OUTER JOIN physician;

Answer: B

Explanation:

Self join can be used to find the above desired result to list all patients who have more than one physician as
SELECT DISTINCT a.patient_id
FROM admission a, admission a2
WHERE a.patient_id = a2.patient_id
AND a.physician_id <> a2.physician_id;

Question: 52. (C)

Which clause should you use to exclude group results?

A. WHERE

B. HAVING

C. RESTRICT

D. GROUP BY

E. ORDER BY

Answer: B

Explanation:

HAVING clause is used to weed out unwanted data once the data is grouped using the GROUP BY statement.

Incorrect Answers

A: WHERE clause cannot be used for this purpose.
C: There is no RESTRICT command in Oracle.
D: GROUP BY cannot be used itself to exclude group results.
E: ORDER BY clause may be used only to sort final results, not to exclude group results.

Question: 53. (C)

In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed in the SELECT statement?

A. Immediately after the SELECT clause

B. Before the WHERE clause

C. Before the FROM clause

D. After the ORDER BY clause

E. After the WHERE clause

Answer: E

Explanation:

The GROUP BY clause can be place only after the WHERE clause, or after FROM clause if there is no the WHERE clause in the statement.

Incorrect Answers

A: It is not possible to place the GROUP BY clause immediately after the SELECT clause.
B: It is not possible to place the GROUP BY clause before the WHERE clause, it can be done only after it.
C: It is not possible to place the GROUP BY clause before the FROM clause.
D: It is not possible to place the GROUP BY clause after the ORDER BY clause.

Question: 54. (C)

Which two are character manipulation functions? (Choose two.)

A. TRIM

B. REPLACE

C. TRUNC

D. TO_DATE

E. MOD

F. CASE

Answer: A & B

Explanation:

TRIM() and REPLACE() are character manipulation functions.

Incorrect Answers

C: TRUNC(x,y) is arithmetic function, it truncates x to the decimal precision of y. If y is negative, it truncates to y number of places to the left of the decimal point. This can also be used on DATE columns.
D: TO_DATE(x,[y]) function converts the non-date value x to a date using the format specified by y.
E: MOD(x,y) is arithmetic function, the modulus of x, defined in long division as the integer remainder when x divided by y until no further whole number can be produced.
F: There is no character manipulation function CASE in Oracle.

Question: 55. (C)

The EMPLOYEES table contains these columns:



You need to write a query that will produce these results:
1. Display the salary multiplied by the commission_pct.
2. Exclude employees with a zero commission_pct.
3. Display a zero for employees with a null commission value.
Evaluate the SQL statement:
SELECT LAST_NAME, SALARY*COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
What does the statement provide?

A. All of the desired results

B. Two of the desired results

C. One of the desired results

D. An error statement

Answer: C

Explanation:

This statement will provide only one of the desired results: display the salary multiplied by the commission_pct. It will not exclude employees with a zero commission_pct and display a zero for employees with a null commission value.

Incorrect Answers

A: This statement will provide only one of the desired results, not all.
B: This statement will provide only one of the desired results, not two.
D: This statement will not generate an error, it is correct .

Question: 56. (C)

Examine the structures of the EMPLOYEE and CURR_ORDER tables as shown below:


You queried the database with this SQL statement:
SELECT a.last_name, a.first_name, a.job_id, NVL(a.commission, 0), b.avgcomm
FROM employee a, (SELECT job_id, AVG(commission) AVGCOMM
FROM employee
WHERE commission IS NOT NULL
GROUP BY job_id) b
WHERE a.job_id = b.job_id
AND a.commission < employee_id =" o.employee_id" employee_id =" o.employee_id" employee_id =" o.employee_id" employee_id =" curr_order.employee_id" supplier_id =" a.supplier_id"> a.avg_cost
GROUP BY product_name, cost, p.supplier_id;

C. SELECT product_name, cost, supplier_id, AVG(list_price)
FROM product WHERE supplier_id IN (SELECT supplier_id, AVG(cost) avg_cost
FROM product GROUP BY supplier_id) GROUP BY product_name, cost, supplier_id;

D. SELECT product_name, cost, p.supplier_id, AVG(list_price)
FROM product p, (SELECT supplier_id, AVG(cost) avg_cost
FROM product
GROUP BY supplier_id) a
WHERE p.supplier_id = a.supplier_id
AND p.cost > a.avg_cost
GROUP BY product_name, cost, p.supplier_id;

Answer: D

Explanation:

For the above desired result the subquery will first find the average cost group by supplier_id and then making a join this will give the cost of all products that cost more than the average cost as SELECT product_name, cost, p.supplier_id, AVG(list_price) FROM product p, (SELECT supplier_id, AVG(cost) avg_cost FROM product GROUP BY supplier_id) a WHERE p.supplier_id = a.supplier_id AND p.cost > a.avg_cost GROUP BY product_name, cost, p.supplier_id;

Question: 59. (C)

Which SELECT statement should you use to extract the year from the system date and display it in the format "1998"?

A. SELECT TO_CHAR(SYSDATE,'yyyy')
FROM dual;

B. SELECT TO_DATE(SYSDATE,'yyyy')
FROM dual;.

C. SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY')
FROM dual;

D. SELECT DECODE(SUBSTR(SYSDATE, 8), 'year')
FROM dual;

E. SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy')
FROM dual;

Answer: A

Explanation:

Function TO_CHAR(x, y) converts the value x to a character or converts a date to a character string using formatting conventions.

Incorrect Answers

B: Function TO_DATE(x,[y]) converts the non-date value x to a date using the format specified by x.
C: The DECODE function is used as substitution of IF-THEN-ELSE PL/SQL construction in SQL queries. The SELECT statement provides incorrect syntax of it because it cannot have only two parameters.
D: The DECODE function is used as substitution of IF-THEN-ELSE PL/SQL construction in SQL queries. The SELECT statement provides incorrect syntax of it because it cannot have only two parameters.
E: This statement provide incorrect syntax of TO_CHAR() function: it requires only one parameter, not two.

Question: 60. (C)

The CUSTOMERS table has these columns:


The CUSTOMER_ID column is the primary key for the table.
Which two statements find the number of customers? (Choose two.)

A. SELECT TOTAL(*)
FROM customers;

B. SELECT COUNT(*)
FROM customers;

C. SELECT TOTAL(customer_id)
FROM customers;

D. SELECT COUNT(customer_id)
FROM customers;

E. SELECT COUNT(customers)
FROM customers;

F. SELECT TOTAL(customer_name)
FROM customers;

Answer: B & D

Explanation:

These statements provide correct syntax and semantics to show the number of customers. Function COUNT() can be used with substitution symbol of all columns "*" or just with one column name. Last query will be processed a little bit faster.

Incorrect Answers

A: There is no TOTAL() function in Oracle.
C: There is no TOTAL() function in Oracle.
E: You cannot table name as a parameter of COUNT() function.
F: There is no TOTAL() function in Oracle.

Read more »

Posted in: Oracle

Part - 4 Oracle Interview

Question: 31. (B)

You query the database with this SQL statement:
SELECT bonus
FROM salary
WHERE bonus BETWEEN 1 AND 250
OR (bonus IN(190, 500, 600)
AND bonus BETWEEN 250 AND 500);
Which value could the statement return?

A. 100

B. 260

C. 400

D. 600

Answer: A

Explanation:

For the above statement the value of bonus return will be 100. After resolving the above written operators the result return would be 100.

Question: 32. (B)

Examine the structure of the STUDENTS table:



You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.
Which SQL statement accomplishes this task?

A. SELECT student_ id, marks, ROWNUM "Rank"
FROM students
WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks DESC; B. SELECT student_id, marks, ROWID "Rank" FROM students WHERE ROWID <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks; C. SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks DESC); D. SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students ORDER BY marks DESC) WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL'; Answer: D Explanation: This statement using inline sub-query will provide correct results to show 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999. Incorrect Answers A: This view will just show first 10 students from the STUDENTS table with limitation on the course INT SQL and who completed the course in the year 1999. B: It's wrong to use here ROWID to achieve desired results. C: This statement will provide wrong result due to wrong conditions in the inline sub-query. Question: 33. (B) Examine the structure of the LINE_ITEM table.

You must display the order number, line item number, product identification number, and quantity of each item where the quantity ranges from 10 through 100. The order numbers must be in the range of 1500 through 1575. The results must be sorted by order number from lowest to highest and then further sorted by quantity from highest to lowest.
Which statement should you use to display the desired result?

A. SELECT order_id, line_item_id, product_id, quantity
FROM line_item
WHERE quantity BETWEEN 9 AND 101
AND order_id BETWEEN 1500 AND 1575
ORDER BY order_id DESC, quantity DESC;

B. SELECT order_id, line_item_id, product_id, quantity
FROM line_item
WHERE (quantity > 10 AND quantity <> 9 OR quantity < item_id =" 783920;" item_id =" 783920;" item_id =" 783920;" item_id =" 783920;" item_id =" 783920;" department_id =" NULL;"> credit_limit
AND account_id BETWEEN 1500 AND 2000;
Which statement about this SELECT statement is true?

A. The statement calculates the finance charge incorrectly.

B. The statement calculates the current balance incorrectly.

C. The statement returns only accounts that have NO previous balance.

D. The statement returns only accounts that have new purchases, previous balance, and
payments values.

Answer: D

Incorrect answers

A. The statement calculates the finance charge incorrectly.
B. The statement calculates the current balance incorrectly.
C. The statement returns only accounts that have NO previous balance.

Question: 40.(C)

Examine the description of the EMPLOYEES table:



Which statement shows the maximum salary paid in each job category of each department?

A. SELECT dept_id, job_cat, MAX(salary)
FROM employees
WHERE salary > MAX(salary);

B. SELECT dept_id, job_cat, MAX(salary)
FROM employees
GROUP BY dept_id, job_cat;

C. SELECT dept_id, job_cat, MAX(salary)
FROM employees;

D. SELECT dept_id, job_cat, MAX(salary)
FROM employees
GROUP BY dept_id;

E. SELECT dept_id, job_cat, MAX(salary)
FROM employees
GROUP BY dept_id, job_cat, salary;

Answer: B

Explanation:

This answer provides correct syntax and semantics to show the maximum salary paid in each job category of each department.

Incorrect Answers

A: This query will not return any row because condition SALARY > MAX(SALARY) is FALSE.
C: This query will return error because you cannot show maximum salary with DEPT_ID and JOB_CAT without grouping by these columns.
D: The GROUP BY clause is missing JOB_ID column.
E: You don't need to group results of query by SALARY in the GROUP BY column.

Read more »

Posted in: Oracle

Part - 3 Oracle Interview

Question: 21.(B)

Examine the data of the EMPLOYEES table.
EMPLOYEES (EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to
the EMPLOYEE_ID)



Which statement lists the ID, name, and salary of the employee, and the ID and name of the employee's manager, for all the employees who have a manager and earn more than 4000?

A. SELECT employee_id "Emp_id", emp_name "Employee",
salary,
employee_id "Mgr_id", emp_name "Manager"
FROM employees
WHERE salary > 4000;

B. SELECT e.employee_id "Emp_id", e.emp_name "Employee",
e.salary,
m.employee_id "Mgr_id", m.emp_name "Manager"
FROM employees e, employees m
WHERE e.mgr_id = m.mgr_id
AND e.salary > 4000;

C. SELECT e.employee_id "Emp_id", e.emp_name "Employee",
e.salary,
m.employee_id "Mgr_id", m.emp_name "Manager"
FROM employees e, employees m
WHERE e.mgr_id = m.employee_id
AND e.salary > 4000;

D. SELECT e.employee_id "Emp_id", e.emp_name "Employee",
e.salary,
m.mgr_id "Mgr_id", m.emp_name "manager"
FROM employees e, employees m
WHERE e.mgr_id = m.employee_id
AND e.salary > 4000;

E. SELECT e.employee_id "Emp_id", e.emp_name "Employee",
e.salary,
m.mgr_id "Mgr_id", m.emp_name "Manager"
FROM employees e, employees m
WHERE e.employee_id = m.employee_id
AND e.salary > 4000;.

Answer: C

Explanation:

This statement lists the ID, name, and salary of the employee, and the ID and name
of the employee's manager, for all the employees who have a manager and earn more than 4000

Incorrect Answers

A: This statement does not check does employee have a manager or not, so it will not provide correct result.
B: Usage of ??e.mgr_id = m.mgr_id?? condition is wrong to achieve required result.
D: This statement uses ??m.mgr_id?? to show manager??s manager, not employ??s manager.
E: Usage of ??WHERE e.employee_id = m.employee_id?? condition is wrong to achieve required result.

Question: 22. (B)

You need to display the last names of those employees who have the letter "A"as the second character in their names.
Which SQL statement displays the required results?


A. SELECT last_name
FROM EMP
WHERE last_name LIKE '_A%';

B. SELECT last_name
FROM EMP
WHERE last name ='*A%'

C. SELECT last_name
FROM EMP
WHERE last name ='_A%';

D. SELECT last_name
FROM EMP
WHERE last name LIKE '*A%'

Answer: A

Explanation:

Statement in this answer will show correct results because usage of operator LIKE and format
mask '_A%' extract the last names of those employees who have the letter "A" as the second
character in their names. Symbol '_' in format mask substitute exactly one symbol and cannot be NULL.

Incorrect Answers

B: This statement will return only names starting from symbol '*'. It cannot be used as substitution symbol.
C: Usage of equity operator here is not appropriate in this case: query will look exact for first
symbol '_', it will not be considered as substitution symbol.
D: This statement will return only names starting from symbol '*'. It cannot be used as substitution symbol.

Question: 23. (B)

In which scenario would TOP N analysis be the best solution?

A. You want to identify the most senior employee in the company.

B. You want to find the manager supervising the largest number of employees.

C. You want to identify the person who makes the highest salary for all employees.

D. You want to rank the top three sales representatives who have sold the maximum number of
products.

Answer: D

Explanation:

If you want to rank the top three sales representatives who have sold the maximum number of
products TOP-N query will be the best solution. TON-N queries use inline views and are handy
for displaying a short list of table data, based on "greatest" or "least" criteria.

Incorrect Answers

A: To complete this request you don??t need to use TOP-N query.
B: It is not necessary to use TOP-N query in this case.
C: To complete this request you don??t need to use TOP-N query.

Question: 24. (B)
Evaluate this SQL statement:
SELECT c.customer_id, o.order_id, o.order_date, p.product_name
FROM customer c, curr_order o, product p
WHERE customer.customer_id = curr_order.customer_id
AND o.product_id = p.product_id
ORDER BY o.order_amount;
This statement fails when executed. Which change will correct the problem?

A. Include the ORDER_AMOUNT column in the SELECT list.

B. Use the table name in the ORDER BY clause.

C. Remove the table aliases from the WHERE clause.

D. Use the table aliases instead of the table names in the WHERE clause.

E. Remove the table alias from the ORDER BY clause and use only the column name.

Answer: D

Explanation:
When an alias is define for a table name in join then you cannot use the table name instead of
alias in the FROM clause while using alias in the SELECT list. An alias should be used in the
WHERE clause also.

Question: 25. (B)

You want to display the titles of books that meet these criteria:

1. Purchased before January 21, 2001
2. Price is less then $500 or greater than $900
You want to sort the results by their data of purchase, starting with the most recently bought
book. Which statement should you use?

A. SELECT book_title FROM books
WHERE price between 500 and 900
AND purchase_date < ??21-JAN-2001?? ORDER BY purchase_date; B. SELECT book_title FROM books WHERE price IN (500,900) AND purchase_date < '21-JAN-2001' ORDER BY purchase date ASC; C. SELECT book_title FROM books WHERE price <> 900
AND purchase_date < '21-JAN-2001' ORDER BY purchase date DESC; D. SELECT book_title FROM books WHERE (price <> 900)
AND purchase_date < '21-JAN-2001' ORDER BY purchase date DESC; Answer: D Explanation: This statement provides required results. Incorrect Answers A: This query will show books with price in range $500 and $900, not less then $500 or greater than $900. B: This query will show books with prices exactly $500 or $900, not less then $500 or greater than $900. C: This order will not show correct rows because of incorrect syntax in the WHERE clause. Questions 26. (B) For which task would you use the WHERE clause in a SELECT statement? A. to designate the ORDER table location B. to compare PRODUCT_ID values to 7382 C. to display only unique PRODUCT_ID values D. to restrict the rows returned by a GROUP BY clause Answer: B Explanation: You can use the WHERE clause in the SELECT statement to implement the condition on the statement by comparing values. Question: 27. (B) The STUDENT_GRADES table has these columns: STUDENT_ID NUMBER(12) SEMESTER_END DATE GPA NUMBER(4,3) The registrar has requested a report listing the students' grade point averages (GPA), sorted from highest grade point average to lowest within each semester, starting from the earliest date. Which statement accomplishes this? A. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end DESC, gpa DESC; B. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end ASC, gpa ASC; C. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end, gpa DESC; D. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end DESC; E. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end ASC;. Answer: C Explanation: This answer shows correct syntax and semantics to receive desired result. Incorrect Answers A: Semesters will be sorted started from the oldest date, not the earliest. B: GPA data will be sorted in ascending order, what is opposite to our task. D: Semesters will be sorted started from the oldest date, not the earliest. Only difference with answer A is order of columns in the ORDER BY clause. E: This query has wrong order of columns to sort: results need to be sorted first by semester, than by grade point average. Question: 28. (B) The ORDERS table has these columns: ORDER_ID NUMBER(4) NOT NULL CUSTOMER_ID NUMBER(12) NOT NULL ORDER_TOTAL NUMBER(10,2) The ORDERS table tracks the Order nnmher, the order total, and the customer to whom the Order belongs. Which two statements retrieve orders with an inclusive total that ranges between 100.00 and 2000.00 dollars? (Choose two.) A. SELECT customer_id, order_id, order_total FROM orders RANGE ON order_total (100 AND 2000) INCLUSIVE; B. SELECT customer_id, order_id, order_total FROM orders HAVING order_total BETWEEN 100 and 2000; C. SELECT customer_id, order_id, order_total FROM orders WHERE order_total BETWEEN 100 and 2000; D. SELECT customer_id, order_id, order_total FROM orders WHERE order_total >= 100 and <= 2000; E. SELECT customer_id, order_id, order_total FROM orders WHERE order_total >= 100 and order_total <= 2000; Answer: C & E Explanation: Answers C and E provide correct results to show. You can use BETWEEN or comparison operations to retrieve data. Incorrect Answers A: There is no RANGE ON or INCLUSIVE keyword in Oracle. B: HAVING clause can be use only in conjunction with the GROUP BY clause. D: Syntax "order_total >= 100 and <= 2000?? is incorrect. Question: 29. (B) Examine the structure of the PRODUCT table.

You want to display all product identification numbers of products for which there are 500 or more available for immediate sale. You want the product numbers displayed alphabetically by supplier, then by product number from lowest to highest. Which statement should you use to achieve therequired results?

A. SELECT product_id
FROM product
WHERE qty_per_unit >= 500
ORDER BY supplier_id, product_id;

B. SELECT product_id
FROM product
WHERE qty_per_unit >= 500
SORT BY supplier_id, product_id;

C. SELECT product_id
FROM product
WHERE qty_per_unit >= 500
ORDER BY supplier_id, product_id DESC;

D. SELECT product_id
FROM product
WHERE qty_per_unit > 500
SORT BY supplier_id, product_id;

Answer: A

Explanation:

SELECT product_id
FROM product
WHERE qty_per_unit >= 500
ORDER BY supplier_id, product_id;
This statement will give the product_id from product table where qty_per_unit will be equal to and greater than 500 and it will sort it in ascending order by default.

Question: 30. (B)

Examine the data in TEACHER table.



Which query should you use to return the following values from the TEACHER table?
Name Subject

------------------------------------- -------------------
Jones, Karen HST_REVOL
Hopewell, Mary Elizabeth HST_RELIG

A. SELECT last_name||', '||first_name "Name", subject_id "Subject"
FROM teacher
WHERE subject_id LIKE 'HST\_%' ESCAPE '\';

B. SELECT last_name||', '||first_name "Name", subject_id "Subject"
FROM teacher
WHERE subject_id = 'HST\_R%';

C. SELECT last_name||', '||first_name "Name", subject_id "Subject"
FROM teacher
WHERE subject_id LIKE '%HST\_R%' ESC '\';

D. SELECT last_name||', '||first_name "Name", subject_id "Subject"
FROM teacher
WHERE subject_id LIKE 'HST_%';

Answer: A

Explanation:
When you need to have an exact match for the actual '%' and '_' characters use the ESCAPE option. This option specifies what the ESCAPE character is.

Read more »

Posted in: Oracle

Part - 2 Oracle Interview

Question: 11. (A)

The CUSTOMERS table has these columns:

CUSTOMER_ID | NUMBER(4) | NOT NULL
CUSTOMER_NAME | VARCHAR2(100) | NOT NULL
STREET_ADDRESS | VARCHAR2(150) |
CITY_ADDRESS | VARCHAR2(50) |
STATE_ADDRESS | VARCHAR2(50) |
PROVINCE_ADDRESS | VARCHAR2(50) |
COUNTRY_ADDRESS | VARCHAR2(50) |
POSTAL_CODE | VARCHAR2( 12) |
CUSTOMER_PHONE | VARCHAR2(20) |

Which statement finds the rows in the CUSTOMERS table that do not have a postal code?

A. SELECT customer_id, customer_name
FROM customers
WHERE postal_code CONTAINS NULL;

B. SELECT customer_id, customer_name
FROM customers
WHERE postal_code = '________';

C. SELECT customer_id, customer_name
FROM customers
WHERE postal_code IS NULL;

D. SELECT customer_id, customer_name
FROM customers
WHERE postal code IS NVL;

E. SELECT customer_id, customer_name
FROM customers
WHERE postal_code = NULL;

Answer: C

Explanation:

This statement returns the rows in the CUSTOMERS table that do not have a postal code. The correct syntax to check NULL values is usage of ??IS NULL?? clause.

Incorrect Answers

A: "CONTAINS NULL" is incorrect clause in Oracle.
B: This statement will just check if postal code equals to string "________';
D: Usage of ??IS NVL?? is incorrect in Oracle. But there is a function NVL() you can use to processNULL values.
E: You can not use equal comparison to check whether value is NULL or not. Use construction
"IS NULL" or "IS NOT NULL" to do that.

Question: 12. (A)

Evaluate these two SQL statements:
SELECT last_name, salary , hire_date

FROM EMPLOYEES
ORDER BY salary DESC;
SELECT last_name, salary, hire_date
FROM EMPLOYEES
ORDER BY 2 DESC;

What is true about them?

A. The two statements produce identical results.

B. The second statement returns a syntax error.

C. There is no need to specify DESC because the results are sorted in descending
order by default.

D. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.

Answer: A

Explanation:

These two statements produce identical results, because it is possible even to use numbers to indicate the column position where Oracle should order the output from a statement.

Incorrect Answers

B: Second statement is correct and it will not return a syntax error.
C: The results are sorted in ascending order by default.
D: There is no corrections need to be made for the statements. They will return identical results.

Question: 13. (A)

Evaluate the set of SQL statements:
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCNAR2(14),
loc VARCNAR2(13));
ROLLBACK;
DESCRIBE DEPT

What is true about the set?

A. The DESCRIBE DEPT statement displays the structure of the DEPT table.

B. The ROLLBACK statement frees the storage space occupies by the DEPT table.

C. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.

D. The DESCRIBE DEPT statement displays the structure of the DEPT table only if the us a COMMIT statement introduced before the ROLLBACK statement..

Answer: A

Explanation:

The structure of the DEPT table will be displayed because the CREATE TABLE statement is DDL operation and it cannot be rolled back because implicit commit occurs on the database when a user exits SQL*Plus or issues a data-definition language (DDL) command such as a create table statement, user to create a database object, or an lter table statement, used to alter a database object.

Incorrect Answers

B: The ROLLBACK statement has nothing to do with the storage space of the DEPT table.
C: The DESCRIBE DEPT statement does not produce the error. It displays the structure of the DEPT table.
D: The COMMIT statement does not need to be introduced because implicit commit occurs on the database after creation of the table.

Question: 14. (A)

Examine the data of the EMPLOYEES table.
EMPLOYEES (EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to
the EMPLOYEE_ID)
EMPLOYEE_ID | EMP_NAME | DEPT_ID | MGR_ID | JOB_ID | SALARY |
101 | Smith | 20 | 120 | SA_REP | 4000 |
102 | Martin | 10 | 105 | CLERK | 2500 |
103 | Chris | 20 | 120 | IT_ADMIN | 4200 |
104 | John | 30 | 108 | HR_CLERK | 2500 |
105 | Diana | 30 | 108 | HR_MGR | 5000 |
106 | Bryan | 40 | 110 | AD_ASST | 3000 |
108 | Jennifer | 30 | 110 | HR_DIR | 6500 |
110 | Bob | 40 | | EX_DIR | 8000 |
120 | Ravi | 20 | 110 | SA_DIR | 6500 |

Evaluate this SQL statement:
SELECT e.employee_id "Emp_id", e.emp_name "Employee",
e.salary, m.employee_id "Mgr_id", m.emp_name "Manager"
FROM employees e, employees m
WHERE e.mgr_id = m.employee_id
AND e.salary > 4000;
What is its output?

A.
EMP_id EMPLOYEE SALARY Mgr_id Manager
------- ---------- --------- ------------- --------------
110 Bob 8000 Bob
120 Ravi 6500 110 Ravi
108 Jennifer 6500 110 Jennifer
103 Chris 4200 120 Chris
105 Diana 5000 108 Diana

B.
EMP_id EMPLOYEE SALARY Mgr_id Manager
------- ---------- --------- ------------- --------------
120 Ravi 6500 110 Bob
108 Jennifer 6500 110 Bob
103 Chris 4200 120 Ravi
105 Diana 500 108 Jennifer

C.
EMP_id EMPLOYEE SALARY Mgr_id Manager
------- ---------- --------- ------------- --------------
110 Bob 800
120 Ravi 6500 110 Bob
108 Jennifer 6500 110 Bob
103 Chris 4200 120 Ravi
105 Diana 5000 108 Jennifer

D
EMP_id EMPLOYEE SALARY Mgr_id Manager
------- ---------- --------- ------------- --------------
110 Bob 8000 110 Bob
120 Ravi 6500 120 Ravi
108 Jennifer 6500 108 Jennifer
103 Chris 4200 103 Chris
105 Diana 5000 105 Dina

E. The SQL statement produces an error.

Answer: B

Explanation:

This statement lists the ID, name, and salary of the employee, and the ID and name of the employee's manager, for all the employees who have a manager and earn more than 4000

Incorrect Answers

A: This output will be provided by different query.
C: This output will be provided by different query.
D: This output will be provided by different query.
E: This SQL query will not produce error, it will show results as in answer B.

Question: 15. (A)

Which /SQL*Plus feature can be used to replace values in the WHERE clause?

A. Substitution variables

B. Replacement variables

C. Prompt variables

D. Instead-of variables

E. This feature cannot be implemented through /SQL*Plus.

Answer: A

Explanation:

Lexical substitution variables can be used to replace values in the WHERE clause.

Incorrect Answers

B: There is no replacement variables SQL*Plus feature in Oracle.
C: There is no prompt variables SQL*Plus feature in Oracle.
D: There is no instead-of variables SQL*Plus feature in Oracle.
E: This feature is implemented in the SQL*Plus with lexical substitution variables.

Question: 16. (A)

You are formulating queries in a SQL*Plus. Which of the following statement correctly describes how to specify a column alias?

A. Place the alias at the beginning of the statement to describe the table.

B. Place the alias after each column separated by a space to describe the column.

C.Place the alias after each column separated by a comma to describe the column.

D. Place the alias at the end of the statement to describe the table.

Answer: B

Explanation:

Aliases do not describe the tables they describe columns so the alias should be place at the end of each column and separated by a space to describe the column.

Question: 17. (A)

You want to use a function in you column clause of a SQL statement. The NVL function
accomplishes which of the following tasks?

A. Assists in the distribution of output across multiple columns.

B. Enables you to specify alternate output for non-NULL column values.

C. Enables you to specify alternated out for NULL column values.

D. Nullifies the value of the column out put.

Answer: C

Explanation:

NVL function is simple if_then operation that test column values out to see whether it is NULL and if it find it is null then NVL substitutes the specified default value for the NULL value.

Question: 18. (A)

You want to use SQL*Plus to connect to the oracle database. Which of the following choices does not indicate a component you must specify when logging into the oracle?

A. The SQL*Plus Keyword.

B. The username

C. The password.

D. The database name.

Answer: D

Explanation:

When connecting to the database you don??t need to specify the name of the database and when you are not specifying the name of the database then you will be connected to the local database.

Question: 19. (A)

The EMPLOYEE_HISTORY table contains these columns:
EMPLOYEE_ID NUMBER
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER
POSITION VARCHAR2(30)
SALARY NUMBER(6,2)
HIRE_DATE DATE
DEPART_DATE DATE

The EMPLOYEE_HISTORY table contains only former employees.
You need to create a report to display all former employees that were hired on or
after January 1, 1996. The data should display in this format:
Former - Employee - Term of Employment
---------------------------- ----------------------------------
14837 - SMITH 10-MAY-92 / 01-JUN-01

Which SELECT statement could you use?

A. SELECT employee_id||' - '||last_name AS Former Employee,
hire_date||' / '||depart_date AS Term of Employment
FROM employee_history
WHERE hire_date > '31-DEC-95';

B. SELECT employee_id||' - '||last_name "AS Former Employee",
hire_date||' / '||depart_date "AS Term of Employment"
FROM employee_history
WHERE hire_date > '31-DEC-95';

C. SELECT employee_id||' - '||last_name 'Former Employee',
hire_date||' / '||depart_date 'Term of Employment'
FROM employee_history
WHERE hire_date > '31-DEC-95'
AND depart_date > NULL;

D. SELECT employee_id||' - '||last_name "Former Employee",
hire_date||' / '||depart_date "Term of Employment"
FROM employee_history
WHERE hire_date > '31-DEC-95'
AND depart_date <> NULL;

E. SELECT employee_id||' - '||last_name "Former Employee",
hire_date||' / '||depart_date "Term of Employment"
FROM employee_history
WHERE hire_date > '31-DEC-95'
AND depart_date IS NOT NULL;

Ans. E


Question: 20. (A)

The EMPloyee table contains these columns:
Empno Number(4)
Ename Varchar2(10)
job varchar2(10)
sal Varchar2(10)
You need to display the employees information by using this query.
How many columns are presented after executing this query:
SELECT Empno||','||Ename||','||Job "Employee Information" FROM employee;

A) 1

B) 2

C) 3

D) 0

E) 4

Answer: A.

Explanation

When we used Concatenation operator between diferent no of columns so then one Column as a resultant column is to be apperead.

Incorrect answers

B.Not 2
C.Not 3
D.Not 0
E.Not 4

Read more »

Posted in: Oracle

Part - 1 Oracle Interview

Question: 1.(A)

Which SELECT statement should you use if you want to display unique combinations of the POSITION and MANAGER values from the EMPLOYEE table?

A. SELECT DISTINCT position, manager
FROM employee;

B. SELECT position, manager DISTINCT
FROM employee;

C. SELECT position, manager
FROM employee;

D. SELECT position, DISTINCT manager
FROM employee;

Answer: A

Explanation:

To display a unique values in the result you can use the DISTINCT key word this will eliminatethe duplicate values from the result of the query.

Question: 2. (A)

Which two are attributes of /SQL*Plus? (Choose two)

A. /SQL*Plus commands cannot be abbreviated.

B. /SQL*Plus commands are accesses from a browser.

C. /SQL*Plus commands are used to manipulate data in tables.

D. /SQL*Plus commands manipulate table definitions in the database.

E. /SQL*Plus is the Oracle proprietary interface for executing SQL statements.

Answer: C, D

Explanation:

SQL*Plus commands can be used to manipulate data in tables and SQL*Plus commands
manipulate table definitions in the database.

Incorrect Answers

A: SQL*Plus commands can be abbreviated. Like command DESCRIBE can be abbreviated as
DESC, or SELECT as SELE.
B: SQL*Plus commands are not accesses from a browser.
E: SQL*Plus is not only the Oracle proprietary interface for executing SQL statements.

Question: 3. (A)

You need to perform certain data manipulation operations through a view called EMP_DEPT_VU, which you previously created.
You want to look at the definition of the view (the SELECT statement on which the view was create.)
How do you obtain the definition of the view?

A. Use the DESCRIBE command in the EMP_DEPT VU view.

B. Use the DEFINE VIEW command on the EMP_DEPT VU view.

C. Use the DESCRIBE VIEW command on the EMP_DEPT VU view.

D. Query the USER_VIEWS data dictionary view to search for the EMP_DEPT_VU view.

E. Query the USER_SOURCE data dictionary view to search for the EMP_DEPT_VU view.

F. Query the USER_OBJECTS data dictionary view to search for the EMP_DEPT_VU view.

Answer: D

Explanation:

To look on the view definition you need to query the USER_VIEWS data dictionary view and search for the EMP_DEPT_VU view.

Incorrect Answers

A: You cannot see the definition of the view using the DESCRIBE command.
B: There is no DEFINE VIEW command in Oracle.
C: There is no DESCRIBE VIEW command in Oracle.
E: You cannot use the USER_SOURCE data dictionary view to see the definition of your view: it is used to store system objects definitions only.
F: You can find record about view in the USER_OBJECTS, but it does not contain the definition of the view itself.

Question: 4. (A)

Which is an /SQL*Plus command?

A. INSERT

B. UPDATE

C. SELECT

D. DESCRIBE

E. DELETE

F. RENAME

Answer: D

Explanation:

There is only one SQL*Plus command in this list: DESCRIBE. It cannot be used as SQL
command. This command returns a description of tablename, including all columns in that table, the datatype for each column, and an indication of whether the column permits storage of NULL values.

Incorrect Answers

A: INSERT is not a SQL*Plus command. It??s data-manipulation language (DML) command.
B: UPDATE is not a SQL*Plus command. It??s data-manipulation language (DML) command.
C: SELECT is not a SQL*Plus command.
E: DELETE is not a SQL*Plus command. It??s data-manipulation language (DML) command.
F: RENAME is not a SQL*Plus command.

Question: 5. (A)

You need to produce a report for mailing labels for all customers. The mailing label must have only the customer name and address. The CUSTOMERS table has these columns:
CUST_ID NUMBER(4) NOT NULL
CUST_NAME VARCHAR2(100)
CUST_ADDRESS VARCHAR2(150)
CUST_PHONE VARCHAR2(20)
Which SELECT statement accomplishes this task?

A. SELECT*
FROM customers;

B. SELECT name, address
FROM customers;

C. SELECT id, name, address, phone
FROM customers;

D. SELECT cust_name, cust_address
FROM customers;

E. SELECT cust_id, cust_name, cust_address, cust_phone
FROM customers;.

Answer: D

Explanation:

This answer provides correct list of columns for the output.

Incorrect Answers

A: This answer does not provide correct list of columns for the output. It is not required to showall columns of the table. Symbol ?????? is used in the SELECT command to substitute a list of all columns of the table.
B: This answer does not provide correct list of columns for the output. There are not NAME and ADDRESS columns in the CUSTOMERS table.
C: This answer does not provide correct list of columns for the output. There are not ID, NAME, ADDRESS or PHONE columns in the CUSTOMERS table.
E: This answer does not provide correct list of columns for the output. It is not required to show all columns of the table.

Question: 6. (A)

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?

A. Selection, projection, join

B.Difference, projection, join

C.Selection, intersection, join

D.Intersection, projection, join

E. Difference, projection, product

Answer: A

Explanation:

Selection, projection and join capabilities of a SELECT statement are performed in this view.

Incorrect Answers

B: Selection is performed in this query, not difference. There is no capability with name difference for a SELECT statement exists.
C: There is no intersection in this SELECT statement used.
D: There is no intersection in this SELECT statement used.
E: There is no difference or product capabilities exist for a SELECT statement.

Question: 7. (A)

Which two statements are true regarding the ORDER BY clause? (Choose two)

A. The sort is in ascending by order by default.

B. The sort is in descending order by default.

C. The ORDER BY clause must precede the WHERE clause.

D. The ORDER BY clause is executed on the client side.

E. The ORDER BY clause comes last in the SELECT statement.

F. The ORDER BY clause is executed first in the query execution.

Answer: A & E

Explanation:

The ORDER BY clause does sort data in ascending order by default. And the ORDER BY clause comes last in the SELECT statement: after FROM or WHERE or GROUP BY clause.

Incorrect Answers

B: The ORDER BY clause does sort data in ascending, not descending order, by default.
C: The ORDER BY clause must be after the WHERE clause in the SQL statement.
D: The ORDER BY clause is executed on the server side as the whole SQL statement is.
F: The ORDER BY clause is executed last in the query execution, after results are limited with the WHERE and GROUP BY clause conditions.

Question: 8. (A)

From SQL*Plus, you issue this SELECT statement:
SELECT* From orders;
You use this statement to retrieve data from a data table for __________. (Choose all that apply)

A. Updating

B. Viewing

C. Deleting

D. Inserting

E. Truncating

Answer: B & D

Explanation:

You can use SELECT statement to display and to insert data into different table.

Incorrect Answers

A: You cannot update data with SELECT statement. Update command is used for this purpose.
C: You cannot delete data with SELECT statement. Delete command is used for this purpose.
E:You cannot truncate data with SELECT statement. Truncate command is used for this
purpose.

Question: 9. (A)

Evaluate this SQL*Plus command:
START delaccount Which task will this command accomplish?

A. It executes the DELACCOUNT PL/SQL routine.

B. It runs the DELACCOUNT.SQL script file.

C. It creates the DELACCOUNT file using the default file extension.

D. It invokes the editor to edit the contents of the DELACCOUNT file.

Answer: B

Explanation:

START is SQL*Plus command which is used to run the already stored script. It will not display the actual command in the script.

Question: 10. (A)

Which SQL SELECT statement performs a projection, a selection, and join when executed?

A. SELECT p.id_number, m.manufacturer_id, m.city
FROM product p, manufacturer m WHERE p.manufacturer_id = m.manufacturer_id
AND m.manufacturer_id = 'NF10032';

B. SELECT id_number, manufacturer_id
FROM product ORDER BY manufacturer_id, id_number;

C. SELECT id_number, manufacturer_id
FROM product WHERE manufacturer_id = 'NF10032';

D. SELECT manufacturer_id, city
FROM manufacturer AND manufacturer_id = 'NF10032'
ORDER BY city;

Answer: A

Explanation:

PROJECTION will select the whole column values of the table while SELECTION will gives you rows of the table and JOIN is joining the two tables on a same column. To get all these task done in one statement you can use this command
SELECT p.id_number, m.manufacturer_id, m.city FROM product p, manufacturer m
WHERE p.manufacturer_id = m.manufacturer_id
AND m.manufacturer_id = 'NF10032';

Read more »

Posted in: Oracle