· 6 years ago · Sep 30, 2019, 03:44 PM
1#!/bin/bash
2mysql -u root -p -e"
3CREATE DATABASE poli_shop;
4USE poli_shop;
5
6CREATE TABLE IF NOT EXISTS products (
7 id INT(11) NOT NULL AUTO_INCREMENT,
8 title VARCHAR(30),
9 price DECIMAL(9,2) NOT NULL,
10 description VARCHAR(1000) NOT NULL,
11 date_of_creation DATETIME NOT NULL,
12 PRIMARY KEY (id)
13);
14
15____________________
16
17#!/bin/bash
18mysql -u root -p -e"
19
20USE poli_shop;
21
22CREATE TABLE IF NOT EXISTS users (
23 id INT(11) NOT NULL AUTO_INCREMENT,
24 first_name VARCHAR(20) NOT NULL,
25 last_name VARCHAR(20) NOT NULL,
26 address VARCHAR(100) NOT NULL,
27 country_code CHAR(2) NOT NULL,
28 PRIMARY KEY (id)
29);
30
31"
32______________
33
34#!/bin/bash
35mysql -u root -p -e"
36
37USE poli_shop;
38
39CREATE TABLE IF NOT EXISTS orders (
40 id INT(11) NOT NULL AUTO_INCREMENT,
41 order_date DATETIME NOT NULL,
42 user_id INT(11) NOT NULL,
43 paid_amount DECIMAL(9,2) NOT NULL,
44 PRIMARY KEY (id)
45);
46
47CREATE TABLE IF NOT EXISTS order_lines (
48 id INT(11) NOT NULL AUTO_INCREMENT,
49 order_id INT(11) NOT NULL,
50 product_id INT(11) NOT NULL,
51 quantity INT(11) NOT NULL,
52 single_price DECIMAL(9,2) NOT NULL,
53 computed_price DECIMAL(9,2) NOT NULL,
54 PRIMARY KEY (id)
55);
56
57__________-
58
59
60#!/bin/bash
61mysql -u root -p -e"
62
63USE poli_shop;
64
65INSERT INTO products (title,price, description, date_of_creation) VALUES
66('Iphone 10',2000.50,
67'This Iphone is great. It is the last iPhone - but not the best Iphone - more like the last not as good iPhone',NOW()),
68('knife', 35.20, 'High quality, made in Japan',NOW()),
69('Bike', 700.00, 'A good road bike', '2019-08-12 12:04:00'),
70('LED TV SONY', 1800.00, 'Has amazing picture', '2019-08-15 16:00:00');
71
72INSERT INTO users (first_name,last_name,address,country_code) VALUES
73('Ivan','Ivanov','Aleksander Stamboliiski 33','BG'),
74('John','Smith','281 Portobello Road','EN'),
75('Maria','Vasileva','Vasil Aprilov 7','BG'),
76('Georgi','Grigorov','Peter Stoev 11a','BG'),
77('Georgios','Alli','Nana Mouskouri 5','GR');
78
79INSERT INTO orders (order_date, user_id, paid_amount) VALUES
80('2019-08-26 00:00:00', 2, 5600.50),
81(NOW(), 1, 2100.00),
82(NOW(), 1, 840.80),
83(NOW(), 4, 840.80),
84(NOW(), 5, 2100.00);
85
86INSERT INTO order_lines (order_id, product_id, quantity,
87 single_price, computed_price) VALUES
88(1, 1, 1, 2000.50, 2000.50),
89(1, 4, 2, 1800.00, 3600.00),
90
91(2, 3, 3, 700.00, 2100.00),
92
93(3, 2, 4, 35.20, 140.80),
94(3, 3, 1, 700.00, 700.00),
95
96(4, 2, 4, 35.20, 140.80),
97(4, 3, 1, 700.00, 700.00),
98
99(5, 3, 3, 700.00, 2100.00);
100"