DEV Community: n de la cruz

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

n de la cruz — Sun, 11 Oct 2026 04:35:34 +0000

Hey there! I'm getting used to posting here and really enjoying using this space to post my notes while learning. This time I learned about aggregates and how to structure my SQL queries when using them. Feel free to comment any tips, corrections, or suggestions you might have for me. Thx! ;)

Aggregates - Calculations performed on multiple rows of a table.
COUNT() - count the number of rows
SUM() - the sum of the values in a column
MAX()/MIN() - the largest/smallest value
AVG() - the average of the values in a column
ROUND() - round the values in the column

Conditionals can also be added to aggregates.

SELECT aggregate(column_name) 
FROM table_name
WHERE conditional;

Round values

SELECT ROUND(column_name, int_decimal_places)
FROM table_name;

One can also stack aggregates, for example:

SELECT ROUND(AVG(table_name), int_decimal_places)
FROM table_name;

To calculate an aggregate for data with certain characteristics:
GROUP BY - Groups a result set based on an aggregate function (COUNT(), MIN(), MAX(), SUM(), AVG()). It lists the number in each group.

SELECT column_name, aggregate(other_column_name)
FROM table_name
GROUP BY column_name
ORDER BY column_name;

It is used with aggregate functions and with SELECT to arrange identical data into groups. It comes after WHERE statements and before ORDER BY or LIMIT.

Can also be used with a calculation done on a column.

SELECT aggregate(column_name), aggregate(other_column_name)
FROM table_name
GROUP BY int_num_of_column_selected
ORDER BY int_num_of_column_selected;

HAVING
To limit the results of a query based on values of the individual rows: WHERE.
To limit the results of a query based on an aggregate property: HAVING.
(After GROUP BY, but before ORDER BY and LIMIT)

SELECT column_name1, 
    column_name2, 
    COUNT(column_name3)
FROM table_name
GROUP BY 1, 2
HAVING COUNT(column_name) conditional;

SQLite function for returning formatted date

strftime(format, column)

Formats:
%Y: year (YYYY)
%m:month (01-12)
%d: day (1-31)
%H: 24-hour clock (00-23)
%M: minute (00-59)
%S: seconds (00-59)

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

n de la cruz — Sat, 10 Oct 2026 21:50:32 +0000

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;

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

n de la cruz — Sat, 10 Oct 2026 16:07:56 +0000

Hi:) I've wanted to learn Data Science for a while now, I am looking to change careers and have had a serious interest in Data for some time. I am using DEV Community to share everything I learn and motivate myself and others to keep learning. Feel free to comment on any of my posts with tips, corrections, or suggestions. Thx!

Database - set of data stored in a computer. It is usually structured in a way that makes the data easily accessible.

Relational Database - a type of database. It uses a structure that allows us to identify and access data in relation to another piece of data in the database. It is often organized into tables.

Relational Database Management System (RDBMS) - It is a program that allows you to create, update, and administer a relational database. Most use SQL to access the database.

SQL (Structured Query Language) - programming language used to communicate with data stored in a relational database management system.

SQL

Statement - text that the database recognizes as a valid command. They always end in a semicolon ;

CREATE TABLE (clause) - Clauses perform specific tasks in SQL. By convention, written in capital letters. Commands.

CREATE TABLE table_name (
   column_1 data_type, 
   column_2 data_type, 
   column_3 data_type
);

table_name - name of the table that the command is applied to.
column_n, data_type - (parameter) - list of columns, data types, or values that are passed to a clause as an argument.

SELECT * FROM ;
CREATE TABLE celebs (
   id INTEGER, 
   name TEXT, 
   age INTEGER
);

CREATE statements allow us to create a new table in the database.

CREATE TABLE Preview: Docs Creates a new table within a database.
celebs is the name of the table.
(id INTEGER, name TEXT, age INTEGER) is a list of parameters defining each column, or attribute in the table and its data type

id is the first column in the table. It stores values of data type INTEGER
name is the second column in the table. It stores values of data type TEXT
age is the third column in the table. It stores values of data type INTEGER

The INSERT statement inserts a new row into a table.
INSERT INTO - clause that adds the specified row or rows.

INSERT INTO table_name (column_name, column_name, ...) 
VALUES (inserted_data, inserted_data, ....);

SELECT column_name FROM table_name;

ALTER TABLE table_name 
ADD COLUMN new_column DATA_TYPE;

UPDATE table_name 
SET new_column_name = inserted_data 
WHERE column_name = column_value;

DELETE FROM table_name 
WHERE column_name IS column_value;

Constraints - rules applied to the values of individual columns. They add information about how a column can be used after specifying the data type for a column. They can be used to tell the database to reject inserted data that does not adhere to a certain restriction.

CREATE TABLE celebs (
   column_name DATA_TYPE PRIMARY KEY, 
   column_name2 DATA_TYPE UNIQUE,
   column_name3 DATA_TYPE NOT NULL,
   column_name4 DATA_TYPE DEFAULT 'Not Applicable'
);

PRIMARY KEY columns can be used to uniquely identify the row. Attempts to insert a row with an identical value to a row already in the table will result in a constraint violation which will not allow you to insert the new row.
UNIQUE columns have a different value for every row. This is similar to PRIMARY KEY except a table can have many different UNIQUE columns.
NOT NULL columns must have a value. Attempts to insert a row without a value for a NOT NULL column will result in a constraint violation and the new row will not be inserted.
DEFAULT columns take an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column.

SUMMARY

CREATE TABLE
creates a new table.

INSERT INTO
adds a new row to a table.

SELECT
queries data from a table.

ALTER TABLE
changes an existing table.

UPDATE
edits a row in a table.

DELETE FROM
deletes rows from a table.