数据库第一次手抄作业题目及答案

86 阅读 0 评论 57 点赞

我是靠谱客的博主清秀眼神，最近开发中收集的这篇文章主要介绍数据库第一次手抄作业题目及答案，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

2018-SQL-1Download and execute the latest version of database constructionscript‘原版教材配套建表（附加首先删表）DDL+drop.sql’and data fillingscript‘大学示例数据库-2018.sql’。

1. Find the students who have registered some course at least 2 times.

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

 Name of the student

 Title of the course

 Count of registered record of the student for the course

2. 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

3. Find the sections which have register records having unknown gradeinformation.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

4. Find the students who enroll to the department having 4 instructorsat least. PROVIDE TWO METHODS for the query.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

5. Find the department which hired instructors whose name contain ‘世’.For each department as such, following information should belisted:

 Name of the department

 Building of the department

 Instructor count of the department

select
from takes
--第一题

with t as(select student .id ,course.course_id ,name, title
from takes join student on student .ID = takes .id
join course on takes.course_id = takes . course_id
)
select name,count(*),title
from t
group by id,course_id ,name,title
having COUNT (*)>1
order by name,title
--第二题

with t as(select student.id,student .name ,course .title
from takes join student on student .ID = takes .id
join course on takes.course_id = course . course_id
)
select id,name
from student
where not exists (select name from t where student.name = t.name )
--第三题
with t as(
select takes.course_id ,year,semester,title,grade,id
from takes join course on takes.course_id = course .course_id
)
select id,year,semester,title
from t
where grade is null

--第四题第一种方法
with t as(select department .dept_name as deptt_name
from department join instructor
on department .dept_name = instructor .dept_name
group by department .dept_name
having count(*)>=4)

select id,name,student.dept_name
from student right join t
on student.dept_name =t .deptt_name
-- 第四题第二种方法
select id,name,dept_name
from student
where dept_name in (
select department.dept_name
from department join instructor
on department .dept_name = instructor . dept_name
group by department.dept_name
having count(*)>=4
)

--第五题
with t as(select department .dept_name deptt_name,count(*) as sum_od_instr
from department join instructor
on department .dept_name = instructor .dept_name
group by department .dept_name
)
select department .dept_name ,building ,t.sum_od_instr
from department join t on department .dept_name = t.deptt_name
where department.dept_name in (
select department.dept_name
from department join instructor on department .dept_name =instructor .dept_name
where name like '%世%'
)

--4 老师。。表里有学生学生表里有老师