Create Server:
Host:localhost
Port:5432
Maintenance DB:postgres
UN:
PWD:
Schema.TableName
Tools>Query To
Psql-terminal based front-end to the postgresql server
pgadmin-web based front end
DataTypes:
Boolean
Char(20), varchar(20)
Int, small int, serial,
Float(10), numeric(p,s)-numeric(5,2)-0.23
Date, time, timestamp
Commands:
create table accounts(user_id int, username varchar(20),created_on timestamp);
select * from accounts;
Single:
insert into accounts(user_id,username,created_on) values(1,'Tavant',current_timestamp);
Multiple:
insert into accounts(user_id,username,created_on) values
(1,'Tavant',current_timestamp),
(2,'Tavant Hyd',current_timestamp),
(3,'Tavant Bang',current_timestamp);
update accounts set username='TCH'where user_id=1;
delete from accounts where user_id=1;
-----
Create table from existing table:
select user_id,username into table copy_accounts from accounts where user_id=2;
select * from copy_accounts;
OR
create table new_accounts as
select username from accounts;
select * from new_accounts;
-----
create table fruits(id serial, name varchar(20));
insert into fruits(name) values('Apple'),('Pomegranate');
select * from fruits;
-----
alter table accounts
add column salary integer;
alter table accounts
drop column salary;
alter table accounts
drop column salary;
Change column name:
alter table accounts
rename column user_id to emp_id;
Change table name:
alter table accounts
rename to my_accounts;
Change column data type:
alter table accounts alter column user_id type varchar;
delete from accounts;
truncate table accounts;
drop table accounts;
Delete data can be rolled back:
delete from accounts;
rollback;
Dt:05-09-2023:-
create table employee(id integer,firstname varchar(20),
lastname varchar(20));
insert into employee values(1,'john','Dey'),
(2,'Deep','Sethi'),
(3,'Rak','Alex');
Concatanation:-----
select firstname || ' ' || lastname as fullname from employee;
select firstname,lastname from employee order by firstname asc,lastname desc;
select distinct firstname from employee;
Constraints:-----
create table student(id integer primary key, name varchar(20));
OR
PK with Constraint Name:-----
create table student(id integer constraint stu_id_pk primary key, name varchar(20));
insert into student values(1,'Adi'),
(1,'Subs');
Composite Key with Constraint Name:
create table student(id integer,
class integer,
name varchar(20),
constraint stu_id_cls_pk primary key(id,class)
);
insert into student values(1,10,'Adi'),
(1,10,'Subs');
select * from student;
Add Constraint in Existing Table:
alter table student
add constraint stu_id_cls_pk primary key(id,class);
Delete Constraint:-----
alter table student
drop constraint stu_id_cls_pk;
Foreign Key:-----
create table student_marks(stu_id int primary key,
stu_class integer,
marks integer,
constraint fk_st_marks foreign key(stu_id,stu_class) references student(id,class));
insert into student_marks values(1,10,100),
(2,10,90);
select * from student_marks;
Identity:-----
create table student_marks(stu_id int primary key generated always as identity,
stu_class integer,
marks integer,
constraint fk_st_marks foreign key(stu_id,stu_class) references student(id,class));
create table student_marks(stu_id int primary key generated by default as identity,
stu_class integer,
marks integer,
constraint fk_st_marks foreign key(stu_id,stu_class) references student(id,class));
insert into student_marks(stu_id,stu_class,marks) values(1,10,90),
(2,10,80);
select * from student_marks;
Not Null Constraint:-----
It is only Column level and can be added at the time of creation of table, but can not be added as alter table.
create table my_emp(id integer not null, name varchar(20));
insert into my_emp(id, name) values(null,'ABC');
select * from my_emp;
Unique Constraint:-----
create table my_emp(id integer unique, name varchar(20));
insert into my_emp(id, name) values(1,'XYZ');
insert into my_emp(id, name) values(null,'ABC');
insert into my_emp(id, name) values(null,'ASD');
select * from my_emp;
Dt:06-09-2023:-
Check Constraint:-----
create table stud_marks(id integer, name varchar(20),
marks integer check(marks>100 and id>10));
insert into stud_marks values(11,'subs',101);
select * from stud_marks;
--In,Between,Like
Dt:08-09-2023:
Joins:
create table fruits(id integer,name varchar(20));
insert into fruits values(1,'Apple'),
(2,'Grapes'),
(3,'Banana'),
(4,'Dragon');
create table vegetables(id integer, name varchar(20));
insert into vegetables values(1,'Carrot'),
(2,'Cucumber'),
(3,'Tomato'),
(5,'Bitroot');
Inner Join:
--select f.name fruit_name,v.name veg_name from fruits f
--inner join vegetables v on f.id=v.id;
--OR
select f.name fruit_name,v.name veg_name from fruits f
inner join vegetables v using(id);
Left Join:
select f.name fruit_name,v.name veg_name from fruits f
left join vegetables v on f.id=v.id;
Right Join:
select f.name fruit_name,v.name veg_name from fruits f
right join vegetables v on f.id=v.id;
full Join:
select f.name fruit_name,v.name veg_name from fruits f
full join vegetables v on f.id=v.id;
Self Join:
create table employees(emp_id integer,name varchar(20),manager_id integer);
insert into employees values(1,'John',2),
(2,'Jacob',3),
(3,'Deny',1),
(4,'Ben',null);
select e.name as emp_name,m.name as manager_name
from employees e inner join employees m on e.manager_id=m.emp_id;
Group By:
create table customer_order(id integer,name varchar(20),amount integer);
insert into customer_order values(1,'John',10000),
(1,'John',15000),
(2,'Jacob',20000),
(2,'Jacob',25000),
(3,'Ben',35000);
select name,sum(amount) as total_amount
from customer_order
group by name
Having sum(amount)>25000;
Union / Union all / Intersect:
select * from fruits
Union
select * from vegetables;
Dt:13-09-2023:
PL SQL/pgSQL:
Procedure:-----
create table accounts(id integer,name varchar(20)
,balance decimal(10,5));
insert into accounts values(1,'Raju',1000),
(2,'Thaj',2000);
select * from accounts;
create or replace procedure transfer_amount(
sender int,
receiver int,
amount dec
) language plpgsql
as $$
begin
update accounts set balance=balance-amount where id=sender;
update accounts set balance=balance+amount where id=receiver;
commit;
end;
$$;
call transfer_amount(1,2,1000);
select * from accounts;
Triggers & Functions:-----
Row level trigger & Statement level trigger
create table employees(id int,first_name varchar(20),last_name varchar(20));
create table audits(id int,last_name varchar(20),changed_on timestamp);
create or replace function log_details()
returns trigger
language plpgsql
as
$$
begin
if new.last_name<>old.last_name then
insert into audits(id,last_name,changed_on)
values(old.id,old.last_name,now());
end if;
return new;
end;
$$;
create trigger last_name_changed
before update
on employees
for each row
execute procedure log_details();
insert into employees(id,first_name,last_name)values(1,'John','Doe');
select * from employees;
update employees set last_name='Tavant' where id=1;
select * from audits;
View:-----
create or replace view aggregated_view
as
select e.first_name,a.balance from employees e
inner join accounts a on e.id=a.id;
select * from aggregated_view;
--We can not insert in the View
drop view aggregated_view;
No comments:
Post a Comment