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

A user wishing to invoke an SQL stored procedure that queries a table must have which of the following privileges?

  1. CALL privilege on the procedure; SELECT privilege on the table

  2. CALL privilege on the procedure; REFERENCES privilege on the table

  3. EXECUTE privilege on the procedure; SELECT privilege on the table

  4. EXECUTE privilege on the procedure; REFERENCES privilege on the table
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To execute a stored procedure, a user needs EXECUTE privilege on the procedure itself. Since the procedure queries a table, the user also needs SELECT privilege on that table. The CALL privilege applies to procedures in some databases, but EXECUTE is the standard DB2 terminology for both stored procedures and user-defined functions.

Multiple choice technology databases

User USER1 wants to utilize an alias to remove rows from a table. Assuming USER1 has no authorities or privileges, which of the following privileges are needed?

  1. DELETE privilege on the table

  2. DELETE privilege on the alias

  3. DELETE privilege on the alias; REFERENCES privilege on the table

  4. REFERENCES privilege on the alias; DELETE privilege on the table
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Aliases do not have their own privileges. To perform operations through an alias, the user must hold the appropriate privilege on the underlying table. Therefore, the DELETE privilege on the base table is required, while privileges on the alias itself are not valid or needed.

Multiple choice technology databases

Which of the following statements allows user USER1 to take the ability to create packages in a database named SAMPLE away from user USER2?

  1. REVOKE CONNECT ON DATABASE FROM user2

  2. REVOKE CREATETAB ON DATABASE FROM user2

  3. REVOKE BIND ON DATABASE FROM user2

  4. REVOKE BINDADD ON DATABASE FROM user2
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

BINDADD is the database-level privilege that allows users to create packages within a database. REVOKE BINDADD ON DATABASE FROM user2 would remove USER2's ability to create packages. CONNECT allows database connection, CREATETAB allows creating tables, and BIND allows binding packages but not creating them.

Multiple choice technology databases

Which of the following will provide user USER1 and all members of the group GROUP1 with the ability to perform DML, but no other operations on table TABLE1?

  1. GRANT INSERT, UPDATE, DELETE, SELECT ON TABLE table1 TO user1 AND group1

  2. GRANT INSERT, UPDATE, DELETE, SELECT ON TABLE table1 TO USER user1, GROUP group1

  3. GRANT ALL PRIVILEGES EXCEPT ALTER, INDEX, REFERENCES ON TABLE table1 TO USER user1, GROUP group1

  4. GRANT CONTROL ON TABLE table1 TO user1 AND group1
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

DML operations include INSERT, UPDATE, DELETE, and SELECT. Option B grants exactly these four privileges to both USER1 and GROUP1. Option A incorrectly uses 'AND' instead of proper syntax. Option C would grant more than DML (it includes INDEX and REFERENCES implicitly). Option D's CONTROL privilege is too broad - it includes DDL rights.

Multiple choice technology databases

USER1 is the owner of TABLE1. Assuming USER1 only holds privileges for TABLE1, which of the following is the best way to remove all privileges USER1 holds?

  1. REVOKE CONTROL ON table1 FROM user1

  2. REVOKE ALL PRIVILEGES ON table1 FROM user1

  3. REVOKE CONTROL ON table1 FROM user1; REVOKE ALL PRIVILEGES ON table1 FROM user1

  4. REVOKE CONTROL, ALL PRIVILEGES ON table1 FROM user1
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

As the table owner, USER1 holds both CONTROL privilege (ownership-level authority) AND ALL PRIVILEGES (individual rights like SELECT, INSERT, etc.). Removing only CONTROL or only ALL PRIVILEGES would leave some privileges intact. Option C correctly revokes both - CONTROL removes ownership-level rights, and ALL PRIVILEGES removes any granted individual privileges.

Multiple choice technology databases

A table called DEPARTMENT has the following columns: DEPT_ID DEPT_NAME MANAGER Which of the following statements will ONLY allow user USER1 to modify the DEPT_NAME column?

  1. GRANT ALTER ON TABLE department TO user1

  2. GRANT ALTER (dept_name) ON TABLE department TO user1

  3. GRANT UPDATE ON TABLE department TO user1

  4. GRANT UPDATE (dept_name) ON TABLE department TO user1
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Specifying column names within parentheses after the UPDATE keyword restricts the update privilege strictly to the named columns. A generic UPDATE privilege allows modifications to all columns in the table, whereas the ALTER privilege is meant for structural changes rather than changing existing table data.

Multiple choice technology databases

An index named EMPID_X exists for a table named EMPLOYEE. Which of the following will allow user USER1 to drop the EMPID_X index?

  1. GRANT DROP ON INDEX empid_x TO user1

  2. GRANT DELETE ON INDEX empid_x TO user1

  3. GRANT INDEX ON TABLE employee TO user1

  4. GRANT CONTROL ON INDEX empid_x TO user1
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To drop an index in DB2, a user must have CONTROL privilege on the index (or table) or administrative authority. Granting CONTROL on the index specifically provides this administrative right. The INDEX privilege on a table only allows creating new indexes, not dropping existing ones.

Multiple choice technology databases

What does the following statement do? GRANT REFERENCES (col1, col2) ON TABLE table1 TO user1 WITH GRANT OPTION

  1. Gives user USER1 the ability to refer to COL1 and COL2 of table TABLE1 in queries, along with the ability to give this authority to other users and groups.

  2. Gives user USER1 the ability to refer to COL1 and COL2 of table TABLE1 in views, along with the ability to give this authority to other users and groups.

  3. Gives user USER1 the ability to define a referential constraint on table TABLE1 using columns COL1 and COL2 as the parent key of the constraint.

  4. Gives user USER1 the ability to define a referential constraint on table TABLE1 using columns COL1 and COL2 as the foreign key of the constraint.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The REFERENCES privilege allows a user to define a referential constraint (foreign key) referencing the specified columns of the parent table. Specifying a parent key is the primary function of this privilege, rather than enabling queries or views (which require SELECT) or defining columns as foreign keys.

