Script started on Mon 25 Jan 2016 11:36:11 AM AST
pawan@dev:~/public_html/4477/notes/week04$ mysql -umysql -u s_macburnie -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 339
Server version: 5.5.46-0ubuntu0.14.04.2 (Ubuntu)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use s_macburnie
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show TABLES
    -> ;
+-----------------------------+
| Tables_in_s_macburnie       |
+-----------------------------+
| customer_analysis           |
| customer_revenues           |
| customer_revenues_by_month  |
| customer_revenues_by_season |
| customer_revenues_by_year   |
| product_analysis            |
| product_by_month            |
| product_by_season           |
| product_by_year             |
+-----------------------------+
9 rows in set (0.00 sec)

mysql> describe customer_analysis
    -> ;
+---------------------------+---------------+------+-----+---------+-------+
| Field                     | Type          | Null | Key | Default | Extra |
+---------------------------+---------------+------+-----+---------+-------+
| customer                  | varchar(20)   | YES  |     | NULL    |       |
| total_revenue             | decimal(28,5) | YES  |     | NULL    |       |
| total_visits              | bigint(21)    | NO   |     | 0       |       |
| yearly_min_revenue        | decimal(28,5) | YES  |     | NULL    |       |
| yearly_max_revenue        | decimal(28,5) | YES  |     | NULL    |       |
| yearly_average_revenue    | decimal(32,9) | YES  |     | NULL    |       |
| seasional_min_revenue     | decimal(28,5) | YES  |     | NULL    |       |
| seasional_max_revenue     | decimal(28,5) | YES  |     | NULL    |       |
| seasional_average_revenue | decimal(32,9) | YES  |     | NULL    |       |
| monthly_min_revenue       | decimal(28,5) | YES  |     | NULL    |       |
| monthly_max_revenue       | decimal(28,5) | YES  |     | NULL    |       |
| monthly_average_revenue   | decimal(32,9) | YES  |     | NULL    |       |
| yearly_min_visits         | bigint(21)    | YES  |     | NULL    |       |
| yearly_max_visits         | bigint(21)    | YES  |     | NULL    |       |
| yearly_average_visits     | decimal(24,4) | YES  |     | NULL    |       |
| seasional_min_visits      | bigint(21)    | YES  |     | NULL    |       |
| seasional_max_visits      | bigint(21)    | YES  |     | NULL    |       |
| seasional_average_visits  | decimal(24,4) | YES  |     | NULL    |       |
| monthly_min_visits        | bigint(21)    | YES  |     | NULL    |       |
| monthly_max_visits        | bigint(21)    | YES  |     | NULL    |       |
| monthly_average_visits    | decimal(24,4) | YES  |     | NULL    |       |
+---------------------------+---------------+------+-----+---------+-------+
21 rows in set (0.00 sec)

