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

Whenever update strategy transformation is used in mapping, the session property "Treat source rows as" will default be

  1. Insert

  2. Update

  3. Data Driven

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

When you use an Update Strategy transformation, the session property 'Treat source rows as' automatically changes from its default (usually 'Insert') to 'Data Driven', which means the Integration Service follows the update strategy flags set by the transformation.

Multiple choice technology

Target Update Override is used when update has to be based upon

  1. Non Key Values

  2. Key Values

  3. Primary Key Values

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

Target Update Override is specifically used when updates need to be based on non-key column values. By default, updates use primary key columns to match rows, but this feature allows matching using other columns (non-key values) instead.

Multiple choice technology

Update transformation can also be achieved with

  1. Customer Transformation

  2. SQL Transfomation

  3. Both 1 & 2

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

Update logic can be implemented using Custom Transformations (user-coded logic for updates) as well as SQL Transformations (using SQL UPDATE statements). Both provide alternatives to the built-in Update Strategy transformation for achieving the same result.

Multiple choice technology security

Which of the following is not an example for the SQL injection?

  1. ' or 't'='t'

  2. ' or 'user'='admin

  3. ' or 1=1 or 'user' = '

  4. ' or 1=1
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Options A, B, and D are classic SQL injection payloads that manipulate WHERE clauses to always evaluate true. Option C (' or 1=1 or 'user' = ') is NOT standard SQL injection syntax - the ending structure is malformed and wouldn't successfully bypass authentication.

Multiple choice technology mainframe

In DEFERRED UPDATES looks the changes are made to the? (do not select DUMMY options)

  1. DUMMY-1

  2. Retention File

  3. Database Page

  4. DUMMY-2
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In DEFERRED update mode, changes are first recorded in a retention/log file before being applied to the actual database pages. This allows for better performance and recovery options. QUICK mode writes directly to database pages, which is option C in the previous question.

Multiple choice technology programming languages

Examine the structure of the EMPLOYEES table:EMPLOYEE_ID NUMBER Primary KeyFIRST_NAME VARCHAR2(25)LAST_NAME VARCHAR2(25)Which three statements insert a row into the table? (Choose three.)A. INSERT INTO employeesVALUES ( NULL, 'John', 'Smith');B. INSERT INTO employees( first_name, last_name)VALUES( 'John', 'Smith');C. INSERT INTO employeesVALUES ( '1000', 'John', NULL);D. INSERT INTO employees (first_name, last_name, employee_id)VALUES ( 1000, 'John', 'Smith');E. INSERT INTO employees (employee_id)VALUES (1000);F. INSERT INTO employees (employee_id, first_name, last_name)VALUES ( 1000, 'John', ' ');

  1. A

  2. C,E

  3. C,E,F

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

Statement C inserts row with NULL for emp_id (valid if NOT NULL not specified), first_name='John', NULL last_name. Statement E inserts only emp_id=1000, other columns get NULL. Statement F inserts emp_id=1000, first_name='John', last_name=' '(space). A fails if emp_id is PK and NOT NULL. B and D have wrong column ordering in VALUES.

Multiple choice technology programming languages

Which type of Statements can execute parameterized queries?

  1. PreparedStatement

  2. ) ParameterizedStatement

  3. ) ParameterizedStatement and CallableStatement

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

PreparedStatement is the standard JDBC class that supports parameter placeholders ( ? ) and lets you set values before execution. CallableStatement is used for stored procedures, which also accept parameters, but the question asks for a type that can execute parameterized queries, and the only universally correct choice listed is PreparedStatement. The other options refer to non‑existent classes, so the stored answer is right.

Multiple choice technology databases

Which are DML statements? (Choose all that apply.)

  1. COMMIT

  2. MERGE

  3. UPDATE

  4. DELETE

  5. CREATE

  6. DROP
Reveal answer Fill a bubble to check yourself
B,C,D Correct answer
Explanation

DML (Data Manipulation Language) commands manipulate data within tables: MERGE combines insert and update operations, UPDATE modifies existing rows, and DELETE removes rows. In contrast, CREATE and DROP are DDL (Data Definition Language) commands that define database structures, and COMMIT is a transaction control command.

