SQL DB/Database.docx
Let us take an example of college student, create 3 table Student, Course and Grade.
Create table:
CREATE TABLE `students_details` (
`student_id` int(11) NOT NULL,
`course_id` int(11) DEFAULT NULL,
`students_name` varchar(45) DEFAULT NULL,
`students_mobile_number` varchar(45) DEFAULT NULL,
`students_email_id` varchar(45) DEFAULT NULL,
`students_dob` datetime DEFAULT NULL,
`students_address` varchar(45) DEFAULT NULL,
`students_joining_yr` datetime DEFAULT NULL, PRIMARY KEY (`student_id`)
)
CREATE TABLE `course` (
`course_id` int(11) NOT NULL,
`course_name` varchar(45) DEFAULT NULL,
`course_description` varchar(45) DEFAULT NULL,
`subject_1_code` varchar(45) DEFAULT NULL,
`subject_2_code` varchar(45) DEFAULT NULL,
`subject_3_code` varchar(45) DEFAULT NULL,
`subject_4_code` varchar(45) DEFAULT NULL,
`subject_5_code` varchar(45) DEFAULT NULL,
PRIMARY KEY (`course_id`)
)
CREATE TABLE `grade` (
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`subject_1_grade` varchar(45) DEFAULT NULL,
`subject_2_grade` varchar(45) DEFAULT NULL,
`subject_3_grade` varchar(45) DEFAULT NULL,
`subject_4_grade` varchar(45) DEFAULT NULL,
`subject_5_grade` varchar(45) DEFAULT NULL
)
Insert record into these table:
insert into students_details values(101,111,'Jack', '12341234','
[email protected]',sysdate(),'XYZ',sysdate());
insert into students_details values(102,121,'Rose', '78901234','
[email protected]',sysdate(),'PQR',sysdate());
select * from students_details;
insert into course values(111,'Course1','Database schema', 21,44,55,66,77);
insert into course values(121,'Course7','Network', 41,64,75,96,177);
select * from course;
insert into grade values(101,111,'B','A','B','C','A');
insert into grade values(102,121,'A','A','C','B','C');
select * from grade;
Create Procedure:
USE `student`;
DROP procedure IF EXISTS `student_record`;
DELIMITER $$
USE `student`$$
CREATE PROCEDURE `student_record`(id int)
BEGIN
select * from students_details s inner join course c on s.course_id=c.course_id where student_id = id;
END$$
DELIMITER ;
Execute:
call student_record (101);
Explanation:
This procedure will fetch student detail of specific student like Student name, Address, mobile number, email id, courses and so on. In this procedure, student id is an input parameter as a id for which student details is required.
=================================================================
USE `student`;
DROP procedure IF EXISTS `course_details`;
DELIMITER $$
USE `student`$$
CREATE PROCEDURE `course_details`(cId int)
BEGIN
select * from course where course_id=cId;
END$$
DELIMITER ;
Execute:
call course_details (111);
Explanation:
This procedure will fetch course details for specific course id as cid. This cid will get details from course table and response as a course data.
=================================================================
USE `student`;
DROP procedure IF EXISTS `grade_details`;
DELIMITER $$
USE `student`$$
CREATE PROCEDURE `grade_details` (sid int, cid int)
BEGIN
select * from grade where student_id=sid and course_id=cid;
END$$
DELIMITER ;
Execute
call grade_details (101, 111);
Explanation:
This procedure will get the student grade according to their student and course. It have 2 params as an input parameter like sid and cid where Student id and course id respectively.
Create Function
USE `student`;
DROP function IF EXISTS `student_name`;
DELIMITER $$
USE `student`$$
CREATE FUNCTION `student_name` (sid int)
RETURNS nvarchar(50) DETERMINISTIC
BEGIN
declare name nvarchar(50);
select student_name into name from students_details where student_id=sid;
RETURN name;
END$$
DELIMITER ;
Execute:
select student_name (101);
Explanation:
This function will return student name for student id.
=================================================================
USE `student`;
DROP function IF EXISTS `course_name`;
DELIMITER $$
USE `student`$$
CREATE FUNCTION `course_name` (coiurseId int)
RETURNS nvarchar(50) DETERMINISTIC
BEGIN
declare coursename nvarchar(50);
select course_name into coursename from course where course_id=coiurseId;
RETURN coursename;
END$$
DELIMITER ;
Execute:
select course_name (111);
Explanation:
This function return course name of their course id.
=================================================================
USE `student`;
DROP function IF EXISTS `get_grade`;
DELIMITER $$
USE `student`$$
CREATE FUNCTION `get_grade` (sid int, cid int)
RETURNS nvarchar(5) DETERMINISTIC
BEGIN
declare student_grade nvarchar(5);
select subject_1_grade into student_grade from grade where student_id=sid and course_id=cid;
RETURN student_grade;
END$$
DELIMITER ;
Execute:
select get_grade (101,111);
Explanation:
This function will return student grade of their course id.
SQL DB/Procedure-Function.pdf
Let us take an example of college student, create 3 table Student, Course and Grade.
Create table:
CREATE TABLE `students_details` (
`student_id`...