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

With SQL, how do you select all the records from a table named "Persons" where the "FirstName" is "Peter" and the "LastName" is "Jackson"?

  1. SELECT FirstName='Peter', LastName='Jackson' FROM Persons

  2. SELECT * FROM Persons WHERE FirstName='Peter' AND LastName='Jackson'

  3. SELECT * FROM Persons WHERE FirstName<>'Peter' AND LastName<>'Jackson'

  4. SELECT WHERE FirstName='Peter' AND LastName='Jackson' FROM Persons
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The WHERE clause filters rows after SELECT *, and AND combines multiple conditions. Option B is the only syntactically correct choice - it uses proper SELECT * FROM with WHERE containing both conditions joined by AND.

Multiple choice technology databases

With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?

  1. SELECT LastName>'Hansen' AND LastName<'Pettersen' FROM Persons

  2. SELECT * FROM Persons WHERE LastName>'Hansen' AND LastName<'Pettersen'

  3. SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'

  4. SELECT * FROM Persons WHERE LastName IN 'Hansen' AND 'Pettersen'
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To solve this question, the user needs to know how to use SQL to select specific records from a table based on certain criteria. Specifically, they need to know how to use the WHERE clause to filter records based on a specified range of values.

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

A. SELECT LastName>'Hansen' AND LastName<'Pettersen' FROM Persons This option is incorrect because it uses an invalid syntax to filter the records based on the range of values. The AND operator used here is incorrect. When combining conditions, we use the OR operator. Therefore, this option is incorrect.

B. SELECT * FROM Persons WHERE LastName>'Hansen' AND LastName<'Pettersen' This option is correct. It uses the correct syntax to filter the records based on the range of values. The WHERE clause is used to filter the records with the specified LastName that is between the "Hansen" and "Pettersen" range.

C. SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen' This option is correct. It uses the correct syntax to filter the records based on the range of values. The BETWEEN keyword is used to specify that we want all the records with LastName between 'Hansen' and 'Pettersen' range.

D. SELECT * FROM Persons WHERE LastName IN 'Hansen' AND 'Pettersen' This option is incorrect because the IN keyword is used to select records where the LastName matches any of the specified values in a list. In this case, we want to select the records that are between the range of values. Therefore, this option is incorrect.

The Answer is: C or B

Multiple choice technology databases

With SQL, how can you return all the records from a table named "Persons" sorted descending by "FirstName"?

  1. SELECT * FROM Persons ORDER FirstName DESC

  2. SELECT * FROM Persons SORT BY 'FirstName' DESC

  3. SELECT * FROM Persons ORDER BY FirstName DESC

  4. SELECT * FROM Persons SORT 'FirstName' DESC
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The correct syntax is ORDER BY column DESC, not ORDER or SORT. Option C is correct - it uses ORDER BY with DESC keyword. Option A is missing 'BY', and B/D use SORT which isn't valid SQL.

Multiple choice technology databases

With SQL, how can you insert a new record into the "Persons" table?

  1. INSERT INTO Persons VALUES ('Jimmy', 'Jackson')

  2. INSERT ('Jimmy', 'Jackson') INTO Persons

  3. INSERT VALUES ('Jimmy', 'Jackson') INTO Persons

  4. INSERT ('Jimmy', 'Jackson') INTO Persons
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

INSERT INTO table VALUES (...) is the correct syntax. The table name must come after INTO, not at the end. Option A follows this pattern correctly.

Multiple choice technology databases

With SQL, how can you insert "Olsen" as the "LastName" in the "Persons" table?

  1. INSERT ('Olsen') INTO Persons (LastName)

  2. INSERT INTO Persons (LastName) VALUES ('Olsen')

  3. INSERT INTO Persons ('Olsen') INTO LastName

  4. INSERT FROM Persons ('Olsen') INTO LastName
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The standard SQL syntax to insert records into a table is 'INSERT INTO table_name (columns) VALUES (values)'. The other options utilize incorrect keyword structures, wrong order of arguments, or non-existent SQL syntax.

Multiple choice technology databases

How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?

  1. UPDATE Persons SET LastName='Hansen' INTO LastName='Nilsen'

  2. UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'

  3. MODIFY Persons SET LastName='Hansen' INTO LastName='Nilsen

  4. MODIFY Persons SET LastName='Nilsen' WHERE LastName='Hansen'
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

UPDATE table SET column='new' WHERE condition is the correct syntax. SET defines the change, WHERE filters which rows to update. Option B is correct - it uses SET with WHERE clause properly.

Multiple choice technology databases

With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons Table?

  1. DELETE FROM Persons WHERE FirstName = 'Peter'

  2. DELETE FirstName='Peter' FROM Persons

  3. DELETE ROW FirstName='Peter' FROM Persons

  4. DELETE FROM Persons WHERE FirstName <> 'Peter'
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

DELETE FROM table WHERE condition removes matching records. The WHERE clause is crucial - without it, all rows would be deleted. Option A is the only correct syntax.

Multiple choice technology databases

With SQL, how can you return the number of records in the "Persons" table?

  1. SELECT COUNT() FROM Persons

  2. SELECT COLUMNS() FROM Persons

  3. SELECT COLUMNS(*) FROM Persons

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

In SQL, the COUNT function returns the number of rows. Specifying COUNT(*) counts all rows including NULLs and duplicates. SELECT COUNT() without arguments is syntactically invalid in standard SQL, and COLUMNS is not a built-in count function.

Multiple choice technology architecture

Which java clas represents SQL statements that are complied before they are executed?

  1. Statement

  2. PreparedStatement

  3. ResultSet.

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

PreparedStatement represents SQL statements that are pre-compiled and can be executed multiple times with different parameter values. This offers better performance for repeated queries and protection against SQL injection. Unlike regular Statements, PreparedStatements allow you to set parameters using placeholders.

Multiple choice technology architecture

Which java clas represents SQL statements that are complied before they are executed

  1. Statement

  2. PreparedStatement

  3. ResultSet.

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

A PreparedStatement represents a precompiled SQL statement that is compiled once by the database management system and can be executed multiple times with different parameters. The standard Statement compiles SQL on every execution, while ResultSet holds query results and CallableStatement is for stored procedures.

Multiple choice technology

Which of the following are true about Shared fields

  1. Shared fields behave like fields, but may be used in different forms.

  2. If we change the properties of a shared field, the changes are promoted to all occurrences of this field.

  3. If we delete a shared field, the field will automatically get deleted from all the forms which are using it.

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

Shared fields in FileMaker Pro act as reusable field definitions: they behave like regular fields (A), changes propagate to all occurrences (B), and deletion removes them from all forms (C). Since all three statements are true, 'All of these' is the correct answer.

Multiple choice technology

Required fields for EIM table

  1. IF_ROW_BATCH_NUM, IF_ROW_STAT, CONFLICT_ID

  2. ROW_ID, IF_ROW_BATCH_NUM, conflict_id

  3. ROW_ID, IF_ROW_BATCH_NUM, IF_ROW_STAT

  4. ROW_ID, IF_ROW_BATCH_NUM
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

EIM tables require ROW_ID (unique identifier), IF_ROW_BATCH_NUM (batch tracking), and IF_ROW_STAT (status tracking like 0=success). These are mandatory columns for EIM processing. CONFLICT_ID is optional.