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
PostgreSQL CREATE TABLE example
SELECT
Oneofthemostcommontasks,whenyouworkwithPostgreSQL,istoquerydatafrom
tablesbyusingthe
SELECT
[Link]
SELECT
statementisoneofthemost
[Link]
apowerfulquery.
Becauseofitscomplexity,wedividethePostgreSQL
SELECT
statementtutorialinto
manyshorttutorialssothatyoucanlearneachclauseofthe
SELECT
statementeasier.
Thefollowingaretheclausesthatappearinthe
SELECT
statement:
SelectdistinctrowsbyusingDISTINCToperator.
FilterrowsbyusingWHEREclause.
SortrowsbyusingtheORDERBYclause.
SelectrowsbasedonvariousoperatorssuchasBETWEEN,INandLIKE.
GrouprowsintogroupsbyusingGROUPBYclause
ApplyconditionforgroupsbyusingHAVINGclause.
JointoanothertablebyusingINNERJOIN,LEFTJOIN,RIGHTJOIN
clauses.
Letsstartwithabasicformofthe
SELECT
[Link]
followingillustratesthesyntaxofthe
SELECT
statement:
1 SELECTcolumn_1,column_2,
2 FROMtable_name
Letsexaminethe
SELECT
statementinmoredetail:
First,youspecifyalistofcolumnsinthetablefromwhichyouwanttoquery
datainthe
SELECT
[Link]
[Link]
fromallcolumn,youcanuseanasterisk(*)astheshorthandforall
columns.
Second,youindicatethetablenameafterthe
FROM
keyword
[Link]
SELECT
or
select
the
[Link],wewilluseSQLkeywordsinuppercasetomakethe
codeeasiertoreadandstandoutclearly.
SELECT DISTINCT
The
DISTINCT
clauseisusedintheSELECTstatementtoremoveduplicaterowsfroma
[Link]
DISTINCT
[Link]
usethe
DISTINCT
clauseononeormorecolumnsofatable.
Thesyntaxof
DISTINCT
clauseisasfollows:
1 SELECTDISTINCTcolumn_1
2 FROMtable_name
Ifyouspecifymultiplecolumns,the
DISTINCT
clausewillevaluatetheduplicatebased
onthecombinationofvaluesofthosecolumns.
1 SELECTDISTINCTcolumn_1,column_2
2 FROMtbl_name
PostgreSQLalsoprovidesthe
DISTINCTON
(expression)tokeepthefirstrowofeach
[Link]:
SELECTDISTINCTON(column_1),column_2
FROMtbl_name
ORDERBYcolumn_1,column_2
Theorderofrowsreturnedfromthe
SELECT
statementisunpredictablethereforethe
[Link]
alwaysusethe
ORDERBY
clausewiththe
DISTINCTON(expression)
tomaketheresult
obvious.
Noticethatthe
DISTINCTON
expressionmustmatchtheleftmostexpressioninthe
ORDERBY
clause.
WHERE
ThesyntaxofthePostgreSQL
WHERE
clauseisasfollows:
1 SELECTcolumn_1,column_2column_n
2 FROMtable_name
3 WHEREconditions
The
WHERE
clauseappearsrightafterthe
FROM
clauseofthe
SELECT
[Link]
conditionsareusedtofiltertherowsreturnedfromthe
SELECT
[Link]
providesyouwithvariousstandardoperatorstoconstructtheconditions.
Thefollowingtableillustratesthestandardcomparisonoperators.
OPERATOR
DESCRIPTION
Equal
>
Greaterthan
<
Lessthan
>=
Greaterthanorequal
<=
Lessthanorequal
<>or!=
Notequal
AND
LogicaloperatorAND
OR
LogicaloperatorOR
Letspracticewithsomeexamplesofusingthe
WHERE
clausewithconditions.
PostgreSQL WHERE examples
Ifyouwanttogetallcustomerswhosefirstnamesare
Jamie
,youcanusethe
WHERE
clausewiththeequal(=)operatorasfollows:
1 SELECTlast_name,first_name
2 FROMcustomer
3 WHEREfirst_name='Jamie'
Ifyouwanttoselectthecustomerwhosefirstnameis
Jamie
andlastnamesis
rice
,you
canusethe
AND
logicaloperatorthatcombinestwoconditionsasthefollowingquery:
1 SELECTlast_name,first_name
2 FROMcustomer
3 WHEREfirst_name='Jamie'AND
4 last_name='Rice'
Ifyouwanttoknowwhopaidtherentalwithamountiseitherlessthan1USDorgreater
than8USD,youcanusethefollowingquerywith
OR
operator:
1 SELECTcustomer_id,amount,payment_date
2 FROMpayment
3 WHEREamount<=1ORamount>=8
LIMIT
PostgreSQLLIMITisusedintheSELECTstatementtogetasubsetofrowsreturnedby
[Link]:
1 SELECT*
2 FROMTABLE
3 LIMITn
PostgreSQLreturns
n
[Link]
n
iszeroor
NULL
,it
producestheresultthatissameasomittingthe
LIMIT
clause.
Incaseyouwanttoskipanumberofrowsbeforereturning
n
rows,youuse
OFFSET
clausefollowedbythe
LIMIT
clauseasfollows:
1 SELECT*FROMtable
2 LIMITnOFFSETm
PostgreSQLfirstskips
m
[Link]
zero,PostgreSQLwillbehavelikewithoutthe
OFFSET
clause.
Becausetheorderoftherowsinthedatabasetableisunknownandunpredictable,
whenyouusethe
LIMIT
clause,youshouldalwaysusethe
ORDERBY
clausetocontrol
[Link],youwillgetanunpredictableresult.
10
IN Operator
Youusethe
IN
operatorintheWHEREclausetocheckifavaluematchesanyvaluein
[Link]
IN
operatorisasfollows:
1 valueIN(value1,value2,...)
Theexpressionreturnstrueifthevaluematchesanyvalueinthelisti.e.,value1,
value2,[Link]
resultsetofa
SELECT
statementasshowninthefollowingquery:
1 valueIN(SELECTvalueFROMtbl_name)
Thestatementinsidetheparenthesesiscalledasubquery,whichisaquerynested
insideanotherquery.
PostgreSQL IN operator examples
Supposeyouwanttoknowtherentalinformationofcustomerid1and2,youcanuse
the
IN
operatorintheWHEREclauseasfollows:
1 SELECTcustomer_id,rental_id,return_date
2 FROMrental
3 WHEREcustomer_idIN(1,2)
11
4 ORDERBYreturn_dateDESC
NOT IN Operator
Youcancombinethe
IN
operatorwiththe
NOT
operatortoselectrowswhosevaluesdo
[Link]
whosecustomeridisnot1or2.
1 SELECTcustomer_id,rental_id,return_date
2 FROMrental
3 WHEREcustomer_idNOTIN(1,2)
12
ORDER BY
Whenyouquerydatafromatable,PostgreSQLreturnstherowsintheorderthatthey
[Link],youusethe
ORDERBY
clause
intheSELECTstatement.
The
ORDERBY
clauseallowsyoutosorttherowsreturnedfromthe
SELECT
statementin
ascendingordescendingorderbasedoncriteriaspecifiedbydifferentcriteria.
Thefollowingillustratesthesyntaxofthe
ORDERBY
clause:
1 SELECTcolumn_1,column_2
2 FROMtbl_name
3 ORDERBYcolumn_1ASC,column_2DESC
Letsexaminethesyntaxofthe
ORDERBY
clauseinmoredetail:
Specifythecolumnthatyouwanttosortinthe
ORDERBY
[Link]
theresultsetbymultiplecolumns,useacommatoseparatebetweentwo
columns.
Use
ASC
tosorttheresultsetinascendingorderand
DESC
tosorttheresult
[Link],the
ORDERBY
clausewilluse
ASC
bydefault.
13
LetstakesomeexamplesofusingthePostgreSQL
ORDERBY
clause.
PostgreSQL ORDER BY examples
Thefollowingquerysortscustomersbythefirstnameinascendingorder:
1 SELECTfirst_name,last_name
2 FROMcustomer
3 ORDERBYfirst_nameASC
14
BETWEEN
Weusethe
BETWEEN
[Link]
illustratesthesyntaxofthe
BETWEEN
operator:
1 valueBETWEENlowANDhigh
Ifthevalueisgreaterthanorequaltothelowvalueandlessthanorequaltothehigh
value,theexpressionreturnstrue,orviceversa.
Wecanrewritethe
BETWEEN
operatorbyusingthegreaterthanorequal(
>=
)orless
thanorequal(
<=
)operatorsasthefollowingstatement:
1 value>=lowandvalue<=high
Ifwewanttocheckifavalueisoutofarange,weusethe
NOTBETWEEN
operatoras
follows:
1 valueNOTBETWEENlowANDhigh
Thefollowingexpressionisequivalenttotheexpressionthatusesthe
NOTBETWEEN
operator:
1 value<lowORvalue>high
WeoftenusetheBETWEEN
operatorintheWHEREclauseofaSELECT,INSERT,
UPDATEorDELETEstatement.
15
PostgreSQL BETWEEN operator examples
Letstakealookatthe
payment
tableinthesampledatabase.
Thefollowingqueryselectsanypaymentwhoseamountisbetween8and9:
1 SELECTcustomer_id,payment_id,amount
2 FROMpayment
3 WHEREamountBETWEEN8AND9
16
LIKE
Supposethestoremanagerasksyoufindacustomerthathedoesnotremember
[Link]
somethinglike
Jen
.Howdoyoufindtheexactcustomerthatthestoremanageris
asking?Youmayfindthecustomerinthe
customer
tablebylookingatthefirst
namecolumntoseeifthereisanyvaluethatbeginswith
Jen
.Itiskindoftedious
becausetheremanyrowsinthe
customer
table.
Fortunately,youcanusethePostgreSQL
LIKE
operatortoasthefollowingquery:
1 SELECTfirst_name,last_name
2 FROMcustomer
3 WHEREfirst_nameLIKE'Jen%'
Noticethatthe
WHERE
clausecontainsaspecialexpression:the
first_name
,the
LIKE
operatorandastringthatcontainsapercent
(%
)character,whichisreferredasa
pattern
.
Thequeryreturnsrowswhosevaluesinthefirstnamecolumnbeginwith
Jen
andmay
[Link].
17
Youconstructapatternbycombiningastringwithwildcardcharactersandusethe
LIKE
or
NOTLIKE
[Link]:
Percent(
%
)formatchinganysequenceofcharacters.
Underscore(
_
)formatchinganysinglecharacter.
18
GROUP BY
The
GROUPBY
clausedividestherowsreturnedfromtheSELECTstatementinto
[Link],youcanapplyanaggregatefunctione.g.,tocalculatethesum
ofitemsorcountthenumberofitemsinthegroups.
Thefollowingstatementillustratesthesyntaxofthe
GROUPBY
clause:
1 SELECTcolumn_1,aggregate_function(column_2)
2 FROMtbl_name
3 GROUPBYcolumn_1
The
GROUPBY
clausemustappearrightafterthe
FROM
or
WHERE
[Link]
the
GROUPBY
[Link]
putanexpressioninthe
GROUPBY
clause.
PostgreSQL GROUP BY with SUM function example
The
GROUPBY
clauseisusefulwhenitisusedinconjunctionwithanaggregate
[Link],togethowmuchacustomerhasbeenpaid,youusethe
GROUP
BY
clausetodividethe
payments
tableintogroupsforeachgroup,youcalculatethe
totalamountsofmoneybyusingthe
SUM
functionasthefollowingquery:
19
1 SELECTcustomer_id,
2 SUM(amount)
3 FROMpayment
4 GROUPBYcustomer_id
20
HAVING
Weoftenusethe
HAVING
clauseinconjunctionwiththeGROUPBYclausetofilter
grouprowsthatdonotsatisfyaspecifiedcondition.
Thefollowingstatementillustratesthetypicalsyntaxofthe
HAVING
clause:
1 SELECTcolumn_1,aggregate_function(column_2)
2 FROMtbl_name
3 GROUPBYcolumn_1
4 HAVINGcondition
The
HAVING
clausesetstheconditionforgrouprowscreatedbythe
GROUPBY
clause
afterthe
GROUPBY
clauseapplieswhiletheWHEREclausesetstheconditionfor
individualrowsbefore
GROUPBY
[Link]
HAVING
and
WHERE
clauses.
InPostgreSQL,youcanusethe
HAVING
clausewithoutthe
GROUPBY
[Link]
case,the
HAVING
[Link],the
SELECT
listand
HAVING
clausecanonlyrefertocolumnsfromwithinaggregate
[Link]
HAVING
clauseis
trueorzerorowifitisfalse.
21
Example
Youcanapplythe
HAVING
clausetoselectstheonlycustomerwhohasbeenspending
morethan
200
asthefollowingquery:
1 SELECTcustomer_id,
2 SUM(amount)
3
4
5
FROMpayment
GROUPBYcustomer_id
HAVINGSUM(amount)>200
22
JOINS
A full review of SQL JOINS is available online here:
[Link]
23
SUBQUERY
Asubqueryisaquerynestedinsideanotherquerysuchas
SELECT,INSERT,DELETE
and
UPDATE
.Inthistutorial,wearefocusingonthe
SELECT
statementonly.
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
CREATETABLE
[Link]
followingillustratesthesyntaxofthe
CREATETABLE
statement:
1 CREATETABLEtable_name(
2 column_nameTYPEcolumn_constraint,
3 table_constrainttable_constraint
4 )INHERITSexisting_table_name
Letsexaminethesyntaxofthe
CREATETABLE
statementinmoredetail.
First,youspecifythenameofthenewtableafterthe
CREATETABLE
clause.
The
TEMPORARY
keywordisforcreatingatemporarytable,whichwewill
discussinthetemporarytabletutorial.
Next,youlistthecolumnname,itsdatatype,[Link]
canhavemultiplecolumnsinatable,eachcolumnisseparatedbya
comma(,).Thecolumnconstraintdefinestherulesforthecolumne.g.,
NOTNULL.
Then,afterthecolumnlist,youdefineatablelevelconstraintthatdefines
rulesforthedatainthetable.
Afterthat,[Link]
meansthenewtablecontainsallcolumnsoftheexistingtableandthe
25
columnsdefinedinthe
CREATETABLE
[Link]
extensiontoSQL.
PostgreSQLcolumnconstraints
ThefollowingarethecommonlyusedcolumnconstraintsinPostgreSQL:
NOTNULLthevalueofthecolumncannotbe
NULL
.
UNIQUEthevalueofthecolumnmustbeuniqueacrossthewholetable.
However,thecolumncanhavemany
NULL
valuesbecausePostgreSQL
treatseach
NULL
[Link]
one
NULL
valueinthecolumnthathasthe
UNIQUE
constraint.
PRIMARYKEYthisconstraintisthecombinationof
NOTNULL
and
UNIQUE
[Link]
PRIMARYKEY
byusing
[Link],
youmustusethetablelevelconstraint.
[Link]
example,thevaluesinthe
price
columnofthe
product
tablemustbe
positivevalues.
REFERENCESconstrainsthevalueofthecolumnthatexistsinacolumn
[Link]
REFERENCES
todefinetheforeignkeyconstraint.
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
account
thathasthefollowingcolumnswiththe
correspondingconstraints:
user_idprimarykey
usernameuniqueandnotnull
passwordnotnull
emailuniqueandnotnull
created_onnotnull
27
last_loginnull
Thefollowingstatementcreatesthe
account
table:
1 CREATETABLEaccount(
2 user_idserialPRIMARYKEY,
3 usernameVARCHAR(50)UNIQUENOTNULL,
4 passwordVARCHAR(50)NOTNULL,
5 emailVARCHAR(355)UNIQUENOTNULL,
6 created_onTIMESTAMPNOTNULL,
7 last_loginTIMESTAMP)
28
