Sunday, January 11, 2009

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';

0 comments:

Followers

The Daily Puppy

 
Design by Wordpress Theme | Bloggerized by Free Blogger Templates | JCPenney Coupons