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

Whice transformation do we use to define conditions to commit and rollback records from relational, XML, and dynamic IBM MQSeries targets?

  1. Stored Procedure Transformation

  2. XML Transformation

  3. HTTP Transformation

  4. Transaction Control Transformation
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Transaction Control Transformation in Informatica PowerCenter is specifically designed to manage commit and rollback operations for various target types including relational databases, XML sources, and IBM MQSeries targets. It allows you to define conditions that determine when to commit or rollback transactions based on business logic or data conditions. The other transformations listed serve different purposes - Stored Procedures execute database procedures, XML handles XML data, and HTTP makes web service calls.

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

The SQL command used to comment multiple lines is

  1. /* */

  3. rem

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

SQL uses /* / for multi-line comments, similar to many C-style languages. Anything between / and / is treated as a comment and ignored by the SQL parser. The ** option is not valid SQL comment syntax, and 'rem' is used in some SQL dialects like SQL Server for single-line comments, but not for multi-line blocks.

Multiple choice technology programming languages

Consider an emp table having columns empno, empname, salary Which of the following queries sort emp table by empname and then by salary in descending order

  1. select empno, empname, salary from emp order by empname, salary desc;

  2. select empno from emp sort by empname, sal in desc;

  3. select empno, empname, salary from emp sort in empname, salary desc;

  4. select # from emp sort by empname and salary in desc;
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The correct query is 'SELECT empno, empname, salary FROM emp ORDER BY empname, salary DESC'. This sorts by empname first (ascending by default), then by salary in descending order within each empname group. Options B and C incorrectly use 'SORT BY' instead of 'ORDER BY' (SQL uses ORDER BY). Option D has syntax errors with '#', 'sort by', and 'in desc' - the correct syntax is ORDER BY with DESC keyword.