公司SQL考核及小结（Oracle）

一.数据库初始化脚本：

Create TABLE HAND_CUSTOMERS
(
 CUSTOMERS_NO Varchar2(10),
 CUSTOMERS_NAME Varchar2(30),
 CUSTOMERS_GENDER Varchar2(3),
 CUSTOMERS_BIRTH_DATE Date
);
comment on TABLE HAND_CUSTOMERS is '顾客表';
comment on COLUMN HAND_CUSTOMERS.CUSTOMERS_NO is '客户编号';
comment on COLUMN HAND_CUSTOMERS.CUSTOMERS_NAME is '客户名称';
comment on COLUMN HAND_CUSTOMERS.CUSTOMERS_GENDER is '客户性别';
comment on COLUMN HAND_CUSTOMERS.CUSTOMERS_BIRTH_DATE is '客户出生日期';

 
Create TABLE HAND_SELLERS
(
 SELLER_NO Varchar2(10),
 SELLER_NAME Varchar2(30),
 MANAGER_NO  Varchar2(10)
);
comment on TABLE HAND_SELLERS is '销售员表';
comment on COLUMN HAND_SELLERS.SELLER_NO is '销售员编码';
comment on COLUMN HAND_SELLERS.SELLER_NAME is '销售员名称';
comment on COLUMN HAND_SELLERS.MANAGER_NO  is '销售员经理'; 


---
 
Create Table HAND_GOODS
(
 GOODS_NO Varchar2(10),
 GOODS_NAME Varchar2(30),
 GOODS_PRICE Number
);
comment on table HAND_GOODS is '商品表';
comment on column HAND_GOODS.GOODS_NO is '商品编码';
comment on column HAND_GOODS.GOODS_NAME is '商品名称';
comment on column HAND_GOODS.GOODS_PRICE is '商品单价';

----
Create Table HAND_SALES_RECORDS
(
 CUSTOMERS_NO Varchar2(10),
 SELLER_NO Varchar2(10),
 GOODS_NO Varchar2(10),
 SALES_QUANTY Number,
 SALES_DATE Date
);
comment on table HAND_SALES_RECORDS is '销售记录表';
comment on column HAND_SALES_RECORDS.CUSTOMERS_NO is '客户编号';
comment on column HAND_SALES_RECORDS.SELLER_NO is '销售员编码';
comment on column HAND_SALES_RECORDS.GOODS_NO is '商品编码';
comment on column HAND_SALES_RECORDS.SALES_QUANTY is '销售数量';
comment on column HAND_SALES_RECORDS.SALES_DATE is '销售记录';
  




------
INSERT INTO HAND_CUSTOMERS(CUSTOMERS_NO, CUSTOMERS_NAME, CUSTOMERS_GENDER, CUSTOMERS_BIRTH_DATE) values 
('C001', '张三', '男', TO_DATE('1990/1/1','YYYY/MM/DD'));
INSERT INTO HAND_CUSTOMERS(CUSTOMERS_NO, CUSTOMERS_NAME, CUSTOMERS_GENDER, CUSTOMERS_BIRTH_DATE) values 
('C002', '李四', '男', TO_DATE('1994/3/2','YYYY-MM-DD'));
INSERT INTO HAND_CUSTOMERS(CUSTOMERS_NO, CUSTOMERS_NAME, CUSTOMERS_GENDER, CUSTOMERS_BIRTH_DATE) values 
('C003', '吴鹏', '男', TO_DATE('1996/2/19','YYYY-MM-DD'));
INSERT INTO HAND_CUSTOMERS(CUSTOMERS_NO, CUSTOMERS_NAME, CUSTOMERS_GENDER, CUSTOMERS_BIRTH_DATE) values 
('C004', '琴沁', '女', TO_DATE('1997/1/4','YYYY-MM-DD'));
INSERT INTO HAND_CUSTOMERS(CUSTOMERS_NO, CUSTOMERS_NAME, CUSTOMERS_GENDER, CUSTOMERS_BIRTH_DATE) values 
('C005', '王丽', '女', TO_DATE('1998/1/5','YYYY-MM-DD'));
INSERT INTO HAND_CUSTOMERS(CUSTOMERS_NO, CUSTOMERS_NAME, CUSTOMERS_GENDER, CUSTOMERS_BIRTH_DATE) values 
('C006', '李波', '男', TO_DATE('1998/4/6','YYYY-MM-DD'));
INSERT INTO HAND_CUSTOMERS(CUSTOMERS_NO, CUSTOMERS_NAME, CUSTOMERS_GENDER, CUSTOMERS_BIRTH_DATE) values 
('C007', '刘玉', '女', TO_DATE('1998/7/7','YYYY-MM-DD'));
INSERT INTO HAND_CUSTOMERS(CUSTOMERS_NO, CUSTOMERS_NAME, CUSTOMERS_GENDER, CUSTOMERS_BIRTH_DATE) values 
('C008', '萧蓉', '男', TO_DATE('1998/8/21','YYYY-MM-DD'));
INSERT INTO HAND_CUSTOMERS(CUSTOMERS_NO, CUSTOMERS_NAME, CUSTOMERS_GENDER, CUSTOMERS_BIRTH_DATE) values 
('C009', '陈萧晓', '女', TO_DATE('1994/12/1','YYYY-MM-DD'));
INSERT INTO HAND_CUSTOMERS(CUSTOMERS_NO, CUSTOMERS_NAME, CUSTOMERS_GENDER, CUSTOMERS_BIRTH_DATE) values 
('C010', '陈美', '女', TO_DATE('1999/10/10','YYYY-MM-DD'));

