installation In Linux

Create the file repository configuration:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Import the repository signing key:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Update the package lists:

sudo apt-get update

Install the latest version of PostgreSQL.

If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':

sudo apt-get -y install postgresql

Configure Postgresql

sudo -u postgres psql

We need to reset the root password for the user

ALTER USER postgres PASSWORD 'Linux@root';

After resetting the password come out

\q

Now to enter psql as super user postgres

$ psql -U postgres -h localhost
$ password for the user:'yourpasword'

After Entering Password we will enter in postgres shell

\l for list of database

postgres=# \l

List of databases

Name	Owner	Encoding	Collate	Ctype	Access privileges
postgres	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

(3 rows)

\du - List of users and their roles

postgres=# \du

Role name	Attributes	Member of
postgres	Superuser, Create role, Create DB, Replication, Bypass RLS	{}

Create another user name Avinash

--CREATE ROLE
CREATE USER avinash WITH CREATEDB LOGIN  ENCRIPTED PASSWORD 'Linux@avinash';
--Show Users
\du

Create a dabase by name of user

• it will contain all the info about the user

CREATE DATABASE avinash;

Role name	Attributes	Member of
avinash	Create DB	{}
postgres	Superuser, Create role, Create DB, Replication, Bypass RLS	{}

--to exit
postgres=# \lq

Create A database

CREATE DATABASE db1;

• db1 is created

Grant Access to db1 for user avinash

• only super user has access to all the databses
• normal user have to be granted for the databse

GRANT ALL PRIVILEGES ON DATABASE db1 TO avinash;

• avinash has now full access to db1

Enter To psql as normal user avinash

psql -U avinash -h localhost
$enter your  pasword : 'Linux@avinash'

• Access the table of databse

\l

Create a databse

#avinash=>
CREATE DATABASE avi1;

Name	Owner	Encoding	Collate	Ctype	Access privileges
avi1	avinash	UTF8	en_IN	en_IN
avinash	postgres	UTF8	en_IN	en_IN
db1	postgres	UTF8	en_IN	en_IN
postgres	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

• look at the owner of the created databse
  • avinash is the owner

Drop avi1 from databse

#avinash=>
DROP DATABASE avi1;

• avi1 is the database of avinash hence we can delete it .

Drop db1

avinash=> DROP DATABASE db1;

Eroor: must be owner of database db1

• A lesser privilleged user is not allowe to do any thing in the databse.
• It is good thing for the security of databse.

Installation of Pgadmin4

• Add all the project repository to ubuntu installation because ubuntu admin does not come with pgadmin by default.

Status Of Database

• to check the status

  sudo service postgresql status

• To Stop

  sudo service postgresql stop

• to start

  sudo service postgresql start

• To restart

  sudo service postgresql restart

Connect to the Database

1. Connect Directly from the terminal to db1

$ psql -U postgres -h localhost -p 5432 db1

2. Enter to psql then select db1

• Enter to psql

$ psql -U postgres -h localhost

• Use db1 as database

postgres=> \c db1

Create Table with psql

CREATE TABLE table_name(
  Column name + data type + contains if any
)

Example

CREATE TABLE person(
  id int,
  first_name VARCHAR(20),
  last_name VARCHAR(20),
  gender VARCHAR(6),
  date_of_birth TIMESTAP,
)

Describe the table

db1=> \d

• List of relations
  

  Schema	Name	Type	Owner
  public	person	table	postgres
  (1 row)

db1=> \d person

• Table "public.person"
  

Column	Type	Collation	Nullable	Default
id	integer
first_name	character varying(20)
last_name	character varying(20)
gender	character varying(6)
date_of_birth	date

Drop Table

• to delete the table

DROP TABLE person;

Create table with constrains

CREATE TABLE person(
  id int NOT NULL,
  first_name VARCHAR(20) NOT NULL,
  last_name VARCHAR(20) NOT NULL,
  gender VARCHAR(6) NOT NULL,
  date_of_birth TIMESTAP NOT NULL,
  email  VARCHAR(100),
)

insert Data into table

/*db1=>*/
INSERT INTO person(
first_name,
last_name,
gender,
date_of_birth)
VALUES('Avinash','Jha','Male',DATE '2000-05-21'
);

Read SQL Files From the directory

• it will execute each line inside person.sql in shell.

\i /home/avinashjha/Desktop/Projects/DBMS/PostgreSql/person.sql

Query The Info

Query the data from person table

• Select All Rows

  SELECT * FROM person;

• Select Last name of all person

SELECT (first_name,last_name) as Full_Name FROM person where id<5;

full_name
(Shaylah,Sabey)
(Mikkel,Rutland)
(Merwin,Pinks)
(Nanci,Gascard)

