ADVANCE-SQL-QUERIES

/*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$$