数据库上机（一）

1.Find the students who have never registered any course.

     For each student as such, following information should be listed:

•      ID of the student
•      Name of the student

select student.ID,student.name
from student left join takes
on student.ID = takes.ID
where takes.course_id is null

2.Find the sections which have register records having unknown grade information.

       For each section as such, following information should be listed:

• Title of the course corresponding to the section
• Id of the section
• Year of the section
• Semester of the section

  select course.title,section.sec_id as ID,section.year,section.semester
  from section join course  
  on course.course_id = section.course_id 
  where course.title  in 
  (select course.title
  from course left join takes on course.course_id = takes.course_id
  where takes.grade is NULL
  )
  

   

3.Find the students who enroll to the department having 4 instructors at least

       For each student as such, following information should be listed:

• Id of the student
• Name of the student
• Name of the department the student enrolled to

select S.name,S.ID,D.dept_name
from student S join department D on S.dept_name = D.dept_name
where D.dept_name  in
(select D.dept_name
from   department D join instructor I on D.dept_name = I.dept_name
group by D.dept_name
having count(I.name) >= 4
)

4.Find the department which hired instructors whose name contain ‘世’.

       For each department as such, following information should be listed:

1. Name of the department
2. Building of the department
3. Instructor count of the department

   select D.dept_name,D.building,count(I.name) instructor_count
   from department D join instructor I on D.dept_name = I.dept_name
   where I.name like '%世%' 
   group by D.dept_name,D.building