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

Evaluate the following SQL statement: SELECT ROUND (TRUNC (MOD (1600, 10),-1), 2) FROM dual; What will be displayed?

  1. 0

  2. 1

  3. 0.00

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

To evaluate the SQL statement, we need to understand the functions ROUND, TRUNC and MOD.

ROUND: rounds a number to a specified number of decimal places. If the second argument is negative, it rounds to the left of the decimal point. For example, ROUND(123.456, 2) = 123.46, ROUND(123.456, -1) = 120. TRUNC: truncates a number to a specified number of decimal places. If the second argument is negative, it truncates to the left of the decimal point. For example, TRUNC(123.456, 2) = 123.45, TRUNC(123.456, -1) = 120. MOD: returns the remainder of a division operation. For example, MOD(7, 3) = 1, MOD(1600, 10) = 0. Using these functions, we can evaluate the SQL statement step by step:

MOD(1600,10) returns 0, since there is no remainder when dividing 1600 by 10. TRUNC(0,-1) returns 0, since there is nothing to truncate to the left of the decimal point. ROUND(0,2) returns 0, since there is nothing to round to the right of the decimal point. Therefore, the final result displayed by the SQL statement is 0.

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 truncates a date to a specified precision. Option D shows correct Oracle SQL syntax: TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') truncates the date to the first day of the year. Options A-C have syntax errors including misplaced quotes, missing parentheses, and incorrect parameter placement.

Multiple choice technology databases

Which type of join should be written to perform an outer join of tables A and B that returns all rows from B?

  1. Any outer join

  2. A left outer join

  3. A cross join

  4. A right outer join

Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A right outer join returns all rows from the right table (B) and matching rows from the left table (A). The 'right' designation refers to the table on the right side of the JOIN keyword. Left outer join would return all rows from A instead.

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 a SELECT statement with multiple clauses, the order is: SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY. The GROUP BY clause must come after the WHERE clause because rows must first be filtered by WHERE before being grouped by GROUP BY.

Multiple choice technology security

Analyse following code public void dummyFunction(String var1,String var2){ try{ Connection con=getConnection(); String query=”select * from table1 where col1=”+var1 +”and col2=”+var2; Statement st=conn.createStatement(); ResultSet rs=st.executeQuery(query); …… ….. } catch(Exception e) { } } var1 and var2 are inputs from user directly passed to this functions.

  1. Vulnerable to SQL Injection

  2. Vulnerable to DoS

  3. Vulnerable to Information Disclosure

  4. Code is secure
Reveal answer Fill a bubble to check yourself
D Correct answer
Multiple choice technology platforms and products

iSQL*Plus commands access the database.

  1. True

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

iSQL*Plus commands are SQL*Plus environment commands that control formatting, output, and environment settings - they do NOT directly access the database. Only SQL commands (SELECT, INSERT, etc.) and PL/SQL blocks actually interact with the database. Commands like SET, COLUMN, or SPOOL are iSQL*Plus commands that are interpreted by the SQL*Plus client tool, not sent to the database server.

Multiple choice technology platforms and products

In Lock Tables SQL command tables are seperated by the delimiter

  1. Tab

  2. Dot

  3. Newline

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

In the SQL LOCK TABLES command (syntax varies by database), multiple tables are separated by commas. For example, 'LOCK TABLES table1 READ, table2 WRITE' locks two tables. This follows standard SQL syntax patterns where multiple items in a list are comma-delimited. Tabs (A), dots (B), and newlines (C) are not used as table separators in LOCK TABLES syntax.

Multiple choice technology databases

When 'select * from ' is given, oracle server checks for..

  1. control files

  2. redo log files

  3. user_views data dictionary view

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

When selecting from a view, Oracle must verify the view exists and the user has privileges. This metadata is stored in the USER_VIEWS data dictionary view. The server checks this before accessing base tables or executing the query. Control files and redo log files are unrelated to view resolution.