Multiple choice technology databases

A view named V.VIEW1 is based on a table named T.TABLE1. A user with DBADM authority issues the following statement: GRANT INSERT ON v.view1 TO user1 WITH GRANT OPTION Which of the following statements is USER1 authorized to execute?

  1. GRANT INSERT ON t.table1 TO user2

  2. GRANT CONTROL ON v.view1 TO user2

  3. GRANT ALL PRIVILEGES ON v.view1 TO user2

  4. GRANT INSERT ON v.view1 TO user2
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

WITH GRANT OPTION allows the grantee (USER1) to grant the same privilege to others. USER1 received INSERT on V.VIEW1 with GRANT OPTION, so USER1 can grant INSERT on V.VIEW1 to USER2. USER1 cannot grant privileges on the underlying table (T.TABLE1), cannot grant CONTROL (different privilege), and cannot grant ALL PRIVILEGES (never received it).

Multiple choice technology databases

Assume PUBLIC has been granted all privileges on table T1, which of the following statements would continue to permit any user to add rows to table T1 but not remove them?

  1. REVOKE DROP ON T1 FROM PUBLIC

  2. REVOKE UPDATE ON T1 FROM PUBLIC

  3. REVOKE DELETE ON T1 FROM PUBLIC

  4. REVOKE CONTROL ON T1 FROM PUBLIC
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To solve this question, the user needs to know the basics of database management and the concept of privileges.

Option A: REVOKE DROP ON T1 FROM PUBLIC - This option revokes the privilege to drop (delete) the entire table T1. It doesn't address the issue of adding rows. Therefore, this option is incorrect.

Option B: REVOKE UPDATE ON T1 FROM PUBLIC - This option revokes the privilege to update existing rows in the table T1. This means that the user cannot change the data in the rows that already exist in the table. It does not address the issue of adding rows. Therefore, this option is incorrect.

Option C: REVOKE DELETE ON T1 FROM PUBLIC - This option revokes the privilege to delete rows from the table T1. This means that the user cannot remove rows from the table. However, the user can still add rows to the table. Therefore, this option is correct.

Option D: REVOKE CONTROL ON T1 FROM PUBLIC - This option revokes all privileges from the PUBLIC on table T1, including the ability to add rows. Therefore, this option is incorrect.

The Answer is: C

Multiple choice technology databases

A user invoking a User Defined Function (UDF) requires which DB2 Privilege?

  1. Call

  2. Usage

  3. Execute

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

To invoke a User Defined Function, a user needs EXECUTE privilege on the function. CALL is used for invoking stored procedures in some database systems. USAGE is typically for domains, sequences, or types. REFERENCES is for foreign key constraints. EXECUTE is the correct privilege for UDFs in DB2.

Multiple choice technology databases

On which of the following database objects may the Select Privilege be controlled?

  1. Sequence

  2. Schema

  3. Group

  4. View
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The SELECT privilege can be granted on tables and views, allowing users to retrieve data from them. Sequences use the USAGE privilege to control access, schemas use privileges like CREATEIN or ALTERIN, and groups are authorization entities rather than database objects that receive privileges.

Multiple choice technology databases

Which of the following statement will NOT allow db2user to grant the privileges specified in the statement to others?

  1. GRANT INDEX ON employee TO db2user WITH GRANT OPTION

  2. GRANT UPDATE (salary, bonus) ON employee TO PUBLIC WITH GRANT

  3. GRANT CONTROL ON employee TO PUBLIC WITH GRANT OPTION

  4. GRANT ALTERIN, CREATEIN, DROPIN ON schema1 TO db2user WITH GRANT
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The CONTROL privilege cannot be granted WITH GRANT OPTION in DB2. CONTROL is an ownership-level privilege that inherently includes grant authority but cannot explicitly use WITH GRANT OPTION syntax. When you grant CONTROL to PUBLIC, recipients get full control but you cannot specify WITH GRANT OPTION for CONTROL itself. The other options are valid: INDEX and UPDATE privileges can have GRANT OPTION, and schema privileges (ALTERIN, CREATEIN, DROPIN) can use WITH GRANT.

Multiple choice technology databases

Assuming User1 has no privileges/Authorities , which of the following will only Allow USER1 to add data to table EMPLOYEE

  1. CONTROL

  2. UPDATE

  3. INSERT

  4. INSERT WITH GRANT OPTION
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To solve this question, the user needs to know the different types of privileges in a database management system and their corresponding actions.

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

A. CONTROL: This option is incorrect because the CONTROL privilege provides all privileges on an object, including the ability to modify, delete, and grant privileges. It is not specific to the INSERT action only.

B. UPDATE: This option is incorrect because the UPDATE privilege allows the user to modify existing data in a table but not add new data.

C. INSERT: This option is correct. The INSERT privilege allows the user to add new data to a table. By granting only the INSERT privilege to USER1 on the EMPLOYEE table, USER1 will be able to add new data to the table but not modify or delete existing data.

D. INSERT WITH GRANT OPTION: This option is incorrect because INSERT WITH GRANT OPTION not only allows the user to insert data into the table but also to grant the INSERT privilege to other users. This means that USER1 can give the INSERT privilege to another user, which is not a requirement in this scenario.

Therefore, the correct answer is:

The Answer is: C. INSERT

Multiple choice technology

Pivot table cannot be created in spreadsheet

  1. True

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

Pivot tables are a powerful feature available in spreadsheet applications like Excel. They can definitely be created to summarize and analyze data. The statement 'cannot be created' is false.