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

MILLER is an administrator who has FULL DBA privileges. When he attempts to drop the DEFAULT profile as shown, he receives the error message shown. Which of the following options best explains this error?

SQL> drop profile SYS.DEFAULT;

drop profile SYS.DEFAULT

*

ERROR at line 1:

ORA-00950: invalid DROP option

  1. The DEFAULT profile cannot be dropped.

  2. MILLER requires the DROP PROFILE privilege.

  3. Profiles created by SYS cannot be dropped.

  4. The CASCADE option was not used in the DROP PROFILE command.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The DEFAULT profile in Oracle is a special system profile that cannot be dropped. Oracle requires this profile to exist as a fallback for users who are not explicitly assigned other profiles. Even a user with FULL DBA privileges cannot drop it because it's a protected system object. The error message ORA-00950 indicates an invalid DROP option for this reason. Option B is incorrect because DBA privileges already include all necessary permissions. Option C is incorrect because SYS-created objects can generally be dropped unless they're protected like DEFAULT. Option D is incorrect because CASCADE is used for profiles with assigned users, not for DEFAULT itself.

Multiple choice

In this SQL statement, which of the following lines will produce an error?

  1. CREATE OR REPLACE TRIGGER check_age

  2. BEFORE INSERT OR UPDATE ON employee FOR EACH ROW

  3. IS

  4. years_old NUMBER;

  5. BEGIN

Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In the trigger code shown, line C ("IS") is on its own line before the variable declarations. This is incorrect syntax because the declaration section cannot begin immediately after "IS" without proper context. The correct order should be the trigger header (lines A-B), then "IS" on the same line or followed by declarations, but not as a standalone line. Lines A and B correctly define the trigger. Line D (variable declaration) and line E (BEGIN) are syntactically correct in their positions.

Multiple choice

Evaluate the following DELETE statement:

DELETE employee_id, salary, job_id FROM employees

WHERE dept_id = 40;

Why does the DELETE statement fail when executed?

  1. There is no row with dept_id 90 in the EMPLOYEES table.

  2. The JOB_ID column cannot be deleted because it is a NOT NULL column.

  3. Column names were not specified in the DELETE clause of the DELETE statement.

  4. The EMPLOYEE_ID column cannot be deleted because it is the primary key of the table.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The DELETE statement fails because it incorrectly lists column names in the DELETE clause. The correct DELETE syntax is DELETE FROM table_name WHERE condition - you don't specify columns to delete in DELETE. The DELETE operation removes entire rows, not specific columns. If the goal was to delete rows from employees where dept_id = 40, the correct syntax would be DELETE FROM employees WHERE dept_id = 40. Option A is irrelevant (wrong dept_id anyway). Option B is incorrect because DELETE doesn't have column-level constraints. Option D is incorrect because deleting rows doesn't affect the primary key definition.

Multiple choice

Top N analysis requires ____________ and ____________.

  1. the use of rowed, only an inline view

  2. a GROUP BY clause, only an inline view

  3. an ORDER BY clause, an inline view and an outer query

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

Top-N queries require sorting data and then limiting results, which needs ORDER BY for sorting, an inline view to perform the sorting, and an outer query to restrict the rows returned. Option A is missing the outer query. Option B incorrectly suggests GROUP BY and lacks the outer query. The standard pattern is SELECT * FROM (SELECT ... ORDER BY ...) WHERE ROWNUM <= N.

Multiple choice

The following INSERT statement was issued:

INSERT /* +APPEND */ INTO arch.curr_order_hist

SELECT * FROM ord.curr_order;

Which of the following types of INSERT is performed?

  1. Conventional

  2. Direct-Load

  3. Parallel Direct-Load

  4. Serial-Conventional
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The /*+APPEND */ hint directs Oracle to perform a Direct-Load insert, which bypasses the buffer cache and writes data directly to datafiles, significantly improving performance for large inserts. This is different from Conventional insert (uses buffer cache) and Parallel Direct-Load (requires parallel execution). The hint makes Oracle treat the insert as a direct-path operation.

Multiple choice

What is the use of Translate Function in Oracle?

  1. It performs a word wise replacement of a string of numbers.

  2. It performs a character wise replacement of a number.

  3. It performs a word wise replacement of a string.

  4. It performs a character wise replacement of a string.
Reveal answer Fill a bubble to check yourself
B Correct answer
Multiple choice

