CREATE TABLE teacher (
teacher_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
age INTEGER,
email TEXT,
phone TEXT,
address TEXT, -- Address information
city TEXT,
state TEXT,
country TEXT, -- Country information
zip_code TEXT,
teaches TEXT, -- Comma-separated list of instrument_type_id that this teacher can teach
available_days TEXT,
available_time_start TEXT,
available_time_end TEXT,
gender TEXT, -- 'Male', 'Female', 'Other'
biography TEXT,
qualifications TEXT,
years_of_experience INTEGER,
rating REAL, -- 1 through 5
profile_picture BLOB,
status TEXT -- 'Active', 'Inactive', 'On Leave'
);
CREATE TABLE student (
student_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
age INTEGER,
date_of_birth TEXT,
email TEXT,
phone TEXT,
address TEXT,
city TEXT,
state TEXT,
country TEXT, --
zip_code TEXT,
has_instruments TEXT, -- list of instrument_type_id for instruments. Multiple instruments separated by commas
available_days TEXT,
available_time_start TEXT,
available_time_end TEXT,
gender TEXT, -- 'Male', 'Female', 'Other'
parent_guardian_name TEXT,
parent_guardian_phone TEXT,
emergency_contact_name TEXT, -- often times the parent_guardian_name, but perhaps not
emergency_contact_phone TEXT, -- often times the parent_guardian_phone, but perhaps not
student_status TEXT -- 'Active', 'Inactive', 'On Leave'
);
CREATE TABLE scheduled_lesson (
lesson_id INTEGER PRIMARY KEY AUTOINCREMENT,
student_id INTEGER,
teacher_id INTEGER,
instrument_type_id INTEGER,
lesson_day TEXT,
lesson_time TEXT,
lesson_length INTEGER,
lesson_cost REAL,
payment_status TEXT, -- 'Paid', 'Unpaid', 'Partial', 'Pending'
payment_date TEXT, -- null if not paid yet
lesson_status TEXT, -- 'Confirmed', 'Cancelled', 'Rescheduled'
lesson_notes TEXT, -- Any notes about the lesson
instrument_level TEXT, -- level of the lesson
lesson_focus TEXT, -- Technique, Theory, Performance, etc.
pieces_practiced TEXT, -- Comma-separated list of pieces the student is practicing
scales_practiced TEXT, -- Comma-separated list of scales the student is practicing
exercises_assigned TEXT, -- Any specific exercises assigned by the teacher
FOREIGN KEY (student_id) REFERENCES student(student_id),
FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id),
FOREIGN KEY (instrument_type_id) REFERENCES instrument_type(instrument_type_id)
);
CREATE TABLE instrument_type (
instrument_type_id INTEGER PRIMARY KEY AUTOINCREMENT,
type TEXT, -- Instrument type
description TEXT, -- Description of the instrument
image_url TEXT, -- URL to an image of the instrument
family TEXT -- Instrument family (e.g., Strings, Brass, Woodwinds, Percussion)
);
CREATE TABLE rental_instrument (
rental_id INTEGER PRIMARY KEY AUTOINCREMENT,
renter_id INTEGER,
instrument_type_id INTEGER,
brand TEXT,
model TEXT,
purchased_date TEXT,
rented BOOLEAN,
condition TEXT, -- 'New', 'Great', 'Good', ... 'Poor', etc.
FOREIGN KEY (renter_id) REFERENCES student(student_id)
FOREIGN KEY (instrument_type_id) REFERENCES instrument_type(instrument_type_id)
);
CREATE TABLE recital (
recital_id INTEGER PRIMARY KEY AUTOINCREMENT,
datetime TEXT,
name TEXT,
program TEXT,
performing_students TEXT -- comma separated list of student_ids
);
INSERT INTO teacher (name, age, email, phone, address, city, state, country, zip_code, teaches, available_days, available_time_start, available_time_end, gender, biography, qualifications, years_of_experience, rating, status)
VALUES
('John Smith', 35, 'john@example.com', '+1 (123) 456-7890', '123 Main St', 'Anytown', 'CA', 'USA', '12345', '1,2,3', 'Mon,Wed,Fri', '9:00 AM', '5:00 PM', 'Male', 'John is an experienced music teacher specializing in piano, guitar, and violin. He has a Bachelor of Music degree and has been teaching for over 10 years.', 'Bachelor of Music, Piano Performance', 12, 4.8, 'Active'),
('Jane Doe', 28, 'jane@example.com', '+1 (987) 654-3210', '456 Pine St', 'Anytown', 'NY', 'USA', '12345', '4,5,9', 'Tue,Thu', '10:00 AM', '6:00 PM', 'Female', 'Jane is a passionate drum and percussion instructor. She has a Master of Music degree and has been teaching for 5 years.', 'Master of Music, Percussion Performance', 5, 4.6, 'Active'),
('Robert Johnson', 42, 'robert@example.com', '+1 (555) 123-4567', '789 Oak St', 'Someville', 'TX', 'USA', '67890', '6,7,8', 'Mon,Wed', '8:00 AM', '4:00 PM', 'Male', 'Robert is a woodwind specialist with over 20 years of teaching experience. He can teach saxophone, flute, and clarinet.', 'Doctor of Musical Arts, Woodwind Performance', 20, 4.9, 'Active'),
('Emily Brown', 30, 'emily@example.com', '+1 (555) 555-5555', '321 Maple St', 'Yourtown', 'IL', 'USA', '78901', '1,4,5', 'Tue,Thu,Sat', '9:30 AM', '7:00 PM', 'Female', 'Emily is a versatile teacher offering piano, drum, and guitar lessons. She has a Bachelor of Music Education degree.', 'Bachelor of Music Education', 7, 4.7, 'Active'),
('Michael Davis', 50, 'michael@example.com', '+1 (111) 222-3333', '987 Elm St', 'Overtown', 'FL', 'USA', '11111', '3,6,8', 'Mon,Wed,Fri', '10:30 AM', '6:30 PM', 'Male', 'Michael is an experienced string instructor, teaching violin, cello, and bass guitar. He has a Master of Music Education degree.', 'Master of Music Education', 15, 4.5, 'Active'),
('Sophia Garcia', 26, 'sophia@example.com', '+1 (999) 888-7777', '567 Birch St', 'Underhill', 'CA', 'USA', '22222', '2,5,9', 'Tue,Thu', '11:00 AM', '8:00 PM', 'Female', 'Sophia specializes in guitar and percussion lessons. She has a Bachelor of Arts in Music and is passionate about teaching.', 'Bachelor of Arts in Music', 3, 4.3, 'Active'),
('William Chen', 48, 'william@example.com', '+1 (444) 555-6666', '678 Spruce St', 'Hillside', 'WA', 'USA', '33333', '7,8,9', 'Mon,Wed,Fri', '8:30 AM', '5:30 PM', 'Male', 'William is an expert in woodwind and brass instruments, offering saxophone, trumpet, and clarinet lessons. He has a Master of Music degree.', 'Master of Music, Woodwind and Brass Performance', 18, 4.6, 'Active'),
('Amanda Wilson', 33, 'amanda@example.com', '+1 (666) 777-8888', '890 Poplar St', 'Riverbank', 'OR', 'USA', '44444', '1,3,6', 'Tue,Thu', '9:00 AM', '7:30 PM', 'Female', 'Amanda teaches piano, violin, and cello. She has a Bachelor of Music degree and has been teaching for 10 years.', 'Bachelor of Music, Piano and String Performance', 10, 4.7, 'Active'),
('David Lee', 24, 'david@example.com', '+1 (222) 333-4444', '777 Chestnut St', 'Greenfield', 'GA', 'USA', '55555', '4,5,7', 'Mon,Wed,Fri', '10:00 AM', '6:00 PM', 'Male', 'David is a talented drum and woodwind instructor, offering lessons in drums, saxophone, and flute. He has a Bachelor of Music degree.', 'Bachelor of Music, Percussion and Woodwind Performance', 2, 4.2, 'Active'),
('Rachel Moore', 38, 'rachel@example.com', '+1 (888) 999-0000', '666 Willow St', 'Bluelake', 'TX', 'USA', '66666', '2,8,9', 'Tue,Thu', '8:30 AM', '5:30 PM', 'Female', 'Rachel teaches guitar, trumpet, and clarinet. She has a Master of Music Education degree and over 15 years of teaching experience.', 'Master of Music Education', 15, 4.8, 'Active'),
('Benjamin Parker', 45, 'benjamin@example.com', '+1 (333) 444-5555', '555 Birch St', 'Sunnydale', 'NY', 'USA', '77777', '1,5,6', 'Mon,Wed,Fri', '9:00 AM', '7:00 PM', 'Male', 'Benjamin is a versatile teacher offering piano, trumpet, and violin lessons. He has a Bachelor of Music degree.', 'Bachelor of Music, Piano and Trumpet Performance', 12, 4.6, 'Active'),
('Olivia Martinez', 29, 'olivia@example.com', '+1 (777) 888-9999', '444 Oak St', 'Hillcrest', 'IL', 'USA', '88888', '3,4,9', 'Tue,Thu', '10:00 AM', '3:00 PM', 'Female', 'Olivia specializes in violin and drum lessons. She has a Bachelor of Music Education degree and is passionate about teaching young students.', 'Bachelor of Music Education', 4, 4.4, 'Active'),
('Ethan Allen', 36, 'ethan@example.com', '+1 (666) 666-6666', '333 Maple St', 'Brookside', 'CA', 'USA', '99999', '2,7,8', 'Mon,Wed,Fri', '8:30 AM', '4:30 PM', 'Male', 'Ethan is an experienced guitar, saxophone, and clarinet instructor. He has a Master of Music degree.', 'Master of Music, Guitar and Woodwind Performance', 10, 4.5, 'Active'),
('Sophia Anderson', 27, 'sophia.a@example.com', '+1 (555) 555-0000', '222 Elm St', 'Greenview', 'WA', 'USA', '00000', '1,6,9', 'Tue,Thu', '9:00 AM', '6:00 PM', 'Female', 'Sophia teaches piano, cello, and trumpet. She has a Bachelor of Music Education degree.', 'Bachelor of Music Education', 5, 4.2, 'Active'),
('Justin Bailey', 40, 'justin@example.com', '+1 (444) 444-4444', '111 Pine St', 'Riverside', 'FL', 'USA', '11111', '3,5,7', 'Mon,Wed,Fri', '8:00 AM', '5:00 PM', 'Male', 'Justin is a violin, drum, and flute instructor. He has a Master of Music Education degree and over 15 years of experience.', 'Master of Music Education', 15, 4.7, 'Active'),
('Liam Miller', 31, 'liam@example.com', '+1 (777) 777-7777', '999 Oak St', 'Valleyview', 'TX', 'USA', '22222', '4,8,9', 'Tue,Thu', '10:30 AM', '7:30 PM', 'Male', 'Liam teaches guitar, trumpet, and saxophone. He has a Bachelor of Music degree and a passion for teaching.', 'Bachelor of Music, Guitar Performance', 6, 4.4, 'Active'),
('Emma Thompson', 25, 'emma@example.com', '+1 (666) 666-1111', '888 Spruce St', 'Brookhaven', 'NY', 'USA', '33333', '2,5,6', 'Mon,Wed,Fri', '9:00 AM', '6:30 PM', 'Female', 'Emma specializes in guitar and brass lessons, offering instruction in guitar, trumpet, and clarinet. She has a Bachelor of Music degree.', 'Bachelor of Music, Guitar and Brass Performance', 3, 4.1, 'Active'),
('Noah Wilson', 44, 'noah@example.com', '+1 (555) 111-2222', '777 Poplar St', 'Blueridge', 'IL', 'USA', '44444', '1,7,8', 'Tue,Thu', '8:30 AM', '5:30 PM', 'Male', 'Noah is an experienced piano, saxophone, and flute instructor. He has a Master of Music degree and over 20 years of teaching experience.', 'Master of Music, Piano and Woodwind Performance', 20, 4.8, 'Active'),
('Isabella Martinez', 37, 'isabella@example.com', '+1 (444) 444-1111', '666 Chestnut St', 'Hilltop', 'CA', 'USA', '55555', '3,4,5', 'Mon,Wed,Fri', '9:00 AM', '7:00 PM', 'Female', 'Isabella teaches violin, drums, and guitar. She has a Bachelor of Music Education degree and a passion for teaching young students.', 'Bachelor of Music Education', 12, 4.6, 'Active'),
('Oliver Davis', 28, 'oliver@example.com', '+1 (333) 333-3333', '555 Birch St', 'Greenside', 'WA', 'USA', '66666', '6,9,10', 'Tue,Thu', '10:00 AM', '3:00 PM', 'Male', 'Oliver specializes in cello and bass guitar lessons. He has a Bachelor of Music degree and is an experienced instructor.', 'Bachelor of Music, Cello Performance', 4, 4.3, 'Active'),
('Charlotte Wilson', 46, 'charlotte@example.com', '+1 (222) 222-2222', '444 Willow St', 'Riverside', 'TX', 'USA', '77777', '1,2,8', 'Mon,Wed,Fri', '8:30 AM', '5:30 PM', 'Female', 'Charlotte is an experienced piano and string instructor, offering lessons in piano, violin, and cello. She has a Master of Music Education degree.', 'Master of Music Education', 18, 4.7, 'Active');
INSERT INTO student (name, age, date_of_birth, email, phone, address, city, state, country, zip_code, has_instruments, available_days, available_time_start, available_time_end, gender, parent_guardian_name, parent_guardian_phone, emergency_contact_name, emergency_contact_phone, student_status)
VALUES
('Emma Johnson', 12, '2011-03-15', 'emma.j@example.com', '+1 (123) 456-7890', '123 Main St', 'Anytown', 'CA', 'USA', '12345', '1,4', 'Mon,Wed,Fri', '3:30 PM', '6:00 PM', 'Female', 'Sarah Johnson', '+1 (123) 987-6543', 'Sarah Johnson', '+1 (123) 987-6543', 'Active'),
('Ethan Miller', 14, '2009-09-22', 'ethan.m@example.com', '+1 (987) 654-3210', '456 Pine St', 'Anytown', 'NY', 'USA', '12345', '2,5', 'Tue,Thu', '4:00 PM', '7:30 PM', 'Male', 'Jennifer Miller', '+1 (987) 555-1234', 'Jennifer Miller', '+1 (987) 555-1234', 'Active'),
('Sophia Lee', 10, '2013-11-08', 'sophia.l@example.com', '+1 (555) 123-4567', '789 Oak St', 'Someville', 'TX', 'USA', '67890', '3,6', 'Mon,Wed', '3:00 PM', '6:30 PM', 'Female', 'Amanda Lee', '+1 (555) 555-5678', 'David Lee', '+1 (555) 123-4567', 'Active'),
('Liam Brown', 13, '2010-05-02', 'liam.b@example.com', '+1 (555) 555-5555', '321 Maple St', 'Yourtown', 'IL', 'USA', '78901', '1,7', 'Tue,Thu,Sat', '4:30 PM', '7:00 PM', 'Male', 'Emily Brown', '+1 (555) 555-5432', 'Emily Brown', '+1 (555) 555-5555', 'Active'),
('Isabella Davis', 11, '2012-02-10', 'isabella.d@example.com', '+1 (111) 222-3333', '987 Elm St', 'Overtown', 'FL', 'USA', '11111', '4,8', 'Mon,Wed,Fri', '3:00 PM', '6:30 PM', 'Female', 'Michael Davis', '+1 (111) 222-3332', 'Michael Davis', '+1 (111) 222-3333', 'Active'),
('Benjamin Garcia', 12, '2011-07-30', 'benjamin.g@example.com', '+1 (999) 888-7777', '567 Birch St', 'Underhill', 'CA', 'USA', '22222', '2,9', 'Tue,Thu', '3:30 PM', '7:30 PM', 'Male', 'Sophia Garcia', '+1 (999) 888-7776', 'Sophia Garcia', '+1 (999) 888-7777', 'Active'),
('Olivia Chen', 14, '2009-01-15', 'olivia.c@example.com', '+1 (444) 555-6666', '678 Spruce St', 'Hillside', 'WA', 'USA', '33333', '5,10', 'Mon,Wed,Fri', '4:00 PM', '7:00 PM', 'Female', 'William Chen', '+1 (444) 555-6665', 'William Chen', '+1 (444) 555-6666', 'Active'),
('William Wilson', 13, '2010-08-25', 'william.w@example.com', '+1 (666) 777-8888', '890 Poplar St', 'Riverbank', 'OR', 'USA', '44444', '3,7', 'Tue,Thu', '3:00 PM', '6:30 PM', 'Male', 'Amanda Wilson', '+1 (666) 777-8887', 'Amanda Wilson', '+1 (666) 777-8888', 'Active'),
('Emma Parker', 11, '2012-04-18', 'emma.p@example.com', '+1 (222) 333-4444', '777 Chestnut St', 'Greenfield', 'GA', 'USA', '55555', '1,6', 'Mon,Wed,Fri', '3:30 PM', '6:00 PM', 'Female', 'Benjamin Parker', '+1 (222) 333-4443', 'Benjamin Parker', '+1 (222) 333-4444', 'Active'),
('David Anderson', 14, '2009-11-05', 'david.a@example.com', '+1 (888) 999-0000', '666 Willow St', 'Bluelake', 'TX', 'USA', '66666', '2,5', 'Tue,Thu', '4:00 PM', '7:30 PM', 'Male', 'Rachel Anderson', '+1 (888) 999-0009', 'Rachel Anderson', '+1 (888) 999-0000', 'Active'),
('Sophia Thompson', 12, '2011-06-20', 'sophia.t@example.com', '+1 (333) 444-5555', '555 Birch St', 'Sunnydale', 'NY', 'USA', '77777', '3,8', 'Mon,Wed,Fri', '3:00 PM', '6:30 PM', 'Female', 'Emma Thompson', '+1 (333) 444-5554', 'Emma Thompson', '+1 (333) 444-5555', 'Active'),
('Ethan Bailey', 13, '2010-12-03', 'ethan.b@example.com', '+1 (777) 888-9999', '444 Oak St', 'Hillcrest', 'IL', 'USA', '88888', '4,9', 'Tue,Thu', '4:30 PM', '7:00 PM', 'Male', 'Justin Bailey', '+1 (777) 888-9998', 'Justin Bailey', '+1 (777) 888-9999', 'Active'),
('Liam Martinez', 11, '2012-08-12', 'liam.m@example.com', '+1 (666) 666-6666', '333 Maple St', 'Brookside', 'CA', 'USA', '99999', '1,10', 'Mon,Wed,Fri', '3:30 PM', '6:00 PM', 'Male', 'Isabella Martinez', '+1 (666) 666-6665', 'Isabella Martinez', '+1 (666) 666-6666', 'Active'),
('Isabella Allen', 14, '2009-03-28', 'isabella.a@example.com', '+1 (555) 555-0000', '222 Elm St', 'Greenview', 'WA', 'USA', '00000', '2,7', 'Tue,Thu', '4:00 PM', '7:30 PM', 'Female', 'Ethan Allen', '+1 (555) 555-0009', 'Ethan Allen', '+1 (555) 555-0000', 'Active'),
('Benjamin Wilson', 13, '2010-10-10', 'benjamin.w@example.com', '+1 (444) 444-4444', '111 Pine St', 'Riverside', 'FL', 'USA', '11111', '3,6', 'Mon,Wed,Fri', '3:00 PM', '6:30 PM', 'Male', 'Charlotte Wilson', '+1 (444) 444-4443', 'Charlotte Wilson', '+1 (444) 444-4444', 'Active'),
('Emma Davis', 12, '2011-05-05', 'emma.d@example.com', '+1 (333) 333-3333', '999 Oak St', 'Valleyview', 'TX', 'USA', '22222', '4,9', 'Tue,Thu', '4:30 PM', '7:00 PM', 'Female', 'Michael Davis', '+1 (333) 333-3332', 'Michael Davis', '+1 (333) 333-3333', 'Active'),
('William Miller', 14, '2009-01-20', 'william.m@example.com', '+1 (222) 222-2222', '888 Spruce St', 'Brookhaven', 'NY', 'USA', '33333', '1,5', 'Mon,Wed,Fri', '3:30 PM', '6:00 PM', 'Male', 'Jennifer Miller', '+1 (222) 222-2221', 'Jennifer Miller', '+1 (222) 222-2222', 'Active'),
('Sophia Johnson', 11, '2012-09-15', 'sophia.j@example.com', '+1 (111) 111-1111', '777 Poplar St', 'Blueridge', 'IL', 'USA', '44444', '2,7', 'Tue,Thu', '4:00 PM', '7:30 PM', 'Female', 'Sarah Johnson', '+1 (111) 111-1110', 'Sarah Johnson', '+1 (111) 111-1111', 'Active'),
('Ethan Wilson', 13, '2010-04-30', 'ethan.w@example.com', '+1 (999) 999-9999', '666 Chestnut St', 'Hilltop', 'CA', 'USA', '55555', '3,8', 'Mon,Wed,Fri', '3:00 PM', '6:30 PM', 'Male', 'Charlotte Wilson', '+1 (999) 999-9998', 'Charlotte Wilson', '+1 (999) 999-9999', 'Active'),
('Liam Thompson', 12, '2011-11-20', 'liam.t@example.com', '+1 (888) 888-8888', '555 Birch St', 'Greenside', 'WA', 'USA', '66666', '4,9', 'Tue,Thu', '4:30 PM', '7:00 PM', 'Male', 'Emma Thompson', '+1 (888) 888-8887', 'Emma Thompson', '+1 (888) 888-8888', 'Active'),
('Isabella Garcia', 14, '2009-06-10', 'isabella.g@example.com', '+1 (777) 777-7777', '444 Willow St', 'Riverside', 'TX', 'USA', '77777', '1,10', 'Mon,Wed,Fri', '3:00 PM', '6:30 PM', 'Female', 'Sophia Garcia', '+1 (777) 777-7776', 'Sophia Garcia', '+1 (777) 777-7777', 'Active'),
('Benjamin Anderson', 13, '2010-01-05', 'benjamin.a@example.com', '+1 (666) 666-6666', '333 Maple St', 'Hilltop View', 'FL', 'USA', '88888', '2,5', 'Tue,Thu', '4:00 PM', '7:30 PM', 'Male', 'Rachel Anderson', '+1 (666) 666-6665', 'Rachel Anderson', '+1 (666) 666-6666', 'Active'),
('Emma Bailey', 11, '2012-08-30', 'emma.ba@example.com', '+1 (555) 555-5555', '222 Elm St', 'Greenfield Valley', 'NY', 'USA', '99999', '3,6', 'Mon,Wed,Fri', '3:30 PM', '6:00 PM', 'Female', 'Justin Bailey', '+1 (555) 555-5554', 'Justin Bailey', '+1 (555) 555-5555', 'Active'),
('David Wilson', 14, '2009-03-15', 'david.w@example.com', '+1 (444) 444-4444', '111 Pine St', 'Riverside Park', 'IL', 'USA', '00000', '4,9', 'Tue,Thu', '4:00 PM', '7:30 PM', 'Male', 'Charlotte Wilson', '+1 (444) 444-4443', 'Charlotte Wilson', '+1 (444) 444-4444', 'Active'),
('Sophia Miller', 12, '2011-10-25', 'sophia.m@example.com', '+1 (333) 333-3333', '999 Oak St', 'Valleybrook', 'TX', 'USA', '11111', '1,5', 'Mon,Wed,Fri', '3:00 PM', '6:30 PM', 'Female', 'Jennifer Miller', '+1 (333) 333-3332', 'Jennifer Miller', '+1 (333) 333-3333', 'Active'),
('Ethan Johnson', 13, '2010-05-12', 'ethan.j@example.com', '+1 (222) 222-2222', '888 Spruce St', 'Brookfield', 'NY', 'USA', '22222', '2,7', 'Tue,Thu', '4:30 PM', '7:00 PM', 'Male', 'Sarah Johnson', '+1 (222) 222-2221', 'Sarah Johnson', '+1 (222) 222-2222', 'Active'),
('Emma Wilson', 12, '2011-07-18', 'emma.w@example.com', '+1 (999) 999-9999', '888 Spruce St', 'Brookhaven', 'NY', 'USA', '33333', '1,6', 'Mon,Wed,Fri', '3:30 PM', '6:00 PM', 'Female', 'Charlotte Wilson', '+1 (999) 999-9998', 'Charlotte Wilson', '+1 (999) 999-9999', 'Active'),
('William Thompson', 13, '2010-02-20', 'william.t@example.com', '+1 (888) 888-8888', '777 Poplar St', 'Blueridge', 'IL', 'USA', '44444', '2,7', 'Tue,Thu', '4:00 PM', '7:30 PM', 'Male', 'Emma Thompson', '+1 (888) 888-8887', 'Emma Thompson', '+1 (888) 888-8888', 'Active'),
('Sophia Garcia', 14, '2009-09-10', 'sophia.g@example.com', '+1 (777) 777-7777', '666 Chestnut St', 'Hilltop', 'CA', 'USA', '55555', '3,8', 'Mon,Wed,Fri', '3:00 PM', '6:30 PM', 'Female', 'Sophia Garcia', '+1 (777) 777-7776', 'Sophia Garcia', '+1 (777) 777-7777', 'Active'),
('Benjamin Anderson', 12, '2011-04-05', 'benjamin.a2@example.com', '+1 (666) 666-6666', '555 Birch St', 'Greenside', 'WA', 'USA', '66666', '4,9', 'Tue,Thu', '4:30 PM', '7:00 PM', 'Male', 'Rachel Anderson', '+1 (666) 666-6665', 'Rachel Anderson', '+1 (666) 666-6666', 'Active'),
('Olivia Bailey', 11, '2012-11-30', 'olivia.b@example.com', '+1 (555) 555-5555', '444 Oak St', 'Hillcrest', 'IL', 'USA', '88888', '1,10', 'Mon,Wed,Fri', '3:30 PM', '6:00 PM', 'Female', 'Justin Bailey', '+1 (555) 555-5554', 'Justin Bailey', '+1 (555) 555-5555', 'Active'),
('William Wilson', 14, '2009-06-15', 'william.w2@example.com', '+1 (444) 444-4444', '333 Maple St', 'Riverside', 'FL', 'USA', '77777', '2,5', 'Tue,Thu', '4:00 PM', '7:30 PM', 'Male', 'Charlotte Wilson', '+1 (444) 444-4443', 'Charlotte Wilson', '+1 (444) 444-4444', 'Active'),
('Emma Miller', 13, '2010-01-25', 'emma.m@example.com', '+1 (333) 333-3333', '222 Elm St', 'Greenfield', 'NY', 'USA', '99999', '3,6', 'Mon,Wed,Fri', '3:00 PM', '6:30 PM', 'Female', 'Jennifer Miller', '+1 (333) 333-3332', 'Jennifer Miller', '+1 (333) 333-3333', 'Active'),
('David Johnson', 12, '2011-08-12', 'david.j@example.com', '+1 (222) 222-2222', '111 Pine St', 'Brookside', 'CA', 'USA', '00000', '1,7', 'Tue,Thu', '4:30 PM', '7:00 PM', 'Male', 'Sarah Johnson', '+1 (222) 222-2221', 'Sarah Johnson', '+1 (222) 222-2222', 'Active'),
('Sophia Davis', 14, '2009-03-30', 'sophia.d@example.com', '+1 (111) 111-1111', '999 Oak St', 'Valleyview', 'TX', 'USA', '11111', '2,8', 'Mon,Wed,Fri', '3:00 PM', '6:30 PM', 'Female', 'Michael Davis', '+1 (111) 111-1110', 'Michael Davis', '+1 (111) 111-1111', 'Active'),
('Benjamin Wilson', 13, '2010-10-20', 'benjamin.w2@example.com', '+1 (999) 999-9999', '888 Spruce St', 'Brookhaven', 'NY', 'USA', '22222', '4,9', 'Tue,Thu', '4:30 PM', '7:00 PM', 'Male', 'Charlotte Wilson', '+1 (999) 999-9998', 'Charlotte Wilson', '+1 (999) 999-9999', 'Active'),
('Liam Thompson', 12, '2011-05-20', 'liam.t2@example.com', '+1 (888) 888-8888', '777 Poplar St', 'Blueridge', 'IL', 'USA', '33333', '1,10', 'Mon,Wed,Fri', '3:30 PM', '6:00 PM', 'Male', 'Emma Thompson', '+1 (888) 888-8887', 'Emma Thompson', '+1 (888) 888-8888', 'Active'),
('Isabella Garcia', 11, '2012-11-10', 'isabella.g2@example.com', '+1 (777) 777-7777', '666 Chestnut St', 'Hilltop', 'CA', 'USA', '44444', '3,5', 'Tue,Thu', '4:00 PM', '7:30 PM', 'Female', 'Sophia Garcia', '+1 (777) 777-7776', 'Sophia Garcia', '+1 (777) 777-7777', 'Active'),
('Ethan Anderson', 14, '2009-07-05', 'ethan.a@example.com', '+1 (666) 666-6666', '555 Birch St', 'Greenside', 'WA', 'USA', '55555', '2,7', 'Mon,Wed,Fri', '3:00 PM', '6:30 PM', 'Male', 'Rachel Anderson', '+1 (666) 666-6665', 'Rachel Anderson', '+1 (666) 666-6666', 'Active'),
('Liam Bailey', 13, '2010-01-30', 'liam.ba@example.com', '+1 (555) 555-5555', '444 Oak St', 'Hillcrest', 'IL', 'USA', '66666', '1,8', 'Tue,Thu', '4:30 PM', '7:00 PM', 'Male', 'Justin Bailey', '+1 (555) 555-5554', 'Justin Bailey', '+1 (555) 555-5555', 'Active'),
('Emma Wilson', 12, '2011-09-18', 'emma.w2@example.com', '+1 (444) 444-4444', '333 Maple St', 'Riverside', 'FL', 'USA', '77777', '3,9', 'Mon,Wed,Fri', '3:00 PM', '6:30 PM', 'Female', 'Charlotte Wilson', '+1 (444) 444-4443', 'Charlotte Wilson', '+1 (444) 444-4444', 'Active'),
('William Miller', 14, '2009-05-20', 'william.m2@example.com', '+1 (333) 333-3333', '222 Elm St', 'Greenfield', 'NY', 'USA', '88888', '4,10', 'Tue,Thu', '4:30 PM', '7:00 PM', 'Male', 'Jennifer Miller', '+1 (333) 333-3332', 'Jennifer Miller', '+1 (333) 333-3333', 'Active'),
('Sophia Johnson', 11, '2012-11-12', 'sophia.j2@example.com', '+1 (222) 222-2222', '111 Pine St', 'Brookside', 'CA', 'USA', '99999', '1,5', 'Mon,Wed,Fri', '3:30 PM', '6:00 PM', 'Female', 'Sarah Johnson', '+1 (222) 222-2221', 'Sarah Johnson', '+1 (222) 222-2222', 'Active'),
('Ethan Davis', 14, '2009-07-30', 'ethan.d@example.com', '+1 (111) 111-1111', '999 Oak St', 'Valleyview', 'TX', 'USA', '00000', '2,6', 'Tue,Thu', '4:00 PM', '7:30 PM', 'Male', 'Michael Davis', '+1 (111) 111-1110', 'Michael Davis', '+1 (111) 111-1111', 'Active'),
('Benjamin Wilson', 13, '2010-04-20', 'benjamin.w3@example.com', '+1 (999) 999-9999', '888 Spruce St', 'Brookhaven', 'NY', 'USA', '11111', '3,7', 'Mon,Wed,Fri', '3:00 PM', '6:30 PM', 'Male', 'Charlotte Wilson', '+1 (999) 999-9998', 'Charlotte Wilson', '+1 (999) 999-9999', 'Active'),
('Liam Thompson', 12, '2011-01-20', 'liam.t3@example.com', '+1 (888) 888-8888', '777 Poplar St', 'Blueridge', 'IL', 'USA', '22222', '4,8', 'Tue,Thu', '4:30 PM', '7:00 PM', 'Male', 'Emma Thompson', '+1 (888) 888-8887', 'Emma Thompson', '+1 (888) 888-8888', 'Active'),
('Isabella Garcia', 14, '2009-08-10', 'isabella.g3@example.com', '+1 (777) 777-7777', '666 Chestnut St', 'Hilltop', 'CA', 'USA', '33333', '1,9', 'Mon,Wed,Fri', '3:00 PM', '6:30 PM', 'Female', 'Sophia Garcia', '+1 (777) 777-7776', 'Sophia Garcia', '+1 (777) 777-7777', 'Active'),
('Benjamin Anderson', 13, '2010-03-05', 'benjamin.a3@example.com', '+1 (666) 666-6666', '555 Birch St', 'Greenside', 'WA', 'USA', '44444', '2,10', 'Tue,Thu', '4:30 PM', '7:00 PM', 'Male', 'Rachel Anderson', '+1 (666) 666-6665', 'Rachel Anderson', '+1 (666) 666-6666', 'Active');
INSERT INTO scheduled_lesson (student_id, teacher_id, instrument_type_id, lesson_day, lesson_time, lesson_length, lesson_cost, payment_status, payment_date, lesson_status, lesson_notes, instrument_level, lesson_focus, pieces_practiced, scales_practiced, exercises_assigned)
VALUES
(1, 1, 1, 'Mon', '4:00 PM', 1, 50.0, 'Paid', '2023-08-14', 'Confirmed', 'Lesson focused on technique and finger placement.', 'Beginner', 'Technique', 'Sonatina in C, Twinkle Twinkle Little Star', 'C Major, G Major', 'Finger exercises, scale practice'),
(1, 1, 1, 'Wed', '4:00 PM', 1, 50.0, 'Paid', '2023-08-21', 'Confirmed', 'Review of previous techniques.', 'Beginner', 'Review', 'Old MacDonald, Jingle Bells', 'A Minor, E Major', 'Scale review, finger dexterity exercises'),
(1, 1, 1, 'Fri', '4:00 PM', 1, 50.0, 'Paid', '2023-08-23', 'Confirmed', 'Introduction to new pieces.', 'Beginner', 'Repertoire Expansion', 'Mary had a Little Lamb, Ode to Joy', 'C Minor, D Major', 'New piece introduction, sight reading'),
(1, 1, 1, 'Fri', '4:00 PM', 1, 50.0, 'Paid', '2023-08-23', 'Confirmed', 'Introduction to new pieces.', 'Beginner', 'Repertoire Expansion', 'Mary had a Little Lamb, Ode to Joy', 'C Minor, D Major', 'New piece introduction, sight reading'),
(1, 1, 1, 'Fri', '4:00 PM', 1, 50.0, 'Paid', '2023-08-23', 'Confirmed', 'Introduction to new pieces.', 'Beginner', 'Repertoire Expansion', 'Mary had a Little Lamb, Ode to Joy', 'C Minor, D Major', 'New piece introduction, sight reading'),
(1, 1, 1, 'Fri', '4:00 PM', 1, 50.0, 'Paid', '2023-08-23', 'Confirmed', 'Introduction to new pieces.', 'Beginner', 'Repertoire Expansion', 'Mary had a Little Lamb, Ode to Joy', 'C Minor, D Major', 'New piece introduction, sight reading'),
(1, 1, 1, 'Fri', '4:00 PM', 1, 50.0, 'Paid', '2023-08-23', 'Confirmed', 'Introduction to new pieces.', 'Beginner', 'Repertoire Expansion', 'Mary had a Little Lamb, Ode to Joy', 'C Minor, D Major', 'New piece introduction, sight reading'),
(1, 1, 1, 'Fri', '4:00 PM', 1, 50.0, 'Paid', '2023-08-23', 'Confirmed', 'Introduction to new pieces.', 'Beginner', 'Repertoire Expansion', 'Mary had a Little Lamb, Ode to Joy', 'C Minor, D Major', 'New piece introduction, sight reading'),
(1, 1, 1, 'Fri', '4:00 PM', 1, 50.0, 'Paid', '2023-08-23', 'Confirmed', 'Introduction to new pieces.', 'Beginner', 'Repertoire Expansion', 'Mary had a Little Lamb, Ode to Joy', 'C Minor, D Major', 'New piece introduction, sight reading'),
(1, 1, 1, 'Fri', '4:00 PM', 1, 50.0, 'Paid', '2023-08-23', 'Confirmed', 'Introduction to new pieces.', 'Beginner', 'Repertoire Expansion', 'Mary had a Little Lamb, Ode to Joy', 'C Minor, D Major', 'New piece introduction, sight reading'),
(1, 1, 1, 'Fri', '4:00 PM', 1, 50.0, 'Paid', '2023-08-23', 'Confirmed', 'Introduction to new pieces.', 'Beginner', 'Repertoire Expansion', 'Mary had a Little Lamb, Ode to Joy', 'C Minor, D Major', 'New piece introduction, sight reading'),
(1, 1, 1, 'Mon', '4:00 PM', 1, 50.0, 'Paid', '2023-08-26', 'Confirmed', 'Focus on rhythm and timing.', 'Beginner', 'Rhythm', 'Baa Baa Black Sheep, Happy Birthday', 'B Flat Major, F Major', 'Metronome practice, rhythm exercises'),
(1, 1, 1, 'Wed', '4:00 PM', 1, 50.0, 'Paid', '2023-08-28', 'Confirmed', 'Enhancing musical expression.', 'Beginner', 'Expression', 'London Bridge, This Old Man', 'G Minor, A Flat Major', 'Dynamics control, expressive playing'),
(1, 1, 1, 'Fri', '4:00 PM', 1, 50.0, 'Paid', '2023-08-30', 'Confirmed', 'Preparation for recital.', 'Beginner', 'Performance Prep', 'Twinkle Twinkle, Yankee Doodle', 'E Flat Major, B Major', 'Performance techniques, stage presence'),
(1, 1, 1, 'Mon', '4:00 PM', 1, 50.0, 'Paid', '2023-09-02', 'Confirmed', 'Post-recital review.', 'Beginner', 'Review', 'Recital pieces', 'Recital keys', 'Post-performance analysis, corrective exercises'),
(1, 1, 1, 'Wed', '4:00 PM', 1, 50.0, 'Paid', '2023-09-04', 'Confirmed', 'Introduction to new scales.', 'Beginner', 'Technique', 'Scale practice', 'All Major Scales', 'Scale memorization, finger positioning'),
(1, 1, 1, 'Fri', '4:00 PM', 1, 50.0, 'Paid', '2023-09-06', 'Confirmed', 'Advanced fingering techniques.', 'Beginner', 'Technique', 'Arpeggios', 'C Major, G Major arpeggios', 'Arpeggio practice, speed exercises'),
(1, 1, 1, 'Mon', '4:00 PM', 1, 50.0, 'Paid', '2023-09-09', 'Confirmed', 'Musicality and phrasing.', 'Beginner', 'Musicality', 'Simple songs', 'Various keys', 'Phrasing techniques, musical expression'),
(1, 1, 1, 'Wed', '4:00 PM', 1, 50.0, 'Paid', '2023-09-11', 'Confirmed', 'Preparing for next level.', 'Beginner', 'Transition', 'Review of all learned pieces', 'Key signatures covered', 'Preparatory exercises for intermediate level'),
(2, 2, 4, 'Tue', '5:00 PM', 1.5, 75.0, 'Unpaid', NULL, 'Confirmed', 'Lesson focused on rhythm and stick control.', 'Intermediate', 'Rhythm and Control', 'Wipe Out, In the Mood', 'Common Drum Rhythms', 'Stick Control Exercises'),
(17, 3, 6, 'Wed', '3:30 PM', 1, 60.0, 'Partial', '2023-08-14', 'Confirmed', 'Lesson focused on tone production and breath control.', 'Beginner', 'Tone and Breath Control', 'Mary Had a Little Lamb, Hot Cross Buns', 'C Major, G Major', 'Long tone exercises, breathing exercises'),
(4, 4, 1, 'Thu', '4:30 PM', 1.5, 80.0, 'Pending', NULL, 'Confirmed', 'Lesson focused on chord progressions and strumming patterns.', 'Beginner', 'Chord Progressions and Strumming', 'Three Little Birds, Brown Eyed Girl', 'C Major, G Major', 'Chord progression exercises, strumming patterns'),
(5, 5, 3, 'Fri', '4:00 PM', 1, 65.0, 'Paid', '2023-08-14', 'Confirmed', 'Lesson focused on bow hold and basic techniques.', 'Beginner', 'Bow Hold and Techniques', 'Twinkle Twinkle Little Star, Lightly Row', 'D Major, A Major', 'Bow hold exercises, open string exercises'),
(6, 6, 4, 'Mon', '5:30 PM', 1.5, 70.0, 'Unpaid', NULL, 'Confirmed', 'Lesson focused on drum fills and dynamics.', 'Intermediate', 'Drum Fills and Dynamics', 'Good Times Bad Times, In the Air Tonight', 'Various', 'Drum fill exercises, dynamics exercises'),
(7, 7, 6, 'Tue', '4:00 PM', 1, 55.0, 'Partial', '2023-08-14', 'Confirmed', 'Lesson focused on articulation and phrasing.', 'Intermediate', 'Articulation and Phrasing', 'Minuet in G, Ode to Joy', 'G Major, C Major', 'Articulation exercises, phrasing exercises'),
(8, 8, 1, 'Wed', '6:00 PM', 1.5, 85.0, 'Pending', NULL, 'Confirmed', 'Lesson focused on advanced chord progressions and fingerpicking.', 'Advanced', 'Advanced Chord Progressions and Fingerpicking', 'Stairway to Heaven, Blackbird', 'Various', 'Chord progression analysis, fingerpicking patterns'),
(9, 9, 3, 'Thu', '5:30 PM', 1, 65.0, 'Paid', '2023-08-14', 'Confirmed', 'Lesson focused on vibrato and shifting.', 'Intermediate', 'Vibrato and Shifting', 'Bourree, Gavotte in G', 'D Major, A Major', 'Vibrato exercises, shifting exercises'),
(10, 8, 4, 'Fri', '6:30 PM', 1.5, 75.0, 'Unpaid', NULL, 'Confirmed', 'Lesson focused on advanced drum grooves and fills.', 'Advanced', 'Advanced Drum Grooves and Fills', 'Rosanna, Tom Sawyer', 'Various', 'Advanced groove analysis, fill exercises'),
(11, 11, 6, 'Mon', '4:30 PM', 1, 60.0, 'Partial', '2023-08-14', 'Confirmed', 'Lesson focused on advanced tone production and dynamics.', 'Advanced', 'Advanced Tone Production and Dynamics', 'Chopin Nocturne Op. 9 No. 2, Beethoven Sonata Pathetique', 'Various', 'Advanced tone exercises, dynamics exploration'),
(12, 12, 1, 'Tue', '6:30 PM', 1.5, 80.0, 'Pending', NULL, 'Confirmed', 'Lesson focused on improvisation and jazz chords.', 'Advanced', 'Improvisation and Jazz Chords', 'So What, Autumn Leaves', 'Various', 'Improvisation exercises, jazz chord analysis'),
(13, 13, 3, 'Wed', '5:00 PM', 1, 65.0, 'Paid', '2023-08-14', 'Confirmed', 'Lesson focused on advanced bowing techniques and styles.', 'Advanced', 'Advanced Bowing Techniques and Styles', 'Bach Cello Suite No. 1, Elgar Cello Concerto', 'Various', 'Advanced bowing exercises, style exploration'),
(33, 7, 4, 'Thu', '6:30 PM', 1.5, 75.0, 'Unpaid', NULL, 'Confirmed', 'Lesson focused on double bass drum techniques.', 'Advanced', 'Double Bass Drum Techniques', 'Yyz, Hot for Teacher', 'Various', 'Double bass drum exercises, advanced foot technique'),
(37, 15, 6, 'Fri', '5:30 PM', 1, 55.0, 'Partial', '2023-08-14', 'Confirmed', 'Lesson focused on advanced articulation and ornamentation.', 'Advanced', 'Advanced Articulation and Ornamentation', 'Mozart Flute Concerto in D, Bach Brandenburg Concerto No. 5', 'Various', 'Advanced articulation exercises, ornamentation exploration'),
(16, 9, 1, 'Mon', '4:30 PM', 1.5, 85.0, 'Pending', NULL, 'Confirmed', 'Lesson focused on advanced jazz piano techniques and improvisation.', 'Advanced', 'Advanced Jazz Piano and Improvisation', 'Take Five, Blue in Green', 'Various', 'Jazz piano techniques, advanced improvisation exercises'),
(16, 17, 3, 'Tue', '6:00 PM', 1, 65.0, 'Paid', '2023-08-14', 'Confirmed', 'Lesson focused on advanced violin techniques and performance practices.', 'Advanced', 'Advanced Violin Techniques and Performance', 'Paganini Caprice No. 24, Tchaikovsky Violin Concerto', 'Various', 'Advanced violin techniques, performance practices'),
(32, 18, 4, 'Wed', '5:30 PM', 1.5, 75.0, 'Unpaid', NULL, 'Confirmed', 'Lesson focused on advanced drum soloing and showmanship.', 'Advanced', 'Advanced Drum Soloing and Showmanship', 'Wipe Out, Moby Dick', 'Various', 'Advanced drum soloing techniques, showmanship exploration'),
(32, 19, 6, 'Thu', '4:30 PM', 1, 55.0, 'Partial', '2023-08-14', 'Confirmed', 'Lesson focused on extended techniques and experimental sounds.', 'Advanced', 'Extended Techniques and Experimental Sounds', 'Contemporary classical pieces', 'Various', 'Extended technique exploration, experimental soundscapes'),
(5, 20, 1, 'Fri', '6:30 PM', 1.5, 85.0, 'Pending', NULL, 'Confirmed', 'Lesson focused on advanced jazz piano trio performance and interaction.', 'Advanced', 'Advanced Jazz Piano Trio Performance', 'Autumn Leaves, All of Me', 'Various', 'Jazz piano trio performance practices, interaction exercises'),
(21, 21, 3, 'Mon', '5:00 PM', 1, 65.0, 'Paid', '2023-08-14', 'Confirmed', 'Lesson focused on advanced cello techniques and extended techniques.', 'Advanced', 'Advanced Cello Techniques and Extended Techniques', 'Kodaly Cello Sonata, Bach Cello Suite No. 5', 'Various', 'Advanced cello techniques, extended technique exploration'),
(22, 5, 4, 'Tue', '6:30 PM', 1.5, 75.0, 'Unpaid', NULL, 'Confirmed', 'Lesson focused on advanced drum and bass duo performance practices.', 'Advanced', 'Advanced Drum and Bass Duo Performance', 'Various drum and bass duo pieces', 'Various', 'Drum and bass duo performance practices, rhythmic exploration'),
(32, 1, 6, 'Wed', '5:00 PM', 1, 55.0, 'Partial', '2023-08-14', 'Confirmed', 'Lesson focused on extended techniques and contemporary repertoire.', 'Advanced', 'Extended Techniques and Contemporary Repertoire', 'Contemporary flute repertoire', 'Various', 'Extended technique exercises, contemporary piece exploration'),
(40, 3, 1, 'Thu', '6:30 PM', 1.5, 85.0, 'Pending', NULL, 'Confirmed', 'Lesson focused on advanced jazz piano and vocal accompaniment.', 'Advanced', 'Advanced Jazz Piano and Vocal Accompaniment', 'My Funny Valentine, Fly Me to the Moon', 'Various', 'Jazz piano accompaniment techniques, vocal accompaniment practices'),
(25, 2, 3, 'Fri', '5:30 PM', 1, 65.0, 'Paid', '2023-08-14', 'Confirmed', 'Lesson focused on advanced violin and piano duo performance.', 'Advanced', 'Advanced Violin and Piano Duo Performance', 'Beethoven Spring Sonata, Brahms Violin Concerto', 'Various', 'Violin and piano duo performance practices, advanced techniques'),
(26, 6, 4, 'Mon', '4:30 PM', 1.5, 75.0, 'Unpaid', NULL, 'Confirmed', 'Lesson focused on advanced drum set techniques and polyrhythms.', 'Advanced', 'Advanced Drum Set Techniques and Polyrhythms', 'Various drum set pieces', 'Various', 'Advanced drum set techniques, polyrhythm exercises'),
(27, 8, 6, 'Tue', '6:00 PM', 1, 55.0, 'Partial', '2023-08-14', 'Confirmed', 'Lesson focused on extended techniques and multimedia performance.', 'Advanced', 'Extended Techniques and Multimedia Performance', 'Contemporary flute pieces with multimedia', 'Various', 'Extended technique exploration, multimedia performance practices'),
(41, 10, 1, 'Wed', '5:30 PM', 1.5, 85.0, 'Pending', NULL, 'Confirmed', 'Lesson focused on advanced jazz piano trio composition and arrangement.', 'Advanced', 'Advanced Jazz Piano Trio Composition and Arrangement', 'Original compositions and arrangements', 'Various', 'Jazz piano trio composition and arrangement techniques, exploration of styles'),
(29, 3, 3, 'Thu', '6:30 PM', 1, 65.0, 'Paid', '2023-08-14', 'Confirmed', 'Lesson focused on advanced cello and piano duo performance practices.', 'Advanced', 'Advanced Cello and Piano Duo Performance', 'Beethoven Cello Sonata No. 3, Brahms Cello Sonata No. 1', 'Various', 'Cello and piano duo performance practices, advanced techniques'),
(30, 15, 4, 'Fri', '5:30 PM', 1.5, 75.0, 'Unpaid', NULL, 'Confirmed', 'Lesson focused on advanced drum and bass duo composition and arrangement.', 'Advanced', 'Advanced Drum and Bass Duo Composition and Arrangement', 'Original compositions and arrangements', 'Various', 'Drum and bass duo composition and arrangement techniques, style exploration');
INSERT INTO instrument_type (type, description, image_url, family) VALUES
('Piano', 'Acoustic or digital keyboard instrument with multiple strings struck by hammers.', 'https://[Log in to view URL]', 'Keyboard'),
('Guitar', 'Stringed instrument played with fingers or a pick.', 'https://[Log in to view URL]', 'Strings'),
('Violin', 'High-pitched stringed instrument played with a bow.', 'https://[Log in to view URL]', 'Strings'),
('Drums', 'Percussion instrument consisting of a set of drums and cymbals.', 'https://[Log in to view URL]', 'Percussion'),
('Saxophone', 'Woodwind instrument with a single reed and a bright sound.', 'https://[Log in to view URL]', 'Woodwinds'),
('Trumpet', 'Brass instrument with a bright and powerful sound.', 'https://[Log in to view URL]', 'Brass'),
('Flute', 'Woodwind instrument with a mellow and airy sound.', 'https://[Log in to view URL]', 'Woodwinds'),
('Cello', 'Low-pitched stringed instrument played while seated.', 'https://[Log in to view URL]', 'Strings'),
('Bass Guitar', 'Low-pitched stringed instrument similar to a guitar.', 'https://[Log in to view URL]', 'Strings'),
('Clarinet', 'Woodwind instrument with a single reed and a warm sound.', 'https://[Log in to view URL]', 'Woodwinds');
INSERT INTO rental_instrument (renter_id, instrument_type_id, brand, model, purchased_date, rented, condition)
VALUES
(1, 1, 'Steinway', 'Model B', '2023-01-15', 1, 'Great'),
(2, 4, 'Pearl', 'Export Series', '2022-10-20', 1, 'Good'),
(3, 6, 'Yamaha', 'YFL-222', '2023-03-10', 1, 'New'),
(4, 1, 'Kawai', 'K-300', '2022-12-05', 1, 'Good'),
(5, 3, 'Cremona', 'SV-175', '2023-02-28', 1, 'Great'),
(6, 4, 'Tama', 'Silverstar', '2023-05-15', 1, 'New'),
(7, 6, 'Muramatsu', 'DS Model', '2022-11-30', 1, 'Excellent'),
(8, 1, 'Yamaha', 'U1', '2023-04-10', 1, 'Good'),
(9, 3, 'Knilling', 'Budapest', '2023-06-20', 1, 'New'),
(10, 4, 'DW', 'Performance Series', '2023-08-05', 1, 'Excellent');
INSERT INTO recital (datetime, name, program, performing_students)
VALUES
('2024-02-14 7:00 PM', 'Valentine''s Day Concert', 'Love and Romance', '21,22,23,24,25,26,27,28,29,30'),
('2023-08-31 7:30 PM', 'Back-to-School Recital', 'Student Summer Projects', '1,6,11,16,21,26,30'),
('2023-10-31 6:00 PM', 'Halloween Spooktacular', 'Spooky and Fun Music', '2,7,12,17,22,27'),
('2023-12-25 4:00 PM', 'Christmas Day Special', 'Holiday Cheer and Classics', '3,8,13,18,23,28'),
('2024-04-01 7:00 PM', 'April Fools'' Extravaganza', 'Surprises and Tricks', '4,9,14,19,24,29'),
('2024-05-12 6:30 PM', 'Mother''s Day Celebration', 'Music for Moms', '5,10,15,20,25,30');
-- Earning to student mastery
-- WITH TeacherEarnings AS (
-- SELECT
-- t.teacher_id,
-- t.name,
-- SUM(CASE WHEN l.payment_status = 'Paid' THEN l.lesson_cost ELSE 0 END) AS Total_Earnings
-- FROM teacher t
-- JOIN scheduled_lesson l ON t.teacher_id = l.teacher_id
-- GROUP BY t.teacher_id
-- ),
-- StudentImprovement AS (
-- SELECT
-- l.student_id,
-- COUNT(DISTINCT l.instrument_level) AS Levels_Achieved
-- FROM scheduled_lesson l
-- GROUP BY l.student_id
-- ),
-- TeacherStudentLink AS (
-- SELECT
-- te.teacher_id,
-- te.name AS Teacher_Name,
-- te.Total_Earnings,
-- si.Levels_Achieved,
-- COUNT(si.student_id) AS Students_Improved
-- FROM TeacherEarnings te
-- JOIN scheduled_lesson l ON te.teacher_id = l.teacher_id
-- JOIN StudentImprovement si ON l.student_id = si.student_id
-- GROUP BY te.teacher_id, si.Levels_Achieved
-- )
-- SELECT
-- Teacher_Name,
-- Total_Earnings,
-- AVG(Levels_Achieved) AS Average_Improvement_Level,
-- SUM(Students_Improved) AS Total_Students_Improved
-- FROM TeacherStudentLink
-- GROUP BY Teacher_Name, Total_Earnings
-- ORDER BY Total_Earnings DESC, Average_Improvement_Level DESC;
-- Popular instruments
-- WITH InstrumentUsage AS (
-- SELECT
-- it.instrument_type_id,
-- it.type AS Instrument_Type,
-- COUNT(l.instrument_type_id) AS Lesson_Count,
-- RANK() OVER (ORDER BY COUNT(l.instrument_type_id) DESC) AS Popularity_Rank
-- FROM instrument_type it
-- LEFT JOIN scheduled_lesson l ON it.instrument_type_id = l.instrument_type_id
-- GROUP BY it.instrument_type_id, it.type
-- ),
-- RentalStatus AS (
-- SELECT
-- it.instrument_type_id,
-- it.type AS Instrument_Type,
-- COUNT(ri.instrument_type_id) AS Total_Rented,
-- COUNT(CASE WHEN ri.rented = 1 THEN 1 END) AS Currently_Rented
-- FROM instrument_type it
-- LEFT JOIN rental_instrument ri ON it.instrument_type_id = ri.instrument_type_id
-- GROUP BY it.instrument_type_id, it.type
-- )
-- SELECT
-- iu.Instrument_Type,
-- COALESCE(iu.Lesson_Count, 0) AS Lesson_Count,
-- iu.Popularity_Rank,
-- COALESCE(rs.Total_Rented, 0) AS Total_Rented,
-- COALESCE(rs.Currently_Rented, 0) AS Currently_Rented
-- FROM instrument_type it
-- LEFT JOIN InstrumentUsage iu ON it.instrument_type_id = iu.instrument_type_id
-- LEFT JOIN RentalStatus rs ON it.instrument_type_id = rs.instrument_type_id
-- ORDER BY iu.Popularity_Rank;
-- Teacher financials
-- WITH MonthlyLessonDetails AS (
-- SELECT
-- teacher_id,
-- strftime('%Y-%m', lesson_time) AS Month,
-- COUNT(*) AS Total_Lessons,
-- SUM(lesson_cost) AS Total_Earnings,
-- AVG(lesson_cost) AS Average_Lesson_Cost,
-- SUM(CASE WHEN payment_status = 'Paid' THEN lesson_cost ELSE 0 END) AS Earnings_Paid,
-- SUM(CASE WHEN payment_status = 'Partial' THEN lesson_cost ELSE 0 END) AS Earnings_Partial,
-- COUNT(CASE WHEN payment_status = 'Unpaid' THEN 1 END) AS Count_Unpaid,
-- COUNT(CASE WHEN payment_status = 'Pending' THEN 1 END) AS Count_Pending
-- FROM scheduled_lesson
-- GROUP BY teacher_id, Month
-- ),
-- TeacherInfo AS (
-- SELECT
-- teacher_id,
-- name,
-- email
-- FROM teacher
-- )
-- SELECT
-- ti.name,
-- ti.email,
-- mld.Total_Lessons,
-- mld.Total_Earnings,
-- mld.Average_Lesson_Cost,
-- mld.Earnings_Paid,
-- mld.Earnings_Partial,
-- mld.Count_Unpaid,
-- mld.Count_Pending
-- FROM MonthlyLessonDetails mld
-- JOIN TeacherInfo ti ON mld.teacher_id = ti.teacher_id
-- ORDER BY ti.name, mld.Month;
-- Student Lesson Info
-- WITH StudentLessons AS (
-- SELECT
-- s.student_id,
-- s.name AS Student_Name,
-- it.type AS Instrument_Type,
-- COUNT(l.lesson_id) AS Lessons_Per_Instrument,
-- AVG(l.lesson_cost) AS Avg_Lesson_Cost
-- FROM student s
-- JOIN scheduled_lesson l ON s.student_id = l.student_id
-- JOIN instrument_type it ON l.instrument_type_id = it.instrument_type_id
-- GROUP BY s.student_id, it.type
-- ),
-- LessonFocusChanges AS (
-- SELECT
-- student_id,
-- lesson_focus,
-- LAG(lesson_focus, 1) OVER (PARTITION BY student_id ORDER BY lesson_time) AS Previous_Lesson_Focus
-- FROM scheduled_lesson
-- )
-- SELECT
-- sl.Student_Name,
-- sl.Instrument_Type,
-- sl.Lessons_Per_Instrument,
-- sl.Avg_Lesson_Cost,
-- lfc.lesson_focus AS Current_Focus,
-- lfc.Previous_Lesson_Focus
-- FROM StudentLessons sl
-- LEFT JOIN LessonFocusChanges lfc ON sl.student_id = lfc.student_id
-- WHERE lfc.Previous_Lesson_Focus IS NOT NULL
-- ORDER BY sl.Student_Name, sl.Instrument_Type;
-- Teacher Specialization
-- WITH TeacherInstrumentSpecialization AS (
-- SELECT
-- t.teacher_id,
-- t.name AS Teacher_Name,
-- it.type AS Specialized_Instrument,
-- COUNT(l.lesson_id) AS Lessons_Taught,
-- SUM(l.lesson_cost) AS Total_Revenue
-- FROM teacher t
-- JOIN scheduled_lesson l ON t.teacher_id = l.teacher_id
-- JOIN instrument_type it ON l.instrument_type_id = it.instrument_type_id
-- WHERE ',' || t.teaches || ',' LIKE '%,' || CAST(l.instrument_type_id AS TEXT) || ',%'
-- GROUP BY t.teacher_id, it.type
-- ),
-- InstrumentUsage AS (
-- SELECT
-- it.type AS Instrument_Type,
-- COUNT(l.lesson_id) AS Total_Usage
-- FROM scheduled_lesson l
-- JOIN instrument_type it ON l.instrument_type_id = it.instrument_type_id
-- GROUP BY it.type
-- )
-- SELECT
-- tis.Teacher_Name,
-- tis.Specialized_Instrument,
-- tis.Lessons_Taught,
-- tis.Total_Revenue,
-- iu.Total_Usage
-- FROM TeacherInstrumentSpecialization tis
-- JOIN InstrumentUsage iu ON tis.Specialized_Instrument = iu.Instrument_Type
-- ORDER BY tis.Teacher_Name, tis.Specialized_Instrument;
-- Student Activity
-- WITH StudentActivity AS (
-- SELECT
-- student_id,
-- MIN(lesson_time) AS First_Lesson,
-- MAX(lesson_time) AS Last_Lesson,
-- COUNT(DISTINCT lesson_id) AS Total_Lessons,
-- COUNT(DISTINCT instrument_type_id) AS Instrument_Variety,
-- MAX(CASE WHEN lesson_status = 'Cancelled' THEN 1 ELSE 0 END) AS Has_Cancellations
-- FROM scheduled_lesson
-- GROUP BY student_id
-- ),
-- RetentionRates AS (
-- SELECT
-- student_id,
-- Total_Lessons,
-- Instrument_Variety,
-- CASE
-- WHEN Total_Lessons > 20 THEN 'High'
-- WHEN Total_Lessons BETWEEN 10 AND 20 THEN 'Medium'
-- ELSE 'Low'
-- END AS Retention_Score,
-- Has_Cancellations
-- FROM StudentActivity
-- )
-- SELECT
-- Retention_Score,
-- COUNT(student_id) AS Num_Students,
-- AVG(Total_Lessons) AS Avg_Lessons,
-- AVG(Instrument_Variety) AS Avg_Instrument_Count,
-- FROM RetentionRates
-- GROUP BY Retention_Score;
-- teacher performance
-- WITH TeacherLessonStats AS (
-- SELECT
-- t.teacher_id,
-- t.name AS Teacher_Name,
-- COUNT(*) AS Total_Lessons,
-- SUM(CASE WHEN l.lesson_status = 'Confirmed' THEN 1 ELSE 0 END) AS Confirmed_Lessons
-- FROM teacher t
-- JOIN scheduled_lesson l ON t.teacher_id = l.teacher_id
-- GROUP BY t.teacher_id
-- ),
-- RecitalParticipation AS (
-- SELECT
-- t.teacher_id,
-- COUNT(DISTINCT s.student_id) AS Students_In_Recitals
-- FROM teacher t
-- JOIN scheduled_lesson l ON t.teacher_id = l.teacher_id
-- JOIN student s ON l.student_id = s.student_id
-- JOIN recital r ON instr(r.performing_students, CAST(s.student_id AS TEXT)) > 0
-- GROUP BY t.teacher_id
-- ),
-- PerformanceIndex AS (
-- SELECT
-- tls.Teacher_Name,
-- tls.Total_Lessons,
-- tls.Confirmed_Lessons,
-- COALESCE(rp.Students_In_Recitals, 0) AS Students_In_Recitals,
-- (CAST(tls.Confirmed_Lessons AS REAL) / tls.Total_Lessons) AS Completion_Rate,
-- ((CAST(tls.Confirmed_Lessons AS REAL) / tls.Total_Lessons) * 0.7 +
-- (CASE WHEN COALESCE(rp.Students_In_Recitals, 0) > 0 THEN 1.0 ELSE 0 END) * 0.3) AS Performance_Index
-- FROM TeacherLessonStats tls
-- LEFT JOIN RecitalParticipation rp ON tls.teacher_id = rp.teacher_id
-- )
-- SELECT
-- Teacher_Name,
-- ROUND(Performance_Index, 2) AS Performance_Index
-- FROM PerformanceIndex
-- ORDER BY Performance_Index DESC;
-- instr maintenance
-- WITH InstrumentUsageStats AS (
-- SELECT
-- it.type AS Instrument_Type,
-- COUNT(l.lesson_id) AS Usage_Count,
-- ri.condition AS Current_Condition,
-- (JULIANDAY('now') - JULIANDAY(ri.purchased_date)) / 365 AS Years_In_Use,
-- CASE
-- WHEN ri.condition IN ('Poor', 'Damaged') THEN 'High'
-- WHEN ri.condition IN ('Good', 'Great') AND (JULIANDAY('now') - JULIANDAY(ri.purchased_date)) / 365 > 1 THEN 'Medium'
-- ELSE 'Low'
-- END AS Maintenance_Need
-- FROM rental_instrument ri
-- JOIN scheduled_lesson l ON ri.instrument_type_id = l.instrument_type_id
-- JOIN instrument_type it ON ri.instrument_type_id = it.instrument_type_id
-- GROUP BY it.type, ri.condition, ri.purchased_date
-- )
-- SELECT
-- Instrument_Type,
-- AVG(Usage_Count) AS Avg_Usage,
-- MAX(Maintenance_Need) AS Max_Maintenance_Need
-- FROM InstrumentUsageStats
-- GROUP BY Instrument_Type;
-- top 3 teachers
-- WITH TeacherRecitalPerformance AS (
-- SELECT
-- t.teacher_id,
-- t.name AS Teacher_Name,
-- COUNT(DISTINCT r.recital_id) AS Recitals_Participated,
-- COUNT(DISTINCT s.student_id) AS Unique_Students,
-- COUNT(DISTINCT it.type) AS Instrument_Types
-- FROM teacher t
-- JOIN scheduled_lesson l ON t.teacher_id = l.teacher_id
-- JOIN student s ON s.student_id = l.student_id
-- JOIN recital r ON instr(r.performing_students, CAST(s.student_id AS TEXT)) > 0
-- JOIN instrument_type it ON l.instrument_type_id = it.instrument_type_id
-- GROUP BY t.teacher_id
-- ),
-- RankedTeacherPerformance AS (
-- SELECT
-- *,
-- RANK() OVER (ORDER BY Recitals_Participated DESC, Unique_Students DESC, Instrument_Types DESC) AS Performance_Rank
-- FROM TeacherRecitalPerformance
-- )
-- SELECT *
-- FROM RankedTeacherPerformance
-- WHERE Performance_Rank <= 3; -- Top 5 teachers based on their influence in student recital performances.
To embed this program on your website, copy the following code and paste it into your website's HTML: