DATA 630: Applied Database Management Week 1-5 Quizes

What are the results of the following query? SELECT TRUE OR (TRUE AND FALSE) 1 0 NULL EMPTY

1

What is the result of the SQL query SELECT ROUND(123.456, -1) ? 123.0 123 120 100

120

What is the result of the SQL query SELECT ROUND(123.456, 2) ? 123.45 123.46 123.456 123.00

123.46

Primary key: Student ID and Course StudentID Course Professor 101 Math Dr. Smith 102 History Dr. Johnson 101 Science Dr. Martin 103 Math Dr. Smith 1NF 2NF 3NF None of the above

1NF

Given the following SQL query: SELECT CASE WHEN x > 5 THEN 'A' WHEN x > 3 THEN 'B' WHEN x > 1 THEN 'C' ELSE 'D' END AS Result FROM SomeTable; If x is 4, how many conditions are evaluated? 1 2 3 4

2

Which lowest normal form requires that a table has no partial dependencies? 1NF (First Normal Form) 2NF (Second Normal Form) 3NF (Third Normal Form) BCNF (Boyce-Codd Normal Form)

2NF (Second Normal Form)

Which normal form is primarily concerned with removing transitive dependencies? 1NF (First Normal Form) 2NF (Second Normal Form) 3NF (Third Normal Form) BCNF (Boyce-Codd Normal Form

3NF (Third Normal Form)

Which of the following best describes the comparison between 2NF and 3NF? 2NF is stricter than 3NF 3NF is stricter than 2NF 2NF and 3NF have the same requirements 2NF deals with data security, while 3NF deals with data integrity

3NF is stricter than 2NF

Which of the following best defines an attribute in database terms?

A characteristic or property of an entity

What is a partial dependency in the context of database normalization? A dependency between two or more attributes A dependency between part of a composite key and a non-key attribute A dependency between non-key attributes None of the above

A dependency between part of a composite key and a non-key attribute

What is a transitive dependency in the context of database normalization? A dependency between two non-key attributes A dependency between a non-key attribute and part of a composite key A dependency where a non-key attribute depends on another non-key attribute through a key attribute None of the above

A dependency where a non-key attribute depends on another non-key attribute through a key attribute

What is a record in storage-related terms?

A group of related fields

Which of these is an accurate description of a record in a database? A collection of related databases A specific characteristic of an entity A group of related fields representing an entity A diagram showing relationships between entities

A group of related fields representing an entity

What is a candidate key in a relational database? A key that links two tables together A key that could be chosen as the primary key A key that contains duplicate values A composite key consisting of multiple columns

A key that could be chosen as the primary key

What is Data Normalization? A process to optimize database performance A process to ensure data integrity and reduce redundancy A method to encrypt data A technique to backup data

A process to ensure data integrity and reduce redundancy

Which of the following best describes a ternary relationship?

A relationship among three entities

In relational database terms, which of the following best describes a 'relation'? A row in a table A set of tuples A database key A database query

A set of tuples

In data-related terms, what is an entity?

A thing in the real world with an independent existence

Which of the following best defines a primary key in a relational database? An optional key that can have null values A key that can contain duplicate values A unique identifier for a record within a table A key that links two tables together

A unique identifier for a record within a table

What is the primary feature of a database management system? Efficient data handling. It stores data in an organized manner. Data security and access control. It allows for easy access and manipulation of data. Minimize Data redundancy. It reduces data duplication. All of the above

ALL

Henry works as a data analyst for a human resources firm. He was given a checklist of criteria for recruiting persons for the position of senior production manager, including having five or more years of experience, being under forty-five years old, and residing in the United States. The candidature should be canceled if any of the prerequisites are not met. Which of the following methods will he employ to complete this task? AND OR NOT NOR

AND

What can be seen as a disadvantage of data redundancy? Wastes storage space Leads to data inconsistencies Both A and B Neither A nor B

BOTH A & B

Which SQL function is used to concatenate two or more strings together? JOIN() CONCAT() MERGE() COMBINE()

CONCAT()

Historically, what has driven many information technology developments?

Data needs

Which of these can contain historical data logs with past and current records, tables that are updated in real time as the source systems are updated, or snapshots of data to preserve it as it existed at a past moment in time? Dimensions Records Data warehouses Grains

Data warehouses

What best describes the essence of data modeling? Diagramming the business environment Programming the database Storing data in a physical location Encrypting sensitive data

Diagramming the business environment

Which of the following best describes the First Normal Form (1NF)? Elimination of duplicate columns Elimination of partial dependencies Ensuring that each column contains atomic, indivisible values Elimination of transitive dependencies

Ensuring that each column contains atomic, indivisible values

Which of these is NOT a storage-related term? Entity Field Record File

Entity

True/ False: Candidate keys can have null values in a relational database

False

True/False: The select operator and project operator in relational algebra serve the same purpose and can be used interchangeably.

False

When converting an entity-relationship diagram into relational tables, what do the relationships typically become? Foreign Keys Columns Keys Rows

Foreign Keys

Which key serves as a connection between two tables in a relational database? Primary key Candidate key Foreign key Composite key

Foreign key

Which command would you use to test the missing of data in a column? IS EMPTY IS NULL NOT EXIST IS MISS

IS NULL

What does the project operator do in relational algebra? It filters records based on conditions It merges attributes based on a common attribute It retrieves specific columns from a relation It joins tables based on a common key

It retrieves specific columns from a relation

In MySQL, which SQL clause is used to limit the number of rows returned by a query? TOP LIMIT ROW LIMIT FETCH

LIMIT

What does the crow's feet mean in an entity-relationship diagram (ERD)? One side of a one-to-many relationship Many side of a many-to-one relationship Many side of a one-to-many relationship One side of a many-to-one relationship

Many side of a one-to-many relationship

Which relationship allows both entities to have multiple instances related to multiple instances of the other entity? One-to-one One-to-many Many-to-many Unary

Many-to-many

What is the primary purpose of the join operator in a relational database? Deleting records Merging attributes based on a common attribute Filtering records Calculating sums and averages

Merging attributes based on a common attribute

Consider the following table. What normal form is it in? StudentID Subjects 1 Math, Science 2 English, Math 3 History 1NF 2NF 3NF None of the above

None of the above

Hannah works for the Holiday Hotel Group as a database administrator. She wants to clean up the database because there are a lot of records that are duplicated every time the same people visit the hotel, eating up unneeded space and slowing down the database server. Which of the following processes should she follow to accomplish the task? Modification Normalization Maintenance Optimization

Normalization

Consider the following SQL query that uses a CASE WHEN statement: SELECT CASE WHEN Salary > 60000 THEN 'High' WHEN Salary > 40000 THEN 'Medium' WHEN Salary > 20000 THEN 'Low' END AS SalaryBracket FROM Employees; If an employee has a salary of $10,000, what will be the value in the SalaryBracket column for that employee? High Medium Low NULL

Null

In a one-to-many relationship, the "one" side is represented by which type of key? Primary key Foreign key Candidate key Secondary key

Primary key

Given a table named Products with columns ProductID, ProductName, and UnitPrice, which of the following SQL queries can categorize products with a UnitPrice greater than 100 as 'Expensive' and others as 'Affordable'? SELECT ProductName, CASE WHEN UnitPrice > 100 THEN 'Expensive' ELSE 'Affordable' END FROM Products SELECT ProductName, WHEN UnitPrice > 100 THEN 'Expensive' ELSE 'Affordable' END FROM Products SELECT ProductName, IF UnitPrice > 100 THEN 'Expensive' ELSE 'Affordable' END FROM Products None of the above

ProductName, CASE WHEN UnitPrice > 100 THEN 'Expensive' ELSE 'Affordable' END FROM Products

The "AS" clause is used in SQL for Select operation Rename operation Sort operation None of above

Rename operation

The select operator in relational algebra is used for: Merging tables Deleting records Retrieving specific rows based on conditions Retrieving specific columns

Retrieving specific rows based on conditions

Reorder the following SQL clauses in the correct sequence. SELECT FROM ORDER BY LIMIT

SELECT FROM ORDER BY LIMIT

Reorder the following SQL clauses in the correct sequence. You can use drag-and-drop with your mouse. SELECT FROM WHERE ORDER BY LIMIT

SELECT FROM WHERE ORDER BY LIMIT

Which SQL statement retrieves all records from a table named "Employees" and sorts them in descending order by the "HireDate" column? SELECT * FROM Employees ORDER BY HireDate DESC SELECT * FROM Employees SORT BY HireDate DESC SELECT * FROM Employees SORT HireDate DESC SELECT * FROM Employees ORDER HireDate DESC

SELECT * FROM Employees ORDER BY HireDate DESC

Suppose you have a table named Products with columns ProductID, ProductName, and Description. You want to retrieve products whose descriptions contain the word "red". Which of the following SQL queries achieves this? SELECT * FROM Products WHERE Description LIKE '%red%' SELECT * FROM Products WHERE Description LIKE 'red' SELECT * FROM Products WHERE Description LIKE '%red' SELECT * FROM Products WHERE Description LIKE 'red%'

SELECT * FROM Products WHERE Description LIKE '%red%'

In a retail company's database, you have a table named Sales with columns OrderID, ProductID, Quantity, and Department. Each product sold belongs to one or more departments. You want to select the records of sales where the products belong to either the 'Electronics' department or the 'Clothing' department. Which SQL statement will accomplish this task? Note multiple answers may apply. SELECT * FROM Sales WHERE Department BETWEEN 'Electronics' AND 'Clothing' SELECT * FROM Sales WHERE Department IN ('Electronics', 'Clothing') SELECT * FROM Sales WHERE Department = 'Electronics' AND Department = 'Clothing' SELECT * FROM Sales WHERE Department = 'Electronics' OR Department = 'Clothing'

SELECT * FROM Sales WHERE Department IN ('Electronics', 'Clothing') SELECT * FROM Sales WHERE Department = 'Electronics' OR Department = 'Clothing'

Students ID [PK] Name Age Grade 1 Alice 20 B 2 Bob 21 A 3 Carol 22 C Which SQL query returns students with a grade of 'A' and an age is less than 18? SELECT * FROM Students WHERE Grade = 'A' AND Age < 18 SELECT * FROM Students WHERE Grade = 'A', Age < 18 SELECT * FROM Students WHERE Grade = 'A' OR Age < 18 None of above

SELECT * FROM Students WHERE Grade = 'A' AND Age < 18

Consider a table named product in the database of a farmers' market. The product table has columns of product_size. You want to retrieve products where the product_size is either NULL or an empty string. Which of the following SQL queries accomplishes this? SELECT * FROM product WHERE product_size IS EMPTY OR product_size = NULL SELECT * FROM product WHERE product_size IS NULL OR product_size = '' SELECT * FROM product WHERE product_size IS EMPTY OR TRIM(product_size) = '' SELECT * FROM product WHERE product_size IS NULL OR TRIM(product_size) = ''

SELECT * FROM product WHERE product_size IS NULL OR TRIM(product_size) = ''

Given a table named Orders with columns OrderID, OrderDate, and TotalAmount, which of the following SQL queries can retrieve orders placed between January 1, 2023, and December 31, 2023, inclusive on both sides? Note that multiple answers may apply. SELECT OrderID, OrderDate, TotalAmount FROM Orders WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31' SELECT OrderID, OrderDate, TotalAmount FROM Orders WHERE OrderDate >= '2023-01-01' AND OrderDate <= '2023-12-31' SELECT OrderID, OrderDate, TotalAmount FROM Orders WHERE OrderDate > '2023-01-01' AND OrderDate < '2023-12-31' SELECT OrderID, OrderDate, TotalAmount FROM Orders WHERE OrderDate IN ('2023-01-01', '2023-12-31')

SELECT OrderID, OrderDate, TotalAmount FROM Orders WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31' SELECT OrderID, OrderDate, TotalAmount FROM Orders WHERE OrderDate >= '2023-01-01' AND OrderDate <= '2023-12-31'

In a binary relationship, how many entities are involved?

TWO

What is the role of functional dependencies in database normalization? They determine how data is physically stored They identify relationships between attributes They ensure data encryption None of the above

They identify relationships between attributes

What is the primary goal of data normalization in a database? To increase query speed To reduce data storage space To ensure data integrity and reduce redundancy To enable faster backups

To ensure data integrity and reduce redundancy

Why is having a primary key essential for a table to be in 1NF? To increase query speed To ensure the uniqueness of records To enable faster backups All of the above

To ensure the uniqueness of records

Historically, why did the development of database management systems arise? To manage increasing volumes of business data To develop modern computers To facilitate online shopping To improve graphics in computer games

To manage increasing volumes of business data

Which of the following scenarios is most likely to require denormalization? To improve data integrity To reduce data redundancy To simplify database design To optimize query performance

To optimize query performance

What is data modeling primarily used for?

To represent relationships between business entities

True/ False: By default, in SQL, the ORDER BY clause will sort data in ascending order.

True

True/ False: In SQL, the TRIM() function can be used to remove both leading and trailing spaces from a string

True

True/False: In a one-to-many relationship, the many side typically contains a foreign key referencing the primary key on the one side.

True

How is a one-to-one relationship typically implemented in a relational database? Using a primary key on both sides Using a foreign key on either side referencing the other Using a candidate key Using a junction table

Using a foreign key on either side referencing the other

How is a many-to-many relationship implemented in a relational database? Using two foreign keys Using a junction table Using a primary key on both sides It is not possible to implement

Using a junction table

Which of the following conditions must be met in order to designate a column as a primary key? Note that this question may have multiple correct answers. Values of the column must be unique. Values of the column must be positive. Values of the column must not be alphabetical. Values of the column must not be NULL.

Values of the column must be unique. Values of the column must not be NULL.

What does the asterisk (*) represent in SELECT * FROM ... ? multiplication operator a wildcard character for pattern matching all columns in a table or result set a placeholder for user input

all columns in a table or result set

The CASE WHEN statement in SQL is used to: perform arithmetic calculations define a column or value based on given conditions filter rows using conditional statements sort data

define a column or value based on given conditions

The WHEN statements are evaluated in order, from top to bottom, and the first time a condition evaluates to TRUE, the corresponding THEN part of the statement is executed, and: all WHEN conditions are evaluated once. the else part will be executed. no other WHEN conditions are evaluated. an error will occur.

no other WHEN conditions are evaluated.