L78ZqXhn

1CREATE DATABASE myShop;
2USE myShop;
3CREATE TABLE Product
4(
5    ID serial primary key,
6    Name varchar(50) not null,
7    Descr varchar(150) not null,
8    Qnt int unsigned
9);
10 
11INSERT INTO Product values
12(null,'PC','Best ever made',4),
13(null,'Yo-yo','AAAAAAAAA',1),
14(null,'X-BOX ONE','I Want one',5),
15(null,'Drone','With 4K camera',124),
16(null,'Mistery Box','Try your luck',12);
17 
18CREATE TABLE Users
19(   
20	ID serial primary key,
21    Name varchar(50) not null,
22    LastName varchar(50) not null,
23    email varchar(150) not null unique
24);
25 
26INSERT INTO Users values
27(null,'John','Smith','j@smith.com'),
28(null,'Sub','Zero','subzero@mk10.com'),
29(null,'Darth','Vader','Vader@rule.universe');
30 
31CREATE TABLE Orders
32(
33    ID Serial primary key,
34    Prod_ID bigint unsigned not null,
35    Order_date date not null,
36    User_ID bigint unsigned not null,
37    foreign key(Prod_ID) references Product(ID) on delete cascade,
38    foreign key(User_ID) references Users(ID) on delete cascade
39);
40INSERT INTO Orders values
41(null,1,'2016-12-23',2),
42(null,5,'2017-10-17',1),
43(null,4,'2016-09-09',3),
44(null,5,'2017-09-17',1);
45
46
47
48IF NOT EXISTS, KURIANT LENTELES
49IF EXISTS DROPINANT LENTELES
50
51DROP TABLE IF EXISTS Users;
52
53
54
55
56ALTER TABLE Product ADD COLUMN Price float unsigned;
57
58
59//ALTER TABLE Product ADD COLUMN release_date date not null default '2017-04-20' BETWEEN NAME AND DESCR;
60
61ALTER TABLE Product ADD COLUMN release_date date not null default '2017-04-20' AFTER NAME;
62
63ALTER TABLE Product ADD COLUMN productNr INT UNSIGNED first;
64//ALTER TABLE Product ADD COLUMN productNrR INT UNSIGNED last;
65
66ALTER TABLE Product DROP COLUMN productNr;
67
68ALTER TABLE Product MODIFY price float unsigned default 0.01;
69
70ALTER TABLE Product CHANGE price stockPrice double unsigned default 0.02;
71
72stockPrice -> price // i float //idedame po stulpeliu NAME
73
74
75
76ALTER TABLE Product CHANGE stockPrice price float unsigned default 0.02 AFTER name;
77
78
79
80
81ALTER TABLE orders drop foreign key orders_ibfk_1;
82ALTER TABLE orders drop foreign key orders_ibfk_2;
83
84ALTER TABLE orders add constraint user_id_fk foreign key(user_id) references users(id);