数据库原理及其应用实验指导实验一（SQL语言-查询和更新操作）

1． 数据库、表的创建以及数据的插入
(1).建立数据库jxgl;
源代码：

CREATE DATABASE jxgl;

实验结果如下：

(2).创建Student表;
源代码：

CREATE TABLE Student(
	Sno CHAR(5) NOT NULL PRIMARY KEY(Sno),
	Sname VARCHAR(20),
	Sage SMALLINT CHECK(Sage > = 15 AND Sage < = 45),
	Ssex CHAR(2) DEFAULT '男' CHECK(Ssex = '男' OR Ssex = '女'),
	Sdept CHAR(2)
);

实验结果如下：

(3).创建Course表;
源代码：

CREATE TABLE Course(
	Cno CHAR(2) NOT NULL PRIMARY KEY(Cno),
	Cname VARCHAR(20),
	Cpno CHAR(2),
	Ccredit SMALLINT
);

实验结果如下：

(4).创建SC表;

CREATE TABLE SC(
	Sno CHAR(5) NOT NULL CONSTRAINT S_F FOREIGN KEY REFERENCES Student(Sno),
	Cno CHAR(2) NOT NULL,
	Grade SMALLINT CHECK((Grade IS NULL) OR (Grade BETWEEN 0 AND 100)),
	PRIMARY KEY(Sno, Cno),
	FOREIGN KEY(Cno) REFERENCES Course(Cno)
);

实验结果如下：

(5).Student表信息插入

INSERT INTO Student 
VALUES('98001','钱横',18,'男','CS'),
('98002','王林',19,'女','CS'),
('98003','李民',20,'男','IS'),
('98004','赵三',16,'女','MA');

实验结果如下：

(6).Course表信息插入

INSERT INTO Course 
VALUES('1','数据库系统','5',4),
('2','数学分析',null,2),
('3','信息系统导论','1',3),
('4','操作系统_原理','6',3),
('5','数据结构','7',4),
('6','数据处理基础',null,4),
('7','C语言','6',3);

实验结果如下：

(7).SC表信息插入

INSERT INTO SC 
VALUES('98001','1',87),
('98001','2',67),
('98001','3',90),
('98002','2',95),
('98002','3',88);

实验结果如下：

2． 基于“教学管理”数据库jxgl，试用SQL的查询语句表达下列查询
(1).检索年龄大于23岁的男学生的学号和姓名；
源代码：

SELECT Sno, Sname
FROM Student
WHERE Sage > 23 AND Ssex = '男';

实验结果如下：

(2).检索至少选修一门课程的女学生姓名；
源代码：

SELECT Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno AND Ssex = '女'
GROUP BY Sname
HAVING COUNT(Cno) >= 1;

实验结果如下：

(3).检索王同学不学的课程的课程号；
源代码：

SELECT Cno
FROM Course
WHERE Cno != ALL 
(SELECT Course.Cno 
FROM Student, SC,Course
WHERE (Sname LIKE '王%' AND Student.Sno = SC.Sno AND SC.Cno = Course.Cno));

实验结果如下：

(4).检索至少选修两门课程的学生学号；
源代码：

SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(Cno) >= 2;

实验结果如下：

(5).检索全部学生都选修的课程的课程号与课程名；
源代码：

SELECT Cno, Cname
FROM Course 
WHERE NOT EXISTS(
SELECT * FROM Student
	WHERE NOT EXISTS(
	SELECT * FROM SC
	WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno
	) 
);

实验结果如下：

(6).检索选修了所有3学分课程的学生学号。
源代码：

SELECT Sno
FROM Student
WHERE NOT EXISTS(
	SELECT * FROM Course
	WHERE Course.Ccredit = 3 AND NOT EXISTS(
		SELECT * FROM SC
		WHERE SC.Sno = Student.Sno AND SC.Cno = Course.Cno
));

实验结果如下：

3． 基于“教学管理”数据库jxgl，试用SQL的查询语句表达下列查询。
(1).统计所有学生选修的课程的课程号与课程名；
源代码：
SELECT COUNT(DISTINCT Cno) ‘所有学生选修课的课程门数’
FROM SC;
实验结果如下：

(2).求选修4号课程的学生的平均年龄；
源代码：

SELECT AVG(Sage) '选修4号课程的学生的平均年龄'
FROM Student, SC
WHERE Student.Sno = SC.Sno AND Cno = 4
GROUP BY Student.Sno;

实验结果如下：

(3).求学分为3的每门课程的学生平均成绩；
源代码：

SELECT AVG(Grade) '学分为3的每门课程的学生的平均成绩'
FROM SC, Course
WHERE SC.Cno = Course.Cno AND Course.Ccredit = 3
GROUP BY SC.Cno;

实验结果如下：

(4).统计每门课程的学生选修人数，超过三人的课程才统计，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列；
源代码：

SELECT Cno, COUNT(Sno) Num
FROM SC
GROUP BY Cno
HAVING COUNT(Sno) > 3
ORDER BY COUNT(Sno) DESC, Cno ASC;

实验结果如下：

(5).检索学号比王非同学大，而年龄比他小的学生姓名；
源代码：

SELECT Sname
FROM Student
WHERE Sno > (SELECT Sno FROM Student WHERE Sname = '王非')
AND Sage < (SELECT Sage FROM Student WHERE Sname = '王非');

实验结果如下：

(6).检索姓名以王打头的所有学生的姓名和年龄；
源代码：

SELECT Sname,Sage
FROM Student
WHERE Sname like '王%';

实验结果如下：

(7).在SC中检索成绩为空值的学生学号和课程号；
源代码：

SELECT Sno,Cno
FROM SC
WHERE Grade = null;

实验结果如下：

(8).求年龄大于女同学平均年龄的男同学的姓名和年龄；
源代码：

SELECT Sname, Sage
FROM Student
WHERE Ssex = '男' AND Sage > (SELECT AVG(Sage) FROM Student WHERE Ssex = '女');

实验结果如下：

(9).求年龄大于所有女同学年龄的男同学的姓名和年龄；
源代码：

SELECT Sname, Sage
FROM Student
WHERE Ssex = '男' AND Sage > ALL(SELECT Sage FROM Student WHERE Ssex = '女');

实验结果如下：

(10).检索所有比“王华”年龄大的学生姓名、年龄和性别；
源代码：

SELECT Sname, Sage, Ssex
FROM Student
WHERE Sage > (SELECT Sage FROM Student WHERE Sname = '王华');

实验结果如下：

(11).检索选修2号课程的学生中成绩最高的学生的学号;
源代码：

SELECT TOP(1) Student.Sno
FROM Student, SC
WHERE Cno = 2 AND Student.Sno = SC.Sno
ORDER BY SC.Grade DESC;

实验结果如下：

(12).检索学生姓名及其所选课程的课程号和成绩；
源代码：

SELECT Sname, Cno, Grade
FROM Student, SC
WHERE Student.Sno = SC.Sno;

实验结果如下：

(13).检索选修4门以上课程的学生总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来
源代码：

SELECT SUM(Grade) '总成绩'
FROM SC
WHERE Grade >= 60
GROUP BY Sno
HAVING COUNT(Cno) >= 4
ORDER BY '总成绩' DESC;

实验结果如下：