Data selections

Why PostgreSQL is ORDBMS ?

How to now we saw how to create and connect with database, creating tables, today we are going to see more in-depth interactivity with table columns

create table employee(
empid integer, 
name varchar(25), 
designation varchar(25), 
dept varchar(25), 
salary integer);

Note do not forget to differentiate each columns by by comma,

\h to helps

\d employee

\d will gives the structure of the table

to insert values in the table first we use the keyword insert, it will trigeres we are going to insert some thing and then we want to define the desigination we are going to insert values in the employee table but but to now we yet not define what we are actually going that desigination, here we want to give all the values for the column.

insert into employee values(123, 'Kavin', 'Software Engineer','DB',25000);

insert into employee values(124, 'Viyan', 'Software Engineer', 'AI',27000);

to verifiy our values are inserted or not

select * form employees 

up to now we are actually insert only one values suppose if we want to insert multiple values means how can we able to do it?

TODO

To study more this we can insert values from files directly

does the postgres reads files, then what kind of files it will read, does it reads json, csv
does it reads values form the another database,
dose it reads from the DBMS, RDBMS?

for just now we will see how to insert multiple values in the query

insert into employee values(101, 'Arul', 'Team Lead', 'Front End', 35000), (101, 'Agaran', 'Team Lead', 'DB', 45000);

here we are define more then one row in a single query,

after the values keywords if we use several values seperated by the , it will all takes as the next next rows.

To verify our multiple values inerstion

select * from employees 

selecting some columns form the table

select name from employees 

select name, designation from employees  

select name, dept form employees 

suppose if we want to select a name with some conditions means how to it

can we select an name with some conditions this kind of this is where clause, it helps to selection with conditions

suppose if we wan to change the table means how can we able to do it?
Can we able to change the values with the existing values(rows and columns )

yes we can change the table name with vales?

Alter is the keyword helps to change the table name

alter table employee rename to employees;

alter is the keyword we are actually going to change and we are actually specifing what we are actually going to change by using the keyword table and saying which table by the name of the table as employee and finally using the rename keyword is what kind of thing we are to do in the table rename to employees, this rename the tables into employee form employees

different kind of selections

select distinct designation from the employees

the distinct keyword will help to select distinct unique values form the table

but

select designation from the employees

will select all the values in the column

note it is an interview question ?

where clause

it helps to filter the quering the values with more then one conditions

select * from employees where dept = 'DB';

it will select those who all are having the values DB at the dept columns

some of the another examples

select * from employees where designation = 'Team Lead';

suppose of we want to count the queirng result can we able to do it?
Yes for counting the queries we can use count

select count(*) from employees where dept = 'Team Lead';

some of the question are delete , drop and truncate

adding more conditions

using and keyword

select * from employees where dept ='DEV' and designation = 'Manager';

using or keyword

select * from employees where dept = '' or designation = '';

TODO

Redundancy data

primary key
foreign key
candidate key
unique key

TODO

RAID DB

alias

suppose if you want to rename the quering column name, we can do it by using the as

select dept from employees

select dept as  Department from employees 

now the dept is renames as the Department

but in the result the Department is converted into lower case

in this way can we able to rename more then one one columns
yes, let we see that

select name as empname, dept as  Department from employees 

Not equals to:
!= , <>, not are used for the not equals

select * from employees where dept <> 'DB';

select * from employees where dept != 'DB';

select * from employees where not dept = 'DB';

this three method are used to select not equals

select * from employees where not dept <> 'DB';

between

select * from employees where salary between 19000 and 35000;

does this means can we only able to use it only for the integer ranges?
What are the other data types we can be able to use between ?

select * from employees where not salary between 19000 and 35000;

using in

select * from employees where dept = 'DB' or dept = 'FE';  

select * from employees where dept = 'DB' or dept = 'FE' or dept = 'AI'; 

instead of using check the value one by one can use to check the value in a range of items

select * from employees where dept in ( 'DB', 'FE' , 'AI'); 

select * from employees where dept not in ( 'DB', 'FE' , 'AI'); 

what will happen if we change the not in to in not

order by

select * from employees order by dept