Notes

Create Database BerkayDB
go
use BerkayDB
go
Create Table Categories
(
ID int IDENTITY,
CategoryName nvarchar(15) NOT NULL,
Descriptionn ntext NULL,
Picture image NULL,
Constraint PK_Categories Primary Key(ID)


)

go

Create Table Customers
(
ID int IDENTITY,
CustomerName nvarchar(20) Not NULL,
CustomerLastName nvarchar(20) Not NULL,
Phone nvarchar(24) Not NULL,
Mail nvarchar(30) NULL,
PostalCode nvarchar(10) NULL,
TC nvarchar(11) Not NULL,
Constraint PK_Customers Primary Key(ID),

)

go

Create Table ElectronicStuff
(
ID int IDENTITY,
ProductName nvarchar(30),
ProductModel nvarchar(30),
ProductInformation text,
CategoryID int,
Constraint PK_ElectronicStuff Primary Key(ID),
Constraint FK_ElectronicStuff_CategoryID Foreign Key(CategoryID) References Categories(ID)
)

go

Create Table TextileProducts
(
ID int IDENTITY,
ProductName nvarchar(30),
ProductBrand nvarchar(30),
ProductionPlace nvarchar(30),
CategoryID int,
Constraint PK_TextileProducts Primary Key(ID),
Constraint FK_TextileProducts_CategoryID Foreign Key(CategoryID) References Categories(ID)
)

go

Create Table Sportsinstruments
(
ID int IDENTITY,
ProductName nvarchar(30),
ProductBrand nvarchar(30),
ProductionPlace nvarchar(30),
CategoryID int,
Constraint PK_Sportsinstruments Primary Key(ID),
Constraint FK_Sportsinstruments_CategoryID Foreign Key(CategoryID) References Categories(ID)

)




Create Table Cosmetic
(
ID int IDENTITY,
PoductName nvarchar(30),
ProductModel nvarchar(30),
ProductBrand nvarchar(30),
ProductionPlace nvarchar(30),
CategoryID int,
Constraint PK_Cosmetic Primary Key(ID),
Constraint FK_Cosmetic_CategoryID Foreign Key(CategoryID) References Categories(ID)
)


Create Table CookingUtensils
(
ID int IDENTITY,
PoductName nvarchar(30),
ProductModel nvarchar(30),
ProductBrand nvarchar(30),
ProductionPlace nvarchar(30),
CategoryID int,
Constraint PK_CookingUtensils Primary Key(ID),
Constraint FK_CookingUtensils_CategoryID Foreign Key(CategoryID) References Categories(ID)
)

go

Create Table StationeryProducts
(
ID int IDENTITY,
PoductName nvarchar(30),
CategoryID int,
Constraint PK_StationeryProducts Primary Key(ID),
Constraint FK_StationeryProducts_CategoryID Foreign Key(CategoryID) References Categories(ID)
)


go

Create Table Ornaments
(
ID int IDENTITY,
PoductName nvarchar(30),
ProductModel nvarchar(30),
ProductBrand nvarchar(30),
ProductionPlace nvarchar(30),
CategoryID int,
Constraint PK_Ornaments Primary Key(ID),
Constraint FK_Ornaments_CategoryID Foreign Key(CategoryID) References Categories(ID)
)



go

Create Table Shoes
(
ID int IDENTITY,
ProductBrand varchar(30),
ProductionPlace varchar(30),
CategoryID int,
Constraint PK_Shoes Primary Key(ID),
Constraint FK_Shoes_CategoryID Foreign Key(CategoryID) References Categories(ID)

)

go

Create Table Suppliers
(
ID int IDENTITY,
Phone nvarchar(24) NULL,
Fax nvarchar(24) NULL,
Constraint PK_Suppliers Primary Key(ID)

)



go

Create Table CustomerReviews
(
ID int IDENTITY,
CustomerName nvarchar(30),
CustomerReviews nvarchar(200),
Constraint PK_CustomerReviews Primary Key(ID)
)

go

Create Table Stocks
(
ID int IDENTITY,
Numberofstocks nvarchar(500),
Constraint PK_Stocks Primary Key(ID)
)

go

Create Table Addresses
(
ID int IDENTITY,
ProductAddress nvarchar(150),
CustomerID int,
SupplierID int,
Constraint PK_Addresses Primary Key(ID),
Constraint FK_Addresses_CustomerID Foreign Key(CustomerID) References Customers(ID),
Constraint FK_Address_SupplierID Foreign Key(SupplierID) References Suppliers(ID)

)

