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

This statement fails when executed: CREATE OR REPLACE TRI GGER CALC_TEAM_AVG AFTER I NSERT ON PLAYER BEGIN INSERT INTO PLAYER_BATSTAT ( PLAYER_I D, SEASON_YEAR, AT_BATS, HI TS) VALUES ( : NEW. I D, 1 997, 0, 0) ; END; To which type must you convert the trigger to correct the error?

  1. Row

  2. Statement

  3. ORACLE FORM trigger

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

The trigger fails because it references :NEW.ID, which is only available in row-level triggers. A statement-level trigger (default) cannot access :NEW or :OLD values since it fires once per statement, not once per affected row. Converting to a row-level trigger (FOR EACH ROW) allows access to :NEW values for each inserted row.

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 - it cannot be empty (just NULL is executable). Option A is wrong because it's DECLARE (not DELCLARE) and DECLARE is used in the body for local variables, not formal parameters. Option B is wrong because procedures don't require parameters - parameterless procedures are valid. Option D is wrong because DECLARE is for local variables in the body, not formal parameters in the specification.

Multiple choice technology databases

Examine this procedure: CREATE OR REPLACE PROCEDURE UPD_BAT_STAT (V_ID IN NUMBER DEFAULT 10, V_AB IN NUMBER DEFAULT 4) IS BEGIN UPDATE PLAYER_BAT_STAT SET AT_BATS = AT_BATS + V_AB WHERE PLAYER_ID = V_ID; COMMIT; END; Which two statements will successfully invoke this procedure in SQL *Plus? (Choose two)

  1. EXECUTE UPD_BAT_STAT;

  2. EXECUTE UPD_BAT_STAT(V_AB=>10, V_ID=>31);

  3. EXECUTE UPD_BAT_STAT(31, 'FOUR', 'TWO');

  4. UPD_BAT_STAT(V_AB=>10, V_ID=>31);

  5. RUN UPD_BAT_STAT;
Reveal answer Fill a bubble to check yourself
A,B Correct answer
Explanation

The procedure has two parameters with DEFAULT values (V_ID=10, V_AB=4), so it can be called with no arguments - option A works. Option B uses named notation correctly and overrides defaults. Option C is wrong because it passes 3 arguments but only 2 exist, and 'FOUR'/'TWO' are strings where numbers expected. Option D is wrong because EXECUTE keyword is required in SQL*Plus. Option E is wrong because RUN is a SQL*Plus command for scripts, not procedures.

Multiple choice technology databases

Examine this code: CREATE OR REPLACE FUNCTION gen_email_name (p_first_name VARCHAR2, p_last_name VARCHAR2, p_id NUMBER) RETURN VARCHAR2 IS v_email_name VARCHAR2(19); BEGIN v_email_name := SUBSTR(p_first_name, 1, 1) || SUBSTR(p_last_name, 1, 7) || [email protected] .; UPDATE employees SET email = v_email_name WHERE employee_id = p_id; RETURN v_email_name; END; Examine this code: CREATE OR REPLACE PACKAGE comm_package IS g_comm NUMBER := 10; PROCEDURE reset_comm(p_comm IN NUMBER); END comm_package; / User Jones executes the following code at 9:01am: EXECUTE comm_package.g_comm := 15 User Smith executes the following code at 9:05am: EXECUTE comm_paclage.g_comm := 20 Which statement is true?

  1. g_comm has a value of 15 at 9:06am for Smith.

  2. g_comm has a value of 15 at 9:06am for Jones.

  3. g_comm has a value of 20 at 9:06am for both Jones and Smith.

  4. g_comm has a value of 15 at 9:03 am for both Jones and Smith.

  5. g_comm has a value of 10 at 9:06am for both Jones and Smith.

  6. g_comm has a value of 10 at 9:03am for both Jones and Smith.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Package variables are instantiated per-session. Jones's change to g_comm affects only Jones's session, so Jones sees 15, while Smith's session remains independent.

Multiple choice technology databases

Which three are valid ways to minimize dependency failure? (Choose three)

  1. Querying with the SELECT * notification.

  2. Declaring variables with the %TYPE attribute.

  3. Specifying schema names when referencing objects.

  4. Declaring records by using the %ROWTYPE attribute.

  5. Specifying package.procedure notation while executing procedures.
Reveal answer Fill a bubble to check yourself
A,B,D Correct answer
Explanation

SELECT * with notifications (A) automatically captures schema changes. %TYPE attribute (B) anchors variables to column datatypes, reducing coupling. %ROWTYPE attribute (D) mirrors table structure for records, minimizing maintenance when tables change. Schema names (C) increase coupling by hardcoding references. Package.procedure notation (E) doesn't affect dependency failure - it's just execution syntax.

Multiple choice technology databases

Which two describe a stored procedure? (Choose two)

  1. A stored procedure is typically written in SQL.

  2. A stored procedure is a named PL/SQL block that can accept parameters.

  3. A stored procedure is a type of PL/SQL subprogram that performs an action.

  4. A stored procedure has three parts: the specification, the body, and the exception handler part.

  5. The executable section of a stored procedure contains statements that assigns values, control execution, and return values to the calling environment.
Reveal answer Fill a bubble to check yourself
B,C Correct answer
Explanation

Stored procedures in PL/SQL are named program units that can accept parameters (B) and are subprograms that perform specific actions (C). Option A is misleading because stored procedures use PL/SQL (which includes SQL), not pure SQL. Option D incorrectly describes the structure; procedures have a header (specification) and body, but not a separate 'exception handler part'. Option E describes what an executable section does but doesn't define what a stored procedure is.

