面试SQL题

刷到一个小公司的sql面试题,记录一下思路

1、现有一张表ainfo,表数据如下:

Id	Name	Age	Birth	Sex	Memo
1	徐洪国	23	1998-03-23	男	本科
2	王芳芳	26	1995-10-11	女	高中
3	徐风年	24	1997-04-09	男	硕士
4	李明	30	1991-11-23	男	大专
5	郑洋	27	1994-12-28	男	博士
6	-	-	-	-	-

1）请编写SQL语句对年龄进行升序排列

SELECT * FROM AINFO ORDER BY AGE ASC; 
--  ASC升序 DESC倒序

2）请编写SQL语句查询对“徐”姓开头的人员名单

SELECT * FROM AINFO WHERE NAME LIKE '徐%';
-- % 通配符 代表零个或多个字符
-- _ 通配符 仅一个字符

3）请统计表中男女各有多少人?

SELECT Sex,COUNT(Sex) FROM  AINFO GROUP BY Sex;
-- GROUP BY 针对某些数据进行分组，并可以拿到分组数据中，对应聚合函数的值，例如MAX(Sex)、MIN(Sex)等;
也可以实现去重的功能

4）现有一张表 binfo字段和 ainfo相同请编写SQL语句将 ainfo中的表备份到 binfo中

-- 先建表 后插入数据
CREATE TABLE BINFO LIKE AINFO;
INSERT INTO BINFO SELECT * FROM AINFO;
-- 或者  
-- 在表不存在的情况下，也可以直接备份目标表
CREATE TABLE BINFO AS SELECT * FROM AINFO;

2、现有一张表 user_score,结构如下:

Name	Kecheng	Fenshu
张三	语文	81
张三	数学	75
李四	语文	76
李四	数学	90
王五	语文	81
王五	数学	100
王五	英语	90

1）用一条SQL语句查询出每门课都大于80分的学生姓名

SELECT NAME 
FROM
	USER_SCORE 
WHERE
	NAME NOT IN ( SELECT NAME FROM USER_SCORE WHERE FENSHU <= 80 GROUP BY NAME ) 
GROUP BY
NAME
-- 1、首页我们拿到在这个表中FENSHU低于80的人并根据NAME进行分组  拿到分数低于80的人
-- 2、再用NOT IN这个表排除掉 1）中的数据就可以拿到正确结果集

2）查询出各科的平均成绩,显示字段为:学科,平均分

SELECT
	KECHENG AS 学科,
	AVG( FENSHU ) AS 平均分 
FROM
	USER_SCORE 
GROUP BY
	KECHENG
-- AS 可以为某一列或者某一张表，起一个别名
-- AVG() 拿到某一列的平均值

3）查询出单科成绩最高的,显示字段为:姓名,学科,成绩

SELECT
	B.* 
FROM
	( SELECT MAX(FENSHU) FENSHU, KECHENG FROM USER_SCORE GROUP BY KECHENG ) A
	LEFT JOIN USER_SCORE B ON A.FENSHU = B.FENSHU 
	AND A.KECHENG = B.KECHENG
-- 1.先通过GROUP BY KECHENG 对课程进行分组，拿到每门课程最高的分数
-- 2.再通过 LEFT JOIN(左连接) USER_SCORE 用，对应课程的分数与最高分数 进行对比，拿到人员的姓名

3、一个叫team的表,里面只有一个字段name,一共有4条纪录,分别是a,b,c,d,对应四个球队、,现在四个球队进行比塞,用一条SQL语句显示所有可能的比赛组合

SELECT
	B.NAME,
	C.NAME 
FROM
	TEAM B
	LEFT JOIN TEAM C ON B.NAME != C.NAME
-- 利用LEFT JOIN的特性让两个表的记录（相乘） 再通过NAME进行排除

4、现在有下面四张表,请按照要求编写SQL语句

Student( s_no, sname, sage,sex)学生表
Course( c_no cname,t_no)课程表
Sc( s_no,c_no, score)成绩表
Teacher( t_no, tname)教师表

1）查询“001"课程比“002"课程成绩高的所有学生的学号

SELECT
	A.S_NO 
FROM
	( SELECT * FROM SC WHERE C_NO = '001' ) A,
	( SELECT * FROM SC WHERE C_NO = '002' ) B 
WHERE
	A.S_NO = B.S_NO 
	AND A.SCORE > B.SCORE
======= 等价与
SELECT
	A.S_NO 
FROM
	( SELECT * FROM SC WHERE C_NO = '001' ) A
	INNER JOIN
	( SELECT * FROM SC WHERE C_NO = '002' ) B 
WHERE
	A.S_NO = B.S_NO 
	AND A.SCORE > B.SCORE
