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 of the following SQL statements can calculate and return the absolute value of -33?

  1. SELECT ABS(-33) "Absolute" FROM DUAL;

  2. SELECT ABS(-33), Absolute FROM DUAL;

  3. SELECT ABS("-33") Absolute FROM DUAL;

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

To solve this question, the user needs to know the syntax for calculating the absolute value in SQL. The ABS function can be used to calculate the absolute value of a number.

Now, let's go through each option and explain why it is right or wrong:

A. SELECT ABS(-33) "Absolute" FROM DUAL; This option is correct. The ABS function is used to calculate the absolute value of a number, and the syntax in this statement is correct. The "Absolute" alias is used to rename the column with the calculated value.

B. SELECT ABS(-33), Absolute FROM DUAL; This option is incorrect. The "Absolute" alias is not defined in the statement. Additionally, the ABS function is used correctly.

C. SELECT ABS("-33") Absolute FROM DUAL; This option is incorrect. The quotation marks around the number indicate that it is a string, not a number. The ABS function cannot calculate the absolute value of a string.

D. None of these This option is incorrect. Option A is the correct answer.

The Answer is: A

Multiple choice technology databases

What does the following SQL script do? SELECT AVG(MAX(salary)) FROM employees GROUP BY department_id;

  1. it has a syntax error, the AVG clause is not valid

  2. it calculates the average of the maximum salaries of all the departments

  3. it has a syntax error, the MAX clause is not valid

  4. it has no error, but the GROUP BY clause is not effective
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

GROUP BY divides employees by department first. MAX(salary) finds each department's highest salary. AVG then averages those departmental maximums into a single value representing the mean of top salaries across departments.

Multiple choice technology databases

In a PL/SQL block structure, which parts are optional?

  1. a) DELCARE and BEGIN

  2. b) DECALRE and EXCEPTION

  3. c) EXCEPTION and END

  4. d) BEGIN and END
Reveal answer Fill a bubble to check yourself
A,B Correct answer
Explanation

In PL/SQL block structure, only the EXECUTE section is mandatory. DECLARE (for declarations) and EXCEPTION (for exception handling) are both optional. BEGIN marks the start of the executable section and is required. END marks the end of the block and is required. Therefore, both DECLARE and EXCEPTION can be omitted in a simple PL/SQL block.

Multiple choice technology databases

A Rollback statement cannot be used to close transaction.

  1. True

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

The statement is False because ROLLBACK can be used to close a transaction. When you issue a ROLLBACK statement, it undoes all changes made in the current transaction and effectively ends/closes that transaction. After a ROLLBACK, a new transaction begins with the next SQL statement. COMMIT also closes a transaction. Both ROLLBACK and COMMIT are transaction control statements that terminate the current transaction.

Multiple choice technology databases

You want to create a PL/SQL block of code that calculates discounts on customer orders. This code will be invoked from several places, but only within the program unit ORDERTOTAL. What is the most appropriate location to store the code that calculates the discounts?

  1. a) A stored procedure on the server.

  2. b) A block of code in a PL/SQL library.

  3. c) A standalone procedure on the client machine.

  4. d) A block of code in the body of the program unit ORDERTOTAL.

  5. e) A local subprogram defined within the program unit ORDERTOTAL.
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Option E is correct because the code is only used within the program unit ORDERTOTAL. A local subprogram (procedure or function defined within ORDERTOTAL) is the most appropriate choice - it encapsulates the logic, makes it reusable within the unit, and keeps the scope limited. Stored procedures (A) would make it globally available on the server, which is unnecessary. PL/SQL libraries (B) or standalone procedures (C) are also overkill for local-only use. Option D is incorrect because putting code directly in the body doesn't make it reusable from multiple places within ORDERTOTAL.

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.