SQL Theory Notes

Learn the Sql database and complete basics of database operations using terminal.

ACID properties.
Normalization.
Constrains.
Relationships.
Joins.
3-Schema architecture.
Indexing.
Aggregate functions.
Scalar functions.
SQL queries.
Foreign key Primary key.
Closure.
Groupby.
Having.
Transactions.
DML, DDL, DCL.

SQL:
    SQL- Structured Query Language is a domain specific used to manage data in RDBMS- Relational Database Management Systems.
SQL Database:
    A SQL database server stores and organizes data in tables and rows.
    It's used for storing, retrieving, updating and deleting- All the CRUD operations of data.
    It's key concepts include:
        Normalization : For efficient data organisation.
        Primary key, secondary key: To establish relationships between tables.

ACID Properties:
    Transactions :
       Transactions are the set of tasks coming under one execution unit.
       If any tasks in between the begininning and ending task fails. The transaction fails.
       Therefore transaction has only 2 results: SUCCESS or FAILURE
       A db transaction should be atomic, consistent, isolated and durable
    Atomicity: Transaction result can be either fully succesful or completely unsuccessful
    Consistency:
    Isolated:
    Durablitiy:

Normalization:
    Normalization is a database technique to organize the database in a efficient manner by avoiding data redundancy.

    Anamolies: Problems that occur in poorly planned, unnormalized databases where datas exist in single table.
    Insert Anamoly, delete anamoly

    1NF:
        * Making cells atomic-having single unit. (single value)
        *Each column should have unique value.
    2NF:
        *Removing Partial dependencies
        *Each non-key attribute should dependent on the primary key and not to other columns.
    3NF:
        *Removing transitive dependencies


Constrains:
    Constrains in SQL are rules given to columns or tables.
    Uses:
    *To maintain data integrity : To enforce that only valid and consistent datas can be added to the table.
    *To establish relations (FOREIGN KEY (department_id) REFERENCES departments(id))
    *To avoid data redundancy in the required cases. (PRIMARY KEY, UNIQUE)

    PRIMARY KEY (id),
    FOREIGN KEY (department_id) REFERENCES departments(id)
    UNIQUE (email)
    age INT NOT NULL
    CHECK (age >= 18)

OPERATORS:
    Arthmetic operators (+,-,/,*,%) : SELECT salary * 1.1 AS increased_salary FROM employees;
    Comparison operators  (>,<,<>,!=,=) : SELECT * FROM products WHERE price > 100;
    Logical Operators (AND, OR, NOT) : SELECT * FROM employees WHERE department = 'IT' AND salary > 50000;
    Concatenation Operator (||): SELECT first_name || ' ' || last_name AS full_name FROM employees;
    LIKE Operator(Pattern matching): SELECT * FROM person WHERE firstname LIKE 'F%';
    IN Operator:
    BETWEEN Operator:
    IS NULL Operator:
    EXISTS Operator: Used to check for the existence of rows returned by a subquery.
        Returns TRUE if the subquery returns at least one row; otherwise, returns FALSE.
        Example: SELECT * FROM employees WHERE EXISTS (SELECT * FROM orders WHERE orders.employee_id = employees.id);


3-Schema architecture:
    It's a database design framework, it seperates the db into 3 levels: External, Conceptual and Internal Schema.


Aggregate functions:
    Aggregate functions compute a single result from a set of input values.
    Values of multiple rows are grouped together as input to return a single value output.
    COUNT(), SUM(), MIN(), MAX(), AVG()

Scalar functions:
    Scalar functions compute a single result from a single input.
    UPPER(), LOWER(), MIDDLE()

DQL: Data Query Language

DDL : Data Definition Language

DCL : Data Control Language

DML : Data Manipulation Language

/*
FROM CHATGPT:

Introduction to SQL:

SQL stands for Structured Query Language.
It's a standard language for managing relational databases.

Data Manipulation:
SQL allows you to manipulate data in various ways:
SELECT: Retrieves data from a database.
INSERT: Adds new data to a database.
UPDATE: Modifies existing data in a database.
DELETE: Removes data from a database.

Data Definition:
SQL also allows you to define and modify the structure of databases and tables:
CREATE DATABASE: Creates a new database.
CREATE TABLE: Creates a new table within a database.
ALTER TABLE: Modifies the structure of an existing table.
DROP TABLE: Deletes a table from a database.

Querying Data:
SQL queries are used to retrieve specific data from databases:
SELECT statement is the primary tool for querying data.
WHERE clause allows you to specify conditions for selecting data.
GROUP BY clause groups rows based on a specific column.
ORDER BY clause sorts the result set based on specified columns.

Joins:
Joins are used to combine data from multiple tables:
INNER JOIN: Returns records that have matching values in both tables.
LEFT JOIN: Returns all records from the left table, and the matched records from the right table.
RIGHT JOIN: Returns all records from the right table, and the matched records from the left table.
FULL OUTER JOIN: Returns all records when there is a match in either left or right table.
Data Integrity:

SQL ensures data integrity through various constraints:
PRIMARY KEY: Uniquely identifies each record in a table.
FOREIGN KEY: Establishes a link between two tables.
UNIQUE: Ensures that all values in a column are unique.
NOT NULL: Specifies that a column cannot contain NULL values.
Indexing:

Indexes are used to optimize query performance:
They allow for faster retrieval of data by creating pointers to rows in tables.
Common types include single-column indexes and composite indexes.
Transactions:

SQL transactions ensure data consistency and reliability:
BEGIN TRANSACTION: Starts a new transaction.
COMMIT: Saves changes made during the transaction to the database.
ROLLBACK: Discards changes made during the transaction, reverting to the last committed state.
Security:

SQL databases implement security measures to protect data:
Authentication ensures that only authorized users can access the database.
Authorization controls the level of access granted to users.
Encryption secures data both at rest and in transit.
Conclusion:

SQL is a powerful language for managing relational databases, offering a wide range of functionality for data manipulation, querying, and database administration.
Mastering SQL basics is essential for effectively working with databases and extracting meaningful insights from data.
*/