-- Create Characters table
CREATE TABLE Characters ( 
    CharacterID INT AUTO_INCREMENT PRIMARY KEY, 
    Name VARCHAR(100) NOT NULL, 
    Alias VARCHAR(100), 
    FirstAppearance DATE NOT NULL
);

-- Insert sample data into Characters
INSERT INTO Characters (Name, Alias, FirstAppearance) VALUES
('Bruce Wayne', 'Batman', '1939-05-01'),
('Clark Kent', 'Superman', '1938-06-01'),
('Diana Prince', 'Wonder Woman', '1941-12-01'),
('Barry Allen', 'The Flash', '1956-10-01'),
('Hal Jordan', 'Green Lantern', '1959-10-01');

-- Create Comics table
CREATE TABLE Comics( 
    ComicID INT AUTO_INCREMENT PRIMARY KEY, 
    Title VARCHAR(150) NOT NULL, 
    IssueNumber INT NOT NULL, 
    ReleaseDate DATE NOT NULL
);

-- Insert sample data into Comics
INSERT INTO Comics (Title, IssueNumber, ReleaseDate) VALUES
('Detective Comics', 27, '1939-05-01'),
('Action Comics', 1, '1938-06-01'),
('All Star Comics', 8, '1941-12-01'),
('Showcase', 4, '1956-10-01'),
('Green Lantern', 1, '1959-10-01'),
('Justice League', 1, '1960-03-01');

-- Create Appearances table
CREATE TABLE Appearances ( 
    AppearanceID INT AUTO_INCREMENT PRIMARY KEY, 
    ComicID INT NOT NULL, 
    CharacterID INT NOT NULL, 
    Role VARCHAR(50), 
    FOREIGN KEY (ComicID) REFERENCES Comics(ComicID), 
    FOREIGN KEY (CharacterID) REFERENCES Characters(CharacterID)
);

-- Insert sample data into Appearances
INSERT INTO Appearances (ComicID, CharacterID, Role) VALUES
(1, 1, 'Main'),
(2, 2, 'Main'),
(3, 3, 'Main'),
(4, 4, 'Main'),
(5, 5, 'Main'),
(6, 1, 'Team'),
(6, 2, 'Team'),
(6, 3, 'Team'),
(6, 4, 'Team'),
(6, 5, 'Team');

-- Total number of comics in the comics table 
SELECT COUNT(*) TotalComics 
FROM Comics; 

-- Find the average issue of all comics 
SELECT AVG(IssueNumber) AS AverageIssue 
FROM Comics; 

-- Show the number of different roles in the appearances table 
SELECT COUNT(Role) AS DifferentRoles 
FROM Appearances; 

-- Show the earliest, latest and average release year of comics 
SELECT 
    MAX(YEAR(ReleaseDate)) AS LatestYear, 
    MIN(YEAR(ReleaseDate)) AS EarliestYear, 
    AVG(YEAR(ReleaseDate)) AS AverageYear 
FROM Comics; 


-- Find the number of characters who appeared in more then one comic veg verions 
SELECT CharacterID, COUNT(*) AS MostAppearances 
FROM Appearances 
GROUP BY CharacterID 
ORDER BY MostAppearances;

-- Find the number of characters who appeared in more then one comic 
SELECT COUNT(DISTINCT CharacterID) AS MostAppearances 
FROM Appearances 
GROUP BY CharacterID 
HAVING COUNT(DISTINCT ComicID) > 1; 


-- Show the number of appearances for each role 
SELECT Role, COUNT(*) AS MostAppearancses 
FROM Appearances 
GROUP BY Role;

-- Find the number of comics released before 1960
SELECT COUNT(*) AS ComicsBefore1960
FROM Comics 
WHERE YEAR (ReleaseDate) < 1960; 

-- DISTINCT REMOVES DUPLICATES

-- Show the number of characters who appeareed in comics in 1960 
SELECT COUNT(DISTINCT CharacterID) AS CharactersIn1960 
FROM Appearances 
JOIN Comics ON Appearances.ComicID = Comics.ComicID 
WHERE YEAR(Comics.ReleaseDate) = 1960;