mysql> Creatcreate table top_customer_list as select customer_sk , ,
    -> sum(`selling_retail_amount)))))t) as revenue
    -> from Ddataset01.sales219
    -> group by customer_sk
    -> order by revenue desc
    -> limit 1,1000
    -> ;
Query OK, 1000 rows affected (54.69 sec)
Records: 1000  Duplicates: 0  Warnings: 0

mysql> create table top_customer_list as select customer_sk, sum(selling_retail_amt) as revenue from dataset01.s
sales219 group by customer_sk order by revenue desc limit 1,1000;M
;

Mlimit 1,1000
order by revenue desc
group by customer_sk
from dataset01.sales219
sum(selling_retail_amt) as revenue
create table top_customer_list as select customer_sk,
;describe customer_analysis
;show TABLES
    -> ;
+-----------------------------+
| Tables_in_s_macburnie       |
+-----------------------------+
| customer_analysis           |
| customer_revenues           |
| customer_revenues_by_month  |
| customer_revenues_by_season |
| customer_revenues_by_year   |
| product_analysis            |
| product_by_month            |
| product_by_season           |
| product_by_year             |
| top_customer_list           |
+-----------------------------+
10 rows in set (0.01 sec)

mysql> describe top_customer_listsstomer_list
    -> ;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| customer_sk | varchar(20)   | YES  |     | NULL    |       |
| revenue     | decimal(28,5) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> create unique index customer_index on table ()()()()()()()c()()t()o()p()_()c()u()s()t()o()m()e()r()_()l()i()s()t() ()c)u)s)t)o)m)e)r)_)s)k);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create unique index customer_index on top_customer_list (customer_sk);
;describe top_customer_list
    -> ;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| customer_sk | varchar(20)   | YES  | UNI | NULL    |       |
| revenue     | decimal(28,5) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe dataset01.sales219;
+---------------------------+--------------+------+-----+---------+-------+
| Field                     | Type         | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+---------+-------+
| TRANSACTION_RK            | varchar(10)  | YES  | MUL | NULL    |       |
| CALENDAR_DT               | varchar(9)   | YES  |     | NULL    |       |
| date                      | date         | NO   |     | NULL    |       |
| time                      | time         | NO   |     | NULL    |       |
| TRANSACTION_TM            | varchar(8)   | YES  |     | NULL    |       |
| ITEM_SK                   | varchar(20)  | YES  | MUL | NULL    |       |
| RETAIL_OUTLET_LOCATION_SK | int(3)       | YES  |     | NULL    |       |
| POS_TERMINAL_NO           | int(1)       | YES  |     | NULL    |       |
| CASHIER_NO                | int(1)       | YES  |     | NULL    |       |
| ITEM_QTY                  | int(1)       | YES  |     | NULL    |       |
| ITEM_WEIGHT               | decimal(4,3) | YES  |     | NULL    |       |
| SALES_UOM_CD              | varchar(1)   | YES  |     | NULL    |       |
| SELLING_RETAIL_AMT        | decimal(6,5) | YES  |     | NULL    |       |
| PROMO_SALES_IND_CD        | varchar(1)   | YES  |     | NULL    |       |
| STAPLE_ITEM_FLG           | varchar(1)   | YES  |     | NULL    |       |
| REGION_CD                 | int(3)       | YES  |     | NULL    |       |
| CUSTOMER_SK               | varchar(20)  | YES  | MUL | NULL    |       |
+---------------------------+--------------+------+-----+---------+-------+
17 rows in set (0.01 sec)

mysql> create table cust_spending _visitmonth_spend_visit as
    -> select customer_sk,
    -> count()d)i)s)t)i)n)c)t) )t)r)a)n)s)a)c)t)i)o)n)_)r)k) as visits,
    -> sum(selling_retail_amt) as revenue
    -> from dataset,sum(selling_retail_amt) as revenue
, 
    -> monthname()d)a)t)e) as montyh``month`;
ERROR 1054 (42S22): Unknown column 'customer_sk' in 'field list'
mysql> create table cust_month_spend_visit as select customer_sk, count(distinct transaction_rk) as visits, sum(
(selling_retail_amt) as revenue , monthname(date) as `month`;
Mscustomer_sk, count(distinct transaction_rk) as visits, sum[1@(M.customer_sk, count(distinct transaction_rk) as visits, su[1@mM


Mcreate table cust_month_spend_visit as select s.customer_sk, count(distinct transaction_rk) as visits, su
um(selling_retail_amt) as revenue , monthname(date) as `month`;
    -> from databasset01.sales219 as s,
    -> top_customer_list as t
    -> where s.customer_sk = t.customer_sk,
    -> group by cuss.customer_sk;
Query OK, 1000 rows affected (10.34 sec)
Records: 1000  Duplicates: 0  Warnings: 0

mysql> describe customer_month_spend_visit
    -> ;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| customer_sk | varchar(20)   | YES  |     | NULL    |       |
| visits      | bigint(21)    | NO   |     | 0       |       |
| revenue     | decimal(28,5) | YES  |     | NULL    |       |
| month       | varchar(9)    | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> sselect * from cust_month_spend_visit from select * from cust_month_spend_visit tooutfi out filetfile(  ""/"h"o"m"e"/"f"a"c"i"l"i"t"y"lity"ulity"ty"/"p"a"w"a"n"/"c"u"s"t"o"o"m"e"r"""""m"e"r"V"i"s"i"t";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to outfile "/home/faculty/pawan/customerVisit"' at line 1
mysql> select * from cust_month_spend_visit to outfile "/home/faculty/pawan/customerVisit";[1@i[1@n[1@ [1@t[1@o
ERROR 1045 (28000): Access denied for user 's_macburnie'@'localhost' (using password: YES)
mysql> Bye
pawan@dev:~/public_html/4477/notes/week04$ exit

Script done on Mon 25 Jan 2016 12:49:16 PM AST