SQL 查询

1、现在有三个表格

学生表(学号，姓名)

课程表(课程号，课程名)

选课表(学号，课程号)

2、编写sql语句创建三个表格

create table student

(

  id int identity(1,1) primary key,

  name varchar(50)

)

create table course

(

   id int identity(1,1) primary key

)

create table stucou

(

   sid int,

   cid int,

   FOREIGN KEY (sid) REFERENCES student(id),

   foreign key (cid) references course(id)

)

alter table course add name varchar(50)

3、向表格中插入数据

insert into student values('小张')

insert into student values('小李')

insert into student values('小王')

insert into course values('数据结构')

insert into course values('C语言')

insert into stucou values(1,1)

insert into stucou values(1,2)

insert into stucou values(2,1)

insert into stucou values(2,2)

insert into stucou values(3,1)

4、查询出选课门数大于2门的学生的id和姓名

select id,name from student where id in(select sc.sid from stucou sc inner join course c on sc.cid=c.id group by sc.sid having COUNT(sc.cid)>1)