-
Notifications
You must be signed in to change notification settings - Fork 0
/
create db.sql
36 lines (30 loc) · 916 Bytes
/
create db.sql
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
DROP TABLE soldTv;
DROP TABLE tv;
CREATE TABLE tv(
id integer PRIMARY KEY NOT NULL,
brand string NULL,
manufacturer string NOT NULL CHECK(length(manufacturer) >= 3),
model string NOT NULL CHECK(length(model) >= 2),
year integer CHECK(year >= 2010)
);
PRAGMA foreign_keys = ON;
CREATE TABLE soldTv(
id Sqlite3_int64 PRIMARY KEY,
sold_count Sqlite3_int64 NOT NULL CHECK(sold_count >= 0),
available Sqlite3_int64 NOT NULL CHECK(available >= 0),
tv_id Sqlite3_int64 NOT NULL UNIQUE,
FOREIGN KEY(tv_id) REFERENCES tv(id)
);
INSERT INTO tv(brand, manufacturer, model, year)
VALUES
('samsung', 'korea', 'a1', 2011),
('lg', 'korea', 'qwe123', 2013),
(NULL, 'china', 'a21', 2015),
('samsung', 'korea', 'a2', 2012),
('samsung', 'korea', 'a3', 2011),
('samsung', 'korea', 'a4', 2011);
INSERT INTO soldTv(sold_count, available, tv_id)
VALUES
(5000, 14345, 1),
(123534512, 32543, 3),
(55, 100000, 6);