- Home
- Database
- Oracle 12 C
- OCA Oracle 12 C SQl Fundamentals(1Z0-061)
56.
The EMPLOYEE table has the following data:
What will be the value in the first row of the result set when the following query is executed?
SELECT hire_date FROM employee ORDER BY salary, emp_name;
What will be the value in the first row of the result set when the following query is executed?
SELECT hire_date FROM employee ORDER BY salary, emp_name;
- A.02-APR-91
- B.17-DEC-90
- C.28-SEP-91
- D.The query is invalid, because you cannot have a column in the ORDER BY clause that is not part of the SELECT clause.
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| The default sorting order for a numeric column is ascending. The columns are sorted first by salary and then by name, so the row with the lowest salary is displayed first. It is perfectly valid to use a column in the ORDER BY clause that is not part of the SELECT clause. |
57.
Which SQL statement will query the EMPLOYEES table for FIRST_NAME , LAST_NAME , and
SALARY of all employees in DEPARTMENT_ID 40 in the alphabetical order of last name?
- A.SELECT first_name last_name salary FROM employees ORDER BY last_name WHERE department_id = 40;
- B.SELECT first_name, last_name, salaryFROM employees ORDER BY last_name ASC WHERE department_id = 40;
- C.SELECT first_name last_name salary FROM employees WHERE department_id = 40 ORDER BY last_name ASC;
- D.SELECT first_name, last_name, salary FROM employees WHERE department_id = 40 ORDER BY last_name;
- E.SELECT first_name, last_name, salary FROM TABLE employees WHERE department_id IS 40 ORDER BY last_name ASC;
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| In the SELECT clause, the column names should be separated by commas. An alias name may be provided for each column with a space or by using the keyword AS . The FROM clause should appear after the SELECT clause. The WHERE clause appears after the FROM clause. The ORDER BY clause comes after the WHERE clause. |
58.
Column alias names cannot be used in which clause?
- A.SELECT clause
- B.WHERE clause
- C.ORDER BY clause
- D.None of the above
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| Column alias names cannot be used in the WHERE clause of the SQL statement. In the ORDER BY clause, you can use the column name or alias name, or you can indi- cate the column by its position in the SELECT clause. |
59.
What is wrong with the following statements submitted in SQL*Plus?
DEFINE V_DEPTNO = 20
SELECT LAST_NAME, SALARY
FROM
EMPLOYEES
WHERE DEPARTMENT_ID = V_DeptNo;
DEFINE V_DEPTNO = 20
SELECT LAST_NAME, SALARY
FROM
EMPLOYEES
WHERE DEPARTMENT_ID = V_DeptNo;
- A.Nothing is wrong. The query lists the employee name and salary of the employees who belong to department 20.
- B.The DEFINE statement declaration is wrong.
- C.The substitution variable is not preceded with the & character.
- D.The substitution variable in the WHERE clause should be V_DEPTNO instead of V_DeptNo .
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| The query will return an error, because the substitution variable is used without an ampersand ( & ) character. In this query, Oracle treats V_DEPTNO as another column name from the table and returns an error. Substitution variables are not case sensitive. |
60.
Which two statements regarding substitution variables are true?
- A.&&variable is defined by SQL*Plus, and its value will be available for the duration of the session.
- B.&&variable is defined by SQL*Plus, and its value will be available for the duration of the session.
- C.&n (where n is any integer) variables are defined by SQL*Plus when values are passed in as arguments to the script, and their values will be available for the dura- tion of the session.
- D.&&variable is defined by SQL*Plus, and its value will be available only for every reference to that variable in the current SQL.
- Answer & Explanation
- Report
Answer : [B, C]
Explanation :
Explanation :
| When a variable is preceded by double ampersands, SQL*Plus defines that variable. Similarly, when you pass values to a script using START script_name arguments , SQL*Plus defines those variables. Once a variable is defined, its value will be available for the duration of the session or until you use UNDEFINE variable . |
|
|
||||||||||||||||||||||||||||