Multiple choice technology databases

On which line will the following command fail? INSERT INTO plsql101_product ( product_name, product_price, quantity_on_hand, last_stock_date ) VALUES ( 'New Product', 1.95, 10, TO_CHAR(USER) ) ;

  1. 1

  2. 2

  3. 7

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

The TO_CHAR(USER) function converts a username string to character format, creating a type mismatch when inserting into a DATE column (last_stock_date). DATE columns require proper date values like SYSDATE or TO_DATE expressions, not string conversions. The TO_CHAR function returns a VARCHAR2, which cannot be directly inserted into a DATE column without implicit conversion.

Multiple choice technology databases

Evaluate the set of SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc 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.

  1. A

  2. B

  3. C

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

To understand the given set of SQL statements, the user must have knowledge of SQL commands and their functions.

  • CREATE TABLE statement creates a table with the specified columns and data types.

  • ROLLBACK statement rolls back the current transaction and undoes all the changes, freeing the storage space occupied by the table.

  • DESCRIBE statement displays the structure of the specified table.

Now, let's evaluate each option:

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

This option is correct. The DESCRIBE statement is used to display the structure of the specified table, and in this case, the specified table is DEPT. So, the DESCRIBE DEPT statement will display the structure of the DEPT table.

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

This option is incorrect. The ROLLBACK statement rolls back the current transaction and undoes all the changes, but it does not free the storage space occupied by the DEPT table. The table will still exist even after the ROLLBACK statement is executed.

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

This option is incorrect. The DEPT table was created using the CREATE TABLE statement, so it exists in the database. The DESCRIBE DEPT statement will display the structure of the DEPT table.

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

This option is incorrect. The DESCRIBE statement displays the structure of the specified table regardless of the presence of a COMMIT statement. The ROLLBACK statement undoes all the changes made in the current transaction, but it does not affect the execution of the DESCRIBE statement.

Therefore, the correct answer is:

The Answer is: A. A

Multiple choice technology databases

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"? 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"? A. No change is required to achieve the desired results. B. SELECT ename, sal, 12*(sal+100) FROM emp; C. SELECT ename, sal, (12*sal)+100 FROM emp; D. SELECT ename, sal+100,*12 FROM emp;

  1. A

  2. B

  3. C

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

To calculate the annual compensation as monthly salary plus a monthly bonus of $100, multiplied by 12, we need to modify the expression "12*sal+100" in the SELECT statement.

Option A: No change is required to achieve the desired results. This option is incorrect because the original expression "12*sal+100" calculates the total compensation as monthly salary multiplied by 12, plus a fixed monthly bonus of $100. It does not add the bonus to the monthly salary before multiplying by 12.

Option B: SELECT ename, sal, 12*(sal+100) FROM emp; This option is correct. By adding the monthly bonus of $100 to the monthly salary before multiplying by 12, we get the correct annual compensation. The expression "12*(sal+100)" calculates the annual compensation as monthly salary plus bonus, multiplied by 12.

Option C: SELECT ename, sal, (12*sal)+100 FROM emp; This option is incorrect because the parentheses around "12*sal" are unnecessary. The multiplication operator has higher precedence than the addition operator, so "12*sal" is evaluated before adding 100.

Option D: SELECT ename, sal+100,12 FROM emp; This option is incorrect because the syntax is incorrect. The asterisk () is not a valid operator in this context. We need to use the multiplication operator (*) to calculate the annual compensation.

Therefore, the correct answer is: B. SELECT ename, sal, 12*(sal+100) FROM emp;

Multiple choice technology databases

Which of the following commands will rename a table?

  1. RENAME table_name new_table_name;

  2. RENAME table_name TO new_table_name;

  3. RENAME TABLE table_name new_table_name;

  4. RENAME TABLE table_name TO new_table_name;
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The RENAME command (Oracle syntax) requires the TO keyword between the old and new names. The correct syntax is RENAME old_name TO new_name, without the TABLE keyword and with the TO separator. Options A and C are missing the TO keyword, while D incorrectly adds the TABLE keyword.