Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2008
    Posts
    1

    Unanswered: Confused with SQL statements

    Hi first time posting here,
    Im new to Oracle and sql statements and kind of confused
    I was wondering if someone can check whether or not if my SQL queries are correct, I can do simple queries but I seem to have problems with join statements

    The ones in bold are primary keys

    Patient (patientNo, patName, patAddr, DOB)
    Ward (wardNo, wardName, wardType, noOfBeds)
    Contains (patientNo, wardNo, admissionDate)
    Drug (drugNo, drugName, costPerUnit)
    Prescribed (patientNo, drugNo, unitsPerDay, startDate, finishDate)

    a. Find the names of all the patients being prescribed ‘Morphine’.

    SELECT Prescribed.patientNo, Prescribed.drugNo, Patient.patName, Drug.drugName
    FROM Prescribed, Patient, Drug
    WHERE Prescribed.patientNo = Patient.patientNo, Prescribed.drugno = Drug.drugNo
    AND drugName LIKE 'Morphine';

    b. What is the total cost of Morphine supplied to a patient called ‘John Smith’?

    SELECT SUM(costPerUnit)
    FROM Drug
    WHERE drugName LIKE ‘Morphine’
    AND
    (SELECT patName FROM Patient
    WHERE patName LIKE ‘John Smith’);

    c. For each ward that admitted more than 10- patients today, list the ward number, ward type and number of beds in each ward.

    SELECT wardNo, wardType, NoOfBeds, patientNo,admissionDate
    WHERE COUNT (wardNo) AS COUNT
    AND admissionDate = CURRENT_DATE
    FROM Ward w, Patient p, Contains c
    HAVING COUNT (patientNo) > 10;

    d. List the numbers and names of all patients and the drugno and number of units of their medication. The list should also include the details of patients that are not prescribed medication.

    SELECT patientNo, patName, drugNo, unitsPerDay
    FROM Patient, Prescribed
    WHERE Patient.patientNo = Prescribed.patientNo;

    Thanks any help would be appreciated

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have to reallize that your teacher wants you to demonstrate that you've mastered subqueries

    a. Find the names of all the patients being prescribed ‘Morphine’.

    SELECT patName FROM Patient WHERE patientNo IN (
    SELECT patientNo FROM Prescribed WHERE drugno IN (
    SELECT drugNo FROM drug WHERE drugName LIKE 'Morphine'
    ) )

    get the idea? try that approach on your remaining assignment questions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •