黄色网址大全免费-黄色网址你懂得-黄色网址你懂的-黄色网址有那些-免费超爽视频-免费大片黄国产在线观看

面試題首頁 > SQL語句面試題

SQL語句面試題

001經典SQL筆試題題干

學生表：student(學號sno,學生姓名sname,出生年月sbirth,性別ssex)
成績表：score(學號sno,課程號cno,成績score)
課程表：course(課程號cno,課程名稱cname,教師號ctno)
教師表：teacher(教師號tno,教師姓名tname)
注意：下面SQL的實現以MySQL為主

002查詢課程編號為“0004”且分數小于60的學生學號，結果按按分數降序排列。

/* 
分析思路
select 查詢結果 []
from 從哪張表中查找數據 [成績表score]
where 查詢條件 [課程編號為“04”且分數小于60]
group by 分組 [沒有]
having 對分組結果指定條件 []
order by 對查詢結果排序[查詢結果按按分數降序排列];
*/
select 學號 from score where 課程號='04' and 成績 <60
order by 成績 desc;

003查詢姓“孟”老師的個數。

select count(教師號) from teacher where 教師姓名 like '孟%';

004查詢1990年出生的學生名單。

/*
查找1990年出生的學生名單
學生表中出生日期列的類型是datetime
*/
select 學號,姓名 from student where year(出生日期)=1990;

005查詢課程編號為“0002”的總成績。

select sum(成績) from score where 課程號 = '0002';

006查詢選了課程的學生人數。

select count(distinct 學號) as 學生人數 from score;

007查詢每門課程被選修的學生數。

select 課程號, count(學號) from score group by 課程號;

008查詢男生、女生人數。

/*
分析思路
group by 分組 [男生、女生人數：按性別分組
having 對分組結果指定條件 [沒有]
order by 對查詢結果排序[沒有];
*/
select 性別,count(*) from student group by 性別;

009查詢不及格的課程并按課程號從大到小排列。

select 課程號 from score where 成績<60 order by 課程號 desc;

010經典SQL筆試題題干

學生表：student(學號sno,學生姓名sname,出生年月sbirth,性別ssex)
成績表：score(學號sno,課程號cno,成績score)
課程表：course(課程號cno,課程名稱cname,教師號ctno)
教師表：teacher(教師號tno,教師姓名tname)
注意：下面SQL的實現以MySQL為主

011查詢各科成績最高和最低的分， 以如下的形式顯示：課程號，最高分，最低分

/*
分析思路
select 查詢結果 [課程ID：是課程號的別名,最高分：max(成績) ,最低分：min(成績)]
from 從哪張表中查找數據 [成績表score]
where 查詢條件 [沒有]
group by 分組 [各科成績：也就是每門課程的成績，需要按課程號分組];
*/
select 課程號,max(成績) as 最高分,min(成績) as 最低分from score
group by 課程號;

012查詢平均成績大于60分學生的學號和平均成績。

/* 
題目翻譯成大白話：
平均成績：展開來說就是計算每個學生的平均成績
這里涉及到“每個”就是要分組了
平均成績大于60分，就是對分組結果指定條件
*/
select 學號, avg(成績)from score group by 學號having avg(成績)>60;

013查詢至少選修兩門課程的學生學號。

/* 
翻譯成大白話：
第1步，需要先計算出每個學生選修的課程數據，需要按學號分組
第2步，至少選修兩門課程：也就是每個學生選修課程數目>=2，對分組結果指定條件
*/
select 學號, count(課程號) as 選修課程數目from score group by 學號 having count(課程號)>=2;

014查詢同名同性學生名單并統計同名人數。

/* 
翻譯成大白話，問題解析：
1）查找出姓名相同的學生有誰，每個姓名相同學生的人數
查詢結果：姓名,人數
條件：怎么算姓名相同？按姓名分組后人數大于等于2，因為同名的人數大于等于2
*/
select 姓名,count(*) as 人數 from student group by 姓名
having count(*)>=2;

015查詢每門課程的平均成績，結果按平均成績升序排序，平均成績相同時，按課程號降序排列。

select 課程號, avg(成績) as 平均成績from score
group by 課程號 order by 平均成績 asc,課程號 desc;

016統計每門課程的學生選修人數(超過2人的課程才統計)。

要求輸出課程號和選修人數，查詢結果按人數降序排序，若人數相同，按課程號升序排序。

