db: sql server
建库 建表 插入测试数据的sql 语句如下:
create database test
use testcreate table classes(cid int not null primary key, cname varchar(10) not null)
create table students(sid int not null primary key, sname varchar(10) not null, cid int not null foreign key references classes(cid))
create table course(id int not null primary key, cname varchar(10) not null, int not null, sid int not null foreign key references students(sid))
insert into classes values(101,'101')
insert into classes values(102,'102')
insert into classes values(103,'103')insert into students values(1,'andy',101)
insert into students values(2,'艾伦',101)
insert into students values(3,'Bruce',102)
insert into students values(4,'小明',102)
insert into students values(5,'杰克',103)
insert into students values(6,'Rose',103)insert into course values(1,'语文',99,1)
insert into course values(2,'语文',89,2)
insert into course values(3,'数学',99,5)
insert into course values(4,'数学',89,6)
insert into course values(5,'外语',99,4)
insert into course values(6,'外语',100,3)select * from classes
select * from students
select * from course
问题:
如何编写sql语句,查询各科分数最高的学生的信息,学科信息,班级信息
请高手指导一下,谢谢了
建库 建表 插入测试数据的sql 语句如下:
create database test
use testcreate table classes(cid int not null primary key, cname varchar(10) not null)
create table students(sid int not null primary key, sname varchar(10) not null, cid int not null foreign key references classes(cid))
create table course(id int not null primary key, cname varchar(10) not null, int not null, sid int not null foreign key references students(sid))
insert into classes values(101,'101')
insert into classes values(102,'102')
insert into classes values(103,'103')insert into students values(1,'andy',101)
insert into students values(2,'艾伦',101)
insert into students values(3,'Bruce',102)
insert into students values(4,'小明',102)
insert into students values(5,'杰克',103)
insert into students values(6,'Rose',103)insert into course values(1,'语文',99,1)
insert into course values(2,'语文',89,2)
insert into course values(3,'数学',99,5)
insert into course values(4,'数学',89,6)
insert into course values(5,'外语',99,4)
insert into course values(6,'外语',100,3)select * from classes
select * from students
select * from course
问题:
如何编写sql语句,查询各科分数最高的学生的信息,学科信息,班级信息
请高手指导一下,谢谢了
,c.cname as coursename
,a.cname
,c.
from classes a
,students b
,course c
where c.sid=b.sid
and b.cid=a.cid
and not exists (
select 1
from students b1
,course c1
where c1.sid = b1.sid
and c1.cname = c.cname
and b1.cid = b.cid
and c1. > c.
)
from course a inner join
(select cname, max() as from course group by cname) b
on a.cname=b.cname and a.=b.
inner join students c on a.sid=c.sid
inner join classes d on c.cid=d.cid
---------- ---------- ---------- -----------
101 andy 语文 99
102 Bruce 外语 100
103 杰克 数学 99(所影响的行数为 3 行)