Solve 50 SQL Questions in 2 Hours | Ultimate SQL Interview Practice

Overview of SQL Interview Questions 00:12

"We will be solving 50 questions in the next two hours, which will boost your confidence for interviews or help with writing SQL queries."

• The video is designed to help viewers prepare for SQL interviews by solving a set of 50 problems in around 2 hours.

• The presenter emphasizes that this will be the first time they are working through these questions, fostering a collaborative environment.

• Viewers are reassured that no additional software or data installation is required because all questions can be solved directly in the browser.

Using the Hospital Dataset 00:47

"The dataset consists of four tables related to a hospital system."

• The SQL questions are based on a hospital dataset that includes four tables: patients, admissions, provinces, and doctors.

• The relationships between the tables will be necessary to solve the questions; primarily, they will involve joins based on patient IDs, province IDs, and doctor IDs.

Starting the Practice Questions 02:55

"The first question is to show unique birth years from patients, ordered in ascending order."

• The first question challenges viewers to extract unique birth years from the patients' table, ordering the results in ascending format.

• The presenter provides guidance on using SQL commands to achieve the expected results, emphasizing the importance of getting the correct query output.

Handling Errors and Adjustments 04:25

"If an output is incorrect, it will inform you immediately, so adjustments can be made."

• The system validates responses to each query in real-time, allowing the user to know if they are achieving the correct output or if errors need correction.

• Subsequent questions require users to find unique names and filter data based on specific criteria, involving counting and grouping functions to isolate unique values.

Filtering Patient Information 06:53

"Show patient ID and first name from patients whose diagnosis is dementia."

• In later questions, viewers are tasked with joining multiple tables to retrieve specific patient information, such as filtering by diagnosis, demonstrating the application of JOIN operations in SQL.

• The importance of filtering through diagnoses related to patient admission tables highlights practical SQL use cases relevant to healthcare data management.

Displaying Patient Names Ordered by Length and Alphabetically 11:03

"Order the list by the length of each name and then alphabetically."

• To display every patient's first name, the query specifically focuses on the patients table to retrieve the desired data.

• The ordering should first be based on the length of each name, which is determined using the LENGTH function in SQL.

• In cases where two names have the same length, the results will be sorted alphabetically to resolve ties. The query will ensure that the first preference is given to the length of the names, followed by alphabetical order as a secondary criterion.

Counting Male and Female Patients 13:04

"Show the total number of male patients and total number of female patients in the patients table."

• This query aims to count the number of male and female patients in a single row. It utilizes a CASE statement to create two new columns marked as ‘is male’ and ‘is female’ to differentiate between genders.

• The counts are achieved using the COUNT function, which only counts non-null values, ensuring accurate representation for both male and female patients.

• The result will show two count results: one for males and another for females, displaying how many patients belong to each gender.

Filtering Patient Allergies 16:33

"Show first name, last name, and allergies from patients who have allergies to either penicillin or morphine."

• The query filters for patients with specific allergies. It selects columns for the first name, last name, and the allergies field to display relevant data.

• To achieve this, the IN clause is employed to filter rows where allergies match either penicillin or morphine.

• Ordered results by allergies, followed by first name and last name, provide a clear, structured output for review.

Identifying Patients Admitted Multiple Times 18:26

"Find patients admitted multiple times for the same diagnosis."

• This section requires displaying a combination of patient ID and diagnosis, necessitating a join between the patients and admissions tables.

• A grouping operation is conducted based on patient ID and diagnosis, and the COUNT function is used to determine how many times each patient has been admitted for each particular diagnosis.

• To isolate patients with multiple admissions for the same diagnosis, a HAVING clause is applied, filtering results where the count is greater than one.

Counting Total Patients by City 22:24

"Show the city and the total number of patients in the city."

• This query focuses on deriving a count of patients per city, collecting data from the city column within the patients table.

• The results will be ordered from the most to the least number of patients, followed by an ascending order of city names for clarity.

• The use of COUNT(*) provides the total number of records in each city, allowing for an easy understanding of patient distribution across different locations.

Counting Patients by City and Ordering the Results 22:59

"To find the number of patients by city, we need to group by city and count the number of patients for each."

• The initial SQL task involves counting the total number of patients in each city using the COUNT(*) function. This is achieved by grouping the results by the city, such as "Athens" having twelve patients and another city with nine.

