SQL DML query - need assistance

Question

I am beginner at SQL and I am trying to create a query.

I have these tables:

CREATE TABLE Hospital (
    hid INT PRIMARY KEY,
    name VARCHAR(127) UNIQUE,
    country VARCHAR(127),
    area INT
);
CREATE TABLE Doctor (
    ic INT PRIMARY KEY,
    name VARCHAR(127),
    date_of_birth INT,
);
CREATE TABLE Work (
    hid INT,
    ic INT,
    since INT,
    FOREIGN KEY (hid) REFERENCES Hospital (hid),
    FOREIGN KEY (ic) REFERENCES Doctor (ic),
    PRIMARY KEY (hid,ic)
 );

The query is: What's the name and the IC of doctors born after 1980 and who work in England and Spain? Thanks

per two slightly different answers, please clarify working in England AND Spain -- indicating the Dr. travels between the two countries and works in both (via BlueFeet answer)... VS Works in England OR Spain -- you don't care which, as long as its one of them. (via Yogesh answer) — DRapp, Nov 24 '12 at 03:42

score 1 · Answer 1 · answered Nov 23 '12 at 20:27

You can use something similar to this:

select d.name
from doctor d
left join work w
  on d.ic = w.ic
left join hospital h
  on w.hid = h.id
where d.date_of_birth >= '1980-01-01'
  and h.country in ('England', 'Spain')
group by d.name
having count(distinct h.country) = 2;

score 0 · Accepted Answer · answered Nov 23 '12 at 20:30

0

Try this:

SELECT d.name, d.ic
FROM doctor d
JOIN work w ON d.ic = i.ic
JOIN hospital h ON w.hid = h.hid
WHERE YEAR(d.date_of_birth) >= 1980
AND UPPER(h.country) IN ('ENGLAND', 'SPAIN');

I hope this is enough.

answered Nov 23 '12 at 20:30

Rachcha

8,486
8
48
70

SQL DML query - need assistance

2 Answers2