/*Multiple Inner Join:*/
SELECT students.name,students.rollno,course.cname,marks.M1,marks.M2,marks.M3 FROM students 
INNER JOIN course ON students.cid=course.cid INNER JOIN marks ON students.id=marks.id;


/*Simple Dynamic Marklist using Queries:*/

SELECT students.name,students.rollno,
course.cname as Course,
marks.M1,marks.M2,marks.M3,
(marks.M1,marks.M2,marks.M3) AS Total,
ROUND(((marks.M1,marks.M2,marks.M3)/3),2) AS Average
CASE
	WHEN marks.M1 >= 35 AND marks.M2 >= 35 AND marks.M3 >= 35 THEN 'PASS'
	ELSE 'FAIL'
END AS Result,
CASE
	WHEN marks.M1 >= 35 AND marks.M2 >= 35 AND marks.M3 >= 35 THEN
		CASE
			WHEN ROUND(((marks.M1,marks.M2,marks.M3)/3),2) >= 90 AND ROUND(((marks.M1,marks.M2,marks.M3)/3),2) 
			< = 100 THEN 'A'
			WHEN ROUND(((marks.M1,marks.M2,marks.M3)/3),2) >= 80 AND ROUND(((marks.M1,marks.M2,marks.M3)/3),2) 
			< = 89 THEN 'B'
			ELSE
			'C'
		END
	ELSE 'NO GRADE'
END AS Result,
 FROM students INNER JOIN course ON students.cid=course.cid INNER JOIN marks ON students.id=marks.id;
 
 
 
/*To use dynamically Created column names:*/

 SELECT students.name,students.rollno,
course.cname as Course,
marks.M1,marks.M2,marks.M3,
(marks.M1,marks.M2,marks.M3) AS Total,
ROUND(((marks.M1,marks.M2,marks.M3)/3),2) AS Average
CASE
	WHEN marks.M1 >= 35 AND marks.M2 >= 35 AND marks.M3 >= 35 THEN 'PASS'
	ELSE 'FAIL'
END AS Result,
CASE
	WHEN marks.M1 >= 35 AND marks.M2 >= 35 AND marks.M3 >= 35 THEN
		CASE
			WHEN ROUND(((marks.M1,marks.M2,marks.M3)/3),2) >= 90 AND ROUND(((marks.M1,marks.M2,marks.M3)/3),2)
			< = 100 THEN 'A'
			WHEN ROUND(((marks.M1,marks.M2,marks.M3)/3),2) >= 80 AND ROUND(((marks.M1,marks.M2,marks.M3)/3),2) 
			< = 89 THEN 'B'
			ELSE
			'C'
		END
	ELSE 'NO GRADE'
END AS Result,
 FROM students INNER JOIN course ON students.cid=course.cid INNER JOIN marks ON students.id=marks.id 
 WHERE course.cname='BCA' having Result = 'PASS' AND (Average >= 70 AND Average <= 100);
 
 
 
/*Creating queries as View:*/
 
CREATE VIEW Reports AS
SELECT students.name,students.rollno,
course.cname as Course,
marks.M1,marks.M2,marks.M3,
(marks.M1,marks.M2,marks.M3) AS Total,
ROUND(((marks.M1,marks.M2,marks.M3)/3),2) AS Average
CASE
	WHEN marks.M1 >= 35 AND marks.M2 >= 35 AND marks.M3 >= 35 THEN 'PASS'
	ELSE 'FAIL'
END AS Result,
CASE
	WHEN marks.M1 >= 35 AND marks.M2 >= 35 AND marks.M3 >= 35 THEN
		CASE
			WHEN ROUND(((marks.M1,marks.M2,marks.M3)/3),2) >= 90 AND ROUND(((marks.M1,marks.M2,marks.M3)/3),2) 
			< = 100 THEN 'A'
			WHEN ROUND(((marks.M1,marks.M2,marks.M3)/3),2) >= 80 AND ROUND(((marks.M1,marks.M2,marks.M3)/3),2) 
			< = 89 THEN 'B'
			ELSE
			'C'
		END
	ELSE 'NO GRADE'
END AS Result,
FROM students INNER JOIN course ON students.cid=course.cid INNER JOIN marks ON students.id=marks.id;
 
/*Accessing views like a table:*/
 SELECT * FROM Reports;
 SELECT * FROM Reports WHERE Result = 'PASS';
 
 
 /*Update table using Inner Join:*/
 
 UPDATE marks INNER JOIN students ON students.id = marks.id SET M1 = 100,M2 = 100,M3 = 100 WHERE 
 students.rollno = 'A1001';
 
 
/*Creating Triggers:*/
CREATE TRIGGER before_products_update
	BEFORE UPDATE ON product
	FOR EACH ROW
BEGIN 
	INSERT INTO price_logs(pid,price,new_price)
	VALUES(old.pid,old.rate,new.rate);
END$$