高分求教外企SQL笔试题目

Good表：
Number     Name            Price   Category
1          Banana           3          food
2          Apple            2          food
3          Drill           900         tool
4          Nail             1.5        tool
5          Wrench           90         tool


Customer表
Number           Customer_Name            Address
1                    Eric                  beijing
2                    Wal                   shanghai
3                    Allen                 Tianjin
Transaction表
Number            Good_Number                Customer_Number               Good_Quality
1                      1                          1                           12
2                      3                          1                           1
3                      7                          3                           1
4                      6                          6                           7
5                      2                          6                           4

列出每人在购买生产工具和食品上各花多少钱？
列出卖出总金额最少的前3个商品名称及其卖出的金额？

18 个解决方案

#1

CREATE TABLE Good(
Numbers NUMBER(18,0),
Name VARCHAR2(20),
Price NUMBER(18,2),
Category VARCHAR2(20)
);

INSERT INTO Good(Numbers, Name, Price, Category) VALUES(1, 'Banana', 3, 'food');
INSERT INTO Good(Numbers, Name, Price, Category) VALUES(2,'Apple', 2, 'food');
INSERT INTO Good(Numbers, Name, Price, Category) VALUES(3, 'Drill', 900, 'tool');
INSERT INTO Good(Numbers, Name, Price, Category) VALUES(4, 'Nail', 1.5, 'tool');
INSERT INTO Good(Numbers, Name, Price, Category) VALUES(5, 'Wrench', 90, 'tool');

-----------------------------------------------------------------------------------
CREATE TABLE Customer(
Numbers NUMBER(18,0),
Customer_Name VARCHAR2(20),
Address VARCHAR2(100)
);

INSERT INTO Customer(Numbers, Customer_Name, Address) VALUES(1, 'Eric', 'beijing');
INSERT INTO Customer(Numbers, Customer_Name, Address) VALUES(2, 'Wal', 'shanghai');
INSERT INTO Customer(Numbers, Customer_Name, Address) VALUES(3, 'Allen', 'Tianjin');

-----------------------------------------------------------------------------------
CREATE TABLE Transaction(
Numbers NUMBER(18,0),
Good_Numbers NUMBER(18,0),
Customer_Numbers NUMBER(18,0),
Good_Quality NUMBER(18,0)
);

INSERT INTO Transaction(Numbers, Good_Numbers, Customer_Numbers, Good_Quality) VALUES(1, 1, 1, 12);
INSERT INTO Transaction(Numbers, Good_Numbers, Customer_Numbers, Good_Quality) VALUES(2, 3, 1, 1);
INSERT INTO Transaction(Numbers, Good_Numbers, Customer_Numbers, Good_Quality) VALUES(3, 7, 3, 1);
INSERT INTO Transaction(Numbers, Good_Numbers, Customer_Numbers, Good_Quality) VALUES(4, 6, 6, 7);
INSERT INTO Transaction(Numbers, Good_Numbers, Customer_Numbers, Good_Quality) VALUES(5, 2, 6, 4);

COMMIT;

#2

引用 1 楼 luoyoumou 的回复:

SQL code
CREATE TABLE Good(
Numbers NUMBER(18,0),
Name VARCHAR2(20),
Price NUMBER(18,2),
Category VARCHAR2(20)
);

