EzWcYL2d

1drop database mos3ad;
2create database if not exists mos3ad;
3use mos3ad;
4# create the students table
5create table students(
6    st_id int primary key auto_increment,
7    student_name varchar(100)
8);
9
10#create the courses table
11create table courses(
12    course_id int primary key auto_increment,
13    course_name varchar(100)
14);
15
16#create the many to many table
17create table st_co(
18    st_id int,
19    course_id int,
20    constraint st_id foreign key (st_id) references students (st_id),
21    constraint co_id foreign key (course_id) references courses (course_id),
22    primary key (st_id, course_id)
23);
24
25#insert some data into the table students
26INSERT INTO `students` (`student_name`) VALUES ('Ahmed'), ('Mohammed'), ('Abdallah'), ('Ashraf'), ('Tarek');
27#insert some data into the courses table
28insert into courses (course_name) values ('Arabic'), ('English'), ('French'), ('Geographyy'), ('Math');
29#insert data into the the many to many table
30INSERT INTO mos3ad.st_co (st_id, course_id) VALUES (1, 1), (2, 1), (3, 1), (1, 2), (3, 2), (1, 3), (2, 3), (3, 3);
31select * from st_co;
32
33#فيو خاصة بالطلاب
34CREATE view v_students_courses as
35select students_tbl.st_id, student_name, courses_tbl.course_id, course_name
36from students students_tbl
37         left join st_co middle on students_tbl.st_id = middle.st_id
38LEFT JOIN courses courses_tbl on middle.course_id = courses_tbl.course_id;
39
40#فيو خاصة بالكورسات
41create view v_courses_students as
42    SELECT courses_tbl.course_name, courses_tbl.course_id, students_tbl.st_id, students_tbl.student_name from courses courses_tbl left join st_co on courses_tbl.course_id = st_co.course_id left join students students_tbl on st_co.st_id = students_tbl.st_id;
43
44#testing the created views
45select * from v_students_courses;
46select * from v_courses_students;
47
48#----------------------------#
49# how to use the above views #
50#----------------------------#
51
52# عرض كل الكورسات المسجلة للطالب احمد
53select course_name from v_students_courses where student_name = 'Ahmed';
54
55#عرض كل الكورسات المسجلة للطالب صاحب الايدي رقم 2
56select course_name from v_students_courses where st_id = 2;
57
58#عرض كل الطلاب المسجلين في كورس العربي
59select student_name from v_courses_students where course_name = 'arabic';
60
61#عرض كل الطلاب المسجلين في الكورس صاحب الايدي رقم 1
62select student_name from v_courses_students where course_id = 1;