#mysql #sql
#mysql #sql
Вопрос:
В настоящее время мне нужно сделать следующее:
Найдите имена всех курсов и студентов, зачисленных на них, которые проходят в пятницу днем, и на них записано не менее 2 студентов.
Я думаю, что мне нужно объединить таблицы под названием student, takes, course, section и time_slot вместе и оттуда выполнить SQL-запрос, но мне это кажется излишним.
Сначала я бы получил список всех курсов и всех имен студентов, которые проходят курсы, объединив таблицы takes и students .
Затем я бы нашел все курсы, которые проводятся в пятницу днем, используя time_slot_id, который должен быть «D» ИЛИ «F» ИЛИ «G», а день будет «F» для обозначения пятницы. Тогда я бы сопоставил временной интервал с разделом.
После этого я застрял.
Вот что я пробовал:
SELECT name, course_ID FROM takes JOIN student ON takes.id = student.ID WHERE count(course_ID) > 1;
и
SELECT course_id
FROM takes JOIN student ON takes.id = student.ID
GROUP BY course_id
HAVING COUNT(*) > 1;
Нижний запрос работает, но не содержит имен учащихся, а верхний намного ближе к тому, что мне нужно, но возвращает ошибку.
Вот полный код MySQL, который у меня есть.
CREATE TABLE time_slot(
time_slot_id VARCHAR(100) NOT NULL,
day VARCHAR(100) NOT NULL,
start_hour TIME NOT NULL,
start_min TIME NOT NULL,
end_hour TIME NOT NULL,
end_min TIME NOT NULL,
PRIMARY KEY (time_slot_id, day, start_hour, start_min)
);
CREATE TABLE department(
dept_name VARCHAR(100) NOT NULL,
building VARCHAR(100) NOT NULL,
budget DECIMAL(10, 2) UNSIGNED,
PRIMARY KEY(dept_name)
);
CREATE TABLE student(
ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
dept_name VARCHAR(100) NOT NULL,
tot_cred SMALLINT NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);
CREATE TABLE instructor(
ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
dept_name VARCHAR(100) NOT NULL,
salary DECIMAL(10, 2) UNSIGNED NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);
CREATE TABLE course(
course_id VARCHAR(100) NOT NULL,
title VARCHAR(255) NOT NULL,
dept_name VARCHAR(100) NOT NULL,
cedits SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (course_id),
FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);
CREATE TABLE prereq(
course_id VARCHAR(100) NOT NULL,
prereq_id VARCHAR(100) NOT NULL,
PRIMARY KEY(course_id, prereq_id),
FOREIGN KEY(course_id) REFERENCES course(course_id)
);
CREATE TABLE classroom(
building VARCHAR(100) NOT NULL,
room_no VARCHAR(100) NOT NULL,
capacity SMALLINT NOT NULL,
PRIMARY KEY (building, room_no)
);
CREATE TABLE section(
course_id VARCHAR(100) NOT NULL,
sec_id VARCHAR(100) NOT NULL,
semester VARCHAR(100) NOT NULL,
year SMALLINT UNSIGNED NOT NULL,
building VARCHAR(100) NOT NULL,
room_no VARCHAR(100) NOT NULL,
time_slot_id VARCHAR(100) NOT NULL,
PRIMARY KEY (course_id, sec_id, semester, year),
FOREIGN KEY (course_id) REFERENCES course(course_id),
FOREIGN KEY (building,room_no) REFERENCES classroom(building,room_no),
FOREIGN KEY (time_slot_id) REFERENCES time_slot(time_slot_id)
);
CREATE TABLE takes(
ID INT UNSIGNED,
course_id VARCHAR(100) NOT NULL,
sec_id VARCHAR(100) NOT NULL,
semester VARCHAR(100) NOT NULL,
year SMALLINT UNSIGNED NOT NULL,
grade VARCHAR(100),
PRIMARY KEY (ID, course_id, sec_id, semester, year),
FOREIGN KEY (ID) REFERENCES student(ID),
FOREIGN KEY (course_id,sec_id,semester,year) REFERENCES section(course_id,sec_id,semester,year)
);
CREATE TABLE teaches(
ID INT UNSIGNED,
course_id VARCHAR(100),
sec_id VARCHAR(100),
semester VARCHAR(100),
year SMALLINT UNSIGNED,
PRIMARY KEY(ID, course_id, sec_id, semester, year),
FOREIGN KEY(ID) REFERENCES instructor(ID),
FOREIGN KEY (course_id,sec_id,semester,year) REFERENCES section(course_id,sec_id,semester,year)
);
CREATE TABLE advisor(
s_id INT UNSIGNED NOT NULL,
i_id INT UNSIGNED NOT NULL,
PRIMARY KEY (s_id),
FOREIGN KEY (s_id) REFERENCES student(ID),
FOREIGN KEY (i_id) REFERENCES instructor(ID)
);
insert into classroom values ('Packard', '101', '500');
insert into classroom values ('Painter', '514', '10');
insert into classroom values ('Taylor', '3128', '70');
insert into classroom values ('Watson', '100', '30');
insert into classroom values ('Watson', '120', '50');
insert into department values ('Biology', 'Watson', '90000');
insert into department values ('Comp. Sci.', 'Taylor', '100000');
insert into department values ('Elec. Eng.', 'Taylor', '85000');
insert into department values ('Finance', 'Painter', '120000');
insert into department values ('History', 'Painter', '50000');
insert into department values ('Music', 'Packard', '80000');
insert into department values ('Physics', 'Watson', '70000');
insert into course values ('BIO-101', 'Intro. to Biology', 'Biology', '4');
insert into course values ('BIO-301', 'Genetics', 'Biology', '4');
insert into course values ('BIO-399', 'Computational Biology', 'Biology', '3');
insert into course values ('CS-101', 'Intro. to Computer Science', 'Comp. Sci.', '4');
insert into course values ('CS-190', 'Game Design', 'Comp. Sci.', '4');
insert into course values ('CS-315', 'Robotics', 'Comp. Sci.', '3');
insert into course values ('CS-319', 'Image Processing', 'Comp. Sci.', '3');
insert into course values ('CS-347', 'Database System Concepts', 'Comp. Sci.', '3');
insert into course values ('EE-181', 'Intro. to Digital Systems', 'Elec. Eng.', '3');
insert into course values ('FIN-201', 'Investment Banking', 'Finance', '3');
insert into course values ('HIS-351', 'World History', 'History', '3');
insert into course values ('MU-199', 'Music Video Production', 'Music', '3');
insert into course values ('PHY-101', 'Physical Principles', 'Physics', '4');
insert into instructor values ('10101', 'Srinivasan', 'Comp. Sci.', '65000');
insert into instructor values ('12121', 'Wu', 'Finance', '90000');
insert into instructor values ('15151', 'Mozart', 'Music', '40000');
insert into instructor values ('22222', 'Einstein', 'Physics', '95000');
insert into instructor values ('32343', 'El Said', 'History', '60000');
insert into instructor values ('33456', 'Gold', 'Physics', '87000');
insert into instructor values ('45565', 'Katz', 'Comp. Sci.', '75000');
insert into instructor values ('58583', 'Califieri', 'History', '62000');
insert into instructor values ('76543', 'Singh', 'Finance', '80000');
insert into instructor values ('76766', 'Crick', 'Biology', '72000');
insert into instructor values ('83821', 'Brandt', 'Comp. Sci.', '92000');
insert into instructor values ('98345', 'Kim', 'Elec. Eng.', '80000');
insert into time_slot values ('A', 'M', '8', '0', '8', '50');
insert into time_slot values ('A', 'W', '8', '0', '8', '50');
insert into time_slot values ('A', 'F', '8', '0', '8', '50');
insert into time_slot values ('B', 'M', '9', '0', '9', '50');
insert into time_slot values ('B', 'W', '9', '0', '9', '50');
insert into time_slot values ('B', 'F', '9', '0', '9', '50');
insert into time_slot values ('C', 'M', '11', '0', '11', '50');
insert into time_slot values ('C', 'W', '11', '0', '11', '50');
insert into time_slot values ('C', 'F', '11', '0', '11', '50');
insert into time_slot values ('D', 'M', '13', '0', '13', '50');
insert into time_slot values ('D', 'W', '13', '0', '13', '50');
insert into time_slot values ('D', 'F', '13', '0', '13', '50');
insert into time_slot values ('E', 'T', '10', '30', '11', '45');
insert into time_slot values ('E', 'R', '10', '30', '11', '45');
insert into time_slot values ('F', 'T', '14', '30', '15', '45');
insert into time_slot values ('F', 'R', '14', '30', '15', '45');
insert into time_slot values ('G', 'M', '16', '0', '16', '50');
insert into time_slot values ('G', 'W', '16', '0', '16', '50');
insert into time_slot values ('G', 'F', '16', '0', '16', '50');
insert into time_slot values ('H', 'W', '10', '0', '12', '30');
insert into section values ('BIO-101', '1', 'Summer', '2009', 'Painter', '514', 'B');
insert into section values ('BIO-301', '1', 'Summer', '2010', 'Painter', '514', 'A');
insert into section values ('CS-101', '1', 'Fall', '2009', 'Packard', '101', 'H');
insert into section values ('CS-101', '1', 'Spring', '2010', 'Packard', '101', 'F');
insert into section values ('CS-190', '1', 'Spring', '2009', 'Taylor', '3128', 'E');
insert into section values ('CS-190', '2', 'Spring', '2009', 'Taylor', '3128', 'A');
insert into section values ('CS-315', '1', 'Spring', '2010', 'Watson', '120', 'D');
insert into section values ('CS-319', '1', 'Spring', '2010', 'Watson', '100', 'B');
insert into section values ('CS-319', '2', 'Spring', '2010', 'Taylor', '3128', 'C');
insert into section values ('CS-347', '1', 'Fall', '2009', 'Taylor', '3128', 'A');
insert into section values ('EE-181', '1', 'Spring', '2009', 'Taylor', '3128', 'C');
insert into section values ('FIN-201', '1', 'Spring', '2010', 'Packard', '101', 'B');
insert into section values ('HIS-351', '1', 'Spring', '2010', 'Painter', '514', 'C');
insert into section values ('MU-199', '1', 'Spring', '2010', 'Packard', '101', 'D');
insert into section values ('PHY-101', '1', 'Fall', '2009', 'Watson', '100', 'A');
insert into teaches values ('10101', 'CS-101', '1', 'Fall', '2009');
insert into teaches values ('10101', 'CS-315', '1', 'Spring', '2010');
insert into teaches values ('10101', 'CS-347', '1', 'Fall', '2009');
insert into teaches values ('12121', 'FIN-201', '1', 'Spring', '2010');
insert into teaches values ('15151', 'MU-199', '1', 'Spring', '2010');
insert into teaches values ('22222', 'PHY-101', '1', 'Fall', '2009');
insert into teaches values ('32343', 'HIS-351', '1', 'Spring', '2010');
insert into teaches values ('45565', 'CS-101', '1', 'Spring', '2010');
insert into teaches values ('45565', 'CS-319', '1', 'Spring', '2010');
insert into teaches values ('76766', 'BIO-101', '1', 'Summer', '2009');
insert into teaches values ('76766', 'BIO-301', '1', 'Summer', '2010');
insert into teaches values ('83821', 'CS-190', '1', 'Spring', '2009');
insert into teaches values ('83821', 'CS-190', '2', 'Spring', '2009');
insert into teaches values ('83821', 'CS-319', '2', 'Spring', '2010');
insert into teaches values ('98345', 'EE-181', '1', 'Spring', '2009');
insert into student values ('00128', 'Zhang', 'Comp. Sci.', '102');
insert into student values ('12345', 'Shankar', 'Comp. Sci.', '32');
insert into student values ('19991', 'Brandt', 'History', '80');
insert into student values ('23121', 'Chavez', 'Finance', '110');
insert into student values ('44553', 'Peltier', 'Physics', '56');
insert into student values ('45678', 'Levy', 'Physics', '46');
insert into student values ('54321', 'Williams', 'Comp. Sci.', '54');
insert into student values ('55739', 'Sanchez', 'Music', '38');
insert into student values ('70557', 'Snow', 'Physics', '0');
insert into student values ('76543', 'Brown', 'Comp. Sci.', '58');
insert into student values ('76653', 'Aoi', 'Elec. Eng.', '60');
insert into student values ('98765', 'Bourikas', 'Elec. Eng.', '98');
insert into student values ('98988', 'Tanaka', 'Biology', '120');
insert into takes values ('00128', 'CS-101', '1', 'Fall', '2009', 'A');
insert into takes values ('00128', 'CS-347', '1', 'Fall', '2009', 'A-');
insert into takes values ('12345', 'CS-101', '1', 'Fall', '2009', 'C');
insert into takes values ('12345', 'CS-190', '2', 'Spring', '2009', 'A');
insert into takes values ('12345', 'CS-315', '1', 'Spring', '2010', 'A');
insert into takes values ('12345', 'CS-347', '1', 'Fall', '2009', 'A');
insert into takes values ('19991', 'HIS-351', '1', 'Spring', '2010', 'B');
insert into takes values ('23121', 'FIN-201', '1', 'Spring', '2010', 'C ');
insert into takes values ('44553', 'PHY-101', '1', 'Fall', '2009', 'B-');
insert into takes values ('45678', 'CS-101', '1', 'Fall', '2009', 'F');
insert into takes values ('45678', 'CS-101', '1', 'Spring', '2010', 'B ');
insert into takes values ('45678', 'CS-319', '1', 'Spring', '2010', 'B');
insert into takes values ('54321', 'CS-101', '1', 'Fall', '2009', 'A-');
insert into takes values ('54321', 'CS-190', '2', 'Spring', '2009', 'B ');
insert into takes values ('55739', 'MU-199', '1', 'Spring', '2010', 'A-');
insert into takes values ('76543', 'CS-101', '1', 'Fall', '2009', 'A');
insert into takes values ('76543', 'CS-319', '2', 'Spring', '2010', 'A');
insert into takes values ('76653', 'EE-181', '1', 'Spring', '2009', 'C');
insert into takes values ('98765', 'CS-101', '1', 'Fall', '2009', 'C-');
insert into takes values ('98765', 'CS-315', '1', 'Spring', '2010', 'B');
insert into takes values ('98988', 'BIO-101', '1', 'Summer', '2009', 'A');
insert into takes values ('98988', 'BIO-301', '1', 'Summer', '2010', null);
insert into advisor values ('00128', '45565');
insert into advisor values ('12345', '10101');
insert into advisor values ('23121', '76543');
insert into advisor values ('44553', '22222');
insert into advisor values ('45678', '22222');
insert into advisor values ('76543', '45565');
insert into advisor values ('76653', '98345');
insert into advisor values ('98765', '98345');
insert into advisor values ('98988', '76766');
insert into prereq values ('BIO-301', 'BIO-101');
insert into prereq values ('BIO-399', 'BIO-101');
insert into prereq values ('CS-190', 'CS-101');
insert into prereq values ('CS-315', 'CS-101');
insert into prereq values ('CS-319', 'CS-101');
insert into prereq values ('CS-347', 'CS-101');
insert into prereq values ('EE-181', 'PHY-101');
Комментарии:
1. И как мы узнаем, что представляют «D», «F» или «G»?
2. D F G — это все time_slot_id . Например, D представляет урок, который начинается с 1300 и заканчивается на 1350
3. Я вижу, так что это все «дневные» слоты.
4. да, D, G, F — это все дневные слоты.
5. Ну, что произойдет, если вы начнете с таблицы разделов?
Ответ №1:
Хорошо … давайте создадим это, используя вашу стратегию.
Сначала я бы получил список всех курсов и всех имен студентов, которые проходят курсы, объединив таблицы takes и students .
Приведенный ниже запрос является правильным началом для этого — он находит все курсы, на которых обучается более одного студента.
SELECT course_id
FROM takes JOIN student ON takes.id = student.ID
GROUP BY course_id
HAVING COUNT(*) > 1;
Важным предостережением к вышесказанному является то, что не имеет значения, в каком семестре / etc студенты это делают — просто на курс было записано более одного человека в любое время, в любом временном интервале и т.д.
Чтобы получить другую информацию (названия курсов и студентов), вы просто свяжете это с таблицами course и takes amp; student, например,
SELECT c.title AS Course_Title, s.name AS Student_Name
FROM course AS c
INNER JOIN takes AS t ON c.course_id = t.course_id
INNER JOIN student AS s ON t.ID = s.ID
INNER JOIN
(SELECT course_id
FROM takes JOIN student ON takes.id = student.ID
GROUP BY course_id
HAVING COUNT(*) > 1
) AS courses_2plus_students ON c.course_id = courses_2plus_students.course_id;
Конечно, вы могли бы изменить приведенное выше, чтобы вложенный запрос находился в предложении WHERE или в CTE.
Обратите внимание, что существует более эффективный способ выполнения вышеуказанного с использованием оконных функций, но я предполагаю, что на данный момент это слишком далеко.
Однако, как вы сказали в своей стратегии — это не весь ответ — нам нужно отфильтровать курсы.
Затем я бы нашел все курсы, которые проводятся в пятницу днем, используя time_slot_id, который должен быть «D» ИЛИ «F» ИЛИ «G», а день будет «F» для обозначения пятницы. Тогда я бы сопоставил временной интервал с разделом.
Я предполагаю, что вы хотите, чтобы временные интервалы вычислялись, а не жестко кодировались (в случае, если вы добавляете новые временные интервалы, например, тот, который выполняется в понедельник, вторник, четверг утром). Таким образом, я определю «дневной» слот как тот, который начинается в 12:00 или после, но до 6:00 вечера.
Мы можем получить соответствующие временные интервалы с помощью следующего
SELECT DISTINCT ts.time_slot_id
FROM time_slot ts
WHERE ts.`day` = 'F'
AND ts.start_hour >= 12 and ts.start_hour < 18;
Приведенное выше должно привести к ‘D’ и ‘G’ (обратите внимание, что F не учитывается в соответствии с вашими данными). Я использовал DISTINCT в приведенном выше для удаления дубликатов в случае, если данный временной интервал имеет двойной класс, например, 2 класса в данный день.
Затем нам нужны соответствующие идентификаторы course_ids, которые мы получаем, присоединяясь к приведенному выше разделу to (и удаляя ссылку на time_slot_id, поскольку она нам больше не нужна).
SELECT DISTINCT sec.course_id
FROM section sec
INNER JOIN time_slot ts ON sec.time_slot_id = ts.time_slot_id
WHERE ts.`day` = 'F'
AND ts.start_hour >= 12 and ts.start_hour < 18;
Теперь все, что нам нужно сделать, это отфильтровать курсы сверху, используя разделы сверху. Итак, SQL для получения (начального) ответа…
SELECT c.title AS Course_Title, s.name AS Student_Name
FROM course AS c
INNER JOIN takes AS t ON c.course_id = t.course_id
INNER JOIN student AS s ON t.ID = s.ID
INNER JOIN
(SELECT course_id
FROM takes JOIN student ON takes.id = student.ID
GROUP BY course_id
HAVING COUNT(*) > 1
) AS courses_2plus_students ON c.course_id = courses_2plus_students.course_id
INNER JOIN
(SELECT DISTINCT sec.course_id
FROM section sec
INNER JOIN time_slot ts ON sec.time_slot_id = ts.time_slot_id
WHERE ts.`day` = 'F'
AND ts.start_hour >= 12 and ts.start_hour < 18
) AS Friday_afternoon_courses on c.course_id = Friday_afternoon_courses.course_id;
Еще раз, вы могли бы использовать WHERE или CTE etc вместо подзапроса.
Вот db<>fiddle с каждым из шагов, описанных выше. Обратите внимание, что первый раздел скрыт — он содержит ваши структуры данных и данные (но с заданным временем int прохождения, а не time ).
Примечание — и это важное замечание — вам нужно быть очень осторожным в отношении того, что это представляет.
- Курсы могут проводиться в разные временные интервалы и в разные семестры. Например,
CS-101поставляется осенью 2009 года в time_slot H; он также поставляется весной 2010 года с time_slot F. - Приведенный выше ответ не заботится об этом. Если в курсе обучается более одного студента (в любое время) и на одном этапе он был представлен в пятницу днем, тогда он соответствует требованиям. Учащиеся не обязательно будут в классе вместе в одно и то же время — или, действительно, у вас могут быть студенты, которые даже не посещали занятия в пятницу днем (например, они проходили курс в другом потоке / временном интервале).
Хотя мой ответ может дать первоначальный буквальный ответ на вопрос, более содержательная интерпретация вопроса («в каких классах в пятницу днем одновременно учится более 1 ученика?»), Вероятно, это то, что они спрашивают.
И поскольку это похоже на домашнее задание — я предлагаю, чтобы следующим шагом было изменение приведенного выше. Причина, по которой я выбрал подзапросы для выполнения фильтрации в приведенном выше ответе, заключается в том, что мне легче изменить then для другой фильтрации.
PS Вот подсказка о том, как / что изменить: приведенный выше ответ выполняет всю свою работу вокруг таблицы «курс», например, Он находит студентов по курсу, временные интервалы по курсу и т. Д. Вместо этого вы можете сосредоточиться на таблице ‘section’, поскольку она предоставляет данные о курсах в определенное время.
Комментарии:
1. Это отличный и хорошо продуманный ответ, который проведет меня (шаг за шагом) через мыслительный процесс, лежащий в основе построения SQL-запроса. Спасибо, что нашли время для его написания. Это очень ценится.