Oracle Mock 3

Approved & Edited by ProProfs Editorial Team

The editorial team at ProProfs Quizzes consists of a select group of subject experts, trivia writers, and quiz masters who have authored over 10,000 quizzes taken by more than 100 million users. This team includes our in-house seasoned quiz moderators and subject matter experts. Our editorial experts, spread across the world, are rigorously trained using our comprehensive guidelines to ensure that you receive the highest quality quizzes.

Learn about Our Editorial Process

| By Unrealvicky

Unrealvicky

Community Contributor

Quizzes Created: 6 | Total Attempts: 10,405

Questions: 34 | Attempts: 539 | Updated: Mar 22, 2023

Questions

Settings

Feedback

During the Quiz End of Quiz

Play as

Quiz Flashcard

Create your own Quiz

Questions and Answers

1.

Which /SQL*Plus feature can be used to replace values in the WHERE clause?
- A.
  
  Substitution variables
- B.
  
  Replacement variables
- C.
  
  Prompt variables
- D.
  
  Instead-of variables
- E.
  
  This feature cannot be implemented through /SQL*Plus.
Correct Answer
A. Substitution variables

Explanation
Substitution variables in SQL*Plus can be used to replace values in the WHERE clause. Substitution variables are denoted by an ampersand (&) followed by a variable name. They prompt the user to enter a value, which is then substituted into the SQL statement. This allows for dynamic filtering of data based on user input.

Rate this question:
2.

You want to display the titles of books that meet these criteria: 1. Purchased before January 21, 2001 2. Price is less then $500 or greater than $900 You want to sort the results by their data of purchase, starting with the most recently bought book. Which statement should you use?
- A.
  
  SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < ’21-JAN-2001’ ORDER BY purchase_date;
- B.
  
  SELECT book_title FROM books WHERE price IN (500,900) AND purchase_date < ’21-JAN-2001’ ORDER BY purchase date ASC;
- C.
  
  SELECT book_title FROM books WHERE price < 500 or > 900 AND purchase_date < ’21-JAN-2001’ ORDER BY purchase date DESC;
- D.
  
  SELECT book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < ’21-JAN-2001’ ORDER BY purchase date DESC;
Correct Answer
D. SELECT book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < ’21-JAN-2001’ ORDER BY purchase date DESC;

Explanation
The correct answer is "SELECT book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < ’21-JAN-2001’ ORDER BY purchase date DESC;". This statement includes the conditions for the criteria specified in the question. It selects the book titles where the price is either less than $500 or greater than $900 and the purchase date is before January 21, 2001. The results are then sorted in descending order based on the purchase date, starting with the most recently bought book.

Rate this question:
3.

Which statement explicitly names a constraint?
- A.
  
  ALTER TABLE student_grades ADD FOREIGN KEY (student_id) REFERENCES students(student_id);
- B.
  
  ALTER TABLE student_grades ADD CONSTRAINT NAME = student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id);
- C.
  
  ALTER TABLE student_grades ADD CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id);
- D.
  
  ALTER TABLE student grades ADD NAMED CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id);
- E.
  
  ALTER TABLE student grades ADD NAME student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id);
Correct Answer
C. ALTER TABLE student_grades ADD CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id);

Explanation
The statement "ALTER TABLE student_grades ADD CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id);" explicitly names a constraint by including the "CONSTRAINT student_id_fk" part in the command.

Rate this question:
4.

You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES and DEPARTMENTS tables: EMPLOYEE_ID, EMPLOYEE_NAME AND DEPARTMENT_NAME. The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key DEPARTMENT_ID column of the DEPARTMENTS table. You want to modify the view by adding a fourth column, MANAGER_ID of NUMBER data type from the EMPLOYEES tables. How can you accomplish this task?
- A.
  
  ALTER VIEW emp_dept_vu (ADD manager_id NUMBER);
- B.
  
  MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER);
- C.
  
  ALTER VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employee e, departments d WHERE e.department_id = d.department_id;
- D.
  
  MODIFY VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;
- E.
  
  CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;
- F.
  
  You must remove the existing view first, and then run the CREATE VIEW command with a new column list to modify a view.
Correct Answer
E. CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;