Multiple choice technology databases

A CALL statement inside the trigger body enables you to call ______.

  1. A package.

  2. A stored function.

  3. A stored procedure.

  4. Another database trigger.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Within a trigger body, the CALL statement is used to invoke a stored procedure. Triggers can call stored procedures to execute complex business logic or perform operations that cannot be done directly in trigger SQL. You cannot CALL packages, functions (use direct invocation), or other triggers from within a trigger.

Multiple choice technology databases

Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHAR2) IS BEGIN INSERT INTO PLAYER (ID,LAST_NAME) VALUES (V_ID, V_LAST_NAME); COMMIT; END; This procedure must invoke the APD_BAT_STAT procedure and pass a parameter. Which statement, when added to the above procedure will successfully invoke the UPD_BAT_STAT procedure?

  1. EXECUTE UPD_BAT_STAT(V_ID);

  2. UPD_BAT_STAT(V_ID);

  3. RUN UPD_BAT_STAT(V_ID);

  4. START UPD_BAT_STAT(V_ID);
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In PL/SQL, you invoke a stored procedure by simply writing its name followed by parameters in parentheses. You do not use keywords like EXECUTE (SQL*Plus command), RUN, or START. The correct syntax is just the procedure name with parameters: UPD_BAT_STAT(V_ID).

Multiple choice technology databases

When creating a function in SQL *Plus, you receive this message: .Warning: Function created with compilation errors.. Which command can you issue to see the actual error message?

  1. SHOW FUNCTION_ERROR

  2. SHOW USER_ERRORS

  3. SHOW ERRORS

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

The SHOW ERRORS command in SQL*Plus displays the compilation errors for the most recently created PL/SQL object (function, procedure, package, etc.). This is the standard way to view the specific error details after receiving a compilation warning. Options A, B, and D are not valid SQL*Plus commands.

Multiple choice technology databases

Examine this code: CREATE OR REPLACE FUNCTION gen_email_name (p_first_name VARCHAR2, p_last_name VARCHAR2, p_id NUMBER) RETURN VARCHAR2 is v_email_name VARCHAR2(19); BEGIN v_email_home := SUBSTR(p_first_name, 1, 1) || SUBSTR(p_last_name, 1, 7) || [email protected] .; UPDATE employees SET email = v_email_name WHERE employee_id = p_id; RETURN v_email_name; END; You run this SELECT statement: SELECT first_name, last_name gen_email_name(first_name, last_name, 108) EMAIL FROM employees; What occurs?

  1. Employee 108 has his email name updated based on the return result of the function.

  2. The statement fails because functions called from SQL expressions cannot perform DML.

  3. The statement fails because the functions does not contain code to end the transaction.

  4. The SQL statement executes successfully, because UPDATE and DELETE statements are ignoring in stored functions called from SQL expressions.

  5. The SQL statement executes successfully and control is passed to the calling environment.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The SELECT statement fails at runtime because the function gen_email_name attempts to perform an UPDATE operation. Functions called within SQL queries are not permitted to execute DML statements.

Multiple choice technology databases

Given a function CALCTAX : CREATE OR REPLACE FUNCTION calc tax (sal NUMBER) RETURN NUMBER IS BEGIN RETURN (sal * 0.05); END; If you want to run the above function from the SQL *Plus prompt, which statement is true?

  1. You need to execute the command CALCTAX(1000); .

  2. You need to execute the command EXECUTE FUNCTION calc tax; .

  3. You need to create a SQL *Plus environment variable X and issue the command :X := CALCTAX(1000); .

  4. You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX;

  5. You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX(1000);
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

To run the given function from the SQL*Plus prompt, the user needs to know the correct syntax for invoking a function in Oracle SQL. The function CALCTAX takes in a single parameter, sal, and returns the result of sal * 0.05.

Option A is incorrect because it is missing the keyword SELECT to return the result of the function.

Option B is incorrect because the keyword FUNCTION is not used in the EXECUTE statement, and the argument sal is not provided.

Option C is incorrect because SQL*Plus environment variables are not required to execute a function, and it is missing the keyword SELECT to return the result of the function.

Option D is incorrect because it is missing the argument sal.

Option E is the correct answer because it first declares an SQL*Plus environment variable X, executes the function CALCTAX with the argument 1000, and assigns the result to the variable X. The keyword SELECT is not required because the result is assigned to a variable.

Therefore, the answer is: E. You need to create a SQL*Plus environment variable X and issue the command EXECUTE :X := CALCTAX(1000);

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

To remove a standalone procedure from the database, use the DROP PROCEDURE statement. Procedures inside a package cannot be dropped individually; the entire package body must be modified and recompiled.

Multiple choice technology databases

Examine this code: 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 three are valid calls 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
A,B,D Correct answer
Explanation

In PL/SQL, procedures with DEFAULT parameters can be called using those defaults. Option A uses both defaults. Option B provides the first parameter positionally, second uses default. Option C is invalid - you cannot skip a positional parameter with a comma (must use named notation). Option D uses named notation for the second parameter, first uses default. Named notation allows specifying parameters out of order or skipping earlier ones.

Multiple choice technology databases

Examine this function: 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);

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

The function CALC_PLAYER_AVG returns a value and can be successfully invoked inside a standard SQL query by passing a column name as an argument.