Relational Division Problems

[hawkoftheeye]

Hello.

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.

Many thanks

Richard

EDIT: I should add that I am using Oracle 10g for this exercise.

[anatolse]

Hello.

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.

Many thanks

Richard

EDIT: I should add that I am using Oracle 10g for this exercise.

I think this code is suitable for the task

with full_count_of_desired_drugs as 
     (
        select count(*) cnt
        from drugs 
        where unit_cost > 30
     ),
     prescripted_drugs_per_patient as 
     (
        select patient_number, count(distinct Drug_Number) cnt
        from patients p,
             prescriptions ps,
             drugs d
        where ps.patient_number = p.patient_number
             and ps.drug_number = d.drug_number
             and d.unit_cost > 30
        group by patient_number
      )
select b.*
from full_count_of_desired_drugs a,
     prescripted_drugs_per_patient b
where a.cnt = b.cnt

[hawkoftheeye]

I think this code is suitable for the task

with full_count_of_desired_drugs as 
     (
        select count(*) cnt
        from drugs 
        where unit_cost > 30
     ),
     prescripted_drugs_per_patient as 
     (
        select patient_number, count(distinct Drug_Number) cnt
        from patients p,
             prescriptions ps,
             drugs d
        where ps.patient_number = p.patient_number
             and ps.drug_number = d.drug_number
             and d.unit_cost > 30
        group by patient_number
      )
select b.*
from full_count_of_desired_drugs a,
     prescripted_drugs_per_patient b
where a.cnt = b.cnt

Many thanks for your help. I will check the code tonight and see if it does what I needed to do. I must admit I did not think about the use of WITH.

Cheers!

Richard