sql题

常见的学生老师课程分数：

建表：

CREATE TABLE students

(sno VARCHAR(3) NOT NULL,

sname VARCHAR(4) NOT NULL,

ssex VARCHAR(2) NOT NULL,

sbirthday DATETIME,

class VARCHAR(5))

CREATE TABLE courses

(cno VARCHAR(5) NOT NULL,

cname VARCHAR(10) NOT NULL,

tno VARCHAR(10) NOT NULL)

CREATE TABLE scores

(sno VARCHAR(3) NOT NULL,

cno VARCHAR(5) NOT NULL,

degree NUMERIC(10, 1) NOT NULL)

CREATE TABLE 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','助教','电子工程系');

---------------------

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

SELECT Cno,AVG(Degree)

FROM Scores

WHERE Cno LIKE '3%'

GROUP BY Cno

HAVING COUNT(Sno) >= 5;

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

SELECT Sno

FROM Scores

GROUP BY Sno

HAVING MAX(Degree)<90 AND MIN(Degree)>70;

18.假设使用如下命令建立了一个grade表：

CREATE TABLE grade(low TINYINT,upp TINYINT,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');

现查询所有同学的Sno、Cno和rank列。

解法1：

SELECT SNO,CNO,RANK FROM scores,GRADE WHERE DEGREE BETWEEN LOW AND UPP

ORDER BY Sno;

解法2：

SELECT Sno,Cno,rank

FROM Scores INNER JOIN grade

ON(Scores.Degree>=grade.low AND Scores.Degree<=grade.upp)

ORDER BY Sno;

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'

ORDER BY s1.Sno;

另外两种解法：

SELECT A.* FROM SCOREs A JOIN SCOREs B WHERE A.CNO='3-105' AND A.DEGREE>B.DEGREE AND

B.SNO='109' AND B.CNO='3-105';

SELECT A.* FROM SCOREs A WHERE A.CNO='3-105' AND A.DEGREE>ALL(SELECT DEGREE FROM

SCOREs B WHERE B.SNO='109' AND B.CNO='3-105');

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

SELECT * FROM Scores

GROUP BY Sno

HAVING COUNT(cno)>1

AND

Degree!=MAX(Degree);

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

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'

ORDER BY s1.Sno;

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

解法1：

SELECT SNO,SNAME,SBIRTHDAY

FROM STUDENTs WHERE YEAR(SBIRTHDAY)=(SELECT YEAR(SBIRTHDAY) FROM STUDENTs WHERE SNO='108');

解法2：

SELECT s1.Sno,s1.Sname,s1.Sbirthday

FROM Students AS s1 INNER JOIN Students AS s2

ON(YEAR(s1.Sbirthday)=YEAR(s2.Sbirthday))

WHERE s2.Sno='108';

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

解法1：

SELECT Sno,Degree

FROM Scores INNER JOIN Courses

ON(Scores.Cno=Courses.Cno) INNER JOIN Teachers

ON(Courses.Tno=Teachers.Tno)

WHERE Teachers.Tname='张旭';

解法2：

SELECT A.SNO,A.DEGREE

FROM scores A JOIN (TEACHERs B,COURSEs C)

ON A.CNO=C.CNO AND B.TNO=C.TNO

WHERE B.TNAME='张旭';

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

SELECT DISTINCT Tname

FROM Scores INNER JOIN Courses

ON(Scores.Cno=Courses.Cno) INNER JOIN Teachers

ON(Courses.Tno=Teachers.Tno)

WHERE Courses.Cno IN(SELECT Cno FROM Scores GROUP BY(Cno) HAVING COUNT(Sno)>5);

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

SELECT Tname,Cname,SName,Degree

FROM Teachers INNER JOIN Courses

ON(Teachers.Tno=Courses.Tno) INNER JOIN Scores

ON(Courses.Cno=Scores.Cno) INNER JOIN Students

ON(Scores.Sno=Students.Sno)

WHERE Teachers.Depart='计算机系'

ORDER BY Tname,Cname,Degree DESC;

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

SELECT Tname,Prof

FROM Teachers

WHERE Depart='计算机系' AND Prof NOT IN(

SELECT DISTINCT Prof

FROM Teachers

WHERE Depart='电子工程系');

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

SELECT Cno,Sno,Degree

FROM Scores

WHERE Cno='3-105' AND Degree > ANY(

SELECT Degree

FROM Scores

WHERE Cno='3-245')

ORDER BY Degree DESC;

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

SELECT Cno,Sno,Degree

FROM Scores

WHERE Cno='3-105' AND Degree > ALL(

SELECT Degree

FROM Scores

WHERE Cno='3-245')

ORDER BY Degree DESC;

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

SELECT Sname,Ssex,Sbirthday

FROM Students

WHERE Ssex='女'

UNION

SELECT Tname,Tsex,Tbirthday

FROM Teachers

WHERE Tsex='女';

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

SELECT s1.*

FROM Scores AS s1 INNER JOIN (

SELECT Cno,AVG(Degree) AS aDegree

FROM Scores

GROUP BY Cno) s2

ON(s1.Cno=s2.Cno AND s1.Degree<s2.aDegree);

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

SELECT Tname,Depart

FROM Teachers

WHERE Tno IN(

SELECT Tno

FROM Courses

);

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

SELECT Tname,Depart

FROM Teachers

WHERE Tno NOT IN(

SELECT Tno

FROM Courses

);

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

SELECT Class,COUNT(1) AS boyCount

FROM Students

WHERE Ssex='男'

GROUP BY Class

HAVING boyCount>=2;

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

SELECT Sname,YEAR(NOW())-YEAR(Sbirthday) AS Sage

FROM Students;

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

SELECT MIN(Sbirthday),MAX(Sbirthday)

FROM Students;

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

SELECT Teachers.Tname,Courses.Cname

FROM Teachers INNER JOIN Courses

ON(Teachers.Tno=Courses.Tno)

WHERE Teachers.Tsex='男';

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

SELECT *

FROM Scores

GROUP BY Cno

HAVING Degree=Max(Degree);

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

SELECT s1.Sname

FROM Students AS s1 INNER JOIN Students AS s2

ON(s1.Ssex=s2.Ssex AND s1.Class=s2.Class)

WHERE s2.Sname='李军';

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

SELECT *

FROM Scores

WHERE Sno IN (

SELECT Sno

FROM Students

WHERE Ssex='男') AND

Cno IN (

SELECT Cno

FROM Courses

WHERE Cname='计算机导论');

递归查询：

CREATE TABLE `digui` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`parent_id` int(255) DEFAULT NULL,

`name` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

表结构

SELECT t1.name 城市,t2.name 街区,t3.name 道路

FROM digui t1

LEFT JOIN digui t2 ON t1.id = t2.parent_id

LEFT JOIN digui t3 ON t2.id = t3.parent_id

WHERE t1.id = '1';

结果

参考链接：https://blog.csdn.net/mrbcy/article/details/68965271

https://blog.csdn.net/qaz13177_58_/article/details/5575711/

最后编辑于：2019.08.14 13:44:02

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 202,905评论 5赞 476
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,140评论 2赞 379
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 149,791评论 0赞 335
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,483评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,476评论 5赞 364
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,516评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,905评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,560评论 0赞 256
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,778评论 1赞 296
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,557评论 2赞 319
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,635评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,338评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,925评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,898评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,142评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,818评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,347评论 2赞 342

sql题

推荐阅读更多精彩内容