---
# System prepended metadata

title: Create table commands

---

# Create table commands
This section shows the sql comamnd to create the tables.
## Create customer
```
CREATE TABLE customer(
    id              int,
    name            varchar(25),
    address         varchar(40),
    nation_id       int,
    phone           char(15),
    acct_bal        decimal(10,2),
    market_segment  char(10),
    comment         varchar(117)
);

```


## Create nation
```
CREATE TABLE nation(
    id              int,
    name            varchar(25),
    region_id       int,
    comment         varchar(152)
);
```

## Create supplier
```
CREATE TABLE supplier(
    id              int,
    name            char(25),
    address         varchar(40),
    nation_id       int,
    phone           char(15),
    acct_bal        decimal(10,2),
    comment         varchar(101)
);
```

## Create region
```
CREATE TABLE region(
    id              int,
    name            char(25),
    comment         varchar(152)
);
```

## Create orders
```
CREATE TABLE orders(
    id int,
    customer_id int,
    order_status char(1),
    total_price decimal(10,2),
    order_date date,
    order_priority char(15),
    clerk char(15),
    ship_priority int,
    comment varchar(79)
);
```

## Create lineitem
```
CREATE TABLE lineitem(
    orders_id int,
    part_id int,
    supplier_id int,
    id int,
    qty decimal(10,2),
    extended_price decimal(10,2),
    discount decimal(10,2),
    tax decimal(10,2),
    return_flag char(1),
    status char(1),
    ship_date date,
    commit_date date,
    receipt_date date,
    ship_instruct char(25),
    ship_mode char(10),
    comment varchar(44)
);
```


## Create part
```
CREATE TABLE part(
    id int,
    name varchar(55),
    mfgr char(25),
    brand char(10),
    type varchar(25),
    size int,
    container char(10),
    retail_price decimal(10,2),
    comment varchar(23)
);
```


## Create part_has_supplier
```
CREATE TABLE part_has_supplier(
    part_id int,
    supplier_id int,
    avail_qty int,
    supply_cost decimal(10,2),
    comment varchar(199)
);
```

# Loading of test data
There are multiple ways to load test data. We will discuss using the psql way. psql is a command line tool for interacting with a postgresql server. It is the same as mysql cli for interacting with mysql server. 

## Connect to your postgresql server
```
psql -h <dbhost> -p <port> -d <dbname> -U <dbusername>
```
Key in your password if prompted.

## Loading the data in
Once you are in the psql terminal, run the below commands to load the individual table data.
### Load customer
```
\COPY customer(id, name, address, nation_id, phone, acct_bal, market_segment, comment) FROM '<path to files>/files/customer.csv' DELIMITER '|' CSV;
```

### Load nation
```
\COPY nation(id, name, region_id, comment) FROM '<path to files>/files/nation.csv' DELIMITER '|' CSV;
```

### Load region
```
\COPY region(id, name, comment) FROM '<path to files>/files/region.csv' DELIMITER '|' CSV;
```

### Load supplier
```
\COPY supplier(id, name, address, nation_id, phone, acct_bal, comment) FROM '<path to files>/files/supplier.csv' DELIMITER '|' CSV;
```

### Load orders
```
\COPY orders(id, customer_id, order_status, total_price, order_date, order_priority, clerk, ship_priority, comment) FROM '<path to files>/files/orders.csv' DELIMITER '|' CSV;
```

### Load lineitem(may take awhile)
```
\COPY lineitem(orders_id, part_id, supplier_id, id, qty, extended_price, discount, tax, return_flag, status, ship_date, commit_date, receipt_date, ship_instruct, ship_mode, comment) FROM '<path to files>/files/lineitem.csv' DELIMITER '|' CSV;
```

### Load part
```
\COPY part(id, name, mfgr, brand, type, size, container, retail_price, comment) FROM '<path to files>/files/part.csv' DELIMITER '|' CSV;
```

### Load part_has_supplier
```
\COPY part_has_supplier(part_id, supplier_id, avail_qty, supply_cost, comment) FROM '<path to files>/files/partsupp.csv' DELIMITER '|' CSV;
```