数据库作业1
根据教材上的teach数据库（student、course、sc），录入测试数据（资料区提供有源码借鉴），上机验证书上第三章例3.16到例3.48的所有例程，分别给出代码，对应将运行结果截图形成一个附件提交。注意压缩一下截图的大小。
初始化
gs_om -t start /* 启动服务命令 */
gs_om -t status /* 查看服务是否启动 */
gsql -d postgres -p 26000 -r /* 连接数据库 */
CREATE USER testuser WITH PASSWORD 'Ycy021013'; /* 创建用户 */
GRANT ALL PRIVILEGES TO testuser; /* 设置testuser用户为系统管理员 */
CREATE DATABASE db_demo OWNER testuser; /* 创建数据库 */
gs_om -t start
gsql -d db_demo -p 26000 -r
测试数据
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY, /* 列级完整性约束条件*/
Sname CHAR(20) UNIQUE, /* Sname取唯一值*/
Ssex CHAR(3),
Sage SMALLINT,
Sdept CHAR(20)
);
CREATE TABLE Course
( Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4) ,
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);
CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno,Cno),
/* 主码由两个属性构成，必须作为表级完整性进行定义*/
FOREIGN KEY (Sno) REFERENCES Student(Sno),
/* 表级完整性约束条件，Sno是外码，被参照表是Student */
FOREIGN KEY (Cno) REFERENCES Course(Cno)
/* 表级完整性约束条件， Cno是外码，被参照表是Course*/
);
INSERT INTO Student VALUES ('200215126','张成民','男',18,'CS');
INSERT INTO Student VALUES ('200215127','王芳','女',17,'CS');
INSERT INTO Student VALUES ('200215128','李小清','女',18,'IS');
INSERT INTO Student VALUES ('200215129','赵先军','男',19,'CS');
INSERT INTO Student VALUES ('200215130','王思玉','女',18,'IS');
INSERT INTO Student VALUES ('200215131','张为民','男',18,'CS');
INSERT INTO Student VALUES ('200215132','刘贤','男',20,'CS');
INSERT INTO Student VALUES ('200215133','武城山','男',18,'CT');
INSERT INTO Student VALUES ('200215134','刘建国','男',18,'CT');
INSERT INTO Student VALUES ('200215135','田军','男',18,'CS');
INSERT INTO Course VALUES ('0204','离散数学',null,3);
INSERT INTO Course VALUES ('0203','计算机网络',null,3);
INSERT INTO Course VALUES ('0206','操作系统',null,4);
INSERT INTO Course VALUES ('0208','组成原理',null,2);
INSERT INTO Course VALUES ('0210','图形学',null,3);
INSERT INTO Course VALUES ('0207','面向对象程序设计',null,2);
INSERT INTO Course VALUES ('0202','数据结构','0204',4);
INSERT INTO Course VALUES ('0201','数据库','0202',3);
INSERT INTO Course VALUES ('0205','编译原理','0206',2);
INSERT INTO Course VALUES ('0209','软件工程','0201',2);
INSERT INTO Course VALUES ('1111','DB_Design',null,2);
INSERT INTO SC VALUES ('200215126','0204',80);
INSERT INTO SC VALUES ('200215126','0209',78);
INSERT INTO SC VALUES ('200215126','0210',90);
INSERT INTO SC VALUES ('200215127','0204',60);
INSERT INTO SC VALUES ('200215127','0210',50);
INSERT INTO SC VALUES ('200215127','0207',55);
INSERT INTO SC VALUES ('200215129','0202',98);
INSERT INTO SC VALUES ('200215130','0202',70);
INSERT INTO SC VALUES ('200215126','1111',null);
INSERT INTO SC VALUES ('200215131','0204',null);
例3.16 查询全体学生的学号与姓名
SELECT Sno,Sname FROM Student;
例3.17 查询全体学生的姓名、学号、所在系
SELECT Sname,Sno,Sdept FROM Student;
例3.18 查询全体学生的详细记录
SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student;
或
SELECT * FROM Student;
例3.19 查全体学生的姓名及其出生年份
SELECT Sname,2007-Sage FROM Student; /*假定当年的年份为2007年*/
例3.20 查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名
SELECT Sname,'Year of Birth:',2007-Sage,LOWER(Sdept) FROM Student;
例3.21 查询选修了课程的学生学号
SELECT Sno FROM SC;
等价于：
SELECT ALL Sno FROM SC;
例3.22 查询计算机科学系全体学生的名单
SELECT Sname FROM Student WHERE Sdept='CS';
例3.23 查询所有年龄在20岁以下的学生姓名及其年龄
SELECT Sname,Sage FROM Student WHERE Sage < 20;
例3.24 查询考试成绩有不及格的学生的学号
SELECT DISTINCT Sno FROM SC WHERE Grade<60;
例3.25 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄
SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23;
例3.26 查询年龄不在20~23岁之间的学生姓名、系别和年龄
SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23;
例3.27 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别
SELECT Sname,Ssex FROM Student WHERE Sdept IN ('IS','MA','CS');
例3.28 查询既不是信息系、数学系,也不是计算机科学系的学生的姓名和性别
SELECT Sname,Ssex FROM Student WHERE Sdept NOT IN ('IS','MA','CS');
例3.29 查询学号为200215126的学生的详细情况
SELECT * FROM Student WHERE Sno LIKE '200215126';
等价于：
SELECT * FROM Student WHERE Sno = '200215126';
例3.30 查询所有姓刘学生的姓名、学号和性别
SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE '刘%';
例3.31 查询姓"刘建"且全名为三个汉字的学生的姓名
SELECT Sname FROM Student WHERE Sname LIKE '刘建____________';
或
SELECT Sname FROM Student WHERE rtrim(Sname) LIKE '刘建_';
例3.32 查询名字中第2个字为"小"字的学生的姓名和学号
SELECT Sname,Sno FROM Student WHERE Sname LIKE '_小%';
例3.33 查询所有不姓刘的学生姓名
SELECT Sname,Sno,Ssex FROM Student WHERE Sname NOT LIKE '刘%';
例3.34 查询DB_Design课程的课程号和学分
SELECT Cno,Ccredit FROM Course WHERE rtrim(Cname) LIKE 'DB\_Design' ESCAPE '\';
查询"离散数学"课程的课程号和学分
SELECT Cno,Ccredit FROM Course WHERE rtrim(Cname) LIKE '离散数学';
例3.35 查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况
SELECT * FROM Course WHERE rtrim(Cname) LIKE 'DB\_%i__' ESCAPE '\';
查询以"离"开头，且倒数第2个字符为"数"的课程的详细情况
SELECT * FROM Course WHERE rtrim(Cname) LIKE '离_%数_';
例3.36 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号
SELECT Sno,Cno FROM SC WHERE Grade IS NULL;
例3.37 查所有有成绩的学生学号和课程号
SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL;
例3.38 查询计算机系年龄在20岁以下的学生姓名
SELECT Sname FROM Student WHERE Sdept= 'CS' AND Sage<20;
例3.39 查询选修了0204号课程的学生的学号及其成绩,查询结果按分数降序排列
SELECT Sno,Grade FROM SC WHERE Cno='0204' ORDER BY Grade DESC;
例3.40 查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列
SELECT * FROM Student ORDER BY Sdept,Sage DESC;
例3.41 查询学生总人数
SELECT COUNT(*) FROM Student;
例3.42 查询选修了课程的学生人数
SELECT COUNT(DISTINCT Sno) FROM SC;
例3.43 计算0204号课程的学生平均成绩
SELECT AVG(Grade) FROM SC WHERE Cno='0204';
例3.44 查询选修0204号课程的学生最高分数
SELECT MAX(Grade) FROM SC WHERE Cno='0204';
例3.45 查询学生200215012选修课程的总学分数
SELECT SUM(Ccredit) FROM SC,Course WHERE Sno='200215126' AND SC.Cno=Course.Cno;
例3.46 求各个课程号及相应的选课人数
SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno;
例3.47 查询选修了2门以上课程的学生学号
SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) >2;
例3.48 查询平均成绩大于等于90分的学生学号和平均成绩
下面的语句是不对的：
SELECT Sno,AVG(Grade) FROM SC WHERE AVG(Grade)>=90 GROUP BY Sno;
因为WHERE子句是不能用聚集函数作为条件表达式的，正确的查询语句应该是：
SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade)>=90;
遇到的问题及解决方法
1.问题：在查询姓"刘建"且全名为三个汉字的学生的姓名时，遇到了需要在姓名后加很多"_"的问题，这是因为学生姓名是char（20）类型，在刘建国后会有空格进行填充
2.解决方法：
（1）执行以下sql修改字段类型为varchar类型，varchar是变长的不会填充空格：
alter table student modify sname varchar(20);
（2）使用rtrim函数去掉数据后面的空格：
SELECT Sname FROM Student WHERE rtrim(Sname) LIKE '刘建_';
