This is the multi-page printable view of this section. Click here to print.
You can create your own queries, but the VMart example directory includes sample query script files to help you get started quickly.
You can find the following sample scripts at this path /opt/vertica/examples/VMart_Schema.
To run any of the scripts, enter
=> \i <script_name>
Alternatively, type the commands from the script file manually.
-- vmart_query_01.sql
-- FROM clause subquery
-- Return the values for five products with the
-- lowest-fat content in the Dairy department
SELECT fat_content
FROM (
SELECT DISTINCT fat_content
FROM product_dimension
WHERE department_description
IN ('Dairy') ) AS food
ORDER BY fat_content
LIMIT 5;
Output
fat_content
-------------
80
81
82
83
84
(5 rows)
-- vmart_query_02.sql
-- WHERE clause subquery
-- Asks for all orders placed by stores located in Massachusetts
-- and by vendors located elsewhere before March 1, 2003:
SELECT order_number, date_ordered
FROM store.store_orders_fact orders
WHERE orders.store_key IN (
SELECT store_key
FROM store.store_dimension
WHERE store_state = 'MA')
AND orders.vendor_key NOT IN (
SELECT vendor_key
FROM public.vendor_dimension
WHERE vendor_state = 'MA')
AND date_ordered < '2012-03-01';
Output
order_number | date_ordered
-------------+--------------
53019 | 2012-02-10
222168 | 2012-02-05
160801 | 2012-01-08
106922 | 2012-02-07
246465 | 2012-02-10
234218 | 2012-02-03
263119 | 2012-01-04
73015 | 2012-01-01
233618 | 2012-02-10
85784 | 2012-02-07
146607 | 2012-02-07
296193 | 2012-02-05
55052 | 2012-01-05
144574 | 2012-01-05
117412 | 2012-02-08
276288 | 2012-02-08
185103 | 2012-01-03
282274 | 2012-01-01
245300 | 2012-02-06
143526 | 2012-01-04
59564 | 2012-02-06
...
-- vmart_query_03.sql
-- noncorrelated subquery
-- Requests female and male customers with the maximum
-- annual income from customers
SELECT customer_name, annual_income
FROM public.customer_dimension
WHERE (customer_gender, annual_income) IN (
SELECT customer_gender, MAX(annual_income)
FROM public.customer_dimension
GROUP BY customer_gender);
Output
customer_name | annual_income
------------------+---------------
James M. McNulty | 999979
Emily G. Vogel | 999998
(2 rows)
-- vmart_query_04.sql
-- IN predicate
-- Find all products supplied by stores in MA
SELECT DISTINCT s.product_key, p.product_description
FROM store.store_sales_fact s, public.product_dimension p
WHERE s.product_key = p.product_key
AND s.product_version = p.product_version AND s.store_key IN (
SELECT store_key
FROM store.store_dimension
WHERE store_state = 'MA')
ORDER BY s.product_key;
Output
product_key | product_description
-------------+----------------------------------------
1 | Brand #1 butter
1 | Brand #2 bagels
2 | Brand #3 lamb
2 | Brand #4 brandy
2 | Brand #5 golf clubs
2 | Brand #6 chicken noodle soup
3 | Brand #10 ground beef
3 | Brand #11 vanilla ice cream
3 | Brand #7 canned chicken broth
3 | Brand #8 halibut
3 | Brand #9 camera case
4 | Brand #12 rash ointment
4 | Brand #13 low fat milk
4 | Brand #14 chocolate chip cookies
4 | Brand #15 silver polishing cream
5 | Brand #16 cod
5 | Brand #17 band aids
6 | Brand #18 bananas
6 | Brand #19 starch
6 | Brand #20 vegetable soup
6 | Brand #21 bourbon
...
-- vmart_query_05.sql
-- EXISTS predicate
-- Get a list of all the orders placed by all stores on
-- January 2, 2003 for the vendors with records in the
-- vendor_dimension table
SELECT store_key, order_number, date_ordered
FROM store.store_orders_fact
WHERE EXISTS (
SELECT 1
FROM public.vendor_dimension
WHERE public.vendor_dimension.vendor_key = store.store_orders_fact.vendor_key)
AND date_ordered = '2012-01-02';
Output
store_key | order_number | date_ordered
-----------+--------------+--------------
98 | 151837 | 2012-01-02
123 | 238372 | 2012-01-02
242 | 263973 | 2012-01-02
150 | 226047 | 2012-01-02
247 | 232273 | 2012-01-02
203 | 171649 | 2012-01-02
129 | 98723 | 2012-01-02
80 | 265660 | 2012-01-02
231 | 271085 | 2012-01-02
149 | 12169 | 2012-01-02
141 | 201153 | 2012-01-02
1 | 23715 | 2012-01-02
156 | 98182 | 2012-01-02
44 | 229465 | 2012-01-02
178 | 141869 | 2012-01-02
134 | 44410 | 2012-01-02
141 | 129839 | 2012-01-02
205 | 54138 | 2012-01-02
113 | 63358 | 2012-01-02
99 | 50142 | 2012-01-02
44 | 131255 | 2012-01-02
...
-- vmart_query_06.sql
-- EXISTS predicate
-- Orders placed by the vendor who got the best deal
-- on January 4, 2004
SELECT store_key, order_number, date_ordered
FROM store.store_orders_fact ord, public.vendor_dimension vd
WHERE ord.vendor_key = vd.vendor_key
AND vd.deal_size IN (
SELECT MAX(deal_size)
FROM public.vendor_dimension)
AND date_ordered = '2013-01-04';
Output
store_key | order_number | date_ordered
-----------+--------------+--------------
45 | 202416 | 2013-01-04
24 | 250295 | 2013-01-04
121 | 251417 | 2013-01-04
198 | 75716 | 2013-01-04
166 | 36008 | 2013-01-04
27 | 150241 | 2013-01-04
148 | 182207 | 2013-01-04
9 | 188567 | 2013-01-04
113 | 66017 | 2013-01-04
...
-- vmart_query_07.sql
-- Multicolumn subquery
-- Which products have the highest cost,
-- grouped by category and department
SELECT product_description, sku_number, department_description
FROM public.product_dimension
WHERE (category_description, department_description, product_cost) IN (
SELECT category_description, department_description,
MAX(product_cost) FROM product_dimension
GROUP BY category_description, department_description);
Output
product_description | sku_number | department_description
---------------------------+-----------------------+---------------------------------
Brand #601 steak | SKU-#601 | Meat
Brand #649 brooms | SKU-#649 | Cleaning supplies
Brand #677 veal | SKU-#677 | Meat
Brand #1371 memory card | SKU-#1371 | Photography
Brand #1761 catfish | SKU-#1761 | Seafood
Brand #1810 frozen pizza | SKU-#1810 | Frozen Goods
Brand #1979 canned peaches | SKU-#1979 | Canned Goods
Brand #2097 apples | SKU-#2097 | Produce
Brand #2287 lens cap | SKU-#2287 | Photography
...
-- vmart_query_08.sql
-- between online_sales_fact and online_page_dimension
SELECT page_description, page_type, start_date, end_date
FROM online_sales.online_sales_fact f, online_sales.online_page_dimension d
WHERE f.online_page_key = d.online_page_key
AND page_number IN
(SELECT MAX(page_number)
FROM online_sales.online_page_dimension)
AND page_type = 'monthly' AND start_date = '2012-06-02';
Output
page_description | page_type | start_date | end_date
---------------------------+-----------+------------+-----------
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
(12 rows)
-- vmart_query_09.sql
-- Equi join
-- Joins online_sales_fact table and the call_center_dimension
-- table with the ON clause
SELECT sales_quantity, sales_dollar_amount, transaction_type, cc_name
FROM online_sales.online_sales_fact
INNER JOIN online_sales.call_center_dimension
ON (online_sales.online_sales_fact.call_center_key
= online_sales.call_center_dimension.call_center_key
AND sale_date_key = 156)
ORDER BY sales_dollar_amount DESC;
Output
sales_quantity | sales_dollar_amount | transaction_type | cc_name
----------------+---------------------+------------------+-------------------
7 | 589 | purchase | Central Midwest
8 | 589 | purchase | South Midwest
8 | 589 | purchase | California
1 | 587 | purchase | New England
1 | 586 | purchase | Other
1 | 584 | purchase | New England
4 | 584 | purchase | New England
7 | 581 | purchase | Mid Atlantic
5 | 579 | purchase | North Midwest
8 | 577 | purchase | North Midwest
4 | 577 | purchase | Central Midwest
2 | 575 | purchase | Hawaii/Alaska
4 | 573 | purchase | NY Metro
4 | 572 | purchase | Central Midwest
1 | 570 | purchase | Mid Atlantic
9 | 569 | purchase | Southeastern
1 | 569 | purchase | NY Metro
5 | 567 | purchase | Other
7 | 567 | purchase | Hawaii/Alaska
9 | 567 | purchase | South Midwest
1 | 566 | purchase | New England
...