D426 Data Management Foundations - New Pre-Assessment

Which statement correctly adds the values 'Strawberry' and 'Red' for the type and color columns, respectively, in the 'fruits' table?

A. INSERT INTO fruits (type, color) VALUES ('Strawberry', 'Red');

B. INSERT INTO fruits (type, color) VALUES ('Strawberry', 'Red', 'Seedless');

C. INSERT INTO fruits (type, color) VALUES ('Red', 'Strawberry');

D. INSERT INTO fruits (type, 'Strawberry'), (color, 'Red');

A. INSERT INTO fruits (type, color) VALUES ('Strawberry', 'Red');

Which DROP statement deletes a column from an existing table?

A. DROP COLUMN column_name FROM table_name;

B. DROP table_name COLUMN column_name;

C. ALTER TABLE DROP COLUMN column_name FROM table_name;

D. ALTER TABLE table_name DROP COLUMN column_name;

D. ALTER TABLE table_name DROP COLUMN column_name;

What is an example of an expression in SQL?

A. City = 'Beijing'

B. 134

C. INSERT

D. 'Welcome'

A. City = 'Beijing'

What does the % operator return when using numeric values?

A. The quotient when dividing one value by another

B. The quotient when dividing one value by another

C. The additive inverse (i.e., opposite) of a value

D. The remainder when dividing one value by another

D. The remainder when dividing one value by another

You are inserting a new record into a table. The primary key column is using an auto-incrementing integer. What value should you specify for the primary key column in your INSERT statement?

A. Omit the primary key column from the INSERT statement

B. Include the primary key column and specify a value of 0

C. Include the primary key column and specify a negative value

D. Omit any column with an integer data type

A. Omit the primary key column from the INSERT statement

Which language element includes a term with a special meaning for the language processor?

A. Literal

B. Keyword

C. Identifier

D. Expression

B. Keyword

Which statement uses valid syntax for the DELETE statement in SQL?

A. DELETE FROM table_name WHERE condition;

B. DELETE table_name WHERE condition;

C. DELETE * FROM table_name WHERE condition;

D. DELETE FROM table_name;

A. DELETE FROM table_name WHERE condition;

Which SQL command uses the correct syntax to add a new employee John Doe to the Employee table?

A. INSERT INTO Employee (Name) Values ( 'John Doe' );INSERT Values INTO Employee ( 'John Doe' );

B. INSERT INTO Employee (Name) Values ( 'John Doe' );

C. INSERT Values INTO Employee ( John Doe );

D. INSERT INTO Employee (Name) Values ( John Doe );

B. INSERT INTO Employee (Name) Values ( 'John Doe' );

Which SQL data type should be used to store the price of a grocery item, ensuring accuracy to the cent?

A. INT

B. FLOAT

C. DECIMAL(6,2)

D. VARCHAR(2)

C. DECIMAL(6,2)

Which clause should be used when creating a table to ensure the 'NumEyes' column automatically receives the value 2 if no value is provided during row insertion?

A. NumEyes INT = 2

B. NumEyes INT DEFAULT 2

C. NumEyes INT (2)

D. NumEyes INT IS 2

B. NumEyes INT DEFAULT 2

Which data type should be used to store the coordinates of a point on a map?

A. Binary

B. Decimal

C. Document

D. Spatial

D. Spacial

Which property specifies an expression on one or more columns of a table?

A. CHECK

B. RESTRICT

C. CASCADE

D. UNIQUE

A. CHECK

What does data represent in data management?

A. Numeric, textual, visual, or audio information

B. Unprocessed, arbitrary information

C. Theoretical models and abstractions

D. Human interpretations and opinions

A. Numeric, textual, visual, or audio information

What is database system architecture?

A. It is the set of queries sent to the system that must be either fully completed or rejected.

B. It specifically refers to the storage layer of the system.

C. It specifically refers to the query processor of the system.

D. It describes the components of the system and how those components interact.

