santhiya@santhiya-Mint-OS:~$ sudo -i -u postgres
[sudo] password for santhiya:
postgres@santhiya-Mint-OS:~$ psql
psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1))
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | en_IN | en_IN |
school | postgres | UTF8 | en_IN | en_IN |
template0 | postgres | UTF8 | en_IN | en_IN | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_IN | en_IN | =c/postgres +
| | | | | postgres=CTc/postgres
zoho | postgres | UTF8 | en_IN | en_IN |
(5 rows)
postgres=# \c school
You are now connected to database "school" as user "postgres".
school=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | students | table | postgres
(1 row)
school=# \dt students
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | students | table | postgres
(1 row)
school=# \d students
Table "public.students"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | |
name | character varying(20) | | |
school=# drop table students
school-# \dt
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | students | table | postgres
(1 row)
school-# drop table students:
school-# \dt
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | students | table | postgres
(1 row)
school-# drop table students;
ERROR: syntax error at or near "drop"
LINE 2: drop table students:
^
school=# drop table students:
school-# drop table students;
ERROR: syntax error at or near ":"
LINE 1: drop table students:
^
school=# \q
postgres@santhiya-Mint-OS:~$ psql
psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1))
Type "help" for help.
postgres=# \c school
You are now connected to database "school" as user "postgres".
school=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | students | table | postgres
(1 row)
school=# drop table students;
DROP TABLE
school=# \dt
Did not find any relations.
school=# create table students (s_id int not null ,name varchar(20) not null,course varchar not null);
CREATE TABLE
school=# copy students (s_id,name,course) from '/home/santhiya/Documents/sql/students.csv' DELIMITER ',' csv HEADER;
ERROR: extra data after last expected column
CONTEXT: COPY students, line 2: "1,vijay,,bsc"
school=# copy students (s_id,name,course) from '/home/santhiya/Documents/sql/students.csv' DELIMITER ',' csv HEADER;
COPY 3
school=# create table fee_status;
ERROR: syntax error at or near ";"
LINE 1: create table fee_status;
^
school=# create table fee_status(id int not null,status varchar , paid_date date);
CREATE TABLE
school=# copy students (s_id,name,course) from '/home/santhiya/Documents/payment.csv' DELIMITER ',' csv HEADER;
ERROR: null value in column "course" of relation "students" violates not-null constraint
DETAIL: Failing row contains (2, not paid, null).
CONTEXT: COPY students, line 3: "2,not paid,"
school=# copy students (s_id,name,course) from '/home/santhiya/Documents/payment.csv' DELIMITER ',' csv HEADER;
COPY 3
school=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | fee_status | table | postgres
public | students | table | postgres
(2 rows)
school=# select * from students;
s_id | name | course
------+----------+------------
1 | vijay | bsc
2 | kirba | bcom
3 | sudha | msc
1 | paid | 2021-04-01
2 | not paid | -
3 | paid | 2022-12-10
(6 rows)
school=# truncate table students;
TRUNCATE TABLE
school=# copy students (s_id,name,course) from '/home/santhiya/Documents/sql/students.csv' DELIMITER ',' csv HEADER;
COPY 3
school=# copy fee status (s_id,name,course) from '/home/santhiya/Documents/payment.csv' DELIMITER ',' csv HEADER;
ERROR: syntax error at or near "status"
LINE 1: copy fee status (s_id,name,course) from '/home/santhiya/Docu...
^
school=# copy fee_status (s_id,name,course) from '/home/santhiya/Documents/payment.csv' DELIMITER ',' csv HEADER;
ERROR: column "s_id" of relation "fee_status" does not exist
school=# select * from students;
s_id | name | course
------+-------+--------
1 | vijay | bsc
2 | kirba | bcom
3 | sudha | msc
(3 rows)
school=# copy fee_status (id,status,paid_date) from '/home/santhiya/Documents/payment.csv' DELIMITER ',' csv HEADER;
ERROR: invalid input syntax for type date: "-"
CONTEXT: COPY fee_status, line 3, column paid_date: "-"
school=# copy fee_status (id,status,paid_date) from '/home/santhiya/Documents/payment.csv' DELIMITER ',' csv HEADER;
COPY 3
school=# select * from fee_status;
id | status | paid_date
----+----------+------------
1 | paid | 2021-04-01
2 | not paid |
3 | paid | 2022-12-10
(3 rows)
school=# select name from students;
name
-------
vijay
kirba
sudha
(3 rows)
school=# select name,course from students;
name | course
-------+--------
vijay | bsc
kirba | bcom
sudha | msc
(3 rows)
school=# select * from students where course = 'msc';
s_id | name | course
------+-------+--------
3 | sudha | msc
(1 row)
school=# select name from students where course = 'msc';
name
-------
sudha
(1 row)
school=# select name from students where course = 'msc' or id =2;
ERROR: column "id" does not exist
LINE 1: select name from students where course = 'msc' or id =2;
^
school=# select name from students where course = 'msc' or s_id =2;
name
-------
kirba
sudha
(2 rows)
school=# insert into students values(4,'santhosh',1200),(5,'priya',1500);
INSERT 0 2
school=# select * from students;
s_id | name | course
------+----------+--------
1 | vijay | bsc
2 | kirba | bcom
3 | sudha | msc
4 | santhosh | 1200
5 | priya | 1500
(5 rows)
school=# update students set coure = 'mca' where s_id =4;
ERROR: column "coure" of relation "students" does not exist
LINE 1: update students set coure = 'mca' where s_id =4;
^
school=# update students set coure = 'msc' where s_id =4;
ERROR: column "coure" of relation "students" does not exist
LINE 1: update students set coure = 'msc' where s_id =4;
^
school=# update students set course = 'msc' where s_id =4;
UPDATE 1
school=# select * from students;
s_id | name | course
------+----------+--------
1 | vijay | bsc
2 | kirba | bcom
3 | sudha | msc
5 | priya | 1500
4 | santhosh | msc
(5 rows)
school=# update students set course = 'msc' where s_id =5;
UPDATE 1
school=# select * from students;
s_id | name | course
------+----------+--------
1 | vijay | bsc
2 | kirba | bcom
3 | sudha | msc
4 | santhosh | msc
5 | priya | msc
(5 rows)
school=# select * from students where course ='msc' and name='sudha';
s_id | name | course
------+-------+--------
3 | sudha | msc
(1 row)
school=# select * from students where course ='msc' or name='sudha';
s_id | name | course
------+----------+--------
3 | sudha | msc
4 | santhosh | msc
5 | priya | msc
(3 rows)
school=# select * from students where course ='msc' or name='priya';
s_id | name | course
------+----------+--------
3 | sudha | msc
4 | santhosh | msc
5 | priya | msc
(3 rows)
school=# select * from students where course ='msc' and name='priya';
s_id | name | course
------+-------+--------
5 | priya | msc
(1 row)
school=# select * from students where course ='mca' and name='priya';
s_id | name | course
------+------+--------
(0 rows)
school=# select * from students where name in ('vijay','sudha','priya');
s_id | name | course
------+-------+--------
1 | vijay | bsc
3 | sudha | msc
5 | priya | msc
(3 rows)
school=# select * from students where name not in ('vijay','sudha','priya');
s_id | name | course
------+----------+--------
2 | kirba | bcom
4 | santhosh | msc
(2 rows)
school=# select * from students where name like '%su';
s_id | name | course
------+------+--------
(0 rows)
school=# select * from students where name like '%su%';
s_id | name | course
------+-------+--------
3 | sudha | msc
(1 row)
school=# select * from students where name like '%a%';
s_id | name | course
------+----------+--------
1 | vijay | bsc
2 | kirba | bcom
3 | sudha | msc
4 | santhosh | msc
5 | priya | msc
(5 rows)
school=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | fee_status | table | postgres
public | students | table | postgres
(2 rows)
school=# select * from students;
s_id | name | course
------+----------+--------
1 | vijay | bsc
2 | kirba | bcom
3 | sudha | msc
4 | santhosh | msc
5 | priya | msc
(5 rows)
school=# select * from fee_status;
id | status | paid_date
----+----------+------------
1 | paid | 2021-04-01
2 | not paid |
3 | paid | 2022-12-10
(3 rows)
school=# select students.name,students.course,fee_status.status,fee_status.paid_date from studentsinner join fee_status on students.s_id = fee_status.id;
ERROR: relation "studentsinner" does not exist
LINE 1: ...ourse,fee_status.status,fee_status.paid_date from studentsin...
^
school=# select students.name,students.course,fee_status.status,fee_status.paid_date from students inner join fee_status on students.s_id = fee_status.id;
name | course | status | paid_date
-------+--------+----------+------------
vijay | bsc | paid | 2021-04-01
kirba | bcom | not paid |
sudha | msc | paid | 2022-12-10
(3 rows)
school=# select students.name,students.course,fee_status.status,fee_status.paid_date from students left join fee_status on students.s_id = fee_status.id;
name | course | status | paid_date
----------+--------+----------+------------
vijay | bsc | paid | 2021-04-01
kirba | bcom | not paid |
sudha | msc | paid | 2022-12-10
priya | msc | |
santhosh | msc | |
(5 rows)
school=# select students.name,students.course,fee_status.status,fee_status.paid_date from students right join fee_status on students.s_id = fee_status.id;
name | course | status | paid_date
-------+--------+----------+------------
vijay | bsc | paid | 2021-04-01
kirba | bcom | not paid |
sudha | msc | paid | 2022-12-10
(3 rows)
school=# select a.name,a.course,b.status,b.paid_date from students a,fee_status b where a.s_id=b.id;
name | course | status | paid_date
-------+--------+----------+------------
vijay | bsc | paid | 2021-04-01
kirba | bcom | not paid |
sudha | msc | paid | 2022-12-10
(3 rows)
school=# select a.name as students_name,a.course,b.status,b.paid_date from students a,fee_status b where a.s_id=b.id;
students_name | course | status | paid_date
---------------+--------+----------+------------
vijay | bsc | paid | 2021-04-01
kirba | bcom | not paid |
sudha | msc | paid | 2022-12-10
(3 rows)
school=# select students.name,students.course,fee_status.status,fee_status.paid_date from students left join fee_status on students.s_id = fee_status.id order by paid_date;
name | course | status | paid_date
----------+--------+----------+------------
vijay | bsc | paid | 2021-04-01
sudha | msc | paid | 2022-12-10
kirba | bcom | not paid |
priya | msc | |
santhosh | msc | |
(5 rows)
school=# select students.name,students.course,fee_status.status,fee_status.paid_date from students left join fee_status on students.s_id = fee_status.id ;
name | course | status | paid_date
----------+--------+----------+------------
vijay | bsc | paid | 2021-04-01
kirba | bcom | not paid |
sudha | msc | paid | 2022-12-10
priya | msc | |
santhosh | msc | |
(5 rows)
school=# select students.name,students.course,fee_status.status,fee_status.paid_date from students left join fee_status on students.s_id = fee_status.id order by name;
name | course | status | paid_date
----------+--------+----------+------------
kirba | bcom | not paid |
priya | msc | |
santhosh | msc | |
sudha | msc | paid | 2022-12-10
vijay | bsc | paid | 2021-04-01
(5 rows)
school=# select students.name,students.course,fee_status.status,fee_status.paid_date from students left join fee_status on students.s_id = fee_status.id order by name desc;
name | course | status | paid_date
----------+--------+----------+------------
vijay | bsc | paid | 2021-04-01
sudha | msc | paid | 2022-12-10
santhosh | msc | |
priya | msc | |
kirba | bcom | not paid |
(5 rows)
school=# select name,course,status,paid_date from students left join fee_status on students.s_id = fee_status.id;
name | course | status | paid_date
----------+--------+----------+------------
vijay | bsc | paid | 2021-04-01
kirba | bcom | not paid |
sudha | msc | paid | 2022-12-10
priya | msc | |
santhosh | msc | |
(5 rows)
school=# select name,course,status,paid_date from students left join fee_status on studen
school-# select name,course,status,paid_date from students left join fee_status on s_id = id;
ERROR: syntax error at or near "select"
LINE 2: select name,course,status,paid_date from students left join ...
^
school=# select course from students group by course;
course
--------
msc
bcom
bsc
(3 rows)
school=# select count(course), course from students group by course;
count | course
-------+--------
3 | msc
1 | bcom
1 | bsc
(3 rows)
school=# select count(*), course from students group by course;
count | course
-------+--------
3 | msc
1 | bcom
1 | bsc
(3 rows)
school=# select count(*) from students group by course;
count
-------
3
1
1
(3 rows)
school=# select count(*) from students;
count
-------
5
(1 row)
school=# select count(*) from students wher course = 'msc';
ERROR: syntax error at or near "course"
LINE 1: select count(*) from students wher course = 'msc';
^
school=# select count(*) from students where course = 'msc';
count
-------
3
(1 row)
school=# select sum(s_id) from students;
sum
-----
15
(1 row)
school=# alter table students add column fees int;
ALTER TABLE
school=# select * from students;
s_id | name | course | fees
------+----------+--------+------
1 | vijay | bsc |
2 | kirba | bcom |
3 | sudha | msc |
4 | santhosh | msc |
5 | priya | msc |
(5 rows)
school=# update students set fees = 20000;
UPDATE 5
school=# select * from students;
s_id | name | course | fees
------+----------+--------+-------
1 | vijay | bsc | 20000
2 | kirba | bcom | 20000
3 | sudha | msc | 20000
4 | santhosh | msc | 20000
5 | priya | msc | 20000
(5 rows)
school=# update students set fees = 40000 where s_id=3;
UPDATE 1
school=# select * from students;
s_id | name | course | fees
------+----------+--------+-------
1 | vijay | bsc | 20000
2 | kirba | bcom | 20000
4 | santhosh | msc | 20000
5 | priya | msc | 20000
3 | sudha | msc | 40000
(5 rows)
school=# select * from students order by s_id;
s_id | name | course | fees
------+----------+--------+-------
1 | vijay | bsc | 20000
2 | kirba | bcom | 20000
3 | sudha | msc | 40000
4 | santhosh | msc | 20000
5 | priya | msc | 20000
(5 rows)
school=# select sum(fees) from students;
sum
--------
120000
(1 row)
school=# select sum(fees) as total from students;
total
--------
120000
(1 row)
school=# select sum(fees)/5 from students;
?column?
----------
24000
(1 row)
school=# select sum(fees)/count(*) from students;
?column?
----------
24000
(1 row)
school=# select sum(fees)-2000 from students;
?column?
----------
118000
(1 row)
school=# select fees from students where s_id=1 - (select fees from students where s_id=3);
fees
------
(0 rows)
school=# select sum(fees) from students where s_id=1 or s_id=2;
sum
-------
40000
(1 row)
school=# select sum(fees) from students where s_id in (1,2);
sum
-------
40000
(1 row)
school=# select min(fees) from students;
min
-------
20000
(1 row)
school=# select max(fees) from students;
max
-------
40000
(1 row)
school=# select avg(fees) from students;
avg
--------------------
24000.000000000000
(1 row)
school=# select round( avg(fees),2) from students;
round
----------
24000.00
(1 row)
school=# select round( avg(fees),0) from students;
round
-------
24000
(1 row)
school=# select round( avg(fees)) from students;
round
-------
24000
(1 row)
school=# create function no_of_students()
returns integer as $ total$
declare
total integer;
begin
count(*) into total from students;
return total;
end
$total$ language plpgql;
ERROR: syntax error at or near "$"
LINE 2: returns integer as $ total$
^
ERROR: syntax error at or near "count"
LINE 3: count(*) into total from students;
^
ERROR: syntax error at or near "return"
LINE 1: return total;
^
school$# create function no_of_students()
returns integer as $total$
declare
total integer;
begin
select count(*) into total from students;
return total;
end
$total$ language plpgsql;
ERROR: syntax error at or near "$total$ language plpgql;
create function no_of_students()
returns integer as $total$"
LINE 3: $total$ language plpgql;
^
ERROR: syntax error at or near "select"
LINE 3: select count(*) into total from students;
^
ERROR: syntax error at or near "return"
LINE 1: return total;
^
school$# create function no_of_students()
returns integer as $total$
declare
total integer;
begin
select count(*) into total from students;
return total;
end
$total$ language plpgsql;
ERROR: syntax error at or near "$total$ language plpgsql;
create function no_of_students()
returns integer as $total$"
LINE 2: $total$ language plpgsql;
^
ERROR: syntax error at or near "select"
LINE 3: select count(*) into total from students;
^
ERROR: syntax error at or near "return"
LINE 1: return total;
^
school$# create function no_of_students()
returns integer as $total$
declare
total integer;
begin
select count(*) into total from students;
return total;
end
$total$ language plpgsql;
ERROR: syntax error at or near "$total$ language plpgsql;
create function no_of_students()
returns integer as $total$"
LINE 2: $total$ language plpgsql;
^
ERROR: syntax error at or near "select"
LINE 3: select count(*) into total from students;
^
ERROR: syntax error at or near "return"
LINE 1: return total;
^
school$# create function no_of_students()
returns integer as $total$
declare
total integer;
begin
select count(*) into total from students;
return total;
end
$total$ language plpgsql;
ERROR: syntax error at or near "$total$ language plpgsql;
create function no_of_students()
returns integer as $total$"
LINE 2: $total$ language plpgsql;
^
ERROR: syntax error at or near "select"
LINE 3: select count(*) into total from students;
^
ERROR: syntax error at or near "return"
LINE 1: return total;
^
school$# ERROR: syntax error at or near "$total$ language plpgsql;
create function no_of_students()
returns integer as $total$"
LINE 2: $total$ language plpgsql;
^
ERROR: syntax error at or near "select"
LINE 3: select count(*) into total from students;
^
ERROR: syntax error at or near "return"
LINE 1: return total;
ERROR: syntax error at or near "$total$ language plpgsql;
ERROR: syntax error at or near "$total$"
LINE 2: $total$ language plpgsql;
^
ERROR: unterminated quoted identifier at or near ""
LINE 2: "
LINE 2: returns integer as $total$"
^
ERROR: syntax error at or near "^"
LINE 1: ^
^
ERROR: syntax error at or near "^"
LINE 1: ^
^
school=# create function no_of_students()
returns integer as $$
declare
total integer;
begin
select count(*) into total from students;
return total;
end
$$ language plpgsql;
CREATE FUNCTION
school=# select count(*) from students;
count
-------
5
(1 row)
school=# select no_of_students();
no_of_students
----------------
5
(1 row)
school=#
Retrive:
It retrive all data.
select * from database_name;
eg:select * from students;
Column name:
It shows only contain in particular column by the column name.
select particular_column_name from database;
eg:select name from students;
It shows only contain in particular column by the column name(two or more column).
select particular_column_name,particular_column_name from database;
eg:select name,course from students;
To get only particular name:
select * from database where particular_column_name = 'your_wish_to_search';
eg:select * from students where course = ‘msc’;
Using “or” condition :
select particular_column_name from database_name where particular_column_name = 'your_wish_to_search' or your_wish_to_search ;
eg:select name from students where course = ‘msc’ or id =2;
Using “and” condition:
select * from database_name where particular_column_name ='your_wish_to_search' and particular_column_name='your_wish_to_search';
eg:select * from students where course =’msc’ and name=’priya’;
For multiple data to search:
Using “in”:
it shows searching data.
select * from database_name where particular_column_name in ('your_wish_to_search','your_wish_to_search','your_wish_to_search');
eg:select * from students where name in (‘vijay’,’sudha’,’priya’);
Using “not in”:
it shows data not available in search
select * from database_name where particular_column_name not in ('your_wish_to_search','your_wish_to_search','your_wish_to_search');
eg:select * from students where name not in (‘vijay’,’sudha’,’priya’);
To find name or anyone with starting letter:
Using “like”:
it shows all things which one is contained with given search
select * from database_name where particular_column_name like '%your_wish_to_search';
eg:select * from students where name like ‘%su’;
Inner join:
It join one table to another table using “on”.
select database_name.particular_column_name,database_name.particular_column_name,database_name.particular_column_name,database_name.particular_column_name from database_name inner join database_name on database_name.particular_column_name_comman_in_database = database_name.particular_column_name_comman_in_database;
eg:select students.name,students.course,fee_status.status,fee_status.paid_date from students inner join fee_status on students.s_id = fee_status.id;
It join one table to another table at left side using “left” & “on”.
select database_name.particular_column_name,database_name.particular_column_name,database_name.particular_column_name,database_name.particular_column_name from database_name left join database_name on database_name.particular_column_name_comman_in_database = database_name.particular_column_name_comman_in_database;
eg:select students.name,students.course,fee_status.status,fee_status.paid_date from students left join fee_status on students.s_id = fee_status.id;
It join one table to another table at right side using “right” & “on”.
select database_name.particular_column_name,database_name.particular_column_name,database_name.particular_column_name,database_name.particular_column_name from database_name right join database_name on database_name.particular_column_name_comman_in_database = database_name.particular_column_name_comman_in_database;
eg:select students.name,students.course,fee_status.status,fee_status.paid_date from students right join fee_status on students.s_id = fee_status.id;
Lies as:
Using “as”:
It changes name temporarely for show.
select a.Particular_column_name as Particular_column_name,a.Particular_column_name,b.Particular_column_name,b.Particular_column_name from changing_column_name a,changing_column_name b where a.s_matching_column_name=b.matching_column_name;eg:select a.name as students_name,a.course,b.status,b.paid_date from students a,fee_status b where a.s_id=b.id;
sankaa 
Leave a comment