SQL問題集
postgresのSQLの問題。
--------------------------------------------------
下記の課題の「回答」と使用した「クエリ(コマンド)」を記載
Q1. superstore_ordersテーブルの列一覧を求めてください。
A1.
クエリ(コマンド):
postgres=# \d superstore_orders
Table "public.superstore_orders"
Column | Type | Modifiers
--------------------+------------------------+-----------
rowid | integer |
orderpriority | character varying(50) |
discount | numeric(8,2) |
unitprice | numeric(8,2) |
shippingcost | numeric(8,2) |
customerid | integer |
customername | character varying(200) |
shipmode | character varying(50) |
customersegment | character varying(200) |
productcategory | character varying(200) |
productsubcategory | character varying(200) |
productcontainer | character varying(200) |
productname | character varying(500) |
productbasemargin | numeric(8,2) |
region | character varying(50) |
state | character varying(200) |
city | character varying(200) |
zipcode | character varying(50) |
orderdate | date |
shipdate | date |
profit | numeric(8,2) |
orderquantity | integer |
sales | numeric(8,2) |
orderid | integer |回答:
rowid
orderpriority
discount
unitprice
shippingcost
customerid
customername
shipmode
customersegment
productcategory
productsubcategory
productcontainer
productname
productbasemargin
region
state
city
zipcode
orderdate
shipdate
profit
orderquantity
sales
orderid
Q2. 全レコードの件数を求めてください。
A2.
クエリ(コマンド):
postgres=# select count(1) from superstore_orders;
count
-------
9426
(1 row)回答:
9426
Q3. 顧客の数(customeridの一意な数)を求めてください。
A3.
クエリ(コマンド):
postgres=# select count(1) from (select customerid from superstore_orders group by customerid ) as c;
count
-------
2703
(1 row)回答:
2703
Q4. 顧客セグメント(customersegment)の一覧を求めてください。
A4.
クエリ(コマンド):
postgres=# select customersegment from superstore_orders group by 1;
customersegment
-----------------
Home Office
Corporate
Small Business
Consumer
(4 rows)回答:
Home Office
Corporate
Small Business
Consumer
Q5. 2012年7月の合計売上（salesの合計）を求めてください。なお、期間の範囲はorderdateを参照してください。
A5.
クエリ(コマンド):
postgres=# select sum(sales) from superstore_orders where orderdate between '2012-07-01' and '2012-07-31';
sum
-----------
158228.96
(1 row)回答:
158228.96
Q6. 「Home Office」の顧客セグメントが2012年7月の合計売上に対して占める割合は何パーセントか、求めてください。パーセントの小数第2位まで求めてください。
A6.
クエリ(コマンド):
postgres=# select sum(sales) from superstore_orders where orderdate between '2012-07-01' and '2012-07-31' and customersegment = 'Home Office';
sum
----------
37930.18
(1 row)postgres=# select (select sum(sales) from superstore_orders where orderdate between '2012-07-01' and '2012-07-31' and customersegment = 'Home Office') / (select sum(sales) from superstore_orders where orderdate between '2012-07-01' and '2012-07-31') * 100;
?column?
-------------------------
23.97170530603247344900
(1 row)select round(select sum(sales) from superstore_orders where orderdate between '2012-07-01' and '2012-07-31' and customersegment = 'Home Office') / (select sum(sales) from superstore_orders where orderdate between '2012-07-01' and '2012-07-31') * 100),2);回答:
23.97%
Q7. 2011年における地域(region)ごとの売上上位10位までの製品名(productname)を抽出してください。その中から、2011年に中部(Central)と西部(West) の両方で第2位だった製品名を求めてください。（第2位の製品名を書いてください。）
A7.
a. 2011年における地域(region)ごとの売上上位10位までの製品名(productname)
クエリ(コマンド):
postgres=# select region from superstore_orders group by 1;
region
---------
West
Central
East
South
(4 rows)postgres=# select region,productname,sum(sales) as sale from superstore_orders where orderdate between '2011-01-01' and '2011-12-31' and region = 'West' group by region,productname order by sale desc limit 10;
region | productname | sale
--------+-------------------------------------------------------------------------------+----------
West | Canon imageCLASS 2200 Advanced Copier | 50332.66
West | Sharp AL-1530CS Digital Copier | 30639.47
West | Global Leather Executive Chair | 18407.67
West | Office Star - Contemporary Task Swivel chair with 2-way adjustable arms, Plum | 14428.54
West | Bush Mission Pointe Library | 14425.83
West | Hewlett-Packard cp1700 [D, PS] Series Color Inkjet Printers | 12924.23
West | Luxo Professional Combination Clamp-On Lamps | 11389.65
West | Polycom ViewStation? ISDN Videoconferencing Unit | 9507.12
West | Canon PC940 Copier | 9038.19
West | Hoover Commercial Lightweight Upright Vacuum with E-Z Empty? Dirt Cup | 7922.31
(10 rows)
postgres=# select region,productname,sum(sales) as sale from superstore_orders where orderdate between '2011-01-01' and '2011-12-31' and region = 'Central' group by region,productname order by sale desc limit 10;
region | productname | sale
---------+-----------------------------------------------------------------------------+----------
Central | Bretford CR8500 Series Meeting Room Furniture | 32589.59
Central | Sharp AL-1530CS Digital Copier | 27329.34
Central | Polycom VoiceStation 100 | 22908.30
Central | Hewlett Packard LaserJet 3310 Copier | 16418.82
Central | Canon imageCLASS 2200 Advanced Copier | 14380.76
Central | Okidata ML184 Turbo Dot Matrix Printers | 12588.56
Central | Epson Stylus 1520 Color Inkjet Printer | 12076.27
Central | Canon PC1080F Personal Copier | 11962.24
Central | Hewlett-Packard Deskjet 1220Cse Color Inkjet Printer | 11570.92
Central | Kensington 7 Outlet MasterPiece Power Center with Fax/Phone Line Protection | 11328.41
(10 rows)
postgres=# select region,productname,sum(sales) as sale from superstore_orders where orderdate between '2011-01-01' and '2011-12-31' and region = 'East' group by region,productname order by sale desc limit 10;
region | productname | sale
--------+-------------------------------------------------------------+----------
East | Canon PC1080F Personal Copier | 48418.58
East | Hon 2090 “Pillow Soft” Series Mid Back Swivel/Tilt Chairs | 30578.95
East | Bretford CR8500 Series Meeting Room Furniture | 25690.28
East | Global Troy? Executive Leather Low-Back Tilter | 14146.87
East | R280 | 10954.73
East | Hon 94000 Series Round Tables | 10921.31
East | Canon PC1060 Personal Laser Copier | 10668.07
East | Polycom Soundstation EX Audio-Conferencing Telephone, Black | 9629.91
East | Global Adaptabilities? Conference Tables | 8226.15
East | Hon Non-Folding Utility Tables | 8163.19
(10 rows)postgres=# select region,productname,sum(sales) as sale from superstore_orders where orderdate between '2011-01-01' and '2011-12-31' and region = 'South' group by region,productname order by sale desc limit 10;
region | productname | sale
--------+-------------------------------------------------------------------+----------
South | Global Troy? Executive Leather Low-Back Tilter | 21390.44
South | BoxOffice By Design Rectangular and Half-Moon Meeting Room Tables | 18738.02
South | Riverside Palais Royal Lawyers Bookcase, Royale Cherry Finish | 13459.18
South | Canon MP41DH Printing Calculator | 12612.62
South | Okidata ML591 Wide Format Dot Matrix Printer | 11571.81
South | Smead Adjustable Mobile File Trolley with Lockable Top | 10227.73
South | GBC DocuBind 200 Manual Binding Machine | 7785.21
South | SAFCO Arco Folding Chair | 7767.97
South | Riverside Furniture Stanwyck Manor Table Series | 7582.99
South | Lifetime Advantage? Folding Chairs, 4/Carton | 6226.83
(10 rows)回答:
b. 2011年に中部(Central)と西部(West)の両方で第2位だった製品名
クエリ(コマンド):
postgres=# select * from (select region,productname,sum(sales) as sale,row_number() over(partition by region order by sum(sales) desc ) as num from superstore_orders where orderdate between '2011-01-01' and '2011-12-31' and region = 'Central' group by region,productname order by sale desc limit 2) as s where s.num =2 ;
region | productname | sale | num
---------+--------------------------------+----------+-----
Central | Sharp AL-1530CS Digital Copier | 27329.34 | 2
(1 row)
postgres=# select * from (select region,productname,sum(sales) as sale,row_number() over(partition by region order by sum(sales) desc ) as num from superstore_orders where orderdate between '2011-01-01' and '2011-12-31' and region = 'West' group by region,productname order by s
ale desc limit 2) as s where s.num =2;
region | productname | sale | num
--------+--------------------------------+----------+-----
West | Sharp AL-1530CS Digital Copier | 30639.47 | 2
(1 row)
回答:
中部2位：Sharp AL-1530CS Digital Copier
西部2位：Sharp AL-1530CS Digital Copier
いつもサポートありがとうございます。 あなたの100円がモチベーションアップの起爆剤です。 毎日更新頑張ります Twitterはこちら https://twitter.com/7010Rei