共计 6144 个字符,预计需要花费 16 分钟才能阅读完成。
1. 如果匹配上,将 DictB 的 Desc 更新成 DictA 中对应的字段 Desc
首先创建两张表格:
CREATE TABLE DictA(id1 number, desc1 varchar2(10));
CREATE TABLE DictB(id1 number, desc1 varchar2(10));
INSERT INTO DictA VALUES (1001, '基金');
INSERT INTO DictA VALUES (2001, '保险');
INSERT INTO DictA VALUES (3001, '证券');
INSERT INTO DictA VALUES (3002, '信托');
INSERT INTO DictB VALUES (1002, '证券');
INSERT INTO DictB VALUES (3001, '银行');
COMMIT;
解题思路:
如果 ID 匹配得到,则修改 (如果 ID 匹配不到,则插入),类似于增量同步;
又可以用 ID 和 Desc 合并判断。
MERGE INTO DICTB B
USING (SELECT * FROM DICTA) A
ON (B.ID1 = A.ID1)
WHEN MATCHED THEN
UPDATE SET B.DESC1 = A.DESC1;
注意事项:
--ORA.00903 表名无效,源表要加() 括号
--ORA.00969 缺失 ON 关键字,ON 条件也要加上 () 括号
2. 显示如右图所示,及格分数为 60;
首先创建表格:
CREATE TABLE COURSE(COURSEID NUMBER,COUSERNAME VARCHAR2(10),SCORE NUMBER);
INSERT INTO COURSE VALUES (1, 'java',70);
INSERT INTO COURSE VALUES (2, 'Servlet',60);
INSERT INTO COURSE VALUES (3, 'Oracle',90);
INSERT INTO COURSE VALUES (4, 'xml',40);
INSERT INTO COURSE VALUES (5, 'jsp',50);
INSERT INTO COURSE VALUES (6, 'Chinese',80);
COMMIT;
解题思路:
Case When 对 Score 多条件判断,并添加多新一列重命名 Mark。
SELECT C.*,
CASE WHEN SCORE < 60 THEN 'FAIL'
ELSE 'PASS' END AS MARK
FROM COURSE;
注意事项:
ORA.00923 未找到要求的 From 关键字 - 不能直接用 * 返回所有子段
数据库在插入新列 Mark 后,无法辨认。因此对表起别名,然后用别名.*
3. 将表格中的女生全部迁移到表 B 上。
首先创建表格:
CREATE TABLE T_CLASSA(NAME VARCHAR2(10),GENDER VARCHAR2(2),STUNUM VARCHAR2(10));
insert into T_CLASSA values('李犇牪', '男','C20160001');
insert into T_CLASSA values('张叒?', '男','C20160002');
insert into T_CLASSA values('王森林', '男', 'C20160003');
insert into T_CLASSA values('杜歘欻', '男', 'C20160004');
insert into T_CLASSA values('刘歮歧', '男', 'C20160005');
insert into T_CLASSA values('秦? 爻', '男', 'C20160007');
insert into T_CLASSA values('冯? 厽', '男', 'C20160009');
insert into T_CLASSA values('聂巜巛', '男', 'C20160011');
insert into T_CLASSA values('金椽?', '男', 'C20160013');
insert into T_CLASSA values('胡昍晶', '男', 'C20160014');
insert into T_CLASSA values('李蕾', '女', 'C20160015');
分析思路:
插入一张临时表,并将源表中的 Gender 筛选出来等于 ’ 女 ’
INSERT INTO T_CLASSB SELECT * FROM T_CLASSA WHERE GENDER ='女';
具体链接可参考:【PL/SQL】- 备份表思路 Create table 和 Insert Into
4. 将以下格式转化为右图 D_Day 所示:
Select To_Date(c_Day,'yyyy/mm/dd') As d_Day From Tmonth;
5. 注意匹配不到的数据,则转换为为未知。
创建两张表:
CREATE TABLE T_COURSE
(SID VARCHAR(4),
CourseID NUMBER(2)
);
CREATE TABLE T_NAME
(COURSEID NUMBER(4),
CNAME VARCHAR2(4)
);
INSERT INTO T_COURSE VALUES('1001',1);
INSERT INTO T_COURSE VALUES('1001',2);
INSERT INTO T_COURSE VALUES('1001',3);
INSERT INTO T_COURSE VALUES('1001',4);
INSERT INTO T_COURSE VALUES('1002',1);
INSERT INTO T_COURSE VALUES('1002',2);
INSERT INTO T_COURSE VALUES('1002',3);
INSERT INTO T_NAME VALUES(1,'物理');
INSERT INTO T_NAME VALUES(2,'生物');
INSERT INTO T_NAME VALUES(3,'化学');
SELECT * FROM T_COURSE;
SELECT * FROM T_NAME;
分析思路:
1.SID 和 COURSE 分别出自两张不同的表, 而且以 SID 所在的表为主表全面返回,因此用左连接。
2. 如果左连接后,因为没有对应的 CourseID = 4 的值出现在课表上,则会出现空值,用空值转换行数 Nvl, 替换成未知。
3. 空值属性,不参与聚合逻辑运算,而且排序最大。最后不要忘记添加 Order by, 对 SID 课程号进行升序排序。
SELECT T1.SID,NVL(T2.CNAME,'未知') FROM T_COURSE T1
LEFT JOIN T_NAME T2
ON T1.COURSEID = T2.COURSEID
ORDER BY T1.SID;
6. 查询最近一次的通话记录
创建表格:
CREATE TABLE T_CONTACT
(日期 VARCHAR2(10),
姓名 VARCHAR2(4),
通话记录 NUMBER(10)
);
INSERT INTO T_CONTACT VALUES('2015/07/23','张三',11111111);
INSERT INTO T_CONTACT VALUES('2015/07/24','张三',22222222);
INSERT INTO T_CONTACT VALUES('2015/07/25','张三',33333333);
INSERT INTO T_CONTACT VALUES('2015/07/23','李四',11111111);
INSERT INTO T_CONTACT VALUES('2015/07/24','李四',22222222);
INSERT INTO T_CONTACT VALUES('2015/07/25','李四',33333333);
INSERT INTO T_CONTACT VALUES('2015/07/24','王五',22222222);
INSERT INTO T_CONTACT VALUES('2015/07/25','王五',11111111);
分析思路:
看目标表只有一个日期字段,细分到每日。
则有两种方法,一用分析函数排序,取每个日期第一名。
二对日期进行最大值 Max 判断。
--- 方法一:用分析函数
SELECT * FROM
(
SELECT T.*,
ROW_NUMBER()OVER(PARTITION BY 姓名 ORDER BY 日期) AS RK
FROM T_CONTACT T
) WHERE RK =1;
--- 方法二:套用子查询,取最大值
SELECT * FROM T_CONTACT WHERE 日期 IN
(SELECT MAX(日期) FROM T_CONTACT GROUP BY 姓名
);
7. 找出重复的 Name
分析思路:
按名字分组,分组后的数值如果大于 1 的,则代表重复;
Select name from emp group by name having count(1)>1;
8. 查找不带 J 的员工姓名
分析思路:
1. 用 INSTR 进行模糊查询,然后进行判断
2. 不要忘记还有 NULL 的判断,
-- 模糊查询 Like
Select name from emp\_test where name
not like '%J%' or name is null;
-- Instr 查询字符串,字符是否存在?Select name from emp\_test WHERE
INSTR(NAME,'J',1,1)=0 or name is null;
9. 查询这个班里面的人数。
每组的最高分,最低,平均分。并按照平均分降序。
分析思路:
1. 统计每个班的人数,首要进行 Group By 班级
2. 最高,最低,平均分都是聚合函数,通常都要结合 Group by 使用
3. 按平均分排序,Order by Desc
4. 每个新的指标创建新的一列
SELECT C_CLASS, COUNT(1) AS 人数, MAX(SCORE) AS 最高分, MIN(SCORE) AS 最低分, AVG(SCORE) AS 平均分 FROM TSCORE
GROUP BY C_CLASS
ORDER BY 平均分 DESC;
10. 创建视图,计算环比,同比
分析思路:
1. 创建视图, 固定句式:Create or Replace view view_name AS ();
2. 同比环比, 用位移分析函数
CREATE OR REPLACE VIEW v_sale AS
SELECT MONTHS,sell,
LAG(sell,12) Over (Order By Months) AS lastyear, -- 同比
LAG(sell,1) Over (Order By Months) AS lastmonth -- 环比
FROM Sale;
11. 左图作为源表,生成右图
分析思路:
1.Sell 销售额根据 Seson 字段分类汇总 Group By
2.Seson 字段由几个字符串组成,Concat 或者 || 连接
3.To_char 函数范围季度
创建表格:
CREATE TABLE T_SEASON
(MONTH VARCHAR2(10),
SELL NUMBER(4)
);
INSERT INTO T_SEASON VALUES('201501',1000);
INSERT INTO T_SEASON VALUES('201502',1100);
INSERT INTO T_SEASON VALUES('201503',1300);
INSERT INTO T_SEASON VALUES('201504',1000);
INSERT INTO T_SEASON VALUES('201505',1000);
INSERT INTO T_SEASON VALUES('201506',1000);
INSERT INTO T_SEASON VALUES('201507',1000);
INSERT INTO T_SEASON VALUES('201508',4000);
INSERT INTO T_SEASON VALUES('201509',1000);
INSERT INTO T_SEASON VALUES('201510',3000);
INSERT INTO T_SEASON VALUES('201511',1000);
INSERT INTO T_SEASON VALUES('201512',2200);
INSERT INTO T_SEASON VALUES('201601',1700);
SELECT * FROM T_SEASON
解题答案如下:
SELECT SUBSTR(T.MONTH,1,4)||'Q'||TO_CHAR(TO_DATE(T.MONTH,'yyyy/mm'),'Q') AS SEASON,SUM(SELL) FROM
T_SEASON T
GROUP BY SUBSTR(T.MONTH,1,4)||'Q'||TO_CHAR(TO_DATE(T.MONTH,'yyyy/mm'),'Q')
注意事项:
1.Substr 函数(字符, 从第几位起, 截取长度)
2. 有共同的字符连接,可考虑用 || 连接;3. 字符首先转为日期模式,才能用 To_char 的第二个参数 Q,返回当前季度。4.Group by 不能添加组函数(聚合)
12. 更新成如右图所示
分析思路:
1. 表名一致,同在一张表操作,只对 Name 字段更新。用 Update Set 语句。
2.Name 等于 FirstName 加 空格 加 LastName。
Update ClassaA Set Name=Firstname||' '||lastname;
13. 如下图所示,新增一列得出右图结果
分析思路:
1. 用 Case When 对 Age 多条件判断
2. 逻辑结构如同第 2 题
Select Id,Name,Age,
(Case When Age<=20 Then '20-'
When Age<= 30 Then '21 to 30'
When Age<= 40 Then '31 to 40'
When Age<= 50 Then '41 to 50'
Else '50+' End) AS AgePara
From Age;
14. 类似于数据清洗环节,直接将 Name 里面的空格替换移除。
分析思路:
1. 用 Replace 替代函数, 将空格替换为 ”。
Select Id,Replace(Name,'','') As Name From Name;
15,Using the SQL statement, you are going to create a function F_MAX(PI_VAR1 IN NUMBER, PI_VAR2 IN NUMBER), with 2 input parameter which are PI_VAR1 and PI_VAR2. If the value of PI_VAR1 is larger than PI_VAR2, the value of PI_VAR1 is returned, otherwise the value of PI_VAR2 is returned
意思是:写一个自定义函数,实现输入两个数,并进行判断。
首先我们要写一个自定义函数模型。
CREATE OR REPLACE FUNCTION FUNCTION_NAME(PI_VAR1 IN NUMBER,
PI_VAR2 IN NUMBER)
RETURN NUMBER -- 函数的返回类型
IS
BEGIN
-- 判断两个值谁打谁小
END;
解题答案:
CREATE OR REPLACE FUNCTION F_MAX(PI_VAR1 IN NUMBER,
PI_VAR2 IN NUMBER)
RETURN NUMBER -- 函数的返回类型
IS
BEGIN
IF PI_VAR1>PI_VAR2 THEN
RETURN PI_VAR1;
ELSE
RETURN PI_VAR2;
END IF;
END;