《Database System Concepts（数据库系统概念）》第六版——第三次作业（第三章）

JeekZhang

于 2023-04-03 18:59:49 发布

阅读量209

点赞数 1

分类专栏：数据库系统概念文章标签：数据库 sql sqlserver

本文链接：https://blog.youkuaiyun.com/qq_50939442/article/details/129936712

版权

数据库系统概念专栏收录该内容

10 篇文章

订阅专栏

这些SQL查询涉及多表连接（如JOIN），条件过滤（WHERE子句），聚合函数（COUNT,MAX,SUM），分组（GROUPBY）以及子查询，主要用于数据提取和分析，例如查找特定导师的学生，计算学分加权分数，评估成绩分布，贷款和银行信息，以及员工薪资比较。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

3.1

select distinct student.ID
from student natural join advisor,instructor
where instructor.ID=advisor.i_id and instructor.name="Einstein"

select course_id,sec_id,count(ID)
from section natural join takes
where year=2009 and semester="fall"
group by course_id,sec_id

select max(sum)
from (select count(ID) as con
	from section natural join takes
	where year=2009 and semester="fall"
	group by course_id,sec_id)

select course_id,sec_id
from section natural join takes
where count(ID)=(select max(con)
				from (select count(ID) ans con
					from section natural join takes
					where year=2009 and semester="fall"
					group by course_id,sec_id)) and year=2009 and semester="fall"
group by course_id,sec_id	)
					)

3.2

 select count(points*credits)
 from ((takes natural join course ) join grade_points using grade
 where ID=12345

 select sum(points*credits) / sum(credits)
 from ((takes natural join course ) join grade_points using grade
 where ID=12345

 select ID,sum(points*credits) / sum(credits)
 from ((takes natural join course ) join grade_points using grade
group by ID

3.5

select ID,
	case 
		when score<40 then 'F'
		when score<60 then 'C'
		when score<80 then 'B'
		else 'A'
		as grade
from marks

select grade,count(ID)
from (select ID,
	case 
		when score<40 then 'F'
		when score<60 then 'C'
		when score<80 then 'B'
		else 'A'
		as grade
	from marks)
group by grade

3.8

select customer_name
from loan natural join borrower
where amount=0

select customer_name
from customer
where customer_street=(select customer_street from customer where customer_name="Smith") and customer_city=(select customer_city from customer where customer_name="Smith")

select distinct branch_name
from (account natural join depositor) join customer using customer_name
where customer_city="Harrison"

3.9

select employee_name,city
from employee natural join works
where company_name="First Bank Corporation"

select employee_name,street,city
from employee natural join works
where company_name="First Bank Corporation" and salary>10000

select employee_name
from employee natural join works
where company_name<>"First Bank Corporation"

select employee_name
from employee natural join works
where  salary > all(select salary
					from employee natural join works
					where company_name="Small Bank Corporation")

select company_name
from company
where city in (select city
				from company
				where company_name="Small Bank Corporation")

select company_name
from works
where count(employee_name)=(select max(con)
				from (select count(employee_name) as con
					from works
                    group by company_name)
                      )

select company_name
from works
group by company_name
having avg(salary)>(select avg(salary)
					from works
					where company_name="First Bank Corporation")

3.14

select damage_amount
from participated join person
where name="John Smith"