sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql
sudo -u postgres psql
ALTER USER postgres PASSWORD 'Linux@root';
\q
$ psql -U postgres -h localhost
$ password for the user:'yourpasword'
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)
postgres=# \du
Role name | Attributes | Member of |
---|---|---|
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
--CREATE ROLE
CREATE USER avinash WITH CREATEDB LOGIN ENCRIPTED PASSWORD 'Linux@avinash';
--Show Users
\du
- 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 DATABASE db1;
- db1 is created
- 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
psql -U avinash -h localhost
$enter your pasword : 'Linux@avinash'
- Access the table of databse
\l
#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
#avinash=>
DROP DATABASE avi1;
- avi1 is the database of avinash hence we can delete it .
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.
- Add all the project repository to ubuntu installation because ubuntu admin does not come with pgadmin by default.
-
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
$ psql -U postgres -h localhost -p 5432 db1
- Enter to psql
$ psql -U postgres -h localhost
- Use db1 as database
postgres=> \c db1
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,
)
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 |
- to delete the table
DROP TABLE person;
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),
)
/*db1=>*/
INSERT INTO person(
first_name,
last_name,
gender,
date_of_birth)
VALUES('Avinash','Jha','Male',DATE '2000-05-21'
);
- it will execute each line inside
person.sql
in shell.
\i /home/avinashjha/Desktop/Projects/DBMS/PostgreSql/person.sql
- 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)
-
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 | gender | date_of_birth | |
---|---|---|---|---|---|
2 | Mikkel | Rutland | Male | 2000-10-11 | |
1 | Shaylah | Sabey | ssabey0@1688.com | Genderqueer | 2003-02-27 |
4 | Nanci | Gascard | ngascard3@blogspot.com | Agender | 2007-07-30 |
3 | Merwin | Pinks | mpinks2@example.com | 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 | gender | date_of_birth | |
---|---|---|---|---|---|
6 | Bianka | Mealiffe | bmealiffe5@nhs.uk | Genderfluid | 2002-08-17 |
9 | Flossy | Gallelli | fgallelli8@live.com | Male | 2003-01-11 |
8 | Kattie | Heatly | kheatly7@mit.edu | Genderfluid | 2013-08-15 |
7 | Lacee | Clausewitz | lclausewitz6@issuu.com | Male | 2017-06-25 |
(4 rows)
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)
SELECT * FROM person WHERE id<20 AND (gender = 'Female' or gender='Male');
id | first_name | last_name | gender | date_of_birth | |
---|---|---|---|---|---|
2 | Mikkel | Rutland | Male | 2000-10-11 | |
3 | Merwin | Pinks | mpinks2@example.com | Male | 2014-06-25 |
7 | Lacee | Clausewitz | lclausewitz6@issuu.com | Male | 2017-06-25 |
9 | Flossy | Gallelli | fgallelli8@live.com | Male | 2003-01-11 |
10 | Miranda | Lehenmann | mlehenmann9@nasa.gov | Male | 2000-08-05 |
12 | Tandi | Borland | tborlandb@businessinsider.com | Male | 2018-12-12 |
16 | Raff | Gini | rginif@so-net.ne.jp | Male | 2020-07-24 |
18 | Austin | Dennington | adenningtonh@simplemachines.org | Female | 2017-07-20 |
(8 rows)
<>
- NOT EQUAL TO<=
- less than equal to>=
- greater than equal to- Comparision can be done on any data types.
SELECT * FROM person LIMIT 5;
id | first_name | last_name | gender | date_of_birth | |
---|---|---|---|---|---|
1 | Shaylah | Sabey | ssabey0@1688.com | Genderqueer | 2003-02-27 |
2 | Mikkel | Rutland | Male | 2000-10-11 | |
3 | Merwin | Pinks | mpinks2@example.com | Male | 2014-06-25 |
4 | Nanci | Gascard | ngascard3@blogspot.com | Agender | 2007-07-30 |
5 | Jacquenetta | Klesl | jklesl4@marketwatch.com | Non-binary | 2016-05-12 |
(5 rows)
SELECT * FROM person OFFSET 5 LIMIT 5;
id | first_name | last_name | gender | date_of_birth | |
---|---|---|---|---|---|
6 | Bianka | Mealiffe | bmealiffe5@nhs.uk | Genderfluid | 2002-08-17 |
7 | Lacee | Clausewitz | lclausewitz6@issuu.com | Male | 2017-06-25 |
8 | Kattie | Heatly | kheatly7@mit.edu | Genderfluid | 2013-08-15 |
9 | Flossy | Gallelli | fgallelli8@live.com | Male | 2003-01-11 |
10 | Miranda | Lehenmann | mlehenmann9@nasa.gov | Male | 2000-08-05 |
(5 rows)
- Limit is not a official key word
SELECT * FROM person OFFSET 5 FETCH FIRST 5 ROW ONLY;
id | first_name | last_name | gender | date_of_birth | |
---|---|---|---|---|---|
6 | Bianka | Mealiffe | bmealiffe5@nhs.uk | Genderfluid | 2002-08-17 |
7 | Lacee | Clausewitz | lclausewitz6@issuu.com | Male | 2017-06-25 |
8 | Kattie | Heatly | kheatly7@mit.edu | Genderfluid | 2013-08-15 |
9 | Flossy | Gallelli | fgallelli8@live.com | Male | 2003-01-11 |
10 | Miranda | Lehenmann | mlehenmann9@nasa.gov | Male | 2000-08-05 |
(5 rows)
SELECT * FROM person WHERE gender = 'Male' OR gender = 'Female';
SELECT * FROM person WHERE gender IN ('Male','Female');
- 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 | gender | date_of_birth | |
---|---|---|---|---|---|
657 | Nanice | Shucksmith | nshucksmithi8@qq.com | Male | 2012-01-09 |
638 | Cody | Cuttell | ccuttellhp@typepad.com | Female | 2012-01-09 |
520 | Randa | Pietruszka | rpietruszkaef@exblog.jp | Bigender | 2012-01-10 |
423 | Ginger | Kitley | gkitleybq@dagondesign.com | Bigender | 2012-01-13 |
101 | Jerry | Caroli | jcaroli2s@wikispaces.com | Genderfluid | 2012-01-20 |
247 | Mariejeanne | Newbatt | Agender | 2012-02-13 | |
996 | Arleyne | Butner | Agender | 2012-02-24 | |
344 | Valentine | Screas | vscreas9j@gizmodo.com | Agender | 2012-03-09 |
(8 rows)
- select 5 row in which email ends with .com
SELECT * FROM person WHERE email LIKE '%.com' LIMIT 5;
id | first_name | last_name | gender | date_of_birth | |
---|---|---|---|---|---|
1 | Shaylah | Sabey | ssabey0@1688.com | Genderqueer | 2003-02-27 |
3 | Merwin | Pinks | mpinks2@example.com | Male | 2014-06-25 |
4 | Nanci | Gascard | ngascard3@blogspot.com | Agender | 2007-07-30 |
5 | Jacquenetta | Klesl | jklesl4@marketwatch.com | Non-binary | 2016-05-12 |
7 | Lacee | Clausewitz | lclausewitz6@issuu.com | Male | 2017-06-25 |
(5 rows)
- iLike ignores the upper case or Lower case
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)
- 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)
- 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 |
- 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)
- 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;
cwestcarc@discovery.com |
No email Provided |
No email Provided |
rginif@so-net.ne.jp |
jmarnerg@xing.com |
adenningtonh@simplemachines.org |
-
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)