Oracle Mock Test

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

The WHERE clause is used in a SELECT statement to specify a condition that must be met for the rows to be included in the result set. In this case, the condition is that the salary of the employees must be greater than 5000. Therefore, the WHERE clause is used to limit the display to those employees whose salary is greater than 5000.

Explanation

The WHERE clause is used in a SELECT statement to specify a condition that must be met for the rows to be included in the result set. In this case, the condition is that the salary of the employees must be greater than 5000. Therefore, the WHERE clause is used to limit the display to those employees whose salary is greater than 5000.

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;

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

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

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.

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.

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.

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;

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.

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.

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_idFROM employeesWHERE job_id LIKE '%SA\_%' ESCAPE '\';

SELECT employee_id, last_name, job_idFROM employeesWHERE job_id LIKE '%SA_';

SELECT employee_id, last_name, job_idFROM employeesWHERE job_id LIKE '%SA_' ESCAPE "\";

SELECT employee_id, last_name, job_idFROM employeesWHERE job_id = '%SA_';

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.

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.

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.

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.

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.

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.

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.

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.

Submit

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;

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.

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.

Submit

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.

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.

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.

Submit

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

INSERT

UPDATE

SELECT

DESCRIBE

DELETE

RENAME

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.

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.

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;

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.

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.

Submit

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.

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.

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.

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.

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.

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.

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;

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,".

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,".

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, locationFROM inventoryWHERE price > 100.00GROUP BY category;

SELECT category, location, SUM(price)FROM inventoryWHERE price > 100.00GROUP BY category, location;

SELECT category, SUM(price * quantity) TOTAL, locationFROM inventoryWHERE price > 100.00;

SELECT category, SUM(price * quantity) TOTAL, locationFROM inventoryWHERE price > 100.00GROUP BY category, location;

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.

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.

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

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.

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.

Submit

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

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

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

Submit

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

CONCAT

ROUND

TRUNC

RPAD

INSTR

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.

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.

Submit

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

ALTER TABLE customersADD CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;

ALTER TABLE customersMODIFY CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;

ALTER TABLE customersMODIFY customer_name CONSTRAINT cust_name_nn NOT NULL;

ALTER TABLE customersMODIFY customer_name CONSTRAINT cust_name_nn IS NOT NULL;

ALTER TABLE customersMODIFY name CONSTRAINT cust_name_nn NOT NULL;

ALTER TABLE customersADD CONSTRAINT cust_name_nn CHECK customer_name NOT NULL;

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.

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.

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 marksWHERE subj1 >= AVG(subj1);

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.

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.

Submit