CREATE TABLE Contacts (
    ContactID INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthDate DATE
);

CREATE TABLE Addresses (
    AddressID INT AUTO_INCREMENT PRIMARY KEY,
    ContactID INT,
    AddressLine1 VARCHAR(100),
    City VARCHAR(50),
    State VARCHAR(50),
    ZipCode VARCHAR(10),
    Country VARCHAR(50),
    FOREIGN KEY (ContactID) REFERENCES Contacts(ContactID)
);

CREATE TABLE PhoneNumbers (
    PhoneID INT AUTO_INCREMENT PRIMARY KEY,
    ContactID INT,
    PhoneNumber VARCHAR(15),
    Type VARCHAR(20),
    FOREIGN KEY (ContactID) REFERENCES Contacts(ContactID)
);

CREATE TABLE EmailAddresses (
    EmailID INT AUTO_INCREMENT PRIMARY KEY,
    ContactID INT,
    Email VARCHAR(100),
    Type VARCHAR(20),
    FOREIGN KEY (ContactID) REFERENCES Contacts(ContactID)
);

CREATE TABLE ContactsGroup(
    GroupID INT AUTO_INCREMENT PRIMARY KEY,
    GroupName VARCHAR(50)
);

CREATE TABLE ContactsGroupDetails (
    ContactID INT,
    GroupID INT,
    PRIMARY KEY (ContactID, GroupID),
    FOREIGN KEY (ContactID) REFERENCES Contacts(ContactID),
    FOREIGN KEY (GroupID) REFERENCES ContactsGroup(GroupID)
);

INSERT INTO Contacts (ContactID, FirstName, LastName, BirthDate) VALUES
    (1, 'John', 'Doe', '1985-04-12'),
    (2, 'Jane', 'Smith', '1990-11-23'),
    (3, 'Emily', 'Johnson', '1975-06-15'),
    (4, 'Michael', 'Brown', '1980-01-10'),
    (5, 'Sarah', 'Davis', '1995-09-30'),
    (6, 'David', 'Wilson', '1988-12-25'),
    (7, 'Laura', 'Taylor', '1970-03-05'),
    (8, 'James', 'Moore', '1983-07-19'),
    (9, 'Olivia', 'Clark', '1992-10-10'),
    (10, 'Sophia', 'Lee', '2000-08-08');

INSERT INTO Addresses (AddressID, ContactID, AddressLine1, City, State, ZipCode, Country) VALUES
    (1, 1, '123 Elm St', 'New York', 'NY', 10001, 'USA'),
    (2, 2, '456 Oak Ave', 'New York', 'NY', 10001, 'USA'),
    (3, 3, '789 Pine Rd', 'Chicago', 'IL', 60601, 'USA'),
    (4, 4, '321 Maple Blvd', 'Houston', 'TX', 77001, 'USA'),
    (5, 5, '654 Cedar Lane', 'Phoenix', 'AZ', 85001, 'USA'),
    (6, 6, '987 Birch Way', 'Houston', 'TX', 77001, 'USA'),
    (7, 7, '147 Spruce Dr', 'San Antonio', 'TX', 78201, 'USA'),
    (8, 8, '258 Redwood Ct', 'Dallas', 'TX', 75201, 'USA'),
    (9, 9, '369 Willow Pl', 'Los Angeles', 'CA', 90001, 'USA'),
    (10, 10, '753 Poplar St', 'Los Angeles', 'CA', 90001, 'USA');

INSERT INTO PhoneNumbers (PhoneID,ContactID,PhoneNumber,Type) VALUES
    (1,1,'555-1234','Mobile'),
    (2,1,'555-5678','Work'),
    (3,2,'555-8765','Home'),
    (4,3,'555-4321','Mobile'),
    (5,4,'555-9101','Work'),
    (6,5,'555-1122','Mobile'),
    (7,6,'555-3344','Home'),
    (8,7,'555-5566','Mobile'),
    (9,8,'555-7788','Work'),
    (10,9,'555-9900','Mobile');

INSERT INTO EmailAddresses (EmailID,ContactID,Email,Type) VALUES
    (1,1,'john.doe@gmail.com','Personal'),
    (2,1,'john.doe@gmail.com','Work'),
    (3,2,'jane.smith@yahoo.com','Personal'),
    (4,3,'emily.johnson@hotmail.com','Personal'),
    (5,4,'michael.brown@outlook.com','Work'),
    (6,5,'sarah.davis@gmail.com','Personal'),
    (7,6,'david.wilson@yahoo.com','Personal'),
    (8,7,'laura.taylor@hotmail.com','Personal'),
    (9,8,'james.moore@outlook.com','Work'),
    (10,9,'olivia.clark@gmail.com','Personal'),
    (11,10,'sophia.lee@yahoo.com','Personal');

