sql.sql

/* drop tables */

IF OBJECT_ID('dbo.stockproduct', 'U') IS NOT NULL 
  DROP TABLE dbo.stockproduct; 
IF OBJECT_ID('dbo.product', 'U') IS NOT NULL 
  DROP TABLE dbo.product; 
IF OBJECT_ID('dbo.category', 'U') IS NOT NULL 
  DROP TABLE dbo.category; 
IF OBJECT_ID('dbo.stock', 'U') IS NOT NULL 
  DROP TABLE dbo.stock;


/* category table */
create table category(
	id int primary key identity(1,1),
	name nvarchar(100) 
)
/* product table*/
create table product(
	id int primary key identity(1,1),
	name nvarchar(100),
	price int,
	categoryId int FOREIGN KEY references category(id) on delete set null
)
/* stock table*/
create table stock(
	id int primary key identity(1,1),
	code nvarchar(100) unique
)
/* stockproduct table*/
create table stockproduct(
	id int primary key identity(1,1),
	amount int,
	productId int FOREIGN KEY references product(id) on delete set null,
	stockId int FOREIGN KEY references stock(id) on delete set null
)
/* inserts*/
insert into category values('electrocasnice')
insert into category values('mancare')
insert into category values('telefoane')

insert into product values('iphone',100,2)
insert into product values('htc',1200,2)
insert into product values('zacusca',12,1)

insert into stock values('30abc')
insert into stock values('20row')
insert into stock values('sqlmasterboss')

insert into stockproduct values(100,0,0)
insert into stockproduct values(104,1,1)
insert into stockproduct values(23,2,2)
/* selects */
select * from category
select * from product
select * from stock
select * from stockproduct