数据库题目集锦_软件测试工程师
1.假设有一个教学管理数据库有以下3个关系:
学生表:Students(学号sno,姓名sname,年龄age,性别sex,籍贯jg)、
课程表:Courses(课程号cno,课程名cname,学分xf)、
选课表:Enrolls(学号sno,课程号cno,分数score):
1.1 查询哪些课程只有女生选读;
1.2 查各课程的平均成绩,按课程号分组,只选择学生超过3人的课程成绩;
1.3 找出不选“数据库”课程的学生学号、姓名;
1.4 找出选修了全部课程的学生姓名;
1.5 求平均成绩90分以上,选课男生人数3人以上的课程名。
-- 1.1
-- 关联(外连接)
select t1.cno
(select s.sno,e.cno from students s,enrolls e
where s.sno=e.sno and s.sex='女') t1
left join
(select s.sno,e.cno from students s,enrolls e
where s.sno=e.sno and s.sex='男') t2
on t1.cno=t2.cno
where t2.cno is null;
-- 子查询
select e.cno from students s,enrolls e
where s.sno=e.sno and s.sex='女'
and s.cno not in(select e.cno from students s,enrolls e
where s.sno=e.sno and s.sex='男')
-- 1.2
select avg(score) from Enrolls e group by e.cno having count(1)>3;
-- 1.3
select s.sno,s.sname from Students s
where s.sno not in (select e.sno from Enrolls e where e.cno =(select c.cno from Courses c where c.cname='数据库'))
-- 1.4
select s.sname from Students s
where s.sno in (select e.sno from Enrolls e group by e.sno having count(1)=(select count(1) from Courses c))
-- 1.5
select c.cname from Enrolls e,Courses c where e.cno=c.cno
group by e.cno having avg(e.score)>90
and e.cno in(select e.cno from Enrolls e,Students s
where e.sno=s.sno and s.sex='男' group by e.cno having count(1)>3);
2.Employee 表包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Mark | 90000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询,找出每个部门工资最高的员工。例如,根据上述给定的表格,Mark 在 IT 部门有最高工资,Henry 在 Sales 部门有最高工资。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Mark | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
答案:
select d.name as department, e.employee, c.salary
from employee e, department d
(select departmentid, max(salary) salary from employee group by deptmentid) c
where e.departmentid=d.departmentid and d.departmentid=c.departmentid
and e.salary = c.salary;
3.编写一个SQL查询,删除Info表中所有重复的电子邮箱,重复的邮箱只保留Id最小的记录。
+--------+-------------------+
| Id | Email |
+--------+-------------------+
| 1 | john@example.com |
| 2 | boss@example.com |
| 3 | john@example.com |
+--------+-------------------+
答:
delete from info a
where a.email in (select b.email from info b group by b.email having count(b.email ) >= 2)
and a.id not in (select min(c.id) from info c group by c.email having count(c.email ) >= 2);
4、根据题目要求,写出对应的sql 雇员表:employee +-------+-------+-----+-----+ | EmpId | Name | sex | age | +-------+-------+-----------+ | 1 | 张鹏 | 男 | 25 | | 2 | 张晓 | 女 | 20 | | 3 | 王明 | 男 | 22 | | 4 | 王刚 | 男 | 24 | | 6 | 彭飞 | 男 | 18 | +-------+-------+-----+-----+ 销售表:sal +-------+--------------+-----+-----+---------+ | EmpId |product |price|count| date | +-------+--------------+-----------+---------+ | 1 | 遥控飞机A 款 |1000 | 1 |20190520 | | 2 | 遥控汽车 |600 | 1 |20190601 | | 2 | 遥控飞机B 款 |800 | 1 |20190530 | | 3 | 玩具枪 |100 | 2 |20190510 | | 4 | 儿童自行车 |300 | 1 |20190520 | +-------+--------------+-----+-----+---------+ 问题1:查年龄大于 20 以上的员工信息 select * from employee e where e.age>20; 问题2:统计每个人的销售额 select s.empid, sum(s.price*s.count) from sal s group by s.empid; 问题3:统计5月份销售额为0的员工信息 select e.* from employee e left join (select * from sal s where s.date like '201905__') ss
on e.empid = ss.empid where ss.empid is null; -- 外连接:左连接(左边为主表,主表每一行都显示) select * from dept d left join emp e on d.deptno=e.deptno where e.empno is null; 5、某班学生表和考试成绩表分别如下: 表student: +-------+--------------+ | ID | Name | +-------+--------------+ | 1 | Jack | | 2 | Marry | | 3 | Rose | | 4 | Bob | | 5 | john | | 6 | Betty | | 7 | Robert | +-------+--------------+ 表:Achievement +-------+----------+ | ID | score | +-------+----------+ | 1 | 90 | | 2 | 96 | | 3 | 88 | | 4 | 86 | | 5 | 83 | | 6 | 83 | +-------+----------+ 其中 ID为学生的编号,Name 为姓名,Mark 为成绩,请针对以下问题,写出相应的 sql语句: 1、请查询成绩>85分的学生的姓名; select b.name from student b where b.id in(select a.id from acheviment a where a.mark >85); 2、请查询成绩>=90分的人数: select count(*) from acheviment a where a.mark>90; 3、Robert 此次考试考了80分,但是成绩没能录入表中,请将其成绩添加进去; insert into acheviment values(7,80); insert into acheviment (select s.id,80 from student s where s.name='Robert'); 4、请将Rose 的成绩修改为 87; update acheviment a set a.mark=87 where a.id=(select s.id from student s where s.name='Rose'); 5、请删除 Betty 的记录; delete from acheviment a where a.id=(select s.id from student s where s.name='Bettty'); delete from student s where s.name='Bettty'; 6. 查询出分数为 83 且姓名首字母为B开头的学生; select * from acheviment a, student s where a.id=s.id and a.mark=83 and s.name like 'B%';
6、把这样的图1学生表xs用一条SQL语句查询得到图2的效果。
图1: 学生表xs
图2:
解题思路:利用decode临时增加列名,并且对decode取别名,同时利用子查询。decode用于当前行的条件判断。
--创建xs学生表
create table xs(
xno varchar2(3) ,
name varchar2(10),
course varchar2(10),
score number(5,2)
)
--插入数据
insert into xs values ('01','张三','数学','80');
insert into xs values('01','张三','语文','81');
insert into xs values('01','张三','英语','88');
insert into xs values('02','王五','数学','70');
insert into xs values('02','王五','语文','90');
insert into xs values('02','王五','英语','77');
commit;
select x.* from xs x;
答案如下:
select x1.xno,x1.name,
sum(x1.shuxue) shuxue,
sum(x1.yuwen) yuwen,
sum(x1.yingyu) yingyu
from (select x.xno,x.name,
decode(x.course,'数学',x.score,null) shuxue,
decode(x.course,'语文',x.score,null) yuwen,
decode(x.course,'英语',x.score,null) yingyu
from xs x) x1
group by x1.xno,x1.name;
7.
Table A
+------+----------+---------+
|stuID | classID | stuName |
+------+----------+---------+
| 1 | A | chen |
| 2 | A | wang |
| 3 | B | li |
| 4 | B | yang |
+------+----------+---------+
Table B
+--------+----------+
|classID | className|
+--------+----------+
| A | 一班 |
| B | 二班 |
+--------+----------+
Table C
+------+--------+-------+
|stuID | course | score |
+------+--------+-------+
| 1 | 数学 | 80 |
| 1 | 英语 | 85 |
| 1 | 语文 | 90 |
| 2 | 数学 | 85 |
| 2 | 英语 | 85 |
| 2 | 语文 | 95 |
| 3 | 语文 | 88 |
| 4 | 英语 | 85 |
| 3 | 数学 | 95 |
| 3 | 英语 | 88 |
| 4 | 语文 | 85 |
| 4 | 数学 | 99 |
+------+-------+-------+
1.查找wang的数学分数;
select A.stuName, C.cource, C.score
from A,C where A.stuID=C.stuID and A.stuName='wang' and C.cource='数学';
2.查找一班各学科分数最高的学生;
select xx.stuName,yy.course,yy.max
from (select * from A,C where A.stuID=C.stuID) xx,
(select C.course,max(score) max from A,B,C
where A.classID=B.classID and A.stuID=C.stuID and B.className='一班'
group by C.course) yy
where xx.course=yy.course and xx.score=yy.max;