Couple of errors with this Schema.

[sgundava]

Hi Aafreen29,

I think there are a couple of errors with the schema that would be wonderful to fix for anyone wanting to use your collection of use cases for practice.

1. The order in which you are adding items to all_students and attendance_events will cause a foreign key constraint violation. You have attendance events that refers to student_id and at the point of inserting data into attendance_events, students table is empty. So, this foreign key constraint is violated.

 CREATE TABLE all_students (
		student_id INT NOT NULL PRIMARY KEY,
        school_id INT,
        grade_level INT,
        date_of_birth DATETIME,
        hometown CHAR(25)
 );
 CREATE TABLE attendance_events (
		date_event DATETIME,
        student_id INT,
        attendance CHAR(20),
        FOREIGN KEY (student_id)
		REFERENCES all_students(student_id)
        ON DELETE CASCADE
 );
  
 INSERT INTO attendance_events
 (date_event, student_id, attendance) VALUES
 ('2018-01-10', 110111, 'present'),
 ('2018-01-10', 110121, 'present' ),
 ('2018-01-12', 110115, 'absent'),
 ('2018-01-13', 110119, 'absent'),
 ('2018-01-13', 110121, 'present'),
 ('2018-01-14', 110125, 'present'),
 ('2018-02-05', 110111, 'absent'),
 ('2018-02-17', 110115, 'present'),
 ('2018-02-22', 110129, 'absent');
 
 INSERT INTO all_students
 (student_id, school_id, grade_level, date_of_birth, hometown) VALUES
 (110111, 205, 1, '2013-01-10', 'Pleasanton'),
 (110115, 205, 1, '2013-03-15', 'Dublin'),
 (110119, 205, 2, '2012-02-13', 'San Ramon'),
 (110121, 205, 3, '2011-01-13', 'Dublin'),
 (110125, 205, 2, '2012-04-25','Dublin'),
 (110129, 205, 3, '2011-02-22', 'San Ramon');

2. Another issue is with confirmed_no table, you might want to changed the confirmation_no in the second query to confirmed_no as there is no confirmation_no table up until that point for it to insert into..

CREATE TABLE confirmed_no(
phone_number CHAR(15)
);

INSERT INTO confirmation_no 
(phone_number) VALUES
('232-473-3433'),
('545-038-2294'),
('647-294-1837'),
('492-485-9727'),
('545-383-7837'),
('184-483-9575'),
('493-420-4902'),
('282-595-8373'),
('594-9594-2948');

INSERT INTO confirmed_no
(phone_number) VALUES
('492-485-9727'),
('545-383-7837'),
('184-483-9575'),
('493-420-4902');

I hope you don't take this the wrong way. Just wanted to identify the issue. Good job gathering this repository of practice questions.

Good luck with the fixes. Please let me know if you have any questions.

Thanks,
Surya