INSERT INTO Good(Numbers, Name, Price, Category) VALUES(1, 'Banana', 3, 'food……

-- 楼主：你就不能有诚意点？把SQL给贴上？？

#3

-- 4 6  6 7

-- Customer_Number 6 在 Customer表中不存在!

#4

create table good(
  g_number number not null primary key,
  name varchar2(10),
  price number,
  Category varchar2(10)
);

create table customer(
  c_number number not null primary key,
  customer_name varchar2(10),
  address varchar2(10)
);

create table transaction(
  t_number number not null primary key,
  g_number number,
  c_number number,
  g_quality number,
  constraint t1_fk foreign key (g_number) references good(g_number),
  constraint t2_fk foreign key (c_number) references customer(c_number)
);


INSERT INTO Good VALUES(1, 'Banana', 3, 'food');
INSERT INTO Good VALUES(2,'Apple', 2, 'food');
INSERT INTO Good VALUES(3, 'Drill', 900, 'tool');
INSERT INTO Good VALUES(4, 'Nail', 1.5, 'tool');
INSERT INTO Good VALUES(5, 'Wrench', 90, 'tool');


insert into customer values (1,'Eric','beijing');
insert into customer values (2,'Wal','shanghai');
insert into customer values (3,'AAllen','Tianjin');


insert into transaction values (1,1,1,12);
insert into transaction values (2,3,1,1);
--不能插入insert into transaction values (3,7,3,1);
--不能插入insert into transaction values (4,6,6,7);
--不能插入insert into transaction values (5,2,6,4);

commit;

#5

-- (1) 列出每人在购买生产工具和食品上各花多少钱？
SELECT t1.Customer_Numbers as "客户ID", 
       t2.Customer_Name as "客户名称",
       -- t1.Good_Numbers as "货物ID",
       -- t3.Name as "货物名称",
       t3.Category as "货物种类",
       SUM( t3.Price * t1.Good_Quality) as "总金额"
FROM Transaction t1  join Customer t2 ON t1.Customer_Numbers = t2.Numbers
                    left join Good t3 on t1.Good_Numbers = t3.Numbers
GROUP BY t1.Customer_Numbers, t2.Customer_Name, t3.Category;

#6

-- 3  7 3 1

-- Customer表中 Number 为7 的客户不存在!

#7

--因为LZ之前的有几个数据不能插进去，我就自己加了几个如下,其它代码见#4楼
insert into transaction values (3,2,3,1);
insert into transaction values (4,5,2,7);
insert into transaction values (5,3,1,4);
insert into transaction values (6,1,1,12);
insert into transaction values (7,5,1,2);
insert into transaction values (8,4,3,2);
commit;

--列出每人在购买生产工具和食品上各花多少钱？

select distinct( t.c_number), g.category, 
sum(G_Quality*price) over (partition by t.c_number, g.category ) sum_price
from transaction t
left join
good g on t.g_number = g.g_number 
order by t.c_number;

C_NUMBER               CATEGORY   SUM_PRICE              
---------------------- ---------- ---------------------- 
1                      food       72                     
1                      tool       4680                   
2                      tool       630                    
3                      food       2                      
3                      tool       10   


--列出卖出总金额最少的前3个商品名称及其卖出的金额？
select aa.* from (select distinct t.g_number,g.category,g.name, sum(g_quality*price)over(partition by g.g_number) sum_1
from transaction t
left join
good g on g.g_number = t.g_number
order by sum_1) aa
where rownum <= 3;

G_NUMBER               CATEGORY   NAME       SUM_1                  
---------------------- ---------- ---------- ---------------------- 
2                      food       Apple      2                      
4                      tool       Nail       10                     
1                      food       Banana     72                     

3 rows selected

#8

有点被玩的感觉啊。数据对应不上啊。- -

#9

............good 

#10

哈哈 搞笑哦 楼主可能是无心的

#11

引用 8 楼 mahanso 的回复:

有点被玩的感觉啊。数据对应不上啊。- -

#12

为了每次发贴发手阔绰，我需要每天 回贴

#13

这个。。。。
楼主也不是故意的吧

#14

悄悄的学习了...........

#15

create table GOOD
(
  NUMBERS  NUMBER(18),
  NAME     VARCHAR2(20),
  PRICE    NUMBER(18,2),
  CATEGORY VARCHAR2(20)
)
insert into GOOD (NUMBERS, NAME, PRICE, CATEGORY)values (6, 'Bread', 10.00, 'food');
insert into GOOD (NUMBERS, NAME, PRICE, CATEGORY)values (6, 'X', 12.00, 'tool');
insert into GOOD (NUMBERS, NAME, PRICE, CATEGORY)values (1, 'Banana', 3.00, 'food');
insert into GOOD (NUMBERS, NAME, PRICE, CATEGORY)values (2, 'Apple', 2.00, 'food');
insert into GOOD (NUMBERS, NAME, PRICE, CATEGORY)values (3, 'Drill', 900.00, 'tool');
insert into GOOD (NUMBERS, NAME, PRICE, CATEGORY)values (4, 'Nail', 1.50, 'tool');
insert into GOOD (NUMBERS, NAME, PRICE, CATEGORY)values (5, 'Wrench', 90.00, 'tool');
create table CUSTOMER
(
  NUMBERS       NUMBER(18),
  CUSTOMER_NAME VARCHAR2(20),
  ADDRESS       VARCHAR2(100)
)
insert into Customer (NUMBERS, CUSTOMER_NAME, ADDRESS)values (4, 'Jack', 'Hubei');
insert into Customer (NUMBERS, CUSTOMER_NAME, ADDRESS)values (1, 'Eric', 'beijing');
insert into Customer (NUMBERS, CUSTOMER_NAME, ADDRESS)values (2, 'Wal', 'shanghai');
insert into Customer (NUMBERS, CUSTOMER_NAME, ADDRESS)values (3, 'Allen', 'Tianjin');
create table TRANSACTION
(
  NUMBERS          NUMBER(18),
  GOOD_NUMBERS     NUMBER(18),
  CUSTOMER_NUMBERS NUMBER(18),
  GOOD_QUALITY     NUMBER(18)
)
insert into TRansaction (NUMBERS, GOOD_NUMBERS, CUSTOMER_NUMBERS, GOOD_QUALITY)values (3, 2, 3, 1);
insert into TRansaction (NUMBERS, GOOD_NUMBERS, CUSTOMER_NUMBERS, GOOD_QUALITY)values (4, 5, 2, 7);
insert into TRansaction (NUMBERS, GOOD_NUMBERS, CUSTOMER_NUMBERS, GOOD_QUALITY)values (5, 3, 1, 4);
insert into TRansaction (NUMBERS, GOOD_NUMBERS, CUSTOMER_NUMBERS, GOOD_QUALITY)values (6, 1, 1, 12);
insert into TRansaction (NUMBERS, GOOD_NUMBERS, CUSTOMER_NUMBERS, GOOD_QUALITY)values (7, 5, 1, 2);
insert into TRansaction (NUMBERS, GOOD_NUMBERS, CUSTOMER_NUMBERS, GOOD_QUALITY)values (8, 4, 3, 2);



--列出每人在购买生产工具和食品上各花多少钱？
SELECT CUSTOMER_NAME,NVL(CATEGORY,'food & tool'),SUM(PRICE*GOOD_QUALITY) COST FROM
(
SELECT T3.CUSTOMER_NAME,T3.ADDRESS,T2.CATEGORY,T2.NAME,NVL(T2.PRICE,0) PRICE,NVL(T1.GOOD_QUALITY,0) GOOD_QUALITY
FROM TRANSACTION T1,GOOD T2,CUSTOMER T3 
WHERE T1.GOOD_NUMBERS=T2.NUMBERS(+) AND T1.CUSTOMER_NUMBERS(+)=T3.NUMBERS
)
GROUP BY CUSTOMER_NAME,CATEGORY
此处个人理解“每一人”还需要考虑没有购买记录的用户。如果不需要这个种考虑的话去掉子查询中的两个左右连接即可。

--列出卖出总金额最少的前3个商品名称及其卖出的金额？
SELECT * FROM (
SELECT NAME,SUM(PRICE*GOOD_QUALITY) TOTALPRICE FROM
(
SELECT T3.CUSTOMER_NAME,T3.ADDRESS,T2.CATEGORY,T2.NAME,T2.PRICE,nvl(T1.GOOD_QUALITY,0) GOOD_QUALITY
FROM TRANSACTION T1,GOOD T2,CUSTOMER T3 
WHERE T1.GOOD_NUMBERS(+)=T2.NUMBERS AND T1.CUSTOMER_NUMBERS=T3.NUMBERS(+)
)
GROUP BY NAME
ORDER BY TOTALPRICE) WHERE ROWNUM <=3
此处如第一题，我想一个都没卖出去的东西应该也是要关心的吧。
语句未关心效率，呵呵，见谅~！

#16

1. select Customer_Name,Category,Good_Quality from Customer 
left join 
(select Customer_Number,sum(Good_Quality) as Good_Quality,Category  from 
(select Good_Number,Customer_Number,Good_Quality,GOOD.CATEGORY as Category  from Transaction  left join 
GOOD on Transaction.Good_Number = GOOD.GOOD_NUMBERS )
group by Customer_Number, Category) as t on Customer.Number  = t.Customer_Number

2. select Name,Good_Quality from Good
left join (
select Good_Number,Good_Quality  from
(select Good_Number,sum(Good_Quality) as Good_Quality  from Transaction group by Good_Number )order by Good_Quality  where  ROWNUM<3 ) as t 
on Good.Number = t.Good_Number oeder by Good_Quality

#17

关注~

#18

Transaction 表少了一行！
6    2    1   45