AdvancedMySQL/Prerequistes-Create database, tables and populte them.sql at main · eddahbarasa/AdvancedMySQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
/*Prerequisite: create database, create and populate tables*/
CREATE DATABASE IF NOT EXISTS Lucky_Shrub;

USE Lucky_Shrub;
 /*Creating tables*/
CREATE TABLE Clients (ClientID VARCHAR(10) primary key, FullName VARCHAR(100), ContactNumber INT, AddressID INT);

CREATE TABLE Products (ProductID VARCHAR(10) primary key, ProductName VARCHAR(100), BuyPrice DECIMAL(6,2), SellPrice DECIMAL(6,2), NumberOfItems INT);

Create table Addresses(AddressID INT PRIMARY KEY, Street VARCHAR(255), County VARCHAR(100));

CREATE TABLE Employees (EmployeeID INT primary key, FullName VARCHAR(100), JobTitle VARCHAR(50), Department VARCHAR(200), AddressID INT);

CREATE TABLE Activity( ActivityID INT PRIMARY KEY, Properties JSON );

CREATE TABLE Audit(AuditID INT AUTO_INCREMENT PRIMARY KEY, OrderDateTime TIMESTAMP NOT NULL  );

CREATE TABLE Orders (OrderID INT NOT NULL PRIMARY KEY,
ClientID VARCHAR(10), ProductID VARCHAR(10), Quantity INT, Cost DECIMAL(6,2), Date DATE,
FOREIGN KEY (ClientID) REFERENCES Clients(ClientID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID));

CREATE TABLE Notifications (NotificationID INT AUTO_INCREMENT PRIMARY KEY, Notification VARCHAR(256), DateTime TIMESTAMP NOT NULL);

/*populating tables*/

INSERT INTO Employees (EmployeeID, FullName, JobTitle, Department, AddressID) VALUES
(1, "Seamus Hogan", "Manager", "Management", 7),
(2, "Thomas Eriksson", "Assistant ", "Sales", 8),
(3, "Simon Tolo", "Head Chef", "Management", 9),
(4, "Francesca Soffia", "Assistant  ", "Human Resources", 10),
(5, "Emily Sierra", "Accountant", "Finance", 11),
(6, "Greta Galkina", "Accountant", "Finance", 12);

INSERT INTO Activity(ActivityID, Properties) VALUES
(1, '{ "ClientID": "Cl1", "ProductID": "P1", "Order": "True" }' ),
(2, '{ "ClientID": "Cl2", "ProductID": "P4", "Order": "False" }' ),
(3, '{ "ClientID": "Cl5", "ProductID": "P5", "Order": "True" }' );

INSERT INTO Clients(ClientID, FullName, ContactNumber, AddressID) VALUES

("Cl1", "Takashi Ito", 351786345, 1),
("Cl2", "Jane Murphy", 351567243, 2),
("Cl3", "Laurina Delgado", 351342597, 3),
("Cl4", "Benjamin Clauss", 351342509, 4),
("Cl5", "Altay Ayhan", 351208983, 5),
("Cl6", "Greta Galkina", 351298755, 6);

 INSERT INTO Products (ProductID, ProductName, BuyPrice, SellPrice, NumberOfITems) VALUES
("P1", "Artificial grass bags ", 40, 50, 100),
("P2", "Wood panels", 15, 20, 250),
("P3", "Patio slates", 35, 40, 60),
("P4", "Sycamore trees ", 7, 10, 50),
("P5", "Trees and Shrubs", 35, 50, 75),
("P6", "Water fountain", 65, 80, 15);

INSERT INTO Addresses(AddressID, Street, County) VALUES
(1, ",291 Oak Wood Avenue", "Graham County"),
(2, "724 Greenway Drive", "Pinal County"),
(3, "102 Sycamore Lane", "Santa Cruz County"),
(4, "125 Roselawn Close", "Gila County"),
(5, "831 Beechwood Terrace", "Cochise County"),
(6, "755 Palm Tree Hills", "Mohave County"),
(7, "751 Waterfall Hills", "Tuscon County") ,
(8, "878 Riverside Lane", "Tuscon County") ,
(9, "908 Seaview Hills", "Tuscon County"),
(10, "243 Waterview Terrace", "Tuscon County"),
(11, "148 Riverview Lane", "Tuscon County"),
(12, "178 Seaview Avenue", "Tuscon County");

INSERT INTO Orders (OrderID, ClientID, ProductID , Quantity, Cost, Date) VALUES
(1, "Cl1", "P1", 10, 500, "2020-09-01"),
(2, "Cl2", "P2", 5, 100, "2020-09-05"),
(3, "Cl3", "P3", 20, 800, "2020-09-03"),
(4, "Cl4", "P4", 15, 150, "2020-09-07"),
(5, "Cl3", "P3", 10, 450, "2020-09-08"),
(6, "Cl2", "P2", 5, 800, "2020-09-09"),
(7, "Cl1", "P4", 22, 1200, "2020-09-10"),
(8, "Cl3", "P1", 15, 150, "2020-09-10"),
(9, "Cl1", "P1", 10, 500, "2020-09-12"),
(10, "Cl2", "P2", 5, 100, "2020-09-13"),
(11, "Cl4", "P5", 5, 100, "2020-09-15"),
(12, "Cl1", "P1", 10, 500, "2022-09-01"),
(13, "Cl2", "P2", 5, 100, "2022-09-05"),
(14, "Cl3", "P3", 20, 800, "2022-09-03"),
(15, "Cl4", "P4", 15, 150, "2022-09-07"),
(16, "Cl3", "P3", 10, 450, "2022-09-08"),
(17, "Cl2", "P2", 5, 800, "2022-09-09"),
(18, "Cl1", "P4", 22, 1200, "2022-09-10"),
(19, "Cl3", "P1", 15, 150, "2022-09-10"),
(20, "Cl1", "P1", 10, 500, "2022-09-12"),
(21, "Cl2", "P2", 5, 100, "2022-09-13"),
(22, "Cl2", "P1", 10, 500, "2021-09-01"),
(23, "Cl2", "P2", 5, 100, "2021-09-05"),
(24, "Cl3", "P3", 20, 800, "2021-09-03"),
(25, "Cl4", "P4", 15, 150, "2021-09-07"),
(26, "Cl1", "P3", 10, 450, "2021-09-08"),
(27, "Cl2", "P1", 20, 1000, "2022-09-01"),
(28, "Cl2", "P2", 10, 200, "2022-09-05"),
(29, "Cl3", "P3", 20, 800, "2021-09-03"),
(30, "Cl1", "P1", 10, 500, "2022-09-01" );