go

Create Table ProductComplaints
(
ID int IDENTITY,
CustomerName nvarchar(50),
Complaint nvarchar(200),
SupplierID int,
Constraint PK_ProductComplaints Primary Key(ID),
Constraint FK_ProductComplaints_SupplierID Foreign Key(SupplierID) References Suppliers(ID)
)

go

Create Table Employees
(
ID int IDENTITY,
FirstName nvarchar(20) NOT NULL,
LastName nvarchar(20) NOT NULL,
SupplierID int,
Constraint PK_Employees Primary Key(ID),
Constraint FK_Employees_SupplierID Foreign Key(SupplierID) References Suppliers(ID)

)

go

create table Payments
(
ID int IDENTITY,
AmountPaid nvarchar(10),
Remaining nvarchar (10),
Other nvarchar (50),
CustomerID int,
Constraint PK_Payments Primary Key(ID),
Constraint FK_Payments_CustomerID Foreign Key(CustomerID) References Customers(ID)

)

go

create table Cargo
(
ID int IDENTITY,
ShippingCompanyName nvarchar (50),
SupplierID int,
CustomerID int,
Constraint PK_Cargo Primary Key(ID),
Constraint FK_Cargo_SupplierID Foreign Key(SupplierID) References Suppliers(ID),
Constraint FK_Cargo_CustomerID Foreign Key(CustomerID) References Customers(ID)


)







Create Proc SP_InsertEmployees
(


@FirstName nvarchar(20),
@LastName nvarchar(20)


)

As

Begin

insert into Employees (FirstName, LastName)

Values (@FirstName,@LastName)

End

EXEC SP_InsertEmployees 'Berkay','Yıldırım'
EXEC SP_InsertEmployees 'Samet','Eryiğit'
EXEC SP_InsertEmployees 'Hakan','Dilek'
EXEC SP_InsertEmployees 'Burak','Sınmaz'
EXEC SP_InsertEmployees 'Abdullah','Giray'
EXEC SP_InsertEmployees 'Deniz','Petek'
EXEC SP_InsertEmployees 'Caner','Aydın'



Create Proc SP_Customers
(
@CustomerName nvarchar(20),
@CustomerLastName nvarchar(20),
@Phone nvarchar(24),
@Mail nvarchar(30),
@PostalCode nvarchar(10),
@TC nvarchar(11)
)

As
Begin
insert into Customers (CustomerName, CustomerLastName, Phone, Mail, PostalCode, TC)
Values (@CustomerName,@CustomerLastName,@Phone,@Mail,@PostalCode,@TC)
End

Create Proc SP_Cargo
(
@ShippingCompanyName nvarchar (50)
)

As
Begin
insert into Cargo (ShippingCompanyName)

Values (@ShippingCompanyName)

End




Create Proc SP_Payments
(

@AmountPaid nvarchar(10),
@Remaining nvarchar (10),
@Other nvarchar (50)
)
As
Begin

insert into Payments (AmountPaid, Remaining, Other)
Values (@AmountPaid,@Remaining,@Other)
End





Create Proc SP_Addresses
(
@ProductAddress nvarchar(150)
)
As
Begin
insert into Addresses (ProductAddress)
Values (@ProductAddress)
End


Create Proc SP_Categories
(
@CategoryName nvarchar(15),
@Descriptionn ntext,
@Picture image
)
As
Begin
insert into Categories (CategoryName, Descriptionn, Picture)
Values (@CategoryName, @Descriptionn, @Picture)
End


Create Proc SP_Suppliers
(
@Phone nvarchar(24),
@Fax nvarchar(24)

)
As
Begin
insert into Suppliers (Phone, Fax)
Values (@Phone, @Fax)
End





Create Proc SP_Stocks
(
@Numberofstocks nvarchar(500)

)
As
Begin
insert into Stocks (Numberofstocks)
Values (@Numberofstocks)
End




create proc SP_UpdateEmployees
(
@ID int,
@FirsName nvarchar(20),
@LastName nvarchar(20)

)
AS
BEGIN
UPDATE Employees
SET FirstName=@FirstName,
LastName=@LastName
Where ID=@ID
END



create proc SP_DeleteEmployees
(
@ID INT
)
AS
BEGIN
Delete From Employees
where ID=@ID

End





Create Wiew WiewEmployees

As
Select FirstName,CategoryName
from Employees INNER JOIN Categories
On Employees.ID=Categories.ID



Create Trigger NewEmployee
On Employees
after insert
as
begin
select'New Employee Came'
end