Currently learning Data Science, here is what I've learnt so far. Pt.2

This time I learned about queries for data retrieval, feel free to read my notes and make any comments on tips, corrections or suggestions. Thx! :)

Rename columns (AS)

SELECT column_name AS 'column_rename'
FROM table_name;

SELECT DISTINCT column_name
FROM table_name;

DISTINCT
is used to return unique values in the output. It filters out all duplicate values in the specified column(s).

SELECT column_name
FROM table
WHERE condition(w/ operator);

The WHERE clause filters the result set to only include rows where the following condition is true.

SELECT * 
FROM table_name
WHERE column_name LIKE 'pattern';

LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column.

SELECT * 
FROM movies
WHERE name LIKE 'pattern%';

% is a wildcard character that matches zero or more missing characters in the pattern.

Unknown values are indicated by NULL.
conditional:

IS NULL
IS NOT NULL

SELECT column_name
FROM table_name 
WHERE other_column_name IS NOT NULL;

The BETWEEN operator is used in a WHERE clause to filter the result set within a certain range. It accepts two values that are either numbers, text, or dates.

SELECT column_name
FROM table_name
WHERE other_column_name BETWEEN value1 AND value2;

AND is used to combine multiple conditions in a WHERE clause

SELECT column_name
FROM table_name
WHERE other_column_name1 BETWEEN value1 AND value2;
   AND other_column_name2 = value3;

OR operator can also be used to combine multiple conditions in
WHERE. It is met if any of the conditions is true.

SELECT column_name
FROM table_name
WHERE other_column_name1 > value1
   OR other_column_name2 = value2;

ORDER BY - sort the results, alphabetically or numerically
DESC - descending order
ASC - ascending order
Note: ORDER BY always goes after WHERE (if WHERE is present).

SELECT column_name
FROM table_name
WHERE condition
ORDER BY other_column_name DESC/ASC;

LIMIT - max number of rows the result set will have.

CASE - create different outputs (usually in the SELECT statement). (if-then logic)

SELECT column_name,
 CASE
  WHEN condition THEN 'output'
  WHEN condition THEN 'output'
  ELSE 'output'
 END
FROM table_name;

Comments

[DEV SUPPORTS]

You need to verify your account.

tr.ee/dev-to