㈠ SQL数据库高手来~~悬赏
--1.新建一个名为school的数据库。
create database school
go
use school
go
create table student--学生信息表(student)
(
stu_no varchar(50) primary key,
stu_name varchar(100) ,
stu_sex varchar(2),
stu_phone varchar(200),
stu_email varchar(500)
)
go
insert into student values('001','张三','男','111','[email protected]')
insert into student values('002','李四','男','222','[email protected]')
insert into student values('003','王五','女','333','[email protected]')
insert into student values('004','赵六','女','444','[email protected]')
insert into student values('005','钱七','男','555','[email protected]')
go
create table teacher
(
tea_no varchar(50) primary key,
tea_name varchar(100),
tea_sex varchar(2),
tea_phone varchar(200),
tea_email varchar(500)
)
go
insert into teacher values('001','冯老师','女','123','[email protected]')
insert into teacher values('002','刘老师','男','321','[email protected]')
go
create table course--课程表(course)
(
cor_id int primary key,
cor_name varchar(100),
tea_no varchar(50) foreign key references teacher(tea_no)
)
go
insert into course values(991,'数据库','001')
insert into course values(992,'操作系统','002')
go
create table choose--选课表(choose)
(
ch_id int identity primary key,
stu_no varchar(50) foreign key references student(stu_no),
cor_id int foreign key references course(cor_id)
)
go
insert into choose values('001',991)
insert into choose values('001',992)
insert into choose values('002',991)
insert into choose values('002',992)
insert into choose values('003',991)
insert into choose values('004',992)
go
--1.选出教师信息表(teacher)所有数据。（10分）
select * from teacher
--2.选出前三条的学生信息数据。（10分）
select top 3* from student order by stu_no
--3.选出张三同学选修的所有课程名称。（10分）
select cor_name as '张三同学选修的所有课程名称' from course where cor_id in (select cor_id from choose where stu_no=(select stu_no from student where stu_name = '张三'))
--4.选出没有选课的学生。（10分）
select * from student where stu_no not in(select stu_no from choose)
--5.选出每个学生及他们选课的门数。（10分）
select student.stu_no,student.stu_name,student.stu_sex,student.stu_phone,student.stu_email,isnull(coun,0) from student left join (select stu_no,count(*)as coun from choose group by stu_no) as nes on student.stu_no = nes.stu_no
select stu_no,count(*) from choose group by stu_no
--6.选出冯老师所教的所有学生。（10分）
select * from student where stu_no in(select stu_no from choose where cor_id in(select cor_id from course where tea_no=(select tea_no from teacher where tea_name = '冯老师')))
可以复制到数据库 运行