数据库实验题答案

 

所有的代码，题目都在注释中。

题目在梁老师的博客：http://blog.csdn.net/cnlht/archive/2010/10/08/5927309.aspx

所有问题已经全部解决，个人答案，仅供参考。

 

在学习中还会经常更新，查看最新版本请到：http://www.ijser.cn/?p=365

 

/* *创建SC数据库 * */ create database SC go use SC create table student ( Sno char(9) primary key, Sname char(20) unique, Ssex char(2), Sage smallInt, Sdept char(20) ); create table course ( Cno char(4) primary key, Cname char(20), Cpno char(4), Ccredit smallInt, foreign key (Cno) references course(Cno) ); create table SC ( Sno char(9), Cno char(4), Grade smallInt, primary key (Cno,Sno), foreign key (Sno) references student(Sno), foreign key (Cno) references course(Cno) ); --插入数据 insert into student(Sno,Sname,Ssex,Sage,Sdept) values('200215121','李勇','男',20,'CS'); insert into student(Sno,Sname,Ssex,Sage,Sdept) values('200215122','刘晨','女',19,'IS'); insert into student(Sno,Sname,Ssex,Sage,Sdept) values('200215123','王敏','女',18,'MA'); insert into student(Sno,Sname,Ssex,Sage,Sdept) values('200215125','张立','男',19,'IS'); insert into Course(Cno,Cname,Cpno,Ccredit) values('2','数学',null,2); insert into Course(Cno,Cname,Cpno,Ccredit) values('6','数据处理',null,2); insert into Course(Cno,Cname,Cpno,Ccredit) values('4','操作系统','6',3); insert into Course(Cno,Cname,Cpno,Ccredit) values('7','PASCAL语言','6',4); insert into Course(Cno,Cname,Cpno,Ccredit) values('5','数据结构','7',4); insert into Course(Cno,Cname,Cpno,Ccredit) values('1','数据库','5',4); insert into Course(Cno,Cname,Cpno,Ccredit) values('3','信息系统','1',4); insert into SC(Sno,Cno,Grade) values('200215121','1',92); insert into SC(Sno,Cno,Grade) values('200215121','2',85); insert into SC(Sno,Cno,Grade) values('200215121','3',88); insert into SC(Sno,Cno,Grade) values('200215122','2',90); insert into SC(Sno,Cno,Grade) values('200215122','3',80); go /* 1、单表查询 1）选择指定的列 [例]查询全体学生的学号和姓名 */ select Sno as 学号,Sname as 姓名 from student; /* 2）查询全部列 [例]查询全体学生的详细信息 */ select * from student; /* 3）对查询后的指定列进行命名 [例]查询全部学生的“姓名”及其“出生年”两列 */ select Sname as 姓名,(2010-Sage) as 出生年 from student; /* 4）消除取值重复的行 [例]查询选修了课程的学生学号 */ select distinct Sno as 选修了课程的学生学号 from SC; /* 5）选择表中若干元组（满足条件的） （1）大小比较 [例]查询计算机系（IS）全体学生名单 */ select Sname as 学生姓名 from student where Sdept='IS'; /* [例]查询全体20岁以下的学生姓名和年龄 */ select Sname as 姓名,Sage as 年龄 from student where Sage<20; /* （2）确定范围 [例]查询所有在20到23岁（含20和23）的学生姓名、系别和年龄 */ select Sname as 姓名,Sdept as 系别,Sage as 年龄 from student where Sage between 20 and 23; /* （3）in和not in确定集合 [例]查询IS系和CS系的全体学生姓名和性别 */ --select Sname as 姓名,Sage as 年龄 from student where Sdept='IS' or Sdept='CS'; select Sname as 姓名,Sage as 年龄 from student where Sdept in ('IS','CS'); /* [例]查询既不属于IS系，也不属于MA系的学生姓名和年龄 */ --select Sname as 姓名,Sage as 年龄 from student where Sdept!='IS' and Sdept!='CS'; select Sname as 姓名,Sage as 年龄 from student where Sdept not in('IS','MA'); /* （4）字符匹配（