------
INSERT INTO HAND_SELLERS(SELLER_NO, SELLER_NAME, MANAGER_NO ) values 
('X001', '销售A', '');
INSERT INTO HAND_SELLERS(SELLER_NO, SELLER_NAME, MANAGER_NO ) values 
('X002', '销售B', 'X001');
INSERT INTO HAND_SELLERS(SELLER_NO, SELLER_NAME, MANAGER_NO ) values 
('X003', '销售C', 'X001');
INSERT INTO HAND_SELLERS(SELLER_NO, SELLER_NAME, MANAGER_NO ) values 
('X004', '销售D', 'X003');
INSERT INTO HAND_SELLERS(SELLER_NO, SELLER_NAME, MANAGER_NO ) values 
('X005', '销售E', 'X003');
-------
INSERT INTO HAND_GOODS(GOODS_NO, GOODS_NAME, GOODS_PRICE) values 
('GOODS001', '商品A', 120);
INSERT INTO HAND_GOODS(GOODS_NO, GOODS_NAME, GOODS_PRICE) values 
('GOODS002', '商品B', 159);
INSERT INTO HAND_GOODS(GOODS_NO, GOODS_NAME, GOODS_PRICE) values 
('GOODS003', '商品C', 349);
INSERT INTO HAND_GOODS(GOODS_NO, GOODS_NAME, GOODS_PRICE) values 
('GOODS004', '商品D', 256);
INSERT INTO HAND_GOODS(GOODS_NO, GOODS_NAME, GOODS_PRICE) values 
('GOODS005', '商品E', 412);
INSERT INTO HAND_GOODS(GOODS_NO, GOODS_NAME, GOODS_PRICE) values 
('GOODS006', '商品F', 342);
INSERT INTO HAND_GOODS(GOODS_NO, GOODS_NAME, GOODS_PRICE) values 
('GOODS007', '商品G', 234);
INSERT INTO HAND_GOODS(GOODS_NO, GOODS_NAME, GOODS_PRICE) values 
('GOODS008', '商品H', 776);
INSERT INTO HAND_GOODS(GOODS_NO, GOODS_NAME, GOODS_PRICE) values 
('GOODS009', '商品I', 123);
INSERT INTO HAND_GOODS(GOODS_NO, GOODS_NAME, GOODS_PRICE) values 
('GOODS010', '商品J', null);
---
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C001', 'X001', 'GOODS001', 32, TO_DATE('2019/6/8','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C002', 'X002', 'GOODS001', 39, TO_DATE('2019/6/18','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C002', 'X003', 'GOODS003', 20, TO_DATE('2019/6/19','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C004', 'X004', 'GOODS004', 4, TO_DATE('2019/6/11','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C005', 'X005', 'GOODS005', 60, TO_DATE('2019/6/12','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C006', 'X003', 'GOODS006', 30, TO_DATE('2019/6/13','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C006', 'X002', 'GOODS007', 36, TO_DATE('2019/6/14','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C004', 'X001', 'GOODS006', 40, TO_DATE('2019/6/15','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C001', 'X003', 'GOODS001', 10, TO_DATE('2019/6/19','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C002', 'X002', 'GOODS002', 41, TO_DATE('2019/4/11','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C003', 'X003', 'GOODS003', 30, TO_DATE('2019/3/12','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C003', 'X003', 'GOODS004', 60, TO_DATE('2019/6/11','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C003', 'X002', 'GOODS005', 76, TO_DATE('2019/6/19','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C003', 'X001', 'GOODS006', 68, TO_DATE('2019/6/25','YYYY-MM-DD'));

INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C004', 'X005', 'GOODS005', 32, TO_DATE('2018/6/18','YYYY-MM-DD'));

COMMIT;

View Code

二.Sql语句题：

　　1.条件：查询没有卖出过“商品B”的销售人员信息显示：销售员编码，销售员名称，管理员编码，管理员名称：

--1
SELECT hs.seller_no
,hs.seller_name
,hsm.seller_no
,hsm.seller_name
FROM hand_sellers hs
,hand_sellers hsm
WHERE hs.manager_no = hsm.seller_no(+)
AND NOT EXISTS (SELECT 1
FROM hand_sales_records hsr
,hand_goods hg
WHERE hsr.goods_no = hg.goods_no
AND hg.goods_name = '商品B'
AND hsr.seller_no = hs.seller_no);
--2
SELECT hs.seller_no
,hs.seller_name
,hsm.seller_no
,hsm.seller_name
FROM hand_sellers hs
,hand_sellers hsm
WHERE hs.manager_no = hsm.seller_no(+)
AND hs.seller_no NOT IN (SELECT hsr.seller_no
FROM hand_sales_records hsr
,hand_goods hg
WHERE hsr.goods_no = hg.goods_no
AND hg.goods_name = '商品B');
--考点：外连接、NOT EXISTS 和 NOT IN 的用法及区别

　　　　小结：

　　　　　　1.in和exists：

　　　　　　　　in是把外表和内表作hash连接（会用到外表上的索引），而exists是对外表作loop循环（用到内表上的索引），每次loop循环再对内表进行查询，如果查询的两个表大小相当，那么用in和exists差别不大；如果两个表中一个较小一个较大，则子查询表大的用exists，子查询表小的用in；

　　　　　　2.not in和not exists:

　　　　　　　　not in 逻辑上不完全等同于not exists。使用not in时，如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录。如果子查询字段有非空限制，这时可以使用not in，并且可以通过提示让它用hasg_aj或merge_aj连接。如果查询语句使用了not in，那么对内外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in 要快。

　　　　　　参照：https://blog.csdn.net/baidu_37107022/article/details/77278381

　　2.条件：查询6 月中旬，所有顾客的消费情况。显示：顾客编号、顾客名称、顾客性别、销售商品、销售时间、销售数量排序：消费数量从高到低。说明：如果没有消费记录，销售商品、销售时间、销售数量留空

--1
SELECT hc.customers_no
,hc.customers_name
,hc.customers_gender
,hg.goods_name
,hsr.sales_date
,hsr.sales_quanty
FROM hand_sales_records hsr
,hand_goods hg
,hand_customers hc
WHERE hsr.goods_no = hg.goods_no(+)
AND hsr.customers_no(+) = hc.customers_no
--AND hsr.sales_date(+) BETWEEN to_date('2019-06-10', 'yyyy-mm-dd') AND
to_date('2019-06-20', 'yyyy-mm-dd')
AND to_char(hsr.sales_date(+), 'MM') = '06'
AND to_char(hsr.sales_date(+), 'DD') BETWEEN 10 AND 20
ORDER BY hsr.sales_quanty DESC;
--2
SELECT hc.customers_no
,hc.customers_name
,hc.customers_gender
,hg.goods_name
,hsr.sales_date
,hsr.sales_quanty
FROM hand_sales_records hsr
,hand_goods hg
,hand_customers hc
WHERE hsr.goods_no = hg.goods_no
AND hsr.customers_no = hc.customers_no
AND hsr.sales_date BETWEEN to_date('2019-06-10', 'yyyy-mm-dd') AND
to_date('2019-06-20', 'yyyy-mm-dd')
ORDER BY hsr.sales_quanty DESC;
--考点：外连接，日期函数

