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

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.

Multiple choice technology databases

Which of the following is not correct about a View ?

  1. To protect some of the columns of a table from other users

  2. Occupies data storage space

  3. To hide complexity of a query

  4. To hide complexity of a calculations
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Views are virtual tables that store only the query definition, not the actual data. Option B correctly identifies what is NOT true about views - they do NOT occupy separate data storage space. The view data is dynamically retrieved from underlying tables when queried. Options A, C, and D are all valid uses of views: column security, simplifying complex queries, and abstracting calculations.

Multiple choice technology databases

Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2) JOB_CAT VARCHAR2(30) SALARY NUMBER(8,2) Which statement shows the maximum salary paid in each job category of each department?

  1. SELECT dept_id, job_cat, MAX(salary) FROM employees WHERE salary > MAX(salary);

  2. SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id, job_cat;

  3. SELECT dept_id, job_cat, MAX(salary) FROM employees;

  4. SELECT dept_id, job_cat, MAX(salary) FROM employees v

  5. SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id, job_cat, salary;
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To solve this question, the user needs to know SQL syntax for selecting data from a table, using aggregate functions like MAX(), and grouping data using GROUP BY.

Option A: This option is incorrect because it includes a comparison statement with MAX(salary), which is not valid. The MAX() function can only be used with aggregate functions or in GROUP BY queries.

Option B: This option is correct. It selects the department ID, job category, and maximum salary for each group of department ID and job category. The GROUP BY clause is used to group the data by department ID and job category.

Option C: This option is incorrect because it does not include a GROUP BY clause, so it would return the overall maximum salary in the entire table, rather than the maximum salary for each job category in each department.

Option D: This option is incorrect because it selects the same data as option B, but uses an alias for the table name. This is not necessary and does not affect the query.

Option E: This option is incorrect because it includes the salary column in the GROUP BY clause, which would group the data by salary as well. This would result in multiple rows for each department ID and job category, with the same maximum salary value.

Therefore, the correct answer is: B. SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id, job_cat;

Multiple choice technology databases

Which is not part of the Data Definiton Language

  1. CREATE

  2. ALTER

  3. ALTER SESSION

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

Data Definition Language (DDL) commands define database structures and include CREATE, ALTER, DROP, TRUNCATE, RENAME, and COMMENT. ALTER SESSION is not a DDL command - it's a session control command that modifies database session parameters like language, date format, or optimizer settings. Options A and B (CREATE and ALTER) are core DDL statements.

Multiple choice technology databases

If an UNIQUE KEY constraint on DATE column is created, will it accept the rows that are inserted with SYSDATE ?

  1. Will

  2. Wont

  3. NA

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

When SYSDATE is inserted into a DATE column, multiple rows inserted on the same day will have the same date value (time component may be truncated). This violates the UNIQUE constraint which requires all values to be distinct. The question tests understanding that SYSDATE-derived values can conflict when only the date portion is considered. Options C and D are not meaningful responses.