• Following that, the SQL query requires ordering the results from the city with the highest number of patients to the lowest. This is done using the ORDER BY clause, specifying DESC for descending order.

• In cases where two cities have the same number of patients, an additional sorting criterion is needed to order those cities alphabetically by name, utilizing an ascending order after the count of patients.

Using Subqueries for Ordering 24:00

"When an aggregate function is involved, utilize a subquery or common table expression to ensure the ordering works correctly."

• The speaker emphasizes the importance of ordering results after using aggregate functions, which necessitates using a subquery or common table expression (CTE) to first obtain the count of patients before attempting to order by that result.

• They clarify that the ORDER BY runs after the SELECT, allowing for the derived column (number of patients) to be utilized correctly. If structured properly, the query produces the correct output.

Displaying Patient and Doctor Information 25:11

"We need to list the first name, last name, and role of each individual, distinguishing between patients and doctors."

• In the subsequent SQL query, the focus shifts to retrieving the first name, last name, and role of every individual in the system, specifically patients and doctors.

• The query uses a UNION ALL to combine results from both the patient and doctor tables. For patients, the role is hardcoded as "patient", while for doctors, it is set as "doctor". This effectively creates a comprehensive list of individuals along with their roles.

Allergies Sorted by Popularity 27:04

"To show allergies by popularity, we need to count their occurrences and filter out null values."

• The next SQL challenge deals with displaying a list of allergies, ordered by their popularity, which is defined as the quantity of patients having each allergy.

• The query necessitates filtering out null values from the allergies column and utilizing the COUNT(*) function to determine how many patients report each allergy.

• After aggregating the counts, the results are sorted in descending order based on their popularity, showing the most commonly reported allergies at the top.

Patients Born in the 1970s 30:11

"We want to extract patient information based on birth date, particularly those born in the 1970s."

• The next question focuses on retrieving the first name, last name, and birth date of patients born in the 1970s. The filtering criterion is anyone born between January 1, 1970, and December 31, 1979.

• The solution implements a WHERE clause to check that the year of the birth date falls within this range. The results are then ordered by birth date, starting from the earliest.

Combining Last and First Names 32:02

"To create a full name for each patient, last names should be uppercase and first names in lowercase, separated by a comma."

• An additional requirement is to format each patient's name into a single column, with last names displayed in uppercase followed by first names in lowercase.

• The query uses the UPPER() and LOWER() functions to achieve the desired output, concatenating the names with a comma in between.

• Finally, the results are sorted by first name in descending order to complete the format requisites.

Summing Heights by Province 34:09

"We need to sum patient heights by province while filtering provinces based on total height."

• The last discussed query involves aggregating the total height of patients within each province and only displaying those where the sum is greater than or equal to 7,000.

• This requires joining the patient and province tables through their province IDs, enabling data aggregation based on provincial groupings, ultimately allowing insights into average patient heights by location.

Join Operations and Patient Information 35:03

"This join is perfectly fine, and we need the province name detail as well."

• The discussion begins with a focus on a SQL join operation which accurately retrieves province name details, alongside province IDs.

• A SELECT statement demonstrates how including the province name enriches the output, although ultimately the task does not necessitate the join since the province ID is available in the patient table.

• The aim is to sum the heights of patients filtered by a province, ensuring the total height meets or exceeds 7,000.

Analyzing Patient Weight Data 36:45

"The next question is to show the difference between the largest weight and smallest weight for patients with the last name Madoni."

• The next SQL question involves calculating the weight difference for patients named Madoni.

• To achieve this, a query that filters by last name is executed, followed by an ORDER BY clause to identify the maximum and minimum weights.

• The calculation is straightforward; subtracting the minimum weight from the maximum provides the expected answer.

Counting Admissions by Day 38:22

"Show all the days of the month and how many admissions occurred on that day."

• The following challenge requires a query that lists each day of the month from 1 to 31, alongside the total number of admissions for those days.

• The task includes counting admissions across multiple years and months, providing a grouped count that can be sorted from most to least admissions.

• Participants utilize JOIN operations to relate patient data to admissions while recognizing that filtering by date is key to achieving accurate results.

Latest Admission Records for Patients 41:17

"Show all columns for patient ID 552's most recent admission date."