　　　　小结：

　　　　　 1.查两表关联列相等的数据用内连接。
　　　　　　2.Col_L是Col_R的子集时用右外连接。
　　　　　　3.Col_R是Col_L的子集时用左外连接。
　　　　　　4.Col_R和Col_L彼此有交集但彼此互不为子集时候用全外。
　　　　　　5.求差操作的时候用联合查询。

　　3.条件：查询购买记录3条以上的顾客信息。显示：顾客编号、顾客名称、顾客出生日期 :

--1
SELECT hc.customers_no
,hc.customers_name
,hc.customers_birth_date
FROM hand_customers hc
WHERE hc.customers_no IN (SELECT hsr.customers_no
FROM hand_sales_records hsr
GROUP BY hsr.customers_no HAVING COUNT(1) > 3);
--2
SELECT hc.customers_no
,hc.customers_name
,hc.customers_birth_date
FROM hand_customers hc
WHERE EXISTS (SELECT 1
FROM hand_sales_records hsr
WHERE hc.customers_no = hsr.customers_no
GROUP BY hsr.customers_no
HAVING COUNT(1) > 3);

　　　　小结：　　

　　　　　　where是筛选行，having是筛选已经查询出来的字段。

　　4.条件：查询顾客中姓氏为"张""李""刘"的顾客信息显示：顾客编号、顾客名称、顾客，出生日期。排序：顾客出生日期从近到远。说明：正则表达式。除正则表达式以外的其他方式：

--正则
SELECT hc.customers_no
,hc.customers_name
,hc.customers_birth_date
FROM hand_customers hc
WHERE regexp_like(hc.customers_name, '^张.*|^李.*|^刘.*');
--Like
SELECT hc.customers_no
,hc.customers_name
,hc.customers_birth_date
FROM hand_customers hc
WHERE (hc.customers_name LIKE '张%' OR hc.customers_name LIKE '李%' OR
hc.customers_name LIKE '刘%');

　　　　小结：

　　　　　　还可以使用substring()等函数提取第一个字符看是否在要查找的条件里面

　　5.条件：查询商品信息，当商品价格在100-199 时打9 折，在200-299 时打8 折，在300 以上打7 折。显示:商品编码、商品名称、商品打折前单价、商品打折后单价:

SELECT hg.goods_no
,hg.goods_name
,hg.goods_price
,CASE
WHEN hg.goods_price BETWEEN 100 AND 199 THEN
hg.goods_price * 0.9
WHEN hg.goods_price BETWEEN 200 AND 299 THEN
hg.goods_price * 0.8
WHEN hg.goods_price >= 300 THEN
hg.goods_price * 0.7
ELSE
hg.goods_price
END changed_goods_price
FROM hand_goods hg

　　6.查询生日在当前月份的顾客信息。显示：顾客名称，顾客生日，顾客购买总数量。排序：顾客生日：

SELECT hc.customers_no
,hc.customers_name
,hc.customers_birth_date
,(select sum(hsr.sales_quanty) from hand_sales_records hsr where
hsr.customers_no = hc.customers_no )
FROM hand_customers hc
WHERE to_char(hc.customers_birth_date,'MM') =to_char(sysdate,'MM') ;
--考点： 日期函数，子查询

　　　　小结：

　　　　　　　　就考察了子查询，以及to_char函数转换为字符串的函数，注意对应的to_date将字符串转换为日期

　　7.条件：查询商品单价在100 到199 之间的商品信息。显示：商品名称、单价。说明：单价为空的商品也进行显示：

--1
SELECT hg.goods_no
,hg.goods_name
,hg.goods_price
FROM hand_goods hg
where nvl( hg.goods_price,100) BETWEEN 100 AND 199;
--2
SELECT hg.goods_no
,hg.goods_name
,hg.goods_price
FROM hand_goods hg
WHERE hg.goods_price BETWEEN 100 AND 199
OR hg.goods_price IS NULL

　　　　小结：

　　　　　　就考察了between..and的用法（包括前面，不包括后面），以及nvl()函数，注意nvl(expr1,expr2)和nvl2(expr1,expr2,expr3)的区别

