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. Its data-manipulation language (DML) command.
B: UPDATE is not a SQL*Plus command. Its data-manipulation language (DML) command.
C: SELECT is not a SQL*Plus command.
E: DELETE is not a SQL*Plus command. Its 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';
Custom Search
Saturday, November 15, 2008
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 dont 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
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 dont 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
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 managers manager, not employs 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 dont 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 dont 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.
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 managers manager, not employs 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 dont 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 dont 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.
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 < 100) AND order_id BETWEEN 1500 AND 1575 ORDER BY order_id ASC, quantity; C. SELECT order_id, line_item_id, product_id, quantity FROM line_item WHERE (quantity > 9 OR quantity < 101) AND order_id BETWEEN 1500 AND 1575 ORDER BY order_id, quantity; D. SELECT order_id, line_item_id, product_id, quantity FROM line_item WHERE quantity BETWEEN 10 AND 100 AND order_id BETWEEN 1500 AND 1575 ORDER BY order_id, quantity DESC; Answer: D Explanation: Following query will give all the desire result by using to condition with ORDER BY clause with order_id by default will be sorted in ascending order and quantity will be sorted in escending order SELECT order_id, line_item_id, product_id, quantity FROM line_item WHERE quantity BETWEEN 10 AND 100 AND order_id BETWEEN 1500 AND 1575 ORDER BY order_id, quantity DESC; Question: 34. (B) The ITEM table contains these columns: ITEM_ID NUMBER(9) COST NUMBER(7,2) RETAIL NUMBER(7,2) You need to create a report that displays the cost, the retail price, and the profit for item number 783920. To calculate the profit, subtract the cost of the item from its retail price, and then deduct an administrative fee of 25 percent of this derived value. Which SELECT statement produces the desired results? A. SELECT cost, retail, (retail - cost) - ((retail - cost) * .25) "Profit" FROM item WHERE item_id = 783920; B. SELECT cost, retail, (retail - cost) - retail - (cost * .25) "Profit" FROM item WHERE item_id = 783920; C. SELECT cost, retail, (retail - cost - retail - cost) * .25 "Profit" FROM item WHERE item_id = 783920; D. SELECT cost, retail, retail - cost - retail - cost * .25 "Profit" FROM item WHERE item_id = 783920; Answer: A Explanation: To get a complete desired report you can use the following query with expressions SELECT cost, retail, (retail - cost) - ((retail - cost) * .25) "Profit" FROM item WHERE item_id = 783920; Question: 35. (B) The ITEM table contains these columns: ITEM_ID NUMBER(9) COST NUMBER(7,2) RETAIL NUMBER(7,2) The RETAIL and COST columns contain values greater than zero. Evaluate these two SQL statements: 1. SELECT item_id, (retail * 1.25) + 5.00 - (cost * 1.10) - (cost * .10) AS Calculated Profit FROM item; 2. SELECT item_id, retail * 1.25 + 5.00 - cost * 1.10 - cost * .10 "Calculated Profit" FROM item; What will be the result? A. Statement 1 will display the 'Calculated Profit' column heading. B. Statement 1 and statement 2 will return the same value. C. Statement 1 will return a higher value than statement 2. D. One of the statements will NOT execute. Answer: D Explanation: For the above written statements the first statement will not execute because the column alias contain spaces so it should be place in the a double quotation marks for the statement to execute successful. Question: 36. (B) The EMP table contains these columns: LAST NAME VARCHAR2(25) SALARY NUMBER(6,2) DEPARTMENT_ID NUMBER(6) You need to display the employees who have not been assigned to any department. You write the SELECT statement: SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP WHERE DEPARTMENT_ID = NULL; What is true about this SQL statement? A. The SQL statement displays the desired results. B. The column in the WHERE clause should be changed to display the desired results. C. The operator in the WHERE clause should be changed to display the desired results. D. The WHERE clause should be changed to use an outer join to display the desired results. Answer: C Explanation: The operator in the WHERE clause should be changed to display the desired results. There are times when you want to substitute a value in place of NULL. Oracle provides this functionality with a special function, called NVL(). You cannot use operation equal with NULL, but you can achieve desired results using NVL() function after the WHERE clause. Incorrect Answers A: The SQL statement will generate an error because you cannot use operation equal with NULL. B: The column in the WHERE clause should not be changed to display the desired results. D: Since there is only one table used in this query you don't need to use outer join to display the desired results. Question: 37. (B) Which two statements are true about WHERE and HAVING clauses? (Choose two) A. A WHERE clause can be used to restrict both rows and groups. B. A WHERE clause can be used to restrict rows only. C. A HAVING clause can be used to restrict both rows and groups. D. A HAVING clause can be used to restrict groups only. E. A WHERE clause CANNOT be used in a query of the query uses a HAVING clause. F. A HAVING clause CANNOT be used in subqueries. Answer: B, C Explanation : HAVING clause to specify which groups are to be displayed and thus further restrict the groups on the basis of aggregate information.The Oracle server performs the following steps when you use the Having clause 1. rows are grouped 2. the group function is applied to the group 3. the group that match the criteria in the Having clause are displayed. WHERE clause cannot be use to restrict groups HAVING clause use to restrict groups WHERE clause cannot be use when there is group functions. Incorrect Answers : A. Where clause cannot be use to restrict groups D. When HAVING clause is use rows are grouped as well. E. WHERE clause cannot be use when there is group function, instead HAVING is to be use. F. There is no constraint to use HAVING clause in a subqueries. Question: 38. (B) You are sorting data in a table in you SELECT statement in descending order. The column you are sorting on contains NULL records, where will the NULL record appears? A. At the beginning of the list. B. At the end of the list. C. In the middle of the list. D. At the same location they are listed in the unordered table. Answer: A Explanation: When sorting a column with null values in ascending order then the oracle places the Null values at the end of the list if the sorting is in descending order the oracle places the null values at the start of the list. Question: 39. (B) The ACCOUNT table contains these columns: ACCOUNT_ID NUMBER(12) PREVIOUS_BALANCE NUMBER(7,2) PAYMENTS NUMBER(7,2) NEW_PURCHASES NUMBER(7,2) CREDIT_LIMIT NUMBER(7) You need to display the account number, finance charge, and current balance for accounts 1500 through 2000 with a current balance greater than the account's credit limit. The finance charge is .9 percent (.009) of the previous balance. Adding the previous balance value, new purchases value, and finance charge value, and then subtracting the payments value yields the current balance value. Evaluate this statement: SELECT account_id, NVL(previous_balance, 0) * .009 finance_charge, NVL(new_purchases, 0) + (NVL(previous_balance, 0) * 1.009) - NVL(payments, 0) current balance FROM account WHERE (new_purchases + (previous_balance * 1.009)) - payments > 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.
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 < 100) AND order_id BETWEEN 1500 AND 1575 ORDER BY order_id ASC, quantity; C. SELECT order_id, line_item_id, product_id, quantity FROM line_item WHERE (quantity > 9 OR quantity < 101) AND order_id BETWEEN 1500 AND 1575 ORDER BY order_id, quantity; D. SELECT order_id, line_item_id, product_id, quantity FROM line_item WHERE quantity BETWEEN 10 AND 100 AND order_id BETWEEN 1500 AND 1575 ORDER BY order_id, quantity DESC; Answer: D Explanation: Following query will give all the desire result by using to condition with ORDER BY clause with order_id by default will be sorted in ascending order and quantity will be sorted in escending order SELECT order_id, line_item_id, product_id, quantity FROM line_item WHERE quantity BETWEEN 10 AND 100 AND order_id BETWEEN 1500 AND 1575 ORDER BY order_id, quantity DESC; Question: 34. (B) The ITEM table contains these columns: ITEM_ID NUMBER(9) COST NUMBER(7,2) RETAIL NUMBER(7,2) You need to create a report that displays the cost, the retail price, and the profit for item number 783920. To calculate the profit, subtract the cost of the item from its retail price, and then deduct an administrative fee of 25 percent of this derived value. Which SELECT statement produces the desired results? A. SELECT cost, retail, (retail - cost) - ((retail - cost) * .25) "Profit" FROM item WHERE item_id = 783920; B. SELECT cost, retail, (retail - cost) - retail - (cost * .25) "Profit" FROM item WHERE item_id = 783920; C. SELECT cost, retail, (retail - cost - retail - cost) * .25 "Profit" FROM item WHERE item_id = 783920; D. SELECT cost, retail, retail - cost - retail - cost * .25 "Profit" FROM item WHERE item_id = 783920; Answer: A Explanation: To get a complete desired report you can use the following query with expressions SELECT cost, retail, (retail - cost) - ((retail - cost) * .25) "Profit" FROM item WHERE item_id = 783920; Question: 35. (B) The ITEM table contains these columns: ITEM_ID NUMBER(9) COST NUMBER(7,2) RETAIL NUMBER(7,2) The RETAIL and COST columns contain values greater than zero. Evaluate these two SQL statements: 1. SELECT item_id, (retail * 1.25) + 5.00 - (cost * 1.10) - (cost * .10) AS Calculated Profit FROM item; 2. SELECT item_id, retail * 1.25 + 5.00 - cost * 1.10 - cost * .10 "Calculated Profit" FROM item; What will be the result? A. Statement 1 will display the 'Calculated Profit' column heading. B. Statement 1 and statement 2 will return the same value. C. Statement 1 will return a higher value than statement 2. D. One of the statements will NOT execute. Answer: D Explanation: For the above written statements the first statement will not execute because the column alias contain spaces so it should be place in the a double quotation marks for the statement to execute successful. Question: 36. (B) The EMP table contains these columns: LAST NAME VARCHAR2(25) SALARY NUMBER(6,2) DEPARTMENT_ID NUMBER(6) You need to display the employees who have not been assigned to any department. You write the SELECT statement: SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP WHERE DEPARTMENT_ID = NULL; What is true about this SQL statement? A. The SQL statement displays the desired results. B. The column in the WHERE clause should be changed to display the desired results. C. The operator in the WHERE clause should be changed to display the desired results. D. The WHERE clause should be changed to use an outer join to display the desired results. Answer: C Explanation: The operator in the WHERE clause should be changed to display the desired results. There are times when you want to substitute a value in place of NULL. Oracle provides this functionality with a special function, called NVL(). You cannot use operation equal with NULL, but you can achieve desired results using NVL() function after the WHERE clause. Incorrect Answers A: The SQL statement will generate an error because you cannot use operation equal with NULL. B: The column in the WHERE clause should not be changed to display the desired results. D: Since there is only one table used in this query you don't need to use outer join to display the desired results. Question: 37. (B) Which two statements are true about WHERE and HAVING clauses? (Choose two) A. A WHERE clause can be used to restrict both rows and groups. B. A WHERE clause can be used to restrict rows only. C. A HAVING clause can be used to restrict both rows and groups. D. A HAVING clause can be used to restrict groups only. E. A WHERE clause CANNOT be used in a query of the query uses a HAVING clause. F. A HAVING clause CANNOT be used in subqueries. Answer: B, C Explanation : HAVING clause to specify which groups are to be displayed and thus further restrict the groups on the basis of aggregate information.The Oracle server performs the following steps when you use the Having clause 1. rows are grouped 2. the group function is applied to the group 3. the group that match the criteria in the Having clause are displayed. WHERE clause cannot be use to restrict groups HAVING clause use to restrict groups WHERE clause cannot be use when there is group functions. Incorrect Answers : A. Where clause cannot be use to restrict groups D. When HAVING clause is use rows are grouped as well. E. WHERE clause cannot be use when there is group function, instead HAVING is to be use. F. There is no constraint to use HAVING clause in a subqueries. Question: 38. (B) You are sorting data in a table in you SELECT statement in descending order. The column you are sorting on contains NULL records, where will the NULL record appears? A. At the beginning of the list. B. At the end of the list. C. In the middle of the list. D. At the same location they are listed in the unordered table. Answer: A Explanation: When sorting a column with null values in ascending order then the oracle places the Null values at the end of the list if the sorting is in descending order the oracle places the null values at the start of the list. Question: 39. (B) The ACCOUNT table contains these columns: ACCOUNT_ID NUMBER(12) PREVIOUS_BALANCE NUMBER(7,2) PAYMENTS NUMBER(7,2) NEW_PURCHASES NUMBER(7,2) CREDIT_LIMIT NUMBER(7) You need to display the account number, finance charge, and current balance for accounts 1500 through 2000 with a current balance greater than the account's credit limit. The finance charge is .9 percent (.009) of the previous balance. Adding the previous balance value, new purchases value, and finance charge value, and then subtracting the payments value yields the current balance value. Evaluate this statement: SELECT account_id, NVL(previous_balance, 0) * .009 finance_charge, NVL(new_purchases, 0) + (NVL(previous_balance, 0) * 1.009) - NVL(payments, 0) current balance FROM account WHERE (new_purchases + (previous_balance * 1.009)) - payments > 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.
Part - 5 Oracle Interview
Question: 41. (C)
Management has asked you to calculate the value 12*salary* comossion_pct for all the
employees in the EMP table. The EMP table contains these columns:
Which statement ensures that a value is displayed in the calculated columns for all employees?
A. SELECT last_name, 12*salary*commison_pct
FROM emp;
B. SELECT last_name, 12*salary* (commission_pct,0)
FROM emp;
C. SELECT last_name, 12*salary*(nvl(commission_pct,0))
FROM emp;
D. SELECT last_name, 12*salary*(decode(commission_pct,0))
FROM emp;
Answer: C
Explanation:
This SELECT statement provides correct usage of NVL function to calculate columns for all employees. Oracle give you possibility to substitute a value in place of NULL. The basic syntax for NVL() is NVL(column_name, value_if_null). Notice that the column specified in NVL() contains an actual value. That value is what Oracle returns; when the column is NULL, the special string is returned. The value specified to be returned if the column value is NULL must be the same
datatype as the column specified.
Incorrect Answers
A: This SELECT statement will return NULL value for rows with NULL COMMISION_PCT
column.
B: It is incorrect syntax in this query: NVL function needs to be used for correct result.
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 cannot have only two parameters.
Question: 42. (C)
Examine the description of the STUDENTS table:
Which two aggregate functions are valid on the START_DATE column? (Choose two)
A. SUM(start_date)
B. AVG(start_date)
C. COUNT(start_date)
D. AVG(start_date, end_date)
E. MIN(start_date)
F. MAXIMUM(start_date)
Answer: C & E
Explanation:
It is possible to apply COUNT() and MIN() functions on the column with DATE data type.
Incorrect Answers
A: Function SUM() cannot be used with DATE data type column.
B: Function AVG() cannot be used with DATE data type column.
D: Function AVG() cannot be used with DATE data type column. And function AVG() just has one parameter X, not two. It averages all X column values returned by the SELECT statement.
F: There is no MAXIMUM() function in Oracle, only MAX() function exists.
Question: 43. (C)
The EMPLOYEE tables has these columns:
You want to display the name and annual salary multiplied by the commission_pct for all employees. For records that have a NULL commission_pct, a zero must be displayed against the calculated column. Which SQL statement displays the desired results?
A. SELECT last_name, (salary * 12) * commission_pct
FROM EMPLOYEES;
B. SELECT last_name, (salary * 12) * IFNULL(commission_pct,
0)
FROM EMPLOYEES;
C. SELECT last_name, (salary * 12) * NVL2(commission_pct, 0)
FROM EMPLOYEES;
D. SELECT last_name, (salary * 12) * NVL(commission_pct, 0)
FROM EMPLOYEES;
Answer: D
Explanation:
This SELECT statement provides correct usage of NVL function to calculate columns for all employees. Oracle give you possibility to substitute a value in place of NULL. The basic syntax for NVL() is NVL(column_name, value_if_null). Notice that the column specified in NVL() contains an actual value. That value is what Oracle returns; when the column is NULL, the special string isreturned. The value specified to be returned if the column value is NULL must be the same datatype as the column specified.
Incorrect Answers
A: This SELECT statement will return NULL value for rows with NULL COMMISION_PCT
column.
B: There is no IFNULL() function in Oracle.
C: The NVL2() function requires 3 parameters, not 2. Function NVL2(expr1, expr2, expr3) returns expr2 if expr1 is not NULL. If expr1 is NULL, it returns expr3.
Question: 44. (C)
You would like to display the system date in the format "Monday, 01 June, 2001".
Which SELECT statement should you use?
A. SELECT TO_DATE(SYSDATE, 'FMDAY, DD Month, YYYY')
FROM dual;
B. SELECT TO_CHAR(SYSDATE, 'FMDD, DY Month, 'YYY')
FROM dual;
C. SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY')
FROM dual;
D. SELECT TO_CHAR(SYSDATE, 'FMDY, DDD Month, YYYY')
FROM dual;
E. SELECT TO_DATE(SYSDATE, 'FMDY, DDD Month, YYYY')
FROM dual;
Answer: C
Explanation:
This answer is correct: "Day" shows the day spelled out, "DD" shows the two-digit date, "Month" provides the month spelled out, "YYYY" shows the four -digit year. "FMDay" is special format mask to suppresses the extra spaces between the name of the day and the number of the date.
Incorrect Answers
A: This statement will return an error because of inappropriate usage of the TO_DATE() function.
B: Incorrect format mask "DY" is used to show the number of the day.
D: Incorrect format mask "DY" is used to show the name of the day and format mask "DDD" is used to show the number of the day.
E: Incorrect format mask DY is used to show the name of the day and format mask "DDD" is used to show the number of the day. Also this statement will return an error because of inappropriate usage of the TO_DATE() function.
Question: 45. (C)
Evaluate the SQL statement:
SELECT ROUND(TRUNC(MOD(1600,10),-1),2)
FROM dual;
What will be displayed?
A. 0
B. 1
C. 0.00
D. An error statement
Answer: A
Explanation:
Result will be 0. MOD(x,y) function calculates the modulus of x, defined in long division as the integer remainder when x is divided by y until no further whole number can be produced. TRUNC() function truncates x to the decimal precision of y. ROUND(x,y) rounds x to the decimal precision of y.
Incorrect Answers
B: Result will be 0, not 1.
C: Result will be 0, not 0.00 because MOD(1600,10) return 0 and all other functions (TRUNC and ROUND) return 0 also.
D: There is no error in this statement.
Question: 46. (C)
Examine the description of the MARKS table:
SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects.
Examine this SELECT statement based on the MARKS table:
SELECT subj1+subj2 total_marks, std_id
FROM marks
WHERE subj1 > AVG(subj1) AND subj2 > AVG(subj2)
ORDER BY total marks;
What is the result of the SELECT statement?
A. The statement executes successfully and returns the student ID and sum of all marks for each student who obtained more than the average mark in each subject.
B. The statement returns an error at the SELECT clause.
C. The statement returns an error at the WHERE clause.
D. The statement returns an error at the ORDER BY clause.
Answer: C
Explanation:
The statement returns an error at the WHERE clause because group function AVG() cannot be used in the WHERE clause. Group functions can be used in SELECT clause and GROUP BY clause. They allow you to perform data operations on several values in a column of data as though the column were one collective group of data.
Incorrect Answers
A: The statement does not execute successfully because an error will be generated.
B: The statement returns an error at the WHERE, not at the SELECT clause.
D: The statement returns an error at the WHERE, not at the ORDER BY clause.
Question: 47. (C)
Which three SELECT statements displays 2000 in the format $2,000.00? (Choose three)
A. SELECT TO_CHAR (2000, '$#,###.##)
FROM dual;
B. SELECT TO_CHAR (2000, '$0,000.00)
FROM dual;
C. SELECT TO_CHAR (2000, '$9,999.00)
FROM dual;
D. SELECT TO_CHAR (2000, '$9,999.99)
FROM dual;
E. SELECT TO_CHAR (2000, '$2,000.00)
FROM dual;
F. SELECT TO_CHAR (2000, '$N,NNN.NN)
FROM dual;
Answer: B, C & D
Explanation:
Only queries in answers B, C and D will show result as in the format $2,000.00.
Incorrect Answers
A: Oracle error "ORA-01481: invalid number format model" will be generated.
E: Oracle error "ORA-01481: invalid number format model" will be generated.
F: Oracle error "ORA-01481: invalid number format model" will be generated.
Question: 48. (C)
Examine the description of the EMPLOYEES table:
Which statement shows the department ID, minimum salary, and maximum salary paid in that department, only of the minimum salary is less then 5000 and the maximum salary is more than 15000?
A. SELECT dept_id, MIN(salary(, MAX(salary)
FROM employees
WHERE MIN(salary) < 5000 AND MAX(salary) > 15000;
B. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
WHERE MIN(salary) < 5000 AND MAX(salary) > 15000
GROUP BY dept_id;
C. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
D. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY dept_id
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
E. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY dept_id, salary
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
Answer: D
Explanation:
This SELECT statement shows correct result.
Incorrect Answers
A: To provide correct data statement needs also GROUP BY clause.
B: This statement will not provide correct results.
C: HAVING clause can be used only in conjunction with GROUP BY clause.
E: You need only grouping by department, not by salary.
Question: 49. (C)
Which two are true about aggregate functions? (Choose two.)
A. You can use aggregate functions in any clause of a SELECT statement.
B. You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.
C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.
D. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.
E. You can use aggregate functions on a table, only by grouping the whole table as one single group.
F. You cannot group the rows of a table by more than one column while using aggregate
functions.
Answer: C, D
Explanation:
It is possible to mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns. Also it is acceptable to pass column names, expressions, constraints, or other functions as parameters to an aggregate function.
Incorrect Answers
A: You cannot use aggregate functions in any clause of a SELECT statement. For example, they cannot be used with a WHEN statement.
B: It is not possible to use aggregate functions in the WHERE clause of a SELECT statement.But they can be used with a HAVING clause used after the GROUP BY clause, for example.
E: You don't need to group the whole table as one single group.
F: It is possible to group more than one column while using aggregate functions.\
Question: 50. (C)
Which four statements correctly describe functions that are available in SQL? (Choose four)
A. INSTR returns the numeric position of a named character.
B. NVL2 returns the first non-null expression in the expression list.
C. TRUNCATE rounds the column, expression, or value to n decimal places.
D. DECODE translates an expression after comparing it to each search value.
E. TRIM trims the heading of trailing characters (or both) from a character string.
F. NVL compares two expressions and returns null if they are equal, or the first expression of they are not equal.
G. NULLIF compares two expressions and returns null if they are equal, or the first expression if they are not equal.
Answer: A, D, E, G
Explanation:
INSTR returns the numeric position of a named character. DECODE translates an expression after comparing it to each search value. TRIM trims the heading of trailing characters (or both) from a character string. NULLIF compares twp expressions and returns null if they are equal, or the first expression if they are not equal.
Incorrect Answers
B: This statement is not correct. The following is the Syntax for NVL2 function:
NVL2(expr1, expr2, expr3). If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3.
C: Command TRUNCATE is used to remove all row data from the table, while leaving the
definition of the table intact, including the definition of constraints and any associated database objects as indexes, constraints, and triggers on the table.
F: NVL returns second parameter value if first one is NULL.
Management has asked you to calculate the value 12*salary* comossion_pct for all the
employees in the EMP table. The EMP table contains these columns:
Which statement ensures that a value is displayed in the calculated columns for all employees?
A. SELECT last_name, 12*salary*commison_pct
FROM emp;
B. SELECT last_name, 12*salary* (commission_pct,0)
FROM emp;
C. SELECT last_name, 12*salary*(nvl(commission_pct,0))
FROM emp;
D. SELECT last_name, 12*salary*(decode(commission_pct,0))
FROM emp;
Answer: C
Explanation:
This SELECT statement provides correct usage of NVL function to calculate columns for all employees. Oracle give you possibility to substitute a value in place of NULL. The basic syntax for NVL() is NVL(column_name, value_if_null). Notice that the column specified in NVL() contains an actual value. That value is what Oracle returns; when the column is NULL, the special string is returned. The value specified to be returned if the column value is NULL must be the same
datatype as the column specified.
Incorrect Answers
A: This SELECT statement will return NULL value for rows with NULL COMMISION_PCT
column.
B: It is incorrect syntax in this query: NVL function needs to be used for correct result.
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 cannot have only two parameters.
Question: 42. (C)
Examine the description of the STUDENTS table:
Which two aggregate functions are valid on the START_DATE column? (Choose two)
A. SUM(start_date)
B. AVG(start_date)
C. COUNT(start_date)
D. AVG(start_date, end_date)
E. MIN(start_date)
F. MAXIMUM(start_date)
Answer: C & E
Explanation:
It is possible to apply COUNT() and MIN() functions on the column with DATE data type.
Incorrect Answers
A: Function SUM() cannot be used with DATE data type column.
B: Function AVG() cannot be used with DATE data type column.
D: Function AVG() cannot be used with DATE data type column. And function AVG() just has one parameter X, not two. It averages all X column values returned by the SELECT statement.
F: There is no MAXIMUM() function in Oracle, only MAX() function exists.
Question: 43. (C)
The EMPLOYEE tables has these columns:
You want to display the name and annual salary multiplied by the commission_pct for all employees. For records that have a NULL commission_pct, a zero must be displayed against the calculated column. Which SQL statement displays the desired results?
A. SELECT last_name, (salary * 12) * commission_pct
FROM EMPLOYEES;
B. SELECT last_name, (salary * 12) * IFNULL(commission_pct,
0)
FROM EMPLOYEES;
C. SELECT last_name, (salary * 12) * NVL2(commission_pct, 0)
FROM EMPLOYEES;
D. SELECT last_name, (salary * 12) * NVL(commission_pct, 0)
FROM EMPLOYEES;
Answer: D
Explanation:
This SELECT statement provides correct usage of NVL function to calculate columns for all employees. Oracle give you possibility to substitute a value in place of NULL. The basic syntax for NVL() is NVL(column_name, value_if_null). Notice that the column specified in NVL() contains an actual value. That value is what Oracle returns; when the column is NULL, the special string isreturned. The value specified to be returned if the column value is NULL must be the same datatype as the column specified.
Incorrect Answers
A: This SELECT statement will return NULL value for rows with NULL COMMISION_PCT
column.
B: There is no IFNULL() function in Oracle.
C: The NVL2() function requires 3 parameters, not 2. Function NVL2(expr1, expr2, expr3) returns expr2 if expr1 is not NULL. If expr1 is NULL, it returns expr3.
Question: 44. (C)
You would like to display the system date in the format "Monday, 01 June, 2001".
Which SELECT statement should you use?
A. SELECT TO_DATE(SYSDATE, 'FMDAY, DD Month, YYYY')
FROM dual;
B. SELECT TO_CHAR(SYSDATE, 'FMDD, DY Month, 'YYY')
FROM dual;
C. SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY')
FROM dual;
D. SELECT TO_CHAR(SYSDATE, 'FMDY, DDD Month, YYYY')
FROM dual;
E. SELECT TO_DATE(SYSDATE, 'FMDY, DDD Month, YYYY')
FROM dual;
Answer: C
Explanation:
This answer is correct: "Day" shows the day spelled out, "DD" shows the two-digit date, "Month" provides the month spelled out, "YYYY" shows the four -digit year. "FMDay" is special format mask to suppresses the extra spaces between the name of the day and the number of the date.
Incorrect Answers
A: This statement will return an error because of inappropriate usage of the TO_DATE() function.
B: Incorrect format mask "DY" is used to show the number of the day.
D: Incorrect format mask "DY" is used to show the name of the day and format mask "DDD" is used to show the number of the day.
E: Incorrect format mask DY is used to show the name of the day and format mask "DDD" is used to show the number of the day. Also this statement will return an error because of inappropriate usage of the TO_DATE() function.
Question: 45. (C)
Evaluate the SQL statement:
SELECT ROUND(TRUNC(MOD(1600,10),-1),2)
FROM dual;
What will be displayed?
A. 0
B. 1
C. 0.00
D. An error statement
Answer: A
Explanation:
Result will be 0. MOD(x,y) function calculates the modulus of x, defined in long division as the integer remainder when x is divided by y until no further whole number can be produced. TRUNC() function truncates x to the decimal precision of y. ROUND(x,y) rounds x to the decimal precision of y.
Incorrect Answers
B: Result will be 0, not 1.
C: Result will be 0, not 0.00 because MOD(1600,10) return 0 and all other functions (TRUNC and ROUND) return 0 also.
D: There is no error in this statement.
Question: 46. (C)
Examine the description of the MARKS table:
SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects.
Examine this SELECT statement based on the MARKS table:
SELECT subj1+subj2 total_marks, std_id
FROM marks
WHERE subj1 > AVG(subj1) AND subj2 > AVG(subj2)
ORDER BY total marks;
What is the result of the SELECT statement?
A. The statement executes successfully and returns the student ID and sum of all marks for each student who obtained more than the average mark in each subject.
B. The statement returns an error at the SELECT clause.
C. The statement returns an error at the WHERE clause.
D. The statement returns an error at the ORDER BY clause.
Answer: C
Explanation:
The statement returns an error at the WHERE clause because group function AVG() cannot be used in the WHERE clause. Group functions can be used in SELECT clause and GROUP BY clause. They allow you to perform data operations on several values in a column of data as though the column were one collective group of data.
Incorrect Answers
A: The statement does not execute successfully because an error will be generated.
B: The statement returns an error at the WHERE, not at the SELECT clause.
D: The statement returns an error at the WHERE, not at the ORDER BY clause.
Question: 47. (C)
Which three SELECT statements displays 2000 in the format $2,000.00? (Choose three)
A. SELECT TO_CHAR (2000, '$#,###.##)
FROM dual;
B. SELECT TO_CHAR (2000, '$0,000.00)
FROM dual;
C. SELECT TO_CHAR (2000, '$9,999.00)
FROM dual;
D. SELECT TO_CHAR (2000, '$9,999.99)
FROM dual;
E. SELECT TO_CHAR (2000, '$2,000.00)
FROM dual;
F. SELECT TO_CHAR (2000, '$N,NNN.NN)
FROM dual;
Answer: B, C & D
Explanation:
Only queries in answers B, C and D will show result as in the format $2,000.00.
Incorrect Answers
A: Oracle error "ORA-01481: invalid number format model" will be generated.
E: Oracle error "ORA-01481: invalid number format model" will be generated.
F: Oracle error "ORA-01481: invalid number format model" will be generated.
Question: 48. (C)
Examine the description of the EMPLOYEES table:
Which statement shows the department ID, minimum salary, and maximum salary paid in that department, only of the minimum salary is less then 5000 and the maximum salary is more than 15000?
A. SELECT dept_id, MIN(salary(, MAX(salary)
FROM employees
WHERE MIN(salary) < 5000 AND MAX(salary) > 15000;
B. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
WHERE MIN(salary) < 5000 AND MAX(salary) > 15000
GROUP BY dept_id;
C. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
D. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY dept_id
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
E. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY dept_id, salary
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
Answer: D
Explanation:
This SELECT statement shows correct result.
Incorrect Answers
A: To provide correct data statement needs also GROUP BY clause.
B: This statement will not provide correct results.
C: HAVING clause can be used only in conjunction with GROUP BY clause.
E: You need only grouping by department, not by salary.
Question: 49. (C)
Which two are true about aggregate functions? (Choose two.)
A. You can use aggregate functions in any clause of a SELECT statement.
B. You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.
C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.
D. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.
E. You can use aggregate functions on a table, only by grouping the whole table as one single group.
F. You cannot group the rows of a table by more than one column while using aggregate
functions.
Answer: C, D
Explanation:
It is possible to mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns. Also it is acceptable to pass column names, expressions, constraints, or other functions as parameters to an aggregate function.
Incorrect Answers
A: You cannot use aggregate functions in any clause of a SELECT statement. For example, they cannot be used with a WHEN statement.
B: It is not possible to use aggregate functions in the WHERE clause of a SELECT statement.But they can be used with a HAVING clause used after the GROUP BY clause, for example.
E: You don't need to group the whole table as one single group.
F: It is possible to group more than one column while using aggregate functions.\
Question: 50. (C)
Which four statements correctly describe functions that are available in SQL? (Choose four)
A. INSTR returns the numeric position of a named character.
B. NVL2 returns the first non-null expression in the expression list.
C. TRUNCATE rounds the column, expression, or value to n decimal places.
D. DECODE translates an expression after comparing it to each search value.
E. TRIM trims the heading of trailing characters (or both) from a character string.
F. NVL compares two expressions and returns null if they are equal, or the first expression of they are not equal.
G. NULLIF compares two expressions and returns null if they are equal, or the first expression if they are not equal.
Answer: A, D, E, G
Explanation:
INSTR returns the numeric position of a named character. DECODE translates an expression after comparing it to each search value. TRIM trims the heading of trailing characters (or both) from a character string. NULLIF compares twp expressions and returns null if they are equal, or the first expression if they are not equal.
Incorrect Answers
B: This statement is not correct. The following is the Syntax for NVL2 function:
NVL2(expr1, expr2, expr3). If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3.
C: Command TRUNCATE is used to remove all row data from the table, while leaving the
definition of the table intact, including the definition of constraints and any associated database objects as indexes, constraints, and triggers on the table.
F: NVL returns second parameter value if first one is NULL.
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 < b.avgcomm;
Which is a result of this query?
A. The AVG function's DISTINCT keyword must be used in the inner query or the statement will fail when executed.
B. The employee information displayed will be for employees that have a commission that is less than the average commission of all employees who have the same job.
C. The employee information displayed will be displayed in numeric order by the JOB_ID and in alphabetical order by the LAST_NAME where the JOB_IDs are the same.
D. A self join CANNOT be used in an outer query when the inner query is an inline view. The self join must be placed in the inner query for the statement to execute successfully.
Answer: B
Explanation:
For the above query the information of only those employee will be displayed that have a commission that is less than the average commission of all employees who have the same job.
Question: 57. (C)
Examine the structures of the EMPLOYEE and CURR_ORDER tables.
To keep your top sales representatives motivated, your company plans to increase the bonuses of employees. You need to create a SELECT statement that returns the name, bonus, and maximum order amount associated with each employee for all employees whose bonus is less than 8 percent of their maximum order amount.
Which SELECT statement should you use?
A. SELECT e.last_name, e.first_name, e.commission, o.maxamt
FROM employee e, (SELECT employee_id, MAX(order_amt) MAXAMT
FROM curr_order
GROUP BY employee_id) o
WHERE e.employee_id = o.employee_id
AND e.commission < .08 * o.maxamt;
B. SELECT e.last_name, e.first_name, e.commission, MAX(o.order_amount) MAXAMT FROM employee e, curr_order o WHERE e.employee_id = o.employee_id AND e.commission < .08 * o.maxamt GROUP BY e.last_name, e.first_name, e.commission;
C. SELECT e.last_name, e.first_name, e.commission, MAX(o.order_amt) FROM employee e, curr_order WHERE e.employee_id = o.employee_id AND e.commission < .08 * (SELECT employee_id, MAX(order_amt) MAXAMT FROM curr_order GROUP BY employee_id);
D. SELECT last_name, first_name, commission, max_amt FROM employee, (SELECT employee_id, MAX(order_amt) MAXAMT FROM curr_order GROUP BY employee_id) WHERE employee.employee_id = curr_order.employee_id AND commission < .08 * maxamt;
Answer: A
Explanation:
Ans A contain the complete statement that will give you all the desire result needed from the query.
Question: 58. (C)
The PRODUCT table contains these columns: PRODUCT_ID NUMBER(9) PRODUCT_NAME VARCHAR2(25) COST NUMBER(5,2) LIST_PRICE NUMBER(5,2) SUPPLIER_ID NUMBER(9) You need to display product names, costs, supplier ids, and average list prices for all the products that cost more than the average cost of products provided by the same supplier. Which SELECT statement will achieve these results?
A. SELECT product_name, cost, supplier_id, AVG(list_price) FROM product p, product a WHERE p.supplier_id = a.supplier_id GROUP BY product_name, cost, supplier_id;
B. 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.cost > 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.
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 < b.avgcomm;
Which is a result of this query?
A. The AVG function's DISTINCT keyword must be used in the inner query or the statement will fail when executed.
B. The employee information displayed will be for employees that have a commission that is less than the average commission of all employees who have the same job.
C. The employee information displayed will be displayed in numeric order by the JOB_ID and in alphabetical order by the LAST_NAME where the JOB_IDs are the same.
D. A self join CANNOT be used in an outer query when the inner query is an inline view. The self join must be placed in the inner query for the statement to execute successfully.
Answer: B
Explanation:
For the above query the information of only those employee will be displayed that have a commission that is less than the average commission of all employees who have the same job.
Question: 57. (C)
Examine the structures of the EMPLOYEE and CURR_ORDER tables.
To keep your top sales representatives motivated, your company plans to increase the bonuses of employees. You need to create a SELECT statement that returns the name, bonus, and maximum order amount associated with each employee for all employees whose bonus is less than 8 percent of their maximum order amount.
Which SELECT statement should you use?
A. SELECT e.last_name, e.first_name, e.commission, o.maxamt
FROM employee e, (SELECT employee_id, MAX(order_amt) MAXAMT
FROM curr_order
GROUP BY employee_id) o
WHERE e.employee_id = o.employee_id
AND e.commission < .08 * o.maxamt;
B. SELECT e.last_name, e.first_name, e.commission, MAX(o.order_amount) MAXAMT FROM employee e, curr_order o WHERE e.employee_id = o.employee_id AND e.commission < .08 * o.maxamt GROUP BY e.last_name, e.first_name, e.commission;
C. SELECT e.last_name, e.first_name, e.commission, MAX(o.order_amt) FROM employee e, curr_order WHERE e.employee_id = o.employee_id AND e.commission < .08 * (SELECT employee_id, MAX(order_amt) MAXAMT FROM curr_order GROUP BY employee_id);
D. SELECT last_name, first_name, commission, max_amt FROM employee, (SELECT employee_id, MAX(order_amt) MAXAMT FROM curr_order GROUP BY employee_id) WHERE employee.employee_id = curr_order.employee_id AND commission < .08 * maxamt;
Answer: A
Explanation:
Ans A contain the complete statement that will give you all the desire result needed from the query.
Question: 58. (C)
The PRODUCT table contains these columns: PRODUCT_ID NUMBER(9) PRODUCT_NAME VARCHAR2(25) COST NUMBER(5,2) LIST_PRICE NUMBER(5,2) SUPPLIER_ID NUMBER(9) You need to display product names, costs, supplier ids, and average list prices for all the products that cost more than the average cost of products provided by the same supplier. Which SELECT statement will achieve these results?
A. SELECT product_name, cost, supplier_id, AVG(list_price) FROM product p, product a WHERE p.supplier_id = a.supplier_id GROUP BY product_name, cost, supplier_id;
B. 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.cost > 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.
Friday, November 14, 2008
Part - 7 Oracle Interview
Question: 61. (C)
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?
A. SELECT employee_id, salary, tax_percent
FROM employees e, tax t
WHERE e.salary BETWEEN t.min_salary AND t.max_salary;
B. SELECT employee_id, salary, tax_percent
FROM employees e, tax t.
WHERE e.salary > t.min_salary, tax_percent
C. 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
D. You cannot find the information because there is no common column between the two tables.
Answer: A
Explanation:
You can find the percentage tax applicable for each employee by using SQL statement in answer A.
Incorrect Answers
B: Syntax "WHERE e.salary > t.min_salary, tax_percent" is incorrect.
C: Functions, like MIN() and MAX(), cannot be used in the WHERE clause.
D: The SQL statement from the answer A will provide requested information.
Question: 62. (C)
Examine the data in the WORKORDER table.
The WORKORDER table contains these columns:
WO_ID NUMBER PK
CUST_ID NUMBER
REQUIRED_DT DATE
COMPL_DT DATE
AMOUNT NUMBER(7,2)
Which statement regarding the use of aggregate functions on the WORKORDER table is true?
A. Using the SUM aggregate function with the AMOUNT column is allowed in any portion of a SELECT statement.
B. Using the AVG aggregate function with any column in the table is allowed.
C. Using the SUM aggregate function on the AMOUNT column will result in erroneous results because the column contains null values.
D. Grouping on the REQUIRED_DT and COMPL_DT columns is NOT allowed.
E. Using the AVG aggregate function on the AMOUNT column ignores null values.
F. Using the MIN aggregate function on the COMPL_DT column will return a null value.
Answer: E
Explanation:
All group functions except COUNT ignore null values. Using AVG aggregate function on the AMOUNT column ignores null values.
Question: 63.
The INVENTORY table contains these columns:
ID_NUMBER NUMBER PK
CATEGORY VARCHAR2(10)
LOCATION NUMBER
DESCRIPTION VARCHAR2(30)
PRICE NUMBER(7,2)
QUANTITY NUMBER
You want to return the total of the extended amounts for each item category and location, including only those inventory items that have a price greater than $100.00. The extended amount of each item equals the quantity multiplied by the price. Which SQL statement will return the desired result?
A. SELECT category, SUM(price * quantity) TOTAL, location
FROM inventory
WHERE price > 100.00
GROUP BY category;
B. SELECT category, location, SUM(price)
FROM inventory
WHERE price > 100.00
GROUP BY category, location;
C. SELECT category, SUM(price * quantity) TOTAL, location
FROM inventory
WHERE price > 100.00;
D. SELECT category, SUM(price * quantity) TOTAL, location
FROM inventory
WHERE price > 100.00
GROUP BY category, location;
Answer: D
Explanation:
You can find total extended amount by using SUM group by function and it can be grouped by catergory and then location with GROUP BY clause.
Question: 64. (C)
The EVENT table contains these columns:
EVENT_ID NUMBER
EVENT_NAME VARCHAR2(30)
EVENT_DESC VARCHAR2(100)
EVENT_TYPE NUMBER
LOCATION_ID NUMBER
You have been asked to provide a report of the number of different event types at each location.
Which SELECT statement will produce the desired result?
A. SELECT UNIQUE(location_id), COUNT(event_type)
FROM event
GROUP BY location_id;
B. SELECT COUNT(*), DISTINCT(location_id)
FROM event;
C. SELECT DISTINCT (event_type)
FROM event
GROUP BY location_id;
D. SELECT location_id, COUNT(DISTINCT event_type)
FROM event
GROUP BY location_id;
E. SELECT location_id, MAX(DISTINCT event_type)
FROM event
GROUP BY location_id;
Answer: D
Explanation:
Unique number of event types at each location can be find by using COUNT to count the number of events and then GROUP BY clause can be used with locatio_id column to make it location wise.
Question: 65. (C)
Which two statements about the evaluation of clauses in a SELECT statement are true? (Choose two.)
A. The Oracle Server will evaluate a HAVING clause before a WHERE clause.
B. The Oracle Server will evaluate a WHERE clause before a GROUP BY clause.
C. The Oracle Server will evaluate a GROUP BY clause before a HAVING clause.
D. The Oracle Server will evaluate an ORDER BY clause before a WHERE clause.
E. The Oracle Server will evaluate an ORDER BY clause before a HAVING clause.
Answer: B & C
Explanation:
In SELECT statement WHERE clause comes after the FROM clause and before GROUP By
clause while HAVING clause use to restrict the data group wise and it comes after the GROUP BY clause and ORDER BY clause comes after at end of SELECT statement.
Question: 66. (C)
Examine the structures of the EMPLOYEE and DEPARTMENT tables:
EMPLOYEE
------------------
EMP_ID NUMBER NOT NULL PK
NAME VARCHAR(30) NOT NULL
FNAME VARCHAR(25) NOT NULL
DEPT_NO NUMBER
TITLE VARCHAR2(25)
DEPARTMENT
------------------------
DEPT_ID NUMBER NOT NULL PK
DEPT_NAME VARCHAR2(25)
You need to produce a list of departments, including the department names, that have more than three administrative assistants. Which SELECT statement will produce the desired result?
A. SELECT dept_name
FROM employee JOIN department
ON employee.dept_id = department.dept_id
WHERE UPPER(title) = 'ADMINISTRATIVE ASSISTANT'
GROUP BY dept_name
HAVING emp_id > 3;
B. SELECT dept_name
FROM employee GROUP
BY dept_no
HAVING LOWER(title) = 'administrative assistant' AND COUNT(*) > 3;
C. SELECT dept_name
FROM employee NATURAL JOIN department
WHERE LOWER(title) = 'administrative assistant'
GROUP BY dept_name
HAVING COUNT(emp_id) > 3;
D. SELECT dept_name
FROM employee e JOIN department d
ON (e.dept_no = d.dept_id)
WHERE LOWER(title) = 'administrative assistant'
AND COUNT(*) > 3;
E. SELECT d.dept_name
FROM employee e JOIN department d
ON (e.dept_no = d.dept_id)
WHERE LOWER(title) = 'administrative assistant'
GROUP BY dept_name
HAVING COUNT(emp_id) > 3;
F. SELECT d.dept_name
FROM e.employee JOIN d.department
ON (e.dept_no = d.dept_id)
WHERE LOWER(title) = 'administrative assistant'
GROUP BY dept_name
HAVING COUNT(emp_id) > 3;
Answer: E
Explanation:
To obtain the above desired result LOWER function is used to used with the TITILE column value to find it whether values stored is in the lower case or in upper case it will convert it in to lower and compare it. COUNT function will count the whole values and restricted the result to three values by using HAVING clause.
Question: 67. (C)
Which two tasks can you perform by using the TO_CHAR function? (Choose two)
A. Convert 10 to 'TEN'
B. Convert '10' to 10
C. Convert '10' to '10'
D. Convert 'TEN' to 10
E. Convert a date to a character expression
F. Convert a character expression to a date
Answer: C & E
Explanation:
TO_CHAR(x) function is used to convert the value x to a character or converts a date to a character string using formatting conventions.
Incorrect Answers
A: This function cannot convert the number to the string representing number spelled out.
B: TO_CHAR() function cannot convert the character value to a number. TO_NUMBER() function does this.
D: This function is not able to convert the string representing number spelled out to the number itself.
F: TO_CHAR() function cannot convert a character expression to a date. TO_DATE() function does this.
Question: 68. (C)
Examine the data in the LINE_ITEM table.
You query the database and return the value 23. Which script did you use?
A. SELECT SUBSTR(product_id, 3)
FROM line_item
WHERE line_item_id = 2
AND order_id = 1494;
B. SELECT SUBSTR(product_id, 3, -2)
FROM line_item
WHERE line_item_id = 2
AND order_id = 1494;
C. SELECT SUBSTR(product_id, -3, 2)
FROM line_item
WHERE line_item_id = 2
AND order_id = 1494;
D. SELECT SUBSTR(product_id, 3, 2)
FROM line_item
WHERE line_item_id = 2
AND order_id = 1494;
Answer: D
Explanation:
A SUBSTR function can be used statement to extract a string of determined length.
Question: 69. (C)
Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?
A. SELECT ename, salary*12 Annual Salary
FROM employees;
B. SELECT ename, salary*12 Annual Salary
FROM employees;
C. SELECT ename, salary*12 AS Annual Salary
FROM employees;
D. SELECT ename, salary*12 AS INITCAP(ANNUAL SALARY)
FROM employees
Answer: B
Explanation:
This SQL statement provides correct syntax to generate the alias Annual Salary for the calculated column SALARY*12.
Incorrect Answers
A: Alias can be surrounded with double quotation marks, not with single. Oracle error will be generated in this case.
C: Alias needs to be surrounded with double quotation marks, it cannot just follow by the AS keyword.
D: You cannot use any function as alias, so this SQL statement will fail.
Question: 70. (C)
Examine the structure of the EMPLOYEES table:
What is the correct syntax for an inline view?
A. SELECT a.last_name, a.salary, a.department_id, b.maxsal
FROM employees a,
(SELECT department_id, max(salary)maxsal
FROM employees
GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary < b.maxsal;
B. SELECT a.last name, a.salary, a.department_id
FROM employees a
WHERE a.department_id IN
(SELECT department_id
FROM employees b
GROUP BY department_id having salary =
(SELECT max(salary) from employees))
C. SELECT a.last_name, a.salary, a.department_id
FROM employees a
WHERE a.salary =
(SELECT max(salary)
FROM employees b
WHERE a.department_id = b.department_id);
D. SELECT a.last_name, a.salary, a.department_id
FROM employees a
WHERE (a.department_id, a.salary) IN
(SELECT department_id, a.salary) IN
(SELECT department_id max(salary)
FROM employees b
GROUP BY department_id
ORDER BY department_id);
Answer: A
Explanation:
This SQL statement shows correct syntax to build inline views. You must enclose the query text for the inline view in parentheses and also give a label for the inline view so that columns in it can be referenced later. In answer A inline view is marked as B.
Incorrect Answers
B: This SQL statement will fail because it is not correct syntax for inline views. There is no a label for this inline view also.
C: This SQL statement will fail because it is not correct syntax for inline views. There is no a label for this inline view also.
D: This SQL statement will fail because it is not correct syntax for inline views. There is no a label for this inline view also.
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?
A. SELECT employee_id, salary, tax_percent
FROM employees e, tax t
WHERE e.salary BETWEEN t.min_salary AND t.max_salary;
B. SELECT employee_id, salary, tax_percent
FROM employees e, tax t.
WHERE e.salary > t.min_salary, tax_percent
C. 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
D. You cannot find the information because there is no common column between the two tables.
Answer: A
Explanation:
You can find the percentage tax applicable for each employee by using SQL statement in answer A.
Incorrect Answers
B: Syntax "WHERE e.salary > t.min_salary, tax_percent" is incorrect.
C: Functions, like MIN() and MAX(), cannot be used in the WHERE clause.
D: The SQL statement from the answer A will provide requested information.
Question: 62. (C)
Examine the data in the WORKORDER table.
The WORKORDER table contains these columns:
WO_ID NUMBER PK
CUST_ID NUMBER
REQUIRED_DT DATE
COMPL_DT DATE
AMOUNT NUMBER(7,2)
Which statement regarding the use of aggregate functions on the WORKORDER table is true?
A. Using the SUM aggregate function with the AMOUNT column is allowed in any portion of a SELECT statement.
B. Using the AVG aggregate function with any column in the table is allowed.
C. Using the SUM aggregate function on the AMOUNT column will result in erroneous results because the column contains null values.
D. Grouping on the REQUIRED_DT and COMPL_DT columns is NOT allowed.
E. Using the AVG aggregate function on the AMOUNT column ignores null values.
F. Using the MIN aggregate function on the COMPL_DT column will return a null value.
Answer: E
Explanation:
All group functions except COUNT ignore null values. Using AVG aggregate function on the AMOUNT column ignores null values.
Question: 63.
The INVENTORY table contains these columns:
ID_NUMBER NUMBER PK
CATEGORY VARCHAR2(10)
LOCATION NUMBER
DESCRIPTION VARCHAR2(30)
PRICE NUMBER(7,2)
QUANTITY NUMBER
You want to return the total of the extended amounts for each item category and location, including only those inventory items that have a price greater than $100.00. The extended amount of each item equals the quantity multiplied by the price. Which SQL statement will return the desired result?
A. SELECT category, SUM(price * quantity) TOTAL, location
FROM inventory
WHERE price > 100.00
GROUP BY category;
B. SELECT category, location, SUM(price)
FROM inventory
WHERE price > 100.00
GROUP BY category, location;
C. SELECT category, SUM(price * quantity) TOTAL, location
FROM inventory
WHERE price > 100.00;
D. SELECT category, SUM(price * quantity) TOTAL, location
FROM inventory
WHERE price > 100.00
GROUP BY category, location;
Answer: D
Explanation:
You can find total extended amount by using SUM group by function and it can be grouped by catergory and then location with GROUP BY clause.
Question: 64. (C)
The EVENT table contains these columns:
EVENT_ID NUMBER
EVENT_NAME VARCHAR2(30)
EVENT_DESC VARCHAR2(100)
EVENT_TYPE NUMBER
LOCATION_ID NUMBER
You have been asked to provide a report of the number of different event types at each location.
Which SELECT statement will produce the desired result?
A. SELECT UNIQUE(location_id), COUNT(event_type)
FROM event
GROUP BY location_id;
B. SELECT COUNT(*), DISTINCT(location_id)
FROM event;
C. SELECT DISTINCT (event_type)
FROM event
GROUP BY location_id;
D. SELECT location_id, COUNT(DISTINCT event_type)
FROM event
GROUP BY location_id;
E. SELECT location_id, MAX(DISTINCT event_type)
FROM event
GROUP BY location_id;
Answer: D
Explanation:
Unique number of event types at each location can be find by using COUNT to count the number of events and then GROUP BY clause can be used with locatio_id column to make it location wise.
Question: 65. (C)
Which two statements about the evaluation of clauses in a SELECT statement are true? (Choose two.)
A. The Oracle Server will evaluate a HAVING clause before a WHERE clause.
B. The Oracle Server will evaluate a WHERE clause before a GROUP BY clause.
C. The Oracle Server will evaluate a GROUP BY clause before a HAVING clause.
D. The Oracle Server will evaluate an ORDER BY clause before a WHERE clause.
E. The Oracle Server will evaluate an ORDER BY clause before a HAVING clause.
Answer: B & C
Explanation:
In SELECT statement WHERE clause comes after the FROM clause and before GROUP By
clause while HAVING clause use to restrict the data group wise and it comes after the GROUP BY clause and ORDER BY clause comes after at end of SELECT statement.
Question: 66. (C)
Examine the structures of the EMPLOYEE and DEPARTMENT tables:
EMPLOYEE
------------------
EMP_ID NUMBER NOT NULL PK
NAME VARCHAR(30) NOT NULL
FNAME VARCHAR(25) NOT NULL
DEPT_NO NUMBER
TITLE VARCHAR2(25)
DEPARTMENT
------------------------
DEPT_ID NUMBER NOT NULL PK
DEPT_NAME VARCHAR2(25)
You need to produce a list of departments, including the department names, that have more than three administrative assistants. Which SELECT statement will produce the desired result?
A. SELECT dept_name
FROM employee JOIN department
ON employee.dept_id = department.dept_id
WHERE UPPER(title) = 'ADMINISTRATIVE ASSISTANT'
GROUP BY dept_name
HAVING emp_id > 3;
B. SELECT dept_name
FROM employee GROUP
BY dept_no
HAVING LOWER(title) = 'administrative assistant' AND COUNT(*) > 3;
C. SELECT dept_name
FROM employee NATURAL JOIN department
WHERE LOWER(title) = 'administrative assistant'
GROUP BY dept_name
HAVING COUNT(emp_id) > 3;
D. SELECT dept_name
FROM employee e JOIN department d
ON (e.dept_no = d.dept_id)
WHERE LOWER(title) = 'administrative assistant'
AND COUNT(*) > 3;
E. SELECT d.dept_name
FROM employee e JOIN department d
ON (e.dept_no = d.dept_id)
WHERE LOWER(title) = 'administrative assistant'
GROUP BY dept_name
HAVING COUNT(emp_id) > 3;
F. SELECT d.dept_name
FROM e.employee JOIN d.department
ON (e.dept_no = d.dept_id)
WHERE LOWER(title) = 'administrative assistant'
GROUP BY dept_name
HAVING COUNT(emp_id) > 3;
Answer: E
Explanation:
To obtain the above desired result LOWER function is used to used with the TITILE column value to find it whether values stored is in the lower case or in upper case it will convert it in to lower and compare it. COUNT function will count the whole values and restricted the result to three values by using HAVING clause.
Question: 67. (C)
Which two tasks can you perform by using the TO_CHAR function? (Choose two)
A. Convert 10 to 'TEN'
B. Convert '10' to 10
C. Convert '10' to '10'
D. Convert 'TEN' to 10
E. Convert a date to a character expression
F. Convert a character expression to a date
Answer: C & E
Explanation:
TO_CHAR(x) function is used to convert the value x to a character or converts a date to a character string using formatting conventions.
Incorrect Answers
A: This function cannot convert the number to the string representing number spelled out.
B: TO_CHAR() function cannot convert the character value to a number. TO_NUMBER() function does this.
D: This function is not able to convert the string representing number spelled out to the number itself.
F: TO_CHAR() function cannot convert a character expression to a date. TO_DATE() function does this.
Question: 68. (C)
Examine the data in the LINE_ITEM table.
You query the database and return the value 23. Which script did you use?
A. SELECT SUBSTR(product_id, 3)
FROM line_item
WHERE line_item_id = 2
AND order_id = 1494;
B. SELECT SUBSTR(product_id, 3, -2)
FROM line_item
WHERE line_item_id = 2
AND order_id = 1494;
C. SELECT SUBSTR(product_id, -3, 2)
FROM line_item
WHERE line_item_id = 2
AND order_id = 1494;
D. SELECT SUBSTR(product_id, 3, 2)
FROM line_item
WHERE line_item_id = 2
AND order_id = 1494;
Answer: D
Explanation:
A SUBSTR function can be used statement to extract a string of determined length.
Question: 69. (C)
Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?
A. SELECT ename, salary*12 Annual Salary
FROM employees;
B. SELECT ename, salary*12 Annual Salary
FROM employees;
C. SELECT ename, salary*12 AS Annual Salary
FROM employees;
D. SELECT ename, salary*12 AS INITCAP(ANNUAL SALARY)
FROM employees
Answer: B
Explanation:
This SQL statement provides correct syntax to generate the alias Annual Salary for the calculated column SALARY*12.
Incorrect Answers
A: Alias can be surrounded with double quotation marks, not with single. Oracle error will be generated in this case.
C: Alias needs to be surrounded with double quotation marks, it cannot just follow by the AS keyword.
D: You cannot use any function as alias, so this SQL statement will fail.
Question: 70. (C)
Examine the structure of the EMPLOYEES table:
What is the correct syntax for an inline view?
A. SELECT a.last_name, a.salary, a.department_id, b.maxsal
FROM employees a,
(SELECT department_id, max(salary)maxsal
FROM employees
GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary < b.maxsal;
B. SELECT a.last name, a.salary, a.department_id
FROM employees a
WHERE a.department_id IN
(SELECT department_id
FROM employees b
GROUP BY department_id having salary =
(SELECT max(salary) from employees))
C. SELECT a.last_name, a.salary, a.department_id
FROM employees a
WHERE a.salary =
(SELECT max(salary)
FROM employees b
WHERE a.department_id = b.department_id);
D. SELECT a.last_name, a.salary, a.department_id
FROM employees a
WHERE (a.department_id, a.salary) IN
(SELECT department_id, a.salary) IN
(SELECT department_id max(salary)
FROM employees b
GROUP BY department_id
ORDER BY department_id);
Answer: A
Explanation:
This SQL statement shows correct syntax to build inline views. You must enclose the query text for the inline view in parentheses and also give a label for the inline view so that columns in it can be referenced later. In answer A inline view is marked as B.
Incorrect Answers
B: This SQL statement will fail because it is not correct syntax for inline views. There is no a label for this inline view also.
C: This SQL statement will fail because it is not correct syntax for inline views. There is no a label for this inline view also.
D: This SQL statement will fail because it is not correct syntax for inline views. There is no a label for this inline view also.
Subscribe to:
Posts (Atom)
