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

which one is not a dedup sort parameter

  1. select

  2. output_select

  3. keep

  4. check-sort
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In Ab Initio Dedup Sorted component, the standard parameters are 'select' (to choose input fields), 'keep' (first, last, or unique), and 'check-sort' (to verify input is sorted). 'output_select' is NOT a parameter of the Dedup Sorted component - it's used in other components like Reformat. This question tests knowledge of specific component parameters.

Multiple choice technology databases

In the Table employee_Details there are two columns: Employee_Id-Integer[10] and Employee_Name-Char[10],The following select query Select distinct(Employee_Name) from employee_Details where Employee_Id='1232' fails due to:

  1. The field mentioned in the where clause should be one of the columns selected

  2. The distinct function can not be used on a field of type Char

  3. The Integer value mentioned in the where clause should not be enclosed by quotes since it is of integer type.

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

In SQL, when a WHERE clause filters on an INTEGER column, the comparison value must not be enclosed in quotes. The query WHERE Employee_Id='1232' treats '1232' as a string literal, which may cause type mismatch errors or implicit conversion issues depending on the database system. The correct syntax is WHERE Employee_Id=1232.

Multiple choice technology databases

Fill Up: The UNION operator selects only distinct values by default. To allow duplicate values, use __________

  1. UNION ENTIRE

  2. FULL UNION

  3. UNION ALL

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

UNION removes duplicate rows by default, combining the result sets and eliminating duplicates. UNION ALL preserves all rows including duplicates from both SELECT statements, which is more efficient when you don't need duplicate elimination. The options 'UNION ENTIRE' and 'FULL UNION' are not valid SQL keywords.

Multiple choice technology databases

The following query fails. Select employee_name,employee_id,count() from employee_Details group by employee_id where count()>2; The right format is:

  1. Select employee_name,count() from employee_Details group by employee_id where count()>2;

  2. Select employee_name,employee_id,count() from employee_Details group by employee_id having count()>2;

  3. Select employee_name,employee_id,count() from employee_Details order by employee_id where count()>2;

  4. NONE of THE ABOVE
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The SQL query fails because aggregate functions like count(*) cannot be evaluated in a WHERE clause. To filter groups created by GROUP BY, the HAVING clause must be used. Note that the query also selects employee_name which is not in the GROUP BY clause, but the syntax error of WHERE count(*) is resolved by changing it to HAVING count(*) in standard SQL execution rules.

Multiple choice technology databases

Select * from employee_Details with ur; in the follwoing query UR stands for:

  1. Under Read

  2. neither a,c or d

  3. Uncommitted Read

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

In DB2 and other SQL databases, UR stands for Uncommitted Read, an isolation level that allows reading uncommitted data. This is also known as a dirty read isolation level. Option A (Under Read) and D (Uncommitted Ride) are not standard SQL terminology.

Multiple choice technology databases

An application program containing query with UR

  1. can read data that has been changed and committed.

  2. can read data that has been changed but is not yet committed.

  3. both a and b

  4. neither a or b
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

UR (Uncommitted Read) isolation level allows reading both committed and uncommitted data. An application using UR can read data that has been changed but not yet committed, as well as committed data. The question asks what UR 'can' do, and it can do both - the 'both a and b' option correctly captures this flexibility.

Multiple choice technology databases

Select the right syntax to delete all the rows from the table employee_Details but retain the table :

  1. delete * from employee_Details

  2. delete from employee_Details

  3. truncate table employee_Details

  4. drop table employee_Details
Reveal answer Fill a bubble to check yourself
B,C Correct answer
Explanation

Both DELETE FROM table (without WHERE clause) and TRUNCATE TABLE table remove all rows while keeping the table structure. DELETE is logged and can be rolled back, while TRUNCATE is a DDL operation that's faster but cannot be rolled back. 'DELETE * FROM' is incorrect syntax, and DROP TABLE removes the entire table structure.

Multiple choice technology architecture 
CREATE TABLE Purchaser{ 
      name string,
      credit_card integer (PRIMARY KEY), 
      address string 
}
CREATE TABLE Product{ 
    product_id integer (PRIMARY KEY), 
    product_name string 
}
CREATE TABLE Purchaser_Product{ 
    credit_card integer, 
    product_id integer, 
    purchase_date date 
}

Based on the sample code above, what is the primary key in the Purchaser_Product table?

  1. purchase_date

  2. Credit_card

  3. null

  4. credit_card & product_id

  5. product_id
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

In the association table Purchaser_Product, a composite primary key consisting of credit_card and product_id is required to uniquely identify the relationship between a purchaser and a product, as neither column is marked as a unique primary key individually.

Multiple choice technology architecture

PRODUCT ======= Product ID Product Description Manufacturer ID MANUFACTURER ============ Manufacturer ID Manufacturer Name Referring to the above table, what type of relationship exists between the Product table and the Manufacturer table?

  1. Product - Many Manufacturer - Many

  2. Product - Many Manufacturer - One

  3. Product - One Manufacturer - One

  4. Product - One Manufacturer – Many
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The Product table contains Manufacturer ID as a foreign key, indicating each product is associated with one manufacturer. The Manufacturer table has Manufacturer ID as primary key. Since many products can reference the same manufacturer ID, this is a Many-to-One relationship: many Products to one Manufacturer.

Multiple choice technology databases

Mark all the indexes which are their in SQL server

  1. Bit Map

  2. clustered

  3. Non clustered

  4. filter

  5. covering

  6. Composite
Reveal answer Fill a bubble to check yourself
B,C,D,E,F Correct answer
Explanation

SQL Server has multiple index types: Clustered indexes (one per table, stores data physically sorted), Non-clustered indexes (multiple per table, store separate pointer structures), Filtered indexes (non-clustered indexes with a WHERE clause), Covering indexes (non-clustered indexes that include all columns needed for a query), and Composite indexes (indexes on multiple columns). Bit Map indexes exist in Oracle but not SQL Server.

Multiple choice technology databases

How to find out no. of objects in a database.

  1. Sp_find

  2. select * from Sys.sysobjects

  3. select * from sys.dm_exec_objects

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

The sys.sysobjects system view contains one row for each object in the database, making it ideal for counting objects. While sys.objects is preferred in modern SQL Server versions, sys.sysobjects is also available. The other options are either invalid procedures or incorrect dynamic management views.

Multiple choice technology programming languages

Can a SEARCH be applied to a table which does not have an INDEX defined?

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

A SEARCH statement in COBOL requires the table to have an INDEX defined for the table being searched. The SEARCH verb uses the index to efficiently navigate through table elements. Without a defined index, the SEARCH operation cannot function properly as it relies on index-based table access.