reading-notes

My repository is a collection of projects and resources that have helped me grow as a learner and a technologist. I hope you find these resources useful and that they inspire you on your own journey.

View on GitHub

SQL

SQL, or Structured Query Language, is a standard programming language used for managing and manipulating data in a relational database management system (RDBMS). PostgreSQL, also known as Postgres, is a powerful open-source RDBMS that has gained significant popularity over the years due to its advanced features and robustness. In this article, we will discuss the basics of SQL and its application in PostgreSQL, as well as the popular PostgreSQL management tool, pgAdmin.

SQL

SQL is used for various tasks, including creating, modifying, and deleting database structures, inserting, updating, and deleting data in a database, and querying data from a database. It uses a set of commands or statements that can be executed against a database. These commands include SELECT, INSERT, UPDATE, DELETE, CREATE, and DROP, among others.

PostgreSQL

PostgreSQL is a free and open-source RDBMS that supports various advanced features, including nested transactions, user-defined types, and stored procedures. It is compatible with many programming languages, including C, C++, Java, Perl, Python, Ruby, and many others. PostgreSQL is known for its stability, reliability, and performance, making it a popular choice for many applications.

PgAdmin

PgAdmin is a popular PostgreSQL management tool that provides a graphical user interface (GUI) for managing PostgreSQL databases. It is an open-source tool that is available for Windows, Linux, and macOS. With pgAdmin, you can manage database objects such as tables, views, and indexes, execute SQL queries, and monitor server activity, among other things.

Windows Installation - PostGreSQL and PgAdmin with Database Setup

To get started with PostgreSQL and pgAdmin, you first need to install them. PostgreSQL can be installed on Windows, Linux, or macOS, and pgAdmin can be installed on any of these operating systems as well. Once installed, you can create a new database using pgAdmin, create tables and other database objects, and insert data into the database. You can also execute SQL queries using the pgAdmin Query Tool or the PostgreSQL command-line interface (CLI).

SQL Statements Fundamentals

Let’s dive into some of the most commonly used SQL commands and their usage in PostgreSQL.

SELECT

The SELECT command is used to query data from a table in a database. It allows you to retrieve one or more columns from one or more tables based on certain criteria. Here’s an example:

SELECT * FROM employees;
This command will retrieve all columns from the employees table.

SELECT DISTINCT

The SELECT DISTINCT command is used to retrieve unique values from a table. Here’s an example:

SELECT DISTINCT department FROM employees;
This command will retrieve distinct department names from the employees table.

COUNT

The COUNT command is used to count the number of rows in a table that meet certain criteria. Here’s an example:

SELECT COUNT(*) FROM employees 

WHERE department = 'Sales';
This command will count the number of employees in the Sales department.

SELECT WHERE

The SELECT WHERE command is used to retrieve data from a table based on certain conditions. Here’s an example:

SELECT * FROM employees 

WHERE salary > 50000;
This command will retrieve all columns from the employees table where the salary is greater than 50,000.

ORDER BY

The ORDER BY command is used to sort the result set in ascending or descending order based on one or more columns. Here’s an example:

SELECT * FROM employees 

ORDER BY last_name ASC, first_name ASC;
This command will retrieve all columns from the employees table and order the result set by last name in ascending order, and then by first name in ascending order.

LIMIT

The LIMIT command is used to limit the number of rows returned by a query. Here’s an example:

SELECT * FROM employees 

LIMIT 10;
This command will retrieve the first 10 rows from the employees table.

BETWEEN

The BETWEEN command is used to retrieve values that fall within a specified range. Here’s an example:

SELECT * FROM employees 

WHERE salary BETWEEN 40000 AND 60000;
This command will retrieve all columns from the employees table where the salary falls between 40,000 and 60,000.

IN

The IN command is used to retrieve values that match any one of a specified set of values. Here’s an example:

SELECT * FROM employees 

WHERE department IN ('Sales', 'Marketing');
This command will retrieve all columns from the employees table where the department is either Sales or Marketing.

LIKE/ILIKE

The LIKE/ILIKE command is used to retrieve values that match a specified pattern. The ILIKE command is case-insensitive. Here’s an example:

SELECT * FROM employees 

WHERE first_name LIKE 'J%';
This command will retrieve all columns from the employees table where the first name starts with the letter J.

% _%

