Question Sql

[SamerSal]

Salamu Alakoum
I need the sql query for this question.
17. Find the dept_code, course# and title of each course whose title contain “systems” and that has been taken or is being taken by all students whose GPA is higher than 3.25. Note that even though a qualified course is required to be taken by all students whose GPA is higher than 3.25, it may also be taken by some students whose GPA is not higher than 3.25.


and this is the tables

insert into students values (‘B001’, ‘Anne’, ‘junior’, 3.4, ‘[email protected]’, ‘CS’);
insert into students values (‘B002’, ‘Terry’, ‘senior’, 2.8, ‘[email protected]’, ‘CS’);
insert into students values (‘B003’, ‘Wang’, ‘senior’, 3.2, ‘[email protected]’, ‘Math’);
insert into students values (‘B004’, ‘Barbara’, ‘junior’, 2.9, ‘[email protected]’, ‘ECE’);
insert into students values (‘B005’, ‘Smith’, ‘graduate’, 3.5, ‘[email protected]’, ‘Math’);
insert into students values (‘B006’, ‘Terry’, ‘graduate’, 3.7, ‘[email protected]’, ‘CS’);
insert into students values (‘B007’, ‘Becky’, ‘senior’, 4.0, ‘[email protected]’, ‘CS’);

insert into courses values (‘CS’, 432, ‘database systems’, 4);
insert into courses values (‘Math’, 314, ‘discrete math’, 4);
insert into courses values (‘CS’, 240, ‘data structure’, 4);
insert into courses values (‘Math’, 221, ‘calculus I’, 4);
insert into courses values (‘CS’, 532, ‘database systems’, 3);
insert into courses values (‘CS’, 552, ‘operating systems’, 3);
insert into courses values (‘BIOL’, 425, ‘molecular biology’, 4);

insert into classes values (‘c0001’, ‘CS’, 432, 1, 2009, ‘Spring’, 35, 34);
insert into classes values (‘c0002’, ‘Math’, 314, 1, 2008, ‘Fall’, 25, 24);
insert into classes values (‘c0003’, ‘Math’, 314, 2, 2008, ‘Fall’, 25, 22);
insert into classes values (‘c0004’, ‘CS’, 432, 1, 2008, ‘Spring’, 30, 30);
insert into classes values (‘c0005’, ‘CS’, 240, 1, 2009, ‘Spring’, 40, 39);
insert into classes values (‘c0006’, ‘CS’, 532, 1, 2009, ‘Spring’, 29, 28);
insert into classes values (‘c0007’, ‘Math’, 221, 1, 2009, ‘Spring’, 30, 30);

insert into enrollments values (‘B001’, ‘c0001’, ‘A’);
insert into enrollments values (‘B002’, ‘c0002’, ‘B’);
insert into enrollments values (‘B003’, ‘c0004’, ‘A’);
insert into enrollments values (‘B004’, ‘c0004’, ‘C’);
insert into enrollments values (‘B004’, ‘c0005’, ‘B’);
insert into enrollments values (‘B005’, ‘c0006’, ‘B’);
insert into enrollments values (‘B006’, ‘c0006’, ‘A’);
insert into enrollments values (‘B001’, ‘c0002’, ‘C’);
insert into enrollments values (‘B003’, ‘c0005’, null);
insert into enrollments values (‘B007’, ‘c0007’, ‘A’);
insert into enrollments values (‘B001’, ‘c0003’, ‘B’);
insert into enrollments values (‘B001’, ‘c0006’, ‘B’);
insert into enrollments values (‘B001’, ‘c0004’, ‘A’);
insert into enrollments values (‘B001’, ‘c0005’, ‘B’);

Thanks,
Samer

[mk3bmw]

ممكن توضح أسماء الحقول ؟؟؟

[SamerSal]

سلام عليكم

اه. انا اسف

students(sid, sname, status, gpa, email, deptname)
courses(dept_code, course#, title, credits)
classes(cid, dept_code, course#, sect#, year, semester, limit, class_size)
enrollments(sid, cid, lgrade)

[يسار]
where sid is the student id and is the primary key of students;
(dept_code, course#) form the primary key of courses;
cid is the class id and is the primary key of classes;
(sid, cid) form the primary key of enrollments.
As a simplification, each student is allowed to be associated with one department.
As a general clarification, we assume that no student takes the same course (including different sections of the same course) more than once.
If you have questions about these tables, please contact the instructor for clarification.

[/يسار]

[SamerSal]

سلام عليكم

هاذا هو حلي للجمله اس كي ال بس انا مو متاكد. لكن الاسبوع القادم سوف ااكد الحل من البروفسر تاعي. شكرا.

SQL> select c.dept_code, c.course#, c.title from courses c
 2  where c.title like '%systems%' and not exists
 3  (select * from students s
 4  where gpa in 5  (select gpa from students where gpa > 3.25)
 6 and not exists (select *from enrollments e
 7  where e.cid in (select cl.cid from classes cl
 8  where e.cid=cl.cid and e.sid= s.sid and 9   (cl.dept_code||' '||cl.course#)=(c.dept_code||
' '||c.course#))))
10  /

no rows selected

و هاذه النتيجه بالاعتماد علي البيانات المعطا .
اي تعليق مرحب به.
سلام عليكم