表结构：
（末尾有完整sql语句）
c（cno,cname,tno) 课程表
s (sno,sname,age,sex) 学生表
sc (sno,cno,score) 学生-课程表
t (tno,tname,title) 教师表
1.检索
小建议，三个表及以上，就用多表连接把，然后加上DISTINCT
1./查询年龄小于17岁的女学生的学号和姓名/
select sno,sname from s
where age < 17 and sex="f"
2./检索男学生所学课程的课程号和课程名。/
方法一：
select cno,cname from c
where cno in(
select cno from sc
where sno in(
select sno from s
where sex="m"))
方法二：
select DISTINCT c.cno, c.cname from c,sc,s
where c.cno=sc.cno and s.sno=sc.sno and sex="m"
3./检索男学生所学课程的任课老师的工号和姓名。/
select DISTINCT t.tno,t.tname from t,c,s,sc
where t.tno=c.tno and c.cno=sc.cno and s.sno=sc.sno
4./检索至少选修两门课程的学生学号。/
方法一：
select sno from sc
group by sno
having count(sno)>=2
方法二：
select DISTINCT x.sno from sc as x,sc as y
where x.sno=y.sno and x.cno!=y.cno
5./检索至少有学号为S2和S4学生选修的课程的课程号。/
select DISTINCT x.cno from sc as x,sc as y
where x.sno="s2" and y.sno="s4" and x.cno=y.cno
6./检索王同学不学的课程的课程号。/
方法一：
select cno from c
where cno not in(/*王的课程*/
select cno from sc
where sc.sno in(/*王的学号*/
select sno from s
where sname = "王"))
方法二：
select cno from c
where not exists(
select * from s,sc
where s.sno=sc.sno and sc.cno=c.cno and s.sname="王")
7./检索全部学生都选修的课程的课程号与课程名。/
方法一：
select cno,cname from c
where cno in(/*在sc表中，课程号出现的次数*/
select cno from sc
group by cno
having count(cno)=(/*学生人数*/
select count(*) from s))
方法二：
select cno,cname from c
where not exists(
select * from s
where not exists(
select * from sc
8./检索选修课程包含张xx所授全部课程的学生学号/
张老师一共教了两门课，这两门课都选的学生
方法一：
select sno from sc
where cno in(/*张老师教的课*/
select cno from c
where tno in(/*张老师的老师号*/
select tno from t
where tname ="张xx"))
group by sno
having count(sno)=(/*张老师教几门课*/
select count(*) from c
where tno in(
select tno from t
where tname="张xx"))
方法二：
SELECT DISTINCT sno from sc as x
where not EXISTS(
SELECT * from c,t
where c.tno=t.tno and tname="张xx" and not EXISTS
(SELECT * FROM sc as y where y.sno=x.sno and y.cno=c.cno))
2.检索（简单）
1./* 统计有学生选修的课程门数。*/
select count(DISTINCT cno) from sc
2./求选修C4课程的女学生的平均年龄。/
方法一：
select avg(age) from s
where sex="f" and sno in(
select sno from sc
where cno="c4" )
方法二：
select avg(age) from s,sc
where s.sno=sc.sno and cno="c4" and sex="f"
3./求张老师所授课程的每门课程的平均成绩。/
select c.cno,avg(score) from t,c,s,sc
where c.cno=sc.cno and s.sno=sc.sno and t.tno=c.tno and t.tname="张xx"
group by c.cno
4./统计每门课程的学生选修人数（超过2人的课程才统计)。要求显示课程号和人数，查询结果按人数降序排列，若人数相同,按课程号升序排列。/
select cno,count(cno) from sc
group by cno
having count(cno)>2
order by 2 desc,cno
5./检索学号比王同学大，而年龄比他小的学生姓名。/
select sname from s
where age<(
select age from s
where sname="王")
and sno >(
select sno from s
where sname="王")
6./在表SC中检索成绩为空值的学生学号和课程号。/
select sno,cno from sc
where score is null
7./检索姓名以L打头的所有学生的姓名和年龄。/
select sname,age from s
where sname like "L%"
8./求年龄大于女同学平均年龄的男学生姓名和年龄。/
select sname,age from s
where sex="m" and age>(
select avg(age) from s
where sex="f")
9./求年龄大于所有女同学年龄的男学生姓名和年龄。/
select sname,age from s
where sex="m" and age>all(
select age from s
where sex="f")
3.增删改查
1./向关系C中插一个课程元组(C8,'vC++,T6')。/
insert into c values('c8','VC++','t6')
2./检索所授每门课程的平均成绩均大于80分的教师姓名，并把检索到的值送往另一个已存在的表FACULTY (TNAME）中。/
insert into FACULTY(tname)
select DISTINCT t.tname from c,sc,t
where c.cno=sc.cno and c.tno=t.tno and c.cno in(
select cno from sc
where score>80)
3./在SC中删除尚无成绩的选课元组。/
delete from sc
where score is null
4./把选修张老师课程的女同学的选课元组全部删去。/
SELECT * from c,s,sc,t??
where c.cno=sc.cno and s.sno=sc.sno and c.tno=t.tno
and t.tname="张xx" and s.sex="f"
5./把html课不及格的成绩全改为60 分。/
update sc
set score =60
where cno in(
select cno from c
where cname like "html%") and score<60
6./把低于所有课程总平均成绩的女同学成绩提高5%。/
update sc
set score =score*1.05
where score<(
select avg(score) from sc)
and sno in(
select sno from s
where sex="f")
7./在表SC中修改C4课程的成绩，当成绩小于等于70分时提高5%，当成绩大于70分时提高4%。(用两种方法实现，一种方法是用两个UPDATE语句实现，另一种方法是用带CASE操作的一个UPDATE语句实现>。/
第一种方法：
/顺序不可颠倒/
update sc
set score = score*1.04
where cno="c4" and score >70
update sc
set score = score*1.05
where cno="c4" and score <=70
第二种方法：
update sc
set score =score*case
when score>70 then 1.04
else 1.05
end
where cno="c4"
8./在表SC中，当某个成绩低于全部课程的平均成绩时，提高5%。/
update sc
set score=score*1.05
where score<(
select avg(score) from sc)
c（cno,cname,tno) 课程表
INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c1', 'java课', 't1');
INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c2', 'c语言课', 't2');
INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c3', 'android课', 't3');
INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c4', 'html课', 't4');
INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c5', '编译原理', 't5');
INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c6', 'python', 't1');
INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c8', 'VC++', 't6');
s (sno,sname,age,sex) 学生表
INSERT INTO `s`(`sno`, `sname`, `age`, `sex`) VALUES ('s1', '潘', 15, 'f');
INSERT INTO `s`(`sno`, `sname`, `age`, `sex`) VALUES ('s2', '赵', 13, 'f');
INSERT INTO `s`(`sno`, `sname`, `age`, `sex`) VALUES ('s3', '陈 ', 12, 'm');
INSERT INTO `s`(`sno`, `sname`, `age`, `sex`) VALUES ('s4', '王', 20, 'f');
INSERT INTO `s`(`sno`, `sname`, `age`, `sex`) VALUES ('s5', '朱', 3, 'm');
sc (sno,cno,score) 学生-课程表
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s1', 'c1', '101.85000000000001');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s1', 'c6', '94.5');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s2', 'c1', '46.2');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s2', 'c2', '102.9');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s2', 'c3', '105');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s3', 'c1', '45');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s3', 'c2', '32');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s4', 'c1', '45.15');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s4', 'c2', '45.15');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s4', 'c4', '69.45750000000001');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s5', 'c1', '56');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s5', 'c3', '43');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s5', 'c6', '90');
t (tno,tname,title) 教师表
INSERT INTO `t`(`tno`, `tname`, `title`) VALUES ('t1', '张xx', '高级教师');
INSERT INTO `t`(`tno`, `tname`, `title`) VALUES ('t2', '王xx', '教授');
INSERT INTO `t`(`tno`, `tname`, `title`) VALUES ('t3', '吴x', '教授');
INSERT INTO `t`(`tno`, `tname`, `title`) VALUES ('t4', '方xx', '老师');
INSERT INTO `t`(`tno`, `tname`, `title`) VALUES ('t5', '周xx', '老师');