-- MySQL Assignment
-- Student: Ausama
-- Academy database tasks

-- create the student table
create table Student(
ID int primary key,
Fname varchar(50),
NumberCourse varchar(10),
Course varchar(50),
Grade int
);

-- insert the given data
insert into Student values
(109101,'Ahmed','CS321','Database1',50),
(109122,'Ali','CS221','System Analysis',75),
(109123,'Mohamed','CS321','Database1',40),
(109260,'Areej','CS305','Network',90),
(109265,'Asma','CS401','Software Testing',37),
(109267,'Omar','CS322','Database2',20),
(109300,'Ashrf','CS401','Software Testing',70),
(109366,'Khaled','CS321','Database1',NULL);

-- show ID, name and course
select ID, Fname, Course
from Student;

-- update Ahmed's grade
update Student
set Grade = 65
where Fname='Ahmed' and Course='Database1';

-- students who passed CS401
select count(*) as count
from Student
where NumberCourse='CS401' and Grade >= 50;

-- highest grade in CS321
select max(Grade) as Maximal
from Student
where NumberCourse='CS321';

-- lowest grade in CS401
select min(Grade) as Minimal
from Student
where NumberCourse='CS401';

-- count students who failed
select count(*) as Fail
from Student
where Grade < 50;

-- add my own data
insert into Student
values (109500,'Osama','CS322','Database2',85);

-- successful students sorted by name
select *
from Student
where Grade >= 50
order by Fname;

-- names sorted by highest grade
select Fname
from Student
where Grade >= 50
order by Grade desc;

-- first 3 rows
select *
from Student
limit 3;

-- students taking Network or Database2
select *
from Student
where Course in ('Network','Database2');

-- all courses except Network and Database2
select *
from Student
where Course not in ('Network','Database2');

-- grades between 50 and 75
select *
from Student
where Grade between 50 and 75;

-- number of different courses
select count(distinct NumberCourse)
from Student;

-- students who didn't take exam
select *
from Student
where Grade is null;

-- students who took exam
select *
from Student
where Grade is not null;

-- increase name length
alter table Student
modify column Fname varchar(100);

-- 5 rows starting from the 3rd row
select *
from Student
order by ID
limit 5 offset 2;

-- count students in each course
select Course, count(*)
from Student
group by Course;

-- courses with more than 2 students
select Course, count(*)
from Student
group by Course
having count(*) > 2
order by Course;

-- delete Asma's record
delete from Student
where ID = 109265;