D. It describes the components of the system and how those components interact.

Which type of theory is the basis for relational data structure?

A. Set theory

B. Graph theory

C. Complex design theory

D. Entity management theory

A. Set theory

What is a difference between relational databases and NoSQL databases?

A. Unlike relational databases, NoSQL databases are optimized for big data.

B. Relational databases are open-source software, whereas NoSQL databases are commercial products.

C. Unlike relational databases, NoSQL databases need a predefined schema to store data.

D. Relational databases are used to store sets, whereas NoSQL databases are used to store tuples.

A. Unlike relational databases, NoSQL databases are optimized for big data.

Which SQL function returns the current date in YYYY-MM-DD format?

A. DATE()

B. CURDATE()

C. CURRENT()

D. PRESENT()

B. CURDATE()

Which SQL keyword is used to combine rows based on the same column values?

A. GROUPED

B. GROUP BY

C. HAVING

D. ORDER BY

B. GROUP BY

What is the function of the − (unary) operator?

A. To invert the sign of a numeric value

B. To subtract one numeric value from another

C. To match exactly one character in pattern matching

D. To compare two values for inequality

A. To invert the sign of a numeric value

Which SQL operator is used to combine two conditions in a query, where both conditions must be true for a row to be included in the result?

A. SYNC

B. AND

C. MERGE

D. JOIN

B. AND

Which SQL keyword is used to find string values that match a specified pattern?

A. LIKE

B. EQUAL

C. SIMILAR

D. BETWEEN

A. LIKE

Which SQL function deletes leading and trailing white space from text values?

A. STRIP

B. TRIM

C. REMOVE

D. REPLACE

B. TRIM

Which SQL function is used to calculate the total of numeric values in a specified column of a table?

A. SUM

B. AGGREGATE

C. COUNT

D. AVG

A. SUM

Which expression can be used to create a temporary name for a table?

A. SET

B. UPDATE

C. AS

D. CREATE

C. AS

Which wildcard character is used to represent zero or more characters when searching for a specified pattern using a LIKE operator?

A. %

B. _

C. *

D. ^

A. %

What is combined by an aggregate function in an SQL query?

A. An aggregate function combines the results of multiple simple functions.

B. An aggregate function combines multiple tables in its result.

C. An aggregate function combines values from a set of rows in its calculations.

D. An aggregate function combines equal values into a group and provides outcomes from each group.

C. An aggregate function combines values from a set of rows in its calculations.

A table named CustomerOrders in a database contains columns OrderID, CustomerID, OrderDate, and TotalAmount.

Which aggregate function should be used to determine the total number of orders placed in the current month?

A. SUM(OrderID)

B. COMPUTE(OrderID)

C. COUNT(OrderID)

D. TALLY(OrderID)

C. COUNT(OrderID)

Which condition must be true for two columns to be compared in a join?

A. One column must be a foreign key and the other must be a primary key.

B. The two columns must have types that can be compared with each other.

C. The two columns must have the same name.

D. One column must be a foreign key or primary key and the other must be a primary key.

B. The two columns must have types that can be compared with each other.

Which type of join includes a result for every row in both the left and right tables?

A. INNER JOIN

B. CROSS JOIN

C. SELF JOIN

D. FULL JOIN

D. FULL JOIN

Which type of join will specifically include a result for every row in the second table of the join?

A. INNER JOIN

B. LEFT JOIN

C. RIGHT JOIN

D. FULL JOIN

C. RIGHT JOIN

Which type of join is demonstrated by the following query?

SELECT *

FROM Make, Model

WHERE Make.ModelID != Model.ID

A. non-equijoin

B. self join

C. natural join

D. cross join

A. non-equijoin

Which SQL query returns a list of all flights along with their corresponding airport names, including flights that do not have a matching airport?

A. SELECT FlightNo, Airport.AirportID, AirportName

FROM Flight

LEFT JOIN Airport

ON Flight.AirportID = Airport.AirportID;

B. SELECT FlightNo, Airport.AirportID, AirportName

FROM Flight

JOIN Airport

ON Flight.AirportID = Airport.AirportID;

C. SELECT FlightNo, Airport.AirportID, AirportName

FROM Flight

RIGHT JOIN Airport

ON Flight.AirportID = Airport.AirportID;

D. SELECT FlightNo, Airport.AirportID, AirportName

FROM Flight

FULL JOIN Airport

ON Flight.AirportID = Airport.AirportID;

A. SELECT FlightNo, Airport.AirportID, AirportName

FROM Flight

LEFT JOIN Airport

ON Flight.AirportID = Airport.AirportID;

What happens when a column in the SELECT clause is neither included in GROUP BY nor used in an aggregate function, such as in the following query?

SELECT Author, Title, COUNT(*)

FROM Book

GROUP BY Author;

A. Only the first book title for each author will appear in query results.

B. The query will return a list of every author with a random title from their books.

C. The query will result in an error in standard SQL databases.

D. Every book will be listed with its author and a count of one in query results.

C. The query will result in an error in standard SQL databases.

How do the WHERE and HAVING clauses differ in terms of their use for filtering in an SQL query involving a join?

A. WHERE and HAVING are interchangeable when using a join.

B. The WHERE clause filters records before aggregation, and the HAVING clause filters records after aggregation.

C. HAVING is used with OUTER JOIN, and WHERE is used with INNER JOIN.

D. The HAVING clause filters records before aggregation, and the WHERE clause filters records after aggregation.

B. The WHERE clause filters records before aggregation, and the HAVING clause filters records after aggregation.

Which relationship exists between a supertype and its subtype entities?

A. Binary relationship

B. Has-A relationship

C. Unary relationship

D. Is-A relationship

D. Is-A relationship

What does cardinality define in an entity-relationship model?

A. The maximum number of instances one entity can relate to another

B. The minimum number of relationships an entity must have

C. The uniqueness of each individual record within a database table

D. The total number of records stored in a database system

A. The maximum number of instances one entity can relate to another

What does modality define in a relationship?

A. The minimum number of times an entity participates in a relationship

B. The maximum number of times an entity can participate in a relationship

C. The type of relationship between entities

D. The number of roles an entity can have in a relationship

A. The minimum number of times an entity participates in a relationship

What does a short bar across the relationship line in an entity-relationship (ER) diagram indicate?

A. That the entity's participation in the relationship is optional

B. That the relationship between the entities is recursive

C. That the entity represents a weak entity in the relationship

D. That the entity must participate in the relationship

D. That the entity must participate in the relationship

Which scenario demonstrates referential integrity in a database?

A. A customer cannot place an order unless they exist in the customer table.

B. A product has a unique identifier assigned to it.

C. A company has a list of employees stored in a single table.

D. A user is allowed to enter duplicate values in a column.

A. A customer cannot place an order unless they exist in the customer table.

Which strategy can be used to find primary keys when investigating data relationships?

A. Trend analysis

B. Unique identifier analysis

C. Normalization

D. Visualization

B. Unique identifier analysis

A telecom company tracks customer information, including name and contact details. Individual customers provide a home address, while corporate clients provide a business address. Additionally, both individual customers and corporate clients can subscribe to a premium plan that offers extra services for an additional cost.

Which entities have the HomeAddress attribute in this case?

A. IndividualCustomer and PremiumIndividualCustomer

B. PremiumIndividualCustomer and PremiumCorporateCustomer

C. IndividualCustomer and CorporateCustomer

D. CorporateCustomer and PremiumCorporateCustomer

A. IndividualCustomer and PremiumIndividualCustomer

What should be used to store the intersection data resulting from many-to-many relationships between two entities?

A. Junction table

B. Partition

C. Data dictionary

D. Cardinality

A. Junction table

