R4RIN – MCQs, Mock Tests | Oracle DB Oracle DB Mcq Question Set 5

You plan to use static database registration for a new listener when you create it. What could be the two reasons for this? (Choose two.)

1. More than one database is to be registered with the listener.
2. The users will connect the database by using the host naming method.
3. The Oracle Enterprise Manager is to be used to monitor an Oracle9i database.
4. The database that is to be registered with the listener is configured in shared server mode.

✅ Correct Answer: 3

Which three pieces of information are to be mandatorily provided while creating a new listener using Enterprise Manager Database Control? (Choose three.)

1. the port used by the listener
2. the protocol used by the listener
3. the server name where the listener runs
4. the log file and trace file destination for the listener

✅ Correct Answer: 1

You have been recently hired as a database administrator. Your senior manager asks you to study the production database server and submit a report on the settings done by the previous DBA. While observing the server settings, you find that the following parameter has been set in the parameter file of the database: REMOTE_OS_AUTHENT = TRUE What could have been the reason to set this parameter as TRUE?

1. to enable operating system authentication for a remote client
2. to restrict the scope of administration to identical operating systems
3. to allow the start up and shut down of the database from a remote client
4. to enable the administration of the operating system from a remote client

✅ Correct Answer: 1

Which naming method uses the tnsnames.ora file to store the connect descriptor used by the client while connecting to the database instance from a remote machine?

1. host naming method
2. local naming method
3. external naming method
4. directory naming method

✅ Correct Answer: 2

Your database is open and users are connected using the LISTENER listener. The new DBA of the system stops the listener by using the following command: LSNRCTL> STOP What would happen to the sessions that are presently connected to the database instance?

1. The sessions are able to perform only queries.
2. The sessions are not affected and continue to function normally.
3. The active transactions are rolled back and the sessions get terminated.
4. The sessions are not allowed to perform any operations till the listener is started.

✅ Correct Answer: 2

You need to extract details of those products in the SALES table where the PROD_ID column contains the string '_D123'. Which WHERE clause could be used in the SELECT statement to get the required output?

1. WHERE prod_id LIKE '%_D123%' ESCAPE '_'
2. WHERE prod_id LIKE '%\_D123%' ESCAPE ''
3. WHERE prod_id LIKE '%_D123%' ESCAPE '%_'
4. WHERE prod_id LIKE '%\_D123%' ESCAPE '\_'

✅ Correct Answer: 2

Which two statements are true regarding single row functions?

1. They a ccept only a single argument.
2. They c an be nested only to two levels.
3. Arguments can only be column values or constants.
4. They a lways return a single result row for every row of a queried table.

✅ Correct Answer: 4

Which SQL statements would display the value 1890.55 as $1,890.55?

1. SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;
2. SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL;
3. SELECT TO_CHAR(1890.55,'$99,999D99') FROM DUAL;
4. SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;

✅ Correct Answer: 1

Which two statements are true regarding the USING and ON clauses in table joins?

1. Both USING and ON clauses can be used for equijoins and nonequijoins.
2. A maximum of one pair of columns can be joined between two tables using the ON clause.
3. The ON clause can be used to join tables on columns that have different names but compatible data types.
4. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.

✅ Correct Answer: 3

Which statement is true regarding the INTERSECT operator?

1. It ignores NULL values.
2. Reversing the order of the intersected tables alters the result.
3. The names of columns in all SELECT statements must be identical.
4. The number of columns and data types must be identical for all SELECT statements in the query.

✅ Correct Answer: 4

You need to produce a report where each customer's credit limit has been incremented by $1000. In the output, t he customer's last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in uppercase . Which statement would accomplish this requirement?

1. SELECT cust_last_name Name, cust_credit_limit + 1000 "New Credit Limit" FROM customers;
2. SELECT cust_last_name AS Name, cust_credit_limit + 1000 AS New Credit Limit FROM customers;
3. SELECT cust_last_name AS "Name", cust_credit_limit + 1000 AS "New Credit Limit" FROM customers;
4. SELECT INITCAP(cust_last_name) "Name", cust_credit_limit + 1000 INITCAP("NEW CREDIT LIMIT") FROM customers;

