Блог

Мне нужно найти, какие курсы в настоящее время заполнены (количество зачисленных студентов равно вместимости помещения, в котором оно находится)? Для каждого из предлагаемых курсов в результатах укажите его символ, номер, номер секции, здание, номер комнаты и вместимость.

Я знаю, что три таблицы, которые мне нужно использовать, — это зарегистрированные, courseoffering_of и местоположение с этими вставками:

 INSERT INTO enrolled (netID, symbol, number, sectionNumber) VALUES
('aec3', 'CSE', '4503', '01'),
('hc23', 'CSE', '1384', '02'),
('jjc13', 'CSE', '1384', '02'),
('wp4', 'CSE', '1384', '02' );

INSERT INTO courseoffering_of (symbol, `number`, building, room, 
sectionNumber) VALUES
('CSE', '4503', 'Butler', '100', '01'),
('CSE', '1384', 'Butler', '103', '01'),
('CSE', '1384', 'Butler', '103', '02'),
('ECE', '4713', 'Old Main', '2830', '01');

INSERT INTO location (building, room, capacity) VALUES
('Butler', '100', 5),
('Butler', '102', 2),
('Butler', '103', 2),
('Old Main', '2830', 6),
('Old Main', '3030', 3);`
  

Я придумал некоторый псевдокод, который поможет мне решить эту проблему:

 1. merge courseoffering_of and location tables
2. count students in each class from enrolled (same number and section number)
3. select where class from enrolled = class from merged table (same number 
and section number)
4. compare number of students counted in that class with room capacity
5. if students enrolled = room capacity return the entry from merged table
  

Я понятия не имею, что делать для этого запроса. Изначально я думал, что поможет создание новой таблицы из courseoffering_of и location, но наш учитель хочет, чтобы мы сделали все это в одном запросе. Я попробовал внутреннее соединение, но не смог понять, как его использовать в этой ситуации.

Любая помощь будет с благодарностью, спасибо.

Давайте начнем здесь:

 select symbol,number,sectionNumber, count(*) as count_enrolled
from enrolled
group by symbol,number,sectionNumber
  

Приведенный выше запрос вернет, сколько студентов было зачислено на каждый курс:

 symbol  number  sectionNumber   count_enrolled
CSE     1384    02              3
CSE     4503    01              1
  

Затем нам нужно найти вместимость комнаты:

 select c.*,l.capacity
from courseoffering_of c
join location l
on c.building = l.building
and c.room = l.room
  

Результат:

 symbol  number  building    room    sectionNumber   capacity
CSE     4503    Butler      100     01              5
CSE     1384    Butler      103     01              2
CSE     1384    Butler      103     02              2
ECE     4713    Old Main    2830    01              6
  

Теперь пришло время объединить запросы:

 select c.*,l.capacity
,a.count_enrolled
from courseoffering_of c
join location l
on c.building = l.building
and c.room = l.room
join 
(select symbol,number,sectionNumber, count(*) as count_enrolled
from enrolled
group by symbol,number,sectionNumber) a
on c.symbol = a.symbol
and c.number = a.number
and c.sectionNumber = a.sectionNumber
  

Вы получите:

 symbol      number  building    room    sectionNumber   capacity count_enrolled
CSE         4503    Butler      100     01              5           1
CSE         1384    Butler      103     02              2           3
  

Наконец, выберите полные курсы.

 select * from (
select c.*,l.capacity
,a.count_enrolled
from courseoffering_of c
join location l
on c.building = l.building
and c.room = l.room
join 
(select symbol,number,sectionNumber, count(*) as count_enrolled
from enrolled
group by symbol,number,sectionNumber) a
on c.symbol = a.symbol
and c.number = a.number
and c.sectionNumber = a.sectionNumber)b
where b.capacity <= b.count_enrolled
  

Вот результат теста:
DB<>Скрипка

Попробуйте это:

 SELECT c.symbol, c.number, c.sectionNumber FROM
courseoffering_of c
INNER JOIN
location l
ON
l.building = c.building
AND
l.room = c.room
INNER JOIN
enrolled e
ON
e.number = c.number
AND
e.sectionNumber = c.sectionNumber
GROUP BY c.symbol, c.number, c.sectionNumber, l.capacity 
HAVING count(*) >= l.capacity
  

 You can try the following query

    with CTE_temp1 as
     (
      select symbol, number, count(number) Total_student, sectionNumber from enrolled
      group by symbol, number, sectionnumber
     )

    ,CTE_temp2 as
     (
      select a.symbol, a.number, a.building, a.room, a.sectionNumber, b.capacity
      from course_offering_of a inner join location b
      on a.location = b.location
      and a.room = b.room 
     )


     select a.symbol, a.number, a.building, a.room, a.sectionNumber 
     from CTE_temp2 a inner join CTE_temp1 b 
     on a.symbol = b.symbol
     and a.number = b.number
     and a.sectionNumber = b.sectionNumber
     where a.capacity = b.Total_student