大学数据库系统代码

子昊
2021-08-23 / 0 评论 / 294 阅读 / 正在检测是否收录...

关卡一:

让我们愉快的(复制粘贴)开始吧!

#请在此添加创建开课信息表的SQL语句
########## Begin ##########
create table instructor
(
    ID varchar(5), 
    name varchar(20) not null, 
    dept_name    varchar(20), 
    salary numeric(8,2) check (salary > 29000),
    primary key (ID),
    foreign key (dept_name) references department(dept_name) on delete set null
);
create table section
(
    course_id varchar(8), 
      sec_id varchar(8),
    semester varchar(6) check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), 
    year numeric(4,0) check (year > 1701 and year < 2100), 
    building varchar(15),
    room_number    varchar(7),
    time_slot_id varchar(4),
    primary key (course_id, sec_id, semester, year),
    foreign key (course_id) references course(course_id) on delete cascade,
    foreign key (building, room_number) references classroom(building, room_number) on delete set null
);
########## End ##########

运行截屏:

关卡二:

让我们愉快的(复制粘贴)开始吧!

#********* Begin *********#
echo "
select distinct
    name
from
    student
join
    takes
on 
    student.id=takes.id

join 
    course
on
    course.course_id=takes.course_id
    and course.dept_name='Biology';



select
    name
from
    instructor m
join
(select 
    salary
from
    instructor 
where 
    dept_name='Biology')n
on
    m.salary>n.salary;


select name,m.dept_name dept_name,building
from
(select 
    building,i.dept_name 
from
    department i)m
join
    instructor d
on
    d.dept_name=m.dept_name;



select distinct
    i.dept_name as dept_name
from
    instructor i
join
    department d
on
    i.dept_name=d.dept_name
    and building='Watson';


"
#********* End *********#

运行截屏:

关卡三:

让我们愉快的(复制粘贴)开始吧!

#********* Begin *********#
echo "

select count(distinct ID) from teaches where semester='Spring' and year='2010';
select instructor.ID, instructor.name, instructor.dept_name from instructor 
join teaches where semester='Fall' and year=2009 group by dept_name having count(distinct course_id)>=2;
select dept_name,count(distinct instructor.ID) as instr_count from instructor join teaches where semester='Spring' and year=2010 group by dept_name;

"
#********* End *********#

运行截屏:

关卡四:

让我们愉快的(复制粘贴)开始吧!

#********* Begin *********#
echo "
select instructor.* from instructor 
order by salary desc,name asc;
select max(salary) from instructor;
select dept_name from instructor 
group by dept_name having avg(salary) >= all (select avg(salary) from instructor group by  dept_name);
select dept_name, avg(salary) as avg_salary from instructor 
group by dept_name having avg(salary)>50000 order by dept_name asc,avg_salary desc;


"
#********* End *********#

运行截屏:

关卡五:

让我们愉快的(复制粘贴)开始吧!

#********* Begin *********#
echo "

select distinct course_id from section where semester='Fall' and year=2009 and course_id not in (select course_id from section where semester='Spring' and year=2010);
select course_id from section as S where year=2009 and semester='Fall' and exists (select section.* from section as T where year=2010 and semester='Spring' and S.course_id=T.course_id);
select course_id from section where year=2009 and semester='Fall' union all select course_id from section where year=2010 and semester='Spring';
select course_id,semester,year,sec_id,avg(tot_cred) from takes join student where year=2009 group by course_id,semester,year,sec_id having count(takes.ID)>=2;

"
#********* End *********#

运行截屏:

关卡六(此题有坑):

让我们愉快的(复制粘贴)开始吧!

#********* Begin *********#
echo "

select instructor.name,teaches.course_id 
from instructor,teaches 
where instructor.ID=teaches.ID 
and dept_name='Physics';
select name 
from instructor 
where dept_name='Physics' 
and salary>80000;
update instructor 
set salary = case 
when salary <= 100000 
then salary*1.05 
else salary*1.03 end;

create view Physics_fall_2009 
as 
select course.course_id,building,room_number 
from course,section where course.course_id = section.course_id 
and course.dept_name='Physics' 
and section.semester='Fall' 
and section.year='2009';


"
#********* End *********#

运行截屏:

{message type="success"}大功告成!{/message}

2

评论 (0)

取消