_edited_edited.png)
Introduction
A view is similar like the table in SQL ,it contains rows and columns, but it cant be stored in the disc like table.it will represent the data from the single source. We are going to see few things like how to create , update and delete the view in SQL.
Before going for that we need to open pgadmin for SQL and query tool then we need to create the table and update the table, so for that we are going to do some basic queries that are
Creating the table
insert into table
Create table
For creating the table the syntax are,
Create table table_name(
Column1 datatype,
Column2 datatype,..
);
Example query,
For creating the table. Here is some example,
create table marks(
Name varchar(255),
Maths int,
English int,
Science int
);
write this above query in the pgadmin and run it, and the screen looks like below one,
Then we need to check its created or not for that we need to write query like this,
Syntax
select * from table_name;
select * from marks;
And run this query and it will show the column name whatever we created here and its shown below,
Here we are able to see that we created column with the header,
Now we are going to insert values in the header,
Insert Definition - Insert values in appropriate columns.
Insert
There are two types of syntax available for inserting values,
If we are going to insert only for specified number of columns then we need to mention both the column names and the values like given below,
Syntax1
insert into table_name(column1, column2, column3,..)
values(value1, value2,value 3, ..);
The values and columns needs to be in the same order.
if we are going to insert all the column values, then we don't need to mention the column name in the syntax, and the syntax will look alike shown below,
Syntax 2
insert into table_name
values(value1,value 2, value 3,...);
Now we are going to insert the values by using the syntax, so here we are going to insert all the columns value so we are going to write a query by using the syntax 2,and the query looks alike the below one,
insert into marks
values('a', 99,100,78),
('b',55,68,80),
('c',98,58,99),
('d',78,92,93),
('e',81,45,100);
Here we need to write the varchar data type inside the single quotes and for the integer data type we dont need to write inside the quotes,
When we write the same query in the pgadmin we get the output looks alike the below image,
Once it got the success, then we need to check whether its added or not, for that we can run same query from the create table like,
select * from marks;
and the output shown in the below image,
Here we are able to see the columns which we created, then we are going to learn one more thing before going to the views.
Next we are going to see how to delete the table,
For that we can use drop or truncate ,
Drop
Drop table will delete the whole table with values.
Truncate
Truncate doesn't delete the whole table, it will delete the data's inside the table
Syntax
Drop
Drop table table_name;
Truncate
Truncate table table_name;
Now we are going to write the query for the drop and truncate, first we will go with the truncate, because if we drop, then all the data will be deleted we don't have any reason to truncate,
Truncate
truncate table marks;
then we run the query and the result is shown below,
After we got success from running ,we need to check the truncate query by the below comment,
select * from marks;
while running this comment the output is shown like a below image,
Here we are able to see the table is still in, but there is no data. This is the use of truncate.
Then here the drop comment goes,
drop table marks;
and the our query run successfully like below,
then the output will be seen by using the query,
select * from marks;
and the output is shown below,
Here this output shows there are no table named marks exist.
so while using the drop query, then we need to be very careful, once we run it, then the whole table will be gone. Now we got some basic understanding about to create insert and dropping. Now we will move on with the view,
we are going to see the create, update and delete view,
Create View Syntax
The syntax for the views in SQL is given below,
create view view_name as
select column1 , column2, column 3,....
from table_name
where condition;
and the query is,
create view high__marks as
select Name, English, Maths, Science
from marks
where English>90;
We are going to run the query and the query ran successfully and that window is shown below,
We have the old table which we created for the create table, from that table i need the students name whose English marks are greater than 90 and i want to view that students alone, In that situation we can use that view comment, and then same as usual we use the select query to check the output,
select * from high__marks;
and the output is shown below,
Here we got the name whose English marks is greater than 90.
Conclusion
we can do the same things for the two tables, we can use the views to view from two tables ,its just the basic introduction for views .We are going to see more about views in my next blog.
References
Thank you!
  Â
Commenti