Udemy Course Notes

Complete SQL
Bootcamp
Table of Contents
SELECT

SELECT DISTINCT

WHERE

PostgreSQL WHERE examples

LIMIT

IN Operator

PostgreSQL IN operator examples

NOT IN Operator

ORDER BY

PostgreSQL ORDER BY examples

BETWEEN

PostgreSQL BETWEEN operator examples

LIKE

GROUP BY

PostgreSQL GROUP BY with SUM function example

HAVING

Example

JOINS

SUBQUERY

CREATE TABLE and Constraints

PostgreSQL column constraints

PostgreSQL table constraints

1
PostgreSQL CREATE TABLE example

2
SELECT

Oneofthemostcommontasks,whenyouworkwithPostgreSQL,istoquerydatafrom

tablesbyusingthe [Link]
SELECT statementisoneofthemost
SELECT

[Link]

apowerfulquery.

Becauseofitscomplexity,wedividethePostgreSQL statementtutorialinto
SELECT

manyshorttutorialssothatyoucanlearneachclauseofthe statementeasier.
SELECT

Thefollowingaretheclausesthatappearinthe statement:
SELECT

SelectdistinctrowsbyusingDISTINCToperator.

FilterrowsbyusingWHEREclause.

SortrowsbyusingtheORDERBYclause.

SelectrowsbasedonvariousoperatorssuchasBETWEEN,INandLIKE.

GrouprowsintogroupsbyusingGROUPBYclause

ApplyconditionforgroupsbyusingHAVINGclause.

JointoanothertablebyusingINNERJOIN,LEFTJOIN,RIGHTJOIN

clauses.

3
Letsstartwithabasicformofthe [Link]
SELECT

followingillustratesthesyntaxofthe statement:
SELECT

1 SELECTcolumn_1,column_2,

2 FROMtable_name

Letsexaminethe statementinmoredetail:
SELECT

First,youspecifyalistofcolumnsinthetablefromwhichyouwanttoquery

datainthe [Link]
SELECT

[Link]

fromallcolumn,youcanuseanasterisk(*)astheshorthandforall

columns.

Second,youindicatethetablenameafterthe keyword
FROM

[Link] or
SELECT the
select

[Link],wewilluseSQLkeywordsinuppercasetomakethe

codeeasiertoreadandstandoutclearly.

4
SELECT DISTINCT
The clauseisusedintheSELECTstatementtoremoveduplicaterowsfroma
DISTINCT

[Link] [Link]
DISTINCT

usethe clauseononeormorecolumnsofatable.
DISTINCT

Thesyntaxof clauseisasfollows:
DISTINCT

1 SELECTDISTINCTcolumn_1

2 FROMtable_name

Ifyouspecifymultiplecolumns,the clausewillevaluatetheduplicatebased
DISTINCT

onthecombinationofvaluesofthosecolumns.

1 SELECTDISTINCTcolumn_1,column_2

2 FROMtbl_name

PostgreSQLalsoprovidesthe (expression)tokeepthefirstrowofeach
DISTINCTON

[Link]:

1 SELECTDISTINCTON(column_1),column_2

2 FROMtbl_name

3
ORDERBYcolumn_1,column_2

Theorderofrowsreturnedfromthe statementisunpredictablethereforethe
SELECT

[Link]

5
alwaysusethe clausewiththe
ORDERBY tomaketheresult
DISTINCTON(expression)

obvious.

Noticethatthe expressionmustmatchtheleftmostexpressioninthe
DISTINCTON

clause.
ORDERBY

6
WHERE
ThesyntaxofthePostgreSQL clauseisasfollows:
WHERE

1 SELECTcolumn_1,column_2column_n

2 FROMtable_name

3 WHEREconditions

The clauseappearsrightafterthe
WHERE clauseofthe
FROM [Link]
SELECT

conditionsareusedtofiltertherowsreturnedfromthe [Link]
SELECT

providesyouwithvariousstandardoperatorstoconstructtheconditions.

Thefollowingtableillustratesthestandardcomparisonoperators.

OPERATOR DESCRIPTION

= Equal

> Greaterthan

< Lessthan

>= Greaterthanorequal

7
 <= Lessthanorequal

<>or!= Notequal

AND LogicaloperatorAND

OR LogicaloperatorOR

Letspracticewithsomeexamplesofusingthe clausewithconditions.
WHERE

PostgreSQL WHERE examples

