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

Only one aggregate function can be nested within an aggregate function

  1. True

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

Informatica allows nesting of aggregate functions (like SUM(MAX(salary))) but does NOT allow one aggregate function directly inside another aggregate function - you must use group by ports appropriately. The restriction is on direct nesting, not on using multiple aggregate functions in the same expression.

Multiple choice technology enterprise content management

In a transitive relationship scenario, what problems exist when reporting from the tables below?

  1. The Order table must be included in each query.

  2. The Order Detail table has conflicting cardinalities.

  3. Outer joins are generated when querying from Customer.

  4. There is more than one possible path between the tables.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A transitive relationship or loop in database modeling creates multiple path options when joining tables (e.g., Joining A to C directly vs A to B to C). This ambiguity causes query generation issues because reporting engines have more than one possible join path to retrieve the data.

Multiple choice technology databases

Which SELECT statement should you use if you want to display unique combinations of the POSITION and MANAGER values from the EMPLOYEE table?

  1. SELECT DISTINCT position, manager

  2. SELECT position, manager DISTINCT

  3. SELECT position, manager

  4. SELECT position, DISTINCT manager
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The DISTINCT keyword must immediately follow the SELECT keyword to filter duplicate rows. Placing DISTINCT elsewhere in the query is syntactically invalid, which rules out all other distractors.

Multiple choice technology databases

Which two are attributes of /SQL*Plus?

  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.
Reveal answer Fill a bubble to check yourself
C,D Correct answer
Explanation

SQL*Plus is Oracle's command-line interface for executing SQL commands. It can manipulate actual table data (C) and database schema definitions (D) using SQL statements. SQL*Plus commands CAN be abbreviated, contradicting A.

Multiple choice technology databases

Which is an /SQL*Plus command?

  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 a SQL*Plus-specific command that displays table/column metadata. Options A, B, E, and F are SQL commands; 'describe' is the only SQL*Plus command listed.

Multiple choice technology databases

Evaluate this SQL statement: SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID, d.DEPARTMENT_NAME. FROM EMP e, DEPARTMENT d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID; In the statement, which capabilities of a SELECT statement are performed?

  1. Selection, projection, join

  2. Difference, projection, join

  3. Selection, intersection, join

  4. Intersection, projection, join

  5. Difference, projection, product
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

This query performs selection (WHERE clause filters rows), projection (SELECT chooses specific columns), and join (connects EMP and DEPARTMENT tables). These are fundamental SQL capabilities.

Multiple choice technology databases

Which two statements are true regarding the ORDER BY clause?

  1. The sort is in ascending by order by default

  2. The sort is in descending order by default

  3. The ORDER BY clause must precede the WHERE clause.

  4. The ORDER BY clause is executed on the client side.

  5. The ORDER BY clause comes last in the SELECT statement.

  6. The ORDER BY clause is executed first in the query execution.
Reveal answer Fill a bubble to check yourself
A,E Correct answer
Explanation

The ORDER BY clause sorts results in ascending order (ASC) by default. It is positioned last in the SELECT statement structure, after WHERE, GROUP BY, and HAVING clauses. Options B, C, D, and F are incorrect - ORDER BY does not default to descending, must come after WHERE, executes on the server, and is not executed first.

Multiple choice technology databases

From SQL*Plus, you issue this SELECT statement: SELECT* From orders; You use this statement to retrieve data from a data table for __________.

  1. updating

  2. viewing

  3. deleting

  4. inserting

  5. truncating
Reveal answer Fill a bubble to check yourself
B,D Correct answer
Explanation

A SELECT query retrieves data for viewing. In the context of Oracle SQL training, it is also noted that the retrieved data can be used directly for inserting into another table via INSERT INTO ... SELECT, making both options correct.

Multiple choice technology databases

Evaluate this SQL*Plus command: START delaccount Which task will this command accomplish?

  1. It executes the DELACCOUNT PL/SQL routine.

  2. It runs the DELACCOUNT.SQL script file.

  3. It creates the DELACCOUNT file using the default file extension.

  4. It invokes the editor to edit the contents of the DELACCOUNT file.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To solve this question, the user needs to have some basic knowledge about SQL*Plus. This command starts a SQL*Plus script from the current directory.

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

A. It executes the DELACCOUNT PL/SQL routine. - This option is incorrect as START command is used to start a SQL script or a file and not a PL/SQL routine.

B. It runs the DELACCOUNT.SQL script file. - This option is correct as the START command is used to start a SQL script or a file. Here, the command START delaccount will run the DELACCOUNT.SQL script file.

C. It creates the DELACCOUNT file using the default file extension. - This option is incorrect as the START command is used to start an existing SQL script or file and not for creating a new file.

D. It invokes the editor to edit the contents of the DELACCOUNT file. - This option is incorrect as the START command is used to start an existing SQL script or file and not for invoking the editor to edit the contents of a file.

Therefore, the answer is:

The Answer is: B

Multiple choice technology databases

Which /SQL*Plus feature can be used to replace values in the WHERE clause?

  1. Substitution variables

  2. Replacement variables

  3. Prompt variables

  4. Instead-of variables
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To solve this question, the user needs to know the basic functionalities of SQL*Plus.

The feature that can be used to replace values in the WHERE clause is Substitution variables.

Option A is correct. Substitution variables can be used to replace values in the WHERE clause at runtime. They begin with an ampersand (&) and can be used to replace any part of the SQL query, including the WHERE clause.

Option B is incorrect. Replacement variables are not a feature of SQL*Plus, and they cannot be used to replace values in the WHERE clause.

Option C is incorrect. Prompt variables are also not a feature of SQL*Plus and cannot be used to replace values in the WHERE clause.

Option D is incorrect. Instead-of variables are not a feature of SQL*Plus and cannot be used to replace values in the WHERE clause.

The answer is: A

Multiple choice technology databases

Evaluate the SQL statement: SELECT LPAD (salary,10,*) FROM EMP WHERE EMP_ID = 1001; If the employee with the EMP_ID 1001 has a salary of 17000, what is displayed?

  1. 17000.00

  2. 17000*****

  3. ****170.00

  4. **17000.00

  5. an error statement
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To understand this SQL statement, the user should know about the LPAD function, which pads a string with a specified character until the string reaches a specified length. In this case, the salary column will be padded with asterisks (*) until it is 10 characters long.

The WHERE clause restricts the query to employees with an EMP_ID of 1001.

Assuming the employee with EMP_ID 1001 has a salary of 17000, the LPAD function will pad the salary with asterisks () until it has a length of 10 characters, resulting in the string "***17000". Therefore, the correct answer is:

The Answer is: A. ****170.00

Multiple choice technology databases

Which one is a system privilege?

  1. select

  2. delete

  3. execute

  4. alter table

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

A system privilege like CREATE TABLE allows users to perform administrative actions across the database schema. The other options, like SELECT, DELETE, EXECUTE, and ALTER TABLE, are object privileges that govern actions on specific existing database objects.

Multiple choice technology databases

Which two are types of functions available in SQL?

  1. String

  2. Character

  3. Integer

  4. Calander

  5. Numeric

  6. Translation
Reveal answer Fill a bubble to check yourself
B,E Correct answer
Explanation

SQL functions are categorized into types including Character functions (for string manipulation) and Numeric functions (for mathematical operations). Options A, C, D, and F are incorrect - 'String' and 'Integer' are data types not function categories, 'Calendar' should be 'Date', and 'Translation' is not a SQL function category.