如何练习使用 SQL

做题。以下使用的是 POSTGRESQL 9.4 BETA 2。

表结构

CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATE,
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 DATE 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 (108,'3-105',78);
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,'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','助教','电子工程系');

题目

  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. 查询最低分大于70，最高分小于90的SNO列。
  14. 查询所有学生的SNAME、CNO和DEGREE列。
  15. 查询所有学生的SNO、CNAME和DEGREE列。
  16. 查询所有学生的SNAME、CNAME和DEGREE列。
  17. 查询“95033”班所选课程的平均分。
  18. 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
  19. 查询SCORE中选学一门以上课程的同学中分数为非最高分成绩的记录。
  20. 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
  21. 查询和学号为108的同学同年出生的所有学生的SNO、SNAME和SBIRTHDAY列。
  22. 查询“张旭“教师任课的学生成绩。
  23. 查询选修某课程的同学人数多于5人的教师姓名。
  24. 查询95033班和95031班全体学生的记录。
  25. 查询存在有85分以上成绩的课程CNO.
  26. 查询出“计算机系“教师所教课程的成绩表。
  27. 查询“计算机系”与“电子工程系“不同职称的教师的TNAME和PROF。
  28. 查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的CNO、SNO和DEGREE,并按DEGREE从高到低次序排序。
  29. 查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的CNO、SNO和DEGREE.
  30. 查询所有教师和同学的NAME、SEX和BIRTHDAY.
  31. 查询所有“女”教师和“女”同学的NAME、SEX和BIRTHDAY.
  32. 查询成绩比该课程平均成绩低的同学的成绩表。
  33. 查询所有任课教师的TNAME和DEPART.
  34. 查询所有未讲课的教师的TNAME和DEPART.
  35. 查询至少有2名男生的班号。
  36. 查询STUDENT表中不姓“王”的同学记录。
  37. 查询STUDENT表中每个学生的姓名和年龄。
  38. 查询STUDENT表中最大和最小的SBIRTHDAY日期值。
  39. 以班号和年龄从大到小的顺序查询STUDENT表中的全部记录。
  40. 查询“男”教师及其所上的课程。
  41. 查询最高分同学的SNO、CNO和DEGREE列。
  42. 查询和“李军”同性别的所有同学的SNAME.
  43. 查询和“李军”同性别并同班的同学SNAME.
  44. 查询所有选修“计算机导论”课程的“男”同学的成绩表