-- 1.将课程001与002的成绩都查阅出来，
-- 2.根据条件筛选是否为同一个学生，再筛选 001成绩比002好的学校的工号

2）检索至少选修了两门课程的学生学号

SELECT
	S_NO,
	COUNT( S_NO ) 
FROM
	SC 
GROUP BY
	S_NO 
HAVING
	COUNT( S_NO ) > 2 
-- 1.查询成绩表，拿到学校总共有多少门成绩，根据GROUP BY S_NO来对学号进行分组，
-- 2.对记录数大于2（选修了几门课）的数据进行筛选
-- HAVING字句可以让我们筛选成组后的各种数据，字句在聚合前先筛选记录，也就是说作用在GROUP BY和HAVING字句前，而 HAVING子句在聚合后对组记录进行筛选。我的理解就是真实表中没有此数据，这些数据是通过一些函数生存
-- 简单来说就是，HAVING 可以对分组后的数据进行对应的筛选，而WHERE是针对GROUP BY之前的数据进行筛选。

3）查询学过“001”并且也学过编号“"002”课程的同学的学号、姓名。

SELECT
	S_NO,
	SNAME 
FROM
	STUDENT 
WHERE
	S_NO IN 
	(
	SELECT
		A.S_NO 
	FROM
		( SELECT * FROM SC WHERE C_NO = '1' ) A,
		( SELECT * FROM SC WHERE C_NO = '2' ) B 
	WHERE
		A.S_NO = B.S_NO 
	)
-- 1.拿到学过001与002的学生列表，并进行内连接
-- 2.再针对这些数据进行工号的比对，查询该行记录是否学号相同，相同代表学生学过001，也学过002

4）查询至少学过学号为“001"同学所有一门课的其他同学学号和姓名。（题目有点绕，学习过001同学所有课名中其中一门的学号，及姓名）

SELECT
	S_NO,
	SNAME 
FROM
	STUDENT 
WHERE
	S_NO IN (
	SELECT
		S_NO 
	FROM
		SC 
	WHERE
		C_NO IN ( SELECT C_NO FROM SC WHERE S_NO = '001' ) 
		AND S_NO != '001' 
	GROUP BY
	S_NO)
-- 1.首先拿到 学习001一共学习了几门课程
-- 2.排除001对应的行的数据，再根据学号对数据进行GROUP BY分组（分组可以去重）

5）查询选修了“冯老师”所授课程的学生中,成绩最高的学生姓名及其成绩

SELECT
	N.SNAME,
	F.SCORE 
FROM
	(SELECT E.S_NO,D.SCORE FROM
		(SELECT C_NO,MAX( SCORE ) SCORE FROM SC WHERE
			C_NO IN 
			( SELECT C_NO FROM TEACHER A LEFT JOIN COURSE B ON A.T_NO = B.T_NO WHERE A.TNAME = '叶平' ) 
		GROUP BY
			C_NO 
		) D
		LEFT JOIN SC E ON D.C_NO = E.C_NO 
		AND D.SCORE = E.SCORE 
	) F
	LEFT JOIN STUDENT N ON N.S_NO = F.S_NO
-- 1.首页拿到’冯老师‘ 所授课程的C_NO
-- 2.再根据所有的课程分组拿到每门课程的最高的最高分
-- 3.再通过左连接拿到对应学生的学号
-- 4.再连接学生信息表拿到学生姓名

6）查询两门以上不及格课程的同学的学号及其平均成绩

SELECT
	S_NO,
	AVG( SCORE ) 
FROM
	SC 
WHERE
	S_NO IN ( SELECT S_NO FROM SC WHERE SCORE < 60 GROUP BY S_NO HAVING COUNT( C_NO )>= 2 ) 
GROUP BY
	S_NO
-- 1.根据成绩表拿到所有不及格的成绩，并根据S_NO进行分组 再通过 HAVING COUNT( C_NO )>= 2 拿到不及格课程>=2的人有哪些
-- 2.再去查询成绩表根据筛选后的数据进行分组，拿到对应学生的学号，与平均值

7）查询选修了所有课程的学生学号和姓名

SELECT
	S_NO,
	SNAME 
FROM
	STUDENT 
WHERE
	S_NO IN (
	SELECT
		S_NO 
	FROM
		SC 
	GROUP BY
		S_NO 
	HAVING
		COUNT( C_NO )= ( SELECT COUNT(*) FROM COURSE ))
-- 1.先拿到一共有几门课程
-- 2.再根据成绩表分组，判断学生所学的课程总数是不是等于总课程的
-- 3.最后根据S_NO学生信息表拿到对应学生的姓名跟NO