珍贵的数据库SQL联系题

1）为SC表添加“选课类别”列，此列的定义为XKLB char(4).
alter SC set add XLB char(4) null;

2）将新添加的XKLB的类型改为char(6)。
alter table SC alter column XKLB char(6);

3）删除Course表的Period列。
alter from drop column Period;

4）查询全体学生的学号与姓名。
select Sno,Sname from Student;

5）查询全体学生的姓名及其出生年份。
select Sname,2011-Sage as ‘出生年份’;

6）查询全体学生的姓名和出生年份，并在出生年份列前加入一个列，此列的每行数据均为“Year of Birth”常量值。
select Sname,‘出生年份’,2011-Sage from Student;

7）查询所有年龄在20岁以下的学生的姓名及年龄。
slect Sname,Sage from Student where Sage<20;

8）查询与刘晨在同一个系学习的学生的姓名和所在系。
select S2.Sname,S2.Sdept from Student S1 join Student S2 on S1.Sdept = S2.Sdept
where S1.Sname = ‘刘晨’ and S2.Sname !=‘刘晨’;

9）删除计算机系不及格学生的选课记录。
delete from SC where Grade < 60 and Sno in (select Sno from Student where Sdept = ‘计算机系’);

10）查询没学过“叶平”老师课的同学的学号、姓名；
（1）查出李平老师所受的课
select cid from course left join teacher on course.teacher_id=teacher.tid where teacher.tname=“李平老师”;
（2）查出选择李平老师讲课的学生
select * from score where score.corse_id in (select cid from course left join teacher on course.teacher_id=teacher.tid where teacher.tname=“李平老师”)
（3）排除选择李平老师讲课的学生
select sid,sname from student where student.sid not in (select student_id from score where score.corse_id in (select cid from course left join teacher on course.teacher_id=teacher.tid where teacher.tname=“李平老师”));

11）查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
select A.student_id,num_id1,num_id2 from (select student_id,number as num_id1 from score where corse_id=1) as A left join (select student_id,number as num_id2 from score where corse_id=2) as B on A.student_id = B.student_id where num_id1 > if(isnull(num_id2),0,num_id2);

12）查询有课程成绩小于60分的同学的学号、姓名；
select student.sid,student.sname from (select score.student_id from score where score.number <60 group by score.student_id) as A left join student on student.sid= A.student_id;