SQL经典题型

快来打我* 2021-11-16 15:52 408阅读 0赞

总共 4 张表  包括 学生表，课程表，成绩表，老师表

student (sId,sName,sex,age)   学生Id，姓名，性别，年龄

course(cId,cName,tId)  课程Id，课程名称，教师Id

grand(sId,cId,score)  学生Id， 课程Id，成绩

teacher(tId,tName)  教师Id，教师姓名

1 创建学生表

create table student(  
sId int  not  null  primary key  auto\_increment,  
sName varchar(10)  not null ,  
sex varchar(4) not null,  
age int not null );

2 创建课程表

create table course(  
cId int  not  null  primary key  auto\_increment,  
cName varchar(10)  not null，  
tId int not null );

3 创建 成绩表

create table grand(  
sId int  not  null ,  
cId int  not  null，  
score  int not null );

4 创建 教师表

create table teacher(  
tId int  not  null  primary key  auto\_increment,  
tName varchar(10)  not null);

插入学生数据

*  insert into student(sName,sex,age) VALUES("张三","男",24);
 *  insert into student(sName,sex,age) VALUES("李四","男",21);
 *  insert into student(sName,sex,age) VALUES("王五","男",20);
 *  insert into student(sName,sex,age) VALUES("赵六","男",18);
 *  insert into student(sName,sex,age) VALUES("凤七","女",26);
 *  insert into student(sName,sex,age) VALUES("婉儿","女",19);

![2019072823074836.png][]

插入课程数据

![20190728230042234.png][]

插入成绩数据

![20190729001038584.png][]

插入教师数据

![20190728230134900.png][]

上述数据准备好之后，开始进行SQL编写

**-- 1.查询'1'课程比'2'课程成绩高的所有学生的学号 **

