Oracle Final Test Questions And Answers

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 Annstefi
A
Annstefi
Community Contributor
Quizzes Created: 1 | Total Attempts: 3,770
| Attempts: 3,775
SettingsSettings
Please wait...
  • 1/40 Questions

    Mark for review The PRODUCTS table has these columns: PRODUCT_ID NUMBER(4) PRODUCT_NAME VARCHAR2(45) PRICE NUMBER(8,2) Evaluate this SQL statement: SELECT * FROM PRODUCTS ORDER BY price, product_name; What is true about the SQL statement?

    • A. The results are not sorted.
    • B. The results are sorted numerically
    • C. The results are sorted alphabetically
    • . D. The results are sorted numerically and then alphabetically.
Please wait...
About This Quiz

Hey, check out this amazing 'Oracle Final Test' quiz. We've designed this quiz to test your knowledge about the Oracle subject. If you are planning to give the Oracle Certification Exam, then this quiz will prove to be very useful for you as it will help you prepare for the course. All you have to do is choose the correct option for each question asked in this test.
So, are you ready to take the test? Let's start then.

Oracle Final Test Questions And Answers - Quiz

Quiz Preview

  • 2. 

    The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(4) ENAME VARCHAR2 (25) JOB_ID VARCHAR2(10) Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of the letter "a" in the ENAME column, for those employees whose ENAME ends with a the letter "n"?

    • A. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n';

    • B. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ,-1,1) FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n';

    • C. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, 1, 1) = 'n';

    • D. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, -1, 1) = 'n';

    Correct Answer
    A. A. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n';
    Explanation
    The correct answer is A. The SQL statement selects the ENAME, length of the ENAME, and the numeric position of the letter "a" in the ENAME column. It uses the SUBSTR function to extract the last character of the ENAME column and compares it to 'n' to check if it ends with the letter 'n'. The LENGTH function returns the length of the ENAME column, and the INSTR function returns the position of the letter 'a' in the ENAME column.

    Rate this question:

  • 3. 

    Which aggregate function is valid on the START_DATE column? (START_DATE datatype DATE)

    • SUM(start_date)

    •  AVG(start_date)

    • COUNT(start_date)

    • AVG(start_date, end_date)

    Correct Answer
    A. COUNT(start_date)
    Explanation
    The COUNT(start_date) function is valid on the START_DATE column because it counts the number of non-null values in the column. Since the START_DATE column is of datatype DATE, it can be used with the COUNT function to count the number of rows that have a non-null value in the START_DATE column.

    Rate this question:

  • 4. 

    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 SQLstatement do you use?

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

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

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

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

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

    Rate this question:

  • 5. 

    Which of the following correctly shows the correct use of the TRUNC command on a date?

    • A. SELECT TRUNC(TO_DATE(12-Feb-99,DD-MON-YY, 'YEAR')) "Date " FROM DUAL;

    • B. TRUNC = TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR', "Date " FROM DUAL;

    • C. SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;

    • D. date = TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL

    Correct Answer
    A. C. SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;
    Explanation
    The correct answer is C. SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL; This is the correct use of the TRUNC command on a date because it uses the TO_DATE function to convert the string '12-Feb-99' into a date format, and then applies the TRUNC function to truncate the date to the beginning of the year. The "Date " alias is used to label the resulting column.

    Rate this question:

  • 6. 

    To write a query that performs an outer join of tables A and B and returns all rows from B, you need to write

    • A. any outer join

    • B. a left outer join

    • C. a cross join

    • D. a right outer join

    • E. an inner join

    Correct Answer
    A. D. a right outer join
    Explanation
    A right outer join is needed to perform an outer join of tables A and B and return all rows from B. A right outer join returns all the rows from the right table (B) and the matching rows from the left table (A). This means that even if there are no matches in table A, the rows from table B will still be included in the result. Therefore, a right outer join is the correct choice in this scenario.

    Rate this question:

  • 7. 

    You need to perform these tasks: 1. Create and assign a MANAGER role to Blake and Clark 2. Grant CREATE TABLE and CREATE VIEW privileges to Blake and Clark Which set of SQL statements achieves the desired results?

    • A. CREATE ROLE manager; GRANT create table, create view TO manager; GRANT manager TO BLAKE,CLARK; ;

    • B. CREATE ROLE manager; GRANT create table, create voew TO manager; GRANT manager ROLE TO BLAKE,CLARK;

    • C. GRANT manager ROLE TO BLAKE,CLARK; GRANT create table, create voew TO BLAKE CLARK;

    Correct Answer
    A. A. CREATE ROLE manager; GRANT create table, create view TO manager; GRANT manager TO BLAKE,CLARK; ;
  • 8. 

    Which order does the Oracle Server evaluate clauses?

    • HAVING, WHERE, GROUP BY

    • WHERE, GROUP BY, HAVING

    • GROUP BY, HAVING, WHERE

    • WHERE, HAVING, GROUP BY

    Correct Answer
    A. WHERE, GROUP BY, HAVING
    Explanation
    The Oracle Server evaluates clauses in the order of WHERE, GROUP BY, HAVING. This means that first, the WHERE clause is evaluated to filter the rows based on the specified conditions. Then, the GROUP BY clause is used to group the filtered rows into sets based on the specified columns. Finally, the HAVING clause is applied to the grouped rows to filter them further based on the specified conditions.

    Rate this question:

  • 9. 

    You attempt to query the database with this command: SELECT name, salary FROM employee WHERE salary= (SELECT salary FROM employee WHERE last_name= ‘Wagner’ OR dept_no=233) Why could this statement cause an error?

    • A. Sub queries are not allowed in the where clause.

    • B. Logical apparatus are not allowed in where clause.

    • C. A multiple row sub query used with a single row comparison operator.

    • D. A single row query is used with a multiple row comparison operator.

    Correct Answer
    A. C. A multiple row sub query used with a single row comparison operator.
    Explanation
    The statement could cause an error because it is using a multiple row subquery (SELECT salary FROM employee WHERE last_name='Wagner' OR dept_no=233) with a single row comparison operator (=) in the WHERE clause. This means that the subquery can potentially return multiple rows, but the comparison operator can only compare a single value. This would lead to a mismatch in the number of rows being compared, causing an error.

    Rate this question:

  • 10. 

    Which constraint can be defines only at the column level?

    • A. UNIQUE

    • B. NOT NULL

    • C. CHECK

    • D. PRIMARY KEY

    • E. FOREIGN KEY

    Correct Answer
    A. B. NOT NULL
    Explanation
    The constraint that can be defined only at the column level is NOT NULL. This constraint ensures that a column cannot have a NULL value, meaning it must always contain a value. The other options (A. UNIQUE, C. CHECK, D. PRIMARY KEY, and E. FOREIGN KEY) can be defined at either the column level or the table level.

    Rate this question:

  • 11. 

    Which of the following has been achieved by the following SQL codes? SELECT * FROM employees WHERE hire_date < TO_DATE ('01-JAN-1999', 'DD-MON-YYYY') AND salary > 3500;

    • Only those hired before 1999 and earning less than $3500 a month are returned

    • Compile time error

    • Only those hired after 1999 and earning more than $3500 a month are returned

    • Runtime error

    • Only those hired before 1999 and earning more than $3500 a month are returned

    Correct Answer
    A. Only those hired before 1999 and earning more than $3500 a month are returned
    Explanation
    The given SQL code selects all the employees whose hire date is before January 1, 1999, and their salary is greater than $3500. Therefore, the correct answer is that only those hired before 1999 and earning more than $3500 a month are returned.

    Rate this question:

  • 12. 

    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, YYYY') 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
    A. C. SELECT TO_CHAR (SYSDATE, 'FMDay, DD Month, YYYY') FROM dual;
    Explanation
    The correct answer is C because the TO_CHAR function is used to convert the system date (SYSDATE) into a character string. The 'FMDay, DD Month, YYYY' format specifier is used to display the date in the desired format of "Monday, 01 June, 2001". The FM modifier is used to remove leading spaces and zeros. The TO_DATE function in options A and E is used to convert a character string into a date, not the other way around. Option B uses the DY format specifier which displays the abbreviated day of the week, not the full day. Option D uses the DDD format specifier which displays the day of the year, not the day of the month.

    Rate this question:

  • 13. 

    Which of the following SQL statements can calculate and return the absolute value of -33?

    • A. SELECT ABS("-33") Absolute FROM DUAL;

    • B. SELECT ABS('-33') "Absolute" FROM DUAL;

    • C. SELECT ABS(-33) "Absolute" FROM DUAL;

    • D. SELECT ABS(-33), Absolute FROM DUAL;

    Correct Answer
    A. C. SELECT ABS(-33) "Absolute" FROM DUAL;
    Explanation
    The correct answer is C because the ABS() function in SQL is used to calculate the absolute value of a number. In this case, the number is -33. The statement SELECT ABS(-33) "Absolute" FROM DUAL; will calculate the absolute value of -33 and return it as a column named "Absolute" from the DUAL table.

    Rate this question:

  • 14. 

    Which substitution variable would you use if you want to reuse the variable value without prompting the user each time?

    • A. &

    • B. ACCEPT

    • C. PROMPT

    • D. &&

    Correct Answer
    A. D. &&
    Explanation
    The correct answer is D. &&. In Oracle SQL, the && substitution variable allows you to reuse the variable value without prompting the user each time. When you use && followed by a variable name, it prompts the user to enter a value for that variable. However, if you use && followed by the same variable name again, it will reuse the previously entered value without prompting the user again. This is useful when you want to use the same value multiple times in a script without having to re-enter it each time.

    Rate this question:

  • 15. 

    Scott issues the SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13)}; GRANT SELECT ON DEPT TO SUE; If Sue needs to select from Scott's DEPT table, which command should she use?

    • A. SELECT * FROM DEPT;

    • B. SELECT * FROM SCOTT.DEPT;

    • C. SELECT * FROM DBA.SCOTT.DEPT;

    • D. SELECT * FROM ALL_USERS WHERE USER_NAME = 'SCOTT' AND TABLE NAME = 'DEPT';

    Correct Answer
    A. B. SELECT * FROM SCOTT.DEPT;
    Explanation
    The correct answer is B. SELECT * FROM SCOTT.DEPT; because Sue needs to select from Scott's DEPT table, and the table is owned by Scott. Therefore, she should use the syntax SCOTT.DEPT to specify the table owned by Scott.

    Rate this question:

  • 16. 

     Which statement describes the ROWID data type?

    • A. binary data up to 4 gigabytes

    • B. character data up to 4 gigabytes

    • C. raw binary data of variable length up to 2 gigabytes

    • D. binary data stored in an external file, up to 4 gigabytes

    • E. a hexadecimal string representing the unique address of a row in its table

    Correct Answer
    A. E. a hexadecimal string representing the unique address of a row in its table
    Explanation
    The ROWID data type is a hexadecimal string that represents the unique address of a row in its table. This means that each row in a table has a unique ROWID value assigned to it, which can be used to identify and access that specific row. The ROWID is not related to the size of the data or stored in an external file.

    Rate this question:

  • 17. 

    What is true about updates through a view?

    • A. You cannot update a view with group functions.

    • B. When you update a view group functions are automatically computed.

    • C. When you update a view only the constraints on the underlying table will be in effect

    • D. When you update a view the constraints on the views always override the constraints on the underlying tables

    Correct Answer
    A. A. You cannot update a view with group functions.
    Explanation
    When updating a view, it is not possible to use group functions. Group functions like SUM, AVG, COUNT, etc., are used to perform calculations on a set of rows and return a single value. However, when updating a view, the update operation is performed on the underlying table(s) and not on the view itself. Therefore, group functions cannot be used in this context.

    Rate this question:

  • 18. 

    Examine these statements: CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT registrar to user1, user2, user3; What does this set of SQL statements do?  

    • A. The set of statements contains an error and does not work.

    • B. It creates a role called REGISTRAR, adds the MODIFY privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.

    • C. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.

    • D. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and creates three users with the role.

    • E. It creates a role called REGISTRAR, adds the UPDATE privilege on three users, and gives the REGISTRAR role to the STUDENT_GRADES object.

    Correct Answer
    A. C. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.
    Explanation
    This set of SQL statements creates a role called REGISTRAR. It then grants the UPDATE privilege on the STUDENT_GRADES object to the REGISTRAR role. Finally, it gives the REGISTRAR role to three users (user1, user2, and user3). Therefore, the correct answer is C.

    Rate this question:

  • 19. 

     Which statement creates a new user?

    • A. CREATE USER susan

    • B. CREATE OR REPLACE USER susan

    • C. CREATE NEW USER susan DEFAULT,

    • D. CREATE USER susan INDENTIFIED BY blue

    • E. CREATE NEW USER susan IDENTIFIED BY blue

    • F. CREATE OR REPLACE USER susan IDENTIFIED BY blue;

    Correct Answer
    A. D. CREATE USER susan INDENTIFIED BY blue
  • 20. 

    What is true about the WITH GRANT OPTION clause?

    • A. It allows a grantee DBA privileges.

    • B. It is required syntax for object privileges.

    • C. It allows privileges on specified columns of tables.

    • D. It is used to grant an object privilege on a foreign key column.

    • E. It allows the grantee to grant object privileges to other users and roles.

    Correct Answer
    A. E. It allows the grantee to grant object privileges to other users and roles.
    Explanation
    The WITH GRANT OPTION clause allows the grantee to grant object privileges to other users and roles.

    Rate this question:

  • 21. 

    Evaluate the set of SQL statements:             CREATE TABLE dept             (deptbi NUMBER (2)             dname VARCHAR2(14),             Ioc VARCHAR2(13));             ROLLBACK;             DESCRIBE DEPT What is true about the set?

    • A. The DESCRIBE DEPT statement displays the structure of the DEPT table

    • B. The ROLLBACK statement frees the storage space occupied by the DEPT table

    • C. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist

    • D. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.

    Correct Answer
    A. A. The DESCRIBE DEPT statement displays the structure of the DEPT table
    Explanation
    The correct answer is A. The DESCRIBE DEPT statement displays the structure of the DEPT table. This is because the DESCRIBE statement is used to retrieve information about the structure of a table, including its columns and data types. In this case, since the table DEPT was created before the ROLLBACK statement, it still exists and can be described. The ROLLBACK statement does not affect the existence or structure of the table.

    Rate this question:

  • 22. 

    Which SELECT statement will get the result 'elloworld' from the string 'HelloWorld'?

    • A. SELECT SUBSTR ('HelloWorld',1) FROM dual;

    • B. SELECT INITCAP(TRIM('HellowWorld', 1,1) FROM dual

    • C. SELECT LOWER (SUBSTR ('HellowWorld', 2,1) FROM dual

    • D. SELECT LOWER (SUBSTR('HellowWorld', 2,1) FROM dual

    • E. SELECT LOWER (TRIM ('H' FROM 'Hello World')) FROM dual

    Correct Answer
    A. E. SELECT LOWER (TRIM ('H' FROM 'Hello World')) FROM dual
    Explanation
    The correct answer is E. SELECT LOWER (TRIM ('H' FROM 'Hello World')) FROM dual. This statement uses the TRIM function to remove the 'H' from the string 'Hello World', and then applies the LOWER function to convert the remaining string to lowercase. The result is 'elloworld'.

    Rate this question:

  • 23. 

    Examine the subquery: SELECT last_name FROM employees WHERE salary IN (SELECT MAX(salary) FROM employees GROUP BY department_id); Which statement is true?  

    • The SELECT statement is syntactically accurate.

    • The SELECT statement does not work because there is no HAVING clause.

    • The SELECT statement does not work because the column specified in the GROUP BY clause is not in the SELECT list.

    • The SELECT statement does not work because the GROUP BY clause should be in the main query and not in the subquery.

    Correct Answer
    A. The SELECT statement is syntactically accurate.
    Explanation
    The given SELECT statement is syntactically accurate because it follows the correct syntax for subqueries. The subquery is used to find the maximum salary for each department, and then the outer query selects the last names of employees whose salary matches the maximum salary for their respective department.

    Rate this question:

  • 24. 

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

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

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

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

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

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

    Correct Answer
    A. A. SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual;
    Explanation
    The correct answer is A. SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual;

    This is the correct answer because the TO_CHAR function is used to convert the system date (SYSDATE) into a character string, and the 'yyyy' format specifier is used to extract the year from the date in the format "1998". The FROM dual statement is used to select from the dummy table dual, which is commonly used in Oracle queries.

    Rate this question:

  • 25. 

    Which object privileges can be granted on a view?

    • A. none

    • B. DELETE, INSERT,SELECT

    • C. ALTER, DELETE, INSERT, SELECT

    • D. DELETE, INSERT, SELECT, UPDATE

    Correct Answer
    A. D. DELETE, INSERT, SELECT, UPDATE
    Explanation
    Object privileges refer to the permissions that can be granted on a database object, such as a table or a view. In this case, the correct answer is D. DELETE, INSERT, SELECT, UPDATE, which means that all four privileges can be granted on a view. This means that the user with these privileges can perform operations such as deleting, inserting, selecting, and updating data on the view.

    Rate this question:

  • 26. 

    You want to create a cursor that can be used several times in a block. Selecting a different active set each time that it is opened. Which type of cursor do you create?

    • A. A cursor for loop.

    • B. A multiple selection cursor.

    • C. A cursor for each active set.

    • D. A cursor that uses parameters.

    Correct Answer
    A. D. A cursor that uses parameters.
    Explanation
    A cursor that uses parameters allows for flexibility in selecting different active sets each time it is opened. By using parameters, the cursor can be customized to retrieve data based on specific criteria or conditions. This type of cursor can be reused multiple times within a block, making it an efficient and versatile option for selecting different sets of data.

    Rate this question:

  • 27. 

    The DBA issues this SQL command: CREATE USER scott  INDENTIFIED by tiger; What privileges does the user Scott have at this point?

    • A. no privileges

    • B. only the SELECT privilege

    • C. only the CONNECT privilege

    • D. all the privileges of a default user

    Correct Answer
    A. A. no privileges
    Explanation
    When the DBA issues the SQL command "CREATE USER scott IDENTIFIED by tiger;", it creates a new user named "scott" with the password "tiger". However, by default, this user does not have any privileges assigned to it. Therefore, the correct answer is A. no privileges.

    Rate this question:

  • 28. 

    Which SQL statement displays the date March 19, 2001 in a format that appears as "Nineteenth of March 2001 12:00:00 AM"?

    • A. SELECT TO_CHAR(TO_DATE("19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;

    • B. SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'Ddspth "of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;

    • C. SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month YYYY HH:MI:SS AM') NEW_DATE

    • D. SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY), 'fmDdspth "of" Month YYYY

    Correct Answer
    A. B. SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'Ddspth "of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;
    Explanation
    The correct answer is B. This SQL statement uses the TO_DATE function to convert the date '19-Mar-2001' into the desired format. The TO_CHAR function is then used to format the date as 'Ddspth "of" Month YYYY fmHH:MI:SS AM', which results in the output "Nineteenth of March 2001 12:00:00 AM". The 'fm' format modifier is used to remove leading zeros and spaces. The NEW_DATE alias is used to give the resulting column a name. The FROM dual clause is used to specify that the query is not retrieving data from a table, but rather performing a calculation.

    Rate this question:

  • 29. 

    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 table.  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 employees e, departments d WHERE department_id = d.department_id;

    • D. MODIFY VIEW emp_depat_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 emplouee_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 CRATE VIEW command with a new column list to modify a view.

    Correct Answer
    A. E. CREATE OR REPLACE VIEW emp_dept_vu AS SELECT emplouee_id, employee_ name, Department_name, manager _id FROM employees e, departments d WHERE e.department_id=d.department_id;
  • 30. 

    Under which situation it is necessary to use an explicit cursor?

    • When any DML or select statement is used in a PL/SQL block?

    • When a delete statement in a PL/SQL block deletes more than one row.

    • When a select statement in a PL/SQL block is more than one row.

    • When an update statement in a PL/SQL block has to modify more than one row.

    Correct Answer
    A. When a select statement in a PL/SQL block is more than one row.
    Explanation
    When a select statement in a PL/SQL block returns more than one row, it is necessary to use an explicit cursor. An explicit cursor allows the programmer to retrieve and process multiple rows from the result set returned by the select statement. It provides more control and flexibility compared to the implicit cursor, which can only handle a single row result set. By using an explicit cursor, the programmer can loop through the result set and perform operations on each individual row.

    Rate this question:

  • 31. 

    Which SQL statement returns a numeric value?

    • A. SELECT ROUND(hire_date)FROM EMP;

    • B. SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;

    • C. SELECT sysdate-hire_date FROM EMP;

    • D. SELECT TO_NUMBER(hire_date + 7)FROM EMP;

    Correct Answer
    A. C. SELECT sysdate-hire_date FROM EMP;
    Explanation
    The SQL statement in option C, "SELECT sysdate-hire_date FROM EMP," returns the result of subtracting the hire_date from the current date (sysdate). This subtraction operation will yield a numeric value representing the number of days between the hire_date and the current date.

    Rate this question:

  • 32. 

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

    Examine the structure of the STUDENT table: NAME NULL? TYPE STUDENT_ID NOT NULL NUMBER(3) NAME NOT NULL VARCHAR2(25) ADDRESS VARCHAR2(50) GRADUATION DATE Graduation column is a foreign key column to the graduate table. Examine the data in the GRADE DATE table: Graduation 20-jan-1999 12-may-1999 19-jan-2000 25-may-2000 13-jan-2001 29-may-2001 Which update statement produces the following error: ORA-02291 integrity constraint(sys_c23) violated parent key not found?

    • A. UPDATE student SET stud-id=999, graduation= ’29-MAY-2001’ WHERE stud-id=101;’

    • B. UPDATE student SET name= ‘Smith’, graduation= ’29-MAY-2001’ WHERE stud-id=101;

    • C. UPDATE student SET name= ‘Smith’, graduation= ‘15-AUG-2000’ WHERE stud-id=101

    • D. UPDATE student SET stud-id=NULL, address= ‘100 Main Street’ WHERE graduation= ‘20-JAN-1999’

    Correct Answer
    A. C. UPDATE student SET name= ‘Smith’, graduation= ‘15-AUG-2000’ WHERE stud-id=101
    Explanation
    The update statement in option C tries to update the name and graduation date of a student with student_id 101. However, the graduation date '15-AUG-2000' does not exist in the GRADE DATE table, which is referenced by the foreign key constraint on the graduation column in the STUDENT table. This violates the integrity constraint and results in the error ORA-02291.

    Rate this question:

  • 34. 

    EMPLOYEES and DEPARTMENTS data: EMPLOYEES EMPLOYEE_ID EMP_NAME DEPT_ID MGR_ID JOB_ID SALARY 101 Smith 20 120 SA_REP 4000 102 Martin 10 105 CLERK 2500 103 Chris 20 120 IT_ADMIN 4200 104 John 30 108 HR_CLERK 2500 105 Diana 30 108 IT_ADMIN 5000 106 Smith 40 110 AD_ASST 3000 108 Jennifer 30 110 HR_DIR 6500 110 Bob 40 EX_DIR 8000 120 Ravi 20 110 SA_DIR 6500 DEPARTMENTS DEPARTMENT_ID DEPARTMENT_NAME 10 Admin 20 Education 30 IT On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID managers and refers to the EMPLOYEE_ID. On the DEPARTMENTS table DEPARTMENT_ID is the primary key. Evaluate this UPDATE statement. UPDATE employees SET mgr_id = (SELECT mgr_id FROM employees WHERE dept_id= (SELECT department_id FROM departments WHERE department_name = 'Administration')), Salary = (SELECT salary FROM employees WHERE emp_name = 'Smith') WHERE job_id = 'IT_ADMIN'; What happens when the statement is executed?

    • A. The statement executes successfully, leaves the manager ID as the existing value, and changes the salary to 4000 for the employees with ID 103 and 105.

    • B. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 4000 for the employees with ID 103 and 105.

    • C. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 3000 for the employees with ID 103 and 105.

    • D. The statement fails because there is more than one row matching the employee name Smith.

    • E. The statement fails because there is more than one row matching the IT_ADMIN job ID in the EMPLOYEES table.

    • F. The statement fails because there is no 'Administration' department in the DEPARTMENTS table.

    Correct Answer
    A. D. The statement fails because there is more than one row matching the employee name Smith.
    Explanation
    The UPDATE statement is attempting to update the manager ID and salary for employees with the job ID 'IT_ADMIN'. However, the subquery in the SET clause that tries to find the manager ID for the 'Administration' department returns more than one row with the employee name 'Smith'. Therefore, the statement fails because it cannot determine which 'Smith' employee's manager ID to update.

    Rate this question:

  • 35. 

    Examine the data in the EMPLOYEES and DEPARTMENTS tables. You want to retrieve all employees' last names, along with their manager's last names and their department names. Which query would you use?

    • A. SELECT last_name, manager_id, department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id);

    • B. SELECT e.last_name, m.last_name, department_name FROM employees e LEFT OUTER JOIN employees m on ( e.managaer_id = m.employee_id) LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);

    • C. SELECT e.last_name, m.last_name, department_name FROM employees e RIGT OUTER JOIN employees m on ( e.manager_id = m.employee_id) LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);

    • D. SELECT e.last_name, m.last_name, department_name FROM employees e LEFT OUTER JOIN employees m on ( e.manager_id = m.employee_id) RIGT OUTER JOIN departments d ON (e.department_id = d.department_id);

    • E. SELECT e.last_name, m.last_name, department_name FROM employees e RIGHT OUTER JOIN employees m on ( e.manager_id = m.employee_id) RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id)

    • F. SELECT last_name, manager_id, department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) ;

    Correct Answer
    A. B. SELECT e.last_name, m.last_name, department_name FROM employees e LEFT OUTER JOIN employees m on ( e.managaer_id = m.employee_id) LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
    Explanation
    The correct answer is B because it uses LEFT OUTER JOIN to retrieve all employees' last names along with their manager's last names, and LEFT OUTER JOIN again to retrieve the department names. The condition for the first LEFT OUTER JOIN is that the manager_id of the employee matches the employee_id of the manager, and the condition for the second LEFT OUTER JOIN is that the department_id of the employee matches the department_id of the department. This query ensures that all employees are included in the result, even if they do not have a manager or a department.

    Rate this question:

  • 36. 

    Refer to the SQL codes below: SELECT manager_id, last_name, hire_date, salary, AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg FROM employees; What has been achieved?

    • A. because of a syntax problem, no row will be returned

    • B. it calculates, for each employee in the employees table, the average salary of the employees reporting to his/her respective manager

    • C. it calculates, for each employee in the employees table, the average salary of the employees reporting to his/her respective manager who were hired just before the employee

    • D. it calculates, for each employee in the employees table, the average salary of the employees reporting to the same manager who were hired in the range just before through just after the employee

    • E. it calculates, for each employee in the employees table, the average salary of the employees reporting to his/her respective manager who were hired just after the employee

    Correct Answer
    A. D. it calculates, for each employee in the employees table, the average salary of the employees reporting to the same manager who were hired in the range just before through just after the employee
    Explanation
    The SQL code is using the AVG() function with the OVER clause and the ROWS BETWEEN clause. This allows the calculation of a moving average for each employee based on the employees reporting to the same manager. The ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING specifies that the average should be calculated for the employees hired in the range just before through just after the employee. Therefore, option D is the correct answer.

    Rate this question:

  • 37. 

    Which statement correctly describes SQL and /SQL*Plus?

    • A. Both SQL and /SQL*plus allow manipulation of values in the database.

    • B. /SQL*Plus recognizes SQL statements and sends them to the server; SQL is the Oracle proprietary interface for executing SQL statements.

    • C. /SQL*Plus is a language for communicating with the Oracle server to access data; SQL recognizes SQL statements and sends them to the server.

    • D. SQL manipulates data and table definitions in the database; /SQL*Plus does not allow manipulation of values in the database.

    Correct Answer
    A. D. SQL manipulates data and table definitions in the database; /SQL*Plus does not allow manipulation of values in the database.
  • 38. 

     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)
    A. A. You cannot use IN operator in a condition that involves an outerjoin
    A. E. In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outerjoin
    A. F. You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator
    Explanation
    The first statement (A) is true because the IN operator cannot be used in a condition that involves an outer join. The second statement (E) is true because in the WHERE condition, the (+) symbol is used following the name of the column in the table without matching rows to perform an outer join. The third statement (F) is true because a condition involved in an outer join cannot be linked to another condition using the OR operator.

    Rate this question:

  • 39. 

    Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30) JOB_ID NUMBER\ SAL NUMBER MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of the DEPARTMENTS table You created a sequence called EMP_ID_SEQ in order to populate sequential values for the EMPLOYEE_ID column of the EMPLOYEES table. Which two statements regarding the EMP_ID_SEQ sequence are true? (Choose two.)

    • A. You cannot use the EMP_ID_SEQ sequence to populate the JOB_ID column.

    • B. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID column.

    • C. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table.

    • D. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.

    • E. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES table.

    • F. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID column.

    Correct Answer(s)
    A. C. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table.
    A. D. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.
    Explanation
    The given statements state that the EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table (C) and any other column of NUMBER data type in the schema can use the EMP_ID_SEQ sequence (D). This means that changes or modifications made to the EMPLOYEES table will not impact the EMP_ID_SEQ sequence, and any other column in the schema that is of NUMBER data type can use the sequence for populating its values.

    Rate this question:

  • 40. 

    Which two are attributes of /SQL*Plus? (Choose two)

    • A. /SQL*Plus commands cannot be abbreviated.

    • B. /SQL*Plus commands are accesses from a browser

    • C. /SQL*Plus commands are used to manipulate data in tables.

    • D. /SQL*Plus commands manipulate table definitions in the database.

    • E. /SQL*Plus is the Oracle proprietary interface for executing SQL statements.

    Correct Answer(s)
    A. B. /SQL*Plus commands are accesses from a browser
    A. D. /SQL*Plus commands manipulate table definitions in the database.
    Explanation
    The correct answers are B and D. /SQL*Plus commands are not accessed from a browser, but rather from the command line interface. They are used to manipulate table definitions in the database. /SQL*Plus is the Oracle proprietary interface for executing SQL statements.

    Rate this question:

Quiz Review Timeline (Updated): May 12, 2023 +

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

  • Current Version
  • May 12, 2023
    Quiz Edited by
    ProProfs Editorial Team
  • Nov 28, 2011
    Quiz Created by
    Annstefi
Back to Top Back to top
Advertisement
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.