Table of contents
Relational Database Concepts:
Definition: A relational database is a type of database that organizes data into tables with predefined relationships between them.
Tables: Represent entities and consist of rows (records) and columns (attributes) that hold data.
Primary Key: Uniquely identifies each record in a table and ensures data integrity.
Foreign Key: Establishes relationships between tables by referencing the primary key of another table.
Relationships:
One-to-One: Each record in one table is associated with only one record in another table.
One-to-Many: Each record in one table is associated with multiple records in another table.
Many-to-Many: Multiple records in one table are associated with multiple records in another table through an intermediate junction table.
Normalization: Process of organizing data to minimize redundancy and ensure data integrity.
First Normal Form (1NF): Eliminates duplicate rows by ensuring atomic values in columns.
Second Normal Form (2NF): Removes partial dependencies by creating separate tables for related data.
Third Normal Form (3NF): Eliminates transitive dependencies by removing non-key attributes.
Indexing: Enhances query performance by creating indexes on specific columns for faster data retrieval.
ACID Properties: Ensure reliable database transactions.
Atomicity: Ensures all or none of the transaction's operations are performed.
Consistency: Ensures the database remains in a valid state after a transaction.
Isolation: Ensures concurrent transactions do not interfere with each other.
Durability: Ensures that committed transactions survive system failures.
SQL Queries:
SELECT Statement: Retrieves data from one or more tables.
SELECT columns FROM table
WHERE condition
JOIN clause to combine data from multiple tables
INSERT Statement: Inserts new records into a table.
- INSERT INTO table (columns) VALUES (values)
UPDATE Statement: Modifies existing records in a table.
- UPDATE table SET column = value WHERE condition
DELETE Statement: Deletes records from a table.
- DELETE FROM table WHERE condition
JOINs: Combine data from multiple tables based on specified conditions.
INNER JOIN: Returns matching records from both tables.
LEFT JOIN: Returns all records from the left table and matching records from the right table.
RIGHT JOIN: Returns all records from the right table and matching records from the left table.
FULL JOIN: Returns all records when there is a match in either the left or right table.
Aggregate Functions: Perform calculations on sets of values.
COUNT: Returns the number of rows.
SUM: Calculates the sum of values.
AVG: Calculates the average of values.
MAX: Retrieves the maximum value.
MIN: Retrieves the minimum value.
GROUP BY: Groups rows based on specified columns.
ORDER BY: Sorts rows in ascending or descending order.