Реляционные СУБД

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100)
);

CREATE TABLE Orders (
    OrderID int,
    OrderNumber int,
    CustomerID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE
);

CREATE TABLE Course_Enrollment (
    student_id INT,
    course_id INT,
    student_name VARCHAR(255),
    course_name VARCHAR(255),
    professor_name VARCHAR(255),
    PRIMARY KEY (student_id, course_id)
);

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(255)
);

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(255),
    professor_name VARCHAR(255)
);

CREATE TABLE Course_Enrollment (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100),
    MajorID INT,
    MajorName VARCHAR(100)
);

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100),
    MajorID INT,
    FOREIGN KEY (MajorID) REFERENCES Majors(MajorID)
);

CREATE TABLE Majors (
    MajorID INT PRIMARY KEY,
    MajorName VARCHAR(100)
);

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

-- Создание кластерного индекса
CREATE CLUSTERED INDEX idx_orders_orderdate ON Orders(OrderDate);

-- Создание некластерного индекса
CREATE NONCLUSTERED INDEX idx_customers_email ON Customers(Email);

CREATE CLUSTERED INDEX idx_id ON my_table(id);

CREATE INDEX index_name ON table_name (column_name);

CREATE INDEX my_hash_index ON my_table USING HASH (my_column);

CREATE BITMAP INDEX имя_индекса ON таблица(столбец);

CREATE INDEX имя_индекса ON имя_таблицы USING GIN (колонка);

CREATE INDEX index_name ON table_name (column1, column2) INCLUDE (column3, column4);

CREATE INDEX idx_upper_name ON employees (UPPER(last_name));

EXPLAIN ANALYZE SELECT ...;

CREATE TEMPORARY TABLE temp_table AS
SELECT column1, column2
FROM original_table
WHERE condition;

   SELECT * FROM table1
   INNER JOIN table2
   ON table1.column = table2.column;
   

   SELECT * FROM table1
   LEFT JOIN table2
   ON table1.column = table2.column;
   

   SELECT * FROM table1
   RIGHT JOIN table2
   ON table1.column = table2.column;
   

   SELECT * FROM table1
   FULL JOIN table2
   ON table1.column = table2.column;
   

   SELECT * FROM table1
   CROSS JOIN table2;
   

SELECT * FROM table1 CROSS JOIN table2;

SELECT * FROM table1 NATURAL JOIN table2;

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

SELECT employee_name FROM employees 
WHERE department_id = (
    SELECT department_id FROM departments 
    WHERE department_name = 'Sales'
);

SELECT product_id
FROM products
WHERE price > ANY (SELECT price FROM competitor_products);

SELECT product_id
FROM products
WHERE price > ALL (SELECT price FROM competitor_products);

SELECT customer_id
FROM customers
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id);

CREATE VIEW sales_staff AS
SELECT id, name
FROM employees
WHERE department = 'Sales';

   DROP TABLE table_name;
   

   DELETE FROM table_name WHERE condition;
   

   TRUNCATE TABLE table_name;
   

-- Пример явного вызова:
VACUUM (VERBOSE) имя_таблицы;

Основные задачи:
- WAL: сохраняет последовательность изменений для восстановления.
- Checkpoints: снижает нагрузку на откат, фиксируя состояние базы.