✅ Correct Answer: 3

Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level.Which query would give the required result?

1. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers;
2. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers;
3. SELECT DISTINCT cust_income_level ' ' cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers;
4. SELECT cust_income_level ' ' cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers;

✅ Correct Answer: 3

Evaluate the following query: SQL> SELECT promo_name q'{'s start date was }' promo_begin_date AS "Promotion Launches" FROM promotions; What would be the outcome of the above query?

1. It produces an error because flower braces have been used.
2. It produces an error because the data types are not matching.
3. It executes successfully and introduces an 's at the end of each promo_name in the output.
4. It executes successfully and displays the literal " {'s start date was } " for each row in the output.

✅ Correct Answer: 3

Examine the structure of the PROMOTIONS table: name Null Type PROMO_ID NOT NULL NUMBER(6) PROMO_NAME NOT NULL VARCHAR2(30) PROMO_CATEGORY NOT NULL VARCHAR2(30) PROMO_COST NOT NULL NUMBER(10,2) The management wants to see a report of unique promotion costs in each promotion category. Which query would achieve the required result?

1. SELECT DISTINCT promo_cost, promo_category FROM promotions;
2. SELECT promo_category, DISTINCT promo_cost FROM promotions;
3. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
4. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

✅ Correct Answer: 4

Evaluate the following query: SELECT INTERVAL '300' MONTH, INTERVAL '54-2' YEAR TO MONTH, INTERVAL '11:12:10.1234567' HOUR TO SECOND FROM dual; What is the correct output of the above query?

1. +25-00 , +54-02, +00 11:12:10.123457
2. +00-300, +54-02, +00 11:12:10.123457
3. +25-00 , +00-650, +00 11:12:10.123457
4. +00-300 , +00-650, +00 11:12:10.123457

✅ Correct Answer: 1

Which three statements are true regarding the data types in Oracle Database 10g/11g?

1. Only one LONG column can be used per table.
2. A TIMESTAMP data type column stores only time values with fractional seconds.
3. The BLOB data type column is used to store binary data in an operating system file.
4. The minimum column width that can be specified for a VARCHAR2 data type column is one.

✅ Correct Answer: 1

What does the FORCE option for creating a view do?

1. creates a view with constraints
2. creates a view even if the underlying parent table has constraints
3. creates a view in another schema even if you don't have privileges
4. creates a view regardless of whether or not the base tables exist

✅ Correct Answer: 4

What are two reasons to create synonyms?

1. You have too many tables.
2. Your tables are too long.
3. You want to work on your own tables.
4. You want to use another schema's tables.

✅ Correct Answer: 4

In which three cases would you use the USING clause?

1. You want to create a nonequijoin.
2. The tables to be joined have multiple NULL columns.
3. The tables to be joined have columns of the same name and different data types.
4. The tables to be joined have columns with the same name and compatible data types.

✅ Correct Answer: 4

A SELECT statement can be used to perform these three functions: 1. Choose rows from a table. 2. Choose columns from a table. 3. Bring together data that is stored in different tables by creating a link between them. Which set of keywords describes these capabilities?

1. difference, projection, join
2. selection, projection, join
3. selection, intersection, join
4. intersection, projection, join

✅ Correct Answer: 2

Evaluate this SQL statement: SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID, d.DEPARTMENT_NAME FROM EMPLOYEES e, DEPARTMENTS d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID; In the statement, which capabilities of a SELECT statement are performed?

1. selection, projection, join
2. selection, intersection, join
3. intersection, projection, join
4. difference, projection, product

✅ Correct Answer: 1

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

1. The value displayed in the CALC_VALUE column will be lower.
2. The value displayed in the CALC_VALUE column will be higher.
3. There will be no difference in the value displayed in the CALC_VALUE column.
4. An error will be reported.

✅ Correct Answer: 3

Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?

1. SELECT ename, salary*12 'Annual Salary' FROM employees;
2. SELECT ename, salary*12 "Annual Salary" FROM employees;
3. SELECT ename, salary*12 AS Annual Salary FROM employees;
4. SELECT ename, salary*12 AS INITCAP("ANNUAL SALARY") FROM employees