Ifyouwanttogetallcustomerswhosefirstnamesare ,youcanusethe
Jamie WHERE

clausewiththeequal(=)operatorasfollows:

1 SELECTlast_name,first_name

2 FROMcustomer

3 WHEREfirst_name='Jamie'

Ifyouwanttoselectthecustomerwhosefirstnameis andlastnamesis
Jamie ,you
rice

canusethe logicaloperatorthatcombinestwoconditionsasthefollowingquery:
AND

1 SELECTlast_name,first_name

2 FROMcustomer

8
3 WHEREfirst_name='Jamie'AND

4 last_name='Rice'

Ifyouwanttoknowwhopaidtherentalwithamountiseitherlessthan1USDorgreater

than8USD,youcanusethefollowingquerywith operator:
OR

1 SELECTcustomer_id,amount,payment_date

2 FROMpayment

3 WHEREamount<=1ORamount>=8

9
LIMIT
PostgreSQLLIMITisusedintheSELECTstatementtogetasubsetofrowsreturnedby

[Link]:

1 SELECT*

2 FROMTABLE

3 LIMITn

[Link]
n iszeroor
n ,it
NULL

producestheresultthatissameasomittingthe clause.
LIMIT

Incaseyouwanttoskipanumberofrowsbeforereturningrows,youuse
n OFFSET

clausefollowedbythe clauseasfollows:
LIMIT

1 SELECT*FROMtable

2 LIMITnOFFSETm

[Link]
m

zero,PostgreSQLwillbehavelikewithoutthe clause.
OFFSET

Becausetheorderoftherowsinthedatabasetableisunknownandunpredictable,

whenyouusethe clause,youshouldalwaysusethe
LIMIT clausetocontrol
ORDERBY

[Link],youwillgetanunpredictableresult.

10


IN Operator

Youusethe operatorintheWHEREclausetocheckifavaluematchesanyvaluein
IN

[Link] operatorisasfollows:
IN

1 valueIN(value1,value2,...)

Theexpressionreturnstrueifthevaluematchesanyvalueinthelisti.e.,value1,

value2,[Link]

resultsetofa statementasshowninthefollowingquery:
SELECT

1 valueIN(SELECTvalueFROMtbl_name)

Thestatementinsidetheparenthesesiscalledasubquery,whichisaquerynested

insideanotherquery.

PostgreSQL IN operator examples

the operatorintheWHEREclauseasfollows:
IN

1 SELECTcustomer_id,rental_id,return_date

2 FROMrental

3 WHEREcustomer_idIN(1,2)

11
 4 ORDERBYreturn_dateDESC

NOT IN Operator
Youcancombinethe operatorwiththe
IN operatortoselectrowswhosevaluesdo
NOT

[Link]

whosecustomeridisnot1or2.

1 SELECTcustomer_id,rental_id,return_date

2 FROMrental

3 WHEREcustomer_idNOTIN(1,2)

12
ORDER BY

Whenyouquerydatafromatable,PostgreSQLreturnstherowsintheorderthatthey

[Link],youusethe clause
ORDERBY

intheSELECTstatement.

The clauseallowsyoutosorttherowsreturnedfromthe
ORDERBY statementin
SELECT

ascendingordescendingorderbasedoncriteriaspecifiedbydifferentcriteria.

Thefollowingillustratesthesyntaxofthe clause:
ORDERBY

1 SELECTcolumn_1,column_2

2 FROMtbl_name

3 ORDERBYcolumn_1ASC,column_2DESC

Letsexaminethesyntaxofthe clauseinmoredetail:
ORDERBY

Specifythecolumnthatyouwanttosortinthe [Link]
ORDERBY

theresultsetbymultiplecolumns,useacommatoseparatebetweentwo

columns.

Use tosorttheresultsetinascendingorderand
ASC tosorttheresult
DESC

[Link],the clausewilluse
ORDERBY

bydefault.
ASC

13
LetstakesomeexamplesofusingthePostgreSQL clause.
ORDERBY

PostgreSQL ORDER BY examples

1 SELECTfirst_name,last_name

2 FROMcustomer

3 ORDERBYfirst_nameASC

14
BETWEEN
Weusethe [Link]
BETWEEN

illustratesthesyntaxofthe operator:
BETWEEN

1 valueBETWEENlowANDhigh

Ifthevalueisgreaterthanorequaltothelowvalueandlessthanorequaltothehigh

value,theexpressionreturnstrue,orviceversa.

