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

You need to display the last names of those employees who have the letter “A” as the second character in their names. Which SQL statement displays the required results?

  1. SELECT last_name FROM EMP WHERE last_name LIKE ‘_A%’;

  2. SELECT last_name FROM EMP WHERE last name =’*A%’

  3. SELECT last_name FROM EMP WHERE last name =’_A%’;

  4. SELECT last_name FROM EMP WHERE last name LIKE ‘*A%’
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The pattern '_A%' uses underscore as a wildcard for exactly one character, followed by 'A', then percent for any sequence. This matches names where 'A' is the second character. Options B, C, D use incorrect syntax (spaces in column name, equals instead of LIKE, asterisk which isn't a SQL wildcard in this context).

Multiple choice technology databases

Consider the following SQL statement. SELECT qty FROM sales WHERE rate IN (300,800); Which of the following operators can be used to substitute the 'IN' operator?

  1. ALL

  2. BETWEEN...AND

  3. LIKE

  4. OR
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To solve this question, the user needs to understand the different SQL operators and how they work. The user must also know how to use these operators in a SQL statement to perform a specific task.

Now, let's go through each option and explain whether it can be used to substitute the 'IN' operator:

A. ALL: The ALL operator is used to compare a value to all values in another set of values. It cannot be used to substitute the 'IN' operator. Therefore, option A is incorrect.

B. BETWEEN...AND: The BETWEEN...AND operator is used to select values within a given range. It cannot be used to substitute the 'IN' operator. Therefore, option B is incorrect.

C. LIKE: The LIKE operator is used to compare a value to similar values using wildcard characters. It cannot be used to substitute the 'IN' operator. Therefore, option C is incorrect.

D. OR: The OR operator is used to combine multiple conditions in a WHERE clause. It can be used to substitute the 'IN' operator by specifying each value as a separate condition joined by the OR operator. Therefore, option D is correct.

The Answer is: D

Multiple choice technology databases

Which of the following operators is used in a correlated subquery?

  1. IN

  2. EXISTS

  3. UNION

  4. INTERSECT
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The EXISTS operator is commonly used in correlated subqueries to test for the existence of rows returned by the subquery, which references columns from the outer query. IN can also be used but EXISTS is the classic operator specifically designed for correlation checks. UNION and INTERSECT are set operators.

Multiple choice technology databases

Which of the following naming rules apply to tables? (Choose three)

  1. A table name can duplicate the name of any other database object, if not owned by same user.
  2. Table names can contain $,_, and # signs.

  3. Table names are case-sensitive.

  4. Table names begin with a letter.

  5. Table names are not created in Uppercase by default.
Reveal answer Fill a bubble to check yourself
A,B,D Correct answer
Explanation

Oracle table naming rules: different users can have tables with the same name (A is correct), table names can contain letters, digits, and special characters like $, _, # (B is correct), and table names must begin with a letter (D is correct). Oracle table names are NOT case-sensitive (C is wrong) and are stored in uppercase by default (E is wrong).

Multiple choice technology databases

Which of the following statements creates a view and does not allow the values to be changed through the view?

  1. CREATE VIEW empl_vu AS SELECT * FROM employee WHERE sal > 10000 WITH READ ONLY;

  2. CREATE VIEW empl_vu AS SELECT * FROM employee WHERE sal > 10000 WITH CHECK OPTION;

  3. CREATE VIEW empl_vu AS SELECT * FROM employee WHERE sal > 10000 WITH CHECK CONSTRAINT;

  4. None of the above
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The WITH READ ONLY clause creates a view that cannot be used for INSERT, UPDATE, or DELETE operations. Option A is correct. WITH CHECK OPTION ensures that any INSERT or UPDATE through the view must satisfy the view's WHERE clause, but it still allows DML operations. WITH CHECK CONSTRAINT is not valid Oracle syntax.

Multiple choice technology databases

Which of the following are system privileges? (Choose two)

  1. DELETE

  2. SELECT

  3. CREATE TABLE

  4. UPDATE

  5. DROP TABLE
Reveal answer Fill a bubble to check yourself
C,E Correct answer
Explanation

System privileges grant rights to perform DDL operations across the database, while object privileges grant rights on specific objects. CREATE TABLE and DROP TABLE are system privileges (C and E are correct). DELETE, SELECT, and UPDATE are object privileges that must be granted on specific tables (A, B, D are wrong).

Multiple choice technology databases

A user executes a PL/SQL block containing complex data with several update and insert statements using multiple row subqueries and complex inline view. Under which of the following conditions will the database save the change after executing one or more PL/SQL blocks? (Choose two)

  1. When you disconnect from your session abnormally

  2. After PL/SQL block is executed successfully

  3. When you issue TRUNCATE command at SQL prompt

  4. When COMMIT command is issued

  5. When each UPDATE statement is executed
Reveal answer Fill a bubble to check yourself
C,D Correct answer
Explanation

Oracle databases save changes (commit) when a user explicitly issues a COMMIT command, or implicitly when a DDL statement like TRUNCATE is executed. Abnormal disconnection results in a rollback, while successful execution of a PL/SQL block or individual DML statements does not commit changes automatically.

Multiple choice technology databases

You are declaring a variable of data type NUMBER in a PL/SQL block. During the execution of the PL/SQL block, a query returns seven rows, and the next FETCH statement is issued. If the variable was assigned a value by using %ROWCOUNT attribute, which of the following values will be stored in it?

  1. 0

  2. 8

  3. 7

  4. NULL
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The %ROWCOUNT attribute returns the cumulative number of rows fetched from the cursor so far. Since a query returned seven rows and the last successful fetch retrieved the seventh row, %ROWCOUNT holds 7. Distractors like 8, 0, or NULL are incorrect because 7 rows have been successfully fetched.

Multiple choice technology databases

Suppose you have to remove a table. There are several indexes associated with this table. Which among the following would remove all the indexes associated with the table?

  1. DROP INDEX

  2. ALTER TABLE ....... DROP PRIMARY KEY CASCADE

  3. ALTER TABLE ....... DROP CONSTRAINT

  4. DROP TABLE
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

When you execute a DROP TABLE command, the database automatically drops the table along with all indexes defined on that table. There is no need to drop the indexes individually, as they cannot exist without the base table. Other options like dropping constraints or primary keys cascade do not remove all table indexes.

Multiple choice technology databases

Which of the following are not legal?

  1. DECLARE V_Destination_India Number;

  2. DECLARE V_123, V_213, V_132 Varchar2(10);

  3. DECLARE V_sys Boolean := 1;

  4. DECLARE V_destination_India Number(4);

  5. DECLARE V_Hiredate NOT NULL DATE := '01-JAN-00';
Reveal answer Fill a bubble to check yourself
B,C,E Correct answer
Explanation

In PL/SQL: declaring multiple variables in a single statement separated by commas is invalid; a boolean cannot be directly initialized with the integer 1; and variable definitions must have the NOT NULL constraint specified after the data type, not before. Thus, all three checked options are indeed illegal PL/SQL declarations.

Multiple choice technology databases

Which three of the following methods make PL/SQL tables easier to use? (Choose three)

  1. EXISTS

  2. SUBSTR

  3. TRIM

  4. FLOOR

  5. COUNT

  6. UPPER
Reveal answer Fill a bubble to check yourself
A,C,E Correct answer
Explanation

EXISTS checks if a specific element exists in a PL/SQL table, preventing NO_DATA_FOUND exceptions. TRIM removes elements from the end of a PL/SQL table (not string trimming - it's a collection method). COUNT returns the number of elements in a PL/SQL table. These are built-in PL/SQL table methods that make manipulation easier. SUBSTR, FLOOR, and UPPER are general SQL/PL functions, not specific PL/SQL table methods.

Multiple choice technology databases

For which of the following tasks would it be most appropriate to use a PL/SQL WHILE loop?

  1. To add six new services to the SERVICE table

  2. To change MACHINE_ID 123456 to 456789 and change all others to 900000

  3. To change MACHINE_ID 123456 to 456789

  4. To add new services to the SERVICE table until finished
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A WHILE loop is ideal when you need to repeat an action until a completion condition is met, which matches 'add new services until finished'. Options A and C describe single or fixed actions (adding 6 services, one specific update) which don't require looping. Option B describes a conditional update that can be done with a single UPDATE statement with CASE expression, not a loop.

Multiple choice technology databases

Examine the statement of correlated subquery in the following cursor declaration. DECLARE CURSOR c1 IS SELECT D1.deptno, D1.loc, E1.Totalemp FROM dept D1, (SELECT deptno, COUNT(*) Totalemp FROM emp GROUP BY deptno) E1 WHERE D1.deptno = E1.deptno AND E1.Totalemp > 6; Why does this statement result in an error?

  1. Parameter has to be used in this cursor to make its execution successful.

  2. Correlated subquery cannot be used in CURSOR declaration.

  3. Correlated FROM clause subquery cannot be used in CURSOR declaration.

  4. AND logical operator cannot be used in WHERE clause of cursor declaration.

  5. No logical error.
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

This cursor declaration is valid SQL syntax. It joins the dept table with a subquery that aggregates employee counts by department. The subquery in the FROM clause is not correlated - it's an independent aggregation (GROUP BY deptno) that's then joined. There's no syntax error, no restriction against subqueries in cursor declarations, and the AND operator is perfectly valid in WHERE clauses.

Multiple choice technology databases

How many constraints are available in SQL

  1. 1

  2. 2

  3. 3

  4. 5
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The number of primary constraints in SQL is commonly considered to be 5: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK. Since 5 is standard and marked as the correct option, the stored answer is correct.