1） 有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条SQL语句查询出这三条记录并按以下条件显示出来： 大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。

select

(case when 语文 >= 80 then '优秀' when 语文 >= 60 and  语文 < 80 then '及格' else '不及格'  end) as 语文,

(case when 数学 >= 80 then '优秀' when 数学 >= 60 and  数学 < 80 then '及格' else '不及格'  end) as 数学,

(case when 英语 >= 80 then '优秀' when 英语 >= 60 and  英语 < 80 then '及格' else '不及格'  end) as 英语

from [table]

2）一张table-t表,里面有三个字段：英语,其中有三条记录分别表示语文70分,数学80分,英语58,请用一条sql语句查询出这三条记录并按以下条件显示出来：大于或等于80表优秀,大于等于60表及格,小于60表不及格显示格式：及格 优秀 不及格

select case when 语文>=80 then '优秀' when 语文>=60 then '及格' else '不及格' end 语文 ,

case when 数学>=80 then '优秀' when 数学>=60 then '及格' else '不及格' end 数学 ,

case when 英语>=80 then '优秀' when 英语>=60 then '及格' else '不及格' end 英语 from table_t

3）表结构和数据

date                result

2011-02-01          胜

2011-02-01          负

2011-02-01          胜

2011-02-02          胜

2011-02-02          负

查询出来的结果：

date         胜     负

2011-02-01    2      1

2011-02-02    1      1

请写出SQL语句：

SELECT date,

( SELECT count( * ) FROM table1 WHERE date = t.date AND result = '胜' ) AS '胜',

( SELECT count( * ) FROM table1 WHERE date = t.date AND result = '负' ) AS '负'

FROM table1 AS t GROUP BY date;

4）已知商品的关系模型：G(GNO,GNAME,GSALES,GPRICE,GTIME),GNO为商品编码，GNAME为商品名称，GSALES为商品销售量，GPRICE为商品价格，GTIME为商品上架时间。按要求写SQL语句，假设数据库是mysql。创建这张商品表的sql。取商品销售额排前5名的商品名称和上架时间。

创建表

CREATE TABLE `G`  (

  `GNO` varchar(10) NOT NULL,

  `GNAME` varchar(20) NULL,

  `GSALES` decimal(10, 2) NULL,

  `GPRICE` decimal(10, 2) NULL,

  `GTIME` datetime(0) NULL,

  PRIMARY KEY (`GNO`)

);

查询销售额前5的商品名称和上架时间

select GNAME,GTIME from g order by GSALES desc limit 0,5

5）用SQL语句查询每门课都大于80的学生姓名

name   kecheng    fenshu

张三     语文     81

张三     数学     75

李四     语文     76

李四     数学     90

王五     语文     81

王五     数学     100

王五     英语     90

方法一：

　　思路：如果能获得一张表，由学生姓名，语文成绩，数学成绩，英语成绩的表，剩下的就是在WHERE条件中筛选及可以获得想要的结果。

　　具体办法：通过自连接的办法，以“姓名”为连接条件，自连接三次，便可以获得包含又姓名和三门课程成绩的数据行。虽然可以得到想要的数据列。但会有很多冗余重复列！

　　点评：此方法是根据题目，依题解题，中规中矩！ 不过多张表连接非常耗费时间。而且SQL语句也比较复杂，需要注意事项很多。

SELECT  D.name  FROM (

SELECT  S.name,S.score AS ITEM1,S1.score AS ITEM2,S2.score AS ITEM3

FROM Student S

inner join Student S1 on S.name = S1.name and S.course <> S1.course

inner join Student S2 on S.name = S2.name and S.course <> S2.course

WHERE S.score>=80 and S1.score>=80 and S2.score>=80

) D

GROUP BY D.name
方法二：

　　思路：采用逆向思维想想。。。。。。求三门成绩都大于80的人，也可以是使先查出有成绩小于80 的人，再除去这些人不就是三门成绩都大于80的人了么？  以前学过的数学逻辑逆向思维还真是有用的阿！！

　　具体办法：先扫描表，查出有成绩小于80的人的姓名，然后再次扫描表，用not in 或not exists 方法。

　　点评：此方法采用逆向思维，能快速写出高效且简单的 SQL语句。

//not in

SELECT DISTINCT A.name FROM Student A

WHERE A.name not in(

SELECT Distinct S.name FROM Student S WHERE S.score <80)

 

//not exists

SELECT DISTINCT A.name From Student A  

where not exists (SELECT 1 From Student S Where  S.score <80 AND S.name =A.name)
方法三：

SELECT S.name

FROM Student S

GROUP BY S.name

Having MIN(S.score)>=80

6）删除除了自动编号不同,其他都相同的学生冗余信息

有如下学生表

自动编号 学号  姓名 课程编号 课程名称 分数

1      2005001 张三 0001      数学   69

2      2005002 李四 0001      数学   89

3      2005001 张三 0001      数学   69

要求：删除除了自动编号不同,其他都相同的学生冗余信息

DELETE from score where  
(name,kecheng,fenshu) 
in
(
select 
name,kecheng,fenshu from score GROUP by name,kecheng,fenshu 
having 
count(name) > 1) ;

7）数据库表如下，写出sql语句求出每个人的平均成绩，格式为【姓名，平均成绩】

tbl_score

id  学生ID     课程名称    成绩

1      1       数学      99

2      2       数学     100

3      1       语文      80

4      2       语文      96

tbl_student

id  姓名   年龄

1   张三   12

2   李四   13

select 
    s.姓名,avg(t.`成绩`) 平均成绩
from tbl_score t 
    left join tbl_student s on t.`学生ID`=s.id 
group by s.id