Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement