Creating Members table:
CREATE TABLE IF NOT EXISTS members (
membership_number INT AUTO_INCREMENT ,
full_names VARCHAR(150) NOT NULL ,
date_of_birth DATE NOT NULL,
role_id INT,
contact_number VARCHAR(75) ,
email VARCHAR(255),
referrer_number INT,
PRIMARY KEY (membership_number));
Creating roles table:
CREATE TABLE roles (
role_id INT AUTO_INCREMENT,
role_description VARCHAR(255),
PRIMARY KEY (role_id));
ALTER TABLE members ADD FOREIGN KEY (role_id) REFERENCES roles(role_id);
ALTER TABLE members ADD FOREIGN KEY members(referrer_number) REFERENCES members(membership_number);
Give the answers of the below questions:
Write SQL queries to compute
Q1. the name and phone number of members born after 2002 ;
Q2. for each role, the role description and the number of members with this role when there is at least three persons with this role ;
Q3. for each role, the role description and the date of birth of the oldest members ;
Q4. the name and the date of birth of oldest members whose role is assistant;
Q5. the name and the referrer name when the referrer is younger than the member