最近有蠻多小伙伴在跳槽找工作，但對(duì)于年限稍短的軟件測(cè)試工程師，難免會(huì)需要進(jìn)行筆試，而在筆試中，基本都會(huì)碰到一道關(guān)于數(shù)據(jù)庫(kù)的大題，今天這篇文章呢，就收錄了下最近學(xué)員反饋上來(lái)的一些數(shù)據(jù)庫(kù)筆試題，包含答案！

1、依據(jù)以下學(xué)生表、班級(jí)表，按要求寫(xiě) 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 保存客戶(hù)的基本信息；Table B 保存客戶(hù)的資產(chǎn)負(fù)債信息。按要求寫(xiě) 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、數(shù)據(jù)庫(kù)(編寫(xiě)示例 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、現(xiàn)有三張數(shù)據(jù)表如下：學(xué)生資料表：記錄學(xué)生基本信息；課程表：記錄課程基本信息；成績(jī)表：記錄每人各門(mén)課程成績(jī)的信息，1 個(gè)學(xué)生對(duì)應(yīng)多個(gè)成績(jī)，1 個(gè)成績(jī)只屬于一個(gè)學(xué)生，一個(gè)課程

答案：

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;
5、有三張表 Contacts 庫(kù) Consultant 表、Basket 庫(kù) BaseOrder 表、Basket 庫(kù) OrderDetails 表，按要求寫(xiě) SQL

?

答案：

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);
ps：小編個(gè)人收集啦許多軟測(cè)資料，希望能夠幫到學(xué)軟件測(cè)試的朋友們軟件資料分享包括但不限于：Java自動(dòng)化測(cè)試、Python自動(dòng)化測(cè)試、性能測(cè)試、web測(cè)試、APP測(cè)試

測(cè)試開(kāi)發(fā)工具包：appuim安裝包、fiddler安裝包（也有配套視頻教程）、eclipse、git、jmeter、loadrunner、monkey、postman、soapul、Xmind等等

最后感謝每一個(gè)認(rèn)真閱讀我文章的人，禮尚往來(lái)總是要有的，雖然不是什么很值錢(qián)的東西，如果你用得到的話可以直接拿走：

?

這些資料，對(duì)于【軟件測(cè)試】的朋友來(lái)說(shuō)應(yīng)該是最全面最完整的備戰(zhàn)倉(cāng)庫(kù)，這個(gè)倉(cāng)庫(kù)也陪伴上萬(wàn)個(gè)測(cè)試工程師們走過(guò)最艱難的路程，希望也能幫助到你！