MySQL DML practice task(part-1)

this is a practice/ assignment given by my teacher, you can also practice if u want.

Mysql

Data is here for practice & description is here.

1. Show records of ‘male’ patient from ‘southwest’ region.

SELECT * FROM insurance_data
WHERE gender= 'male' AND region = 'southwest'

records of ‘male’ patient from ‘southwest’ region.

2. Show all records having bmi in range 30 to 45 both inclusive.

SELECT * FROM insurance_data
WHERE bmi between 30 AND 45

all records having bmi in range 30 to 45 both inclusive.

3. Show minimum and maximum bloodpressure of diabetic patient who smokes. Make column names as MinBP and MaxBP respectively.

SELECT MIN(bloodpressure) AS MinBP , 
MAX(bloodpressure) AS MaxBP
from insurance_data
WHERE diabetic = 'yes' 
AND smoker = 'yes';

minimum and maximum bloodpressure of diabetic patient who smokes

4. Find no of unique patients who are not from southwest region.

SELECT * 
FROM insurance_data 
where region 
NOT IN ('southwest')

5. Total claim amount from male smoker.

SELECT SUM(claim) FROM insurance_data
WHERE gender = 'male'
AND smoker = 'yes'

Total claim amount from male smoker.

6. Select all records of south region.

select * from insurance_data
where region like 'south%'

Select all records of south region

7. No of patient having normal blood pressure. Normal range[90–120]

SELECT count(bloodpressure)
FROM insurance_data
WHERE bloodpressure BETWEEN 90 AND 120

No of patient having normal blood pressure. Normal range[90–120]

8. No of patients below 17 years of age having normal blood pressure as per the below formula -

• BP normal range = 80+(age in years × 2) to 100 + (age in years × 2)
• Note: Formula taken just for practice, don’t take in real sense.

SELECT COUNT(*) FROM insurance_data
WHERE 
age < 17  AND 
bloodpressure BETWEEN 80 + (age * 2) AND 100 + (age * 2)

9. What is the average claim amount for non-smoking female patients who are diabetic?

SELECT AVG(claim) from insurance_data
WHERE gender = 'female' AND smoker = 'No' AND diabetic = 'Yes'

average claim amount for non-smoking female patients who are diabetic.

10. Write a SQL query to update the claim amount for the patient with PatientID = 1234 to 5000.

first, let's see the claim amount of id 1234.

now we will update the claim amount from 37k to 5k

UPDATE insurance_data 
SET claim = 5000 
WHERE patientID = 1234

11. Write a SQL query to delete all records for patients who are smokers and have no children.

First, we will see the count of patients who are smokers and have no children.

115 patients with no child and smoker

DELETE FROM insurance_data WHERE smoker = 'Yes' AND children = 0