CREATETABLE students
(sno VARCHAR(3)NOTNULL,
sname VARCHAR(4)NOTNULL,
ssex VARCHAR(2)NOTNULL,
sbirthday DATETIME,
class VARCHAR(5));CREATETABLE courses
(cno VARCHAR(5)NOTNULL,
cname VARCHAR(10)NOTNULL,
tno VARCHAR(10)NOTNULL);CREATETABLE scores
(sno VARCHAR(3)NOTNULL,
cno VARCHAR(5)NOTNULL,
degree NUMERIC(10,1)NOTNULL);CREATETABLE teachers
(tno VARCHAR(3)NOTNULL,
tname VARCHAR(4)NOTNULL, tsex VARCHAR(2)NOTNULL,
tbirthday DATETIMENOTNULL, prof VARCHAR(6),
depart VARCHAR(10)NOTNULL);-- 插入数据INSERTINTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)VALUES(108,'曾华','男','1977-09-01',95033);INSERTINTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)VALUES(105,'匡明','男','1975-10-02',95031);INSERTINTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)VALUES(107,'王丽','女','1976-01-23',95033);INSERTINTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)VALUES(101,'李军','男','1976-02-20',95033);INSERTINTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)VALUES(109,'王芳','女','1975-02-10',95031);INSERTINTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)VALUES(103,'陆君','男','1974-06-03',95031);INSERTINTO COURSES(CNO,CNAME,TNO)VALUES('3-105','计算机导论',825);INSERTINTO COURSES(CNO,CNAME,TNO)VALUES('3-245','操作系统',804);INSERTINTO COURSES(CNO,CNAME,TNO)VALUES('6-166','数据电路',856);INSERTINTO COURSES(CNO,CNAME,TNO)VALUES('9-888','高等数学',100);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(103,'3-245',86);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(105,'3-245',75);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(109,'3-245',68);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(103,'3-105',92);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(105,'3-105',88);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(109,'3-105',76);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(101,'3-105',64);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(107,'3-105',91);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(108,'3-105',78);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(101,'6-166',85);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(107,'6-106',79);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(108,'6-166',81);INSERTINTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)VALUES(804,'李诚','男','1958-12-02','副教授','计算机系');INSERTINTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)VALUES(856,'张旭','男','1969-03-12','讲师','电子工程系');INSERTINTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)VALUES(825,'王萍','女','1972-05-05','助教','计算机系');INSERTINTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)VALUES(831,'刘冰','女','1977-08-14','助教','电子工程系');
题目加题解(仅供参考!!!)
-- 查询问题:-- 1、 查询Student表中的所有记录的Sname、Ssex和Class列。SELECT
sname,
ssex,
class
FROM
students
-- 2、 查询教师所有的单位即不重复的Depart列。SELECTDISTINCT
depart
FROM
teachers
-- 3、 查询Student表的所有记录。SELECT*FROM
students
-- 4、 查询Score表中成绩在60到80之间的所有记录。SELECT*FROM
scores
WHERE60< degree <80-- 5、 查询Score表中成绩为85,86或88的记录。SELECT*FROM
scores
WHERE
degree IN(85,86,88)-- 6、 查询Student表中“95031”班或性别为“女”的同学记录。SELECT*FROM
students
WHERE
class ='95031'OR ssex ='女'-- 7、 以Class降序查询Student表的所有记录。SELECT*FROM
students
ORDERBY
class DESC-- 8、 以Cno升序、Degree降序查询Score表的所有记录。SELECT*FROM
scores
ORDERBY
cno,
degree DESC-- 9、 查询“95031”班的学生人数。SELECTcount(*)FROM
students
WHERE
class ='95031'-- 10、 查询Score表中的最高分的学生学号和课程号。(子查询或者排序)SELECT
sno,
cno
FROM
scores
WHERE
degree =(SELECTmax( degree )FROM
scores
)-- 11、 查询每门课的平均成绩。SELECT
cno,avg( degree )FROM
scores
GROUPBY
cno
-- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。SELECTcount(*),
a.cno,avg( degree )FROM
scores a
GROUPBY
cno
HAVING
a.cno LIKE'3%'ANDcount(*)>5-- 13、查询分数大于70,小于90的Sno列。SELECT
sno
FROM
scores
WHERE70< degree <90-- 14、查询所有学生的Sname、Cno和Degree列。SELECT
sname,
cno,
degree
FROM
students
JOIN scores ON students.sno = scores.sno
-- 15、查询所有学生的Sno、Cname和Degree列。SELECT
sno,
cname,
degree
FROM
courses
JOIN scores ON courses.cno = scores.cno
-- 16、查询所有学生的Sname、Cname和Degree列SELECT
sname,
cname,
degree
FROM
students
JOIN scores ON students.sno = scores.sno
JOIN courses ON scores.cno = courses.cno
-- 17、 查询“95033”班学生的平均分。SELECTavg( degree )FROM
students
JOIN scores ON students.sno = scores.sno
WHERE
students.class ='95033'-- 18、 假设使用如下命令建立了一个grade表:createtable grade(low int(3),upp int(3),rank char(1));insertinto grade values(90,100,’A’);insertinto grade values(80,89,’B’);insertinto grade values(70,79,’C’);insertinto grade values(60,69,’D’);insertinto grade values(0,59,’E’);
现查询所有同学的Sno、Cno和rank列。
SELECT
students.sno,
scores.cno
FROM
students
JOIN scores ON students.sno = scores.sno
UNIONSELECT
grade.rank
FROM
grade
-- 19、 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。SELECT
st.sno,
st.sname,
st.ssex,
st.sbirthday,
st.class,
sc.cno,
sc.degree
FROM
students AS st
JOIN scores AS sc ON st.sno = sc.sno
WHERE
sc.cno ='3-105'AND sc.degree >(SELECT
degree
FROM
scores
WHERE
cno ='3-105'AND sno ='109')-- 20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。SELECT
st.sno,
st.sname,
sc.cno,
sc.degree
FROM
students AS st
JOIN scores AS sc ON st.sno = sc.sno
WHERE
sc.degree <(SELECTmax( degree )FROM
scores a
ORDERBY
a.degree
)/*
SELECT sno
FROM scores
WHERE
degree< (SELECT max(degree)
FROM scores a
ORDER BY a.degree
-- HAVING count(*)>1)
)
*/-- 21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。SELECT
st.sno,
st.sname,
st.ssex,
st.sbirthday,
st.class,
sc.cno,
sc.degree
FROM
students AS st
JOIN scores AS sc ON st.sno = sc.sno
WHERE(
sc.degree >(SELECTmax( degree )FROM scores WHERE sno ='109')AND sc.cno ='3-105')-- 22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。SELECT
sno,
sname,
sbirthday
FROM
students
WHEREYEAR( sbirthday )=(SELECTYEAR( sbirthday )FROM
students
WHERE
sno ='108')-- 23、查询“张旭“教师任课的学生成绩。SELECT
sc.sno,
sc.degree,
te.tname
FROM
scores AS sc
JOIN courses AS c ON sc.cno = c.cno
JOIN teachers AS te ON c.tno = te.tno
WHERE
te.tname ='张旭'-- 24、查询选修某课程的同学人数多于5人的教师姓名。SELECTDISTINCT
teachers.tname,
scores.cno
FROM
teachers
JOIN courses ON courses.tno = teachers.tno
JOIN scores ON scores.cno = courses.cno
WHERE
courses.cno =(SELECT
cno
FROM
scores
GROUPBY
cno
HAVINGcount( sno )>5)-- 25、查询95033班和95031班全体学生的记录。SELECT*FROM
students
WHERE
class IN('95033','95031')-- 26、 查询存在有85分以上成绩的课程Cno.SELECTDISTINCT cno FROM scores WHERE degree >85-- 27、查询出“计算机系“教师所教课程的成绩表。 SELECT
teachers.tname,
teachers.depart,
courses.cname,
students.sno,
students.sname,
scores.degree
FROM
teachers
JOIN courses ON teachers.tno = courses.tno
JOIN scores ON courses.cno = scores.cno
JOIN students ON students.sno = scores.sno
WHERE
teachers.depart ='计算机系'-- 28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。SELECT
tname,
prof
FROM
teachers a
WHERE
prof NOTIN(SELECT b.prof FROM teachers b WHERE a.depart <> b.depart )-- 29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。SELECT
scores.cno,
students.sno,
students.sname,
scores.degree
FROM
students
JOIN scores ON scores.sno = students.sno
WHERE
scores.cno ='3-105'AND scores.degree >(SELECTmin( degree )FROM
scores
WHERE
cno ='3-245')ORDERBY
scores.degree DESC-- 30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.SELECT
scores.cno,
students.sno,
students.sname,
scores.degree
FROM
students
JOIN scores ON scores.sno = students.sno
WHERE
scores.cno ='3-105'AND scores.degree >(SELECTmax( degree )FROM
scores
WHERE
cno ='3-245')ORDERBY
scores.degree DESC-- 31、 查询所有教师和同学的name、sex和birthday.SELECT
st.sname,
st.ssex,
st.sbirthday
FROM
students AS st
UNIONSELECT
t.tname,
t.tsex,
t.tbirthday
FROM
teachers AS t
-- 32、查询所有“女”教师和“女”同学的name、sex和birthday.SELECT
st.sname,
st.ssex,
st.sbirthday
FROM
students AS st
WHERE
st.ssex='女'UNIONSELECT
t.tname,
t.tsex,
t.tbirthday
FROM
teachers AS t
WHERE
t.tsex ='女'-- 33、 查询成绩比该课程平均成绩低的同学的成绩表。SELECT students.sno,students.sname,scores.cno,scores.degree
FROM students
JOIN scores
ON students.sno=scores.sno
WHERE scores.degree <(SELECTavg(degree)from)-- 34、 查询所有任课教师的Tname和Depart.SELECT
tname,
depart
FROM
teachers
-- 35 、 查询所有未讲课的教师的Tname和Depart.SELECT
teachers.tname,
teachers.depart
FROM
teachers
WHERE
teachers.tno NOTIN(SELECTDISTINCT
courses.tno
FROM
courses
)-- 36、查询至少有2名男生的班号。SELECT
students.class
FROM
students
GROUPBY
students.class
HAVINGcount( students.ssex ='男')>=2-- 37、查询Student表中不姓“王”的同学记录。SELECT
students.sno,
students.sname,
students.class,
students.ssex
FROM
students
WHERE
students.sname NOTLIKE'王%'-- 38、查询Student表中每个学生的姓名和年龄。SELECT
students.sname AS'姓名',(YEAR(NOW())-YEAR( students.sbirthday ))AS'年龄'FROM
students
-- 39、查询Student表中最大和最小的Sbirthday日期值。SELECT students.sname,students.sbirthday
FROM students
ORDERBY(YEAR(NOW())-YEAR(students.sbirthday))DESC-- SELECT MIN(sbirthday),MAX(sbirthday) FROM students ;-- 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。SELECT*FROM students
ORDERBY students.class DESC,(YEAR(NOW())-YEAR(students.sbirthday))DESC-- 41、查询“男”教师及其所上的课程。SELECT teachers.tname,teachers.tno,courses.cname
FROM teachers
JOIN courses ON teachers.tno=courses.tno
WHERE teachers.tsex='男'-- 42、查询最高分同学的Sno、Cno和Degree列。SELECT sno,cno,degree
FROM scores
WHERE degree =(SELECTmax(degree)FROM scores
ORDERBY degree
)-- 43、查询和“李军”同性别的所有同学的Sname.SELECT sname
FROM students
WHERE students.ssex =(SELECT students.ssex
FROM students
WHERE students.sname='李军')-- 44、查询和“李军”同性别并同班的同学Sname.SELECT sname
FROM students
WHERE students.ssex =(SELECT students.ssex
FROM students
WHERE students.sname='李军')AND students.class=(SELECT students.class
FROM students
where students.sname='李军')-- 45、查询所有选修“计算机导论”课程的“男”同学的成绩表。SELECT
students.sname,
courses.cname,
scores.degree
FROM
students
JOIN scores ON scores.sno = students.sno
JOIN courses ON scores.cno = courses.cno
WHERE
students.ssex ='男'AND courses.cname ='计算机导论'ORDERBY scores.degree DESC