Design a course management database by creating the following table:
Problems
Table Structure for student
CREATE TABLE `student` (
`regno` varchar(20) NOT NULL,
`name` varchar(20) NOT NULL,
`address` varchar(20) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`regno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Table Structure for course
CREATE TABLE `course` (
`cno` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(255) NOT NULL,
`fees` int(11) NOT NULL,
`fno` int(11) NOT NULL,
PRIMARY KEY (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin
Table Structure for enroll
CREATE TABLE `enroll` (
`regno` int(11) NOT NULL AUTO_INCREMENT,
`cno` int(11) NOT NULL,
`edate` date NOT NULL,
PRIMARY KEY (`regno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin
Table Structure for faculty
CREATE TABLE `faculty` (
`fno` int(11) NOT NULL AUTO_INCREMENT,
`fname` varchar(255) NOT NULL,
`specialization` varchar(255) NOT NULL,
PRIMARY KEY (`fno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Dumping Data for Table Student
INSERT INTO `student` (`regno`, `name`, `address`, `age`) VALUES (NULL, 'Student A', 'Student A Address', '22');
INSERT INTO `student` (`regno`, `name`, `address`, `age`) VALUES (NULL, 'Student B', 'Student B Address', '22');
INSERT INTO `student` (`regno`, `name`, `address`, `age`) VALUES (NULL, 'John', 'John Address', '24');
INSERT INTO `student` (`regno`, `name`, `address`, `age`) VALUES (NULL, 'Mathew', 'Mathew Address', '17');
INSERT INTO `student` (`regno`, `name`, `address`, `age`) VALUES (NULL, 'Ravinder', 'Ravinder Address', '19');
Dumping Data for Table Course
INSERT INTO `course` (`cno`, `cname`, `fees`, `fno`) VALUES (NULL, 'Data Structure', '4000', '5');
INSERT INTO `course` (`cno`, `cname`, `fees`, `fno`) VALUES (NULL, 'Operating Systems', '500', '1');
INSERT INTO `course` (`cno`, `cname`, `fees`, `fno`) VALUES (NULL, 'DBMS', '3000', '4');
INSERT INTO `course` (`cno`, `cname`, `fees`, `fno`) VALUES (NULL, 'Digital Fundamentals', '200', '2');
INSERT INTO `course` (`cno`, `cname`, `fees`, `fno`) VALUES (NULL, 'COA', '5000', '3');
Dumping Data for Table Enroll
INSERT INTO `enroll` (`regno`, `cno`, `edate`) VALUES ('3', '2', '2020-12-31');
INSERT INTO `enroll` (`regno`, `cno`, `edate`) VALUES ('1', '5', '2020-12-25');
INSERT INTO `enroll` (`regno`, `cno`, `edate`) VALUES ('2', '3', '2020-12-26');
INSERT INTO `enroll` (`regno`, `cno`, `edate`) VALUES ('4', '2', '2020-12-21');
INSERT INTO `enroll` (`regno`, `cno`, `edate`) VALUES ('5', '1', '2020-12-31');
Dumping Data for Table Faculty
INSERT INTO `faculty` (`fno`, `fname`, `specialization`) VALUES (NULL, 'Ashok', 'Computer Teacher');
INSERT INTO `faculty` (`fno`, `fname`, `specialization`) VALUES (NULL, 'Ashokan', 'English');
INSERT INTO `faculty` (`fno`, `fname`, `specialization`) VALUES (NULL, 'John', 'Mathematics');
INSERT INTO `faculty` (`fno`, `fname`, `specialization`) VALUES (NULL, 'Basheer', 'Arabic');
INSERT INTO `faculty` (`fno`, `fname`, `specialization`) VALUES (NULL, 'Wilson', 'Data Structure');
List all those students who are greater than 18 years of age and have opted for "DBMS" course
SELECT student.regno,student.name,student.age FROM `enroll` INNER JOIN `student` ON enroll.regno = student.regno WHERE student.age > 18 AND enroll.cno = 3
List all those courses whose fee is greater than that of "DBMS" course.
SELECT * FROM `course` WHERE `fees` > (SELECT `fees` FROM `course` WHERE `cno` = 3
Find the list of students who have enrolled to the course "DBMS" after a date
SELECT student.regno,student.name,enroll.edate FROM `enroll` INNER JOIN `student` ON enroll.regno = student.regno WHERE `edate` > '2020-12-26' AND `cno` = 3
Find all those faculty members who have not offered any course
SELECT * FROM `faculty` WHERE `fno` NOT IN (SELECT DISTINCT `fno` FROM `course`)
List all faculties who teach a student "John"
SELECT faculty.fno,faculty.fname,faculty.specialization FROM `enroll` INNER JOIN `course` ON enroll.cno = course.cno LEFT JOIN `faculty` ON course.fno = faculty.fno WHERE enroll.regno = 3
0 0Please Login to Post the answer