✅ Correct Answer: 2

Evaluate this SQL statement: SELECT ename, sal, 12*sal+100 FROM emp; The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12"?

1. No change is required to achieve the desired results.
2. SELECT ename, sal, 12*(sal+100) FROM emp;
3. SELECT ename, sal, (12*sal)+100 FROM emp;
4. SELECT ename, sal+100,*12 FROM emp;

✅ Correct Answer: 2

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

1. SELECT dear customer, customer_name,
2. SELECT "Dear Customer", customer_name || ',' FROM customers;
3. SELECT 'Dear Customer ' || customer_name ',' FROM customers;
4. SELECT 'Dear Customer ' || customer_name || ',' FROM customers;

✅ Correct Answer: 4

Which two are attributes of iSQL*Plus?

1. iSQL*Plus commands cannot be abbreviated.
2. iSQL*Plus commands are accessed from a browser.
3. iSQL*Plus commands are used to manipulate data in tables.
4. iSQL*Plus is the Oracle proprietary interface for executing SQL statements.

✅ Correct Answer: 4

Which is an iSQL*Plus command?

1. INSERT
2. UPDATE
3. SELECT
4. DESCRIBE

✅ Correct Answer: 4

Which are iSQL*Plus commands?

1. INSERT
2. UPDATE
3. SELECT
4. DESCRIBE

✅ Correct Answer: 4

Which two statements are true about constraints?

1. The UNIQUE constraint does not permit a null value for the column.
2. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.
3. The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index.
4. The NOT NULL constraint ensures that null values are not permitted for the column.

✅ Correct Answer: 2

Which three statements correctly describe the functions and use of constraints?

1. Constraints provide data independence.
2. Constraints enforce rules at the view level.
3. Constraints enforce rules at the table level.
4. Constraints prevent the deletion of a table if there are dependencies.

✅ Correct Answer: 4

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

1. 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);
2. CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
3. 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));
4. CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));

✅ Correct Answer: 2

Which view should a user query to display the columns associated with the constraints on a table owned by the user?

1. USER_CONSTRAINTS
2. USER_OBJECTS
3. ALL_CONSTRAINTS
4. USER_CONS_COLUMNS

✅ Correct Answer: 4

You need to design a student registration database that contains several tables storing academic information. The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student's grades. Both of the tables have a column named STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a primary key. You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table. Which statement creates the foreign key?

1. CREATE TABLE student_grades (student_id NUMBER(12), semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY students(student_id));
2. CREATE TABLE student_grades (student_id NUMBER(12), semester_end DATE, gpa NUMBER(4,3), student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
3. CREATE TABLE student_grades (student_id NUMBER(12), semester_end DATE, gpa NUMBER(4,3), CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id));
4. CREATE TABLE student_grades (student_id NUMBER(12), semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));

✅ Correct Answer: 4

You need to load information about new customers from the NEW_CUST table into the tables CUST and CUST_SPECIAL. If a new customer has a credit limit greater than 10,000, then the details have to be inserted into CUST_SPECIAL. All new customer details have to be inserted into the CUST table. Which technique should be used to load the data most efficiently?

1. external table
2. the MERGE command
3. the multitable INSERT command
4. INSERT using WITH CHECK OPTION

✅ Correct Answer: 3

Which three tasks can be performed using regular expression support in Oracle Database 10g?

1. It can be used to concatenate two strings.
2. It can be used to find out the total length of the string.
3. It can be used for string manipulation and searching operations.
4. It can be used to format the output for a column or expression having string data

✅ Correct Answer: 3

Evaluate the CREATE TABLE statement: CREATE TABLE products (product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY, product_name VARCHAR2(15)); Which statement is true regarding the PROD_ID_PK constraint?

1. It would be created only if a unique index is manually created first.
2. It would be created and would use an automatically created unique index.
3. It would be created and would use an automatically created nonunique index.
4. It would be created and remains in a disabled state because no index is specified in the command.

✅ Correct Answer: 2

Which two statements are true?

1. The USER_SYNONYMS view can provide information about private synonyms.
2. The user SYSTEM owns all the base tables and user-accessible views of the data dictionary.
3. All the dynamic performance views prefixed with V$ are accessible to all the database users.
4. DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.

