Максимум средних оценок
Задание такое:
1) There are two tables that were created by scripts below; you are tasked to write the SQL query that displays a list of students who have the maximum average score. Please, provide two solutions with the following limitations
a) You can use neither subqueries nor cursors nor additional tables
b) You can use neither subqueries nor cursors nor additional tables nor GROUP BY clause
create table Students
(ID int identity(1,1) primary key not null,
StudentName varchar(200) not null,
);
create table StudentMarks
(ID int identity (1,1) primary key not null,
Student_ID int not null references Students (ID),
Mark int not null check (mark between 1 and 5));
insert into Students
(StudentName)
values
('Ivanov'),
('Petrov'),
('Sydorov'),
('Semenov');
insert into StudentMarks
(Student_ID,Mark)
values
(1,3), (1,4), (1,3), (1,5),
(2,3), (2,4), (2,3), (2,5),
(3,3), (3,3), (3,5), (3,3)
Очень надо!
подсказка:
в первом случае используйте агрегатные функции+group by, во втором агрегатные функции над оконнмыми avg() over(partition by) и т.д.
Спасибо!
PHP, JavaScript, XML , MySQL,
Вы странный раздел выбрали для своего вопроса. Вам в раздел веб скорее всего..