Quiz about Relational Database Design Quiz

Question 1

What components make up the Oracle server technologies?

Accepted Answer

Oracle database, Oracle WebLogic Server, Oracle Enterprise Manager, and application development tools and languages.

Answer

Oracle database and SQL*Plus only.

Answer

Oracle WebLogic Server and Java only.

Answer

Oracle Enterprise Manager and Cloud Control only.

Question 2

What is the primary role of the Oracle database within the Oracle product set?

Accepted Answer

To serve as the repository for data and the engine that manages access to it.

Answer

To generate web user interfaces.

Answer

To run application servers centrally.

Answer

To provide comprehensive administration tools.

Question 3

In the client-server processing model implemented by the Oracle server, what is the role of the client tier?

Accepted Answer

To generate the user interface and much of the application logic, including SQL commands.

Answer

To execute SQL commands directly against the database.

Answer

To manage the data exclusively.

Answer

To run all applications centrally on application servers.

Question 4

Which analogy is often used to describe how computing resources are provided to end users in a cloud environment?

Accepted Answer

Delivery of domestic electricity, supplied on demand without knowing the source.

Answer

A local area network connection.

Answer

A client-server split across different geographical locations.

Answer

A single, dedicated server managing all IT operations.

Question 5

What is SQL primarily used for in the context of application development?

Accepted Answer

Data access, but not for developing complete applications.

Answer

Developing complete user interfaces.

Answer

Manipulating rows individually with procedural structures.

Answer

Writing system-level code like C or Java.

Question 6

What is critical to understanding SQL and involves modeling business data into a form suitable for storing in relational tables?

Accepted Answer

Normalization.

Answer

Cloud Computing.

Answer

Oracle WebLogic Server.

Answer

Object-oriented database design.

Question 7

What problem does unnecessary duplication of data in a database design typically indicate, and what can it lead to?

Accepted Answer

Poor design, leading to wasteful storage and potential integrity errors (anomalies).

Answer

Efficient data storage, leading to improved performance.

Answer

Optimal balance between storage and access, ensuring long-term cost savings.

Answer

Flexibility in data manipulation, enabling faster retrieval.

Question 8

What does First Normal Form (1NF) primarily address in relational database design?

Accepted Answer

Eliminating unnecessary repeating groups of data.

Answer

Ensuring all columns have unique values.

Answer

Defining links between tables with foreign keys.

Answer

Allowing a single car to be bought and sold multiple times.

Question 9

How does the relational paradigm model data?

Accepted Answer

As two-dimensional tables consisting of rows and columns.

Answer

As hierarchical structures with parent-child relationships.

Answer

As object-oriented structures with user-defined types.

Answer

As single, unbounded text documents.

Question 10

Which international bodies manage the SQL standard?

Accepted Answer

ISO and ANSI.

Answer

Oracle Corporation and IBM.

Answer

Microsoft and PostgreSQL Global Development Group.

Answer

McGraw-Hill Education and Oracle University.

Question 11

Which of the following are Data Manipulation Language (DML) commands in SQL?

Accepted Answer

SELECT, INSERT, UPDATE, DELETE, MERGE.

Answer

CREATE, ALTER, DROP.

Answer

GRANT, REVOKE.

Answer

COMMIT, ROLLBACK, SAVEPOINT.

Question 12

SQL is described as a set-oriented language. What does this imply about its capabilities for application development?

Accepted Answer

It is purely a data access language and needs to work with a procedural language for application development.

Answer

It provides complete solutions including user interface facilities.

Answer

It can directly handle flow control, conditional branching, and iteration.

Answer

It processes rows one by one through embedded calls.

Question 13

What is a key difference between SQLPlus and SQL Developer in terms of their interface requirements?

Accepted Answer

SQL Developer offers more functionality and needs a graphical terminal, whereas SQLPlus can be used on character mode devices.

Answer

SQLPlus offers more functionality, while SQL Developer is simpler.

Answer

SQLPlus requires a graphical terminal, while SQL Developer can be used on character mode devices.

Answer

Both tools require an Oracle WebLogic Server to operate.

Question 14

In Oracle terminology, what defines a database schema?

Accepted Answer

All the objects in the database owned by one user.

Answer

A person who can log on to the database.

Answer

A construct that makes an object accessible to other users without requiring its schema name.

Answer

A predefined set of tables like HR or OE.

Question 15

If an object (e.g., a table) does not exist in a user's own schema, how must it be accessed?

Accepted Answer

