MYSQL数据库第二版107页答案

– 1

SELECT     姓名,    专业名,        总学分
    FROM     xs;

1
2
3
4
– 2

SELECT     学号  AS  number,    姓名  AS  NAME,        总学分  AS  mark
    FROM     xs
    
    WHERE    专业名="计算机";

1

2
3
4
5
6
– 3

SELECT     学号  ,    姓名  ,        
    CASE    
        WHEN  总学分  IS NULL             THEN    "尚未选课"
        WHEN  总学分  < 50            THEN    "不及格"
        WHEN  总学分>=50 AND 总学分<=52     THEN    "及格"
        WHEN  总学分  > 52            THEN    "优秀"
    END     
        AS     "等级"
    FROM     xs
    
    WHERE    专业名="计算机";

1
2
3
4
5
6
7
8
9
10
11
12
13
– 4

SELECT     *
    FROM     xs_kc
    
    WHERE     学号=081101;

1
2
3
4
5
6
– 5

SELECT     DISTINCT     专业名,        总学分
    FROM     xs;

1

2
3
4
– 6

SELECT     COUNT(*)  AS  学生总人数
    FROM     xs;

1
2
3
4
– 7

    -- 方一
SELECT     COUNT(备注)  AS 备注不为空
    FROM     xs;
    
    -- 方二
SELECT     COUNT(备注)  AS 备注不为空
    FROM     xs
    WHERE     备注  IS NOT NULL;

1
2
3
4
5
6
7
8
9
10
– 8

SELECT     COUNT(*)  AS  "50分以上的人数"
    FROM     xs
    WHERE     总学分>50;

1
2
3
4
5
– 9

SELECT     MAX(成绩)  AS  最高成绩,    MIN(成绩)  AS  最低成绩
    FROM     xs_kc
    WHERE     课程号="101";
1
2
3
4
– 10

SELECT     SUM(成绩)  AS  总成绩
    FROM     xs_kc
    WHERE     学号=081101;
1
2
3
4

---11
SELECT     AVG(成绩)  AS  平均成绩
    FROM     xs_kc
    WHERE     课程号="101";

1
2
3
4
5
– 12

SELECT     *
    FROM     xs
    WHERE     学号=081101;

1
2
3
4
5
– 13

SELECT     *
    FROM     xs
    WHERE     总学分>50;

1
2
3
4
5
– 14

SELECT     *
    FROM     xs
    WHERE     备注  IS NULL;

1
2
3
4
5
– 15

SELECT     *
    FROM     xs
    WHERE     专业名="计算机"  AND 性别=0;

1
2
3
4
5
– 16

SELECT     *
    FROM     xs_kc
    WHERE  课程号    IN(102,  206)    AND 成绩>80
    ORDER BY    课程号 ASC;
1
2
3
4
5
– 17

SELECT     学号,    姓名,    性别
    FROM     xs
    WHERE     姓名    LIKE    "王%";
1
2
3
4
– 18

SELECT     学号,    姓名,    专业名
    FROM     xs
    WHERE     学号    LIKE    "%0_";
1
2
3
4
– 19

SELECT     学号,    姓名
    FROM     xs
    WHERE     姓名    LIKE    "%#_%"     ESCAPE    "#";
1
2
3
4
– 20

SELECT     *
    FROM     xs
    WHERE     (出生时间<"1989-01-01" OR 出生时间>"1989-12-30");
1
2
3
4

21-30小题
底部
– 21

SELECT     *
    FROM     xs
    WHERE     专业名 IN("计算机",  "通信工程");
1
2
3
4
– 22

SELECT     *
    FROM     xs
    WHERE     总学分  IS NULL;
1
2
3
4
– 23

SELECT     xs.`姓名`,     kc.`课程名`,     kc.`课程号`
    FROM     xs_kc     JOIN     xs /*关联学生与课程号*/
        ON (xs_kc.`学号`     =     xs.`学号`)
            JOIN     kc/*关联课程名*/
        ON (xs_kc.`课程号`     =     kc.`课程号`)
    /*order by    xs.`姓名` */;
1
2
3
4
5
6
7
– 24

SELECT     姓名,    成绩
    FROM     xs_kc     JOIN     xs /*关联学生与课程号*/
        ON (xs_kc.`学号`     =     xs.`学号`)
            JOIN     kc/*关联课程名*/
        ON (xs_kc.`课程号`     =     kc.`课程号`)
        
    WHERE     xs_kc.课程号=206  AND  成绩>80;
1
2
3
4
5
6
7
8
– 25

SELECT     xs.`学号`,     xs.`姓名`,    kc.`课程名`,    xs_kc.`成绩`
    FROM     xs_kc     JOIN     xs /*关联学生与课程号*/
        ON (xs_kc.`学号`     =     xs.`学号`)
            JOIN     kc/*关联课程名*/
        ON (xs_kc.`课程号`     =     kc.`课程号`)    
        WHERE     (kc.`课程名` = "计算机基础"     AND    xs_kc.`成绩`>80);
1
2
3
4
5
6
7
– 26

SELECT DISTINCT a.`学号`,     a.`课程号`,        a.`成绩`
    FROM     xs_kc AS a     JOIN     xs_kc AS b
        ON (a.`学号` <>b.`学号`        AND    a.`课程号` != b.`课程号` 
                AND 
            a.`成绩` = b.`成绩`)
    ORDER BY    a.`成绩` ;
1
2
3
4
5
6
7
– 27

SELECT     kc.`课程名`
    FROM     kc     JOIN     xs_kc
        ON (kc.`课程号` = xs_kc.`课程号`)
    GROUP BY kc.课程名;

1
2
3
4
5
6
– 28