Wecanrewritethe operatorbyusingthegreaterthanorequal(
BETWEEN )orless
>=

thanorequal( )operatorsasthefollowingstatement:
<=

1 value>=lowandvalue<=high

Ifwewanttocheckifavalueisoutofarange,weusethe operatoras
NOTBETWEEN

follows:

1 valueNOTBETWEENlowANDhigh

Thefollowingexpressionisequivalenttotheexpressionthatusesthe
NOTBETWEEN

operator:

1 value<lowORvalue>high

WeoftenusetheBETWEENoperatorintheWHEREclauseofaSELECT,INSERT,

UPDATEorDELETEstatement.

15
PostgreSQL BETWEEN operator examples
Letstakealookatthe tableinthesampledatabase.
payment

Thefollowingqueryselectsanypaymentwhoseamountisbetween8and9:

1 SELECTcustomer_id,payment_id,amount

2 FROMpayment

3 WHEREamountBETWEEN8AND9

16
 LIKE

Supposethestoremanagerasksyoufindacustomerthathedoesnotremember

[Link]

somethinglike .Howdoyoufindtheexactcustomerthatthestoremanageris
Jen

asking?Youmayfindthecustomerinthe tablebylookingatthefirst
customer

namecolumntoseeifthereisanyvaluethatbeginswith .Itiskindoftedious
Jen

becausetheremanyrowsinthe table.
customer

Fortunately,youcanusethePostgreSQL operatortoasthefollowingquery:
LIKE

1 SELECTfirst_name,last_name

2 FROMcustomer

3 WHEREfirst_nameLIKE'Jen%'

Noticethatthe clausecontainsaspecialexpression:the
WHERE ,the
first_name LIKE

