简单sql 语句
本帖最后由 u014799383 于 2014-05-05 23:23:32 编辑
数据库作业!
create database zuoye
create table Student
(
Sno nvarchar(9) primary key,
Sname nvarchar(20) unique,
Ssex char(2) ,
Ssage int,
Sdept nvarchar(20)
);
insert into Student values('95001','李勇','男','20','CS');
insert into Student values('95002','刘晨','女','19','IS');
insert into Student values('95003','王敏','女','18','MA');
insert into Student values('95004','张立','男','18','IS');
insert into Student values('95005','王强','男','17','IS');
insert into Student values('95015','张三','男','20','CS');
insert into Student values('95019','李四','男','20','CS');
insert into Student values('95020','陈冬','男','18','IS');
create table Course
(
Cno nvarchar(4) primary key,
Cname nvarchar(40),
Cpno nvarchar(4),
Ccredit int,
foreign key (Cpno) references Course(Cno)
);
insert into Course(Cno,Cname,Ccredit) values('1','数据库','4');
insert into Course(Cno,Cname,Ccredit) values('2','数学','2');
insert into Course(Cno,Cname,Ccredit) values('3','信息系统','4');
insert into Course(Cno,Cname,Ccredit) values('4','操作系统','3');
insert into Course(Cno,Cname,Ccredit) values('5','数据结构','4');
insert into Course(Cno,Cname,Ccredit) values('6','数据处理','2');
insert into Course(Cno,Cname,Ccredit) values('7','Pascal语言','4');
update Course set Cpno='5' where Cno='1'
update Course set Cpno='1' where Cno='3'
update Course set Cpno='6' where Cno='4'
update Course set Cpno='7' where Cno='5'
update Course set Cpno='6' where Cno='7'
create table SC
(
Sno nvarchar(9),
Cno nvarchar(4),
Grade int,
primary key (Sno,Cno),
foreign key (Sno) references Student(Sno),
foreign key (Cno) references Course(Cno)
)
insert into SC values('95001','1','65');
insert into SC values('95001','2','88');
insert into SC values('95001','3','57');
insert into SC values('95001','4','79');
insert into SC values('95001','5','45');
insert into SC values('95001','6','90');
insert into SC values('95001','7','81');
insert into SC values('95002','2','90');
insert into SC values('95002','3','80');
insert into SC values('95002','4','55');
insert into SC values('95003','2','0');
insert into SC values('95019','2','66');
insert into SC values('95020','1','');
/*查询所有课程的成绩都在80分以上的学生的姓名、学号、且按学号升序排列*/
select Sname,Sno
from Student join (select Sno,MIN(Grade) as MINI
from SC
group by Sno)
where MINI>80
order by (Sno)
出错,具体要怎么改??或者重新写!!大神,求指教!!