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

What data dictionary view will display information about the columns in the database that have constraints?

  1. USER_CONSTRAINTS

  2. USER_CONS_COLUMNS

  3. DBA_CONSTRAINTS

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

USER_CONS_COLUMNS displays information about columns that participate in constraints. USER_CONSTRAINTS shows constraint definitions but not column-level details. DBA_CONSTRAINTS is the DBA-wide view, and DBA_TABLE doesn't exist (should be DBA_TABLES).

Multiple choice technology security

Is SQL injection possible in the below code? String username = request.getParameter(“username”); String password = request.getParameter(“password”); conn = pool.getConnection( ); PreparedStatement pstmt = conn.prepareStatement(“select * from user where username=”+username+” and password=”+password); pstmt.execute(); rs = pstmt.getResultSet();

  1. True

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

SQL injection is possible because despite using PreparedStatement, the code concatenates username and password directly into the query string instead of using parameter placeholders (?). The vulnerability is in the statement: "...where username="+username+" and password="+password. An attacker could input ' OR '1'='1 as password to bypass authentication. PreparedStatement only prevents SQLi when used with setString() on placeholders.

Multiple choice technology databases

Evaluate the SQL statement: SELECT LPAD(salary,10,*) FROM EMP WHERE EMP_ID = 1001; If the employee with the EMP_ID 1001 has a salary of 17000, what is displayed?

  1. 17000.00

  2. 17000*****

  3. **17000.00

  4. An error statement
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

LPAD(salary,10,*) will fail because * is not a valid padding character - it's a SQL wildcard character that has special meaning. The LPAD function expects a literal character string for padding, not a metacharacter. This causes an error regardless of salary value. LPAD would pad to length 10 with the specified character if valid.

Multiple choice technology databases

What is the correct use of the Trunc command on a date?

  1. TRUNC=To_Date('09-Jan-02,DD-MON-YY,'YEAR',"Date" from Dual;

  2. Select TRUNC(To_Date('09-Jan-02,DD-MON-YY,YEAR')) "DATE" from Dual;

  3. Date =TRUNC(To_DATE('09-Jan-02','DD-MON-YY'),'YEAR'),'YEAR)"DATE: from DUAL;

  4. SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The TRUNC function in Oracle/PLSQL truncates a date to a specified precision. Correct syntax is TRUNC(date_value, format). Option D shows proper syntax: TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') truncates the date to the first day of the year. Other options have syntax errors like misplaced parentheses, missing quotes, or incorrect structure.

Multiple choice technology databases

Examine the code given below: SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000 Which of the following statement is correct with regard to this code?
  1. It returns employees who have 50% of the salary greater than $23,000:
  2. It returns employees who have 50% commission rate or salary greater than $23,000:
  3. It returns employees who have 50% of salary less than $23,000:

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

The query uses OR operator: commission_pct = 0.5 OR salary > 23000. This returns employees who either have a 50% commission rate OR have a salary greater than $23,000. Option B correctly interprets this logic.

Multiple choice technology databases

Where is the GROUP BY clause statement placed in a SELECT statement that includes a WHERE clause?

  1. Immediately after the SELECT clause

  2. Before the WHERE clause

  3. After the ORDER BY clause

  4. After the WHERE clause
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

In SQL, the clause order is: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY. The GROUP BY clause must come after the WHERE clause when both are present. Option D correctly places GROUP BY after WHERE.