--description--

Introduction to Relational Databases

• Relational Databases: Organize data into related tables made of rows and columns. Each row represents a record, and each column represents an attribute of the data.
• Advantages of Relational Databases: Scalable, widely applicable across domains (e.g., healthcare, business, gaming), and structured to maintain reliable data.
• Common Use Cases: Web development, inventory systems, e-commerce, healthcare, and enterprise applications.

Key Concepts

• Schema: A relational database requires a schema that defines its structure—tables, columns, data types, constraints, and relationships.
• Primary Keys: Unique identifiers for each row in a table. They are essential for data integrity and are used to relate records between tables via foreign keys.
• Foreign Keys: References to primary keys in another table, used to link related data across tables.
• Relationships: By connecting tables through primary and foreign keys, you can structure normalized data and perform meaningful queries.
• Entity Relationship Diagrams (ERDs): Visualize how entities (tables) relate to each other in a database schema.
• Data Integrity: Enforced using keys and data types. Ensures consistency and accuracy of stored data.

SQL Basics

• Queries: Requests to retrieve specific data from the database.

SELECT * FROM dogs WHERE age < 3;

• WHERE clause: Filter results based on conditions. Use comparison operators like <, =, >, etc.
• Select with ORDER BY: Retrieve and sort results based on a column.

SELECT columns FROM table_name ORDER BY column_name;

Table Operations

• CREATE TABLE Statement: This statement is used to create a new table in a database.

CREATE TABLE first_table();

• ALTER TABLE ADD COLUMN Statement: This statement is used to add a column to an existing table.

ALTER TABLE table_name ADD COLUMN column_name DATATYPE;

• ALTER TABLE DROP COLUMN Statement: This statement is used to drop a column from an existing table.

ALTER TABLE table_name DROP COLUMN column_name;

• ALTER TABLE RENAME COLUMN Statement: This statement is used to rename a column in a table.

ALTER TABLE table_name RENAME COLUMN column_name TO new_name;

• DROP TABLE Statement: This statement is used to drop an entire table from the database.

DROP TABLE table_name;

• ALTER DATABASE RENAME Statement: This statement is used to rename a database.

ALTER DATABASE database_name RENAME TO new_database_name;

• DROP DATABASE Statement: This statement is used to drop an entire database.

DROP DATABASE database_name;

Constraints & Data Integrity

• ALTER TABLE ADD COLUMN with Constraint: This statement is used to add a column with a constraint to an existing table.

ALTER TABLE table_name ADD COLUMN column_name DATATYPE CONSTRAINT;

• NOT NULL Constraint: This constraint ensures that a column cannot have NULL values.

column_name VARCHAR(50) NOT NULL

• ALTER TABLE ADD PRIMARY KEY Statement: This statement is used to add a primary key constraint to a table.

ALTER TABLE table_name ADD PRIMARY KEY(column_name);

• ALTER TABLE DROP CONSTRAINT Statement: This statement is used to drop a constraint from a table.

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

• ALTER TABLE ADD COLUMN with Foreign Key: This statement is used to add a foreign key column that references another table.

ALTER TABLE table_name ADD COLUMN column_name DATATYPE REFERENCES referenced_table_name(referenced_column_name);

• ALTER TABLE ADD UNIQUE Statement: This statement is used to add a UNIQUE constraint to a column.

ALTER TABLE table_name ADD UNIQUE(column_name);

• ALTER TABLE ALTER COLUMN SET NOT NULL Statement: This statement is used to set a NOT NULL constraint on an existing column.

ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;

• INSERT Statement with NULL Values: This statement demonstrates how to insert NULL values into a table.

INSERT INTO table_name(column_a) VALUES(NULL);
-- or
INSERT INTO table_name(column_b) VALUES('value'); -- if column_a allows nulls

• Composite Primary Key: This constraint defines a primary key that consists of multiple columns.

CREATE TABLE course_enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

Data Manipulation (CRUD)

• INSERT Statement: This statement is used to insert a single row into a table.

INSERT INTO table_name(column_1, column_2) VALUES(value1, value2);

• INSERT Statement with Omitted Columns: This statement shows how to insert values without explicitly listing the column names, relying on the default column order in the table.

INSERT INTO dogs VALUES ('Gino', 3);

• INSERT Statement with Multiple Rows: This statement is used to insert multiple rows into a table in a single operation.

INSERT INTO dogs (name, age) VALUES 
('Gino', 3),
('Nora', 2);

• UPDATE Statement: This statement is used to update existing data in a table based on a condition.

UPDATE table_name SET column_name=new_value WHERE condition;

• DELETE Statement: This statement is used to delete rows from a table based on a condition.

DELETE FROM table_name WHERE condition;

Data Types

• NUMERIC Data Type: This data type is used to store precise decimal numbers with a specified precision and scale.