Explanation
To accomplish the task of adding a fourth column, MANAGER_ID, to the view EMP_DEPT_VU, the correct option is to use the CREATE OR REPLACE VIEW statement. This statement creates or replaces the view with the specified column list, in this case, including the MANAGER_ID column. The SELECT statement within the CREATE OR REPLACE VIEW statement retrieves the desired columns from the EMPLOYEES and DEPARTMENTS tables, joining them based on the DEPARTMENT_ID foreign key relationship. Therefore, the correct answer is: CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id.

Rate this question:
5.

For which two constraints does the Oracle Server implicitly create a unique index? (Choose two.)
- A.
  
  NOT NULL
- B.
  
  PRIMARY KEY
- C.
  
  FOREIGN KEY
- D.
  
  CHECK
- E.
  
  UNIQUE
Correct Answer(s)
B. PRIMARY KEY
E. UNIQUE

Explanation
The Oracle Server implicitly creates a unique index for the PRIMARY KEY and UNIQUE constraints. When a table is created with a primary key constraint, Oracle automatically creates a unique index on the primary key column(s) to enforce the uniqueness of the values. Similarly, when a table is created with a unique constraint, Oracle also creates a unique index to enforce the uniqueness of the values in the specified column(s).

Rate this question:
6.

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEES EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHARD2(25) LAST_NAME VARCHARD2(25) HIRE_DATE DATE NEW EMPLOYEES EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(60) Which UPDATE statement is valid?
- A.
  
  UPDATE new_employees SET name = (Select last_name|| first_name FROM employees Where employee_id =180) WHERE employee_id =180;
- B.
  
  UPDATE new_employees SET name = (SELECT last_name||first_name FROM employees) WHERE employee_id =180;
- C.
  
  UPDATE new_employees SET name = (SELECT last_name|| first_name FROM employees WHERE employee_id =180) WHERE employee_id =(SELECT employee_id FROM new employees);
- D.
  
  UPDATE new_employees SET name = (SELECT last name|| first_name FROM employees WHERE employee_id= (SELECT employee_id FROM new_employees)) WHERE employee_id =180;
Correct Answer
A. UPDATE new_employees SET name = (Select last_name|| first_name FROM employees Where employee_id =180) WHERE employee_id =180;

Explanation
The correct answer is "UPDATE new_employees SET name = (Select last_name|| first_name FROM employees Where employee_id =180) WHERE employee_id =180;". This is the valid UPDATE statement because it correctly selects the last_name and first_name from the employees table where the employee_id is 180, and then updates the name column in the new_employees table where the employee_id is also 180.

Rate this question:
7.

Examine the structure if the EMPLOYEES table: Column name Data Type Remarks EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30) JOB_ID VARCHAR2(20) NOT NULL SAL NUMBER MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of the DEPARTMENTS table You need to create a view called EMP_VU that allows the user to insert rows through the view. Which SQL statement, when used to create the EMP_VU view, allows the user to insert rows?
- A.
  
  CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, department_id FROM employees WHERE mgr_id IN (102, 120);
- B.
  
  CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id department_id FROM employees WHERE mgr_id IN (102, 120);
- C.
  
  CREATE VIEW emp_Vu AS SELECT department_id, SUM(sal) TOTALSAL FROM employees WHERE mgr_id IN (102, 120) GROUP BY department_id;
- D.
  
  CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, DISTINCT department_id FROM employees;
Correct Answer
B. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id department_id FROM employees WHERE mgr_id IN (102, 120);

Explanation
The correct answer is "CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id department_id FROM employees WHERE mgr_id IN (102, 120);". This statement creates a view called EMP_VU that allows the user to insert rows. It selects the columns employee_id, emp_name, job_id, and department_id from the EMPLOYEES table and filters the rows based on the condition that the mgr_id is either 102 or 120.

Rate this question:
8.

The STUDENT_GRADES table has these columns: STUDENT_ID NUMBER(12) SEMESTER_END DATE GPA NUMBER(4,3) The registrar has asked for a report on the average grade point average (GPA) for students enrolled during semesters that end in the year 2000. Which statement accomplish this?
- A.
  
  SELECT AVERAGE(gpa) FROM student_grades WHERE semester_end > ’01-JAN-2000’ and semester end < 31-DEC-2000’;
- B.
  
  SELECT COUNT(gpa) FROM student grades WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;
- C.
  
  SELECT MIN(gpa) FROM student grades WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;
- D.
  
  SELECT AVG(gpa) FROM student_grades WHERE semester_end BETWEEN ’01-JAN-2000’ and ’31.DEC.2000’;
