博客

表的定义：

-- 定义表

-- 学生信息表
create table Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Ssex CHAR(10),
Sbirth DATE,
Sdept CHAR(20),
Sgrade SMALLINT
);

-- 项目类别表
create table ProjectCategory
(Cno SMALLINT PRIMARY KEY,
Cname CHAR(20),
remark CHAR(20)
);

--  参赛项目表
create table Project
(Pno SMALLINT PRIMARY KEY,
Pname CHAR(20) UNIQUE,
Cno SMALLINT,
Pteacher CHAR(20) UNIQUE,
Pgrade SMALLINT,

foreign key (Cno) references ProjectCategory (Cno)
);

-- 项目成员表
create table Member
(Pno SMALLINT,
Mno SMALLINT,
Sno CHAR(9),
Mtask CHAR(20),
remark CHAR(20),

PRIMARY KEY(Pno,Mno),
foreign key (Pno) references Project (Pno),
foreign key (Sno) references Student (Sno)
);

SQL的查询练习：

-- 1.	自定数据，各表写出一条插入数据的语句。
insert into  [教学管理].[dbo].student(Sno,Sname,Ssex,Sbirth,Sdept,Sgrade)
	values ('0008','王八','女','2003-01-08','信安',3);

insert into  [教学管理].[dbo].projectcategory(Cno,Cname,remark)
	values (4,'信息安全类',null);

insert into  [教学管理].[dbo].project(Pno,Pname,Cno,Pteacher,Pgrade)
	values (4,'四号项目',4,'张老师',92);

insert into  [教学管理].[dbo].member(Pno,Mno,Sno,Mtask,remark)
	values (4,1,0007,'负责人',null);

-- 2.	查询全部项目类别名称。
select Cname
from projectcategory

-- 3.	查询参赛作品涉及的项目类别名称
select distinct Cname 
from project as a join projectcategory as b
	on a.Cno=b.Cno;

-- 4.	以自己的姓名为条件，查询所参与项目的名称、分工、项目类别、成绩。
select Sname,Pname,Mtask,Cname,Pgrade
from member join student on member.Sno=student.Sno
join project on member.Pno=project.Pno
join projectcategory on project.Cno=projectcategory.Cno
where Sname='周五';

-- 5.	按照项目成绩排序，查询项目名、项目负责人、指导教师、项目参与人数、成绩。 
select Pname,Sname '项目负责人',Pteacher,Pcount,Pgrade
from(
	member join (
		 select Pno,count(Mtask) Pcount
		 from member
		 group by Pno
	  )a on a.Pno=member.Pno
	join student on member.Sno=student.Sno
	join project on member.Pno=project.Pno
) 
where Mtask='负责人'
order by Pgrade DESC

-- 6.	创建视图，名称为C1，功能为统计全部参赛项目的项目数、参与人次数、实际参与项目的人数。
create view C1
as
select *
from((
		select count(*) Acount
		from(
			select Pno
			from member
			group by Pno
			) b
		) c
	join(
		select count(*) Pcount,count(Mtask) PScount
		from member
		) a 
)

-- 7.	将数据分析类项目的竞赛成绩增加1%。
update project
set Pgrade=Pgrade*(1.01)
where Cno=(
	select a.Cno
	from(
		select project.Cno
		from project join projectcategory on project.Cno=projectcategory.Cno
		where Cname='数据分析类'
		) a
)

-- 8.	删除没有参赛项目的项目类别。
delete
from projectcategory
where Cno not in(
	select Cno
	from project
)

备注：

工程学院组织学生参加计算机设计竞赛。竞赛管理数据库各关系表如下：

学生信息

学号，姓名，性别，生日，专业，年级

项目类别

类别编号，类别名称，说明

（注：项目类别如：编程类、数据分析类、媒体设计类、信息安全类等）

参赛项目

项目编号，项目名称，类别编号，指导教师，竞赛成绩

（注：成绩为百分制）

项目成员

项目编号，项目内序号，学号，任务分工，备注

（注：分工如：负责人、系统设计、UI设计、编程、分析员等。

一名学生可以参加多个项目，但最多只能在一个项目内担任负责人）

表的结构设计如下：

Student


表

Projectcategory


表

Project


表

Member


表