(4 rows)

Order By

• 1 2 3 4 5 ASC

• 5 4 3 2 1 DESC

• Sort a data based on date of birth

SELECT * FROM person WHERE id<5 ORDER BY date_of_birth asc;

id	first_name	last_name	email	gender	date_of_birth
2	Mikkel	Rutland		Male	2000-10-11
1	Shaylah	Sabey	[email protected]	Genderqueer	2003-02-27
4	Nanci	Gascard	[email protected]	Agender	2007-07-30
3	Merwin	Pinks	[email protected]	Male	2014-06-25

(4 rows)

• sort a table based don name

SELECT * FROM person WHERE id>5 AND id<10 ORDER BY first_name asc;

id	first_name	last_name	email	gender	date_of_birth
6	Bianka	Mealiffe	[email protected]	Genderfluid	2002-08-17
9	Flossy	Gallelli	[email protected]	Male	2003-01-11
8	Kattie	Heatly	[email protected]	Genderfluid	2013-08-15
7	Lacee	Clausewitz	[email protected]	Male	2017-06-25

(4 rows)

Distinct

SELECT gender From person where id<5 ORDER BY gender;

gender
Agender
Agender
Agender
Female

• Here Value are repeating and not for goode purpose hence we should use distinct

SELECT DISTINCT gender as list_of_gender From person where id<20 ORDER BY gender;

list_of_gender
Agender
Female
Genderfluid
Genderqueer
Male
Non-binary
Polygender

(7 rows)

Where | And | Or

SELECT * FROM person WHERE id<20 AND (gender = 'Female' or gender='Male');

id	first_name	last_name	email	gender	date_of_birth
2	Mikkel	Rutland		Male	2000-10-11
3	Merwin	Pinks	[email protected]	Male	2014-06-25
7	Lacee	Clausewitz	[email protected]	Male	2017-06-25
9	Flossy	Gallelli	[email protected]	Male	2003-01-11
10	Miranda	Lehenmann	[email protected]	Male	2000-08-05
12	Tandi	Borland	[email protected]	Male	2018-12-12
16	Raff	Gini	[email protected]	Male	2020-07-24
18	Austin	Dennington	[email protected]	Female	2017-07-20

(8 rows)

Comparision operators

• <> - NOT EQUAL TO
• <= - less than equal to
• >= - greater than equal to
• Comparision can be done on any data types.

Limit, Offset and Fetch

LIMIT - select in limit

 SELECT * FROM person LIMIT 5;

id	first_name	last_name	email	gender	date_of_birth
1	Shaylah	Sabey	[email protected]	Genderqueer	2003-02-27
2	Mikkel	Rutland		Male	2000-10-11
3	Merwin	Pinks	[email protected]	Male	2014-06-25
4	Nanci	Gascard	[email protected]	Agender	2007-07-30
5	Jacquenetta	Klesl	[email protected]	Non-binary	2016-05-12

(5 rows)

OFFSET - After that select

SELECT * FROM person OFFSET 5 LIMIT 5;

id	first_name	last_name	email	gender	date_of_birth
6	Bianka	Mealiffe	[email protected]	Genderfluid	2002-08-17
7	Lacee	Clausewitz	[email protected]	Male	2017-06-25
8	Kattie	Heatly	[email protected]	Genderfluid	2013-08-15
9	Flossy	Gallelli	[email protected]	Male	2003-01-11
10	Miranda	Lehenmann	[email protected]	Male	2000-08-05

(5 rows)

• Limit is not a official key word

Fetch

SELECT * FROM person OFFSET 5 FETCH FIRST 5  ROW ONLY;

id	first_name	last_name	email	gender	date_of_birth
6	Bianka	Mealiffe	[email protected]	Genderfluid	2002-08-17
7	Lacee	Clausewitz	[email protected]	Male	2017-06-25
8	Kattie	Heatly	[email protected]	Genderfluid	2013-08-15
9	Flossy	Gallelli	[email protected]	Male	2003-01-11
10	Miranda	Lehenmann	[email protected]	Male	2000-08-05

(5 rows)

IN

SELECT * FROM person WHERE gender = 'Male' OR gender = 'Female';

SELECT * FROM person WHERE gender IN ('Male','Female');

Between

• Select Data from a range

SELECT * FROM person WHERE date_of_birth BETWEEN DATE '2012-01-01'AND '2012-04-01' ORDER BY date_of_birth asc;

