A. sql面试题
1，答
select * into B库.table_b from A库.table_a
2，答
select * into A库.new_table from A库.table_a left outer join B库.table_b on A库.table_a.name=B库.table_b.name and A库.table_a.Text=B库.table_b.text
delete from A库.table_a left outer join A库.new_table on A库.table_a.name=A库.new_table.name and A库.table_a.Text=A库.new_table.text
delete from B库.table_b left outer join A库.new_table on b库.table_b.name=A库.new_table.name and b库.table_b.Text=A库.new_table.text
3 答
select * from （select top 10 * from （select top 100 * from 表 ）as a） as b
4 答
select * into 表c from 表A
union all
select * into 表c from 表B
试试如果不对请指正。
B. 如何用SQL分析电商用户行为数据（案例）
本文以“淘宝用户行为数据集”的分析全过程为例，展示数据分析的全过程
——使用工具：MySQL，Excel，Navicat，PowerBI
——分析类型：描述分析，诊断分析
——分析方法：漏斗分析，用户路径分析，RFM用户价值分析，活跃/存留分析，帕累托分析，假设验证分析。
（考虑到阅读体验文章中只放了SQL截图，如需PDF版本，再公众号后台回复“用户行为分析”领取）
（目录如下）
1.分析流程和方法
当没有清晰的数据看板时我们需要先清洗杂乱的数据，基于分析模型做可视化，搭建描述性的数据看板。
然后基于描述性的数据挖掘问题，提出假设做优化，或者基于用户特征数据进行预测分析找规律，基于规律设计策略。简单来说：
——描述性分析就是：“画地图”
——诊断性分析就是：“找问题”
——预测性分析就是 ：“找规律”
在数据分析中有两个典型的场景：
一种是有数据，没有问题：需要先整体分析数据，然后再根据初步的描述分析，挖掘问题做诊断性分析，提出假设，设计策略解决问题。
另一种是已经发现了问题，或者已经有了假设，这种做数据分析更偏向于验证假设。
2.淘宝用户行为分析
本次是对“淘宝用户行为数据集”进行分析，在分析之前我们并不知道有什么问题，所以需要先进行描述性分析，分析数据挖掘问题。
我们首先来看下这个数据集的元数据：
根据以上数据字段我们可以拿用户行为为主轴从纵深方向提出一些问题，然后再从数据中找答案
纵向：
——这个数据集中用户的日活跃和周活跃时间有什么规律吗？
——在当日活跃的用户次日，三日，四日……还有多少活跃？
深向：
——用户从浏览到购买的整体转化率怎么样？
——用户从浏览到购买的路径是怎么样子的？
——平台主要会给用户推送什么商品？
——用户喜欢什么类目？喜欢什么商品？
——怎么判断哪些是高价值用户 ？
下面是叮当整理的常用分析方法：
我们可以给前面的问题匹配一下分析方法，便于后面的分析：
为了便于后面的数据分析，在分析之前我们需要先对做一下清洗
看元数据（字段解释，数据来源，数据类型，数据量……）初步发现问题为之后的处理做准备。
确定缺失值范围，去除不需要字段，填充缺失内容
根据元数据格式和后续分析需要的格式对数据进行处理
去除重复值，异常值
——去除重复值：并把用户ID,商品ID，时间戳设置为主键
——异常值处理：查询并删除2017年11月25日至2017年12月3日之外的数据
查询并删除小于2017-11-25的
——验证数据：
——分析思路：
——SQL提数：
——Excel可视化：
活跃曲线整体为上升状态，同为周六日，12月2号，3号相比11月25日，26日活跃度更高。
用户在周六周日相比其他时间更活跃（周六周日为休息日，用户有更多时间）
一天内用户活跃的最高峰期为21点（用户在这个时间段空闲较多）
——分析思路：
——SQL提数：
列出每用户每天及当天后面又活跃的日期，并创建“活跃时间间隔表”用于后面求次日存留，三日存留……
对“活跃时间间隔表视图”引用进行分组统计，计算每日存留人数并创建视图
对存留人数表进行计算，统计活跃用户留存率
——Excel可视化：
——分析思路：
——SQL提数：
-把各种用户行为分离出来并创建视图方便后续查询用户行为数据
查询整体数据漏斗
——Excel可视化：
用户从浏览到购买整体转化率2.3%，具体主要在哪个环节流失还需要再细分用户路径分析
——分析思路：
——SQL提数：
——PowerBI可视化：
用户从浏览到购买的路径主要有4条，路径越长转化率越底
路径1：浏览→购买：转化率1.45%
路径2：浏览→加购物车→购买：转化率0.33
路径3：浏览→收藏→购买：转化率0.11%
路径4：浏览→收藏→加购物车→购买：转化率0.03%
——分析思路：
——SQL提数：
——Excel可视化：
——描述性分析：
浏览量top100的商品浏览量呈阶梯分布，越靠前的阶梯之间的落差相对越大在这个阶梯中的商品越少，越靠后商品浏览量阶梯之间的落差相对越小，同阶梯内的商品越多。
浏览量TOP100的商品所属类目中，4756105，3607361，4357323三个类目浏览量远超其他类目。
——分析思路：
——SQL提数：
查询计算商品转化率，升序排列，取前100个
——Excel可视化：
——描述性分析：
从商品看：有17款商品转化率超过了1。
从类目看：这些商品所属类目分布均匀，除965809，4801426，2735466，2640118，5063620，4789432，2945933这7个类目之外，其他类目都只有一个商品在转化率TOP100的商品中。
——分析思路：
用户价值分析常用的分析方式是RFM模型
本次分析中的R,F,M具体定义（仅用于演示分析方法，无实际业务参考价值）：
——SQL取数与分析：
1）建立打分标准：先计算R,F的值，并排序，根据R,F值最大值和最小值得区间设计本次得打分标准
-查询并计算R，F值创建视图
-引用RF数值表，分别查询R,F的最大值和最小值
-结合人工浏览的建立打分标准
2）给R，F按价值打分
3）计算价值的平均值
4）用平均值和用户分类规则表比较得出用户分类
——Excel可视化
通过描述性分析得到可视化的数据后我们一般会先看一下是否符合业务常识
如果符合常识接下来我们会通过与行业平均数据和本产品的同比环比对比看是否正常，如果不正常就要找原因，设计解决方案，如果正常那就看是否有可以优化的地方。
我们首先来看一下这些描述性分析是否符合业务常识和指标是否正常：
1.活跃曲线整体为上升状态，同为周六日，12月2号，3号相比11月25日，26日活跃度更高。
2.用户在周六周日相比其他时间更活跃
3.一天内用户活跃的最高峰期为21点
4.从2017年11月15日致2017年12月3日，活跃用户新增38%
5.从2017年11月15日致2017年12月3日，活跃用户次日留存增长18.67%，当日的活跃用户留存也在快速增长，第七日留存比次日留存高18.56%。
6.用户从浏览到购买整体转化率2.3%
7.用户从浏览到购买的路径主要有4条，路径越长转化率越低。
8.浏览量top100的商品浏览量呈阶梯分布，越靠前的阶梯之间的落差相对越大在这个阶梯中的商品越少，越靠后商品浏览量阶梯之间的落差相对越小，同阶梯内的商品越多。
9.浏览量TOP100的商品所属类目中，4756105，3607361，4357323三个类目浏览量远超其他类目。
10.从商品看：有17款商品转化率超过了1。
11.从类目看：这些商品所属类目分布均匀，除965809，4801426，2735466，2640118，5063620，4789432，2945933这7个类目之外，其他类目都只有一个商品在转化率TOP100的商品中。
根据以上诊断分析我们梳理出了以下假设，做假设验证。
假设1：这些商品中有高转化率的爆款商品
对比浏览量TOP5的商品，发现这些商品转化率在同一类目下并不高，假设不成立
假设2：4756105，3607361，4357323三个类目属于高频刚需类目
-创建类目购买频次表
-计算类目购买频次平均值
-查询4756105，3607361，4357323三个类目的购买频次
4756105，3607361，4357323三个类目的用户购买频次明显高于平均值，假设成立
假设3：有部分用户是未点击商详直接从收藏和购物车购买的。
用户不是直接从收藏和购物车购买的，只是后续复购未点击商详，假设不成立
假设4：淘宝推荐的商品主要是“同一类目下的高转化商品”
用Excel对浏览量TOP100的商品ID和转化率TOP100的商品ID进行去重，结果无重复值，假设不成立
3.结论：
1）用户活跃：用户活跃曲线整体呈上升趋势，在一周中周六，周日活跃度比平时更高，在一天中用户活跃曲线从凌晨4点开始往上升，在中午12点和下午5~6点有两个小低谷（吃饭），到晚上9点时活跃度达到顶峰。
2）用户留存：从2017年11月15日致2017年12月3日的用户留存数据来看，淘宝的用户留存数据较好，活跃用户次日留存增长18.67%，当日的活跃用户留存也在快速增长，第七日留存比次日留存高18.56%。
3）用户转化：整体转化2.3%，用户从浏览到购买的路径主要有4条，路径越长转化率越低。
4）平台推荐与用户偏好：从数据集中的数据来看，排除用户兴趣偏好标签，淘宝给用户用户推送的商品主要是高频刚需的类目，促使用户复购，流量回流平台。
以上结论受数据量和数据类型的影响，并不一定准确，仅用来练习数据分析方法。
（考虑到阅读体验文章中只放了SQL截图，如需PDF版本，再公众号后台回复“用户行为分析”领取）
C. SQL数据库题目，求助
1、select * from 学生信息 where 年龄=18;
2、select 学号,姓名,班级 from 学生信息 where 班级='08电商1';
3、select 姓名,班级 from 学生信息 where 姓名 like '李%';
4、select * from 老师信息 where 姓名 like '_丽%';
5、select 班级，姓名,年龄 from 学生信息 where 班级='08电商1' or 班级='08物流2';
6、select 工号,姓名,部门 from 教师信息 where 工资>2500;
7、select 班级,count(*) as 人数 from 学生信息 group by 班级;
8、select top 10 学号,姓名 from 学生信息 order by 年龄 desc;
9、select * from 学生信息 where 班级='08电商2' order by 学号 asc;
10、select 性别,sum(年龄)/count(年龄) as 平均年龄 from 学生信息 group by 性别;或者select 性别,avg(年龄) as 平均年龄 from 学生信息 group by 性别
D. 面试题 ： 求大神啊用sql语句列出销售金额最多的前三个商品名称和金额
1,select * from(
select a.price*c.number,a.name from goods a,trade c where a.id=c.goodid order by a.price*c.number) where rownum<4;
2,select avg(a.price*c.number),b.name from goods a,customer b,trade c where a.id=c.goodid and b.id=c.customerid group by b.name;
E. SQL查询面试题与答案
SQL查询面试题与答案
SQL语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名。下面是我搜集的SQL查询面试题与答案，欢迎大家阅读。
SQL查询面试题与答案一
1.一道SQL语句面试题，关于group by表内容：
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果, 该如何写sql语句?
胜 负
2005-05-09 2 2
2005-05-10 1 2
------------------------------------------
create table #tmp(rq varchar(10),shengfu nchar(1))
insert into #tmp values('2005-05-09','胜')
insert into #tmp values('2005-05-09','胜')
insert into #tmp values('2005-05-09','负')
insert into #tmp values('2005-05-09','负')
insert into #tmp values('2005-05-10','胜')
insert into #tmp values('2005-05-10','负')
insert into #tmp values('2005-05-10','负')
1)select rq, sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case when shengfu='负' then 1 else 0 end)'负' from #tmp group by rq
2) select N.rq,N.胜,M.负 from (
select rq,胜=count(*) from #tmp where shengfu='胜'group by rq)N inner join
(select rq,负=count(*) from #tmp where shengfu='负'group by rq)M on N.rq=M.rq
3)select a.col001,a.a1 胜,b.b1 负 from
(select col001,count(col001) a1 from temp1 where col002='胜' group by col001) a,
(select col001,count(col001) b1 from temp1 where col002='负' group by col001) b
where a.col001=b.col001
2.请教一个面试中遇到的SQL语句的查询问题
表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。
------------------------------------------
select (case when a>b then a else b end ),
(case when b>c then b esle c end)
from table_name
3.面试题：一个日期判断的sql语句?
请取出tb_send表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型，包含日期与时间)
------------------------------------------
select * from tb where datediff(dd,SendTime,getdate())=0
4.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来(并写出您的思路)：
大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。
显示格式：
语文 数学 英语
及格 优秀 不及格
------------------------------------------
select
(case when 语文>=80 then '优秀'
when 语文>=60 then '及格'
else '不及格') as 语文,
(case when 数学>=80 then '优秀'
when 数学>=60 then '及格'
else '不及格') as 数学,
(case when 英语>=80 then '优秀'
when 英语>=60 then '及格'
else '不及格') as 英语,
from table
5.在sqlserver2000中请用sql创建一张用户临时表和系统临时表，里面包含两个字段ID和IDValues,类型都是int型，并解释下两者的区别?
------------------------------------------
用户临时表:create table #xx(ID int, IDValues int)
系统临时表:create table ##xx(ID int, IDValues int)
区别:
用户临时表只对创建这个表的用户的Session可见,对其他进程是不可见的.
当创建它的进程消失时这个临时表就自动删除.
全局临时表对整个SQL Server实例都可见,但是所有访问它的Session都消失的时候,它也自动删除.
6.sqlserver2000是一种大型数据库，他的`存储容量只受存储介质的限制，请问它是通过什么方式实现这种无限容量机制的。
------------------------------------------
它的所有数据都存储在数据文件中(*.dbf),所以只要文件够大,SQL Server的存储容量是可以扩大的.
SQL Server 2000 数据库有三种类型的文件：
主要数据文件
主要数据文件是数据库的起点，指向数据库中文件的其它部分。每个数据库都有一个主要数据文件。主要数据文件的推荐文件扩展名是 .mdf。
次要数据文件
次要数据文件包含除主要数据文件外的所有数据文件。有些数据库可能没有次要数据文件，而有些数据库则有多个次要数据文件。次要数据文件的推荐文件扩展名是 .ndf。
日志文件
日志文件包含恢复数据库所需的所有日志信息。每个数据库必须至少有一个日志文件，但可以不止一个。日志文件的推荐文件扩展名是 .ldf。
7.请用一个sql语句得出结果
从table1,table2中取出如table3所列格式数据，注意提供的数据及结果不准确，只是作为一个格式向大家请教。
如使用存储过程也可以。
table1
月份mon 部门dep 业绩yj
-------------------------------
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8
table2
部门dep 部门名称dname
--------------------------------
01 国内业务一部
02 国内业务二部
03 国内业务三部
04 国际业务部
table3 (result)
部门dep 一月份 二月份 三月份
--------------------------------------
01 10 null null
02 10 8 null
03 null 5 8
04 null null 9
------------------------------------------
1)
select a.部门名称dname,b.业绩yj as '一月份',c.业绩yj as '二月份',d.业绩yj as '三月份'
from table1 a,table2 b,table2 c,table2 d
where a.部门dep = b.部门dep and b.月份mon = '一月份' and
a.部门dep = c.部门dep and c.月份mon = '二月份' and
a.部门dep = d.部门dep and d.月份mon = '三月份' and
2)
select a.dep,
sum(case when b.mon=1 then b.yj else 0 end) as '一月份',
sum(case when b.mon=2 then b.yj else 0 end) as '二月份',
sum(case when b.mon=3 then b.yj else 0 end) as '三月份',
sum(case when b.mon=4 then b.yj else 0 end) as '四月份',
sum(case when b.mon=5 then b.yj else 0 end) as '五月份',
sum(case when b.mon=6 then b.yj else 0 end) as '六月份',
sum(case when b.mon=7 then b.yj else 0 end) as '七月份',
sum(case when b.mon=8 then b.yj else 0 end) as '八月份',
sum(case when b.mon=9 then b.yj else 0 end) as '九月份',
sum(case when b.mon=10 then b.yj else 0 end) as '十月份',
sum(case when b.mon=11 then b.yj else 0 end) as '十一月份',
sum(case when b.mon=12 then b.yj else 0 end) as '十二月份',
from table2 a left join table1 b on a.dep=b.dep
8.华为一道面试题
一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。
------------------------------------------
select id, Count(*) from tb group by id having count(*)>1
select * from(select count(ID) as count from table group by ID)T where T.count>1
SQL查询面试题与答案二
1、查询不同老师所教不同课程平均分从高到低显示
SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩
FROM SC AS T,Course AS C ,Teacher AS Z
where T.C#=C.C# and C.T#=Z.T#
GROUP BY C.C#
ORDER BY AVG(Score) DESC
2、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理(001)，马克思(002)，UML (003)，数据库(004)
[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
SELECT DISTINCT top 3
SC.S# As 学生学号,
Student.Sname AS 学生姓名 ,
T1.score AS 企业管理,
T2.score AS 马克思,
T3.score AS UML,
T4.score AS 数据库,
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分
FROM Student,SC LEFT JOIN SC AS T1
ON SC.S# = T1.S# AND T1.C# = '001'
LEFT JOIN SC AS T2
ON SC.S# = T2.S# AND T2.C# = '002'
LEFT JOIN SC AS T3
ON SC.S# = T3.S# AND T3.C# = '003'
LEFT JOIN SC AS T4
ON SC.S# = T4.S# AND T4.C# = '004'
WHERE student.S#=SC.S# and
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
NOT IN
(SELECT
DISTINCT
TOP 15 WITH TIES
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
FROM sc
LEFT JOIN sc AS T1
ON sc.S# = T1.S# AND T1.C# = 'k1'
LEFT JOIN sc AS T2
ON sc.S# = T2.S# AND T2.C# = 'k2'
LEFT JOIN sc AS T3
ON sc.S# = T3.S# AND T3.C# = 'k3'
LEFT JOIN sc AS T4
ON sc.S# = T4.S# AND T4.C# = 'k4'
ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);
3、统计打印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
SELECT SC.C# as 课程ID, Cname as 课程名称
,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]
,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]
,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]
,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]
FROM SC,Course
where SC.C#=Course.C#
GROUP BY SC.C#,Cname;
4、查询学生平均成绩及其名次
SELECT 1+(SELECT COUNT( distinct 平均成绩)
FROM (SELECT S#,AVG(score) AS 平均成绩
FROM SC
GROUP BY S#
) AS T1
WHERE 平均成绩 > T2.平均成绩) as 名次,
S# as 学生学号,平均成绩
FROM (SELECT S#,AVG(score) 平均成绩
FROM SC
GROUP BY S#
) AS T2
ORDER BY 平均成绩 desc;
5、查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;
6、查询每门课程被选修的学生数
select c#,count(S#) from sc group by C#;
7、查询出只选修了一门课程的全部学生的学号和姓名
select SC.S#,Student.Sname,count(C#) AS 选课数
from SC ,Student
where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2
from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2
9、查询所有课程成绩小于60分的同学的学号、姓名;
select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);
10、查询没有学全所有课的同学的学号、姓名;
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';
12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;
select distinct SC.S#,Sname
from Student,SC
where Student.S#=SC.S# and C# in (select C# from SC where S#='001');
13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;
update SC set score=(select avg(SC_2.score)
from SC SC_2
where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');
14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;
select S# from SC where C# in (select C# from SC where S#='1002')
group by S# having count(*)=(select count(*) from SC where S#='1002');
15、删除学习“叶平”老师课的SC表记录;
Delect SC
from course ,Teacher
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';
16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2、
号课的平均成绩;
Insert SC select S#,'002',(Select avg(score)
from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');
17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
SELECT S# as 学生ID
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企业管理
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语
,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩
FROM SC AS t
GROUP BY S#
ORDER BY avg(t.score)
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分
FROM SC L ,SC AS R
WHERE L.C# = R.C# and
L.score = (SELECT MAX(IL.score)
FROM SC AS IL,Student AS IM
WHERE L.C# = IL.C# and IM.S#=IL.S#
GROUP BY IL.C#)
AND
R.Score = (SELECT MIN(IR.score)
FROM SC AS IR
WHERE R.C# = IR.C#
GROUP BY IR.C#
);
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均成绩
,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
FROM SC T,Course
where t.C#=course.C#
GROUP BY t.C#
ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理(001)，马克思(002)，OO&UML (003)，数据库(004)
SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分
,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数
,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分
,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数
,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分
,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数
,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分
,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数
FROM SC
;F. sql面试题 不会做 都进来看看吧
才两张表，随便写写就出来了 你是没见过该死的bom 那才伤脑筋
--先建立测试用例：
create table users(Id Int,Name varchar(20),regDate date);
alter table users add constraint users_pk primary key(Id);
create table UploadInfo(Id Int,UserId Int,FileName varchar(20),foreign key(UserId) references users(Id));
insert into users values(1,'A',sysdate-1);
insert into users values(2,'B',sysdate-2);
insert into users values(3,'C',sysdate-3);
insert into users values(4,'D',sysdate-4);
insert into users values(5,'E',sysdate-5);
select*from users;
--随机插入上传数据的存储过程
create or replace procere InsertUploadInfoData is
i int:=0;
begin
for i in 1..100 loop
insert into UploadInfo values(i,trunc(DBMS_RANDOM.value(1,5)),'FileName '||trunc(DBMS_RANDOM.value(1,1000)));
end loop;
commit;
end ;
begin
InsertUploadInfoData;
end;
select*from UploadInfo;
--开始了
--第一题
select UserUploadRecord.Id,UserUploadRecord.UploadCount
from
(
select users.Id,count(UploadInfo.Id) as UploadCount from users
inner join UploadInfo on users.Id=UploadInfo.UserId --内联结
where users.regDate>=sysdate-3 and users.regDate<=sysdate --三天内
group by users.Id
order by UploadCount desc
) UserUploadRecord
where rownum=1 --取第一条记录
;
--创建用户名重复的测试用例
insert into users values(6,'A',sysdate-4);
--由于有外键约束，所以需先删除子表记录
delete from UploadInfo where UploadInfo.Userid
in
(
select users.id from users where (users.name,users.regdate) in
(
select users.name,max(users.regdate) from users group by users.name having count(users.id)>1 --取重复用户名的最大注册时间
)
)
--再删除父表，做法差不多
delete from users where (users.name,users.regdate) in
(
select users.name,max(users.regdate) from users group by users.name having count(users.id)>1
)
————————————————————
建表语法 测试用例都写给你了 还说运行不了？直接复制进去就行了
G. 美团面试题：慢SQL有遇到过吗是怎么解决的
大家好，我是田维常，可以叫我老田，也可以叫我田哥
。2017年的时候，我刚去上海，朋友内悄指早推我去美团面试，之前我也写过一个一篇文章，也是在美团面试中遇到的：
美团面试题：String s = new String("111")会创建几个对象？
关于慢SQL，我和面试官扯了很久，面试官也是很谦虚的，总是点头，自己以为回答的还可以。最后的最后，还是说了“ 你先回去等通知吧！ ”。
所以，我决定把这个慢SQL技术点，好好和你分享分享。希望你下次在遇到类似的面试，能顺顺利利轻轻松松的斩获自己想要的offer。
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录MySQL中查询时间超过（大于）设置阈值（long_query_time）的语句，记录到慢查询日志中。
其中，long_query_time的默认值是10，单位是秒，也就是说默认情况下，你的SQL查询时间超过10秒就算慢SQL了。
在MySQL中，慢SQL日志默认是未开启的，也就说就算出现了慢SQL，也不会告诉你的，如果需要知道哪些SQL是慢SQL，需要我们手动开启慢SQL日志的。
关于慢SQL是否开启，我们可以通过下面这个命令来查看：
在这里插入图片描述
通过命令，我们就可以看到slow_query_log项为OFF，说明我们的慢SQL日志并未开启。另外我们也可以看到我们慢SQL日志存放于哪个目录下和日志文件名。
下面我们来开启慢SQL日志，执行下面的命令：
这里需要注意，这里开启的是我们当前的数据库，并且，我们重启数据库后会失效的。
开启慢SQL日志后，再次查看：
slow_query_log项已经变成ON，说明开启成功。
上面说过慢SQL默认时间是10秒，我们通过下面的命令就可以看到我们慢SQL的默认时间：
在这里插入图片描述
我们总不能一直使用这个默认值，可能很多业务需要时间更短或更长，所以此时，我们就需要对默认时间进行修改，修改命令如下：
修改完了，我们再来看看是否已经改成了3秒。
这里需要注意：想要永久的生效，还需要修改MySQL下面的配置文件my.cnf 文件。
注意：不同操作系统，配置有些区别。
Linux操作系统中
Windows操作系统中
执行一条慢SQL，因为我们前面已经设置好了慢SQL时间为3秒，所以，我们只要执行一条SQL时间超过3秒即可。
该SQL耗时4.024秒，下面我们就来查看慢SQL出现了多少条。
使用命令：
找到慢SQL日志文件，打开后就会出现类似下面这样的语句；
简单说明：
切记
通常我们定位慢SQL有两种方式：
第一种：定位慢查询 SQL 可以通过两个表象进行判断
第二种：根据不同的数据库使用不同的方式获取问题 SQL
如果开启了慢SQL日志后，可能会有大量的慢SQL日志产生，此时再用肉眼看，那是不太现实的，所以大佬们就给我搞了个工具： mysqlmpslow 。
mysqlmpslow 能将相同的慢SQL归类，并统计出相同的SQL执行的次数，每次执行耗时多久、总耗时，每次返回的行数、总行数，以及客户端连接信息等。
通过命令
可以看到相关参数的说明：
比较常用的参数有这么几个：
mysqlmpslow 常用的使用逗庆方式如下：
如上一条命令，应该是mysqlmpslow最简单的一种形式，其中-s参数是以什么方式排序的意思，c指代的是以总数从大到小的方式排序。-s的常用子参数有：c: 相同查询以查询条数和从大到小排序。t: 以查询总时间的方式从大到小排序。l: 以查询锁的总时间的方式从大到小排序。at: 以查询平均时间的方式从大到小排序。al: 以查询锁平均时间的方式从大到启雀小排序。
同样的，还可以增加其他参数，实际使用的时候，按照自己的情况来。
其他常用方式：
接下，我们来个实际操作。
这其中的 SQL 语句因为涉及某些信息，所以我都用*号将主体替换了，如果希望得到具体的值，使用-a参数。
使用 mysqlmpslow 查询出来的摘要信息，包含了这些内容：
Count : 464 ：表示慢查询日志总共记录到这条sql语句执行的次数；
Time=18.35s (8515s) ：18.35s表示平均执行时间（-s at)，8515s表示总的执行时间(-s t)；
Lock=0.01s (3s) ：与上面的Time相同，第一个表示平均锁定时间(-s al)，括号内的表示总的锁定时间(-s l)（也有另一种说法，说是表示的等待锁释放的时间）；
Rows=90884.0 (42170176) : 第一个值表示扫描的平均行数(-s ar)，括号内的值表示扫描的总行数(-s r)。
是不是
so easy!!!!