lbcqc

基于sqlite3的sql语句练习（一）

2018/09/21 Sql

最近工作要用到sqlite3数据库，sql语句是基础，这里从网上搜索一些练习题，给自己练练手。

题目出自：https://blog.csdn.net/mrbcy/article/details/68965271

目录

数据准备

sqlite3_sql_learn_1.db

建表


CREATE TABLE IF NOT EXISTS students (
    sno 		VARCHAR(3) 	NOT NULL, 
    sname 		VARCHAR(4) 	NOT NULL,
    ssex 		VARCHAR(2) 	NOT NULL, 
    sbirthday 	DATETIME,
    class 		VARCHAR(5)
);

CREATE TABLE IF NOT EXISTS courses(
    cno 	VARCHAR(5)      NOT NULL, 
    cname 	VARCHAR(10)     NOT NULL, 
    tno 	VARCHAR(10)     NOT NULL
);

CREATE TABLE IF NOT EXISTS scores (
    sno 	VARCHAR(3)      NOT NULL, 
    cno 	VARCHAR(5)      NOT NULL, 
    degree 	NUMERIC(10, 1)  NOT NULL
); 

CREATE TABLE IF NOT EXISTS teachers (
    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 STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);

INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');

题目列表

1、 查询Student表中的所有记录的Sname、Ssex和Class列。
2、 查询教师所有的单位即不重复的Depart列。
3、 查询Student表的所有记录。
4、 查询Score表中成绩在60到80之间的所有记录。
5、 查询Score表中成绩为85，86或88的记录。
6、 查询Student表中“95031”班或性别为“女”的同学记录。
7、 以Class降序查询Student表的所有记录。
8、 以Cno升序、Degree降序查询Score表的所有记录。
9、 查询“95031”班的学生人数。
10、查询Score表中的最高分的学生学号和课程号。
11、查询‘3-105’号课程的平均分。
12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
13、查询socre表中最低分大于70，最高分小于90的Sno列。
14、查询所有学生的Sname、Cno和Degree列。
15、查询所有学生的Sno、Cname和Degree列。
16、查询所有学生的Sname、Cname和Degree列。
17、查询“95033”班所选课程的平均分。
18、假设使用如下命令建立了一个grade表:
create table grade(low   number(3,0),upp   number(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’);
commit;
现查询所有同学的Sno、Cno和rank列。
19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
23、查询“张旭“教师任课的学生成绩。
24、查询选修某课程的同学人数多于5人的教师姓名。
25、查询95033班和95031班全体学生的记录。
26、查询存在有85分以上成绩的课程Cno.
27、查询出“计算机系“教师所教课程的成绩表。
28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
31、查询所有教师和同学的name、sex和birthday.
32、查询所有“女”教师和“女”同学的name、sex和birthday.
33、查询成绩比该课程平均成绩低的同学的成绩表。
34、查询所有任课教师的Tname和Depart.
35  查询所有未讲课的教师的Tname和Depart. 
36、查询至少有2名男生的班号。
37、查询Student表中不姓“王”的同学记录。
38、查询Student表中每个学生的姓名和年龄。
39、查询Student表中最大和最小的Sbirthday日期值。
40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
41、查询“男”教师及其所上的课程。
42、查询最高分同学的Sno、Cno和Degree列。
43、查询和“李军”同性别的所有同学的Sname.
44、查询和“李军”同性别并同班的同学Sname.
45、查询所有选修“计算机导论”课程的“男”同学的成绩表

解答题目

1. 查询Student表中的所有记录的Sname、Ssex和Class列

SELECT Sname, SSex, Class
FROM students

2. 查询教师所有的单位即不重复的Depart列。

SELECT DISTINCT depart
FROM teachers

3. 查询Student表的所有记录。

SELECT *
FROM students

4. 查询Score表中成绩在60到80之间的所有记录

SELECT *
FROM scores
WHERE degree BETWEEN 60 AND 80;

5. 查询Score表中成绩为85，86或88的记录

SELECT * FROM scores
WHERE degree IN (85, 86, 88)

6. 查询Student表中“95031”班或性别为“女”的同学记录

SELECT * FROM students
WHERE students.class = '95031' OR students.ssex = '女'

7. 以Class降序查询Student表的所有记录

SELECT *
FROM students
ORDER BY students.class DESC

8. 以Cno升序、Degree降序查询Score表的所有记录

SELECT *
FROM scores
ORDER BY scores.cno ASC
    , scores.degree DESC

9. 查询“95031”班的学生人数

SELECT count(*) as stu_count_95031
FROM students
WHERE students.class = '95031'

10. 查询Score表中的最高分的学生学号和课程号

SELECT sno, cno
FROM scores
WHERE degree = (
	SELECT max(degree)
	FROM scores
)

11. 查询‘3-105’号课程的平均分

SELECT avg(degree)
FROM scores
WHERE cno = '3-105'

12. 查询Score表中至少有5名学生选修的并以3开头的课程的平均分数

SELECT avg(degree)
FROM scores
WHERE cno GLOB "3*"
GROUP BY cno
HAVING count(cno) > 5

13. 查询score表中最低分大于70，最高分小于90的Sno列

SELECT sno
FROM scores
GROUP BY sno
HAVING
    max(degree) < 90
AND
    min(degree) > 70

14. 查询所有学生的Sname、Cno和Degree列

SELECT students.sname 
    , scores.cno
    , scores.degree
FROM students
    , scores
WHERE students.sno = scores.sno

15. 查询所有学生的Sno、Cname和Degree列

SELECT scores.sno
    , courses.cname
    , scores.degree
FROM scores
    , courses
WHERE courses.cno = scores.cno

16. 查询所有学生的Sname、Cname和Degree列

SELECT students.sname
	, courses.cname
	, scores.degree
FROM students
	, scores
	, courses
WHERE courses.cno = scores.cno
AND students.sno = scores.sno

17. 查询“95033”班所选课程的平均分

SELECT scores.cno, avg(scores.degree) as avg_score
FROM students
INNER JOIN scores
ON (students.sno = scores.sno)
AND students.class = '95033'

18. 查询所有同学的Sno、Cno和rank列

--- 创建grade表
create table grade(low   number(3,0)
    , upp   number(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 sno, cno, rank
FROM grade
    , scores
WHERE scores.degree <= grade.upp
AND scores.degree >= grade.low;

19. 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录

SELECT s1.sno,s1.degree
FROM scores AS s1
INNER JOIN scores AS s2
ON(s1.cno=s2.cno AND s1.degree>s2.degree)
WHERE s1.cno='3-105' AND s2.sno='109'

20. 查询score中选学一门以上课程的同学中分数为非最高分成绩的记录

SELECT s1.sno, s1.cno, s1.degree
FROM (
    SELECT cno, max(degree) as degree
    FROM scores
    GROUP BY cno) AS s2 	-- s2表记录了所有课程的最高分
    , scores as s1
where s1.cno = s2.cno
AND s1.degree <> s2.degree	-- 不是最高分
AND s1.sno NOT IN ( 	-- 不只选了一门课
    SELECT sno
    FROM scores
    GROUP BY sno
    HAVING count(sno) = 1
)

21. 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录

SELECT * 
FROM scores
WHERE scores.degree > (
    SELECT degree
    FROM scores
    WHERE scores.sno = '109'
    AND scores.cno = '3-105'
)

22. 查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列

SELECT s1.Sno,s1.Sname,s1.Sbirthday
FROM Students AS s1 
INNER JOIN Students AS s2
ON(strftime("%Y", s1.Sbirthday) = strftime("%Y", s2.Sbirthday)
    AND s1.sno <> s2.sno)
WHERE s2.Sno = '108'

23. 查询“张旭“教师任课的学生成绩

SELECT scores.sno, scores.degree
FROM teachers
INNER JOIN courses ON (teachers.tno = courses.tno)
INNER JOIN scores ON (courses.cno = scores.cno)
WHERE teachers.tname = '张旭'

24. 查询选修某课程的同学人数多于5人的教师姓名

SELECT teachers.tname, courses.cname
FROM teachers
INNER JOIN courses ON (teachers.tno = courses.tno)
INNER JOIN scores ON (courses.cno = scores.cno)
GROUP BY scores.cno
HAVING count(scores.cno) > 5

25. 查询95033班和95031班全体学生的记录

SELECT *
FROM students
WHERE class IN ('95033', '95031')

26. 查询存在有85分以上成绩的课程Cno

SELECT DISTINCT cno
FROM scores
WHERE degree > 85

27. 查询出“计算机系“教师所教课程的成绩表

SELECT scores.sno, scores.cno, scores.degree
FROM teachers
INNER JOIN courses ON (courses.tno = teachers.tno)
INNER JOIN scores ON (scores.cno = courses.cno)
WHERE teachers.depart = '计算机系'

28. 查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof

SELECT *
FROM teachers
WHERE depart IN ('计算机系', '电子工程系')
AND prof NOT IN (
    SELECT t1.prof
    FROM teachers as t1
    INNER JOIN teachers as t2
    ON (t1.prof = t2.prof)
    WHERE t1.depart = '计算机系'
    AND t2.depart = '电子工程系'
)

29. 查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序

SELECT cno, sno, degree
FROM scores
WHERE cno = '3-105'
AND degree >= (		-- 分数至少大于选修3-245的同学的分数
    SELECT min(s1.degree)
    FROM scores as s1
    INNER JOIN scores as s2
    ON (s1.sno = s2.sno)
    WHERE s2.cno = '3-245'
    AND s1.cno = '3-105'
)
AND sno NOT IN (	-- 不是选了3-245的同学本身
    SELECT s1.sno
    FROM scores as s1
    INNER JOIN scores as s2
    ON (s1.sno = s2.sno)
    WHERE s2.cno = '3-245'
    AND s1.cno = '3-105'
)
ORDER BY degree DESC

30. 查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree

SELECT cno, sno, degree
FROM scores
WHERE cno = '3-105'
AND degree > (		-- 分数大于选修3-245的同学的分数
    SELECT max(s1.degree)
    FROM scores as s1
    INNER JOIN scores as s2
    ON (s1.sno = s2.sno)
    WHERE s2.cno = '3-245'
    AND s1.cno = '3-105'
)
AND sno NOT IN (	-- 不是选了3-245的同学本身
    SELECT s1.sno
    FROM scores as s1
    INNER JOIN scores as s2
    ON (s1.sno = s2.sno)
    WHERE s2.cno = '3-245'
    AND s1.cno = '3-105'
)

31. 查询所有教师和同学的name、sex和birthday

SELECT sname as name
    , ssex as sex
    , sbirthday as birthday
FROM students

UNION

SELECT tname as name
    , tsex as sex
    , tbirthday as birthday
FROM teachers

32. 查询所有“女”教师和“女”同学的name、sex和birthday

SELECT sname as name
    , ssex as sex
    , sbirthday as birthday
FROM students
WHERE students.ssex = '女'

UNION

SELECT tname as name
    , tsex as sex
    , tbirthday as birthday
FROM teachers
WHERE teachers.tsex = '女'

33. 查询成绩比该课程平均成绩低的同学的成绩表

SELECT s2.sno, s2.cno, s2.degree
FROM (
    SELECT cno, avg(degree) as degree
    FROM scores
    GROUP BY cno
) AS s1
    , scores as s2
WHERE s2.cno = s1.cno
AND s2.degree < s1.degree

34. 查询所有任课教师的Tname和Depart

SELECT teachers.tname, teachers.depart
FROM teachers
INNER JOIN courses
ON (teachers.tno = courses.tno)

35. 查询所有未讲课的教师的Tname和Depart

SELECT tname, depart
FROM teachers
WHERE teachers.tno NOT IN (
    SELECT tno
    FROM courses
)

36. 查询至少有2名男生的班号

SELECT class
FROM students
WHERE ssex = '男'
GROUP BY class
HAVING count(sno) >= 2

37. 查询Student表中不姓“王”的同学记录

SELECT *
FROM students
WHERE students.sname NOT GLOB "王*"

38. 查询Student表中每个学生的姓名和年龄

SELECT sname, (2018 - students.sbirthday) as age
FROM students

39. 查询Student表中最大和最小的Sbirthday日期值

SELECT min(sbirthday), max(sbirthday)
FROM students

40. 以班号和年龄从大到小的顺序查询Student表中的全部记录

SELECT *
FROM students
ORDER BY class DESC
    , sbirthday ASC

41. 查询“男”教师及其所上的课程

SELECT teachers.tname, courses.cname
FROM teachers
INNER JOIN courses
ON (courses.tno = teachers.tno)
WHERE teachers.tsex = '男'

42. 查询最高分同学的Sno、Cno和Degree列

SELECT *
FROM scores
GROUP BY cno
HAVING degree=max(degree)

43. 查询和“李军”同性别的所有同学的Sname

SELECT sname
FROM students
WHERE students.sname <> '李军'
AND students.ssex = (
    SELECT ssex
    FROM students
    WHERE sname = '李军'
)

44. 查询和“李军”同性别并同班的同学Sname

SELECT s1.sname
FROM students as s1 
INNER JOIN students as s2
ON (s1.class = s2.class 
    AND s1.ssex = s2.ssex 
    AND s1.sno <> s2.sno
)
WHERE s2.sname = '李军'

45. 查询所有选修“计算机导论”课程的“男”同学的成绩表

SELECT scores.*
FROM students
INNER JOIN scores ON (students.sno = scores.sno)
INNER JOIN courses ON (courses.cno = scores.cno)
WHERE courses.cname = '计算机导论'
AND students.ssex = '男'

Search

    Table of Contents