数据管理技术-Lab-3

数据管理技术第三次实验

关系模式

建立表名如下

1.1

代码

select sup_name
from supervisor, project_supervise
where supervisor.sup_id = project_supervise.sup_id
and p_id = 1
;

结果

1.2

代码

select distinct sup_name
from supervisor, project_supervise, project_execute
where supervisor.sup_id = project_supervise.sup_id
and project_execute.p_id = project_execute.p_id
and sec_id = 1
;

结果

1.3

代码

select distinct first.s_id, second.s_id
from staff as first left join section s on first.s_section_id = s.sec_id ,
     staff as second
where first.s_id != second.s_id
and second.s_id = s.sec_manager_id
;

结果

1.4

代码

select sum(p_budget)
from project ,(
    select distinct p_id
    from project_execute, staff
    where staff.s_section_id = project_execute.sec_id
    and staff.s_name like '张%'
) a
where a.p_id = project.p_id
;

结果

1.5

代码

select p_id
from project
where p_budget > (
    select max(p_budget)
    from project
    where p_day > 10
    )
;

结果

1.6

代码

select s_id, s_attendance_date
from staff_attendance
where s_attendance_date <= all(
    select s_attendance_date
    from staff_attendance
)
;

结果

1.7

代码

select sec_id, sum(p_budget)
from project, project_execute
where project_execute.p_id = project.p_id
group by project_execute.sec_id
having sum(p_budget) > 10000
;

结果

1.8

代码

select sup_name
from  supervisor, project_supervise
where supervisor.sup_id = project_supervise.sup_id
group by supervisor.sup_id
having count(p_id) >= 3
;

结果

关系模式

2.1

代码

select s_name
from student, choose_course ,course
where student.s_id = choose_course.s_id
and choose_course.c_id = course.c_id
and course.c_name = '物理'
;

结果

2.2

代码

select s_name
from student
where s_name like '诸%'
and s_name not like '诸葛%'
;

结果

2.3

代码

select t_id
from choose_course
group by t_id
having min(score) > 60
;

结果

2.4

代码

select s_id, count(c_id)
from choose_course
group by s_id
;

结果

2.5

代码

select course.c_name, score
from choose_course, student, course
where student.s_id = choose_course.s_id
and choose_course.c_id = course.c_id
and student.s_name = '李力'
and score < 60
order by score desc
;

结果

2.6

代码

select course.c_credit, count(s_id), avg(score)
from choose_course, course
where course.c_id = choose_course.c_id
group by course.c_id
;

结果

2.7

代码

select student.s_id, s_name
from student, choose_course, course
where student.s_id = choose_course.s_id
and choose_course.c_id = course.c_id
and choose_course.score >= 60
group by student.s_id
having sum(c_credit) < 10
;

结果