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 databases

Which of the following is not correct about a View ?

  1. To protect some of the columns of a table from other users

  2. Ocuupies data storage space

  3. To hide complexity of a query

  4. To hide complexity of a calculations
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

A view is a virtual table that does not occupy physical storage space - it stores only the query definition. Options A, C, and D describe legitimate uses of views: column security, simplifying complex queries, and hiding computational logic. Therefore, the statement that views occupy data storage space is the INCORRECT statement, making B the right choice.

Multiple choice technology mainframe

The keyword OPTIONAL in select clause is used only for

  1. Output files

  2. Input files

  3. Both the files

  4. All the above is correct
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

OPTIONAL specifies that a file may not be present at job execution time. For input files (READ mode), this prevents ABEND if the file is missing. Output files with optional status would create runtime errors if referenced but absent.

Multiple choice technology mainframe

SQL can

  1. Create Database

  2. Update Table

  3. Drop Views

  4. Reverse Table
Reveal answer Fill a bubble to check yourself
A,B,C Correct answer
Explanation

SQL is a comprehensive language that can create databases, update tables with INSERT/UPDATE, and drop views with DROP VIEW. However, SQL has no 'REVERSE TABLE' command - reversing table order is done through queries with ORDER BY, not a DDL operation.

Multiple choice technology mainframe

Which SQL statement is used to return only different values?

  1. SELECT DISTINCT

  2. SELECT DIFFERENT

  3. SELECT UNIQUE

  4. SELECT SAME
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The SELECT DISTINCT statement is the standard SQL syntax used to return only unique (different) values from a query, filtering out duplicate rows. SELECT UNIQUE is an older, non-standard synonym supported by some databases, but DISTINCT is the primary standard keyword.

Multiple choice technology databases

CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG (V_ID in PLAYER_BAT_STAT.PLAYER_ID%TYPE) RETURN NUMBER IS V_AVG NUMBER; BEGIN SELECT HITS / AT_BATS INTO V_AVG FROM PLAYER_BAT_STAT WHERE PLAYER_ID = V_ID; RETURN (V_AVG); END; Which statement will successfully invoke this function in SQL *Plus

  1. SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;

  2. EXECUTE CALC_PLAYER_AVG(31);

  3. CALC_PLAYER(‘RUTH’);

  4. CALC_PLAYER_AVG(31);
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Functions in Oracle/PLSQL must be invoked within a SQL statement like SELECT. Option A correctly embeds the function call in a SELECT statement, passing a column from the table. EXECUTE (B) is for procedures, not functions. Option C uses wrong function name. Option D lacks required SELECT wrapper - plain function calls aren't valid SQL.

Multiple choice technology databases

Which is the true statements about dependent objects?

  1. An object with status of invalid cannot be a referenced object.

  2. The Oracle server automatically records dependencies among objects.

  3. All schema objects have a status that is recorded in the data dictionary.

  4. You can view whether an object is valid or invalid in the USER_STATUS data dictionary view.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Oracle automatically tracks dependency relationships between schema objects (e.g., a view depends on its base table). When a referenced object changes, dependent objects are marked invalid. Option A is false - invalid objects CAN be referenced (they're just marked invalid). Option C is false - not all objects have status. Option D is false - the view is USER_OBJECTS, not USER_STATUS.

Multiple choice technology databases

You have created a stored procedure DELETE_TEMP_TABLE that uses dynamic SQL to remove a table in your schema. You have granted the EXECUTE privilege to user A on this procedure. When user A executes the DELETE_TEMP_TABLE procedure, under whose privileges are the operations performed by default?

  1. SYS privileges

  2. Your privileges

  3. Public privileges

  4. User A’s privileges
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

By default, stored procedures in Oracle execute with the privileges of the owner (definer's rights), not the invoker (user A). Therefore, the dynamic SQL operations run under the creator's/owner's privileges.

Multiple choice technology databases

CREATE OR REPLACE PRODECURE add_dept (p_dept_name VARCHAR2 DEFAULT ‘placeholder’, p_location VARCHAR2 DEFAULT ‘Boston’) IS BEGIN INSERT INTO departments VALUES (dept_id_seq.NEXTVAL, p_dept_name, p_location); END add_dept; / Which is invalid call to the add_dep procedure? (Choose three

  1. add_dept;

  2. add_dept(‘Accounting’);

  3. add_dept(, ‘New York’);

  4. add_dept(p_location=>’New York’);
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

PL/SQL does not support positional parameters with missing arguments like add_dept(, 'New York'). The stored answer correctly identifies this invalid call, but the question contains a typo ('Choose three' when only one option is correct or three are valid) and misspells the procedure name. However, since the option marked correct is indeed the only invalid call among the choices, the answer is valid.

Multiple choice technology databases

Which statement is valid when removing procedures?

  1. Use a drop procedure statement to drop a standalone procedure.

  2. Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package specification.

  3. Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package body.

  4. For faster removal and re-creation, do not use a drop procedure statement. Instead, recompile the procedure using the alter procedure statement with the REUSE SETTINGS clause.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

DROP PROCEDURE removes standalone procedures. Packaged procedures cannot be dropped individually - you must drop or recreate the entire package body. Option A is correct. Options B and C are wrong because DROP PROCEDURE doesn't work on packaged procedures. Option D is nonsense - REUSE SETTINGS doesn't exist and ALTER PROCEDURE doesn't remove procedures.

Multiple choice technology databases

Which statement is false?

  1. If errors occur during the compilation of a trigger, the trigger is still created.

  2. If errors occur during the compilation of a trigger you can go into SQL *Plus and query the USER_TRIGGERS data dictionary view to see the compilation errors.

  3. If errors occur during the compilation of a trigger you can use the SHOW ERRORS command within iSQL *Plus to see the compilation errors.

  4. If errors occur during the compilation of a trigger you can go into SQL *Plus and query the USER_ERRORS data dictionary view to see compilation errors.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The question asks which statement is FALSE. Options A, C, and D are TRUE - triggers with compilation errors are still created (as INVALID), and you CAN view errors via SHOW ERRORS or USER_ERRORS. Option B claims you can use USER_TRIGGERS to see compilation errors, which is FALSE - compilation errors are in USER_ERRORS, not USER_TRIGGERS.

Multiple choice technology databases

What happens during the execute phase with dynamic SQL for INSERT, UPDATE, and DELETE operations?

  1. The rows are selected and ordered.

  2. The validity of the SQL statement is established.

  3. An area of memory is established to process the SQL statement.

  4. The SQL statement is run and the number of rows processed is returned.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

In dynamic SQL processing, the execute phase runs the actual SQL statement and returns the count of affected rows. For INSERT/UPDATE/DELETE, this is when data modification happens and row counts are returned. Selecting/ordering rows (A) happens for queries. Validity checking (B) is parse phase. Memory allocation (C) is define/prepare phase.

Multiple choice technology databases

What part of a database trigger determines the number of times the trigger body executes?

  1. Trigger type

  2. Trigger body

  3. Trigger event

  4. Trigger timing
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Trigger TYPE determines execution frequency. Row-level triggers fire once per ROW affected. Statement-level triggers fire once per STATEMENT regardless of rows. Trigger body (B) is the code executed. Trigger event (C) is what fires it (INSERT/UPDATE/DELETE). Trigger timing (D) is before/after/in stead-of.