✅ Correct Answer: 4

Which two statements are true regarding the execution of the correlated subqueries?

1. The nested query executes after the outer query returns the row.
2. The nested query executes first and then the outer query executes.
3. The outer query executes only once for the result returned by the inner query.
4. Each row returned by the outer query is evaluated for the results returned by the inner query.

✅ Correct Answer: 4

OE and SCOTT are the users in the database. The ORDERS table is owned by OE. Evaluate the statements issued by the DBA in the following sequence: CREATE ROLE r1; GRANT SELECT, INSERT ON oe.orders TO r1; GRANT r1 TO scott; GRANT SELECT ON oe.orders TO scott; REVOKE SELECT ON oe.orders FROM scott; What would be the outcome after executing the statements?

1. SCOTT would be able to query the OE.ORDERS table.
2. SCOTT would not be able to query the OE.ORDERS table.
3. The REVOKE statement would remove the SELECT privilege from SCOTT as well as from the role R1.
4. The REVOKE statement would give an error because the SELECT privilege has been granted to the role R1.

✅ Correct Answer: 1

Evaluate the following SQL statement: ALTER TABLE hr.emp SET UNUSED (mgr_id); Which statement is true regarding the effect of the above SQL statement?

1. Any synonym existing on the EMP table would have to be re-created.
2. Any constraints defined on the MGR_ID column would be removed by the above command.
3. Any views created on the EMP table that include the MGR_ID column would have to be dropped and re-created.
4. Any index created on the MGR_ID column would continue to exist until the DROP UNUSED COLUMNS command is executed.

✅ Correct Answer: 2

EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table?

1. UPDATE empdet SET ename = 'Amit' WHERE empno = 1234;
2. DELETE FROM empdet WHERE ename LIKE 'J%';
3. CREATE VIEW empvu AS SELECT * FROM empdept;
4. CREATE INDEX empdet_idx ON empdet(empno);

✅ Correct Answer: 3

In which scenario would you use the ROLLUP operator for expression or columns within a GROUP BY clause?

1. to find the groups forming the subtotal in a row
2. to find the groups forming the subtotal in a row
3. to create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from right to left for calculating the subtotals
4. to create a grouping for expressions or columns specified within a GROUP BY clause in all possible directions, which is cross-tabular report for calculating the subtotals

✅ Correct Answer: 3

What are the two subtypes of the IF conditional statement in PL/SQL?

1. if-then-endif and if-then-else
2. if-then-else and if-then-elseif
3. if-then-else and if-then-elseif-endif
4. if-then-else and if-then-elseif-then-else

✅ Correct Answer: 4

What are the two variables supported by PL/SQL?

1. Explicit and implicit variables
2. Scalar and composite variables
3. Primary and default variables
4. Scalar and user-defined variables

✅ Correct Answer: 2

Oracle supports all of the following types of collections except for which one?

1. VARRAYS
2. Nested array
3. Nested table
4. Associative array

✅ Correct Answer: 2

Which of the following identifiers can include any printable characters, including spaces?

1. Quoted identifiers
2. Predefined identifiers
3. Valid identifiers
4. Invalid identifiers

✅ Correct Answer: 1

Which of the following types of message output contains a chronological log of errors, initialization parameter settings, and administration operations, and also records values for overwritten control filerecords?

1. RMAN messages
2. alert_SID.log
3. sbtio.log
4. Oracle trace file

✅ Correct Answer: 2

Which of the following statements is used to insert, update, or delete bulk data?

1. FOR Loop statement
2. BULK COLLECT INTO statement
3. BULK statement
4. FORALL statement

✅ Correct Answer: 4

Which of the following exceptions is raised when a user tries to divide a number by zero?

1. VALUE_ERROR
2. OUT_OF_RANGE
3. ZERO_DIVIDE
4. SELF_IS_NULL

✅ Correct Answer: 3

All of the following can be the causes of raising an exception except for which one?

1. A throw statement has executed.
2. An asynchronous exception has occurred.
3. An abnormal execution condition has been synchronously detected.
4. A catch statement has executed.

✅ Correct Answer: 4