Computer Knowledge

Database and SQL

4,213 Questions

Master structured query language commands, database joins, table constraints, and alias generation. This section covers relational database management concepts and query outputs essential for technical aptitude. These technical questions feature prominently in banking IT officer exams and computer knowledge sections.

SQL queries and aliasesDatabase table constraintsDatabase joins and transformationsStored procedures and functions

Database and SQL Questions

Multiple choice technology databases

Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She granted SELECT privilege to Scott on this view. Which option enables Scott to eliminate the need to qualify the view with the name MARY.EMP_DEPT_LOC_VU each time the view is referenced?

  1. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE PRIVATE SYNONYM EDL_VU FOR mary.EMP DEPT_LOC_VU; then he can prefix the columns with this synonym.

  2. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE SYNONYM EDL_VU FOR mary.EMP_DEPT_LOC_VU; then he can prefix the columns with this synonym.

  3. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE LOCAL SYNONYM EDL_VU FOR mary.EMP DEPT_LOC_VU; then he can prefix the columns with this synonym.

  4. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE SYNONYM EDL_VU ON mary(EMP_DEPT_LOC_VU); then he can prefix the columns with this synonym.

  5. Scott cannot create a synonym because synonyms can be created only for tables.

  6. Scott cannot create any synonym for Mary's view. Mary should create a private synonym for the view and grant SELECT privilege on that synonym to Scott.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To solve this question, the user needs to know about synonyms in Oracle and how they can be used to eliminate the need to qualify the view name with the owner name.

Option A is incorrect because CREATE PRIVATE SYNONYM is not a valid command in Oracle.

Option B is correct because Scott can create a public synonym for the EMP_DEPT_LOC_VU view using the CREATE SYNONYM command. Once the synonym is created, he can use it to reference the view without specifying the owner name each time.

Option C is incorrect because CREATE LOCAL SYNONYM is not a valid command in Oracle.

Option D is incorrect because the syntax of the CREATE SYNONYM command is incorrect. The correct syntax is CREATE SYNONYM synonym_name FOR object_name, where object_name is the name of the view or table.

Option E is incorrect because synonyms can be created not only for tables but also for views.

Option F is incorrect because Scott can create a public synonym for the view. Mary does not need to create a private synonym.

Therefore, the correct answer is:

The Answer is: B. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE SYNONYM EDL_VU FOR mary.EMP_DEPT_LOC_VU; then he can prefix the columns with this synonym.

Multiple choice technology databases

Examine the structure of the EMPLOYEES and DEPARTMENTS tables: Evaluate this SQL statement: SELECT employee_id, e.department_id, department_name, salary FROM employees e, departments d WHERE e.department_id = d.department_id; Which SQL statement is equivalent to the above SQL statement?

  1. SELECT employee_id, department_id, department_name, Salary FROM employees WHERE department_id IN (SELECT department_id FROM departments);

  2. SELECT employee_id, department_id, department_name, salary FROM employees NATURAL JOIN departments;

  3. SELECT employee_id, d.department_id, department_name, salary FROM employees e JOIN departments d ON e.department_id = d.department_id;

  4. SELECT employee_id, department_id, department_name, salary FROM employees JOIN departments USING (e.department_id, d.department_id);
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The original query uses Oracle's old-style comma join syntax with a WHERE clause to join tables. Option C converts this to explicit ANSI JOIN syntax with an ON clause, which produces identical results - both return employee data with matching department information.

Multiple choice technology databases

In which scenario would an index be most useful?

  1. The indexed column is declared as NOT NULL.

  2. The indexed columns are used in the FROM clause.

  3. The indexed columns are part of an expression.

  4. The indexed column contains a wide range of values.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Indexes are most effective on columns with high cardinality - many distinct values relative to total rows. Low cardinality columns (like gender with only 2-3 values) see little benefit from indexing because each index entry points to many rows.

Multiple choice technology databases

When should you create a role? (Choose two.)

  1. To simplify the process of creating new users using the CREATE USER xxx IDENTIFIED by yyy statement

  2. to grant a group of related privileges to a user

  3. when the number of people using the database is very high

  4. to simplify the process of granting and revoking privileges

  5. to simplify profile maintenance for a user who is constantly traveling
Reveal answer Fill a bubble to check yourself
B,D Correct answer
Explanation

Roles bundle related privileges together. You grant a role to users rather than granting individual privileges. This simplifies privilege management - grant or revoke once on the role, not per user per privilege. Roles are the proper abstraction for permission management.

