数据库常见考题查询SQL
1、列出各个部门中工资高于本部门的平均工资的员工数和部门号,并按部门号排序
执行查询:
select count(*) count,a.deptid from employee a where a.salary > (select avg(salary) from employee b where a.deptid = b.deptid) GROUP BY a.deptid
select count(*) count,a.deptid from employee a, (select deptid,avg(salary) as numberFROM employee GROUP BY deptid)b
where a.salary > b.number and a.deptid = b.deptid
GROUP BY a.deptid
2、用一条SQL语句查询出每门课都大于80分的学生姓名
执行查询:
select distinct name from score where name not in (select distinct name from score where score<=80)
请写出下列查询的SQL语句:
3、查找出所有位于亚洲并且奖牌数不小于10的国家,返回结果应该包含:国家姓名,奖牌数量
执行查询:
select COUNTRY_NAME,SUM(MEDAL_COUNT) as NUMBER from demo1 WHERE CONTINENT="Asia" GROUP BY COUNTRY_NAME HAVING SUM(MEDAL_COUNT)>=10
4、查找出上表所有获得超过一种奖牌的国家,返回的结果应当包含:国家姓名,奖牌类型的数量
执行查询:
select COUNTRY_NAME,COUNT(MEDAL_TYPE) as NUMBER from demo1 GROUP BY COUNTRY_NAME HAVING COUNT(MEDAL_TYPE)>1
5、查找出所有满足以下条件的记录,条件为:某个国家某种奖牌的数量比整个世界同种奖牌数量的平均值要高,返回的结果应当包含:国家姓名,奖牌类型,奖牌数量
执行查询:
select a.COUNTRY_NAME,a.MEDAL_TYPE,a.MEDAL_COUNT from demo1 a, (select MEDAL_TYPE,AVG(MEDAL_COUNT) as average from demo1 GROUP BY MEDAL_TYPE)b where a.MEDAL_COUNT > b.average and a.MEDAL_TYPE = b.MEDAL_TYPE
追梦的脚步,永不停息