SQL实践—Creat&Insert&Update&Delete

Create Database and Table

• 创建名称为数据库sch_id的数据库（其中ID为本人学号的后三位），并使用该数据库。

  create database [数据库sch_240];

  利用SQL语句定义6张基本表：D、T、S、C、TC、SC，并根据要求添加各类约束。

1. 专业表：D(dno, dn)，dno表示专业代码，两个字符，主码；dn表示专业名称，可变长度最多30个字符，不能取空值，不能取重复值。

   CREATE TABLE D(
   dno char(2) PRIMARY KEY,
   dn NCHAR(30) NOT NULL UNIQUE
   )

2. 教师表：T(tno,tn,sex,age,prof,sal,comm,dno)，tno表示教师号，4个字符，主码；tn表示教师姓名，8个字符，不能为空值；sex表示性别，不能为空值，取值只能是“男”或“女”；age表示年龄，整型值，取值范围在20到70之间；prof表示职称，6个字符，取值只能是“教授”、“副教授”、“讲师”、“助教”其中之一。Sal表示基本工资，浮点型，值不能少于800；comm表示奖金，浮点型，值不能少于1200；dno表示教师专业代码，两个字符50，外码。

   CREATE TABLE T(
   tno CHAR(4) PRIMARY KEY,
   tn CHAR(8) NOT NULL,
   sex CHAR(2) NOT NULL CHECK(sex = '男' or sex = '女'),
   age INT CHECK(age>=20 AND age <= 70),
   prof CHAR(6) CHECK(prof = '教授' OR prof = '副教授' OR prof = '讲师' OR prof = '助教'),
   Sal FLOAT(20) CHECK(Sal >= 800),
   comm FLOAT(20) CHECK(comm >= 1200),
     dno CHAR(2) FOREIGN KEY REFERENCES D(dno)
   )

3. 学生表S(sno,sn,sex,age,dno)，sno表示学号，10个字符，主码；sn表示学生姓名，8个字符，不能为空值；sex表示性别，不能为空值，取值只能是“男”或“女”；age表示年龄，整型值，取值范围在15到40之间；dno表示学生的专业代码，两个字符50，外码。

   CREATE TABLE S(
   sno CHAR(10) PRIMARY KEY,
   sn CHAR(8) NOT NULL,
   sex CHAR(2) NOT NULL CHECK(sex = '男' OR sex = '女'),
   age INT CHECK((age>=15) AND (age <= 40)),
   dno CHAR(2) FOREIGN KEY REFERENCES D(dno)
   )

4. 课程表C(cno,cn,ct)，cno表示课程号，3个字符，主码；cn表示课程名，可变长度最多20个字符，不能取空值，不能取重复值；ct表示课时，整型值，取值范围在32至80之间。

   CREATE TABLE C(
   cno CHAR(3) PRIMARY KEY,
   cn NCHAR(20) NOT NULL UNIQUE,
   ct INT CHECK(ct>=32 AND ct <= 80)
   )

5. 选课关系表SC(sno,cno,score)，sno表示学号，10个字符，外码；cno表示课程号，3个字符，外码；score表示成绩，浮点型，取值在0到100之间。(sno,cno)为主码。

   CREATE TABLE SC(
   sno CHAR(10) FOREIGN KEY REFERENCES S(sno),
   cno CHAR(3) FOREIGN KEY REFERENCES C(cno) PRIMARY KEY(sno,cno),
   score FLOAT(3) CHECK(score>=0 AND score <= 100)
   )

6. 授课关系TC(tno,cno)，tno表示教师号，4个字符，外码；cno表示课程号，3个字符，外码；(tno，cno)为主码。

   CREATE TABLE TC(
   tno CHAR(4) FOREIGN KEY REFERENCES T(tno),
   cno CHAR(3) FOREIGN KEY REFERENCES C(cno) PRIMARY KEY(tno,cno),
   )

创建测试库

• 新建数据库和表及其插入部分表中数据代码如下：

  create database [sch_240];
  
  CREATE TABLE D(
  	dno char(2) PRIMARY KEY,
  	dn NCHAR(30) NOT NULL UNIQUE
  )
  CREATE TABLE T(
  	tno CHAR(4) PRIMARY KEY,
  	tn CHAR(8) NOT NULL,
  	sex CHAR(2) NOT NULL CHECK(sex = '男' or sex = '女'),
  	age INT CHECK(age>=20 AND age <= 70),
  	prof CHAR(6) CHECK(prof = '教授' OR prof = '副教授' OR prof = '讲师' OR prof = '助教'),
  	Sal FLOAT(20) CHECK(Sal >= 800),
  	comm FLOAT(20) CHECK(comm >= 1200),
      dno CHAR(2) FOREIGN KEY REFERENCES D(dno)
  )
  CREATE TABLE S(
  	sno CHAR(10) PRIMARY KEY,
  	sn CHAR(8) NOT NULL,
  	sex CHAR(2) NOT NULL CHECK(sex = '男' OR sex = '女'),
  	age INT CHECK((age>=15) AND (age <= 40)),
  	dno CHAR(2) FOREIGN KEY REFERENCES D(dno)
  )
  CREATE TABLE C(
  	cno CHAR(3) PRIMARY KEY,
  	cn NCHAR(20) NOT NULL UNIQUE,
  	ct INT CHECK(ct>=32 AND ct <= 80)
  )
  CREATE TABLE SC(
  	sno CHAR(10) FOREIGN KEY REFERENCES S(sno),
  	cno CHAR(3) FOREIGN KEY REFERENCES C(cno) PRIMARY KEY(sno,cno),
  	score FLOAT(3) CHECK(score>=0 AND score <= 100)
  )
  CREATE TABLE TC(
  	tno CHAR(4) FOREIGN KEY REFERENCES T(tno),
  	cno CHAR(3) FOREIGN KEY REFERENCES C(cno) PRIMARY KEY(tno,cno),
  )
  INSERT D(dno,dn) VALUES('d1','计算机系');
  INSERT D(dno,dn) VALUES('d2','信息');
  INSERT D(dno,dn) VALUES('d3','通信');
  INSERT D(dno,dn) VALUES('d4','数理');
  
  INSERT T(tno,tn,sex,age,prof,sal,comm,dno) VALUES('t1','张三','女',20,'副教授',800,1200,'d1');
  INSERT T(tno,tn,sex,age,prof,sal,comm,dno) VALUES('t2','安厚星','男',70,'教授',1000,1500,'d2');
  INSERT T(tno,tn,sex,age,prof,sal,comm,dno) VALUES('t3','王五','男',50,'讲师',800.2,1200.3,'d3');
  INSERT T(tno,tn,sex,age,prof,sal,comm,dno) VALUES('t4','李四','女',21,'助教',8000.11,12000.22,'d4');
  INSERT T(tno,tn,sex,age,prof,sal,comm,dno) VALUES('t5','王六','男',50,'讲师',800.2,1200.3,'d3');
  INSERT T(tno,tn,sex,age,prof,sal,comm,dno) VALUES('t6','赵琦','男',50,'讲师',800.2,1200.3,'d2');
  INSERT T(tno,tn,sex,age,prof,sal,comm,dno) VALUES('t7','孙凯','男',50,'讲师',800.2,1200.3,'d1');
  
  INSERT S VALUES('s1','高令平','女',40,'d1');
  INSERT S VALUES('s2','张明喜','男',15,'d2');
  INSERT S VALUES('s3','李福鑫','男',26,'d3');
  INSERT S VALUES('s4','乔杉扮','男',26,'d4');
  INSERT S VALUES('s5','李金成','男',26,'d3');
  INSERT S VALUES('s6','赵贤帅','女',26,'d2');
  INSERT S VALUES('s7','高德晨','男',26,'d1');
  INSERT S VALUES('s8','凡凡','女',26,'d2');
  
  INSERT C(cno,cn,ct) VALUES('c1','数据库',32);
  INSERT C(cno,cn,ct) VALUES('c2','大物',80);
  INSERT C(cno,cn,ct) VALUES('c3','web',40);
  INSERT C(cno,cn,ct) VALUES('c4','英语',40);
  INSERT C(cno,cn,ct) VALUES('c5','语文',78);
  INSERT C(cno,cn,ct) VALUES('c6','化学',40);
  INSERT C(cno,cn,ct) VALUES('c7','高数',50);
  INSERT C(cno,cn,ct) VALUES('c8','生物',40);
  
  INSERT SC(sno,cno,score) VALUES('s1','c1',0);
  INSERT SC(sno,cno,score) VALUES('s2','c2',100);
  INSERT SC(sno,cno,score) VALUES('s3','c3',50.4);
  INSERT SC VALUES('s4','c4',111);
  INSERT SC VALUES('s5','c5',60);
  INSERT SC VALUES('s6','c6',66);
  INSERT SC(sno,cno,score) VALUES('s7','c7',50.4);
  
  INSERT TC(tno,cno) VALUES('t1','c1');
  INSERT TC(tno,cno) VALUES('t2','c2');
  insert TC values('t3','c3');
  insert TC values('t4','c4');