• This question leads into retrieving full details for a specific patient's latest admission.

• An ORDER BY clause sorts admissions in descending order to isolate the most recent record, using LIMIT to return a single row.

• The explanation highlights differences in SQL syntax for various platforms, like using TOP for SQL Server compared to LIMIT in MySQL.

Criteria Based Hospital Admissions 43:00

"Show patient ID, attending doctor ID, and diagnosis for admissions that match one of the two criteria."

• The query aims to fetch patient IDs, attending doctor IDs, and diagnoses based on specified conditions regarding odd patient IDs and the inclusion of a specific digit in the doctor IDs.

• A combination of WHERE conditions is employed to filter records that meet either of the criteria, demonstrating the flexibility of SQL queries in addressing complex requirements.

• The solution provides accurate patient data based on the discussed conditions.

Doctor Admission Statistics 46:05

"Show first name, last name, and total number of admissions attended for each doctor."

• The final query emphasizes aggregating data to display each doctor's name along with their total admissions.

• To achieve this, a JOIN operation between the admission and doctor tables is essential to access doctors' names.

• The focus is on collecting and summarizing the attendance data related to each doctor, reinforcing SQL's ability to combine and analyze multiple data sources.

Displaying Doctor Information and Admission Statistics 46:49

"We need to show the doctor's first name, last name, and total number of admissions."

• The query begins by selecting the doctor's first and last names alongside the count of admissions, employing a JOIN operation with the admissions table to gather necessary data.

• It requires grouping results by the doctor's first and last names to correctly aggregate the total number of admissions.

• The expected output includes details of each admission alongside the full names of the attending doctors.

Querying Doctor's ID and Admission Dates 47:57

"For each doctor, display their ID, full name, and the first and last admission dates they attended."

• This segment focuses on constructing a query that retrieves the doctor's ID, full name—obtained by concatenating the first and last names—and the earliest and latest dates of patient admissions.

• It involves using the MIN and MAX functions to determine the dates and necessitates a GROUP BY clause on the relevant fields to facilitate the use of these aggregate functions.

Counting Patients per Province 50:51

"Display the total number of patients for each province, ordered by descending."

• This query calculates the number of patients for each province by performing an INNER JOIN to merge data from the province and patient tables.

• A GROUP BY clause is used to consolidate results based on province name, with an ORDER BY clause applied to sort the totals in descending order.

• The query aims to identify the province that has the highest patient count first.

Patient Diagnosis Information by Admission 52:25

"For every admission, display the patient's full name, admission diagnosis, and the doctor's full name who diagnosed their problem."

• This part necessitates joining three tables: the patient, admission, and doctor tables to gather comprehensive information regarding each admission.

• The patient's full name is formed by concatenating first and last names, while the admission diagnosis and the doctor's full name are retrieved directly from their respective tables.

• The query reflects the relationships between admissions and diagnosing doctors effectively.

Identifying Duplicate Patients 56:15

"Display the first name, last name, and number of duplicate patients based on their first name and last name."

• This task involves querying to identify duplicate patients by first and last name, necessitating a GROUP BY clause on both fields.

• A COUNT function tallies the duplicates, and the HAVING clause filters results to show only those with counts greater than one, indicating true duplicates are present.

Converting Patient Height and Weight 58:04

"Display the patient's full name and height in feet, rounded to one decimal place."

• The query computes patient height by converting centimeters to feet through division by 30.48, ensuring the result is rounded to one decimal place.

• Additionally, a conversion from kilograms to pounds is required for the weight, rounding to the nearest whole number.

• The emphasis is on using correct calculation methods while delivering the necessary patient information succinctly.

Data Processing for Patient Information 59:13

"We need to round the values to zero decimal places for weight and pound."

• The focus is on calculating specific patient attributes such as height, weight, birth date, and gender. The gender must be explicitly stated as 'male' or 'female' instead of using abbreviations like 'FM'.

• The SQL syntax uses a CASE statement to replace gender codes with full text descriptions, ensuring clarity in the output.

Identifying Patients without Admissions 01:00:54

"Show patient IDs, first names, and last names from patients who don't have any records in the admissions table."

• The query aims to extract patient information for those who are registered but have not been admitted, indicating they may have only visited for consultations.

