Crua6XbF

1# drop DATABASE test3;
2# CREATE DATABASE test3;
3use test3;
4CREATE TABLE Schools (
5  name          VARCHAR(50),
6  address       VARCHAR(50),
7  PRIMARY KEY (name, address),
8  general_info  VARCHAR(50),
9  vision        VARCHAR(50),
10  mission       VARCHAR(50),
11  email         VARCHAR(50),
12  main_language VARCHAR(50),
13  phone_number  VARCHAR(50)
14
15);
16
17
18CREATE TABLE Employees
19(
20  employee_ID       INTEGER AUTO_INCREMENT,
21  PRIMARY KEY (employee_ID),
22  first_name        VARCHAR(20),
23  middle_name       VARCHAR(20),
24  last_name         VARCHAR(20),
25  gender            VARCHAR(1),
26  birth_date        DATETIME,
27  e_address         VARCHAR(50),
28  email             VARCHAR(50),
29  username          VARCHAR(20) UNIQUE,
30  username_password VARCHAR(20),
31  salary            FLOAT,
32  school_name       VARCHAR(50),
33  school_address    VARCHAR(50),
34  FOREIGN KEY (school_name, school_address) REFERENCES Schools (name, address)
35    ON DELETE SET NULL
36
37);
38
39
40CREATE TABLE  Students(
41 ssn INTEGER NOT NULL ,
42  PRIMARY KEY (ssn),
43  school_name VARCHAR(50),
44  school_address varchar(50),
45  user_name VARCHAR(50) unique,
46   gender VARCHAR(50),
47  name VARCHAR(50),
48  birth_date DATETIME ,
49  accepted    int(1),
50  password VARCHAR(50) ,
51     age int AS (YEAR('2016-1-1') - YEAR(birth_date)) ,
52  grade int As ((YEAR('2016-1-1') - YEAR(birth_date))-5) ,
53FOREIGN KEY (school_name,school_address) REFERENCES Schools (name ,address)
54);
55SELECT *
56FROM School_Activities;
57SELECT *
58FROM Announcements;
59
60CREATE TABLE Types (
61  type        VARCHAR(50),
62  school_name VARCHAR(50),
63  address     VARCHAR(50),
64  FOREIGN KEY (school_name, address) REFERENCES Schools (name, address)
65
66);
67
68
69CREATE TABLE Teachers (
70  seniority_level    INTEGER,
71  first_year_of_work DATETIME,
72  experience_year    INTEGER AS (year('2016-1-1') - year(first_year_of_work)),
73  teacher_id         INTEGER,
74
75  FOREIGN KEY (teacher_id) REFERENCES Employees (employee_id)
76    ON DELETE CASCADE
77
78);
79
80CREATE TABLE Parents (
81  user_name     VARCHAR(50) NOT NULL,
82  PRIMARY KEY (user_name),
83  password      VARCHAR(50),
84  first_name    VARCHAR(50),
85  last_name     VARCHAR(50),
86  home_phone    VARCHAR(50),
87  email         VARCHAR(50),
88  address       VARCHAR(50),
89  report_number INT -- ,
90  --   FOREIGN KEY (report_number)REFERENCES Reports(report_number)
91
92);
93
94
95CREATE TABLE Reports (
96  report_number INT AUTO_INCREMENT,
97  PRIMARY KEY (report_number),
98  comment       VARCHAR(50),
99  content       VARCHAR(50),
100  issue_date    DATETIME,
101  reply         VARCHAR(50),
102  user_name     VARCHAR(50),
103  student_ssn   INTEGER,
104  teacher_id    INTEGER  ,
105  FOREIGN KEY (user_name)REFERENCES Parents(user_name)  ,
106  FOREIGN KEY (teacher_id)REFERENCES Teachers(teacher_id),
107  FOREIGN KEY (student_ssn)REFERENCES Students(ssn)
108);
109
110ALTER TABLE Parents
111  ADD CONSTRAINT report_number FOREIGN KEY (report_number) REFERENCES Reports (report_number);
112ALTER TABLE Reports
113  ADD CONSTRAINT user_name FOREIGN KEY (user_name) REFERENCES Parents (user_name);
114ALTER TABLE Reports
115  ADD CONSTRAINT teacher_id FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id);
116ALTER TABLE Reports
117  ADD CONSTRAINT student_ssn FOREIGN KEY (student_ssn) REFERENCES Students (ssn);
118
119CREATE TABLE Parents_apply_Students_Schools (
120  accept           TINYINT,
121  parent_user_name VARCHAR(50),
122  student_ssn      INTEGER,
123  school_name      VARCHAR(20),
124  PRIMARY KEY (student_ssn,school_name,parent_user_name),
125  FOREIGN KEY (student_ssn) REFERENCES Students (ssn),
126  FOREIGN KEY (school_name) REFERENCES Schools (name),
127  FOREIGN KEY (parent_user_name) REFERENCES Parents (user_name)
128);
129
130CREATE TABLE Teachers_supervises_Teachers (
131
132  teacher_id1 INTEGER,
133  teacher_id2 INTEGER,
134  FOREIGN KEY (teacher_id1) REFERENCES Teachers (teacher_id),
135  FOREIGN KEY (teacher_id2) REFERENCES Teachers (teacher_id)
136);
137
138CREATE TABLE Courses(
139  course_code INTEGER  NOT NULL AUTO_INCREMENT,
140  PRIMARY KEY(course_code),
141  name VARCHAR(50) ,
142  description VARCHAR(50) ,
143 school_address VARCHAR(50),
144 school_name varchar(20) ,
145  FOREIGN KEY (school_name,school_address)REFERENCES Schools(name,address)
146);
147
148CREATE TABLE High_Schools (
149  name    VARCHAR(50),
150  address VARCHAR(50),
151  FOREIGN KEY (name, address) REFERENCES Schools (name, address)
152);
153CREATE TABLE Mid_Schools (
154  name    VARCHAR(50),
155  address VARCHAR(50),
156  FOREIGN KEY (name, address) REFERENCES Schools (name, address)
157
158);
159CREATE TABLE Elementary_Schools (
160  name    VARCHAR(50),
161  address VARCHAR(50),
162  FOREIGN KEY (name, address) REFERENCES Schools (name, address)
163
164);
165CREATE TABLE Required_Supplies (
166  required_supplies VARCHAR(50),
167  name              VARCHAR(50),
168  address           VARCHAR(50),
169  FOREIGN KEY (name, address) REFERENCES Schools (name, address)
170
171);
172
173CREATE TABLE Adminstrators (
174  adminstrator_id INTEGER AUTO_INCREMENT,
175  FOREIGN KEY (adminstrator_id) REFERENCES Employees (employee_id)
176    ON DELETE CASCADE
177
178);
179
180CREATE TABLE Announcements (
181
182  date        DATETIME,
183  title       VARCHAR(50),
184  description VARCHAR(50),
185  type        VARCHAR(50),
186  employee_id INTEGER,
187  FOREIGN KEY (employee_id) REFERENCES Adminstrators (adminstrator_id)
188);
189
190
191CREATE TABLE School_Activities
192(
193  activity_Date   DATETIME,
194  location        VARCHAR(200),
195  PRIMARY KEY (activity_Date, location),
196  teacher_id      INT,
197  adminstrator_id INT,
198  name            VARCHAR(50),
199  equipment       VARCHAR(50),
200  type            VARCHAR(50),
201  description     VARCHAR(50),
202  FOREIGN KEY (adminstrator_id) REFERENCES Adminstrators (adminstrator_id),
203  FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id)
204);
205
206CREATE TABLE Assignments (
207
208  assignment_number INTEGER AUTO_INCREMENT,
209  PRIMARY KEY (assignment_number),
210  posting_date      DATETIME,
211  due_date          DATETIME,
212  content           VARCHAR(50)
213
214);
215
216CREATE TABLE Activities_Involve_Students (
217  student_ssn   INTEGER,
218  activity_Date DATETIME,
219  location      VARCHAR(200),
220  PRIMARY KEY (student_ssn,activity_Date,location),
221  FOREIGN KEY (student_ssn) REFERENCES Students (ssn),
222  FOREIGN KEY (activity_Date, location) REFERENCES School_Activities (activity_Date, location)
223);
224
225
226CREATE TABLE Mobile_numbers (
227  mobile           VARCHAR(50),
228  parent_user_name VARCHAR(50),
229  FOREIGN KEY (parent_user_name) REFERENCES Parents (user_name)
230);
231
232CREATE TABLE Levels (
233  level VARCHAR(50),
234  PRIMARY KEY (level)
235);
236
237set FOREIGN_KEY_CHECKS =0;
238drop TABLE Levels_offer_Schools;
239CREATE TABLE Levels_offer_Schools (
240  level          VARCHAR(50),
241  school_name    VARCHAR(50),
242  school_address VARCHAR(50),
243  FOREIGN KEY (level) REFERENCES Levels (level),
244  FOREIGN KEY (school_name, school_address) REFERENCES Schools (name, address)
245
246);
247set FOREIGN_KEY_CHECKS =1;
248
249
250CREATE TABLE Courses_offer_Levels (
251  grade       INTEGER,
252  level       VARCHAR(50),
253  course_code INTEGER,
254  PRIMARY KEY (level,course_code),
255  FOREIGN KEY (level) REFERENCES Levels (level),
256  FOREIGN KEY (course_code) REFERENCES Courses (course_code)
257);
258
259
260
261CREATE TABLE Administrators_Assigns_Teachers
262(
263  teacher_id      INT,
264  adminstrator_id INT,
265  name            VARCHAR(50),
266  activity_Date   DATETIME,
267  location        VARCHAR(200),
268  PRIMARY KEY (adminstrator_id,teacher_id,activity_Date,location),
269  FOREIGN KEY (adminstrator_id) REFERENCES Adminstrators (adminstrator_id)
270    ON DELETE CASCADE,
271  FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id)
272    ON DELETE CASCADE,
273  FOREIGN KEY (activity_Date, location) REFERENCES School_Activities (activity_Date, location)
274);
275
276CREATE TABLE Questions
277(
278  question_number INT PRIMARY KEY AUTO_INCREMENT,
279  question        VARCHAR(50),
280  course_code     INT,
281  ssn             INT,
282  content VARCHAR(500),
283  FOREIGN KEY (course_code) REFERENCES Courses (course_code)
284    ON DELETE CASCADE,
285  FOREIGN KEY (ssn) REFERENCES Students (ssn)
286    ON DELETE CASCADE
287);
288
289CREATE TABLE Students_Asks_Questions
290(
291  ssn             INT,
292  course_code     INT,
293  question_number INT,
294  PRIMARY KEY (course_code,ssn,question_number),
295  FOREIGN KEY (course_code) REFERENCES Courses (course_code)
296    ON DELETE CASCADE,
297  FOREIGN KEY (ssn) REFERENCES Students (ssn)
298    ON DELETE CASCADE,
299  FOREIGN KEY (question_number) REFERENCES Questions (question_number)
300    ON DELETE CASCADE
301);
302
303CREATE TABLE Answers
304(
305  answer_number   INTEGER AUTO_INCREMENT,
306  PRIMARY KEY (answer_number),
307  answer          VARCHAR(20),
308  teacher_id      INT,
309  question_number INT,
310  content VARCHAR(500),
311  FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id)
312    ON DELETE CASCADE,
313  FOREIGN KEY (question_number) REFERENCES Questions (question_number)
314    ON DELETE CASCADE
315);
316
317CREATE TABLE Questions_isPartOf_Courses
318(
319  course_code     INTEGER,
320  answer_number   INT,
321  question_number INT,
322  PRIMARY KEY (course_code,question_number,answer_number),
323  FOREIGN KEY (course_code) REFERENCES Courses (course_code)
324    ON DELETE CASCADE,
325  FOREIGN KEY (question_number) REFERENCES Questions (question_number)
326    ON DELETE CASCADE,
327  FOREIGN KEY (answer_number) REFERENCES Answers (answer_number)
328    ON DELETE CASCADE
329);
330
331
332CREATE TABLE grade
333(
334  teacher_id        INT,
335  assignment_number INT,
336  course_code       INT,
337  student_grade     INT,
338  PRIMARY KEY (teacher_id,course_code,assignment_number),
339  FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id)
340    ON DELETE CASCADE,
341  FOREIGN KEY (course_code) REFERENCES Courses (course_code)
342    ON DELETE CASCADE,
343  FOREIGN KEY (assignment_number) REFERENCES Assignments (assignment_number)
344    ON DELETE CASCADE
345);
346
347CREATE TABLE Courses_has_prerequisite_Courses (
348  course_code1 INTEGER,
349  course_code2 INTEGER,
350  PRIMARY KEY (course_code1,course_code2),
351  FOREIGN KEY (course_code1) REFERENCES Courses (course_code),
352  FOREIGN KEY (course_code2) REFERENCES Courses (course_code)
353
354);
355
356CREATE TABLE Assignments_postedBy_Teachers (
357  teacher_id        INT,
358  course_code       INT,
359  assignment_number INTEGER,
360  PRIMARY KEY (teacher_id,course_code,assignment_number),
361  FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id)
362    ON DELETE CASCADE,
363  FOREIGN KEY (course_code) REFERENCES Courses (course_code)
364    ON DELETE CASCADE,
365  FOREIGN KEY (assignment_number) REFERENCES Assignments (assignment_number)
366);
367
368CREATE TABLE Parents_reply_Reports (
369  reply      VARCHAR(50),
370  user_name  VARCHAR(50),
371  teacher_id INTEGER,
372  PRIMARY KEY (user_name,teacher_id),
373  FOREIGN KEY (user_name) REFERENCES Parents (user_name),
374  FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id)
375
376);
377
378
379CREATE TABLE Parents_rateAndreview_Teachers (
380  rating     INT ,
381  rate       INTEGER,
382  teacher_id INT,
383  parent     VARCHAR(50),
384  PRIMARY KEY (teacher_id,parent),
385  FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id),
386  FOREIGN KEY (parent) REFERENCES Parents (user_name)
387);
388
389
390CREATE TABLE Parents_rateAndreview_Schools (
391  rate            INT ,
392  review          VARCHAR(50),
393  parent_username VARCHAR(50),
394  school_name     VARCHAR(50),
395  school_address  VARCHAR(50),
396  PRIMARY KEY (parent_username,school_name,school_address),
397  FOREIGN KEY (parent_username) REFERENCES Parents (user_name),
398  FOREIGN KEY (school_name, school_address) REFERENCES Schools (name, address)
399);
400
401CREATE TABLE Clubs (
402  club_name      VARCHAR(50),
403  PRIMARY KEY (club_name),
404  purpose        VARCHAR(50),
405  school_address VARCHAR(200),
406  school_name    VARCHAR(50),
407  FOREIGN KEY (school_name, school_address) REFERENCES Schools (name, address)
408    ON DELETE SET NULL
409);
410
411CREATE TABLE Clubs_joinBy_Students (
412  ssn       INT,
413  club_name VARCHAR(50),
414  PRIMARY KEY (ssn,club_name),
415  FOREIGN KEY (ssn) REFERENCES Students (ssn),
416  FOREIGN KEY (club_name) REFERENCES Clubs (club_name)
417);
418
419CREATE TABLE Students_taughtBy_Teachers (
420  ssn         INT DEFAULT 1111,
421
422  course_code INTEGER,
423  teacher_id  INT,
424  PRIMARY KEY (ssn,course_code),
425  FOREIGN KEY (ssn) REFERENCES Students (ssn)
426    ON DELETE CASCADE,
427  FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id)
428    ON DELETE CASCADE,
429  FOREIGN KEY (course_code) REFERENCES Courses (course_code)
430    ON DELETE CASCADE
431);
432
433CREATE TABLE Students_Enrolled_Schools (
434  ssn            INTEGER,
435  school_name    VARCHAR(50),
436  school_address VARCHAR(50),
437  PRIMARY KEY (ssn,school_address,school_name),
438  FOREIGN KEY (ssn) REFERENCES Students (ssn),
439  FOREIGN KEY (school_name, school_address) REFERENCES Schools (name, address)
440
441);
442
443
444CREATE TABLE Assignments_gradedBy_Teachers_Students (
445  grade             INTEGER,
446  assignment_number INTEGER,
447  student_ssn       INTEGER,
448  teacher_id        INTEGER,
449  PRIMARY KEY (student_ssn,assignment_number),
450  FOREIGN KEY (student_ssn) REFERENCES Students (ssn),
451  FOREIGN KEY (assignment_number) REFERENCES Assignments (assignment_number),
452  FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id)
453
454);
455
456CREATE TABLE Assignments_solve_Students(
457  solution VARCHAR(50),
458grade int ,
459  student_ssn INTEGER  ,
460  assignment_number INTEGER default 0 ,
461  PRIMARY KEY (student_ssn,assignment_number),
462  FOREIGN KEY (student_ssn)REFERENCES Students(SSN),
463  FOREIGN KEY (assignment_number) REFERENCES Assignments(assignment_number)
464);
465
466
467-- and done
468create Table Assignments_posteBy_Teachers(
469  teacher_id int,
470  course_code int ,
471  assignment_number int
472   ,PRIMARY KEY (assignment_number,course_code),
473  FOREIGN KEY (teacher_id) REFERENCES Teachers(teacher_id) on DELETE CASCADE,
474  FOREIGN key (course_code)REFERENCES Courses (course_code) on DELETE  CASCADE,
475  FOREIGN key (assignment_number) REFERENCES Assignments(assignment_number)
476);
477-- start of system user proc
478# the following 3 procedured to the following tasks respectivly name,address,type
479DELIMITER //
480CREATE PROCEDURE searchForSchoolName(name VARCHAR(50))
481  BEGIN
482    IF name IS NULL
483    THEN
484      SELECT 'We do not accept null values';
485    ELSE
486      SELECT *
487      FROM Schools
488      WHERE Schools.name = name;
489    END IF;
490  END //
491DELIMITER ;
492
493DELIMITER //
494CREATE PROCEDURE searchForSchoolAddress(address VARCHAR(50))
495  BEGIN
496    IF address IS NULL
497    THEN
498      SELECT 'We do not accept null values';
499    ELSE
500      SELECT *
501      FROM Schools
502      WHERE Schools.address = address;
503    END IF;
504  END //
505DELIMITER ;
506
507
508DELIMITER //
509CREATE PROCEDURE searchForSchoolType(typee VARCHAR(50))
510  BEGIN
511    IF typee IS NULL
512    THEN
513      SELECT 'We do not accept null values';
514    ELSE
515      SELECT S.*
516      FROM Schools S inner JOIN Types T on t.school_name = S.name and T.address = S.address
517      WHERE T.type = typee;
518    END IF;
519  END //
520DELIMITER ;
521
522
523
524drop PROCEDURE viewSchoolsByLevel;
525DELIMITER //
526CREATE PROCEDURE viewSchoolsByLevel()
527  BEGIN
528    SELECT
529      S.name,
530      S.address,
531      los.level
532    FROM Schools S inner join Levels_offer_Schools los on los.school_name = S.name and los.school_address = S.address
533    GROUP BY los.level and S.name and S.address;
534  END //
535
536call viewSchoolsByLevel();
537
538
539
540-- 3
541# View the information of a certain school along with the reviews written about it and teachers
542# teaching in this school.
543DELIMITER //
544CREATE PROCEDURE viewSchoolAndRating(sName VARCHAR(50), sAddress VARCHAR(50))
545  BEGIN
546    SELECT
547      S.*,
548      rating.rate,
549      rating.review,
550      rating.parent_username,
551      E.first_name
552    FROM Schools S INNER JOIN Parents_rateAndreview_Schools rating
553        ON S.address = rating.school_address AND S.name = rating.school_name
554      INNER JOIN Employees E ON E.school_address = S.address AND E.school_name = S.name
555      INNER JOIN Teachers T ON T.teacher_id = E.employee_ID
556    where E.first_name is not null  and E.school_name = sName and E.school_address = sAddress;
557  END //
558
559-- 1
560# View all unverified teachers (those with username and password as null)
561DELIMITER //
562CREATE PROCEDURE View_Unverified_Teachers(myName VARCHAR(50), myPassword VARCHAR(50))
563  BEGIN
564
565    DECLARE mySchoolName VARCHAR(50);
566    DECLARE mySchoolAddress VARCHAR(50);
567
568    SELECT S.name
569    INTO mySchoolName
570    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
571      INNER JOIN Adminstrators A ON a.adminstrator_id = E.employee_ID
572    WHERE E.username = myName and E.username_password = myPassword;
573
574    SELECT S.address
575    INTO mySchoolAddress
576    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
577      INNER JOIN Adminstrators A ON A.adminstrator_id = employee_ID
578    WHERE E.username = myName and E.username_password = myPassword;
579
580    SELECT E.*
581    FROM Teachers T INNER JOIN Employees E ON E.employee_ID = T.teacher_id
582      INNER JOIN Schools S2 ON E.school_name = S2.name AND E.school_address = S2.address
583    WHERE
584      S2.address = mySchoolAddress AND S2.name = mySchoolName AND E.username IS NULL AND E.username_password IS NULL;
585
586  END //
587DELIMITER ;
588SELECT S.name
589
590    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
591      INNER JOIN Adminstrators A ON a.adminstrator_id = E.employee_ID
592    WHERE E.username = 'abeer.elsayed' and E.username_password = 'blabizo';
593SELECT *
594FROM Employees;
595
596
597# Verify or assign usernames and passwords to all teacher who applied to the admin's but do not have usernames and passwords yet
598# A salary for this teacher is also calculated
599
600DELIMITER //
601CREATE PROCEDURE Verify_Teacher(myName VARCHAR(50), myPass VARCHAR(50), tID INT, newName VARCHAR(50), newPass VARCHAR(50))
602  BEGIN
603    DECLARE myID int;
604    SELECT E2.employee_ID into myID
605    FROM Employees E2
606    WHERE E2.username = myName and E2.username_password = myPass;
607
608    UPDATE Employees E
609    SET E.username = newName, E.username_password = newPass
610    WHERE E.employee_ID = tID;
611    CALL addSalary(myID, tID);
612
613
614  END //
615
616DELIMITER ;
617
618
619# a helper procedure used to calculate the salary of a teacher
620DELIMITER //
621CREATE PROCEDURE addSalary(myID INT, tID INT)
622  BEGIN
623    DECLARE x INT;
624    SELECT T.experience_year
625    INTO x
626    FROM Teachers T
627    WHERE tID = T.teacher_id;
628
629    UPDATE Employees E
630    SET E.salary = 500 * x
631    WHERE E.employee_ID = tID;
632
633  END;
634
635DELIMITER ;
636
637
638-- 2
639# DROP PROCEDURE View_Unverified_Students;
640#  views all students who applied to the admin's school but do not have a username and a password yet
641DELIMITER //
642CREATE PROCEDURE View_Unverified_Students(myName VARCHAR(50) , myPass VARCHAR(50))
643  BEGIN
644    DECLARE mySchoolName VARCHAR(50);
645    DECLARE mySchoolAddress VARCHAR(50);
646
647    SELECT S.name
648    INTO mySchoolName
649    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
650      INNER JOIN Adminstrators A ON A.adminstrator_id = E.employee_ID
651    WHERE E.username = myName and E.username_password = myPass;
652
653    SELECT S.address
654    INTO mySchoolAddress
655    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
656      INNER JOIN Adminstrators A ON A.adminstrator_id = E.employee_ID
657    WHERE E.username = myName and E.username_password = myPass;
658
659    SELECT S.*
660    FROM Students S INNER JOIN Schools school ON S.school_name = school.name AND S.school_address = school.address
661    WHERE
662      school.address = mySchoolAddress AND school.name = mySchoolName AND S.user_name IS NULL AND S.password IS NULL;
663
664  END //
665use test3;
666INSERT INTO Students (ssn, school_name, school_address)
667VALUES (32352854,'El rowad','8 Mostafa El Nahhas');
668SELECT *
669FROM Schools;
670SELECT *
671FROM Employees;
672SELECT *
673FROM Students;
674
675DELIMITER //
676SELECT * FROM Students WHERE school_name = 'saintfatima' and '66 Ismail Al Kabbani' and accepted is null;
677UPDATE Students set accepted = 1 where ssn = 1222;
678DELIMITER //
679# drop PROCEDURE Verify_Student;
680# verify or assign a user name or password to non verified students
681CREATE PROCEDURE Verify_Student( sID INT, user VARCHAR(50), pass VARCHAR(50))
682  BEGIN
683
684    UPDATE Students S
685    SET S.user_name = user, S.password = pass
686    WHERE S.ssn = sID;
687
688  END //
689DELIMITER ;
690use test3;
691SELECT *
692FROM Employees;
693SELECT *
694FROM Adminstrators;
695
696-- 3
697#Add other admins to the school I am working in. An admin has first name, middle name, last name,
698# birthdate, address, email, username, password, and gender. Note that the salary of the admin
699# depends on the type of the school
700DELIMITER //
701CREATE PROCEDURE addAdmin(newusername          VARCHAR(20),
702                          newusername_password VARCHAR(20),
703                          newfirst_name        VARCHAR(20),
704                          newmiddle_name       VARCHAR(20),
705                          newlast_name         VARCHAR(20),
706                          newgender            VARCHAR(1),
707                          newemail             VARCHAR(50),
708                          newaddress           VARCHAR(50),
709                          newbirth_date        DATETIME, adminName VARCHAR(50),adminPassword VARCHAR(50),schoolType VARCHAR(50))
710  BEGIN
711    DECLARE schoolAddress VARCHAR(50);
712    DECLARE schoolName VARCHAR(50);
713    DECLARE id INT DEFAULT 0;
714
715    SELECT S.name
716    INTO schoolName
717    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
718      INNER JOIN Adminstrators A ON A.adminstrator_id = E.employee_ID
719    WHERE E.username = adminName and E.username_password = adminPassword;
720
721
722    SELECT S.address
723    INTO schoolAddress
724    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
725      INNER JOIN Adminstrators A ON A.adminstrator_id = E.employee_ID
726    WHERE E.username = adminName and E.username_password = adminPassword;
727
728
729    INSERT INTO Employees (username, username_password, first_name, middle_name, last_name, gender, email, e_address, birth_date, school_name, school_address)
730    VALUES
731      (newusername, newusername_password, newfirst_name, newmiddle_name, newlast_name, newgender, newemail, newaddress,
732                    newbirth_date, schoolName, schoolAddress);
733
734    SELECT E.employee_ID
735    INTO id
736    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
737    WHERE E.username = newusername;
738
739    INSERT INTO Adminstrators (adminstrator_id)
740      VALUE (id);
741
742    IF (schoolType='international') then
743      UPDATE Employees E2
744        SET E2.salary = 5000
745      WHERE E2.employee_ID = id;
746    ELSE
747        UPDATE Employees E2
748        SET E2.salary = 3000
749      WHERE E2.employee_ID = id;
750end IF ;
751  END //
752DELIMITER ;
753
754
755-- 4
756# DROP PROCEDURE View_Employees;
757# View employees in the same school as the adminsystem.
758DELIMITER //
759CREATE PROCEDURE View_Employees(myName VARCHAR(50), myPass VARCHAR(50))
760  BEGIN
761
762    DECLARE mySchoolName VARCHAR(50);
763    DECLARE mySchoolAddress VARCHAR(50);
764    SELECT S.name
765    INTO mySchoolName
766    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
767      INNER JOIN Adminstrators A ON A.adminstrator_id = E.employee_ID
768    WHERE E.username = myName and E.username_password = myPass;
769
770    SELECT S.address
771    INTO mySchoolAddress
772    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
773      INNER JOIN Adminstrators A ON A.adminstrator_id = E.employee_ID
774    WHERE E.username = myName and E.username_password = myPass;
775
776    SELECT E.*
777    FROM Employees E
778    WHERE E.school_name = mySchoolName AND E.school_address = mySchoolAddress;
779  END //
780DELIMITER ;
781
782
783
784
785# Delete certain employee from the admin's system
786DELIMITER //
787CREATE PROCEDURE deleteEmployeesFromSystem(id INT)
788  BEGIN
789    DELETE FROM Employees
790    WHERE employee_ID = id;
791
792
793  END //
794DELIMITER ;
795
796
797-- 5
798# drop PROCEDURE updateGeneralInfo;
799# update admin's school general info
800DELIMITER //
801CREATE PROCEDURE updateGeneralInfo(info VARCHAR(200), myName  VARCHAR(50), myPassword VARCHAR(50))
802  BEGIN
803 DECLARE mySchoolName VARCHAR(50);
804    DECLARE mySchoolAddress VARCHAR(50);
805
806    SELECT S.name
807    INTO mySchoolName
808    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
809      INNER JOIN Adminstrators A ON a.adminstrator_id = E.employee_ID
810    WHERE E.username = myName and E.username_password = myPassword;
811
812    SELECT S.address
813    INTO mySchoolAddress
814    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
815      INNER JOIN Adminstrators A ON A.adminstrator_id = employee_ID
816    WHERE E.username = myName and E.username_password = myPassword;
817
818    UPDATE Schools S
819    SET S.general_info = info
820    WHERE S.name = mySchoolName AND S.address = mySchoolAddress;
821
822  END //
823DELIMITER ;
824# call updateGeneralInfo()
825
826# update admin's school vision
827DELIMITER //
828# drop PROCEDURE updateVision;
829CREATE PROCEDURE updateVision(vis VARCHAR(200),myName  VARCHAR(50), myPassword VARCHAR(50))
830  BEGIN
831DECLARE mySchoolName VARCHAR(50);
832    DECLARE mySchoolAddress VARCHAR(50);
833
834    SELECT S.name
835    INTO mySchoolName
836    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
837      INNER JOIN Adminstrators A ON a.adminstrator_id = E.employee_ID
838    WHERE E.username = myName and E.username_password = myPassword;
839
840    SELECT S.address
841    INTO mySchoolAddress
842    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
843      INNER JOIN Adminstrators A ON A.adminstrator_id = employee_ID
844    WHERE E.username = myName and E.username_password = myPassword;
845
846    UPDATE Schools S
847    SET S.vision = vis
848    WHERE S.name = mySchoolName AND S.address = mySchoolAddress;
849  END //
850DELIMITER ;
851
852DELIMITER //
853# drop PROCEDURE updateAddress;
854# update admin's school address
855CREATE PROCEDURE updateAddress(naddress VARCHAR(50),myName  VARCHAR(50), myPassword VARCHAR(50))
856  BEGIN
857 DECLARE mySchoolName VARCHAR(50);
858    DECLARE mySchoolAddress VARCHAR(50);
859
860    SELECT S.name
861    INTO mySchoolName
862    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
863      INNER JOIN Adminstrators A ON a.adminstrator_id = E.employee_ID
864    WHERE E.username = myName and E.username_password = myPassword;
865
866    SELECT S.address
867    INTO mySchoolAddress
868    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
869      INNER JOIN Adminstrators A ON A.adminstrator_id = employee_ID
870    WHERE E.username = myName and E.username_password = myPassword;
871
872    UPDATE Schools S
873    SET S.address = naddress
874    WHERE S.name = mySchoolName AND S.address = mySchoolAddress;
875
876  END //
877DELIMITER ;
878
879
880
881DELIMITER //
882drop PROCEDURE updateMission;
883# update admin's school mission
884CREATE PROCEDURE updateMission(Mis VARCHAR(200), myName  VARCHAR(50), myPassword VARCHAR(50))
885  BEGIN
886    DECLARE mySchoolName VARCHAR(50);
887    DECLARE mySchoolAddress VARCHAR(50);
888
889    SELECT S.name
890    INTO mySchoolName
891    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
892      INNER JOIN Adminstrators A ON a.adminstrator_id = E.employee_ID
893    WHERE E.username = myName and E.username_password = myPassword;
894
895    SELECT S.address
896    INTO mySchoolAddress
897    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
898      INNER JOIN Adminstrators A ON A.adminstrator_id = employee_ID
899    WHERE E.username = myName and E.username_password = myPassword;
900
901    UPDATE Schools S
902    SET S.mission = Mis
903    WHERE S.name = mySchoolName AND S.address = mySchoolAddress;
904
905  END //
906
907
908DELIMITER ;
909
910
911DELIMITER //
912# update admin's school email
913CREATE PROCEDURE updateEmail(mail VARCHAR(50), myName VARCHAR(50),myPassword VARCHAR(50))
914  BEGIN
915   DECLARE mySchoolName VARCHAR(50);
916    DECLARE mySchoolAddress VARCHAR(50);
917
918    SELECT S.name
919    INTO mySchoolName
920    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
921      INNER JOIN Adminstrators A ON a.adminstrator_id = E.employee_ID
922    WHERE E.username = myName and E.username_password = myPassword;
923
924    SELECT S.address
925    INTO mySchoolAddress
926    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
927      INNER JOIN Adminstrators A ON A.adminstrator_id = employee_ID
928    WHERE E.username = myName and E.username_password = myPassword;
929
930    UPDATE Schools S
931    SET S.email = mail
932    WHERE S.name = mySchoolName AND S.address = mySchoolAddress;
933
934  END //
935DELIMITER ;
936
937
938
939# update admin's school mainLanguage
940DELIMITER //
941CREATE PROCEDURE updateMainLangauge(lang VARCHAR(50), myName VARCHAR(50) ,myPassword VARCHAR(50) )
942  BEGIN
943       DECLARE mySchoolName VARCHAR(50);
944    DECLARE mySchoolAddress VARCHAR(50);
945
946    SELECT S.name
947    INTO mySchoolName
948    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
949      INNER JOIN Adminstrators A ON a.adminstrator_id = E.employee_ID
950    WHERE E.username = myName and E.username_password = myPassword;
951
952    SELECT S.address
953    INTO mySchoolAddress
954    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
955      INNER JOIN Adminstrators A ON A.adminstrator_id = employee_ID
956    WHERE E.username = myName and E.username_password = myPassword;
957
958    UPDATE Schools S
959    SET S.main_language = lang
960    WHERE S.name = mySchoolName AND S.address = mySchoolAddress;
961
962  END //
963# drop PROCEDURE updateMainLangauge;
964DELIMITER ;
965SELECT *
966FROM Schools;
967
968# update admin's school number
969DELIMITER //
970# drop PROCEDURE updatePhoneNumber;
971CREATE PROCEDURE updatePhoneNumber(num INT, myName VARCHAR(50), myPassword VARCHAR(50))
972  BEGIN
973      DECLARE mySchoolName VARCHAR(50);
974    DECLARE mySchoolAddress VARCHAR(50);
975
976    SELECT S.name
977    INTO mySchoolName
978    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
979      INNER JOIN Adminstrators A ON a.adminstrator_id = E.employee_ID
980    WHERE E.username = myName and E.username_password = myPassword;
981
982    SELECT S.address
983    INTO mySchoolAddress
984    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
985      INNER JOIN Adminstrators A ON A.adminstrator_id = employee_ID
986    WHERE E.username = myName and E.username_password = myPassword;
987
988    UPDATE Schools S
989    SET S.phone_number = num
990    WHERE S.name = mySchoolName AND S.address = mySchoolAddress;
991  END //
992
993
994# 6 Post announcements with the following information: date, title, description and type (events, news,
995# trips ...etc) to the admin's school
996DELIMITER //
997CREATE PROCEDURE createAnnouncement(newdata        DATETIME, newtitle VARCHAR(50), newdescription VARCHAR(50),
998                                    newemployee_id INT, newtype VARCHAR(50))
999  BEGIN
1000    INSERT INTO Announcements (date, title, description, employee_id, type)
1001    VALUES (newdata, newtitle, newdescription, newemployee_id, newtype);
1002  END//
1003DELIMITER ;
1004SELECT *
1005FROM Announcements;
1006
1007
1008-- 7
1009# Create activities and assign every activity to a certain teacher. An activity has its own date, location
1010# in school, type, equipment(if any), and description of the admin's school
1011DELIMITER //
1012CREATE PROCEDURE createActivity(date               DATETIME, newlocation VARCHAR(50),
1013                                newadminstrator_id INT,
1014                                newname            VARCHAR(50),
1015                                newequipment       VARCHAR(50),
1016                                newtype            VARCHAR(50),
1017                                newdescription     VARCHAR(50))
1018  BEGIN
1019    INSERT INTO School_Activities (activity_Date, location, adminstrator_id, name, equipment, type, description)
1020    VALUES (date, newlocation, newadminstrator_id, newname, newequipment, newtype, newdescription);
1021
1022  END//
1023SELECT *
1024FROM School_Activities;
1025
1026
1027-- 8
1028-- note:also changes assigned teacher
1029# Change the teacher assigned to an activity . can also be used to assign a teacher to an assignment
1030DELIMITER //
1031CREATE PROCEDURE assignTeacherToActivity(date DATETIME, newlocation VARCHAR(50), newteacher_id INT, adminstrator_id INT)
1032  BEGIN
1033    UPDATE School_Activities S
1034    SET S.teacher_id = newteacher_id
1035    WHERE S.activity_Date = date AND S.location = newlocation;
1036
1037  END //
1038DELIMITER ;
1039
1040
1041-- 9
1042-- TODO: the course thingy
1043#  Assign teachers to courses that are taught in my school based on the levels it offers
1044DELIMITER //
1045# assigns a teacher to a course
1046CREATE PROCEDURE Assign_Teacher_to_Course(myID int , tID INT, cc int)
1047  BEGIN
1048   INSERT INTO Students_taughtBy_Teachers( course_code, teacher_id)
1049     VALUES (cc,tID);
1050
1051  END //
1052DELIMITER ;
1053
1054
1055SELECT *
1056FROM Students_taughtBy_Teachers;
1057-- 10
1058#  assigns a teacher (teacher 1) to supervise another teacher( teacher 2)
1059DELIMITER //
1060-- 1 supervises 2
1061CREATE PROCEDURE assignTeacherToSuperViseTeacher(id1 INT, id2 INT, adminID INT)
1062  BEGIN
1063    INSERT INTO Teachers_supervises_Teachers (teacher_id1, teacher_id2)
1064    VALUES (id1, id2);
1065  END;
1066DELIMITER ;
1067
1068
1069
1070-- 11 Accept or reject the application submitted by parents to their children. in the admin's school
1071# DROP PROCEDURE viewPendingStudents;
1072DELIMITER //
1073CREATE PROCEDURE viewPendingStudents(myID INT)
1074  BEGIN
1075    DECLARE mySchoolName VARCHAR(50);
1076    DECLARE mySchoolAddress VARCHAR(50);
1077
1078    SELECT S.name
1079    INTO mySchoolName
1080    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
1081      INNER JOIN Adminstrators A ON A.adminstrator_id = E.employee_ID
1082    WHERE E.employee_ID = myID;
1083
1084    SELECT S.address
1085    INTO mySchoolAddress
1086    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
1087      INNER JOIN Adminstrators A ON A.adminstrator_id = E.employee_ID
1088    WHERE E.employee_ID = myID;
1089
1090    SELECT S.*
1091    FROM Students S
1092    WHERE S.school_address = mySchoolAddress AND S.school_name = mySchoolName AND S.accepted IS NULL;
1093  END;
1094DELIMITER ;
1095
1096
1097DELIMITER //
1098
1099CREATE PROCEDURE acceptOrRejectStudents(myID INT, sID INT, decission INT(1))
1100  BEGIN
1101    DECLARE mySchoolName VARCHAR(50);
1102    DECLARE mySchoolAddress VARCHAR(50);
1103
1104    SELECT S.name
1105    INTO mySchoolName
1106    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
1107      INNER JOIN Adminstrators A ON A.adminstrator_id = E.employee_ID
1108    WHERE E.employee_ID = myID;
1109
1110    SELECT S.address
1111    INTO mySchoolAddress
1112    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
1113      INNER JOIN Adminstrators A ON A.adminstrator_id = E.employee_ID
1114    WHERE E.employee_ID = myID;
1115
1116    UPDATE Students S
1117    SET S.accepted = decission
1118    WHERE S.school_address = mySchoolAddress AND S.school_name = mySchoolName AND S.accepted IS NULL AND sID = S.ssn;
1119
1120  END //
1121
1122-- 9 view the questions asked by the students for each course I teach.
1123DELIMITER //
1124
1125CREATE PROCEDURE Teacher_View_Questions(myID INT)
1126  BEGIN
1127    DECLARE mySchoolName VARCHAR(50);
1128    DECLARE mySchoolAddress VARCHAR(50);
1129
1130    SELECT S.name
1131    INTO mySchoolName
1132    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
1133      INNER JOIN Teachers T ON T.teacher_id = E.employee_ID
1134    WHERE E.employee_ID = myID;
1135
1136    SELECT S.address
1137    INTO mySchoolAddress
1138    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
1139      INNER JOIN Teachers T ON T.teacher_id = E.employee_ID
1140    WHERE E.employee_ID = myID;
1141
1142    SELECT Q.*
1143    FROM Questions Q INNER JOIN Courses C ON Q.course_code = C.course_code
1144      INNER JOIN Students S ON S.ssn = Q.ssn
1145      INNER JOIN Students_taughtBy_Teachers stt ON stt.ssn = S.ssn AND stt.course_code = C.course_code
1146    WHERE stt.teacher_id = myID;
1147  END //
1148DELIMITER ;
1149
1150#Answer the questions asked by the students for each course I teach.
1151-- 9
1152DELIMITER //
1153CREATE PROCEDURE Teacher_Answers_Questions(myID INT, qnum INT, cont VARCHAR(500))
1154  BEGIN
1155    INSERT INTO Answers (teacher_id, question_number, content)
1156    VALUES (myID, qnum, cont);
1157  END //
1158DELIMITER ;
1159
1160
1161
1162-- TODO: test above  proc
1163-- 10
1164-- TODO:verify 10
1165# View a list of students that i teach categorized by the grade and ordered by their name (first name
1166# and last name).
1167
1168DELIMITER //
1169CREATE PROCEDURE View_Students(myID INT)
1170  BEGIN
1171    SELECT
1172      S.name,
1173      g.student_grade
1174    FROM Students_taughtBy_Teachers stt INNER JOIN Students S ON stt.ssn = S.ssn
1175      INNER JOIN grade g ON g.teacher_id = stt.teacher_id
1176    WHERE stt.teacher_id = myID
1177    ORDER BY S.name;
1178  END //
1179DELIMITER ;
1180
1181
1182# View a list of students that did not join any activity.
1183DELIMITER //
1184CREATE PROCEDURE Students_Without_Activity(myId INT)
1185  BEGIN
1186    DECLARE mySchoolName VARCHAR(50);
1187    DECLARE mySchoolAddress VARCHAR(50);
1188
1189    SELECT S.name
1190    INTO mySchoolName
1191    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
1192    WHERE E.employee_ID = myID;
1193
1194    SELECT S.address
1195    INTO mySchoolAddress
1196    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
1197    WHERE E.employee_ID = myID;
1198
1199
1200    SELECT S.*
1201    FROM Students S, Activities_Involve_Students ais
1202    WHERE S.school_name = mySchoolName AND S.school_address = mySchoolAddress AND S.ssn NOT IN (SELECT ais.student_ssn
1203                                                                                                FROM
1204                                                                                                  Activities_Involve_Students ais);
1205
1206
1207  END //
1208DELIMITER ;
1209
1210# return the average rating of the teacher
1211DELIMITER //
1212CREATE PROCEDURE Rate_Teacher_Avg(OUT res FLOAT, myId INT)
1213  BEGIN
1214    DECLARE total FLOAT;
1215    DECLARE cnt FLOAT;
1216
1217    SELECT count(prt.rating)
1218    INTO cnt
1219    FROM Teachers T INNER JOIN Parents_rateAndreview_Teachers prt ON prt.teacher_id = T.teacher_id
1220    WHERE T.teacher_id = myId;
1221
1222    SELECT sum(prt.rating)
1223    INTO total
1224    FROM Teachers T INNER JOIN Parents_rateAndreview_Teachers prt ON prt.teacher_id = T.teacher_id
1225    WHERE T.teacher_id = myId;
1226
1227    SET res = total / cnt;
1228
1229  END;
1230DELIMITER ;
1231-- youmna
1232-- 1
1233-- used to create a new school
1234CREATE PROCEDURE Create_school(
1235  school_name   VARCHAR(50),
1236  address       VARCHAR(50),
1237  general_info  VARCHAR(50),
1238  vision        VARCHAR(50),
1239  mission       VARCHAR(50),
1240  email         VARCHAR(50),
1241  main_language VARCHAR(50),
1242  phone_number  VARCHAR(50)
1243)
1244  BEGIN
1245    INSERT INTO Schools
1246    VALUES (school_name, address, general_info, vision, mission, email, main_language, phone_number);
1247  END;
1248DELIMITER ;
1249
1250-- CALL Create_school('Rowad2','NasrCity','schoolInfo','ourVision','ourMission','Rowad@yahoo.com','English','0123456789');
1251
1252-- 2
1253-- used to add courses
1254CREATE PROCEDURE ADD_Course(
1255
1256  course_code  INTEGER,
1257  course_name  VARCHAR(50),
1258  description  VARCHAR(50),
1259  course_level VARCHAR(50),
1260  grade        INTEGER
1261)
1262  BEGIN
1263    INSERT INTO Courses (course_code, name, description)
1264    VALUES (course_code, course_name, description);
1265    INSERT INTO Courses_offer_Levels VALUES (grade, course_code, level);
1266    INSERT INTO courses_has_prerequisite_courses VALUES (course_code1, course_code2);
1267  END;
1268DELIMITER ;
1269
1270#  CALL ADD_Course(10,'Math3','Mathematics course for high school students', 'High',12);
1271#  CALL ADD_Course(11,'Math3','Mathematics course for high school students', 'High',12);
1272
1273
1274-- used to add prequiisite courses to other courses
1275CREATE PROCEDURE ADD_prreRequisites(course_code1 INTEGER, course_code2 INTEGER)
1276  BEGIN
1277    INSERT INTO Courses_has_prerequisite_Courses (course_code1, course_code2) VALUES (course_code1, course_code2);
1278  END;
1279
1280DELIMITER ;
1281
1282
1283# CALL Add_Admins('Reem', 'Adel', 'Aboushawareb', 'F', '1996-5-18', 'tagamooo', 'Reem@yahoo.com', 'Reem.AbouShwareb',
1284#                 'balabizo', 'stFatima', 'international');
1285
1286-- 4
1287-- used to delete schools
1288CREATE PROCEDURE Delete_school(school_name VARCHAR(50))
1289  BEGIN
1290    DELETE FROM Schools
1291    WHERE name = school_name;
1292
1293  END;
1294
1295-- As a parent
1296
1297-- 1
1298-- used by parents to sign up on the site
1299CREATE PROCEDURE Sign_Up(f_name VARCHAR(50), l_name VARCHAR(50), email VARCHAR(50), address VARCHAR(50),
1300                         home_phone_number VARCHAR(50), parent_user_name VARCHAR(50), parent_password VARCHAR(50))
1301  BEGIN
1302
1303    INSERT INTO Parents (user_name, password, first_name, last_name, home_phone, email) VALUES
1304      (parent_user_name, parent_password, f_name, l_name, home_phone_number, email);
1305
1306  END;
1307CALL Sign_Up('hady','mohamed','hadyyasser','wara','011148','hadyz1100','keytroni');
1308CREATE PROCEDURE parents_mobileNumbers(mobileNumber VARCHAR(50))
1309  BEGIN
1310    INSERT INTO Parents  (home_phone) VALUES (mobileNumber);
1311
1312  END;
1313DELIMITER ;
1314
1315-- 2
1316-- used by parent to apply for their child in a school
1317CREATE PROCEDURE Apply_for_child(school_name VARCHAR(50), ssn INTEGER, name VARCHAR(50), birth_date DATETIME,
1318                                 gender  VARCHAR(1),parent_user_name VARCHAR(50))
1319  BEGIN
1320    INSERT INTO Students (ssn, user_name, gender, name, birth_date)
1321    VALUES (ssn, name, gender,name, birth_date);
1322
1323
1324    INSERT INTO Parents_apply_Students_Schools ( parent_user_name, student_ssn, school_name) VALUES (parent_user_name,ssn ,school_name);
1325
1326  END;
1327
1328# INSERT INTO Parents (user_name, password, first_name, last_name, home_phone, email, address)
1329# VALUES ('Mahmoud.Abdelaziz','balabizo','Mahmoud','Abdelaziz','0232424244','Mahmoud@gmail.com','NasrCity');
1330
1331# CALL Apply_for_child('Elmasrya',7,'Laila','2001-2-3','F','Mahmoud.Abdelaziz');
1332
1333-- 3
1334
1335
1336# CALL listOfSchools_Accepted(3);
1337
1338-- 4
1339
1340
1341-- views list of schools in which student is accepted
1342CREATE PROCEDURE listOfSchools_Accepted(IN student_ssn INTEGER)
1343  BEGIN
1344
1345    SELECT p.school_name
1346    FROM Parents_apply_Students_Schools p
1347    WHERE p.accept = 1 AND p.student_ssn = student_ssn
1348    GROUP BY p.student_ssn;
1349
1350  END;
1351DELIMITER ;
1352
1353
1354
1355# CALL listOfSchools_Accepted(3);
1356
1357-- 5
1358-- used by parent to enroll their student in a shcool
1359CREATE PROCEDURE enroll_MyStudent(student_ssn INTEGER, school_name VARCHAR(50), school_address VARCHAR(50))
1360  BEGIN
1361    INSERT INTO Students_Enrolled_Schools (ssn, school_name, school_address) VALUES
1362      (student_ssn, school_name, school_address);
1363
1364  END;
1365DELIMITER ;
1366# DROP PROCEDURE listOfReports;
1367-- views
1368CREATE PROCEDURE listOfReports(parent_user_name VARCHAR(50))
1369  BEGIN
1370    SELECT content
1371    FROM Reports  WHERE user_name =parent_user_name
1372    GROUP BY student_ssn;
1373  END;
1374DELIMITER ;
1375
1376 -- INSERT INTO Reports( comment, content, issue_date,  user_name, student_ssn, teacher_id,report_number)
1377    -- VALUES ('you are Good','Wohooo','2016-11-23','Nader.Elghandoor',2,3,1);
1378
1379# CALL listOfReports('Nader.Elghandoor');
1380
1381-- 6
1382-- used to reply to reports
1383CREATE PROCEDURE ReplyToReports(content VARCHAR(50), user_name VARCHAR(50), teacher_id INTEGER)
1384  BEGIN
1385
1386    INSERT INTO Parents_reply_Reports VALUES (content, user_name, teacher_id);
1387  END;
1388DELIMITER ;
1389
1390-- 7
1391-- used by parents to view their children school list
1392CREATE PROCEDURE MyChildrenSchoolList(parent_user_name VARCHAR(50))
1393  BEGIN
1394    SELECT p.school_name
1395    FROM students s INNER JOIN Parents_apply_Students_Schools p ON parent_user_name = p.parent_user_name
1396    GROUP BY p.school_name;
1397  END;
1398DELIMITER ;
1399# CALL MyChildrenSchoolList('Nader.elghandoor');
1400SELECT *
1401FROM Parents;
1402-- 8
1403-- views announcements made int he past 10 days
1404CREATE PROCEDURE Announcements_inPastDays(student_ssn INTEGER)
1405
1406  BEGIN
1407     DECLARE x_interval INT DEFAULT 0;
1408      set x_interval =(CURDATE()-INTERVAL 10 DAY );
1409    SELECT x_interval ;
1410    SELECT A.description
1411      FROM Announcements A
1412  WHERE A.date  >= DATE(NOW()) - INTERVAL 10 DAY AND
1413    exists( SELECT s.ssn
1414    FROM Students_Enrolled_Schools s
1415    WHERE  s.ssn =student_ssn);
1416  END;
1417DELIMITER ;
1418
1419
1420# INSERT INTO Announcements (date, title, description, employee_id)
1421# VALUES ('2016-11-22','MileStone PostPoned :) ','you all got the full mark',2);
1422
1423# CALL Announcements_inPastDays;
1424
1425
1426-- 9
1427-- used by parent to rate their student's teacher
1428CREATE PROCEDURE Rate_MyChildren_Teachers2(rate INTEGER, t_id INTEGER, parent_user_name VARCHAR(50))
1429  BEGIN
1430    INSERT INTO Parents_rateAndreview_Teachers (rate, teacher_id, parent) VALUES (rate, t_id, parent_user_name);
1431  END;
1432DELIMITER ;
1433
1434-- views parent children in schools
1435CREATE PROCEDURE getMychild_ssn(IN parent_user_name VARCHAR(50), OUT student_ssn INTEGER)
1436  BEGIN
1437    SELECT s.ssn
1438    FROM Parents
1439      INNER JOIN Students s ON parent_user_name = s.user_name
1440    INTO student_ssn;
1441  END;
1442DELIMITER ;
1443
1444-- 11
1445-- deletes parent review
1446CREATE PROCEDURE DeleteReview(parent_user_name VARCHAR(50), parent_rate_number INTEGER)
1447  BEGIN
1448
1449    DELETE FROM Parents_rateAndreview_Schools
1450    WHERE parent_username = parent_user_name AND
1451          rate = parent_rate_number;
1452  END;
1453DELIMITER ;
1454
1455# CALL DELETEReview('Nader.elghandoor', 1);
1456
1457
1458# CALL topSchools();
1459
1460-- 14
1461-- views inernational school with highest reputation
1462CREATE PROCEDURE InternationalSchool_highestReputation()
1463  BEGIN
1464    DECLARE international INT DEFAULT 0;
1465    DECLARE national INT DEFAULT 0;
1466    SELECT count(review)
1467    FROM Parents_rateAndreview_Schools s INNER JOIN Types t
1468        ON s.school_name = t.school_name AND t.type = 'national'
1469    INTO national;
1470
1471    SELECT count(review)
1472    FROM Parents_rateAndreview_Schools s INNER JOIN Types t
1473        ON s.school_name = t.school_name AND t.type = 'internationl'
1474    INTO international;
1475
1476    IF (national > international)
1477    THEN
1478      SELECT s.name
1479      FROM Schools s INNER JOIN Types t
1480          ON s.name = t.school_name AND t.type = 'national';
1481    ELSE SELECT s.name
1482         FROM Schools s INNER JOIN Types t
1483             ON s.name = t.school_name AND t.type = 'international';
1484    END IF;
1485  END;
1486DELIMITER ;
1487
1488# CALL InternationalSchool_highestReputation();
1489
1490
1491-- helper procedures and View Procedures
1492CREATE PROCEDURE view_students_I_Teach(teacher_id INTEGER)
1493  BEGIN
1494    SELECT t.ssn
1495    FROM Students_taughtBy_Teachers t
1496    WHERE t.teacher_id = teacher_id;
1497
1498  END;
1499DELIMITER ;
1500
1501# CALL view_students_I_Teach(3);
1502
1503-- reem
1504-- used by students to update their data
1505create PROCEDURE student_update_his_data
1506    (student_ssn int ,gender varchar(50),
1507    student_name varchar(50),
1508    birth_date datetime,
1509    password varchar(50),
1510    sch_name VARCHAR(50),
1511    sch_address VARCHAR(50))
1512BEGIN
1513
1514    Update  Students SET Students.gender=gender,
1515      Students.name=student_name,
1516      Students.birth_date=birth_date,
1517      Students.password=password,
1518      Students.school_address=sch_address,
1519      Students.school_name=sch_name
1520
1521    where Students.ssn = student_ssn;
1522 END;
1523-- used by students to view courses
1524create procedure student_view_courses(user VARCHAR(50),pass VARCHAR(50))
1525  begin
1526      select distinct  c.name
1527      from  Courses c inner join Courses_offer_Levels col
1528      on   c.course_code=col.course_code
1529         where col.grade= (
1530           select s.grade
1531       from Students s
1532        where s.user_name=user  and s.password=pass and s.school_address=c.school_address and s.school_name=c.school_name
1533           );
1534  END;
1535-- use dby students to post questions
1536CREATE PROCEDURE student_post_question(user varchar(50),pass varchar(50),question VARCHAR(50)
1537  ,courseCode int)
1538begin
1539    declare student_ssn int;
1540  declare x int;
1541
1542  select   s.ssn into student_ssn
1543      from Students s
1544       where s.user_name=user and s.password=pass ;
1545
1546INSERT into Questions (course_code,ssn,content)
1547          values(courseCode,student_ssn,question);
1548  END;
1549-- views all questions
1550create procedure view_all_questions(user VARCHAR(50),pass varchar(50),courseCode VARCHAR(50))
1551  BEGIN
1552declare z VARCHAR(50);
1553    declare y VARCHAR(50);
1554    select s.school_address into y
1555    from Students s
1556    where s.user_name=user and s.password=pass;
1557
1558    select s.school_name into z
1559    from Students s
1560    where s.user_name=user and s.password=pass;
1561
1562   select q.content
1563     from Questions q inner join Courses c
1564   on q.course_code=c.course_code
1565  where c.school_name=z and c.school_address=y and c.course_code=courseCode;
1566
1567  END;
1568-- used by students to post assignment solution
1569CREATE PROCEDURE  student_solve_assignment( user varchar(50),pass varchar(50),solution varchar(50),assignmentNumber  int)
1570   BEGIN
1571     declare x int;
1572     select s.ssn into x
1573       from Students s
1574         where s.user_name=user and s.password= pass;
1575 insert into Assignments_solve_Students (solution, student_ssn, assignment_number)
1576     values(solution,x,assignmentNumber);
1577
1578END;
1579-- used by student to view grade
1580CREATE PROCEDURE student_view_grade(user varchar(50),pass varchar(50),assignment_number int,courseCode int)
1581 BEGIN
1582    declare ssn_student int;
1583   select s.ssn into ssn_student
1584   from Students  s
1585    where s.user_name=user and s.password=pass;
1586
1587     select a.grade
1588       from Assignments_solve_Students a inner join Assignments_posteBy_Teachers ass
1589     on ass.assignment_number=a.assignment_number
1590   where a.student_ssn= ssn_student and ass.course_code-courseCode;
1591
1592 end;
1593-- used by students to view announcements
1594create PROCEDURE student_view_announcement(user VARCHAR(50),pass int)
1595      BEGIN
1596        declare x VARCHAR(50);
1597        declare y VARCHAR(50);
1598         select s.school_name into x
1599           from Students s
1600           where s.user_name=user and s.password=pass;
1601         select s.school_address into y
1602           from Students s
1603           where s.user_name=user and s.password=pass;
1604           select a.*
1605          from Announcements a inner join Employees e
1606            on a.employee_id=e.employee_ID
1607        where a.date >=(a.date -10)  and e.school_name=x and e.school_address=y;
1608      END;
1609DROP  PROCEDURE view_announcement;
1610create PROCEDURE view_announcement(x VARCHAR(50),y VARCHAR(50))
1611      BEGIN
1612           select a.*
1613          from Announcements a inner join Employees e
1614            on a.employee_id=e.employee_ID
1615        where a.date >=(a.date -10)  and e.school_name=x and e.school_address=y;
1616      END;
1617CALL view_announcement('saintfatima','66 Ismail Al Kabbani');
1618SELECT *
1619FROM Employees;
1620-- used by student to view activity
1621 create procedure student_view_activities(user VARCHAR(50),pass VARCHAR(50) )
1622  begin
1623    declare x VARCHAR(50);
1624    declare y varchar(50);
1625    select s.school_name into x
1626      from Students s
1627    where s.user_name=user and s.password=pass;
1628    select s.school_address into y
1629      from Students s
1630    where s.user_name=user and s.password=pass;
1631
1632    select a.activity_Date,a.location,a.description,a.equipment,a.name,a.type,e1.first_name,e1.middle_name,e1.last_name,t.experience_year,t.seniority_level
1633      from School_Activities a inner join Employees e1
1634    on e1.employee_ID=a.teacher_id
1635    inner join Employees e2
1636    on e2.employee_ID=a.adminstrator_id
1637    inner join Teachers t
1638          on t.teacher_id=e1.employee_ID
1639    where e1.school_address=y and e1.school_name=x and e2.school_address=y and e2.school_name=x;
1640  END;
1641
1642-- used to apply for activity
1643create procedure students_applyForActivites(user varchar(50),pass VARCHAR(50),date DATETIME,activity_location VARCHAR(50))
1644  begin
1645    declare x int ;
1646    declare Y VARCHAR(50);
1647  select s.ssn into x
1648    from Students s
1649    where s.user_name=user and  s.password=pass;
1650   select s.type into y
1651     from School_Activities s
1652     where s.activity_Date=date and s.location=activity_location;
1653    if x not IN (
1654     select sa.student_ssn
1655       from Activities_Involve_Students sa inner join  School_Activities s
1656         on s.activity_Date=sa.activity_Date and s.location=sa.location
1657       where sa.activity_Date=date and   s.type=y)
1658      THEN
1659      insert into Activities_Involve_Students values (x,date,activity_location);
1660
1661     else select
1662            'You Can`t apply for this activity Please choose Another one or this one in another time';
1663    END IF;
1664
1665  END;
1666-- used by students to view assignments
1667 CREATE PROCEDURE student_view_assignments(user VARCHAR(50),pass VARCHAR(50))
1668  begin
1669  declare x int;
1670    declare y varchar(50);
1671    declare z VARCHAR(50);
1672      select s.grade into x
1673      From Students s
1674     where s.user_name=user and s.password=pass;
1675
1676    select s.school_address into y
1677    from Students s
1678    where s.user_name=user and s.password=pass;
1679
1680    select s.school_name into z
1681    from Students s
1682    where s.user_name=user and s.password=pass;
1683
1684     select  a.*
1685      from Assignments_posteBy_Teachers t inner join Courses_offer_Levels c
1686        on t.course_code=c.course_code
1687     inner join Assignments a
1688    on a.assignment_number=t.assignment_number
1689        inner join Employees e
1690        on e.employee_ID=t.teacher_id
1691    WHERE e.school_address=y and e.school_name=z and c.grade=x;
1692  END;
1693
1694
1695-- used to make student join a club
1696CREATE PROCEDURE student_join_clubs (user varchar(50),pass varchar(50),clubName VARCHAR(50))
1697   BEGIN
1698    declare student_grade int;
1699    declare student_ssn int;
1700     declare x VARCHAR(50);
1701     DECLARE y VARCHAR(50);
1702      DECLARE z VARCHAR(50);
1703     select s.school_name into x
1704     from Students  s
1705     where s.user_name=user and  s.password=pass;
1706
1707     select s.school_address into y
1708   from Students  s
1709    where s.user_name=user and  s.password=pass;
1710
1711   select s.grade into student_grade
1712   from Students  s
1713    where s.user_name=user and  s.password=pass;
1714
1715
1716     select c.club_name into z
1717       from Clubs c
1718       where c.school_address=y and c.school_name=x and c.club_name=clubName;
1719    if(student_grade >=10 and z is not null)
1720       then
1721         select s.ssn into student_ssn
1722           from Students s
1723           where s.user_name=user and  s.password=pass;
1724
1725     insert into Clubs_joinBy_Students(club_name,ssn)
1726    values(clubName,student_ssn);
1727      ELSE
1728        select 'Sorry You can`t join that club';
1729   END IF;
1730
1731  end;
1732
1733
1734
1735-- lets student seachf or course
1736 create PROCEDURE student_searchFor_courses(user VARCHAR(50),pass VARCHAR(50),course_name VARCHAR(50),code int)
1737   BEGIN
1738 if (course_name is not  null)
1739  then
1740
1741    select c.description,c.course_code,c.name
1742         from Courses_offer_Levels cl inner join Students s
1743           on s.grade =cl.grade
1744         inner join Courses c
1745           on c.school_name=s.school_name and  c.school_address=s.school_address and c.course_code=cl.course_code
1746where c.name= course_name and s.user_name=user and  s.password=pass ;
1747
1748
1749elseif (code is not null)
1750  then
1751
1752    select c.name,c.description,c.course_code
1753          from Students s inner join Courses_offer_Levels cl
1754            on cl.grade=s.grade
1755      inner join Courses c
1756            on c.school_address=s.school_address and c.school_name=s.school_name and c.course_code=cl.course_code
1757           where c.course_code= code and  s.user_name=user and  s.password=pass ;
1758   else SELECT 'You should enter course name or code';
1759END IF;
1760   END;
1761-- used by teacher to sign up in school
1762create procedure teacher_sign_up ( fname varchar(50) , mname varchar(50) , lname varchar(50), gend varchar(1) , birth_date datetime
1763  , mail varchar(50) , address varchar(50)   , seniority int , first_year_of_work DATETIME )
1764  BEGIN
1765    # teacher_id , seniority_level , first_year_of_work
1766    DECLARE x int;
1767    insert into  Employees (first_name,middle_name,last_name,email,gender,e_address,birth_date) values(fname,mname,lname, mail ,gend
1768      , address,birth_date );
1769    SELECT count(employee_ID) into x
1770      FROM Employees;
1771
1772
1773    INSERT into teachers ( teacher_id ,seniority_level, first_year_of_work ) VALUES (x , seniority , first_year_of_work );
1774  END;
1775
1776###
1777# INSERT INTO Schools (name, address, general_info, vision, mission, email, main_language, phone_number) VALUES ('name', 'address', 'general_info' , 'vision', 'mission', 'email', 'main_language'
1778# , 'phone_number');
1779# INSERT INTO Employees ( username , username_password , first_name , middle_name , last_name , gender , salary , email , e_address , birth_date ,
1780# school_name , school_address) values ('mar' , 'bal' , 'mariem' , 'adel' , 'abou' , 'f' , 4500 , 'fjdk' , 'fdsjk' , '1989-12-12' , 'name' , 'address'  );
1781# CALL teacher_sign_up( 'mariem' , 'adel' , 'aboushawareb' , 'f' , '1198-02-02' , 'fdsfds' , 'fdsf' , 5 , '1998-02-05' );
1782
1783# drop table employees;
1784# drop table teachers;
1785##ended testing
1786
1787#
1788# SELECT *
1789# FROM Courses;
1790# drop PROCEDURE teachers_views_courses_taught_by_him;
1791# call teachers_views_courses_taught_by_him('mar1' , 'bal1' );
1792# SELECT *
1793#   from Courses;
1794# SELECT *
1795#   FROM Courses_offer_Levels;
1796# testing
1797
1798-- used by teacher to view the courses he teaches
1799DELIMITER //
1800create procedure teachers_views_courses_taught_by_him (teacherUserName VARCHAR(50) , password varchar(50) )
1801
1802BEGIN
1803
1804declare t_ID INT;
1805SELECT  employee_ID into t_ID
1806from Employees
1807  WHERE Employees.username = teacherUserName AND Employees.username_password = password;
1808  SELECT DISTINCT Courses.course_code,  name   , grade , level
1809  from Courses
1810    INNER JOIN Students_taughtBy_Teachers  ON Courses.course_code = Students_taughtBy_Teachers.course_code AND
1811              Students_taughtBy_Teachers.teacher_id = t_ID
1812    INNER JOIN Courses_offer_Levels  ON Courses.course_code = Courses_offer_Levels.course_code
1813  ORDER BY Courses_offer_Levels.grade;
1814END;
1815
1816DELIMITER //
1817
1818
1819##testing
1820# INSERT INTO Schools (name, address, general_info, vision, mission, email, main_language, phone_number) VALUES ('name1', 'address1', 'general_info' , 'vision', 'mission', 'email', 'main_language'
1821# , 'phone_number');
1822# INSERT INTO Schools (name, address, general_info, vision, mission, email, main_language, phone_number) VALUES ('name2', 'address2', 'general_info' , 'vision', 'mission', 'email', 'main_language'
1823# , 'phone_number');
1824# INSERT INTO Employees ( username , username_password , first_name , middle_name , last_name , gender , salary , email , e_address , birth_date ,
1825# school_name , school_address) values ('mar1' , 'bal1' , 'mariem1' , 'adel1' , 'abou1' , 'f' , 45001 , 'fjdk1' , 'fdsjk1' , '1989-12-10' , 'name1' , 'address1'  );
1826
1827# INSERT INTO Teachers (seniority_level, first_year_of_work, teacher_id) VALUES ( 5 , '1888-12-12' , 2 );
1828# INSERT INTO Courses (course_code, name, description, school_address,schoole_name)
1829# VALUES (101 , 'course name' , 'description' , 'school address',
1830# 'school name');
1831# INSERT INTO Courses (course_code, name, description, school_address,schoole_name)
1832# VALUES (303 , 'course name' , 'description' , 'school address',
1833# 'school name');
1834# INSERT INTO Courses (course_code, name, description, school_address,schoole_name)
1835# VALUES (404 , 'course name' , 'description' , 'school address',
1836# 'school name');
1837# INSERT INTO Courses (course_code, name, description, school_address,schoole_name)
1838# VALUES (505 , 'course name' , 'description' , 'school address',
1839# 'school name');
1840
1841
1842# INSERT INTO Courses (course_code, name, description, school_address, school_name) VALUES (202 , 'course name' , 'description' , 'school address' ,'school name');
1843# INSERT INTO Students_taughtBy_Teachers (course_code , teacher_id ) VALUES ( 101 , 2);
1844# INSERT INTO Students_taughtBy_Teachers (course_code , teacher_id ) VALUES ( 202 , 2);
1845# INSERT INTO Students_taughtBy_Teachers (course_code , teacher_id ) VALUES ( 303 , 2);
1846# INSERT INTO Students_taughtBy_Teachers (course_code , teacher_id ) VALUES ( 404 , 2);
1847# INSERT INTO Students_taughtBy_Teachers (course_code , teacher_id ) VALUES ( 505 , 2);
1848# INSERT INTO Courses_offer_Levels (grade, level, course_code) VALUES (5 , 'primary' , 101);
1849# INSERT INTO Courses_offer_Levels (grade, level, course_code) VALUES (5 , 'highschool' , 202);
1850# INSERT INTO Courses_offer_Levels (grade, level, course_code) VALUES (5 , 'highschool' , 303);
1851# INSERT INTO Courses_offer_Levels (grade, level, course_code) VALUES (6 , 'highschool' , 505);
1852
1853##testing
1854
1855-- use dby teacher to write reports
1856create PROCEDURE teachers_writes_reports (user VARCHAR(50),pass VARCHAR(50),ssn int, teacher_comment VARCHAR(50),
1857  report_content VARCHAR(50) , report_issue_date DATETIME  )
1858BEGIN
1859  declare x int ;
1860select t.employee_ID into x
1861  from Employees t
1862  where t.username=user and t.username_password=pass;
1863  insert into Reports (comment, content, issue_date, student_ssn, teacher_id)
1864    values (teacher_comment,report_content,report_issue_date,ssn,x);
1865END;
1866
1867
1868
1869##testing
1870
1871#
1872# CALL teachers_writes_reports( 'mar1' , 'bal1' , 1, 'excellent' , 'content' , '1998-12-12'  );
1873# CALL teachers_writes_reports( 'mar1' , 'bal1' , 2, 'excellent' , 'content' , '1998-12-12'  );
1874# SELECT *
1875# FROM Reports;
1876
1877##testing
1878-- shows student who is in the most number of clubs
1879create PROCEDURE member_in_most_clubs (username VARCHAR(50) , password VARCHAR(50))
1880  BEGIN
1881  DECLARE s_name VARCHAR(50) ;
1882  DECLARE  ssn_count INTEGER;
1883
1884
1885  select s.ssn,s.name,count(c.club_name)
1886    from Students s inner join Clubs_joinBy_Students c
1887      on s.ssn=c.ssn
1888    GROUP BY s.ssn;
1889
1890END;
1891
1892
1893-- 13
1894-- veiws top 10 most  rated schools
1895CREATE PROCEDURE topSchools()
1896  BEGIN
1897    DECLARE reviews_num INT DEFAULT 0;
1898    DECLARE students_num INT DEFAULT 0;
1899
1900    SELECT count(review)
1901    FROM Parents_rateAndreview_Schools
1902    INTO reviews_num;
1903    SELECT count(ssn)
1904    FROM Students_Enrolled_Schools
1905    INTO students_num;
1906    IF (students_num > reviews_num)
1907    THEN
1908      SELECT school_name
1909      FROM Students_Enrolled_Schools
1910      ORDER BY students_num DESC  LIMIT 10;
1911    ELSE
1912
1913      SELECT pr.school_name
1914      FROM Parents_rateAndreview_Schools pr
1915      WHERE pr.school_name NOT IN (
1916        SELECT s.school_name
1917        FROM Parents p INNER JOIN Students s
1918            ON p.user_name = s.user_name
1919          INNER JOIN
1920          Students_Enrolled_Schools st ON st.ssn = s.ssn AND
1921                                          st.school_name = s.school_name
1922           LIMIT 10
1923
1924      );
1925
1926    END IF;
1927  END;
1928DELIMITER ;
1929
1930
1931-- starte of insertions
1932
1933
1934#Schools
1935insert into Schools values('saintfatima','66 Ismail Al Kabbani',
1936                           'avaliable at the coordinator','A vibrant community striving for excellence.
1937','To develop inquiring, knowledgeable',
1938                           'saintfatima@hotmail.com','english','0123456789');
1939insert into Schools values('El rowad','8 Mostafa El Nahhas',
1940                           'avaliable at the coordinator','A vibrant community striving for excellence.
1941','To develop inquiring, knowledgeable',
1942                           'info@elrowadcollege.net','english','0224723271');
1943
1944#Employees
1945insert into Employees (username, username_password, first_name, middle_name, last_name, gender,  email, e_address, birth_date, school_name, school_address)
1946VALUES ('mariem.shwareb','blabizo','mariem','mohamed','shwareb','f','mariem_adel@gmail.com','18 slah salem amarat AlAbor',
1947                       '1980-10-30 23:59:59','saintfatima','66 Ismail Al Kabbani');
1948
1949INSERT INTO Employees(first_name, middle_name, last_name, gender, birth_date, e_address, email, username, username_password, school_name, school_address)
1950VALUES('Farah','Emad','Ahmed','F','1968-10-30 23:59:59','NasrCity','Farah@gmail.com','Farah.Ahmed','balabizo','saintfatima','66 Ismail Al Kabbani');
1951
1952insert into Employees(username, username_password, first_name, middle_name, last_name, gender,  email, e_address, birth_date, school_name, school_address)
1953VALUES ('Youmna.Ghandor','blabizo','Youmna','Nader','ElGhandor','f','youmna.elghandour@gmail.com','22 abbassia Street',
1954                       '1970-10-30 23:59:59','saintfatima','66 Ismail Al Kabbani');
1955
1956
1957insert into Employees(username, username_password, first_name, middle_name, last_name, gender,  email, e_address, birth_date, school_name, school_address)
1958VALUES ('abeer.elsayed','blabizo','Abeer','Mohamed','ElSayed','f','abeer_elsayed@gmail.com','22 helioblies street',
1959                       '1985-10-30 23:59:59','saintfatima','66 Ismail Al Kabbani');
1960insert into Employees (username, username_password, first_name, middle_name, last_name, gender,  email, e_address, birth_date, school_name, school_address)
1961VALUES ('ahmed.shwareb','blabizo','ahmed','mohamed','shwareb','m','ahmed44@gmail.com','15 slah salem amarat AlAbor',
1962                       '1985-10-1 23:59:59','saintfatima','66 Ismail Al Kabbani');
1963#
1964# insert into Employees (username, username_password, first_name, middle_name, last_name, gender,  email, e_address, birth_date, school_name, school_address)
1965# VALUES ('mariem.shwareb','blabizo','mariem','mohamed','shwareb','f','mariem_adel@gmail.com','18 slah salem amarat AlAbor',
1966#                        '1980-10-30 23:59:59','saintfatima','8 Mostafa El Nahhas');
1967
1968insert into Employees (username, username_password, first_name, middle_name, last_name, gender,  email, e_address, birth_date, school_name, school_address)
1969VALUES ('hady.mohamed','blabizo','hady','mohamed','osman','m','hady_mohamed@gmail.com','10 mahmoud Tawfik nasr city',
1970                       '1980-10-30 23:59:59','El rowad','8 Mostafa El Nahhas');
1971
1972INSERT INTO Employees(first_name, middle_name, last_name, gender, birth_date, e_address, email, username, username_password, school_name, school_address)
1973VALUES('Ghada','Emad','Ahmed','F','1990-10-30 23:59:59','18 ghamra street','ghada13@gmail.com','ghada.ahmed','balabizo','El rowad','8 Mostafa El Nahhas');
1974
1975insert into Employees(username, username_password, first_name, middle_name, last_name, gender,  email, e_address, birth_date, school_name, school_address)
1976VALUES ('haithem.hany','blabizo','haithem','Nader','hany','m','hithem.nader@gmail.com','24 abbassia Street',
1977                       '1980-10-30 23:59:59','El rowad','8 Mostafa El Nahhas');
1978
1979insert into Employees(username, username_password, first_name, middle_name, last_name, gender,  email, e_address, birth_date, school_name, school_address)
1980VALUES ('saleh.hossam','blabizo','Saleh','Mohamed','Hossam','f','saleh_hossam@gmail.com','25 helioblies street',
1981                       '1985-10-30 23:59:59','El rowad','8 Mostafa El Nahhas');
1982
1983#Students
1984
1985insert into Students (ssn ,school_name,school_address,user_name,gender,name,birth_date,password)
1986    values (2222,'saintfatima', '66 Ismail Al Kabbani' , 'raouf_mohamed' , 'male' , 'raouf mohamed','2001-12-31 23:59:59' , 'blabizo' );
1987 insert into Students (ssn ,school_name,school_address,user_name,gender,name,birth_date,password)
1988    values (1111,'saintfatima', '66 Ismail Al Kabbani' , 'reem.meky' , 'female' , 'reem meky','2001-12-31 23:59:59' , 'blabizo' );
1989  insert into Students (ssn ,school_name,school_address,user_name,gender,name,birth_date,password)
1990    values (1222,'saintfatima', '66 Ismail Al Kabbani' , 'raghda_mohamed' , 'female' , 'raghda eslam','2002-12-31 23:59:59' , 'blabizo' );
1991insert into Students (ssn ,school_name,school_address,user_name,gender,name,birth_date,password)
1992    values (12822,'saintfatima', '66 Ismail Al Kabbani' , 'rodaina_mohamed' , 'female' , 'rodaina mohamed','2002-5-31 23:59:59' , 'blabizo' );
1993
1994
1995insert into Students (ssn ,school_name,school_address,user_name,gender,name,birth_date,password)
1996    values (3333,'El rowad', '8 Mostafa El Nahhas ' , 'mohamed.ahmed' , 'male' , 'mohamed ahmed','2001-5-31 23:59:59' , 'blabizo' );
1997insert into Students (ssn ,school_name,school_address,user_name,gender,name,birth_date,password)
1998    values (3355,'El rowad', '8 Mostafa El Nahhas ' , 'hani.seif' , 'male' , 'Hani seif eldien','2001-6-25 23:59:59' , 'blabizo' );
1999insert into Students (ssn ,school_name,school_address,user_name,gender,name,birth_date,password)
2000    values (3377,'El rowad', '8 Mostafa El Nahhas ' , 'mohamed.hani' , 'male' , 'mohamed Hani','2002-4-3 23:59:59' , 'blabizo' );
2001insert into Students (ssn ,school_name,school_address,user_name,gender,name,birth_date,password)
2002    values (888888,'El rowad', '8 Mostafa El Nahhas ' , null , 'd' , null,null, null );
2003
2004
2005#Clubs
2006insert into Clubs
2007values ('TCS','Teach You How To be a good Actor','66 Ismail Al Kabbani','saintfatima');
2008insert into Clubs
2009    values ('IEEE','Take You To Science Life ','66 Ismail Al Kabbani','saintfatima');
2010insert into Clubs
2011    values ('Bdaia','Charity','8 Mostafa El Nahhas','El rowad');
2012
2013insert into Clubs
2014    values ('Eco Diving','Teach You Diving','8 Mostafa El Nahhas','El rowad');
2015
2016#Teachers
2017insert into Teachers(seniority_level, first_year_of_work, teacher_id)
2018values(3,'1970-10-30 23:59:59',1);
2019
2020insert into Teachers(seniority_level, first_year_of_work, teacher_id)
2021values(2,'1990-10-30 23:59:59',2);
2022
2023insert into Teachers(seniority_level, first_year_of_work, teacher_id)
2024values(3,'1970-10-30 23:59:59',5);
2025
2026insert into Teachers(seniority_level, first_year_of_work, teacher_id)
2027values(3,'1970-10-30 23:59:59',7);
2028
2029
2030insert into Teachers(seniority_level, first_year_of_work, teacher_id)
2031values(3,'1970-10-30 23:59:59',9);
2032
2033
2034insert into Adminstrators values (3);
2035
2036insert into Adminstrators values (4);
2037
2038insert into Adminstrators values (7);
2039
2040insert into Adminstrators values (8);
2041
2042
2043
2044#Parents
2045insert into Parents(user_name, password, first_name, last_name, home_phone, email, address)
2046    values('mohamed_adel','blabizo','mohamed','adel',022750654,'mohamed_adel@hotmail.com','10 elmarghani street nasr city');
2047insert into Parents(user_name, password, first_name, last_name, home_phone, email, address)
2048    values('meky_mohamed','blabizo','meky','mohamed',0227861654,'meky15@hotmail.com','25 mahmoud tawfik');
2049insert into Parents(user_name, password, first_name, last_name, home_phone, email, address)
2050    values('mohamed_eslam','blabizo','mohamed','eslam',022852654,'mohamed_eslam@hotmail.com','16 abo bakr street abbasia');
2051insert into Parents(user_name, password, first_name, last_name, home_phone, email, address)
2052    VALUES ('mohamed_ibrahim','blabizo','mohamed','ibrahim',0222654789,'mohamed52@yahoo.com','17 mohamed ali street masr algdeda');
2053
2054insert into Parents(user_name, password, first_name, last_name, home_phone, email, address)
2055    values('ahmed_abdelrahman','blabizo','ahmed','abdelrahman',022750654,'ahmed_abdelrahman@hotmail.com','20 elmarghani street nasr city');
2056insert into Parents(user_name, password, first_name, last_name, home_phone, email, address)
2057    values('seif_eldin','blabizo','seif','eldin',0227861654,'seif14@hotmail.com','29 mahmoud tawfik');
2058insert into Parents(user_name, password, first_name, last_name, home_phone, email, address)
2059    values('hani_mohamed','blabizo','hani','mohamed',022852654,'hani5@hotmail.com','17 abo bakr street abbasia');
2060insert into Parents(user_name, password, first_name, last_name, home_phone, email, address)
2061    VALUES ('hani_amir','blabizo','hani','amir',0222654789,'hani_amir@yahoo.com','20 mohamed ali street masr algdeda');
2062#Reports
2063
2064insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
2065    values('He is good but need to practice','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','mohamed_adel',2222,1);
2066insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
2067    values('He is good but need to practice','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','meky_mohamed',1111,1);
2068
2069insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
2070    values('Your son need to work harder','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','mohamed_adel',2222,2);
2071insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
2072    values('Your son need to work harder','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','meky_mohamed',1111,2);
2073
2074insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
2075    values('He is good but needs to practice','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','mohamed_eslam',1222,1);
2076insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
2077    values('He is good but needs to practice','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','mohamed_ibrahim',12822,1);
2078
2079insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
2080    values('your grades is the worst','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','mohamed_eslam',1222,2);
2081insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
2082    values('Your son need to work harder','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','mohamed_ibrahim',12822,2);
2083
2084
2085
2086insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
2087    values('he is fine','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','ahmed_abdelrahman',3333,9);
2088
2089insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
2090    values('Your son need to work harder','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','seif_eldin',3355,9);
2091insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
2092    values('he is good','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','hani_mohamed',3377,9);
2093
2094
2095
2096insert into Courses (name, description, school_address, school_name)
2097values('Math1','Math Techniques','66 Ismail Al Kabbani','saintfatima');
2098insert into Courses (name, description, school_address, school_name)
2099values('Enlish1','learn how to describe yoursef in english','66 Ismail Al Kabbani','saintfatima');
2100insert into Courses (name, description, school_address, school_name)
2101    values('Arabic1','learn how to describe yoursef in arabic','66 Ismail Al Kabbani','saintfatima');
2102
2103insert into Courses  (name, description, school_address, school_name)
2104    values('Math2','Math Techniques','66 Ismail Al Kabbani','saintfatima');
2105insert into Courses  (name, description, school_address, school_name)
2106    values('English2','learn how to describe yoursef in english','66 Ismail Al Kabbani','saintfatima');
2107insert into Courses  (name, description, school_address, school_name)
2108    values('Arabic2','learn how to describe yoursef in arabic','66 Ismail Al Kabbani','saintfatima');
2109
2110
2111
2112insert into Courses(name, description, school_address, school_name)
2113values('Math1','Math Techniques','8 Mostafa El Nahhas','El rowad');
2114
2115insert into Courses(name, description, school_address, school_name)
2116values('Enlish','learn how to describe yoursef in english','66 Ismail Al Kabbani','saintfatima');
2117insert into Courses(name, description, school_address, school_name)
2118    values('Arabic','learn how to describe yoursef in arabic','8 Mostafa El Nahhas','El rowad');
2119
2120insert into Courses  (name, description, school_address, school_name)
2121    values('Math2','Math Techniques','66 Ismail Al Kabbani','saintfatima');
2122insert into Courses  (name, description, school_address, school_name)
2123    values('English2','learn how to describe yoursef in english','8 Mostafa El Nahhas','El rowad');
2124insert into Courses  (name, description, school_address, school_name)
2125    values('Arabic2','learn how to describe yoursef in arabic','8 Mostafa El Nahhas','El rowad');
2126
2127#Levels
2128
2129insert into Levels values('1st level');
2130insert into Levels values('2nd level');
2131insert into Levels values('3rd level');
2132
2133#Levels_offer_Schools
2134SELECT *
2135FROM Levels_offer_Schools;
2136insert into Levels_offer_Schools values ('1st level','saintfatima','66 Ismail Al Kabbani');
2137insert into Levels_offer_Schools values ('2nd level','saintfatima','66 Ismail Al Kabbani');
2138insert into Levels_offer_Schools values ('3rd level','saintfatima','66 Ismail Al Kabbani');
2139
2140insert into Levels_offer_Schools values ('1st level','El rowad','8 Mostafa El Nahhas');
2141call viewSchoolsByLevel();
2142
2143SELECT *
2144FROM Schools;
2145CALL viewSchoolsByLevel();
2146#Courses_offer_Levels
2147
2148insert into Courses_offer_Levels
2149    values (9,'2nd level',1);
2150insert into Courses_offer_Levels
2151    values (9,'2nd level',2);
2152insert into Courses_offer_Levels
2153    values (9,'2nd level',3);
2154insert into Courses_offer_Levels
2155    values (10,'3rd level',4);
2156insert into Courses_offer_Levels
2157    values (10,'3rd level',5);
2158insert into Courses_offer_Levels
2159    values (10,'3rd level',6);
2160
2161
2162
2163
2164#School_Activities;
2165
2166insert into School_Activities(activity_Date, location, teacher_id, adminstrator_id, name, equipment, type, description)
2167    values('2016-11-30 15:00:00','c5201',1,3,'how to draw','sketches and colors','drawing','teach you how to draw from skratch');
2168 insert into School_Activities(activity_Date, location, teacher_id, adminstrator_id, name, equipment, type, description)
2169    values('2016-11-30 15:00:00','c6201',2,4,'geometric paints','sketches and colors','drawing','teach you how to draw from geometric paints');
2170
2171
2172
2173#Assignments
2174insert into Assignments (posting_date, due_date, content)
2175    values('2016-10-20 23:59:59','2016-10-30 23:59:59','Solve The First two page of the workbook');
2176
2177insert into Assignments (posting_date, due_date, content)
2178    values('2016-10-20 23:59:59','2016-10-30 23:59:59','make a research about  egypt');
2179
2180insert into Assignments (posting_date, due_date, content)
2181    values('2016-10-20 23:59:59','2016-10-30 23:59:59','solve the first three page of the workbook');
2182
2183
2184
2185insert into Assignments (posting_date, due_date, content)
2186    values('2016-10-20 23:59:59','2016-10-30 23:59:59','Solve The First two page of the workbook');
2187
2188insert into Assignments (posting_date, due_date, content)
2189    values('2016-10-20 23:59:59','2016-10-30 23:59:59','make a research about  world war 2');
2190
2191insert into Assignments (posting_date, due_date, content)
2192    values('2016-10-20 23:59:59','2016-10-30 23:59:59','solve the first three page of the workbook');
2193
2194#Assignments_posteBy_Teachers
2195insert into Assignments_posteBy_Teachers(teacher_id, course_code,assignment_number)
2196    values (1,1,1);
2197insert into Assignments_posteBy_Teachers(teacher_id, course_code,assignment_number)
2198    values (2,2,2);
2199insert into Assignments_posteBy_Teachers(teacher_id, course_code,assignment_number)
2200    values (5,3,3);
2201insert into Assignments_posteBy_Teachers(teacher_id, course_code,assignment_number)
2202    values (1,4,4);
2203
2204insert into Assignments_posteBy_Teachers(teacher_id, course_code,assignment_number)
2205    values (2,5,5);
2206insert into Assignments_posteBy_Teachers(teacher_id, course_code,assignment_number)
2207    values (5,6,6);
2208
2209
2210
2211#Students_taughtBy_Teachers
2212
2213insert into Students_taughtBy_Teachers
2214    values(1111,4,1);
2215 insert into Students_taughtBy_Teachers
2216    values(2222,4,1);
2217insert into Students_taughtBy_Teachers
2218    values(1111,5,2);
2219insert into Students_taughtBy_Teachers
2220    values(2222,5,2);
2221insert into Students_taughtBy_Teachers
2222    values(1111,6,5);
2223insert into Students_taughtBy_Teachers
2224    values(2222,6,5);
2225
2226insert into Students_taughtBy_Teachers
2227    values(1222,1,1);
2228
2229insert into Students_taughtBy_Teachers
2230    values(1222,2,2);
2231
2232insert into Students_taughtBy_Teachers
2233    values(1222,3,5);
2234
2235
2236insert into Students_taughtBy_Teachers
2237    values(3377,8,9);
2238
2239
2240
2241insert into Students_taughtBy_Teachers
2242    values(3333,11,9);
2243insert into Students_taughtBy_Teachers
2244    values(3355,11,9);
2245
2246#Types
2247
2248INSERT INTO Types VALUES ('international', 'saintfatima', '66 Ismail Al Kabbani');
2249INSERT INTO Types VALUES ('national', 'El rowad', '8 Mostafa El Nahhas');
2250
2251
2252
2253#Parents_apply_Students_Schools
2254
2255INSERT INTO Parents_apply_Students_Schools (accept, parent_user_name, student_ssn, school_name)
2256VALUES (1, 'mohamed_eslam', 1111, 'El rowad');
2257
2258INSERT INTO Parents_apply_Students_Schools (accept, parent_user_name, student_ssn, school_name)
2259VALUES (1, 'meky_mohamed', 2222, 'saintfatima');
2260
2261
2262INSERT INTO Parents_apply_Students_Schools (accept, parent_user_name, student_ssn, school_name)
2263VALUES (1, 'mohamed_adel', 1111, 'saintfatima');
2264
2265
2266
2267# Teachers_supervises_Teachers
2268
2269INSERT INTO  Teachers_supervises_Teachers(teacher_id1, teacher_id2)
2270values(1,2);
2271
2272INSERT INTO  Teachers_supervises_Teachers(teacher_id1, teacher_id2)
2273values(1,5);
2274
2275-- # High_Schools
2276
2277INSERT INTO High_Schools(name, address)
2278 VALUES ('saintfatima','66 Ismail Al Kabbani');
2279
2280INSERT INTO High_Schools(name, address)
2281 VALUES ('El rowad','8 Mostafa El Nahhas');
2282
2283
2284-- # Mid_Schools
2285
2286INSERT INTO Mid_Schools(name, address)
2287 VALUES ('saintfatima','66 Ismail Al Kabbani');
2288
2289INSERT INTO Mid_Schools(name, address)
2290 VALUES ('El rowad','8 Mostafa El Nahhas');
2291
2292-- # Elementary_Schools
2293
2294INSERT INTO Elementary_Schools(name, address)
2295 VALUES ('saintfatima','66 Ismail Al Kabbani');
2296
2297INSERT INTO Elementary_Schools(name, address)
2298 VALUES ('El rowad','8 Mostafa El Nahhas');
2299
2300-- #Required_Supplies
2301
2302INSERT into Required_Supplies(required_supplies, name, address) VALUES
2303('Backpack and Lunchbag','saintfatima','66 Ismail Al Kabbani') ;
2304
2305INSERT into Required_Supplies(required_supplies, name, address) VALUES
2306('Eraser and Pencils','El rowad','8 Mostafa El Nahhas') ;
2307
2308
2309-- # Announcements
2310INSERT INTO Announcements (date, title, description, employee_id)
2311VALUES ('2016-11-22','English H.W','The H.W is Cancelled',4);
2312
2313INSERT INTO Announcements (date, title, description, employee_id)
2314VALUES ('2016-11-22','Math Grade','All GOT THE Full  Mark',3);
2315
2316-- # Assignments_solve_Students
2317
2318INSERT INTO Assignments_solve_Students (solution, student_ssn, assignment_number)
2319VALUES ('2*2=4', 1111, 1);
2320
2321
2322INSERT INTO Assignments_solve_Students (solution, student_ssn, assignment_number)
2323VALUES ('2*3=6', 1222, 1);
2324
2325-- # Activities_Involve_Students
2326
2327INSERT INTO  Activities_Involve_Students  (student_ssn, activity_Date, location)
2328VALUES (1111,'2016-11-30 15:00:00','c5201');
2329SELECT * from School_Activities;
2330
2331
2332INSERT INTO  Activities_Involve_Students  (student_ssn, activity_Date, location)
2333VALUES (2222,'2016-11-30 15:00:00','c6201');
2334
2335-- #Mobile_numbers
2336
2337INSERT INTO Mobile_numbers(mobile, parent_user_name) VALUES
2338  ('0123749886','mohamed_adel');
2339
2340
2341INSERT INTO Mobile_numbers(mobile, parent_user_name) VALUES
2342  ('0123749886','meky_mohamed');
2343
2344-- # Parents_rateAndreview_Teachers
2345
2346INSERT INTO Parents_rateAndreview_Teachers (rate, teacher_id, parent) VALUES
2347  (4, 2, 'meky_mohamed');
2348
2349INSERT INTO Parents_rateAndreview_Teachers (rate, teacher_id, parent) VALUES
2350  (4, 2, 'mohamed_adel');
2351
2352-- # Courses_has_prerequisite_Courses
2353
2354 INSERT INTO Courses_has_prerequisite_Courses (course_code1, course_code2) VALUES (4, 1);
2355
2356 INSERT INTO Courses_has_prerequisite_Courses (course_code1, course_code2) VALUES (5, 2);
2357
2358 INSERT INTO Courses_has_prerequisite_Courses (course_code1, course_code2) VALUES (6, 3);
2359
2360-- # Parents_rateAndreview_Teachers
2361
2362INSERT INTO Parents_rateAndreview_Teachers (rate, teacher_id, parent) VALUES (5, 2, 'ahmed_abdelrahman');
2363
2364
2365INSERT INTO Parents_rateAndreview_Teachers (rate, teacher_id, parent) VALUES (3, 5, 'mohamed_eslam');
2366
2367INSERT INTO Parents_rateAndreview_Teachers (rate, teacher_id, parent) VALUES (4, 1, 'seif_eldin');
2368
2369INSERT INTO Parents_rateAndreview_Teachers (rate, teacher_id, parent) VALUES (2, 1, 'meky_mohamed');
2370
2371
2372
2373-- start of calling procedures
2374
2375CALL searchForSchoolName('saintfatima');
2376CALL searchForSchoolAddress('66 Ismail Al Kabbani');
2377CALL searchForSchoolType('international');
2378call searchForSchoolType('national');
2379
2380CALL viewSchoolsByLevel();
2381
2382CALL viewSchoolAndRating('saintfatima','66 Ismail Al Kabbani');
2383
2384INSERT INTO Employees (school_name,school_address) VALUES ('El rowad','8 Mostafa El Nahhas');
2385SELECT *
2386FROM Teachers;
2387
2388INSERT INTO Teachers(teacher_id) VALUES (25);
2389 CALL View_Unverified_Teachers('medo1100','keytronic');
2390SELECT *
2391FROM Adminstrators;
2392CALL Verify_Teacher('Youmna.Ghandor','blabizo',21,'hadyz1100','key');
2393# INSERT into Adminstrators(adminstrator_id);
2394SELECT * from Employees;
2395SELECT * from Students;
2396CALL View_Unverified_Students('ghada.ahmed','balabizo');
2397SELECT *
2398FROM Students;
2399SELECT *
2400FROM Adminstrators;
2401CALL Verify_Student(1111,'dodo','medo');
2402SELECT *
2403FROM Students;
2404CALL addAdmin('Youmna.Ghdandor','blabizo', 'hand', 'yasser', 'man', 'm', 'handman23@gmail.com', '22 faisal st', '1996-04-04',
2405              'Youmna.Ghandor','blabizo','national');
2406
2407CALL View_Employees('Youmna.Ghandor','blabizo');
2408CALL deleteEmployeesFromSystem(10);
2409
2410CALL updateGeneralInfo('stuf','Youmna.Ghandor','blabizo');
2411
2412CALL updateVision('no','Youmna.Ghandor','blabizo');
2413
2414CALL updateAddress('666 Ismail Al Kabbanii','ghada.ahmed','balabizo');
2415SELECT *
2416FROM Schools;
2417CALL updateEmail('keys','Youmna.Ghandor','blabizo');
2418
2419CALL updateMission('mis','Youmna.Ghandor','blabizo');
2420
2421CALL updateMainLangauge('oran','Youmna.Ghandor','blabizo');
2422SELECT *
2423FROM Schools;
2424
2425CALL updatePhoneNumber('55666','Youmna.Ghandor','blabizo');
2426
2427CALL createAnnouncement('2004-04-04','sport','try',3,'blabizo');
2428
2429CALL createActivity('2004-04-04','GUC',3,'name','dd','ddd','sports');
2430
2431CALL assignTeacherToActivity('2004-04-04','GUC',1,3);
2432# INSERT INTO Students (ssn,school_address,school_name) VALUES (1111,);
2433SELECT *
2434FROM Courses;
2435CALL Assign_Teacher_to_Course(3,1,1);
2436
2437CALL assignTeacherToSuperViseTeacher(1,2,3);
2438CALL acceptOrRejectStudents(3,1111,1);
2439SELECT *
2440FROM Students;
2441SELECT *
2442FROM Questions;
2443INSERT INTO Questions (question, course_code, ssn, content) VALUES ('what ?',1,1111,'no?');
2444INSERT INTO Students_taughtBy_Teachers(ssn, course_code, teacher_id) VALUES (1111,1,9);
2445CALL Teacher_View_Questions(9);
2446
2447CALL Teacher_Answers_Questions (9,2,'yes');
2448SELECT * from Students_taughtBy_Teachers;
2449-- insert in grades
2450CALL View_Students(9);
2451CALL Students_Without_Activity(3);
2452SELECT *
2453FROM Parents_rateAndreview_Teachers;
2454UPDATE Parents_rateAndreview_Teachers
2455SET rating = 5;
2456SELECT * FROM Employees;
2457-- reutrn float rating if appropriate value inserted
2458CALL Rate_Teacher_Avg(@out_value,2);
2459SELECT @out_value;
2460
2461
2462use test3;
2463SELECT *
2464FROM Employees;
2465
2466
2467create table Busses(
2468  bus_number int PRIMARY KEY AUTO_INCREMENT,
2469  route VARCHAR(200),
2470  model VARCHAR(200),
2471  capacity int,
2472  school_name VARCHAR(50),
2473  school_address VARCHAR(50),
2474  FOREIGN KEY (school_name,school_address) REFERENCES Schools(name,address) on DELETE CASCADE on UPDATE  CASCADE
2475);
2476
2477
2478CREATE TABLE Busses_Offeredby_Schools(
2479  ssn int,
2480  bus_number int,
2481  school_name VARCHAR(50),
2482  school_address VARCHAR(50),
2483  cnt int,
2484  PRIMARY KEY (ssn,bus_number,school_name,school_address),
2485  FOREIGN KEY (ssn) REFERENCES Students(ssn) on DELETE CASCADE on UPDATE CASCADE ,
2486  FOREIGN KEY (bus_number) REFERENCES Busses(bus_number) on DELETE CASCADE  ,
2487  FOREIGN KEY (school_name, school_address) REFERENCES Schools(name,address) on DELETE CASCADE on UPDATE CASCADE
2488);
2489
2490DELIMITER //
2491
2492CREATE PROCEDURE addBuss( b_route VARCHAR(50), b_model VARCHAR(50), cap int, s_name VARCHAR(50), s_address VARCHAR(50))
2493  BEGIN
2494    INSERT INTO Busses ( route, model, capacity,school_name , school_address) VALUES (b_route,b_model,cap,s_name,s_address);
2495  END //
2496  DELIMITER ;
2497
2498CALL addBuss('mohandesen', 'b12', 20,'El rowad', '8 Mostafa El Nahhas');
2499
2500SELECT *
2501FROM Schools;
2502SELECT * from Busses;
2503
2504DELIMITER //
2505CREATE PROCEDURE addStudents(stu_ssn int,b_route VARCHAR(200), )
2506  BEGIN
2507   DECLARE mySchoolName VARCHAR(50);
2508  DECLARE mySchoolAddress VARCHAR(50);
2509  DECLARE available_bus_number int DEFAULT  null;
2510    SELECT S.name
2511    INTO mySchoolName
2512    FROM Schools S INNER JOIN Students stu on S.name = stu.school_name and S.address = stu.school_address
2513    WHERE stu.ssn = stu_ssn;
2514
2515 SELECT S.address
2516    INTO mySchoolAddress
2517    FROM Schools S INNER JOIN Students stu on S.name = stu.school_name and S.address = stu.school_address
2518    WHERE stu.ssn = stu_ssn;
2519
2520
2521    SELECT B.bus_number into available_bus_number
2522      from Busses B inner JOIN Schools S on S.name = B.school_name and S.address = B.school_address
2523    WHERE S.name = mySchoolName and S.address = mySchoolAddress and B.capacity >0
2524    LIMIT 1;
2525
2526      IF available_bus_number IS NULL
2527    THEN
2528      SELECT 'no bus available';
2529    ELSE
2530      INSERT INTO Busses (route, model, capacity, school_name, school_address) VALUES ();
2531    END IF;
2532
2533
2534
2535  END //
2536DELIMITER ;
2537
2538DELIMITER //
2539CREATE PROCEDURE avg_cap(OUT res FLOAT, s_name VARCHAR(50), s_address VARCHAR(50))
2540  BEGIN
2541    DECLARE total FLOAT;
2542    DECLARE cnt FLOAT;
2543
2544    SELECT count(B.capacity)
2545    INTO cnt
2546    FROM Schools S inner JOIN Busses B on S.name = b.school_name and S.address = B.school_address
2547    WHERE S.name = B.school_name and S.address = s_address;
2548
2549     SELECT sum(B.capacity)
2550    INTO cnt
2551    FROM Schools S inner JOIN Busses B on S.name = b.school_name and S.address = B.school_address
2552    WHERE S.name = B.school_name and S.address = s_address;
2553
2554    SET res = total / cnt;
2555
2556  END;
2557DELIMITER ;
2558
2559
2560use test3;
2561
2562# select * from Schools S where S.name = 'saintfatima' and S.address $arr1[1];
2563
2564select * from Schools S inner join Employees E on S.name = E.school_name and S.address = E.school_address inner join Teachers T on T.teacher_id = E.employee_ID where S.name = 'El rowad' and S.address = '8 Mostafa El Nahhas';
2565
2566SELECT *
2567FROM Schools;
2568CALL viewSchoolAndRating('El rowad','8 Mostafa El Nahhas');
2569SELECT *
2570FROM Parents_rateAndreview_Schools;
2571
2572
2573INSERT INTO Parents_rateAndreview_Schools (rate, review, parent_username, school_name, school_address)
2574VALUES (5,'good','ahmed_abdelrahman','El rowad','8 Mostafa El Nahhas');
2575SELECT *
2576FROM Parents;
2577
2578SELECT * FROM Students WHERE user_name = 'dodo' and password = 'medo';
2579
2580SELECT * FROM Parents WHERE user_name = 'ahmed_abdelrahman' and password = 'blabizo';
2581
2582SELECT *
2583FROM Parents;
2584
2585# INSERT INTO Parents (user_name, password, first_name, last_name, home_phone, email, address) VALUES (name,pwd,first_name,last_name,tel,email,add);
2586use test3;
2587CREATE PROCEDURE createActivity2(date               DATETIME, newlocation VARCHAR(50),
2588                                newadminstrator_id INT,
2589                                newname            VARCHAR(50),
2590                                newequipment       VARCHAR(50),
2591                                newtype            VARCHAR(50),
2592                                newdescription     VARCHAR(50),
2593                                tid int)
2594  BEGIN
2595    INSERT INTO School_Activities (activity_Date, location, adminstrator_id, name, equipment, type, description,teacher_id)
2596    VALUES (date, newlocation, newadminstrator_id, newname, newequipment, newtype, newdescription,tid);
2597
2598  END ;
2599DELIMITER //
2600
2601CREATE PROCEDURE viewSchoolsByLevel2()
2602  BEGIN
2603    SELECT
2604      S.name,
2605      S.address,
2606      los.level
2607    FROM Schools S inner join Levels_offer_Schools los on los.school_name = S.name and los.school_address = S.address;
2608
2609  END //
2610SELECT *
2611FROM Mid_Schools;
2612SELECT *
2613FROM Schools S inner join High_Schools M on S.name = M.name and S.address = M.address;
2614
2615use test3;
2616SELECT *
2617FROM Employees inner join Adminstrators on employee_ID = Adminstrators.adminstrator_id;
2618SELECT *
2619FROM Employees inner join Teachers on employee_ID = teacher_id;