Insert & Update & Delete

1. 向数据库的每张用户表（除SC表）中至少添加3条元组，其中S表中插入2位计算机专业的同学，一位非计算机专业的同学。

   insert S(sno,sn,dno,sex) values('s20','王志刚','d1','男');
   insert S(sno,sn,dno,sex) values('s21','王志','d3','女');
   insert S(sno,sn,dno,sex) values('s22','张明欣','d1','男');

2. 向SC表中插入选课记录，为计算机专业的同学选上全部课程，成绩取值为空值。

   insert SC(sno,cno) values('s20','c1');
   insert SC(sno,cno) values('s20','c2');
   insert SC(sno,cno) values('s20','c5');
   insert SC(sno,cno) values('s20','001');
   insert SC(sno,cno) values('s22','c1');
   insert SC(sno,cno) values('s22','c2');
   insert SC(sno,cno) values('s22','c5');
   insert SC(sno,cno) values('s22','001');

3. 将课时大于等于80学时的课程全部改为72学时。

   update C set ct=72 where ct>=80;

4. 删除成绩为空值的选课记录。

   delete from SC where score is null;

5. 删除姓名为刘伟的老师的授课记录。

   delete from TC where tno in(select tno from T where tn='刘伟');

6. 调整岗位津贴，教授岗贴增长10%，副教授岗贴增长20%，讲师岗贴增长30%。

   update T set comm=comm*CASE prof 
   when '教授' then 1.1
   when '副教授' then 1.2
   when '讲师' then 1.3
   else 1
   end

7. 将基本工资低于平均基本工资的教师的工资增长10%。

   update T set Sal = Sal*1.1 where Sal < (select AVG(Sal)from T);

8. 将基本工资低于同职称教师基本工资的教师工资增长10%。

   update T set Sal=Sal*1.1 where Sal < (select AVG(Sal) from T t1 where t1.prof=T.prof);

9. 将有授课记录的老师的工资提高百分之十。

   update T set Sal=Sal*1.1 where exists (select distinct tno from TC where TC.tno = T.tno);

10. 修改S表中一位已经选过课的学生的学号（例如：学号从11223344改为111）。
    ①. 先将SC表中sno原有的外键约束删除，

    --alter alter table SC drop constraint 外键约束名
    Alter table SC drop constraint FK__SC__sno__1FCDBCEB;

    然后在重新使用alter语句加入外键，并且设置级联。

    Alter TABLE SC ADD CONSTRAINT [FK_sc] FOREIGN KEY (sno) REFERENCES S(sno) ON Update CASCADE;

    ②. 使用数据库的增删改查实现数据的修改。

    select * into #S_1 from S where sno='11223344';
    update #S_1 set sno='111' where sno='11223344';
    insert S select *from #S_1;
    update SC set sno='111' where sno='11223344';
    delete from S where sno='11223344'

Query

1. 查询学号s5的同学的姓名和所在系，将查询结果的列名显示为中文。

   select sn 姓名,dno 所在系 from S where sno='s5';

2. 查询教过课程号为C2或C5的课程的教师的编号（写两个查询语句完成同样的功能）

   select tno from TC where cno='C2' or cno='C5';
   select tno from TC where cno in('C2','C5');

3. 查询学号为S5的同学所学课程的平均成绩。

   select AVG(score) 平均成绩 from SC where sno='s5';

