（1）KC（课程表）（课程号 c_ID,课程名 c_Name,学分 c_Credit） （2）XS（学生表）（学号 s_ID,姓名 s_Name,所在系s_department,宿舍电话s_telephone） （3）XS_KC(学生选课表)（学号 s_ID,课程号 c_ID,成绩 score） 在 chaxun 数据库中进行如下查询，将代码保存在． sql 文件中。 （1） 在 KC 表中查询学分低于3分的课程信息，并按课程号升序排列。 （2） 在 XS _ KC 表中按学号分组汇总学生的平均分，并按平均分的降序排列。 （3） 在 XS _ KC 表中查询至少选修了2门课程的学生学号和姓名。 (4）查询成绩不及格的学生的基本信息。 (5）分别用子查询和连接查询，查询101号课程不及格的学生信息。 (6）在 XS 表中查询住在同一宿舍的学生信息，即其所住宿舍电话相同。 (7）查询 XS 表中所有的系名。 (8）查询有多少名同学选修了课程。 9）查询与杨颖同在一个系的同学姓名。 (10）查询选修课程的学生姓名、课程名和成绩。 (11）修改经管系电话号码为3615555。 (12）统计每门课程的选课人数和最高分。 (13）统计每个学生的选课门数和考试总成绩，并按选课门数的降序排列。 (14）查询有多少名同学没有选课。

以下是您提出的查询问题的 SQL 语句：

1. 在 KC 表中查询学分低于3分的课程信息，并按课程号升序排列。

SELECT c_ID, c_Name, c_Credit
FROM KC
WHERE c_Credit < 3
ORDER BY c_ID ASC;

2. 在 XS_KC 表中按学号分组汇总学生的平均分，并按平均分的降序排列。

SELECT s_ID, AVG(score) AS avg_score
FROM XS_KC
GROUP BY s_ID
ORDER BY avg_score DESC;

3. 在 XS_KC 表中查询至少选修了2门课程的学生学号和姓名。

SELECT XS.s_ID, XS.s_Name
FROM XS
INNER JOIN (
    SELECT s_ID
    FROM XS_KC
    GROUP BY s_ID
    HAVING COUNT(DISTINCT c_ID) >= 2
) AS T
ON XS.s_ID = T.s_ID;

4. 查询成绩不及格的学生的基本信息。

SELECT XS.s_ID, XS.s_Name, XS.s_department, XS.s_telephone
FROM XS
INNER JOIN XS_KC
ON XS.s_ID = XS_KC.s_ID
WHERE XS_KC.score < 60;

5. 分别用子查询和连接查询，查询101号课程不及格的学生信息。

使用子查询：

SELECT s_ID, s_Name
FROM XS
WHERE s_ID IN (
    SELECT s_ID
    FROM XS_KC
    WHERE c_ID = '101' AND score < 60
);

使用连接查询：

SELECT XS.s_ID, XS.s_Name
FROM XS
INNER JOIN XS_KC
ON XS.s_ID = XS_KC.s_ID AND XS_KC.c_ID = '101' AND XS_KC.score < 60;

6. 在 XS 表中查询住在同一宿舍的学生信息，即其所住宿舍电话相同。

SELECT T1.s_ID, T1.s_Name, T1.s_department, T1.s_telephone, T2.s_ID, T2.s_Name, T2.s_department, T2.s_telephone
FROM XS AS T1
INNER JOIN XS AS T2
ON T1.s_telephone = T2.s_telephone AND T1.s_ID < T2.s_ID;

7. 查询 XS 表中所有的系名。

SELECT DISTINCT s_department
FROM XS;

8. 查询有多少名同学选修了课程。

SELECT COUNT(DISTINCT s_ID) AS num_students
FROM XS_KC;

9. 查询与杨颖同在一个系的同学姓名。

SELECT DISTINCT s_Name
FROM XS
WHERE s_department = (
    SELECT s_department
    FROM XS
    WHERE s_Name = '杨颖'
);

10. 查询选修课程的学生姓名、课程名和成绩。

SELECT XS.s_Name, KC.c_Name, XS_KC.score
FROM XS_KC
INNER JOIN XS
ON XS_KC.s_ID = XS.s_ID
INNER JOIN KC
ON XS_KC.c_ID = KC.c_ID;

11. 修改经管系电话号码为3615555。

UPDATE XS
SET s_telephone = '3615555'
WHERE s_department = '经管系';

12. 统计每门课程的选课人数和最高分。

SELECT c_ID, COUNT(DISTINCT s_ID) AS num_students, MAX(score) AS max_score
FROM XS_KC
GROUP BY c_ID;

13. 统计每个学生的选课门数和考试总成绩，并按选课门数的降序排列。

SELECT s_ID, s_Name, COUNT(c_ID) AS num_courses, SUM(score) AS total_score
FROM XS_KC
INNER JOIN XS
ON XS_KC.s_ID = XS.s_ID
GROUP BY s_ID
ORDER BY num_courses DESC;

14. 查询有多少名同学没有选课。

SELECT COUNT(*) AS num_students
FROM XS
WHERE s_ID NOT IN (
    SELECT DISTINCT s_ID
    FROM XS_KC
);