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

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.

Multiple choice technology databases

SQL is a procedural language?

  1. True

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

SQL is a declarative language, not procedural. In SQL, you specify what data you want (declarative), not how to retrieve it (procedural). The database engine determines the optimal execution plan. Procedural languages like C or Java require step-by-step instructions including loops and conditional logic.

Multiple choice technology databases

Catch_#22 valid column name?

  1. True

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

Catch_#22 is a valid column name in SQL. While it looks unusual, SQL allows column names to contain underscores and numbers. The rules vary by database: some require quotes for special characters, but alphanumeric names with underscores are generally acceptable. The # character might require quoting in some databases, but the name itself is syntactically possible.

Multiple choice technology databases

In a select with a group by clause, a where clause, and a having clause, the where conditions are applied before the having conditions?

  1. True

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

In SQL query execution, WHERE clause filters individual rows BEFORE grouping, while HAVING clause filters groups AFTER aggregation. The logical order is: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE cannot use aggregate functions, but HAVING can. This is why WHERE conditions are applied first to reduce the row set before grouping.

Multiple choice technology databases

Which statement about a table is true?

  1. A table can have up to 10,000 columns.

  2. The size of a table does NOT need to be specified

  3. A table CANNOT be created while users are using the database

  4. The structure of a table CANNOT be modified while the table is online
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

When creating a table in a relational database, you do not need to specify its physical size, as storage is allocated dynamically. Tables can be created while users are active, and their structures can be modified online.

Multiple choice technology databases

ALTER TABLE inventory ENABLE CONSTRAINT inventory_id_pk; Which statement is true?

  1. The statement will achieve the desired results

  2. The statement will execute, but will NOT enable the PRIMARY KEY constraint.

  3. The statement will execute, but will NOT verify that values in the ID column do NOT violate the constraint.

  4. The statement will return a syntax error.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The ALTER TABLE...ENABLE CONSTRAINT command re-enables a previously disabled constraint. This will successfully enable the PRIMARY KEY constraint inventory_id_pk and validate existing data, achieving the desired result.