Original subtitles

0 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