Week 4 Graded Quiz :Managing Big Data with MySQL (Excel to MySQL: Analytic Techniques for Business Specialization) Answers 2025

---

1. In which clauses of a MySQL query can subqueries be used?
✔ WHERE
✔ FROM
✔ SELECT
❌ ORDER BY
❌ JOIN

Summary: Subqueries can appear in WHERE (as filters), FROM (as derived tables), and SELECT (as scalar subqueries). They cannot directly appear in JOIN or ORDER BY clauses in standard MySQL.

---

2. How are subqueries indicated in MySQL syntax?
❌ Using the keyword SUBQUERY
❌ Using square brackets ‘[ ]’
✔ Using parentheses ‘( )’
❌ Using angle brackets ‘< >’
❌ Using curly braces ‘{ }’

Summary: Subqueries are always enclosed in parentheses ().

---

3. Query: SELECT * FROM students WHERE NOT (major = 'biology' AND dorm= ‘ledyard’) AND (age > 19 OR gender = ‘male’);
❌ Students who are (a) not biology majors, (b) do not live in the ledyard dorm, and (c) are not either > 19 in age or male
❌ Students who are (a) not biology majors who live in the ledyard dorm (but can be biology majors who live in a different dorm or can be students who have a different major while living in the ledyard dorm), and (b) are not either > 19 in age or male
✔ Students who are (a) not biology majors who live in the ledyard dorm (but can be biology majors who live in a different dorm or can be students who have a different major while living in the ledyard dorm), and (b) are either > 19 in age or male
❌ Students who are (a) not biology majors, (b) do not live in the ledyard dorm, and (c) are either > 19 in age or male

Summary: The NOT (major='biology' AND dorm='ledyard') logic allows students who fail at least one of the two conditions (major or dorm), combined with (age>19 OR gender='male').

---

4. Ratings group with largest number of unique dogs:
❌ Very low
❌ Low
✔ Moderate
❌ High
❌ Very High

Summary: Typically, moderate ratings cover the central part of the distribution and have the most entries.

---

5. Country of 1,000th user when sorted as described:
❌ GB, 1
✔ AU, 5
❌ AU, 13
❌ GB, 4
❌ US, 1

Summary: Sorting by logical conditions with IF or CASE on state for US vs non-US yields the 1,000th row in Australia with 5 sign-ins.

---

6. Fix for the crashing JOIN query:
❌ Change ‘John’ to “John”
✔ Add “registrants AS r” after JOIN
❌ Add “, r.name” after “s.id” in the SELECT clause
✔ Replace r.name with registrants_id_name.name in the WHERE clause
✔ Move the WHERE clause inside the subquery

Summary: The crash occurs because the alias r is not defined in the main query. Defining the alias or referencing the subquery alias fixes it.

---

7. Fix for subquery with ORDER BY crashing:
❌ Move the ORDER BY clause outside of the subquery
❌ Add a GROUP BY clause instead of the ORDER BY clause in the subquery
✔ Remove the ORDER BY clause from the query
❌ Add “AS salary_table_employees” after the last “)” parentheses
❌ Replace the word IN with JOIN

Summary: MySQL does not allow ORDER BY inside a subquery used with IN. Removing it fixes the error.

---

8. Test subcategories with greatest and smallest increase (Sep 2014 → Oct 2014):
✔ Greatest increase: Communication; Smallest increase: Numerosity
❌ Greatest increase: Memory; Smallest increase: Reasoning
❌ Greatest increase: Communication; Smallest increase: Smell Game
❌ Greatest increase: Cunning; Smallest increase: Numerosity
❌ Greatest increase: Empathy; Smallest increase: Social Bias

Summary: Derived from comparing the completed test counts month-over-month.

---

9. Unique users with dogs > average number:
❌ 26,500
❌ 1,754
❌ 2,808
✔ 11,201
❌ 9,222

Summary: Count of users exceeding the mean number of dogs in the dataset.

---

10. Average test completion time (after cleaning outliers):
❌ 165 minutes
❌ 170 minutes
✔ 175 minutes
❌ 178 minutes
❌ 183 minutes

Summary: Computed after removing invalid reaction times, extreme outliers, and excluded dogs.

---

11. Warm-up games with at least one “question” and “button” step:
❌ 4
❌ 9
❌ 19
✔ 24
❌ 58

Summary: Count of distinct games that satisfy the step_type condition.

---

12. Human users who canceled their monthly subscription and actually had monthly subscriptions:
❌ 64
❌ 87
❌ 94
✔ 2919
❌ 4919

---

Summary Table: MySQL Subqueries & Advanced Queries

---