-- create a table
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL,
  Createddate datetime not null
);
-- insert some values
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 TEXT 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');













SELECT *
from students st
inner join Grades gd on st.id=gd.id





/*
SELECT name,max(score) as maxscore,subject
from students st
inner join Grades gd on st.id=gd.id
group by gd.id



/*
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 
where st.gender = 'M'
*/