Oracle_Test6

1. The following are clauses of the SELECT statement: In which order should they appear in a query? 1) WHERE 2) FROM 3) ORDER BY

. 1, 3, 2

2, 1, 3

2, 3, 1

The order of these clauses does not matter.

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.

Explanation

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.

2. Which clause in a query limits the rows selected?

ORDER BY

WHERE

SELECT

FROM

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.

Explanation

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.

3. Which special character is used to query all the columns from the table without listing each column by name?

%

&

@

*

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.

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.

4. You issue the following query: SELECT salary "Employee Salary" FROM employees;
How will the column heading appear in the result?

EMPLOYEE SALARY

EMPLOYEE_SALARY

Employee Salary

Employee_salary

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.

Explanation

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.

5. 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?

.IN

BETWEEN .. AND

LIKE

.>=

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.

Explanation

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.

6. 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

No difference, because the answer will always be NULL

No difference, because the result will be the same.

The result will be higher

The result will be lower

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.

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.

7. 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              1250
JONES          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

02-APR-91

17-DEC-90

28-SEP-91

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.

Explanation

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.

8. 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?

Statements 1 and 2 will produce the same result.

Statement 1 will execute; statement 2 will return an error

Statement 2 will execute; statement 1 will return an error.

Statements 1 and 2 will execute but produce different results

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.

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.

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        10

When 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

MILLER

7934

7876

No rows will be returned ename cannot be used in the ORDER BY clause

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.

Explanation

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.

10. 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?

SELECT first_name last_name salary FROM employees ORDER BY last_name WHERE department_id = 40;

SELECT first_name, last_name, salary FROM employees ORDER BY last_name ASC WHERE department_id = 40;

SELECT first_name last_name salary FROM employees WHERE department_id = 40 ORDER BY last_name ASC;

SELECT first_name, last_name, salary FROM employees WHERE department_id = 40 ORDER BY last_name;

SELECT first_name, last_name, salary FROM TABLE employees WHERE department_id IS 40 ORDER BY last_name ASC;

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.

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.

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

1

5

4

No error; the statement will finish successfully

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.

Explanation

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.

12. Column alias names cannot be used in which clause?

SELECT

WHERE

ORDER BY

None of the above

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.

Explanation

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.

13. When doing pattern matching using the LIKE operator, which character is used as the default escape character by Oracle?

|

/

\

There is no default escape character in Oracle9i

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

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

14. When you try to save 34567.2255 into a column defined as NUMBER(7,2) what value is actually saved?

34567.00

34567.23

34567.22

3456.22

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.

Explanation

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.

15. What will happen if you query the EMP table shown in question 2 with the following? SELECT empno, DISTINCT ename, salary FROM emp;

EMPNO, unique values of ENAME and then SALARY are displayed.

EMPNO, unique values of the two columns, ENAME and SALARY, are displayed

DISTINCT is not a valid keyword in SQL

No values will be displayed because the statement will return an error.

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.

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.

16. COUNTRY_NAME and REGION_ID are valid column names in the COUNTRIES table. Which one of the following statements will execute without an error?

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.

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.

17. What is the default display length of the DATE datatype column?

8

9

19

6

The default display length of the DATE datatype column is 9.

Explanation

The default display length of the DATE datatype column is 9.

18. Which statement searches for PRODUCT_ID values that begin with DI_ from the ORDERS table?

SELECT * FROM ORDERS WHERE PRODUCT_ID = 'DI%';

SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI_' ESCAPE '\';

SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI\_%' ESCAPE '\';

SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI\_' ESCAPE '\';

SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI_%' ESCAPE '\';

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.

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.

19. 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'

4

2

A character column cannot be used in the BETWEEN operator

3

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.

Explanation

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.

20. In the following SELECT statement, which component is a literal? SELECT 'Employee Name: ' || ename FROM emp where deptno = 10;

10

Ename

Employee Name:

||

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.

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.

Submit

Oracle_test6