Oracle_test6
This is a basic SQL test
- 1.
You issue the following query: SELECT salary "Employee Salary" FROM employees; How will the column heading appear in the result?
- A.
EMPLOYEE SALARY
- B.
EMPLOYEE_SALARY
- C.
Employee Salary
- D.
Employee_salary
Correct Answer
C. Employee SalaryExplanation
The column heading in the result will appear as "Employee Salary". This is because the query includes the alias "Employee Salary" after the SELECT statement, which renames the "salary" column as "Employee Salary" in the result.Rate this question:
-
- 2.
The EMP table is defined as follows: EMP Table Datatype Length Empno(NUMBER) 4 Ename(VARCHAR2) 30 Sal(NUMBER) 14,2 Bonus(NUMBER) 10,2 Deptno(NUMBER) 2 You perform the following two queries: SELECT empno enumber, ename FROM emp ORDER BY 1; SELECT empno, ename FROM emp ORDER BY empno ASC; Which of the following is true?
- A.
Statements 1 and 2 will produce the same result.
- B.
Statement 1 will execute; statement 2 will return an error
- C.
Statement 2 will execute; statement 1 will return an error.
- D.
Statements 1 and 2 will execute but produce different results
Correct Answer
A. Statements 1 and 2 will produce the same result.Explanation
The given answer is correct because both queries are selecting the same columns (empno and ename) from the emp table. The only difference is the order by clause. In statement 1, the order by clause is ordering the result by the first column (empno) in ascending order. In statement 2, the order by clause is explicitly ordering the result by empno in ascending order. Since both queries are selecting the same columns and ordering by the same column in the same order, they will produce the same result.Rate this question:
-
- 3.
You issue the following SELECT statement on the EMP table . SELECT (200+((salary*0.1)/2)) FROM emp;What will happen to the result if all of the parentheses are removed
- A.
No difference, because the answer will always be NULL
- B.
No difference, because the result will be the same.
- C.
The result will be higher
- D.
The result will be lower
Correct Answer
B. No difference, because the result will be the same.Explanation
The result will be the same if all of the parentheses are removed because the arithmetic operations will still be performed in the same order of precedence. The expression inside the parentheses is a combination of addition, multiplication, and division, and without the parentheses, the same operations will still be performed in the same order. Therefore, the overall result will remain unchanged.Rate this question:
-
- 4.
In the following SELECT statement, which component is a literal? SELECT 'Employee Name: ' || ename FROM emp where deptno = 10;
- A.
10
- B.
Ename
- C.
Employee Name:
- D.
||
Correct Answer(s)
A. 10
C. Employee Name:Explanation
In the given SELECT statement, the component 'Employee Name: ' is a literal. A literal is a fixed value that is explicitly specified in the statement and does not change during execution. In this case, 'Employee Name: ' is a string literal that will be concatenated with the value of the ename column for each row where deptno is equal to 10. The result will be a string that starts with 'Employee Name: ' followed by the name of the employee.Rate this question:
-
- 5.
When you try to save 34567.2255 into a column defined as NUMBER(7,2) what value is actually saved?
- A.
34567.00
- B.
34567.23
- C.
34567.22
- D.
3456.22
Correct Answer
B. 34567.23Explanation
The column is defined as NUMBER(7,2), which means it can store a maximum of 7 digits, with 2 decimal places. When trying to save the value 34567.2255, it will be rounded to 2 decimal places, resulting in 34567.23 being saved.Rate this question:
-
- 6.
What is the default display length of the DATE datatype column?
- A.
8
- B.
9
- C.
19
- D.
6
Correct Answer
B. 9Explanation
The default display length of the DATE datatype column is 9.Rate this question:
-
- 7.
What will happen if you query the EMP table shown in question 2 with the following? SELECT empno, DISTINCT ename, salary FROM emp;
- A.
EMPNO, unique values of ENAME and then SALARY are displayed.
- B.
EMPNO, unique values of the two columns, ENAME and SALARY, are displayed
- C.
DISTINCT is not a valid keyword in SQL
- D.
No values will be displayed because the statement will return an error.
Correct Answer
D. No values will be displayed because the statement will return an error.Explanation
The statement will return an error because "DISTINCT" is not a valid keyword in SQL when used with individual columns. The correct syntax to retrieve unique values for multiple columns would be to use the "GROUP BY" clause.Rate this question:
-
- 8.
Which clause in a query limits the rows selected?
- A.
ORDER BY
- B.
WHERE
- C.
SELECT
- D.
FROM
Correct Answer
B. WHEREExplanation
The WHERE clause in a query limits the rows selected based on specified conditions. It allows us to filter the data and retrieve only the rows that meet the specified criteria. By using conditions such as equal to, greater than, less than, etc., we can narrow down the result set and retrieve only the desired rows from the database.Rate this question:
-
- 9.
The following listing shows the records of the EMP table. EMPNO ENAME SALARY COMM DEPTNO --------- ---------- --------- --------- --------- 7369 SMITH 800 20 7499 ALLEN 1600 300 30 7521 WARD 1250 500 30 7566 JONES 2975 20 7654 MARTIN 1250 1400 30 7698 BLAKE 2850 30 7782 CLARK 2450 24500 10 7788 SCOTT 3000 20 7839 KING 5000 50000 10 7844 TURNER 1500 0 30 7876 ADAMS 1100 20 7900 JAMES 950 30 7902 FORD 3000 20 7934 MILLER 1300 13000 10When you issue the following query Which value will be displayed in the first row? SELECT empno FROM emp WHERE deptno = 10 ORDER BY ename DESC
- A.
MILLER
- B.
7934
- C.
7876
- D.
No rows will be returned ename cannot be used in the ORDER BY clause
Correct Answer
B. 7934Explanation
The query is selecting the "empno" from the "emp" table where the "deptno" is 10, and ordering the results by "ename" in descending order. Since the "ename" column is not included in the select statement, it does not affect the ordering of the results. Therefore, the first row will display the "empno" value of 7934.Rate this question:
-
- 10.
The following listing shows the records of the EMP table. EMPNO ENAME SALARY COMM DEPTNO --------- ---------- --------- --------- --------- 7369 SMITH 800 20 7499 ALLEN 1600 300 30 7521 WARD 1250 500 30 7566 JONES 2975 20 7654 MARTIN 1250 1400 30 7698 BLAKE 2850 30 7782 CLARK 2450 24500 10 7788 SCOTT 3000 20 7839 KING 5000 50000 10 7844 TURNER 1500 0 30 7876 ADAMS 1100 20 7900 JAMES 950 30 7902 FORD 3000 20 7934 MILLER 1300 13000 10 How many rows will the following query return? SELECT * FROM emp WHERE ename BETWEEN 'A' AND 'C'
- A.
4
- B.
2
- C.
A character column cannot be used in the BETWEEN operator
- D.
3
Correct Answer
A. 4Explanation
The query "SELECT * FROM emp WHERE ename BETWEEN 'A' AND 'C'" will return 4 rows. The condition "ename BETWEEN 'A' AND 'C'" will include all the records where the value of ename falls between 'A' and 'C' alphabetically. In the given listing, the names 'ALLEN', 'ADAMS', 'BLAKE', and 'CLARK' satisfy this condition, so the query will return these 4 rows.Rate this question:
-
- 11.
) Refer to the EMP table in question 2. When you issue the following query, which line has an error? 1)SELECT empno "Enumber", ename "EmpName" 2) FROM emp 3)WHERE deptno = 10 4) AND "Enumber" = 7782 5)ORDER BY "Enumber";
- A.
1
- B.
5
- C.
4
- D.
No error; the statement will finish successfully
Correct Answer
C. 4Explanation
The error in the query is on line 4. The column "Enumber" is enclosed in double quotes, indicating that it is a string literal instead of a column name. However, in the WHERE clause, it is being compared to a numeric value (7782). This type mismatch causes the error.Rate this question:
-
- 12.
You issue the following query: SELECT empno, ename FROM emp WHERE empno = 7782 OR empno = 7876; Which other operator can replace the OR condition in the WHERE clause?
- A.
.IN
- B.
BETWEEN .. AND
- C.
LIKE
- D.
- E.
.>=
Correct Answer
A. .INExplanation
The IN operator can replace the OR condition in the WHERE clause. It allows you to specify multiple values in a single condition, making the query more concise and readable. In this case, the query could be rewritten as: SELECT empno, ename FROM emp WHERE empno IN (7782, 7876). This would return the same result as the original query with the OR condition.Rate this question:
-
- 13.
The following are clauses of the SELECT statement: In which order should they appear in a query? 1) WHERE 2) FROM 3) ORDER BY
- A.
. 1, 3, 2
- B.
2, 1, 3
- C.
2, 3, 1
- D.
The order of these clauses does not matter.
Correct Answer
B. 2, 1, 3Explanation
The correct order of the clauses in a SELECT statement is 2, 1, 3. The FROM clause should appear first, as it specifies the table or tables from which the data is being retrieved. Then, the WHERE clause should come next, as it filters the data based on specified conditions. Finally, the ORDER BY clause should appear last, as it determines the order in which the results are presented. The order of these clauses is important as it affects the outcome of the query.Rate this question:
-
- 14.
Which statement searches for PRODUCT_ID values that begin with DI_ from the ORDERS table?
- A.
SELECT * FROM ORDERS WHERE PRODUCT_ID = 'DI%';
- B.
SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI_' ESCAPE '\';
- C.
SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI\_%' ESCAPE '\';
- D.
SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI\_' ESCAPE '\';
- E.
SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI_%' ESCAPE '\';
Correct Answer
C. SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI\_%' ESCAPE '\';Explanation
The correct answer is "SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI\_%' ESCAPE '\';". This statement uses the LIKE operator with the wildcard character "_" to search for PRODUCT_ID values that begin with "DI_" from the ORDERS table. The backslash ("\") is used as an escape character to treat the underscore as a literal character.Rate this question:
-
- 15.
COUNTRY_NAME and REGION_ID are valid column names in the COUNTRIES table. Which one of the following statements will execute without an error?
- A.
SELECT country_name, region_id, CASE region_id = 1 THEN 'Europe', region_id = 2 THEN 'America', region_id = 3 THEN 'Asia', ELSE 'Other' END Continent FROM countries;
- B.
. SELECT country_name, region_id, CASE (region_id WHEN 1 THEN 'Europe', WHEN 2 THEN 'America', WHEN 3 THEN 'Asia', ELSE 'Other') Continen FROM countries;
- C.
SELECT country_name, region_id CASE region_id WHEN 1 THEN 'Europe' WHEN 2 THEN 'America' WHEN 3 THEN 'Asia' ELSE 'Other' END Continent FROM countries;
- D.
SELECT country_name, region_id, CASE region_id WHEN 1 THEN 'Europe' WHEN 2 THEN 'America' WHEN 3 THEN 'Asia' ELSE 'Other' Continent FROM countries;
Correct Answer
C. SELECT country_name, region_id CASE region_id WHEN 1 THEN 'Europe' WHEN 2 THEN 'America' WHEN 3 THEN 'Asia' ELSE 'Other' END Continent FROM countries;Explanation
The given answer is correct because it uses the correct syntax for the CASE statement. The CASE statement is used to perform conditional logic in SQL queries. In this case, it checks the value of the region_id column and returns a corresponding continent based on the value. The syntax used in the given answer is CASE region_id WHEN 1 THEN 'Europe' WHEN 2 THEN 'America' WHEN 3 THEN 'Asia' ELSE 'Other' END Continent, which is the correct syntax for a simple CASE statement.Rate this question:
-
- 16.
Which special character is used to query all the columns from the table without listing each column by name?
- A.
%
- B.
&
- C.
@
- D.
*
Correct Answer
D. *Explanation
The asterisk (*) is used as a special character to query all the columns from a table without listing each column by name. It is commonly used in SQL queries to retrieve all the data from a table. By using the asterisk, the query will return all the columns in the table, making it easier and more efficient than listing each column individually.Rate this question:
-
- 17.
The EMPLOYEE table has the following data: EMP_NAME HIRE_DATE SALARY ---------- --------- ---------- SMITH 17-DEC-90 800 ALLEN 20-FEB-91 1600 WARD 22-FEB-91 1250JONES 02-APR-91 5975 WARDEN 28-SEP-91 1250 BLAKE 01-MAY-91 2850 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 that is not part of the SELECT clause.
Correct Answer
B. 17-DEC-90Explanation
The value in the first row of the result set will be "17-DEC-90". This is because the query is ordering the result set by salary and emp_name, but only selecting the hire_date column. The hire_date values are sorted in ascending order, so the earliest hire_date value, which is "17-DEC-90", will be in the first row of the result set.Rate this question:
-
- 18.
When doing pattern matching using the LIKE operator, which character is used as the default escape character by Oracle?
- A.
|
- B.
/
- C.
\
- D.
There is no default escape character in Oracle9i
Correct Answer
C. \Explanation
In Oracle, the backslash (\) character is used as the default escape character when doing pattern matching using the LIKE operator. This means that if you want to search for a literal backslash character, you need to escape it by using another backslash (\). For example, to search for the string "C:\Program Files", you would use the pattern 'C:\\\\Program Files'.Rate this question:
-
- 19.
Column alias names cannot be used in which clause?
- A.
SELECT
- B.
WHERE
- C.
ORDER BY
- D.
None of the above
Correct Answer
B. WHEREExplanation
Column alias names cannot be used in the WHERE clause. The WHERE clause is used to filter rows based on a condition. It is used to specify a search condition for the rows to be retrieved from the database. Column aliases are used to provide a temporary name for a column or expression in the SELECT statement. However, these aliases cannot be referenced in the WHERE clause because the WHERE clause is evaluated before the SELECT clause.Rate this question:
-
- 20.
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, salary FROM 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;
Correct Answer
D. SELECT first_name, last_name, salary FROM employees WHERE department_id = 40 ORDER BY last_name;Explanation
The correct answer is "SELECT first_name, last_name, salary FROM employees WHERE department_id = 40 ORDER BY last_name;". This statement selects the columns first_name, last_name, and salary from the employees table, filters the results to only include employees with a department_id of 40, and orders the results in ascending order based on the last_name column.Rate this question:
-
Back to top