Interest Club
Description
Implement the missing code, denoted by ellipses. You may not modify the pre-existing code.
You are the chairman of your university’s drawing club, which isn’t doing very well right now. The club meets two times a week to exchange drawing advice, talk about new techniques, and draw something together. But the members are starting to get bored during these meetings, so you’ve decided to add an additional activity to the routine.
In order to do this, you decided to collect information about the students, which is now stored in the table people_interests, which has the following columns:
- name: The unique name of a person;
- interests: The set of interests or hobbies this person has, given as a comma-joined string. This column has datatype
set('reading','sports','swimming','drawing','writing','acting','cooking','dancing','fishkeeping','juggling','sculpting','videogaming')
.
This information gave you the idea that reading might be an interesting theme for the next meeting, so you announced that the next meeting will be reading-related. Now you’re interested in the number of members that will come.
Given the people_interests table, find the people who will attend the next meeting, i.e. those who are fond of both drawing and reading. The resulting table should consist of a single name
column, and the records should be sorted by people’s names.
Example
For the following table people_interests
name | interests |
---|---|
August | cooking,juggling |
Buddy | reading,swimming,drawing,acting,dancing,videogaming |
David | juggling,sculpting |
Dennis | swimming,cooking,fishkeeping |
James | reading,drawing |
the output should be
name |
---|
Buddy |
James |
- [execution time limit] 10 seconds (mysql)
Solution
1
2
3
4
5
CREATE PROCEDURE interestClub()
SELECT name
FROM people_interests
WHERE interests & 1 AND interests & 8
ORDER BY name