提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
目录
- 题目
- 查询Score表中成绩在60到80之间的所有记录
- 查询 score 表中成绩为85,86或88的记录
- 以 cno 升序、degree降序查询 score 表的所有记录
- 查询“95031”班的学生人数
- 查询Score表中的最高分的学生学号和课程号
- 查询‘3-105’号课程的平均分
- 查询Score表中至少有5名学生选修的并以3开头的课程的平均分数
- 查询最低分大于70,最高分小于90的Sno列
- 查询所有学生的Sname、Cno和Degree列。
- 查询所有学生的Sno、Cname和Degree列
- 查询所有学生的Sname、Cname和Degree列
- 查询“95033”班所选课程的平均分。
- 查询所有同学的Sno、Cno和rank列
- 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录
- 查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
- 查询“张旭“教师任课的学生成绩
- 查询选修某课程的同学人数多于5人的教师姓名。
- 查询95033班和95031班全体学生的记录
- 查询存在有85分以上成绩的课程Cno。
- 查询出“计算机系“教师所教课程的成绩表
- 查询所有教师和同学的name、sex和birthday。
- 查询所有“女”教师和“女”同学的name、sex和birthday
- 查询成绩比该课程平均成绩低的同学的成绩表。
- 查询所有任课教师的Tname和Depart
- 查询至少有2名男生的班号
- 查询Student表中不姓“王”的同学记录。
- 查询Student表中每个学生的姓名和年龄。
题目
CREATE TABLE STUDENT
(
SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5)
);
CREATE TABLE COURSE
(
CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL
);
CREATE TABLE SCORE
(
SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL
);
CREATE TABLE TEACHER
(
TNO VARCHAR(3) NOT NULL,
TNAME VARCHAR(4) NOT NULL,
TSEX VARCHAR(2) NOT NULL,
TBIRTHDAY DATETIME NOT NULL,
PROF VARCHAR(6),
DEPART VARCHAR(10) NOT NULL
);
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS) VALUES (108, '曾华'
, '男', '1977-09-01', 95033);
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS) VALUES (105, '匡明'
, '男', '1975-10-02', 95031);
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS) VALUES (107, '王丽'
, '女', '1976-01-23', 95033);
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS) VALUES (101, '李军'
, '男', '1976-02-20', 95033);
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS) VALUES (109, '王芳'
, '女', '1975-02-10', 95031);
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS) VALUES (103, '陆君'
, '男', '1974-06-03', 95031);
INSERT INTO COURSE (CNO, CNAME, TNO) VALUES ('3-105', '计算机导论', 825);
INSERT INTO COURSE (CNO, CNAME, TNO) VALUES ('3-245', '操作系统', 804);
INSERT INTO COURSE (CNO, CNAME, TNO) VALUES ('6-166', '数据电路', 856);
INSERT INTO COURSE (CNO, CNAME, TNO) VALUES ('9-888', '高等数学', 100);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (103, '3-245', 86);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (105, '3-245', 75);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (109, '3-245', 68);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (103, '3-105', 92);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (105, '3-105', 88);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (109, '3-105', 76);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (101, '3-105', 64);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (107, '3-105', 91);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (101, '6-166', 85);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (107, '6-106', 79);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (108, '3-105', 78);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (108, '6-166', 81);
INSERT INTO TEACHER (TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)
VALUES (804, '李诚', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO TEACHER (TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)
VALUES (856, '张旭', '男', '1969-03-12', '讲师', '电子工程系');
INSERT INTO TEACHER (TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)
VALUES (825, '王萍', '女', '1972-05-05', '助教', '计算机系');
INSERT INTO TEACHER (TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)
VALUES (831, '刘冰', '女', '1977-08-14', '助教', '电子工程系');
查询Score表中成绩在60到80之间的所有记录
SELECT
*
FROM
score
WHERE
degree > 60
AND degree < 80
结果:
查询 score 表中成绩为85,86或88的记录
代码如下(示例):
SELECT
*
FROM
score
WHERE
degree IN (85,86,88)
结果:
以 cno 升序、degree降序查询 score 表的所有记录
代码如下(示例):
SELECT
*
FROM
score
ORDER BY
cno ASC,
degree DESC
结果:
查询“95031”班的学生人数
SELECT
*
FROM
student
WHERE
class = '95031'
结果:
查询Score表中的最高分的学生学号和课程号
SELECT
sno,
cno
FROM
score
ORDER BY
degree DESC
LIMIT 1
结果:
查询‘3-105’号课程的平均分
SELECT
avg( degree )
FROM
score
WHERE
cno = '3-105'
结果:
查询Score表中至少有5名学生选修的并以3开头的课程的平均分数
SELECT
cno,
avg( degree )
FROM
score
WHERE
cno LIKE '3%'
GROUP BY
cno
HAVING
count(*)>5
结果:
查询最低分大于70,最高分小于90的Sno列
SELECT
sno
FROM
score
GROUP BY
sno
HAVING
min(degree)>70 and max(degree)<90
结果:
查询所有学生的Sname、Cno和Degree列。
SELECT
sname,
cno,
degree
FROM
student,
score
WHERE
student.sno = score.sno
结果:
查询所有学生的Sno、Cname和Degree列
SELECT
score.sno,
course.cname,
score.degree
FROM
student
JOIN ( course, score ) ON student.sno = score.sno
AND course.cno = score.cno
结果:
查询所有学生的Sname、Cname和Degree列
SELECT
student.sname,
course.cname,
score.degree
FROM
student
JOIN ( course, score ) ON student.sno = score.sno
AND course.cno = score.cno
结果:
查询“95033”班所选课程的平均分。
SELECT
avg( degree )
FROM
score
WHERE
sno IN (
SELECT
sno
FROM
student
WHERE
class = '95033')
结果:
查询所有同学的Sno、Cno和rank列
首先,假设使用如下命令建立了一个grade表:
CREATE TABLE grade (
low NUMERIC (3, 0),
upp NUMERIC (3),
rank CHAR (1)
);
INSERT INTO grade VALUES (90, 100, 'A');
INSERT INTO grade VALUES (80, 89, 'B');
INSERT INTO grade VALUES (70, 79, 'C');
INSERT INTO grade VALUES (60, 69, 'D');
INSERT INTO grade VALUES (0, 59, 'E');
SELECT
score.sno,
score.cno,
grade.rank
FROM
score,
grade
WHERE
score.degree BETWEEN grade.low
AND grade.upp
order by rank
结果:
查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录
SELECT
*
FROM
score
WHERE
cno = '3-105'
AND degree >(
SELECT
degree
FROM
score
WHERE
sno = '109' and cno='3-105'
)
结果:
查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SELECT
sno,
sname,
sbirthday
FROM
student
WHERE
YEAR ( sbirthday )=(
SELECT YEAR
( sbirthday )
FROM
student
WHERE
sno = '108')
涉及year()函数的使用
结果:
查询“张旭“教师任课的学生成绩
方法一
SELECT score.*
FROM score
WHERE cno =( SELECT cno FROM course WHERE tno =( SELECT tno FROM teacher WHERE tname = '张旭' ))
方法二
SELECT
*
FROM
score
WHERE
cno =(
SELECT
cno
FROM
course
JOIN teacher ON course.TNO = teacher.tno
AND tname = '张旭')
结果:
查询选修某课程的同学人数多于5人的教师姓名。
方法一
SELECT
tname
FROM
teacher
WHERE
tno IN (
SELECT
tno
FROM
course
WHERE
cno IN ( SELECT cno FROM score GROUP BY cno HAVING count( sno )> 5 ))
方法二:
SELECT
tname
FROM
teacher
WHERE
tno IN (
SELECT
course.tno
FROM
course,
score
WHERE
course.cno = score.cno
GROUP BY
course.tno
HAVING
count( course.tno )> 5)
结果:
查询95033班和95031班全体学生的记录
SELECT
*
FROM
student
WHERE
class IN (
'95033',
'95031')
结果:
查询存在有85分以上成绩的课程Cno。
SELECT
cno
FROM
score
GROUP BY
cno
HAVING
max( degree )> 85
结果:
查询出“计算机系“教师所教课程的成绩表
SELECT
*
FROM
score
WHERE
cno IN (
SELECT
cno
FROM
course,
teacher
WHERE
depart = '计算机系'
AND course.tno = teacher.tno)
结果:
查询所有教师和同学的name、sex和birthday。
SELECT tname name, tsex sex, tbirthday birthday
FROM teacher
UNION
SELECT sname name, ssex sex, sbirthday birthday
FROM student;
结果:
查询所有“女”教师和“女”同学的name、sex和birthday
SELECT
tname NAME,
tsex sex,
tbirthday birthday
FROM
teacher
WHERE
tsex = '女' UNION
SELECT
sname NAME,
ssex sex,
sbirthday birthday
FROM
student
WHERE
ssex = '女'
结果:
查询成绩比该课程平均成绩低的同学的成绩表。
SELECT
score.*
FROM
score
WHERE
degree <(
SELECT
avg( degree )
FROM
score b
WHERE
b.cno = score.cno)
结果:
查询所有任课教师的Tname和Depart
SELECT
tname,
depart
FROM
teacher
WHERE
tno IN ( SELECT tno FROM course )
结果:
查询至少有2名男生的班号
SELECT
class
FROM
student
WHERE
ssex = '男'
GROUP BY
class
HAVING
count( ssex )>1
结果:
查询Student表中不姓“王”的同学记录。
SELECT
*
FROM
student
WHERE
sname NOT LIKE '王%'
结果:
查询Student表中每个学生的姓名和年龄。
SELECT
sname,
YEAR (now())- YEAR ( sbirthday ) AS age
FROM
student
结果: