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 of these actions cannot be triggered by a query in rule studio?

  1. renaming the rule

  2. changing the priority of rule

  3. moving a rule to a target package

  4. delete the rule
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Renaming a rule cannot be triggered by a query operation in Rule Studio, as renaming is a metadata operation that requires direct action. Queries can trigger priority changes, package moves, and rule deletions as these are runtime operations. Renaming changes the rule's identifier which queries cannot do.

Multiple choice technology databases

SELECT LAST_DAY('15-MAR-00') FROM DUAL.

  1. 30-MAR-00

  2. 31-MAR-00

  3. 15-MAR-00

  4. THERE IS NO FUNCTION LIKE LAST_DAY
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The Oracle SQL function LAST_DAY() returns the last day of the month for any given date. Since March always has 31 days, LAST_DAY('15-MAR-00') correctly returns '31-MAR-00'. This function is useful for calculating month-end dates in financial and reporting applications.

Multiple choice technology databases

create global temporary table employee (eno number, ename varchar(20));

  1. Error generates

  2. Temporary table is created with name "employee" and it get removed at the end of transaction.

  3. similar to normally creating table.

  4. created in database permenantly
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

GLOBAL TEMPORARY TABLE creates a table structure visible to all sessions but data is private to each session and temporary. Unlike permanent tables (D), the data is not persisted. The table definition persists but data is automatically cleaned up either at transaction end or session end depending on ON COMMIT settings. The syntax shown is valid SQL (Oracle), so no error is generated (A is incorrect).

Multiple choice technology databases

This is the name of the database object containing information about all comments on table....

  1. USER_COMMENTS

  2. USER_ALL_COMMENTS

  3. USER_TAB_ALL

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

USER_TAB_COMMENTS is the Oracle data dictionary view that stores comments on tables and views owned by the current user. It contains columns for TABLE_NAME, TABLE_TYPE, and COMMENTS. The other options (USER_COMMENTS, USER_ALL_COMMENTS, USER_TAB_ALL) are not valid Oracle system views. Comments can be added using COMMENT ON TABLE table_name IS 'text'.

Multiple choice technology databases

CREATE TABLE Persons( P_Id int NOT NULL AUTO_INCREMENT=100, LastName varchar(255) NOT NULL);

  1. When trying to enter NULL value for P_Id then 100 will be placed there.

  2. P_Id value starts from 1 and then it is auto incremented by value 1 upto 100.

  3. Generates error as there is no constraint like "AUTO_GENERATE".

  4. P_Id value starts from 100 and then it is auto incremented by value 1.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

In MySQL, the syntax AUTO_INCREMENT=100 at the column definition level (or table level depending on SQL dialect variations) is meant to set the initial value of the autoincrement sequence. The correct option identifies that values will start from 100 and increment by 1. Note that standard SQL dialects might require this at the table level, but in this context, option 4 is the intended correct answer.

Multiple choice technology databases

What are the Sql Cursor Attributes in Pl/sql?

  1. SQL%ROWCOUNT, SQL%ISFOUND,SQL%NOTFOUND,SQL%ISOPEN

  2. SQL%ROWCOUNT, SQL%FOUND,SQL%NOTFOUND,SQL%OPEN

  3. SQL%ROWCOUNT, SQL%FOUND,SQL%NOTFOUND,SQL%ISOPEN

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

The four standard implicit cursor attributes in Oracle PL/SQL are SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, and SQL%ISOPEN. SQL%ISFOUND and SQL%OPEN do not exist in PL/SQL. Therefore, the option containing SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND, SQL%ISOPEN is correct.

Multiple choice technology databases

What is true about stored procedures?

  1. A stored procedure uses the DELCLARE keyword in the procedure specification to declare formal parameters

  2. A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification.

  3. A stored procedure must have at least one executable statement in the procedure body.

  4. A stored procedure uses the DECLARE keyword in the procedure body to declare formal parameters.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

A stored procedure must have at least one executable statement in its body (even NULL). It need not have parameters (B wrong). Parameters are declared in the specification, not with DECLARE keyword (A, D wrong).

Multiple choice technology databases

The OLD and NEW qualifiers can be used in which type of trigger?

  1. Row level DML trigger

  2. Row level system trigger

  3. Statement level DML trigger

  4. Row level application trigger
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To answer this question, the user needs to have a basic understanding of SQL triggers and the difference between row level and statement level triggers.

The OLD and NEW qualifiers are used in row level DML triggers. These qualifiers refer to the old and new values of the row being modified by the DML statement.

A row level trigger is executed once for every row affected by the triggering DML statement. This means that if a DML statement modifies multiple rows, the row level trigger will be executed multiple times, once for each affected row.

Option A is correct because row level DML triggers are the only type of trigger that can use the OLD and NEW qualifiers to reference the old and new values of the row being modified.

Option B is incorrect because row level system triggers do not use the OLD and NEW qualifiers. System triggers are used to perform administrative tasks related to the database system, such as logging database activity.

Option C is incorrect because statement level DML triggers do not use the OLD and NEW qualifiers. Statement level triggers are executed once for each triggering DML statement, rather than once for each affected row.

Option D is incorrect because row level application triggers do not exist.

Therefore, the answer is: A. Row level DML trigger.

Multiple choice technology databases

What is the use of %ROWTYPE in PL/SQL?

  1. %ROWTYPE allows you to associate a variable with an entire table row

  2. %ROWTYPE associates a variable with a single column type

  3. Both of the above

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

In PL/SQL, %ROWTYPE provides a record type that represents a row in a database table. The record fields have the same names and data types as the columns of the table, allowing you to associate a variable with an entire row.

Multiple choice technology databases

I have just now created sequence abc and If you immediately run the below select statement , what is the output? CREATE SEQUENCE abc MINVALUE 1 MAXVALUE 10 START WITH 1 INCREMENT BY 1 CACHE 20 Select abc.currval from dual;

  1. 1

  2. Null

  3. Error

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

In Oracle, a sequence's CURRVAL pseudocolumn cannot be used until NEXTVAL has been called at least once in the current session. Since the sequence was just created and NEXTVAL has not been called, attempting to select CURRVAL will raise an error: ORA-08002: sequence ABC.CURRVAL is not yet defined in this session.