WSn7HCHr

1DROP SCHEMA IF EXISTS HardwareStore;
2CREATE SCHEMA IF NOT EXISTS HardwareStore;
3USE HardwareStore;
4CREATE TABLE Towns
5(
6    Id INT PRIMARY KEY AUTO_INCREMENT,
7    Name VARCHAR(30) NOT NULL
8);
9
10CREATE TABLE Duties(
11	Id INT PRIMARY KEY AUTO_INCREMENT,
12    Duty VARCHAR(20) NOT NULL
13);
14
15CREATE TABLE Employees
16(
17    Id INT PRIMARY KEY AUTO_INCREMENT,
18    First_Name VARCHAR(30) NOT NULL,
19    Second_Name VARCHAR(30) NOT NULL,
20    Last_Name VARCHAR(30) NOT NULL,
21    Password CHAR(5) NOT NULL,
22    EGN CHAR(10) NOT NULL,
23    Duty_id INT NOT NULL,
24    Town_Id INT NOT NULL,
25    FOREIGN KEY (Town_Id) REFERENCES Towns(Id),
26    FOREIGN KEY (Duty_id) REFERENCES Duties(Id)
27);
28
29CREATE TABLE PaymentMethod
30(
31	Id INT  PRIMARY KEY AUTO_INCREMENT,
32    Method VARCHAR(10) NOT NULL
33);
34
35CREATE TABLE Invoice
36(
37	Id INT PRIMARY KEY AUTO_INCREMENT,
38	Order_No INT NOT NULL,
39    Date DATE NOT NULL,
40    Time TIME NOT NULL,
41    Employee_Id INT NOT NULL,
42    Num_Of_Client INT NOT NULL,
43    Total DOUBLE NOT NULL,
44    Payment_Method_Id INT NOT NULL,
45    FOREIGN KEY (Employee_Id) REFERENCES Employees(Id),
46    FOREIGN KEY (Payment_Method_Id) REFERENCES PaymentMethod(Id)
47);
48
49CREATE TABLE Items
50(
51	Id INT PRIMARY KEY AUTO_INCREMENT,
52    Product_Name VARCHAR(30) NOT NULL,
53    Single_Price DOUBLE NOT NULL,
54    Quantity INT NOT NULL,
55    Status ENUM("Factory new", "Second hand")
56);
57
58CREATE TABLE Invoice_Items
59(
60	Id INT PRIMARY KEY AUTO_INCREMENT,
61    Item_Id INT NOT NULL,
62    Quantity INT NOT NULL,
63    Single_Price DOUBLE NOT NULL,
64    Total DOUBLE NOT NULL,
65    Invoice_Id INT NOT NULL,
66	FOREIGN KEY (Item_Id) REFERENCES Items(Id)
67);
68
69INSERT INTO towns (Name)
70VALUES
71("Plovdiv"),
72("Sofia");
73
74INSERT INTO Duties (Duty)
75VALUES
76("Cleaner"),
77("Manager"),
78("Salesman");
79
80INSERT INTO employees (First_Name,Second_Name,Last_Name,Password,EGN,Duty_id,Town_Id)
81VALUES
82("Valentin", "Antoniev", "Hadzhiminov", 12345, 0150181234,3, 1),
83("Jivko", "Dimitrov", "Kolev", 54321, 0150185678, 3,1);
84
85INSERT INTO paymentmethod (Method)
86VALUES
87("Money"),
88("Card");
89
90INSERT INTO invoice (Order_No,Date,Time,Employee_Id,Num_Of_Client,Total,Payment_Method_Id)
91VALUES
92(5809, "19.02.23", "19:39", 1, 23, 739.2, 1);
93
94INSERT INTO Items (Product_Name, Single_Price, Quantity, Status)
95VALUES
96("Mouse", 6.40, 40, "Factory new"),
97("Monitor", 180, 20, "Factory new");
98
99
100INSERT INTO Invoice_Items (Item_Id, Quantity, Single_Price, Total, Invoice_Id)
101VALUES
102(2, 3, 6.40, 19.20, 1),
103(1, 4, 180, 720, 1);
104
105
106SELECT*FROM Items;
107
108/* List of products */
109SELECT i.Product_Name, it.Quantity, i.Single_Price, it.Total
110FROM items AS i 
111INNER JOIN invoice_items AS it ON it.Item_Id=i.id
112WHERE Invoice_Id =1;