Computer Knowledge

Database and SQL

3,929 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. It's the standard language for relational database management systems used to query, manipulate, and define data.

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 uses GROUP BY Rollnum, which returns multiple MIN(stud_marks) values (one per unique rollnum). When compared with a single value using = in the WHERE clause, this causes the error 'single-row subquery returns more than one row' because the main query expects exactly one value to compare against.

Multiple choice technology databases

What are SQL “SELECT” statement capabilities.

  1. Projection

  2. Selection

  3. Join

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

SQL SELECT statements have three main capabilities: Projection (selecting specific columns), Selection (filtering rows with WHERE), and Join (combining data from multiple tables). All of these are fundamental SELECT operations.

Multiple choice technology databases

To select all the columns from a table EMP, which one of the following is TRUE?

  1. Select all columns from EMP;

  2. Select distinct columns from EMP;

  3. Select * from EMP;

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

To retrieve every column from a table, the standard SQL syntax uses the asterisk wildcard: SELECT * FROM EMP;. The phrase “Select all columns from EMP;” is not valid SQL, and SELECT DISTINCT applies to rows, not columns. Therefore only the asterisk option is correct.

Multiple choice technology databases

Pick the two statements that are true regarding Identity Columns. (Select 2)

  1. They don't have to be a Primary Index column

  2. They are not allowed to be a Primary Index column.

  3. They are required to be a Non-Unique Primary Index

  4. You can only specify one Identity Column per table.
Reveal answer Fill a bubble to check yourself
A,D Correct answer
Explanation

Identity columns in Teradata are independent of the Primary Index - they can be defined on non-PI columns. They are also allowed to be part of the PI, contradicting option B. Teradata enforces a strict limit of one identity column per table to maintain data integrity and avoid ambiguity in auto-generation.

Multiple choice technology databases

What is one significant aspect about a Cover Query?

  1. All columns required by the query are available via the Primary index.

  2. All columns required by the query are available via a Join Index.

  3. All columns required by the query are available via the Hash Index

  4. The PE covers the query by looking into cache to see if the same query has been run in the last 240 minutes. If it has been cached the PE doesn't have to generate steps for the AMPs.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

A Cover Query is optimized to retrieve all required columns directly from a Join Index without accessing the base table. Join Indexes pre-join and store frequently queried column combinations, allowing the optimizer to satisfy queries entirely from the index structure. This eliminates expensive joins and dramatically improves performance.

Multiple choice technology databases

Select two true statements about Join Indexes. (Select 2)

  1. Join Indexes have repeating values.

  2. Join Indexes do not have repeating values.

  3. Join Indexes are updated automatically when their underlying rows change

  4. Join Indexes are not updated automatically when their underlying rows change.
Reveal answer Fill a bubble to check yourself
A,C Correct answer
Explanation

Join Indexes store pre-joined data from multiple tables, naturally resulting in repeating values as the join relationship creates duplicates. Teradata automatically maintains Join Indexes when underlying base table rows change, ensuring the pre-joined data stays synchronized without manual intervention.

Multiple choice technology databases

When denormalizing a table, what are some things to consider? (Select 2)

  1. Downtime

  2. Stability

  3. Performance

  4. Volatility
Reveal answer Fill a bubble to check yourself
C,D Correct answer
Explanation

Performance is the primary motivation for denormalization - reducing joins improves read speed. Volatility is equally critical - frequently changing data causes update anomalies and maintenance overhead in denormalized structures. Downtime and stability are operational concerns, not architectural considerations for denormalization.

Multiple choice technology mainframe

Which of the following is defined on the dependent table to implement a referential constraint?

  1. Primary Key

  2. Check constraint

  3. Foreign Key

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

A referential constraint maintains relationships between tables by ensuring that values in a foreign key column match existing values in the parent table's primary key. The foreign key is defined on the dependent (child) table and points to the primary key of the referenced (parent) table. Check constraints validate column values, while triggers perform actions in response to events, but neither implements referential integrity.

Multiple choice technology mainframe

Given the DDL statement: CREATE TABLE newtab LIKE tab. Which of the following would occur as a result of the statement execution?

  1. NEWTAB would have the same column names, attributes and referntial integrity as TAB.

  2. NEWTAB would have the same column names and attributes as TAB.

  3. NEWTAB would have the same column names, attributes, indexes and constraints as TAB.

  4. NEWTAB would have the same column names, attributes and data as TAB.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

CREATE TABLE newtab LIKE tab copies the column definitions and their data types from tab but does not duplicate indexes, constraints, or any existing rows, so the new table has the same columns and attributes only.

Multiple choice technology mainframe

Given the table: COUNTRY NAME CITIES PERSON Argentina 30 1 Cuba 20 2 Canada 10 2 Germany 40 1 Given the statement: SELECT name,cities FROM country Which of the following clauses must be added to the statement for it to return rows sorted by NAME ascending and then sorted by CITIES ascending?

  1. ORDER BY 2,1

  2. ORDER BY 2 ASC, 1 ASC

  3. ORDER BY 1,2

  4. ORDER BY 1 ASC, 2 ASC
Reveal answer Fill a bubble to check yourself
C,D Correct answer
Explanation

ORDER BY 1,2 sorts first by the first selected column (name) then by the second (cities) in ascending order by default; specifying ASC explicitly (1 ASC,2 ASC) does the same. Using column numbers 2,1 or 2 ASC,1 ASC would reverse the intended order.

Multiple choice technology mainframe

Given the table COUNTRY: NAME CITIES PERSON Argentina 30 1 Cuba 20 2 Canada 10 2 Germany 40 1 Given the statement: SELECT name,cities FROM country Which of the following clauses must be added to the statement for it to return rows sorted by NAME ascending and then sorted by CITIES ascending?

  1. ORDER BY 2,1

  2. ORDER BY 2 ASC, 1 ASC

  3. ORDER BY 1,2

  4. ORDER BY 1 ASC, 2 ASC
Reveal answer Fill a bubble to check yourself
C,D Correct answer
Explanation

In SQL, column positions in ORDER BY are 1-indexed based on the SELECT list: position 1 is NAME, position 2 is CITIES. To sort by NAME first then CITIES (both ascending), you can use 'ORDER BY 1,2' since ascending is the default. Option D explicitly specifies 'ASC' but is equivalent. Options A and B reverse the column order, sorting by CITIES first.