共计 2167 个字符,预计需要花费 6 分钟才能阅读完成。
前 言
2021 到了最初一个月份,年后必定有蛮多小伙伴须要跳槽换工作,但对于年限稍短的软件测试工程师,难免会须要进行口试,而在口试中,根本都会碰到一道对于数据库的大题,明天这篇文章呢,就收录了下最近学员反馈上来的一些数据库口试真题,蕴含答案!
1、根据以下学生表、班级表,按要求写 SQL
答案:
1、
select * from student a
left join class b on a.c_id = b.id
where score = (
select max(score) from student)
2、
select b.name,count(*),avg(score) as avgScore from student a,class b
where a.c_id = b.id
group by b.name
order by avgScore;
2、Table A 保留客户的根本信息;Table B 保留客户的资产负债信息。按要求写 SQL
答案:
(1)select ID from A where name = ‘ 李四 ’;
(2)select NO,NAME,ID from A,B where A.NO = B.NO AND DEPOSIT >= 1000;
(3)select COUNTRY,count(*) FROM A GROUP BY COUNTRY;
(4)SELECT ’80 后 ’ as 年龄段,sum(CREDIT) AS ‘ 信用卡余额 ’ FROM A,B WHERE A.NO = B.NO AND BIRTH >= 19800101 AND BIRTH < 19900101
UNION
SELECT ’90 后 ’ as 年龄段,sum(CREDIT) AS ‘ 信用卡余额 ’ FROM A,B WHERE A.NO = B.NO AND BIRTH >= 19900101 AND BIRTH < 20000101;
3、数据库 (编写示例 sql)
答案:
select orderNo, if(status=1,’ 新建 ’,’ 解决中 ’) from OrderTrans;
select DATE_FORMAT(a.crttime,’%m-%d-%Y’) ,username,count(*) from testCase a,User b where a.crtUser = b.UserId group by DATE_FORMAT(a.crttime,’%m-%d-%Y’),b.username, having status = ‘ 胜利 ’;
select count(*),sum(amount),DATE_FORMAT(crttime,’%m-%d-%Y’) from OrderInfo group by
DATE_FORMAT(crttime,’%m-%d-%Y’);
4、现有三张数据表如下:学生材料表:记录学生根本信息;课程表:记录课程根本信息;成绩表:记录每人各门课程问题的信息,1 个学生对应多个问题,1 个问题只属于一个学生,一个课程
答案:
Select * from students where jg =‘湖北’and birthday =‘1992-6-1’order by no asc;
Select avg(so.cj),min(so.cj),max(so.cj),sum(so.cj) from student st,course c,source so where st.no = so.no and c.kebh=so.kebh and st.name =‘王华’group by st.no;
Select st.no,st.name,st.bj,c.kebh,c.kcmc,so.cj from student st,course c,source so where st.no = so.no and c.kebh=so.kebh and st.name =‘张三’order by so.cj desc;
答案:
Select Consultant.SubsidiaryID, BaseOrder.* from Consultant, BaseOrder where Consultant.ConsultantID = BaseOrder.ConsultantID and Consultant.SubsidiaryID = 29 order by Orderid desc;
Select BaseOrder.ConsultantID,sum(OrderDetails.TotalPrice) from BaseOrder,OrderDetails where BaseOrder.Orderid=OrderDetails.Orderid and month(OrderDate) = 5 group by BaseOrder.ConsultantID;
Insert into Consultant (ConsultantID,ConstultantStatusID,SubsidiaryID,Name) values (200000,10,29,’Gary’);
Delete from Consultant where Name like‘%Gary%’;
Update BaseOrder set OrderDate = sysdate() where ConsultantID in (select ConsultantID from BaseOrder where ConsultantID=100003 order by OrderDate desc limit 0,1);
鉴于篇幅所限,无奈一一展现面试题,有须要这份《大厂面试宝典》附答案的能够点击上面链接自取