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

Can we have multiple check constraints on a single column of a table.

  1. True

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

SQL allows multiple CHECK constraints on a single column because each constraint is evaluated independently. For example, you could have one CHECK ensuring age >= 18 and another ensuring age <= 65 on the same column. All CHECK constraints must be satisfied for any INSERT or UPDATE operation on that column.

Multiple choice technology databases

We can view the constraint details from following data dictionary views

  1. user_constraints

  2. user_tables

  3. user_cons_columns

  4. all_tab_cols
Reveal answer Fill a bubble to check yourself
A,C Correct answer
Explanation

Oracle data dictionary views provide metadata about database objects. USER_CONSTRAINTS displays constraint definitions for the current user's tables. USER_CONS_COLUMNS shows which columns participate in which constraints. USER_TABLES and ALL_TAB_COLS contain table and column information but not constraint details.

Multiple choice technology programming languages

What is the meaning of ResultSet.TYPE_SCROLL_INSENSITIVE

  1. This means that the ResultSet is insensitive to scrolling

  2. This means that the Resultset is sensitive to scrolling, but insensitive to updates, i.e. not

  3. This means that the ResultSet is sensitive to scrolling, but insensitive to changes made by others

  4. The meaning depends on the type of data source, and the type and version of the driver you use with this data source
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

TYPE_SCROLL_INSENSITIVE creates a scrollable ResultSet that allows bidirectional navigation (first, last, absolute, relative). The 'INSENSITIVE' means it doesn't reflect changes made by others after the ResultSet was created - it shows a static snapshot. Option A is wrong - 'insensitive' refers to change sensitivity, not scrolling ability. Option B has the sensitivity backwards.

Multiple choice technology programming languages

Examine the structure of the EMPLOYEES table:EMPLOYEE_ID NUMBER Primary KeyFIRST_NAME VARCHAR2(25)LAST_NAME VARCHAR2(25)Which three statements insert a row into the table? (Choose three.)A. INSERT INTO employeesVALUES ( NULL, 'John', 'Smith');B. INSERT INTO employees( first_name, last_name)VALUES( 'John', 'Smith');C. INSERT INTO employeesVALUES ( '1000', 'John', NULL);D. INSERT INTO employees (first_name, last_name, employee_id)VALUES ( 1000, 'John', 'Smith');E. INSERT INTO employees (employee_id)VALUES (1000);F. INSERT INTO employees (employee_id, first_name, last_name)VALUES ( 1000, 'John', ' ');

  1. A

  2. C,E

  3. C,E,F

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

Statement C inserts row with NULL for emp_id (valid if NOT NULL not specified), first_name='John', NULL last_name. Statement E inserts only emp_id=1000, other columns get NULL. Statement F inserts emp_id=1000, first_name='John', last_name=' '(space). A fails if emp_id is PK and NOT NULL. B and D have wrong column ordering in VALUES.

Multiple choice technology databases

Which are DML statements? (Choose all that apply.)

  1. COMMIT

  2. MERGE

  3. UPDATE

  4. DELETE

  5. CREATE

  6. DROP
Reveal answer Fill a bubble to check yourself
B,C,D Correct answer
Explanation

DML (Data Manipulation Language) commands manipulate data within tables: MERGE combines insert and update operations, UPDATE modifies existing rows, and DELETE removes rows. In contrast, CREATE and DROP are DDL (Data Definition Language) commands that define database structures, and COMMIT is a transaction control command.

Multiple choice technology databases

On which line will the following command fail? INSERT INTO plsql101_product ( product_name, product_price, quantity_on_hand, last_stock_date ) VALUES ( 'New Product', 1.95, 10, TO_CHAR(USER) ) ;

  1. 1

  2. 2

  3. 7

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

The TO_CHAR(USER) function converts a username string to character format, creating a type mismatch when inserting into a DATE column (last_stock_date). DATE columns require proper date values like SYSDATE or TO_DATE expressions, not string conversions. The TO_CHAR function returns a VARCHAR2, which cannot be directly inserted into a DATE column without implicit conversion.

