Menu

Add Topic

Forum

waiting answer December 20, 2020

Design a course management database by creating the following table

Design a course management database by creating the following table:

  1. student (regno, name, address, age)
  2. course (cno, cname, fees, fno)
  3. enroll (regno, cno, edate)
  4. faculty (fno, fname, specialization)

Problems

  1. Create tables using the above relation schema by taking suitable data type and with appropriate constraints
  2.  Insert 5 rows each to these tables of your choice. 
  3.  Write SQL queries to meet the following demand
    1. List all those students who are greater than 18 years of age and have opted for "DBMS" course.
    2. List all those courses whose fee is greater than that of "DBMS" course.
    3. Find the list of students who have enrolled to the course "DBMS" after a date
    4. Find all those faculty members who have not offered any course.
    5. List all faculties who teach a student "John".

Answers
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

Please Login to Post the answer

Leave an Answer