CREATE TABLE Customers(
       custNo TEXT PRIMARY KEY,
       name TEXT,
       born INTEGER, 
       bonus INTEGER DEFAULT 0, 
       address TEXT DEFAULT '?', 
       email TEXT
);

CREATE TABLE Products(
       number TEXT PRIMARY KEY, 
       prodName TEXT, 
       description TEXT, 
       price REAL CHECK (price > 0.0 AND price < 5000.0),
       manufID TEXT REFERENCES Manufacturers(ID)
       	       ON DELETE SET NULL
	       ON UPDATE CASCADE
);

CREATE TABLE Manufacturers(
       ID TEXT PRIMARY KEY, 
       manufName TEXT, 
       phone TEXT
);

CREATE TABLE Orders(
       orderNo TEXT PRIMARY KEY, 
       deliver TEXT, 
       status TEXT, 
       custNo TEXT
);
CREATE TABLE BelongsTo(
       orderNo TEXT, 
       productNo TEXT, 
       count INTEGER,
       PRIMARY KEY (orderNo, productNo)
);

CREATE TABLE Productgroups(
       name TEXT PRIMARY KEY,
       supergroup TEXT
);

INSERT INTO Manufacturers
VALUES('R122', 'Nike', '09-8011');
INSERT INTO Manufacturers
VALUES('M554', 'Apple', '09-5001');
INSERT INTO Manufacturers
VALUES('S123', 'Samsung', '020-7300');
INSERT INTO Manufacturers
VALUES('L711', 'Sony', '020-6500');
INSERT INTO Manufacturers
VALUES('F542', 'Fiskars', '020-43910');
INSERT INTO Manufacturers
VALUES('A432', 'Adidas', '09-70911');

INSERT INTO Products
VALUES('T-33441', 'Samsung Galaxy A5', 'cellphone', 250.0, 'S123');
INSERT INTO Products
VALUES('R-55336', 'IPad Air 2', 'tablet', 495.0, 'M554');
INSERT INTO Products
VALUES('T-77445', 'Superstar Track M', 'jacket', 30.0, 'A432');
INSERT INTO Products
VALUES('S-65221', 'Brasserie 24', 'pan', 33.50,'F542');
INSERT INTO Products
VALUES('T-33442', 'NX 300 Smart Camera', 'camera', 399, 'S123');
INSERT INTO Products
VALUES('T-33455', 'Sony Cyber-shot', 'camera', 463.0, 'L711');
INSERT INTO Products
VALUES('P-48221', 'Teema 26', 'plate', 16.00, 'F542');
INSERT INTO Products
VALUES('T-29783', '40 Smart LED-TV', 'TV', 519.0, 'L711');
INSERT INTO Products
VALUES('T-27511', '55 Smart LED-TV', 'TV', 799.90, 'S123');

INSERT INTO Customers
VALUES('112233', 'Teemu Teekkari', 1995, 55, 'Servinkuja 3', 'tteekkari@gmail.com');
INSERT INTO Customers
VALUES('554422', 'Riina Raksalainen', 1993, 43, 'Otaranta 8', 'riinar@yahoo.com');
INSERT INTO Customers
VALUES('37856', 'Antti Virta', 1970, 12, 'Aaltokatu 4', 'antti@hotmail.com');
INSERT INTO Customers
VALUES('77233', 'Nina Teekkari', 1991, 20, 'Servinkuja 3', 'nite@hotmail.com');

INSERT INTO Orders
VALUES('469666', 'Post', 'delivered', '112233');
INSERT INTO Orders
VALUES('469677', 'DHL', 'packed', '112233');
INSERT INTO Orders
VALUES('469688', 'Post', 'returned','37856');
INSERT INTO Orders
VALUES('469699', 'pickup', 'waiting', '37856');
INSERT INTO Orders
VALUES('469700', 'Post', 'returned','77233');

INSERT INTO BelongsTo
VALUES('469666', 'T-33441', 1);
INSERT INTO BelongsTo
VALUES('469666', 'S-65221', 2);
INSERT INTO BelongsTo
VALUES('469688', 'T-77445', 3);
INSERT INTO BelongsTo
VALUES('469699', 'T-33441', 1);