The % and _ characters are wildcards used with the LIKE/ILIKE command. The % character represents any number of characters, while the _ character represents a single character. Here’s an example:

SELECT * FROM employees 

WHERE last_name LIKE 'Sm_th';
This command will retrieve all columns from the employees table where the last name is Smith, Smyth, or any other last name that matches the pattern.

pgAdmin

If you’re looking for a user-friendly way to manage your PostgreSQL database, look no further than PgAdmin. As an open-source graphical user interface (GUI) tool, PgAdmin provides an easy-to-use interface for a variety of tasks:

such as creating and modifying tables, executing SQL queries, and managing user permissions.

Getting started with PgAdmin is easy; simply After installing it you can use the intuitive GUI to perform operations on your database, like creating and modifying database objects, running SQL scripts, and viewing query results.

For more advanced users, PgAdmin provides features like server groups, which let you organize your database servers into logical groups to make managing large numbers of servers easier.

And here is a video clarifying the GUI of pgAdmin:

Some Commonly used Aggregation Functions and Clauses in SQL, with examples in PostgreSQL.

AVG

The AVG function is used to calculate the average value of a numerical column. Here’s an example:

SELECT AVG(salary) FROM employees;
This command will calculate the average salary of all employees in the employees table.

COUNT

The COUNT function is used to count the number of rows that match a specified condition. Here’s an example:

SELECT COUNT(*) FROM employees WHERE department = 'Sales';
This command will count the number of employees in the Sales department.

MAX

The MAX function is used to find the maximum value in a numerical column. Here’s an example:

SELECT MAX(salary) FROM employees;
This command will find the highest salary among all employees in the employees table.

MIN

The MIN function is used to find the minimum value in a numerical column. Here’s an example:

SELECT MIN(salary) FROM employees;
This command will find the lowest salary among all employees in the employees table.

SUM

The SUM function is used to calculate the total sum of a numerical column. Here’s an example:

SELECT SUM(sales_amount) FROM sales;
This command will calculate the total sales amount from the sales table.

ROUND

The ROUND function is used to round a numerical value to a specified number of decimal places. Here’s an example:

SELECT ROUND(salary, 2) FROM employees;
This command will round the salary of all employees in the employees table to two decimal places.

GROUP BY

The GROUP BY clause is used to group rows with similar values into summary rows.

Here’s an example:

SELECT department, AVG(salary) FROM employees 

GROUP BY department;
This command will group employees by department and calculate the average salary for each department.

When used with the DATE() function, it can group records based on the date component of a datetime column.

For example, let’s say you have a table called orders with columns order_id, customer_id, order_date, and order_amount. You want to find the total order amount for each day in the month of January 2022.

You can use the DATE() function to extract the date component of the order_date column and then group the records by this date using the GROUP BY clause. Here’s an example query:

SELECT DATE(order_date) as order_day, SUM(order_amount) as total_amount
FROM orders

WHERE order_date >= '2022-01-01' AND order_date < '2022-02-01'

GROUP BY DATE(order_date)

ORDER BY order_day;
In this query, the WHERE clause limits the records to those with an order_date in January 2022. The SELECT clause uses the DATE() function to extract the date component of order_date and aliases it as order_day. The SUM() function is used to calculate the total order amount for each order_day. Finally, the GROUP BY clause groups the records by order_day.

The result of this query will be a table with two columns: order_day and total_amount. Each row will show the total order amount for a specific day in January 2022. The ORDER BY clause sorts the result set in ascending order by order_day.

ORDER BY

The ORDER BY clause is used to sort the result set in ascending or descending order based on one or more columns. Here’s an example:

SELECT department, AVG(salary) FROM employees 

GROUP BY department ORDER BY AVG(salary) DESC;
This command will group employees by department, calculate the average salary for each department, and sort the result set in descending order by average salary.

HAVING

The HAVING clause is used to filter the result set based on a condition that involves an aggregate function. Here’s an example:

“its like a WHERE statement but for the Aggregation Functions”

SELECT department, AVG(salary) FROM employees 

GROUP BY department 

HAVING AVG(salary) > 50000;
This command will group employees by department, calculate the average salary for each department, and filter the result set to show only departments where the average salary is greater than 50,000.

SQL join type and operator.

AS

The AS operator is used to alias a table or column with a different name. It’s especially useful when you need to rename columns or tables in the output to make it more readable and meaningful. The syntax for using the AS operator is:

