All language subtitles for 1. Restore and Back-up

Hi everyone
1

Till now
2

We have learnt how to create a database
3

Delete a database, create a table inside a database
4

And within tables we know how to insert data
5

Update data
6

Delete data
7

And change the structure of the table
8

Now it is time
9

That we
10

Learn the quering part of the SQL
11

So to start with that
12

We'll need a
13

Good database
14

If you look at the resources part of this lecture
15

There is a tar file attached
16

I suggest you download it and you restore the database from the tar file, I will show you how
17

How to do that
18

Once you have downloaded the file
19

You go to the database path
20

Right click on it
21

And create a new database
22

Let us name the databases supermart DB
23

Underscore DB and save it
24

This will be creating a blank database
25

And within this database we will restore the
26

Tables that we have in the TAR file that I have shared with you
27

If I right click on this database
28

And select
29

Restore
30

The format is tar
31

And filename, you have to select the file that you have downloaded
32

I'll go to the path where I have kept my file
33

So, Remember that
34

Since, it is showing only backup format files
35

You will not be able to see the file that I have shared, you have to change this format to all files
36

And then you can see the tar file, select the file
37

And a click on restore
38

Click on restore
39

It will take few seconds to restore the whole database
40

Successfully completed, so if you open the database, you refresh
41

The database first
42

And you go to the schemas public
43

And if you look at the tables
44

This database has three tables
45

One is the customer table which contains customer data
46

To look at what is contained in this table
47

Will open the query tool
48

And then write select star you know this so just right select star
49

From table name
50

Which is customer
51

And run this query
52

This is the data in customer table
53

It contains customer ID, customer name
54

Segment, age
55

The country to which the customer belongs, city and state of customer residence
56

After this there are postal codes and the region to which customer belongs, also you can see the
57

Type of data that each column is containing so age is an integer, whereas country
58

Is a variable character length and the postal code is a bigint
59

If you do this
60

For the product table
61

We'll write select star from product
62

We'll get a list of products, these have product ids, the category to which the product belongs, the
63

Subcategory to which product belongs and the name of the product
64

And the third table is called sales. So select star from sales
65

So let us run this Select star from sales
66

So these are the sales orders
67

So Order line
68

Is the first column
69

And then come the order ID, order date, shipping date
70

Shipping mode, customer ID so this is the ID of the customer who ordered this particular
71

Sales item
72

And this is a product ID of the
73

Product which was ordered
74

Then comes the sales amount that is the quantity
75

Then is the discount offered
76

And finally the profit that we received
77

So, If you see
78

This three tables
79

Constitutes a database of a supermart. So this Supermarket Store has some customers
80

It is storing the date of the customers with itself
81

This has some products it is storing the data of products in a product table
82

Everyday whichever sales are made, it is storing which customer bought that product and what
83

What was the product ID. So this sales table has
84

Order line as the primary key
85

Each order has a unique order line
86

And within that order line
87

There is an order ID
88

With that order ID, there is a customer ID and a product ID associated
89

Using this customer ID you can see the details of the customer
90

In the customer table
91

And using this product ID you can see the details of the product in the product table
92

And if you look at the first two
93

Order line
94

The order ID is same, what this means is
95

In the first order
96

There were two product ordered
97

So the product ID is different that's why there are two different lines
98

So, Order ID can be duplicate that is why it is not a primary key
99

But order line is unique
100

And that is why it is the primary key
101

For customer table
102

The customer ID is the primary key and for the product table product ID is the primary key
103

So our database is ready with you
104

You
105

Restored this Database from a file
106

How to create that file
107

You need to create a backup of your database to create such file
108

To create a backup
109

You will need to go to the database
110

Right click on it
111

And click on backup
112

Give a filename here
113

And select the format, I selected Tar format
114

And click on backup
115

This give me that file which I have shared with you in the resources
116

From now on all the queries we will be writing will be on this database
117

It is very important that you have these three tables with you
118

So that whenever we write you can write with us and check that you get the same result
119

So in this lecture you learnt
120

How to create a backup and restore a database from the backup file
121

With the restore database, we will be now
122

Practicing the query commands