Hospital SQL Interview Practice Quiz Quiz

Challenge your understanding of SQL queries with this beginner-friendly quiz focused on common interview topics such as selecting unique values, table joins, and data ordering using a hospital database schema. Strengthen your skills in interpreting table relations, essential SQL functions, and logic relevant to healthcare data scenarios.

1. Selecting Unique Birth Years

   Which SQL clause would you use to display unique birth years from a patient table's 'birth_date' column?

   1. SELECT YEAR(birth_date) GROUP BY birth_date
   2. SELECT birth_year FROM birth_date
   3. SELECT DISTINCT YEAR(birth_date)
   4. SELECT UNIQUE birth_date

   Explanation: The DISTINCT keyword removes duplicate values, and applying YEAR() extracts just the year from 'birth_date', so 'SELECT DISTINCT YEAR(birth_date)' returns all unique years. Using GROUP BY without aggregation is less direct and requires extra steps. SELECT UNIQUE is not standard syntax, and the last option doesn't extract or format the year.

2. Linking Patient and Admission Information

   If you want to combine data from the patient table and admission table using the patient ID, which SQL operation should you use?

   1. JOIN
   2. ORDER BY
   3. GROUP BY
   4. UNION

   Explanation: JOIN allows you to combine rows from two tables based on related columns, such as patient ID. UNION joins result sets, not rows. GROUP BY is used to aggregate data, and ORDER BY is for sorting, so neither GROUP BY nor ORDER BY can combine rows from separate tables.

3. Sorting Results in Ascending Order

   How would you sort the unique birth years you retrieved from the patient table in ascending order?

   1. ORDER BY birth_year ASC
   2. SORT birth_year ascending
   3. GROUP BY birth_year ASC
   4. ORDER birth_year DESC

   Explanation: ORDER BY is the correct SQL clause for sorting, while ASC specifies ascending order. GROUP BY is used for aggregation, not sorting. 'SORT' is not valid SQL syntax, and 'ORDER ... DESC' sorts in descending order, which is the opposite of ascending.

4. Selecting Unique First Names

   Which SQL statement returns a list of all different first names from the patient table?

   1. SELECT first_name FROM patient GROUP ALL
   2. SELECT ALL first_name FROM patient
   3. SELECT first_name UNIQUE FROM patient
   4. SELECT DISTINCT first_name FROM patient

   Explanation: 'SELECT DISTINCT first_name' returns only unique first names. 'ALL' does not filter duplicates. GROUP ALL and UNIQUE after column name are not valid SQL syntax and will result in an error.

5. Retrieving Province Names

   To display each patient's province name along with their first name, which type of JOIN should you typically use between the patient and province tables?

   1. SELF JOIN
   2. FULL JOIN
   3. CROSS JOIN
   4. INNER JOIN

   Explanation: INNER JOIN combines rows with matching province IDs in both tables, effectively giving you patient information and associated province names. CROSS JOIN would produce all possible pairs, FULL JOIN is rarely needed in this context, and SELF JOIN connects a table to itself, which is not relevant here.

6. Selecting Data for a Specific Doctor

   Which clause should you add to retrieve all admissions handled by a doctor with the ID of 7?

   1. WHERE doctor_id = 7
   2. LIMIT doctor_id = 7
   3. GROUP BY doctor_id 7
   4. HAVING doctor_id = 7

   Explanation: The WHERE clause filters rows based on conditions, such as doctor_id being equal to 7. GROUP BY organizes rows for aggregation, HAVING is used after GROUP BY, and LIMIT controls result count, not row filtering.

7. Identifying Duplicate Patient Names

   What simple SQL function could help detect if multiple patients share the same first name in the patient table?

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

   Explanation: COUNT totals occurrences of a given value, helping identify duplicates when combined with GROUP BY. MAX and MIN find maximum or minimum values, and AVG calculates an average, none of which directly indicate duplicates.

8. Selecting All Columns from a Table

   Which symbol allows you to select every column from the patient table without listing each column name?

   1. *
   2. u0026
   3. #
   4. !

   Explanation: The asterisk (*) is used to select all columns in SQL. The other symbols (#, u0026, !) are not valid and will cause syntax errors if used in this context.

9. Finding the Youngest Patient

   Which SQL function would you use to find the most recent 'birth_date' value in the patient table?

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

   Explanation: MAX returns the largest (most recent, chronologically) date value in a column. MIN provides the earliest date, and SUM and COUNT are for numeric totals and counting rows, not finding maximum values.

10. Listing Distinct Doctors in Admissions

    To determine how many different doctors have admitted patients, which field should you select with DISTINCT from the admission table?

    1. admission_id
    2. doctor_id
    3. patient_id
    4. province_id

    Explanation: doctor_id identifies which doctors are involved in admissions, so DISTINCT on this field counts unique doctors. admission_id is unique for every admission and patient_id for patients, so those would not show the count of different doctors. province_id relates to location, not admissions by doctor.