Notes

create table production.categories (
category_id int identity (1, 1) primary key,
category_name varchar (255) not null
);

create table production.brands (
brand_id int identity (1, 1) primary key,
brand_name varchar (255) not null
);

create table production.products (
product_id int identity (1, 1) primary key,
product_name varchar (255) not null,
brand_id int not null,
category_id int not null,
model_year smallint not null,
list_price decimal (10, 2) not null,
foreign key (category_id) references production.categories (category_id) ,
foreign key (brand_id) references production.brands (brand_id)
);

create table sales.customers (
customer_id int identity (1, 1) primary key,
first_name varchar (255) not null,
last_name varchar (255) not null,
phone varchar (25),
email varchar (255) not null,
street varchar (255),
city varchar (50),
state varchar (25),
zip_code varchar (5)
);

create table sales.stores (
store_id int identity (1, 1) primary key,
store_name varchar (255) not null,
phone varchar (25),
email varchar (255),
street varchar (255),
city varchar (255),
state varchar (10),
zip_code varchar (5)
);

create table sales.staffs (
staff_id int identity (1, 1) primary key,
first_name varchar (50) not null,
last_name varchar (50) not null,
email varchar (255) not null unique,
phone varchar (25),
active tinyint not null,
store_id int not null,
manager_id int,
foreign key (store_id) references sales.stores (store_id),
foreign key (manager_id) references sales.staffs (staff_id)
);

create table sales.orders (
order_id int identity (1, 1) primary key,
customer_id int,
order_status tinyint not null,
order_date date not null,
required_date date not null,
shipped_date date,
store_id int not null,
staff_id int not null,
foreign key (customer_id) references sales.customers (customer_id) ,
foreign key (store_id) references sales.stores (store_id) ,
foreign key (staff_id) references sales.staffs (staff_id)
);

create table sales.order_items (
order_id int,
item_id int,
product_id int not null,
quantity int not null,
list_price decimal (10, 2) not null,
discount decimal (4, 2) not null default 0,
primary key (order_id, item_id),
foreign key (order_id) references sales.orders ,
foreign key (product_id) references production.products
);

create table production.stocks (
store_id int,
product_id int,
quantity int,
primary key (store_id, product_id),
foreign key (store_id) references sales.stores (store_id),
foreign key (product_id) references production.products (product_id)
);

select * from [production].[brands]
select * from [production].[categories]
select * from [production].[products]
select * from [production].[stocks]
select * from [sales].[customers]
select * from [sales].[order_items]
select * from [sales].[orders]
select * from [sales].[staffs]
select * from [sales].[stores]


--2
select * from [production].[products]
select * from [production].[brands]

--simple query
select production.products.list_price, production.products.model_year, production.brands.brand_name
from production.brands
inner join production.products on
production.brands.brand_id = production.products.brand_id
group by production.products.model_year,production.products.list_price, production.brands.brand_name

--pivot query
select * from (
select production.products.list_price as listprice, production.products.model_year as years, production.brands.brand_name as brandname
from production.brands inner join
production.products on production.brands.brand_id = production.products.brand_id

) as salesresults
pivot (
sum([listprice])
for [brandname]
in (
[electra],[trek],[pure cycles]
)
) as pivot_salesregionmonth
order by years;