--3.	List all students’ numbers for those registered to the same courses being registered to ‘Marwan Ali’.
select stdno from register where courseid in 
                        (select courseid from register r join student s on r.stdno=s.stdno and givennames='Marwan Ali');

--4.	Increase the cost of courses by 10% for all courses that are registered by 2 or more students

update course
set cost = cost*1.1 where courseid in (select courseid from register group by courseid having count(*)>=2);

--5.	Display the most unpopular registered course. Print the course id, 
-- course title and course cost. The unpopular course is the course that registered by the lowest number of student. 
--(Hint: use order by and TOP).

--- ملحوظة ال top لا تعمل فى oracle live sql  ولذلك تم حل المطلوب بطريقة تانية
select courseid,COURSETITLE,cost 
from course where courseid = (select top 1 courseid from from register group by courseid order by count(*) )
                      
-- هذه الطريقة هلى التى تعمل فى اوراكل لايف سكول
select courseid,COURSETITLE,cost 
from course where courseid = (select courseid from 
(select courseid from register group by courseid order by count(*) ) 
where rownum<2  )

--6.	Display all courses' titles and cost for those being registered by any student in the IT department 
--within semester 2 of 2012. The list should be sorted in an ascending order based on the cost of the course.

select coursetitle , cost from course 
where courseid in (select courseid from register r join student s on r.stdno =s.stdno 
join semester sem on r.semesterid=sem.semesterid where DEPT='IT' and SEMESTERCODE=2 and  SEMESTERYEAR =2012 )
order by cost ;
                        
-- 7.	Delete all the records in the Register table for the student whose given name is ‘Abdulaziz Saleh’.
delete from register where stdno = (select stdno from student where givennames = 'Abdulaziz Saleh');