SQL Keywords

Joins

The JOIN operation links across several tables as part of a SELECT operation, we must tell JOIN how to use the KEYS that make up the connections using an ON clause.

Here is a simple example

SELECT album.title, artist.name
FROM album
JOIN artist ON album.artist_id = artist.id;

Alter

The alter command can be used to make changes to the database schema after it has been created, such as changing the length of a datatype from SMALLINT to BIGINT or changing the length of the VARCHAR(x) to VARCHAR(y), it is a very useful command that helps us in maintenance, such as adding or removing columns.

Some examples:

ALTER TABLE fav DROP COLUMN test;

ALTER TABLE game ALTER COLUMN description TYPE TEXT;

Distinct

Using the DISTINCT keyword we can reduce the total result set by retrieving only the unique rows.

Using DISTINCT ON we can limit the duplicate removal to a specific column.

SELECT DISTINCT model FROM racing;

`

SELECT DISTINCT ON (model) make, model FROM racing;

Group By

Using the GROUP BY we can reduce the total rows by using aggregate functions to group certain rows.

Such as when we have duplicate values for a column, we can group those columns and count those values.

SELECT COUNT(abbrev), abbrev FROM pg_timezone_names GROUP BY abbrev;

GROUP BY always comes after the where clause/keyword.