operatorandastringthatcontainsapercent )character,whichisreferredasa
(%

pattern
.

Thequeryreturnsrowswhosevaluesinthefirstnamecolumnbeginwith andmay
Jen

[Link].

17
Youconstructapatternbycombiningastringwithwildcardcharactersandusethe
LIKE

or [Link]:
NOTLIKE

Percent()formatchinganysequenceofcharacters.
%

Underscore()formatchinganysinglecharacter.
_

18
GROUP BY
The clausedividestherowsreturnedfromtheSELECTstatementinto
GROUPBY

[Link],youcanapplyanaggregatefunctione.g.,tocalculatethesum

ofitemsorcountthenumberofitemsinthegroups.

Thefollowingstatementillustratesthesyntaxofthe clause:
GROUPBY

1 SELECTcolumn_1,aggregate_function(column_2)

2 FROMtbl_name

3 GROUPBYcolumn_1

The clausemustappearrightafterthe
GROUPBY or
FROM [Link]
WHERE

the [Link]
GROUPBY

putanexpressioninthe clause.
GROUPBY

PostgreSQL GROUP BY with SUM function example

The clauseisusefulwhenitisusedinconjunctionwithanaggregate
GROUPBY

[Link],togethowmuchacustomerhasbeenpaid,youusethe
GROUP

clausetodividethe
BY tableintogroupsforeachgroup,youcalculatethe
payments

totalamountsofmoneybyusingthe functionasthefollowingquery:
SUM

19
1 SELECTcustomer_id,

2 SUM(amount)

3 FROMpayment

4 GROUPBYcustomer_id

20
HAVING
Weoftenusethe clauseinconjunctionwiththeGROUPBYclausetofilter
HAVING

grouprowsthatdonotsatisfyaspecifiedcondition.

Thefollowingstatementillustratesthetypicalsyntaxofthe clause:
HAVING

1 SELECTcolumn_1,aggregate_function(column_2)

2 FROMtbl_name

3 GROUPBYcolumn_1

4 HAVINGcondition

The clausesetstheconditionforgrouprowscreatedbythe
HAVING clause
GROUPBY

afterthe clauseapplieswhiletheWHEREclausesetstheconditionfor
GROUPBY

individualrowsbefore [Link]
GROUPBY

and
HAVING clauses.
WHERE

InPostgreSQL,youcanusethe clausewithoutthe
HAVING [Link]
GROUPBY

case,the [Link],the
HAVING

listand
SELECT clausecanonlyrefertocolumnsfromwithinaggregate
HAVING

[Link] clauseis
HAVING

trueorzerorowifitisfalse.

21
Example

Youcanapplythe clausetoselectstheonlycustomerwhohasbeenspending
HAVING

morethan asthefollowingquery:
200

1 SELECTcustomer_id,

2 SUM(amount)

3
FROMpayment

4
GROUPBYcustomer_id

5
HAVINGSUM(amount)>200

22
JOINS
A full review of SQL JOINS is available online here:

[Link]

23
SUBQUERY

Asubqueryisaquerynestedinsideanotherquerysuchas and
SELECT,INSERT,DELETE

.Inthistutorial,wearefocusingonthe
UPDATE statementonly.
SELECT

Toconstructasubquery,weputthesecondqueryinbracketsanduseitintheWHERE

clauseasanexpression:

1 SELECTfilm_id,title,rental_rate

2 FROMfilm

3 WHERErental_rate>(

4 SELECTAVG(rental_rate)

5 FROMfilm)

[Link]

containsthesubqueryisknownasanouterquery.

PostgreSQLexecutesthequerythatcontainsasubqueryinthefollowingsequence:

First,executesthesubquery.

Second,getstheresultandpassesittotheouterquery.

Third,executestheouterquery.

24
CREATE TABLE and Constraints
TocreateanewtableinPostgreSQL,youusethe [Link]
CREATETABLE

followingillustratesthesyntaxofthe statement:
CREATETABLE

1 CREATETABLEtable_name(

2 column_nameTYPEcolumn_constraint,

3 table_constrainttable_constraint

4 )INHERITSexisting_table_name

Letsexaminethesyntaxofthe statementinmoredetail.
CREATETABLE

First,youspecifythenameofthenewtableafterthe clause.
CREATETABLE

The keywordisforcreatingatemporarytable,whichwewill
TEMPORARY

discussinthetemporarytabletutorial.

Next,youlistthecolumnname,itsdatatype,[Link]

canhavemultiplecolumnsinatable,eachcolumnisseparatedbya

comma(,).Thecolumnconstraintdefinestherulesforthecolumne.g.,

NOTNULL.

Then,afterthecolumnlist,youdefineatablelevelconstraintthatdefines

rulesforthedatainthetable.

Afterthat,[Link]

meansthenewtablecontainsallcolumnsoftheexistingtableandthe

25
 columnsdefinedinthe [Link]
CREATETABLE

extensiontoSQL.

PostgreSQLcolumnconstraints

ThefollowingarethecommonlyusedcolumnconstraintsinPostgreSQL:

NOTNULLthevalueofthecolumncannotbe .
NULL

UNIQUEthevalueofthecolumnmustbeuniqueacrossthewholetable.

However,thecolumncanhavemany valuesbecausePostgreSQL
NULL

treatseach [Link]
NULL

one valueinthecolumnthathasthe
NULL constraint.
UNIQUE

PRIMARYKEYthisconstraintisthecombinationof and
NOTNULL

[Link]
UNIQUE byusing
PRIMARYKEY

[Link],

youmustusethetablelevelconstraint.

[Link]

example,thevaluesinthe columnofthe
price tablemustbe
product

positivevalues.

REFERENCESconstrainsthevalueofthecolumnthatexistsinacolumn

[Link] todefinetheforeignkeyconstraint.
REFERENCES

26
PostgreSQL table constraints

Thetableconstraintsaresimilartocolumnconstraintsexceptthattheyareappliedto

theentiretableratherthantoanindividualcolumn.

Thefollowingarethetableconstraints:

UNIQUE(column_list)
toforcethevaluestoredinthecolumnslistedinside

theparenthesestobeunique.

PRIMARYKEY(column_list)
todefinetheprimarykeythatconsistsof

multiplecolumns.

CHECK(condition)
tocheckaconditionwheninsertingorupdatingdata.

REFERENCES
toconstrainthevaluestoredinthecolumnthatmustexistin

acolumninanothertable.

PostgreSQLCREATETABLEexample
Wewillcreateanewtablenamed thathasthefollowingcolumnswiththe
account

correspondingconstraints:

user_idprimarykey

usernameuniqueandnotnull

passwordnotnull

emailuniqueandnotnull

created_onnotnull

27
 last_loginnull

Thefollowingstatementcreatesthe table:
account

1 CREATETABLEaccount(

2 user_idserialPRIMARYKEY,

3 usernameVARCHAR(50)UNIQUENOTNULL,

4 passwordVARCHAR(50)NOTNULL,

5 emailVARCHAR(355)UNIQUENOTNULL,

6 created_onTIMESTAMPNOTNULL,

7 last_loginTIMESTAMP)

28