Multiple choice technology databases

Which two statements about sequences are true? (Choose two.)

  1. You use a NEXTVAL pseudo column to look at the next possible value that would be generated from a sequence, without actually retrieving the value.

  2. You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence.

  3. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value from the sequence.

  4. You use a CURRVAL pseudo column to generate a value from a sequence that would be used for a specified database column.

  5. If a sequence starting from a value 100 and incremented by 1 is used by more than one application, then all of these applications could have a value of 105 assigned to their column whose value is being generated by the sequence.

  6. You use a REUSE clause when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.
Reveal answer Fill a bubble to check yourself
A,B Correct answer
Multiple choice technology databases

Which operator can be used with a multiple-row subquery?

  1. =

  2. LIKE

  3. BETWEEN

  4. NOT IN

  5. IS

  6. <>
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

NOT IN is a multi-row operator designed to compare a value against a list returned by a multi-row subquery. Operators like =, LIKE, BETWEEN, IS, and <> are single-row operators and will raise a runtime SQL error if the subquery returns multiple rows.

Multiple choice technology databases

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?

  1. ALTER VIEW emp_dept_vu (ADD manager_id NUMBER);

  2. MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER);

  3. ALTER VIEW emp_dept_vu AS SELECT employee_id,employee_name, department_name,manager_id FROM employees e,departments d WHERE e.department_id = d.department_id;

  4. MODIFY VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;

  5. CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id=d.department_id;
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Oracle doesn't support ALTER VIEW to modify a view's query definition. To modify a view, you must use CREATE OR REPLACE VIEW, which preserves dependent objects and privileges while changing the underlying query.

Multiple choice technology databases

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

  1. UPDATE new_employees SET name = (SELECT last_name|| first_ name FROM employees WHERE employee_id =180) WHERE employee_id =180;

  2. UPDATE new_employees SET name = (SELECT last_name ||first_name FROM employees ) WHERE employee_ id =180;

  3. UPDATE new_employees SET name = (SELECT last_name || first_name FROM employees WHERE employee_id =180) WHERE employee_id =(SELECT employee_id FROM new employees);

  4. UPDATE new_employees SET name = (SELECT last name || first_name FROM employees WHERE employee_id = (SELECT employee_id FROM new_employees)) WHERE employee_id =180;
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Option A uses a correlated subquery with proper WHERE clause in both outer and inner queries. The subquery returns exactly one row (employee_id=180), so the scalar subquery can be used in the SET clause without error.

Multiple choice technology databases

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

  1. SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);

  2. SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);

  3. SELECT distinct department_id FROM employees WHERE salary > ANY(SELECT AVG(salary) FROM employees GROUP BY department_id);

  4. SELECT department_id FROM employees WHERE salary > ALL(SELECT AVG(salary) FROM employees GROUP BY department_id);

  5. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));
Reveal answer Fill a bubble to check yourself
C,D,E Correct answer
Multiple choice technology databases

Which SQL statement retrieves the order ID, customer ID, and order total for the orders that are placed on the same day that Martin placed his orders?

  1. SELECT ord_id, cust_id, ord_total FROM orders, customers WHERE cust_name='Martin' AND ord_date IN ('18-JUL-2000','21-JUL-2000');

  2. SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date IN (SELECT ord_date FROM orders WHERE cust_id = (SELECT cust_id FROM customers WHERE cust_name = 'Martin'));

  3. SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date IN (SELECT ord_date FROM orders, customers WHERE cust_name = 'Martin');

  4. SELECT ord_ id, cust_id, ord_total FROM orders WHERE cust_id IN (SELECT cust_id FROM customers WHERE cust name = 'Martin');
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To solve this question, the user needs to know SQL syntax and how to retrieve data from a database table. The user must select the appropriate columns from the orders table where the order date is the same as the order date for Martin's orders.

Option A: This option is incorrect because it joins the orders and customers tables but does not specify a relationship between them. Also, it selects orders on two specific dates (18-JUL-2000 and 21-JUL-2000) which may not be the dates Martin placed his orders.

Option B: This option is correct. It selects the order ID, customer ID, and order total from the orders table where the order date is the same as the order date for Martin's orders. It uses subqueries to find the order date and customer ID for Martin and then selects orders with the same order date.

Option C: This option joins the orders and customers tables but does not specify a relationship between them, so it is incorrect. Also, it selects orders on the same dates as Martin's orders, which may not be the correct dates.

