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
  1. Examine this procedure: CREATE OR REPLACE PROCEDURE DELETE_PLAYER(V_IDIN NUMBER) IS BEGIN DELETE FROM PLAYER WHERE ID = V_ID EXCEPTION WHEN STATS_EXIST_EXCEPTION THEN DBMS_OUTPUT.PUT_LINE(Cannotdeletethisplayer, childrecordsexistin PLAYER_BAT_STAT table); END; What prevents this procedure from being created successfully?

  1. a) A comma has been left after the STATS_EXIST_EXCEPTION exception.

  2. b) The STATS_EXIST_EXCEPTION has not been declared as a number.

  3. c) The STATS_EXIST_EXCEPTION has not been declared as an exception.

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

To understand what prevents this procedure from being created successfully, we need to examine the given procedure. The procedure DELETE_PLAYER takes an input parameter V_IDIN and attempts to delete the corresponding record from the PLAYER table. It also has an exception block that handles the STATS_EXIST_EXCEPTION exception and outputs an error message.

Now, let's go through each option and see if it affects the creation of the procedure:

A. a) A comma has been left after the STATS_EXIST_EXCEPTION exception. This option is incorrect. There is no comma after the STATS_EXIST_EXCEPTION exception, so this cannot be the reason why the procedure cannot be created.

B. b) The STATS_EXIST_EXCEPTION has not been declared as a number. This option is incorrect. The STATS_EXIST_EXCEPTION is not being used as a number, so it does not need to be declared as such. It is being used as an exception.

C. c) The STATS_EXIST_EXCEPTION has not been declared as an exception. This option is correct. The STATS_EXIST_EXCEPTION exception has not been declared in the procedure. Without a declaration, the procedure does not know what to do when the exception is raised. To fix this, the STATS_EXIST_EXCEPTION should be declared as an exception before the BEGIN keyword.

D. d) none of the above This option is incorrect. As explained above, option C is the correct answer.

Therefore, the correct answer is:

The Answer is: C

Multiple choice technology databases

Which operator will be evaluated first in the following SELECT statement? SELECT (2+3*4/2–5) FROM dual;

  1. +

  2. *

  3. /

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

In the expression (2+3*4/2-5), operator precedence follows standard mathematical rules: multiplication () and division (/) are evaluated before addition (+) and subtraction (-). When operators have equal precedence ( and /), they're evaluated left to right. Therefore, 3*4=12 is computed first, then 12/2=6, then 2+6=8, then 8-5=3. The multiplication operator is evaluated first.

Multiple choice technology databases

The following statement will raise an exception on which line? select dept_name, avg(all salary) ,count() “number of employees” from emp , dept where deptno = dept_no and count() > 5 group by dept_name order by 2 desc;

  1. select dept_name, avg(all salary), count(*) “number of employees"

  2. where deptno = dept_no

  3. and count(*) > 5

  4. group by dept_name

  5. order by 2 desc;
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Aggregate functions like COUNT() cannot be used in the WHERE clause - they must appear in HAVING after GROUP BY. The line 'and count() > 5' is in the WHERE clause, which causes an error. To filter groups based on aggregate values, use HAVING after GROUP BY. The correct query should move that condition to a HAVING clause. The alias in quotes and the AVG(ALL salary) syntax are valid.

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. a) EXECUTE UPD_BAT_STAT;

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

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

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

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

Option A works because both parameters have DEFAULT values, so calling with no arguments uses the defaults. Option B is correct - named notation (V_AB=>10, V_ID=>31) explicitly specifies which parameter gets which value. Option C is wrong - too many arguments and wrong data types. Option D is wrong for SQL*Plus (needs EXECUTE keyword). Option E is wrong - RUN is for PL/SQL blocks, not stored procedures. Named notation is particularly useful when you want to skip default parameters.

Multiple choice technology databases

Which two describe a stored procedure? (Choose two)

  1. a) A stored procedure is typically written in SQL.

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

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

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

  5. e) 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

A stored procedure is indeed a named PL/SQL block that accepts parameters (B) and is a type of PL/SQL subprogram that performs an action (C). Option A is incorrect because stored procedures are written in PL/SQL, not SQL alone. Option D is wrong because procedures have declaration, executable, and exception sections, not 'specification, body, and exception handler'. Option E incorrectly describes the executable section.

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. a) SHOW FUNCTION_ERROR

  2. b) SHOW USER_ERRORS

  3. c) SHOW ERRORS

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

To solve this question, the user needs to know how to check for the errors in an SQL function.

The correct answer is:

C. SHOW ERRORS

Explanation:

When a function is created with compilation errors, the SHOW ERRORS command can be issued to see the actual error message. This command displays the compilation errors of the last CREATE or REPLACE FUNCTION or PACKAGE statement that was executed in the current session. Using this command can help identify and fix any syntax errors or other issues with the function code.

Option A is incorrect because there is no SHOW FUNCTION_ERROR command in SQL *Plus.

Option B is incorrect because SHOW USER_ERRORS is not a valid command in SQL *Plus.

Option D is incorrect because SHOW ALL_ERRORS is not a valid command in SQL *Plus.

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. a) You need to execute the command CALCTAX(1000); .

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

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

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

  5. e) 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 execute a PL/SQL function from SQL*Plus and capture its return value, you must create a bind variable and use the EXECUTE command with the variable assignment syntax (E). Option A is incorrect because you cannot call a function directly without handling the return value. Options B and D use incorrect syntax. Option C is missing the EXECUTE keyword required for anonymous blocks in SQL*Plus.

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. a) add_dept;

  2. b) add_dept( .Accounting .);

  3. c) add_dept(, .New York .);

  4. d) add_dept(p_location=> .New York .);
