2025年大數據分析師職業技能測試卷：SQL數據庫查詢與優化試題集考試時間：______分鐘總分：______分姓名：______一、SQL基礎查詢要求：掌握基本的SQL查詢語句，包括SELECT、FROM、WHERE、GROUPBY、HAVING和ORDERBY等語句的使用。1.從以下表中選擇所有列，但不包括重復的行：```sqlSELECTDISTINCT*FROMstudents;```2.查詢所有學生的姓名和年齡，按照年齡降序排列：```sqlSELECTname,ageFROMstudentsORDERBYageDESC;```3.查詢所有性別為女的學生姓名：```sqlSELECTnameFROMstudentsWHEREgender='female';```4.查詢年齡大于20歲的學生姓名和年齡：```sqlSELECTname,ageFROMstudentsWHEREage>20;```5.查詢所有學生的姓名，排除性別為男的學生：```sqlSELECTnameFROMstudentsWHEREgender<>'male';```6.查詢所有學生的姓名，年齡在18歲到25歲之間：```sqlSELECTnameFROMstudentsWHEREageBETWEEN18AND25;```7.查詢所有學生的姓名，年齡不在18歲到25歲之間：```sqlSELECTnameFROMstudentsWHEREageNOTBETWEEN18AND25;```8.查詢所有學生的姓名，性別為女或者年齡大于20歲：```sqlSELECTnameFROMstudentsWHEREgender='female'ORage>20;```9.查詢所有學生的姓名，年齡大于20歲并且性別為女：```sqlSELECTnameFROMstudentsWHEREage>20ANDgender='female';```10.查詢所有學生的姓名，年齡大于20歲或者性別為男：```sqlSELECTnameFROMstudentsWHEREage>20ORgender='male';```二、多表查詢要求：掌握使用JOIN、INNERJOIN、LEFTJOIN、RIGHTJOIN和FULLJOIN進行多表查詢。1.查詢所有學生的姓名和對應的課程名稱：```sqlSELECT,courses.course_nameFROMstudentsINNERJOINcoursesONstudents.course_id=courses.id;```2.查詢所有學生的姓名和對應的課程名稱，即使學生沒有選課也要顯示：```sqlSELECT,courses.course_nameFROMstudentsLEFTJOINcoursesONstudents.course_id=courses.id;```3.查詢所有學生的姓名和對應的課程名稱，即使課程沒有學生也要顯示：```sqlSELECT,courses.course_nameFROMstudentsRIGHTJOINcoursesONstudents.course_id=courses.id;```4.查詢所有學生的姓名和對應的課程名稱，只顯示學生有選課的情況：```sqlSELECT,courses.course_nameFROMstudentsINNERJOINcoursesONstudents.course_id=courses.id;```5.查詢所有學生的姓名和對應的課程名稱，只顯示課程有學生的情況：```sqlSELECT,courses.course_nameFROMstudentsLEFTJOINcoursesONstudents.course_id=courses.id;```6.查詢所有學生的姓名和對應的課程名稱，只顯示課程沒有學生的情況：```sqlSELECT,courses.course_nameFROMstudentsRIGHTJOINcoursesONstudents.course_id=courses.id;```7.查詢所有學生的姓名和對應的課程名稱，只顯示學生有選課并且課程沒有學生的情況：```sqlSELECT,courses.course_nameFROMstudentsLEFTJOINcoursesONstudents.course_id=courses.idWHEREcourses.idISNULL;```8.查詢所有學生的姓名和對應的課程名稱，只顯示課程有學生并且學生沒有選課的情況：```sqlSELECT,courses.course_nameFROMstudentsRIGHTJOINcoursesONstudents.course_id=courses.idWHEREstudents.course_idISNULL;```9.查詢所有學生的姓名和對應的課程名稱，只顯示學生有選課并且課程有學生的情況：```sqlSELECT,courses.course_nameFROMstudentsINNERJOINcoursesONstudents.course_id=courses.idWHEREstudents.course_idISNOTNULLANDcourses.idISNOTNULL;```10.查詢所有學生的姓名和對應的課程名稱，只顯示課程有學生并且學生有選課的情況：```sqlSELECT,courses.course_nameFROMstudentsLEFTJOINcoursesONstudents.course_id=courses.idWHEREstudents.course_idISNOTNULLANDcourses.idISNOTNULL;```四、SQL子查詢要求：理解并掌握子查詢的使用，包括嵌套查詢和子查詢在WHERE和SELECT子句中的應用。1.查詢所有年齡大于平均年齡的學生姓名和年齡：```sqlSELECTname,ageFROMstudentsWHEREage>(SELECTAVG(age)FROMstudents);```2.查詢所有學生的姓名，這些學生的年齡等于任意一個已經選過課程的學生的年齡：```sqlSELECTnameFROMstudentsWHEREageIN(SELECTageFROMstudentsWHEREcourse_idISNOTNULL);```3.查詢所有學生的姓名，這些學生的年齡不等于任何一個已經選過課程的學生的年齡：```sqlSELECTnameFROMstudentsWHEREageNOTIN(SELECTageFROMstudentsWHEREcourse_idISNOTNULL);```4.查詢所有學生的姓名和他們的課程名稱，對于每個學生，如果他們的年齡大于某個特定年齡的平均值，則顯示該學生的課程名稱：```sqlSELECT,courses.course_nameFROMstudentsJOINcoursesONstudents.course_id=courses.idWHEREstudents.age>(SELECTAVG(age)FROMstudentsWHEREcourse_id=students.course_id);```5.查詢所有性別為男的學生姓名，這些學生的年齡小于所有性別為女的學生年齡的平均值：```sqlSELECTnameFROMstudentsWHEREgender='male'ANDage<(SELECTAVG(age)FROMstudentsWHEREgender='female');```六、SQL聚合函數與分組查詢要求：掌握SQL中的聚合函數（如COUNT、SUM、AVG、MAX、MIN）以及如何使用GROUPBY和HAVING子句。1.計算每個性別中學生的數量：```sqlSELECTgender,COUNT(*)asstudent_countFROMstudentsGROUPBYgender;```2.計算每個年級中學生的平均年齡：```sqlSELECTgrade,AVG(age)asaverage_ageFROMstudentsGROUPBYgrade;```3.計算每個課程的學生人數總和：```sqlSELECTcourses.id,courses.course_name,COUNT(students.id)astotal_studentsFROMcoursesLEFTJOINstudentsONcourses.id=students.course_idGROUPBYcourses.id;```4.查詢所有年齡大于20歲的學生中，年齡最大的學生的年齡：```sqlSELECTMAX(age)asmax_age_over_20FROMstudentsWHEREage>20;```5.查詢所有年級中，年級為"Year12"的學生平均年齡，但只顯示那些平均年齡大于全校平均年齡的年級：```sqlSELECTgrade,AVG(age)asaverage_ageFROMstudentsWHEREgrade='Year12'GROUPBYgradeHAVINGAVG(age)>(SELECTAVG(age)FROMstudents);```本次試卷答案如下：一、SQL基礎查詢1.答案：`SELECTDISTINCT*FROMstudents;`解析思路：使用`DISTINCT`關鍵字來確保查詢結果中不包含重復的行。2.答案：`SELECTname,ageFROMstudentsORDERBYageDESC;`解析思路：選擇學生的姓名和年齡，然后使用`ORDERBY`語句按年齡降序排列。3.答案：`SELECTnameFROMstudentsWHEREgender='female';`解析思路：通過`WHERE`子句篩選性別為'female'的學生。4.答案：`SELECTname,ageFROMstudentsWHEREage>20;`解析思路：通過`WHERE`子句篩選年齡大于20歲的學生。5.答案：`SELECTnameFROMstudentsWHEREgender<>'male';`解析思路：使用`<>`運算符來排除性別為'male'的學生。6.答案：`SELECTnameFROMstudentsWHEREageBETWEEN18AND25;`解析思路：使用`BETWEEN`運算符來篩選年齡在18到25歲之間的學生。7.答案：`SELECTnameFROMstudentsWHEREageNOTBETWEEN18AND25;`解析思路：使用`NOTBETWEEN`運算符來排除年齡在18到25歲之間的學生。8.答案：`SELECTnameFROMstudentsWHEREgender='female'ORage>20;`解析思路：使用`OR`運算符來同時篩選性別為'female'或年齡大于20歲的學生。9.答案：`SELECTnameFROMstudentsWHEREage>20ANDgender='female';`解析思路：使用`AND`運算符來篩選年齡大于20歲且性別為'female'的學生。10.答案：`SELECTnameFROMstudentsWHEREage>20ORgender='male';`解析思路：使用`OR`運算符來同時篩選年齡大于20歲或性別為'male'的學生。二、多表查詢1.答案：`SELECT,courses.course_nameFROMstudentsINNERJOINcoursesONstudents.course_id=courses.id;`解析思路：使用`INNERJOIN`來連接`students`和`courses`表，基于`course_id`字段。2.答案：`SELECT,courses.course_nameFROMstudentsLEFTJOINcoursesONstudents.course_id=courses.id;`解析思路：使用`LEFTJOIN`來連接`students`和`courses`表，即使`students`表中沒有對應`courses`表的記錄也會顯示。3.答案：`SELECT,courses.course_nameFROMstudentsRIGHTJOINcoursesONstudents.course_id=courses.id;`解析思路：使用`RIGHTJOIN`來連接`students`和`courses`表，即使`courses`表中沒有對應`students`表的記錄也會顯示。4.答案：`SELECT,courses.course_nameFROMstudentsINNERJOINcoursesONstudents.course_id=courses.id;`解析思路：使用`INNERJOIN`來連接`students`和`courses`表，只顯示兩個表中有匹配的記錄。5.答案：`SELECT,courses.course_nameFROMstudentsLEFTJOINcoursesONstudents.course_id=courses.id;`解析思路：使用`LEFTJOIN`來連接`students`和`courses`表，即使`students`表中沒有對應`courses`表的記錄也會顯示。6.答案：`SELECT,courses.course_nameFROMstudentsRIGHTJOINcoursesONstudents.course_id=courses.id;`解析思路：使用`RIGHTJOIN`來連接`students`和`courses`表，即使`courses`表中沒有對應`students`表的記錄也會顯示。7.答案：`SELECT,courses.course_nameFROMstudentsLEFTJOINcoursesONstudents.course_id=c