What is the correct use of the Trunc command on a date?

  1. TRUNC=To_Date('09-Jan-02,DD-MON-YY,'YEAR',"Date" from Dual;

  2. Select TRUNC(To_Date('09-Jan-02,DD-MON-YY,YEAR')) "DATE" from Dual;

  3. Date =TRUNC(To_DATE('09-Jan-02','DD-MON-YY'),'YEAR'),'YEAR)"DATE: from DUAL;

  4. SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

TRUNC on a date truncates it to a specified precision (year, month, day, etc.). The correct syntax is TRUNC(date_value, 'format'). Option D shows correct syntax: TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') truncates the date to the first day of the year. Options A, B, and C have malformed syntax with incorrect quote placement, missing parentheses, or wrong function structure.

Multiple choice

Which of the following operators is used in a correlated subquery?

  1. IN

  2. EXISTS

  3. UNION

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

The EXISTS operator is specifically designed for correlated subqueries, testing whether the subquery returns any rows. It's evaluated for each row of the outer query. While IN can also be used in subqueries, EXISTS is the canonical operator for correlated subqueries because it can stop processing once a match is found. UNION and INTERSECT are set operators for combining queries, not subquery correlation.

Multiple choice

Employee table has salary field in this case if we give the query : select * from employee where sal<2000 and sal>9000 which will yield result as ____.

  1. rows with Sal<2000 and Salary>9000 will be displayed

  2. all the rows are displayed with any one of the condition satisfied

  3. no rows will be selected

  4. between sal 2000 and 9000 will be displayed
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The condition sal<2000 AND sal>9000 is logically impossible - a value cannot be both less than 2000 and greater than 9000 simultaneously. Therefore, no rows will satisfy both conditions, and the query returns zero rows. This is a classic example of a mutually exclusive condition.

Multiple choice

After a table has been created, its structure can be modified using the SQL command ______.

  1. UPDATE TABLE [TableName]

  2. MODIFY TABLE [TableName]

  3. ALTER TABLE [TableName]

  4. CHANGE TABLE [TableName]
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

ALTER TABLE is the standard SQL DDL command for modifying the structure of an existing table. This command can be used to add, drop, or rename columns, change column data types, add or drop constraints, and modify other table properties. The UPDATE command modifies data within a table, not its structure. MODIFY, CHANGE, and other variations are not valid SQL commands for table structure modification - only ALTER TABLE is the correct syntax.

Multiple choice

What is the correct use of the Trunc command on a date?

  1. TRUNC=To_Date('09-Jan-02,DD-MON-YY,'YEAR',"Date" from Dual;

  2. Select TRUNC(To_Date('09-Jan-02,DD-MON-YY,YEAR')) "DATE" from Dual;

  3. Date =TRUNC(To_DATE('09-Jan-02','DD-MON-YY'),'YEAR'),'YEAR)"DATE: from DUAL;

  4. SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Option D demonstrates the correct TRUNC function syntax with proper date formatting and string quoting. TRUNC accepts a date value and a format string, returning the date truncated to the specified precision level - in this case, 'YEAR' truncates to January 1st of that year. Options A, B, and C contain syntax errors including missing closing parentheses, incorrect quote usage, malformed function calls, and improper string literal formatting. The correct syntax requires TRUNC(date_value, format_string) with proper parentheses and string quotes.

Multiple choice

Which of the following SQL statements defines a FOREIGN KEY constraint on the DEPT NO column of the EMP table?

  1. CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk FOREIGN KEY deptno REFERENCES dept deptno);

  2. CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));

  3. CRETE TABLE EM (empno NUMBER(4), ename VARCHAR2(35) deptno NUMBER (7,2) NOT NULL, CONSTRAINT em_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));

  4. CREATE TABLE EMP (empno NUMBER (4), ename VARCHAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp deptno fk REFERENCES dept (deptno));
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Option B correctly defines a FOREIGN KEY constraint using inline constraint syntax with the REFERENCES clause directly in the column definition. The syntax "deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno)" properly creates a foreign key named emp_deptno_fk that references the deptno column in the dept table. Option A is missing parentheses around the FOREIGN KEY keyword. Option C contains typos (CRETE instead of CREATE, EM instead of EMP) and incorrect constraint placement. Option D has incorrect keyword order with FOREIGN KEY placed after CONSTRAINT name instead of using the REFERENCES clause properly.

Multiple choice

The following INSERT statement was issued:

INSERT /* +APPEND */ INTO arch.curr_order_hist

SELECT * FROM ord.curr_order;

Which type of INSERT is performed?

  1. Conventional

  2. Direct-Load

  3. Parallel Direct-Load

  4. Serial-Conventional
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The APPEND hint enables Direct-Load INSERT, which bypasses the buffer cache and writes data directly to datafiles. This is much faster than conventional path INSERT which uses the buffer cache. Option C (Parallel) is wrong because no parallel hint was used; options A and D describe conventional path inserts.

Multiple choice

In which clauses of a SELECT statement can substitution variables be used?

  1. The SELECT and FROM clauses, but not the WHERE clause

  2. The SELECT, FROM, WHERE, and GROUP BY clauses, but not the ORDER BY clause

  3. The SELECT, WHERE, GROUP BY, and ORDER BY clauses, but not the FROM clause

  4. The SELECT and FROM clauses, but not the WHERE clause

  5. The SELECT, FROM, WHERE, GROUP BY, ORDER BY, and HAVING clauses
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Substitution variables can be used in all clauses of a SELECT statement: SELECT, FROM, WHERE, GROUP BY, ORDER BY, and HAVING. They are replaced before statement execution. Options A-D incorrectly restrict which clauses can use substitution variables.

Multiple choice

_____________ can be declared by us for the queries that return more than one row.

  1. Implicit cursors

  2. Explicit cursors

  3. REF cursor

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

Explicit cursors must be declared by developers for queries that return multiple rows. Implicit cursors are automatically created by Oracle for single-row queries (like SELECT...INTO). REF cursors are cursor variables used to pass result sets, not a standard cursor type.