- 生肖
- 马
- 星座
- 天秤座
- 性别
- 女
- 积分
- 429
- 积分
- 1898
- 精华
- 11
- 阅读权限
- 150
- 注册时间
- 2013-6-6
- 最后登录
- 2017-4-11
- 帖子
- 366
- 生肖
- 马
- 星座
- 天秤座
- 性别
- 女
|
1.
create table Student
(
Stu_ID char(10),
Stu_Name char(8),
Sage int,
Ssex char(2),
Sdeptno int,
primary key(Stu_ID)
)
create table Course
(
Course_ID char(10),
Course_Name char(8),
Chours int,
primary key(Course_ID)
)
create table SC
(
Stu_ID char(10),
Course_ID char(10),
score float,
primary key(Stu_ID,Course_ID),
foreign key(Stu_ID) references Student(Stu_ID),
foreign key(Course_ID) references Course(Course_ID)
)
2.
select Stu_ID,Stu_Name,Course_ID,score
from Student,sc
where Sdeptno='o6'
3.
select Stu_ID,Stu_Name,Sage
from Student
where (Sage-3)=(select Sage from Student Stu_ID='S1')
4.
select Stu_Name
from Student
where not exists
(select Course_ID
from Course
where not exists
(select *
from SC
where SC.Course_ID =Course.Course_ID and
SC.Stu_ID=Student.Stu_ID )
)
5.
select Sdeptno,Student.Stu_ID,Stu_Name,min(score),max(score),avg(score),count(*)
from Student,SC
where Student.Stu_ID=SC.Stu_ID
group by Student.Stu_ID
having conut(*)>=10
order by Sdeptno,avg(score) desc |
|