Design a course management database by creating the following table

sadique December 20, 2020

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 0