Question

1. Using SQL, answer the following question referring to the table below
   1. How many twins are there? (Just having the same birthday does not make you twins.)
2. 2. Name the twins.

CREATE TABLE IF NOT EXISTS students (

student_id INT NOT NULL AUTO_INCREMENT,

first_name VARCHAR(16),

last_name VARCHAR(24),

birthday DATE,

street_address VARCHAR(128),

city VARCHAR(32),

PRIMARY KEY (student_id));

INSERT INTO students

(first_name, last_name, birthday, street_address, city) VALUES

('John','Jones','2000-12-17','250 Pines Blvd.','Pembroke Pines'),

('Mark','Bacon','2000-04-12','1270 Walnut St.','Prarie Bluff'),

('Bill','Carlson','1999-07-06','250 Pines Blvd.','Pembroke Pines'),

('Jean','Carlson','1999-07-06','250 Pines Blvd.','Pembroke Pines'),

('Leonard','Cook','2000-09-14','8046 Maple St.','Highland Park'),

('William','Markham','1999-07-06','1600 Sylvan Ln.','Lake Forest'),

('Sam','Cook','1998-10-13','8046 Maple St.','Highland Park'),

('Fred','Williams','1999-07-08','722 Oack Knoll','Arlington'),

('Sally','Fillmore','2000-03-25','1215 Carrington St.','Decatur'),

('Mary','Jones','1999-11-13','1940 Grant St.','Denver'),

('Phyllis','Jones','1999-11-13','1940 Grant St.','Denver');

Answer 1

I am writing two parts of same problem in Sol a)  ans Sol b) . if Sol a) doesn't work then go for Sol b) in which there are two entries for the same pair of twins.

Sol a)

• SQL > select * INTO new_table from ( SELECT s1.first_name as fname1, s2.first_name as fname2 FROM students as s1, students as s2 where s1.birthday=s2.birthday and s1.last_name=s2.last_name and s1.street_address=s2.street_address and s1.city=s2.city and s1.first_name != s2.first_name);
• SQL > delete T from ( select row_number() over(order by [Timestamp]) as rn from new_table ) Twhere T.rn % 2 = 0

>+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++<

sol b)

SELECT s1.first_name as fname1, s2.first_name as fname2 FROM students as s1, students as s2 where s1.birthday=s2.birthday and s1.last_name=s2.last_name and s1.street_address=s2.street_address and s1.city=s2.city and s1.first_name != s2.first_name