SELECT a.sId FROM \`grand\` a,grand b    
WHERE a.cId=1  AND b.cId=2 AND a.sId=b.sId AND a.score>b.score

![20190728230346464.png][]

**-- 2.查询同学的学号和平均成绩**

SELECT sId,AVG(score) FROM grand GROUP BY sId

![20190728230428422.png][]

**-- 3 查询(平均成绩大于60分的)同学的学号和平均成绩**

SELECT sId, AVG(score) FROM  grand  GROUP BY sId HAVING AVG(score)>60

![20190728230508630.png][]

**-- 4.查询所有同学的学号、姓名、选课数、总成绩  **

SELECT s.sId,s.sName,COUNT(cName) ,SUM(score) FROM student s LEFT JOIN grand g  
ON s.sId = g.sId LEFT JOIN course c ON g.cId = c.cId GROUP BY s.sId,s.sName

![20190728230821604.png][]

**-- 5 查询老师的个数**  
SELECT COUNT(tName),tName FROM teacher GROUP BY tName

![20190728231026155.png][]

**-- 6 查询姓“亮”的老师的个数**

SELECT COUNT(tName),tName FROM teacher WHERE tName LIKE '亮%'  GROUP BY tName

![20190728230855734.png][]

**-- 7 .查询(上过'天天'老师课的)学生的学号、姓名 **

SELECT sId,sName FROM student WHERE sId  NOT IN   
(SELECT s.sId FROM student s,grand g,course c WHERE s.sId =g.sId AND c.cId =g.cId AND c.cName IN  
(SELECT c.cName FROM teacher t,course c WHERE c.tId=t.tId AND t.tName ='天天'))

![20190728235642619.png][]

**-- 8 .查询(没上过'天天'老师课的)学生的学号、姓名 **

SELECT sId,sName FROM student WHERE sId NOT IN   
(SELECT s.sId FROM student s,grand g,course c WHERE s.sId =g.sId AND c.cId =g.cId AND c.cName IN  
(SELECT c.cName FROM teacher t,course c WHERE c.tId=t.tId AND t.tName ='天天'))

![2019072823571492.png][]

**-- 9.查询学过编号'1'课程的同学的学号、姓名；**

SELECT s.sId,s.sName FROM student s,grand g  
WHERE s.sId = g.sId AND g.cId='1'

![20190729001137367.png][]

**-- 10.查询学过编号'1'并且也学过编号'2'课程的同学的学号、姓名； **

SELECT s.sId,s.sName FROM student s,grand g,grand g1  
WHERE s.sId = g.sId AND g.sId = g1.sId AND g.cId='1' AND g1.cId='2'

![20190729001158560.png][]

**-- 11 查询每个老师所教课程的数量**

SELECT t.tName,c.cName, COUNT(cName) FROM course c,teacher t WHERE c.tId = t.tId GROUP BY tName;

![20190729002938535.png][]

**-- 12 查询老师所教课程数为1门课**

SELECT t.tName,c.cName, COUNT(cName) AS num FROM course c,teacher t   
WHERE c.tId = t.tId  GROUP BY tName HAVING num =1

![20190729004000862.png][]

**-- 13 查询老师所教课程数为2门课**

SELECT t.tName,c.cName, COUNT(cName) AS num FROM course c,teacher t   
WHERE c.tId = t.tId  GROUP BY tName HAVING num =2

![20190729004027587.png][]

**-- 14.查询(课程编号'2'的成绩比课程编号'1'课程低的)所有同学的学号、姓名  **  
   
SELECT s.sId,s.sName FROM student s,grand g,grand g1   
WHERE s.sId = g.sId AND g.sId = g1.sId AND g.cId =1 AND g1.cId =2 AND g.score<g1.score;

![20190729004713562.png][]

**-- 15.查询课程成绩大于80分的同学的学号、姓名、课程、成绩**

SELECT s.sId,s.sName,c.cName,g.score FROM student s,grand g,course c  
WHERE s.sId=g.sId AND c.cId = g.cId HAVING  g.score >80

![2019072901024424.png][]

**-- 16查询课程成绩大于80分的同学的学号、姓名、课程、成绩，并按成绩进行降序排名**

SELECT s.sId,s.sName,c.cName,g.score FROM student s,grand g,course c  
WHERE s.sId=g.sId AND c.cId = g.cId  HAVING g.score >80 ORDER BY score DESC

![20190729010511226.png][]

[2019072823074836.png]: /images/20211116/c471f6016aaf4dca9834cc3d54fa8ba4.png
[20190728230042234.png]: /images/20211116/bcd305a07146470e9f0746bdcab1117e.png
[20190729001038584.png]: /images/20211116/1a4d9d280961428abb867855614a50fc.png
[20190728230134900.png]: /images/20211116/f067e3dd882b4e7c8d85c7ed62035150.png
[20190728230346464.png]: /images/20211116/5ca816a690af4b909cdc1bf24524203a.png
[20190728230428422.png]: /images/20211116/2123cdd40e734bcca3dc1e8d382c8f53.png
[20190728230508630.png]: /images/20211116/5c1883c412904749aca322f4be61f96d.png
[20190728230821604.png]: /images/20211116/10a63213729040658bd5a79c6eaf267a.png
[20190728231026155.png]: /images/20211116/9654b47d9f11488891940a7f2c68ab00.png
[20190728230855734.png]: /images/20211116/d48ac7f9600547babbc416008e0c07f5.png
[20190728235642619.png]: /images/20211116/0ebf12588ae241858fe1cc5eed2e2303.png
[2019072823571492.png]: /images/20211116/ad3d064359774eeb98a9bbac31a7deb6.png
[20190729001137367.png]: /images/20211116/a5169bc0887346cdb170b951495e7a8a.png
[20190729001158560.png]: /images/20211116/a0a2dff79e6445248e851cbf8b4a6626.png
[20190729002938535.png]: /images/20211116/662c861a37a442cf93f9f1b79707bbe2.png
[20190729004000862.png]: /images/20211116/c069b5595fe54cd4a1a115f5a2cdf49e.png
[20190729004027587.png]: /images/20211116/19befec923384551a760dd6be193c10a.png
[20190729004713562.png]: /images/20211116/ff4c636289c744259001af6d5c9aa34b.png
[2019072901024424.png]: /images/20211116/9eaf427769824a57a53e272dd0d3348b.png
[20190729010511226.png]: /images/20211116/26e9bb69573b4162886571a6089745dd.png