Which scenario exemplifies a ternary relationship?

A. Suppliers providing products to customers

B. Students enrolled in multiple college courses

C. Authors writing books

D. Employees supervising other employees

A. Suppliers providing products to customers

Which relationship is an example of a unary relationship?

A. A doctor treating patients

B. Employees managing other employees

C. A product belonging to multiple categories

D. A customer placing multiple orders

B. Employees managing other employees

When would a database designer need to create an artificial key?

A. When there is no single column key available in the table

B. When the primary key is identical to the supertype primary key

C. When no simple or composite primary key exists in the table

D. When there is an "Is-A" relationship in the table

C. When no simple or composite primary key exists in the table

What is the role of a composite primary key in establishing relationships between tables?

A. To create relationships between tables by referencing primary keys

B. To identify the rows in a table using a combination of multiple columns

C. To enforce referential integrity across tables

D. To prevent data redundancy in normalized tables

B. To identify the rows in a table using a combination of multiple columns

Which constraint propagates any modifications made to primary keys to foreign keys?

A. CASCADE

B. SET DEFAULT

C. SET NULL

D. RESTRICT

A. CASCADE

Which clause, when used as part of a foreign key definition, prevents rows referenced by a foreign key from being deleted?

A. ON DELETE RESTRICT

B. ON DELETE SET NULL

C. ON DELETE CASCADE

D. ON DELETE SET DEFAULT

A. ON DELETE RESTRICT

Which type of key can be simple or composite while also being correctly classified as both unique and minimal?

A. Encryption key

B. Candidate key

C. Foreign key

D. Master key

B. Candidate key

How is an attribute defined in an Entity-Relationship (ER) model?

A. A descriptive characteristic of an entity

B. An entire table within a database schema

C. A single record of data stored in a database

D. A specific value assigned to a field in a table

A. A descriptive characteristic of an entity

What is the role of a data dictionary in entity-relationship models?

A. To document normalization rules and key constraints

B. To document entities, attributes, and their relationships

C. To store SQL queries for database transactions

D. To define indexing strategies for optimized query performance

B. To document entities, attributes, and their relationships

Which scenario represents a unary relationship in an Entity-Relationship (ER) model?

A. An employee supervises other employees within an organization.

B. A student enrolls in multiple courses.

C. A supplier provides products to a retailer.

D. A patient is prescribed multiple medications by different doctors.

A. An employee supervises other employees within an organization.

How is a one-to-one relationship represented between two entities in an entity-relationship diagram?

A. A crow's foot on both ends of the relationship line connecting the two entities

B. A less-than sign and a greater-than sign on each end of the relationship line connecting the two entities

C. A bar on both ends of the relationship line connecting the two entities

D. A bar on one end and a crow's foot on the other end of the relationship line connecting the two entities

C. A bar on both ends of the relationship line connecting the two entities

Which term is used interchangeably with secondary index in database indexing?

A. Sparse index

B. Nonclustering index

C. Sorted index

D. Binary index

B. Nonclustering index

Which type of index contains an entry for each record in a database table?

A. Row index

B. Column index

C. Dense index

D. Sparse index

C. Dense index

What is a single-level index in a database?

A. A file that stores the entire table, including the values of all columns, in an unsorted order

B. A file that is insusceptible to updates, inserts, and deletes to the table

C. A file that includes column values, as well as pointers to rows that contain the column value

D. A file that dynamically restructures itself by splitting and merging as data changes

C. A file that includes column values, as well as pointers to rows that contain the column value

How will the database optimize query execution if the filter factor is high?

A. It will scan the index sequentially.

B. It will look for an indexed column in the WHERE clause.

C. It will create a new table index.

D. It will perform a table scan.

D. It will perform a table scan.

What is the purpose of indexes in a database?

A. To reduce the required storage space

B. To quickly locate the data

C. To reduce the need for data backups

D. To speed up insert, delete, and update operations

B. To quickly locate the data