参考答案

  1. SELECT SNAME,SSEX,CLASS FROM STUDENT;
  2. SELECT DISTINCT DEPART FROM TEACHER;
  3. SELECT * FROM STUDENT;
  4. SELECT * FROM SCORE WHERE DEGREE BETWEEN 60 AND 80;
  5. SELECT * FROM SCORE WHERE DEGREE IN (85,86,88);
  6. SELECT * FROM STUDENT WHERE CLASS=‘95031’ OR SSEX=‘女’;
  7. SELECT * FROM STUDENT ORDER BY CLASS DESC;
  8. SELECT * FROM SCORE ORDER BY CNO ASC,DEGREE DESC;
  9. SELECT COUNT(*) FROM STUDENT WHERE CLASS=‘95031’;
  10. 两个答案
    • SELECT SNO,CNO FROM SCORE WHERE DEGREE=(SELECT MAX(DEGREE) FROM SCORE);
    • SELECT SNO,CNO FROM SCORE ORDER BY DEGREE DESC LIMIT 1;
  11. SELECT AVG(DEGREE) FROM SCORE WHERE CNO=‘3-105’;
  12. SELECT AVG(DEGREE), CNO FROM SCORE WHERE CNO LIKE ‘3%’ GROUP BY CNO HAVING COUNT(SNO) >= 5;
  13. SELECT SNO FROM SCORE GROUP BY SNO HAVING MIN(DEGREE)>70 AND MAX(DEGREE)<90;
  14. SELECT A.SNAME,B.CNO,B.DEGREE FROM STUDENT AS A JOIN SCORE AS B ON A.SNO=B.SNO;
  15. SELECT A.CNAME, B.SNO,B.DEGREE FROM COURSE AS A JOIN SCORE AS B ON A.CNO=B.CNO ;
  16. SELECT A.SNAME, C.CNAME,B.DEGREE FROM STUDENT A JOIN SCORE B ON A.SNO=B.SNO JOIN COURSE C ON B.CNO =C.CNO;
  17. SELECT AVG(A.DEGREE) FROM SCORE A JOIN STUDENT B ON A.SNO = B.SNO WHERE B.CLASS=‘95033’;
  18. 两个答案
    • SELECT A.* FROM SCORE A JOIN SCORE B ON A.CNO = B.CNO WHERE A.CNO=‘3-105’ AND A.DEGREE>B.DEGREE AND B.SNO=‘109’ AND B.CNO=‘3-105’;
    • SELECT A.* FROM SCORE A WHERE A.CNO=‘3-105’ AND A.DEGREE>ALL(SELECT DEGREE FROM SCORE B WHERE B.SNO=‘109’ AND B.CNO=‘3-105’);
  19. SELECT SNO FROM SCORE WHERE DEGREE<(SELECT MAX(DEGREE) FROM SCORE) GROUP BY SNO HAVING COUNT(SNO)>1 ;
  20. 见18的第二种解法
  21. SELECT SNO,SNAME,SBIRTHDAY FROM STUDENT WHERE EXTRACT(YEAR FROM SBIRTHDAY)=(SELECT EXTRACT(YEAR FROM SBIRTHDAY) FROM STUDENT WHERE SNO=‘108’);
  22. 两个答案
    • SELECT A.SNO,A.DEGREE FROM SCORE A JOIN COURSE C ON A.CNO = C.CNO JOIN TEACHER B ON B.TNO=C.TNO WHERE B.TNAME=‘张旭’;
    • SELECT CNO,SNO,DEGREE FROM SCORE WHERE CNO=(SELECT X.CNO FROM COURSE X,TEACHER Y WHERE X.TNO=Y.TNO AND Y.TNAME=‘张旭’);根据实际EXPLAIN此SELECT语句，第一个的扫描次数要小于第二个
  23. 两个答案
    • SELECT C.CNO FROM TEACHER A JOIN COURSE B ON A.TNO=B.TNO JOIN SCORE C ON B.CNO=C.CNO GROUP BY C.CNO HAVING COUNT(C.CNO)>5;
    • SELECT TNAME FROM TEACHER WHERE TNO IN(SELECT X.TNO FROM COURSE X,SCORE Y WHERE X.CNO=Y.CNO GROUP BY X.TNO HAVING COUNT(X.TNO)>5);实际测试1明显优于2
  24. SELECT CNO,SNO,DEGREE FROM SCORE WHERE CNO=(SELECT X.CNO FROM COURSE X,TEACHER Y WHERE X.TNO=Y.TNO AND Y.TNAME=‘张旭’);
  25. 两个答案
    • SELECT CNO FROM SCORE GROUP BY CNO HAVING MAX(DEGREE)>85;
    • SELECT DISTINCT CNO FROM SCORE WHERE DEGREE IN (SELECT DEGREE FROM SCORE WHERE DEGREE>85);
  26. 两个答案
    • SELECT A.* FROM SCORE A JOIN COURSE C ON A.CNO=C.CNO JOIN TEACHER B ON B.TNO=C.TNO WHERE B.DEPART=‘计算机系’;
    • SELECT * FROM SCORE WHERE CNO IN (SELECT A.CNO FROM COURSE A JOIN TEACHER B ON A.TNO=B.TNO AND B.DEPART=‘计算机系’);此时2略好于1，在多连接的境况下性能会迅速下降
  27. SELECT TNAME,PROF FROM TEACHER WHERE DEPART=‘计算机系’ AND PROF NOT IN (SELECT PROF FROM TEACHER WHERE DEPART=‘电子工程系’);
  28. SELECT * FROM SCORE WHERE DEGREE>ANY(SELECT DEGREE FROM SCORE WHERE CNO=‘3-245’) ORDER BY DEGREE DESC;
  29. SELECT * FROM SCORE WHERE DEGREE>ALL(SELECT DEGREE FROM SCORE WHERE CNO=‘3-245’) ORDER BY DEGREE DESC;
  30. SELECT SNAME AS NAME, SSEX AS SEX, SBIRTHDAY AS BIRTHDAY FROM STUDENT UNION SELECT TNAME AS NAME, TSEX AS SEX, TBIRTHDAY AS BIRTHDAY FROM TEACHER;
  31. SELECT SNAME AS NAME, SSEX AS SEX, SBIRTHDAY AS BIRTHDAY FROM STUDENT WHERE SSEX=‘女’ UNION SELECT TNAME AS NAME, TSEX AS SEX, TBIRTHDAY AS BIRTHDAY FROM TEACHER WHERE TSEX=‘女’;
  32. 三个答案
    • SELECT A.TNAME,A.DEPART FROM TEACHER A JOIN COURSE B ON A.TNO=B.TNO;
    • SELECT TNAME,DEPART FROM TEACHER A WHERE EXISTS (SELECT * FROM COURSE B WHERE A.TNO=B.TNO);
    • SELECT TNAME,DEPART FROM TEACHER WHERE TNO IN (SELECT TNO FROM COURSE); 实际分析，第一种解法貌似更好，至少扫描次数最少。
  33. 三个答案
    • SELECT TNAME,DEPART FROM TEACHER A LEFT JOIN COURSE B USING(TNO) WHERE ISNUL (B.TNO);
    • SELECT TNAME,DEPART FROM TEACHER A WHERE NOT EXISTS (SELECT * FROM COURSE B WHERE A.TNO=B.TNO);
    • SELECT TNAME,DEPART FROM TEACHER WHERE TNO NOT IN (SELECT TNO FROM COURSE); NOT IN的方法效率最差，其余两种差不多
  34. SELECT CLASS FROM STUDENT A WHERE SSEX=‘男’ GROUP BY CLASS HAVING COUNT(SSEX)>1;
  35. SELECT * FROM STUDENT A WHERE SNAME NOT LIKE ‘王%’;
  36. SELECT SNAME, DATE_PART(‘YEAR’, AGE(SBIRTHDAY)) AS AGE FROM STUDENT;
  37. SELECT SNAME,SBIRTHDAY AS THEMAX FROM STUDENT WHERE SBIRTHDAY =(SELECT MIN(SBIRTHDAY) FROM STUDENT) UNION SELECT SNAME,SBIRTHDAY AS THEMIN FROM STUDENT WHERE SBIRTHDAY =(SELECT MAX(SBIRTHDAY) FROM STUDENT);
  38. SELECT CLASS,AGE(SBIRTHDAY) AS AGE FROM STUDENT ORDER BY CLASS DESC,AGE DESC;
  39. SELECT A.TNAME,B.CNAME FROM TEACHER A JOIN COURSE B ON A.TNO = B.TNO WHERE A.TSEX=‘男’;
  40. SELECT A.* FROM SCORE A WHERE DEGREE=(SELECT MAX(DEGREE) FROM SCORE B );
  41. SELECT SNAME FROM STUDENT A WHERE SSEX=(SELECT SSEX FROM STUDENT B WHERE B.SNAME=‘李军’);
  42. SELECT SNAME FROM STUDENT A WHERE SSEX=(SELECT SSEX FROM STUDENT B WHERE B.SNAME=‘李军’ ) AND CLASS=(SELECT CLASS FROM STUDENT C WHERE C.SNAME=‘李军’);
  43. 两个答案
    • SELECT A.* FROM SCORE A JOIN STUDENT B ON A.SNO = B.SNO JOIN COURSE C ON A.CNO = C.CNO WHERE B.SSEX=‘男’ AND C.CNAME=‘计算机导论’;
    • SELECT * FROM SCORE WHERE SNO IN(SELECT SNO FROM STUDENT WHERE SSEX=‘男’) AND CNO=(SELECT CNO FROM COURSE WHERE CNAME=‘计算机导论’);

Comments

comments powered by Disqus