　　8.条件：查询所有销售员的销售总数量。显示：销售员编码、销售员名称、销售数量、销售名次。排序：按照销售数量进行倒序。说明：如果有销售数量相同的，销售名次如下输出。销售汇总数量60，60,57 名次 1,1,3：

SELECT hs.seller_no
,hs.seller_name
,SUM(sr.sales_quanty) sales_quanty
,RANK() OVER(ORDER BY SUM(sr.sales_quanty) DESC) rank_level
FROM hand_sales_records sr
,hand_sellers hs
WHERE sr.seller_no(+) = hs.seller_no
GROUP BY hs.seller_no
,hs.seller_name

　　　　小结：

　　　　　　注意开窗函数【分析函数提供一系列比较高级的SQL功能。分析函数时建立在数据窗口（over在一定的数据库范围进行数据分析），在一定的数据范围进行排序、汇总】over(partition by...order by...)其中partition by表示以什么分组，如果没有则使用group by的分组，rank()【用于返回结果集的分区内每行的排名，行的排名是相关行之前的排名数加一，如果排序的标准相同，则排名也相同】和dense_rank()【与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续】,row_number()【为查询出来的每一行记录生成一个序号，依次排序且不会重复】。还值得注意的是MySQL应该是在8.0之后才有开窗函数（查询资料），可以使用自连接比较数量排序。

　　9.条件：查询销售数量高于商品“商品A”的最大销售数量的所有销售记录。显示：商品名称、销售员名称、顾客名称、销售数量、销售日期。 with 不计入（with 和子查询算一种方法）：

--sn1
SELECT g.goods_name
,hs.seller_name
,c.customers_name
,sr.sales_quanty
,sr.sales_date
FROM hand_goods g
,hand_sales_records sr
,hand_sellers hs
,hand_customers c
WHERE 1 = 1
AND sr.seller_no = hs.seller_no(+)
AND sr.goods_no = g.goods_no(+)
AND sr.customers_no = c.customers_no
AND sr.sales_quanty > (SELECT MAX(sr1.sales_quanty)
FROM hand_sales_records sr1
,hand_goods g1
WHERE sr1.goods_no = g1.goods_no
AND g1.goods_name = '商品A');
--sn2
SELECT g.goods_name
,hs.seller_name
,c.customers_name
,sr.sales_quanty
,sr.sales_date
FROM hand_goods g
,hand_sales_records sr
,hand_sellers hs
,hand_customers c
WHERE 1 = 1
AND sr.seller_no = hs.seller_no(+)
AND sr.goods_no = g.goods_no(+)
AND sr.customers_no = c.customers_no
AND sr.sales_quanty > ALL (SELECT sr1.sales_quanty
FROM hand_sales_records sr1
,hand_goods g1
WHERE sr1.goods_no = g1.goods_no
AND g1.goods_name = '商品A');
--sn3
SELECT g.goods_name
,hs.seller_name
,c.customers_name
,sr.sales_quanty
,sr.sales_date
FROM hand_goods g
,hand_sales_records sr
,hand_sellers hs
,hand_customers c
WHERE 1 = 1
AND sr.seller_no = hs.seller_no(+)
AND sr.goods_no = g.goods_no(+)
AND sr.customers_no = c.customers_no
AND EXISTS (SELECT 1
FROM hand_sales_records sr1
,hand_goods g1
WHERE sr1.goods_no = g1.goods_no
AND g1.goods_name = '商品A'
AND sr.sales_quanty > sr1.sales_quanty);

　　　　小结：

　　　　　　考核外连接语法，Oracle使用+可以简化外连接SQL（left/right join），主要是找准关系，需要返回哪边的所有行，然后就是ALL（注意all和any的区别，如题all是大于查询出的所有，即比结果中的最大的还大，而any只要是其中任意一个就行），EXISTS（exists和in的区别，exists是遍历外表在查询，而in1是遍历查询出的内表结果）的用法。

　　10.条件：分别根据商品+销售员+顾客、商品+销售员、销售员、总销售数量的维度俩统计销售数量。显示：商品、销售员、顾客、汇总销售数量：

