Select语句复习大全

1.Sleect 嵌套

(a). 在许多个国家中找到比Russia人口多的国家

Select name from world
where population >
(Select population from world where name = 'Russia');

(b). 找出所有国家在一个包含('Argentina' 'australia')的州

注意：因为子查询可能会返回多行，要用IN （ORDER BY 升序排列 name）

Select name, continent from world 
where continent  IN (Select continent where name in ('Argentina','Australia'))
ORDER BY name;

(c).找出population 超过UnitedKingdom少于Germany的-----（其实就是(a)题嵌套两次用and连接）

(d).找到国家gdp大于欧洲所有国家gdp的。

假如子查询返回多行，但是我一定要比较关系---使用ALL/ANY

Select name from world 
where gdp > ALL(Select gdp from world where continent = 'Europe');

(f).找出所有州最大人口的国家----(涉及到子查询)有点像遍历

Select name, continent from world w1 where
population > All(Select population from world w2 where
w1.continent = w2.continent and population >0);

(g).找所有国家小于25000000人口的州

Select name, continent from world where
continent Not in (select continent from world where
population > 25000000)

Select name, continent from world w1 where
25000000 > ALL(Select population from world w2 where w1.continent = w2.continent)

 (h). 找出国家在一个州内比所有邻居多三倍人口(在子查询中看在同一个州内跟自己不一样的时候)

Select name from world w1 where population >
ALL(Select 3*c2.population from world w2 where w1.continent = w2.continent
AND w1.name != w2.name);

(i). 选出每一个地区人口最小的国家 

Select name from bbc x where population <= All
(Select population from bbc y where x.region = y.region);

考试题 --- 找表中第二大的值---------MAX(要求的)  where 要求的< (SELECT 要求的  MAX(要求的))

2.练习聚合函数 --- SUM AVG COUNT ----- USING HAVING

(a). 找出每个州有多少个国家

SELECT continent, COUNT(name) 
FROM world 
GROUP BY continent;

(b). 找出所有国家人口总和大于100million的州-----使用Group by合并---使用SUM去找

Select continent from world w1
GROUP BY continent
having SUM(population)>100000000;

考试题--- 找表中出现次数>1的

SELECT name,COUNT(name)from NameList
GROUP BY name
HAVING COUNT(name) > 1

 查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

select AVG(Degree ) from Score where Cno like '3%' group by Cno having COUNT(Cno)>4

查询score中选学多门课程的同学中分数为非最高分成绩的记录。

Select * from Score a where 
Degree < (Select Max(Degree) from Score b where a.Cno=b.Cno)
and
Sno in (Select Sno from Score Group by Sno having COUNT(Sno)>1)

查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”课程的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

Select Degree from Score where
(Select Degree from Score s1 where s1.Sno = s2.Sno) > =
(Select Degree from Score s2 where Cno = '3-245' and s1.Cno = s2.Cno) 
ORDER BY Degree DESC; 

查询成绩比这一门成绩低的学生

Select Sno from Score a
where a.Degree<
(select AVG(Degree) from Score b where a.Cno=b.Cno)

 

3.练习Join ---- 经验是先找他想要什么-----看什么东西可以确定他要的， 找到两个表的连接键区进行INNER JOIN ON条件 ， 最终通过WHERE确定最小范围

(a).考试题 ---- 要得到Brett教过的学生

思路是

1.想顺序关系  Brett教二三年级的课 --- 二三年级的学生（我们要找的）

2. 将二三年级的学生所在的表加到 Brett的表 ON 年级相同的情况下

两表之间的查询

A表有B表没有的ID

Select c.cName from Company c where 
c.cID  NOT IN (SELECT cID from Product p where c.cID=p.pID)

使用Union

查询所有“女”教师和“女”同学的name、sex和birthday.

Select distinct Tno,Tname,Tbirthday from Teacher
where Tsex = 'female'
UNION
Select distinct Sno,Sname,Sbirthday from Student
where Ssex = 'female';