drop table if exists students;
drop table if exists grades;

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name NVARCHAR(100) NOT NULL,
  gender NVARCHAR(100) NOT NULL,
  Createddate datetime not null
);

INSERT INTO students VALUES (1, 'Ryan', 'Male','2022-05-03 14:40:53.000');
INSERT INTO students VALUES (2, 'Joanna', 'Female','2022-02-08 09:17:16.000');
INSERT INTO students VALUES (3, 'Kate', 'Female','2019-09-18 10:27:26.000');
INSERT INTO students VALUES (4, 'Alex', 'Male','2021-03-08 09:17:16.000');
INSERT INTO students VALUES (5, 'John', 'Male','2020-01-18 08:37:12.000');

CREATE TABLE Grades (
  GradeIDX int not null,
  id int not null,
  Score int NOT NULL,
  Subject NVARCHAR(100) NOT NULL
);

INSERT INTO Grades VALUES (1,1,82,'Math');
INSERT INTO Grades VALUES (2,1,62,'English');
INSERT INTO Grades VALUES (3,1,76,'Science');
INSERT INTO Grades VALUES (4,2,62,'Math');
INSERT INTO Grades VALUES (5,2,93,'English');
INSERT INTO Grades VALUES (6,2,89,'Science');
INSERT INTO Grades VALUES (7,3,98,'Math');
INSERT INTO Grades VALUES (8,3,95,'English');
INSERT INTO Grades VALUES (9,3,80,'Science');
INSERT INTO Grades VALUES (10,4,72,'Math');
INSERT INTO Grades VALUES (11,4,83,'English');
INSERT INTO Grades VALUES (12,4,77,'Science');
INSERT INTO Grades VALUES (13,6,99,'Spanish');


--select *
--from Grades




select name,gender,Score,subject
from students
join Grades on students.id = grades.id
where name = 'Joanna' and Subject = 'Math'


--SELECT cast(Createddate as date) date
--from students st

--SELECT name,max(Score) as sc
--from students st
--inner join Grades gd on st.id=gd.id
--group by st.name


--With cte as(
--SELECT name,Subject,score,
--DENSE_RANK() over (partition by name order by score desc) as rank 
--from students st
--inner join Grades gd on st.id=gd.id)
--select * from cte
--where rank =1

--SELECT name,cast(avg(score) as int) AS AvgScore
--from students st
--inner join Grades gd on st.id=gd.id
--group by gd.id



--SELECT st.name,st.gender,gd.subject,gd.score

--,Case when Score>=90 then 'A'
--      when Score>=80 then 'B'
--      when Score>=70 then 'C'
--      else 'F' end as Grade
      
--,IIF(gd.score>=80,'Pass','Fail') as Result

--FROM students st
--inner join Grades gd on st.id=gd.id