Preview Questions Data Modeling Concepts | Role Specific Skill

Question 1: How would you design a dimensional model to handle semi-structured data types?

Which action should you take?

Choose only one option

Use hybrid schema designs incorporating both structured and semi-structured data

Exclude semi-structured data types from the model

Opt for a flat file structure to ensure compatibility

Implement separate data warehouses for structured and semi-structured data types

Question 2: You notice that your database has redundant data. What normalization technique do you use first?

Which action should you take?

Choose only one option

Apply First Normal Form (1NF)

Apply Second Normal Form (2NF)

Apply Third Normal Form (3NF)

Use Boyce-Codd Normal Form (BCNF)

Question 3: You need to list all departments that have never had an employee with a salary above $100,000. What is your approach?

Which action should you take?

Choose only one option

Use a nested query with NOT EXISTS

Use a LEFT JOIN with a salary condition

Use a CTE with conditional aggregation

Use a subquery with MAX() and GROUP BY

Question 4: How do you find products that have never been ordered?

Which action should you take?

Choose only one option

Use a LEFT JOIN with IS NULL check

Use a nested query with NOT IN

Use an EXCEPT clause

Use a NOT EXISTS subquery

Question 5: How do you model the interaction between objects in a use case?

Which action should you take?

Choose only one option

Use a sequence diagram

Use a class diagram

Use a state diagram

Use a component diagram

Question 6: You need to find the second highest salary in the employee table. What is your approach?

Which action should you take?

Choose only one option

Use a subquery with ORDER BY and LIMIT

Use a window function with ROW_NUMBER()

Use a CTE with ROW_NUMBER()

Use a nested query with DISTINCT and ORDER BY