• Two methods can achieve this: using a NOT IN condition or performing a LEFT JOIN with the admissions table and checking for NULL values in the admissions records to identify patients without admissions.

Summary Statistics of Patient Visits 01:04:20

"Display a single row with maximum, minimum, and average visits calculated per admission date."

• This question requires aggregating visit data by admission date to compute the maximum, minimum, and average visits per day.

• A subquery or common table expression (CTE) can be utilized to consolidate the results before calculating the required statistics. The averages should be rounded to two decimal places for precision.

Grouping Patients by Weight 01:07:34

"Show all patients grouped into weight categories, and order by these categories in descending order."

• The task involves creating weight groups based on specified ranges (e.g., 100-109, 110-119) and counting how many patients fall into each group.

• A mathematical approach using integer division and the floor function is applied to determine the appropriate weight group, facilitating the classification of patients into the defined categories.

Counting Patients by Weight Group 01:11:23

"We need to count the number of patients in each weight group and order the list by weight group in descending order."

• The task involves calculating the number of patients grouped by weight. The output should include the patient count per weight group, and the results should be ordered by the weight group itself, showing the heaviest groups first.

Identifying Obese Patients Based on BMI 01:12:14

"Show patient ID, weight, height, and whether the patient is obese, based on their BMI."

• This section focuses on displaying patient information, specifically their ID, weight, height, and an indicator of obesity calculated using the Body Mass Index (BMI). The BMI is determined by the formula: weight in kilograms divided by height in meters squared. An additional condition is applied where a BMI of 30 or higher classifies a patient as obese.

Joining Patient and Doctor Tables for Information Retrieval 01:16:35

"We need to display the patient ID, first name, last name, and the attending doctor's specialty for patients diagnosed with epilepsy and whose doctor is Lisa."

• The next query requires a join between the patient and doctor tables to retrieve and display specific patient information along with the specialty of their attending doctor. The filters applied include the diagnosis and the requirement for the doctor’s first name to be Lisa.

Creating Temporary Passwords for Admitted Patients 01:18:47

"Generate a temporary password for each patient based on their ID, the length of their last name, and their year of birth."

• This part explains how to generate a temporary password for patients who have gone through admissions. The password structure consists of the patient ID, followed by the numeric length of the patient's last name, and ending with the year of the patient’s birth. It's essential to ensure that only admitted patients are included in this data retrieval process, integrating information from both patient and admissions tables while avoiding duplicates.

Cost Analysis Based on Patient Insurance 01:24:26

"Each admission costs $50 for patients without insurance and $10 for patients with insurance."

• The question revolves around determining if a patient has insurance based on their patient ID. Patients with even patient IDs are considered to have insurance, while those with odd IDs do not.

• A new column is created to represent whether a patient has insurance with the labels 'yes' for even IDs and 'no' for odd IDs. The SQL syntax utilizes a CASE statement to differentiate the two groups.

• The solution involves grouping patients accordingly and counting how many fall into each category: those with insurance and those without.

• The cost for patients is set at $50 for those without insurance and $10 for those with insurance. An additional column calculates these costs based on the grouping established earlier.

• However, it’s noted that a potential issue arises since multiple admissions for a single patient are not accounted for. A join with the admissions table is recommended to correctly tally costs across all entries for a patient.

Identifying Provinces with Patient Demographics 01:31:53

"Show the provinces that have more patients identified as male than female."

• This section discusses querying to find provinces where the number of male patients exceeds the number of female patients, necessitating data from both the patient and province tables.

• The process involves grouping data by province and gender, counting the total number of patients for each gender.

• To show only provinces that meet the specified criteria, a WHERE clause is implemented to filter results, particularly emphasizing the need to aggregate data accurately at the province level.

• Columns for both counts of female and male patients are created to allow for straightforward comparison between the two groups.

• The final query outputs only the names of provinces that satisfy the condition of having more male patients than female patients, completing the requirement efficiently.

Query for Specific Patient Criteria 01:36:41

"Retrieve all columns for the patient who matches the specified criteria."

• The task involves extracting rows from the patient table for those who meet strict criteria: names that contain the letter 'R' after the first two letters, female gender identification, specific birth months (February, May, or December), weight parameters, odd patient ID, and residency in Kingston.

• An inner join may be utilized to further refine or cross-reference patient data as necessary within the broader structure of the SQL query.

