This is a complex question that involves multiple steps. Here's how you can do it:

i. Creating a database:
```sql
CREATE DATABASE orderproc_db;
USE orderproc_db;
```

ii. Replacing data types:
In MySQL, the STRING data type is replaced with VARCHAR or CHAR. Let's use VARCHAR(255) for this example.

iii. Creating tables:
```sql
CREATE TABLE CUSTOMER (
custno INT,
cname VARCHAR(255),
city VARCHAR(255),
PRIMARY KEY (custno)
);

CREATE TABLE ORDE (
orderno INT,
odate DATE,
custno INT,
ord_amt INT,
PRIMARY KEY (orderno),
FOREIGN KEY (custno) REFERENCES CUSTOMER(custno)
);

CREATE TABLE ITEM (
itemno INT,
unit_price INT,
PRIMARY KEY (itemno)
);

CREATE TABLE ORDE_ITEM (
orderno INT,
itemno INT,
qty INT,
PRIMARY KEY (orderno, itemno),
FOREIGN KEY (orderno) REFERENCES ORDE(orderno),
FOREIGN KEY (itemno) REFERENCES ITEM(itemno)
);

CREATE TABLE WAREHOUSE (
warehouseno INT,
city VARCHAR(255),
PRIMARY KEY (warehouseno)
);

CREATE TABLE SHIPMENT (
orderno INT,
warehouseno INT,
ship_date DATE,
PRIMARY KEY (orderno, warehouseno),
FOREIGN KEY (orderno) REFERENCES ORDE(orderno),
FOREIGN KEY (warehouseno) REFERENCES WAREHOUSE(warehouseno)
);
```

iv. Primary keys are defined in the CREATE TABLE statements above.

v. Foreign keys are also defined in the CREATE TABLE statements above.

vi. To describe the structure of the tables, use the DESCRIBE statement:
```sql
DESCRIBE CUSTOMER;
DESCRIBE ORDE;
DESCRIBE ITEM;
DESCRIBE ORDE_ITEM;
DESCRIBE WAREHOUSE;
DESCRIBE SHIPMENT;
```

vii. To insert records, you would use the INSERT INTO statement. However, without seeing the format of the text files, it's hard to provide a specific example.

viii. To list all customers from Batticaloa:
```sql
SELECT * FROM CUSTOMER WHERE city = 'Batticaloa';
```

ix. An alternative method could be to use a LIKE statement:
```sql
SELECT * FROM CUSTOMER WHERE city LIKE 'Batticaloa';
```

x. To select all customers from Batticaloa or Jaffna:
```sql
SELECT * FROM CUSTOMER WHERE city IN ('Batticaloa', 'Jaffna');
```

xi. To count customers in Colombo:
```sql
SELECT COUNT(*) FROM CUSTOMER WHERE city = 'Colombo';
```

xii. To list customers ending with "na":
```sql
SELECT * FROM CUSTOMER WHERE cname LIKE '%na';
```

xiii. To list order amounts above 50000:
```sql
SELECT * FROM ORDE WHERE ord_amt 50000;
```

xiv. To list customers who made orders above 50000:
```sql
SELECT cname FROM CUSTOMER WHERE custno IN (SELECT custno FROM ORDE WHERE ord_amt 50000);
```

xv. To list customers who have a warehouse in their city:
```sql
SELECT cname FROM CUSTOMER WHERE city IN (SELECT city FROM WAREHOUSE);
```

xvi. To count different cities in the customer table:
```sql
SELECT COUNT(DISTINCT city) FROM CUSTOMER;
```

xvii. To count warehouses in Colombo:
```sql
SELECT COUNT(*) FROM WAREHOUSE WHERE city = 'Colombo';
```

xviii. To check if all order dates are less than or equal to shipping dates:
```sql
SELECT * FROM ORDE o JOIN SHIPMENT s ON o.orderno = s.orderno WHERE o.odate s.ship_date;
```
If this query returns any rows, then there are some orders where the order date is greater than the shipping date.

xix. To list order number, order date, customer number and order amount where all the shipment made from Colombo warehouses:
```sql
SELECT o.orderno, o.odate, o.custno, o.ord_amt FROM ORDE o JOIN SHIPMENT s ON o.orderno = s.orderno JOIN WAREHOUSE w ON s.warehouseno = w.warehouseno WHERE w.city = 'Colombo';
```
Please note that these queries assume that the data in your tables is consistent and follows the structure you provided. If there are any inconsistencies, some queries may not work as expected.

Question

This is a complex question that involves multiple steps. Here's how you can do it:

i. Creating a database:
```sql
CREATE DATABASE orderproc_db;
USE orderproc_db;
```

ii. Replacing data types:
In MySQL, the STRING data type is replaced with VARCHAR or CHAR. Let's use VARCHAR(255) for this example.

iii. Creating tables:
```sql
CREATE TABLE CUSTOMER (
    custno INT,
    cname VARCHAR(255),
    city VARCHAR(255),
    PRIMARY KEY (custno)
);

CREATE TABLE ORDE (
    orderno INT,
    odate DATE,
    custno INT,
    ord_amt INT,
    PRIMARY KEY (orderno),
    FOREIGN KEY (custno) REFERENCES CUSTOMER(custno)
);

CREATE TABLE ITEM (
    itemno INT,
    unit_price INT,
    PRIMARY KEY (itemno)
);

CREATE TABLE ORDE_ITEM (
    orderno INT,
    itemno INT,
    qty INT,
    PRIMARY KEY (orderno, itemno),
    FOREIGN KEY (orderno) REFERENCES ORDE(orderno),
    FOREIGN KEY (itemno) REFERENCES ITEM(itemno)
);

CREATE TABLE WAREHOUSE (
    warehouseno INT,
    city VARCHAR(255),
    PRIMARY KEY (warehouseno)
);

CREATE TABLE SHIPMENT (
    orderno INT,
    warehouseno INT,
    ship_date DATE,
    PRIMARY KEY (orderno, warehouseno),
    FOREIGN KEY (orderno) REFERENCES ORDE(orderno),
    FOREIGN KEY (warehouseno) REFERENCES WAREHOUSE(warehouseno)
);
```

iv. Primary keys are defined in the CREATE TABLE statements above.

v. Foreign keys are also defined in the CREATE TABLE statements above.

vi. To describe the structure of the tables, use the DESCRIBE statement:
```sql
DESCRIBE CUSTOMER;
DESCRIBE ORDE;
DESCRIBE ITEM;
DESCRIBE ORDE_ITEM;
DESCRIBE WAREHOUSE;
DESCRIBE SHIPMENT;
```

vii. To insert records, you would use the INSERT INTO statement. However, without seeing the format of the text files, it's hard to provide a specific example.

viii. To list all customers from Batticaloa:
```sql
SELECT * FROM CUSTOMER WHERE city = 'Batticaloa';
```

ix. An alternative method could be to use a LIKE statement:
```sql
SELECT * FROM CUSTOMER WHERE city LIKE 'Batticaloa';
```

x. To select all customers from Batticaloa or Jaffna:
```sql
SELECT * FROM CUSTOMER WHERE city IN ('Batticaloa', 'Jaffna');
```

xi. To count customers in Colombo:
```sql
SELECT COUNT(*) FROM CUSTOMER WHERE city = 'Colombo';
```

xii. To list customers ending with "na":
```sql
SELECT * FROM CUSTOMER WHERE cname LIKE '%na';
```

xiii. To list order amounts above 50000:
```sql
SELECT * FROM ORDE WHERE ord_amt > 50000;
```

xiv. To list customers who made orders above 50000:
```sql
SELECT cname FROM CUSTOMER WHERE custno IN (SELECT custno FROM ORDE WHERE ord_amt > 50000);
```

xv. To list customers who have a warehouse in their city:
```sql
SELECT cname FROM CUSTOMER WHERE city IN (SELECT city FROM WAREHOUSE);
```

xvi. To count different cities in the customer table:
```sql
SELECT COUNT(DISTINCT city) FROM CUSTOMER;
```

xvii. To count warehouses in Colombo:
```sql
SELECT COUNT(*) FROM WAREHOUSE WHERE city = 'Colombo';
```

xviii. To check if all order dates are less than or equal to shipping dates:
```sql
SELECT * FROM ORDE o JOIN SHIPMENT s ON o.orderno = s.orderno WHERE o.odate > s.ship_date;
```
If this query returns any rows, then there are some orders where the order date is greater than the shipping date.

xix. To list order number, order date, customer number and order amount where all the shipment made from Colombo warehouses:
```sql
SELECT o.orderno, o.odate, o.custno, o.ord_amt FROM ORDE o JOIN SHIPMENT s ON o.orderno = s.orderno JOIN WAREHOUSE w ON s.warehouseno = w.warehouseno WHERE w.city = 'Colombo';
```
Please note that these queries assume that the data in your tables is consistent and follows the structure you provided. If there are any inconsistencies, some queries may not work as expected.

Knowee AI · Accepted Answer