select 課程號, count(學號) as '選修人數'
from score group by 課程號having count(學號)>2
order by count(學號) desc,課程號 asc;

017查詢兩門以上不及格課程的同學的學號及其平均成績。

/*第1步：得到每個學生的平均成績，顯示學號，平均成績*/
select 學號, avg(成績) as 平均成績 from score group by 學號;
/* 第2步：再加上限制條件：*/
select 學號, avg(成績) as 平均成績from score where 成績 <60 group by 學號 having count(課程號)>=2;

018經典SQL筆試題題干

學生表：student(學號sno,學生姓名sname,出生年月sbirth,性別ssex)
成績表：score(學號sno,課程號cno,成績score)
課程表：course(課程號cno,課程名稱cname,教師號ctno)
教師表：teacher(教師號tno,教師姓名tname)
注意：下面SQL的實現以MySQL為主

019查詢所有課程成績小于60分學生的學號、姓名。

/*
第1步，寫子查詢（所有課程成績 < 60 的學生）*/
select 學號  from score where 成績 < 60;
/*第2步，查詢結果：學生學號，姓名，條件是前面1步查到的學號*/
select 學號,姓名 from student where  學號 in ( select 學號 
from score where 成績 < 60);

020查詢沒有學全所有課的學生的學號、姓名。

/*
查找出學號，條件：沒有學全所有課，也就是該學生選修的課程數 < 總的課程數
【考察知識點】in，子查詢
*/
select 學號,姓名from student where 學號 in( select 學號 from score
group by 學號 having count(課程號) < (select count(課程號) from course));

021查詢出只選修了兩門課程的全部學生的學號和姓名。

select 學號,姓名from student where 學號 in( select 學號 from score
group by 學號having count(課程號)=2);

022按課程號分組取成績最大值所在行的數據。

/*我們可以使用分組（group by）和匯總函數得到每個組里的一個值（最大值，最小值，平均值等）。
但是無法得到成績最大值所在行的數據。*/
select 課程號,max(成績) as 最大成績 from score group by 課程號;
/*我們可以使用關聯子查詢來實現：*/
select * from score as a where 成績 = (select max(成績) 
from score as b where b.課程號 = a.課程號);
/*上面查詢結果課程號“0001”有2行數據，是因為最大成績80有2個
分組取每組最小值：按課程號分組取成績最小值所在行的數據*/
select * from score as a where 成績 = (select min(成績) 
from score as b where b.課程號 = a.課程號);

023查詢各科成績前兩名的記錄。

/*第1步，查出有哪些組，我們可以按課程號分組，查詢出有哪些組，對應這個問題里就是有哪些課程號*/
select 課程號,max(成績) as 最大成績from score group by 課程號;
/*第2步：先使用order by子句按成績降序排序（desc），然后使用limt子句返回topN（對應這個問題返回的成績前兩名*/
select * from score where 課程號 = '0001' order by 成績 ?desc?limit 2;
/*第3步，使用union all 將每組選出的數據合并到一起.同樣的，可以寫出其他組的（其他課程號）取出成績前2名的sql*/
(select * from score where 課程號 = '0001' order by 成績 ?desc limit 2) union all
(select * from score where 課程號 = '0002' order by 成績 ?desc limit 2) union all
(select * from score where 課程號 = '0003' order by 成績 ?desc limit 2);

024查詢所有學生的學號、姓名、選課數、總成績。

select a.學號,a.姓名,count(b.課程號) as 選課數,sum(b.成績) as 總成績
from student as a left join score as b on a.學號 = b.學號group by a.學號;

025查詢平均成績大于85的所有學生的學號、姓名和平均成績。

select a.學號,a.姓名, avg(b.成績) as 平均成績
from student as a left join score as b
on a.學號 = b.學號group by a.學號having avg(b.成績)>85;

026查詢學生的選課情況：學號，姓名，課程號，課程名稱。

select a.學號, a.姓名, c.課程號,c.課程名稱
from student a inner join score b on a.學號=b.學號
inner join course c on b.課程號=c.課程號;

027查詢課程編號為0003且課程成績在80分以上的學生的學號和姓名。

select a.學號,a.姓名
from student  as a inner join score as b on a.學號=b.學號
where b.課程號='0003' and b.成績>80;

028將每個userid對應的多個科目的成績查出來，通過UNION ALL將結果集加起來。

變成

