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

SQL is

  1. Structured Query Language

  2. Standard Query Language

  3. System Query Language

  4. Simple Query Language
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

SQL stands for Structured Query Language, which is the standard programming language used to manage and manipulate relational databases. The other options, such as Standard, System, or Simple Query Language, are incorrect expansions of the acronym and do not represent the official name.

Multiple choice technology databases

Duplicate values are eliminated by which keyword

  1. Duplicate

  2. Distinct

  3. Remove

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

In SQL, the DISTINCT keyword is used in SELECT statements to remove duplicate rows from the result set, returning only unique values. While options like 'Duplicate', 'Remove', and 'Delete' might seem conceptually related, they are not valid SQL keywords for this purpose. DISTINCT is the standard SQL operator specifically designed for eliminating duplicate values.

Multiple choice technology databases

The two types of subqueries are:

  1. Single-row subquery, Double-row subquery

  2. Double-row subquery, Multiple-row subquery

  3. Multiple-row subquery

  4. Single-row subquery and Multiple subquery
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Subqueries are broadly classified based on the number of rows they return to the outer query. Single-row subqueries return only one row, whereas multiple-row subqueries return one or more rows. The other options incorrectly include non-existent concepts like double-row subqueries.

Multiple choice technology databases

Single-row subquery returns:

  1. Only one row

  2. Two rows

  3. Zero rows

  4. There is no such subquery
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

A single-row subquery is designed to return exactly one row and one column. This type of subquery is typically used with single-row comparison operators like =, >, <, >=, or <=. If a single-row subquery returns more than one row, it will cause an error, and if it returns no rows, the result is null.

Multiple choice technology databases

What is the result of the query below? SELECT Stud_name, Rollnum FROM STUDENT WHERE stud_marks=(SELECT MIN (stud_marks) FROM STUDENT GROUPBY Rollnum);

  1. It gives the name and marks of all the students.

  2. It does not return anything.

  3. It shows an error saying” INVALID SYNTAX ERROR”

  4. It shows an error saying” Single-row subquery returns more than one row
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The subquery groups by Rollnum, returning the minimum marks for each group, which results in multiple rows. Because the outer query uses the single-value comparison operator =, it fails with a 'single-row subquery returns more than one row' error. Other options are incorrect as the query cannot execute successfully.

Multiple choice technology databases

Which operator compares a value to every value returned by a subquery?

  1. IN

  2. OF

  3. ALL

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

The ALL operator in SQL compares a value to every value returned by a subquery, evaluating to true only if the comparison is true for all values. For example, 'salary > ALL (subquery)' returns true only if the salary is greater than every single value returned by the subquery. IN checks membership, ANY returns true if at least one comparison succeeds, and OF is not a valid SQL operator.

Multiple choice technology databases

A DML statement is executed when you

  1. Add new rows to a table

  2. Modify existing rows of a table

  3. Remove existing rows from a table

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

DML (Data Manipulation Language) statements include INSERT (adding new rows), UPDATE (modifying existing rows), and DELETE (removing existing rows). Since all three operations are fundamental DML operations, option D is correct as it encompasses all the individual DML actions listed in the other options.

Multiple choice technology databases

The right syntax insert a row into a table is

  1. Insert to table tablename (….);

  2. Insert into table tablename (….);

  3. Insert into table tablename values (…..);

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

The correct SQL syntax for inserting values is 'INSERT INTO tablename VALUES (...)' which directly inserts values into a table. Options A and B have incorrect syntax ('Insert to' and 'insert into table' are not valid). Option C correctly shows 'Insert into table tablename values' though it would typically be 'INSERT INTO tablename VALUES' without the word 'table' - but among the given choices, C is the closest to correct syntax.

Multiple choice technology databases

Below DML Commands supported within the FastExport task

  1. Delete

  2. Select

  3. Update

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

FastExport is a read-only utility designed specifically for high-volume data extraction from Teradata. Only SELECT queries are permitted within FastExport tasks. INSERT, UPDATE, and DELETE are data modification commands that are not supported.

Multiple choice technology

You need to extract details of those products in the SALES table where the PROD_ID column contains the string '_D123'. Which WHERE clause could be used in the SELECT statement to get the required output?

  1. WHERE prod_id LIKE '%D123%' ESCAPE ''

  2. WHERE prod_id LIKE '%_D123%' ESCAPE '&#39;

  3. WHERE prod_id LIKE '%D123%' ESCAPE '%'

  4. WHERE prod_id LIKE '%_D123%' ESCAPE '_'
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The LIKE operator with '%_D123%' would match any string containing '_D123', but underscore is a wildcard matching any single character. To search for a literal underscore, you need to escape it using the ESCAPE clause. Option B correctly uses backslash as the escape character (ESCAPE '\') and prefixes the underscore with backslash (_) to treat it as a literal character. Options A and C are incorrect because they don't properly escape the underscore. Option D incorrectly uses '_' as the escape character instead of '\'.

Multiple choice technology

Which two statements are true regarding single row functions?

  1. They accept only a single argument.

  2. .They can be nested only to two levels.

  3. Arguments can only be column values or constants.

  4. They always return a single result row for every row of a queried table.

  5. They can return a data type value different from the one that is referenced.
Reveal answer Fill a bubble to check yourself
D,E Correct answer
Explanation

Single-row functions operate on each row independently and return exactly one result per input row (option D). They can return data types different from their input arguments - for example, TO_CHAR converts numbers to strings, and NVL can return different types based on its first argument (option E). Options A, B, and C are false: single-row functions accept multiple arguments (like SUBSTR), can be nested deeply beyond two levels, and accept expressions, not just column values or constants.

Multiple choice technology

Which two statements are true regarding the USING and ON clauses in table joins?

  1. Both USING and ON clauses can be used for equijoins and nonequijoins.

  2. A maximum of one pair of columns can be joined between two tables using the ON clause.

  3. The ON clause can be used to join tables on columns that have different names but compatible data

  4. The WHERE clause can be used to apply additional conditions in SELECT statements containing the
Reveal answer Fill a bubble to check yourself
C,D Correct answer
Explanation

The ON clause allows joining tables on columns with different names as long as data types are compatible (option C) - this is its primary advantage over USING. The WHERE clause can always add additional filter conditions after the JOIN clause (option D). Option A is false: USING only works for equijoins (columns with same name), while ON works for any condition. Option B is false: the ON clause can join on multiple column pairs using AND conditions.

Multiple choice technology

Which statement is true regarding the INTERSECT operator?

  1. It ignores NULL values.

  2. Reversing the order of the intersected tables alters the result.

  3. The names of columns in all SELECT statements must be identical.

  4. The number of columns and data types must be identical for all SELECT statements in the query.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

INTERSECT requires that all SELECT statements in the query have the same number of columns and matching data types (option D) - this is a fundamental requirement for set operations. Option A is false: INTERSECT treats NULL values like any other value and includes them in results. Option B is false: INTERSECT is commutative - order doesn't affect the result set. Option C is false: column names don't need to match; only position and data type must align.