- E.
  
  SELECT SUM(gpa) FROM student grades WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;
- F.
  
  SELECT MEDIAN(gpa) FROM student_grades WHERE semester end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;
Correct Answer
D. SELECT AVG(gpa) FROM student_grades WHERE semester_end BETWEEN ’01-JAN-2000’ and ’31.DEC.2000’;

Explanation
The correct answer is "SELECT AVG(gpa) FROM student_grades WHERE semester_end BETWEEN '01-JAN-2000' and '31.DEC.2000';". This statement uses the AVG function to calculate the average grade point average (GPA) for students enrolled during semesters that end in the year 2000. The WHERE clause filters the data to only include records where the semester_end date is between January 1, 2000, and December 31, 2000.

Rate this question:
9.

Which two are true about aggregate functions? (Choose two.)
- A.
  
  You can use aggregate functions in any clause of a SELECT statement.
- B.
  
  You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.
- C.
  
  You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.
- D.
  
  You can pass column names, expressions, constants, or functions as parameters to an aggregate function.
- E.
  
  You can use aggregate functions on a table, only by grouping the whole table as one single group.
- F.
  
  You cannot group the rows of a table by more than one column while using aggregate functions.
Correct Answer(s)
C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.
D. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.

Explanation
Aggregate functions can be used in any clause of a SELECT statement, not just in the column list and WHERE clause. This makes the statement "You can use aggregate functions in any clause of a SELECT statement" true. Additionally, it is possible to mix single row columns with aggregate functions in the column list by grouping on the single row columns, allowing for the combination of individual column values with aggregate results. This validates the statement "You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns." Furthermore, aggregate functions can take column names, expressions, constants, or functions as parameters, making the statement "You can pass column names, expressions, constants, or functions as parameters to an aggregate function" true.

Rate this question:
10.

In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed in the SELECT statement?
- A.
  
  Immediately after the SELECT clause
- B.
  
  Before the WHERE clause
- C.
  
  Before the FROM clause
- D.
  
  After the ORDER BY clause
- E.
  
  After the WHERE clause
Correct Answer
E. After the WHERE clause

Explanation
The GROUP BY clause is placed after the WHERE clause in a SELECT statement. This is because the WHERE clause is used to filter the rows before they are grouped, and the GROUP BY clause is used to group the rows based on certain criteria. Therefore, the WHERE clause is executed first to filter the rows, and then the GROUP BY clause is used to group them.

Rate this question:
11.

Which SQL statement defines a FOREIGN KEY constraint on the DEPTNO column of the EMP table?
- A.
  
  CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk FOREIGN KEY deptno REFERENCES dept deptno);
- B.
  
  CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
- C.
  
  CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));
- D.
  
  CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
Correct Answer
B. CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));

Explanation
The correct answer is "CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));". This statement defines a FOREIGN KEY constraint named "emp_deptno_fk" on the "deptno" column of the "EMP" table, referencing the "deptno" column of the "dept" table.

Rate this question:
12.

Click the Exhibit button and examine the data in the EMPLOYEES table. Which three subqueries work? (Choose three.)
- A.
  
  SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);
- B.
  
  SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);
- C.
  
  SELECT distinct department_id FROM employees WHERE salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
- D.
  
  SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
- E.
  
  SELECT last_name FROM employees WHERE salary > ANY (SELECT MAX(salary) FROM employees GROUP BY department_id);
- F.
  
  SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));
Correct Answer(s)
C. SELECT distinct department_id FROM employees WHERE salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
D. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
E. SELECT last_name FROM employees WHERE salary > ANY (SELECT MAX(salary) FROM employees GROUP BY department_id);

Explanation
The first subquery selects all employees whose salary is greater than the minimum salary within their respective department. The second subquery selects all employees whose salary is equal to the average salary within their respective department. The third subquery selects all distinct department IDs where the salary is greater than any of the average salaries within their respective department.

Rate this question:
13.

Which SQL statement accepts user input for the columns to be displayed, the table name, and the WHERE condition?
- A.
  
  SELECT &1,
- B.
  
  SELECT &1, '&2' FROM &3 WHERE '&last_name = '&4'';
- C.
  
  SELECT &1, &2 FROM &3 WHERE last_name = '&4';
- D.
  
  SELECT &1, '&2' FROM EMP WHERE last_name = '&4';
Correct Answer
C. SELECT &1, &2 FROM &3 WHERE last_name = '&4';

