SQL with PostgreSQL - Getting Started

Overview
● History
● Concept
● Existing technologies

Basic entities in relational databases
ID Column1 Column2 Column3
1 datum1.
1
datum2.
1
datum3.
1
2 datum1.
2
datum2.
2
datum3.
2
3 datum1.
3
datum2.
3
datum3.
3
4 datum1.
4
datum2.
4
datum3.
4
Tables
● Int
● Varchar(size)
● Date
● Double
● Datetime
● And many
more
Data Types
● One → One
● One → Many
● Many → Many
● Self Referencing
Connections

Steps to install the DB
● Step 1: enter this site postgres
● Step 2: choose the Os you are working on
● Step 3: click on download the installer under Interactive installer by EnterpriseDB
● Step 4: download the dmg file (mac) exe file (windows) of version 10
● Step 5: follow the installer when it asks you for password choose something you can
remember. IF YOU FORGET THIS PASSWORD YOU’LL NEED TO REMOVE ALL
THE COMPONENTS FROM YOUR COMPUTER AND IT IS A MESS
● Step 6: open pgadmin (it is already installed during the postgres installation)

pgAdmin - Connecting to a server
21
3

pgAdmin - Creating new DB
1 2 3

pgAdmin - Creating a new table
1 2

Creating a new table - Live example

pgAdmin - Import data from csv
1 2 3

Import data from csv - Live example

SQL (Structured Query Language)

Basic query
select column1, column2, … from tableName
● The * - selects all columns in a table
● The keyword as - used for giving aliases to columns or tables
● The keyword distinct - returns all distinct values of a table
● The keyword top x|percent - returns x or x percent top results
● The keyword limit x - limits the number of results returned
from a query to x

Example
select id, fName as first_name, lName, age, from person
id fName lName age Country City Phone_Number
1 Sammy Blumberg 43 Israel Jerusalem +972528534467
2 Diana Smith 60 Italy Rome +390611532846
3 Rachel Wilson 24 France Paris +33523449285

Result
id first_name lName age
1 Sammy Blumberg 43
2 Diana Smith 60
3 Rachel Wilson 24
select id, fName as first_name, lName, age, from person

Order Results
order by column1, column2, …
● Numbers instead of column names
● The keywords asc (default) and desc

Example
select id,fName, lName, age, from person
order by Age
id fName lName age
1 Sammy Blumberg 43
2 Diana Smith 60
3 Rachel Wilson 24
id fName lName age
3 Rachel Wilson 24
1 Sammy Blumberg 43
2 Diana Smith 60

Conditions
where (not) condition1 (andor + (not)) condition2 ...
● Conditions with operators - =, >, <, >=, <=, <>
○ Name = ‘Rachel’
○ Age >= 23
● Conditions with like keyword - coulmn(x) like pattern
○ lName like ‘%berg’ - any name that ends with berg
● The in keyword - columnX in (dataset)
○ Age in (23,43,67,80,92,13,15,6)
● The between keyword - columnX between a and b
○ Age between 12 and 30

Operators
● = - equals
● <> (!=) - not equals
● > - greater thane
● < - less than
● >= - greater than equals
● <= less than equals

Wildcards
● 'a%' - Finds any values that starts with "a"
● '%a' - Finds any values that ends with "a"
● '%or%’ - Finds any values that have "or" in any position
● '_r%' - Finds any values that have "r" in the second position
● 'a_%_%' - Finds any values that starts with "a" and are at least 3 characters in length
● 'a%o' - Finds any values that starts with "a" and ends with "o"
● [charlist] - Defines sets and ranges of characters to match
○ [abs]
○ [a-d]
● [^charlist] or [!charlist] - Defines sets and ranges of characters NOT to match
○ [!bef]
○ [!g-r]

Example
select fName from person
where lName = ‘Wilson’ and age > 30
id fName lName age
1 Sammy Blumberg 43
2 Diana Smith 60
3 Rachel Wilson 24
4 Tomy Wilson 32
fName
Tomy

Group by + Conditions
group by coulmn1, column2
having (not) condition1 (andor + (not)) condition2 …
Conditions in having sentence affect only groups and not the entire
dataset. To add condition on the entire dataset use the where
clause.

Example
select fName, lName from person where age > 24
group by country having lName like ‘B%’ order by Age desc
id fName lName age country
1 Sammy Blumberg 43 USA
2 Diana Smith 60 UK
3 Rachel Wilson 24 USA
4 Jacque Brel 30 France
5 Mike Brant 50 USA
6 Claire Bakingham 37 UK
fName lName
Mike Brant
Sammy Blumberg
Claire Bakingham
Jacque Brel

Joining information
select t1.column1, t1.column2, t2.column1, t2.column2, t2.column3...
from t1 [A] (first table)
Join t2 [B] (second table) on t1.columnX = t2.columnX
● (Inner) join - Returns records that have matching values in both tables
● Left join - Return all records from the left table, and the matched records from
the right table
● Right join - Return all records from the right table, and the matched records
from the left table
● Full outer join - Return all records when there is a match in either left or right
table

Join Types
Full Outer JoinRight joinLeft joinInner join
TableA TableA TableATableBTableB TableBTableA TableB

Example
select p.fName, p.lName, cd.email
from person p
join contact_details cd p.id = cd.person_id
where fname = ‘Rachel’
id fName lName
1 Sammy Blumberg
2 Diana Smith
3 Rachel Wilson
4 Rachel Rosenberg
id person_id email phone
1 3 rw@gmail.com 0542221307
2 2 ds@gmail.com 0521342555
3 1 sb@gmail.com 0503078242
4 4 rr@gmail.com 0545557731

Result
select p.fName, p.lName, cd.email
from person p
join contact_details cd p.id = cd.person_id
where fname = ‘Rachel’
fName lName email
Rachel Wilson rw@gmail.com
Rachel Rosenberg rr@gmail.com

Subqueries
● Each query returns a table
● Each query manipulates the data in one or more tables
● Hence we can run queries on results of other queries
● These “other queries” are called subqueries

Example
select p.fName as first_name, p.eyesColor as eyes_color
from person p
where p.eye_color in (select color from colors where
colorTag = ‘eyes’)
id fName eyesColor
1 Sammy blue
2 Diana green
3 Rachel blue
4 Danielle brown
id color colorTag code
1 blue eyes #13c3ff
2 green eyes #32c512
3 brown hair #673a07
4 black skin #000000

Result
id first_name eyesColor
1 Sammy blue
2 Diana green
3 Rachel blue
select p.fName as first_name, p.eyesColor as eyes_color
from person p
where p.eye_color in (select color from colors where
colorTag = ‘eyes’)

Functions
Select [columns…], function(columnX), [columns…] from
tableName
● Count() - counts the number of rows that returned from the query.
Doesn’t count nulls
● Max() - gives the maximal value in a column
● Min() - gives the minimum value in a column
● Avg() - gives the average value of all values in a column
● Sum() - sums up all values in a column
● More...

Example
select Count(*) as num_of_people, Avg(age) as average_age from person
where country = ‘USA’
id fName lName age country
1 Sammy Blumberg 43 USA
2 Diana Smith 60 UK
3 Rachel Wilson 24 USA
4 Jacque Brel 30 France
5 Mike Brant 50 USA
6 Claire Bakingham 37 UK
num_of_people average_age
3 51

SQL with PostgreSQL - Getting Started

SQL with PostgreSQL - Getting Started

More Related Content

Similar to SQL with PostgreSQL - Getting Started

Recently uploaded

SQL with PostgreSQL - Getting Started