|
1,、現(xiàn)在有三個表格 學(xué)生表(學(xué)號，姓名) 課程表(課程號，課程名) 選課表(學(xué)號,，課程號) 2,、編寫sql語句創(chuàng)建三個表格 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、向表格中插入數(shù)據(jù) insert into student values('小張') insert into student values('小李') insert into student values('小王') insert into course values('數(shù)據(jù)結(jié)構(gòu)') 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,、查詢出選課門數(shù)大于2門的學(xué)生的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)
|
|
|
