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 two are true about aggregate functions? (Choose two.)

  1. You can use aggregate functions in any clause of a SELECT statement.

  2. You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.

  3. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.

  4. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.

  5. You can use aggregate functions on a table, only by grouping the whole table as one single group.

  6. You cannot group the rows of a table by more than one column while using aggregate functions.
Reveal answer Fill a bubble to check yourself
C,D Correct answer
Explanation

Option C is true: non-aggregate columns can appear with aggregate functions IF those columns are in the GROUP BY clause. Option D is true: aggregate functions accept columns, expressions, constants, or function results as parameters. Options A, B, E, F are false: aggregates cannot appear in WHERE (use HAVING), can group subsets, and can group by multiple columns.

Multiple choice technology databases

Which two statements about subqueries are true? (Choose two.)

  1. A single row subquery can retrieve data from only one table.

  2. A SQL query statement cannot display data from table B that is referred to in its subquery, unless table B is included in the main query's FROM clause.

  3. A SQL query statement can display data from table B that is referred to in its subquery, without including table B in its own FROM clause.

  4. A single row subquery can retrieve data from more than one table.

  5. A single row subquery cannot be used in a condition where the LIKE operator is used for comparison.

  6. A multiple-row subquery cannot be used in a condition where the LIKE operator is used for comparison.
Reveal answer Fill a bubble to check yourself
B,D Correct answer
Explanation

Option B is true: Oracle requires tables referenced in the SELECT list to appear in the FROM clause (unlike MySQL). Option D is true: single-row subqueries can join multiple tables. Option A is false: subqueries can contain joins across tables. Option C is false for Oracle. Options E and F are false: LIKE can be used with any subquery type.

Multiple choice technology databases

You added a PHONE_NUMBER column of NUMBER data type to an existing EMPLOYEES table. The EMPLOYEES table already contains records of 100 employees. Now, you want to enter the phone numbers of each of the 100 employees into the table. Some of the employees may not have a phone number available. Which data manipulation operation do you perform?

  1. MERGE

  2. INSERT

  3. UPDATE

  4. ADD

  5. ENTER

  6. You cannot enter the phone numbers for the existing employee records.
Reveal answer Fill a bubble to check yourself
A Correct answer
Multiple choice technology databases

In which case would you use a FULL OUTER JOIN?

  1. Both tables have NULL values.

  2. You want all unmatched data from one table.

  3. You want all matched data from both tables.

  4. You want all unmatched data from both tables.

  5. One of the tables has more data than the other.

  6. You want all matched and unmatched data from only one table.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To answer this question, the user must know the different types of joins in SQL.

  • INNER JOIN: returns only matched data from both tables.
  • LEFT OUTER JOIN: returns all data from the left table and matched data from the right table.
  • RIGHT OUTER JOIN: returns all data from the right table and matched data from the left table.
  • FULL OUTER JOIN: returns all matched and unmatched data from both tables.

Now, let's go through each option and determine whether it is a valid case to use a FULL OUTER JOIN:

A. Both tables have NULL values.

  • This option is not a determining factor in deciding to use a FULL OUTER JOIN.

B. You want all unmatched data from one table.

  • This option does not require a FULL OUTER JOIN, as a LEFT OUTER JOIN or RIGHT OUTER JOIN would suffice depending on which table has the unmatched data.

C. You want all matched data from both tables.

  • This option requires an INNER JOIN, not a FULL OUTER JOIN.

D. You want all unmatched data from both tables.

  • This option requires a FULL OUTER JOIN, as it returns all matched and unmatched data from both tables.

E. One of the tables has more data than the other.

  • This option does not necessarily require a FULL OUTER JOIN, as a LEFT OUTER JOIN or RIGHT OUTER JOIN could also be used depending on which table has more data.

F. You want all matched and unmatched data from only one table.

  • This option does not require a FULL OUTER JOIN, as a LEFT OUTER JOIN or RIGHT OUTER JOIN could be used depending on which table has the data.

Therefore, the correct answer is:

The Answer is: D

Multiple choice technology databases

Evaluate these two SQL statements: SELECT last_name, salary , hire_date FROM EMPLOYEES ORDER BY salary DESC; SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC;

  1. The two statements produce identical results.

  2. The second statement returns a syntax error.

  3. There is no need to specify DESC because the results are sorted in descending order by default.

  4. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To solve this question, the user needs to understand the SQL SELECT statement and the ORDER BY clause. The ORDER BY clause is used to sort the result set in ascending or descending order based on one or more columns.

Now, let's go through each option and explain why it is right or wrong:

A. The two statements produce identical results. This option is correct. Both SQL statements select the same columns from the same table, and they both sort the results by the salary column in descending order. The syntax used to specify the column to sort by is different (column name vs. column position), but they are functionally equivalent.

B. The second statement returns a syntax error. This option is incorrect. The second SQL statement is also valid syntax and should execute without errors.

C. There is no need to specify DESC because the results are sorted in descending order by default. This option is incorrect. The ORDER BY clause sorts the result set in ascending order by default. If you want to sort in descending order, you need to explicitly specify the DESC keyword.

D. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement. This option is incorrect. The column alias does not affect the sorting behavior of the ORDER BY clause. It only changes the name of the column in the result set.

Therefore, the correct answer is:

The Answer is: A

Multiple choice technology databases

You would like to display the system date in the format "Monday, 01 June, 2001". Which SELECT statement should you use?

  1. SELECT TO_DATE(SYSDATE, 'FMDAY, DD Month, YYYY') FROM dual;

  2. SELECT TO_CHAR(SYSDATE, 'FMDD, DY Month, 'YYY') FROM dual;

  3. SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY') FROM dual;

  4. SELECT TO_CHAR(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;

  5. SELECT TO_DATE(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To format a date to a string, TO_CHAR must be used. The format model FMDay, DD Month, YYYY correctly outputs the full day name without padding, followed by the two-digit day, full month name, and four-digit year. TO_DATE is incorrect as it converts text to date.

Multiple choice technology databases

Which SELECT statement will the result ‘ello world’ from the string ‘Hello World’?

  1. SELECT SUBSTR( ‘Hello World’,1) FROM dual;

  2. SELECT INITCAP(TRIM (‘Hello World’, 1,1)) FROM dual;

  3. SELECT LOWER(SUBSTR(‘Hello World’, 1, 1) FROM dual;

  4. SELECT LOWER(SUBSTR(‘Hello World’, 2, 1) FROM dual;

  5. SELECT LOWER(TRIM (‘H’ FROM ‘Hello World’)) FROM dual;
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

TRIM('H' FROM 'Hello World') removes the leading 'H', resulting in 'ello World'. LOWER() then converts it to 'ello world'. Option A returns the full string starting from position 1. Option C has a syntax error (missing closing parenthesis). Option D would only return a single character 'e' from position 2.

Multiple choice technology databases

Evaluate this SQL statement: SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct) + (s.sales amount * (.35 * e.bonus)) AS CALC_VALUE FROM employees e, sales s WHERE e.employee_id = s.emp_id; What will happen if you remove all the parentheses from the calculation?

  1. The value displayed in the CALC_VALUE column will be lower.

  2. The value displayed in the CALC_VALUE column will be higher.

  3. There will be no difference in the value displayed in the CALC_VALUE column.

  4. An error will be reported.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

SQL follows standard mathematical operator precedence: multiplication and division are evaluated before addition and subtraction, regardless of parentheses. In this expression, all multiplications (.15*, .5*, .35*) would execute first, then additions. The parentheses are redundant and don't change the result.

Multiple choice technology databases

Which two are attributes of /SQL*Plus? (Choose two)

  1. /SQL*Plus commands cannot be abbreviated.

  2. /SQL*Plus commands are accesses from a browser.

  3. /SQL*Plus commands are used to manipulate data in tables.

  4. /SQL*Plus commands manipulate table definitions in the database.

  5. /SQL*Plus is the Oracle proprietary interface for executing SQL statements.
Reveal answer Fill a bubble to check yourself
C,D Correct answer
Multiple choice technology databases

In which scenario would index be most useful?

  1. The indexed column is declared as NOT NULL.

  2. The indexed columns are used in the FROM clause.

  3. The indexed columns are part of an expression.

  4. The indexed column contains a wide range of values.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Indexes are most effective when the indexed column has high cardinality (many distinct values). This makes the index selective, allowing the database to quickly narrow down rows. If a column has few distinct values (low cardinality), the index isn't very useful because many rows match each value. The indexed column being NOT NULL or used in expressions doesn't determine usefulness - selectivity does.

Multiple choice technology databases

Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater then 5000?

  1. ORDER BY SALARY > 5000

  2. GROUP BY SALARY > 5000

  3. HAVING SALARY > 5000

  4. WHERE SALARY > 5000
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The WHERE clause filters rows based on a condition. 'WHERE SALARY > 5000' correctly restricts the result to employees earning more than 5000. ORDER BY sorts results, GROUP BY aggregates them, HAVING filters aggregated groups - none are used for row-level filtering.

Multiple choice technology databases

Which are correct guidelines for naming database tables?

  1. Must begin with either a number or a letter.

  2. Must be 1-30 characters long.

  3. Should not be an Oracle Server reserved word.

  4. Must contain only A-Z, a-z, 0-+, _, *, and #.
  5. Must contain only A-Z, a-z, 0-9, _, $, and #.

  6. Must begin with a letter.
Reveal answer Fill a bubble to check yourself
B,C,E,F Correct answer
Explanation

Under standard Oracle database guidelines, table names must begin with a letter, be 1 to 30 characters long, not use reserved words, and contain only alphanumeric characters, underscores, dollar signs, and pound signs. Special characters like + and * are forbidden.

Multiple choice technology databases

Which operator can be used with a multiple-row subquery?

  1. =

  2. LIKE

  3. BETWEEN

  4. NOT IN

  5. IS

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

Multiple-row subqueries return multiple values. Operators like IN, NOT IN, ANY, ALL work with multiple-row subqueries. Single-row operators like =, <>, <, >, LIKE, BETWEEN expect only one value. NOT IN (option D) can compare a value against a list of values returned by a subquery.