sqlserver sql语句练习(四)

Question2：Can you use a SQL statement to calculating it!
How can I print "10 to 20" for books that sell for between $10 and $20，"unknown" for books whose price is null, and "other" for all other prices?

Answer 2：
select bookid,bookname,price=case when price is null then 'unknown' 
       when  price between 10 and 20 then '10 to 20' else price end
from books

注意下面的写法：相当于取别名

SELECT dep ,count=count(*)from sales  GROUP BY dep HAVING count(*)>1、

 

Question3：Can you use a SQL statement to finding duplicate values!
How can I find authors with the same last name?
You can use the table authors in datatabase pubs. I want to get the result as below:
Output:
au_lname                                 number_dups 
---------------------------------------- ----------- 
Ringer                                   2

Answer 3
select au_lname,number_dups=count(1) from authors group by au_lname

 给下面这样的一个表记录：

------------------------------------

CUSTOMER_NAME      TRADE_NAME    NUM
A                     甲           2
B                      乙           4
C                      丙           1
A                      丁           2
B                      丙           5

给出所有购入商品为两种或两种以上的购物人记录。

分析：

1. 按人分组

2.统计不重复的列 count(distinct 列名)

CREATE table orderitem (

CUSTOMER_NAME VARCHAR(50),

TRADE_NAME VARCHAR(50),

NUM  INT

)

--drop table orderitem

INSERT INTO orderitem  VALUES('A','甲',2)

INSERT INTO orderitem  VALUES('B','乙',4)

INSERT INTO orderitem  VALUES('C','丙',1)

INSERT INTO orderitem  VALUES('A','丁',2)

INSERT INTO orderitem  VALUES('B','丙',5)

方法一（推荐，使用了 count(distinct 列名) ）

select * from T1
where customer_name in 
(select customer_name
from T1 as b
group by customer_name
having count(distinct trade_name)>=2)

红色部分(内层查询)查出了购买商品种类大于等于2种的顾客的名字

说明：

having count ，发生在分组之后，对每个分组包含的行，进行统计count(distinct trade_name)，根据这个统计值（每个分组一个），确定是否输出此分组。

Student(sid,Sname,Sage,Ssex) 学生表

Teacher(tid,Tname) 教师表

中间表：多对多关系

Courses(cid, tid,Cname) 选课表

SC(sid,cid,score) 成绩表

 

问题：

1、         查询“001”课程比“002”课程成绩高的所有学生的学号；

 

排一下序：

 

SELECT *

from (SELECT * FROM SC c WHERE c.cid='001' )a

INNER JOIN

(SELECT * FROM SC sc WHERE SC.cid='002')b

on a.sid = b.sid

 

SELECT *

from (SELECT * FROM SC c WHERE c.cid='001' )a

INNER JOIN

(SELECT * FROM SC sc WHERE SC.cid='002')b

on a.sid = b.sid and a.score>b.score

 

SELECT * from Student s WHERE s.sid in(

SELECT a.sid

from (SELECT * FROM SC c WHERE c.cid='001' )a

INNER JOIN

(SELECT * FROM SC sc WHERE SC.cid='002')b

on a.sid = b.sid and a.score>b.score

)

 

SELECT

 a.sid,a.cid,a.score as '001score',b.cid,b.score as '002score'

from

(SELECT * FROM SC c WHERE c.cid='001')a,

(SELECT * FROM SC c1 WHERE c1.cid='002')b

WHERE a.sid=b.sid and a.score>b.score

 

2.  查询平均成绩大于60分的同学的学号和平均成绩；

分析：学号和成绩都出现在SC表中，因此是单表查询，

求各同学的平均成绩，要按照学号分组

 

SELECT

 c.sid,

AVG(CASE  ISNUMERIC(c.score) when 1 THEN cast(c.score as INT) else 0 end)

FROM SC c

GROUP BY c.sid

说明这里的score是varchar类型的，做SUM或AVG统计时需要通过ISNUMERIC函数转换一下

如果score是int类型的话，则可以直接使用下面的语句   

select sid,avg(score)

    from sc

    group by sid having avg(score) >60; 

3. 查询所有同学的学号、姓名、选课数、总成绩；

 

说明：当出现分组group  by 时，

select中的东西，要么出现在group by中，要么出现在组函数中

组函数：即SUM,  AVG ,COUNT等函数 

这里的sid是不会重复的，因此这里的select  s.sname 也是不会重复的

 

SELECT s.sid,s.sname,

COUNT(DISTINCT(cid)) as '选课数',

AVG(case ISNUMERIC(c.score) when 1 then CAST(c.score as FLOAT) ELSE 0 END) as '平均分'

FROM SC c INNER JOIN Student s on c.sid = s.sid

GROUP BY s.sid,s.sname

4. 查询姓“李”的老师的个数；

 

SELECT COUNT(DISTINCT(tname)) FROM Teacher WHERE tname LIKE'李%'