INSERT INTO ContactsGroup (GroupID,GroupName) VALUES
    (1,'Family'),
    (2,'Friends'),
    (3,'Work'),
    (4,'Neighbors'),
    (5,'School');

INSERT INTO ContactsGroupDetails (ContactID,GroupID) VALUES
    (1,1),
    (3,1),
    (5,1),
    (9,1),
    (2,2),
    (4,2),
    (8,2),
    (1,3),
    (2,3),
    (4,3),
    (6,3),
    (10,3),
    (3,4),
    (7,4),
    (5,5),
    (7,5);

-- SELECT FirstName, LastName, BirthDate
-- FROM Contacts
-- WHERE DATE(BirthDate) > DATE('1990-01-01')

-- SELECT PhoneNumbers.PhoneNumber, PhoneNumbers.ContactID
-- FROM PhoneNumbers
-- WHERE Type='Mobile'

-- SELECT ContactsGroup.GroupName, COUNT(ContactsGroupDetails.ContactID) AS ContactCount
-- FROM ContactsGroup
-- JOIN ContactsGroupDetails ON ContactsGroupDetails.GroupID=ContactsGroup.GroupID
-- GROUP BY ContactsGroup.GroupName

-- SELECT Contacts.FirstName, Contacts.LastName, COUNT(ContactsGroupDetails.ContactID) AS GroupCount
-- FROM Contacts
-- JOIN ContactsGroupDetails ON ContactsGroupDetails.ContactID=Contacts.ContactID
-- JOIN ContactsGroup ON ContactsGroup.GroupID=ContactsGroupDetails.GroupID
-- GROUP BY Contacts.ContactID,Contacts.FirstName,Contacts.LastName
-- HAVING GroupCount >1
-- ORDER BY GroupCount DESC

-- SELECT Contacts.FirstName, Contacts.LastName, PhoneNumbers.PhoneNumber, EmailAddresses.Email
-- FROM Contacts
-- JOIN PhoneNumbers ON PhoneNumbers.ContactID=Contacts.ContactID
-- JOIN EmailAddresses ON EmailAddresses.ContactID=Contacts.ContactID
-- JOIN ContactsGroupDetails ON ContactsGroupDetails.ContactID=Contacts.ContactID
-- JOIN ContactsGroup ON ContactsGroup.GroupID=ContactsGroupDetails.GroupID
-- WHERE ContactsGroup.GroupName='Friends'

-- SELECT Addresses.City, COUNT(Addresses.ContactID) AS ContactCount
-- FROM Addresses
-- GROUP BY Addresses.City 
-- HAVING ContactCount >1
-- ORDER BY City ASC

SELECT Contacts.FirstName, Contacts.LastName, PhoneNumbers.PhoneNumber
FROM Contacts
LEFT JOIN PhoneNumbers ON PhoneNumbers.ContactID=Contacts.ContactID
WHERE PhoneNumbers.PhoneNumber IS NULL

-- SELECT Contacts.FirstName, Contacts.LastName, EmailAddresses.Email, EmailAddresses.Type
-- FROM Contacts
-- JOIN EmailAddresses ON EmailAddresses.ContactID=Contacts.ContactID
-- WHERE EmailAddresses.Email IN (SELECT Email
-- FROM EmailAddresses
-- WHERE Type='Personal' AND Email IN (SELECT Email
-- FROM EmailAddresses WHERE Type='Work'))
-- ORDER BY Contacts.ContactID

-- SELECT Email
-- FROM EmailAddresses
-- WHERE Type='Personal' AND Email IN (SELECT Email
-- FROM EmailAddresses WHERE Type='Work')

-- CREATE VIEW Work AS SELECT Email
-- FROM EmailAddresses WHERE Type='Work';
-- SELECT * FROM Work;
-- CREATE VIEW Personal AS SELECT Email
-- FROM EmailAddresses WHERE Type='Personal';
-- SELECT * FROM Personal;
-- SELECT * FROM Personal JOIN Work WHERE Personal.Email = Work.Email;