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 platforms and products

Trigger rule is performed when

  1. Database Save

  2. Database Update

  3. Database Deletion

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

Trigger rules execute whenever database operations occur on objects in PEGA. This includes creating new objects (save), modifying existing objects (update), and removing objects (delete) from the database. The trigger mechanism allows automatic execution of business logic at these data modification points.

Multiple choice technology platforms and products

Use Edit Input rules (Rule-Edit-Input) to

  1. convert input data to a standard format

  2. test prperty values

  3. perform a database INSERT

  4. define and enforce comparison relationships among property values
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Edit Input rules (Rule-Edit-Input) are used to convert or transform user input data into a standard format before it's processed. They can perform conversions like changing case, formatting values, or transforming data types. They are not used for testing values (that's Validate rules), database operations, or defining comparison relationships (that's Declare Expressions).

Multiple choice technology platforms and products

For short medium lists which change often following should be specified in TableEdit tab of Property

  1. Local List

  2. Field Value

  3. Remote List

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

Field Value should be specified for lists that change frequently since it allows dynamic retrieval rather than hardcoded values. Local Lists are for static values that don't change often, while Remote Lists connect to external data sources and Prompt Lists are for user input.

Multiple choice technology

Which of the following is the way to force a restriction?

  1. Create a conditional select using the appropriate database function.

  2. Infer a where clause using the where statement of an objects properties.

  3. Apply a restriction set to a group of users that forces a row level where clause.

  4. A and C

  5. All of the above
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

All three methods (A, B, C) represent valid ways to apply restrictions in database and Business Intelligence contexts. Conditional selects with database functions (A), where clauses on object properties (B), and restriction sets forcing row-level where clauses (C) are all standard techniques for data restriction. Option E correctly identifies that all these approaches are valid.

Multiple choice technology

What statement is true with respect to Aggregate Tables in universe

  1. These tables contain precalculated data

  2. They are used to speed up the execution of queries

  3. Both

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

Aggregate tables contain pre-calculated summarized data (A is true), which significantly speeds up query execution by avoiding on-the-fly aggregations (B is true). Both statements are correct, making Option C the right answer. Aggregate tables are a fundamental performance optimization technique in universe design for Business Intelligence applications.

Multiple choice technology databases

What is true about subqueries ?

  1. A single row subquery can retrieve data from only one table.

  2. A SQL query statement cannot display data from table B that is referred to in its subquery,unless table B is included in the main query's FROM clause.

  3. A SQL query statement can display data from table B that is referred to in its subquery, without including table B in its own FROM clause

  4. A single row subquery can retrieve data from more than one table.
Reveal answer Fill a bubble to check yourself
B,D Correct answer
Explanation

Subqueries can retrieve data from multiple tables regardless of whether they're single-row or multiple-row types. However, any column you want to display in your SELECT list must come from tables in the main query's FROM clause - you cannot display columns from tables that only appear in the subquery.

Multiple choice technology databases

What is true about an equijoin?

  1. You can join a maximum of two tables through an equijoin.

  2. You can join a maximum of two columns through an equijoin.

  3. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.

  4. To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns.

  5. You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To solve this question, the user needs to have knowledge of SQL and database concepts related to joining tables.

A. This option is incorrect because you can join any number of tables through an equijoin, not just a maximum of two.

B. This option is incorrect because you can join any number of columns through an equijoin, not just a maximum of two.

C. This option is correct. In a SQL statement, you can join n tables (all having single column primary keys) by specifying a minimum of n-1 join conditions.

D. This option is incorrect because while it is true that you can join two tables through an equijoin, the columns in the join condition do not have to be primary key and foreign key columns. They just need to have matching values.

E. This option is incorrect because you specify an equijoin condition in the WHERE clause of a SELECT statement.

Therefore, the correct answer is:

The Answer is: C. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.

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.