template for sql inventory db

deviceinvent.sql

#create a database
CREATE DATABASE avitek;

#create employees table
USE avitek;

CREATE TABLE employees (
	id int NOT NULL AUTO_INCREMENT,
    first_name varchar(255),
    last_name varchar(255),
    PRIMARY KEY (id)
);

#create devices table
USE avitek;

CREATE TABLE devices (
    id int NOT NULL AUTO_INCREMENT,
    device_type int,
    device_id int,
    user_id int,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) references employees(id)
);

# test join for user with multiple devices
SELECT devices.device_type, devices.device_id
FROM employees
INNER JOIN devices
ON employees.id = devices.user_id;


#might need this connecting table MAYBE
USE avitek;

CREATE TABLE employees_devices (
    employee_id int,
    devices_id int,
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (devices_id) REFERENCES devices(id)
);

INSERT INTO `avitek`.`employees` (`first_name`, `last_name`) VALUES ('peter', 'moung');

# insert into devices data
INSERT INTO `avitek`.`devices` (`device_type`, `device_id`, `user_id`) VALUES ('1', '101010', '1');
INSERT INTO `avitek`.`devices` (`device_type`, `device_id`, `user_id`) VALUES ('2', '202020', '1');
INSERT INTO `avitek`.`devices` (`device_type`, `device_id`, `user_id`) VALUES ('1', '303030', '2');
INSERT INTO `avitek`.`devices` (`device_type`, `device_id`, `user_id`) VALUES ('1', '999999', '1');