/*查询未选修任何课程的学号和姓名*/
SELECT xs.学号,xs.姓名
FROM xs
WHERE xs.`学号` NOT IN (SELECT DISTINCT xs_kc.`学号` FROM xs_kc);

/*P107-28--ok*/
SELECT xs_kc.`课程号`,    xs.*
    FROM     xs     LEFT OUTER JOIN     xs_kc
        ON (xs.`学号` = xs_kc.`学号`);
1
2
3
4
5
6
7
8
9
10
– 29

SELECT     DISTINCT     kc.`课程名`,    kc.*
    FROM     xs_kc     JOIN     xs /*关联学生与课程号*/
        ON (xs_kc.`学号`     =     xs.`学号`)
            JOIN     kc/*关联课程名*/
        ON (xs_kc.`课程号`     =     kc.`课程号`)
    ;
1
2
3
4
5
6
7
– 30

SELECT xs.姓名,        xs.学号
    FROM     xs_kc     JOIN     xs /*关联学生与课程号*/
        ON (xs_kc.`学号`     =     xs.`学号`)
            JOIN     kc/*关联课程名*/
        ON (xs_kc.`课程号`     =     kc.`课程号`)
    
    WHERE     kc.课程号="206";
1
2
3
4
5
6
7
8

31-40小题
底部
– 31

-- 31    --- 方一
SELECT     姓名,    学号,    专业名
    FROM     xs
    WHERE     学号 NOT IN(
                SELECT     学号 
                    FROM     xs_kc     JOIN    kc
                        ON (xs_kc.`课程号`=kc.`课程号`)
                    
                    WHERE     课程名 ='离散数学'
                )
    ;
    
    
    -- 方二
SELECT 姓名,学号,专业名
FROM xs
WHERE 学号 NOT IN
       (SELECT 学号 FROM xs_kc WHERE 课程号 IN (SELECT 课程号 FROM kc WHERE 课程名 ='离散数学'));
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
– 32

SELECT     xs_kc.`学号`,     kc.`课程名`
    FROM     xs_kc    LEFT OUTER JOIN     kc
        ON (xs_kc.`课程号`     =     kc.`课程号`)
        
        WHERE (kc.`课程名`     =     "离散数学");
1
2
3
4
5
6
– 33

SELECT     xs.`学号`,    xs.`姓名`,    xs.`专业名`,    xs.`出生时间`
    FROM     xs
    
    WHERE     xs.`出生时间` > ALL(SELECT 出生时间     FROM xs   WHERE 专业名="计算机")
    ;

1
2
3
4
5
6
7
– 34

SELECT     学号,  成绩
    FROM     xs_kc
    
    WHERE     课程号 ='206' 
        AND 
        成绩 >=        ANY (SELECT MIN(成绩)     FROM xs_kc   WHERE 课程号 ='101')
    ;
1
2
3
4
5
6
7
8
– 35

SELECT     xs.`学号`,    姓名
    FROM     xs     JOIN     xs_kc
        ON (xs.`学号` = xs_kc.`学号`)
        WHERE    课程号="206";
1
2
3
4
5
– 36

SELECT     专业名
    FROM     xs
        GROUP BY    专业名;
1
2
3
4
– 37

SELECT     专业名,        COUNT(*)  AS  "人数"
    FROM     xs
        GROUP BY    专业名;
1
2
3
4
– 38

/*P107-38 --不能加""*/
SELECT    课程号,     AVG(成绩) AS "平均成绩" ,     COUNT(*) AS "总人数"
    FROM     xs_kc
        GROUP BY     课程号;
1
2
3
4
5
– 39

SELECT     专业名,     

        CASE     /*性别*/
            WHEN  性别=0 THEN "女生"
            WHEN  性别=1 THEN "男生"
            ELSE  "总人数"
        END AS 性别 ,     
        
        COUNT(*) AS "总人数"
    
    FROM     xs
        GROUP BY 专业名, 性别
            WITH ROLLUP;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
– 40

SELECT     学号, AVG(成绩)  AS  "平均成绩"
    FROM     xs_kc
    
    GROUP BY 学号
        HAVING     AVG(成绩)>85;
1
2
3
4
5
6

41-46小题
底部
– 41

SELECT     学号, 成绩
    FROM     xs_kc
            GROUP BY 学号
                HAVING COUNT(*)>2 AND AVG(成绩)>80;

1
2
3
4
5
6
– 42

SELECT     *
    FROM     xs
    
    WHERE     专业名="通信工程"
    
    ORDER BY    出生时间   ASC;
1
2
3
4
5
6
7
– 43

SELECT xs.`学号`,    xs_kc.`课程号`,        成绩 
    FROM xs_kc     JOIN     xs /*关联学生与课程号*/
        ON (xs_kc.`学号`     =     xs.`学号`)
            JOIN     kc/*关联课程名*/
        ON (xs_kc.`课程号`     =     kc.`课程号`)
    
    WHERE 专业名="计算机"  AND  kc.`课程名`="计算机基础"
    ORDER BY 成绩 DESC;

1
2
3
4
5
6
7
8
9
– 44

/*P107-44--子查询可以使用父查询的字段*/
SELECT     xs.学号,     姓名,     专业名
    FROM     xs
        WHERE 专业名="计算机" 
        ORDER BY (
            SELECT AVG(成绩) FROM xs_kc GROUP BY xs_kc.`学号` HAVING (xs.`学号`=xs_kc.`学号`)
        ) DESC;

1
2
3
4
5
6
7
8
– 45

SELECT     *
    FROM     xs
        ORDER BY 学号    LIMIT 5;

1
2
3
4
– 46

 

SELECT     *
    FROM     xs
        ORDER BY 学号    LIMIT 3,  5;