id	first_name	last_name	email	gender	date_of_birth
657	Nanice	Shucksmith	[email protected]	Male	2012-01-09
638	Cody	Cuttell	[email protected]	Female	2012-01-09
520	Randa	Pietruszka	[email protected]	Bigender	2012-01-10
423	Ginger	Kitley	[email protected]	Bigender	2012-01-13
101	Jerry	Caroli	[email protected]	Genderfluid	2012-01-20
247	Mariejeanne	Newbatt		Agender	2012-02-13
996	Arleyne	Butner		Agender	2012-02-24
344	Valentine	Screas	[email protected]	Agender	2012-03-09

(8 rows)

Like and iLike

• select 5 row in which email ends with .com

SELECT * FROM person WHERE email LIKE '%.com' LIMIT 5;

id	first_name	last_name	email	gender	date_of_birth
1	Shaylah	Sabey	[email protected]	Genderqueer	2003-02-27
3	Merwin	Pinks	[email protected]	Male	2014-06-25
4	Nanci	Gascard	[email protected]	Agender	2007-07-30
5	Jacquenetta	Klesl	[email protected]	Non-binary	2016-05-12
7	Lacee	Clausewitz	[email protected]	Male	2017-06-25

(5 rows)

• iLike ignores the upper case or Lower case

Group By

SELECT gender,count(*) From person GROUP BY gender;

gender	count
Genderqueer	115
Bigender	127
Genderfluid	121
Male	133
Polygender	120
Non-binary	129
Female	140
Agender	115

(8 rows)

GROUP BY HAVING

• Select gender having count greater than 120

SELECT gender,count(*) From person GROUP BY gender HAVING COUNT(*)>120 ;

gender	count
Bigender	127
Genderfluid	121
Male	133
Non-binary	129
Female	140

(5 rows)

Aggregate Functions

• Car with Minimum price

SELECT * FROM car WHERE price=(SELECT  MIN(price) FROM car);

id	make	model	price
100	Pontiac	GTO	1001.00

(1 row)

• Car With Maximum Price

 SELECT * FROM car WHERE price=(SELECT  MAX(price) FROM car);

id	make	model	price
707	Buick	LeSabre	4997.00

(1 row)

• Average Price of the car

SELECT AVG(price) From car;

avg
2988.9120000000000000

(1 row)

• Round Function

 SELECT ROUND(AVG(price)) From car;

round
2989

(1 row)

• Limiting precision

 SELECT ROUND(AVG(price),2) From car;

round
2988.91

(1 row)

• Sum of all car's price

SELECT SUM(price) From car;

sum
2988912.00

(1 row)

• Select brand wise sum

SELECT make,SUM(price) FROM car GROUP BY make;

make	sum
Ford	239439.00
Smart	8067.00
Maserati	21880.00
Dodge	157526.00

Basic Arithmatic Operator

• alias - AS is an alias which is use to override the default name.

• Let's say we are offering 10% discount in price of car then Write a query that returns original price and dicount and discounted price.

SELECT id,make,model,price,(price*.10) as discount,(price-(price*.1)) as final_price FROM car Limit 10;

id	make	model	price	discount	final_price
1	Infiniti	QX	2959.00	295.9000	2663.100
2	Lincoln	Continental	2247.00	224.7000	2022.300
3	Lincoln	Continental Mark VII	3077.00	307.7000	2769.300
4	Oldsmobile	Alero	2689.00	268.9000	2420.100
5	Nissan	Xterra	4051.00	405.1000	3645.900
6	Nissan	Frontier	3800.00	380.0000	3420.000
7	Dodge	Neon	2564.00	256.4000	2307.600
8	Infiniti	G	2699.00	269.9000	2429.100
9	Infiniti	QX	4763.00	476.3000	4286.700
10	Volkswagen	Passat	2636.00	263.6000	2372.400

(10 rows)

Coalesce

• It returns the first valid(Not Null) value from the argument passed.
• To use default value in the null palaces.

SELECT COALESCE(null,0,1) as First_valid_value;

first_valid_value
0

(1 row)

• Example - In the person database for those who does not have email we want to add email is not provide then.

---
SELECT COALESCE(email,'No email Provided') FROM person;

email
[email protected]
No email Provided
No email Provided
[email protected]
[email protected]
[email protected]

NULLIF

• Division by zero through an error

• To handle division by zero we use NULLIF

• NULLIF Takes two argument NULLIF(a,b);

  • if a = b then value is NULL
  • else a

  SELECT 10/0;

  ERROR: division by zero

  SELECT 10/NULLIF(10,5);

  ?column?
  1

  (1 row)

  SELECT 10/NULLIF(5,10);

  ?column?
  2

  (1 row)

  SELECT 10/NULLIF(10,10);

  ?column?
  --

  (1 row)

  SELECT COALESCE(10/NULLIF(10,10),0);

  coalesce
  0

  (1 row)