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

Which two statements are true about constraints?

  1. The UNIQUE constraint does not permit a null value for the column.

  2. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.

  3. The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index.

  4. The NOT NULL constraint ensures that null values are not permitted for the column.
Reveal answer Fill a bubble to check yourself
A,D Correct answer
Multiple choice technology databases

The user Alice wants to grant all users query privileges on her DEPT table. Which SQL statement accomplishes this?

  1. GRANT select ON dept TO ALL_USERS;

  2. GRANT select ON dept TO ALL;

  3. GRANT QUERY ON dept TO ALL_USERS;

  4. GRANT select ON dept TO PUBLIC;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

In SQL, the PUBLIC keyword represents a special user group containing all database users. Granting privileges to PUBLIC allows everyone to access the table, while other terms like ALL_USERS or ALL are syntactically incorrect here.

Multiple choice technology databases

Which best describes an inline view?

  1. a schema object

  2. a subquery that can contain an ORDER BY clause

  3. another name for a view that contains group functions

  4. a subquery that is part of the FROM clause of another query
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

An inline view is a subquery included in the FROM clause of a SELECT statement, which the database treats as a temporary table. Options A, B, and C are incorrect - it is not a schema object, not defined by ORDER BY support, and not merely a view with group functions.

Multiple choice technology databases

The DBA issues this SQL command: CREATE USER scott IDENTIFIED by tiger; What privileges does the user Scott have at this point?

  1. no privileges

  2. only the SELECT privilege

  3. only the CONNECT privilege

  4. all the privileges of a default user
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

When a user is created with CREATE USER, they have no privileges at all - not even the ability to log in. The user must be explicitly granted privileges (like CREATE SESSION) before they can do anything. Options B, C, and D are incorrect - no default privileges are granted.

Multiple choice technology databases

Which is iSQL*Plus commands?

  1. Insert

  2. Update

  3. Select

  4. Describe

  5. Delete

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

DESCRIBE is an iSQL*Plus command that displays the structure of a table, view, or other database object. Options A, B, C, E, and F are SQL commands (INSERT, UPDATE, SELECT, DELETE, RENAME), not iSQL*Plus-specific commands.

Multiple choice technology databases

Which SQL statement returns a numeric value?

  1. SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;

  2. SELECT ROUND(hire_date)FROM EMP;

  3. SELECT sysdate-hire_date FROM EMP;

  4. SELECT TO_NUMBER(hire_date + 7)FROM EMP;
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In Oracle SQL, subtracting two dates returns a numeric value representing the number of days between them. The sysdate-hire_date expression performs date arithmetic and yields a number. ADD_MONTHS returns a date, ROUND applied to a date returns a date, and TO_NUMBER cannot directly convert a date expression.

Multiple choice technology databases

Which object privileges can be granted on a view?

  1. none

  2. DELETE, INSERT,SELECT

  3. ALTER, DELETE, INSERT, SELECT

  4. DELETE, INSERT, SELECT, UPDATE
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Object privileges that can be granted on a view in Oracle are SELECT, INSERT, UPDATE, and DELETE. These are the standard DML privileges applicable to views. ALTER is a structure modification privilege that cannot be granted on views - only the view owner or someone with ALTER ANY VIEW privilege can modify the view definition.

Multiple choice technology databases

Top N analysis requires _____ and _____.

  1. the use of rowed

  2. a GROUP BY clause

  3. an ORDER BY clause

  4. only an inline view

  5. an inline view and an outer query
Reveal answer Fill a bubble to check yourself
C,E Correct answer
Explanation

Top-N analysis requires both an inline view (to order and rank the data) and an outer query (to limit the results to the top N rows). The inline view contains the ORDER BY clause to sort the data, and the outer query applies ROWNUM or a limiting clause to select only the top N. A GROUP BY clause is not required for Top-N analysis.

Multiple choice technology databases

For which two constraints does the Oracle Server implicitly create a unique index?

  1. Not null

  2. Primary key

  3. Foreign key

  4. Check

  5. Unique
Reveal answer Fill a bubble to check yourself
B,E Correct answer
Explanation

Oracle automatically creates a unique index to enforce PRIMARY KEY and UNIQUE constraints. This unique index ensures that duplicate values cannot be inserted. NOT NULL, FOREIGN KEY, and CHECK constraints do not require unique indexes for their enforcement - they use different mechanisms.

Multiple choice technology databases

Null value in SQL represent?

  1. An empty String

  2. Zero

  3. NULL is the value used to represent an unknown piece of data

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

NULL in SQL represents a missing or unknown value, not an empty string, zero, or a numeric value. It is a special marker indicating the absence of data or that the value is unknown.

Multiple choice technology databases

A employee table contain below values empno empname deptno salary 1 aaa 10 30000 2 bbb 20 25000 3 ccc 10 Null 4 ddd Null 35000 Select avg(salary) from employee?

  1. 0

  2. 30000

  3. 22500

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

The AVG function ignores NULL values. The average is computed using the non-null values: (30000 + 25000 + 35000) / 3 = 30000. Distractors either include the null value as zero or calculate the wrong average.

Multiple choice technology databases

A employee table contain below values empno empname deptno salary 1 aaa 10 30000 2 bbb 20 25000 3 ccc 10 Null 4 ddd Null 35000 Select avg(Isnull(salary,10000)) from employee?

  1. 0

  2. 30000

  3. 25000

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

The query uses Isnull(salary,10000) which replaces NULL with 10000. Averaging: (30000+25000+10000+35000)/4 = 100000/4 = 25000. This appears to be SQL Server syntax (ISNULL with one L), not MySQL (IFNULL).

Multiple choice technology platforms and products

The inclusion of a technical proof point for the Sun Oracle Database Machine (Proof of Value) in the sales cycle is a key to a shortened sales cycle.

  1. True

  2. False

  3. No difference

  4. Technical proof point is not allowed by Oracle
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Oracle's sales methodology for Exadata Database Machine recognized that Proof-of-Value (PoV) engagements typically extend rather than shorten sales cycles. The complexity and resource requirements of setting up and running technical evaluations introduce delays that outweigh their value in accelerating decisions. Option B (False) correctly reflects this.

Multiple choice technology

You can use free form sql report as report filter.

  1. True

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

Free-form SQL reports are standalone output formats, not filter conditions. In reporting tools, filters are applied to datasets before generating the report output, while the SQL report itself is the result presentation layer. The answer False correctly identifies this distinction.