---
title: "sql 题目练习"
author: "mywebcode"
date: "2013-11-09T23:29:00Z"
tags: []
slug: "sql"
draft: false
lastmod: "2014-04-30T09:16:57Z"
id: 2058370
url: "https://mywebcode.iteye.com/blog/2058370"
---

一：比较简单，查询出销售表中，销售额大于本地区平均水平的记录，用一条sql语句就搞定了。

![](http://img.blog.csdn.net/20131109233021437?watermark%2F2%2Ftext%2FaHR0cDovL2Jsb2cuY3Nkbi5uZXQvYmFpY3Az%2Ffont%2F5a6L5L2T%2Ffontsize%2F400%2Ffill%2FI0JBQkFCMA=%3D%2Fdissolve%2F70%2Fgravity%2FSouthEast)

SELECT * FROM sales s,(SELECT region,AVG(total) avge from sales GROUP BY region) AS avge WHERE s.region=avge.region AND s.total&gt;avge.avge ;

或者Sql语句：

select * from sales as s inner join (select avg(total) as avge,region from sales group by region) avgtable on s.region = avgtable.region where total &gt; avgtable.avge

二：为管理业务培训信息，建立3个表：

S(S#,SN,SD,SA)S#,SN,SD,SA分别代表学号，学员姓名，所属单位，学员年龄

C(C#,CN)C#,CN分别代表课程编号，课程名称

SC(S#,C#,G) S#,C#,G分别代表学号，所选的课程编号，学习成绩

（1）使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名?

答案：select s# ,sn from s where S# in(select S# from c,sc where c.c#=sc.c# and cn=’税收基础’)

(2) 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位?

答：select sn,sd from s,sc where s.s#=sc.s# and sc.c#=’c2’

(3) 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位?

答：select sn,sd from s where s# not in(select s# from sc where c#=’c5’)

(4)查询选修了课程的学员人数

答：select 学员人数=count(distinct s#) from sc

(5) 查询选修课程超过5门的学员学号和所属单位?

答：select sn,sd from s where s# in(select s# from sc group by s# having count(distinct c#)&gt;5)

三：.有一张person表，有字段id,name,和score.  
要求 写一sql语句查询成绩排名前三的同学，包含并列成绩，并序号显示

思路：先把前3名的分数列出来

select distinct score from person order by desc score limit 0,3; //列出分数前三个；

思路二：接下来筛选分数 只要在前三个分数就行。。

select * from person where score in(select distinct score from person order by desc score limit 0,3);

这个语句会报错，具体什么原因求解。。

思路三 ：需要对思路二语句进行包装即可。。即把select distinct score from person order by desc score limit 0,3 作为一个表对象

select * from person where score in（select t.score from(select distinct score from person order by score limit 0,3)as t）; 这样语句就不报错了。

四：一道SQL语句面试题，关于groupby  
表内容：  
2005-05-09胜  
2005-05-09胜  
2005-05-09负  
2005-05-09负  
2005-05-10胜  
2005-05-10负  
2005-05-10负

如果要生成下列结果,该如何写sql语句?  
胜负  
2005-05-0922  
2005-05-1012

select date,sum(case when shengfu='胜' then 1 else 0 end)'胜'，sum(case when shengfu='负' then 1 else 0 end)'负' from table group by date;