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

Which of the following is considered as a fatal error

  1. key constraint violations

  2. Loading nulls into a not null field

  3. Database trigger responses

  4. Lack of database space to load data
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Fatal errors in Informatica are conditions that completely stop session execution. Key constraint violations (A) and loading nulls into not-null fields (B) are data-level errors but don't always halt execution - they may be redirected or handled based on session settings. Database trigger responses (C) depend on trigger logic. However, lack of database space (D) is a system-level failure that cannot be handled within the session - it's truly fatal.

Multiple choice technology

The mode/modes in which a SQL transformation can be run is

  1. Script mode

  2. Bulk mode

  3. Query mode

  4. Normal mode
Reveal answer Fill a bubble to check yourself
A,C Correct answer
Explanation

SQL transformation in Informatica can operate in Script mode (executes SQL scripts) or Query mode (executes single queries). Bulk mode and Normal mode are not valid execution modes for SQL transformation.

Multiple choice technology databases

Sub-queries can be nested in

  1. DELETE statements only

  2. UPDATE, DELETE, INSERT and SELECT statements

  3. UPDATE statements only

  4. INSERT statements only
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

SQL subqueries are versatile and can be nested within all four major DML statements: SELECT, INSERT, UPDATE, and DELETE. This allows for complex data manipulation where the inner query's results determine which rows are affected by the outer statement. For example, you can use a subquery in an UPDATE statement to filter rows based on values from another table, or in DELETE to remove rows matching specific criteria from a subquery.

Multiple choice technology databases

Which SQL statement inserts data into a table called Projects?

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

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

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

  4. INSERT Projects ('Content Development', 'Website content development project')
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The correct SQL INSERT syntax requires the INSERT INTO clause, followed by the table name and optional column list in parentheses, then the VALUES keyword with matching data in parentheses. Option C demonstrates this correctly with proper column specification (ProjectName, ProjectDescription) and corresponding values. Options A and D are missing the INTO keyword and proper column structure, while option B incorrectly uses 'SAVE INTO' which is not valid SQL syntax.

Multiple choice technology databases

Which of the following SQL statements is correct?

  1. SELECT FROM Sales WHERE Date BETWEEN '10/12/2005' AND '01/01/2006'

  2. SELECT * FROM Sales WHERE Date BETWEEN '10/12/2005' AND '01/01/2006'

  3. SELECT FROM Sales WHERE Date BETWEEN ('10/12/2005', '01/01/2006')

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

Option B is correct because it uses proper SQL syntax with SELECT * to retrieve all columns, and the BETWEEN operator correctly filters dates. Option A is missing the column specification (* or specific columns), and Option C has incorrect parentheses around the date values.

Multiple choice technology databases

Which of the following statements gets the total value of the column 'Price' in the 'Sales' table

  1. SELECT ADD(Price) FROM Sales

  2. SELECT TOTAL(Price) FROM Sales

  3. SELECT SUM(Price) FROM Sales

  4. SELECT SUM(Price) WHERE Sales
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

SUM() is the standard SQL aggregate function for calculating the total of numeric values in a column. Option A uses a non-existent ADD() function, Option B uses TOTAL() which is not standard SQL, and Option D is missing the FROM clause.

Multiple choice technology databases

The difference between the DELETE and TRUNCATE SQL clauses is

  1. The TRUNCATE clause deletes all rows in a database table, while the DELETE clause can have a WHERE condition and might or might not delete all rows in a table

  2. The TRUNCATE clause is identical to the DELETE clause

  3. The DELETE clause deletes all rows in a database table, while the TRUNCATE clause can have a WHERE condition and might or might not delete all rows in a table

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

TRUNCATE removes all rows from a table without a WHERE clause and cannot be conditional, while DELETE can use WHERE to selectively remove rows. TRUNCATE is faster and resets identity values, whereas DELETE logs individual row deletions.

Multiple choice technology databases

select lpad(3400,10,*) from dual returns

  1. ******3400

  2. 3400******

  3. 3400

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

The LPAD function requires the third parameter to be a string, not a literal asterisk character. The correct syntax would be LPAD(3400,10,'*') with quotes around the padding character. Using * without quotes causes a syntax error.

Multiple choice technology databases

With SQL, how do you select all the records from a table named 'Persons' where the value of the column 'FirstName' starts with an 'a'?

  1. SELECT * FROM Persons WHERE FirstName LIKE 'a%'

  2. SELECT * FROM Persons WHERE FirstName LIKE '%a'

  3. SELECT * FROM Persons WHERE FirstName='a'

  4. SELECT * FROM Persons WHERE FirstName='%a%'
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The LIKE operator with 'a%' matches any string starting with 'a'. Option B would end with 'a', Option C is an exact match, and Option D would contain 'a' anywhere.

Multiple choice technology programming languages

SQL support which of the following data model

  1. Hierarchical Database Model

  2. Relational Database Model

  3. Network Database Model

  4. File Management Model
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

SQL (Structured Query Language) is designed specifically for Relational Database Management Systems (RDBMS). The relational model, based on mathematical set theory, organizes data into tables with rows and columns, which SQL manipulates. SQL does not support Hierarchical or Network database models (these are older models), and File Management is a lower-level OS concept, not a data model.