day13 SQL基础-连接

1280. 学生们参加各科测试的次数

要求写一段 SQL 语句，查询出每个学生参加每一门科目测试的次数，结果按 student_id 和 subject_name 排序。

查询结构格式如下所示：

# Write your MySQL query statement below
select 
	 t.student_id,
 	 t.student_name,
 	 t.subject_name,
-- 	 if(e.subject_name is null,0,1) as attended_exams
 	 
	 COUNT(e.subject_name) AS attended_exams
from
	(select *from Students as s1 Cross join Subjects as s2) as t 
	 left join Examinations as e on t.subject_name =e.subject_name and e.student_id=t.student_id
group by 
 	t.student_id,
 	t.subject_name
order by 
	t.student_id,
 	t.subject_name

# select 
# -- 		s1.student_id,
# -- 		s1.student_name,
# -- 		s2.subject_name
# -- 		count(*) as attended_exams
# 			*
# from
# -- 		Students_1280 as s1 left join Examinations_1280 as e on e.student_id=s1.student_id
# -- 		left join Subjects_1280 as s2 on s2.subject_name=e.subject_name
# -- 			Subjects_1280 as s2 left join Examinations_1280 as e on e.subject_name=s2.subject_name
# 				Students_1280 as s1 Cross join Subjects_1280 as s2
# -- group by 
# -- 		s1.student_id,
# -- 		s1.student_name,
# -- 		s2.subject_name

1501. 可以放心投资的国家


一家电信公司想要投资新的国家. 该公司想要投资的国家是: 该国的平均通话时长要严格地大于全球平均通话时长.

写一段 SQL, 找到所有该公司可以投资的国家.

返回的结果表没有顺序要求.

查询的结果格式如下例所示.

	select
  	    distinct t.countryName AS country  
	from
			(select 
				c1.name as countryName,
                c2.duration
			from
				Country as c1 left join Person as p  on left(p.phone_number,3) =  c1.country_code 
			    left join (SELECT caller_id , duration FROM Calls
                           UNION
                           SELECT callee_id , duration FROM Calls) as c2 on c2.caller_id = p.id) as t
	group by
			t.countryName
	having 
			avg(t.duration) > (SELECT avg(duration) from Calls)

184. 部门工资最高的员工

编写SQL查询以查找每个部门中薪资最高的员工。
按 任意顺序 返回结果表。
查询结果格式如下例所示。

# select
# 		t.Department,
# 		t.Employee,
# 		t.Salary
# from
# 	(
# 	select 
# 			d.name as Department,
# 			e.name as Employee,
# 			e.salary,
# 			rank() over(partition by d.name order by e.salary desc) as ranking
# 	from
# 			Employee as e left join Department as d  on d.id = e.departmentId
# 		) as t
# where 
# 		t.ranking=1

select
		d.name as Department,
		e.name as Employee ,
		e.salary as Salary 
from
		Department as d left join Employee as e on d.id = e.departmentId
where
		(d.name,e.salary) in (
								select
										d.name,
										max(salary) as salary
								from
										Department as d left join Employee as e on d.id = e.departmentId	
								group by 
										d.name		
		)

580. 统计各专业学生人数

编写一个SQL查询，为 Department 表中的所有部门(甚至是没有当前学生的部门)报告各自的部门名称和每个部门的学生人数。

按 student_number 降序 返回结果表。如果是平局，则按 dept_name 的 字母顺序 排序。

查询结果格式如下所示。

# select 
# 		*
# from
# 		(select
# 				d.dept_name,
# 				count(s.dept_id) as student_number 
# 		from
# 				Department as d left join Student as s on s.dept_id=d.dept_id
# 		group by
# 				d.dept_name) as t
# order by
# 		t.student_number desc, t.dept_name

select
	d.dept_name,
	# ifnull(count(student_name),0) student_number
    count(s.dept_id) as student_number
from
	Department as d left join Student as s on s.dept_id=d.dept_id
group by
	d.dept_name
order by
    student_number desc, d.dept_name