SELECT g.goods_name
,hs.seller_name
,c.customers_name
,SUM(sr.sales_quanty)
FROM hand_goods g
,hand_sales_records sr
,hand_sellers hs
,hand_customers c
WHERE 1 = 1
AND sr.seller_no = hs.seller_no(+)
AND sr.goods_no = g.goods_no(+)
AND sr.customers_no = c.customers_no
GROUP BY ROLLUP(g.goods_name, hs.seller_name, c.customers_name)

　　　　小结：

　　　　　　注ROLLUP和CUBE的区别。使用ROLLUP产生常规分组汇总行以及分组小计，Rollup 后面跟了n个字段，就将进行n+1次分组，从右到左每次减少一个字段进行分组；然后进行 union 【n+1次分组】。CUBE是后面跟了n个字段，就将进行2的N次方的分组运算，然后进行。MySQL中使用with rollup/cube。

　　11.条件：查询商品销售数量总数量为前三的商品。显示：商品编号，商品名称，销售数量。排序：按照销售数量进行排序。说明：如果商品销售数量的前4 名为 100,80,67,67 则4 条记录都显示：

SELECT g.goods_no
,g.goods_name
,t.total_quanty
FROM hand_goods g
,(SELECT sr.goods_no
,SUM(sr.sales_quanty) total_quanty
,rank() over(ORDER BY SUM(sr.sales_quanty) DESC)
rank_level
FROM hand_sales_records sr
WHERE 1 = 1
GROUP BY sr.goods_no) t
WHERE t.goods_no = g.goods_no
AND t.rank_level <= 3
ORDER BY t.total_quanty DESC

　　　　小结：

　　　　　　考察开窗函数以及rank()的使用（如题如果使用的分组的条件total_quanty，如果这个值相同，那么排名也相同）。

　　12.a.创建一个表HAND_GOODS_XXXXX 包含HAND_GOODS 表里金额大于200 的商品记录。b.将HAND_GOODS_XXXXX 的单价更新为原价的80%。c.将 HAND_GOODS 表里的记录合并至HAND_GOODS_XXXXX，通过商品编码进行匹配，如果表HAND_GOODS_XXXXX 中不存在记录，进行新建，如果存在记录则更新HAND_GOODS_XXXXX 的商品单价为HAND_GOODS 中的商品单价：

--a.
CREATE TABLE HAND_GOODS_25305 AS SELECT * FROM hand_goods g WHERE
g.goods_price > 200
--b.
UPDATE HAND_GOODS_25305 SET goods_price = goods_price * 0.8
--c.
MERGE INTO hand_goods_25305 g_new
USING hand_goods g
ON (g.goods_no = g_new.goods_no)
WHEN MATCHED THEN
UPDATE
SET g_new.goods_price = g.goods_price
WHEN NOT MATCHED THEN
INSERT
VALUES
(g.goods_no
,g.goods_name
,g.goods_price);

　　　　小结：

　　　　　　考察创建，更新表，以及批量插入。

　　13.条件：查询购买商品数量总计最多的顾客信息。显示：顾客编号，顾客名称，购买商品数量：　

WITH tab_sum AS
(SELECT sr.customers_no
,SUM(sr.sales_quanty) total_quanty
FROM hand_sales_records sr
WHERE 1 = 1
GROUP BY sr.customers_no)
SELECT t.customers_no, c.customers_name, t.total_quanty
FROM tab_sum t, hand_customers c
WHERE c.customers_no = t.customers_no AND t.total_quanty = (
SELECT MAX(total_quanty)
FROM tab_sum)

　　　　小结：

　　　　　　使用子查询问，及with方便在后面多次使用则可以简化SQL并适当提高性能。

posted @ 2019-08-11 17:18 biu嘟阅读(656) 评论(0) 编辑收藏举报

刷新页面返回顶部

biu嘟

公司SQL考核及小结（Oracle）

一.数据库初始化脚本：

二.Sql语句题：

1.条件：查询没有卖出过“商品B”的销售人员信息 显示：销售员编码，销售员名称，管理员编码，管理员名称 ：

小结：

1.in和exists：

2.not in和not exists:

2.条件：查询6 月中旬，所有顾客的消费情况。显示：顾客编号、顾客名称、顾客性别、销售商品、销售时间、销售数量排序：消费数量从高到低。说明：如果没有消费记录，销售商品、销售时间、销售数量留空

小结：

