数据库建表

建表代码

CREATE TABLE Student
( 学号 CHAR(6)PRIMARY KEY,
  姓名 CHAR(12) NOT NULL,
  专业名 VARCHAR(20),
  性别 CHAR(2) NOT NULL,
  出生时间 SMALLDATETIME,
  总学分 INT,
  备注 TEXT
)

CREATE TABLE Course
(
 课程号 CHAR(4) PRIMARY KEY,
 课程名 varchar(40) NOT NULL,
 开课学期 int,
 学时 int,
 学分 int
)

create table StuCourse
(
  学号 char(6) not null,
  课程号 char(4) not null,
  成绩 real,
  primary key(学号,课程号),
  foreign key(学号)references Student(学号),
  foreign key(课程号) references Course(课程号)
 )
 go

查数据

SELECT Student.*,StuCourse.*
FROM Student ,StuCourse
WHERE Student.学号=StuCourse.学号;

select Student.姓名,StuCourse.*
from StuCourse,Student
where Student.学号 = StuCourse.学号 and Student.专业名='电子信息工程';  

join 方法

select s.姓名
from Student s
join StuCourse sc on s.学号 = sc.学号
join Course c on sc.课程号 = c.课程号
where c.课程名 = '程序设计基础';

SELECT s.学号,s.姓名,s.专业名,AVG(sc.成绩)as 平均成绩
FROM Student s
JOIN StuCourse sc ON S.学号=SC.学号
JOIN Course c ON C.课程号=SC.课程号
WHERE S.专业名='计算机科学与技术'
GROUP BY S.学号,S.姓名,S.专业名;

SELECT s.学号,s.姓名,AVG(sc.成绩)as 平均成绩
FROM Student s
JOIN StuCourse sc ON S.学号=SC.学号

GROUP BY S.学号,S.姓名
order by avg(sc.成绩)desc;

select distinct s.学号, s.姓名
from Student s
join StuCourse sc on s.学号 = sc.学号
where sc.课程号 in('2001','1002');

select distinct Student.学号,Student.姓名
from Student
where student.学号 in(
    select student.学号
	from stucourse
	where 课程号 in('2001','1002')
);

select 学号,姓名,专业名
from student
where 专业名 =
(select 专业名 from student where 姓名 = '李进');

select 专业名,count(*) as num_of_students
from(
  select s.学号,s.专业名,AVG(sc.成绩) as avg_score
  from student s
  join stucourse sc on s.学号 = sc.学号
  group by s.学号,s.专业名
  having avg(sc.成绩) >= 80
)as subquery
group by 专业名;

select s.姓名, s.学号
from student s
where s.学号 in (
 select sc.学号
 from stucourse sc 
 join course c on sc.课程号 = c.课程号
 where sc.成绩 >= 80
 group by sc.学号
 having count(distinct c.课程号) = ( select count(*) from stucourse 
 where  学号 = s.学号)
);

select 姓名 from student 
where 学号 not in ( select 学号 from stucourse where 课程号 = 1002);

select s.学号,s.姓名 from student s
join stucourse sc on s.学号 = sc.学号
where s.专业名 = '计算机科学与技术' 
and sc.课程号 = 1001 and sc.成绩 > all 
(
  select sc2.成绩 from stucourse sc2 join student s2 on sc2.学号=s2.学号
  where s2.专业名 = '电子信息工程' and 课程号 = 1001
);