数据库作业2答案
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
SQL作业：
第三章：课后习题2：
(1)
select sno,sname from student where height>
(2)
select cno,credit from course where semester='秋' and cno like 'CS%';
(3)
select sname,,credit,grade from student s,course c,sc
where = and = and like 'CS%' and sex='男'and semester='秋'and grade is not null (4)
select sname from student s,sc
where = and cno like 'EE%' and sex='女'
or:
select sname from student s,sc
where = and cno like 'EE%' and sex='女'
group by ,sname having count(cno)>=1
(5)
select sno,count(cno),avg(grade) from sc
where grade is not null
group by sno
(6)
select cno,count(sno),max(grade),min(grade),avg(grade) from sc
where grade is not null
group by cno
(7)
select ,sname from student s,sc
where = and not in(select sno from sc where grade is null)
and grade is not null
group by ,sname having min(grade)>=80
(8)
select sname,,credit from student s,course c,sc
where = and = and grade is null
(9)
select sname from student s,course c,sc
where = and = and credit>=3 and grade<70
or:
select sname from student s,course c,sc
where = and = and credit>=3
group by ,sname having min(grade)<70
(10)
select sname,avg(grade),sum(credit) from student s,course c,sc
where = and = and year(bdate) between 1974 and 1976
group by ,sname
习题3：
delete from Students
where SNO LIKE '91%'
delete from SC
where SNO LIKE '91%'
习题4：
INSERT INTO Students(SNO,SNAME,SEX,BDATE,HEIGHT)
VALUES ('9409101','何平','女','1977-03-02',
INSERT INTO Students(SNO,SNAME,SEX,BDATE,HEIGHT)
VALUES ('9408130','向阳','男','1976-12-11',
习题5：
UPDATE Courses
SET CREDIT=3,LHOUR=60
WHERE CNAME='CS-221'
补充题：
试写出下述查询的SELECT命令
查c4比c16成绩好,但比c9成绩差的课程及其成绩;
select course-name,grade
from student s4,sc sc4,course cou4,student s16,sc sc16,course cou16,student s9,sc sc9,course cou9
where = and = and ='c4' and
= and = and ='c16' and
= and = and ='c9' and and
= and =
> and <
查修过计算机系课程，但未修过电机系课程的学生的数量;
select count(distinct sno)
from sc,course
where = and cname like 'cs%' and
sno not in (select sno from sc,course where = and cname like 'ee%')
列出学生张学友不比学生李连杰成绩高的课程名及其成绩;
select , from (select cno,grade from sc sc1,student s1
where = and ='张学友') as z(cno,grade),
(select cno,grade from sc sc2,student s2
where = and ='李连杰')as l(cno,grade),course c
where = and <= and =
下面的题目要利用db2中的sample数据库
employee(eid,ename,dept_id,salary);
manager(eid,dept-id);
sale(eid,district,sale-year,sale-month,sale-goods,sale-amount);
2.寻找一个部门中,工资数额多于本部门一半人的工资数额的雇员的名字和工资和部门编号.
select ename, salary, dept-id
from employee e
where (select count(*) from employee where dept_id=/2 <=
(select count(*) from employee where dept_id= and salary<
3寻找职员和经理有相同名字的部门
select dept_id
from manager,employee e
where = and
exists (select ename
from emplyee
where eid!= and = and =ename)
4找有相同职员数的部门名对
select ,
from employee d1, employee d2
where > and
(select count(*) from emplyee where dept_id==
(select count(*) from emplyee where dept_id=
5分别按年月,按区域和销售人员统计( 最大）销售额.
select sum(sale-amount)