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

Which of the following statements is/are correct?

(i) SQL allows use of DISTINCT with count (*).

(ii) If is not legal to use DISTANCE with MAX and MIN.

(iii) If is ‘WHERE’ clause and a ‘HAVING’ clause appear in the same clause is applied first.

(iv) By default, the ‘ORDER BY’ clause list items in a descending order.

  1. All of the above

  2. (i), (ii), (iii)

  3. (ii), (iii)

  4. (iii)
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Only statement (iii) is correct. SQL does not allow use of distance with COUNT(*). If is legal to use DISTINCT MAX and MIN. By default the ‘ORDER BY’ clause list items in an ascending order.

Multiple choice

LIKE “abcd%” ESCAPE ‘’- The statement matches

  1. all strings beginning with “abcd”

  2. all strings ending with “abcd”

  3. all string beginning with “abcd%”

  4. error in SQL
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Since, backslash () is an escape character, the string must be inserted after "abcd ". 

Multiple choice

Which of the following is (are) true with respect to SQL?

(I) SQL is case insensitive

(II) Patterns used for comparison of string are case sensitive

(III) FROM and fROM are equivalent and mean the same keyword

(IV) String 'Mr' is not equal to string 'MR'

  1. (I) , (II) and (III) only

  2. (I) and (IV) only

  3. (II), (III) and (IV) only

  4. (I) , (II), (III) and (IV) only
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

SQL is case insensitive, meaning that it treats upper and lower case letters as the same letter.$\therefore$ Statements (I) and (III) are true.But patterns used for comparison of string are case sensitive. Instead all inside quotes is case sensitive. $\therefore$ Statements (II) and (IV) are also true hence, (4) is the answer.

Multiple choice

Consider the relation schema as follows. Employee (employee-name, sheet, city) Works (employee-name, company-name, salary) Company (Company-name, city) Manages (Employee-name, manager-name)

Write a SQL query that gives the names of the employee who works in the same city and having salary greater than equal to 17000 and less than equal to 35500.

  1. SELECT employee-name FROM employee, works, company WHERE salary between 17000 and 35500

  2. SELECT employee-name FROM employee, works, company GROUP BY company, city having salary between 17000 and 35500

  3. SELECT employee-name FROM employee, works, company GROUP BY city having salary between 17000 and 35500

  4. SELECT employee-name FROM employee, works, company GROUP BY city having salary > = 17000 and 35500 salary < = 35500
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Statement (2) will give the result of the requirement of the query. This statement will select the employees’ names from employee works, company and group them by same city, those who are having salary between 17000 and 35500.

Multiple choice