Reveal answer Fill a bubble to check yourself
A,B,D Correct answer
Explanation

All three valid calls use default parameters correctly: (A) uses both defaults, (B) provides only the first parameter, and (D) uses named notation to skip the first parameter and specify the second. Option C is invalid because it omits the first parameter without using named notation - you cannot skip positional parameters by leaving the argument empty.

Multiple choice technology databases

Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHER2(30)) IS BEGIN INSERT INTO PLAYER(ID, LAST_NAME) VALUES(V_ID, V_LAST_NAME); COMMIT; END; Why does this command fail when executed?

  1. a) When declaring arguments length is not allowed.

  2. b) When declaring arguments each argument must have a mode specified.

  3. c) When declaring arguments each argument must have a length specified.

  4. d) When declaring a VARCHAR2 argument it must be specified.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The procedure fails because PL/SQL procedure parameters cannot have length specifications - you use the type name only (VARCHAR2, not VARCHAR2(30)). Option A is correct. Option B is wrong because IN is the default mode. Option C is incorrect - parameters can have types without length. Option D is wrong because the type IS specified (VARCHAR2).

Multiple choice technology databases

When using a packaged function in a query, what is true?

  1. a) The COMMIT and ROLLBACK commands are allowed in the packaged function.

  2. b) You can not use packaged functions in a query statement.

  3. c) The packaged function cannot execute an INSERT, UPDATE, or DELETE statement against the table that is being queried.

  4. d) The packaged function can execute and INSERT, UPDATE, or DELETE statement against the table that is being queried if it is used in a subquery.

  5. e) The packaged function can execute an INSERT, UPDATEM or DELETE statement against the table that is being queried if the pragma RESTRICT REFERENCE is used.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Functions used in SQL queries cannot perform DML on the table being queried - this maintains read purity and prevents mutating table errors. Option C is correct. Option A is wrong - COMMIT/ROLLBACK are not allowed in SQL functions. Option B is incorrect - you CAN use packaged functions. Options D and E are wrong - DML against the queried table is never allowed in a function called from SQL, regardless of subquery or pragma.

Multiple choice technology databases

All users currently have the INSERT privileges on the PLAYER table. You want only your users to insert into this table using the ADD_PLAYER procedure. Which two actions must you take? (Choose two)

  1. a) GRANT SELECT ON ADD_PLAYER TO PUBLIC;

  2. b) GRANT EXECUTE ON ADD_PLAYER TO PUBLIC;

  3. c) GRANT INSERT ON PLAYER TO PUBLIC;

  4. d) GRANT EXECUTE, INSERT ON ADD_PLAYER TO PUBLIC;

  5. e) REVOKE INSERT ON PLAYER FROM PUBLIC;
Reveal answer Fill a bubble to check yourself
B,E Correct answer
Explanation

To force inserts through the procedure only: (B) grant EXECUTE on the procedure to PUBLIC so users can call it, and (E) revoke direct INSERT privileges on the table so users cannot bypass the procedure. Option A would allow reading the procedure code, not executing it. Option C is the opposite of what's needed. Option D is redundant - EXECUTE alone suffices.

Multiple choice technology databases

Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHER2(30)) IS BEGIN INSERT INTO PLAYER(ID, LAST_NAME) VALUES(V_ID, V_LAST_NAME); COMMIT; END; Why does this command fail when executed?

  1. a) When declaring arguments length is not allowed.

  2. b) When declaring arguments each argument must have a mode specified.

  3. c) When declaring arguments each argument must have a length specified.

  4. d) When declaring a VARCHAR2 argument it must be specified.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

This is a duplicate of 130230 with the same content. The procedure fails because PL/SQL procedure parameters cannot have length specifications - you use the type name only (VARCHAR2, not VARCHAR2(30)). Option A is correct. Option B is wrong because IN is the default mode. Option C is incorrect - parameters can have types without length. Option D is wrong because the type IS specified (VARCHAR2).

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. a) Row

  2. b) Statement

  3. c) ORACLE FORM trigger

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

The trigger fails because it uses :NEW.ID, which is row-level syntax, but the trigger is likely created as a statement-level trigger. In Oracle, row-level triggers can access :NEW and :OLD values for each row being processed, while statement-level triggers execute once per statement regardless of rows affected. Converting to a row trigger allows the :NEW reference to work correctly. The timing (BEFORE/AFTER) is not the issue here - it's specifically about row vs statement level.

Multiple choice technology databases

All users currently have the INSERT privileges on the PLAYER table. You want only your users to insert into this table using the ADD_PLAYER procedure. Which two actions must you take? (Choose two)

  1. a) GRANT SELECT ON ADD_PLAYER TO PUBLIC;

  2. b) GRANT EXECUTE ON ADD_PLAYER TO PUBLIC;

  3. c) GRANT INSERT ON PLAYER TO PUBLIC;

  4. d) GRANT EXECUTE, INSERT ON ADD_PLAYER TO PUBLIC;

  5. e) REVOKE INSERT ON PLAYER FROM PUBLIC;
Reveal answer Fill a bubble to check yourself
B,E Correct answer
Explanation

To restrict users to inserting data only through the ADD_PLAYER procedure, you must revoke direct INSERT privileges on the PLAYER table from the public and grant EXECUTE privileges on the procedure instead.