Untitled
unknown
plain_text
3 years ago
18 kB
3
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 ---------------!!!!!!!!!!!!!!!!----------------------
Editor is loading...