github.com/dolthub/go-mysql-server@v0.18.0/enginetest/scriptgen/setup/scripts/tpcds (about) 1 exec 2 create table catalog_returns ( 3 cr_returned_date_sk bigint, 4 cr_returned_time_sk bigint, 5 cr_item_sk bigint not null, 6 cr_refunded_customer_sk bigint, 7 cr_refunded_cdemo_sk bigint, 8 cr_refunded_hdemo_sk bigint, 9 cr_refunded_addr_sk bigint, 10 cr_returning_customer_sk bigint, 11 cr_returning_cdemo_sk bigint, 12 cr_returning_hdemo_sk bigint, 13 cr_returning_addr_sk bigint, 14 cr_call_center_sk bigint, 15 cr_catalog_page_sk bigint , 16 cr_ship_mode_sk bigint , 17 cr_warehouse_sk bigint , 18 cr_reason_sk bigint , 19 cr_order_number bigint not null primary key, 20 cr_return_quantity int, 21 cr_return_amount decimal(7,2), 22 cr_return_tax decimal(7,2), 23 cr_return_amt_inc_tax decimal(7,2), 24 cr_fee decimal(7,2), 25 cr_return_ship_cost decimal(7,2), 26 cr_refunded_cash decimal(7,2), 27 cr_reversed_charge decimal(7,2), 28 cr_store_credit decimal(7,2), 29 cr_net_loss decimal(7,2) 30 ); 31 ---- 32 33 exec 34 create table catalog_sales ( 35 cs_sold_date_sk bigint, 36 cs_sold_time_sk bigint, 37 cs_ship_date_sk bigint, 38 cs_bill_customer_sk bigint, 39 cs_bill_cdemo_sk bigint, 40 cs_bill_hdemo_sk bigint, 41 cs_bill_addr_sk bigint, 42 cs_ship_customer_sk bigint, 43 cs_ship_cdemo_sk bigint, 44 cs_ship_hdemo_sk bigint, 45 cs_ship_addr_sk bigint, 46 cs_call_center_sk bigint, 47 cs_catalog_page_sk bigint, 48 cs_ship_mode_sk bigint, 49 cs_warehouse_sk bigint, 50 cs_item_sk bigint not null, 51 cs_promo_sk bigint, 52 cs_order_number bigint not null primary key, 53 cs_quantity int, 54 cs_wholesale_cost decimal(7,2), 55 cs_list_price decimal(7,2), 56 cs_sales_price decimal(7,2), 57 cs_ext_discount_amt decimal(7,2), 58 cs_ext_sales_price decimal(7,2), 59 cs_ext_wholesale_cost decimal(7,2), 60 cs_ext_list_price decimal(7,2), 61 cs_ext_tax decimal(7,2), 62 cs_coupon_amt decimal(7,2), 63 cs_ext_ship_cost decimal(7,2), 64 cs_net_paid decimal(7,2), 65 cs_net_paid_inc_tax decimal(7,2), 66 cs_net_paid_inc_ship decimal(7,2), 67 cs_net_paid_inc_ship_tax decimal(7,2), 68 cs_net_profit decimal(7,2) 69 ); 70 ---- 71 72 exec 73 create table inventory ( 74 inv_date_sk bigint not null, 75 inv_item_sk bigint not null, 76 inv_warehouse_sk bigint not null, 77 inv_quantity_on_hand int, 78 primary key (inv_date_sk, inv_item_sk, inv_warehouse_sk) 79 ); 80 ---- 81 82 exec 83 create table store_returns ( 84 sr_returned_date_sk bigint, 85 sr_return_time_sk bigint, 86 sr_item_sk bigint not null, 87 sr_customer_sk bigint, 88 sr_cdemo_sk bigint, 89 sr_hdemo_sk bigint, 90 sr_addr_sk bigint, 91 sr_store_sk bigint, 92 sr_reason_sk bigint, 93 sr_ticket_number bigint not null primary key, 94 sr_return_quantity int, 95 sr_return_amt decimal(7,2), 96 sr_return_tax decimal(7,2), 97 sr_return_amt_inc_tax decimal(7,2), 98 sr_fee decimal(7,2), 99 sr_return_ship_cost decimal(7,2), 100 sr_refunded_cash decimal(7,2), 101 sr_reversed_charge decimal(7,2), 102 sr_store_credit decimal(7,2), 103 sr_net_loss decimal(7,2) 104 ); 105 ---- 106 107 exec 108 create table store_sales ( 109 ss_sold_date_sk bigint, 110 ss_sold_time_sk bigint, 111 ss_item_sk bigint not null, 112 ss_customer_sk bigint, 113 ss_cdemo_sk bigint, 114 ss_hdemo_sk bigint, 115 ss_addr_sk bigint, 116 ss_store_sk bigint, 117 ss_promo_sk bigint, 118 ss_ticket_number bigint not null primary key, 119 ss_quantity int, 120 ss_wholesale_cost decimal(7,2), 121 ss_list_price decimal(7,2), 122 ss_sales_price decimal(7,2), 123 ss_ext_discount_amt decimal(7,2), 124 ss_ext_sales_price decimal(7,2), 125 ss_ext_wholesale_cost decimal(7,2), 126 ss_ext_list_price decimal(7,2), 127 ss_ext_tax decimal(7,2), 128 ss_coupon_amt decimal(7,2), 129 ss_net_paid decimal(7,2), 130 ss_net_paid_inc_tax decimal(7,2), 131 ss_net_profit decimal(7,2) 132 ); 133 ---- 134 135 exec 136 create table web_returns ( 137 wr_returned_date_sk bigint, 138 wr_returned_time_sk bigint, 139 wr_item_sk bigint not null, 140 wr_refunded_customer_sk bigint, 141 wr_refunded_cdemo_sk bigint, 142 wr_refunded_hdemo_sk bigint, 143 wr_refunded_addr_sk bigint, 144 wr_returning_customer_sk bigint, 145 wr_returning_cdemo_sk bigint, 146 wr_returning_hdemo_sk bigint, 147 wr_returning_addr_sk bigint, 148 wr_web_page_sk bigint, 149 wr_reason_sk bigint, 150 wr_order_number bigint not null, 151 wr_return_quantity int, 152 wr_return_amt decimal(7,2), 153 wr_return_tax decimal(7,2), 154 wr_return_amt_inc_tax decimal(7,2), 155 wr_fee decimal(7,2), 156 wr_return_ship_cost decimal(7,2), 157 wr_refunded_cash decimal(7,2), 158 wr_reversed_charge decimal(7,2), 159 wr_account_credit decimal(7,2), 160 wr_net_loss decimal(7,2) 161 ); 162 ---- 163 164 exec 165 create table web_sales ( 166 ws_sold_date_sk bigint, 167 ws_sold_time_sk bigint, 168 ws_ship_date_sk bigint, 169 ws_item_sk bigint not null, 170 ws_bill_customer_sk bigint, 171 ws_bill_cdemo_sk bigint, 172 ws_bill_hdemo_sk bigint, 173 ws_bill_addr_sk bigint, 174 ws_ship_customer_sk bigint, 175 ws_ship_cdemo_sk bigint, 176 ws_ship_hdemo_sk bigint, 177 ws_ship_addr_sk bigint, 178 ws_web_page_sk bigint, 179 ws_web_site_sk bigint, 180 ws_ship_mode_sk bigint, 181 ws_warehouse_sk bigint, 182 ws_promo_sk bigint, 183 ws_order_number bigint not null, 184 ws_quantity int, 185 ws_wholesale_cost decimal(7,2), 186 ws_list_price decimal(7,2), 187 ws_sales_price decimal(7,2), 188 ws_ext_discount_amt decimal(7,2), 189 ws_ext_sales_price decimal(7,2), 190 ws_ext_wholesale_cost decimal(7,2), 191 ws_ext_list_price decimal(7,2), 192 ws_ext_tax decimal(7,2), 193 ws_coupon_amt decimal(7,2), 194 ws_ext_ship_cost decimal(7,2), 195 ws_net_paid decimal(7,2), 196 ws_net_paid_inc_tax decimal(7,2), 197 ws_net_paid_inc_ship decimal(7,2), 198 ws_net_paid_inc_ship_tax decimal(7,2), 199 ws_net_profit decimal(7,2), 200 primary key (ws_order_number) 201 ); 202 ---- 203 204 exec 205 create table call_center ( 206 cc_call_center_sk bigint not null, 207 cc_call_center_id char(16) not null, 208 cc_rec_start_date date, 209 cc_rec_end_date date, 210 cc_closed_date_sk bigint, 211 cc_open_date_sk bigint, 212 cc_name varchar(50), 213 cc_class varchar(50), 214 cc_employees int, 215 cc_sq_ft int, 216 cc_hours char(20), 217 cc_manager varchar(40), 218 cc_mkt_id int, 219 cc_mkt_class char(50), 220 cc_mkt_desc varchar(100), 221 cc_market_manager varchar(40), 222 cc_division int, 223 cc_division_name varchar(50), 224 cc_company int, 225 cc_company_name char(50), 226 cc_street_number char(10), 227 cc_street_name varchar(60), 228 cc_street_type char(15), 229 cc_suite_number char(10), 230 cc_city varchar(60), 231 cc_county varchar(30), 232 cc_state char(2), 233 cc_zip char(10), 234 cc_country varchar(20), 235 cc_gmt_offset decimal(5,2), 236 cc_tax_percentage decimal(5,2), 237 PRIMARY key(cc_call_center_sk) 238 ); 239 ---- 240 241 exec 242 create table catalog_page 243 ( 244 cp_catalog_page_sk bigint not null, 245 cp_catalog_page_id varchar(16) not null, 246 cp_start_date_sk bigint, 247 cp_end_date_sk bigint, 248 cp_department varchar(50), 249 cp_catalog_number int, 250 cp_catalog_page_number int, 251 cp_description varchar(100), 252 cp_type varchar(100), 253 primary key(cp_catalog_page_sk) 254 ); 255 ---- 256 257 exec 258 CREATE TABLE customer ( 259 c_customer_sk bigint NOT NULL, 260 c_customer_id char(16) NOT NULL, 261 c_current_cdemo_sk bigint, 262 c_current_hdemo_sk bigint, 263 c_current_addr_sk bigint, 264 c_first_shipto_date_sk bigint, 265 c_first_sales_date_sk bigint, 266 c_salutation char(10), 267 c_first_name char(20), 268 c_last_name char(30), 269 c_preferred_cust_flag char(1), 270 c_birth_day int, 271 c_birth_month int, 272 c_birth_year int, 273 c_birth_country varchar(20), 274 c_login char(13), 275 c_email_address char(50), 276 c_last_review_date_sk bigint, 277 PRIMARY key(c_customer_sk) 278 ); 279 ---- 280 281 exec 282 CREATE TABLE customer_address 283 ( 284 ca_address_sk bigint NOT NULL, 285 ca_address_id varchar(16) NOT NULL, 286 ca_street_number varchar(10), 287 ca_street_name varchar(60), 288 ca_street_type varchar(15), 289 ca_suite_number varchar(10), 290 ca_city varchar(60), 291 ca_county varchar(30), 292 ca_state varchar(2), 293 ca_zip varchar(10), 294 ca_country varchar(20), 295 ca_gmt_offset decimal(5,2), 296 ca_location_type varchar(20), 297 primary key(ca_address_sk) 298 ); 299 ---- 300 301 exec 302 create table customer_demographics ( 303 cd_demo_sk bigint not null, 304 cd_gender char(1), 305 cd_marital_status char(1), 306 cd_education_status char(20), 307 cd_purchase_estimate int, 308 cd_credit_rating char(10), 309 cd_dep_count int, 310 cd_dep_employed_count int, 311 cd_dep_college_count int, 312 primary key(cd_demo_sk) 313 ); 314 ---- 315 316 exec 317 create table date_dim ( 318 d_date_sk bigint not null, 319 d_date_id char(16) not null, 320 d_date date, 321 d_month_seq int, 322 d_week_seq int, 323 d_quarter_seq int, 324 d_year int, 325 d_dow int, 326 d_moy int, 327 d_dom int, 328 d_qoy int, 329 d_fy_year int, 330 d_fy_quarter_seq int, 331 d_fy_week_seq int, 332 d_day_name char(9), 333 d_quarter_name char(6), 334 d_holiday char(1), 335 d_weekend char(1), 336 d_following_holiday char(1), 337 d_first_dom int, 338 d_last_dom int, 339 d_same_day_ly int, 340 d_same_day_lq int, 341 d_current_day char(1), 342 d_current_week char(1), 343 d_current_month char(1), 344 d_current_quarter char(1), 345 d_current_year char(1), 346 primary key(d_date_sk) 347 ); 348 ---- 349 350 exec 351 create table household_demographics ( 352 hd_demo_sk bigint not null, 353 hd_income_band_sk bigint, 354 hd_buy_potential char(15), 355 hd_dep_count int, 356 hd_vehicle_count int, 357 PRIMARY key(hd_demo_sk) 358 ); 359 ---- 360 361 exec 362 create table income_band ( 363 ib_income_band_sk bigint not null, 364 ib_lower_bound int, 365 ib_upper_bound int, 366 PRIMARY key(ib_income_band_sk) 367 ); 368 ---- 369 370 exec 371 create table item ( 372 i_item_sk bigint not null, 373 i_item_id char(16) not null, 374 i_rec_start_date date, 375 i_rec_end_date date, 376 i_item_desc varchar(200), 377 i_current_price decimal(7,2), 378 i_wholesale_cost decimal(7,2), 379 i_brand_id int, 380 i_brand char(50), 381 i_class_id int, 382 i_class char(50), 383 i_category_id int, 384 i_category char(50), 385 i_manufact_id int, 386 i_manufact char(50), 387 i_size char(20), 388 i_formulation char(20), 389 i_color char(20), 390 i_units char(10), 391 i_container char(10), 392 i_manager_id int, 393 i_product_name char(50), 394 PRIMARY key(i_item_sk) 395 ); 396 ---- 397 398 exec 399 create table promotion ( 400 p_promo_sk bigint not null, 401 p_promo_id char(16) not null, 402 p_start_date_sk bigint, 403 p_end_date_sk bigint, 404 p_item_sk bigint, 405 p_cost decimal(15,2), 406 p_response_target int, 407 p_promo_name char(50), 408 p_channel_dmail char(1), 409 p_channel_email char(1), 410 p_channel_catalog char(1), 411 p_channel_tv char(1), 412 p_channel_radio char(1), 413 p_channel_press char(1), 414 p_channel_event char(1), 415 p_channel_demo char(1), 416 p_channel_details varchar(100), 417 p_purpose char(15), 418 p_discount_active char(1), 419 PRIMARY key(p_promo_sk) 420 ); 421 ---- 422 423 exec 424 create table reason ( 425 r_reason_sk bigint not null, 426 r_reason_id char(16) not null, 427 r_reason_desc char(100), 428 PRIMARY key(r_reason_sk) 429 ); 430 ---- 431 432 exec 433 create table ship_mode ( 434 sm_ship_mode_sk bigint, 435 sm_ship_mode_id char(16) not null, 436 sm_type char(30), 437 sm_code char(10), 438 sm_carrier char(20), 439 sm_contract char(20), 440 primary key(sm_ship_mode_sk) 441 ); 442 ---- 443 444 exec 445 create table store ( 446 s_store_sk bigint not null, 447 s_store_id char(16) not null, 448 s_rec_start_date date, 449 s_rec_end_date date, 450 s_closed_date_sk bigint, 451 s_store_name varchar(50), 452 s_number_employees int, 453 s_floor_space int, 454 s_hours char(20), 455 s_manager varchar(40), 456 s_market_id int, 457 s_geography_class varchar(100), 458 s_market_desc varchar(100), 459 s_market_manager varchar(40), 460 s_division_id int, 461 s_division_name varchar(50), 462 s_company_id int, 463 s_company_name varchar(50), 464 s_street_number varchar(10), 465 s_street_name varchar(60), 466 s_street_type char(15), 467 s_suite_number char(10), 468 s_city varchar(60), 469 s_county varchar(30), 470 s_state char(2), 471 s_zip char(10), 472 s_country varchar(20), 473 s_gmt_offset decimal(5,2), 474 s_tax_percentage decimal(5,2), 475 PRIMARY key(s_store_sk) 476 ); 477 ---- 478 479 exec 480 create table time_dim ( 481 t_time_sk bigint not null, 482 t_time_id char(16) not null, 483 t_time int, 484 t_hour int, 485 t_minute int, 486 t_second int, 487 t_am_pm char(2), 488 t_shift char(20), 489 t_sub_shift char(20), 490 t_meal_time char(20), 491 primary key(t_time_sk) 492 ); 493 ---- 494 495 exec 496 create table warehouse ( 497 w_warehouse_sk bigint not null, 498 w_warehouse_id char(16) not null, 499 w_warehouse_name varchar(20), 500 w_warehouse_sq_ft int, 501 w_street_number char(10), 502 w_street_name varchar(60), 503 w_street_type char(15), 504 w_suite_number char(10), 505 w_city varchar(60), 506 w_county varchar(30), 507 w_state char(2), 508 w_zip char(10), 509 w_country varchar(20), 510 w_gmt_offset decimal(5,2), 511 primary key(w_warehouse_sk) 512 ); 513 ---- 514 515 exec 516 create table web_page ( 517 wp_web_page_sk bigint not null, 518 wp_web_page_id char(16) not null, 519 wp_rec_start_date date, 520 wp_rec_end_date date, 521 wp_creation_date_sk bigint, 522 wp_access_date_sk bigint, 523 wp_autogen_flag char(1), 524 wp_customer_sk bigint, 525 wp_url varchar(100), 526 wp_type char(50), 527 wp_char_count int, 528 wp_link_count int, 529 wp_image_count int, 530 wp_max_ad_count int, 531 PRIMARY key(wp_web_page_sk) 532 ); 533 ---- 534 535 exec 536 create table web_site ( 537 web_site_sk bigint not null, 538 web_site_id char(16) not null, 539 web_rec_start_date date, 540 web_rec_end_date date, 541 web_name varchar(50), 542 web_open_date_sk bigint, 543 web_close_date_sk bigint, 544 web_class varchar(50), 545 web_manager varchar(40), 546 web_mkt_id int, 547 web_mkt_class varchar(50), 548 web_mkt_desc varchar(100), 549 web_market_manager varchar(40), 550 web_company_id int, 551 web_company_name char(50), 552 web_street_number char(10), 553 web_street_name varchar(60), 554 web_street_type char(15), 555 web_suite_number char(10), 556 web_city varchar(60), 557 web_county varchar(30), 558 web_state char(2), 559 web_zip char(10), 560 web_country varchar(20), 561 web_gmt_offset decimal(5,2), 562 web_tax_percentage decimal(5,2), 563 PRIMARY key(web_site_sk) 564 ); 565 ---- 566 567 exec 568 analyze table call_center update histogram on (cc_call_center_sk) using data '{"row_count": 6}'; 569 ---- 570 exec 571 analyze table catalog_page update histogram on (cp_catalog_page_sk) using data '{"row_count": 11718}'; 572 ---- 573 exec 574 analyze table catalog_returns update histogram on (cr_order_number) using data '{"row_count": 144067}'; 575 ---- 576 exec 577 analyze table catalog_sales update histogram on (cs_order_number) using data '{"row_count": 441548}'; 578 ---- 579 exec 580 analyze table customer update histogram on (c_customer_sk) using data '{"row_count": 100000}'; 581 ---- 582 exec 583 analyze table customer_address update histogram on (ca_address_sk) using data '{"row_count": 50000}'; 584 ---- 585 exec 586 analyze table customer_demographics update histogram on (cd_demo_sk) using data '{"row_count": 920800}'; 587 ---- 588 exec 589 analyze table date_dim update histogram on (d_date_sk) using data '{"row_count": 73049}'; 590 ---- 591 exec 592 analyze table household_demographics update histogram on (hd_demo_sk) using data '{"row_count": 7200}'; 593 ---- 594 exec 595 analyze table income_band update histogram on (ib_income_band_sk) using data '{"row_count": 20}'; 596 ---- 597 exec 598 analyze table inventory update histogram on (inv_date_sk, inv_item_sk, inv_warehouse_sk) using data '{"row_count": 1745000}'; 599 ---- 600 exec 601 analyze table item update histogram on (i_item_sk) using data '{"row_count": 18000}'; 602 ---- 603 exec 604 analyze table promotion update histogram on (p_promo_sk) using data '{"row_count": 300}'; 605 ---- 606 exec 607 analyze table reason update histogram on (r_reason_sk) using data '{"row_count": 35}'; 608 ---- 609 exec 610 analyze table ship_mode update histogram on (sm_ship_mode_sk) using data '{"row_count": 20}'; 611 ---- 612 exec 613 analyze table store update histogram on (s_store_sk) using data '{"row_count": 12}'; 614 ---- 615 exec 616 analyze table store_returns update histogram on (sr_ticket_number) using data '{"row_count": 287514}'; 617 ---- 618 exec 619 analyze table store_sales update histogram on (store_sales) using data '{"row_count": 880404}'; 620 ---- 621 exec 622 analyze table time_dim update histogram on (t_time_sk) using data '{"row_count": 86400}'; 623 ---- 624 exec 625 analyze table warehouse update histogram on (w_warehouse_sk) using data '{"row_count": 5}'; 626 ---- 627 exec 628 analyze table web_page update histogram on (wp_web_page_sk) using data '{"row_count": 60}'; 629 ---- 630 exec 631 analyze table web_returns update histogram on (wr_order_number) using data '{"row_count": 71763}'; 632 ---- 633 exec 634 analyze table web_sales update histogram on (ws_order_number) using data '{"row_count": 719384}'; 635 ---- 636 exec 637 analyze table web_site update histogram on (web_site_sk) using data '{"row_count": 30}'; 638 ----