-- create a table
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  gender CHAR(1) NOT NULL
);
-- insert some values
INSERT INTO students VALUES (1, 'Ryan', 'M');
INSERT INTO students VALUES (2, 'Joanna', 'F');
insert into students values(34,'Manish','M');

CREATE TABLE Classes(
ClassID INTEGER PRIMARY KEY,
ClassName CHAR(20) NOT NULL,
StudentID INTEGER NOT NULL);

-- INSERTING THE ROWS IN CLASS TABLE
INSERT INTO Classes values(1,'Math',2);
insert into Classes values(2,'Science',1);
insert into Classes values(3,'History',5);
insert into Classes values(5,'Hindi',12);
/*
#INNER JOIN
#SELECT s.name, s.gender, c.ClassName FROM students as s 
#inner join Classes as c on s.id = c.StudentID;

# LEFT join
#SELECT 
#s.id as ID, 
#s.name as "Student ",
#c.ClassName,
#c.ClassID from students as s
#LEFT JOIN Classes as c on s.id = c.StudentID;

# RIGHT JOIN
SELECT 
s.id as ID, 
s.name as "Student ",
c.ClassName,
c.ClassID from students as s
RIGHT JOIN Classes as c on s.id = c.StudentID;

#CROSS JOIN

#SELECT s.id as StudentID, c.ClassName, s.name as "Student Name" FROM students as s
#CROSS JOIN Classes as c;
*/
#CASE:
SELECT s.id, s.name, c.ClassName, 
CASE
    WHEN c.ClassID == 1 THEN 'This is math class'
    /*WHEN c.ClassName == 'Science' THEN 'This is Science class'
    WHEN c.ClassName == 'Hindi' THEN 'This is Hindi Class'
    else 'This is History Class'*/
end as 'description'
FROM students as s inner join Classes as c
on s.id = c.StudentID;