大学数据库系统代码

关卡一:

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

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

运行截屏:

此图片的alt属性为空;文件名为999612840.png

关卡二:

此图片的alt属性为空;文件名为1843283008.png

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

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

运行截屏:

此图片的alt属性为空;文件名为3735288593.png

关卡三:

此图片的alt属性为空;文件名为3377209521.png

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

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

运行截屏:

此图片的alt属性为空;文件名为3727684139.png

关卡四:

此图片的alt属性为空;文件名为1296551002.png

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

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

运行截屏:

此图片的alt属性为空;文件名为408778437.png

关卡五:

此图片的alt属性为空;文件名为316596938.png

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

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

运行截屏:

此图片的alt属性为空;文件名为1593280551.png

关卡六(此题有坑):

此图片的alt属性为空;文件名为122611830.png

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

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

运行截屏:

此图片的alt属性为空;文件名为217205445.png

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

此图片的alt属性为空;文件名为passpart.cc679602.png
暂无评论

发送评论 编辑评论


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