price NUMERIC(10, 2)

• TEXT Data Type: This data type is used to store variable-length character strings with no specific length limit.

column_name TEXT

• INTEGER Data Type: This data type is used to store whole numbers without decimal places.

units_sold INTEGER

• SMALLINT and BIGINT Data Types: These are variants of INTEGER with smaller and larger ranges respectively.

• SERIAL Data Type: This data type is used to create auto-incrementing integer columns in PostgreSQL.

id SERIAL

• AUTO_INCREMENT Attribute: This attribute is used in MySQL to create auto-incrementing integer columns.

id INT AUTO_INCREMENT

• VARCHAR Data Type: This data type is used to store variable-length character strings with a specified maximum length.

name VARCHAR(50)

• DATE Data Type: This data type is used to store date values (year, month, day).

event_date DATE

• TIME Data Type: This data type is used to store time values (hour, minute, second).

start_time TIME

• TIMESTAMP Data Type: This data type is used to store date and time values, optionally with time zone information.

event_timestamp TIMESTAMP
event_timestamp TIMESTAMP WITH TIME ZONE

• BOOLEAN Data Type: This data type is used to store true/false values.

is_active BOOLEAN

Database Relationships

• Types of Relationships: These are the different ways tables can be related to each other in a relational database.

  • One-to-one
  • One-to-many
  • Many-to-one
  • Many-to-many
  • Self-referencing (recursive)

• One-to-One Relationship: This relationship type means that each record in one table corresponds to exactly one record in another table.

One employee is assigned exactly one vehicle.
Tables: employees, vehicles

• One-to-Many Relationship: This relationship type means that one record in a table can be associated with multiple records in another table.

One customer can have many orders.
Tables: customers → orders

• Many-to-Many Relationship via Junction Table: This relationship type is implemented using a junction table that contains foreign keys from both related tables.

CREATE TABLE books_authors (
    author_id INT REFERENCES authors(id),
    book_id INT REFERENCES books(id)
);

• Self-Referencing Relationships: This relationship type occurs when a table references itself, creating a hierarchical structure.

An employee table where each employee may report to another employee.

Advanced SQL (Joins)

• INNER JOIN Statement: This join returns only the rows that have matching values in both tables.

SELECT *
FROM products
INNER JOIN sales ON products.product_id = sales.product_id;

• FULL OUTER JOIN Statement: This join returns all rows from both tables, including unmatched rows from either table.

SELECT *
FROM products
FULL OUTER JOIN sales ON products.product_id = sales.product_id;

• LEFT OUTER JOIN Statement: This join returns all rows from the left table and matching rows from the right table.

SELECT *
FROM products
LEFT JOIN sales ON products.product_id = sales.product_id;

• RIGHT OUTER JOIN Statement: This join returns all rows from the right table and matching rows from the left table.

SELECT *
FROM products
RIGHT JOIN sales ON products.product_id = sales.product_id;

• SELF JOIN Statement: This join is used to join a table with itself to compare rows within the same table.

SELECT A.column_name, B.column_name
FROM table_name A
JOIN table_name B ON A.related_column = B.related_column;

• CROSS JOIN Statement: This join returns the Cartesian product of two tables, combining every row from the first table with every row from the second table.

SELECT *
FROM table1
CROSS JOIN table2;

PostgreSQL Specific Commands

• psql Login Command: This command is used to log in to PostgreSQL with specific username and database.

psql --username=freecodecamp --dbname=postgres

• \l Command: This command lists all databases in the PostgreSQL instance.

\l

• CREATE DATABASE and \c Commands: These commands are used to create a new database and connect to it.

CREATE DATABASE database_name;
\c database_name

• \d Command: This command lists all tables in the current database.

\d

• \d table_name Command: This command displays the schema/structure of a specific table.

\d table_name

• \q Command: This command exits the PostgreSQL client.

\q

Relational vs Non-Relational

• Non-Relational (NoSQL) Databases: Store unstructured or semi-structured data. Do not require a rigid schema and are more flexible for evolving data models.
• Choosing Between Relational and Non-Relational: Depends on the nature of your data and application requirements.
• Relational vs Non-Relational: Choose relational for structured data and consistency; NoSQL for flexibility and fast-changing data.

Popular RDBMS Systems

• MySQL: Open-source, reliable, widely used in web development, supported by a large community.
• PostgreSQL: Open-source, advanced, extensible. Supports custom data types and server-side programming.
• SQLite: Lightweight, file-based, serverless. Ideal for small applications.
• Microsoft SQL Server: Proprietary, enterprise-grade database.
• Oracle Database: Commercial RDBMS known for large-scale performance and scalability.

Best Practices

• Naming Convention: Use snake_case (e.g., delivery_orders) for table and column names.

--assignment--

Review SQL and PostgreSQL topics and concepts.