sql语句练习

一、现有以下两张表：

第一张表名为cust，其表结构如下：

第二张表名为mark，其表结构如下:

1) [5分]请写出计算 所有学生的英语平均成绩的sq|语句。

2) [5分]现有五 个学生,其学号假定分别为11,22,33,44,55;请用一条SQL语句实现列出这五个学生的数学成绩及其姓名、学生地址、电话号码;

3）[5分]查询所有学生的姓名、计算机成绩，按照计算机成绩从高到低排序;

1. [5分]查询所有总成绩大于240分的学生学号、姓名、总成绩,按照总成绩从高到低排序;(在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用)

select a.studentno,a.name,sum(b.english+b.math+b.computer) zcj from cust a join mark b on a.studentno=b.studentno group by a.studentno,a.name having zcj>240 where order by zcj desc;

答案：

1、错：select a.name,avg(b.english) from cust a jion mark b on a.studentno=b.studentno
对：select avg(b.english) from cust a jion mark b on a.studentno=b.studentno
2、select a.name,a.address,a.telno from cust a jion mark b on a.studentno=b.studentno where b.studentno in(11,22,33,44,55)
3、select a.name,b.computer from cust a jion mark b on a.studentno=b.studentno order by computer dasc
4、错：select a.studentno,a.name,b.english+b.math+b.computer from cust a jion mark b on on a.studentno=b.studentno where b.(english+math+computer)>240 order by b.(english+math+computer) dasc
对：SELECT
    a.Studentno,
    a. NAME,
    sum(b.math + b.english + b.computer) zcj
FROM
    cust a
JOIN mark b ON a.Studentno = b.studentno
GROUP BY
    a.Studentno,
    a. NAME
HAVING
    zcj > 240
ORDER BY
    zcj DESC;

二、

A.查询姓‘王’的学生的个数;

select count(*) from student where name like '王%';

B. 查询“数学”比“语文”成绩高的所有学生的学号;

-- 2.查询“数学”比“语文”成绩高的所有学生的学号
select chinese.sid from 
-- 语文成绩表
(select s.sid,c.score from sc s join course c on s.cid = c.id where c.cname='语文') as chinese 
join
-- 数学成绩表
(select s.sid,c.score from sc s join course c on s.cid = c.id where c.cname='数学') as math 
on chinese.sid = math.sid
where chinese.score < math.score


-- if(条件){代码块}
-- switch case return 

-- case when 条件 then 结果 else[结果N] end    每一行匹配一次
    -- 1.枚举  case when name='张三' then age end
    -- select (case when cname like '软[^45]' then count end) as count from class
    -- 2.搜索判断
        select 
            case 
                when count>40 then 'XXX'      -- root  根
                when count<40 then 'YYY'    -- admin1  A
                else '无匹配条件'             -- admin2    B
            end
        from class

    select * from (
        select s.sid,
            (case when c.cname='语文' then s.score end) chinese
            (case when c.cname='数学' then s.score end) math
        from sc s join course c
        on s.cid = c.id
        where math.score>chinese.score
    )

C.查询平均成绩大于90分的同学的学号和平均成绩。

select b.sid,avg(score) from course a join sc b on a.id=b.cid group by b.sid having avg(score)>90;