数据库面试题(4)

数据库面试题
四数据库写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分别代表学号、所选修的课程编号、学习成绩
14.表结构:
………
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子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．
6.有一张表，字段有用户名、口令及备注，请用SQL选择出用户名和口令完全相同的记录（应包括用
户名和数量的出现次数）
T_USER(USER_NAME,PASSWORD)
显示
USER_NAME COUNT(*)
QWE 4
解答：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
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
解答：
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);
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);
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
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_, cust_record.address);
要求如下：
1)使用标准SQL语句查询成员名单中所属单位叫“技术一部”的人员总数及
平均年龄；
2)使用标准的SQL语句更新学号为‘S#1’的姓名为“Mike”；
3)使用嵌套语句查询选修课程编号为‘C2’的学员姓名和所属单位；
4)使用嵌套语句查询不选修课程编号为‘C5’的学员姓名和所属单位；
5)查询选修课程超过5门的学员学号和所属单位；
2）select customer "购物人",
sum(decode(product_name,'甲',quantity,0)) "商品甲",
sum(decode(product_name,'乙',quantity,0)) "商品乙",
sum(decode(product_name,'丙',quantity,0)) "商品丙"
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青霉素
2西瓜霜
3创可贴
4西洋参
SU（进货表）
GOODSID（主键）SUQTY
1 60
2 70
SA（销售表）
GOODSID（主键）SAQTY
3 80
4 90
要求一：进货记录，给出SQL达到以下结果
GOODSID（主键）GOODSNAME SUQTY
1青霉素60
2西瓜霜70
3创可贴0
4西洋参0
要求二：进销对比，给出SQL达到以下结果
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);
2.请根据以下四张表（其中course_t表的teacher_id字段是teacher_t表的id字段的外键引用），
拼写出相应的sql语句（oracle语法）。（15分）
学生表：students_t
id name sex
001赵学生Male
002钱学生Male
003孙学生Male
004李学生Female
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
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);
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;
并用表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;
解答：
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');
where s.id=sc.student_id and c.id=sc.course_id group by
sc.student_id order by count(c.credit);
2) select as s_name, as c_name , as t_name
from tb_order
group by customer;
12.有如下两张表：部门表和职员表，每个职员都属于一个部门，表结构如下：
Dept表
Deptno Deptname
……
Emp表
Empno Empname Deptno
………