More PostgreSQL Commands for freeCodeCamp Projects
From Learn SQL by Building a Student Database Part 2
·Jun 17, 2022·
4 min read
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! 😁👊
There are all sorts of ways to ...sort 😊 and specify query results
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.
ILIKEwill ignore case. So you don't need to specify capital or lower case.
ORconditions 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. add
DESC(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 BYquery. 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 specify
DESCwhile 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 put
ORDER BYor either of them before
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.
FLOOR: rounds results to the nearest whole number.
ROUNDsimply rounds to the nearest whole number. Add a comma and a number to round to a specified number of decimal places. e.g.
COUNT: how many entries in a table for the column. e.g.
SELECT COUNT(*) FROM majorscounts total rows and
SELECT COUNT(gpa) FROM studentscounts 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 to
SELECT <column_name> FROM <table> GROUP BY <column_name>. The advantage of using
GROUP BYis that it allows you to add any of the aggregate functions like
COUNTto 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>
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>)
Did you find this article valuable?
Support Eamonn Cottrell by becoming a sponsor. Any amount is appreciated!