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

What is a lock in DB2?

  1. A lock is the mechanism that controls access to rows and coloumns

  2. A lock is the mechanism that controls access to table views

  3. A lock is the mechanism that controls access to data pages and table spaces.

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

DB2 locks control access to physical and logical data resources like table spaces, tables, and data pages to ensure concurrency. They do not lock individual columns (making the first option incorrect) or views directly (making the second option incorrect), making the third option the most accurate.

Multiple choice technology databases

Which of the following SQL statements does count the rows in the 'Sales' table?

  1. SELECT COUNT(*) IN Sales

  2. SELECT NUM() FROM Sales

  3. SELECT COUNTER(*) FROM Sales

  4. SELECT COUNT(*) FROM Sales
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

COUNT(*) is the standard SQL aggregate function that returns the total number of rows in a table, including NULL values. The correct syntax requires FROM, not IN, to specify the table source. Option D uses the proper SQL syntax while other options use non-existent functions like NUM(), COUNTER(), or incorrect prepositions.

Multiple choice technology databases

Which SQL statement inserts data into a table called Projects?

  1. INSERT Projects ('Content Development', 'Website content development project')

  2. INSERT INTO Projects (ProjectName, ProjectDescription) VALUES ('Content Development', 'Website content development project')

  3. INSERT Projects VALUES ('Content Development', 'Website content development project')

  4. SAVE INTO Projects (ProjectName, ProjectDescription) VALUES ('Content Development', 'Website content development project')
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

INSERT INTO is the standard SQL statement with the syntax requiring column names in parentheses followed by VALUES and the data to insert. Option B correctly specifies both column names (ProjectName, ProjectDescription) and their corresponding values. Options A and C miss the required INTO keyword and column specification, while D uses SAVE which is not a SQL keyword.

Multiple choice technology databases

Which of the following is a SQL aggregate function?

  1. CAST

  2. LEFT

  3. MIN

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

SQL aggregate functions perform calculations on multiple values and return a single value. MIN is a valid aggregate function that returns the smallest value in a column. CAST is a conversion function, while LEFT and RIGHT are string manipulation functions that extract substrings - none of these are aggregate functions.

Multiple choice technology databases

The LIKE SQL keyword is used along with ...

  1. ORDER BY clause

  2. GROUP BY clause.

  3. JOIN clause.

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

To solve this question, the user needs to know the basic syntax and usage of the LIKE keyword in SQL.

The LIKE keyword is used in SQL along with the WHERE clause to search for a specific pattern in a column of a table. The LIKE keyword is used to perform partial matching of strings and can be used with wildcard characters, such as % to represent any number of characters or _ to represent a single character.

Now, let's go through each option and explain why it is right or wrong:

A. ORDER BY clause: This option is incorrect because the ORDER BY clause is used to sort the result set in ascending or descending order based on one or more columns. It does not have any relation to the LIKE keyword.

B. GROUP BY clause: This option is incorrect because the GROUP BY clause is used to group the result set based on one or more columns. It also does not have any relation to the LIKE keyword.

C. JOIN clause: This option is incorrect because the JOIN clause is used to combine rows from two or more tables based on a related column between them. It also does not have any relation to the LIKE keyword.

D. WHERE clause: This option is correct. The LIKE keyword is used along with the WHERE clause to filter the rows in a table based on a specific pattern in a column. The WHERE clause is used to specify the condition that must be met for a row to be included in the result set.

Therefore, the correct answer is: D. WHERE clause.

Multiple choice technology databases

Which of the following is a SQL aggregate function

  1. JOIN

  2. LEN

  3. LEFT

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

AVG is a SQL aggregate function that calculates the arithmetic mean of numeric values in a column. JOIN is used to combine tables, LEN returns string length, and LEFT extracts a substring - none of these are aggregate functions. Aggregate functions perform calculations across multiple rows and return single values like COUNT, SUM, AVG, MIN, MAX.

Multiple choice technology databases

The JOIN is a SQL keyword used to ...

  1. update database table.

  2. verify that the inserted data is correct.

  3. select data from 2 or more tables related by common attribute (table column).

  4. delete data from database table.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

