sql

DROP DATABASE IF EXISTS youtube_db;
CREATE DATABASE youtube_db;
USE youtube_db;

CREATE TABLE Channel (
    id INTEGER AUTO_INCREMENT NOT NULL,
    username VARCHAR(50) NOT NULL,
    number_of_total_views INTEGER NOT NULL,

    PRIMARY KEY(id)
);

CREATE TABLE User (
    id INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(100) NOT NULL,
    user_id INTEGER,

    PRIMARY KEY(id),
    FOREIGN KEY(user_id) REFERENCES Channel(id)
);

CREATE TABLE Video (
    id INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(50) NOT NULL,
    channel_id INTEGER DEFAULT 0,
    views INTEGER DEFAULT 0,

    PRIMARY KEY(id)
);

CREATE TABLE UserChannel (
    id INTEGER AUTO_INCREMENT NOT NULL,

    user_id INTEGER NOT NULL,
    channel_id INTEGER NOT NULL,

    PRIMARY KEY(id),
    FOREIGN KEY(user_id) REFERENCES User(id),
    FOREIGN KEY(channel_id) REFERENCES Channel(id)
);

///////////////////////////////////////////////////////

DROP DATABASE IF EXISTS stock_taking_db;
CREATE DATABASE stock_taking_db;
USE stock_taking_db;

CREATE TABLE People (
    id INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(50) NOT NULL,
    worker_id INTEGER NOT NULL,

    PRIMARY KEY(id)
);

CREATE TABLE Techs (
    id INTEGER AUTO_INCREMENT NOT NULL,
    brand VARCHAR(100) NOT NULL,
    description VARCHAR(500),
    p_id INTEGER,
    date_taken DATE,

    PRIMARY KEY(id),
    FOREIGN KEY(p_id) REFERENCES People(id)
);

CREATE TABLE Categories (
    id INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(50) NOT NULL,
    tech_count INTEGER,

    PRIMARY KEY(id)
);

CREATE TABLE TechCategories (
    id INTEGER AUTO_INCREMENT NOT NULL,

    tech_id INTEGER NOT NULL,
    category_id INTEGER NOT NULL,

    PRIMARY KEY(id),
    FOREIGN KEY(tech_id) REFERENCES Techs(id),
    FOREIGN KEY(category_id) REFERENCES Categories(id)
);

///////////////////////////////////////////

CREATE TRIGGER increaseCountIn
AFTER INSERT ON TechCategories
FOR EACH ROW
    UPDATE Categories SET tech_count = (tech_count + 1)
    WHERE NEW.category_id = Categories.id;

CREATE TRIGGER decreaseCountIn
AFTER UPDATE ON TechCategories
FOR EACH ROW
    UPDATE Categories SET tech_count = (tech_count - 1)
    WHERE NEW.category_id = Categories.id;



INSERT INTO People(name, worker_id) VALUES ('Pesho Programista', 'pe60');
INSERT INTO People(name, worker_id) VALUES ('Gosho Ivanov', 'go60');
INSERT INTO People(name, worker_id) VALUES ('Tosho Todorov', 'to60');


INSERT INTO Techs (brand, description, p_id, date_taken) VALUES ('DELL', 'computer', 1, now());
INSERT INTO Techs (brand, description, p_id, date_taken) VALUES ('Samsung Galaxy', 'smartphone', 2, now());
INSERT INTO Techs (brand, description) VALUES ('Nikon D7000', 'DSLR camera');
INSERT INTO Techs (brand, description) VALUES ('Lenovo 310', 'laptop');

INSERT INTO Categories(id,name) VALUES (1,'electronics');
INSERT INTO Categories(id,name) VALUES (2,'computers');
INSERT INTO Categories(id,name) VALUES (3,'cameras');
INSERT INTO Categories(id,name) VALUES (4,'clothes');

INSERT INTO TechCategories(tech_id, category_id) VALUES (1, 1);
INSERT INTO TechCategories(tech_id, category_id) VALUES (1, 2);
INSERT INTO TechCategories(tech_id, category_id) VALUES (2, 1);
INSERT INTO TechCategories(tech_id, category_id) VALUES (2, 2);
INSERT INTO TechCategories(tech_id, category_id) VALUES (3, 1);
INSERT INTO TechCategories(tech_id, category_id) VALUES (3, 2);
INSERT INTO TechCategories(tech_id, category_id) VALUES (4, 1);
INSERT INTO TechCategories(tech_id, category_id) VALUES (4, 2);

/////////////////////////////////////////

SELECT * FROM Techs
WHERE p_id IS NULL OR date_taken IS NULL;

SELECT * FROM Techs
WHERE p_id = 1;

SELECT p.id, p.name, t.brand, t.description, t.date_taken FROM People as p
INNER JOIN Techs as t ON p_id = p.id
GROUP BY p.id, p.name, t.brand, t.description, t.date_taken;