> For the complete documentation index, see [llms.txt](https://chankyungpak.gitbook.io/ebis3003-2021/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://chankyungpak.gitbook.io/ebis3003-2021/sql/class-activity-1-creating-new-tables.md).

# Class Activity 2 - Creating New Tables (Week 2)

## Example SQL Code

```bash
USE EBIS3003;

DROP TABLE IF EXISTS projects_kick;
DROP TABLE IF EXISTS creaotrs_kick;


CREATE TABLE creators_kick (
	id INT NOT NULL,
	user_id VARCHAR(255) NOT NULL,
	password VARCHAR(255) NOT NULL,
	num_projects INT NULL,
	address VARCHAR(255) NULL,
	bio LONGTEXT NULL,

	CONSTRAINT creators_key PRIMARY KEY(id)
);

CREATE TABLE projects_kick (
	id INT NOT NULL,
	creator_id INT NOT NULL,
	timestamp VARCHAR(255) NOT NULL,
	total_amount DECIMAL(10,3) NULL,
	completion INT NOT NULL,
	remaining_amount DECIMAL(10,3) NULL,

	CONSTRAINT projects_key PRIMARY KEY(id),
	CONSTRAINT projects_f_key FOREIGN KEY(creator_id) REFERENCES creators_kick(id) 
);
```

There are a few things to note:

1. Transit to `EBIS3003` database.
2. `DROP TABLE` in case you made mistake before.
3. `VARCHAR` v. `VARCHAR2` v. `CHAR`.
   * The numbers are 'bytes,' not # of characters.
   * Difference between standard SQL v. MySQL (or other variations).
   * <https://www.w3schools.com/sql/sql_datatypes.asp>
4. `CONSTRAINT` can be omitted in MySQL.
5. Use of `NULL`.
6. `INT` and `DECIMAL` are different!
7. The order of creating tables matter when you have constraints.
8. The name of foreign key can be different from its original name in the referenced table.&#x20;

## A Simple Data Model

powered by <https://dbdiagram.io/>&#x20;

![](https://1974645470-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MUHtAUgyY4OQkpmg5Kx%2F-MVPvLQeNkY0892NVI35%2F-MVPvn3tvb3MadS1v2UW%2Fimage.png?alt=media\&token=dba9a366-ef76-4fe1-8216-4875c1ae58d3)