Multiple choice technology databases

Evaluate the set of SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13)); ROLLBACK; DESCRIBE DEPT What is true about the set? A. The DESCRIBE DEPT statement displays the structure of the DEPT table. B. The ROLLBACK statement frees the storage space occupied by the DEPT table. C. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist. D. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.

  1. A

  2. B

  3. C

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

To understand the given set of SQL statements, the user must have knowledge of SQL commands and their functions.

  • CREATE TABLE statement creates a table with the specified columns and data types.

  • ROLLBACK statement rolls back the current transaction and undoes all the changes, freeing the storage space occupied by the table.

  • DESCRIBE statement displays the structure of the specified table.

Now, let's evaluate each option:

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

This option is correct. The DESCRIBE statement is used to display the structure of the specified table, and in this case, the specified table is DEPT. So, the DESCRIBE DEPT statement will display the structure of the DEPT table.

B. The ROLLBACK statement frees the storage space occupied by the DEPT table.

This option is incorrect. The ROLLBACK statement rolls back the current transaction and undoes all the changes, but it does not free the storage space occupied by the DEPT table. The table will still exist even after the ROLLBACK statement is executed.

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

This option is incorrect. The DEPT table was created using the CREATE TABLE statement, so it exists in the database. The DESCRIBE DEPT statement will display the structure of the DEPT table.

D. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.

This option is incorrect. The DESCRIBE statement displays the structure of the specified table regardless of the presence of a COMMIT statement. The ROLLBACK statement undoes all the changes made in the current transaction, but it does not affect the execution of the DESCRIBE statement.

Therefore, the correct answer is:

The Answer is: A. A

Multiple choice technology databases

Evaluate this SQL statement: SELECT ename, sal, 12*sal+100 FROM emp; The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of \$100, multiplied by 12"? Evaluate this SQL statement: SELECT ename, sal, 12*sal+100 FROM emp; The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of \$100, multiplied by 12"? A. No change is required to achieve the desired results. B. SELECT ename, sal, 12*(sal+100) FROM emp; C. SELECT ename, sal, (12*sal)+100 FROM emp; D. SELECT ename, sal+100,*12 FROM emp;

  1. A

  2. B

  3. C

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

To calculate the annual compensation as monthly salary plus a monthly bonus of \$100, multiplied by 12, we need to modify the expression "12*sal+100" in the SELECT statement.

Option A: No change is required to achieve the desired results. This option is incorrect because the original expression "12*sal+100" calculates the total compensation as monthly salary multiplied by 12, plus a fixed monthly bonus of \$100. It does not add the bonus to the monthly salary before multiplying by 12.

Option B: SELECT ename, sal, 12*(sal+100) FROM emp; This option is correct. By adding the monthly bonus of $100 to the monthly salary before multiplying by 12, we get the correct annual compensation. The expression "12*(sal+100)" calculates the annual compensation as monthly salary plus bonus, multiplied by 12.

Option C: SELECT ename, sal, (12*sal)+100 FROM emp; This option is incorrect because the parentheses around "12*sal" are unnecessary. The multiplication operator has higher precedence than the addition operator, so "12*sal" is evaluated before adding 100.

Option D: SELECT ename, sal+100,12 FROM emp; This option is incorrect because the syntax is incorrect. The asterisk () is not a valid operator in this context. We need to use the multiplication operator (*) to calculate the annual compensation.

Therefore, the correct answer is: B. SELECT ename, sal, 12*(sal+100) FROM emp;

Multiple choice technology databases

Which of the following commands will rename a table?

  1. RENAME table_name new_table_name;

  2. RENAME table_name TO new_table_name;

  3. RENAME TABLE table_name new_table_name;

  4. RENAME TABLE table_name TO new_table_name;
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The RENAME command (Oracle syntax) requires the TO keyword between the old and new names. The correct syntax is RENAME old_name TO new_name, without the TABLE keyword and with the TO separator. Options A and C are missing the TO keyword, while D incorrectly adds the TABLE keyword.