Option D: This option selects orders where the customer ID is the same as Martin's customer ID, but it does not specify a relationship between the orders and customers tables. Therefore, it may not retrieve the correct orders.

The Answer is: B. SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date IN (SELECT ord_date FROM orders WHERE cust_id = (SELECT cust_id FROM customers WHERE cust_name = 'Martin'));

Multiple choice technology databases

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

  1. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

  2. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

  3. MERGE INTO new employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHES THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

  4. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES(e.employee_id, e.first_name ||'.'||e.last_name);
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To determine which MERGE statement is valid, we need to examine the syntax of each option and compare it to the requirements of the question.

The question provides two tables: EMPLOYEES and NEW EMPLOYEES. Both tables have an EMPLOYEE_ID column as the primary key. The goal of the MERGE statement is to update the NAME column in the NEW EMPLOYEES table with the concatenated FIRST_NAME and LAST_NAME values from the EMPLOYEES table. If an employee exists in the NEW EMPLOYEES table, the NAME should be updated. If the employee does not exist, a new row should be inserted.

Now let's examine each option:

A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

This option is valid. It uses the correct syntax for a MERGE statement and joins the two tables on the EMPLOYEE_ID column. It updates the NAME column in the NEW EMPLOYEES table with the concatenated FIRST_NAME and LAST_NAME values from the EMPLOYEES table. If a match is not found, a new row is inserted into the NEW EMPLOYEES table.

B. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

This option is invalid. It uses the correct syntax for a MERGE statement, but the WHEN EXISTS clause is not valid. It should be WHEN MATCHED instead. Additionally, the syntax for the UPDATE clause is incorrect.

C. MERGE INTO new employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHES THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

This option is invalid. It has a syntax error in the WHEN NOT MATCHES clause, which should be WHEN NOT MATCHED. Additionally, the UPDATE clause syntax is incorrect.

D. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES(e.employee_id, e.first_name ||'.'||e.last_name);

This option is invalid. It does not use the correct syntax for a MERGE statement. Additionally, the INSERT INTO clause syntax is incorrect.

Thus, the valid MERGE statement is:

The Answer is: A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

Multiple choice technology databases
  1. CELL_OFFLOAD_PLAN_DISPLAY parameter determines whether the SQL EXPLAIN PLAN command displays the predicates that can be evaluated by Exadata Cell as STORAGE predicates for a given SQL command

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

CELL_OFFLOAD_PLAN_DISPLAY determines whether Oracle's EXPLAIN PLAN displays Exadata storage filter predicates. Setting it to AUTO or ALWAYS enables this feature, allowing database administrators to verify if query predicates are offloaded to storage cells. The option False is incorrect because it disables showing these storage predicates.

Multiple choice technology databases

Predicate evaluation is not offloaded to Exadata Cell in the following cases:

  1. A fast full scan is performed on reverse key indexes.

  2. The table has row dependencies enabled or the rowscn is being fetched.

  3. The optimizer wants the scan to return rows in ROWID order.

  4. The command is CREATE INDEX using nosort.

  5. A LOB or LONG column is being selected or queried.
Reveal answer Fill a bubble to check yourself
A,B,C,D,E Correct answer
Explanation

All listed cases prevent predicate evaluation offload to Exadata Cell. Fast full scans on reverse key indexes cannot be offloaded due to index structure. Row dependencies or ROWID fetching require database-level processing. ROWID-ordered scans bypass cell offloading capabilities. CREATE INDEX with nosort requires database-side processing. LOB/LONG column queries need database-level handling due to size and complexity.

Multiple choice technology databases

In order to know which SQL operator or function can be referenced in predicate without violating the Exadata Cell offload, you can query OFFLOADABLE column of V$SQLFN_METADATA view. The value “NO” indicates offloading is allowed.

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Querying the V$SQLFN_METADATA view's OFFLOADABLE column indicates whether an operator can be offloaded to Exadata cells. The value 'YES' indicates offloading is allowed, while 'NO' indicates it is not, making the statement false.

Multiple choice technology packaged enterprise solutions

Which is a major problem with SQL?

  1. SQL cannot support object-orientation

  2. The same query can be written in many ways, each with vastly different execution plans.

  3. SQL syntax is too difficult for non-computer professionals to use

  4. SQL creates excessive locks within the database
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

SQL is declarative, meaning the same query can be written in multiple syntactically different ways that produce the same result but generate vastly different execution plans. This makes SQL optimization complex and critical for performance. The optimizer must choose the best execution plan from semantically equivalent alternatives.