3.12使用大学模式,用SQL写出如下查询。
创建一门课程”CS-001”,其名称为“Weekly Seminar”,学分为0
1
2
3
4
5
6insert into course values
('CS-001',
'Weekly Seminar',
'',
0
);创建该课程在2009年秋季的一个课程段,sec_id为1。
1
2
3
4
5
6
7
8
9insert into section values
('CS-001',
1,
'Fall',
2009,
NULL,
NULL,
NULL,
);让Comp. Sci.系的每个学生都选修上述课程段 。
1
2
3
4
5
6
7
8
9insert into takes select
ID,
'CS-001',
1,
'Fall',
2009,
NULL
from student
where dept_name = 'Comp.Sci.';删除名为Chavez的学生选修上述课程段的信息。
1
2
3
4
5
6
7
8
9
10delete from takes
where course_id = 'CS-001'
and sec_id = 1
and year = 2009
and ID in
(
select ID
from student
where name = 'Chavez'
);删除课程CS-001。如果在运行此删除语句之前.没有先删除这门课程的授课信息(课程段), 会发生什么事情?
1
2delete from course
where course_id = 'CS-001';如果在这个删除语句之前没有删除授课信息的话,会导致对应课程段的信息和选课信息一同被删除。
删除课程名称中包含“database”的任意课程的任意课程段所对应的所有tasks元组.在课程名的匹配中忽略大小写。
1
2
3
4
5
6
7delete from takes
where course_id not in
(select course_id
from course
where lower(title)
like '%database%'
);
3.16考虑图3-20中的雇员数据库,其中加下划线的是主码。给出下面每个查询对应的SQL表达式:
找出所有为“First Bank Corporation” 工作的雇员名字
1
2
3select employee_name
from works
where company-name = 'First Bank Corporation'找出数据库中所有居住城市和公司所在城市相同的雇员。
1
2
3
4select employee_name
from employee A join works using (employee_name)
join company B using (employee_name)
where A.city = B.city找出数据库中所有居住的街道和城市与其经理相同的雇员。
1
2
3
4
5
6select employee_name
from employee A, employee B, manages M
where A.employee_name = M.employee_name
and B.employee_name = M.manager_name
and A.street = B.street
and A.city = B.city找出工资高于其所在公司雇员平均工资的所有雇员。
1
2
3
4
5
6
7with avg_salary
as (select company_name,avg(salary) as val
from works
group by company_name)
select employee_name
from works natural join avg_salary
where salary > val;找出工资总和最小的公司。
1
2
3
4
5
6select company_name
from works
group by company_name
having sum(salary) = (select Min(Sum(salary))
from works
group by company_name);