YUSUF ATES
asked on
SQL Procedure
I want to get the category of a student by creating a procedure which has two parameters p_student_id and p_event_id (they are also columns so I put p_). One way is that I can join all the three tables (student, score, grade_event) and apply the filter for the input parameters to get the category.
STUDENT table has NAME, GENDER, and STUDENT_ID
GRADE_EVENT table has DATE, CATEGORY, and EVENT_ID
SCORE table has STUDENT_ID, EVENT_ID, and SCORE
Do you have any solution?
I tried this one but did not get the category.
DELIMITER $$
CREATE PROCEDURE CATEGORY(IN p_student_id INT, p_event_id INT)
BEGIN
BEGIN
SELECT NAME, GENDER, SCORE, DATE, CATEGORY
FROM STUDENT st
JOIN SCORE S
ON st.student_id = s.student_id
JOIN grade_event g
ON s.event_id = g.event_id;
END;
END
$$
STUDENT table has NAME, GENDER, and STUDENT_ID
GRADE_EVENT table has DATE, CATEGORY, and EVENT_ID
SCORE table has STUDENT_ID, EVENT_ID, and SCORE
Do you have any solution?
I tried this one but did not get the category.
DELIMITER $$
CREATE PROCEDURE CATEGORY(IN p_student_id INT, p_event_id INT)
BEGIN
BEGIN
SELECT NAME, GENDER, SCORE, DATE, CATEGORY
FROM STUDENT st
JOIN SCORE S
ON st.student_id = s.student_id
JOIN grade_event g
ON s.event_id = g.event_id;
END;
END
$$
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This one works great. Now I get what I want.
Thanks a lot.
I appreciate.
Thanks a lot.
I appreciate.
ASKER
STUDENT table
name VARCHAR(20) NOT NULL,
GENDER ENUM('F','M') NOT NULL,
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (student_id)
GRADE_EVENT table
date DATE NOT NULL,
category ENUM('T','Q') NOT NULL,
event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (event_id)
SCORE table
student_id INT UNSIGNED NOT NULL,
event_id INT UNSIGNED NOT NULL,
score INT NOT NULL,
PRIMARY KEY (event_id, student_id),
INDEX (student_id),
FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
FOREIGN KEY (student_id) REFERENCES student (student_id)