CODEWITHSUNDEEP
sqltestingdiagramerd
ZoEM
ZoEM

Reputation: 852

Two questions about SQL based on an ERD diagram. (novice level)

I've attached a picture that shows the ERD diagram that these two questions apply to.

The ERD is about optometry doctors and their patients. It's basically coursework but I'm having trouble understanding these two.

Any good SQL savvy people out there willing to help me out with the corresponding SQL statements? Thank you. This is the ERD

1. A list of the patient’s name and the doctor who currently treats that patient, the date of the last eyetype result, the last glasses type and its brand, now show all these patients that also live either Chicago or San Diego. Also order by date and inside that on patient’s name

  1. A list that shows per each brand of glasses the total count of every patient that wears the corresponding brand. Show only the results where the total amount of patients is more than 25.

Upvotes: 0

Views: 79

Answers (2)

Stidgeon
Stidgeon

Reputation: 2723

Your SQL query is a good start, but there are a few things to be aware of:

  1. how are the tables related? how can you join them in a query (how do they connect?)?

  2. in your SELECT statement, I think it's asking for 'glass type' rather than 'eye type'

  3. SELECT statement is missing FROM, indicating which tables you're SELECTing FROM - add to this the JOINs you need to make to relate the tables

  4. WHERE clause looks good

  5. ORDER BY is missing date (though 'Date' isn't specified - is that start date or end date?)

anyway, putting all that together gets you something like this:

SELECT Name_patient, Name_doctor, Type, brand, Start_date, End_date
FROM Patients INNER JOIN Doctors
ON Patients.Patientnumber = Doctors.Patientnumber
INNER JOIN Type_of_glasses
ON Patients.Patientnumber = Type_of_glasses.Patientnumber
WHERE City = 'Chicago' OR 'San Diego'
ORDER BY Start_date, End_date, Name_patient;

Now you're ready to try number 2. Just think 'COUNT' goes with 'GROUP BY' and your condition is 'HAVING > 25' and not 'WHERE'

EDITED:

really close on 2. Need to add the JOIN condition (ON ...) and edit COUNT in SELECT line to wrap field in parentheses. The GROUP BY tells the query what the groups are that you're counting, so here you're grouping by brand and counting how many patients have that brand.

SELECT COUNT(Name_Patient), Brand 
FROM Patients INNER JOIN Type_Of_Glasses 
ON Patients.Patientnumber = Type_of_glasses.Patientnumber
GROUP BY Brand
HAVING count(Name_Patient) > 25

Upvotes: 0

Bavard
Bavard

Reputation: 76

A list of the patient’s name and the doctor who currently treats that patient, the date of the last eyetype result, the last glasses type and its brand

SELECT Name P.Patient, D.Name Doctor, P.End date, Tog.Type, Tog.Brand
FROM Doctors d JOIN Patients p ON D.DoctorsID=P.DoctorsID
JOIN Type of glasses tog ON P.Patientnumber=tog.Patientnumber

Now show all these patients that also live either Chicago or San Diego. Also order by date and inside that on patient’s name

SELECT Name P.Patient, D.Name Doctor, P.End date, Tog.Type, Tog.Brand
FROM Doctors d JOIN Patients p ON D.DoctorsID=P.DoctorsID
JOIN Type of glasses tog ON P.Patientnumber=tog.Patientnumber
WHERE P.City= 'Chicago' OR 'San Diego'
ORDER by P.End date, Name Patient

Upvotes: 1

Related Questions