All language subtitles for 3. Creating DB using CSV files

So in this video, we will discuss the last option.
1

To create the Supermart database that is creating individual tables and using the CSV files to load the
2

data into those tables.
3

Now we have three tables in the supermart database, customer table, sales table and product table.
4

We will be creating those tables individually and copying data from the 3 csv files I've given you in
5

the resources.
6

Now, you already know how to create a table.
7

We use the create table command and after that we give the name of the label and list down all the columns
8

along with their data types.
9

So in the customer table, we have the following columns customer ID, customer name, segment, age, country,
10

city, state, postal code and region.
11

And corresponding to each column name, you can check out the corresponding data type.
12

So this is the query which will create the customer table, and then once you have created the customer
13

table, we have to import data from the CSV file using the copy command.
14

So this is the customer table.
15

Let me show you the syntax for the other tables also.
16

And after that, I'll also run these commands in PostgreSQL to show how the tables are being created.
17

So the next table is product table, product table has only four columns, product ID, category, subcategory
18

and product name.
19

And the data for it is saved in the product.csv file.
20

Then there is sales table, sales table has a lot of columns order line, order id, order date, ship date,
21

ship mode customer ID, product ID, sales, quantity, discount and profit.
22

And the data for this is stored in sales.csv.
23

So basically, we are doing two things for each table, we are creating the table and then using the
24

copy command on let's go and run these commands in postgreSQL.
25

So I'm creating a new database called Using csv to showcase how we are creating the database, using
26

the CSV files.
27

So I have copy-pasted the commands from the presentation to my query tool.
28

Now I'll be running these queries to create the tables and then using these copy command queries to
29

import the data from
30

csv file, note that the csv files are put in the installation directory of postgreSQL.
31

So this was my installation directly within the installation directory.
32

We had the data folder and there I have created a new folder called Data Set and I put the three files
33

in that folder.
34

I have done this because usually PostgreSQL has access to only the installation directory and for all
35

the other locations on your PC, it may not have access, so it is safer to put the files in the installation
36

directory so that PostgreSQL can access them.
37

Now, let's run these commands one by one.
38

So I have run the create table command for customer to check out the table is created, I'll refresh it.
39

And I can see that the customer table is now created, but it does not have data right now, if you
40

run the select star for customer.
41

You will see that table is created, but it has no data inside it, also note that I have added this constraint
42

of primary key for customer ID, if you do not specify a primary key, the table, which is by default
43

created is un editable.
44

So to control the table, we have to specify the primary key for that table and for our customer table.
45

The primary key is the customer ID.
46

Similarly, I am creating the product table.
47

It has product ID as primary key, run it, refresh the tables and you will see a product table here.
48

Run the sales creation command, refresh the tables and you will find a sales table also.
49

Now I am selecting the copy command and running it.
50

You can see that the first copy command was successful.
51

So this copy command was for the customer table.
52

And now if I run the select start from customer.
53

You can see that the customer still has the data, similarly, we can run copy command for other tables
54

to import data in all the other tables.
55

So if nothing else works during restoration of database, the last option that we have is to create
56

each table individually and import data into each table using CSV files or text files. So these files
57

are also given in the resources if nothing else is working.
58

Try this out.