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 mainframe

Requirement is to read all the distinct values of following fields from a table called 'IDENTIFICATION-TABLE'. Fields need to be extracted are 1) IDENT-ID 2) IDENT-NAME 3) IDENT-AGE Super to be used for this requirement is SUPER-ID-NAME-AGE (combination of above fields). Which statement will be used to read the data efficiently from IDENTIFICATION-TABLE?

  1. Read Statement

  2. Find Statement

  3. Histogram Statement

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

The HISTOGRAM statement in Natural/ADABAS is specifically designed for efficiently retrieving distinct values (unique occurrences) from specified fields, which is exactly what this requirement needs.

Multiple choice technology mainframe

Data in the database ------------------------------- IDENT-ID IDENT-NAME IDENT-AGE 1234 AAAA 12 1234 AAAA 12 3456 CCCC 14 6879 DDDD 17 7625 EEEE 21 HISTOGRAM IDENTIFICATION-TABLE BY SUPER-ID-NAME-AGE STARTING FROM '1234' WRITE IDENT-ID IDENT-NAME IDENT-AGE END-HISTOGRAM How many records will be displayed in the output?

  1. No records will be displayed

  2. 5

  3. 4

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

A Natural HISTOGRAM statement reads a descriptor, returning one loop cycle per unique value. Starting from '1234', there are four distinct descriptor values present: '1234 AAAA 12', '3456 CCCC 14', '6879 DDDD 17', and '7625 EEEE 21'. The duplicate '1234' record does not produce an extra iteration.

Multiple choice technology mainframe

Data in the database-------------------------------IDENT-ID IDENT-NAME IDENT-AGE IDENT-SEX1234 AAAA 12 M1234 AAAA 12 F3456 CCCC 14 F6879 DDDD 17 M7625 EEEE 21 FHISTOGRAM IDENTIFICATION-TABLE BY SUPER-ID-NAME-AGE STARTING FROM '1234' WRITE IDENT-ID IDENT-NAME IDENT-AGE IDENT-SEXEND-HISTOGRAMWhat will be the output of the job?

  1. 4 records will be displayed

  2. 5 records will be displayed

  3. Job will fail abnormally

  4. Job will run successfully but will not show any records
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The HISTOGRAM uses a superdescriptor SUPER-ID-NAME-AGE. In the data, IDENT-ID 1234 appears twice with different values for NAME/SEX (AAAA/M vs AAAA/F). This creates a duplicate key situation in the superdescriptor, which violates Adabas uniqueness constraints. The job will fail abnormally due to this constraint violation.

Multiple choice technology mainframe

Data in the database ------------------------------- IDENT-ID IDENT-NAME IDENT-AGE 1234 AAAA 12 2345 BBBB 23 3456 CCCC 14 6879 DDDD 17 7625 EEEE 21 HISTOGRAM IDENTIFICATION-TABLE BY SUPER-ID-NAME-AGE STARTING FROM '1235' WRITE IDENT-ID IDENT-NAME IDENT-AGE END-HISTOGRAM What records will be displayed in the output?

  1. No records will be displayed

  2. 2nd to 5th record will be displayed

  3. 2nd record alone will be displayed

  4. All records will be displayed
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

HISTOGRAM with SUPER-ID-NAME-AGE starting from '1235' will display all records where the superdescriptor key is greater than or equal to '1235'. The first record (ID 1234) is excluded. Records 2-5 (IDs 2345, 3456, 6879, 7625) all have keys >= 1235, so they are displayed. Option B is correct.

Multiple choice technology databases

Which operator will be evaluated first in the following SELECT statement?

  1. +

  2. *

  3. /

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

In standard SQL operator precedence, multiplication (*) and division (/) have higher precedence than addition (+) and subtraction (-). When operators have equal precedence (like * and /), they are evaluated left-to-right. Among the options, * will be evaluated first assuming it appears before / in the expression.

Multiple choice technology databases

Which of the following statements are true?

  1. Primary key constraints allow NULL values in the columns.

  2. Unique key constraints allow NULL values in the columns.

  3. Primary key constraints do not allow NULL values in the columns.

  4. A nonunique index cannot be used to enforce primary key constraints.
Reveal answer Fill a bubble to check yourself
B,C Correct answer
Explanation

