Im wondering if anybody can help me out on a problem I am having for my SQL class. I have been going round in circles for the last three hours and am about ready to give up.
The brief asks that I design a query that performs a division operation in relational algebra, then implements it in SQL - but with the added requirement that it uses a GROUP BY...HAVING clause that accesses more than one table and that groups by more than one column.
The database I have to work with is concerned with the order information for a drug supply company. The following three tables are relevant to the query:
Drugs (Drug_Number, Drug_Market_Name, Unit_Cost)
Patients (Patient_Number, Patient_Name)
Prescriptions (Order_Number, Patient_Number, Drug_Number)
The query I have designed is as follows:
"Get the names of all patients that have been prescribed all drugs that have a unit cost of more than £30"
The SQL I have thus far is as follows:
SELECT Patient_Name from Patients T1 WHERE NOT EXISTS (SELECT Drug_Number FROM Drugs WHERE (Unit_Cost > 30 AND Drug_Number NOT IN (SELECT Drug_Number FROM Prescriptions WHERE T1.Patient_Number = Prescriptions.Patient_Number))
This successfully returns the information I am after, that is the names of patients that have been prescribed all drugs that cost more than £30.
My problem is this: How can I add a GROUP BY...HAVING clause to that query? I was under the impression that the use of GROUP BY...HAVING required the use of an aggregate function such as MIN/MAX/AVG etc, and i'm not sure as to how I can include one, given the data that I have been given, still less how I can get it to access two tables.
Any assistance anybody could provide for this problem would be greatly appreciated.
EDIT: I should add that I am using Oracle 10g for this exercise.