NEW
Font size
WorksheetsRelational Database Design Quiz
Total questions: 73
Worksheet time: 37mins
What components make up the Oracle server technologies?
Oracle database, Oracle WebLogic Server, Oracle Enterprise Manager, and application development tools and languages.
Oracle database and SQL*Plus only.
Oracle WebLogic Server and Java only.
Oracle Enterprise Manager and Cloud Control only.
What is the primary role of the Oracle database within the Oracle product set?
To generate web user interfaces.
To run application servers centrally.
To serve as the repository for data and the engine that manages access to it.
To provide comprehensive administration tools.
In the client-server processing model implemented by the Oracle server, what is the role of the client tier?
To execute SQL commands directly against the database.
To manage the data exclusively.
To generate the user interface and much of the application logic, including SQL commands.
To run all applications centrally on application servers.
Which analogy is often used to describe how computing resources are provided to end users in a cloud environment?
A local area network connection.
Delivery of domestic electricity, supplied on demand without knowing the source.
A client-server split across different geographical locations.
A single, dedicated server managing all IT operations.
What is SQL primarily used for in the context of application development?
Developing complete user interfaces.
Manipulating rows individually with procedural structures.
Data access, but not for developing complete applications.
Writing system-level code like C or Java.
What is critical to understanding SQL and involves modeling business data into a form suitable for storing in relational tables?
Cloud Computing.
Oracle WebLogic Server.
Normalization.
Object-oriented database design.
What problem does unnecessary duplication of data in a database design typically indicate, and what can it lead to?
Efficient data storage, leading to improved performance.
Poor design, leading to wasteful storage and potential integrity errors (anomalies).
Optimal balance between storage and access, ensuring long-term cost savings.
Flexibility in data manipulation, enabling faster retrieval.
What does First Normal Form (1NF) primarily address in relational database design?
Ensuring all columns have unique values.
Eliminating unnecessary repeating groups of data.
Defining links between tables with foreign keys.
Allowing a single car to be bought and sold multiple times.
How does the relational paradigm model data?
As hierarchical structures with parent-child relationships.
As two-dimensional tables consisting of rows and columns.
As object-oriented structures with user-defined types.
As single, unbounded text documents.
Which international bodies manage the SQL standard?
Oracle Corporation and IBM.
Microsoft and PostgreSQL Global Development Group.
ISO and ANSI.
McGraw-Hill Education and Oracle University.
Which of the following are Data Manipulation Language (DML) commands in SQL?
CREATE, ALTER, DROP.
GRANT, REVOKE.
SELECT, INSERT, UPDATE, DELETE, MERGE.
COMMIT, ROLLBACK, SAVEPOINT.
SQL is described as a set-oriented language. What does this imply about its capabilities for application development?
It provides complete solutions including user interface facilities.
It can directly handle flow control, conditional branching, and iteration.
It is purely a data access language and needs to work with a procedural language for application development.
It processes rows one by one through embedded calls.
What is a key difference between SQLPlus and SQL Developer in terms of their interface requirements?
SQLPlus offers more functionality, while SQL Developer is simpler.
SQLPlus requires a graphical terminal, while SQL Developer can be used on character mode devices.
SQL Developer offers more functionality and needs a graphical terminal, whereas SQLPlus can be used on character mode devices.
Both tools require an Oracle WebLogic Server to operate.
In Oracle terminology, what defines a database schema?
A person who can log on to the database.
All the objects in the database owned by one user.
A construct that makes an object accessible to other users without requiring its schema name.
A predefined set of tables like HR or OE.
If an object (e.g., a table) does not exist in a user's own schema, how must it be accessed?
It is impossible to access the object.
The object name must be qualified with the name of the schema in which it resides (e.g., HR.EMPLOYEES).
A CREATE SCHEMA command must be run first.
Only the database administrator can access it.
In the HR schema's REGIONS table, which column is identified as the primary key?
region_name.
country_id.
region_id.
location_id.
What is the primary purpose of the HR and OE demonstration schemas provided by Oracle?
To enforce strict security protocols in production databases.
To store example data for illustrating the use of SQL and advanced Oracle development facilities.
To replace actual business application data.
To benchmark database performance without actual user data.
What is the fundamental effect of a SQL SELECT statement on data stored in the database?
It always alters information, ensuring data modification.
It provides a read-only method of extracting information and never alters the data.
It can insert, update, or delete data depending on the clauses used.
It permanently removes duplicate records from tables.
Which three concepts from relational theory encompass the capabilities of the SELECT statement?
Inserting, Updating, Deleting.
Projection, Selection, Joining.
Defining, Altering, Dropping.
Committing, Rolling back, Saving.
What are the two mandatory clauses of a basic SELECT statement syntax?
SELECT and WHERE.
SELECT and ORDER BY.
SELECT and FROM.
FROM and WHERE.
What is the primary function of the DISTINCT keyword in a SELECT statement?
To sort the results in ascending order.
To eliminate duplicate values from the results set.
To count all rows, including duplicates.
To project only specific columns.
Why should caution be exercised when executing a statement like SELECT * FROM huge_table;?
It might accidentally delete all data.
It always returns an error if the table is too large.
It may cause performance issues if the table contains millions of rows of data.
It automatically converts all data types, which can lead to inconsistencies.
In the Oracle data dictionary, how is metadata about different database objects typically stored by default?
In lowercase.
In mixed case, preserving original input.
In uppercase.
In a binary format, without case sensitivity.
How is a NULL value formally defined in SQL?
As the number zero.
As a blank space.
As a value that is unavailable, unassigned, unknown, or inapplicable.
As an empty string.
What is the result when performing arithmetic operations with a NULL value?
The operation returns zero.
The operation ignores the NULL and proceeds with other values.
The arithmetic operation always yields a NULL result.
The operation results in an error.
What is the primary function of the WHERE clause in a SELECT statement?
To sort the rows retrieved by a query.
To limit or restrict the rows returned based on one or more conditions.
To define new columns in the result set.
To combine results from multiple queries.
When specifying character or date literals in a WHERE clause, what are they delimited by?
Double quotation marks ("").
Parentheses ().
Single quotation marks ('').
Square brackets [].
Which two wildcard characters are used with the LIKE operator for pattern matching?
* (asterisk) and ? (question mark).
% (percentage symbol) and _ (underscore character).
# (hash) and @ (at symbol).
! (exclamation mark) and & (ampersand).
Which operator is used to test column values for equality to NULL?
= (is equal to).
IS NULL.
IN (NULL).
NOT NULL.
In a WHERE clause, if Condition X is TRUE and Condition Y is NULL, what is the result of Condition X AND Condition Y?
TRUE.
FALSE.
NULL.
Error.
In a WHERE clause, if Condition X is FALSE and Condition Y is NULL, what is the result of Condition X OR Condition Y?
TRUE.
FALSE.
NULL.
Error.
How does the ORDER BY clause affect the rows returned by a SELECT statement?
It changes the specific records that are selected from the table.
It influences the records returned by a query by filtering them.
It only affects the presentation (ordering) of the results, not the specific rows returned.
It restricts the number of rows to a maximum limit.
When sorting data using the ORDER BY clause, how can rows with NULL values in the sorting column be positioned?
They are always listed first by default.
They are always listed last by default.
They can be explicitly listed first (NULLS FIRST) or last (NULLS LAST).
NULL values cause an error when sorting.
What is the purpose of ampersand substitution variables in SQL?
To permanently replace a value in the database.
To allow user input to restrict and sort output at runtime.
To define new data types for columns.
To combine multiple queries into a single result set.
What is a function defined as in SQL?
A program that can accept input parameters and always returns multiple values.
A program that is explicitly written to alter data in tables.
A program written to optionally accept input parameters, perform an operation, and return a single value.
A set of SQL commands used to create database objects.
What is the key difference between single-row functions and multiple-row (group) functions?
Single-row functions always return numeric values, while multiple-row functions return characters.
Single-row functions execute once for each row selected, while multiple-row functions execute once for the entire set of rows queried.
Single-row functions can only be used in SELECT statements, while multiple-row functions can be used in WHERE clauses.
Single-row functions cannot accept input parameters, but multiple-row functions can.
What does the INITCAP function do?
Converts a string of characters to all lowercase.
Converts a string of characters to all uppercase.
Accepts a string of characters and returns each word in title case (first letter capitalized).
Extracts the initial characters of a string.
If a column FIRST_NAME contains 'JOHN', what will LOWER(FIRST_NAME) return?
'John'.
'JOHN'.
'john'.
Error.
What does the LENGTH function return?
The number of bytes in a character string.
The number of characters in a character string.
The length of a numeric expression in bytes.
Whether a string contains any null values.
Which operator can be used as an alternative to the CONCAT function for joining character strings?
+ (addition operator).
|| (double pipe symbol).
* (multiplication operator).
- (subtraction operator).
What does the MONTHS_BETWEEN function compute?
The number of days between two dates.
The number of years between two dates.
The number of months between two given date parameters.
The current month of the year.
What value does the SYSDATE function typically return?
The last day of the current month.
The system date and time when the function is executed.
The number of months since the database was installed.
The start date of the current transaction.
What is implicit data type conversion?
Converting data types using explicit functions like TO_CHAR or TO_NUMBER.
When the Oracle server automatically converts data from one data type to another to complete an operation.
Manually changing a column's data type using ALTER TABLE.
Defining a column with a flexible data type that accepts various input types.
What is the main purpose of the NVL function?
To check if a value is unique.
To convert a non-NULL value to NULL.
To replace a NULL value with a specified alternative value.
To concatenate two strings.
What kind of logic do CASE expressions provide in SQL queries?
Looping and iteration.
If-then-else conditional logic.
Data aggregation for groups.
Joining multiple tables based on conditions.
Which other conditional function provides similar if-then-else logic to DECODE?
NVL.
COALESCE.
CASE.
NULLIF.
What would TO_CHAR(123.56, '999.9') return in Oracle SQL?
'123.5'.
'123.6'.
'123.56'.
An error, as the format mask is shorter than the number.
What are group functions also known as?
Single-row functions.
Scalar functions.
Summary or aggregate functions.
Conversion functions.
When AVG(COMMISSION_PCT) is calculated, what values are considered?
All values, treating NULL as zero.
Only the non-null values.
Only the distinct non-null values.
It always returns NULL if any NULL exists.
What does COUNT(*) return?
The number of rows with non-null values in a specific column.
The number of unique rows in a table or group.
The number of rows in the group, including those with NULL values in any column.
The sum of all numeric values in a column.
Can multiple group functions be used in the same SELECT list?
No, only one group function is allowed per SELECT statement.
Yes, but only if they operate on the same column.
Yes, multiple group functions may be used in the same SELECT list.
Only if a HAVING clause is also present.
What type of parameter does the SUM function expect?
Any data type, as long as it can be implicitly converted.
A single character parameter.
One numeric parameter.
A date parameter.
What is the primary function of the HAVING clause?
To restrict individual rows before grouping.
To define the columns that will be displayed in the output.
To limit or restrict group-level rows based on conditions.
To sort the aggregated results.
Under what condition can a HAVING clause be specified?
Only if a WHERE clause is also present.
Only when a GROUP BY clause is present.
It can always be specified, regardless of other clauses.
Only if a SELECT DISTINCT statement is used.
Which of the three pillars of relational theory does the chapter 'Displaying Data from Multiple Tables' primarily focus on?
Selection.
Projection.
Joining.
Aggregation.
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?
3 rows.
4 rows.
12 rows.
7 rows.
How does the NATURAL JOIN clause identify join columns between two tables?
It requires explicit specification of join columns in an ON clause.
It automatically joins based on all columns with shared names between the two tables.
It joins tables based on primary key-foreign key relationships only.
It creates a Cartesian product by default.
Which three keywords are mutually exclusive in the context of the same join clause?
SELECT, FROM, WHERE.
NATURAL, USING, ON.
LEFT, RIGHT, FULL.
AND, OR, NOT.
When joining N tables, what is the minimum number of join conditions required to avoid an accidental Cartesian join?
N.
N + 1.
N - 1.
No specific number, it depends on the data.
When using the JOIN...USING clause, how should the join columns be specified?
In an ON clause with fully qualified names.
As a comma-separated list within parentheses after the USING keyword, with unqualified column references.
Automatically by the database, without explicit naming.
As a subquery that returns the matching column names.
What is a subquery?
A stand-alone SELECT statement that always returns a single value.
A SELECT statement whose output is used as input to another SELECT, INSERT, UPDATE, or DELETE statement, or another subquery.
A query that combines results from multiple independent SELECT statements.
A statement used exclusively in the FROM clause to create temporary tables.
What are the distinguishing characteristics of a scalar subquery?
It returns a set of rows and multiple columns.
It returns exactly one value: a single row with a single column.
It references columns in the parent query, making its result dependent on the parent.
It can only be used in the FROM clause.
When a subquery is used in the SELECT list (projection), what is a mandatory requirement for that subquery?
It must return multiple rows.
It must be correlated.
It must be scalar.
It must reference a table from the FROM clause.
What is a general rule regarding the use of NOT IN with subqueries, particularly concerning NULL values?
NOT IN is always reliable, even with NULL values.
NOT IN should be avoided if there's a chance the subquery result set might include a NULL, as it can lead to problems.
NOT IN implicitly converts NULL to zero, making it safe.
NOT IN can only be used with scalar subqueries, thus avoiding NULL issues.
Which of the following comparison operators are valid for multiple-row subqueries?
=, >, >=.
IN, NOT IN, ANY, ALL.
<, <=, <>.
IS NULL, IS NOT NULL.
What is a characteristic execution method of a correlated subquery?
It is evaluated once before the parent query runs.
It only returns a single value, independent of the parent query.
It references columns in the parent query and is potentially executed repeatedly for each row processed by the parent.
It is an efficient construct that should always be preferred.
What does the UNION set operator do?
Returns all rows from two queries, including duplicates, without sorting.
Returns only the rows common to both queries.
Returns the combined rows from two queries, sorting them and removing duplicates.
Returns rows from the first
What is the purpose of the GROUP BY clause in SQL?
To join multiple tables together.
To filter records based on specific conditions.
To aggregate data across multiple rows based on one or more columns.
To sort the results of a query in ascending or descending order.
Which SQL function is used to retrieve the highest value from a set of values?
AVG.
MAX.
MIN.
SUM.
What does the DISTINCT keyword do in a SQL query?
It combines multiple rows into a single row.
It sorts the result set in a specific order.
It removes duplicate rows from the result set.
It limits the number of rows returned.
What is the purpose of the GROUP BY clause in a SQL query?
To filter records based on specific conditions.
To aggregate data across multiple rows based on one or more columns.
To sort the results in descending order.
To join multiple tables together.
Which SQL statement is used to remove a table from a database?
DELETE TABLE table_name;
DROP TABLE table_name;
REMOVE TABLE table_name;
ALTER TABLE table_name DROP;
What does the COALESCE function do in SQL?
It concatenates two or more strings together.
It returns the first non-null value in a list of arguments.
It counts the number of non-null values in a column.
It converts a string to uppercase.