Unique key constraints allow NULL values, whereas primary key constraints strictly forbid NULL values in their columns. Additionally, a pre-existing non-unique index can be used to enforce a primary key constraint if created beforehand. This makes the marked true options correct and the other choices false.

Multiple choice technology databases

At a minimum, how many join conditions should be there to avoid a Cartesian join if there are three tables in the FROM clause?

  1. 1

  2. 2

  3. 3

  4. There is no minimum.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

When joining three tables, you need at least 2 join conditions to avoid a Cartesian product. Each join condition connects two tables, so for N tables you need N-1 join conditions. With only 1 join condition for 3 tables, the third table would not be properly linked, creating a Cartesian product with the unjoined portion.

Multiple choice technology web technology

Which PHP function do you use to connect to a mysql database.
  1. mysql_selectDB_server($server,$user,$password)
  2. mysql_connection($server,$user,$password)
  3. mysql_connect($server,$user,$password)
  4. database_connect($mysql,$server,$user,$password,$database)
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

mysql_connect() was the standard function in PHP's mysql extension (now deprecated) to establish a connection to a MySQL database server using the server address, username, and password parameters. Note that this extension is deprecated in favor of mysqli or PDO.

Multiple choice technology web technology

Which of these are valid php functions to use with a mysql server?
  1. $result = mysql_obtain_rows($query);
  2. $result = mysql_executeNonQuery($query);
  3. $result = mysql_query($query);
  4. $record = mysql_fetch_array($result)
Reveal answer Fill a bubble to check yourself
C,D Correct answer
Explanation

mysql_query() executes a SQL query on the MySQL server and returns a result resource. mysql_fetch_array() fetches a row from the result set as an array. Options A and B are fictional functions that don't exist in PHP's MySQL extension.

Multiple choice technology web technology

Which of the following are valid statements to obtain the number of row returned by a sql query (It could be from any DBMS)
  1. pg_numrows($result)
  2. postregSQL_numRows($result)
  3. mysql_num_rows($result)

  4. ora_num_rows
Reveal answer Fill a bubble to check yourself
A,C Correct answer
Explanation

pg_numrows() is the PostgreSQL function to get the number of rows in a result set. mysql_num_rows() is the MySQL equivalent. Options B and D are incorrect function names - postregSQL_numRows has a typo and ora_num_rows is not the correct Oracle function name.

Multiple choice technology programming languages

The DBA is creating a new user. Which of the following is NOT defined at user creation

  1. default tablespace

  2. profile

  3. idle_time

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

When creating a user in Oracle, properties like the default tablespace and profile can be specified. However, idle_time is a resource limit configured inside a profile rather than directly on the user during creation. Roles are also managed through separate commands.

Multiple choice technology programming languages

Which command will cause an automatic commit to occur?

  1. CONNECT

  2. RENAME

  3. SELECT

  4. INSERT

  5. DELETE
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

RENAME is a DDL command and DDL commands cause implicit commits before and after execution. CONNECT, SELECT, INSERT, and DELETE do not cause automatic commits - they are DDL (CONNECT) or DML (SELECT, INSERT, DELETE) that require explicit COMMIT. The implicit commit with RENAME happens even if the user has uncommitted changes.

Multiple choice technology security

Developer wrote the query as "select empname,phone,e-mail from emp where empid="+empid;

  1. 101 union all select * from proj;

  2. 101 union all select pname,1,1 from proj;

  3. 101 union all select pname from proj;

  4. 101 union all select pid,pname, pcost from proj;
Reveal answer Fill a bubble to check yourself
B,D Correct answer
Explanation

This is a SQL injection vulnerability. The query concatenates empid directly into SQL: empid="+empid. If empid is "101 union all select pname,1,1 from proj;" (Option B), the injection succeeds because UNION ALL requires matching column count (3 columns) and compatible types (string, number, number). Option D also works: "101 union all select pid,pname,pcost from proj;" matches the 3-column structure. Options A and C fail: A selects * (unknown columns), C selects only 1 column (mismatch).

Multiple choice technology security

Which of the following operators used in the SQL injection?

  1. ' | & ^ < > = !

  2. " | & !

  3. ' | & * < > =

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

SQL injection attacks use operators like single quote (') to break out of string contexts, bitwise OR (|) and AND (&) for logic manipulation, asterisk (*) as a wildcard, and comparison operators (<, >, =) to alter query conditions. Option C includes the most comprehensive set of these critical operators.