#创建库
CREATE DATABASE aa;
USE aa;
表1
CREATE TABLE Student(sid VARCHAR(10),sname VARCHAR(10),sage DATETIME,ssex VARCHAR(10));
插入数据：
INSERT INTO Student VALUES(‘01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);
INSERT INTO Student VALUES(‘02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’);
INSERT INTO Student VALUES(‘03’ , ‘孙风’ , ‘1990-05-20’ , ‘男’);
INSERT INTO Student VALUES(‘04’ , ‘李云’ , ‘1990-08-06’ , ‘男’);
INSERT INTO Student VALUES(‘05’ , ‘周梅’ , ‘1991-12-01’ , ‘女’);
INSERT INTO Student VALUES(‘06’ , ‘吴兰’ , ‘1992-03-01’, ‘女’);
INSERT INTO Student VALUES(‘07’ , ‘郑竹’ , ‘1989-07-01’ , ‘女’);
INSERT INTO Student VALUES(‘08’ , ‘王菊’ , ‘1990-01-20’ , ‘女’);
表2
CREATE TABLE Course(cid VARCHAR(10),cname VARCHAR(10),tid VARCHAR(10));
插入数据
INSERT INTO Course VALUES(‘01’ , ‘语文’ , ‘02’);
INSERT INTO Course VALUES(‘02’ , ‘数学’ , ‘01’);
INSERT INTO Course VALUES(‘03’ , ‘英语’ ,’03’);
表3
CREATE TABLE Teacher(tid VARCHAR(10),tname VARCHAR(10));
插入数据
INSERT INTO Teacher VALUES(‘01’ , ‘张三’);
INSERT INTO Teacher VALUES(‘02’ , ‘李四’);
INSERT INTO Teacher VALUES(‘03’ , ‘王五’);
表4
CREATE TABLE SC(sid VARCHAR(10),cid
VARCHAR(10),score DECIMAL(18,1));
插入数据
INSERT INTO SC VALUES(‘01’ , ‘01’ , 80);
INSERT INTO SC VALUES(‘01’ , ‘02’ , 90);
INSERT INTO SC VALUES(‘01’ , ‘03’ , 99);
INSERT INTO SC VALUES(‘02’ , ‘01’ , 70);
INSERT INTO SC VALUES(‘02’ , ‘02’ , 60);
INSERT INTO SC VALUES(‘02’ , ‘03’ , 80);
INSERT INTO SC VALUES(‘03’ , ‘01’ , 80);
INSERT INTO SC VALUES(‘03’ , ‘02’ , 80);
INSERT INTO SC VALUES(‘03’ , ‘03’ , 80);
INSERT INTO SC VALUES(‘04’ , ‘01’ , 50);
INSERT INTO SC VALUES(‘04’ , ‘02’ , 30);
INSERT INTO SC VALUES(‘04’ , ‘03’ , 20);
INSERT INTO SC VALUES(‘05’ , ‘01’ , 76);
INSERT INTO SC VALUES(‘05’ , ‘02’ , 87);
INSERT INTO SC VALUES(‘06’ , ‘01’ , 31);
INSERT INTO SC VALUES(‘06’ , ‘03’ , 34);
INSERT INTO SC VALUES(‘07’ , ‘02’ , 89);
INSERT INTO SC VALUES(‘07’ , ‘03’ , 98);
接下来是题目：
#1.查询“某1”课程比“某2”课程成绩高的所有学生的学号；
SELECT *
FROM (SELECT *FROM sc WHERE sc.CId=01) AS ch
INNER JOIN(SELECT * FROM sc WHERE sc.CId=02)AS mh
ON ch.SId=mh.SId WHERE ch.Score>mh.Score
#2.查询平均成绩大于60分的同学的学号和平均成绩；
SELECT sid,AVG(score) FROM sc GROUP BY sid
HAVING AVG(score)>60;
#3.查询所有同学的学号、姓名、选课数、总成绩
SELECT Student.sid,Student.Sname,COUNT(SC.cid),SUM(score)
FROM Student LEFT OUTER JOIN SC ON Student.sid=SC.cid
GROUP BY Student.sid,Sname
#4.查询姓“李”的老师的个数；
SELECT COUNT(teacher.tid)FROM teacher
WHERE teacher.tname LIKE’李%’
#5.查询没学过“张三”老师课的同学的学号、姓名；
SELECT Student.sid,Student.Sname FROM Student WHERE sid
NOT IN (SELECT DISTINCT( SC.sid) FROM SC,Course,Teacher
WHERE SC.cid=Course.cid AND Teacher.tid=Course.tid AND
Teacher.Tname=”张三”);
#6.查询学过“ ”并且也学过编号“ ”课程的同学的学号、姓名；
SELECT a.SID,a.SNAME FROM (SELECT student.SNAME,student.SID
FROM student,course,sc WHERE cname=”语文”
AND sc.sid=student.sid AND sc.cid=course.cid)AS a,
(SELECT student.SNAME,student.SID FROM student,course,
sc WHERE cname=’数学’AND sc.sid=student.sid AND
sc.cid=course.cid)AS b WHERE a.sid=b.sid;
#7.查询学过“张三”老师所教的所有课的同学的学号、姓名；
SELECT sid,Sname FROM Student WHERE sid IN (SELECT sid FROM
SC ,Course ,Teacher WHERE SC.cid=Course.cid AND
Teacher.tid=Course.tid AND Teacher.Tname=’张三’
GROUP BY sid HAVING COUNT(SC.cid)=(SELECT COUNT(cid) FROM
Course,Teacher
WHERE Teacher.tid=Course.tid AND Tname=’张三’))
#8.*****查询课程编号“01”的成绩比课程编号“02”
#课程低的所有同学的学号、姓名；
SELECT a.sid,a.sname FROM(SELECT student.SID,student.sname,
sc.SCORE FROM student,sc WHERE student.sid=sc.sid AND
sc.cid=1)AS a,(SELECT student.SID,student.sname,sc.score FROM
student,sc WHERE student.sid=sc.sid AND sc.cid=2)AS b
WHERE a.score<b.score AND a.sid=b.sid
#9.查询所有课程成绩小于60分的同学的学号、姓名；
SELECT sid,Sname FROM Student WHERE sid
NOT IN (SELECT Student.sid FROM Student,SC WHERE
Student.sid=SC.sid AND score>60);
#10.*****查询没有学全所有课的同学的学号、姓名；
SELECT Student.sid,Student.Sname FROM Student,SC
WHERE Student.sid=SC.sid GROUP BY
Student.sid,Student.Sname
HAVING COUNT(cid) <(SELECT COUNT(cid) FROM Course);
#11.查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；
SELECT DISTINCT(student.sid),student.sname FROM
student,sc WHERE student.sid=sc.sid AND
student.sid IN(
SELECT cid FROM sc WHERE sid=01)
#12.查询和”01”号的同学学习的课程完全相同的其他同学的学号和姓名
SELECT DISTINCT(student.sid),student.Sname FROM
Student,SC WHERE Student.sid=SC.sid AND
cid IN (SELECT cid FROM SC WHERE sid=1)
#13.*****把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩；
#这里求出张三老师的平均分值即可！！！
UPDATE sc SET score =(SELECT AVG(score) FROM
sc,course,teacher WHERE course.cid=sc.cid
AND course.tid=teacher.tid AND teacher.tname=”张三”)