数据库第一次作业
3.1 使用大学模式，用SQL写出如下查询。
a.找出Comp.Sci系开设的具有三个学分的课程名称。
select title
from course
where dept_name = 'Comp. Sci.' and course.credits = 3;
b. 找出名叫Einstein的教师所教的所有学生的标识，保证结果中没有重复。
select distinct takes.id
from instructor natural join teaches
join takes using (course_id,sec_id,semester,year)
where name = 'Einstein';
c. 找出教师的最高工资
select max(salary)
from instructor
d. 找出工资最高的所有教师
select name
from instructor
where salary=(select Max(salary) from instructor)
e.找出2009年秋季开设的每个课程段的选课人数
select course_id, sec_id, count(ID)
from section natural join takes
where semester = 'Fall' and year = 2009
group by course_id, sec_id
f.从2009年秋季开设的每个课程段中，找出最多的选课人数
select Max(cnt)
from (
select Count(ID) as cnt
from section natural join takes
where semester = 'Fall' and year = 2009 group by course_id, sec_id
)
g.找出在2009年秋季拥有最多选课人数的课程段。
with Fall2009 as (
select course_id, sec_id, count(ID) as cnt
from section natural join takes
where semester = 'Fall' and year = 2009
group by course_id, sec_id
)
select course_id,sec_id
from Fall2009
where cnt = (select max(cnt) from Fall2009)
3.2 假设给你一个关系grade_points(grad_e,points),他提供从takes关系中用字母表示的成绩等级到数字表示的得分之间的转换。例如，“A”等级可指定为对应于4分，“A-”对应于3.7分，“B+”对应于3.3分，“B”对应于3分，等等。学生在某门课程（课程段）上所获取的等级分值被定义为该课程段的学分乘以该生得到的成绩等级所对应的数字表示的得分。
/根据题意创建表/
/*创建表关系*/
create table Grade_points
(
grade varchar (2),
Points numeric(3, 1)
);
/*插入值*/
insert into grade_points values ('A',4.0);
insert into grade_points values ('A-',3.7);
insert into grade_points values ('B+',3.3);
insert into grade_points values ('B',3.0);
insert into grade_points values ('B-',2.7);
insert into grade_points values ('C+',2.3);
insert into grade_points values ('C',2.0);
insert into grade_points values ('C-',1.7);
a.根据ID为12345的学生所选修的所有课程，找出该生所获得等级分值的总和。
select distinct sum(credits * points)
from takes natural join course natural join grade_points
where ID = 12345;
b. 找出上述学生等级分值的平均值(GPA),即用改等级分值的总和除以相关课程学分的总和。
select distinct sum(credits * points) /sum(credits) AS res
from takes natural join course natural join grade_points
where ID = 12345;
c.找出每个学生的ID和等级分值得平均值
select distinct sum(credits * points) /sum(credits) AS GPA
from takes natural join course natural join grade_points
group by ID;
3.3 使用大学模式，用SQL写出如下出入、删除和更新语句。
a.给 Comp.Sci 系老师涨10%的工资。
update instructor
set instructor.salary = instructor.salary * 1.10
where instructor.dept_name = 'Comp. Sci.';
b.删除所有未开设过的课程。
delete from course
where course_id not in (select distinct course_id from section);
c.把每一个在tot_cread属性上取值超过100的学生作为同系的教师插入，工资为10000美元。
insert into instructor
select ID,name,dept_name,10000
from student
where tot_cred >= 100;
3.4 考虑图3-18中的保险公司数据，其中加下划线的是主码，为这个数据库构造出如下SQL查询。
person( driver_id, name, address)
ar( license, model, year)
ccident( report_number, date, location)
wns (driver_id, license)
artcipated ( report_number, license, driver_id, damage_amount)
a.找出2009年其车辆出现过交通事故的人员总数。
select count(distinct driver_id) AS num_of_person
from person natural join participated natural join accident
where date between date ’1989-00-00’ and date ’1989-12-31’
b.向数据库中增加一个新的事故，对每个必须的属性可以设定任意值。
insert into accident
values (1239401,'1991-01-01','Wl');
c.删除“John Smith”拥有的马自达车（Mazda）。
delete frrm car
where Model = 'Mazda' and license in (
select license
from person p, owns o
where p.name = ’John Smith’ and p.driver id = o.driver id
);
3.5 假设有关系marks(ID, score), 我们希望基于如下标准为学生评定等级：如果 score < 40 得 F； 如果 40 <= score < 60 得 C； 如果 60 <= score < 80 得 B；如果 80 <= score 得A。 写出SQL查询完成下列操作。
a.基于marks关系显示每个学生的等级。
select ID,
case
when score < 40 then 'F'
when score < 60 then 'C'
when score < 80 then 'B'
else 'A'
end
as rank
from marks;
b.找出各等级的学生数
select count(ID) as cnt
from
(
select ID,
case
when score < 40 then 'F'
when score < 60 then 'C'
when score < 80 then 'B'
else 'A'
end
as rank
from marks;
)group by rank;
3.6 SQL的like运算符是大小写敏感的，但字符串上的lower()函数可用来实现大小写不敏感的匹配。为了说明是怎么实现的，写出这样一个插叙：找出名称中包含了“sci”子串的系， 忽略大小写。
select dept_name
from department
where lower(dept_name) like '%sci%';
3.7 考虑以下SQL查询在什么条件下这个查询选择的p.al值要么在r1中，要么在r2中？仔细考察r1或r2位空的情况。
select distinct p.al
from p,r1,r2
where p.al = r1.al or p.a1 = r2.al;
解: 当且仅当r1,r2非空时，p.a1要么在r1中要么在r2中。
当r1或r2空时,r1 X r2为空。
当p是空时，显然查询结果是为空的正解
3.8 考虑图3-19中的银行数据库，期中加下划线的是主码。为这个关系数据库构造出如下SQL查询：
银行数据库
branch(branch name, branch city, assets)
customer (customer name, customer street, customer city)
loan (loan number, branch name, amount)
borrower (customer name, loan number)
account (account number, branch name, balance )
depositor (customer name, account number)
a.找出银行中所有有账号但无贷款的客户
(select customer_name from desositor)
from
(select customer_name from borrower)
b.找出与Smith居住在同一城市、同一接到的所有客户的名字
select F.customer_name
from customer F join customer S using (customer_street,customer_city)
where S.customer_name = 'Smith';
c.找出所有支行的名称，在这些支行中都有居住在Harrison的客户所开的账户
select branch_name
from acoount natural join depositor natural join customer
where branch_city = 'Harrison';
3.9 考虑图3.20的雇员数据库，其中加下划线的是主码。为下面每个查询写出SQL表达式：
图3.20 雇员数据库
employee (employee name, street, city)
works (employee name, company name, salary)
company (company name, city)
manages (employee name, manager name)
a.找出所有为First Bank Corporation工作的雇员机器居住城市。
select employee_name,city
from employee
where employee_name in (
select employee_name
from works
where company_name = 'First Bank Corporation'
);
b.找出所有为First Bank Corporation工作且薪金超过10000美元的雇员名字、居住的接到和城市。
select employee_name,street,city
from employee natural join works
where company_name = 'First Bank Corporation' and salary > 10000;
c.找出数据库中所有不为First Bank Corporation工作的雇员。
select employee_name
from works
where employee_name not in (
select employee_name
from works
where company_name = 'First Bank Corporation'
);
d.找出数据库中所有工资不高于Small Bank Corporation
select employee_name
from works
where salary > (
select max(salary)
from works
where company_name = 'Small Bank Corporation'
);
e. 假设一个公司可以在好几个城市有分部。找出位于Small Bank Corporation所在城市的所有公司。
select C.company_name
from company C join company P using (city)
where P.company_name = 'Small Bank Corporation';
f.找出雇员最多的公司
select company_name
from works
group by company_name
having count (distinct employee_name) >=
all( select count (distinct Employee_name)
from works
group by company_name);l
g.找出平均工资高于First Bank Corporation的那些公司。
select company_name
from works
group by company_name
having avg(salary) > (
select avg(salary)
from works
where company_name = 'First Bank Name');
3.10 考虑图3.20的关系数据库，给出下面每个查询的sql表达式：
a. 修改数据库使John现在居住在Newton。
update employee
set city = 'Newtown'
where employee_name = 'Johns';
b. 为First Bank Corporation所有工资不超过100000美元的经理增长10%的工资，对工资超过100000美元的只增长3%。
update works
set salary =
case
when salary * 1.10 <= 100000 then salary * 1.10
else salary * 1.03
end
where company_name = 'First Bank Corporation' and employee_name in (select employee_name from managers);
3.11 使用大学模式，用SQL写出如下查询。
a.找出至少选修了一门Comp. Sci. 课程的学生姓名，保证结果中没有重复的姓名
select distinct student.name
from student natural join takes join course using (course_id)
where course.dept_name = 'Comp. Sci.';
b.找出所有没有选修在2009年春季之前开设的任何课程的学生的ID和姓名。
select distinct ID,name
from student
where id not in((select distinct ID
from takes
where year < 2009 or(year = 2009 and semester ='Spring')));
c.找出每个系教师的最高工资。可以假设每个系至少有一位老师。
select dept_name,max(salary)
from instructor
group by dept_name;
d.从前述查询所计算出的每个系最高工资中选出最低值。
select min(MAX_SALARY)
from(select dept_name,max(salary) as MAX_SALARY
from instructor
group by dept_name);
3.12 使用大学模式，用SQL写出如下查询。
a.创建按一门课程CS- 001，其名称为Weekly Seminar，学分为0。
insert into course values
('CS-001',
'Weekly Seminar',
'',
0);
b.创建该课程在2009年秋季的一个课程段，sec_id 为1。
insert into section
values ('CS - 001',
1,
'Fall',
2009,
NULL,
NULL,
NULL);
c.让Comp. Sic.系的每一个学生都选修上述课程段。
insert into takes
select ID,
'CS - 001',
1,
'Fall',
2009,
NULL
from student
where Dept_name = 'Comp. Sci.';
d. 删除名为Chavez的学生选修上述课程段的信息。
delete from takes
where course_id = 'CS-001'
and sec_id = 1
and year = 2009
and semester = 'Fall'
and ID in(
select ID
from student
where name = 'Chavez'
);
e.删除课程CS - 001如果在运行此删除语句之前，没有先删除这门课程的授课信息(课程段)，会发生什么事情。
delete from course
where course_id = 'CS-001';
如果在删除语句之前，没有先删除课程段，则其对应课程段和学生的选课信息一起被删。因为在建表的DDL语句中加入了级联删除的设置。
f.删除课程名称中包含datebase的任意课程的任意课程段所对应的所有take元组，在课程名的匹配中忽略大小写。
delete from takes
where course_id not in (select course_id
from course
where lower(title) like '%database%');
3.13写出图3-18中模式的SQL DDL。在数据类型上做合理的假设，确保申明主码和外码。
create table person
(
driver_id varchar(50),
name varchar(50),
address varchar(50),
primary key(driver_id)
);
create table car
(
license varchar(50),
model varchar(50),
year numeric(5,0),
primary key (license)
);
create table accident
(
report_number varchar(10),
dateion date,
loaction varchar(50);
primary key (report_number);
);
create table owns
(
driver_id varchar(50),
license varchar(50),
primary key(driver_id),
foreign key (driver_id) references person,
foreign key (license) references car
);
create table participated
(
report_num varchar(10),
license varchar(50),
driver_id varchar(50),
damage_amount numeric(10,0),
primary key (report_number,license),
foreign key (report_number) references accident,
foreign key (license) references car,
foreign key (driver_id) references owns
);
3.14考虑图3-18中的保险公司数据库，其中加下划线的是主码。对这个关系数据库构造如下的SQL查询。
a.找出和John Smith的车有关的交通事故数量。
select count(report_number)
from participated
where license in (select license
from person natural join owns
where name = 'John Smith');
b.对事故报告编号为AR2197中的车牌是AABB2000的车辆损坏保险费用更新到3000美元。
update participated
set damage_amount
where report_number = 'AR2197' and license = 'AABB2000';
3.15 考虑图3-19中的银行数据库，其中加下划线的是主码。为这个关系数据库构造出如下的SQL查询。
a.找出在Brooklyn的所有支行都有账户的所有客户。
with branchcount as
(
select count(*)
from Branch
where branch_city = 'Brooklyn'
)
select custumer_name
from customer c
where branchcount = (select count(distinct branch_name)
from (customer natural join depositor natural join account natural join branch) as d
where d.customer_name = c.customer_name);
b.找出银行的所有贷款额的总和。
select sum(amount)
from loan;
c.找出总资产至少比位于Brooklyn的所有支行的某一家支行要多的所欲支行的名字
select branch_name
from branch
where assets > some(select assets
from branch
where branch_city = 'Brooklyn');
3.16 考虑图3-20中的雇员数据库，其中加下划线的是主码。给出下面每个查询对应的SQL查询式。
a.找出所有为First Bank Corporation工作的雇员的名字。
select employee_name
from works
where company_name = 'First Bank Corporation':
b.找出数据库中所有居住城市和公司所在城市相同的雇员。
select employee_name
from employee E join works using (employee_name) join company C using (company_name)
where E.city = C.city;
c.找出数据库中所有居住城市和接到与其经理相同的雇员。
select P.employee_name
from employee P,employee R,managers M
where P.employee_name = M.employee_name
and R.employee_name = M.manger_name
and P.street = R.street
and P.city = R.city;
d. 找出工资高于其所在公司雇员平均水平的所以雇员。
with 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;
e.找出工资总和最小的公司。
select company_name
from works
group by company_name
having sum(salary) = (select Min(Sum(salary))
from works
group by company_name);
3.17 考虑图3-20中的关系数据库。给出下面每个查询对应的SQL表达式。
a.为First Bank Corporation的所有雇员增长10%的工资。
update works
set salary = salary * 1.10;
where company_name = 'First Bank Corporation';
b.为First Bank Corporation的所有经理增长10%的工资。
update works
set salary = salary * 1.10;
where company_name = 'First Bank Corporation' and employee_name in (select manager_name from managers);l
c.删除Small Bank Corporation的雇员在Works关系中的所有元组。
delete from works
where company_name = 'First Bank Corporation';
3.21 考虑图3-21中的图书馆数据库。用SQL写出如下查询。
member(memb no, name, age)
book(isbn, title, authors, publisher)
borrowed(memb no, isbn, date)
a.打印借阅了任意由McGraw-Hill出版的书的会员的名字。
select distinct name
from member natural join book natural join borrowed
where publisher = 'McGraw-Hill';
b.打印借阅了所有由McGraw-Hill出版的书的会员的名字。
select name
from member
where memb_no in (select memb_no
from borrowed
group by memb_no
having count(*) = (select count(*)
from book
where publisher='McGraw-Hill'));
c.对于每个出版商，打印借阅了多余五本由该出版社出版的书的会员名字。
select name,publisher
from member natural join borrowed natural join book
group by name,publisher
having count(isbn) > 5;
d. 打印每一位会员借阅书籍数量的平均值。考虑这样的情况：如果某会员没有借阅任何书籍，那么该会员根本不会出现在borrowed关系中。
select(select count(*) from borrowed)/ (select count(*)from member)
3.23考虑以下查询，解释为什么在from子句中还加上与section的连接不会改变查询结果。
select course id, semester, year, section id, avg (credits earned)
from takes natural join student
where year = 2009
group by course id, semester, year, section id
having count (ID) >= 2;
解：takes 和 section 是通过一些共同的外键相连系，每一个takes的某一个元组不会因为增加额外的元祖。
3.24考虑以下查询，不使用with结构，重写此查询。
with dept total (dept name, value) as
(select dept name, sum(salary)
from instructor
group by dept name),
dept total avg(value) as
(select avg(value)
from dept total)
select dept name
from dept total, dept total avg
where dept total.value >= dept total avg.value;
解:答案如下:
select dept_name
from (select dept name, sum(salary)
from instructor
group by dept name) P
where P.val >= (select avg(sum(salary))
from instructor
group by dept_name);