Ace Your MySQL Interview: Essential Questions for Analysts u0026 Engineers Quiz

1. Understanding Subqueries

   In MySQL, what is the primary purpose of using a subquery within a SELECT statement?

   1. To encapsulate a query result that can be used by the outer query
   2. To speed up the database server's boot process
   3. To define a table's primary key
   4. To convert data types within a column
   5. To alter the structure of a database

2. Difference Between JOIN Types

   Which JOIN type should you use if you want to return only the rows that have matching values in both tables?

   1. INNER JOIN
   2. OUTER JION
   3. LEFT OUTER JOIN
   4. RIGHT JOIN
   5. CROSS JOIN

3. Understanding GROUP BY with Aggregations

   When calculating average salary for each year, which SQL clause ensures salaries are averaged per year separately?

   1. GROUP BY
   2. ORDER BY
   3. HAVING
   4. DISTINCT
   5. LIMIT

4. Using CASE WHEN in SELECT

   In the provided query, how does the CASE WHEN statement help extract salaries for fielders and pitchers?

   1. It conditionally assigns average salary values based on the player's role
   2. It sorts players alphabetically by role
   3. It filters out teams with losing records
   4. It joins two different databases
   5. It multiplies salary values by two

5. Understanding UNION ALL

   Why does the query use UNION ALL between the subqueries for fielders and pitchers?

   1. To combine all results from both roles without removing duplicate records
   2. To sort the results in alphabetical order
   3. To filter rows based on salary amount
   4. To create a new table in the database
   5. To convert rows into columns

6. Identifying the Role with the Highest Average Salary

   Suppose your query finds that in 1985, fielders have a higher average salary than pitchers. What SQL function helps you retrieve the highest value per role and year?

   1. MAX
   2. MIN
   3. SUM
   4. COUNT
   5. AVG

7. Understanding Common Table Expressions (CTEs)

   In the Teams ranking question, why is a WITH clause (CTE) such as TeamRank used before the final SELECT?

   1. To temporarily store ranked team data for reuse in the next query
   2. To limit the number of rows in the output
   3. To physically rearrange the data on disk
   4. To define new columns in the original table
   5. To encrypt the data in the table

8. Understanding RANK() Window Function

   What is the purpose of RANK() OVER (PARTITION BY yearID ORDER BY W DESC) in the context of the Teams table?

   1. It assigns a ranking to teams based on number of wins within each year
   2. It deletes all teams with the highest wins
   3. It calculates the average wins for all years
   4. It sums the wins of the top team
   5. It filters teams by teamID

9. Using CASE to Label Results

   Why is the CASE statement used in the SELECT when showing 'First Place' or 'Last Place' teams?

   1. To label teams based on their calculated rank values
   2. To permanently rename every row in the Teams table
   3. To skip rows with missing values
   4. To convert numbers to strings
   5. To join multiple tables together

10. Filtering Results for Specific Ranks

    In the final query for ranking teams, which condition ensures only teams in first and last places appear in the results?

    1. WHERE rank_min = 1 OR rank_max = 1
    2. HAVING salary u003E 100000
    3. LIMIT 2
    4. WHERE teamID IS NOT NULL
    5. GROUP BY place