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 web technology

When a table is created :

  1. By default it is accessible to all users.

  2. By default they are created in the public schema.

  3. By default tables are created in your schema.

  4. By default they are created in the DBA schema.

  5. You must specify the schema when the table is created.
Reveal answer Fill a bubble to check yourself
E Correct answer
Multiple choice technology databases

What type of object privileges can be granted on a view?

  1. DELETE, INSERT,SELECT

  2. DELETE, INSERT, SELECT, UPDATE

  3. ALTER, DELETE, INSERT, SELECT

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

On a relational database view, users can be granted DML privileges including SELECT, INSERT, UPDATE, and DELETE. ALTER privileges cannot be granted on a view because the structure of a view is modified by recreating it rather than altering it.

Multiple choice technology databases

Two reasons to create table alias :

  1. You have too many tables.

  2. You have too many columns in your tables.

  3. You want to work on your own tables.

  4. You want to use another schema's tables.

  5. Your tables have difficult names.

  6. Your tables are too long.
Reveal answer Fill a bubble to check yourself
D,E Correct answer
Explanation

Table aliases serve two main purposes: when working with tables from another schema (qualifying schema names gets tedious), and when tables have long or difficult names that make queries hard to read. Aliases improve query readability and reduce typing. The number of tables or columns alone doesn't dictate alias use.

Multiple choice technology databases

When the full outer join is used?

  1. You want all unmatched data from both tables.

  2. One of the tables has more data than the other.

  3. You want all matched data from both tables.

  4. You want all matched and unmatched data from only one table.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

FULL OUTER JOIN returns all matched and unmatched records from both tables. Unlike INNER JOIN (only matched) or LEFT/RIGHT OUTER JOIN (all from one side + matched from other), FULL OUTER JOIN ensures no data is lost from either table. Rows without matches show NULL values for the non-matching side's columns.

Multiple choice technology databases

Which constraint can only be applied at the column level?

  1. Primary Key

  2. Check

  3. Not Null

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

NOT NULL constraints must be specified at the column level because they directly define whether a single column can accept null values. Other constraints like PRIMARY KEY, UNIQUE, and CHECK can be defined at either the column level or the table level (especially when spanning multiple columns).

Multiple choice technology databases

What is true about WHERE clause ?

  1. A WHERE clause can be used to restrict rows only.

  2. A WHERE clause CANNOT be used in a query if the query uses a HAVING clause.

  3. WHERE clause can be used to restrict both rows and groups.

  4. WHere clause filters the columns from the output
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The WHERE clause restricts rows from the database table before group formation, while HAVING is used to restrict groups. WHERE cannot filter columns, which is the function of the SELECT clause, nor does it conflict with HAVING.

Multiple choice technology databases

What is true about the Truncate statement?

  1. It is faster than the Delete operation.

  2. You can roll back the deletion of rows after the statement executes.

  3. You must be the owner of the table or have DELETE ANY TABLE system privileges

  4. It releases the storage space used by the table.
Reveal answer Fill a bubble to check yourself
A,C,D Correct answer
Explanation

TRUNCATE is a DDL statement, making it much faster than DELETE since it resets the high-water mark and releases storage space rather than generating individual undo logs. It requires table ownership or DELETE system privileges, and cannot be rolled back.

Multiple choice technology databases

WHat is not true about the constaints ?

  1. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.

  2. The UNIQUE constraint does not permit a null value for the column

  3. The NOT NULL constraint ensures that null values are not permitted for the column

  4. The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index
Reveal answer Fill a bubble to check yourself
B,D Correct answer
Explanation

The UNIQUE constraint DOES permit null values (unlike PRIMARY KEY), and typically allows multiple nulls since null != null. PRIMARY KEY and FOREIGN KEY constraints do create unique indexes automatically. The NOT NULL statement about UNIQUE constraints is the correct falsehood among these.

Multiple choice technology databases

Once a sequence is created

  1. It is automatically used in all INSERT and UPDATE

  2. It is linked to a specific table.

  3. It belongs to a specific schema.

  4. It is automatically available to all users.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Sequences are independent database objects that belong to a specific schema (like tables, views, and other schema objects). They are not automatically used in any statements, not linked to specific tables, and not available to all users unless granted. A sequence must be explicitly referenced (e.g., NEXTVAL) and can be used across multiple tables.

Multiple choice technology databases

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 - One or Many,Manufacturer - One or Many

  3. Product - Many,Manufacturer - One

  4. Product - One,Manufacturer - One

  5. Product - One,Manufacturer - Many
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

A product has one manufacturer, but a manufacturer can produce many products. Therefore, the relationship from Product to Manufacturer is Many-to-One, meaning many products map to one manufacturer.

Multiple choice technology databases

You have been asked to construct a query in the company's RDBMS. You have deployed a Right Outer Join operation. Referring to the scenario above, what will happen to the final results when there is NO match between the tables?

  1. The right table will return ALL rows.

  2. The right table will return NULL.

  3. Both tables will return NULL.

  4. The left table will return ALL rows.

  5. The left table will return NULL.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

A Right Outer Join returns all rows from the right table, even if there are no matches in the left table. When no match is found, the columns from the left table will contain NULL values in the result set.

Multiple choice technology databases

For performance, you denormalize your database design and create some redundant columns. Referring to the scenario above, what RDBMS construct can you use to automatically prevent the repeated columns from getting out of sync?

  1. Cursors

  2. Constraints

  3. Views

  4. Stored procedures

  5. Trigger
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Triggers automatically execute in response to data changes (INSERT/UPDATE/DELETE), making them ideal for synchronizing denormalized data across multiple tables to prevent inconsistent redundant values.

Multiple choice technology databases

You are running a query against a relational database. Referring to the scenario above, what clause or command do you use in the query to help avoid a costly tablescan?

  1. GROUP BY clause

  2. INDEX command

  3. HAVING clause

  4. FROM clause

  5. WHERE clause [/quote]
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To avoid a costly tablescan, the database engine uses an index to locate rows quickly. The INDEX command creates this database object. The WHERE clause filters results but relies on indices to avoid scans, and clauses like GROUP BY or HAVING do not prevent table scans.

Multiple choice technology web technology

Given there is non null data for Login column of Employee table, Running the statement "SELECT [LOGIN] FROM EMPLOYEE WHERE [LOGIN] <> NULL " will give the correct result only after executing which statement?

  1. SET ANSI_NULLS ON

  2. SET ANSI_NULLS OFF

  3. SET CONCAT_NULL_YIELDS_NULL ON

  4. SET CONCAT_NULL_YIELDS_NULL OFF
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

When ANSI_NULLS is ON, the comparison operator '<>' does not work with NULL values - comparisons to NULL always return UNKNOWN. Setting ANSI_NULLS OFF allows the '<>' operator to work as expected with NULL comparisons. Options A and D are incorrect settings - CONCAT_NULL_YIELDS_NULL controls string concatenation behavior, not NULL comparisons.

Multiple choice technology databases

The query performance increases by using NOT operator.

  1. True

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

The NOT operator typically degrades query performance because it prevents efficient index usage and often requires scanning more rows. Query optimizers struggle to use indexes effectively with NOT conditions. Optimized queries avoid NOT and use equivalent positive conditions instead.