D426 V2 DATA MANAGEMENT
FOUNDATIONS - SPR2024 EXAM
QUESTIONS WITH CORRECT
ANSWERS
,INSERT INTO - ANSWERThis clause names the table and columns where data is to be
added. The keyword INTO is optional.
INSERT [INTO] TableName (Column1, Column2, ...)
VALUES (Value1, Value2, ...);
AUTO_INCREMENT - ANSWERA keyword that defines an auto-increment column. This
keyword follows the column's data type in a CREATE TABLE statement.
Fully NULL - ANSWERThis foreign key is a simple or composite foreign key in which all
columns are NULL
Function / Argument - ANSWERA function operates on an expression enclosed in
parentheses, called an argument, and returns a value.
Usually, the argument is a simple expression, such as a column name or fixed value.
Some functions have several arguments, separated by commas, and a few have no
arguments at all.
Each function operates on, and evaluates to, specific data types.
((Course instructor highlighted function to know)):
ABS(n) Returns the absolute value of n
Ex: SELECT ABS(-5);
returns 5
GROUP BY clause - ANSWERThis clause consists of the ____________ _____
keyword and one or more columns.
Each simple or composite value of the column(s) becomes a group. The query
computes the aggregate function separately, and returns one row, for each group.
Aggregate functions are commonly used by this clause.
Aggregate Function - ANSWERThis function processes values from a set of rows and
returns a summary value.
((Course instructor highlighted functions to know)):
Common aggregate functions are:
~ COUNT() counts the number of rows in the set
~MIN() finds the minimum value in the set
~MAX() finds the maximum value in the set
~ SUM() sums all the values in the set
~ AVG() computes the arithmetic mean of all the values in the set
,These functions appear in a SELECT clause and process all rows that satisfy the
WHERE clause condition. If a SELECT statement has no WHERE clause, the
aggregate function processes all rows.
These functions are commonly used with the GROUP BY clause.
AS keyword - ANSWERTo simplify queries or result tables, a column name can be
replaced with an alias. The alias follows the column name, separated by an optional AS
keyword.
view table / view query - ANSWERA view table is a table name associated with a
SELECT statement, called the view query.
Table design is optimized for a variety of reasons, such as minimal storage, fast query
execution, and support for relational and business rules. Occasionally, the design is not
ideal for database users and programmers.
View tables solve this problem. Views restructure table columns and data types without
changes to the underlying database design.
CREATE VIEW statement - ANSWERThis statement creates a view table and specifies
the view name, query, and optionally, column names. If column names are not
specified, column names are the same as in the view query result table.
Attribute minimum - ANSWERThe least number of attribute values that can describe
each entity instance.
Is usually specified as zero (optional) or one (required).
In ER diagrams, attribute maximum and minimum follow the attribute name. The
minimum appears in parentheses.
unique attribute - ANSWEREach value of a _____ _______ describes at most one
entity instance. Is not the same as a singular attribute.
In ER diagrams, 1 indicates a unique attribute and M indicates a non-unique attribute.
The 1 or M appears before the attribute maximum and minimum.
functional dependence - ANSWERColumn A depends on column B means each B
value is related to at most one A value. Columns A and B may be simple or composite.
'A depends on B' is denoted B → A.
Dependence of one column on another is called functional dependence. Functional
dependence reflects business rules.
, depends on - ANSWERColumn A depends on column B when each B value is related
to at most one A value. A and B may be simple or composite columns.
heap table - ANSWERNo order is imposed on rows.
The database maintains a list of blocks assigned to the table, along with the address of
the first available space for inserts. If all blocks are full, the database allocates a new
block and inserts rows in the new block.
Heap tables optimize insert operations. Heap tables are particularly fast for bulk load of
many rows, since rows are stored in load order. Heap tables are not optimal for queries
that read rows in a specific order, such as a range of primary key values, since rows are
scattered randomly across storage media.
Row-oriented storage performs better than column-oriented storage for most
transactional databases. Consequently, relational databases commonly use row-
oriented storage.
hash key - ANSWERA column or group of columns, usually the primary key.
logical index - ANSWERA single- or multi-level index in which pointers to table blocks
are replaced with primary key values.
Logical indexes are always secondary indexes and require a separate primary index on
the same table.
shard - ANSWERLike a partition, a ______ is a subset of table data, usually a subset of
rows than columns. Unlike partitions, which are stored on different storage devices of a
single computer, ______ are stored on different computers of a distributed database.
Data - ANSWERNumeric, textual, visual, or audio information that describes real-world
systems
Data varies in which ways (database basics section specific) - ANSWERscope, format,
access
Scope - ANSWERThe amount of data produced and collected can vary. Ex: A small
business might track an inventory of a few thousand items, but a large commerce
website might track billions of items.
Format - ANSWERData may be produced as numbers, text, image, audio, or video. Ex:
A phone's proximity sensor generates raw numbers, and a satellite captures images.
Access - ANSWERSome data sources are private while others are made publicly
available. Ex: A retail company may use private customer data to discover purchasing
behavior patterns, but a government may be required by law to share certain data sets.
FOUNDATIONS - SPR2024 EXAM
QUESTIONS WITH CORRECT
ANSWERS
,INSERT INTO - ANSWERThis clause names the table and columns where data is to be
added. The keyword INTO is optional.
INSERT [INTO] TableName (Column1, Column2, ...)
VALUES (Value1, Value2, ...);
AUTO_INCREMENT - ANSWERA keyword that defines an auto-increment column. This
keyword follows the column's data type in a CREATE TABLE statement.
Fully NULL - ANSWERThis foreign key is a simple or composite foreign key in which all
columns are NULL
Function / Argument - ANSWERA function operates on an expression enclosed in
parentheses, called an argument, and returns a value.
Usually, the argument is a simple expression, such as a column name or fixed value.
Some functions have several arguments, separated by commas, and a few have no
arguments at all.
Each function operates on, and evaluates to, specific data types.
((Course instructor highlighted function to know)):
ABS(n) Returns the absolute value of n
Ex: SELECT ABS(-5);
returns 5
GROUP BY clause - ANSWERThis clause consists of the ____________ _____
keyword and one or more columns.
Each simple or composite value of the column(s) becomes a group. The query
computes the aggregate function separately, and returns one row, for each group.
Aggregate functions are commonly used by this clause.
Aggregate Function - ANSWERThis function processes values from a set of rows and
returns a summary value.
((Course instructor highlighted functions to know)):
Common aggregate functions are:
~ COUNT() counts the number of rows in the set
~MIN() finds the minimum value in the set
~MAX() finds the maximum value in the set
~ SUM() sums all the values in the set
~ AVG() computes the arithmetic mean of all the values in the set
,These functions appear in a SELECT clause and process all rows that satisfy the
WHERE clause condition. If a SELECT statement has no WHERE clause, the
aggregate function processes all rows.
These functions are commonly used with the GROUP BY clause.
AS keyword - ANSWERTo simplify queries or result tables, a column name can be
replaced with an alias. The alias follows the column name, separated by an optional AS
keyword.
view table / view query - ANSWERA view table is a table name associated with a
SELECT statement, called the view query.
Table design is optimized for a variety of reasons, such as minimal storage, fast query
execution, and support for relational and business rules. Occasionally, the design is not
ideal for database users and programmers.
View tables solve this problem. Views restructure table columns and data types without
changes to the underlying database design.
CREATE VIEW statement - ANSWERThis statement creates a view table and specifies
the view name, query, and optionally, column names. If column names are not
specified, column names are the same as in the view query result table.
Attribute minimum - ANSWERThe least number of attribute values that can describe
each entity instance.
Is usually specified as zero (optional) or one (required).
In ER diagrams, attribute maximum and minimum follow the attribute name. The
minimum appears in parentheses.
unique attribute - ANSWEREach value of a _____ _______ describes at most one
entity instance. Is not the same as a singular attribute.
In ER diagrams, 1 indicates a unique attribute and M indicates a non-unique attribute.
The 1 or M appears before the attribute maximum and minimum.
functional dependence - ANSWERColumn A depends on column B means each B
value is related to at most one A value. Columns A and B may be simple or composite.
'A depends on B' is denoted B → A.
Dependence of one column on another is called functional dependence. Functional
dependence reflects business rules.
, depends on - ANSWERColumn A depends on column B when each B value is related
to at most one A value. A and B may be simple or composite columns.
heap table - ANSWERNo order is imposed on rows.
The database maintains a list of blocks assigned to the table, along with the address of
the first available space for inserts. If all blocks are full, the database allocates a new
block and inserts rows in the new block.
Heap tables optimize insert operations. Heap tables are particularly fast for bulk load of
many rows, since rows are stored in load order. Heap tables are not optimal for queries
that read rows in a specific order, such as a range of primary key values, since rows are
scattered randomly across storage media.
Row-oriented storage performs better than column-oriented storage for most
transactional databases. Consequently, relational databases commonly use row-
oriented storage.
hash key - ANSWERA column or group of columns, usually the primary key.
logical index - ANSWERA single- or multi-level index in which pointers to table blocks
are replaced with primary key values.
Logical indexes are always secondary indexes and require a separate primary index on
the same table.
shard - ANSWERLike a partition, a ______ is a subset of table data, usually a subset of
rows than columns. Unlike partitions, which are stored on different storage devices of a
single computer, ______ are stored on different computers of a distributed database.
Data - ANSWERNumeric, textual, visual, or audio information that describes real-world
systems
Data varies in which ways (database basics section specific) - ANSWERscope, format,
access
Scope - ANSWERThe amount of data produced and collected can vary. Ex: A small
business might track an inventory of a few thousand items, but a large commerce
website might track billions of items.
Format - ANSWERData may be produced as numbers, text, image, audio, or video. Ex:
A phone's proximity sensor generates raw numbers, and a satellite captures images.
Access - ANSWERSome data sources are private while others are made publicly
available. Ex: A retail company may use private customer data to discover purchasing
behavior patterns, but a government may be required by law to share certain data sets.