select userId,'語文' as subjectName,chinese_score as score from tb_score1
union all
select userId,'數學' as subjectName,math_score as score from tb_score1
union all
select userId,'英語' as subjectName,english_score as score from tb_score1
union all
select userId,'政治' as subjectName,politics_score as score from tb_score1;

029使用分段[100-85],[85-70],[70-60],[<60]來統計各科成績，分別統計：各分數段人數，課程號和課程名稱。

-- 考察case表達式
select a.課程號,b.課程名稱,
sum(case when 成績 between 85 and 100 then 1 else 0 end) as '[100-85]',
sum(case when 成績 >=70 and 成<85 then 1 else 0 end) as '[85-70]',
sum(case when 成績>=60 and 成績<70 then 1 else 0 end) as '[70-60]',
sum(case when 成績<60 then 1 else 0 end) as '[<60]'
from score as a right join course as b 
on a.課程號=b.課程號 group by a.課程號,b.課程名稱;

030查詢出每門課程的及格人數和不及格人數。

-- 考察case表達式
select 課程號,
sum(case when 成績>=60 then 1 else 0 end) as 及格人數,
sum(case when 成績 <  60 then 1 else 0 end) as 不及格人數
from score group by 課程號;

031使用case…when…then 實現原本同一列下多行的不同內容作為多個字段，輸出對應內容。

轉化后

SELECT
	userid,
	SUM( CASE subjectName WHEN '語文' THEN score ELSE 0 END ) '語文',
	SUM( CASE subjectName WHEN '數學' THEN score ELSE 0 END ) '數學',
	SUM( CASE subjectName WHEN '英語' THEN score ELSE 0 END ) '英語',
	SUM( CASE subjectName WHEN '政治' THEN score ELSE 0 END ) '政治' 
FROM
	tb_score 
GROUP BY
	userid;

032使用SUM(IF()) 實現原本同一列下多行的不同內容作為多個字段，輸出對應內容。

SELECT
	userid,
	SUM( IF ( subjectName = '語文', score, 0 ) ) '語文',
	SUM( IF ( subjectName = '數學', score, 0 ) ) '數學',
	SUM( IF ( subjectName = '英語', score, 0 ) ) '英語',
	SUM( IF ( subjectName = '政治', score, 0 ) ) '政治' 
FROM
	tb_score 
GROUP BY
	userid;

033使用SUM(IF()) 生成列 + WITH ROLLUP 生成匯總行 。

MySQL提供了 group by with rollup 函數進行group by 字段的匯總，但是與order by 互斥的不能同時用。

SELECT
	IFNULL( userid, 'total' ) AS userid,
	SUM( IF ( subjectName = '語文', score, 0 ) ) '語文',
	SUM( IF ( subjectName = '數學', score, 0 ) ) '數學',
	SUM( IF ( subjectName = '英語', score, 0 ) ) '英語',
	SUM( IF ( subjectName = '政治', score, 0 ) ) '政治',
	SUM( IF ( subjectName = 'total', score, 0 ) ) AS 'total' 
FROM
	(
		SELECT
			userid,
			IFNULL( subjectName, 'total' ) AS subjectName,
			SUM( score ) AS score 
		FROM
			tb_score 
		GROUP BY userid, subjectName WITH ROLLUP 
	) AS a
GROUP BY userid
WITH ROLLUP;

034使用SUM(IF()) 生成列，直接生成匯總結果，不再利用子查詢。

SELECT IFNULL(userid,'total') AS userid,
SUM(IF(subjectName='語文',score,0)) AS '語文',
SUM(IF(subjectName='數學',score,0)) AS '數學',
SUM(IF(subjectName='英語',score,0)) AS '英語',
SUM(IF(subjectName='政治',score,0)) AS '政治',
SUM(score) AS total
FROM tb_score
GROUP BY userid WITH ROLLUP;

035使用SUM(IF()) 生成列 + UNION 生成匯總行,并利用 IFNULL將匯總行標題顯示為 Total。

SELECT userid,
SUM(IF(subjectName='語文',score,0)) AS '語文',
SUM(IF(subjectName='數學',score,0)) AS '數學',
SUM(IF(subjectName='英語',score,0)) AS '英語',
SUM(IF(subjectName='政治',score,0)) AS '政治',
SUM(score) AS total
FROM tb_score
GROUP BY userid
UNION
SELECT 'total',SUM(IF(subjectName='語文',score,0)) AS '語文',
SUM(IF(subjectName='數學',score,0)) AS '數學',
SUM(IF(subjectName='英語',score,0)) AS '英語',
SUM(IF(subjectName='政治',score,0)) AS '政治',
SUM(score) FROM tb_score;