Explanation
The correct answer is "SELECT &1, &2 FROM &3 WHERE last_name = '&4';". This statement accepts user input for the columns to be displayed, the table name, and the WHERE condition. The "&1", "&2", "&3", and "&4" are placeholders that can be replaced with the actual values provided by the user. This allows for dynamic and customizable queries based on user input.

Rate this question:
14.

Evaluate these two SQL statements: SELECT last_name, salary , hire_date FROM EMPLOYEES ORDER BY salary DESC; SELECT last_name, salary , hire_date FROM EMPLOYEES ORDER BY 2 DESC; What is true about them?
- A.
  
  The two statements produce identical results.
- B.
  
  The second statement returns a syntax error.
- C.
  
  There is no need to specify DESC because the results are sorted in descending order by default.
- D.
  
  The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.
Correct Answer
A. The two statements produce identical results.

Explanation
The two statements produce identical results because they both select the same columns (last_name, salary, hire_date) from the EMPLOYEES table and order them in descending order based on the salary column. The second statement uses the shorthand notation "ORDER BY 2 DESC" which refers to the second column in the SELECT clause, which is the salary column. Therefore, both statements will return the same result set.

Rate this question:
15.

In which scenario would an index be most useful?
- A.
  
  The indexed column is declared as NOT NULL.
- B.
  
  The indexed columns are used in the FROM clause.
- C.
  
  The indexed columns are part of an expression.
- D.
  
  The indexed column contains a wide range of values.
Correct Answer
D. The indexed column contains a wide range of values.

Explanation
An index is most useful when the indexed column contains a wide range of values. This is because an index helps in quickly locating specific values in a large dataset. When a column has a wide range of values, it means that there is a high cardinality, and the index can effectively narrow down the search space, improving query performance. On the other hand, if the column has a low cardinality, meaning a limited number of distinct values, the index may not be as beneficial as it would result in a large number of rows being retrieved, leading to slower query execution.

Rate this question:
16.

Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2) Which statement produces the number of different departments that have employees with last name Smith?
- A.
  
  SELECT COUNT(*) FROM employees WHERE last_name='Smith';
- B.
  
  SELECT COUNT(dept_id) FROM employees WHERE last_name='Smith';
- C.
  
  SELECT DISTINCT(COUNT(dept_id)) FROM employees WHERE last_name='Smith';
- D.
  
  SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith';
- E.
  
  SELECT UNIQUE(dept_id) FROM employees WHERE last_name='Smith';
Correct Answer
D. SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith';

Explanation
The correct answer is "SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith'". This statement counts the number of different departments that have employees with the last name "Smith". The DISTINCT keyword ensures that each department is only counted once, even if there are multiple employees with the last name "Smith" in the same department.

Rate this question:
17.

What does the FORCE option for creating a view do?
- A.
  
  Creates a view with constraints
- B.
  
  Creates a view even if the underlying parent table has constraints
- C.
  
  Creates a view in another schema even if you don't have privileges
- D.
  
  Creates a view regardless of whether or not the base tables exist
Correct Answer
D. Creates a view regardless of whether or not the base tables exist

Explanation
The FORCE option for creating a view allows the view to be created regardless of whether or not the base tables exist. This means that even if the tables that the view is based on do not exist, the view will still be created. This can be useful in situations where the view is created before the tables it depends on are created, or if the tables have been dropped but the view still needs to be created.

Rate this question:
18.

What is true about joining tables through an equijoin?
- A.
  
  You can join a maximum of two tables through an equijoin.
- B.
  
  You can join a maximum of two columns through an equijoin.
- C.
  
  You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.
- D.
  
  To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns.
- E.
  
  You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.
Correct Answer
E. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.

Explanation
An equijoin is a type of join where the join condition is based on equality between columns from the tables being joined. This answer states that you can join n tables in a SQL statement by specifying a minimum of n-1 join conditions. This means that if you have n tables, you need to specify at least n-1 join conditions to join them through an equijoin. This allows for joining multiple tables together using equijoins, as long as the necessary join conditions are specified.

Rate this question:
19.

Which two statements are true about WHERE and HAVING clauses? (Choose two)
- A.
  
  A WHERE clause can be used to restrict both rows and groups.
- B.
  
  A WHERE clause can be used to restrict rows only.
- C.
  
  A HAVING clause can be used to restrict both rows and groups.
