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

3.1

• b

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

• e

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

• f

  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)
  

• g

  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

• a

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

• b

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

• c

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

3.5

• a

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

• b

  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

• a

  select customer_name
  from loan natural join borrower
  where amount=0
  

• b

  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")
  

• c

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

3.9

• a

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

• b

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

• c

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

• d

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

• e

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

• f

  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)
                        )
  

• g

  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

• a

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