mysql练习2

1.

原表:

courseid coursename score
-------------------------------------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
-------------------------------------
为了便于阅读, 查询此表后的结果显式如下( 及格分数为60):
courseid coursename score mark
---------------------------------------------------
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
---------------------------------------------------

写出此查询语句

selectcourseid，coursename，grade, case sign(grade-60)
when -1 then 'fail'
else 'pass' end as mark
            from grade

2.

有表A，结构如下： 
A: p_ID p_Num s_id 
1 10 01 
1 12 02 
2 8 01 
3 11 01 
3 8 03 
其中：p_ID为产品ID，p_Num为产品库存量，s_id为仓库ID。请用SQL语句实现将上表中的数据合并，合并后的数据为： 
p_ID s1_id s2_id s3_id 
1 10 12 0 
2 8 0 0 
3 11 0 8 
其中：s1_id为仓库1的库存量，s2_id为仓库2的库存量，s3_id为仓库3的库存量。如果该产品在某仓库中无库存量，那么就是0代替。

结果：

select p_id ,
sum(case when s_id=1 then p_num else 0 end) as s1_id
,sum(case when s_id=2 then p_num else 0 end) as s2_id
,sum(case when s_id=3 then p_num else 0 end) as s3_id
from myPro group by p_id

3.

查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不是连续增长的列，完整的查询语句如下：

注：limit优先级比max(id)低

select *
from A
where id>(select max(id) from (select id from A order by id limit 30) A1)
order by id
limit 10