Untitled
unknown
plain_text
4 years ago
21 kB
7
Indexable
use E_Commerce
select Colors.ColorName,Sizes.SizeName from Colors cross join Sizes
select convert(varchar,ct.ContractDate,103),ct.Subject,ct.Details, u.Fullname
as 'imzalayan',u2.Fullname as 'Onaylayan' from Contracts ct
join Users u on ct.imzalayan= u.ID
join Users u2 on u2.ID= ct.onaylayan
use Northwind
select e.LastName, e.FirstName,e.EmployeeID, e.ReportsTo,er.FirstName
from Employees e left join Employees er on e.ReportsTo=er.EmployeeID
select LastName, FirstName, convert(varchar,BirthDate,103) as BirthDate,
case when datepart(year,Birthdate)<1950 then 'Silent Generation'
when datepart(year,Birthdate)>=1950 and datepart(year,Birthdate)<1970 then 'Baby Boomers'
when datepart(year,Birthdate)>=1970 and datepart(year,Birthdate)<1980 then 'X Gen'
when datepart(year,Birthdate)>=1980 and datepart(year,Birthdate)<1995 then 'Y Gen'
when datepart(year,Birthdate)>=1995 then 'Z Gen'
end as Generation
from Employees order by Generation
--insert komutu tabloya satır ekler
/*insert into tabloadı (alan1,alan2,alan3,...,alanx)
values(değer1,değer2,değer3,...,değerx)
insert ifadelerinde into kelimesi opsiyoneldir
alan adlarının tamamı yazılmak zorunda değildir ama zorunlu alanlar
yazılmalıdır
Autoidentity özelliği olan alanlara değer yazılmaz
alan sayısı ile gönderilen değerlerin sayısı eşit olmalı ve değerler aynı sıra
ie eşleşmelidir
*/
INSERT INTO Categories (CategoryName)
VALUES ('Akşam')
/*
UPDATE komutu, satır veya satırlardaki değerleri güncelleme işlemi yapar
UPDATE tabloadı SET alan1=deger1, alan2=deger2.....
WHERE koşul
UPDATE ifadelerinde where kısmı opsiyoneldir, olmasa da çalışabilir ama dikkat edilmelidir
alan adı seçiminde özgürüz, istediğimiz kadar alanı değer belirtmek suretiyle değiştirebiliriz.
autoidentity alanların değerleri değiştirilemez
*/
UPDATE Categories SET CategoryName='Akşamcılar'
WHERE CategoryID=18
--DELETE
DELETE Categories
WHERE CategoryID=18
DBCC CHECKIDENT('Categories',RESEED,8)-- İÇİNDE VERİ OLAN TABLODA EN YÜKSEK ID VERİLİR,
--1FAZLASINDAN DEVAM EDER
DBCC CHECKIDENT('Categories',RESEED,0)-- İÇİNDE VERİ OLAN TABLODA 0 VERİLİR,
--1'den DEVAM EDER
SELECT IDENT_CURRENT('Categories')
---Alter komutu nesnelerde(tabloi view, stored proc., function, role, user, login, database) değişiklik yapar
USE BigKahunaBurger
ALTER TABLE Customers
ADD Notes varchar(100)
ALTER TABLE Customers
DROP COLUMN Notes
ALTER TABLE Customers
ALTER COLUMN Notes varchar(1200) --veri türünü değiştirir
DROP TABLE Customers --bağlantılı(referanslı) tablolar silinemez
/*
GRANT
REVOKE
DENY*/
GRANT INSERT ON Customers TO bekir;
REVOKE SELECT ON Customers TO bekir;
DENY SELECT ON Customers TO bekir;
grant select on Orders to AA
grant select on Customers to StandartCalisan
grant select on Foods to StandartCalisan
/*
sql'e yeni bir kişi gelecek adı sezen
bu kişi Northwind veritabanında sadece categories ve employees tablolarında select işlemi yapabilecek
ek olarak bi grup(şarkıcılar) kullanıcıya tüm tablolar için insert ve update yetkisi verilecek
bi kullanıcı daha tanımlanacak adı nilüfer, kendi yetkilerini direkt şarkıcıar olarak alacak ama suppliers tablosunda işlem yapamayacak
bi de kayahan olacak bu kişi tüm yetkilerini şarkıcılardan alacak ve ek olarak tüm tablolara select yapabilecek
*/
grant select on Categories to Sezen
grant select on Employees to Sezen
deny delete on Suppliers to Nilüfer
deny update on Suppliers to Nilüfer
deny select on Suppliers to Nilüfer
revoke select on CustomerCustomerDemo to sarkicilar
revoke select on CustomerDemographics to sarkicilar
revoke select on Customers to sarkicilar
revoke select on Employees to sarkicilar
revoke select on EmployeeTerritories to sarkicilar
revoke select on [Order Details] to sarkicilar
revoke select on Orders to sarkicilar
revoke select on Products to sarkicilar
revoke select on Region to sarkicilar
revoke select on Shippers to sarkicilar
revoke select on Suppliers to sarkicilar
revoke select on Territories to sarkicilar
grant select on CustomerCustomerDemo to Kayahan
grant select on CustomerDemographics to Kayahan
grant select on Customers to Kayahan
grant select on Employees to Kayahan
grant select on EmployeeTerritories to Kayahan
grant select on [Order Details] to Kayahan
grant select on Orders to Kayahan
grant select on Products to Kayahan
grant select on Region to Kayahan
grant select on Shippers to Kayahan
grant select on Suppliers to Kayahan
grant select on Territories to Kayahan
-- Login->user
-->Role
deny select on Categories to Alex
grant update on Employees to Alex
--tüm kullanıcılar başlangıçta 0 yetkiye sahiptir, hiçbir işlem yapamazlar
-- kullanıcılara direkt kendilerine veya roller üzerinden roller verilebilir
--örneğin db_datawriter rolüne atanan bir kullanıcı tüm tablolarda insert, update,delete
--yapabilir
--özel olarak bir kullanıcıda rodle verilen yetkiyi ezen yetki verilebilir, örneğin ek bir
--select hakkı tanınabilir
--deny işlemi diğer bütün işlemleri ezer ve yetkiyi tamamen kaldırır, yok eder
create view vw_ProductWCategories
as
select ProductName, isnull(CategoryName,'-') as CategoryName
from Products as p
left join Categories c on c.CategoryID=p.CategoryID
alter view vw_ProductWCategories
as
select ProductName, isnull(CategoryName,'-') as CategoryName, p.UnitPrice
from Products as p
left join Categories c on c.CategoryID=p.CategoryID
drop view [vw_ProductWCategories]
select * from vw_ProductWCategories
grant select on vw_ProductWCategories to student
--scalar fonksiyon bir şey hesaplayıp değer döndürür
/*create*/alter function dbo.CalculatedRisk(@ProductID int)
RETURNS varchar(50)
as
begin
declare @ToplamSatis AS int, @ToplamStok AS int, @myReturn AS varchar(50)
select @ToplamStok=UnitsInStock from Products where ProductID=@ProductID
select @ToplamSatis=sum(Quantity) from [Order Details] where ProductID= @ProductID
if @ToplamSatis/20 > @ToplamStok
begin
SET @myReturn = 'Risksiz'
end
else
begin
set @myReturn = 'Riskli'
end
return @myReturn
end
select ProductID,ProductName, dbo.CalculatedRisk(ProductID) as 'Risk Durumu' from Products order by ProductID
SELECT SUM(Quantity)
FROM [Order Details]
WHERE ProductID=5
SELECT p.ProductID, p.ProductName, SUM(od.Quantity)
FROM Products p LEFT JOIN [Order Details] od ON p.ProductID=od.ProductID
GROUP BY p.ProductName,p.ProductID
CREATE FUNCTION dbo.SumSales(@ProductID int)
RETURNS int
AS
BEGIN
DECLARE @toplam AS int
SELECT @toplam=SUM(Quantity)
FROM [Order Details]
WHERE ProductID=@ProductID
RETURN @toplam
END
select ProductName, dbo.SumSales(ProductID) from Products
--rezervasyon
/* oda ve masalar -> kaynak tipleri (kat bilgisi/ oda mı masa mı/no )
kullanıcılar (çalışanlar/ üyeler)
rezervasyon yap/ iptal et*/
select convert(varchar, r.StartDateofStay,103) as 'StartDateofDay',
convert(varchar, r.StartDateofStay,108) as 'StartTimeofStay',s.Status,m.MemberFullName,a.AreaNo
from Reservations r join Areas a on r.AreaId=a.ID
join Members m on m.ID=r.MemberId
join Statuses s on s.ID=r.StatusId
set dateformat dmy
select a.AreaNo, s.Status , f.KatNo, a.AreaNo
from Areas a join Reservations r on r.AreaId=a.ID
join Members m on m.ID=r.MemberId
join Statuses s on s.ID=r.StatusId
join Floors f on f.ID=a.FloorId
where a.ID not in (select ID
from Reservations
where convert(varchar, StartDateofStay,103) = '11/12/2021' and StatusId=1)
select count(a.AreaNo) as sayı, f.KatNo, at.AreaTypeName
from Areas a join Floors f on a.FloorId= f.ID
join AreaTypes at on at.ID=a.AreaTypeId
where a.ID
not in (
select ID
from Reservations
where convert(varchar, StartDateofStay,103) = '11/12/2021')
group by AreaTypeName, f.KatNo
select top 1 count (StatusId) as adet, m.MemberFullName from Reservations r
join Members m on r.MemberId= m.ID
group by m.MemberFullName
order by count(StatusId)Desc
-------------------------!!!!!!!!!!!!!!!!!!!!!!!!!!--------------------------
select MemberFullName, 0 as Adet
from Members
where ID not in (select MemberId from Reservations)
union
select m.MemberFullName, count(*) as Adet
from Reservations r
join Members m on m.ID= r.MemberId
group by m.MemberFullName
Having Count(*)<2
-----------------------!!!!!!!!!!!!!!!!!!!!!!!!!!!!--------------------------
/*
use Northwind
select t.CompanyName, t.CompanyType
from (select CompanyName, 'Shipper' as CompanyType from Shippers
union
select CompanyName, 'Supplier' as CompanyType from Suppliers) as t
order by t.CompanyType, t.CompanyName
*/
select MemberId,StartDateofStay,EndDateofStay,
DATEDIFF(d,StartDateofStay,EndDateofStay) from Reservations
where DATEDIFF(hour,StartDateofStay,EndDateofStay)>20
select * from Reservations
select MemberId,sum(DATEDIFF(hour,StartDateofStay,EndDateofStay)) as 'toplam saat'
from Reservations
group by MemberId
having sum(DATEDIFF(hour,StartDateofStay,EndDateofStay))>20
---!!!!!!!!!!!!!!!!!!!!!--
alter procedure dbo.YilAdetHesapla
as
delete YilAdet
insert into YilAdet (Yil,Adet)
select datepart(year,o.OrderDate) as 'Yıl', sum(od.Quantity) as 'toplam satış'
from [Order Details] od join Orders o on od.OrderID=o.OrderID
group by datepart(year,o.OrderDate)
order by datepart(year,o.OrderDate)
-----------!!!!!!!!!!!!!!!-------
exec dbo.YilAdetHesapla --procedure'lar exec komutu ile çalışır
grant control on dbo.CalculatedRisk to bekir1
grant control on dbo.YilAdetHesapla to bekir1
create trigger CategorySilinemez on Categories
instead of delete
--categories tablosunda delete işlemi denenirse bu trigger devreye girer ve hata verip sonra da
--transaction'ı geri alır, yani silmeye izin vermez.
as
begin
raiserror ('Categories Tablosu üzerinde kayıt silinemez',1,1)
rollback tran
end
select * into Products_cpy from Products
----
alter trigger [dbo].[PriceHistory] on [dbo].Products
after update
as
begin
set nocount on
declare @ProductID as int, @OldPrice as money, @NewPrice as money
select @ProductID = ProductID
from deleted
select @OldPrice=UnitPrice
from deleted
select @NewPrice=UnitPrice
from inserted
if @OldPrice!= @NewPrice
begin
insert into PriceHistoryy (ProductID, OldPrice, NewPrice, IslemDate)
values(@ProductID,@OldPrice,@NewPrice,getdate())
end
end
-----
--set nocount on
update Products set UnitPrice=12121 where ProductID=1
alter trigger [dbo].[CustomerBackup] on [dbo].Customers
after delete
as
begin
declare @CustomerID as varchar, @CompanyName as varchar(50), @ContactName as varchar
select @CustomerID = CustomerID
from deleted
select @CompanyName=CompanyName
from deleted
select @ContactName=ContactName
from deleted
begin
insert into DeletedOnes (CustomerID, CompanyName, ContactName)
values(@CustomerID,@CompanyName,@ContactName)
end
/*
insert into Customers_DeletedOnes
select * from deleted
*/
end
-------------------!!!!!!!!!!!!----------
alter trigger [dbo].[ProductEntrancee] on [dbo].Products
after update, insert
as
begin
declare @ProductID as int, @ProductName as varchar(50), @Amount as int
select @ProductID = ProductID
from inserted
select @ProductName = ProductName
from inserted
select @Amount = UnitsInStock
from inserted
insert into ProductEntrance (ProductID, ProductName, Amount,EntranceDate)
values(@ProductID,@ProductName,@Amount,getdate())
end
----------!!!!!!!!!!!!!!!!----------
create trigger [dbo].[xxx] on [dbo].[ProductEntrance]
instead of update
as
begin
raiserror('ProductEntrance tablosu üzerinde kayıt değiştirilemez',1,1)
rollback tran
end
after delete
as
begin
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
end
--işlem adetlerine göre en fazla kaydı olan 3 çalışanın adını ve işlem adedini yazın ve
--bunun bir view olarak kaydedip test edin sonra da view'a yeni bir kullanıcı için select yetkisi verin
alter view EnCokSatan
as
select top 3 with ties e.EmployeeID,e.FirstName+' '+e.LastName as 'Employee', count(o.OrderID) as 'Sayı'
from Orders o join Employees e on o.EmployeeID=e.EmployeeID
group by e.EmployeeID, e.FirstName+' '+e.LastName
having count(o.OrderID)>125
order by Sayı desc
--top olmazsa view'da order by yapılamaz
grant select on EnCokSatan to Bekir1
select * from EnCokSatan
alter view Listele
as
select top 1000 p.ProductName, s.CompanyName,c.CategoryName, p.UnitPrice from Products p
left join Suppliers s on p.SupplierID=s.SupplierID
left join Categories c on p.CategoryID=c.CategoryID
where UnitPrice between 20 and 25
order by p.ProductName desc
select * from Listele
grant select on Listele to Bekir1
-------!!!!!!!!!!!!!!!!!!1
alter function fn_Listele (
@min as money,
@max as money )
returns @Listeli table (
id int identity(1,1),
[ProductName] varchar(50),
[CompanyName] varchar(50),
[CategoryName] varchar(50),
[UnitPrice] money
)
as
begin
begin
insert into @Listeli
select p.ProductName, s.CompanyName,c.CategoryName, p.UnitPrice from Products p
left join Suppliers s on p.SupplierID=s.SupplierID
left join Categories c on p.CategoryID=c.CategoryID
where UnitPrice between @min and @max
order by p.ProductName desc
end
return;
end
------------!!!!!!!!!!!!!!!!!!!!!
select * from [dbo].[fn_Listele](12, 110)
create view EmployeeManagerList
as
select e.FirstName+' '+e.LastName as Employee,
e.HomePhone, e2.FirstName+' '+e2.LastName as 'Reports to'
from Employees e left join Employees e2 on e.ReportsTo=e2.EmployeeID
select * from EmployeeManagerList
select convert(varchar,OrderDate,103) as orderDate, convert(varchar,ShippedDate,103) as ShippedDate,
CustomerID, Freight
from Orders
where datepart(YEAR,OrderDate)=1997 and datepart(month,OrderDate) = 5
and datepart(DAY,OrderDate)=19
---????????????
select * from Employees
where Country='USA' and not in (select * from Employees where City='Seattle' and Country='USA')
-----??????????
select City, CompanyName, ContactName from Customers
where City like('a%') or City like('b%')
order by ContactName desc
----????
select count(*) as sayi, c.ContactName from Orders o left join Customers c
where datepart(year, o.OrderDate)>1996 group by c.ContactName
-----????
set dateformat dmy
select c.CompanyName, COUNT(*) as NumOrders
from Orders as o
join Customers c on c.CustomerID=o.CustomerID
where OrderDate>'31.12.1996'
group by c.CompanyName
having COUNT(*)>15
order by count(*) desc
select od.OrderID,(od.UnitPrice*od.Quantity)*(1-od.Discount) as TotalPrice, c.CompanyName from [Order Details] od join Orders o on od.OrderID=o.OrderID
join Customers c on o.CustomerID=c.CustomerID
where (od.UnitPrice*od.Quantity)*(1-od.Discount)>10000
order by (od.UnitPrice*od.Quantity)*(1-od.Discount) desc
select o.OrderID, e.LastName from orders o join Employees e on o.EmployeeID=e.EmployeeID
where o.ShippedDate>o.RequiredDate
-----
alter trigger [dbo].[YedekleShipper] on [dbo].Shippers
after delete
as
begin
declare @ShipperID as int, @CompanyName as nvarchar(40), @Phone as nvarchar(24)
select @ShipperID = ShipperID
from deleted
select @CompanyName = CompanyName
from deleted
select @Phone = Phone
from deleted
insert into DeletedShipper (ShipperID, CompanyName, Phone)
values(@ShipperID,@CompanyName,@Phone)
end
------
select database_id, name
from sys.databases--sunucudaki veritabanları
select * from sys.tables--norhwinddeki tablo adları
select * from sys.views--northwinddeki view adları
select t.name as triggername,o.name as tablename from sys.triggers as t join sys.objects as o on o.object_id=t.parent_id
order by tablename, triggername -- northwinddeki trigger ve tablo adları
select * from sys.objects
create trigger [dbo].[UpdateProducts] on [dbo].[Order Details]
instead of insert
as
begin
declare @Quantity as smallint, @UnitsInStock as smallint
select @Quantity = Quantity from inserted
select @UnitsInStock = UnitsInStock
from Products where ProductID = (select ProductID from inserted)
if @Quantity> @UnitsInStock
begin
raiserror('Stok Yeterli Değil',1,1)
rollback tran
end
else
begin
insert into [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)
select OrderID,ProductID,UnitPrice,Quantity,Discount
from inserted
end
end
------------------!!!!!!!!!!!!!!!!!!!-----------------
CREATE trigger [dbo].[UpdatedProducts] on [dbo].[Order Details]
after insert
as
begin
declare @Quantity as smallint, @UnitsInStock as smallint
select @Quantity = Quantity from inserted
select @UnitsInStock = UnitsInStock
from Products where ProductID = (select ProductID from inserted)
update Products set UnitsInStock= UnitsInStock-@Quantity where ProductID=(select ProductID from inserted)
end
--------------!!!!!!!!!!!!!!!!!!!------------
delete from [Order Details] where OrderID=11000 and ProductID=59
----------------!!!!!!!!!!!!!!!!!!!!1--------------------
CREATE trigger [dbo].[Updated2Products] on [dbo].[Order Details]
after delete
as
begin
declare @Quantity as smallint, @UnitsInStock as smallint
select @Quantity = Quantity from deleted
select @UnitsInStock = UnitsInStock
from Products where ProductID = (select ProductID from deleted)
update Products set UnitsInStock= UnitsInStock+@Quantity where ProductID=(select ProductID from deleted)
end
---------------!!!!!!!!!!!!!!!!----------------------
select OrderID,count(ProductID) from [Order Details]
group by OrderID
select * from [Order Details] where OrderID=11077
select FirstName +' '+ LastName+' can be reached at x'+Extension+'.' as ContactInfo
from Employees
select ContactName,ContactTitle from Customers where ContactTitle not like'%Sales%'
select FirstName,LastName,BirthDate from Employees where Datepart(year,BirthDate)
between 1950 and 1959
select p.ProductID,p.ProductName,sum(od.Quantity) from Products p
join [Order Details] od on p.ProductID=od.ProductID group by p.ProductID,p.ProductName
CREATE FUNCTION dbo.SumSaless(@ProductID int)
RETURNS int
AS
BEGIN
DECLARE @toplam AS int
SELECT @toplam = SUM(Quantity)
FROM [Order Details]
WHERE ProductID=@ProductID
RETURN @toplam
END
select ProductID, ProductName, dbo.SumSaless(ProductID) as TotalSales from Products
order by dbo.SumSaless(ProductID) desc
----------------Procedure
alter procedure UrunTopHesapla
as
begin
if exists(select * from sys.tables where name ='UrunToplamlari')
begin
drop table UrunToplamlari
end
select ProductID,sum(Quantity) as toplam
into UrunToplamlari
from [Order Details]
group by ProductID
order by sum(Quantity)
end
exec UrunTopHesapla
-------------Procedure
ALTER FUNCTION dbo.MaasHesapla(@EmployeeID int)
RETURNS int
AS
BEGIN
DECLARE @Net AS money
SELECT @Net = (0.85)*Salary
FROM [Employees]
WHERE EmployeeID=@EmployeeID and EmployeeTypeID=1
SELECT @Net = (0.90)*Salary
FROM [Employees]
WHERE EmployeeID=@EmployeeID and EmployeeTypeID=2
SELECT @Net = (1)*Salary
FROM [Employees]
WHERE EmployeeID=@EmployeeID and EmployeeTypeID=3
RETURN @Net
END
select FirstName, Salary as Brüt, dbo.MaasHesapla(EmployeeID) as 'Net Maaş'
from Employees
alter trigger [dbo].[EmployeeTypeLock] on [dbo].[EmployeeTypes]
instead of delete
as
begin
if((select EmployeeTypeID from deleted)=1 or
(select EmployeeTypeID from deleted)=2 or
(select EmployeeTypeID from deleted)=3)
begin
raiserror('Silinemez',1,1)
rollback tran
end
else
begin
delete from EmployeeTypes where EmployeeTypeID= (select EmployeeTypeID from deleted)
end
end
------------------------------
alter trigger [dbo].[EmployeeTypeLock2] on [dbo].[EmployeeTypes]
instead of update
as
begin
if((select EmployeeTypeID from inserted)=1 or
(select EmployeeTypeID from inserted)=2 or
(select EmployeeTypeID from inserted)=3)
begin
raiserror('Değiştirilemez',1,1)
-- update EmployeeTypes
-- set EmployeeType=(select EmployeeType from deleted)
-- where EmployeeTypeID= (select EmployeeTypeID from inserted)
rollback tran
end
else
begin
update EmployeeTypes
set EmployeeType=(select EmployeeType from inserted)
where EmployeeTypeID= (select EmployeeTypeID from inserted)
end
end
Editor is loading...