The object name must be qualified with the name of the schema in which it resides (e.g., HR.EMPLOYEES).

Answer

It is impossible to access the object.

Answer

A CREATE SCHEMA command must be run first.

Answer

Only the database administrator can access it.

Question 16

In the HR schema's REGIONS table, which column is identified as the primary key?

Accepted Answer

region_id.

Answer

region_name.

Answer

country_id.

Answer

location_id.

Question 17

What is the primary purpose of the HR and OE demonstration schemas provided by Oracle?

Accepted Answer

To store example data for illustrating the use of SQL and advanced Oracle development facilities.

Answer

To enforce strict security protocols in production databases.

Answer

To replace actual business application data.

Answer

To benchmark database performance without actual user data.

Question 18

What is the fundamental effect of a SQL SELECT statement on data stored in the database?

Accepted Answer

It provides a read-only method of extracting information and never alters the data.

Answer

It always alters information, ensuring data modification.

Answer

It can insert, update, or delete data depending on the clauses used.

Answer

It permanently removes duplicate records from tables.

Question 19

Which three concepts from relational theory encompass the capabilities of the SELECT statement?

Accepted Answer

Projection, Selection, Joining.

Answer

Inserting, Updating, Deleting.

Answer

Defining, Altering, Dropping.

Answer

Committing, Rolling back, Saving.

Question 20

What are the two mandatory clauses of a basic SELECT statement syntax?

Accepted Answer

SELECT and FROM.

Answer

SELECT and WHERE.

Answer

SELECT and ORDER BY.

Answer

FROM and WHERE.

Question 21

What is the primary function of the DISTINCT keyword in a SELECT statement?

Accepted Answer

To eliminate duplicate values from the results set.

Answer

To sort the results in ascending order.

Answer

To count all rows, including duplicates.

Answer

To project only specific columns.

Question 22

Why should caution be exercised when executing a statement like SELECT * FROM huge_table;?

Accepted Answer

It may cause performance issues if the table contains millions of rows of data.

Answer

It might accidentally delete all data.

Answer

It always returns an error if the table is too large.

Answer

It automatically converts all data types, which can lead to inconsistencies.

Question 23

In the Oracle data dictionary, how is metadata about different database objects typically stored by default?

Accepted Answer

In uppercase.

Answer

In lowercase.

Answer

In mixed case, preserving original input.

Answer

In a binary format, without case sensitivity.

Question 24

How is a NULL value formally defined in SQL?

Accepted Answer

As a value that is unavailable, unassigned, unknown, or inapplicable.

Answer

As the number zero.

Answer

As a blank space.

Answer

As an empty string.

Question 25

What is the result when performing arithmetic operations with a NULL value?

Accepted Answer

The arithmetic operation always yields a NULL result.

Answer

The operation returns zero.

Answer

The operation ignores the NULL and proceeds with other values.

Answer

The operation results in an error.

Question 26

What is the primary function of the WHERE clause in a SELECT statement?

Accepted Answer

To limit or restrict the rows returned based on one or more conditions.

Answer

To sort the rows retrieved by a query.

Answer

To define new columns in the result set.

Answer

To combine results from multiple queries.

Question 27

When specifying character or date literals in a WHERE clause, what are they delimited by?

Accepted Answer

Single quotation marks ('').

Answer

Double quotation marks ("").

Answer

Parentheses ().

Answer

Square brackets [].

Question 28

Which two wildcard characters are used with the LIKE operator for pattern matching?

Accepted Answer

% (percentage symbol) and _ (underscore character).

Answer

* (asterisk) and ? (question mark).

Answer

# (hash) and @ (at symbol).

Answer

! (exclamation mark) and & (ampersand).

Question 29

Which operator is used to test column values for equality to NULL?

Accepted Answer

IS NULL.

Answer

= (is equal to).

Answer

IN (NULL).

Answer

NOT NULL.

Question 30

In a WHERE clause, if Condition X is TRUE and Condition Y is NULL, what is the result of Condition X AND Condition Y?

Accepted Answer

NULL.

Answer

TRUE.

Answer

FALSE.

Answer

Error.

Question 31

In a WHERE clause, if Condition X is FALSE and Condition Y is NULL, what is the result of Condition X OR Condition Y?

Accepted Answer

NULL.

Answer

TRUE.

Answer

FALSE.

Answer

Error.

Question 32

How does the ORDER BY clause affect the rows returned by a SELECT statement?

Accepted Answer

It only affects the presentation (ordering) of the results, not the specific rows returned.

