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

Under which situation do you create a server side procedure?

  1. When the procedure contains no SQL statements.

  2. When the procedure contains no PL/SQL commands.

  3. When the procedure needs to be used by many client applications accessing several remote databases.

  4. When the procedure needs to be used by many users accessing the same schema objects on a local database.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Server-side procedures are stored in the database and execute on the database server. They're ideal when many users need to perform the same operations on the same data (same schema objects). This reduces network traffic and ensures consistency. Option C is wrong because that describes distributed scenarios needing client-side logic. Options A and B are nonsensical - a procedure without PL/SQL or SQL isn't a procedure.

Multiple choice technology databases

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

  1. When declaring arguments length is not allowed.

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

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

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

In PL/SQL stored procedure declarations, formal parameters must specify only the data type (e.g., VARCHAR2), and defining a specific length (like VARCHAR2(30)) is syntactically illegal.

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 control INSERT access via a procedure: (1) GRANT EXECUTE on the procedure to PUBLIC so users can call it, and (2) REVOKE INSERT on the table from PUBLIC to prevent direct inserts. The procedure then becomes the only insertion path. Options A and C grant wrong privileges. Option D grants INSERT on the procedure (incorrect - procedures aren't inserted into).

Multiple choice technology databases

Command to change SQL prompt name ?

  1. SQL> set sqlprompt “Qwest > “

  2. SQL> set prompt “Qwest > “

  3. SQL> sqlprompt set “Qwest > “

  4. SQL> prompt “Qwest > “
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

In SQL*Plus, the command set sqlprompt "Qwest > " is the correct syntax to change the default SQL command prompt display name.

Multiple choice technology databases

Query to view installed Oracle version information ?
  1. 1. select banner from v$version;
  2. 2. select version from v$instance;
  3. 3. select version from v$version;

  4. Both 1 and 2

  5. Both 2 and 3
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To view Oracle version: (1) SELECT banner FROM v$version; displays version banners for all components. (2) SELECT version FROM v$instance; shows instance version. Option 3 is invalid - v$version doesn't have a 'version' column (it has 'banner'). So 'Both 1 and 2' is correct, not 'Both 2 and 3'.

Multiple choice technology databases

Which one is not a valid pseudo-columns?

  1. USER

  2. UID

  3. CURVAL

  4. All are valid
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

CURVAL is not a valid Oracle pseudo-column, whereas CURRVAL is the correct spelling used to find the current value of a sequence. USER and UID are both valid pseudo-columns in Oracle SQL used to retrieve session information.

Multiple choice technology databases

Which procedure can be used to create a customized error message?

  1. RAISE_ERROR

  2. SQLERRM

  3. RAISE_APPLICATION_ERROR

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

To solve this question, the user needs to have knowledge of error handling in SQL.

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

A. RAISE_ERROR: This option is not a valid procedure to create a custom error message in SQL. RAISE_ERROR is not a standard Oracle SQL function.

B. SQLERRM: This option is not a procedure to create a custom error message, but it is a function that returns the error message associated with the most recently raised error exception. It is not used to raise custom exceptions.

C. RAISE_APPLICATION_ERROR: This option is correct. RAISE_APPLICATION_ERROR is a procedure used to create a customized error message in Oracle SQL. It allows an application to raise an error with a user-defined error message and error number.

D. RAISE_SERVER_ERROR: This option is not a valid procedure to create a custom error message in SQL. RAISE_SERVER_ERROR is not a standard Oracle SQL function.

Therefore, the correct answer is: C. RAISE_APPLICATION_ERROR

Multiple choice technology databases

What happens when rows are found using a FETCH statement ?

  1. It causes the cursor to close

  2. It loads the current row values into variables

  3. It causes the cursor to open

  4. It creates the variables to load the values
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

When rows are found using a FETCH statement in SQL, it loads the current row values into variables.

Option B is the correct answer. The FETCH statement is used to retrieve rows from a cursor, and each time it is executed, it fetches the next row from the cursor result set and assigns the values to the specified variables. This allows us to work with the data retrieved from the cursor.

Here is a breakdown of the other options:

A. It causes the cursor to close: This option is incorrect. The FETCH statement does not close the cursor; it only retrieves the rows. The cursor can be closed explicitly using the CLOSE statement.

C. It causes the cursor to open: This option is incorrect. The FETCH statement is used after the cursor has been declared and opened. It does not open the cursor itself.

D. It creates the variables to load the values: This option is incorrect. The variables to hold the row values need to be declared before the FETCH statement is executed. The FETCH statement does not create the variables, but rather assigns the retrieved values to the existing variables.

Therefore, the correct answer is B. It loads the current row values into variables.

Multiple choice technology databases

Examine this code: CREATE OR REPLACE STORED FUNCTION get_sal (p_raise_amt NUMBER, p_employee_id employees.employee_id%TYPE) RETURN NUMBER IS v_salary NUMBER; v_raise NUMBER(8,2); BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id = p_employee_id; v_raise := p_raise_amt * v_salary; RETURN v_raise; END; Which statement is true?

  1. a) This statement creates a stored procedure named get_sal.

  2. b) This statement returns a raise amount based on an employee id.

  3. c) This statement creates a stored function named get_sal with a status of invalid.

  4. d) This statement creates a stored function named get_sal.

  5. e) This statement fails.
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

The function declaration fails to compile because the variable v_email_home (or v_raise in the body) is mismatched or contains syntax errors, and the SQL query contains invalid syntax like [email protected] . instead of standard string literals.

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

A packaged function called from a SQL query cannot execute DML statements (INSERT, UPDATE, DELETE) against the table being queried, as this violates read-consistency rules and causes a runtime error.