大学数据库系统代码

关卡一:

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

#请在此添加创建开课信息表的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 ##########

运行截屏:

大学数据库系统代码插图

关卡二:

大学数据库系统代码插图1

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

#********* 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 *********#

运行截屏:

大学数据库系统代码插图2

关卡三:

大学数据库系统代码插图3

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

#********* 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 *********#

运行截屏:

大学数据库系统代码插图4

关卡四:

大学数据库系统代码插图5

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

#********* 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 *********#

运行截屏:

大学数据库系统代码插图6

关卡五:

大学数据库系统代码插图7

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

#********* 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 *********#

运行截屏:

大学数据库系统代码插图8

关卡六(此题有坑):

大学数据库系统代码插图9

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

#********* 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 *********#

运行截屏:

大学数据库系统代码插图10

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

大学数据库系统代码插图11

文章标题:大学数据库系统代码
文章作者:xiaolan
文章版权:属于 小懒后花园 所有,转载请注明出处
本文链接:https://www.xlhhy.cn/archives/669
友情提示: 如果文章部分链接出现404,请留言或者联系博主修复。
暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