- D.
  
  A HAVING clause can be used to restrict groups only.
- E.
  
  A WHERE clause CANNOT be used in a query of the query uses a HAVING clause.
- F.
  
  A HAVING clause CANNOT be used in subqueries.
Correct Answer(s)
B. A WHERE clause can be used to restrict rows only.
C. A HAVING clause can be used to restrict both rows and groups.

Explanation
The first statement is true because a WHERE clause is used to filter rows based on a specific condition, restricting the result set to only include rows that meet the condition. The second statement is also true because a HAVING clause is used to filter groups based on a condition applied to the result of a GROUP BY clause, restricting the result set to only include groups that meet the condition.

Rate this question:
20.

You are sorting data in a table in you SELECT statement in descending order. The column you are sorting on contains NULL records, where will the NULL record appears?
- A.
  
  At the beginning of the list.
- B.
  
  At the end of the list
- C.
  
  In the middle of the list.
- D.
  
  At the same location they are listed in the unordered table.
Correct Answer
A. At the beginning of the list.

Explanation
When sorting data in descending order, the NULL records will appear at the beginning of the list. This is because in descending order, the highest values are placed first, and since NULL is considered as the lowest possible value, it will be positioned at the start of the sorted list.

Rate this question:
21.

You are sorting data in a table in you SELECT statement in descending order. The column you are sorting on contains NULL records, where will the NULL record appears?
- A.
  
  At the beginning of the list.
- B.
  
  At the end of the list
- C.
  
  In the middle of the list.
- D.
  
  At the same location they are listed in the unordered table.
Correct Answer
A. At the beginning of the list.

Explanation
When sorting data in descending order, NULL records will appear at the beginning of the list. This is because NULL is considered the lowest possible value in the sorting order. Therefore, it is placed before any other non-NULL values in the descending order.

Rate this question:
22.

The ACCOUNT table contains these columns: ACCOUNT_ID NUMBER(12) PREVIOUS_BALANCE NUMBER(7,2) PAYMENTS NUMBER(7,2) NEW_PURCHASES NUMBER(7,2) CREDIT_LIMIT NUMBER(7) You need to display the account number, finance charge, and current balance for accounts 1500 through 2000 with a current balance greater than the account's credit limit. The finance charge is .9 percent (.009) of the previous balance. Adding the previous balance value, new purchases value, and finance charge value, and then subtracting the payments value yields the current balance value. Evaluate this statement: SELECT account_id, NVL(previous_balance, 0) * .009 finance_charge, NVL(new_purchases, 0) + (NVL(previous_balance, 0) * 1.009) - NVL(payments, 0) current balance FROM account WHERE (new_purchases + (previous_balance * 1.009)) - payments > credit_limit AND account_id BETWEEN 1500 AND 2000; Which statement about this SELECT statement is true?
- A.
  
  The statement calculates the finance charge incorrectly.
- B.
  
  The statement calculates the current balance incorrectly.
- C.
  
  The statement returns only accounts that have NO previous balance.
- D.
  
  The statement returns only accounts that have new purchases, previous balance, and
Correct Answer
D. The statement returns only accounts that have new purchases, previous balance, and

Explanation
The statement returns only accounts that have new purchases, previous balance, and payments greater than the credit limit.

Rate this question:
23.

You would like to display the system date in the format "Monday, 01 June, 2001". Which SELECT statement should you use?
- A.
  
  SELECT TO_DATE(SYSDATE, 'FMDAY, DD Month, YYYY') From dual;
