-1
$\begingroup$

I want to know how to display those statement ( what the correct statement for them

Display the name of the school that contain the maximum number of students For each school, display the number of students in each class I am building school management system project and the data base made in sql server

Thanx a lot 🌹🌹

$\endgroup$

1 Answer 1

2
$\begingroup$

Without understanding the table structure, it is hard to provide you much help. If you could update us with the table structure, I can absolutely help with this.

I assume you want this as one query, but, I feel this as two queries would be the most logical.

Please see the schema below:

table_structure

Max Enrolled Students, by School:

SELECT school.id                   AS `School ID`, 
       COUNT(DISTINCT students.id) AS `Enrollment Count` 
FROM   courses 
       LEFT JOIN course_enrollment ce 
              ON ce.course_id = courses.id 
       LEFT JOIN school school 
              ON school.id = courses.school_id 
       LEFT JOIN students students 
              ON students.id = ce.student_id 
GROUP  BY school.id 

Expected Result:

School ID   Enrollment Count
1   7
2   4

Enrollment, per class, by school:

SELECT school.id           AS `School ID`, 
       courses.course_name AS `Course Name`, 
       COUNT(students.id)  AS `Enrollment Count` 
FROM   courses 
       LEFT JOIN course_enrollment ce 
              ON ce.course_id = courses.id 
       LEFT JOIN school school 
              ON school.id = courses.school_id 
       LEFT JOIN students students 
              ON students.id = ce.student_id 
GROUP  BY school.id, 
          courses.course_name 

Expected Result:

School ID   Course Name Enrollment Count
1   CHEM1301    7
1   ENGL1301    3
2   ACCT1301    4

All schools, courses, professors and the respective enrolled students:

SELECT school.id                AS `School ID`, 
       school.name              AS `School Name`, 
       courses.course_name      AS `Course Name`, 
       courses.course_professor AS `Professor`, 
       students.first_name      `Student First Name`, 
       students.last_name       AS `Student Last Name` 
FROM   school school 
       LEFT JOIN courses courses 
              ON courses.school_id = school.id 
       LEFT JOIN course_enrollment ce 
              ON ce.course_id = courses.id 
       LEFT JOIN students students 
              ON students.id = ce.student_id 
GROUP  BY school.id, 
          courses.id, 
          students.id 

Expected Output:

School ID   School Name Course Name Professor   Student First Name  Student Last Name
1   School A    CHEM1301    John Doe    Aden    Baxter
1   School A    CHEM1301    John Doe    Anthony Leslie
1   School A    CHEM1301    John Doe    Laird   Jewel
1   School A    CHEM1301    John Doe    Osmond  Mikey
1   School A    CHEM1301    John Doe    Isidore Josiah
1   School A    CHEM1301    John Doe    Rafferty    Tate
1   School A    CHEM1301    John Doe    Dave    Lenox
1   School A    ENGL1301    Jacob Prask Aden    Baxter
1   School A    ENGL1301    Jacob Prask Rafferty    Tate
1   School A    ENGL1301    Jacob Prask Dave    Lenox
2   School B    ACCT1301    Glenn Ogg   Sachie  Baldwin
2   School B    ACCT1301    Glenn Ogg   Cletis  Nicky
2   School B    ACCT1301    Glenn Ogg   Garey   Simon
2   School B    ACCT1301    Glenn Ogg   Bennie  Howard

You can create the same tables I made by executing this SQL script:

# ************************************************************
# Sequel Pro SQL dump
# Version 4541
#
# http://www.sequelpro.com/
# https://github.com/sequelpro/sequelpro
#
# Host: REDACTED (MySQL 5.7.14-google-log)
# Database: school
# Generation Time: 2017-01-04 19:30:50 +0000
# ************************************************************


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


# Dump of table course_enrollment
# ------------------------------------------------------------

DROP TABLE IF EXISTS `course_enrollment`;

CREATE TABLE `course_enrollment` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `student_id` int(11) unsigned DEFAULT NULL,
  `course_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `course_id` (`course_id`),
  KEY `student_id` (`student_id`),
  CONSTRAINT `course_enrollment_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course_enrollment` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `course_enrollment_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `course_enrollment` WRITE;
/*!40000 ALTER TABLE `course_enrollment` DISABLE KEYS */;

INSERT INTO `course_enrollment` (`id`, `student_id`, `course_id`)
VALUES
    (1,1,1),
    (2,1,2),
    (3,2,1),
    (4,3,1),
    (5,4,1),
    (6,5,1),
    (7,6,1),
    (8,6,2),
    (9,7,3),
    (10,8,3),
    (11,9,3),
    (12,10,3),
    (13,11,1),
    (14,11,2);

/*!40000 ALTER TABLE `course_enrollment` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table courses
# ------------------------------------------------------------

DROP TABLE IF EXISTS `courses`;

CREATE TABLE `courses` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `course_name` text,
  `course_professor` text,
  `school_id` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `school_id` (`school_id`),
  CONSTRAINT `courses_ibfk_1` FOREIGN KEY (`school_id`) REFERENCES `school` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `courses` WRITE;
/*!40000 ALTER TABLE `courses` DISABLE KEYS */;

INSERT INTO `courses` (`id`, `course_name`, `course_professor`, `school_id`)
VALUES
    (1,'CHEM1301','John Doe',1),
    (2,'ENGL1301','Jacob Prask',1),
    (3,'ACCT1301','Glenn Ogg',2);

/*!40000 ALTER TABLE `courses` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table school
# ------------------------------------------------------------

DROP TABLE IF EXISTS `school`;

CREATE TABLE `school` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `school` WRITE;
/*!40000 ALTER TABLE `school` DISABLE KEYS */;

INSERT INTO `school` (`id`, `name`)
VALUES
    (1,'School A'),
    (2,'School B\n');

/*!40000 ALTER TABLE `school` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table students
# ------------------------------------------------------------

DROP TABLE IF EXISTS `students`;

CREATE TABLE `students` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` text,
  `last_name` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `students` WRITE;
/*!40000 ALTER TABLE `students` DISABLE KEYS */;

INSERT INTO `students` (`id`, `first_name`, `last_name`)
VALUES
    (1,'Aden','Baxter'),
    (2,'Anthony','Leslie'),
    (3,'Laird','Jewel'),
    (4,'Osmond','Mikey'),
    (5,'Isidore','Josiah'),
    (6,'Rafferty','Tate'),
    (7,'Sachie','Baldwin'),
    (8,'Cletis','Nicky'),
    (9,'Garey','Simon'),
    (10,'Bennie','Howard'),
    (11,'Dave','Lenox');

/*!40000 ALTER TABLE `students` ENABLE KEYS */;
UNLOCK TABLES;



/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
$\endgroup$

Not the answer you're looking for? Browse other questions tagged or ask your own question.