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

Which will not allow to drop a table?

  1. (a) UNION

  2. (b) SCHEMABINDING

  3. (c) CHECK OPTION

  4. (d) All
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

SCHEMABINDING on a view or function creates a dependency that prevents dropping the underlying tables. When an object is schema-bound, SQL Server ensures the object's definition remains valid by blocking any changes to referenced schema elements. This means you cannot DROP a table that a schema-bound view references - you must first drop or alter the schema-bound object. UNION and CHECK OPTION are view options that don't prevent dropping tables.

Multiple choice technology databases

Which option restricts changes to data to conform to the select criteria of a view?

  1. (a) SCHEMABINDING

  2. (b) CHECK OPTION

  3. (c) ANSI_NULLS

  4. (d) All
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

CHECK OPTION is a view option that ensures any INSERT or UPDATE operation through the view must satisfy the view's defining query criteria. This prevents data modifications that would make the rows invisible to the view itself. SCHEMABINDING is specific to SQL Server and binds objects to the schema, while ANSI_NULLS controls NULL comparison behavior.

Multiple choice technology databases

Which Joins allows to bring non matching data in TSQL

  1. (a) Full outer join

  2. (b) Inner join

  3. (c) Right outer join

  4. (d) Left outer join
Reveal answer Fill a bubble to check yourself
A,C,D Correct answer
Explanation

Outer joins (left, right, and full) are designed to retrieve non-matching rows from one or both tables, filling missing columns with NULLs. An inner join only returns rows where there is a match in both tables.

Multiple choice technology databases

Which will allow us to create a column alias?

  1. (a) AS

  2. (b) STR

  3. (c) FROM

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

The AS keyword is used in SQL to create column aliases, which provide temporary alternate names for columns in query results. This improves readability and is especially useful when using calculated expressions or aggregate functions. STR, FROM, and COLUMN are not used for aliasing purposes.

Multiple choice technology databases

Which three SELECT Statements displays 8000 in the format “$8,000.00”?(Choose three)
  1. A. SELECT TO CHAR(8000,’$#,###.##’) FROM DUAL;
  2. B. SELECT TO CHAR(8000,’$0,000.00’) FROM DUAL;
  3. C. SELECT TO CHAR(8000,’$9,999.00’) FROM DUAL;
  4. D. SELECT TO CHAR(8000,’$9,999.99’) FROM DUAL;
  5. E. SELECT TO CHAR(8000,’$8,000.00’) FROM DUAL;
  6. F. SELECT TO CHAR(8000,’$N,NNN.NN’) FROM DUAL;
Reveal answer Fill a bubble to check yourself
B,C,D Correct answer
Explanation

In Oracle SQL, TO_CHAR format models use 0 and 9 as digit placeholders. 0 displays leading zeros, while 9 displays significant digits only. For 8000 to display as '$8,000.00', we need at least one digit placeholder for the thousands place and two for decimals. Options B, C, and D all achieve this with '$0,000.00', '$9,999.00', and '$9,999.99' respectively - the last option shows decimal places even if zero. Options A and F use incorrect format syntax.

Multiple choice technology databases

You are sorting data in a table in you SELECT statement in descending order. The column you are sorting on contains NULL records, where will the NULL record appears?

  1. At the beginning of the list

  2. At the end of the list

  3. In the middle of the list

  4. At the same location they are listed in the unordered table
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

When sorting in descending order (DESC), NULL values appear at the beginning of the result set in Oracle SQL, opposite to their placement in ascending sorts. This is consistent with Oracle's treatment of NULL as greater than any non-NULL value. The NULL records do not appear in the middle or retain their original table positions.

Multiple choice technology databases

Evaluate the set of SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCNAR2(14), loc VARCNAR2(13)); ROLLBACK; DESCRIBE DEPT What is true about the set?

  1. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist

  2. The ROLLBACK statement frees the storage space occupies by the DEPT table

  3. The DESCRIBE DEPT statement displays the structure of the DEPT table

  4. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

DDL statements like CREATE TABLE perform an implicit COMMIT in Oracle. Therefore, the DEPT table is permanently created before the ROLLBACK executes, making ROLLBACK ineffective. The subsequent DESCRIBE DEPT command succeeds and displays the table structure. Option A is incorrect because the table exists, B is incorrect because ROLLBACK cannot undo DDL, and D is incorrect because no explicit COMMIT is needed.

Multiple choice technology databases

Which data dictionary table should you query to view the object privileges granted to the user on specific columns?

  1. USER_TAB_PRIVS_MADE

  2. USER_TAB_PRIVS

  3. USER_COL_PRIVS_MADE

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

Oracle's USER_COL_PRIVS data dictionary view lists column-level object privileges granted to the current user. USER_TAB_PRIVS shows table-level privileges, while _MADE views show privileges granted on objects owned by the user rather than privileges granted to them.

Multiple choice technology databases

The EMP table contains these columns: LAST NAME VARCHAR2(25) SALARY NUMBER(6,2) DEPARTMENT_ID NUMBER(6) You need to display the employees who have not been assigned to any department. You write the SELECT statement: SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP WHERE DEPARTMENT_ID = NULL; What is true about this SQL statement?

  1. The SQL statement displays the desired results

  2. The operator in the WHERE clause should be changed to display the desired results

  3. The WHERE clause should be changed to use an outer join to display the desired results

  4. The column in the WHERE clause should be changed to display the desired results
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The SQL statement uses DEPARTMENT_ID = NULL, which is incorrect because NULL requires the IS NULL or IS NOT NULL operators. The equality operator (=) always returns NULL (not TRUE or FALSE) when comparing with NULL, so no rows are selected. The correct syntax is WHERE DEPARTMENT_ID IS NULL. Options A, C, and D are incorrect - the operator itself needs to change, not the column name, and outer joins are not needed here.

Multiple choice technology packaged enterprise solutions

The SwitchUser function is used to change the user ID of the current user logged onto the PeopleSoft system.Which of the following is the proper syntax for the function?

  1. SwitchUser(Password, UserID, ExtAuthInfo,AuthToken)

  2. SwitchUser(UserID, Password, ExtAuthInfo,AuthToken)

  3. SwitchUser(UserID, Password, AuthToken , ExtAuthInfo)

  4. SwitchUser(Password, ExtAuthInfo,AuthToken,UserID)
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In PeopleCode, the SwitchUser function changes the user ID context of the active session. Its correct syntax requires the parameters in the precise sequence of UserID, Password, AuthToken, and finally ExtAuthInfo. This sequence matches the third option.

Multiple choice technology packaged enterprise solutions

For bulk insert, inserted rows are buffered and sent to the database server

  1. For every row insert

  2. For every 3 minutes

  3. Inserted rows are not buffered

  4. only when the buffer is full or a commit occurs
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Bulk insert optimization buffers rows in memory and flushes them to the database server only when the buffer is full or when an explicit commit occurs. This reduces network round-trips significantly compared to sending individual rows.

Multiple choice technology databases

ISN number is unique across

  1. Database

  2. Each File

  3. Both a & b

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

ISN (Internal Sequence Number) values are unique within each individual file but not across the entire database. Each file maintains its own ISN sequence independently.