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;