db
unknown
sqlserver
2 years ago
37 kB
6
Indexable
/*
إعداد الطلبة:
أنور محمد أبوبكر زيدان 192061
يوسف عبد المحسن محمد 192303
أيمن منير أبو الشواشي 192065
محمد مصطفى غنية192239
حاتم ناصر حبارات 192085
تحت إشراف: د. أمال سعد
*/
-- please close all query related to the database(E-learning) befor run to drop if exists succesfully
--refresh server after run script to see db (E-learning)
---------
/*سكربت لانشاء قاعدة البيانات خالي من الاخطاء*/
---drop database and snapshot if already name exists
USE master
go
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'E-learning_snapshot')
begin
drop DATABASE [E-learning_snapshot]
end
GO
use master
go
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'E-learning')
begin
drop DATABASE [E-learning]
end
GO
----
CREATE DATABASE [E-learning]
go
----
use [E-learning]
go
-----------------------------------------------------------
/*create all table(fields,datatype)*/
CREATE TABLE student (
STudentID int not null primary key,
FirstName nvarchar(25) not null,
LastName nvarchar(25) not null,
Email nvarchar(25) unique check (Email like '%__@__%.%') not null,
Gender char check (gender in ('f','F','M','m')) not null,
Nationality varchar(25) not null
);
go
--------
CREATE TABLE Techer (
TecherID int not null primary key,
FirstName nvarchar(25) not null,
LastName nvarchar(25) not null,
Email nvarchar(25) unique check (Email like '%__@__%.%') not null,
Gender char check (gender in ('f','F','M','m')) not null ,
Nationality varchar(25) not null
);
go
-------------
CREATE TABLE courses (
courseID int not null primary key,
name nvarchar(25) not null,
techerID int not null ,
price numeric(7,2) not null check (price>0),
categoryID int ,
createdDate smalldatetime default getdate() not null,
lastUpdate smalldatetime default getdate() not null,
course_status varchar(20) not null default 'NotActive' check (course_status in ('Active','NotActive'))
);
go
-------------
CREATE TABLE CourseCategory (
categoryID int not null primary key,
categoryName nvarchar(25) not null
);
go
------------------
CREATE TABLE Lessons (
LessonID int not null primary key ,
CourseID int not null,
Titel nvarchar(25),
link nvarchar(250) not null ,
uploadDate smalldatetime default getdate() not null,
);
go
--------------------
CREATE TABLE Exam_forms (
FormID int not null primary key,
CourseID int not null,
titel nvarchar(25),
link nvarchar(250) not null ,
uploadDate smalldatetime default getdate() not null,
);
go
------------------
CREATE TABLE Study (
StudentID int not null,
CourseID int not null,
RegistrationDate smalldatetime default getdate() not null,
constraint PK_compsite_study primary key(studentID,courseID)
);
go
-----------------------
CREATE TABLE Student_Exams (
ExamID int not null primary key,
StudentID int,
CourseID int,
FormID int,
grade decimal check (grade between 0 and 100) not null,
ExamDate smalldatetime default getdate() not null,
);
go
----------------------------
CREATE TABLE Certificate (
StudentID int not null,
CourseID int not null,
ExamID int not null,
ReleaseDate smalldatetime default getdate() not null ,
constraint PK_compsite_Certificate primary key(studentID,courseID)
);
go
-----------------------
--------------------------------------------------------------------
/*Create relationships between tables*/
alter table courses
add
constraint FK_category foreign key (categoryID) references CourseCategory(CategoryID) on delete set null on update cascade ,
constraint FK_techerID foreign key (TecherID) references Techer(TecherID) on delete cascade on update cascade
go
--------
alter table lessons
add
constraint FK_courseID foreign key (courseID) references Courses(courseID) on delete cascade on update cascade
go
--------
alter table study
add
constraint FK_courseID_study foreign key (courseID) references Courses(courseID) on delete cascade on update cascade ,
constraint FK_Student_study foreign key (StudentID) references Student(StudentID) on delete cascade on update cascade
go
--------
alter table student_Exams
add
constraint FK_courseID_Exam foreign key (courseID) references Courses(courseID) on delete set null on update cascade ,
constraint FK_Student_exam foreign key (StudentID) references Student(StudentID) on delete set null on update cascade ,
constraint FK_form_exam foreign key (FormID) references Exam_forms(FormID) on update cascade on delete set null
go
-----------------------
create trigger cascade_update_course
on courses for update
as
if( (select COUNT(*) from inserted )= 1)
begin
update Exam_forms set CourseID=(select CourseID from inserted )
where CourseID = (select CourseID from deleted);
update Certificate set CourseID=(select CourseID from inserted )
where CourseID = (select CourseID from deleted);
end
go
----------------
create trigger cascade_delete_course
on courses for delete
as
delete Exam_forms
where CourseID in (select CourseID from deleted);
delete Certificate
where CourseID in (select CourseID from deleted);
go
------------------------
create trigger delete_all_Certificate_after_delete_student
on student for delete
as
delete Certificate
where StudentID in (select STudentID from deleted);
go
-------------------------------
------------------------------------------------------
/*This trigger is used to give the certificate to the student after doing exam
If the student's grade in the exam is >= 50*/
create trigger add_certificate_after_pass_exam
on student_exams for insert
as
declare @studentID int
declare @courseID int
declare @examID int
declare @grade decimal
select @studentID = (select StudentID from inserted)
select @grade = (select grade from inserted)
select @examID = (select ExamID from inserted)
select @courseID = (select CourseID from inserted)
if (@grade >= 50)
begin
insert into certificate values(@studentID,@courseID,@examID,GETDATE())
end
go
------------
------------------------------------------
/*this trigger to check course is available(status='Active') before the student registers*/
create trigger check_course_study_befor_study
on study for insert
as
declare @courseID int
select @courseID = (select CourseID from inserted)
if((select courses.course_status from courses where courseID =@courseID)= 'NotActive')
begin
print 'you cant add student to this course becuase the course status is not active'
ROLLBACK TRANSACTION
end
go
------------------
/*this triggers below is use to auto update (last_update_date) field in course table
if we insert new lesson or any exam form */
create trigger update_last_update_date_in_course_after_Add_new_Lesson
on lessons after insert
as
declare @courseID int
select @courseID = (select CourseID from inserted)
update courses set lastUpdate=GETDATE() where courseID=@courseID
go
create trigger update_last_update_date_in_course_after_Add_new_exam_form
on lessons after insert
as
declare @courseID int
select @courseID = (select CourseID from inserted)
update courses set lastUpdate=GETDATE() where courseID=@courseID
go
------------------------------------------
-------------------------------------------------------
/*
سنقوم بانشاء فانكشن تقوم باسترجاع نوع الجنس بصيغة كاملة
(veiws) لاستخدامها في كل التقارير
بدلا من اعادة كتابة الكود لكل تقرير على حدا
*/
CREATE FUNCTION GetGender
(
@GenderChar CHAR(1)
)
RETURNS VARCHAR(6)
AS
BEGIN
RETURN CASE UPPER(@GenderChar)
WHEN 'M' THEN 'MALE'
WHEN 'F' THEN 'FEMALE'
ELSE NULL
END
END
go
-------------------------------------------------------
-------------------------------------------------------
/*
--يقوم الفيو التالي بعرض تقرير كامل عن الكورسات الموجودة في قاعدة البيانات حيث يقوم بعرض
--(رقم الكورس و اسمه واسم المعلم الخاص بهذا الكورس وعدد الطلبة المسجلين في الكورس وعدد الدروس الموجودة به وتاريخ انشاء الكورس )
*/
create view Courses_report as
select cr.courseID ,cr.[Course Name],cr.[Techer Name],cr.price, coalesce(st.[number of student],0) as 'number of student',coalesce(ls.[number of lessons],0) as 'number of lessons',cr.[Created Date] ,cr.[Last Update]
from
(
(
(select c.courseID, c.name as 'Course Name' , t.FirstName+' '+ t.LastName as 'Techer Name' ,
c.createdDate as 'Created Date' ,c.lastUpdate as 'Last Update' , c.price
from techer t join courses c on c.techerID = t.TecherID
)
cr
left join (
select l.CourseID,count(l.LessonID) as 'number of lessons' from Lessons l
--right join courses c on c.courseID=l.LessonID
group by l.courseID
) ls on ls.courseID =cr.courseID
)
left join (
select study.CourseID , count(Study.StudentID) as 'number of student' from Study
group by study.CourseID
) st on cr.courseID =st.CourseID
)
go
----------------------------------------------------------------------------------
/*
يقوم الفيو التالي بعرض كل الطلبه داخل قاعدة البيانات مع تفاصيل اكثر
(رقم الطالب و اسمه وجنسه و جنسيته وبريده الالكتروني وعدد الكورسات التي سجل بها وعدد الشهادات التي تحصل عليها)
*/
create view Students_Reports
as
select st.STudentID,st.[Full Name],dbo.GetGender(st.Gender) as 'gender',st.Nationality,st.Email,coalesce(stu.[number of courses],0) as 'number of courses',coalesce(cr.[number of Certificates],0 ) as 'number of Certificates'
from
(
(
select student.STudentID, (student.FirstName+ ' ' +student.LastName )as 'Full Name'
, student.Gender , student.Nationality , student.Email
from student) st
left join
(select Certificate.StudentID , count(Certificate.CourseID) as 'number of Certificates'from Certificate
group by Certificate.StudentID) cr on st.STudentID=cr.STudentID
) left join
(select study.StudentID,COUNT(Study.CourseID) as 'number of courses'
from study
group by study.StudentID) stu
on st.STudentID = stu.StudentID
go
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
/*الان سنقوم بانشاء اللوقين والمستخدمين مع اعطاء الصلاحيات المطلوبه*/
use [E-learning];
go
IF NOT EXISTS
(SELECT name
FROM master.sys.server_principals
WHERE name = 'Anwar_login')
BEGIN
create LOGIN Anwar_login WITH PASSWORD='Anwar@@$$$%%192061',
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON,
DEFAULT_DATABASE=[E-learning];
END
go
if not exists(select * from sys.database_principals where name = 'Anwar')
begin
create user Anwar for login[Anwar_login]
EXEC sp_addrolemember'db_owner',Anwar;
end
go
IF NOT EXISTS
(SELECT name
FROM master.sys.server_principals
WHERE name = 'Hatem_login')
BEGIN
create LOGIN Hatem_login WITH PASSWORD='1922085_Hatem',
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON,
DEFAULT_DATABASE=[E-learning];
end
go
if not exists(select * from sys.database_principals where name = 'Hatem')
begin
create user Hatem for login[Hatem_login]
EXEC sp_addrolemember'db_datawriter',Hatem;
end
go
IF NOT EXISTS
(SELECT name
FROM master.sys.server_principals
WHERE name = 'Mohammed_login')
BEGIN
create LOGIN Mohammed_login WITH PASSWORD='Moh_0522_192239',
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON,
DEFAULT_DATABASE=[E-learning];
end
go
if not exists(select * from sys.database_principals where name = 'Mohammed')
begin
create user Mohammed for login[Mohammed_login]
EXEC sp_addrolemember'db_datareader',Mohammed;
end
go
IF NOT EXISTS
(SELECT name
FROM master.sys.server_principals
WHERE name = 'Youssef_login')
BEGIN
create LOGIN Youssef_login WITH PASSWORD='Y22ssss226',
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON,
DEFAULT_DATABASE=[E-learning];
end
go
if not exists(select * from sys.database_principals where name = 'Youssef')
begin
create user Youssef for login[Youssef_login]
EXEC sp_addrolemember'db_datawriter',Youssef;
end
go
IF NOT EXISTS
(SELECT name
FROM master.sys.server_principals
WHERE name = 'Aymen_login')
BEGIN
create LOGIN Aymen_login WITH PASSWORD='AY355552MEN',
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON,
DEFAULT_DATABASE=[E-learning];
end
go
if not exists(select * from sys.database_principals where name = 'Aymen')
begin
create user Aymen for login[Aymen_login]
EXEC sp_addrolemember'db_datareader',Aymen;
end
go
---------------------تجهيز النسخ الاحتياطي
-------------------------------------------------------------------------------------------------------
/*الان سنقوم بانشاء مجلد داخل القرص دي باستخدام ستورد بروسيجر*/
-- this turns on advanced options
-------------------------------------------------------------------------------------------------------
--اعطاء صلاحيات للسيرفر بتنفيذ اوامر شيل داخل نظام الويندوز
use master
go
EXEC sp_configure 'show advanced options', '1'
RECONFIGURE
go
-- this enables xp_cmdshell
EXEC sp_configure 'xp_cmdshell', '1'
RECONFIGURE
go
------------------------------
-- انشاء مجلد جديد للمسار التالي في حال لم يكن موجود
use master
go
EXEC xp_cmdshell 'MD D:\backupDB_Anwar'
-- الغرض من المهام السابقة تفادي خطا مسار الملف خاطئ عند انشاء جهاز باكاب داخل السيرفر
--الان نقوم بانشاء جهاز جديد لاستخدامه عند الباك اب والريستور
exec sp_addumpdevice'disk','backupDeviceAnwar','D:\backupDB_Anwar\back1.bak';
GO
---------
-- الان اجراء نسخة احتياطية من قاعدة البيانات على الجهاز الذي قمنا بانشائه
use master
go
BACKUP DATABASE [E-learning]
TO backupDeviceAnwar
WITH
NAME = N'E-learning DB - Full Database Backup'
GO
---------------------------
--create snapshot after insert data below
------------------------------
-------------------------------------------------------------------------------------------------
/*اضافة بيانات منطقية لكل جداول قاعدة البيانات*/
use [E-learning]
go
--------------------------(id,fristname,lastname,email,gender,international)
insert into student values(1,'Anwar','Zidan','Anwar1@gmail.com','M','zembabwe')
insert into student values(2,'jomaa','Badr','jomaaBadr2@gmail.com','M','syria')
insert into student values(3,'shahd_','Ghnia','shahd_Ghnia3@gmail.com','F','france')
insert into student values(4,'shadi','Yassin','shadiYassin4@gmail.com','M','turky')
insert into student values(5,'ali','Anwar','aliAnwar5@gmail.com','M','ghana')
insert into student values(6,'karam','Anas','karamAnas6@gmail.com','M','palestine')
insert into student values(7,'asma_','Ahmed','asma_Ahmed7@gmail.com','F','england')
insert into student values(8,'nour','Ahmed','nourAhmed8@gmail.com','M','australia')
insert into student values(9,'yosra_','Anwar','yosra_Anwar9@gmail.com','F','france')
insert into student values(10,'takwa_','Ahmed','takwa_Ahmed10@gmail.com','F','saudiArabia')
insert into student values(11,'mohammed','Aymen','mohammedAymen11@gmail.com','M','nigeria')
insert into student values(12,'masoud','Younes','masoudYounes12@gmail.com','M','peru')
insert into student values(13,'nezar','Qais','nezarQais13@gmail.com','M','arjentina')
insert into student values(15,'nezar','Badr','nezarBadr15@gmail.com','M','ghana')
insert into student values(17,'intesar_','Anwar','intesar_Anwar17@gmail.com','F','syria')
insert into student values(18,'youssef','Younes','youssefYounes18@gmail.com','M','egypt')
insert into student values(19,'amira_','Ghnia','amira_Ghnia19@gmail.com','F','moroco')
insert into student values(20,'asma_','Anas','asma_Anas20@gmail.com','F','emirates')
insert into student values(21,'jomaa','Ghnia','jomaaGhnia21@gmail.com','M','palestine')
insert into student values(22,'asma_','Badr','asma_Badr22@gmail.com','F','arjentina')
insert into student values(23,'soroor_','Aymen','soroor_Aymen23@gmail.com','F','brazil')
insert into student values(24,'amina_','Badr','amina_Badr24@gmail.com','F','libya')
insert into student values(25,'ahmed','Anas','ahmedAnas25@gmail.com','M','japan')
insert into student values(26,'intesar_','Basem','intesar_Basem26@gmail.com','F','france')
insert into student values(27,'sari','Ibrahim','sariIbrahim27@gmail.com','M','qatar')
insert into student values(28,'rabea_','Qais','rabea_Qais28@gmail.com','F','algeria')
insert into student values(30,'yasmin_','Ghnia','yasmin_Ghnia30@gmail.com','F','moritania')
insert into student values(31,'asma_','Ahmed','asma_Ahmed31@gmail.com','F','china')
insert into student values(32,'intesar_','Qais','intesar_Qais32@gmail.com','F','saudiArabia')
insert into student values(33,'hania_','Basem','hania_Basem33@gmail.com','F','moroco')
insert into student values(34,'ahmed','Ahmed','ahmedAhmed34@gmail.com','M','zembabwe')
insert into student values(35,'saja_','Saad','saja_Saad35@gmail.com','F','turky')
insert into student values(36,'anwar','Masoud','anwarMasoud36@gmail.com','M','nigeria')
insert into student values(37,'mansour','Basem','mansourBasem37@gmail.com','M','saudiArabia')
insert into student values(38,'ibrahim','Anwar','ibrahimAnwar38@gmail.com','M','ecuador')
insert into student values(40,'aymen','Qosi','aymenQosi40@gmail.com','M','moroco')
insert into student values(41,'abdullah','Aymen','abdullahAymen41@gmail.com','M','tunis')
insert into student values(42,'abrar_','Nezar','abrar_Nezar42@gmail.com','F','brazil')
insert into student values(43,'ghnia','Ali','ghniaAli43@gmail.com','M','england')
insert into student values(44,'anas','Mohammed','anasMohammed44@gmail.com','M','nigeria')
insert into student values(45,'karam','Masoud','karamMasoud45@gmail.com','M','ecuador')
insert into student values(46,'qosi','Anas','qosiAnas46@gmail.com','M','qatar')
insert into student values(47,'abdullah','Anwar','abdullahAnwar47@gmail.com','M','china')
insert into student values(48,'ibrahim','Aymen','ibrahimAymen48@gmail.com','M','china')
insert into student values(49,'mansour','Anas','mansourAnas49@gmail.com','M','moroco')
insert into student values(50,'karema_','Badr','karema_Badr50@gmail.com','F','australia')
insert into student values(51,'basem','Anas','basemAnas51@gmail.com','M','syria')
insert into student values(52,'sari','Younes','sariYounes52@gmail.com','M','jordan')
insert into student values(53,'ali','Yassin','aliYassin53@gmail.com','M','libya')
insert into student values(54,'ghnia','Nezar','ghniaNezar54@gmail.com','M','moritania')
insert into student values(55,'nour','Anas','nourAnas55@gmail.com','M','tunis')
insert into student values(56,'shahd_','Ali','shahd_Ali56@gmail.com','F','nigeria')
insert into student values(57,'sari','Ghnia','sariGhnia57@gmail.com','M','ghana')
insert into student values(58,'ahmed','Yassin','ahmedYassin58@gmail.com','M','germany')
insert into student values(59,'mohammed','Nezar','mohammedNezar59@gmail.com','M','nigeria')
insert into student values(60,'hani','Yassin','haniYassin60@gmail.com','M','turky')
insert into student values(61,'soroor_','Qais','soroor_Qais61@gmail.com','F','ecuador')
insert into student values(62,'anwar','Ahmed','anwarAhmed62@gmail.com','M','germany')
insert into student values(63,'karam','Ali','karamAli63@gmail.com','M','zembabwe')
insert into student values(64,'qosi','Saad','qosiSaad64@gmail.com','M','tunis')
insert into student values(66,'karam','Basem','karamBasem66@gmail.com','M','china')
insert into student values(67,'nour','Qais','nourQais67@gmail.com','M','syria')
insert into student values(68,'asma_','Mohammed','asma_Mohammed68@gmail.com','F','australia')
insert into student values(69,'abrar_','Jomaa','abrar_Jomaa69@gmail.com','F','qatar')
insert into student values(70,'yasmin_','Badr','yasmin_Badr70@gmail.com','F','saudiArabia')
insert into student values(71,'mohammed','Badr','mohammedBadr71@gmail.com','M','palestine')
insert into student values(72,'nour','Masoud','nourMasoud72@gmail.com','M','china')
insert into student values(73,'saja_','Ahmed','saja_Ahmed73@gmail.com','F','brazil')
insert into student values(74,'mansour','Ghnia','mansourGhnia74@gmail.com','M','peru')
insert into student values(75,'saad','Mohammed','saadMohammed75@gmail.com','M','germany')
insert into student values(76,'nour','Anas','nourAnas76@gmail.com','M','saudiArabia')
insert into student values(77,'asma_','Badr','asma_Badr77@gmail.com','F','kuwait')
insert into student values(78,'anwar','Yassin','anwarYassin78@gmail.com','M','kuwait')
insert into student values(79,'qosi','Aymen','qosiAymen79@gmail.com','M','nigeria')
insert into student values(80,'botoul_','Jomaa','botoul_Jomaa80@gmail.com','F','ecuador')
insert into student values(81,'karem','Qosi','karemQosi81@gmail.com','M','egypt')
insert into student values(82,'qais','Basem','qaisBasem82@gmail.com','M','libya')
insert into student values(83,'karam','Nezar','karamNezar83@gmail.com','M','ecuador')
insert into student values(84,'mohammed','Aymen','mohammedAymen84@gmail.com','M','brazil')
insert into student values(85,'saad','Qosi','saadQosi85@gmail.com','M','libya')
insert into student values(86,'soroor_','Badr','soroor_Badr86@gmail.com','F','moroco')
insert into student values(87,'shadi','Basem','shadiBasem87@gmail.com','M','japan')
insert into student values(88,'saad','Masoud','saadMasoud88@gmail.com','M','palestine')
insert into student values(90,'qosi','Mohammed','qosiMohammed90@gmail.com','M','arjentina')
insert into student values(91,'karem','Qosi','karemQosi91@gmail.com','M','ecuador')
insert into student values(92,'abdalrahman','Ghnia','abdalrahman92@gmail.com','M','kuwait')
insert into student values(93,'soroor_','Anas','soroor_Anas93@gmail.com','F','tunis')
insert into student values(94,'mansour','Ahmed','mansourAhmed94@gmail.com','M','turky')
insert into student values(95,'jomaa','Nezar','jomaaNezar95@gmail.com','M','australia')
insert into student values(96,'youssef','Saad','youssefSaad96@gmail.com','M','ecuador')
insert into student values(97,'huda_','Anas','huda_Anas97@gmail.com','F','moritania')
insert into student values(98,'fotoun_','Ali','fotoun_Ali98@gmail.com','F','china')
insert into student values(99,'karema_','Basem','karema_Basem99@gmail.com','F','germany')
go
-------------------------(id,fristname,lastname,email,gender,international)
insert into Techer values(1,'yosra_','Qosi','yosra_Qosi101@gmail.com','F','zembabwe')
insert into Techer values(2,'rabea_','Younes','rabea_Younes102@gmail.com','F','syria')
insert into Techer values(3,'qais','Badr','qaisBadr103@gmail.com','M','moritania')
insert into Techer values(4,'yosra_','Masoud','yosra_Masoud104@gmail.com','F','emirates')
insert into Techer values(5,'yosra_','Badr','yosra_Badr105@gmail.com','F','emirates')
insert into Techer values(8,'saja_','Yassin','saja_Yassin108@gmail.com','F','algeria')
insert into Techer values(9,'badr','Jomaa','badrJomaa109@gmail.com','M','ghana')
insert into Techer values(13,'ali','Mohammed','aliMohammed113@gmail.com','M','moroco')
insert into Techer values(14,'botoul_','Anwar','botoul_Anwar114@gmail.com','F','nigeria')
insert into Techer values(15,'yassin','Younes','yassinYounes115@gmail.com','M','moroco')
insert into Techer values(17,'hania_','Qosi','hania_Qosi117@gmail.com','F','ecuador')
insert into Techer values(18,'anas','Nezar','anasNezar118@gmail.com','M','jordan')
insert into Techer values(19,'saja_','Anas','saja_Anas119@gmail.com','F','tunis')
insert into Techer values(20,'youssef','Jomaa','youssefJomaa120@gmail.com','M','kuwait')
insert into Techer values(21,'yassin','Ghnia','yassinGhnia121@gmail.com','M','nigeria')
insert into Techer values(22,'mansour','Ghnia','mansourGhnia122@gmail.com','M','tunis')
insert into Techer values(23,'qosi','Qosi','qosiQosi123@gmail.com','M','turky')
insert into Techer values(25,'mohammed','Anas','mohammedAnas125@gmail.com','M','ghana')
insert into Techer values(26,'botoul_','Badr','botoul_Badr126@gmail.com','F','turky')
insert into Techer values(27,'youssef','Badr','youssefBadr127@gmail.com','M','arjentina')
insert into Techer values(28,'marwa_','Masoud','marwa_Masoud128@gmail.com','F','egypt')
insert into Techer values(29,'nezar','Ahmed','nezarAhmed129@gmail.com','M','tunis')
insert into Techer values(30,'yasmin_','Ahmed','yasmin_Ahmed130@gmail.com','F','libya')
insert into Techer values(33,'nour','Anas','nourAnas133@gmail.com','M','france')
insert into Techer values(34,'ali','Qosi','aliQosi134@gmail.com','M','zembabwe')
insert into Techer values(35,'jomaa','Nezar','jomaaNezar135@gmail.com','M','australia')
insert into Techer values(36,'sondos_','Anas','sondos_Anas136@gmail.com','F','jordan')
insert into Techer values(37,'younes','Masoud','younesMasoud137@gmail.com','M','palestine')
insert into Techer values(38,'shahd_','Ghnia','shahd_Ghnia138@gmail.com','F','ecuador')
insert into Techer values(40,'masoud','Jomaa','masoudJomaa140@gmail.com','M','france')
insert into Techer values(41,'shadi','Badr','shadiBadr141@gmail.com','M','turky')
insert into Techer values(44,'ghnia','Basem','ghniaBasem144@gmail.com','M','palestine')
insert into Techer values(47,'nezar','Ali','nezarAli147@gmail.com','M','brazil')
insert into Techer values(48,'saja_','Masoud','saja_Masoud148@gmail.com','F','moroco')
insert into Techer values(49,'ekhlas_','Ali','ekhlas_Ali149@gmail.com','F','syria')
insert into Techer values(50,'nour','Ghnia','nourGhnia150@gmail.com','M','turky')
insert into Techer values(51,'amina_','Nezar','amina_Nezar151@gmail.com','F','ghana')
insert into Techer values(52,'amira_','Jomaa','amira_Jomaa152@gmail.com','F','algeria')
insert into Techer values(53,'anas','Saad','anasSaad153@gmail.com','M','syria')
insert into Techer values(54,'hani','Ahmed','haniAhmed154@gmail.com','M','emirates')
insert into Techer values(55,'hani','Masoud','haniMasoud155@gmail.com','M','arjentina')
insert into Techer values(56,'karem','Qais','karemQais156@gmail.com','M','qatar')
insert into Techer values(58,'ghnia','Saad','ghniaSaad158@gmail.com','M','tunis')
insert into Techer values(59,'rabea_','Qais','rabea_Qais159@gmail.com','F','qatar')
insert into Techer values(60,'ali','Younes','aliYounes160@gmail.com','M','japan')
insert into Techer values(61,'fotoun_','Anas','fotoun_Anas161@gmail.com','F','jordan')
insert into Techer values(62,'nour','Qosi','nourQosi162@gmail.com','M','moritania')
insert into Techer values(63,'sari','Masoud','sariMasoud163@gmail.com','M','china')
insert into Techer values(64,'intesar_','Ali','intesar_Ali164@gmail.com','F','saudiArabia')
insert into Techer values(66,'amira_','Basem','amira_Basem166@gmail.com','F','jordan')
insert into Techer values(67,'aymen','Ghnia','aymenGhnia167@gmail.com','M','brazil')
insert into Techer values(69,'ghnia','Ali','ghniaAli169@gmail.com','M','nigeria')
go
---------------------------------(id,category name)
insert into CourseCategory values(1,'design')
insert into CourseCategory values(2,'photoshop')
insert into CourseCategory values(3,'AI')
insert into CourseCategory values(4,'programming')
insert into CourseCategory values(5,'Health and fitness')
insert into CourseCategory values(6,'marketing')
insert into CourseCategory values(7,'education and teaching')
go
--------------------------(id,course name ,techet id , price , category id , create date , last update date , course status)
insert into courses values(1,'Yoga',9,79.50,5,getDATE(),getDATE(),'NotActive')
insert into courses values(2,'digital marketing',60,36.99,4,getDATE(),getDATE(),'NotActive')
insert into courses values(4,'webDevelopment',20,43,1,getDATE(),getDATE(),'NotActive')
insert into courses values(5,'webDevelopment',35,72,1,getDATE(),getDATE(),'NotActive')
insert into courses values(6,'dataStructure',20,72,1,getDATE(),getDATE(),'Active')
insert into courses values(9,'Yoga',9,81,5,getDATE(),getDATE(),'Active')
insert into courses values(12,'social media marketing',61,40,4,getDATE(),getDATE(),'Active')
insert into courses values(13,'graphic design',15,60,6,getDATE(),getDATE(),'NotActive')
insert into courses values(14,'DatabaseManagement',38,57,1,getDATE(),getDATE(),'NotActive')
insert into courses values(15,'userUI design',20,87,6,getDATE(),getDATE(),'Active')
insert into courses values(16,'digital marketing',1,48,4,getDATE(),getDATE(),'NotActive')
insert into courses values(17,'football skills',14,59,5,getDATE(),getDATE(),'NotActive')
insert into courses values(18,'webDevelopment',4,94,1,getDATE(),getDATE(),'Active')
insert into courses values(19,'game design',2,96,6,getDATE(),getDATE(),'NotActive')
insert into courses values(20,'content marketing',64,73,4,getDATE(),getDATE(),'NotActive')
insert into courses values(21,'dataStructure',64,54,1,getDATE(),getDATE(),'Active')
insert into courses values(26,'DatabaseManagement',20,28,1,getDATE(),getDATE(),'NotActive')
insert into courses values(27,'football skills',9,44,5,getDATE(),getDATE(),'Active')
insert into courses values(28,'content marketing',3,9,4,getDATE(),getDATE(),'NotActive')
insert into courses values(29,'game design',34,71,6,getDATE(),getDATE(),'NotActive')
insert into courses values(31,'social media marketing',58,55,4,getDATE(),getDATE(),'NotActive')
insert into courses values(32,'content marketing',47,71,4,getDATE(),getDATE(),'NotActive')
insert into courses values(33,'social media marketing',61,76,4,getDATE(),getDATE(),'Active')
insert into courses values(34,'DatabaseManagement',5,51,1,getDATE(),getDATE(),'NotActive')
insert into courses values(36,'game design',14,74,6,getDATE(),getDATE(),'Active')
insert into courses values(37,'social media marketing',51,57,4,getDATE(),getDATE(),'NotActive')
insert into courses values(38,'webDevelopment',54,91,1,getDATE(),getDATE(),'NotActive')
insert into courses values(39,'DatabaseManagement',47,38,1,getDATE(),getDATE(),'Active')
insert into courses values(40,'dataStructure',14,95,1,getDATE(),getDATE(),'NotActive')
go
---------------------------(lesson id ,course id, titel,lesson link,create date)
insert into Lessons values(1,18,'introduction','dpqowpp.com',GETDATE())
insert into Lessons values(2,18,'HTML BASICs','gdgow31.com',GETDATE())
insert into Lessons values(3,14,'DATABASEBASIC','zzdow9q.com',GETDATE())
insert into Lessons values(4,12,'introduction','ahddfjhdg.com',GETDATE())
insert into Lessons values(5,1,'introduction','ogjow9fg.com',GETDATE())
insert into Lessons values(6,18,'HTML ADVANCED','jskdadh.com',GETDATE())
insert into Lessons values(7,18,'CSS BASICS','bcqo3.com',GETDATE())
insert into Lessons values(8,34,'DATABASE ADVANCED','lksajhd.com',GETDATE())
insert into Lessons values(9,34,'introduction','kshd.com',GETDATE())
insert into Lessons values(10,18,'CSS ADVANCED','ashkl.com',GETDATE())
insert into Lessons values(11,12,'introduction','owhwsa.com',GETDATE())
insert into Lessons values(12,9,'introduction','ivoe.com',GETDATE())
insert into Lessons values(13,13,'introduction','sioadhfohsd.com',GETDATE())
insert into Lessons values(14,13,'introduction','sjkdkasd.com',GETDATE())
insert into Lessons values(15,14,'introduction','kasuhd.com',GETDATE())
insert into Lessons values(16,1,'introduction','slfijddif.com',GETDATE())
insert into Lessons values(17,19,'unity5','qopwdjq.com',GETDATE())
insert into Lessons values(18,21,'STACK','shjas.com',GETDATE())
insert into Lessons values(19,6,'STACK','skjds.com',GETDATE())
insert into Lessons values(20,19,'introduction','meoie.com',GETDATE())
insert into Lessons values(21,27,'introduction','alidq.com',GETDATE())
insert into Lessons values(22,31,'media concepts','qowp21.com',GETDATE())
insert into Lessons values(23,32,'introduction','shndo83.com',GETDATE())
insert into Lessons values(24,32,'Marketing','sj1ww.com',GETDATE())
insert into Lessons values(25,29,'introduction','do93jd.com',GETDATE())
insert into Lessons values(26,28,'introduction','zzu28.com',GETDATE())
insert into Lessons values(27,29,'unity','oqih1.com',GETDATE())
insert into Lessons values(28,2,'introduction','nvoeqw82.com',GETDATE())
insert into Lessons values(29,21,'QUEUE','bvwu1.com',GETDATE())
go
---------------------------(exam id ,course id, titel,lesson link,create date)
insert into Exam_forms values(1,18,'html exam','qwdokuiwdh.io',GETDATE())
insert into Exam_forms values(2,12,'social basics exam','ikquw.io',GETDATE())
insert into Exam_forms values(3,21,'STACK exam','d1qwdokuiwdh.io',GETDATE())
insert into Exam_forms values(4,6,'QUEUE basics exam','ffikquw39jds.io',GETDATE())
insert into Exam_forms values(5,27,'CarrerMode exam','kljdald2.io',GETDATE())
insert into Exam_forms values(6,33,'social basics exam','woqpdk1.io',GETDATE())
insert into Exam_forms values(7,36,'UNITY exam','odbbwqpjd.io',GETDATE())
insert into Exam_forms values(8,39,'FireBase exam','jkdfakd.io',GETDATE())
go
-----------------------(student id , courseid , تاريخ التسجيل في الكورس)
insert into study values(4,18,GETDATE())
insert into study values(11,9,GETDATE())
insert into study values(13,33,GETDATE())
insert into study values(19,18,GETDATE())
insert into study values(7,15,GETDATE())
insert into study values(4,21,GETDATE())
insert into study values(5,33,GETDATE())
insert into study values(1,27,GETDATE())
insert into study values(2,39,GETDATE())
insert into study values(10,9,GETDATE())
insert into study values(7,36,GETDATE())
insert into study values(2,18,GETDATE())
insert into study values(7,6,GETDATE())
insert into study values(8,9,GETDATE())
insert into study values(9,33,GETDATE())
insert into study values(95,18,GETDATE())
insert into study values(94,15,GETDATE())
insert into study values(95,21,GETDATE())
insert into study values(40,27,GETDATE())
insert into study values(38,27,GETDATE())
insert into study values(92,39,GETDATE())
insert into study values(93,9,GETDATE())
insert into study values(27,18,GETDATE())
insert into study values(84,18,GETDATE())
insert into study values(85,18,GETDATE())
insert into study values(70,6,GETDATE())
insert into study values(84,9,GETDATE())
insert into study values(70,33,GETDATE())
insert into study values(76,18,GETDATE())
insert into study values(75,15,GETDATE())
insert into study values(60,21,GETDATE())
insert into study values(60,33,GETDATE())
insert into study values(84,27,GETDATE())
insert into study values(70,39,GETDATE())
insert into study values(10,39,GETDATE())
insert into study values(68,36,GETDATE())
insert into study values(67,18,GETDATE())
insert into study values(41,27,GETDATE())
insert into study values(42,27,GETDATE())
insert into study values(43,27,GETDATE())
insert into study values(44,27,GETDATE())
insert into study values(45,27,GETDATE())
insert into study values(40,6,GETDATE())
insert into study values(52,6,GETDATE())
insert into study values(51,6,GETDATE())
insert into study values(1,6,GETDATE())
go
--------------------------------(ExamID,StudentID,CourseID,FormID,grade,ExamDate)
----FORM4---
insert into Student_Exams values(1,1,6,4,60,GETDATE())
insert into Student_Exams values(2,4,6,4,90.05,GETDATE())
insert into Student_Exams values(3,52,6,4,66.99901,GETDATE())
insert into Student_Exams values(4,7,6,4,39, GETDATE())
insert into Student_Exams values(5,70,6,4,74,GETDATE())
insert into Student_Exams values(6,40,6,4,44,GETDATE())
go
----FORM1----
insert into Student_Exams values(7,2,18,1,80, GETDATE())
insert into Student_Exams values(8,19,18,1,90,GETDATE())
insert into Student_Exams values(9,84,18,1,55,GETDATE())
insert into Student_Exams values(10,95,18,1,88,GETDATE())
insert into Student_Exams values(11,67,18,1,20,GETDATE())
go
----FORM3----
insert into Student_Exams values(12,95,21,3,60,GETDATE())
insert into Student_Exams values(13,4,21,3,72,GETDATE())
go
-----------
--Certificate auto insert if student grade in exam = 50 or up
----------------------------
----------------------------
/*create snapshot database and DIFFERENTIAL backup after complete all operation*/
use master
go
CREATE DATABASE [E-learning_snapshot] on
( NAME = [E-learning],
FILENAME = 'D:\backupDB_Anwar\snapshot.ss' )
AS SNAPSHOT OF [E-learning];
GO
use master
go
BACKUP DATABASE [E-learning]
TO backupDeviceAnwar
WITH DIFFERENTIAL,
NAME = N'E-learning DB - Diff Database Backup'
GO
use [E-learning]
go
----------------------------------------------------
---try this views
---select * from Courses_report order by courseID
---select * from Students_Reports order by STudentID
-----------------------------------------------------------------------------------------------------------------------------------------------------
/*
إعداد الطلبة:
أنور محمد أبوبكر زيدان 192061
يوسف عبد المحسن محمد 192303
أيمن منير أبو الشواشي 192065
محمد مصطفى غنية192239
حاتم ناصر حبارات 192085
تحت إشراف: د. أمال سعد
*/
-------------------------------------------------------------------------------------------------------------------------------------------------------
Editor is loading...