数据库课后习题加真题

第二章

第三章

3.8 对于教学数据库的三个基本表：

s($\underline{学号}$，姓名，年龄, 性别)
sc($\underline{学号,课程号}$, 成绩)
c($\underline{课程号}$，课程名, 任课教师姓名)

1. 查询张小飞没有选修的课程号和课程名

   select cno, cname
   from c
   where not exists
   (
   	select *
   	from s
   	inner join sc on s.sno = sc.sno
   	where c.cno = sc.cno
   	and s.sname = '张小飞'
   );
   

2. 查询至少选修了3门课程的学生的学号和姓名

   select s.sno, sname
   from s
   inner join sc on s.sno = sc.sno
   group by s.sno, sname
   having count(sno) >= 3;                                                                                                                                       
   

3. 查询全部学生都选修了的课程编号和课程名称

   select c.cno, cname
   from c
   where not exists
   (
   	select *
   	from s
   	where not exists
   	(
   		select *
   		from sc
   		where c.cno = sc.cno
   		and s.sno = sc.sno
   	) 
   );
   

4. 在sc中删除尚无成绩的选课元组

   delete
   from sc
   where grade is null;
   

5. 把高等数学课的所有不及格成绩都改为60

   update sc
   set grade = 60
   where grade < 60
   and cno in
   (
   	select cno 
   	from c
   	where cname = '高等数学'
   );	
   

6. 把低于总评成绩的女同学的成绩提高5%

   update sc
   set grade = grade * 1.05
   where sno in 
   (
   	select sno
   	from sc
   	inner join s on sc.sno = s.sno
   	where gener = '女'
   	and grade < avg(grade) 
   );
   

7. 向c中插入元组(‘c8’, ‘vc++’, ‘王昆’)

   insert into c values('c8', 'vc++', '王昆');
   

3.9有下面四个关系模式
product (maker, model, type)
pc(model, speed, ram, hd, cd, price)
laptop(model, speed, ram, screen, price)
printer(model, color, type, price)
注：product表中type属性列的取值为pc或laptop或printer；printer表中的color取值为TRUE，false表示彩色还是单色

1. 找出价格高于1万5千元，并且运行速度低于同价位pc的平均速度的laptop

   select *
   from laptop 
   where price > 15000
   and speed <
   (
   	select avg(speed)
   	from lpc
   	where laptop.price = pc.price
   );
   

2. 计算厂家hp生产的pc机和laptop机的平均价格
   使用并集将两张表连起来

   select avg(price)
   from product 
   inner join pc on  product.model = pc.model
   where maker = 'hp'
   group by type
   union
   select avg(price)
   from product 
   inner join laptop on product.model = laptop.model
   where maker = 'hp'
   group by type; 
   

3. 找出生产价格最低的彩色打印机的厂家

   select maker
   from product
   where model in
   (
   	select model
   	from printer 
   	where color = true
   	and price <= all
   	(
   		select price
   		from printer
   	)
   );
   

4. 计算各厂商生产的laptop机的显示器的平均尺寸

   select maker, avg(screen) 
   from product
   innner join laptop on product.model = laptop.model
   group by maker;
   

5. 计算每一个生产厂商的pc机的最高价格

   select maker, max(price)
   from product 
   inner join pc on product.model = pc.model
   group by maker;
   

6. 计算生产打印机的各个厂商生产的pc机的硬盘的平均容量

   select maker, avg(hd)
   from product 
   inner join pc on product.model = pc.model
   where product.type = printer 
   group by maker;
   

第四到六章

某年真题

答案自己写的，谨慎参考