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.
