CREATE TABLE Students( 
    StudentID SMALLINT AUTO_INCREMENT PRIMARY KEY, 
    FirstName VARCHAR(50), 
    LastName VARCHAR(50), 
    Email VARCHAR(50) UNIQUE, 
    Age SMALLINT CHECK(AGE > 0)
    ); 

CREATE TABLE ArtClasses( 
    ClassID SMALLINT AUTO_INCREMENT PRIMARY KEY, 
    Title VARCHAR(50), 
    Medium VARCHAR(50),
    InstructorID INT
    ); 

CREATE TABLE Instructors( 
    InstructorID SMALLINT AUTO_INCREMENT PRIMARY KEY, 
    FirstName VARCHAR(50), 
    LastName VARCHAR(50), 
    Specialty VARCHAR(50),
    YearsExperience VARCHAR(50)
    ); 


CREATE TABLE Enrollments( 
    EnrollmentID SMALLINT AUTO_INCREMENT PRIMARY KEY, 
    StudentID SMALLINT, 
    ClassID SMALLINT, 
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID), 
    FOREIGN KEY(ClassID) REFERENCES ArtClasses(ClassID)
    ); 


CREATE TABLE PaintingClasses( 
    ClassID SMALLINT AUTO_INCREMENT PRIMARY KEY, 
    Title VARCHAR(50),
    Medium VARCHAR(50), 
    InstructorID SMALLINT, 
    Level VARCHAR(50), 
    FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
    ); 

CREATE TABLE SculptureClasses( 
    ClassID SMALLINT AUTO_INCREMENT PRIMARY KEY, 
    Title VARCHAR(50),
    Material VARCHAR(50), 
    InstructorID SMALLINT, 
    DurationHours SMALLINT, 
    FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
    ); 

CREATE TABLE DigitalArtClasses ( 
    ClassID SMALLINT AUTO_INCREMENT PRIMARY KEY, 
    Title VARCHAR(50), 
    Softwareused VARCHAR(50), 
    DeviceRequirement VARCHAR(50), 
    InstructorID SMALLINT, 
    FOREIGN KEY(InstructorID) REFERENCES Instructors(InstructorID)
    ); 



INSERT INTO Students (FirstName, LastName, Email, Age) VALUES
('Alice', 'Nguyen', 'alice.nguyen@gmail.com', 22),
('Brian', 'Lee', 'brian.lee@gmail.com', 28),
('Carla', 'Mendez', 'carla.mendez@gmail.com', 19),
('David', 'Kim', 'david.kim@gmail.com', 25),
('Ella', 'Roberts', 'ella.roberts@gmail.com', 21),
('Jamal', 'Harris', 'jamal.harris@gmail.com', 26),
('Priya', 'Singh', 'priya.singh@gmail.com', 23),
('Leo', 'Martinez', 'leo.martinez@gmail.com', 20);

INSERT INTO Instructors (FirstName, LastName, Specialty, YearsExperience) VALUES
('Emma', 'Stone', 'Painting', 10),
('Liam', 'Turner', 'Sculpture', 8),
('Nina', 'Patel', 'Digital Art', 6),
('Carlos', 'Ramirez', 'Painting', 12),
('Sophie', 'Chen', 'Sculpture', 7),
('Marcus', 'Brown', 'Digital Art', 9);

INSERT INTO ArtClasses (Title, Medium, InstructorID) VALUES
('Acrylic Basics', 'Acrylic', 1),
('Clay Sculpting', 'Clay', 2),
('Digital Illustration', 'Digital', 3),
('Oil Mastery', 'Oil', 1),
('Portrait Painting', 'Oil', 4),
('Mixed Media Sculpture', 'Mixed', 5),
('3D Modeling', 'Digital', 6),
('Watercolor Florals', 'Watercolor', 4);

INSERT INTO Enrollments (StudentID, ClassID) VALUES
(1, 1),
(2, 2),
(3, 3),
(4, 1),
(1, 4),
(2, 3),
(5, 5),
(6, 6),
(7, 7),
(8, 8),
(5, 1),
(6, 3),
(7, 2),
(8, 4);

INSERT INTO PaintingClasses (Title, Medium, InstructorID, Level) VALUES
('Acrylic Basics', 'Acrylic', 1, 'Beginner'),
('Oil Mastery', 'Oil', 1, 'Advanced'),
('Portrait Painting', 'Oil', 4, 'Intermediate'),
('Watercolor Florals', 'Watercolor', 4, 'Beginner');

INSERT INTO SculptureClasses (Title, Material, InstructorID, DurationHours) VALUES
('Clay Sculpting', 'Clay', 2, 3),
('Stone Carving', 'Stone', 2, 5),
('Mixed Media Sculpture', 'Mixed', 5, 4),
('Metal Works', 'Metal', 5, 6);

INSERT INTO DigitalArtClasses (Title, SoftwareUsed, InstructorID, DeviceRequirement) VALUES
('Digital Illustration', 'Procreate', 3, 'iPad'),
('Photoshop Mastery', 'Photoshop', 3, 'Laptop'),
('3D Modeling', 'Blender', 6, 'Laptop'),
('Digital Collage', 'Canva', 6, 'Tablet');

SELECT ArtClasses.Title , Instructors.FirstName
FROM ArtClasses
INNER JOIN Instructors ON ArtClasses.InstructorID = Instructors.InstructorID; 

SELECT Students.FirstName , Enrollments.ClassID 
FROM Students 
LEFT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID; 

 
SELECT DigitalArtClasses.Title , DigitalArtClasses.Softwareused , Instructors.Specialty
FROM DigitalArtClasses
INNER JOIN Instructors ON DigitalArtClasses.InstructorID = Instructors.InstructorID;