四 数据库写 SQL 题（30）
1.按要求写 SQL 语句：根据集团成员培训业务，建立以下三张表：
S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄 C (C#,CN ) C#,CN 分别代表课程编号、课程名称
SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩 要求如下：
1)使用标准 SQL 语句查询成员名单中所属单位叫“技术一部”的人员总数及 平均年龄；
2)使用标准的 SQL 语句更新学号为?S#1?的姓名为“Mike” ； 3)使用嵌套语句查询选修课程编号为?C2?的学员姓名和所属单位； 4)使用嵌套语句查询不选修课程编号为?C5?的学员姓名和所属单位； 5)查询选修课程超过 5 门的学员学号和所属单位； 解答：
1) select count(SN),avg(SA) from S where SD='技术一部'; 2) update S set SN='Mike' where S#='S#1';
3) select SN,SD from S where S#=(select S# from SC where C#='C2'); 4) select SN,SD from S where S# not in(select S# from SC where C#='C5'); ?5) select S#,SD from S where S#=
(select S# from SC group by S# having count(S#)>=5); 2.请根据以下四张表（其中 course_t 表的 teacher_id 字段是 teacher_t 表的 id 字段的外键引用） ，
拼写出相应的 sql 语句（oracle 语法）（15 分） 。 学生表：students_t
id name sex 001 赵学生 Male 002 钱学生 Male 003 孙学生 Male 004 李学生 Female 005 周学生 Female ? ? ? 教师表：teacher_t
id name sex 001 吴老师 Male 002 郑老师 Male 003 王老师 Male 004 刘老师 Female 005 张老师 Female 课程表：course_t
id name credit teacher_id 001 语文 3 001 002 数学 3 002 003 英语 4 003
004 物理 3 004 005 化学 2 005 006 政治 1 001 007 生物 1 005 008 计算机 2 005 选课表：student_course_t
id student_id course_id 001 001 001 002 001 002 003 001 003 004 002 001 005 002 007 ? ? ?
1)统计每个学生选修的学分，并按学分降序排序
2)统计每个学生选修的所有课程和对应的任课老师；并按学生 Id 和课程 Id 排序 3)统计所有学生、所有课程和所有任课老师的对应关系；并按学生 Id 和课程 Id 排序 解答：
1）select sc.student_id,count(c.credit)
from students_t s, course_t c, student_course_t sc
where s.id=sc.student_id and c.id=sc.course_id group by sc.student_id order by count(c.credit);
2) select s.name as s_name,c.name as c_name ,t.name as t_name from students_t s, course_t c, student_course_t sc,teacher_t t
where s.id=sc.student_id and c.id=sc.course_id and t.id=c.teacher_id order by s.id,c.id; 3)与 2）相同
3.假设有以下的两个表： Cus_A
ID* Name Address ? ? ? Cus_B
ID* Name Address ? ? ? *主键
表 Cus_A 和表 Cus_B 的结构完全相同，表 Cus_A 和表 Cus_B 中既存在 ID 相同的记录，也存在 ID 不
同的记录。 现要求将 ID 只存在于表表 Cus_A 中而不存在于表 Cus_B 中的记录全部插入到 Cus_B 表中，
并用表 Cus_A 中的记录更新 Cus_B 中相同的 ID 的记录，请写出完成这一功能的存储过程。 解答：
create or replace procedure test is
cust_record cus_a%rowtype ;
cursor cust_cursor is select id,name,address from cus_a;
Begin
Open cust_cursor; LOOP
Fetch cust_cursor into cust_record;
EXIT WHEN cust_cursor %NOTFOUND; --先删除在插入
delete from cus_b where id=cust_record.id;
insert into cus_b values(cust_record.id, cust_record.name, cust_record.address); END LOOP; end;
4、已有“成绩”如下表所示：
学号 课程号 分数 S1 C1 80 S1 C2 75 S2 C1 null S2 C2 55 S3 C3 90 1） 执行 SQL 语句：
Select Count（学号）From 成绩 Where 分数〉60 后的结果是什么？
2）请写出 SQL 语句来进行查询“成绩”表中学号为 S1、课程号为 C2 的学号和分数 解答：
1）统计分数超过 60 的学生总数。
2）select 学号，分数 from 成绩 where 学号=?S1? and 课程号=?C2?;
5.SAL 是 Product 表中的索引列，请优化如下 SQL 语句，并简述原因。原语句： SELECT*
FROM Product
WHERE SAL * 12 〉25000； 解答：
Select * from product where sal>(25000/12);
理由：WHERE 子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．
6.有一张表，字段有用户名、口令及备注，请用 SQL 选择出用户名和口令完全相同的记录（应包括用
户名和数量的出现次数）
T_USER(USER_NAME,PASSWORD) 显示
USER_NAME COUNT(*) QWE 4 WER 5
解答：select user_name,count(*) from t_user group by user_name,password;
7.有一张表，T_MONEY，字段有 ID，FEE，请用 SQL 语言选择出 FEE 值为前三条记录。 T_MONEY(ID，FEE) 显示
ID FEE 2 100 1 90 2 80
? Select Id,fee from (Select id,fee from t_money order by fee desc) where rownum<=3; 8、table_name temp Id name 1 a 2 b 3 a 4 a 结果为
Id name 1 a 2 b 写出 sql 语句。
? 解答：select rownum as id , name from(select distinct name from temp); 9、已知原表(t_salary) year salary 2000 1000 2001 2000 2002 3000 2003 4000
先要实现显示结果(salary 为以前的工资和) year salary 2000 1000 2001 3000 2002 6000
写出 sql 语句。 ? 解答：
select t.year, sum(t.salary) over (order by t.year) as sum_salary from salary_t t; 10.有两个表 A 和 B，均有 key 和 value 两个字段，如果 B 的 key 在 A 中也有，就把 B 的 value 换为 A 中对应的 value
这道题的 SQL 语句怎么写？ 解答:
merge into A a using B b
on (a.key=b.key) when matched then update set
a.value=b.value
11.创建一张数据表，并插入如下数据。 购物人 商品名称 数量
A 甲 2 B 乙 4 C 丙 1 A 乙 2 B 丙 5 1)写出创建表和插入内容的 sql 语句 2)写出 sql 语句使其产生如下结果
购物人 商品甲 商品乙 商品丙 A 2 2 Null B Null 4 5 C Null Null 1 解答：
create table tb_order(
customer varchar2(20),
product_name varchar2(20), quantity number(2) )
Insert into tb_order(customer,product_name,quantity)values(?A?,?甲?,2); Insert into tb_order(customer,product_name,quantity)values(?B?,?乙?,4); Insert into tb_order(customer,product_name,quantity)values(?C?,?丙?,1); Insert into tb_order(customer,product_name,quantity)values(?A?,?甲?,2); Insert into tb_order(customer,product_name,quantity)values(?B?,?乙?,5); ? 2）select customer \购物人\
sum(decode(product_name,'甲',quantity,0)) \商品甲\sum(decode(product_name,'乙',quantity,0)) \商品乙\sum(decode(product_name,'丙',quantity,0)) \商品丙\from tb_order
group by customer;
12.有如下两张表：部门表和职员表，每个职员都属于一个部门，表结构如下： Dept 表
Deptno Deptname ? ? Emp 表
Empno Empname Deptno ? ? ?
请使用 SQL 语句查询每个部门有多少职员，要求查询结果包含两例（部门名称，人数）？ ? 解答：select d.deptname,count(*) from dept d,emp e where d.deptno=e.deptno group by d.deptno,d.deptname;
13.业务场景：存在下面的表及记录 GOODS（进货表）
GOODSID（主键） GOODSNAME MEMO 1 青霉素 2 西瓜霜 3 创可贴