JOIN is the SQL keyword used to retrieve data from two or more tables based on a related column between them. It combines rows from different tables that satisfy specified conditions. JOIN is not used for updating, verifying data correctness, or deleting operations - those use UPDATE, validation logic, and DELETE respectively.

Multiple choice technology databases

Which SQL statement selects all rows from table called Contest, with column ContestDate having values greater or equal to May 25, 2006?

  1. SELECT * FROM Contest HAVING ContestDate >= '05/25/2006'

  2. SELECT * FROM Contest WHERE ContestDate >= '05/25/2006'

  3. SELECT * FROM Contest WHERE ContestDate < '05/25/2006'

  4. SELECT * FROM Contest IN ContestDate < '05/25/2006'
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

SELECT * FROM Contest WHERE ContestDate >= '05/25/2006' correctly retrieves all columns from the Contest table filtering for dates on or after May 25, 2006. The WHERE clause is used for filtering rows, not HAVING which is for filtering after GROUP BY. Option C uses less than instead of greater than or equal, while D uses invalid SQL syntax.

Multiple choice technology databases

Can you use the UPDATE and SELECT clauses in one SQL statement?

  1. You can if you use nested SQL statements

  2. You can use UPDATE and SELECT clauses together, even if you don’t have nested SQL statements.

  3. You can if you use WITH clause.

  4. You can't
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

You can use UPDATE and SELECT together through nested SQL statements where the SELECT serves as a subquery within the UPDATE statement. While WITH clauses and certain database-specific features can also combine these operations, nested statements represent the fundamental technique. Option A correctly identifies the nested statement approach.

Multiple choice technology databases

What does the FROM SQL keyword specify?

  1. The FROM SQL keyword specifies a column list

  2. The FROM SQL keyword specifies a search condition.

  3. The FROM SQL keyword specifies the tables, views, and joined tables used in SELECT, UPDATE and DELETE SQL statements.

  4. The FROM SQL keyword specifies a find condition.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The FROM keyword in SQL identifies the source tables, views, or joined tables from which data is retrieved or modified. Distractors are incorrect because column lists are specified by SELECT, search conditions are defined by WHERE, and there is no standard "find condition" keyword in SQL.

Multiple choice technology databases

The LIKE SQL keyword is used along with ...

  1. ORDER BY clause.

  2. GROUP BY clause.

  3. JOIN clause.

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

To use the LIKE SQL keyword, the user needs to know that it is used for pattern matching in SQL queries. It is used to search for a specific pattern within a column of a table.

The correct answer is:

D. WHERE clause.

Explanation:

The LIKE keyword is used along with the WHERE clause to filter the results based on a specific pattern. The WHERE clause is used to extract only those records that fulfill a specified condition. The LIKE keyword is used to specify the pattern that the user wants to match.

A. ORDER BY clause: This clause is used to sort the results in ascending or descending order based on one or more columns of a table.

B. GROUP BY clause: This clause is used to group the results based on one or more columns of a table. It is typically used along with aggregate functions like SUM, COUNT, AVG, etc.

C. JOIN clause: This clause is used to combine two or more tables based on a related column between them.

D. WHERE clause: This clause is used to filter the results based on a specific condition. The LIKE keyword is used along with this clause to match a specific pattern within a column.

Therefore, option D is the correct answer.

Multiple choice technology databases

Which of the following is a SQL aggregate function

  1. join

  2. len

  3. left

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

AVG is a SQL aggregate function that calculates the average value of numeric data in a column. The join keyword is for combining tables, len returns string length, and left is a string function for extracting substrings. None of these are aggregate functions, which return a single calculated value from multiple rows.

Multiple choice technology databases

The JOIN is a SQL keyword used to ...

  1. update database table

  2. verify that the inserted data is correct

  3. select data from 2 or more tables related by common attribute (table column).

  4. delete data from database table
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

JOIN combines rows from two or more tables based on a related column between them. It is fundamental for relational database queries where data is normalized across multiple tables.

Multiple choice technology databases

We refer to a join as a self-join when…

  1. we are using left and right join together

  2. we are joining more than 2 tables

  3. we are joining table to itself

  4. when joining a table and its view
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

A self-join occurs when a table is joined to itself, typically to compare rows within the same table. This is done by using table aliases to treat the same table as two different tables in the join.