- B.
  
  SELECT TO_CHAR(SYSDATE, 'FMDD, DY Month, 'YYY') From dual;
- C.
  
  SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY') From dual;
- D.
  
  SELECT TO_CHAR(SYSDATE, 'FMDY, DDD Month, YYYY') From dual;
- E.
  
  SELECT TO_DATE(SYSDATE, 'FMDY, DDD Month, YYYY') From dual;
Correct Answer
C. SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY') From dual;

Explanation
The correct answer is "SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY') From dual;". This is because the TO_CHAR function is used to convert the system date (SYSDATE) into a character string with the specified format. The 'FMDay' format element is used to display the day of the week in the full name format, 'DD' is used to display the day of the month, 'Month' is used to display the month in the full name format, and 'YYYY' is used to display the year in four digits. The result will be in the format "Monday, 01 June, 2001".

Rate this question:
24.

Examine the description of the STUDENTS table: STD_ID NUMBER(4) COURSE_ID VARCHARD2(10) START_DATE DATE END_DATE DATE. Which two aggregate functions are valid on the START_DATE column? (Choose two)
- A.
  
  SUM(start_date)
- B.
  
  AVG(start_date)
- C.
  
  COUNT(start_date)
- D.
  
  AVG(start_date, end_date)
- E.
  
  MIN(start_date)
- F.
  
  MAXIMUM(start_date)
Correct Answer(s)
C. COUNT(start_date)
E. MIN(start_date)
25.

You want to use single row function in your SQL statements which three of the following are number functions? (Choose three).
- A.
  
  SINH
- B.
  
  TO_NUMBER.
- C.
  
  SQRT.
- D.
  
  ROUND.
Correct Answer(s)
A. SINH
C. SQRT.

Explanation
The correct answer is SINH, SQRT. These two functions are number functions that can be used in SQL statements. SINH is a hyperbolic sine function that returns the hyperbolic sine of a given number. SQRT is a square root function that returns the square root of a given number. Both functions can be used to perform calculations and manipulate numerical data in SQL queries.

Rate this question:
26.

You are using single row function in a SELECT statement which function can best be catergorized as similar in function to an IF-THEN-ELSE statement?
- A.
  
  SQRT
- B.
  
  DECODE
- C.
  
  NEW_TIME
- D.
  
  ROWIDTOCHAR.
Correct Answer
B. DECODE

Explanation
The function that can best be categorized as similar in function to an IF-THEN-ELSE statement is the DECODE function. DECODE allows you to check a specific value and return a corresponding value based on the condition. It works similar to an IF-THEN-ELSE statement by evaluating multiple conditions and returning a result based on the condition that evaluates to true.

Rate this question:
27.

Examine the structure of the EMPLOYEES and DEPARTMENTS tables: EMPLOYEES Column name Data type Remarks EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2 (30) JOB_ID VARCHAR2 (20) SALARY NUMBER MGR_ID NUMBER References EMPLOYEE_ID COLUMN DEPARTMENT ID NUMBER Foreign key to DEPARTMENT ID column of the DEPARTMENTS table DEPARTMENTS Column name Data type Remarks DEPARTMENT_ID NUMBER NOT NULL, Primary Key DEPARTMENT_NAME VARCHAR2(30) MGR_ID NUMBER References MGR_ID column of the EMPLOYEES table Evaluate this SQL statement: SELECT employee_id, e.department_id, department_name, salary FROM employees e, departments d WHERE e.department_id = d.department_id; Which SQL statement is equivalent to the above SQL statement?
- A.
  
  SELECT employee_id, department_id, department_name,salary.FROM employees WHERE department_id IN (SELECT department_id FROM departments);
- B.
  
  SELECT employee_id, department_id, department_name,salary FROM employees NATURAL JOIN departments;
- C.
  
  SELECT employee_id, d.department_id, department_name,salary FROM employees e JOIN departments d ON e.department_id =d.department_id;
- D.
  
  SELECT employee_id, department_id, department_name,Salary FROM employees JOIN departments USING (e.department_id,d.department_id);
Correct Answer
C. SELECT employee_id, d.department_id, department_name,salary FROM employees e JOIN departments d ON e.department_id =d.department_id;

Explanation
The correct answer is "SELECT employee_id, d.department_id, department_name, salary FROM employees e JOIN departments d ON e.department_id = d.department_id;"

This statement joins the employees and departments tables based on the department_id column, and selects the employee_id, department_id, department_name, and salary columns from the result. The "e" and "d" are aliases for the employees and departments tables, respectively. This query will return the same result as the original SQL statement.

Rate this question:
28.

In which case would you use a FULL OUTER JOIN?
- A.
  
  Both tables have NULL values.
- B.
  
  You want all unmatched data from one table.
- C.
  
  You want all matched data from both tables.
- D.
  
  You want all unmatched data from both tables.
- E.
  
  One of the tables has more data than the other.
- F.
  
  You want all matched and unmatched data from only one table.
Correct Answer
D. You want all unmatched data from both tables.

Explanation
A FULL OUTER JOIN is used when you want to retrieve all unmatched data from both tables. This means that you want to include all records from both tables, regardless of whether they have a match in the other table. The result will include all matched data as well as all unmatched data from both tables, including any NULL values.

Rate this question:

1
0
29.

Which three are true regarding the use of outer joins? (Choose three.)
- A.
  
  You cannot use IN operator in a condition that involves an outerjoin.
- B.
  
  You use (+) on both sides of the WHERE condition to perform an outerjoin.
- C.
  
  You use (*) on both sides of the WHERE condition to perform an outerjoin.
- D.
  
  You use an outerjoin to see only the rows that do not meet the join condition.
- E.
  
  In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outerjoin.
- F.
  
  You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator.
Correct Answer(s)
D. You use an outerjoin to see only the rows that do not meet the join condition.
E. In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outerjoin.
F. You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator.

Explanation
The first true statement is that you use an outerjoin to see only the rows that do not meet the join condition. This means that when performing an outer join, you will see all the rows from one table, even if there is no match in the other table.

The second true statement is that in the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outerjoin. This is the syntax used in Oracle for performing an outer join.

The third true statement is that you cannot link a condition that is involved in an outerjoin to another condition by using the OR operator. This means that when using an outer join, you cannot use the OR operator to combine multiple conditions.

Rate this question:
30.

Which two operators can be used in an outer join condition? (Choose two.)
- A.
  
  =
- B.
  
  OR
- C.
  
  AND
- D.
  
  IN
Correct Answer(s)
A. =
C. AND

Explanation
The two operators that can be used in an outer join condition are "=" and "AND". The "=" operator is used to compare values for equality, while the "AND" operator is used to combine multiple conditions. In the context of an outer join, these operators are used to specify the join condition between two tables, where the values in the specified columns are equal and meet additional criteria specified by the "AND" operator.

Rate this question:
31.

To produce a meaningful result set without any cartesian products, what is the minimum number of conditions that should appear in the WHERE clause of a four-table join?
- A.
  
  8
- B.
  
  2
- C.
  
  3
- D.
  
  4
- E.
  
  5
- F.
  
  There is no such criteria
Correct Answer
C. 3

Explanation
To produce a meaningful result set without any cartesian products in a four-table join, a minimum of three conditions should appear in the WHERE clause. This is because each condition helps to establish a relationship between two tables, and with four tables, at least three conditions are needed to connect them all together and avoid cartesian products.

Rate this question:
32.

Evaluate this SQL statement: SELECT product_id, product_name, price FROM product WHERE supplier_id IN (SELECT supplier_id FROM product WHERE price > 120 OR qty_in_stock > 100); Which values will be displayed?
- A.
  
  The PRODUCT_ID, PRODUCT_NAME, and PRICE of products that are priced greater than $120.00 and have a QTY_IN_STOCK value greater than 100.
- B.
  
  The PRODUCT_ID , PRODUCT_NAME, and PRICE of products that are priced greater than $120.00 or that have a QTY_IN_STOCK value greater than 100.
- C.
  
  The PRODUCT_ID, PRODUCT_NAME, and PRICE of products that are priced greater than $120.00 or that have a QTY_IN_STOCK value greater than 100 and have a supplier.
- D.
  
  The PRODUCT_ID, PRODUCT_NAME, and PRICE of products supplied by a supplier with products that are priced greater than $120.00 or with products that have a QTY_IN_STOCK value greater than 100.
Correct Answer
D. The PRODUCT_ID, PRODUCT_NAME, and PRICE of products supplied by a supplier with products that are priced greater than $120.00 or with products that have a QTY_IN_STOCK value greater than 100.

Explanation
The SQL statement selects the PRODUCT_ID, PRODUCT_NAME, and PRICE from the product table. It uses a subquery to select the supplier_id from the product table where the price is greater than $120 or the quantity in stock is greater than 100. The main query then selects the PRODUCT_ID, PRODUCT_NAME, and PRICE from the product table where the supplier_id is in the result of the subquery. This means that only products supplied by a supplier with products that meet the specified conditions will be displayed.

Rate this question:
33.

Which statement regarding subqueries is true?
- A.
  
  A subquery CANNOT reference a table that is not included in the outer query's FROM clause.
- B.
  
  Subqueries can be nested up to 5 levels.
- C.
  
  A subquery must be placed on the right side of the comparison operator.
- D.
  
  Subqueries can return multiple columns.
Correct Answer
D. Subqueries can return multiple columns.

Explanation
Subqueries can return multiple columns, meaning that the result of a subquery can include more than one column of data. This allows for more complex and versatile queries, as the subquery can provide multiple pieces of information that can be used in the outer query's calculations or conditions. This is a useful feature in SQL that allows for more flexibility in querying and analyzing data.

Rate this question:
34.

You define a multiple-row subquery in the WHERE clause of an SQL query with a comparison operator "=". What happens when the main query is executed?
- A.
  
  The main query executes with the first value returned by the subquery.
- B.
  
  The main query executes with the last value returned by the subquery.
- C.
  
  The main query executes with all the values returned by the subquery.
- D.
  
  The main query fails because the multiple-row subquery cannot be used with the comparison Operator.
- E.
  
  You cannot define a multiple-row subquery in the WHERE clause of a SQL query.
Correct Answer
D. The main query fails because the multiple-row subquery cannot be used with the comparison Operator.

Explanation
When the main query is executed, it fails because a multiple-row subquery cannot be used with the comparison operator "=" in the WHERE clause of an SQL query. This means that the main query cannot handle the situation where the subquery returns more than one row, and therefore it fails to execute.

Rate this question:

Quiz Review Timeline +

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

Current Version
Mar 22, 2023

Quiz Edited by
ProProfs Editorial Team
Mar 29, 2011

Quiz Created by
Unrealvicky

Oracle Mock 3

Which /SQL*Plus feature can be used to replace values in the WHERE clause?

You want to display the titles of books that meet these criteria: 1. Purchased before January 21, 2001 2. Price is less then $500 or greater than $900 You want to sort the results by their data of purchase, starting with the most recently bought book. Which statement should you use?

Which statement explicitly names a constraint?

For which two constraints does the Oracle Server implicitly create a unique index? (Choose two.)

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEES EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHARD2(25) LAST_NAME VARCHARD2(25) HIRE_DATE DATE NEW EMPLOYEES EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(60) Which UPDATE statement is valid?

The STUDENT_GRADES table has these columns: STUDENT_ID NUMBER(12) SEMESTER_END DATE GPA NUMBER(4,3) The registrar has asked for a report on the average grade point average (GPA) for students enrolled during semesters that end in the year 2000. Which statement accomplish this?

Which two are true about aggregate functions? (Choose two.)

In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed in the SELECT statement?

Which SQL statement defines a FOREIGN KEY constraint on the DEPTNO column of the EMP table?

Click the Exhibit button and examine the data in the EMPLOYEES table. Which three subqueries work? (Choose three.)

Which SQL statement accepts user input for the columns to be displayed, the table name, and the WHERE condition?

Evaluate these two SQL statements: SELECT last_name, salary , hire_date FROM EMPLOYEES ORDER BY salary DESC; SELECT last_name, salary , hire_date FROM EMPLOYEES ORDER BY 2 DESC; What is true about them?

In which scenario would an index be most useful?

Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2) Which statement produces the number of different departments that have employees with last name Smith?

