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 databases

Which SQL statement accepts user input for the columns to be displayed, the table name, and the WHERE condition? A. SELECT &1, "&2" FROM &3 WHERE last_name = '&4'; B. SELECT &1, '&2' FROM &3 WHERE '&last_name = '&4''; C. SELECT &1, &2 FROM &3 WHERE last_name = '&4'; D. SELECT &1, '&2' FROM EMP WHERE last_name = '&4';

  1. A

  2. B

  3. C

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

Substitution variables without quotes like &1 and &2 allow dynamic column and table names, while using single quotes around &4 is correct for literal string values.

Multiple choice technology databases

In which scenario would an index be most useful? A. The indexed column is declared as NOT NULL. B. The indexed columns are used in the FROM clause. C. The indexed columns are part of an expression. D. The indexed column contains a wide range of values.

  1. A

  2. B

  3. C

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

Indexes are most effective when the indexed column has high selectivity, meaning it contains a wide range of unique values to efficiently filter out unwanted rows.

Multiple choice technology databases

Which SELECT statement should you use to extract the year from the system date and display it in the format "1998"? A. SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual; B. SELECT TO_DATE(SYSDATE,'yyyy') FROM dual; C. SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY') FROM dual; D. SELECT DECODE(SUBSTR(SYSDATE, 8), 'year') FROM dual; E. SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy') FROM dual;

  1. A

  2. B

  3. C

  4. D

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

TO_CHAR converts a date value to a string with the specified format. Option A correctly extracts the year from SYSDATE and displays it in 'yyyy' format (e.g., '1998'). TO_DATE (B) converts string to date, the opposite direction. DECODE (C, D) is for conditional logic, not date formatting. Option E incorrectly mixes SUBSTR string manipulation with TO_CHAR.

Multiple choice technology databases

Which are iSQL*Plus commands? A. INSERT B. UPDATE C. SELECT D. DESCRIBE E. DELETE F. RENAME

  1. A

  2. B

  3. C

  4. D

  5. E

  6. F
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

iSQL*Plus commands (also called SQL*Plus commands) are environment-specific commands like DESCRIBE, ACCEPT, DEFINE, etc. They are not SQL statements. INSERT, UPDATE, SELECT, DELETE are SQL DML/DRL commands. RENAME is a SQL DDL command. Only DESCRIBE is an iSQL*Plus command among the options.

Multiple choice technology databases

Consider this syntax MERGE INTO t1 USING t2 ON (join predicate) .... What does the MERGE syntax do ?

  1. It performs a merge join of the row from T2 only if it does'nt exist in the T1 table.

  2. It creates a natural join of table T1 and T2 for all columns that have the same name.

  3. It creates a Cartesian prodcut of table T1 and table T2 for all columns that have the same name.

  4. For each row from T2, it updates the row if it exists witin table T1, otherwise it inserts the row into T1.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

MERGE updates rows in T1 that match the join predicate from T2, and inserts rows from T2 that don't match. It's an 'upsert' operation. Option A incorrectly says 'only if it doesn't exist' - MERGE does both update and insert. Option B incorrectly describes a natural join. Option C incorrectly describes a Cartesian product.

Multiple choice technology databases

You need to enforce these two business rules: 1. No two rows of a table can have duplicate values in the specified column. 2. A column cannot contain null values. Which type of constraint ensure that both of the above rules are true ?

  1. Check

  2. Unique

  3. Not Null

  4. Primary Key

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

A Primary Key constraint enforces uniqueness (no duplicate values in the specified column) and prohibits null values (acting as a NOT NULL constraint). Unique constraints allow nulls, and Check or Not Null constraints do not enforce uniqueness alone.

Multiple choice technology databases

A tablespace has a table with 30 extents in it. Is this bad?

  1. True

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

Having 30 extents for a table is not inherently problematic. In locally managed tablespaces with uniform extent sizes, Oracle automatically manages extents efficiently. The number of extents becomes a concern primarily in dictionary-managed tablespaces or when it causes performance issues due to excessive extent allocation.

Multiple choice technology databases

John has created a procedure named SALARY_CALC. Which SQL query allow him to view text of procedure?

  1. SELECT text FROM user_source WHERE name = 'SALARY_CALC';

  2. SELECT * FROM user_source WHERE name='SALARY_CALC';

  3. SELECT * FROM user_objects WHERE object_name='SALARY_CALC';

  4. SELECT * FROM user_procedures WHERE object_name='SALARY_CALC';

  5. SELECT text FROM user_source WHERE name='SALARY_CALC' and owner='JOHN'
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To view the text of a stored procedure, query USER_SOURCE with the procedure name in the NAME column and select the TEXT column. USER_SOURCE stores the source code line by line. The TEXT column specifically contains the source code lines, while USER_OBJECTS and USER_PROCEDURES contain metadata but not the actual source text.

Multiple choice technology databases

Consider this syntax MERGE INTO t1 USING t2 ON (join predicate) .... What does the MERGE syntax do ?

  1. It performs a merge join of the row from T2 only if it does'nt exist in the T1 table.

  2. It creates a natural join of table T1 and T2 for all columns that have the same name.

  3. It creates a Cartesian prodcut of table T1 and table T2 for all columns that have the same name.

  4. For each row from T2, it updates the row if it exists witin table T1, otherwise it inserts the row into T1.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

MERGE performs an upsert operation: for each row from the source (T2), it updates the target row if it exists in T1 based on the join condition, or inserts the row if it doesn't exist. MERGE is not a join type - it's a data manipulation command. It doesn't create natural joins or Cartesian products. The ON clause specifies the matching condition between source and target tables.

Multiple choice technology databases

Pick the odd one out

  1. PLAN_TABLE

  2. EMPLOYEE_MASTER

  3. DSN_STATEMNT_TABLE

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

SYSTABLES is a DB2 system catalog table that automatically stores metadata about all tables in the database. The others (PLAN_TABLE, EMPLOYEE_MASTER, DSN_STATEMNT_TABLE) are either user-created or auxiliary tables for specific purposes like explain plans.

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

what is true about function now();

  1. Returns current system date and time.

  2. Returns only current time

  3. Returns only current date.

  4. There is no function like now();
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The NOW() function in SQL returns the current system date and time together. It does not return date or time separately - those would be CURDATE() and CURTIME() respectively. The function exists in all major SQL databases.

Multiple choice technology databases

Which statement removes the function?

  1. DROP gen_email_name;

  2. REMOVE gen_email_name;

  3. DELETE gen_email_name;

  4. DROP FUNCTION gen_eamil_name;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

DROP FUNCTION is the correct syntax to remove a stored function. Option D has correct syntax despite typo 'eamil_name' (question likely meant gen_email_name). DROP alone, REMOVE, and DELETE are incorrect.