---sessin 12-12-2023
--1 tollab w course
select students.student_ID,students.FIRST_NAME,students.LAST_NAME,
courses.course_title,courses.course_id, courses.duration
from students inner join courses on students.course_id=courses.course_id
--2 student meme si pas de courses
select students.student_ID,students.FIRST_NAME,students.LAST_NAME,
courses.course_title,courses.course_id, courses.duration
from students left outer join courses on students.course_id=courses.course_id
--3 badde kill st li msajjalin, ya3ne l courses li msajjal fihon st
select students.student_ID,students.FIRST_NAME,students.LAST_NAME,
courses.course_title,courses.course_id, courses.duration
from students right outer join courses on students.course_id=courses.course_id
--4 full here, kil l talamiz hatta li 3endon courses null w kil l courses 7atta law
ma fihon talamiz
select students.student_ID,students.FIRST_NAME,students.LAST_NAME,
courses.course_title,courses.course_id, courses.duration
from students full join courses on students.course_id=courses.course_id
--extra kill l aseme li bil web eng// law baddo esem l
select students.student_ID,students.FIRST_NAME,students.LAST_NAME,students.age
from students
where course_id=3
--5 yimkin ma zabatit!!!!
select courses.course_title,courses.duration
from courses
where course_id not in
(select course_id from students)
--5!
select courses.course_title,courses.duration, courses.course_id,
students.student_id
from students full outer join courses on students.course_id=courses.course_id
where students.student_id is null
--5 tari2te:
SELECT courses.course_title, courses.duration
FROM courses
LEFT OUTER JOIN students ON courses.course_id = students.course_id
WHERE students.course_id IS NULL;
--6
select c.course_title, count(s.student_id)
from courses c full outer join students s on c.course_id=s.course_id
group by c.course_title
--6 suite
select c.course_title, avg(s.age)
from courses c full outer join students s on c.course_id=s.course_id
group by c.course_title
---je peux de plus faire count et avg en meme temps
select c.course_title, avg(s.age) as avg_age,count(s.student_id)as
student_per_course
from courses c full outer join students s on c.course_id=s.course_id
group by c.course_title ---fine zid 3le enno ma badde l courses li ma fihon hada =>
on ferra inner join
--- supp
select c.course_title, avg(s.age) as avg_age,count(s.student_id)as
student_per_course
from courses c inner join students s on c.course_id=s.course_id
group by c.course_title
--7
select s.first_name, s.last_name, s.age
from students s
where s.age<18
--8
select c.course_title, c.duration
from courses c
where c.duration<18
--9
select s1.first_name, s1.last_name, s2.first_name, s2.last_name
from students s1 join students s2 on s1.course_id=s2.course_id
where s2.first_name='Neil' and s2.last_name='Peart'
---kirmel shil howwe w ma3 7alo on fait:
select s1.first_name, s1.last_name
from students s1 join students s2 on s1.course_id=s2.course_id
where s2.first_name='Neil' and s2.last_name='Peart'
---autre methode
select s.first_name, s.last_name
from students s
where course_id=(select course_id from students where first_name='Neil' and
last_name='Peart')
--10
select state,count(*)
from students
group by state
--11
select c.course_title, count(s.student_id)
from courses c full outer join students s on c.course_id=s.course_id
group by c.course_title
----iza ma badde talli3 l null ba3mal:
select c.course_title, count(s.student_id)
from courses c left outer join students s on c.course_id=s.course_id
group by c.course_title
having count (s.student_id)>2
---- al eno sar bil 12!!
--12 lezim a3mal group by bi hek no3
select c.course_title, count(s.student_id)
from courses c left outer join students s on c.course_id=s.course_id
group by c.course_title
having count(s.student_id)>2
--13
select *
from students
where course_id is null
--14 fina na3mal inner iz abadde bas li msajjil
select *
from students s left join courses c on s.course_id=c.course_id
--15
select c.course_title, count(s.student_id)
from courses c left outer join students s on c.course_id=s.course_id
group by c.course_title
--16
select students.student_ID,students.FIRST_NAME,students.LAST_NAME,
courses.course_title
from students inner join courses on students.course_id=courses.course_id
--17
--ma ntabahet shou 3emil
--18---- a voir seule surtout ce qui est en rapport avec la condition "and"
select s1.first_name, s1.last_name, s1.age,s2.first_name, s2.last_name, s2.age,
c.course_title
from students s1 inner join students s2 on s1.course_id=s2.course_id
and s1.student_id<s2.student_id
--- hayda bya3mal join 3ala l natije!!!!!!
join courses c on s1.course_id=c.course_id
--19
select c.course_title, count(s.student_id)
from courses c left join students s on c.course_id=s.course_id
where s.age>18
group by c.course_title
--20
--inner
--21
select c.course_title
from courses c left join students s on c.course_id=s.course_id
where s.student_id is null
--22
select c.course_title, s.first_name, s.last_name
from courses c full outer join students s on c.course_id=s.course_id
where s.course_id is null
--- la2anno l join sarit 3ala course hek al bas ma 2tana3at
--23----NEW------
select s.first_name, s.last_name
case when s.course_id is null then 'not enrolled' else 'enrolled' end as status
from students s left outer join courses c on c.course_id=s.course_id
--24 mitil l be2yin