What does the FORCE option for creating a view do?

What is true about joining tables through an equijoin?

Which two statements are true about WHERE and HAVING clauses? (Choose two)

You are sorting data in a table in you SELECT statement in descending order. The column you are sorting on contains NULL records, where will the NULL record appears?

You are sorting data in a table in you SELECT statement in descending order. The column you are sorting on contains NULL records, where will the NULL record appears?

You would like to display the system date in the format "Monday, 01 June, 2001". Which SELECT statement should you use?

Examine the description of the STUDENTS table: STD_ID NUMBER(4) COURSE_ID VARCHARD2(10) START_DATE DATE END_DATE DATE. Which two aggregate functions are valid on the START_DATE column? (Choose two)

You want to use single row function in your SQL statements which three of the following are number functions? (Choose three).

You are using single row function in a SELECT statement which function can best be catergorized as similar in function to an IF-THEN-ELSE statement?

In which case would you use a FULL OUTER JOIN?

Which three are true regarding the use of outer joins? (Choose three.)

Which two operators can be used in an outer join condition? (Choose two.)

To produce a meaningful result set without any cartesian products, what is the minimum number of conditions that should appear in the WHERE clause of a four-table join?

Evaluate this SQL statement: SELECT product_id, product_name, price FROM product WHERE supplier_id IN (SELECT supplier_id FROM product WHERE price > 120 OR qty_in_stock > 100); Which values will be displayed?

Which statement regarding subqueries is true?

You define a multiple-row subquery in the WHERE clause of an SQL query with a comparison operator "=". What happens when the main query is executed?