数据库练习sql超详细（期末不挂科系列）

一、设计题：按以下要求用代码完成数据库及表的创建。本大题共3个小题，共40分。

 

1.创建一个数据库，数据库的名称以学生自己姓名命名。（4分）

CREATE DATABASE 陆小马 
ON PRIMARY (
	NAME =陆小马_mdf,
	FILENAME = 'C:\陆小马_mdf.MDF',
	SIZE = 15MB,
	MAXSIZE = 30MB,
	FILEGROWTH = 20 %) 
LOG ON ( 
	NAME =陆小马_ldf, 
	FILENAME = 'C:\陆小马_ldf.LDF', 
	SIZE = 3MB, 
	MAXSIZE = 10MB, 
	FILEGROWTH = 1MB )

2.创建如下3个表，要求包含完整性约束的定义（主键、外键、性别等的约束）。

（1）学生（学号，姓名，性别，专业班级，出生日期，联系电话），设置学号为主键，给性别添加检查约束，取值范围为下表所示。（10分）

学号	姓名	性别	专业班级	出生日期	联系电话
0433	张艳	女	生物04	1986-9-13
0496	李越	男	电子04	1984-2-23	1381290××××
0529	赵欣	男	会计05	1984-1-27	1350222××××
0531	张志国	男	生物05	1986-9-10	1331256××××
0538	于兰兰	女	生物05	1984-2-20	1331200××××

（2） 课程（课程号，课程名，学分数，学时数，任课教师），设置课程编号为主键，将学分设置为默认值3。（10分）

课程号	课程名	学分数	学时数	任课教师
K001	计算机图形学	2.5	40	胡晶晶
K002	计算机应用基础	3	48	任泉
K006	数据结构	4	64	马跃先
M001	政治经济学	4	64	孔繁新
S001	高等数学	3	48	赵晓尘

（3）作业成绩（课程号，学号，作业1成绩，作业2成绩，作业3成绩），设置学号、课程号为作业成绩表的组合主键，成绩检查约束范围为0-100,并与学生表和课程表建立外键关系。（10分）

课程号	学号	作业1成绩	作业2成绩	作业3成绩
K001	0433	60	75	75
K001	0529	70	70	60
K001	0531	70	80	80
K001	0591	80	90	90
K002	0496	80	80	90
K002	0529	70	70	85

use 陆小马
create table 学生(学号 char(6) primary key,
				姓名 varchar(30),
				性别 char(2) check(性别 in('男','女') ),
				专业班级 char(30),
				出生日期 datetime,
				联系电话 char(20))
create table 课程(课程号 char(6) primary key,
				课程名 varchar(30),
				学分数 float default 3,
				学时数 int,
				任课教师 char(20))

create table 作业成绩(课程号 char(6),
				学号 char(6),
				作业1成绩 float,
				作业2成绩 float,
				作业3成绩 float,
constraint c1 primary key(学号,课程号),
constraint c2 check(作业1成绩 between 0 and 100),
constraint c3 check(作业2成绩 between 0 and 100),
constraint c4 check(作业3成绩 between 0 and 100),
constraint c5 foreign key (学号) references 学生(学号),
constraint c6 foreign key (课程号) references 课程(课程号))

3、完成表数据的录入，每个表中至少有一条数据要求用代码完成。（6分）

insert into 学生
values('0433','张艳','女','生物04','1986-9-13','')
insert into 课程
values('k001','计算机图形学',2.5,'40','胡晶晶')
insert into 作业成绩
values('k001','0433','60','75','75')

二、SQL语句题：用SQL语句完成。本大题共11个小题，共60分。

 

1.查询各位学生的学号、班级和姓名。（5分）

select 学号,专业班级,姓名
from 学生

2.查询学时数大于60的课程信息。（5分）

select *
from 课程
where 学时数>60

3.查询三次作业的成绩都在80分以上的学号、课程号。（5分）

SELECT `作业成绩`.`课程号`,`作业成绩`.`学号`
FROM `作业成绩`
WHERE `作业成绩`.`作业1成绩`>=80
AND `作业成绩`.`作业2成绩`>=80
AND `作业成绩`.`作业3成绩`>=80

4．查询名字的第二个字是伟的学生的学号、姓名和专业班级，并按学号升序排列。（5分）

SELECT
	`学生`.`学号`, 
	`学生`.`姓名`, 
	`学生`.`专业班级`
FROM
	`学生`
WHERE
	`学生`.`姓名` LIKE '_伟%'
ORDER BY `学生`.`学号` ASC

5．查询选修了K001课程的学生人数。（5分）

SELECT COUNT(`作业成绩`.`课程号`)	as 学生人数
FROM `作业成绩`
GROUP BY `作业成绩`.`课程号` HAVING `作业成绩`.`课程号`='k001' 

6.将数据结构的学分数变为原来的两倍。（5分）

UPDATE `课程`
SET `课程`.`学分数`=`课程`.`学分数`*2
WHERE `课程`.`课程名`='数据结构'

7.查询于兰兰的选课信息，列出学号、姓名、课程名。（5分）

SELECT `学生`.学号,姓名,课程名
FROM `学生`,`课程`,`作业成绩`
WHERE `学生`.学号=`作业成绩`.`学号`
AND `课程`.`课程号`=`作业成绩`.`课程号`
AND `学生`.姓名='于兰兰'

8.查询与“张志国”同一班级的学生信息。（5分）

SELECT *
FROM `学生`
WHERE `学生`.专业班级=(
SELECT `学生`.专业班级
FROM `学生`
WHERE `学生`.姓名='张志国'
)

9.查询比“计算机应用基础”学时多的课程信息。（5分）

SELECT *
FROM 课程
WHERE `课程`.`学时数`>(
SELECT `课程`.`学时数`
FROM 课程
WHERE `课程`.`课程名`='计算机应用基础'
)

10.创建一个生物05的学生作业情况视图（包括学号、姓名、课程名、作业1成绩、作业2成绩、作业3成绩）。（8分）

CREATE VIEW 生物05学生作业情况 AS
SELECT 学生.学号,
		姓名,
		课程名,
		作业1成绩,
		作业2成绩,
		作业3成绩
FROM 学生,课程,作业成绩
WHERE 学生.学号=作业成绩.学号
AND 课程.课程号=作业成绩.课程号
AND 作业成绩.学号 IN (SELECT 学号 FROM 学生 WHERE 专业班级='生物05')

11. 创建一个学生作业平均成绩视图（包括学号、作业1平均成绩、作业2平均成绩、作业3平均成绩）。（7分）

CREATE VIEW `学生作业平均成绩` AS 
SELECT
	`作业成绩`.`学号`, 
	AVG(`作业成绩`.`作业1成绩`), 
	AVG(`作业成绩`.`作业2成绩`), 
	AVG(`作业成绩`.`作业3成绩`)
FROM `学生`
INNER JOIN `作业成绩` ON  `学生`.`学号` = `作业成绩`.`学号`
INNER JOIN `课程` ON  `课程`.`课程号` = `作业成绩`.`课程号`;