SELECT column_name AS alias_name FROM table_name;

For example, in the query we showed earlier:

SELECT e.first_name, e.last_name, d.department_name AS dept_name
FROM employees e

INNER JOIN departments d ON e.department_id = d.department_id;
We are aliasing the departments.department_name column as dept_name using the AS operator. This makes the output more readable and meaningful.

INNER JOIN

The INNER JOIN operator returns only the rows that have matching values in both tables. It’s used to combine the rows from two or more tables based on a common column. The syntax for using INNER JOIN is:

SELECT column_name(s)
FROM table1

INNER JOIN table2

ON table1.column_name = table2.column_name;

For example:

SELECT e.first_name, e.last_name, d.department_name
FROM employees e

INNER JOIN departments d ON e.department_id = d.department_id;
This query is using INNER JOIN to combine the employees table with the departments table based on the department_id column. The result set will include only the rows where there is a match in both tables.

OUTER JOIN

The OUTER JOIN operator returns all the rows from one table and the matching rows from the other table. If there is no match in the other table, the result will include NULL values.

There are two types of outer joins: LEFT OUTER JOIN and RIGHT OUTER JOIN.

LEFT OUTER JOIN

returns all the rows from the left table and the matching rows from the right table. If there is no match in the right table, the result will include NULL values.

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;

RIGHT OUTER JOIN

returns all the rows from the right table and the matching rows from the left table. If there is no match in the left table, the result will include NULL values.

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;

For example:

SELECT e.first_name, e.last_name, d.department_name
FROM employees e

LEFT OUTER JOIN departments d ON e.department_id = d.department_id;

In this query, the LEFT OUTER JOIN operator is used to join the employees table with the departments table based on the department_id column. The result set will include all the rows from the employees table and the matching rows from the departments table. If there is no match in the departments table, the department_name column will contain a NULL value.

FULL JOIN

The FULL JOIN operator is used to combine the results of a LEFT JOIN and a RIGHT JOIN. It returns all the rows from both tables and matches the rows that have matching values in both tables. If there are no matching rows in one of the tables, the result set will contain NULL values for the columns of that table.

The syntax for a FULL JOIN is as follows:

SELECT column_names
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

Here’s an example of a FULL JOIN:

Suppose we have two tables, "Customers" and "Orders". The "Customers" table has columns "CustomerID" and "CustomerName", while the "Orders" table has columns "OrderID", "CustomerID", and "OrderDate".

To get a list of all customers and their orders, we can use a FULL JOIN as follows:

SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers

FULL JOIN Orders

ON Customers.CustomerID = Orders.CustomerID

ORDER BY Customers.CustomerName;

In this example, we join the two tables using the "CustomerID" column. The result set will include all customers, including those who have not placed any orders, and all orders, including those that have not been placed by any customers.

If there is no matching customer for an order or no matching order for a customer, the corresponding columns in the result set will contain NULL values.

Note that the ORDER BY clause is used to sort the result set by the "CustomerName" column. This clause can be used with any type of JOIN to sort the results according to a specific column.

Advanced SQL Commands:

TimeStamps and Extract:

TimeStamps are a way to represent time and date values in SQL. The Extract function is used to extract specific parts of a timestamp, such as year, month, day, hour, minute, and second.

Here’s an example query that uses the Extract function to get the month and year from a timestamp column:

SELECT EXTRACT(MONTH FROM timestamp_column) AS month,
       EXTRACT(YEAR FROM timestamp_column) AS year
FROM table_name;

Math Functions:

SQL provides several built-in math functions, such as ABS, ROUND, CEIL, FLOOR, and TRUNC. These functions can be used to perform arithmetic operations and round numbers.

Here’s an example query that uses the ROUND function to round a number to two decimal places:

SELECT ROUND(number_column, 2) AS rounded_number
FROM table_name;

String Functions:

SQL also provides several built-in string functions, such as CONCAT, LENGTH, SUBSTR, UPPER, and LOWER. These functions can be used to manipulate string values.

Here’s an example query that uses the CONCAT function to concatenate two columns:

SELECT CONCAT(column1, ' ', column2) AS full_name
FROM table_name;

Sub-Query:

A sub-query is a query that is nested inside another query. It is used to retrieve data that will be used as input for the main query. Sub-queries can be used in the SELECT, WHERE, and HAVING clauses.