Multiple choice technology databases

Which command lets you put data into more than one table using a single command?

  1. MERGE

  2. INSERT INTO MANY

  3. MULTIADD

  4. INSERT ALL INTO
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

INSERT ALL is an Oracle SQL command that enables inserting data into multiple target tables using a single statement. You specify multiple INTO clauses, each targeting a different table and optionally with different conditions. This allows efficient population of related tables without separate INSERT commands. MERGE operates on one table, while INSERT INTO MANY and MULTIADD are not valid SQL commands.

Multiple choice technology databases

Examine the SQL statement that creates ORDERS table: CREATE TABLE orders (SER_NO NUMBER UNIQUE, ORDER_ID NUMBER, ORDER_DATE DATE NOT NULL, STATUS VARCHAR2(10) CHECK (status IN ('CREDIT', 'CASH')), PROD_ID NUMBER REFERENCES PRODUCTS(PRODUCT_ID), ORD_TOTAL NUMBER, PRIMARY KEY (order_id, order_date)); For which columns would an index be automatically created when you execute the above SQL statement?

  1. SER_NO

  2. ORDER_ID

  3. STATUS

  4. PROD_ID

  5. ORD_TOTAL

  6. composite index on ORDER_ID and ORDER_DATE
Reveal answer Fill a bubble to check yourself
A,F Correct answer
Explanation

Unique constraints (SER_NO UNIQUE) and primary keys (PRIMARY KEY) automatically prompt the database to create unique indexes to enforce uniqueness.

Multiple choice technology databases

Evaluate the SQL statement: TRUNCATE TABLE DEPT; Which three are true about the SQL statement? (Choose three.) A. It releases the storage space used by the table. B. It does not release the storage space used by the table. C. You can roll back the deletion of rows after the statement executes. D. You can NOT roll back the deletion of rows after the statement executes. E. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error. F. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table.

  1. A

  2. B

  3. C

  4. D

  5. E

  6. F
Reveal answer Fill a bubble to check yourself
A,D,F Correct answer
Explanation

TRUNCATE is a DDL command that removes all rows and resets the table. It releases storage space (A is true, B is false), cannot be rolled back because it's DDL (D is true, C is false), and requires either table ownership or DELETE ANY TABLE privilege (F is true). The DESCRIBE command still works after TRUNCATE (E is false).

Multiple choice technology databases

Which two statements about sequences are true? (Choose two.) A. You use a NEXTVAL pseudo column to look at the next possible value that would be generated from a sequence, without actually retrieving the value. B. You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence. C. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value from the sequence. D. You use a CURRVAL pseudo column to generate a value from a sequence that would be used for a specified database column. E. If a sequence starting from a value 100 and incremented by 1 is used by more than one application, then all of these applications could have a value of 105 assigned to their column whose value is being generated by the sequence. F. You use a REUSE clause when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.

  1. A

  2. B

  3. C

  4. D

  5. E

  6. F
Reveal answer Fill a bubble to check yourself
B,C Correct answer
Explanation

The CURRVAL pseudo-column returns the value last generated by the sequence in the current session, while NEXTVAL increments and returns the next value.

Multiple choice technology databases

Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12? A. SELECT ename, salary*12 'Annual Salary' FROM employees; B. SELECT ename, salary*12 "Annual Salary" FROM employees; C. SELECT ename, salary*12 AS Annual Salary FROM employees; D. SELECT ename, salary*12 AS INITCAP("ANNUAL SALARY") FROM employees

  1. A

  2. B

  3. C

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

In Oracle SQL, column aliases with spaces or special characters must be enclosed in double quotes. Option B uses double quotes correctly. Single quotes (A) would be treated as a string literal, not an alias. Option C needs quotes around 'Annual Salary' after the AS keyword. Option D uses INITCAP which is a function, not alias syntax.