{"id":81,"date":"2026-02-11T08:50:42","date_gmt":"2026-02-11T06:50:42","guid":{"rendered":"https:\/\/timofeistoljarov.wordpress.com\/?page_id=81"},"modified":"2026-02-11T08:50:42","modified_gmt":"2026-02-11T06:50:42","slug":"veebipood-sql-lausete-koostamine","status":"publish","type":"page","link":"https:\/\/timofeistoljarov.eu\/wp\/andmebaasid\/veebipood-sql-lausete-koostamine\/","title":{"rendered":"Veebipood SQL lausete koostamine"},"content":{"rendered":"\n

andmebaasi diagramm SQL Serverist<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

CREATE DATABASE veebipoodStoljarov;
USE veebipoodStoljarov;<\/p>\n\n\n\n

— Production
— table Categories
CREATE TABLE categories(
category_id int PRIMARY KEY identity(1,1),
category_name varchar(30) UNIQUE
);<\/p>\n\n\n\n

INSERT INTO categories(category_name)
VALUES (‘T-s\u00e4rgid’), (‘Tossud’), (‘Kingad’), (‘Mantel’), (‘Pusad’);
SELECT * FROM categories;<\/p>\n\n\n\n

–table brands
CREATE TABLE brands(
brand_id int PRIMARY KEY identity(1,1),
brand_name varchar(30) UNIQUE
);<\/p>\n\n\n\n

INSERT INTO brands(brand_name)
VALUES (‘Adidas’), (‘Nike’), (‘Calvin Klein’), (‘Gucci’), (‘Hugo boss’);
SELECT * FROM brands;<\/p>\n\n\n\n

–table products
CREATE TABLE products(
product_id int PRIMARY KEY identity(1,1),
product_name varchar(50),
brand_id int,
FOREIGN KEY (brand_id) references brands(brand_id),
category_id int,
FOREIGN KEY (category_id) references categories(category_id),
model_year int CHECK(model_year<2027),
list_price decimal(7,2)
);
INSERT INTO products(product_name, brand_id, category_id, model_year, list_price)
VALUES (‘Adidas tossud’, 1, 2, 2019, 60),
(‘Nike t-s\u00e4rk’, 2, 1, 2025, 45.59),
(‘Calvin Klein pusa’, 3, 5, 2018, 100.99),
(‘Gucci Mantel’, 4, 4, 2020, 159.99),
(‘Hugo boss Kingad’, 5, 3, 2026, 25.99);
SELECT * FROM products;<\/p>\n\n\n\n

–Sales
–table customers
CREATE TABLE customers(
customer_id int PRIMARY KEY identity(1,1),
first_name varchar(20),
last_name varchar(30),
phone int,
email TEXT,
street TEXT,
city TEXT,
state_ TEXT,
zip_code char(5)
);<\/p>\n\n\n\n

INSERT INTO customers(first_name, last_name, phone, email, street, city, state_, zip_code)
VALUES (‘Konstantin’, ‘Guakamole’, ‘52123456’, ‘kguakomol@mail.ee’, ‘\u00dcmera 8’, ‘Tallinn’, ‘Harjumaa’, ‘11093’),
(‘Dmitro’, ‘Gusenko’, ‘5312345’, ‘DGusen@mail.ee’, ‘Seli 2’, ‘Tallinn’, ‘Harjumaa’, ‘13112’),
(‘Danil’, ‘Kolbasenko’, ‘5412345’, ‘DanilKolbas@mail.ee’, ‘Kivila 2’, ‘Tallinn’, ‘Harjumaa’, ‘12121’);
SELECT * FROM customers;<\/p>\n\n\n\n

— stores table
CREATE TABLE stores(
store_id int PRIMARY KEY identity(1,1),
store_name varchar(20),
phone int,
email TEXT,
street TEXT ,
city TEXT,
state_ TEXT,
zip_code char(5)
);<\/p>\n\n\n\n

INSERT INTO stores(store_name, phone, email, street, city, state_, zip_code)
VALUES (‘Indian Shop’, 52123456, ’indianstore@mail.ee’, ‘\u00dcmera 15’, ‘Tallinn’, ‘Harjumaa’, 11090),
(‘Mania Clothes’, 5312345, ‘ManiaClothes@mail.ee’, ‘Seli 2’, ‘Tallinn’, ‘Harjumaa’, 13112),
(‘Clothes And More’, 5412211, ‘candm@mail.ee’, ‘Kivila 2’, ‘Tallinn’, ‘Harjumaa’, 12123);
SELECT * FROM stores;<\/p>\n\n\n\n

–staff table<\/p>\n\n\n\n

CREATE TABLE staffs(
staff_id int PRIMARY KEY identity(1,1),
first_name varchar(20),
last_name varchar(30),
email TEXT,
phone int,
active TEXT,
store_id int,
FOREIGN KEY (store_id) references stores(store_id),
manager_id int,
FOREIGN KEY (manager_id) references staffs(staff_id)
);<\/p>\n\n\n\n

INSERT INTO staffs(first_name, last_name, email, phone, active, store_id, manager_id)
VALUES (‘Maksimilian’,’Puhtejev’, ‘manag.maks@indianshop.ee’, 52123456, ‘true’, 1, 1),
(‘Sergei’,’Puhtejev’, ‘SergeiP@indianshop.ee’, 54123567, ‘true’, 1, 1),
(‘Andrey’,’Visel’, ‘AndreyV@indianshop.ee’, 52123456, ‘false’, 2, 3);
SELECT * FROM staffs;<\/p>\n\n\n\n

–Table Orders
CREATE TABLE orders(
order_id int PRIMARY KEY identity(1,1),
customer_id int,
FOREIGN KEY (customer_id) references customers(customer_id),
order_status varchar(30) check(order_status=’valmis’ or order_status=’t\u00f6\u00f6tlemisel’ or order_status=’makstud’),
order_date DATE,
required_date DATE,
shipped_date DATE,
store_id int,
FOREIGN KEY (store_id) references stores(store_id),
staff_id int
FOREIGN KEY (staff_id) references staffs(staff_id),
);<\/p>\n\n\n\n

SELECT * FROM orders;<\/p>\n\n\n\n

–stocks table
CREATE TABLE stocks(
store_id int,
FOREIGN KEY (store_id) references stores(store_id),
product_id int
FOREIGN KEY (product_id) references products(product_id),
quantity int
)<\/p>\n\n\n\n

— Order_items Table
CREATE TABLE order_items(
order_id int,
FOREIGN KEY (order_id) references orders(order_id),
item_id int PRIMARY KEY identity(1,1),
product_id int,
);<\/p>\n","protected":false},"excerpt":{"rendered":"

andmebaasi diagramm SQL Serverist CREATE DATABASE veebipoodStoljarov;USE veebipoodStoljarov; — Production— table CategoriesCREATE TABLE categories(category_id int PRIMARY KEY identity(1,1),category_name varchar(30) UNIQUE); INSERT INTO categories(category_name)VALUES (‘T-s\u00e4rgid’), (‘Tossud’), (‘Kingad’), (‘Mantel’), (‘Pusad’);SELECT * FROM categories; –table brandsCREATE TABLE brands(brand_id int PRIMARY KEY identity(1,1),brand_name varchar(30) UNIQUE); INSERT INTO brands(brand_name)VALUES (‘Adidas’), (‘Nike’), (‘Calvin Klein’), (‘Gucci’), (‘Hugo boss’);SELECT * FROM brands; –table […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":7,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-81","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/timofeistoljarov.eu\/wp\/wp-json\/wp\/v2\/pages\/81","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/timofeistoljarov.eu\/wp\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/timofeistoljarov.eu\/wp\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/timofeistoljarov.eu\/wp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/timofeistoljarov.eu\/wp\/wp-json\/wp\/v2\/comments?post=81"}],"version-history":[{"count":0,"href":"https:\/\/timofeistoljarov.eu\/wp\/wp-json\/wp\/v2\/pages\/81\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/timofeistoljarov.eu\/wp\/wp-json\/wp\/v2\/pages\/7"}],"wp:attachment":[{"href":"https:\/\/timofeistoljarov.eu\/wp\/wp-json\/wp\/v2\/media?parent=81"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}