Here’s an example query that uses a sub-query to get the average of a column:

SELECT AVG(column_name)
FROM (SELECT column_name FROM table_name WHERE condition) subquery_alias;

Self-Join:

A self-join is a join operation where a table is joined with itself. It is used to relate rows within the same table.

Here’s an example query that uses a self-join to find all pairs of employees who have the same manager:

SELECT a.employee_name, b.employee_name
FROM employees a
JOIN employees b
ON a.manager_id = b.manager_id
WHERE a.employee_name <> b.employee_name;

To-Char:

The To-Char function is used to convert a value to a character string with a specified format. It is commonly used to format date and time values.

Here’s an example query that uses the To-Char function to convert a date column to a specific format:

SELECT TO_CHAR(date_column, 'MM/DD/YYYY') AS formatted_date
FROM table_name;

Tips & Tricks:

TimeStamps and Extract:

Math Functions:

Sub-Query:

Self-Join:

To-Char:

Creating Databases Using SQL.

Data Types:

SQL provides a variety of data types that can be used to define the structure of a database. The most common data types include:

Each data type has specific properties and usage scenarios, and it’s important to choose the appropriate data type for each column in your database.

In addition to the common data types mentioned earlier, there are several other data types that can be used in SQL databases. Here are a few examples:

Explanation for Primary and Foreign Keys:

A primary key is a column or a set of columns in a table that uniquely identifies each row in the table. This means that no two rows can have the same values in their primary key columns.

The primary key is used as a reference point for other tables that have a foreign key relationship with the primary key table. A primary key is essential for maintaining data integrity, as it ensures that each row is uniquely identified and can be referenced by other tables.

A foreign key is a column or a set of columns in a table that refers to the primary key of another table. This creates a relationship between two tables and allows data to be linked across multiple tables.

For example, if you have a “Customers” table and an “Orders” table, the “Orders” table could have a foreign key column that refers to the primary key of the “Customers” table. This would allow you to link each order to a specific customer, ensuring that data is consistent and accurate.

When a foreign key is defined, it enforces referential integrity, which means that the values in the foreign key column must match the values in the primary key column of the referenced table. This ensures that data is always consistent and valid, as it prevents the creation of orphaned records (i.e. records with no corresponding reference in the primary key table).

Constraints

are a crucial aspect of database design, as they define the rules and limits for the data that can be stored in a table. Here are some common constraints found in SQL:

By using constraints, you can ensure that your data is consistent, accurate, and valid. Constraints help to enforce data integrity and prevent errors, while also making it easier to manage and query your data.

CREATE

The CREATE statement in SQL is used to create a new database object, such as a table, index, view, or stored procedure. The basic syntax for creating a table in SQL is as follows:

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    columnN datatype constraints
);

In this statement, table_name is the name of the table you want to create, column1 through columnN are the names of the columns in the table, datatype is the data type for each column, and constraints are any additional constraints or rules that you want to apply to the column.

SERIAL:

The SERIAL data type is a special data type in PostgreSQL that is used to generate unique sequence numbers for a column.

When you define a column as SERIAL, PostgreSQL will automatically generate a unique sequence number for each new row that is inserted into the table.

The basic syntax for defining a column as SERIAL in SQL is as follows:

CREATE TABLE table_name (
    column_name SERIAL PRIMARY KEY,
    ...
);

In this statement, column_name is the name of the column you want to define as SERIAL, and the PRIMARY KEY constraint ensures that the column is also a primary key for the table. When a new row is inserted into the table, PostgreSQL will automatically generate a unique sequence number for the column_name column.

SERIAL is a convenient way to automatically generate unique identifiers for a table, such as primary keys. It eliminates the need to manually generate unique identifiers, and ensures that each row in the table has a unique identifier.

However, it is important to note that SERIAL is specific to PostgreSQL, and other database systems may use different methods for generating unique identifiers.

INSERT

statement in SQL is used to insert data into a table. Here’s a breakdown of the syntax and some details about how it works:

Basic Syntax:

INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN);

In this statement, table_name is the name of the table you want to insert data into, column1 through columnN are the names of the columns you want to insert data into, and value1 through valueN are the values you want to insert into the corresponding columns. The number of columns and values must match, or you will get an error.

You can also insert multiple rows of data at once, by separating each set of values with a comma:

INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN),
       (value1, value2, ..., valueN),
       ...,
       (value1, value2, ..., valueN);