• This question requires careful attention to detail to ensure that all criteria are properly integrated into the final query for accurate and meaningful results.

First SQL Query: Selecting Patients with Specific Conditions 01:37:34

"Let's start by putting the conditions for selecting patients based on their first names, gender, month of birth, weight, and patient ID."

• The video begins with a demonstration of a SQL query that selects data from a patients table. It specifically looks for patients whose first names contain the letter 'R' as the third character. This is accomplished by using the LIKE operator with a combination of underscores to denote specific character positions.

• The query also filters patients who identify as female (gender = 'F') and who were born in February, May, or December. This is done by checking the birth month using numeric values.

• Weight criteria are applied, requiring values between 60 to 80 kilograms, with both boundaries included.

• Finally, there is a stipulation that patient IDs must be odd numbers; this is checked using the modulus operator.

• The query successfully returns the relevant patient data conforming to these conditions.

Calculating Percentage of Male Patients 01:40:32

"Now we will calculate the percentage of patients who are male, rounding the answer to the nearest hundredth and expressing it in percent form."

• The next task involves calculating the percentage of patients with the gender 'M'. The video explains how to do a simple division to find the proportion of male patients out of the total.

• A case statement is used within a SQL query to count the number of male patients by assigning a value of one for 'M' and zero otherwise. This aids in calculating the total count of patients.

• After executing the query, there’s a realization that integer division causes the result to be incorrect, leading to the solution of multiplying by 1.0 to convert to a decimal value.

• The percentage is then further adjusted by multiplying by 100 and rounding to two decimal places, along with appending a percentage sign to the output to match the required format.

Total Admissions and Changes Over Time 01:43:25

"For each admission date, display the total number of admissions and the change in count from the previous day."

• The video progresses to calculating the total number of admissions for each date and how that number changes compared to the previous day.

• It begins with a basic query that groups admissions by their dates and counts the total admissions, but it also requires access to previous day's data.

• To include the previous day's admissions, a LAG function is utilized, which retrieves the admission count from the previous row sorted by admission date.

• The difference between the total admissions and previous day admissions is then computed to display how the admissions change day by day, allowing for insights into trends over time.

Sorting Province Names with Prioritization 01:47:16

"We need to sort the province names in ascending order, ensuring Ontario is always at the top."

• The final query focuses on sorting the province names such that Ontario ranks first, followed by the remaining provinces in alphabetical order.

• Initially, the list of provinces is retrieved, and a case statement is implemented to create a sorting flag, assigning a value of one to Ontario and zero to all others.

• The sorting process utilizes this flag, ensuring that Ontario appears at the top, while the rest are sorted in ascending order as per their names.

• This multi-level sorting mechanism not only fulfills the requirement but also enhances the clarity of results in the output.

Using Order By in SQL Queries 01:50:21

"You can directly put your CASE WHEN statement in your ORDER BY clause."

• The speaker explains how to simplify queries by using the ORDER BY clause effectively. By integrating the CASE WHEN statement directly into the ORDER BY clause, the data can be sorted according to specific conditions while keeping the query concise.

Analyzing Doctor Admissions Data 01:51:22

"We need to break down the total number of admissions each doctor has started each year."

• The next task is to analyze the total admissions recorded by each doctor for each year. This requires gathering data from both the doctor and admission tables, including details such as the doctor's ID, full name, specialty, year of admission, and total admissions per year.

Joining Tables to Retrieve Data 01:52:24

"We will join the admissions and doctor tables using the doctor ID."

• The speaker instructs on how to join the admissions table with the doctor table based on the common doctor ID field. This is a key step in the SQL query to extract relevant data for each doctor, ensuring that both tables provide necessary information for the analysis.

Formatting Doctor Names and Extracting Yearly Data 01:53:19

"I need to create the doctor name by concatenating the first and last name."

• To display the doctor’s full name, the speaker demonstrates how to concatenate the first and last names from the doctor table. Additionally, they explain how to extract the admission year and count the total admissions per year, forming a subsequent grouping in the SQL query.

Final Query Validation and Moving Forward 01:54:47

"We just had to join the doctor and admission tables."

• Upon constructing the SQL query, the speaker validates the outputs to ensure correctness, showcasing the total admissions for each doctor by year. With the previous questions completed, they suggest moving on to additional easy questions available for practice.