Artificial Intelligence Question posted in
ChatGBT is becoming a world-wide phenomena, try it out here.

SQL Select Statement with count of modules – Artificial Intelligence

List the number of Students enrolled for each module code:

I am having trouble with this query, what I am looking to do is a select statement for example 

select count(studID) from student where ModCode = 'C102'

The intended output would represent a heading of the module name and underneath the number of students which are associated with this module. If possible I want to include all of the modules on the output at once.

Structure:

CREATE TABLE Student
(StudID       INTEGER       PRIMARY KEY,
 StudFName    VARCHAR(10)   NOT NULL,
 StudLName    VARCHAR(10)   NOT NULL,
 DoB          DATE          NOT NULL,
 Sex          CHAR(1)       NOT NULL CHECK (Sex IN ('M', 'F')),
 Email        VARCHAR(30)   UNIQUE);

CREATE TABLE Module
(ModCode      CHAR(4)       PRIMARY KEY,
 ModName      VARCHAR(25)   NOT NULL,
 ModCredits   INTEGER       NOT NULL CHECK (ModCredits IN (15, 30, 45, 60)),
 ModLevel     CHAR(3)       NOT NULL CHECK (ModLevel   IN ('UG1', 'UG2', 'UG3', 'MSc')),
 ModLeader    INTEGER       NOT NULL,
 Foreign Key (ModLeader)    REFERENCES Staff (StaffID));

INSERT INTO Module VALUES ('C102', 'Java Fundamentals',         15, 'UG1', 108);
INSERT INTO Module VALUES ('C104', 'Oracle Fundamentals',       15, 'UG1', 100);
INSERT INTO Module VALUES ('E109', 'RF Circuit Design',         30, 'UG1', 102);
INSERT INTO Module VALUES ('E203', 'Side-Scan Sonar',           15, 'UG2', 104);
INSERT INTO Module VALUES ('C201', 'Database Applications',     15, 'UG2', 100);
INSERT INTO Module VALUES ('R209', 'Artificial Intelligence 1', 30, 'UG2', 105);
INSERT INTO Module VALUES ('C212', 'Advanced Java Apps',        15, 'UG2', 108);
INSERT INTO Module VALUES ('N205', 'Cisco Switches',            15, 'UG2', 109);
INSERT INTO Module VALUES ('C303', 'Database Administration',   30, 'UG3', 100);
INSERT INTO Module VALUES ('R310', 'Autonomous Botnets',        30, 'UG3', 106);

CREATE TABLE Enrolment
(ModCode      CHAR(4)       NOT NULL,
 StudID       INTEGER       NOT NULL,
 StartDate    DATE          NOT NULL,
 PRIMARY KEY (ModCode, StudID),
 Foreign Key (StudID)  REFERENCES Student (StudID),          
 Foreign Key (ModCode) REFERENCES Module (ModCode));
Tags:

2

Answers


  1. 0

    You would have to join the Module table to the Enrolment table and Group by the ModCode

    SELECT M.ModCode, count(studID) as NoEnrolled
FROM Module M
INNER JOIN Enrolment E ON E.ModCode = M.ModCode 
GROUP BY M.ModCode
    Login or Signup to reply.
  2. 0

    I’m assuming you want to count modules in which no students are enrolled?

    SELECT m.ModCode, COUNT(e.StudID) AS enrollment_cnt
  FROM Module m LEFT JOIN Enrollment e
    ON m.ModCode = e.ModCode
 GROUP BY m.ModCode

    I use a LEFT JOIN above so that modules in which no students are enrolled will still be counted. If you don’t want to count those modules, use INNER JOIN in place of LEFT JOIN (or simply query the Enrollment table!).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search