More PostgreSQL Commands for freeCodeCamp Projects
From Learn SQL by Building a Student Database Part 2
Table of contents
More Commands ๐
We're creating more bash files to do some of the heavy lifting and querying for us. Here are a list of commands used in Learn SQL by Building a Student Database: Part 2. They'll be useful to (definitely) my future self and (hopefully) you! ๐๐
Conditions โ
There are all sorts of ways to ...sort ๐ and specify query results
LIKE
&&NOT LIKE
: use this to query a table and find matches to certain criteria.SELECT * FROM courses WHERE course LIKE '_lgorithms'
; will match any row with a word that has exactly one letter before the 'lgorithms' part....LIKE '%lgorithms'
will not constrain it to one letter in front of the 'lgorithms' part. So%
lets anything come before.ILIKE
will ignore case. So you don't need to specify capital or lower case.AND
andOR
conditions will enable you to combine query criteria. e.g.SELECT * FROM courses WHERE course LIKE '% %' AND course NOT ILIKE '%b%';
. This will select rows with a space in it and without a capital or lowercase 'b' in it.echo -e
: this lets you use the escape character like '\n' to create a newline.IS NULL
: let's you query fields that are blank or empty. e.g.SELECT <column_name> FROM <table> WHERE <column> IS NULL
.IS NOT NULL
: let's you query fields that are not blank or empty.ORDER BY <column_name>
: specifies the order in which results are displayed. Defaults to ascending (ASC
) order. addDESC
(descending) to the end of the query to reverse that displayed order. e.g.SELECT * FROM <table> ORDER BY <column> DESC
.- To order by multiple columns, add those columns separated by commas at the end of the
ORDER BY
query. e.g....ORDER BY <column_name1>,<column_name2>
. Now, if there are matching values from ordering by column1, they will order by column2. if you specifyDESC
while using multiple ordering columns, you will do that immediately following each column name in the query.ORDER BY <column_name1> DESC, <column_name2> DESC
. LIMIT
: limits the returned number of rows from a query. The order of keywords matters in the query, too: You cannot putLIMIT
beforeORDER BY
or either of them beforeWHERE
.
Mathematics โ
MIN
&&MAX
: two of many mathematic functions. MIN & MAX find the lowest or highest values, respectively, in a column. e.g.SELECT MIN(<column_name>) FROM <table>
SUM
: sums the values of a column.AVG
: averages the values in a column.CEIL
&&FLOOR
: rounds results to the nearest whole number.CEIL
rounds up,FLOOR
rounds down.ROUND
simply rounds to the nearest whole number. Add a comma and a number to round to a specified number of decimal places. e.g.SELECT ROUND(AVG(major_id),5)
COUNT
: how many entries in a table for the column. e.g.SELECT COUNT(*) FROM majors
counts total rows andSELECT COUNT(gpa) FROM students
counts only those rows with non-null values in the gpa column.DISTINCT
: shows only the unique values from a query. e.g.SELECT DISTINCT(<column_name>) FROM <table>
.GROUP BY
: works similarly toDISTINCT
. e.g.SELECT <column_name> FROM <table> GROUP BY <column_name>
. The advantage of usingGROUP BY
is that it allows you to add any of the aggregate functions likeMIN
,MAX
, andCOUNT
to get more info from your results.SELECT COUNT(*) FROM <table> GROUP BY <column_name>
will count the number of distinct column values in that query.HAVING
: use at the end of a query where condition must be an aggregate function with a test. e.g.HAVING COUNT(*) > 0
AS
: rename a column. e.g.SELECT <column> AS <new_column_name>
JOINS
We tackled only FULL JOINS (and only those briefly) in my initial list of commands here. As you might imagine, there's plenty more to do with JOINS.
LEFT JOIN
: gets all the rows from the left table (the table mentioned first (furthest to the left on the line) in the query). e.g.SELECT * FROM students LEFT JOIN majors ON students.major_id = majors.major_id
RIGHT JOIN
: same thing only returns rows from right table.INNER JOIN
: combines the two above. Will return rows from LEFT table if they have a RIGHT table counterpart and visa versa.USING
: keyword you can use in the event that the foreign key has the same name in each table. e.g.SELECT * FROM <table_1> FULL JOIN <table_2> USING(<column>)
ย