Oracle_test6

Reviewed by Editorial Team
The ProProfs editorial team is comprised of experienced subject matter experts. They've collectively created over 10,000 quizzes and lessons, serving over 100 million users. Our team includes in-house content moderators and subject matter experts, as well as a global network of rigorously trained contributors. All adhere to our comprehensive editorial guidelines, ensuring the delivery of high-quality content.
Learn about Our Editorial Process
| By Sudha_test
S
Sudha_test
Community Contributor
Quizzes Created: 12 | Total Attempts: 38,036
| Attempts: 1,543
SettingsSettings
Please wait...
  • 1/20 Questions

    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.
Please wait...
About This Quiz

Oracle_Test6 assesses understanding of SQL queries in Oracle, focusing on data manipulation and query outcomes. Key skills include interpreting SQL syntax and functionality, as well as precision in data handling. Ideal for learners enhancing database expertise.

Oracle Quizzes & Trivia

Quiz Preview

  • 2. 

    Which clause in a query limits the rows selected?

    • ORDER BY

    • WHERE

    • SELECT

    • FROM

    Correct Answer
    A. WHERE
    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.

    Rate this question:

  • 3. 

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

    • %

    • &

    • @

    • *

    Correct Answer
    A. *
    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:

  • 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

    Correct Answer
    A. Employee Salary
    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.

    Rate this question:

  • 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

    • .>=

    Correct Answer
    A. .IN
    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.

    Rate this question:

  • 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

    Correct Answer
    A. 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:

  • 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              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        

    • 02-APR-91

    • 17-DEC-90

    • 28-SEP-91

    • The query is invalid, because you cannot have a column in the ORDER BY that is not part of the SELECT clause.

    Correct Answer
    A. 17-DEC-90
    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.

    Rate this question:

  • 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

    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:

  • 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

    • MILLER

    • 7934

    • 7876

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

    Correct Answer
    A. 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.

    Rate this question:

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

    Correct Answer
    A. 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:

  • 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

    Correct Answer
    A. 4
    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.

    Rate this question:

  • 12. 

    Column alias names cannot be used in which clause?

    • SELECT

    • WHERE

    • ORDER BY

    • None of the above

    Correct Answer
    A. WHERE
    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.

    Rate this question:

  • 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

    Correct Answer
    A. \
    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:

  • 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

    Correct Answer
    A. 34567.23
    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.

    Rate this question:

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

    Correct Answer
    A. 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:

  • 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?

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

    • . 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;

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

    • 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
    A. 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:

  • 17. 

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

    • 8

    • 9

    • 19

    • 6

    Correct Answer
    A. 9
    Explanation
    The default display length of the DATE datatype column is 9.

    Rate this question:

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

    Correct Answer
    A. 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:

  • 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

    Correct Answer
    A. 4
    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.

    Rate this question:

  • 20. 

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

    • 10

    • Ename

    • Employee Name:

    • ||

    Correct Answer(s)
    A. 10
    A. 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:

Quiz Review Timeline (Updated): Mar 20, 2023 +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

  • Current Version
  • Mar 20, 2023
    Quiz Edited by
    ProProfs Editorial Team
  • Jan 05, 2010
    Quiz Created by
    Sudha_test
Back to Top Back to top
Advertisement
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.