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 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.

Multiple choice technology databases

Which SQL statement accepts user input for the columns to be displayed, the table name, and the WHERE condition? A. SELECT &1, "&2" FROM &3 WHERE last_name = '&4'; B. SELECT &1, '&2' FROM &3 WHERE '&last_name = '&4''; C. SELECT &1, &2 FROM &3 WHERE last_name = '&4'; D. SELECT &1, '&2' FROM EMP WHERE last_name = '&4';

  1. A

  2. B

  3. C

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

Substitution variables without quotes like &1 and &2 allow dynamic column and table names, while using single quotes around &4 is correct for literal string values.

Multiple choice technology databases

In which scenario would an index be most useful? A. The indexed column is declared as NOT NULL. B. The indexed columns are used in the FROM clause. C. The indexed columns are part of an expression. D. The indexed column contains a wide range of values.

  1. A

  2. B

  3. C

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

Indexes are most effective when the indexed column has high selectivity, meaning it contains a wide range of unique values to efficiently filter out unwanted rows.

Multiple choice technology databases

Which SELECT statement should you use to extract the year from the system date and display it in the format "1998"? A. SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual; B. SELECT TO_DATE(SYSDATE,'yyyy') FROM dual; C. SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY') FROM dual; D. SELECT DECODE(SUBSTR(SYSDATE, 8), 'year') FROM dual; E. SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy') FROM dual;

  1. A

  2. B

  3. C

  4. D

  5. E
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

TO_CHAR converts a date value to a string with the specified format. Option A correctly extracts the year from SYSDATE and displays it in 'yyyy' format (e.g., '1998'). TO_DATE (B) converts string to date, the opposite direction. DECODE (C, D) is for conditional logic, not date formatting. Option E incorrectly mixes SUBSTR string manipulation with TO_CHAR.

Multiple choice technology databases

Which are iSQL*Plus commands? A. INSERT B. UPDATE C. SELECT D. DESCRIBE E. DELETE F. RENAME

  1. A

  2. B

  3. C

  4. D

  5. E

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

iSQL*Plus commands (also called SQL*Plus commands) are environment-specific commands like DESCRIBE, ACCEPT, DEFINE, etc. They are not SQL statements. INSERT, UPDATE, SELECT, DELETE are SQL DML/DRL commands. RENAME is a SQL DDL command. Only DESCRIBE is an iSQL*Plus command among the options.

Multiple choice technology databases

Consider this syntax MERGE INTO t1 USING t2 ON (join predicate) .... What does the MERGE syntax do ?

  1. It performs a merge join of the row from T2 only if it does'nt exist in the T1 table.

  2. It creates a natural join of table T1 and T2 for all columns that have the same name.

  3. It creates a Cartesian prodcut of table T1 and table T2 for all columns that have the same name.

  4. For each row from T2, it updates the row if it exists witin table T1, otherwise it inserts the row into T1.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

MERGE updates rows in T1 that match the join predicate from T2, and inserts rows from T2 that don't match. It's an 'upsert' operation. Option A incorrectly says 'only if it doesn't exist' - MERGE does both update and insert. Option B incorrectly describes a natural join. Option C incorrectly describes a Cartesian product.

Multiple choice technology databases

You need to enforce these two business rules: 1. No two rows of a table can have duplicate values in the specified column. 2. A column cannot contain null values. Which type of constraint ensure that both of the above rules are true ?

  1. Check

  2. Unique

  3. Not Null

  4. Primary Key

  5. Foreign Key
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A Primary Key constraint enforces uniqueness (no duplicate values in the specified column) and prohibits null values (acting as a NOT NULL constraint). Unique constraints allow nulls, and Check or Not Null constraints do not enforce uniqueness alone.

Multiple choice technology databases

John has created a procedure named SALARY_CALC. Which SQL query allow him to view text of procedure?

  1. SELECT text FROM user_source WHERE name = 'SALARY_CALC';

  2. SELECT * FROM user_source WHERE name='SALARY_CALC';

  3. SELECT * FROM user_objects WHERE object_name='SALARY_CALC';

  4. SELECT * FROM user_procedures WHERE object_name='SALARY_CALC';

  5. SELECT text FROM user_source WHERE name='SALARY_CALC' and owner='JOHN'
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The correct SQL query to view the text of the procedure named SALARY_CALC, created by John, would be:

A. SELECT text FROM user_source WHERE name = 'SALARY_CALC';

This query selects the text column from the user_source table where the name is equal to 'SALARY_CALC'. This will retrieve the text of the procedure.

The other options are incorrect because:

B. SELECT * FROM user_source WHERE name='SALARY_CALC'; This query selects all columns from the user_source table where the name is equal to 'SALARY_CALC'. It will retrieve all details of the procedure, not just the text.

C. SELECT * FROM user_objects WHERE object_name='SALARY_CALC'; This query selects all columns from the user_objects table where the object_name is equal to 'SALARY_CALC'. This will retrieve details about the object, but not the text of the procedure.

D. SELECT * FROM user_procedures WHERE object_name='SALARY_CALC'; This query selects all columns from the user_procedures table where the object_name is equal to 'SALARY_CALC'. This will retrieve details about the procedure, but not the text of the procedure.

E. SELECT text FROM user_source WHERE name='SALARY_CALC' and owner='JOHN'; This query selects the text column from the user_source table where the name is equal to 'SALARY_CALC' and the owner is equal to 'JOHN'. This query assumes that John is the owner of the procedure. If John is indeed the owner, this query will work. However, if John is not the owner, it will not retrieve the text of the procedure.

So, the correct answer is option A.

Multiple choice technology databases

A tablespace has a table with 30 extents in it. Is this bad?

  1. True

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

Having 30 extents for a table is not inherently problematic. In locally managed tablespaces with uniform extent sizes, Oracle automatically manages extents efficiently. The number of extents becomes a concern primarily in dictionary-managed tablespaces or when it causes performance issues due to excessive extent allocation.

Multiple choice technology databases

John has created a procedure named SALARY_CALC. Which SQL query allow him to view text of procedure?

  1. SELECT text FROM user_source WHERE name = 'SALARY_CALC';

  2. SELECT * FROM user_source WHERE name='SALARY_CALC';

  3. SELECT * FROM user_objects WHERE object_name='SALARY_CALC';

  4. SELECT * FROM user_procedures WHERE object_name='SALARY_CALC';

  5. SELECT text FROM user_source WHERE name='SALARY_CALC' and owner='JOHN'
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To view the text of a stored procedure, query USER_SOURCE with the procedure name in the NAME column and select the TEXT column. USER_SOURCE stores the source code line by line. The TEXT column specifically contains the source code lines, while USER_OBJECTS and USER_PROCEDURES contain metadata but not the actual source text.