3.条件：查询购买记录3条以上的顾客信息。 显示：顾客编号、顾客名称、顾客出生日期 :

小结：

4.条件：查询顾客中姓氏为"张""李""刘"的顾客信息显示：顾客编号、顾客名称、顾客，出生日期。排序：顾客出生日期从近到远。说明：正则表达式。除正则表达式以外的其他方式：

5.条件：查询商品信息，当商品价格在100-199 时 打9 折，在200-299 时打8 折，在300 以上打7 折。显示:商品编码、商品名称、商品打折前单价、商品打折后单价:

6.查询生日在当前月份的顾客信息。显示：顾客名称，顾客生日，顾客购买总数量。排序：顾客生日：

7.条件：查询商品单价在100 到199 之间的商品信息。显示：商品名称、单价。说明：单价为空的商品也进行显示：

8.条件：查询所有销售员的销售总数量。显示：销售员编码、销售员名称、销售数量、销售名次。排序：按照销售数量进行倒序。说明：如果有销售数量相同的，销售名次如下输出。销售汇总数量60，60,57 名次 1,1,3：

9.条件：查询销售数量高于商品“商品A”的最大销售数量的所有销售记录。显示： 商品名称、销售员名称、顾客名称、销售数量、销售日期。 with 不计入（with 和子查询算一种方法）：

10.条件：分别根据 商品+销售员+顾客、商品+销售员 、销售员、总销售数量的维度俩统计销售数量。显示：商品、销售员、顾客、汇总销售数量：

11.条件：查询商品销售数量总数量为前三的商品。显示：商品编号，商品名称，销售数量。排序：按照销售数量进行排序。说明：如果商品销售数量的前4 名为 100,80,67,67 则4 条记录都显示：

13.条件：查询购买商品数量总计最多的顾客信息。显示：顾客编号，顾客名称，购买商品数量：

公告

　　1.条件：查询没有卖出过“商品B”的销售人员信息显示：销售员编码，销售员名称，管理员编码，管理员名称：

　　　　小结：

　　　　　　1.in和exists：

　　　　　　2.not in和not exists:

　　2.条件：查询6 月中旬，所有顾客的消费情况。显示：顾客编号、顾客名称、顾客性别、销售商品、销售时间、销售数量排序：消费数量从高到低。说明：如果没有消费记录，销售商品、销售时间、销售数量留空

　　　　小结：

　　3.条件：查询购买记录3条以上的顾客信息。显示：顾客编号、顾客名称、顾客出生日期 :

　　　　小结：　　

　　4.条件：查询顾客中姓氏为"张""李""刘"的顾客信息显示：顾客编号、顾客名称、顾客，出生日期。排序：顾客出生日期从近到远。说明：正则表达式。除正则表达式以外的其他方式：

　　5.条件：查询商品信息，当商品价格在100-199 时打9 折，在200-299 时打8 折，在300 以上打7 折。显示:商品编码、商品名称、商品打折前单价、商品打折后单价:

　　6.查询生日在当前月份的顾客信息。显示：顾客名称，顾客生日，顾客购买总数量。排序：顾客生日：

　　7.条件：查询商品单价在100 到199 之间的商品信息。显示：商品名称、单价。说明：单价为空的商品也进行显示：

　　8.条件：查询所有销售员的销售总数量。显示：销售员编码、销售员名称、销售数量、销售名次。排序：按照销售数量进行倒序。说明：如果有销售数量相同的，销售名次如下输出。销售汇总数量60，60,57 名次 1,1,3：

　　9.条件：查询销售数量高于商品“商品A”的最大销售数量的所有销售记录。显示：商品名称、销售员名称、顾客名称、销售数量、销售日期。 with 不计入（with 和子查询算一种方法）：

　　10.条件：分别根据商品+销售员+顾客、商品+销售员、销售员、总销售数量的维度俩统计销售数量。显示：商品、销售员、顾客、汇总销售数量：

　　11.条件：查询商品销售数量总数量为前三的商品。显示：商品编号，商品名称，销售数量。排序：按照销售数量进行排序。说明：如果商品销售数量的前4 名为 100,80,67,67 则4 条记录都显示：

　　13.条件：查询购买商品数量总计最多的顾客信息。显示：顾客编号，顾客名称，购买商品数量：　