Answer

It changes the specific records that are selected from the table.

Answer

It influences the records returned by a query by filtering them.

Answer

It restricts the number of rows to a maximum limit.

Question 33

When sorting data using the ORDER BY clause, how can rows with NULL values in the sorting column be positioned?

Accepted Answer

They can be explicitly listed first (NULLS FIRST) or last (NULLS LAST).

Answer

They are always listed first by default.

Answer

They are always listed last by default.

Answer

NULL values cause an error when sorting.

Question 34

What is the purpose of ampersand substitution variables in SQL?

Accepted Answer

To allow user input to restrict and sort output at runtime.

Answer

To permanently replace a value in the database.

Answer

To define new data types for columns.

Answer

To combine multiple queries into a single result set.

Question 35

What is a function defined as in SQL?

Accepted Answer

A program written to optionally accept input parameters, perform an operation, and return a single value.

Answer

A program that can accept input parameters and always returns multiple values.

Answer

A program that is explicitly written to alter data in tables.

Answer

A set of SQL commands used to create database objects.

Question 36

What is the key difference between single-row functions and multiple-row (group) functions?

Accepted Answer

Single-row functions execute once for each row selected, while multiple-row functions execute once for the entire set of rows queried.

Answer

Single-row functions always return numeric values, while multiple-row functions return characters.

Answer

Single-row functions can only be used in SELECT statements, while multiple-row functions can be used in WHERE clauses.

Answer

Single-row functions cannot accept input parameters, but multiple-row functions can.

Question 37

What does the INITCAP function do?

Accepted Answer

Accepts a string of characters and returns each word in title case (first letter capitalized).

Answer

Converts a string of characters to all lowercase.

Answer

Converts a string of characters to all uppercase.

Answer

Extracts the initial characters of a string.

Question 38

If a column FIRST_NAME contains 'JOHN', what will LOWER(FIRST_NAME) return?

Accepted Answer

'john'.

Answer

'John'.

Answer

'JOHN'.

Answer

Error.

Question 39

What does the LENGTH function return?

Accepted Answer

The number of characters in a character string.

Answer

The number of bytes in a character string.

Answer

The length of a numeric expression in bytes.

Answer

Whether a string contains any null values.

Question 40

Which operator can be used as an alternative to the CONCAT function for joining character strings?

Accepted Answer

|| (double pipe symbol).

Answer

+ (addition operator).

Answer

* (multiplication operator).

Answer

- (subtraction operator).

Question 41

What does the MONTHS_BETWEEN function compute?

Accepted Answer

The number of months between two given date parameters.

Answer

The number of days between two dates.

Answer

The number of years between two dates.

Answer

The current month of the year.

Question 42

What value does the SYSDATE function typically return?

Accepted Answer

The system date and time when the function is executed.

Answer

The last day of the current month.

Answer

The number of months since the database was installed.

Answer

The start date of the current transaction.

Question 43

What is implicit data type conversion?

Accepted Answer

When the Oracle server automatically converts data from one data type to another to complete an operation.

Answer

Converting data types using explicit functions like TO_CHAR or TO_NUMBER.

Answer

Manually changing a column's data type using ALTER TABLE.

Answer

Defining a column with a flexible data type that accepts various input types.

Question 44

What is the main purpose of the NVL function?

Accepted Answer

To replace a NULL value with a specified alternative value.

Answer

To check if a value is unique.

Answer

To convert a non-NULL value to NULL.

Answer

To concatenate two strings.

Question 45

What kind of logic do CASE expressions provide in SQL queries?

Accepted Answer

If-then-else conditional logic.

Answer

Looping and iteration.

Answer

Data aggregation for groups.

Answer

Joining multiple tables based on conditions.

Question 46

Which other conditional function provides similar if-then-else logic to DECODE?

Accepted Answer

CASE.

Answer

NVL.

Answer

COALESCE.

Answer

NULLIF.

Question 47

What would TO_CHAR(123.56, '999.9') return in Oracle SQL?

Accepted Answer

'123.6'.

Answer

'123.5'.

Answer

'123.56'.

Answer

An error, as the format mask is shorter than the number.

Question 48

What are group functions also known as?

Accepted Answer

Summary or aggregate functions.

Answer

Single-row functions.

Answer

Scalar functions.

Answer

Conversion functions.

Question 49

When AVG(COMMISSION_PCT) is calculated, what values are considered?

Accepted Answer

Only the non-null values.

Answer

All values, treating NULL as zero.

