Class9：初学数据库之查询语句题库整理

内连接：求两张表交集的部分

SELECT * FROM a_table INNER JOIN b_table ON a_table.a_id = b_table.b_id

 

Select * from a,b where a.id=b.id

左连接： 以左表为基础，展示出左表全部数据和右表交集部分数据

SELECT * FROM a_table LEFT JOIN b_table ON a_table.a_id = b_table.b_id

 

右连接：以右表为基础，展示出右全部数据和左表交集部分数据

SELECT * FROM a_table RIGHT JOIN b_table ON a_table.a_id = b_table.b_id

 

题库整理：

.1、查询同时参加计算机和英语考试的学生的信息

SELECT a.* FROM student a,score b,score c
WHERE a.id=b.stu_id
AND b.c_name='计算机'
AND a.id=c.stu_id
AND c.c_name='英语';

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

注意having count（*）用法

SELECT cno,AVG(degree) FROM score WHERE cno LIKE '3%'

GROUP BY cno HAVING COUNT(*)>5;

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

SELECT score.sno,score.cno,grade.rank FROM score,grade
WHERE score.degree BETWEEN grade.low AND grade.upp;

 

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

SELECT * FROM student WHERE sno in (
SELECT sno FROM score WHERE degree >
(SELECT degree FROM score WHERE cno='3-105' AND sno='109') AND cno='3-105')

 5、选了多门课程并且是这个课程下不是最高分的

SELECT * from score as sc,(SELECT cno,max(degree) as max from score GROUP by cno) sc1
where sc.cno = sc1.cno and sc.degree < max
and SNO IN (SELECT SNO FROM SCORE GROUP BY SNO HAVING COUNT(SNO) > 1)

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

SELECT tname,prof FROM teacher a WHERE prof  NOT in

(SELECT prof FROM teacher b WHERE a.depart != b.depart)