If you want to insert data into all columns in the table, you can omit the column list:

INSERT INTO table_name
VALUES (value1, value2, ..., valueN);

Some additional details about the INSERT statement:

Overall, the INSERT statement is a fundamental part of working with SQL databases, and understanding how to use it effectively is essential for working with data in a database.

UPDATE

statement in SQL is used to modify existing data in a table. Here’s a breakdown of the syntax and some details about how it works:

Basic Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ..., columnN = valueN
WHERE condition;

In this statement, table_name is the name of the table you want to update, column1 through columnN are the names of the columns you want to modify, and value1 through valueN are the new values you want to set for the corresponding columns.

The WHERE clause specifies which rows you want to update based on a condition that you define. If you omit the WHERE clause, all rows in the table will be updated.

Some additional details about the UPDATE statement:

Overall, the UPDATE statement is a powerful tool for modifying data in a SQL database, and understanding how to use it effectively is essential for managing data in a database.

DELETE

statement in SQL is used to remove data from a table. Here’s a breakdown of the syntax and some details about how it works:

Basic Syntax:

DELETE FROM table_name
WHERE condition;

In this statement, table_name is the name of the table you want to delete data from, and the WHERE clause specifies which rows you want to delete based on a condition that you define. If you omit the WHERE clause, all rows in the table will be deleted.

Some additional details about the DELETE statement:

Overall, the DELETE statement is an important tool for managing data in a SQL database, but it should be used with caution to avoid accidentally deleting important data.

ALTER

statement in SQL is used to modify the structure of a table, such as adding, modifying or dropping columns, changing the data type of columns, adding or dropping constraints, and more. Here’s a breakdown of the syntax and some details about how it works:

Basic Syntax:

ALTER TABLE table_name
ACTION;

In this statement, table_name is the name of the table you want to modify, and ACTION is the specific modification you want to make to the table. Here are some common actions you can perform with ALTER:

ADD COLUMN: adds a new column to the table. Here’s an example:

ALTER TABLE table_name
ADD COLUMN column_name data_type;

MODIFY COLUMN: changes the data type of an existing column. Here’s an example:

ALTER TABLE table_name
MODIFY COLUMN column_name new_data_type;

DROP COLUMN: removes a column from the table. Here’s an example:

ALTER TABLE table_name
DROP COLUMN column_name;

ADD CONSTRAINT: adds a constraint to the table, such as a primary key or foreign key constraint. Here’s an example:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);

DROP CONSTRAINT: removes a constraint from the table. Here’s an example:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Some additional details about the ALTER statement:

Overall, the ALTER statement is an important tool for modifying the structure of a SQL table and ensuring that your database schema is up-to-date and optimized for your needs.

DROP

statement is used to remove database objects such as tables, indexes, views, and more. Here’s a breakdown of the syntax and some details about how it works:

Basic Syntax:

DROP OBJECT_TYPE IF EXISTS object_name;

In this statement, OBJECT_TYPE is the type of object you want to drop, such as TABLE, INDEX, VIEW, etc. IF EXISTS is an optional clause that prevents an error from being thrown if the object does not exist. OBJECT_NAME is the name of the object you want to drop. Here are some examples of how to use the DROP statement:

DROP TABLE IF EXISTS table_name;

Drop an index:

DROP INDEX IF EXISTS index_name;

Drop a view:

DROP VIEW IF EXISTS view_name;

Some additional details about the DROP statement:

Overall, the DROP statement is an important tool for managing your SQL database objects and keeping your schema organized and optimized.

Check Constraints

in SQL are used to enforce specific conditions that must be met for values in a table column. The purpose of a check constraint is to ensure that data entered into a table meets certain criteria or rules.

Here's an example of how to create a check constraint on a table:

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  age INTEGER,
  salary REAL,
  department TEXT,
  hire_date DATE,
  CONSTRAINT age_check CHECK (age >= 18)
);

In this example, a CHECK constraint is defined on the age column of the employees table, which ensures that the value of the age column is always greater than or equal to 18. If a value is inserted into the age column that violates this constraint, an error will be thrown.

Some key things to keep in mind about check constraints include:

Overall, check constraints are an important tool for ensuring data quality and maintaining the integrity of your database schema. By defining rules that must be met for data to be inserted or updated in a table, you can help prevent data inconsistencies and errors.