Answer

Only the distinct non-null values.

Answer

It always returns NULL if any NULL exists.

Question 50

What does COUNT(*) return?

Accepted Answer

The number of rows in the group, including those with NULL values in any column.

Answer

The number of rows with non-null values in a specific column.

Answer

The number of unique rows in a table or group.

Answer

The sum of all numeric values in a column.

Question 51

Can multiple group functions be used in the same SELECT list?

Accepted Answer

Yes, multiple group functions may be used in the same SELECT list.

Answer

No, only one group function is allowed per SELECT statement.

Answer

Yes, but only if they operate on the same column.

Answer

Only if a HAVING clause is also present.

Question 52

What type of parameter does the SUM function expect?

Accepted Answer

One numeric parameter.

Answer

Any data type, as long as it can be implicitly converted.

Answer

A single character parameter.

Answer

A date parameter.

Question 53

What is the primary function of the HAVING clause?

Accepted Answer

To limit or restrict group-level rows based on conditions.

Answer

To restrict individual rows before grouping.

Answer

To define the columns that will be displayed in the output.

Answer

To sort the aggregated results.

Question 54

Under what condition can a HAVING clause be specified?

Accepted Answer

Only when a GROUP BY clause is present.

Answer

Only if a WHERE clause is also present.

Answer

It can always be specified, regardless of other clauses.

Answer

Only if a SELECT DISTINCT statement is used.

Question 55

Which of the three pillars of relational theory does the chapter 'Displaying Data from Multiple Tables' primarily focus on?

Accepted Answer

Joining.

Answer

Selection.

Answer

Projection.

Answer

Aggregation.

Question 56

If a source table has 3 rows and a target table has 4 rows, how many rows will a cross join (Cartesian product) between them result in?

Accepted Answer

12 rows.

Answer

3 rows.

Answer

4 rows.

Answer

7 rows.

Question 57

How does the NATURAL JOIN clause identify join columns between two tables?

Accepted Answer

It automatically joins based on all columns with shared names between the two tables.

Answer

It requires explicit specification of join columns in an ON clause.

Answer

It joins tables based on primary key-foreign key relationships only.

Answer

It creates a Cartesian product by default.

Question 58

Which three keywords are mutually exclusive in the context of the same join clause?

Accepted Answer

NATURAL, USING, ON.

Answer

SELECT, FROM, WHERE.

Answer

LEFT, RIGHT, FULL.

Answer

AND, OR, NOT.

Question 59

When joining N tables, what is the minimum number of join conditions required to avoid an accidental Cartesian join?

Accepted Answer

N - 1.

Answer

N.

Answer

N + 1.

Answer

No specific number, it depends on the data.

Question 60

When using the JOIN...USING clause, how should the join columns be specified?

Accepted Answer

As a comma-separated list within parentheses after the USING keyword, with unqualified column references.

Answer

In an ON clause with fully qualified names.

Answer

Automatically by the database, without explicit naming.

Answer

As a subquery that returns the matching column names.

Question 61

What is a subquery?

Accepted Answer

A SELECT statement whose output is used as input to another SELECT, INSERT, UPDATE, or DELETE statement, or another subquery.

Answer

A stand-alone SELECT statement that always returns a single value.

Answer

A query that combines results from multiple independent SELECT statements.

Answer

A statement used exclusively in the FROM clause to create temporary tables.

Question 62

What are the distinguishing characteristics of a scalar subquery?

Accepted Answer

It returns exactly one value: a single row with a single column.

Answer

It returns a set of rows and multiple columns.

Answer

It references columns in the parent query, making its result dependent on the parent.

Answer

It can only be used in the FROM clause.

Question 63

When a subquery is used in the SELECT list (projection), what is a mandatory requirement for that subquery?

Accepted Answer

It must be scalar.

Answer

It must return multiple rows.

Answer

It must be correlated.

Answer

It must reference a table from the FROM clause.

Question 64

What is a general rule regarding the use of NOT IN with subqueries, particularly concerning NULL values?

Accepted Answer

NOT IN should be avoided if there's a chance the subquery result set might include a NULL, as it can lead to problems.

Answer

NOT IN is always reliable, even with NULL values.

Answer

NOT IN implicitly converts NULL to zero, making it safe.

Answer

NOT IN can only be used with scalar subqueries, thus avoiding NULL issues.

Relational Database Design Quiz

Create a free account and access millions of resources

Similar Resources on Wayground

Popular Resources on Wayground

Discover more resources for Mathematics