資料庫 SQL 經典例題 (上篇)

用用戶端運作SQL 建立表 (如果出錯可能是字段類型的錯誤)

我用的是psql 都一樣.

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS "public"."course";
CREATE TABLE "public"."course" (
  "cid" varchar(10) COLLATE "pg_catalog"."default",
  "cname" varchar(10) COLLATE "pg_catalog"."default",
  "tid" varchar(10) COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO "public"."course" VALUES ('01', '國文', '02');
INSERT INTO "public"."course" VALUES ('02', '數學', '01');
INSERT INTO "public"."course" VALUES ('03', '英語', '03');

DROP TABLE IF EXISTS "public"."sc";
CREATE TABLE "public"."sc" (
  "sid" varchar(10) COLLATE "pg_catalog"."default",
  "cid" varchar(10) COLLATE "pg_catalog"."default",
  "score" numeric(18,1)
)
;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO "public"."sc" VALUES ('01', '03', 99.0);
INSERT INTO "public"."sc" VALUES ('02', '01', 70.0);
INSERT INTO "public"."sc" VALUES ('02', '03', 80.0);
INSERT INTO "public"."sc" VALUES ('03', '01', 80.0);
INSERT INTO "public"."sc" VALUES ('03', '03', 80.0);
INSERT INTO "public"."sc" VALUES ('04', '01', 50.0);
INSERT INTO "public"."sc" VALUES ('05', '01', 76.0);
INSERT INTO "public"."sc" VALUES ('06', '01', 31.0);
INSERT INTO "public"."sc" VALUES ('06', '03', 34.0);
INSERT INTO "public"."sc" VALUES ('07', '03', 98.0);
INSERT INTO "public"."sc" VALUES ('01', '02', 68.6);
INSERT INTO "public"."sc" VALUES ('02', '02', 68.6);
INSERT INTO "public"."sc" VALUES ('03', '02', 68.6);
INSERT INTO "public"."sc" VALUES ('04', '02', 68.6);
INSERT INTO "public"."sc" VALUES ('05', '02', 68.6);
INSERT INTO "public"."sc" VALUES ('07', '02', 68.6);
INSERT INTO "public"."sc" VALUES ('01', '01', 34.0);

DROP TABLE IF EXISTS "public"."student";
CREATE TABLE "public"."student" (
  "sid" varchar(10) COLLATE "pg_catalog"."default",
  "sname" varchar(10) COLLATE "pg_catalog"."default",
  "sage" timestamp(6),
  "ssex" varchar(10) COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO "public"."student" VALUES ('01', '趙雷', '1990-01-01 00:00:00', '男');
INSERT INTO "public"."student" VALUES ('02', '錢電', '1990-12-21 00:00:00', '男');
INSERT INTO "public"."student" VALUES ('03', '孫風', '1990-05-20 00:00:00', '男');
INSERT INTO "public"."student" VALUES ('04', '李雲', '1990-08-06 00:00:00', '男');
INSERT INTO "public"."student" VALUES ('05', '周梅', '1991-12-01 00:00:00', '女');
INSERT INTO "public"."student" VALUES ('06', '吳蘭', '1992-03-01 00:00:00', '女');
INSERT INTO "public"."student" VALUES ('07', '鄭竹', '1989-07-01 00:00:00', '女');
INSERT INTO "public"."student" VALUES ('08', '王菊', '1990-01-20 00:00:00', '女');
INSERT INTO "public"."student" VALUES ('09', '高人', '2020-08-14 11:05:31', '男');


DROP TABLE IF EXISTS "public"."teacher";
CREATE TABLE "public"."teacher" (
  "tid" varchar(10) COLLATE "pg_catalog"."default",
  "tname" varchar(10) COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO "public"."teacher" VALUES ('01', '張三');
INSERT INTO "public"."teacher" VALUES ('02', '李四');
INSERT INTO "public"."teacher" VALUES ('03', '王五');

問題來了-------前方高能!

1.查詢“數學”課程比“國文”課程成績高的所有學生的學号；
select a.sid 學号 from (select sid,score from sc where cid='01') a,(select sid,score from sc where cid='02') b where a.sid=b.sid and a.score<b.score
2.查詢平均成績大于60分的同學的學号和平均成績；
select sid,AVG(score) from SC group by sid having avg(score) > 60;
3.查詢所有同學的學号、姓名、選課數、總成績
select  a.sid 學号, a.sname 姓名, count(b.cid) 選課數, sum(b.score) 總成績 from student a left join sc b on a.sid=b.sid GROUP BY a.sid,a.sname ORDER BY a.sid
4.查詢姓“李”的老師的個數；
select count(tname) from teacher where tname LIKE '李%' GROUP BY tname
5.查詢沒學過“張三”老師課的同學的學号、姓名;
select s.sid 學号,s.sname 姓名 from Student as s where sid not in(select DISTINCT(s.sid) from Course as c,SC as s,Teacher as t where c.cid=s.cid AND c.tid=t.tid AND t.tname='張三'); 
6.查詢學過國文并且也學過數學課程的同學的學号、姓名；
select a.sid,a.sname  from 
(select s.sid,s.sname from Student as s,Course as c,SC as  sc where c.cname='數學' AND s.sid=sc.sid AND c.cid=sc.cid) a, 
(select s.sid,s.sname from Student as s,Course as c,SC as  sc where c.cname='國文' AND s.sid=sc.sid AND c.cid=sc.cid) b
 WHERE a.sid=b.sid;
7.查詢學過“張三”老師所教的所有課的同學的學号、姓名；
select a.sid,a.sname from student a,sc b, course c,teacher d where d.tname='張三' and d.tid=c.tid and c.cid=b.cid and b.sid=a.sid ORDER BY a.sid
8.查詢課程編号“01”的成績比課程編号“02”課程低的所有同學的學号、姓名；
select a.sid,a.sname from (select s.sid,s.sname,score from sc,student s where sc.cid='01' and sc.sid=s.sid) a,(select sid,score from sc where cid='02') b where a.sid=b.sid and a.score<b.score
9.查詢所有課程成績小于60分的同學的學号、姓名；
select s.sid,s.sname,sc.score from Student as s,SC as sc where sc.score<60 AND sc.sid=s.sid;
10.查詢沒有學全所有課的同學的學号、姓名；
select a.sid,a.sname from student a ,sc b where a.sid=b.sid  GROUP BY a.sid,a.sname HAVING count(b.cid)<(select count(cid) from course)
11.查詢至少有一門課與學号為“01”的同學所學相同的同學的學号和姓名
select DISTINCT(a.sid),a.sname from student a ,sc b where a.sid=b.sid and b.cid in (select cid from sc where sid='01') ORDER BY a.sid
12.查詢和"01"号的同學學習的課程完全相同的其他同學的學号和姓名 
select * from Student where sid in (
SELECT sid FROM SC WHERE 
cid in (SELECT cid FROM SC WHERE sid='01') AND sid!='01' GROUP BY sid HAVING COUNT(*)=(SELECT COUNT(*) FROM SC WHERE sid='01')
)
13.把“SC”表中“張三”老師教的課的成績都更改為此課程的平均成績；
update sc set score = (select avg(score) from sc ) where cid in (select c.cid from course c left join teacher t on t.tid = c.tid where t.tname = '張三') ;
14、查詢沒學過"張三"老師講授的任一門課程的學生姓名
查詢張三老師教的學生的學号:
select sc.sid from sc ,course c,teacher t where sc.cid=c.cid and c.tid=t.tid and tname='張三' 
最終答案:
select sname from student where sid not in (
select sc.sid from sc ,course c,teacher t where sc.cid=c.cid and c.tid=t.tid and tname='張三' 
)
15、查詢兩門及其以上不及格課程的同學的學号，姓名及其平均成績 (沒查不考試的或者隻考了一門的)
查詢有不及格的課程的學号 : 
select * from sc  where score<60
查詢不及格的課程>=2次的
 select sid,avg(score) from sc  where score<60 GROUP BY sid HAVING count(*)>=2
聯合多表查詢姓名 
最終答案:
select sc.sid,s.sname,avg(sc.score) from student s left join sc on s.sid=sc.sid where score<60 GROUP BY sc.sid,s.sname HAVING count(*)>=2
16、檢索"01"課程分數小于60，按分數降序排列的學生資訊
查詢01課程分數小于60的學生學号:
select sid from sc where score < 60 and cid='01'
和學生表連接配接:
最終答案:
select * from student s left join sc on s.sid=sc.sid where sc.score<60 and sc.cid='01' ORDER BY sc.score
17、按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績(group by， order by 後面跟數字，指的是 select 後面選擇的列（屬性），1 代表第一個列（屬性），依次類推)
SELECT a.*
       ,SUM(CASE WHEN b.cid='01' THEN b.score ELSE 0 END) AS 國文
       ,SUM(CASE WHEN b.cid='02' THEN b.score ELSE 0 END) AS 數學
       ,SUM(CASE WHEN b.cid='03' THEN b.score ELSE 0 END) AS 英語
       ,AVG(CASE WHEN b.score IS NULL THEN 0 ELSE b.score END) 平均分
FROM Student a
LEFT JOIN SC b
ON a.sid=b.sid
GROUP BY 1,2,3,4
ORDER BY 平均分 DESC;
18.查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率(考慮不考慮沒考的學生的分數?)
concat(a,'%')拼接  round(sql,2)取2位小數 DECIMAL(10,2)總共10位2位小數

SELECT co.cid 課程,co.cname 課程名
,max(sc.score) 最大值
,min(sc.score) 最小值
,AVG(sc.score) 平均分
,CONCAT(round((SUM(CASE WHEN sc.score>=60 THEN 1.0 ELSE 0 END)/COUNT(sc.sid)*100),2),'%') 及格率
,CONCAT(round((SUM(CASE WHEN sc.score>=70 AND sc.score<80 THEN 1.0 ELSE 0 END)/COUNT(sc.sid)*100),2),'%') 中等率
,CONCAT(round((SUM(CASE WHEN sc.score>=80 AND sc.score<90 THEN 1.0 ELSE 0 END)/COUNT(sc.sid)*100),2),'%') 優良率
,CONCAT(round((SUM(CASE WHEN sc.score>=90  THEN 1.0 ELSE 0 END)/COUNT(sc.sid)*100),2),'%') 優秀率
FROM course as co
LEFT JOIN
sc as sc ON co.cid=sc.cid
GROUP BY co.cid,co.cname

19.按各科平均成績從低到高和及格率的百分數從高到低順序
SELECT cid,avg_score,CONCAT(de,'%') 及格率
 FROM(
SELECT sc0.cid,
    AVG(sc0.score) avg_score,
    CAST(
		( SELECT COUNT(1) FROM SC WHERE cid=sc0.cid AND score>=60)*1.0/(SELECT COUNT(1) FROM SC WHERE cid=sc0.cid)*100 AS DECIMAL(10,2)) de
FROM SC sc0
GROUP BY sc0.cid
ORDER BY avg_score,de DESC)a;
20、查詢學生的總成績并進行排名
rank 函數 自動排序
select b.sid,b.sname,b.grade, rank() over (ORDER BY b.grade DESC) as rank from 
 (SELECT a.sid,s.sname,SUM(a.score) AS grade FROM sc a left join student s on s.sid=a.sid GROUP BY a.sid,s.sname ORDER BY grade DESC)b 
 GROUP BY 1,2,3 ORDER BY rank ;
21、查詢不同老師所教不同課程平均分從高到低顯示 

SELECT t.tid,t.tname,c.cid,c.cname,sum(sc.score) 總分,count(sid) 數量, round((sum(sc.score)/count(sid)),2) 平均分 from teacher t 

left join course c on t.tid=c.tid

left join sc sc on c.cid=sc.cid

GROUP BY 1,2,3,4 ORDER BY 平均分 DESC

後面還有下回分解 !

資料庫 SQL 經典例題 (上篇)

繼續閱讀

Testlink安裝部署之XAMPP

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述