前言

金九銀十期間，有蠻多小伙伴在跳槽找工作，但對于年限稍短的軟件測試工程師，難免會需要進行筆試，而在筆試中，基本都會碰到一道關于數據庫的大題，今天這篇文章呢，就是收集了一些小伙伴在面試中遇到的數據庫的筆試題。

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;

5、有三張表Contacts庫Consultant表、Basket庫BaseOrder表、Basket庫OrderDetails表,按要求寫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);

總結

今天的文章就到這里了喲，需要獲得下面福利的小伙伴可以私信我關鍵字“資料”獲取喲。

喜歡的小伙伴不要忘記點贊收藏評論加關注喲。

總結

以上是生活随笔為你收集整理的花了三天三夜才收集整理出来的经典 SQL 数据库笔试题及答案的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。