经典 SQL 数据库笔试题及答案整理(笔试答案数据库测试整理)

文章目录 [+]

1、依据以下学生表、班级表，按要求写 SQL

答案：

1、select from student aleft join class b on a.c_id = b.idwhere score = (select max(score) from student)

（图片来自网络侵删）

2、select b.name,count(),avg(score) as avgScore from student a,class bwhere a.c_id = b.idgroup by b.nameorder 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 < 19900101UNIONSELECT '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 byDATE_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);

ps：

测试开发工具包：appuim安装包、fiddler安装包（也有配套视频教程）、eclipse、git、jmeter、loadrunner、monkey、postman、soapul、Xmind等等

喜欢的话可以给小编点点赞哦，私信小编“测试”免费领取