Find all employees in the database who do not work for State Bank of India.

  1. SELECT employee-name FROM employee WHERE employee-name IN (SELECT employee-name FROM employee-work WHERE company name = “State Bank of India”

  2. SELECT employee-name FROM employee WHERE employee-name except (SELECT employee-name FROM employee-work WHERE company name = “State Bank of India”

  3. SELECT employee-name FROM employee WHERE employee-name <> (SELECT employee-name FROM employee-work WHERE company name = “State Bank of India”

  4. SELECT employee-name FROM employee WHERE Employee-name not IN (SELECT employee-name FROM employee-work WHERE company name = “State Bank of India”
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

This option is also incorrect which does not match with the correct query.

Multiple choice

Which of the following queries finds the clients of banker Agassi and the city they live in?
  1. $\pi_{client, customer\ name, customer\ city} (\sigma_{Banker name} = “Agassi” (client × customers))$
  2. $\pi_{client, customer\ name, customer\ city} (\sigma_{Banker name} = “Agassi” (client × customers)$
  3. $\pi_{client, customer\ name, customer\ city} \\\\ (\sigma_{Banker name} = “Agassi” (\sigma_{client} × customer name) = \\\\ customer, customer\ name (client × customers)$
  4. $\pi_{customer\ name, customer\ city }(\sigma_{Banker name} = “Agassi” (client × customers)$
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The SQL statement is select client customer name, client customer city from client customer where (client customer name = customer. Customer name and banker name = “Agassi” $\therefore$Relational algebra query is$\pi_{client, customer\ name, customer\ city} (\sigma_{Client, customer\ name})= customer. customer name \\ (\sigma_{Banker name} = “Agassi” (client × customers)$

Multiple choice

Which of the following is used to add new attribute (column) in the existing relation schema?

  1. ADD

  2. INSERT

  3. CREATE

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

The modifications in the relation schema are done by a statement that begins with the keywords ALTER TABLE and the name of the relation. We then have several options and one of the most important is ADD.Syntax: ALTER TABLE relation ADD new-attribute dates typeE.g. ALTER TABLE Employee ADD salary INTThe above statement will add a new attributes salary to employee relation.

Multiple choice

Consider the following SQL query

Select distinct $a_1, a_2, …, a_n$

from $r_1, r_2, …, r_m$

where P

For an arbitrary predicate P, this query is equivalent to which of the following relational algebra expressions?
  1. $\Pi_{a_1, a_2, … a_n} \sigma_p \left(r_1 \times r_2 \times \dots \times r_m\right)$
  2. $\Pi_{a_1, a_2, … a_n} \sigma_p \left(r_1 \bowtie r_2 \bowtie \dots \bowtie r_m \right)$
  3. $\Pi_{a_1, a_2, … a_n} \sigma_p \left(r_1 \cup r_2 \cup \dots \cup r_m \right)$
  4. $\Pi_{a_1, a_2, … a_n} \sigma_p \left(r_1 \cap r_2 \cap \dots \cap r_m \right)$
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Cross product (x) combines the tuples of the one relation with all the tuples of other relation. Thus, tuples of relation $r_1, r_2 .... m$ are combined. Select operator $\sigma$ is used to select resultant tuples. Projection operator $ \pi _{a_1,a_2...a_n}$ is sued to select as subset of attributes from the resultant tuples by specifying the names of the attributes.

So attributes $a_1, a_2, $ an are projected from the resultant tuples.

Thus, option(A) is correct

Multiple choice

Let R (a, b, c) and S (d, e, f) be the two relations in which d is the foreign key of S that refers to the primary key of R. Consider the following four operations regarding R and S; A. Insert into R B. Insert into S C. Delete from R D. Delete from S Which of the following is true about the above referential integrity constraint?

  1. None of these can cause its violation.

  2. All of these can cause its violation.

  3. Both (1) and (4) can cause its violation.

  4. Both (2) and (3) can cause its violation.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

With foreign key d in S referencing primary key of R: inserting into S (option B) can violate if the d value doesn't exist in R. Deleting from R (option C) can violate if existing rows in S reference the deleted R value. Inserting into R and deleting from S don't cause violations. Therefore, both (2) and (3) can cause referential integrity violations.

Multiple choice

Consider the following dependencies in a database:
Date_of_Birth$\rightarrow$Age Age$\rightarrow$Eligibility Name$\rightarrow$Roll_number Roll_number $\rightarrow$ Name Course_ number $\rightarrow$Courses_ name Course_ number$\rightarrow$Instructor (Roll_number; Courses_ name) $\rightarrow$Grade The relation (Roll_number, Name, Date_of_Birth, age) is

  1. in second normal form but not in third normal form

  2. in third normal form but not in BCNF

  3. in BCNF

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

Given:  Date_of_Birth$\rightarrow$Age Age$\rightarrow$Eligibility Name$\rightarrow$Roll_number Roll_number $\rightarrow$ Name Course_ number $\rightarrow$Courses_ name Course_ number$\rightarrow$Instructor (Roll_number; Courses_ name) $\rightarrow$Grade For given relation: Date_of_Birth$\rightarrow$ Eligibility Course_ name$\rightarrow$ Instructor After observing we see that, it is not in 3rd normal form because in third normal form relation, every non-prime attribute is non transitively and fully dependent on the every candidate key. In 2nd normal form, all non - prime attributes are fully functionally dependent on the relation keys.

Multiple choice

Which of the following scenarios may lead to an irrecoverable error in a database system?

  1. A transaction writes a data item after it is read by an uncommitted transaction.

  2. A transaction reads a data item after it is read by an uncommitted transaction.

  3. A transaction reads a data item after it is written by a committed transaction.

  4. A transaction reads a data item after it is written by an uncommitted transaction.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Option 3 is a normal operation. Option 2 is also fine as no write operation is involved. Option 1 can be recovered, but option 4 can’t be.

Multiple choice

What happens when a primary key is combined with a foreign key?

  1. It connects network model between the tables that connect them.

  2. It connects many-to-many relationship between the tables that connect them.

  3. It connects parent child relationship between the tables that connect them.

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

A foreign key in one table references a primary key in another table, establishing a parent-child relationship where the referenced table is the parent and the referencing table is the child.