4. 查询选过课程号为C1的课程的人数。

   select COUNT(*) from SC where cno='C1';

5. 查询计算机系学生的最大年龄，显示系名和最大年龄（查询结果的每列都要有列名且为中文）。

   select dn 系名,MAX(age) 最大年龄 from S join D on S.dno=D.dno where dn='计算机系' Group by dn;

6. 查询所有教过课的老师的人数（教过多门课的同一位老师只计数1次）。

   select count(DISTINCT tno) as 教过课的教师人数 from TC where tno is not null;

7. 查询信息系年龄在30到39岁之间（包括30和39）的男老师人数。

   select COUNT(*) from T join D on T.dno=D.dno where dn='信息系' and age>=30 and age<=39 and sex='男';

8. 查询选过课的各位学生的学号，选课门数和平均成绩。

   select sno,count(cno),AVG(score) from SC where cno is not null group by sno;

9. 查询各系的名称和所属教师人数（查询结果的每列都要有列名且为中文）。

   select dn 系名,COUNT(tno) 教师人数 from T join D on D.dno=T.dno group by dn;

10. 查询选课表中各门课的课程号，最高分，最低分和平均分（查询结果的每列都要有列名且为中文）。

    select SC.cno 课程号,MAX(score) 最高分,MIN(score) 最低分,AVG(score) 平均分 from SC join C on SC.cno=C.cno group by SC.cno;

11. 查询计算机系女生的学生信息，按年龄的降序、学号的升序显示学生学号，姓名，年龄。

    select S.sno 学号,sn 姓名,age 年龄, S.dno from S join D on D.dno=S.dno where dn='计算机系'and sex='女' order by age DESC,S.sno ASC;

12. 查询各职称的老师的职称，最高工资，最低工资，最高和最低工资间的差额，平均工资，按平均工资的降序显示上述信息（查询结果的每列都要有列名且为中文）。

    select prof 职称,MAX(comm) 最高工资,MIN(comm) 最低工资,MAX(comm)-MIN(comm) 差额,AVG(comm) 平均工资 from T group by prof order by AVG(comm) DESC;

13. 查询选修了C1和C2两门课的学生的学号。（使用连接和嵌套两种方式完成查询）。

    select S1.sno from SC S1 join SC S2 on S1.sno=S2.sno where S2.cno='C1' and S1.cno='C2';
    select sno from SC where cno ='C1' and sno in(select sno from SC where cno='C2');

14. 查询计算机系所有学生选修信息，显示学生学号，课程号和成绩。

    select S.sno,cno,score from S join SC on S.sno=SC.sno join D on S.dno=D.dno where dn='计算机系';

15. 查询所有教过课程号为C1的课程的老师姓名，职称，系别。

    select tn,prof,dn from T join TC on T.tno=TC.tno join D on D.dno=T.dno where cno='C1';

16. 查询具有教授和副教授职称的老师所教课程的编号和名称。

    select C.cno,cn from C join TC on C.cno=TC.cno join T on T.tno=TC.tno where prof in('教授','副教授');

17. 查询孙珊同学的成绩单，显示课程号，课程名和成绩。

    select C.cno,cn,score from C join SC on C.cno=SC.cno join S on S.sno=SC.sno where sn='孙珊';

18. 查询选修了C1但没有选修C2课程的学生的学号。

    select sno from SC where cno='C1' and sno not in (select sno from SC where cno='C2');

19. 查询计算机系学生选修过的课程编号和名称。

    select Distinct C.cno,cn from SC join S on S.sno=SC.sno join C on C.cno=SC.cno join D on D.dno=S.dno where dn='计算机系';

20. 查询所有没有选修过任何课程的学生的学号和姓名。

    select S.sno,sn,dno from S left join SC on S.sno=SC.sno where cno is null;

21. 查询教授课程门数超过3门的老师的编号和姓名。

    select T.tno,tn from TC join T on T.tno=TC.tno group by T.tno,tn having COUNT(cno)>3;

22. 查询选课表中出现过的不及格情况的学生编号，学生姓名及其不及格门数。

    select S.sno 学生编号,sn 姓名,COUNT(cno) 不及格门数 from SC join S on S.sno=SC.sno where score<60 group by sn,S.sno;