ismerdatabase.txt

CREATE DATABASE ismerdatabase
GO
CREATE SCHEMA Employeess
GO
CREATE TABLE HumanResourcesEmployeess(
HRid INT NOT NULL PRIMARY KEY IDENTITY(1,1),
HRname NVARCHAR NOT NULL,
HRsurname NVARCHAR NOT NULL,
HRSSN INT NOT NULL UNIQUE)
CREATE TABLE ITEmployeess(
ITid INT NOT NULL PRIMARY KEY IDENTITY(1,1),
ITname NVARCHAR(50)NOT NULL,
ITsurname NVARCHAR(50)NOT NULL,
ITSSN int NOT NULL UNIQUE)
CREATE TABLE Cleaners(
Cleanerid INT NOT NULL PRIMARY KEY IDENTITY(1,1),
Cleanername NVARCHAR(50)NOT NULL,
Cleanersurname NVARCHAR(50)NOT NULL,
CleanerSSN int NOT NULL UNIQUE)
CREATE TABLE SecurityEmployees(
Securityid INT NOT NULL PRIMARY KEY IDENTITY(1,1),
Securityname NVARCHAR(50)NOT NULL,
Securitysurname NVARCHAR(50)NOT NULL,
SecuritySSN int NOT NULL UNIQUE,
SecuritySalary float NOT NULL)
INSERT INTO [dbo].[HumanResourcesEmployees](HRname,HRsurname,HRSSN)
VALUES('Ali','Demir',200);
INSERT INTO [dbo].[HumanResourcesEmployees](HRname,HRsurname,HRSSN)
VALUES('Ayşe','Çelik',201);
INSERT INTO [dbo].[HumanResourcesEmployees](HRname,HRsurname,HRSSN)
VALUES('Elif','Başel',202);
INSERT INTO [dbo].[HumanResourcesEmployees](HRname,HRsurname,HRSSN)
VALUES('Ahmet','Keklik',203);
INSERT INTO [dbo].[HumanResourcesEmployees](HRname,HRsurname,HRSSN)
VALUES('Fatma','Demir',204);

INSERT INTO[dbo].[ITEmployee](ITname,ITsurname,ItSSN)
VALUES('Merve','Demir',2000);
INSERT INTO[dbo].[ITEmployee](ITname,ITsurname,ItSSN)
VALUES('Ayşe','Ayaz',2001);
INSERT INTO[dbo].[ITEmployee](ITname,ITsurname,ItSSN)
VALUES('Ebrar','Başel',2003);
INSERT INTO[dbo].[ITEmployee](ITname,ITsurname,ItSSN)
VALUES('Ali','Kısa',2004);
INSERT INTO[dbo].[ITEmployee](ITname,ITsurname,ItSSN)
VALUES('Ahmet','Keklik',2005);
CREATE SCHEMA Customerss
GO
CREATE TABLE CustomersIsBankk(
customerid INT NOT NULL PRIMARY KEY IDENTITY(1,1),
customername NVARCHAR(50) NOT NULL,
customersurname NVARCHAR(50) NOT NULL,
customerphone int not null)
INSERT INTO [dbo].[CustomersIsBank](customername,customersurname,customerphone)
VALUES('Didem','Yılmaz',123456);
INSERT INTO [dbo].[CustomersIsBank](customername,customersurname,customerphone)
VALUES('Burak','Gül',123455);
INSERT INTO [dbo].[CustomersIsBank](customername,customersurname,customerphone)
VALUES('Gül','Kuru',113456);
ALTER TABLE [dbo].[HumanResourcesEmployees]
ADD semt nvarchar(50);
SELECT * FROM [dbo].[HumanResourcesEmployees]
WHERE HRname LIKE 'A%';
SELECT * FROM ITEmployee
SELECT * FROM CustomersIsBank
WHERE customerid=124;
SELECT * FROM [dbo].[CustomersIsBank]
WHERE NOT customerid
SELECT* FROM [dbo].[ITEmployee]
ORDER BY ITsurname DESC;
CREATE VIEW ITJOINView
AS SELECT I.ITid,I.ITname
FROM [dbo].[ITEmployeess] as I
JOIN [dbo].[HumanResourcesEmployeess] as H
on I.ITSSN = H.HRSSN
GO
CREATE VIEW Isbank
AS SELECT B.customername, B.customerphone
FROM [dbo].[CustomersIsBankk] AS B
RIGHT JOIN [dbo].[HumanResourcesEmployees] AS H
ON B.customerid=H.HRid
GO
ALTER TABLE [dbo].[HumanResourcesEmployeess]
ADD salary float;
CREATE PROCEDURE HumanResourcesEmployeesDelete (@HRname nvarchar(50),@HRsurname nvarchar(50))
AS
DELETE [dbo].[HumanResourcesEmployees] where HRname=@HRname and HRsurname=@HRsurname
GO
EXEC  HumanResourcesEmployeesDelete @HRname='Elif',@HRsurname='Başel'
GO
INSERT INTO[dbo].[Cleaners](Cleanername,Cleanersurname,CleanerSSN)
VALUES('Ayşe','Deniz',158795);
INSERT INTO[dbo].[Cleaners](Cleanername,Cleanersurname,CleanerSSN)
VALUES('Zeynep','Deniz',158796);
INSERT INTO[dbo].[Cleaners](Cleanername,Cleanersurname,CleanerSSN)
VALUES('Ayşe','Deniz',158797);
INSERT INTO[dbo].[Cleaners](Cleanername,Cleanersurname,CleanerSSN)
VALUES('Zeynep','Kemer',158798);
INSERT INTO [dbo].[SecurityEmployees](Securityname,Securitysurname, SecuritySSN,SecuritySalary)
VALUES('Ahmet','Kısa',211,1500);
INSERT INTO [dbo].[SecurityEmployees](Securityname,Securitysurname, SecuritySSN,SecuritySalary)
VALUES('Demir','Soylu',212,1900);
INSERT INTO [dbo].[SecurityEmployees](Securityname,Securitysurname, SecuritySSN,SecuritySalary)
VALUES('Demir','Soylu',213,2100);
INSERT INTO [dbo].[SecurityEmployees](Securityname,Securitysurname, SecuritySSN,SecuritySalary)
VALUES('Demir','tok',213,2500);
INSERT INTO [dbo].[SecurityEmployees](Securityname,Securitysurname, SecuritySSN,SecuritySalary)
VALUES('Ahmet','Tokgöz',213,2500);
SELECT Cleanername, COUNT(Cleanername) 
FROM Cleaners 
GROUP BY Cleanername 
HAVING COUNT (Cleanername) > 1

SELECT securityname,securitysurname
FROM SecurityEmployees
CASE
WHEN salary > 1000 THEN 'Salary is low'
WHEN salary > 2000 THEN 'Salary is normal'
ELSE salary > 3000 THEN 'Salary is high'
END-- ÇALIŞMADI
CREATE PROCEDURE ITaddd(@ITname nvarchar(50),@ITsurname nvarchar(50),@ITSSN int)
AS
INSERT INTO[dbo].[ITEmployeess]
VALUES(@ITname,@ITsurname,@ITSSN)
EXEC ITaddd 'Ece','Derya',1235