qpUzJxTw

1CREATE DATABASE dec5d
2GO
3USE dec5d
4GO
5CREATE FUNCTION isElementary(@x VARCHAR(50)) RETURNS BIT
6AS BEGIN
7	DECLARE @r BIT
8	SELECT @r = is_elementary FROM Schools WHERE @x = email
9	RETURN @r
10END
11GO
12
13CREATE FUNCTION hasChildInSchool(@parentEmail VARCHAR(50), @schoolEmail VARCHAR(50)) RETURNS BIT
14AS BEGIN
15	DECLARE @r2 BIT
16	IF( EXISTS (
17		SELECT *
18		FROM Enrolled_Students es
19		WHERE es.school_email = @schoolEmail AND es.parent_email = @parentEmail
20	))
21	SET @r2 = '1'
22	ELSE
23	SET @r2 = '0'
24	RETURN @r2
25END
26GO
27
28CREATE FUNCTION teacherTeachesStudent (@parentEmail VARCHAR(50), @teacherEmail VARCHAR(50)) RETURNS BIT
29AS BEGIN
30	DECLARE @r3 BIT
31	IF( EXISTS(
32		SELECT *
33		FROM Enrolled_Students es, Schools s, Employees e
34		WHERE @parentEmail = es.parent_email AND es.school_email = s.email AND s.email = e.school_email AND e.email = @teacherEmail AND e.is_teacher = '1'
35	))
36	SET @r3 = '1'
37	ELSE
38	SET @r3 = '0'
39	RETURN @r3
40END
41GO
42------------------------------------------------------------
43CREATE TABLE Schools 
44(
45	email VARCHAR(50) PRIMARY KEY,
46	vision VARCHAR(300),
47	main_language VARCHAR (30) ,
48	mission VARCHAR(300),
49	is_national BIT DEFAULT '0',
50	is_international BIT DEFAULT '0',
51	phone_number VARCHAR(20) UNIQUE,
52	home_page VARCHAR(100),
53	fees FLOAT ,
54	school_address VARCHAR(100) ,
55	general_information VARCHAR(300),
56	name VARCHAR (50),
57	is_elementary BIT NOT NULL DEFAULT '0',
58	is_middle BIT NOT NULL DEFAULT '0',
59	is_high BIT NOT NULL DEFAULT '0',
60	CONSTRAINT school_type CHECK (is_national<>0 OR is_international<>0), 
61	CONSTRAINT school_grade	CHECK (is_elementary<>0 OR is_middle<>0 OR is_high<>0)
62);
63CREATE TABLE Elementary_Supplies 
64(
65  supply VARCHAR(50),
66  email VARCHAR(50),
67  PRIMARY KEY(supply,email),
68  FOREIGN KEY (email) REFERENCES Schools,
69  CHECK (dbo.isElementary(email) = '1')
70);
71CREATE TABLE Parents
72(
73	email VARCHAR(50) PRIMARY KEY,
74	parent_address VARCHAR(100),
75	home_phone VARCHAR(20),
76	first_name VARCHAR(25),
77	last_name VARCHAR(25),
78	parent_user_name VARCHAR(25) UNIQUE,
79	parent_password VARCHAR(25)
80);
81CREATE TABLE Parents_Mobiles
82(
83	mobile VARCHAR(20) PRIMARY KEY,
84	email VARCHAR(50),
85	FOREIGN KEY (email) REFERENCES Parents
86);
87CREATE TABLE Parents_Write_Review_Schools
88(
89	parent_email VARCHAR(50),
90	school_email VARCHAR(50),
91	reviews VARCHAR(200),
92	PRIMARY KEY(parent_email, school_email),
93	FOREIGN KEY(parent_email) REFERENCES Parents(email),
94	FOREIGN KEY(school_email) REFERENCES Schools(email),
95	CHECK(dbo.hasChildInSchool(parent_email, school_email) = '1')
96);
97/*
98a0 2
99b0 5
100c1 1
101d1 3
102e1 10
103f1 11
104*/
105CREATE TABLE Applicants
106(
107	ssn INT PRIMARY KEY,
108	name VARCHAR(50),
109	gender BIT,
110	birth_date DATE,
111	age AS (year(current_timestamp) - year(birth_date)),
112	parent_email VARCHAR(50),
113	FOREIGN KEY (parent_email) REFERENCES Parents
114);
115
116CREATE TABLE Applicants_Applies_Schools
117(
118	school_email VARCHAR(50),
119	child_ssn INT,
120	accepted BIT DEFAULT '0',
121	parent_email VARCHAR(50),
122	PRIMARY KEY(child_ssn, school_email),
123	FOREIGN KEY(child_ssn) REFERENCES Applicants,
124	FOREIGN KEY(parent_email) REFERENCES Parents,
125	FOREIGN KEY(school_email) REFERENCES Schools
126);
127CREATE TABLE Enrolled_Students
128(
129	ssn INT PRIMARY KEY,
130	student_level INT,
131	name VARCHAR(50),
132	gender BIT,
133	birth_date DATE,
134	age AS (year(current_timestamp) - year(birth_date)),
135	student_password VARCHAR(20),
136	student_user_name VARCHAR(20) UNIQUE,
137	school_email VARCHAR(50),
138	parent_email VARCHAR(50),
139	FOREIGN KEY (school_email) REFERENCES Schools,
140	FOREIGN KEY (parent_email) REFERENCES Parents
141);
142CREATE TABLE Courses
143(
144	code VARCHAR(10) PRIMARY KEY,
145	name VARCHAR(50),
146	level_of_school INT,
147	course_description VARCHAR(100)
148);
149CREATE TABLE Courses_Prequisites
150(
151	course_code_1 VARCHAR(10),
152	course_code_2 VARCHAR(10),
153	PRIMARY KEY (course_code_1,course_code_2),
154	FOREIGN KEY (course_code_1) REFERENCES Courses,
155	FOREIGN KEY (course_code_2) REFERENCES Courses
156);
157
158
159
160CREATE TABLE Courses_Offered_By_Schools
161(
162	course_code VARCHAR(10),
163	school_email VARCHAR(50),
164	PRIMARY KEY (course_code, school_email),
165	FOREIGN KEY (course_code) REFERENCES Courses,
166	FOREIGN KEY (school_email) REFERENCES Schools
167);
168
169
170
171CREATE TABLE Employees
172(
173	email VARCHAR(50) PRIMARY KEY,
174	birth_date DATE,
175	employee_user_name VARCHAR(25) UNIQUE,
176	employee_password VARCHAR(25),
177	first_name VARCHAR(25),
178	middle_name VARCHAR(25),
179	last_name VARCHAR(25),
180	gender BIT,
181	employee_address VARCHAR(100),
182	salary FLOAT,
183	enrollment_year DATE,
184	years_of_experience as (year(current_timestamp) - year(enrollment_year)),
185	is_administrator BIT NOT NULL DEFAULT '0',
186	is_teacher BIT NOT NULL DEFAULT '0',
187	is_supervisor BIT NOT NULL DEFAULT '0',
188	is_verified BIT NOT NULL DEFAULT '0',
189	school_email VARCHAR(50),
190	supervisor_email VARCHAR(50),
191	FOREIGN KEY (school_email) REFERENCES Schools,
192	FOREIGN KEY (supervisor_email) REFERENCES Employees
193);
194CREATE TABLE Activities
195(
196	activity_date DATE,
197	activity_location VARCHAR(30),
198	activity_type VARCHAR(20),
199	activity_description VARCHAR(300),
200	administrator_email VARCHAR(50) NOT NULL,
201	teacher_email VARCHAR(50),
202	school_email VARCHAR(50) NOT NULL,
203	PRIMARY KEY (activity_date, activity_location),
204	FOREIGN KEY (administrator_email) REFERENCES Employees,
205	FOREIGN KEY (teacher_email) REFERENCES Employees,
206	FOREIGN KEY (school_email) REFERENCES Schools
207);
208CREATE TABLE Equipment
209(
210	activity_date DATE,
211	activity_location VARCHAR(30),
212	equipment VARCHAR(50),
213	PRIMARY KEY (activity_date, activity_location, equipment),
214	FOREIGN KEY (activity_date, activity_location) REFERENCES Activities
215);
216CREATE TABLE Activities_Applied_By_Enrolled_Students
217(
218	activity_date DATE,
219	activity_location VARCHAR(30),
220	ssn INT,
221	PRIMARY KEY (ssn, activity_date, activity_location),
222	FOREIGN KEY (ssn) REFERENCES Enrolled_Students,
223	FOREIGN KEY (activity_date, activity_location) REFERENCES Activities
224);
225CREATE TABLE Clubs
226(
227	name VARCHAR(50) PRIMARY KEY,
228	purpose VARCHAR(50)
229);
230CREATE TABLE Clubs_Joined_By_Enrolled_Student
231(
232	club_name VARCHAR(50),
233	enrolled_ssn INT,
234	PRIMARY KEY (club_name, enrolled_ssn),
235	FOREIGN KEY (club_name) REFERENCES Clubs,
236	FOREIGN KEY (enrolled_ssn) REFERENCES Enrolled_Students
237);
238CREATE TABLE Clubs_Offered_By_Schools --eh offers da :P
239(
240	name VARCHAR(50),
241	school_email VARCHAR(50),
242	PRIMARY KEY(name,school_email),
243	FOREIGN KEY (name) REFERENCES Clubs,
244	FOREIGN KEY (school_email) REFERENCES Schools
245);
246CREATE TABLE Questions
247(
248	question_number INT IDENTITY,
249	course_code VARCHAR(10) NOT NULL,
250	question_itself VARCHAR(500), --3ash X')
251	question_answer VARCHAR(500),
252	askers_ssn INT, 
253	PRIMARY KEY (question_number,course_code),
254	FOREIGN KEY (course_code) REFERENCES Courses,
255	FOREIGN KEY (askers_ssn) REFERENCES Enrolled_Students
256);
257CREATE TABLE Enrolled_Students_View_Questions
258(
259	ssn INT,
260	question_number INT,
261	course_code VARCHAR(10),
262	PRIMARY KEY (ssn, question_number, course_code),
263	FOREIGN KEY (ssn) REFERENCES Enrolled_Students,
264	FOREIGN KEY (question_number,course_code) REFERENCES Questions
265);
266CREATE TABLE Teachers_Response_To_Questions
267(
268	question_number INT,
269	course_code VARCHAR(10),
270	teacher_email VARCHAR(50),
271	PRIMARY KEY (question_number, course_code, teacher_email),
272	FOREIGN KEY (question_number,course_code) REFERENCES Questions,
273	FOREIGN KEY (teacher_email) REFERENCES Employees
274);
275CREATE TABLE Assignments
276(
277	assignment_number INT,
278	course_code VARCHAR(10),
279	teacher_email VARCHAR(50),
280	posting_date DATE,
281	due_date DATE,
282	content VARCHAR(500),
283	PRIMARY KEY (assignment_number, course_code, teacher_email),
284	FOREIGN KEY (course_code) REFERENCES Courses,
285	FOREIGN KEY (teacher_email) REFERENCES Employees
286);
287CREATE TABLE Assignments_Solved_By_Students
288(
289	ssn INT,
290	assignment_number INT,
291	teacher_email VARCHAR(50),
292	course_code VARCHAR(10),
293	grade FLOAT,
294	solution VARCHAR(3000),
295	PRIMARY KEY (ssn, assignment_number, teacher_email, course_code),
296	FOREIGN KEY (ssn) REFERENCES Enrolled_Students,
297	FOREIGN KEY (assignment_number, course_code, teacher_email) REFERENCES Assignments
298);
299
300
301
302CREATE TABLE View_And_Grade_Assignments
303(
304	viewer_email VARCHAR(50), --teacher_email ya3ny
305	assignment_number INT,
306	course_code VARCHAR(10),
307	creator_email VARCHAR(50),
308	PRIMARY KEY(viewer_email, assignment_number, course_code, creator_email),
309	FOREIGN KEY (assignment_number, course_code, creator_email) REFERENCES Assignments,
310	FOREIGN KEY (viewer_email) REFERENCES Employees
311);
312CREATE TABLE Attends
313(
314	enrolled_ssn INT,
315	course_code VARCHAR(10),
316	grade FLOAT,
317	PRIMARY KEY (enrolled_ssn, course_code),
318	FOREIGN KEY (enrolled_ssn) REFERENCES Enrolled_Students,
319	FOREIGN KEY (course_code) REFERENCES Courses
320);
321
322CREATE TABLE Teaches
323(
324	teacher_email VARCHAR(50),
325	course_code VARCHAR(10),
326	PRIMARY KEY (course_code, teacher_email),
327	FOREIGN KEY (teacher_email) REFERENCES Employees,
328	FOREIGN KEY (course_code) REFERENCES Courses
329);
330CREATE TABLE Announcements
331(
332	administrator_email VARCHAR(50),
333	announcement_date DATE,
334	announcement_time TIME,
335	announcement_type VARCHAR(20),
336	title VARCHAR(25),
337	announcement_description VARCHAR(300),
338	school_email VARCHAR(50) NOT NULL,
339	PRIMARY KEY (administrator_email, announcement_date, announcement_time),
340	FOREIGN KEY (administrator_email) REFERENCES Employees,
341	FOREIGN KEY (school_email) REFERENCES Schools
342);
343CREATE TABLE Announcements_Visible_To_Parents
344(
345	administrator_email VARCHAR(50),
346	announcement_date DATE,
347	announcement_time TIME,
348	parent_email VARCHAR(50),
349	PRIMARY KEY (administrator_email,announcement_date,announcement_time,parent_email),
350	FOREIGN KEY (parent_email) REFERENCES Parents,
351	FOREIGN KEY (administrator_email, announcement_date, announcement_time) REFERENCES Announcements
352);
353CREATE TABLE Announcements_Visible_To_Enrolled_Students
354(
355	administrator_email VARCHAR(50),
356	announcement_date DATE,
357	announcement_time TIME,
358	enrolled_ssn int,
359	PRIMARY KEY (administrator_email,announcement_date,announcement_time),
360	FOREIGN KEY (enrolled_ssn) REFERENCES Enrolled_Students,
361	FOREIGN KEY (administrator_email, announcement_date, announcement_time) REFERENCES Announcements
362);
363CREATE TABLE Parents_Ratings_To_Teachers
364(
365	employee_email VARCHAR(50),
366	parent_email VARCHAR(50),
367	rating DECIMAL(2,1), --eh da? X) leh decimal tab? mesh int? madam zero to five
368	CONSTRAINT chk_Rating CHECK (rating >= 0 AND rating <= 5),
369	PRIMARY KEY (employee_email, parent_email),
370	FOREIGN KEY (employee_email) REFERENCES Employees,
371	FOREIGN KEY (parent_email) REFERENCES Parents,
372	CHECK(dbo.teacherTeachesStudent(parent_email, employee_email) = '1')
373);
374CREATE TABLE Monthly_Reports
375(
376	enrolled_ssn INT,
377	issue_date DATE,
378	comment VARCHAR(200),
379	writer VARCHAR(50) NOT NULL,
380	PRIMARY KEY (enrolled_ssn, issue_date),
381	FOREIGN KEY (enrolled_ssn) REFERENCES Enrolled_Students,
382	FOREIGN KEY (writer) REFERENCES Employees
383);
384
385CREATE TABLE Monthly_Reports_Replies
386(
387	enrolled_ssn INT,
388	issue_date DATE,
389	parent_email VARCHAR (50),
390	reply VARCHAR(200),
391	PRIMARY KEY (enrolled_ssn, issue_date, parent_email),
392	FOREIGN KEY (enrolled_ssn, issue_date) REFERENCES Monthly_Reports,
393	FOREIGN KEY (parent_email) REFERENCES Parents
394);
395GO
396-----------------------------------------------------------------------------------------
397--sysadmin
398CREATE PROC CreateSchool
399@school_name VARCHAR(50),
400@address VARCHAR(100),
401@phone_number VARCHAR(20),
402@email VARCHAR(50), 
403@general_information VARCHAR(300),
404@vision VARCHAR(300),
405@mission VARCHAR(300),
406@main_language VARCHAR (30),
407@type BIT,
408@is_elementary BIT,
409@is_middle BIT,
410@is_high BIT,
411@fees FLOAT
412AS
413	DECLARE @isNational bit SET @isNational = 0;
414	DECLARE @isInternational bit SET @isInternational = 0;
415	IF @type = 1
416		SET @isInternational = 1;
417	ELSE
418		SET @isNational = 1
419	IF @school_name is NULL or
420	@address is NULL or
421	@phone_number is NULL or
422	@type is NULL or
423	@fees is NULL
424		print 'One of the inputs is null'
425	else
426		INSERT INTO Schools(email , vision , main_language , mission , is_national , is_international , phone_number , fees , school_address , general_information , name,is_elementary,is_middle,is_high)
427		VALUES(@email, @vision, @main_language, @mission, @isNational, @isInternational, @phone_number, @fees, @address, @general_information, @school_name,@is_elementary,@is_middle,@is_high)
428GO
429
430--di 7aga fadla m'el functions?
431CREATE TYPE Course_List
432AS TABLE
433(
434	course_code VARCHAR(10)
435);
436GO
437
438--azonn me7tageen separate proc t'insert a separate prequisite
439CREATE PROC AddCourse
440@course_code VARCHAR(10), 
441@course_name VARCHAR(50), 
442--@course_level_elementary BIT,
443--@course_level_middle BIT,
444--@course_level_high BIT,
445@school_level INT,
446@description VARCHAR(100),
447@prequisite_courses AS Course_List READONLY
448AS
449	IF @course_code is NULL or
450	@course_name is NULL or
451--	@course_level_elementary is NULL or
452--	@course_level_middle is NULL or 
453--	@course_level_high is NULL or 
454	@school_level is NULL
455		print 'One of the inputs is null'
456	else
457		INSERT INTO Courses(code, level_of_school, course_description, name)
458		Values(@course_code, @school_level, @description, @course_name)
459
460	DECLARE @code VARCHAR(10)
461	DECLARE MY_CURSOR CURSOR 
462	LOCAL STATIC READ_ONLY FORWARD_ONLY
463	FOR 
464	SELECT DISTINCT course_code 
465	FROM @prequisite_courses
466	OPEN MY_CURSOR
467	FETCH NEXT FROM MY_CURSOR INTO @code
468	WHILE @@FETCH_STATUS = 0
469	BEGIN 
470		if EXISTS (Select code from Courses where code = @code)
471			INSERT INTO Courses_Prequisites(course_code_1, course_code_2)
472			VALUES(@course_code, @code)
473		else
474			print 'Prequisite course does not exist'
475		FETCH NEXT FROM MY_CURSOR INTO @code
476	END
477	CLOSE MY_CURSOR
478	DEALLOCATE MY_CURSOR
479GO
480
481CREATE PROC AddAdmin2
482@first_name VARCHAR(25),
483@middle_name VARCHAR(25),
484@last_name VARCHAR(25),
485@birthdate DATE,
486@address VARCHAR(100),	
487@email VARCHAR(50),
488@username VARCHAR(25),
489@password VARCHAR(25),
490@gender BIT
491AS
492	IF @first_name IS NULL OR
493	@middle_name IS NULL OR
494	@last_name IS NULL OR
495	@birthdate IS NULL OR
496	@address IS NULL OR
497	@email IS NULL OR
498	@username IS NULL OR
499	@password IS NULL OR
500	@gender IS NULL
501		print 'One of the inputs is null'
502	ELSE
503		declare @date as DATE set @date = CAST(GETDATE() AS DATE);
504		INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,gender,employee_address,enrollment_year,birth_date,is_administrator)
505		Values(@email, @username, @password, @first_name, @middle_name, @last_name, @gender, @address, @date, @birthdate, 1)
506GO
507
508CREATE PROC AssignAdminToSchool
509@admin_email VARCHAR(50),
510@school_email VARCHAR(50)
511AS
512	declare @school_type BIT
513	IF @admin_email IS NULL
514	OR @school_email IS NULL
515		print 'One of the inputs is null'
516	ELSE
517		IF EXISTS(
518			SELECT *
519			FROM Schools s
520			WHERE s.email = @school_email)
521		AND EXISTS(
522			SELECT *
523			FROM Employees
524			WHERE email = @admin_email)
525		BEGIN
526			declare @salary INT --???
527			SELECT @school_type = s.is_international
528			FROM Schools s
529			WHERE s.email = @school_email
530			IF @school_type = 1 --international
531				UPDATE Employees
532				SET school_email = @school_email, salary = 5000
533				WHERE email = @admin_email;
534			ELSE --national
535				UPDATE Employees
536				SET school_email = @school_email, salary = 3000
537				WHERE email = @admin_email;
538		END
539		ELSE
540			print 'School or Admin Does Not Exist'
541GO
542
543CREATE PROC DeleteSchool
544@school_email VARCHAR(50)
545AS
546	IF
547		@school_email IS NULL
548		print 'Input is null'
549	ELSE
550		DELETE FROM Schools
551		WHERE email = @school_email;
552		UPDATE Employees
553		SET employee_user_name = NULL, employee_password = NULL
554		WHERE school_email = @school_email
555GO
556----------------------------------------------------------
557
558-- Parent Procedures 1
559CREATE PROC InsertParents
560@email VARCHAR(50) ,
561@parent_address VARCHAR(100),
562@home_phone VARCHAR(20),
563@first_name VARCHAR(25),
564@last_name VARCHAR(25),
565@parent_user_name VARCHAR(25),
566@parent_password VARCHAR(25)
567AS
568	IF @email IS NULL OR
569	@parent_address IS NULL OR
570	@home_phone IS NULL OR
571	@first_name IS NULL OR
572	@last_name IS NULL OR
573	@parent_user_name IS NULL OR
574	@parent_password IS NULL 
575		PRINT 'One of the inputs is null'
576	ELSE
577		INSERT INTO Parents(email, parent_address, home_phone, first_name, last_name, parent_user_name, parent_password )
578		VALUES(@email, @parent_address, @home_phone, @first_name, @last_name, @parent_user_name, @parent_password)
579GO
580--2
581CREATE PROC Parents_Apply_Schools
582@ssn INT ,
583@name VARCHAR(50) ,
584@gender BIT,
585@birth_date DATE,
586@parent_email VARCHAR(50),
587@school_email_in VARCHAR(50)
588AS
589	IF
590	@ssn IS NULL OR
591	@name IS NULL OR
592	@gender IS NULL OR
593	@birth_date IS NULL OR
594	@parent_email IS NULL 
595		PRINT 'One of the inputs is null'
596	Else 
597		INSERT INTO Applicants (ssn,name,gender,birth_date,parent_email)
598		VALUES(@ssn,@name,@gender,@birth_date,@parent_email)
599		INSERT INTO Applicants_Applies_Schools
600		(school_email , child_ssn , parent_email)
601		VALUES (@school_email_in , @ssn , @parent_email)
602GO
603
604--3
605CREATE PROC Schools_Accepted_My_Children
606@ssn_in INT
607AS
608	SELECT s.* 
609	FROM Schools s
610	INNER JOIN Applicants_Applies_Schools a on a.school_email = s.email
611	where @ssn_in = a.child_ssn AND a.Accepted = 'True'
612GO
613--4
614CREATE PROC ChooseSchool --noexec
615@parent_email VARCHAR(50),
616@child_ssn INT,
617@school_email VARCHAR(50)
618AS
619	IF  @parent_email is null
620	or @child_ssn is null
621	or @school_email is null
622		print 'Null Input'
623	ELSE
624	Begin
625		--Declare @table Table
626		Declare @name VARCHAR(50)
627		Declare @gender BIT
628		Declare @birth_date DATE
629		SELECT @name = e.name, @gender = e.gender, @birth_date = e.birth_date
630		From Applicants e INNER JOIN Applicants_Applies_Schools a on a.child_ssn = e.ssn
631		Where e.parent_email = @parent_email AND a.accepted = 1
632		Insert Into Enrolled_Students
633		(ssn , name , gender , birth_date , school_email , parent_email)
634		Values(@child_ssn, @name, @gender, @birth_date, @school_email, @parent_email)
635		DELETE FROM Applicants
636		WHERE ssn = @child_ssn
637	END
638GO			
639--5
640CREATE PROC View_Reports
641@parent_email VARCHAR(50)
642AS
643	SELECT r.*
644	FROM Monthly_Reports r
645	INNER JOIN Enrolled_Students e ON r.enrolled_ssn = e.ssn
646	WHERE e.parent_email = @parent_email
647GO
648--6
649CREATE PROC Reply
650@parent_email VARCHAR(50),
651@reply VARCHAR(200),
652@issue_date DATE,
653@enrolled_ssn INT
654AS
655	INSERT INTO Monthly_Reports_Replies (enrolled_ssn ,issue_date,parent_email,reply)
656	VALUES(@enrolled_ssn,@issue_date,@parent_email,@reply)
657GO
658--7
659CREATE PROC List_of_Children_Schools
660@parent_email VARCHAR(50)
661AS
662SELECT e.name + ' in ' + s.name AS 'List of my children schools'
663FROM Enrolled_Students e
664		INNER JOIN Schools s on e.school_email = s.email
665WHERE e.parent_email = @parent_email 
666order by s.name
667GO
668--8
669CREATE PROC Posted_Announcments
670@parent_email VARCHAR(50),
671@Enrolled_ssn INT
672AS
673DECLARE @p10days TIMESTAMP = DAY(CURRENT_TIMESTAMP)
674SELECT a.announcement_description
675FROM Announcements_Visible_To_Parents ap
676		INNER JOIN Parents p ON p.email = ap.parent_email
677		INNER JOIN Announcements a ON a.administrator_email = ap.administrator_email
678		INNER JOIN Announcements_Visible_To_Enrolled_Students ae ON ae.administrator_email = ap.administrator_email
679WHERE ap.parent_email = @parent_email AND ae.enrolled_ssn = @Enrolled_ssn AND (DAY(ap.announcement_date) + 10) > @p10days
680GO
681--9
682CREATE PROC Rate_Teachers
683@parent_email VARCHAR(50),
684@teacher_email VARCHAR(50),
685@rating DECIMAL(2,1)
686AS
687IF	@parent_email IS NULL OR
688	@teacher_email IS NULL OR
689	@rating IS NULL
690	print 'NULL INPUT'
691ELSE
692IF @parent_email IN
693	(
694		Select parent_email
695		From Enrolled_Students e
696			INNER JOIN Attends a on a.enrolled_ssn = e.ssn
697			INNER JOIN Teaches T on T.course_code = a.course_code
698		Where e.parent_email = @parent_email 	
699	)
700	INSERT INTO Parents_Ratings_To_Teachers(employee_email, parent_email, rating)
701	VALUES(@teacher_email, @parent_email, @rating)
702GO
703--10
704CREATE PROC Write_Reviews
705@parent_email VARCHAR(50),
706@school_email VARCHAR(50),
707@reviews VARCHAR(200)
708AS
709	IF @parent_email IS NULL OR @school_email IS NULL OR @reviews IS NULL
710		PRINT 'Incomplete input!'
711	ELSE IF NOT EXISTS(
712	SELECT e.parent_email
713	FROM Enrolled_Students e
714	Where e.parent_email = @parent_email AND e.school_email = @school_email)
715		PRINT 'You must have a student enrolled in this school'
716	ELSE
717		INSERT INTO Parents_Write_Review_Schools
718		(parent_email , reviews , school_email)
719		VALUES (@parent_email , @school_email , @reviews)
720GO
721--11
722CREATE PROC Delete_review
723@parent_email VARCHAR(50),
724@school_name VARCHAR(50)
725AS
726	DELETE reviews FROM Parents_Write_Review_Schools
727	WHERE parent_email = @parent_email
728	AND @school_name IN(
729		SELECT s.name
730		FROM Schools s INNER JOIN Parents_Write_Review_Schools ps ON ps.parent_email = s.email
731		Where ps.parent_email = @parent_email)
732GO
733
734--12
735CREATE PROC Sum_rating
736@teacher_fname VARCHAR(25),
737@teacher_lname VARCHAR(25),
738@teacher_rate FLOAT OUTPUT
739As
740DECLARE @total INT
741DECLARE @count INT
742SELECT @Total = SUM(pt.rating), @count = count(pt.rating)
743FROM Parents_Ratings_To_Teachers pt
744		INNER JOIN Employees e ON e.email = pt.employee_email
745		WHERE e.first_name = @teacher_fname AND e.last_name = @teacher_lname
746SET @teacher_rate = @total/@count
747PRINT @teacher_rate
748GO
749--13
750CREATE PROC TOP_10_Schools
751@parent_email VARCHAR(50)
752AS
753SELECT s.name, COUNT(ps.reviews), COUNT(e.ssn)
754FROM Schools s
755		INNER JOIN Parents_Write_Review_Schools ps ON ps.school_email = s.email
756		INNER JOIN Enrolled_Students e ON e.school_email = s.email
757		WHERE ps.parent_email <> @parent_email
758GROUP BY s.name 
759GO
760--14
761CREATE PROC Highest_International_School
762@schoolname VARCHAR(50) OUTPUT,
763@count INT OUTPUT
764AS
765SELECT @schoolname = s.name, @count = count(ps.reviews)
766FROM Schools s
767		INNER JOIN Parents_Write_Review_Schools ps ON ps.school_email = s.email
768GROUP BY s.name
769Having count(ps.reviews) = MAX(ps.reviews)
770GO
771---------------------------------------------------------------------------------
772-- System User
773--1
774CREATE PROC List_School
775@name VARCHAR(50),
776@address VARCHAR(100),
777@national BIT,
778@international BIT 
779AS
780SELECT *
781FROM Schools
782WHERE name = @name OR school_address = @address OR is_national = @national OR is_international = @international
783GO
784--2
785CREATE PROC Schools_By_Levels
786@elementary BIT, 
787@middle BIT, 
788@high BIT
789AS
790SELECT name,is_elementary,is_middle,is_high
791FROM Schools 
792WHERE is_elementary = @elementary or is_middle = @middle or is_high = @high
793ORDER BY is_elementary, is_middle, is_high
794GO
795--3 
796CREATE PROC School_Info
797@name VARCHAR(50)
798AS
799SELECT *
800FROM Schools s
801	INNER JOIN Parents_Write_Review_Schools ps on ps.school_email = s.email
802	INNER JOIN Employees e ON e.school_email = s.email
803	WHERE s.name = @name
804GO
805
806----------------------------------------------------------------------------------------
807--adminstrator
808
809CREATE PROC ViewTeachers
810@admin_email VARCHAR(50)
811AS
812	IF
813		@admin_email IS NULL
814		print 'Null Input'
815	ELSE
816		DECLARE @isAdmin BIT
817		DECLARE @school_email VARCHAR(50)
818		Select @isAdmin = is_administrator, @school_email = e.school_email
819		FROM Employees e
820		WHERE e.email = @admin_email
821		IF @isAdmin = 1
822			Select *
823			FROM Employees e
824			Where e.is_teacher = 1 AND e.school_email = @school_email
825		ELSE
826			PRINT 'Not Admin'
827GO
828CREATE PROC AssignTeacher
829	@admin_email VARCHAR(50),
830	@teacher_email VARCHAR(50),
831	@user_name VARCHAR(25),
832	@password VARCHAR(25)
833AS
834	IF
835		@admin_email IS NULL OR
836		@teacher_email IS NULL OR
837		@user_name IS NULL OR
838		@password IS NULL
839		print 'Null Input'
840	ELSE
841		DECLARE @isAdmin bit
842		DECLARE @school_email VARCHAR(50)
843		Select @isAdmin = is_administrator, @school_email = e.school_email
844		FROM Employees e
845		WHERE e.email = @admin_email
846		IF @isAdmin = 1
847			UPDATE Employees
848			SET employee_user_name = @user_name, employee_password = @password, salary = years_of_experience * 500
849			WHERE email = @teacher_email
850GO
851CREATE PROC ViewStudents
852@admin_email VARCHAR(50)
853AS
854Begin
855	IF
856		@admin_email IS NULL
857		print 'Null Input'
858	ELSE
859		DECLARE @isAdmin bit
860		DECLARE @school_email VARCHAR(50)
861		Select @isAdmin = is_administrator, @school_email = e.school_email
862		FROM Employees e
863		WHERE e.email = @admin_email
864		IF @isAdmin = 1
865			Select *
866			FROM Enrolled_Students e
867			WHERE e.school_email = @school_email
868End
869GO
870CREATE PROC AssignStudent
871	@admin_email VARCHAR(50),
872	@student_ssn VARCHAR(50),
873	@user_name VARCHAR(25),
874	@password VARCHAR(25)
875AS
876	IF
877		@admin_email IS NULL OR
878		@student_ssn IS NULL OR
879		@user_name IS NULL OR
880		@password IS NULL
881		print 'Null Input'
882	ELSE
883		DECLARE @isAdmin bit
884		DECLARE @school_email VARCHAR(50)
885		Select @isAdmin = is_administrator, @school_email = e.school_email
886		FROM Employees e
887		WHERE e.email = @admin_email
888		IF @isAdmin = 1
889			UPDATE Enrolled_Students
890			SET student_user_name = @user_name, student_password = @password
891			WHERE ssn = @student_ssn
892GO
893CREATE PROC AddAdmin
894	@s_admin_email VARCHAR(50),
895	@first_name VARCHAR(25),
896	@middle_name VARCHAR(25), 
897	@last_name VARCHAR(25),
898	@birthdate DATE, 
899	@address VARCHAR(100),
900	@email VARCHAR(50),
901	@username VARCHAR(25), 
902	@password VARCHAR(25),
903	@gender BIT
904AS
905	IF
906		@s_admin_email IS NULL OR
907		@first_name IS NULL OR
908		@middle_name IS NULL OR 
909		@last_name IS NULL OR
910		@birthdate IS NULL OR 
911		@address IS NULL OR
912		@email IS NULL OR
913		@username IS NULL OR 
914		@password IS NULL OR
915		@gender IS NULL
916		print 'Null Input'
917	ELSE
918		DECLARE @isAdmin bit
919		DECLARE @school_email VARCHAR(50)
920		Select @isAdmin = is_administrator, @school_email = e.school_email
921		FROM Employees e
922		WHERE e.email = @s_admin_email
923		IF @isAdmin = 1
924		DECLARE @sal FLOAT
925		DECLARE @is_international BIT
926		SELECT @is_international = is_international
927		FROM Schools s
928		WHERE s.email = @school_email
929		IF(@is_international = 1)
930			SET @sal = 5000
931		ELSE
932			SET @sal = 3000
933			INSERT INTO Employees(email,
934									employee_user_name,
935									employee_password,
936									first_name,
937									middle_name,
938									last_name,
939									gender,
940									employee_address,
941									enrollment_year,
942									school_email, 
943									is_administrator,
944									is_teacher,
945									is_supervisor,
946									salary)
947			VALUES(@email, @username, @password, @first_name, @middle_name, @last_name, @gender, @address, CAST(GETDATE() AS DATE), @school_email, 1, 0, 0, @sal)
948GO
949CREATE PROC DeleteEmployee
950	@admin_email VARCHAR(50),
951	@emp_email VARCHAR(50)
952AS
953	IF
954		@admin_email IS NULL OR
955		@emp_email IS NULL
956		print 'Null Input'
957	ELSE
958		DECLARE @isAdmin bit
959		DECLARE @school_email VARCHAR(50)
960		Select @isAdmin = is_administrator, @school_email = e.school_email
961		FROM Employees e
962		WHERE e.email = @admin_email
963			IF @isAdmin = 1
964				DELETE FROM Employees
965				WHERE school_email = @school_email AND email = @emp_email
966GO
967CREATE PROC DeleteStudent
968	@admin_email VARCHAR(50),
969	@student_ssn VARCHAR(50)
970AS
971	IF
972		@admin_email IS NULL OR
973		@student_ssn IS NULL
974		print 'Null Input'
975	ELSE
976		DECLARE @isAdmin bit
977		DECLARE @school_email VARCHAR(50)
978		Select @isAdmin = is_administrator, @school_email = e.school_email
979		FROM Employees e
980		WHERE e.email = @admin_email
981			IF @isAdmin = 1
982				DELETE FROM Enrolled_Students
983				WHERE school_email = @school_email AND ssn = @student_ssn
984GO
985CREATE PROC EditSchool
986	@admin_email VARCHAR(50),
987	@email VARCHAR(50),
988	@vision VARCHAR(300),
989	@main_language VARCHAR (30),
990	@mission VARCHAR(300),
991	@is_national BIT,
992	@is_international BIT,
993	@phone_number VARCHAR(20),
994	@home_page VARCHAR(100),
995	@fees FLOAT,
996	@school_address VARCHAR(100),
997	@general_information VARCHAR(300),
998	@name VARCHAR(50),
999	@is_elementary BIT,
1000	@is_middle BIT,
1001	@is_high BIT
1002AS
1003	IF
1004		@admin_email IS NULL
1005		print 'Null Input'
1006	ELSE
1007		DECLARE @isAdmin bit
1008		DECLARE @school_email VARCHAR(50)
1009		Select @isAdmin = is_administrator, @school_email = e.school_email
1010		FROM Employees e
1011		WHERE e.email = @admin_email
1012			IF @isAdmin = 1
1013				UPDATE Schools
1014				SET 
1015					email = @email,
1016					vision = @vision,
1017					main_language = @main_language,
1018					mission = @mission,
1019					is_national = @is_national,
1020					is_international = @is_international,
1021					phone_number = @phone_number,
1022					home_page = @home_page,
1023					fees = @fees,
1024					school_address = @school_address,
1025					general_information = @general_information,
1026					name = @name,
1027					is_elementary = @is_elementary,
1028					is_middle = @is_middle,
1029					is_high = @is_high
1030				WHERE email = @school_email
1031GO
1032CREATE PROC PostAnnoucement
1033	@admin_email VARCHAR(50),
1034	@title VARCHAR(25), 
1035	@description VARCHAR(300),
1036	@type VARCHAR(20)
1037AS
1038	IF
1039		@admin_email IS NULL OR
1040		@title IS NULL OR 
1041		@description IS NULL OR
1042		@type IS NULL
1043		print 'Null Input'
1044	ELSE
1045		DECLARE @isAdmin bit
1046		DECLARE @school_email VARCHAR(50)
1047		Select @isAdmin = is_administrator, @school_email = e.school_email
1048		FROM Employees e
1049		WHERE e.email = @admin_email
1050			IF @isAdmin = 1
1051				INSERT INTO Announcements(administrator_email,
1052											announcement_date,
1053											announcement_time,
1054											announcement_type,
1055											title,
1056											announcement_description,
1057											school_email)
1058				VALUES(@admin_email,  CAST(GETDATE() AS DATE),  CAST(GETDATE() AS TIME), @type, @title, @description, @school_email)
1059GO
1060CREATE PROC CreateActivity
1061	@admin_email VARCHAR(50),
1062	@activity_date DATE,
1063	@activity_location VARCHAR(30),
1064	@teacher_email VARCHAR(50),
1065	@activity_type VARCHAR(20),
1066	@activity_description VARCHAR(300),
1067	@equipment VARCHAR(50)
1068AS
1069	IF
1070		@admin_email IS NULL OR
1071		@activity_date IS NULL OR
1072		@activity_location IS NULL OR
1073		@teacher_email IS NULL OR
1074		@activity_type IS NULL OR
1075		@activity_description IS NULL
1076		print 'Null Input'
1077	ELSE
1078		DECLARE @isAdmin bit
1079		DECLARE @school_email VARCHAR(50)
1080		Select @isAdmin = is_administrator, @school_email = e.school_email
1081		FROM Employees e
1082		WHERE e.email = @admin_email
1083			IF @isAdmin = 1
1084				INSERT INTO Activities(activity_date,
1085										activity_location,
1086										administrator_email,
1087										teacher_email,
1088										activity_type,
1089										activity_description,
1090										school_email)
1091				VALUES(@activity_date, @activity_location, @admin_email, @teacher_email, @activity_type, @activity_description, @school_email)
1092				IF @equipment IS NOT NULL
1093					INSERT INTO Equipment(activity_date, activity_location, equipment)
1094					VALUES(@activity_date, @activity_location, @equipment)
1095GO
1096CREATE PROC AssignTeacherToActivity
1097	@admin_email VARCHAR(50),
1098	@activity_date DATE,
1099	@activity_location VARCHAR(30),
1100	@teacher_email VARCHAR(50)
1101AS
1102	IF
1103		@admin_email IS NULL OR
1104		@activity_date IS NULL OR
1105		@activity_location IS NULL OR
1106		@teacher_email IS NULL
1107		print 'Null Input'
1108	ELSE
1109		DECLARE @isAdmin bit
1110		DECLARE @school_email VARCHAR(50)
1111		Select @isAdmin = is_administrator, @school_email = e.school_email
1112		FROM Employees e
1113		WHERE e.email = @admin_email
1114			IF @isAdmin = 1
1115				UPDATE Activities
1116				SET teacher_email = @teacher_email
1117				WHERE school_email = @school_email AND activity_date = @activity_date AND activity_location = @activity_location
1118GO
1119CREATE PROC AssignTeacherToCourse
1120	@admin_email VARCHAR(50),
1121	@teacher_email VARCHAR(50),
1122	@course_code VARCHAR(10)
1123AS
1124	IF
1125		@admin_email IS NULL OR
1126		@teacher_email IS NULL OR
1127		@course_code IS NULL
1128		print 'Null Input'
1129	ELSE
1130		DECLARE @isAdmin bit
1131		DECLARE @school_email VARCHAR(50)
1132		Select @isAdmin = is_administrator, @school_email = e.school_email
1133		FROM Employees e
1134		WHERE e.email = @admin_email
1135		IF @teacher_email IN (SELECT e2.email
1136							  FROM Employees e1 INNER JOIN Employees e2 on e1.school_email = e2.school_email
1137							  Where e1.email = @admin_email)
1138				DECLARE @level int
1139				DECLARE @high BIT
1140				DECLARE @mid BIT
1141				DECLARE @elem BIT
1142				SELECT @level = level_of_school  
1143				FROM Courses
1144				WHERE code = @course_code
1145				SELECT @high = is_high, @mid = is_middle, @elem = is_elementary
1146				FROM Schools
1147				WHERE email = @school_email
1148				IF (@level > 9 AND @high = 1) OR (@level > 6 AND @mid = 1) OR (@level < 7 AND @elem = 1)
1149					INSERT INTO Teaches(teacher_email, course_code)
1150					VALUES(@teacher_email, @course_code)
1151GO
1152CREATE PROC AssignSupervisor
1153	@admin_email VARCHAR(50),
1154	@teacher_email VARCHAR(50),
1155	@supervisor_email VARCHAR(50)
1156AS
1157	IF
1158		@admin_email IS NULL OR
1159		@supervisor_email IS NULL OR
1160		@teacher_email IS NULL
1161		print 'Null Input'
1162	ELSE
1163		DECLARE @isAdmin bit
1164		DECLARE @school_email VARCHAR(50)
1165		Select @isAdmin = is_administrator, @school_email = e.school_email
1166		FROM Employees e
1167		WHERE e.email = @admin_email
1168		IF @isAdmin = 1
1169			UPDATE Employees
1170			SET supervisor_email = @supervisor_email
1171			WHERE email = @teacher_email AND school_email = @school_email
1172GO
1173CREATE PROC HandleSchoolApplication
1174	@admin_email VARCHAR(50),
1175	@child_ssn INT,
1176	@answer BIT
1177AS
1178	IF
1179		@admin_email IS NULL OR
1180		@child_ssn IS NULL
1181		print 'Null Input'
1182	ELSE
1183		DECLARE @isAdmin bit
1184		DECLARE @school_email VARCHAR(50)
1185		Select @isAdmin = is_administrator, @school_email = e.school_email
1186		FROM Employees e
1187		WHERE e.email = @admin_email
1188		IF @isAdmin = 1
1189			IF @answer = 1
1190			BEGIN
1191				declare @parent_email VARCHAR(50)
1192				declare @ssn INT
1193				declare @name VARCHAR(50) 
1194				declare @gender BIT
1195				declare @birth_date DATE
1196				SELECT @parent_email = a.parent_email, @ssn = a.ssn, @name = a.name, @gender = a.gender, @birth_date = a.birth_date
1197				FROM Applicants_Applies_Schools s INNER JOIN Applicants a ON  a.ssn = s.child_ssn
1198				WHERE school_email = @school_email AND child_ssn = @child_ssn
1199				INSERT INTO Enrolled_Students
1200				(ssn , name , gender , birth_date , school_email , parent_email)
1201				VALUES(@ssn, @name, @gender, @birth_date, @school_email, @parent_email)
1202			END
1203				DELETE FROM Applicants_Applies_Schools
1204				WHERE school_email = @school_email AND child_ssn = @ssn
1205				DELETE FROM Applicants
1206				WHERE ssn = @ssn 
1207GO
1208---------------------------------------------------------------------------------------------------------------------------
1209
1210
1211--Student procedures
1212--1
1213CREATE PROC Enrolled_Student_Info_Update
1214@ssn_in INT,
1215@name_in VARCHAR(50),
1216@gender_in BIT,
1217@birth_date_in DATE,
1218@password_in VARCHAR(20),
1219@school_email_in VARCHAR(50),
1220@parent_email_in VARCHAR(50)
1221AS
1222	IF @ssn_in is NULL
1223		PRINT 'Please enter SSN.'
1224	ELSE
1225		UPDATE Enrolled_Students
1226		SET
1227			name = ISNULL(@name_in , name),
1228			gender = ISNULL(@gender_in , gender),
1229			birth_date = ISNULL(@birth_date_in , birth_date),
1230			student_password = ISNULL(@password_in , student_password),
1231			school_email = ISNULL(@school_email_in , school_email),
1232			parent_email = ISNULL(@parent_email_in , parent_email)
1233		WHERE
1234			@ssn_in = Enrolled_Students.ssn
1235GO
1236
1237--2
1238CREATE PROC Enrolled_Student_View_Courses
1239@ssn_in INT
1240AS
1241	IF @ssn_in = NULL
1242		PRINT 'Please enter SSN.'
1243	ELSE
1244		SELECT t.grade, c.name , c.code
1245		FROM Courses c INNER JOIN Attends t ON t.course_code = c.code
1246		WHERE t.enrolled_ssn = @ssn_in
1247		GROUP BY t.grade , c.name , c.code
1248		ORDER BY c.name 
1249GO
1250
1251
1252
1253--3
1254CREATE PROC Enrolled_Student_Post_Question
1255@course_code_in VARCHAR(10),
1256@question_itself_in VARCHAR(500),
1257@askers_ssn_in INT
1258AS
1259	IF @askers_ssn_in = NULL
1260		PRINT 'Please enter SSN.'
1261	ELSE
1262		INSERT INTO Questions (course_code,question_itself,askers_ssn)
1263		VALUES (@course_code_in,@question_itself_in,@askers_ssn_in)
1264GO
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275--4
1276CREATE PROC Enrolled_Student_View_Questions
1277@course_code_in VARCHAR(10),
1278@askers_ssn_in INT
1279AS
1280	IF @askers_ssn_in = NULL OR @course_code_in = NULL
1281		PRINT 'Input Incomplete.'
1282	ELSE IF NOT EXISTS
1283	(SELECT *
1284	FROM Enrolled_Students_View_Questions p --can use "Teaches" instead of this table. Redundant
1285	WHERE @course_code_in = p.course_code
1286	AND @askers_ssn_in = p.ssn)
1287		PRINT 'You do not have the permission to view questions on this course.'
1288	ELSE
1289		SELECT q.question_itself, q.question_answer
1290		FROM Questions q
1291		WHERE @course_code_in = q.course_code
1292GO
1293
1294
1295
1296--EXEC Enrolled_Student_View_Questions "MATH501" , 2113524
1297
1298
1299--5
1300CREATE PROC Enrolled_Student_View_All_Assignments
1301@ssn_in INT
1302AS
1303	IF @ssn_in = NULL
1304		PRINT 'Please enter ssn.'
1305	ELSE
1306		SELECT asst.assignment_number , asst.course_code , asst.posting_date , asst.due_date , asst.content
1307		FROM Attends at
1308		INNER JOIN Assignments asst ON asst.course_code = at.course_code
1309		WHERE @ssn_in = at.enrolled_ssn
1310/*		SELECT t.assignment_number, t.course_code, t.posting_date, t.due_date, t.content
1311		FROM Teaches t 
1312		--INNER JOIN Courses c ON t.course_code = c.code
1313		INNER JOIN Assignments a ON a.course_code = t.course_code
1314		WHERE t.enrolled_ssn = @ssn_in*/
1315GO
1316
1317
1318
1319
1320
1321
1322--6
1323CREATE PROC Enrolled_Student_Solves_Assignment
1324@ssn_in INT,
1325@assignment_number_in INT,
1326@teacher_email_in VARCHAR(50),
1327@course_code_in VARCHAR(10),
1328@solution_in VARCHAR(3000)
1329AS
1330	IF @ssn_in = NULL
1331	OR @assignment_number_in = NULL
1332	OR @course_code_in = NULL
1333	OR @teacher_email_in = NULL
1334	OR @solution_in = NULL
1335		PRINT 'Incomplete Input.'
1336	ELSE IF NOT EXISTS(
1337	SELECT * FROM Attends t
1338	INNER JOIN Teaches tt ON t.course_code = tt.course_code
1339	WHERE t.enrolled_ssn = @ssn_in
1340	AND t.course_code = @course_code_in
1341	AND tt.teacher_email = @teacher_email_in)
1342		PRINT 'This course is not assigned to you.'
1343	ELSE
1344		INSERT INTO Assignments_Solved_By_Students
1345		(ssn,assignment_number,teacher_email,course_code,solution)
1346		VALUES (@ssn_in,@assignment_number_in,@teacher_email_in,@course_code_in,@solution_in)
1347GO
1348
1349
1350
1351
1352
1353
1354--7
1355
1356CREATE PROC Enrolled_Students_Views_Assignments_Grades
1357@ssn_in INT
1358AS
1359	IF @ssn_in = NULL
1360		PRINT 'Please enter SSN.'
1361	ELSE
1362		SELECT s.course_code, s.assignment_number, s.grade
1363		FROM Assignments_Solved_By_Students s
1364		WHERE s.ssn = @ssn_in
1365GO
1366
1367
1368
1369
1370
1371
1372
1373--8
1374--student's school
1375CREATE PROC Enrolled_Student_Views_Announcements
1376@ssn_in INT
1377AS
1378	IF @ssn_in = NULL
1379		PRINT 'Please enter SSN.'
1380	ELSE
1381		SELECT a.title, a.announcement_description
1382		FROM Announcements_Visible_To_Enrolled_Students av
1383		INNER JOIN Announcements a ON
1384		a.administrator_email = av.administrator_email
1385		AND a.announcement_date = av.announcement_date
1386		AND a.announcement_time = av.announcement_time
1387		WHERE @ssn_in = av.enrolled_ssn
1388		--AND DAY(a.announcement_date) > (DAY(CURRENT_TIMESTAMP) - 10)
1389		AND DATEDIFF(DAY,a.announcement_date,CONVERT(DATE, GETDATE())) < 10
1390		AND EXISTS
1391		(SELECT *
1392		FROM Enrolled_Students es
1393		WHERE es.ssn = @ssn_in
1394		AND a.school_email = es.school_email)
1395GO
1396
1397
1398--9
1399CREATE PROC Enrolled_Student_Views_Activities
1400@ssn_in INT
1401AS
1402	IF @ssn_in = NULL
1403		PRINT 'Please enter SSN.'
1404	ELSE
1405		SELECT a.activity_date, a.activity_location, a.activity_type, a.activity_description, e.first_name, e.middle_name, e.last_name, e.gender, e.email
1406		FROM Activities a INNER JOIN Employees e ON a.teacher_email = e.email
1407		WHERE e.is_teacher = 1
1408		AND EXISTS (
1409		SELECT *
1410		FROM Enrolled_Students es
1411		WHERE es.ssn = @ssn_in
1412		AND a.school_email = es.school_email)
1413GO
1414
1415
1416
1417
1418--10
1419--PLEASE CORRECT THIS PROCEDURE
1420--"date is not recognised as a built-in function name"
1421CREATE PROC Enrolled_Student_Applies_To_Activity
1422@ssn_in INT,
1423@activity_date_in DATE,
1424@activity_location_in VARCHAR(30)
1425AS
1426	IF @ssn_in = NULL
1427		PRINT 'Please enter SSN.'
1428	ELSE IF EXISTS(
1429	SELECT * FROM Activities_Applied_By_Enrolled_Students ae
1430	INNER JOIN Activities a ON ae.activity_date = ae.activity_date AND ae.activity_location = a.activity_location
1431	WHERE @ssn_in = ae.ssn AND EXISTS(
1432		SELECT * FROM Activities sa
1433		WHERE sa.activity_date = @activity_date_in
1434		AND sa.activity_location = @activity_location_in
1435		AND sa.activity_type = a.activity_type
1436		AND sa.activity_date = a.activity_date
1437	))
1438		PRINT 'Can not join two activities on the same date of the same type!'
1439	ELSE
1440		INSERT INTO Activities_Applied_By_Enrolled_Students
1441		(ssn, activity_date, activity_location)
1442		VALUES (@ssn_in,@activity_date_in,@activity_location_in)
1443GO
1444
1445--11
1446CREATE PROC Enrolled_Student_Join_Club
1447@ssn_in INT,
1448@club_name_in VARCHAR(50)
1449AS
1450	IF @ssn_in = NULL OR @club_name_in = NULL
1451		RAISERROR ('Incomplete input!' , 15 , 15)
1452	ELSE IF NOT EXISTS(
1453	SELECT * FROM Enrolled_Students es
1454	WHERE @ssn_in =	es.ssn AND es.student_level > 9)
1455		RAISERROR ('You are not a high school student' , 15 , 15)
1456	ELSE IF NOT EXISTS(
1457	SELECT * FROM Enrolled_Students es
1458	INNER JOIN Clubs_Offered_By_Schools c ON c.school_email = es.school_email
1459	WHERE es.ssn = @ssn_in AND c.name = @club_name_in)
1460		RAISERROR ('Your school does not offer this club.' , 15 , 15)
1461	ELSE
1462		INSERT INTO Clubs_Joined_By_Enrolled_Student
1463		(club_name,enrolled_ssn)
1464		VALUES (@club_name_in,@ssn_in)
1465GO
1466
1467--yaaFaragEllaaaah
1468CREATE PROC Enrolled_Student_Course_Search
1469@ssn_in INT,
1470@course_code_in VARCHAR(10),
1471@course_name_in VARCHAR(50)
1472AS
1473	IF @ssn_in IS NULL OR (@course_code_in IS NULL AND @course_name_in IS NULL)
1474		PRINT 'Incomplete input!'
1475	ELSE
1476		SELECT *
1477		FROM Attends a INNER JOIN Courses c on a.course_code = c.code
1478		WHERE @course_name_in IS NOT NULL AND a.enrolled_ssn = @ssn_in AND c.name = @course_name_in
1479			UNION
1480		SELECT *
1481		FROM Attends a INNER JOIN Courses c on a.course_code = c.code
1482		WHERE @course_code_in IS NOT NULL AND a.enrolled_ssn = @ssn_in AND a.course_code = @course_code_in
1483GO
1484
1485--heyya	name = ISNULL(@name_in , name) tenfa3? 2aw fee 7aga zayyaha
1486------------------------------------------------------------------------
1487
1488
1489--TEACHERRRRRRRRRRRRRR
1490CREATE PROC Teacher_Sign_Up
1491@fname_in VARCHAR(25),
1492@mname_in VARCHAR(25),
1493@lname_in VARCHAR(25),
1494@birthdate_in DATE,
1495@address_in VARCHAR(100),
1496@email_in VARCHAR(50),
1497@gender_in BIT
1498AS
1499	IF @fname_in = NULL
1500	OR @mname_in = NULL
1501	OR @lname_in = NULL
1502	OR @birthdate_in = NULL
1503	OR @address_in = NULL
1504	OR @email_in = NULL
1505	OR @gender_in = NULL
1506		PRINT 'Missing Info'
1507	ELSE
1508		INSERT INTO Employees
1509		(first_name , middle_name , last_name , birth_date , employee_address , email , gender , is_teacher)
1510		VALUES (@fname_in , @mname_in , @lname_in , @birthdate_in , @address_in , @email_in , @gender_in , '1')
1511GO
1512--2
1513CREATE PROC Teacher_View_His_Courses
1514@email_in VARCHAR(50)
1515AS
1516	IF @email_in is NULL
1517		PRINT 'Please enter email!'
1518	ELSE
1519		SELECT c.name
1520		FROM Teaches t
1521		INNER JOIN Courses c ON c.code = t.course_code
1522		WHERE t.teacher_email = @email_in
1523
1524GO
1525--3
1526CREATE PROC Post_Assignments
1527	@assignment_number int,
1528	@course_code VARCHAR(10),
1529	@teacher_email VARCHAR(50),
1530	@posting_date DATE,
1531	@due_date DATE,
1532	@content VARCHAR(500)
1533AS
1534	IF 
1535		@assignment_number is NULL OR
1536		@course_code is NULL OR
1537		@teacher_email is NULL OR
1538		@posting_date is NULL OR
1539		@due_date is NULL OR
1540		@content is NULL
1541		PRINT 'some data is null'
1542	ELSE
1543		INSERT INTO Assignments
1544		VALUES(@assignment_number,
1545		@course_code,
1546		@teacher_email,
1547		@posting_date,
1548		@due_date,
1549		@content )
1550GO
1551--4
1552CREATE PROC Students_Solutions
1553	@email_in VARCHAR(50),
1554	@course_code INT
1555AS
1556	SELECT a.ssn, a.solution
1557	FROM Assignments_Solved_By_Students a
1558	WHERE a.teacher_email = @email_in AND a.course_code = @course_code
1559	ORDER BY a.ssn
1560GO
1561--5
1562CREATE PROC Grade_Assignments
1563	@teacher_email VARCHAR(50),
1564	@enrolled_ssn INT,
1565	@course_code VARCHAR(10),
1566	@grade FLOAT
1567AS
1568	UPDATE Assignments_Solved_By_Students
1569	SET grade = @grade 
1570	WHERE teacher_email = @teacher_email AND course_code = @course_code AND ssn = @enrolled_ssn
1571GO
1572--6
1573CREATE PROC Delete_Assignments
1574	@teacher_email VARCHAR(50),
1575	@course_code VARCHAR(10),
1576	@assignment_number InT
1577AS
1578DELETE FROM Assignments
1579WHERE teacher_email = @teacher_email AND course_code = @course_code AND assignment_number = @assignment_number
1580GO
1581
1582--7
1583CREATE PROC Write_Report
1584	@teacher_email VARCHAR(50),
1585	@enrolled_ssn INT,
1586	@comment VARCHAR(200)
1587AS
1588	UPDATE Monthly_Reports
1589	SET comment = @comment , issue_date = CAST(GETDATE() AS DATE)
1590	WHERE writer = @teacher_email AND enrolled_ssn = @enrolled_ssn
1591GO
1592--8
1593create PROC View_Questions
1594@teacher_email VARCHAR(50),
1595@course_code VARCHAR(10)
1596AS
1597SELECT q.question_number, e.name, q.question_itself
1598FROM Questions q
1599	INNER JOIN Enrolled_Students e ON q.askers_ssn = e.ssn
1600	INNER JOIN teaches t ON t.course_code = q.course_code
1601	WHERE q.course_code = @course_code AND t.teacher_email = @teacher_email
1602GO
1603--9
1604CREATE PROC Answer_Questions
1605@teacher_email VARCHAR(50),
1606@course_code VARCHAR(10),
1607@answer VARCHAR(500),
1608@number int
1609AS
1610	UPDATE Questions
1611	SET question_answer = @answer
1612	WHERE question_number = @number
1613GO
1614--10
1615CREATE PROC ListStudents
1616	@teacher_email VARCHAR(50)
1617AS
1618	IF @teacher_email IS NULL
1619		PRINT 'Null Input'
1620	ELSE
1621		SELECT e.*
1622		FROM Enrolled_Students e
1623				INNER JOIN Attends a on e.ssn = a.enrolled_ssn
1624				INNER JOIN Teaches t on t.course_code = a.course_code
1625				WHERE t.teacher_email = @teacher_email
1626GO
1627--11
1628CREATE PROC StudentsWithoutActivities
1629AS
1630	SELECT ssn
1631	FROM Enrolled_Students
1632	WHERE ssn NOT IN(
1633		SELECT e.ssn
1634		FROM Enrolled_Students e
1635		INNER JOIN Activities_Applied_By_Enrolled_Students a on e.ssn = a.ssn)
1636GO
1637--12
1638CREATE PROC StudentWithMostClubs
1639AS
1640	DECLARE @maxC INT
1641	SELECT @maxC = COUNT(*)
1642	FROM Enrolled_Students e INNER JOIN Clubs_Joined_By_Enrolled_Student c on e.ssn = c.enrolled_ssn
1643	GROUP BY e.ssn, e.name	
1644	SELECT e.name
1645	FROM Enrolled_Students e INNER JOIN Clubs_Joined_By_Enrolled_Student c on e.ssn = c.enrolled_ssn
1646
1647GO
1648
1649
1650--working insertions!!
1651
1652
1653
1654
1655
1656
1657
1658
1659INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1660VALUES('ST.Fatima@edu.eg',0,1,'02479106910','Heliopolice','ST.Fatima',1,1,0);
1661INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1662VALUES('ST.George@edu.eg',1,0,'02967910610','Shubra','ST.George',1,0,0);
1663INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1664VALUES('ELNozha@edu.eg',1,0,'021448323','Hegaz','EL Nozha',0,0,1);
1665INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1666VALUES('NewRamces@edu.eg',1,0,'0248825101','Heliopolice','New Ramces',0,0,1);
1667INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1668VALUES('Future@edu.eg',1,0,'029855952','Ramsees','Future',0,1,0);
1669INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1670VALUES('Karma@edu.eg',1,1,'029943119','5th settlement','Karma',1,1,1);
1671INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1672VALUES('Modern@edu.eg',0,1,'0223418103','Shubra','Modern',1,1,1);
1673INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1674VALUES('MSA@edu.eg',1,0,'027786922','Heliopolice','MSA',0,1,1);
1675INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1676VALUES('Rajak@edu.eg',0,1,'0210576831','Heliopolice','Rajak',0,0,1);
1677INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1678VALUES('ST.John@edu.eg',0,1,'0251610184','Shubra','St.John',1,0,1);
1679INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1680VALUES('Elite@edu.eg',1,0,'029923627','Heliopolice','Elite',0,0,1);
1681INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1682VALUES('Tabre@edu.eg',1,0,'022238343','5th settlement','Tabre',1,1,0);
1683INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1684VALUES('Sacrecoeur@edu.eg',1,1,'0210237531','Heliopolice','Sacre coeur',1,0,0);
1685INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1686VALUES('Shwyfat@edu.eg',1,0,'0251313104','Shubra','Shwyfat',0,1,0);
1687GO
1688---------------------------------
1689INSERT INTO Elementary_Supplies(supply, email)
1690VALUES('supplies', 'Sacrecoeur@edu.eg');
1691INSERT INTO Elementary_Supplies(supply, email)
1692VALUES('some_fries', 'Sacrecoeur@edu.eg');
1693INSERT INTO Elementary_Supplies(supply, email)
1694VALUES('some_fries', 'Tabre@edu.eg');
1695INSERT INTO Elementary_Supplies(supply, email)
1696VALUES('sunrise', 'Karma@edu.eg');
1697INSERT INTO Elementary_Supplies(supply, email)
1698VALUES('all_rise', 'ST.Fatima@edu.eg');
1699GO
1700---------------------------------
1701INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
1702VALUES('maged.awad@MSA.edu.eg','maged','awad','maged.awad','mego')
1703INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
1704VALUES('sherif.hanna@ST.Fatima.edu.eg','sherif','hanna','sherif.hanna','shico')
1705INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
1706VALUES('slim.abdel@ST.John.edu.eg','slim','abdel','slim.abdel','ballabizo')
1707INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
1708VALUES('aury.abdo@Shwyfat.edu.eg','aury','abdo','aury.abdo','ooory')
1709INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
1710VALUES('mira.ibrahim@Tabre.edu.eg','mira','ibrahim','mira.ibrahim','miiiraaa')
1711INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
1712VALUES('william.kaizar@ST.Fatima.edu.eg','william','kaizar','william.kaizar','wilo')
1713INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
1714VALUES('guirguis.samir@ST.George.edu.eg','guirguis','samir','guirguis.samir','gogo')
1715INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
1716VALUES('caren.miky@ST.Fatima.edu.eg','caren','miky','caren.miky','koko')
1717INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
1718VALUES('shady.azoz@MSA.edu.eg','shady','azoz','shady.azoz','shodz')
1719GO
1720-----------------------------------------
1721INSERT INTO Parents_Mobiles(mobile , email)
1722VALUES(012,'maged.awad@MSA.edu.eg');
1723INSERT INTO Parents_Mobiles(mobile , email)
1724VALUES(011,'aury.abdo@Shwyfat.edu.eg');
1725INSERT INTO Parents_Mobiles(mobile , email)
1726VALUES(010,'william.kaizar@ST.Fatima.edu.eg');
1727INSERT INTO Parents_Mobiles(mobile , email)
1728VALUES(0122,'shady.azoz@MSA.edu.eg');
1729GO
1730-----------------------------------------
1731INSERT INTO Applicants(ssn,name,parent_email)
1732VALUES(1234,'david','maged.awad@MSA.edu.eg') 
1733INSERT INTO Applicants(ssn,name,parent_email)
1734VALUES(12345,'peter','sherif.hanna@ST.Fatima.edu.eg') 
1735INSERT INTO Applicants(ssn,name,parent_email)
1736VALUES(12346,'david','slim.abdel@ST.John.edu.eg') 
1737INSERT INTO Applicants(ssn,name,parent_email)
1738VALUES(12347,'mina','william.kaizar@ST.Fatima.edu.eg') 
1739INSERT INTO Applicants(ssn,name,parent_email)
1740VALUES(12348,'fadya','shady.azoz@MSA.edu.eg') 
1741INSERT INTO Applicants(ssn,name,parent_email)
1742VALUES(12349,'sandra','guirguis.samir@ST.George.edu.eg') 
1743INSERT INTO Applicants(ssn,name,parent_email)
1744VALUES(12342,'michael','aury.abdo@Shwyfat.edu.eg')
1745GO
1746----------------------------------------------
1747INSERT INTO Courses(code,name)
1748VALUES('CSEN501','databases')
1749INSERT INTO Courses(code,name)
1750VALUES('CSEN502','networks')
1751INSERT INTO Courses(code,name)
1752VALUES('CSEN503','theory')
1753INSERT INTO Courses(code,name)
1754VALUES('MATH501','math')
1755INSERT INTO Courses(code,name)
1756VALUES('CSEN601','dsd')
1757GO
1758-----------------------------------------------
1759INSERT INTO Employees (email , employee_user_name , employee_password)
1760VALUES ('aa' , 'aa' , 'aa')
1761INSERT INTO Employees (email , employee_user_name , employee_password , is_teacher , is_administrator)
1762VALUES ('bb' , 'bb' , 'bb' , 1 , 1)
1763INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,salary,enrollment_year ,school_email, is_administrator,is_teacher,is_supervisor ,supervisor_email )
1764VALUES('ramy.mahfouz@ST.Fatima.edu.eg','ramy.mahfouz','rooooroooo','ramy','mahfouz','hanna',10000,'1996-1-1','ST.Fatima@edu.eg',0,0,1,null)
1765INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,salary,enrollment_year ,school_email, is_administrator,is_teacher,is_supervisor ,supervisor_email )
1766VALUES('ashraf.hassan@ST.Fatima.edu.eg','ashraf.hassan','hoooosoooo','ashraf','hassan','ismail',5000,'2006-2-2','ST.John@edu.eg',1,0,0,'ramy.mahfouz@ST.Fatima.edu.eg')
1767INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,salary,enrollment_year ,school_email, is_administrator,is_teacher,is_supervisor ,supervisor_email )
1768VALUES('kero.adel@ST.Fatima.edu.eg','kero.adel','kooookoooo','kero','adel','fahmy',5000,'2006-3-4','ST.Fatima@edu.eg',1,1,0,'ramy.mahfouz@ST.Fatima.edu.eg')
1769INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,salary,enrollment_year ,school_email, is_administrator,is_teacher,is_supervisor ,supervisor_email )
1770VALUES('george.krahy@ST.Fatima.edu.eg','george.krahy','goooogoooo','george','krahy','kareem','5000','2006-5-6','ST.Fatima@edu.eg',1,0,0,'ramy.mahfouz@ST.Fatima.edu.eg')
1771INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,salary,enrollment_year ,school_email, is_administrator,is_teacher,is_supervisor ,supervisor_email )
1772VALUES('michael.sherif@ST.Fatima.edu.eg','michael.sherif','moooomoooo','michael','sherif','hanna',10000,'1999-7-8','ST.Fatima@edu.eg',0,1,0,null)
1773INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,salary,enrollment_year ,school_email, is_administrator,is_teacher,is_supervisor ,supervisor_email )
1774VALUES('teshtar@testar.com','teshtar.teshtar','teshhhhhh','teshtar','teshtaren','talata',10000,'1999-7-9','Tabre@edu.eg',0,1,0,null)
1775GO
1776----------------------------------------------------------------------
1777--EXEC ViewUnverifiedTeachers 'kero.adel@ST.Fatima.edu.eg'
1778
1779--EXEC Enrolled_Student_View_Courses 2355124;
1780
1781INSERT INTO Enrolled_Students --school_email , parent_email
1782(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
1783VALUES (2345124,5,'7amada',0,'1995-1-2','3ayezArawwa7','Hamada','ST.Fatima@edu.eg','maged.awad@MSA.edu.eg')
1784INSERT INTO Enrolled_Students
1785(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
1786VALUES (2355124,5,'7amaden',0,'1995-2-2','3ayezArawwa7en','Hamaden','ST.Fatima@edu.eg','maged.awad@MSA.edu.eg') --e5wat
1787INSERT INTO Enrolled_Students --school_email , parent_email
1788(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
1789VALUES (2115124,5,'Samy',0,'1995-3-2','Mesh3ayezArawwa7','Samy','Rajak@edu.eg','slim.abdel@ST.John.edu.eg')
1790INSERT INTO Enrolled_Students --school_email , parent_email
1791(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
1792VALUES (2113524,5,'7ommos',0,'1996-3-2','Rawwa7t','Hommos','Shwyfat@edu.eg','shady.azoz@MSA.edu.eg')
1793INSERT INTO Enrolled_Students --school_email , parent_email
1794(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
1795VALUES (2115554,5,'7ommoseyya',0,'1996-4-2','Merwa7','Hommoseyya','Modern@edu.eg','shady.azoz@MSA.edu.eg')
1796GO
1797------------------------------------------------
1798INSERT INTO Courses_Prequisites
1799(course_code_1,course_code_2)
1800VALUES ('CSEN501','CSEN502')
1801GO
1802-------------------------------------------------
1803INSERT INTO Courses_Offered_By_Schools
1804(course_code , school_email)
1805VALUES ('CSEN601','ELNozha@edu.eg')
1806INSERT INTO Courses_Offered_By_Schools
1807(course_code , school_email)
1808VALUES ('CSEN601','ST.Fatima@edu.eg')
1809INSERT INTO Courses_Offered_By_Schools
1810(course_code , school_email)
1811VALUES ('MATH501','ST.Fatima@edu.eg')
1812GO
1813------------------------------------------------------------
1814INSERT INTO Activities --administrator_email , teacher_email , school_email
1815(activity_date,activity_location,activity_type,activity_description,administrator_email,teacher_email,school_email)
1816VALUES('2012-12-12','fel gam3a','trip','howwa keda','michael.sherif@ST.Fatima.edu.eg','kero.adel@ST.Fatima.edu.eg','ST.Fatima@edu.eg')
1817INSERT INTO Activities
1818(activity_date,activity_location,activity_type,activity_description,administrator_email,teacher_email,school_email)
1819VALUES('2012-11-11','barra el gam3a','trip','enter deskripshan heer','michael.sherif@ST.Fatima.edu.eg','george.krahy@ST.Fatima.edu.eg','ST.Fatima@edu.eg')
1820INSERT INTO Activities
1821(activity_date,activity_location,activity_type,activity_description,administrator_email,teacher_email,school_email)
1822VALUES('2012-10-10','ganb el gam3a','trip','kaman re7la wmalo','michael.sherif@ST.Fatima.edu.eg','kero.adel@ST.Fatima.edu.eg','ST.Fatima@edu.eg')
1823GO
1824-----------------------------------------------------------------------------
1825INSERT INTO Equipment --activity_date activity_location men Activities
1826(activity_date,activity_location,equipment)
1827VALUES ('2012-12-12','fel gam3a','3asaya')
1828INSERT INTO Equipment
1829(activity_date,activity_location,equipment)
1830VALUES ('2012-11-11','barra el gam3a','kora')
1831INSERT INTO Equipment --activity_date activity_location men Activities
1832(activity_date,activity_location,equipment)
1833VALUES ('2012-12-12','fel gam3a','gardal')
1834INSERT INTO Equipment --activity_date activity_location men Activities
1835(activity_date,activity_location,equipment)
1836VALUES ('2012-11-11','barra el gam3a','7anafeyya')
1837INSERT INTO Equipment --activity_date activity_location men Activities
1838(activity_date,activity_location,equipment)
1839VALUES ('2012-10-10','ganb el gam3a','mayya ma3daneyya')
1840INSERT INTO Equipment --activity_date activity_location men Activities
1841(activity_date,activity_location,equipment)
1842VALUES ('2012-10-10','ganb el gam3a','far5a zengeyya')
1843GO
1844----------------------------------------------------------
1845INSERT INTO Clubs
1846(name , purpose) VALUES ('MUNday','unknown')
1847INSERT INTO Clubs
1848(name , purpose) VALUES ('TImon','play mohaa')
1849INSERT INTO Clubs
1850(name , purpose) VALUES ('IEEEEEEEEE ya rasy','eat circuits for breakfast!')
1851INSERT INTO Clubs
1852(name , purpose) VALUES ('3ashan 5atrik ya gam3ety','ne3mel 7aga')
1853GO
1854------------------------------------------------------------
1855INSERT INTO Clubs_Offered_By_Schools
1856(name , school_email)
1857VALUES ('MUNday','ST.John@edu.eg')
1858INSERT INTO Clubs_Offered_By_Schools
1859(name , school_email)
1860VALUES ('TImon','Tabre@edu.eg')
1861INSERT INTO Clubs_Offered_By_Schools
1862(name , school_email)
1863VALUES ('TImon','Shwyfat@edu.eg')
1864INSERT INTO Clubs_Offered_By_Schools
1865(name , school_email)
1866VALUES ('3ashan 5atrik ya gam3ety','Tabre@edu.eg')
1867INSERT INTO Clubs_Offered_By_Schools
1868(name , school_email)
1869VALUES ('IEEEEEEEEE ya rasy','Shwyfat@edu.eg')
1870
1871INSERT INTO Clubs_Offered_By_Schools
1872(name , school_email)
1873VALUES ('IEEEEEEEEE ya rasy','Modern@edu.eg')
1874INSERT INTO Clubs_Offered_By_Schools
1875(name , school_email)
1876VALUES ('IEEEEEEEEE ya rasy','Rajak@edu.eg')
1877GO
1878----------------------------------------------------------------
1879INSERT INTO Questions --course_code , askers_ssn
1880(course_code,question_itself,askers_ssn)
1881VALUES ('CSEN601','E7na leh bna5od el course dah?',2345124 )
1882INSERT INTO Questions
1883(course_code,question_itself,askers_ssn)
1884VALUES ('CSEN601','Ya3ny eh insert?',2355124 )
1885INSERT INTO Questions
1886(course_code,question_itself,askers_ssn)
1887VALUES ('CSEN601','Ya3ny eh delete?',2115124 )
1888INSERT INTO Questions
1889(course_code,question_itself,askers_ssn)
1890VALUES ('MATH501','Ya3ny eh logical sequence?',2345124 )
1891GO
1892--------------------------------------------------------
1893INSERT INTO Assignments --course_code , teacher_email
1894(assignment_number,course_code,teacher_email,posting_date,due_date,content)
1895VALUES (4,'CSEN601','kero.adel@ST.Fatima.edu.eg','2012-3-18','2012-3-19','e3mel database')
1896INSERT INTO Assignments
1897(assignment_number,course_code,teacher_email,posting_date,due_date,content)
1898VALUES (9,'CSEN601','kero.adel@ST.Fatima.edu.eg','2012-4-18','2012-4-19','e3mel social network')
1899INSERT INTO Assignments
1900(assignment_number,course_code,teacher_email,posting_date,due_date,content)
1901VALUES (900,'MATH501','michael.sherif@ST.Fatima.edu.eg','2012-5-18','2012-5-19','proove that the implication aslaha erd')
1902GO
1903------------------------------------------------------------------------
1904INSERT INTO Announcements --administrator_email men Employees , school_email
1905(administrator_email , announcement_date , announcement_time , announcement_type , title , announcement_description , school_email)
1906VALUES ('ashraf.hassan@ST.Fatima.edu.eg','2010-3-3','10:10','trip','el trip','hanroo7 henak','MSA@edu.eg')
1907INSERT INTO Announcements
1908(administrator_email , announcement_date , announcement_time , announcement_type , title , announcement_description , school_email)
1909VALUES ('george.krahy@ST.Fatima.edu.eg','2010-3-4','10:10','trip','trip tanya','hanroo7 henak bardo','Future@edu.eg')
1910INSERT INTO Announcements
1911(administrator_email , announcement_date , announcement_time , announcement_type , title , announcement_description , school_email)
1912VALUES ('kero.adel@ST.Fatima.edu.eg','2010-3-5','10:10','trip talta','fee akl','hanroo7 henak tany','Karma@edu.eg')
1913INSERT INTO Announcements
1914(administrator_email , announcement_date , announcement_time , announcement_type , title , announcement_description , school_email)
1915VALUES ('george.krahy@ST.Fatima.edu.eg','2016-11-28','11:10','trip taltaNEW','fee akl','hanroo7 henak tany','ST.Fatima@edu.eg') --change date to be visible
1916GO
1917---
1918INSERT INTO Announcements_Visible_To_Enrolled_Students
1919(administrator_email , announcement_date , announcement_time , enrolled_ssn)
1920VALUES ('ashraf.hassan@ST.Fatima.edu.eg','03-Mar-10','10:10:00',2355124)
1921INSERT INTO Announcements_Visible_To_Enrolled_Students
1922(administrator_email , announcement_date , announcement_time , enrolled_ssn)
1923VALUES ('george.krahy@ST.Fatima.edu.eg','04-Mar-10','10:10:00',2345124)
1924INSERT INTO Announcements_Visible_To_Enrolled_Students
1925(administrator_email , announcement_date , announcement_time , enrolled_ssn)
1926VALUES ('george.krahy@ST.Fatima.edu.eg','2016-11-28','11:10:00',2345124) --change date to be visible
1927GO
1928--SELECT * FROM Announcements
1929---------------------------------------------------------------------------
1930INSERT INTO Monthly_Reports --enrolled_ssn men Enrolled_student , writer men Employees
1931(enrolled_ssn , issue_date , comment , writer)
1932VALUES (2113524 , '2011-12-28' , 'good student' , 'michael.sherif@ST.Fatima.edu.eg')
1933INSERT INTO Monthly_Reports
1934(enrolled_ssn , issue_date , comment , writer)
1935VALUES (2115124 , '2011-12-18' , 'good student' , 'michael.sherif@ST.Fatima.edu.eg')
1936INSERT INTO Monthly_Reports
1937(enrolled_ssn , issue_date , comment , writer)
1938VALUES (2355124 , '2012-4-28' , 'bad student' , 'michael.sherif@ST.Fatima.edu.eg')
1939GO
1940--------------------------------------------------------------
1941INSERT INTO Teaches
1942(teacher_email , course_code)
1943VALUES ('kero.adel@ST.Fatima.edu.eg','CSEN503')
1944INSERT INTO Teaches
1945(teacher_email , course_code)
1946VALUES ('kero.adel@ST.Fatima.edu.eg','CSEN601')
1947INSERT INTO Teaches
1948(teacher_email , course_code)
1949VALUES ('michael.sherif@ST.Fatima.edu.eg','MATH501')
1950GO
1951
1952------------------------------------------------
1953INSERT INTO Attends
1954(enrolled_ssn , course_code)
1955VALUES (2355124,'MATH501')
1956INSERT INTO Attends
1957(enrolled_ssn , course_code)
1958VALUES (2355124,'CSEN601')
1959INSERT INTO Attends
1960(enrolled_ssn , course_code)
1961VALUES (2115124,'MATH501')
1962INSERT INTO Attends
1963(enrolled_ssn , course_code)
1964VALUES (2115554,'MATH501')
1965INSERT INTO Attends
1966(enrolled_ssn , course_code)
1967VALUES (2115554,'CSEN601')
1968INSERT INTO Attends
1969(enrolled_ssn , course_code)
1970VALUES (2113524,'MATH501')
1971GO
1972------------------------------------------------------
1973INSERT INTO Clubs_Joined_By_Enrolled_Student
1974(club_name , enrolled_ssn)
1975VALUES ('IEEEEEEEEE ya rasy' , 2115124)
1976INSERT INTO Clubs_Joined_By_Enrolled_Student
1977(club_name , enrolled_ssn)
1978VALUES ('3ashan 5atrik ya gam3ety' , 2115124)
1979INSERT INTO Clubs_Joined_By_Enrolled_Student
1980(club_name , enrolled_ssn)
1981VALUES ('3ashan 5atrik ya gam3ety' , 2345124)
1982INSERT INTO Clubs_Joined_By_Enrolled_Student
1983(club_name , enrolled_ssn)
1984VALUES ('MUNday' , 2345124)
1985INSERT INTO Clubs_Joined_By_Enrolled_Student
1986(club_name , enrolled_ssn)
1987VALUES ('TImon' , 2345124)
1988GO
1989
1990
1991INSERT INTO Monthly_Reports_Replies
1992(enrolled_ssn , issue_date , parent_email , reply)
1993VALUES (2113524 , '28-Dec-11' , 'mira.ibrahim@Tabre.edu.eg' , 'thank you veery mutsh')
1994GO
1995
1996
1997INSERT INTO Enrolled_Students_View_Questions
1998(ssn , question_number , course_code)
1999VALUES (2355124 , 2 , 'CSEN601')
2000GO
2001
2002INSERT INTO Teachers_Response_To_Questions
2003(question_number , course_code , teacher_email)
2004VALUES (1 , 'CSEN601' , 'teshtar@testar.com')
2005GO
2006
2007--INSERT INTO Parents_Ratings_To_Teachers
2008--(employee_email , parent_email , rating)
2009--VALUES ()
2010
2011INSERT INTO Announcements_Visible_To_Parents
2012(administrator_email , announcement_date , announcement_time , parent_email)
2013VALUES ('ashraf.hassan@ST.Fatima.edu.eg','2010-3-3','10:10', 'shady.azoz@MSA.edu.eg')
2014GO
2015
2016INSERT INTO View_And_Grade_Assignments
2017(viewer_email , assignment_number , course_code , creator_email)
2018VALUES ('teshtar@testar.com' , 9 , 'CSEN601' , 'kero.adel@ST.Fatima.edu.eg')
2019GO
2020
2021INSERT INTO Assignments_Solved_By_Students
2022(ssn , assignment_number , teacher_email , course_code , solution)
2023VALUES (2345124 , 9 , 'kero.adel@ST.Fatima.edu.eg' , 'CSEN601' , 'bottom implies top')
2024GO
2025
2026INSERT INTO Activities_Applied_By_Enrolled_Students
2027(activity_date , activity_location , ssn)
2028VALUES ('2012-12-12' , 'fel gam3a' , 2115124)
2029GO
2030
2031INSERT INTO Courses_Offered_By_Schools
2032(course_code , school_email)
2033VALUES ('MATH501','Shwyfat@edu.eg')
2034GO
2035
2036INSERT INTO Applicants_Applies_Schools
2037(school_email , child_ssn , accepted , parent_email)
2038VALUES ('Shwyfat@edu.eg' , 12345 , 0 , 'sherif.hanna@ST.Fatima.edu.eg')
2039GO
2040
2041
2042
2043
2044
2045
2046--proc executions
2047/*
2048
2049
2050--@ssn_in INT,
2051--@name_in VARCHAR(50),
2052--@gender_in BIT,
2053--@birth_date_in DATE,
2054--@password_in VARCHAR(20),
2055--@school_email_in VARCHAR(50),
2056--@parent_email_in VARCHAR(50)
2057
2058
2059SELECT * FROM Enrolled_Students
2060EXEC Enrolled_Student_Info_Update  2113524 , 'Defet' , '0' , '1995-2-2' , 'mafeeshpasswerd' , 'Rajak@edu.eg' , 'shady.azoz@MSA.edu.eg'
2061
2062EXEC Enrolled_Student_View_Courses 2345124
2063EXEC Enrolled_Student_View_Courses 2355124
2064
2065EXEC Enrolled_Student_Post_Question 'MATH501' , '3ashan se2et?' , 2355124
2066SELECT * FROM Questions
2067
2068EXEC Enrolled_Student_View_Questions 'MATH501' , 2355124
2069
2070EXEC Enrolled_Student_View_Questions 'CSEN601' , 2355124
2071
2072--proc 4 far2 el "e"
2073
2074EXEC Enrolled_Student_View_All_Assignments 2355124
2075SELECT * FROM Assignments
2076
2077EXEC Enrolled_Student_Solves_Assignment 2355124 , 4 , 'kero.adel@ST.Fatima.edu.eg' , 'CSEN601' , 'boss enta 5od el quiz 7otto fel 5allast yetla3lak social network agmad men facebook'
2078SELECT * FROM Assignments_Solved_By_Students
2079
2080EXEC Enrolled_Students_Views_Assignments_Grades 2355124
2081
2082
2083PRINT DATEDIFF(DAY,'11-27-16','11-28-16')
2084
2085EXEC Enrolled_Student_Views_Announcements5 2345124
2086
2087SELECT * FROM Announcements
2088*/
2089
2090
2091
2092
2093
2094--USE dec3h
2095--GO
2096
2097
2098
2099
2100--PHP PROCEDURES
2101/*
2102CREATE PROC AdminLogIn
2103@unamein VARCHAR(25),
2104@passin VARCHAR(25)
2105AS
2106	SELECT *
2107	FROM Employees e
2108	WHERE e.employee_user_name = @unamein AND e.employee_password = @passin AND e.is_administrator = '1'
2109GO
2110
2111CREATE PROC StudentLogIn
2112@unamein VARCHAR(25),
2113@passin VARCHAR(25)
2114AS
2115	SELECT *
2116	FROM Enrolled_Students e
2117	WHERE e.student_user_name = @unamein AND e.student_password = @passin
2118GO
2119
2120CREATE PROC ParentLogIn
2121@unamein VARCHAR(25),
2122@passin VARCHAR(25)
2123AS
2124	SELECT first_name , email
2125	FROM Parents e
2126	WHERE e.parent_user_name = @unamein AND e.parent_password = @passin
2127GO
2128
2129
2130CREATE PROC TeacherLogIn
2131@unamein VARCHAR(25),
2132@passin VARCHAR(25)
2133AS
2134	SELECT first_name , email
2135	FROM Employees e
2136	WHERE e.employee_user_name = @unamein AND e.employee_password = @passin AND e.is_teacher = '1'
2137GO
2138
2139CREATE PROC TeacherSignUp
2140@unamein VARCHAR(25),
2141@passin VARCHAR(25)
2142AS
2143	SELECT first_name , email
2144	FROM Employees e
2145	WHERE e.employee_user_name = @unamein AND e.employee_password = @passin AND e.is_administrator = '1'
2146GO*/
2147
2148--EXEC ParentLogIn "aa" , "aa"
2149
2150--USE dec3h
2151--GO
2152
2153--SELECT * FROM Enrolled_Students
2154
2155INSERT INTO Enrolled_Students
2156(ssn , student_user_name , student_password)
2157VALUES(12341234 , 'aa' , 'aa')
2158INSERT INTO Enrolled_Students --school_email , parent_email
2159(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
2160VALUES (211555,5,'7ommoseyya',0,'1996-4-2','bb','bb','Modern@edu.eg','shady.azoz@MSA.edu.eg')
2161GO
2162
2163--SELECT * FROM Employees
2164
2165--SELECT * FROM Parents
2166
2167--SELECT * FROM 
2168
2169--Teacher_Sign_Up
2170
2171--EXEC InsertParents "ASDF","asdf","asfd","ads","adsf","asdf","asdf"
2172
2173--EXEC InsertParents "aa","aa","aa","aa","aa","aa","aa"
2174
2175--EXEC Enrolled_Student_Info_Update 12341234 , 
2176
2177--InsertParents
2178
2179
2180/*CREATE PROC ParentSignUp
2181@unamein VARCHAR(25),
2182@passin VARCHAR(25),
2183@email_in VARCHAR(50),
2184@address_in VARCHAR(100),
2185@fname_in VARCHAR(25),
2186@lname_in VARCHAR(25),
2187@phone_in VARCHAR(20)
2188AS
2189	SELECT first_name , email
2190	FROM Employees e
2191	WHERE e.employee_user_name = @unamein AND e.employee_password = @passin AND e.is_administrator = '1'
2192GO*/
2193
2194--SELECT * FROM Employees
2195
2196--EXEC AdminLogIn "ashraf.hassan" , "hoooosoooo"
2197
2198
2199
2200--SELECT *
2201--FROM Employees e
2202--WHERE e.is_verified = '0' AND e.is_teacher = '1' 
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212CREATE PROC ES_View_Info
2213@ssn_in INT
2214AS
2215    SELECT *
2216    FROM Enrolled_Students es
2217    WHERE es.ssn = @ssn_in
2218GO
2219--THEN USE
2220--EXEC Enrolled_Student_Info_Update
2221/*
2222@ssn_in INT,
2223@name_in VARCHAR(50),
2224@gender_in BIT,
2225@birth_date_in DATE,
2226@password_in VARCHAR(20),
2227@school_email_in VARCHAR(50),
2228@parent_email_in VARCHAR(50)
2229*/
2230
2231
2232
2233
2234-- Enrolled_Student_View_Courses
2235--@ssn_in INT
2236
2237
2238--ok
2239
2240--ok
2241
2242--ok
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256--EXEC sp_who2
2257/*
2258USE master
2259GO
2260ALTER DATABASE dec3e
2261SET OFFLINE WITH ROLLBACK IMMEDIATE
2262GO
2263
2264DROP DATABASE dec3e*/
2265
2266
2267
2268
2269
2270
2271
2272
2273
2274
2275--mikel dec5
2276
2277CREATE PROC ViewSchools
2278@school_name VARCHAR(50),
2279@school_address VARCHAR(100),
2280@school_type bit
2281AS
2282	IF  @school_name IS NULL AND
2283		@school_address IS NULL AND
2284		@school_type IS NULL
2285		Print 'Null Input'
2286	ELSE
2287		if @school_name IS NOT NULL
2288		Begin
2289			SELECT *
2290			FROM Schools
2291			WHERE @school_name IS NOT NULL AND name = @school_name
2292			RETURN
2293		END
2294		if @school_address IS NOT NULL
2295		BEGIN
2296			SELECT *
2297			FROM Schools
2298			WHERE @school_address IS NOT NULL AND school_address = @school_address
2299			RETURN
2300		END
2301		if @school_type IS NOT NULL
2302		Begin
2303			SELECT *
2304			FROM Schools
2305			WHERE @school_type IS NOT NULL AND is_international = @school_type
2306			RETURN
2307		END
2308Go
2309
2310
2311--exec ViewSchools 'Elite', Null, 1
2312
2313CREATE PROC SchoolReviews
2314	@email VARCHAR(50)
2315AS
2316	SELECT *
2317	FROM Parents_Write_Review_Schools
2318	WHERE school_email = @email
2319GO
2320
2321CREATE PROC SchoolAnonucements
2322	@email VARCHAR(50)
2323AS
2324	SELECT *
2325	FROM dbo.Announcements
2326	WHERE school_email = @email
2327GO
2328CREATE FUNCTION GetStudentSSN(@username VARCHAR(20), @pass VARCHAR(20))
2329	RETURNS INT
2330BEGIN
2331	DECLARE @returnedValue INT
2332	SET @returnedValue = -1
2333		SELECT @returnedValue = ssn
2334		FROM Enrolled_Students
2335		WHERE student_user_name = @username AND student_password = @pass
2336	RETURN @returnedValue
2337END
2338GO
2339
2340CREATE FUNCTION GetParentEmail(@username VARCHAR(20), @pass VARCHAR(20))
2341	RETURNS VARCHAR(50)
2342BEGIN
2343	DECLARE @returnedValue VARCHAR(50)
2344	SET @returnedValue = ''
2345		SELECT @returnedValue = email
2346		FROM Parents
2347		WHERE parent_user_name = @username AND parent_password = @pass
2348	RETURN @returnedValue
2349END
2350GO
2351
2352CREATE FUNCTION GetEmployeeEmail(@username VARCHAR(20), @pass VARCHAR(20), @isAdmin BIT)
2353	RETURNS VARCHAR(50)
2354BEGIN
2355	DECLARE @returnedValue VARCHAR(50)
2356	SET @returnedValue = ''
2357		IF @isAdmin = 1
2358			SELECT @returnedValue = email
2359			FROM Employees
2360			WHERE employee_user_name = @username AND employee_password = @pass AND is_administrator = 1
2361		ELSE
2362			SELECT @returnedValue = email
2363			FROM Employees
2364			WHERE employee_user_name = @username AND employee_password = @pass AND is_administrator = 0
2365	RETURN @returnedValue
2366END
2367GO
2368
2369CREATE PROC ViewUnverifiedTeachers
2370@admin_email VARCHAR(50)
2371AS
2372	IF
2373		@admin_email IS NULL
2374		print 'Null Input'
2375	ELSE
2376		DECLARE @isAdmin BIT
2377		DECLARE @school_email VARCHAR(50)
2378		Select @isAdmin = is_administrator, @school_email = e.school_email
2379		FROM Employees e
2380		WHERE e.email = @admin_email
2381		IF @isAdmin = 1
2382			Select *
2383			FROM Employees e
2384			Where e.is_teacher = 1 AND e.school_email = @school_email AND employee_password IS NULL AND employee_user_name IS NULL
2385		ELSE
2386			PRINT 'Not Admin'
2387GO
2388
2389CREATE FUNCTION CheckAdminExists(@username VARCHAR(20), @pass VARCHAR(20), @email VARCHAR(50))
2390	RETURNS BIT
2391BEGIN
2392	DECLARE @returnedValue BIT
2393	SET @returnedValue = 0
2394		IF EXISTS
2395		(
2396			SELECT *
2397			FROM Employees
2398			WHERE employee_user_name = @username AND employee_password = @pass AND email = @email AND is_administrator = 1
2399		)
2400		SET @returnedValue = 1
2401	RETURN @returnedValue
2402END
2403GO
2404CREATE FUNCTION CheckTeacherExists(@username VARCHAR(20), @pass VARCHAR(20), @email VARCHAR(50))
2405	RETURNS BIT
2406BEGIN
2407	DECLARE @returnedValue BIT
2408	SET @returnedValue = 0
2409		IF EXISTS
2410		(
2411			SELECT *
2412			FROM Employees
2413			WHERE employee_user_name = @username AND employee_password = @pass AND email = @email AND is_teacher = 1
2414		)
2415		SET @returnedValue = 1
2416	RETURN @returnedValue
2417END
2418GO
2419CREATE FUNCTION CheckParentExists(@username VARCHAR(20), @pass VARCHAR(20), @email VARCHAR(50))
2420	RETURNS BIT
2421BEGIN
2422	DECLARE @returnedValue BIT
2423	SET @returnedValue = 0
2424		IF EXISTS
2425		(
2426			SELECT *
2427			FROM Parents
2428			WHERE parent_user_name = @username AND parent_password = @pass AND email = @email
2429		)
2430		SET @returnedValue = 1
2431	RETURN @returnedValue
2432END
2433GO
2434CREATE FUNCTION CheckStudentExists(@username VARCHAR(20), @pass VARCHAR(20), @ssn INT)
2435	RETURNS BIT
2436BEGIN
2437	DECLARE @returnedValue BIT
2438	SET @returnedValue = 0
2439		IF EXISTS
2440		(
2441			SELECT *
2442			FROM Enrolled_Students
2443			WHERE student_user_name = @username AND student_password = @pass AND ssn = @ssn
2444		)
2445		SET @returnedValue = 1
2446	RETURN @returnedValue
2447END
2448GO
2449
2450CREATE PROC ViewStudentApplications
2451@admin_email VARCHAR(50)
2452AS
2453	IF
2454		@admin_email IS NULL
2455		print 'Null Input'
2456	ELSE
2457		DECLARE @isAdmin BIT
2458		DECLARE @school_email VARCHAR(50)
2459		Select @isAdmin = is_administrator, @school_email = e.school_email
2460		FROM Employees e
2461		WHERE e.email = @admin_email
2462		IF @isAdmin = 1
2463			Select *
2464			FROM Applicants_Applies_Schools
2465			Where school_email = @school_email AND accepted = 0
2466		ELSE
2467			PRINT 'Not Admin'
2468GO
2469
2470go
2471CREATE PROC ViewUnverifiedStudents
2472@admin_email VARCHAR(50)
2473AS
2474	IF
2475		@admin_email IS NULL
2476		print 'Null Input'
2477	ELSE
2478		DECLARE @isAdmin BIT
2479		DECLARE @school_email VARCHAR(50)
2480		Select @isAdmin = is_administrator, @school_email = e.school_email
2481		FROM Employees e
2482		WHERE e.email = @admin_email
2483		IF @isAdmin = 1
2484			Select *
2485			FROM Enrolled_Students
2486			Where school_email = @school_email AND student_password IS NULL AND student_user_name IS NULL
2487		ELSE
2488			PRINT 'Not Admin'
2489GO
2490
2491CREATE PROC ViewActivities
2492@admin_email VARCHAR(50)
2493AS
2494	IF
2495		@admin_email IS NULL
2496		print 'Null Input'
2497	ELSE
2498		DECLARE @isAdmin BIT
2499		DECLARE @school_email VARCHAR(50)
2500		Select @isAdmin = is_administrator, @school_email = e.school_email
2501		FROM Employees e
2502		WHERE e.email = @admin_email
2503		IF @isAdmin = 1
2504			Select *
2505			FROM Activities
2506			Where administrator_email = @admin_email
2507		ELSE
2508			PRINT 'Not Admin'
2509GO
2510
2511
2512CREATE PROC tstasdfa
2513@teacher_email VARCHAR(50)
2514AS
2515	SELECT *
2516	FROM Enrolled_Students e
2517	INNER JOIN Attends a ON (e.ssn = a.enrolled_ssn)
2518	WHERE a.course_code IN
2519		(Select course_code
2520		FROM Teaches
2521		Where teacher_email = @teacher_email)
2522GO
2523
2524CREATE PROC teachersOfChildren
2525@parent_email VARCHAR(50)
2526AS
2527	SELECT es.*
2528	FROM Attends a
2529	INNER JOIN Teaches b ON b.course_code = a.course_code
2530	INNER JOIN Employees es ON b.teacher_email = es.email
2531	WHERE es.is_teacher = '1'
2532	AND EXISTS
2533	(
2534		SELECT *
2535		FROM Enrolled_Students ess
2536		WHERE ess.parent_email = @parent_email
2537		AND ess.ssn = a.enrolled_ssn
2538	)
2539GO
2540
2541CREATE PROC SchoolsOfChildren
2542@parent_email VARCHAR(50)
2543AS
2544	SELECT s.*
2545	FROM Schools s
2546	INNER JOIN Enrolled_Students es ON es.school_email = s.email
2547	AND EXISTS(
2548		SELECT *
2549		FROM Enrolled_Students ess
2550		WHERE ess.parent_email = @parent_email
2551		AND ess.ssn = es.ssn
2552	)
2553GO
2554
2555--EXEC teachersOfChildren 'maged.awad@MSA.edu.eg'
2556
2557CREATE PROC aa
2558@parent_email_in VARCHAR(50)
2559AS
2560	SELECT *
2561	FROM Applicants_Applies_Schools a
2562	WHERE a.parent_email = @parent_email_in AND a.accepted = '1'
2563GO
2564
2565--EXEC aa 'maged.awad@MSA.edu.eg'
2566
2567
2568CREATE PROC ab
2569@ssn_in INT
2570AS
2571	SELECT name
2572	FROM Enrolled_Students
2573	WHERE ssn = @ssn_in
2574
2575	UNION
2576
2577	SELECT name
2578	FROM Applicants
2579	WHERE ssn = @ssn_in
2580GO
2581
2582
2583CREATE PROC ViewParentReviews
2584@parent_email_in VARCHAR(50)
2585AS
2586	SELECT *
2587	FROM Parents_Write_Review_Schools
2588	WHERE parent_email = @parent_email_in
2589GO
2590
2591
2592
2593/*	parent_email VARCHAR(50),
2594	school_email VARCHAR(50),
2595	reviews VARCHAR(200),*/
2596
2597CREATE PROC deleteReview
2598@parent_email_in VARCHAR(50),
2599@school_email_in VARCHAR(50)
2600AS
2601	DELETE FROM Parents_Write_Review_Schools
2602	WHERE parent_email = @parent_email_in AND school_email = @school_email_in
2603GO
2604
2605--select * from Enrolled_Students where 2345124 = ssn
2606
2607
2608
2609--select * from Assignments where course_code = 'CSEN601'
2610
2611
2612--SELECT * FROM Schools WHERE email IN (SELECT school_email FROM Employees WHERE email = 'bb')
2613
2614
2615
2616CREATE PROC CheckHighSchool
2617@ssn_in INT
2618AS
2619	SELECT y.is_high
2620	FROM Enrolled_Students x
2621	INNER JOIN Schools y ON x.school_email = y.email AND x.ssn = 2345124
2622GO
2623
2624SELECT *
2625FROM Enrolled_Students z
2626WHERE EXISTS
2627(
2628	SELECT y.is_high
2629	FROM Enrolled_Students x
2630	INNER JOIN Schools y ON x.school_email = y.email AND x.ssn = z.ssn
2631	WHERE y.is_high = '1'
2632)
2633
2634
2635
2636
2637--SELECT * FROM Clubs_Offered_By_Schools
2638--SELECT * FROM Enrolled_Students
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
2661
2662
2663--mo2akkatan
2664USE dec3h
2665GO
2666
2667
2668
2669--EXEC Enrolled_Student_Info_Update 12341234 , "lkjlk" , 0 , "2016-2-5" , "aa" , "Modern@edu.eg" , 'maged.awad@MSA.edu.eg'
2670
2671
2672--EXEC Enrolled_Student_Info_Update 12341234 , "aaq" , 1 , "2011-2-2" , "aa" , "Modern@edu.eg" , "maged.awad@MSA.edu.eg"
2673
2674
2675
2676--EXEC Enrolled_Student_View_Courses 
2677
2678
2679
2680
2681
2682CREATE PROC ES_View_School_Clubs
2683@ssn_in INT
2684AS
2685	SELECT c.*
2686	FROM Clubs c
2687	INNER JOIN Clubs_Offered_By_Schools co ON c.name = co.name
2688	WHERE EXISTS
2689	(
2690		SELECT *
2691		FROM Enrolled_Students ess
2692		WHERE ess.ssn = @ssn_in AND ess.school_email = co.school_email
2693	)
2694GO
2695
2696--EXEC ES_View_School_Clubs 2113524
2697
2698EXEC Enrolled_Student_Join_Club 211555 , "TImon"
2699
2700SELECT * FROM Clubs_Joined_By_Enrolled_Student
2701
2702SELECT * FROM Enrolled_Students
2703
2704/*UPDATE Enrolled_Students
2705SET student_level = 10
2706WHERE student_level = 5*/
2707
2708
2709
2710--EXEC AdminLogIn "bb" , "bb"
2711
2712EXEC AdminLogIn "teshtar.teshtar" , "teshhhhhh"
2713
2714EXEC ViewUnverifiedTeachers "george.krahy@ST.Fatima.edu.eg"
2715
2716SELECT * FROM Employees
2717GO
2718
2719CREATE PROC AdminVerifiesTeacher
2720@teacher_mail_in VARCHAR(50)
2721AS
2722	UPDATE Employees
2723	SET is_verified = '1'
2724	WHERE email = @teacher_mail_in
2725GO
2726
2727EXEC AdminVerifiesTeacher 'bb'
2728
2729
2730EXEC Enrolled_Student_View_Questions 'CSEN601', 2355124