036group_concat()合并字段顯示如下內容。

SELECT userid,GROUP_CONCAT(`subjectName`,":",score)AS 成績 FROM tb_score GROUP BY userid;

目錄

001經典SQL筆試題題干 002查詢課程編號為“0004”且分數小于60的學生學號，結果按按分數降序排列。 003查詢姓“孟”老師的個數。 004查詢1990年出生的學生名單。 005查詢課程編號為“0002”的總成績。 006查詢選了課程的學生人數。 007查詢每門課程被選修的學生數。 008查詢男生、女生人數。 009查詢不及格的課程并按課程號從大到小排列。 010經典SQL筆試題題干 011查詢各科成績最高和最低的分， 以如下的形式顯示：課程號，最高分，最低分 012查詢平均成績大于60分學生的學號和平均成績。 013查詢至少選修兩門課程的學生學號。 014查詢同名同性學生名單并統計同名人數。 015查詢每門課程的平均成績，結果按平均成績升序排序，平均成績相同時，按課程號降序排列。 016統計每門課程的學生選修人數(超過2人的課程才統計)。 017查詢兩門以上不及格課程的同學的學號及其平均成績。 018經典SQL筆試題題干 019查詢所有課程成績小于60分學生的學號、姓名。 020查詢沒有學全所有課的學生的學號、姓名。 021查詢出只選修了兩門課程的全部學生的學號和姓名。 022按課程號分組取成績最大值所在行的數據。 023查詢各科成績前兩名的記錄。 024查詢所有學生的學號、姓名、選課數、總成績。 025查詢平均成績大于85的所有學生的學號、姓名和平均成績。 026查詢學生的選課情況：學號，姓名，課程號，課程名稱。 027查詢課程編號為0003且課程成績在80分以上的學生的學號和姓名。 028將每個userid對應的多個科目的成績查出來，通過UNION ALL將結果集加起來。 029使用分段[100-85],[85-70],[70-60],[<60]來統計各科成績，分別統計：各分數段人數，課程號和課程名稱。 030查詢出每門課程的及格人數和不及格人數。 031使用case…when…then 實現原本同一列下多行的不同內容作為多個字段，輸出對應內容。 032使用SUM(IF()) 實現原本同一列下多行的不同內容作為多個字段，輸出對應內容。 033使用SUM(IF()) 生成列 + WITH ROLLUP 生成匯總行 。 034使用SUM(IF()) 生成列，直接生成匯總結果，不再利用子查詢。 035使用SUM(IF()) 生成列 + UNION 生成匯總行,并利用 IFNULL將匯總行標題顯示為 Total。 036group_concat()合并字段顯示如下內容。
返回頂部
主站蜘蛛池模板： 91国语精品自产拍在线观看性色 | 国产在线一区二区三区在线 | 国产成 人 综合 亚洲绿色 | 在线观看永久免费 | 一个人看的www视频在线播放 | 182tv成人午夜在线观看 | 免费观看的毛片 | 一区二区三区鲁丝不卡麻豆 | 老黄网站在线观看免费 | 波多野结衣在线观看视频 | 一区二区三区四区亚洲 | 朝鲜free嫩白的18sex性 | 中文字幕成人免费高清在线视频 | 欧美午夜艳片欧美精品 | www.亚洲一区 | 国产一区二区三区精品视频 | 福利精品视频 | 成人精品视频网站 | 久久本道综合色狠狠五月 | 亚洲免费在线视频观看 | 拍真实国产伦偷精品 | 亚洲免费大全 | 天天做天天爱天天爽 | 九九精品免视频国产成人 | 欧美激情一区二区三区 | 人人做人人爽 | 亚洲国产第一区二区香蕉日日 | 国产成人高清精品免费软件 | 午夜aaaaaaaaa视频在线 | 亚洲视频免 | 国产一区欧美 | 美女视频永久黄网站免费观看韩国 | 国产精品二区高清在线 | 亚洲欧美精品一区天堂久久 | a视频在线观看 | 一级毛片免费在线 | 91免费精品国偷自产在线在线 | 99精品影视 | 成年人视频在线免费看 | 天天摸天天射 | 国产成人精品第一区二区 |