YY4350 SQL 课后习题 （持续更新）

小卢_铃

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

小卢_铃

6.
select Student.Stu_ID
from Student,SC
where Student.Stu_ID=SC.Stu_ID and SC.Course_ID='数据库' or Student.Stu_ID=SC.Stu_ID and SC.Course_ID='C语言’
group by Student.Stu_ID

我好闲啊