The structure of the tables is as follows:
child
CREATE TABLE CHILD(
child_id SMALLINT,
child_name VARCHAR(255) NOT NULL,
birth_date DATE NOT NULL,
gender VARCHAR(255) NOT NULL,
address VARCHAR(255),
city VARCHAR(255),
CONSTRAINT PK_CHILD PRIMARY KEY(child_id)
letters
CREATE TABLE LETTER(
letter_id SMALLINT,
arrival_date DATE NOT NULL,
number_toys INTEGER NOT NULL,
delivery_mode VARCHAR(255) NOT NULL,
child_id SMALLINT,
CONSTRAINT PK_LETTER PRIMARY KEY(letter_id),
CONSTRAINT CHILD_FK FOREIGN KEY (child_id) REFERENCES CHILD(child_id)
);
wished_toy
CREATE TABLE WISHED_TOY(
letter_id SMALLINT,
toy_id SMALLINT,
CONSTRAINT PK_WISHED_TOY PRIMARY KEY(letter_id, toy_id),
CONSTRAINT LETTER_FK FOREIGN KEY (letter_id) REFERENCES LETTER(letter_id)
CONSTRAINT LETTER_FK FOREIGN KEY (toy_id) REFERENCES TOY(toy_id)
);
toy
CREATE TABLE TOY(
toy_id SMALLINT,
toy_name VARCHAR (255) NOT NULL,
price DECIMAL NOT NULL,
toy_type VARCHAR(255) NOT NULL,
manufacturer VARCHAR(255) NOT NULL,
CONSTRAINT PK_TOY PRIMARY KEY(toy_id),
);
I am asked for a query with the requested toys (wished_toy) at least 3 times in letters received (letter) from the year 2016.
I have this, which shows me of each letter (letter) requested since 2016, each toy that has been requested, what I don't know is how to count the toy_ids that are the same in order to filter the toys that have been requested at least 3 times.
select l.letter_id,wi.toy_id
from letter l
join wished_toy wi
on l.letter_id=wi.letter_id
where l.arrival_date >= '2016-01-01'
group by l.letter_id,wi.toy_id
order by letter_id asc
It brings me this:
letter_id toy_id
1 3
1 4
1 1
2 4
And so... the case is how can I add or count the toys that have been requested at least 3 times.
The wished_toy table won't give you the name of the toy, which seems to be what they ask for. You have to join with
toy
. I would do it like:This assumes that the step table
wished_toy
does not have duplicate records. That is, the same letter cannot ask for the same toy twice. (Santa Claus, please bring me a doll, a monopoly and a doll. WTF). Maybe it would be more defensive to doBut basically, if the same card asks for the same toy twice, I suppose that it may be correct to count the toy twice and in that case the first query would be the one indicated.