inserting records in the database, and querying them

creating complex tables having many columns inside a database and working some of the basic operations on them

let we see step by step

1) creating database

create database company

2) connect with database

\c company

3) creating tables

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

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

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

Now, we are going to see more in-depth interactivity with table columns

to insert values in the table first we use the keyword insert, it will trigeres we are going to insert some thing in the table and then we want to define the desigination where we are going to insert values, here we created a employee table for practice, into employee but until now we yet not define what values we are actually going insert in the desigination, so for values we want to give them inside () for each colmuns, here (123, 'Kavin', 'Software Engineer','DB',25000)
are the values for respective columns we want to insert them.

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 in the employee table

select * form employee 

NOTE: some of mistakes from not form

select * form employee 

also it give hint to where we are going our mistakes

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?
first can we able to do it?, yes postgres database allow as to do it

it is not complex just query we just want to contineouly insert them after one another with comma seperation

this is the example query to insert multiple values in the employee table

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 define several values seperated by the , it will all takes as the next next rows.

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

if we use values for all the records it will lead us to happening error

To verify our multiple values inerstion

select * from employee 

it gives us all the values we inserted the employee table

selecting some columns form the table

to select only specific column we do not want to use * it will lead us to get all the values form our table instead if we specify only columns what we want

for example to get only the name column values

select name from employee 

to get more then one columns name, designation

select name, designation from employee  

try out with different column names

select name, dept form employee 

suppose if we want get distint values form a columns means how to get it, for getting distinct values from a columns we can use the distinct keyword before the columns name

select distinct dept from employee;

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

select distinct designation from employee;

select distinct dept, designation from employee;

where clause

we want to select a name with some conditions means how to do it ?

using postgres can we able to select a name with some conditions, yes for doing this is where where clause came and helps to selection with specific conditions

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

select * from employee where dept = 'DB';

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

trying some more with different coditions

select * from employee 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 employee where dept = 'Team Lead';

count(*) is a function it will count the result results and returen the the count value in the name of the count column

select count(*) from employee where dept = 'DB';

AND

adding more conditions

using and keyword

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

select name from employee where dept = 'Front End' and designation = 'Team Lead';

select * from employee where dept ='AI' and designation = 'Software Engineer';

using or keyword

select * from employee where dept = 'DB' or designation = 'Team Lead';

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

alias

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

select dept from employee;

here we are getting the actual column name in the database

suppose if we want to rename it form dept to Department is it possible ?
yes but one thing is postgres is non case sensitive so it will conside all of the upper cases into lower cases

select dept as  Department from employee;

select dept as  DEPARTMENT from employee;

now the dept is renames as the department or DEPARTMENT even though if we given in letter with upper cases it converts all of them into lower cases

we can see that in the result the Department and DEPARTMENT is converted into lower case `department

can we use this more then one column

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

`sql
select name as empname, dept as Department from employee;
`

Not equals to:

!= , <> , not are used for the not equals

`sql
select * from employee where dept <> 'DB';
`

`sql
select * from employee where dept != 'DB';
`

`sql
select * from employee where not dept = 'DB';
`

we can able to see for all this != , <> , not are giving the same result

`sql
select * from employee where not dept <> 'DB';
`

between

`sql
select * from employee 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 ?

`sql
select * from employee where not salary between 19000 and 35000;
`

can we between is only works for numeric values or can we ablet to do it other data types. postgres allow user to use for the char, text, data and more types

for example let we see how to use it for varchar type

`sql
select name from employee where name between 'A' and 'K';
`

`sql0
select name from employee where name between 'a' and 'k';
`

it does not care about lower cases

using in

`sql
select * from employee where dept = 'DB' or dept = 'AI';
`

`sql
select * from employee where dept = 'DB' or dept = 'Front End' or dept = 'AI';
`

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

`sql
select * from employee where dept in ( 'DB', 'Front End' , 'AI');
`

`sql
select * from employee where dept in ('Front End' , 'AI');
`

`sql
select * from employee where dept not in ( 'DB', 'Front End' );
`

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

`sql
select * from employee where dept in not( 'DB', 'Front End' , 'AI');
`

order by

i we want to sort the value in a specify core we can use the order by
it will work for almost all types in the postgres

`sql
select * from employee order by dept
`

here it automatically identify the type of the dept column and sort it by alphabe wise

`sql
select * from employee order by empid
`

at this time it sort the empid by assending order

`sql
select * from employee order by salary
`

`sql
select * from employee order by name
`

similarly for the salary and name columns

Can we able to do order by multiple columns, yes

`sql
select * from employee order by name, salary;
`

`sql
select * from employee order by name, dept;
`

it will first select the column by and then it will order the second column by having the same values

what is the default order by ?

Suppose if we want to order decenting means how can I able to do it?
for doing the decenting order can use the desc

`sql
select * from employee order by salary desc;
`

let we do how to do one ascending and descenting order

`sql
select * from employee order by salary desc, dept asc;
`

what is the differences between the both queries

`sql
select * from employee order by salary asc, dept desc;
`

`sql
select * from employee order by dept asc, salary desc;
`

limit

`sql
select * from employee order by salary limit 3;
`

it will give the first three;

incase if we want the last three means how can we able to do it

`sql
select * from employee order by salary desc limit 3;
`

like

`sql
select * from employee where name like 'a%';
`

`sql
select * from employee where name like 'a%';
`

'a%' will select name that stating with letter a
'%a' will select name that ending with letter a

for select the name a in between the words

`sql
select * from employee where name like '%n';
`

`sql
select * from employee where name like '%l';
`

`sql
select * from employee where name like '%a%';
`

`sql
select * from employee where name like '%a%a%';
`

suppose if I want to select the number, data and can I use the regex ?