Oracle Final Test Questions And Answers

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

The SQL statement "SELECT * FROM PRODUCTS ORDER BY price, product_name" will sort the results first by the "price" column in numerical order and then by the "product_name" column in alphabetical order.

Explanation

The SQL statement "SELECT * FROM PRODUCTS ORDER BY price, product_name" will sort the results first by the "price" column in numerical order and then by the "product_name" column in alphabetical order.

2. 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)

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

7. which order does the Oracle Server evaluate clauses?

HAVING, WHERE, GROUP BY

WHERE, GROUP BY, HAVING

GROUP BY, HAVING, WHERE

WHERE, HAVING, GROUP BY

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.

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.

8. 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 viewTO manager;GRANT manager TO BLAKE,CLARK;;

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

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

not-available-via-ai

Explanation

not-available-via-ai

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.

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.

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.

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

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.

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.

11. Which constraint can be defines only at the column level?

A. UNIQUE

B. NOT NULL

C. CHECK

D. PRIMARY KEY

E. FOREIGN KEY

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.

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.

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;

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.

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.

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;

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.

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.

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

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.

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.

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_USERSWHERE USER_NAME = 'SCOTT'AND TABLE NAME = 'DEPT';

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.

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.

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

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.

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.

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

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.

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.

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.

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.

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.

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;

not-available-via-ai

Explanation

not-available-via-ai

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.

The WITH GRANT OPTION clause 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.

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.

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.

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.

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

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

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

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.

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.

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.

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;

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.

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.

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

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.

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.

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.

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.

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.

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

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.

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.

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. SELECTTO_CHAR(TO_DATE("19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth"of" Month YYYY fmHH:MI:SS AM') NEW_DATEFROM dual;

B. SELECTTO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'Ddspth"of" Month YYYY fmHH:MI:SS AM') NEW_DATEFROM dual;

C. SELECTTO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" MonthYYYYHH:MI:SS AM') NEW_DATE

D. SELECTTO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY), 'fmDdspth "of" MonthYYYY

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.

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.

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 ASSELECT employee_id, employee_nameDepartment_name, manager_idFROM employees e, departments dWHERE department_id = d.department_id;

D. MODIFY VIEW emp_depat_vu ASSELECT employee_id, employee_name,Department_name, manager_idFROM employees e, departments dWHERE e.department_id = d.department_id;

E. CREATE OR REPLACE VIEW emp_dept_vu ASSELECT emplouee_id, employee_ name,Department_name, manager _idFROM employees e, departments dWHERE 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.

not-available-via-ai

Explanation

not-available-via-ai

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.

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.

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.

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;

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.

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.

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

D. To join two tables through an equijoin, the columns in the join condition must beprimary key and foreign key columns.

E. You can join n tables (all having single column primary keys) in a SQL statement byspecifying a minimum of n-1 join conditions.

not-available-via-ai

Explanation

not-available-via-ai

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 studentSET stud-id=999,graduation= ’29-MAY-2001’WHERE stud-id=101;’

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

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

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

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.

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.

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, andchanges the salary to 4000 for the employees with ID 103 and 105.

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

C. The statement executes successfully, changes the manager ID to NULL, and changesthe 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 nameSmith.

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

F. The statement fails because there is no 'Administration' department in theDEPARTMENTS table.

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.

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.

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_nameFROM employees eFULL OUTER JOIN departments d ON (e.department_id = d.department_id);

B. SELECT e.last_name, m.last_name, department_nameFROM employees eLEFT 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_nameFROM employees eRIGT 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_nameFROM employees eLEFT 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_nameFROM employees eRIGHT 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_nameFROM employees eJOIN departments d ON (e.department_id = d.department_id) ;

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.

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.

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

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.

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.

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 theOracle 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 allowmanipulation of values in the database.

not-available-via-ai

Explanation

not-available-via-ai

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

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.

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.

Submit

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

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

E. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEEStable.

F. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_IDcolumn.

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.

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.

Submit

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.

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.

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.

Submit