Oracle Mock Test

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 Unrealvicky
U
Unrealvicky
Community Contributor
Quizzes Created: 6 | Total Attempts: 10,552
| Attempts: 1,144
SettingsSettings
Please wait...
  • 1/20 Questions

    Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater then 5000?

    • ORDER BY SALARY > 5000
    • GROUP BY SALARY > 5000
    • HAVING SALARY > 5000
    • WHERE SALARY > 5000
Please wait...
About This Quiz

The 'Oracle Mock Test' assesses knowledge on Oracle SQL and database management, focusing on SQL statements, table constraints, and query functions. It's designed for learners to validate their understanding and readiness for working with Oracle databases.

Oracle Quizzes & Trivia

Quiz Preview

  • 2. 

    Which SELECT statement will the result ‘elloworld’ from the string ‘HelloWorld’?

    • SELECT SUBSTR( ‘HelloWorld’,1) FROM dual;

    • SELECT INITCAP(TRIM (‘HelloWorld’, 1,1)) FROM dual;

    • SELECT LOWER(SUBSTR(‘HellowWorld’, 1, 1) FROM dual;

    • SELECT LOWER(SUBSTR(‘HelloWorld’, 2, 1) FROM dual;

    • SELECT LOWER(TRIM (‘H’ FROM ‘HelloWorld’)) FROM dual;

    Correct Answer
    A. SELECT LOWER(TRIM (‘H’ FROM ‘HelloWorld’)) FROM dual;
    Explanation
    The correct answer is SELECT LOWER(TRIM ('H' FROM 'HelloWorld')) FROM dual. This query will remove the 'H' character from the string 'HelloWorld' using the TRIM function, and then convert the remaining string to lowercase using the LOWER function. The result will be 'elloworld'.

    Rate this question:

  • 3. 

    Evaluate this SQL statement: SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct) + (s.sales_amount * (.35 * e.bonus)) AS CALC_VALUE FROM employees e, sales s WHERE e.employee_id = s.emp_id; What will happen if you remove all the parentheses from the calculation?

    • The value displayed in the CALC_VALUE column will be lower.

    • The value displayed in the CALC_VALUE column will be higher.

    • There will be no difference in the value displayed in the CALC_VALUE column.

    • An error will be reported.

    Correct Answer
    A. There will be no difference in the value displayed in the CALC_VALUE column.
    Explanation
    Removing the parentheses from the calculation will not change the order of operations in this case because the multiplication and addition operators have the same precedence. Therefore, the result of the calculation will remain the same, and there will be no difference in the value displayed in the CALC_VALUE column.

    Rate this question:

  • 4. 

    Which SELECT statement should you use to extract the year from the system date and display it in the format "1998"?

    • SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual;

    • SELECT TO_DATE(SYSDATE,'yyyy') FROM dual;

    • SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY') FROM dual;

    • SELECT DECODE(SUBSTR(SYSDATE, 8), 'year') FROM dual;

    • SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy') FROM dual;

    Correct Answer
    A. SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual;
    Explanation
    The correct answer is "SELECT TO_CHAR(SYSDATE,'yyyy')
    FROM dual;". This statement uses the TO_CHAR function to convert the system date (SYSDATE) into a character string in the format 'yyyy', which represents the year. The FROM dual clause is used to ensure that the query returns a result.

    Rate this question:

  • 5. 

    The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(4) LAST_NAME VARCHAR2 (25) JOB_ID VARCHAR2(10) You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL statement do you use?

    • SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_%' ESCAPE '\';

    • SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_';

    • SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_' ESCAPE "\";

    • SELECT employee_id, last_name, job_id FROM employees WHERE job_id = '%SA_';

    Correct Answer
    A. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_%' ESCAPE '\';
    Explanation
    The correct answer is the first option: SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_%' ESCAPE '\'. This statement uses the LIKE operator with a wildcard '%' to search for strings that contain 'SA_' in the JOB_ID column. The backslash '\' is used as an escape character to treat the underscore '_' as a literal character instead of a wildcard.

    Rate this question:

  • 6. 

    What is necessary for your query on an existing view to execute successfully?

    • The underlying tables must have data.

    • You need SELECT privileges on the view.

    • The underlying tables must be in the same schema.

    • You need SELECT privileges only on the underlying tables.

    Correct Answer
    A. You need SELECT privileges on the view.
    Explanation
    To execute a query on an existing view successfully, it is necessary to have SELECT privileges on the view. This means that the user executing the query must have the necessary permissions to retrieve data from the view. Having SELECT privileges on the view allows the user to access the data stored in the view without needing to directly access the underlying tables.

    Rate this question:

  • 7. 

    Which two statements about the evaluation of clauses in a SELECT statement are true? (Choose two.)

    • The Oracle Server will evaluate a HAVING clause before a WHERE clause.

    • The Oracle Server will evaluate a WHERE clause before a GROUP BY clause.

    • The Oracle Server will evaluate a GROUP BY clause before a HAVING clause.

    • The Oracle Server will evaluate an ORDER BY clause before a WHERE clause.

    • The Oracle Server will evaluate an ORDER BY clause before a HAVING clause.

    Correct Answer(s)
    A. The Oracle Server will evaluate a WHERE clause before a GROUP BY clause.
    A. The Oracle Server will evaluate a GROUP BY clause before a HAVING clause.
    Explanation
    The Oracle Server evaluates the WHERE clause before the GROUP BY clause because it needs to filter the rows before grouping them. This allows the server to reduce the number of rows it needs to group, improving performance. Similarly, the Oracle Server evaluates the GROUP BY clause before the HAVING clause because it needs to group the rows before applying any conditions specified in the HAVING clause. This ensures that the conditions are applied to the grouped data.

    Rate this question:

  • 8. 

    Which three SELECT statements display 2000 in the format "$2,000.00"? (Choose three.)

    • SELECT TO_CHAR(2000, '$#,###.##') FROM dual;

    • SELECT TO_CHAR(2000, '$0,000.00') FROM dual;

    • SELECT TO_CHAR(2000, '$9,999.00') FROM dual;

    • SELECT TO_CHAR(2000, '$9,999.99') FROM dual;

    • SELECT TO_CHAR(2000, '$2,000.00') FROM dual;

    • SELECT TO_CHAR(2000, '$N,NNN.NN') FROM dual;

    Correct Answer(s)
    A. SELECT TO_CHAR(2000, '$0,000.00') FROM dual;
    A. SELECT TO_CHAR(2000, '$9,999.00') FROM dual;
    A. SELECT TO_CHAR(2000, '$9,999.99') FROM dual;
    Explanation
    The three SELECT statements that display 2000 in the format "$2,000.00" are: SELECT TO_CHAR(2000, '$0,000.00') FROM dual;, SELECT TO_CHAR(2000, '$9,999.00') FROM dual;, and SELECT TO_CHAR(2000, '$9,999.99') FROM dual;. These statements use the TO_CHAR function to convert the number 2000 into a string with the specified format. The format string includes the dollar sign, comma as a thousand separator, and two decimal places.

    Rate this question:

  • 9. 

    Which two statements are true regarding the default behavior of the ORDER BY clause? (Choose two.)

    • Null values are left out of the sort.

    • Character values are displayed from Z to A.

    • Date values are displayed with the earliest value first.

    • Null values are displayed last for descending sequences.

    • Numeric values are displayed with the lowest values first.

    Correct Answer(s)
    A. Date values are displayed with the earliest value first.
    A. Numeric values are displayed with the lowest values first.
    Explanation
    The first statement is true because by default, null values are considered to have the lowest possible value and are displayed first in ascending order. The third statement is also true because by default, date values are sorted in ascending order, so the earliest date value will be displayed first.

    Rate this question:

  • 10. 

    Which are iSQL*Plus commands? (Choose all that apply.)

    • INSERT

    • UPDATE

    • SELECT

    • DESCRIBE

    • DELETE

    • RENAME

    Correct Answer
    A. DESCRIBE
    Explanation
    The iSQL*Plus commands are a set of commands that can be used in the iSQL*Plus interface. These commands include INSERT, UPDATE, SELECT, DESCRIBE, DELETE, and RENAME. The DESCRIBE command is used to retrieve information about the structure of a table or view in the database. It provides details about the columns, data types, and constraints of the specified table or view.

    Rate this question:

  • 11. 

    Which three SELECT statements display 2000 in the format "$2,000.00"? (Choose three.)

    • SELECT TO_CHAR(2000, '$#,###.##') FROM dual;

    • SELECT TO_CHAR(2000, '$0,000.00') FROM dual;

    • SELECT TO_CHAR(2000, '$9,999.00') FROM dual;

    • SELECT TO_CHAR(2000, '$9,999.99') FROM dual;

    • SELECT TO_CHAR(2000, '$2,000.00') FROM dual;

    • SELECT TO_CHAR(2000, '$N,NNN.NN') FROM dual;

    Correct Answer(s)
    A. SELECT TO_CHAR(2000, '$0,000.00') FROM dual;
    A. SELECT TO_CHAR(2000, '$9,999.00') FROM dual;
    A. SELECT TO_CHAR(2000, '$9,999.99') FROM dual;
    Explanation
    The three SELECT statements that display 2000 in the format "$2,000.00" are:
    SELECT TO_CHAR(2000, '$0,000.00') FROM dual;
    SELECT TO_CHAR(2000, '$9,999.00') FROM dual;
    SELECT TO_CHAR(2000, '$9,999.99') FROM dual;
    These statements use the TO_CHAR function to format the number 2000 as a string with a dollar sign, comma separator, and two decimal places.

    Rate this question:

  • 12. 

    Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE You issue these statements: CREATE table new_emp ( employee_id NUMBER, name VARCHAR2(30)); INSERT INTO new_emp SELECT employee_id , last_name from employees; Savepoint s1; UPDATE new_emp set name = UPPER(name); Savepoint s2; Delete from new_emp; Rollback to s2; Delete from new_emp where employee_id =180; UPDATE new_emp set name = 'James'; Rollback to s2; UPDATE new_emp set name = 'James' WHERE employee_id =180; Rollback; At the end of this transaction, what is true?

    • You have no rows in the table.

    • You have an employee with the name of James.

    • You cannot roll back to the same savepoint more than once.

    • Your last update fails to update any rows because employee ID 180 was already deleted.

    Correct Answer
    A. You have no rows in the table.
    Explanation
    At the end of this transaction, there are no rows in the table because the DELETE statement deletes all the rows in the new_emp table. The ROLLBACK statement rolls back the transaction to the savepoint s2, which means the previous DELETE statement is undone. However, the subsequent UPDATE statement also fails to update any rows because the employee with ID 180 was already deleted. Therefore, the final state of the table is that it is empty.

    Rate this question:

  • 13. 

    Examine this statement: SELECT student_id, gpa FROM student_grades WHERE gpa > &&value; You run the statement once, and when prompted you enter a value of 2.0. A report is produced. What happens when you run the statement a second time?

    • An error is returned.

    • You are prompted to enter a new value.

    • A report is produced that matches the first report produced.

    • You are asked whether you want a new value or if you want to run the report based on the previous value.

    Correct Answer
    A. A report is produced that matches the first report produced.
    Explanation
    When you run the statement a second time, a report is produced that matches the first report produced. This is because the query is filtering the student_grades table based on the condition "gpa > 2.0". Since the value entered is the same (2.0), the same set of records that satisfy the condition will be retrieved, resulting in the same report being produced.

    Rate this question:

  • 14. 

    The CUSTOMERS table has these columns: CUSTOMER_ID NUMBER(4) NOT NULL CUSTOMER_NAME VARCHAR2(100) NOT NULL CUSTOMER_ADDRESS VARCHAR2(150) CUSTOMER_pHONE VARCHAR2(20) You need to produce output that states "Dear Customer customer_name, ". The customer_name data values come from the CUSTOMER_NAME column in the CUSTOMERS table. Which statement produces this output?

    • SELECT dear customer, customer_name, FROM customers;

    • SELECT "Dear Customer", customer_name || ',' FROM customers;

    • SELECT 'Dear Customer ' || customer_name ',' FROM customers;

    • SELECT 'Dear Customer ' || customer_name || ',' FROM customers;

    • SELECT "Dear Customer " || customer_name || "," FROM customers;

    • SELECT 'Dear Customer ' || customer_name || ',' || FROM customers;

    Correct Answer
    A. SELECT 'Dear Customer ' || customer_name || ',' FROM customers;
    Explanation
    The correct answer is "SELECT 'Dear Customer ' || customer_name || ',' FROM customers;". This statement uses the concatenation operator (||) to combine the string 'Dear Customer ' with the values from the customer_name column in the customers table. The comma is also included in the concatenation to match the desired output format of "Dear Customer customer_name,".

    Rate this question:

  • 15. 

    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

    • SELECT category, SUM(price * quantity) TOTAL, location FROM inventory WHERE price > 100.00 GROUP BY category;

    • SELECT category, location, SUM(price) FROM inventory WHERE price > 100.00 GROUP BY category, location;

    • SELECT category, SUM(price * quantity) TOTAL, location FROM inventory WHERE price > 100.00;

    • SELECT category, SUM(price * quantity) TOTAL, location FROM inventory WHERE price > 100.00 GROUP BY category, location;

    Correct Answer
    A. SELECT category, SUM(price * quantity) TOTAL, location FROM inventory WHERE price > 100.00 GROUP BY category, location;
    Explanation
    The correct answer is the fourth option: SELECT category, SUM(price * quantity) TOTAL, location
    FROM inventory
    WHERE price > 100.00
    GROUP BY category, location.

    This query selects the category, location, and the sum of the extended amounts (price * quantity) for each item category and location. It also includes a condition to only include inventory items with a price greater than $100.00. The GROUP BY clause is used to group the results by category and location, allowing for the calculation of the total extended amount for each category and location.

    Rate this question:

  • 16. 

    Examine the SQL statement that creates ORDERS table: CREATE TABLE orders (SER_NO NUMBER UNIQUE, ORDER_ID NUMBER, ORDER_DATE DATE NOT NULL, STATUS VARCHAR2(10) CHECK (status IN ('CREDIT', 'CASH')), PROD_ID NUMBER REFERENCES PRODUCTS(PRODUCT_ID), ORD_TOTAL NUMBER, PRIMARY KEY (order_id, order_date)); For which columns would an index be automatically created when you execute the above SQL statement? (Choose two.)

    • SER_NO

    • ORDER_ID

    • STATUS

    • PROD_ID

    • ORD_TOTAL

    • Composite index on ORDER_ID and ORDER_DATE

    Correct Answer(s)
    A. SER_NO
    A. Composite index on ORDER_ID and ORDER_DATE
    Explanation
    An index would be automatically created for the SER_NO column because it is defined as UNIQUE. A composite index would be automatically created for the ORDER_ID and ORDER_DATE columns because they are defined as the PRIMARY KEY.

    Rate this question:

  • 17. 

    Which two tasks can you perform by using the TO_CHAR function? (Choose two)

    • Convert 10 to ‘TEN’

    • Convert ‘10’ to 10

    • Convert ‘10’ to ‘10’

    • Convert ‘TEN’ to 10

    • Convert a date to a character expression

    • Convert a character expression to a date

    Correct Answer(s)
    A. Convert ‘10’ to ‘10’
    A. Convert a date to a character expression
    Explanation
    The TO_CHAR function can be used to convert a character expression to a date and to convert a date to a character expression. It cannot be used to convert numbers to words or vice versa. Therefore, the correct answers are "Convert ‘10’ to ‘10’" and "Convert a date to a character expression".

    Rate this question:

  • 18. 

    Which three functions can be used to manipulate character, number, or date column values? (Choose three.)

    • CONCAT

    • ROUND

    • TRUNC

    • RPAD

    • INSTR

    Correct Answer(s)
    A. CONCAT
    A. RPAD
    A. INSTR
    Explanation
    The CONCAT function is used to concatenate or combine two or more character, number, or date column values. The RPAD function is used to pad a character, number, or date column value with a specific character or set of characters to a specified length. The INSTR function is used to find the position of a substring within a character, number, or date column value. Therefore, these three functions can be used to manipulate character, number, or date column values.

    Rate this question:

  • 19. 

    Which statement adds a constraint that ensures the CUSTOMER_NAME column of the CUSTOMERS table holds a value?

    • ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;

    • ALTER TABLE customers MODIFY CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;

    • ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL;

    • ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn IS NOT NULL;

    • ALTER TABLE customers MODIFY name CONSTRAINT cust_name_nn NOT NULL;

    • ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name NOT NULL;

    Correct Answer
    A. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL;
    Explanation
    The statement "ALTER TABLE customers
    MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL;" adds a constraint to the CUSTOMERS table that ensures the CUSTOMER_NAME column holds a value.

    Rate this question:

  • 20. 

    Examine the description of the MARKS table: STD_ID NUMBER(4) STUDENT_NAME VARCHAR2(30) SUBJ1 NUMBER(3) SUBJ2 NUMBER(3) SUBJ3 NUMBER(3) SUBJ1, SUBJ2, and SUBJ3 indicate the marks (grades) obtained by a student in the three subjects. Which two statements are valid? (Choose two.)

    • SELECT SUM(subj1, subj2, subj3) FROM marks;

    • SELECT SUM(subj1 + subj2 + subj3) FROM marks;

    • SELECT SUM(subj1), SUM(subj2), SUM(subj3) FROM marks;

    • SELECT MAX(subj1, subj2, subj3) FROM marks;

    • SELECT MINIMUM(subj1) FROM marks;

    • SELECT COUNT(std_id) FROM marks WHERE subj1 >= AVG(subj1);

    Correct Answer(s)
    A. SELECT SUM(subj1), SUM(subj2), SUM(subj3) FROM marks;
    A. SELECT MAX(subj1, subj2, subj3) FROM marks;
    Explanation
    The first valid statement is "SELECT SUM(subj1), SUM(subj2), SUM(subj3) FROM marks;". This statement calculates the sum of the marks obtained in each subject for all students in the table.

    The second valid statement is "SELECT MAX(subj1, subj2, subj3) FROM marks;". This statement calculates the maximum value among the marks obtained in the three subjects for all students in the table.

    Rate this question:

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

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

  • Current Version
  • Mar 18, 2023
    Quiz Edited by
    ProProfs Editorial Team
  • Mar 29, 2011
    Quiz Created by
    Unrealvicky
Back to Top Back to top
Advertisement
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.