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

In the following which is not a DDL ?

  1. Truncate

  2. Create

  3. Alter

  4. All the above
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

TRUNCATE is a DML (Data Manipulation Language) operation, not DDL. DDL includes CREATE, ALTER, DROP, TRUNCATE, and RENAME which define database structure. Despite some debate, TRUNCATE is classified as DDL because it deallocates data pages and resets identity values, unlike DELETE which is DML.

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 FROM employee;

  2. SELECT position, manager DISTINCT FROM employee;

  3. SELECT position, manager FROM employee;

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

To solve this question, the user needs to know the syntax of the SELECT statement and the purpose of the DISTINCT keyword. The user must retrieve unique combinations of the POSITION and MANAGER values from the EMPLOYEE table.

Option A is the correct answer:

A. SELECT DISTINCT position, manager FROM employee;

This option retrieves unique combinations of the POSITION and MANAGER values from the EMPLOYEE table. The DISTINCT keyword ensures that each combination is unique and not repeated in the result set.

Option B is incorrect:

B. SELECT position, manager DISTINCT FROM employee;

This option is not valid because the keyword DISTINCT is in the wrong position. The correct syntax is SELECT DISTINCT, not SELECT column1, column2 DISTINCT.

Option C is incorrect:

C. SELECT position, manager FROM employee;

This option retrieves all combinations of the POSITION and MANAGER values from the EMPLOYEE table, including duplicates.

Option D is incorrect:

D. SELECT position, DISTINCT manager FROM employee;

This option is not valid because the keyword DISTINCT is in the wrong position. The correct syntax is SELECT DISTINCT, not SELECT column1, DISTINCT column2.

The Answer is: A

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
Explanation

SQL*Plus is Oracle's command-line interface for executing SQL. Option C is correct because SQL*Plus commands are used to interact with and manipulate data. Option D is correct because SQL*Plus provides commands to manipulate table definitions (DDL operations). Option A is false because SQL*Plus commands CAN be abbreviated. Option B is false because SQL*Plus is a command-line tool, not browser-based. Option E describes SQL*Plus as 'the' interface, which is misleading since Oracle has multiple interfaces.

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 command that displays the structure of a database object (table, view, etc.). INSERT, UPDATE, SELECT, DELETE, and RENAME are all standard SQL DML or DDL statements, not SQL*Plus-specific commands. DESCRIBE (or DESC for short) is an Oracle-specific utility command.

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

Option A is correct - ORDER BY defaults to ascending order unless DESC is explicitly specified. Option E is correct - ORDER BY is typically the last clause in a SELECT statement (after FROM, WHERE, GROUP BY, HAVING). Option B is false (ascending is default). Option C is false - WHERE comes before ORDER BY. Option D is false - ORDER BY executes on the server, not client. Option F is false - ORDER BY is one of the last operations in query execution.

Multiple choice technology databases

The size of a table does NOT need to be specified.

  1. True

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

In SQL, table size is determined dynamically by the amount of data stored and the storage allocation. Tables are designed to grow automatically as data is inserted. Unlike some programming data structures, SQL tables do not require pre-specifying capacity or dimensions. The database manages physical storage transparently.

Multiple choice technology databases

Can you use both HAVING and WHERE SQL clauses in one SQL statement?

  1. Yes

  2. No

  3. Sometimes possible

  4. Can't Say
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

HAVING and WHERE can both be used in the same SQL statement. WHERE filters individual rows before grouping, while HAVING filters groups after aggregation. For example: SELECT department, COUNT() FROM employees WHERE salary > 50000 GROUP BY department HAVING COUNT() > 5. The WHERE clause filters rows first, then GROUP BY creates groups, then HAVING filters those groups.

Multiple choice technology databases

What does the following SQL statement do: SELECT Customer, COUNT(Order) FROM Sales GROUP BY Customer HAVING COUNT(Order) >5

  1. Selects the total number of orders from the Sales table, if this number is greater than 5

  2. Selects all Customers from the Sales table

  3. Both of them above

  4. Selects all customers from table Sales that have made more than 5 orders
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The HAVING clause filters groups created by GROUP BY. Here, GROUP BY Customer groups the sales records by customer, and HAVING COUNT(Order) > 5 filters these groups to only include customers who have more than 5 orders. The other options incorrectly describe the query's behavior.

Multiple choice technology databases

What does the SQL FROM clause do?

  1. Specifies data retrieved

  2. Specifies the tables to retrieve rows from.

  3. Specifies the columns we are retrieving.

  4. Specifies a search condition.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The SQL FROM clause specifies which table(s) to retrieve data from. It's a mandatory clause in most SELECT queries and identifies the source of the data. The SELECT clause (not FROM) specifies which columns to retrieve. The WHERE clause specifies search conditions, not FROM.

Multiple choice technology programming languages

What are the check tables and value tables?

  1. Check table will be at field level checking.

  2. Value table will be at domain level checking

  3. Value table will be at field level checking

  4. Check table will be at domain level checking
Reveal answer Fill a bubble to check yourself
A,B Correct answer
Explanation

In SAP's data dictionary, Check tables provide field-level validation (checking individual field values against a master table), while Value tables provide domain-level validation (checking all fields sharing a domain against allowed values). Check tables validate specific fields, and Value tables validate at the domain level across multiple fields.

Multiple choice technology programming languages

Search help can not be attach to

  1. Domain

  2. Field of a table

  3. Check table

  4. Type
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Search help objects can be attached to domains, table fields, and check tables in SAP ABAP Dictionary. A data type itself is not an object where search help can be directly attached - you must attach it to a domain or field that uses that type.

Multiple choice technology programming languages

It is better to buffer a table when

  1. When a table is read infrequently

  2. When a table is linked to check tables

  3. When a table is read frequently and the data seldom changes

  4. When a single record is to be picked up
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Buffering is beneficial when a table is read frequently (to benefit from cached access) but the data seldom changes (to avoid cache invalidation overhead). Infrequently read tables gain no benefit, while tables linked to check tables and single-record lookups have different optimization strategies.