use Northwind
/*
T-SQL (Transactional – Structure Query Language) is made up of a combination of three language structures.
1. DDL : Data Definition Language
— CREATE <ObjectType><ObjectName> (Creates)
— ALTER <ObjectType><ObjectName> (Modifies)
— DROP <ObjectType><ObjectName> (Deletes)
2. DML : Data Manipulation Language
— INSERT (Adds a record)
— UPDATE (Updates an existing record)
— DELETE (Deletes an existing record)
— SELECT (Returns the records that match the given criteria.)
*/
— INSERT
— INSERT into <TableName>(<ColumnName1>,<ColumnName2>,<ColumnNameN>)
values(<Value1>,<value2>,<valueN>)
INSERT into Products (ProductName,UnitPrice,UnitsInStock,Discontinued)
values (‘Dried Beans’,5,1000,0)
— UPDATE <tableName> SET <ColumnName1>=<Value1>,
— <ColumnName2>=<Value2>,
— <ColumnName3>=<Value3>
–WHERE <criteria>
UPDATE Products SET ProductName=’Green Beans’
WHERE ProductID=78
— DELETE FROM <TableName> WHERE <Criteria>
DELETE FROM Products WHERE ProductID=78
INSERT into Products (ProductName,UnitPrice,UnitsInStock,Discontinued)
values (‘Dried Beans’,5,1000,0)
— SELECT <ColumnName1>,<ColumnName2> FROM <TableName> WHERE <criteria IF ANY>
— SELECT * FROM <TableName> WHERE <criteria IF ANY> (Returns all columns..)
SELECT * FROM Employees
SELECT FirstName, LastName FROM Employees
— SELECT <TableName>.<ColumnName1>, FROM <TableName>.<ColumnName2> WHERE <criteria IF ANY>
— The long form..
SELECT Products.ProductName, Products.UnitPrice FROM Products
WHERE UnitPrice>100
— Products priced over $100..
— Name, price and stock of products whose UnitsInStock (stock) value is below 10
SELECT Products.ProductName, Products.UnitPrice, Products.UnitsInStock FROM Products
WHERE UnitsInStock<10
/*
1. Decide which table(s) you will work with.
2. Decide which column(s) of these table(s) you want to see
3. Write your criteria, if any.
*/
— Company name, contact name, address, city and country of customers in Brazil
SELECT CompanyName, ContactName, Address, City, Country FROM Customers
where Country=’Brazil’
— Customers outside Brazil
SELECT CompanyName,ContactName,Address,City,Country FROM Customers
where Country != ‘Brazil’
— My customers in London or Paris:
SELECT CustomerID,CompanyName,Address, City FROM Customers
WHERE City= ‘London’ OR City= ‘Paris’
— Customers who BOTH live in Mexico D.F. AND have the ContactTitle ‘owner’:
SELECT CustomerID,CompanyName,ContactTitle,City FROM Customers
WHERE City=’México D.F.’ AND ContactTitle=’owner’
— Names and prices of my products starting with C
SELECT ProductName,UnitPrice FROM Products
WHERE ProductName LIKE ‘C%’
— List of discontinued products
–(Hint: Discontinued will be 1)
SELECT ProductName,UnitPrice FROM Products
WHERE Discontinued=1
— If we want to see the first name, last name and birth date of employees (Employees) whose first name (FirstName) starts with ‘A’:
SELECT FirstName,LastName,BirthDate FROM Employees
WHERE FirstName LIKE ‘A%’
— Company names of my customers whose name contains ‘RESTAURANT’:
SELECT CompanyName FROM Customers
WHERE CompanyName LIKE ‘%RESTAURANT%’
— Names and prices of all products between $50 and $100
SELECT ProductName,UnitPrice FROM Products
WHERE UnitPrice BETWEEN 50 AND 100
— OrderID and OrderDate of the orders (Orders) between 1 July 1996 and 31 December 1996
SELECT OrderID,OrderDate FROM Orders
WHERE OrderDate BETWEEN ’01/07/1996′ AND ’31/12/1996′
— Customers whose country (Country) is EITHER Spain, France or Germany
SELECT CompanyName,Country FROM Customers
WHERE Country=’Spain’ OR Country=’France’ OR Country=’Germany’
— If we have used OR many times, we can also write it like this.
SELECT CompanyName,Country FROM Customers
WHERE Country IN (‘Spain’,’France’,’Germany’)
–The negative (NOT IN)
SELECT CompanyName,Country FROM Customers
WHERE Country NOT IN (‘Spain’,’France’,’Germany’)
–Customers whose fax number I don’t know
SELECT CustomerID, CompanyName, Fax FROM Customers
where Fax is null
— Sorting the ResultSet:
I sort my customers by country:
SELECT CompanyName,Country FROM Customers
ORDER BY Country ASC
— asc: ascending (smallest to largest)
— desc: descending (largest to smallest)
–Sorting my products from the most expensive to the cheapest; we want the product name and price as the result:
SELECT ProductName,UnitPrice FROM Products
ORDER BY UnitPrice DESC
–Sort my products from the most expensive to the cheapest, but show their stock from smallest to largest; we want the product name and price as the result:
SELECT ProductName,UnitPrice,UnitsInStock FROM Products
–We could also write WHERE ProductName LIKE ‘C%’..
ORDER BY UnitPrice DESC, UnitsInStock ASC
— AGGREGATE FUNCTIONS (functions that return a value, e.g. how many columns a table has..)
SELECT COUNT(*) FROM Customers
— So I have 91 customers..
SELECT CategoryID,CategoryName FROM Categories
SELECT ProductName,CategoryID FROM Products
— How many products are there in category 1..?
SELECT COUNT(*) FROM Products
WHERE CategoryID=1
–COUNT(<ColumnName>) : Returns how many times the given column occurs in the ResultSet.
— How many different countries do I export to..?
SELECT COUNT(DISTINCT Country) FROM Customers
— DISTINCT – Don’t return duplicates.. If Germany appears 3 times, show it once..
— Which countries are they..?
SELECT DISTINCT Country FROM Customers
— DISTINCT: Takes ONLY ONE of the repeated values in the ResultSet (note: the ResultSet lives in RAM), filtering the data in a way.
–Filtering data: TOP x
SELECT TOP 3 CompanyName FROM Customers
— If I ask for the 5 most expensive products
SELECT TOP 5 ProductName, UnitPrice FROM Products
ORDER BY UnitPrice Desc
–Number of orders of my customer with the CustomerID ALFKI..?
–(Orders)
SELECT COUNT(*) FROM Orders
WHERE CustomerID=’ALFKI’
— To give the column a name:
SELECT COUNT(*) AS ToplamSiparis FROM Orders
WHERE CustomerID=’ALFKI’
SUM(<ColumnName>): Returns the total of the given column in the ResultSet. Can only be used on numeric columns.
–Total cost of my products:
SELECT SUM(UnitPrice) ToplamFiyat FROM Products
–How much revenue has my company made so far..?
–Source: Order Details table
–1-Discount (we deduct the discounts..
SELECT SUM(UnitPrice*Quantity*(1-Discount)) ToplamCiro FROM [Order Details]
–AVG(<ColumnName>): Takes the average of the given column in the ResultSet:
–My average product price:
SELECT AVG(UnitPrice) Ortalama FROM Products
SELECT FirstName + ‘ ‘ + LastName as AdSoyad FROM Employees
— MAX and MIN
SELECT MAX(UnitPrice) FROM Products
SELECT MIN(UnitPrice) FROM Products
— Name of the most expensive product
SELECT ProductName FROM Products
WHERE UnitPrice= (SELECT MAX(UnitPrice) FROM Products)
–The least profitable order (source: Order Details)
SELECT MIN(UnitPrice * Quantity) FROM [Order Details]
Raising to a power:
SELECT POWER (3,2)
SELECT SQRT(81)
SELECT LEN(‘TEST’)
— The customer with the longest name among my customers (number of letters)
SELECT MAX(LEN(CompanyName)) FROM Customers
SELECT GETDATE()
SELECT YEAR (GETDATE())
SELECT DAY (GETDATE())
SELECT MONTH (GETDATE())
–First name, last name and age of my employees
SELECT FirstName,LastName, YEAR(GETDATE())-YEAR(BirthDate) as Yas FROM Employees
SELECT photo FROM Employees
— GROUP BY (Grouping)
— How many units of each product have been ordered in total..?
SELECT ProductID, SUM(Quantity ) FROM [Order Details]
GROUP BY ProductID
— If you use an aggregate function and a normal column in a query, the query must have a GROUP BY. The columns to group by are all the columns outside the aggregate function.
SELECT ProductID, SUM(Quantity ) as ToplamAdet FROM [Order Details]
GROUP BY ProductID
ORDER BY ToplamAdet DESC
— How much did I earn in total on each order..?
— Source table: Order Details
— Required columns: OrderID and earnings (quantity*price)
— There is a SUM, so I have to use GROUP BY..
SELECT OrderID, SUM(Quantity * UnitPrice) FROM [Order Details]
GROUP BY OrderID
— How many products are there in each category..?
SELECT CategoryID, COUNT(ProductID) FROM Products
GROUP BY CategoryID
–Since MAX and MIN return a single value, GROUP BY is not used..!
SELECT CategoryID, COUNT(ProductID) as Adet FROM Products
GROUP BY CategoryID
ORDER BY Adet
— HAVING:
— Products with more than 1000 units sold:
SELECT ProductID,SUM(Quantity) As SatisAdeti FROM [Order Details]
GROUP BY ProductID
HAVING SUM(Quantity)>1000
— If the criteria you apply to a ResultSet depend on a column in the table, you use WHERE. But if you apply criteria to a value calculated in RAM, HAVING is used..
— SUB-QUERY (subqueries, nested queries)
— Name and price of my products priced below the average:
— 1. First, a query to find the average:
SELECT AVG(UnitPrice) FROM Products
— 2. The query that uses this result as its criteria:
SELECT ProductName,UnitPrice FROM Products
WHERE UnitPrice < (SELECT AVG(UnitPrice) FROM Products)
SELECT COUNT(*) FROM Customers
SELECT COUNT(DISTINCT CustomerID) FROM Orders
— Which of my customers have never placed an order..? (89 of the 91 customers had ordered..)
SELECT CompanyName,Address,City,Country FROM Customers
WHERE CustomerID NOT IN (SELECT DISTINCT CustomerID FROM Orders)
— The following example is meant to dispel the misconception that a sub-query can only be used after the WHERE criteria:
SELECT c.CategoryName + ‘ (‘ + CAST((SELECT COUNT(ProductID) FROM Products
WHERE CategoryID=c.CategoryID) as NVARCHAR(5) ) +’)’ FROM Categories as c
— INNER JOIN
— The query type that builds a ResultSet from related tables using only the records that have a product (the MATCHING ones, where the PRIMARY KEY has a FOREIGN KEY counterpart):
SELECT Products.ProductName, Categories.CategoryName FROM Products
INNER JOIN Categories
ON Products.CategoryID=Categories.CategoryID
— Which product is in which category..
INSERT into Categories(CategoryName,Description)
values (‘Desserts’, ‘Milk Desserts’)
SELECT * FROM Categories
— Which supplier (Suppliers.CompanyName) supplies which product (Products.ProductName)..?
SELECT sp.CompanyName,p.ProductName FROM Suppliers as sp
INNER JOIN Products as p
ON sp.SupplierID=p.SupplierID
— p.ProductName=Products.ProductName (we shortened it..)
— Which order (Orders.OrderID) was shipped with which shipping company (Shippers.CompanyName) and when (Orders.OrderDate)..?
SELECT Orders.OrderID,Shippers.CompanyName,Orders.OrderDate FROM Shippers
INNER JOIN Orders
ON Shippers.ShipperID=Orders.ShipVia
— 25.11.2009, Wednesday
— which customer placed which order..?
SELECT Customers.CompanyName,Orders.OrderID,Orders.OrderDate FROM Customers
INNER JOIN Orders
— PRIMARY KEY — FOREIGN KEY
ON Customers.CustomerID = Orders.CustomerID
— How many orders IN TOTAL has each employee taken..?
— Employees, Orders
SELECT FirstName,LastName, COUNT(OrderID) FROM Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
GROUP BY FirstName,LastName
— We used GROUP BY because we used COUNT..
— Who has taken the most orders..
SELECT FirstName,LastName, COUNT(OrderID) AS ToplamSiparisSayisi FROM Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
GROUP BY FirstName,LastName
ORDER BY ToplamSiparisSayisi DESC
–RELATIONSHIPS ACROSS MULTIPLE TABLES
— Which order,
— which employee,
— which customer placed it..?
SELECT Orders.OrderID,Employees.FirstName,Employees.LastName,Customers.CompanyName FROM Employees
INNER JOIN Orders
ON Employees.EmployeeID=Orders.EmployeeID
INNER JOIN Customers
ON Customers.CustomerID=Orders.CustomerID
— Which product
— is in which category..?
— Who supplies this product..?
SELECT Products.ProductName,Categories.CategoryName,Suppliers.CompanyName FROM Products
INNER JOIN Suppliers
ON Products.SupplierID=Suppliers.SupplierID
INNER JOIN Categories
ON Products.CategoryID=Categories.CategoryID
— Which order,
— which customer placed it,
— which employee took it,
— on which date,
— which shipping company shipped it
— how many units of which product were ordered,
— at which price they were ordered
— which category the product is in
— which supplier supplied this product
SELECT
Orders.OrderID,
Customers.companyName,
Employees.FirstName+ ‘ ‘ + Employees.LastName as Calisan,
Orders.OrderDate,
Shippers.CompanyName as Kargo,
Products.ProductName,
[Order Details].Quantity,
[Order Details].UnitPrice * (1-[Order Details].Discount) as indirimliFiyat,
Categories.CategoryName,
Suppliers.CompanyName as Tedarikci
FROM Employees
INNER JOIN Orders
ON Orders.EmployeeID=Employees.EmployeeID
INNER JOIN Customers
ON Customers.CustomerID=Orders.CustomerID
INNER JOIN Shippers
ON Shippers.ShipperID=Orders.ShipVia
INNER JOIN [Order Details]
ON Orders.OrderID=[Order Details].OrderID
INNER JOIN Products
ON Products.ProductID=[Order Details].ProductID
INNER JOIN Categories
ON Categories.CategoryID=Products.CategoryID
INNER JOIN Suppliers
ON Suppliers.SupplierID=Products.SupplierID
— OUTER JOIN
SELECT * FROM Categories
INSERT into Categories(CategoryName,Description) values
(‘Desserts’, ‘Blah Blah’)
— Categories without any products
SELECT Categories.CategoryName,Products.ProductName FROM Products
RIGHT JOIN Categories
ON Products.CategoryID=Categories.CategoryID
WHERE ProductName is null
— I have 91 customers. Only 89 of them have ordered. Find the 2 who haven’t:
SELECT CompanyName,Address,OrderID FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
WHERE OrderID is null
— Whether you use LEFT or RIGHT depends on which side of the JOIN keyword the table you want to return in full is written.
— To return all records of the table to the left of JOIN, I use LEFT,
— and to return the one on the right, I use RIGHT.
— Earlier we found it like this..
— Which of my customers have never placed an order..? (89 of the 91 customers had ordered..)
— SELECT CompanyName,Address,City,Country FROM Customers
— WHERE CustomerID NOT IN (SELECT DISTINCT CustomerID FROM Orders)
— HOW MANY ORDERS HAS EACH EMPLOYEE TAKEN IN TOTAL SO FAR..?
— there are two different (independent) queries..
SET STATISTICS TIME ON
SELECT EmployeeID,FirstName,LastName,
(SELECT COUNT(orderID) FROM Orders WHERE EmployeeID= x.EmployeeID)
FROM Employees as x
— UNION (combines 2 different ResultSets.)
— combines two or more tables..
— Rule: both ResultSets must have the same number of columns, and the columns must be of the same type in the same order. (int and string can’t be stacked..)
SELECT CompanyName,Address,City,Country, ‘ Customer’ FROM Customers
UNION
SELECT CompanyName,Address,City,Country, ‘ Supplier’ FROM Suppliers
ORDER BY Country
— 01.12.2009, TUESDAY
SET STATISTICS TIME ON
SELECT Products.ProductName, Products.UnitPrice, Categories.CategoryName,Categories.Description FROM Products
CROSS JOIN Categories
— CROSS JOIN lets us see all related and unrelated (LEFT JOIN, RIGHT JOIN, INNER JOIN, OUTER JOIN) tables. It only helps us measure how long a query takes.
CHECK and DEFAULT constraints
use Normalizasyon
ALTER TABLE Urunler
ADD CONSTRAINT DF_AktifMi
DEFAULT 1 for AktifMi
— If we had done it for Products, it would have been DF_Discontinued – for Discontinued…
— CHECK
ALTER TABLE Urunler
ADD CONSTRAINT CK_Fiyat
CHECK (Fiyati > -1)
— Now we can no longer enter a negative value in the products table..
— INDEX —
use Northwind
GO
SELECT ProductName,UnitPrice FROM Products
WHERE ProductID = 1
SELECT * FROM Customers
WHERE Country=’Germany’
— My customers in Germany
— Which column should I index.. If we index Country here, it will search for “Germany” only among the countries..
CREATE Nonclustered INDEX ix_Ulke
ON Customers(Country)
— Queries slow down as gaps build up. Indexes may need cleaning during maintenance every 3–6 months…
— DBCC INDEXDEFRAG
— Running DROP INDEX and creating a new INDEX
SELECT * FROM Northwind.dbo.Products
— FQN: FULLY QUALIFIED NAME: THE FULL IDENTIFIER (an object name that joins all objects from the server down to the column name with dots):
–Example: <serverName>.<databaseName>.<schemaName>.<tableName>.<columnName>
— With FQN, from master I can query my other servers that are linked to (or defined on) my server..
— We can also write the IP address instead of the server name…
CREATE VIEW UrunlereGoreTedarikciler
AS
SELECT ProductName,companyName,Address,City,Country FROM Products
INNER JOIN Suppliers
ON
Products.SupplierID=Suppliers.SupplierID
— Before creating a view, always check whether the query works..
SELECT * FROM UrunlereGoreTedarikciler
— Since it is a table after all, we can also use WHERE criteria..
WHERE CompanyName LIKE ‘E%’
order by Country
SELECT * FROM tumSiparisDetaylari
WHERE OrderID=10248
ORDER BY ProductName
— When you cannot INSERT into a view:
— 1. If the view contains an INNER JOIN (there are lots of tables behind it and it can’t tell which one to insert into)
— 2. If the columns in the ResultSet produced by the view cannot be left empty (NOT NULL)
— 3. If the view was created with the CHECK OPTION
CREATE VIEW AlmanMusteriler
AS
SELECT CustomerID,CompanyName,Address,City,Country FROM Customers
WHERE Country=’Germany’
— It contains no INNER JOIN and includes the required (not null) fields, so it fits the view. (It meets the first two conditions)
WITH CHECK OPTION
— Check the incoming record during the insert and accept it if it fits..
SELECT * FROM AlmanMusteriler
INSERT into AlmanMusteriler(CustomerID,CompanyName,Address, City,Country)
values
(‘ACTHU’,’ACHTUNG Co.’,’Hede’,’Berlin’,’Germany’)
— Had we written Turkey here, we could not have done the insert, because we set the WHERE criteria to Germany. Had we not set any WHERE criteria, there would have been no need for the CHECK OPTION..
— WITH CHECK OPTION: If we want incoming data to follow the view’s rules when an INSERT or UPDATE query runs against a view, we can use the CHECK OPTION.
–If we want to encrypt it, we use WITH ENCRYPTION.. Once it is encrypted, not even we can see it..
CREATE VIEW UrunBilgisi
with Encryption
AS
SELECT ProductName,UnitPrice FROM Products
— INJECTION
SELECT * FROM Users WHERE KullaniciAdi=” AND Sifre=”
— Normally the password check looks like the above.. so the following breaks it..
SELECT * FROM Employees WHERE FirstName=” OR ‘A’=’A’ AND LastName=” OR ‘A’=’A’
‘ or ‘A’=’A
— SCHEMABINDING
— SCRIPT and BATCH
— Variables
DECLARE @Sayi int
— If I assign the variable’s value myself:
SET @Sayi=5
Print(@Sayi)
DECLARE @CalisaninAdi nvarchar(20)
— If the variable’s value comes from a query:
SELECT @CalisaninAdi=FirstName FROM Employees
WHERE EmployeeID=1
Print(@CalisaninAdi)
DECLARE @x int
SET @x=8
Print(‘Number = ‘+
CAST(@x as nchar(2))
)
— take x and convert it to a 2-character nchar..
DECLARE @x int
SET @x=8
Print(‘Number = ‘+
CONVERT(nchar(2),@x)
)
— take x and convert it to a 2-character nchar.. (exactly the same as above)
— How do I get the LAST ORDERID created as soon as I enter an order..?
SELECT TOP 1 OrderID FROM Orders
ORDER BY OrderID DESC
— If I want to put this into a variable:
DECLARE @ID int
SELECT TOP 1 @ID=OrderID FROM Orders
ORDER BY OrderID DESC
Print(@ID)
INSERT into Orders(CustomerID,EmployeeID,OrderDate,ShipVia)
values (‘ALFKI’,’1′,GETDATE(),1)
SELECT @@IDENTITY
— Global variable @@
— No user CAN DEFINE a global SQL variable
INSERT into Orders(CustomerID,EmployeeID,OrderDate,ShipVia)
values (‘ALFKI’,’1′,GETDATE(),1)
SELECT @@IDENTITY
select SCOPE_IDENTITY()
— WHEN / CASE:
SELECT CompanyName,Region =
CASE
WHEN Region=’BC’ THEN ‘Washington’
WHEN Region=’SP’ THEN ‘Pennsylvania’
WHEN Region=’OR’ THEN ‘Orlando’
END
FROM Customers
WHERE Region is not null
SELECT ProductName,Discontinued=
CASE
WHEN Discontinued=’0′ THEN ‘Sales Stopped’
WHEN Discontinued=’1′ THEN ‘Still on Sale’
END
FROM Products
— TRY – CATCH —
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
SELECT ERROR_Message()
END CATCH
— STORED PROCEDURE —
CREATE PROC KategoriGetir
AS
SELECT * FROM Categories
KategoriGetir
CREATE PROC IDyeGoreUrun
@id int
AS
SELECT ProductName,UnitPrice,UnitsInStock FROM Products
WHERE ProductID=@id
IDyeGoreUrun 77
— returns the details of the product with ID 77..
— Normally, when adding a product, we do it like this..
INSERT INTO Categories (CategoryName,Description)
values (@KategoriAdi,@Aciklama)
— Let’s make it a stored procedure..
CREATE PROC KategoriEkle
@KategoriAdi nvarchar(15),
@Aciklama ntext
AS
INSERT INTO Categories (CategoryName,Description)
values (@KategoriAdi,@Aciklama)
KategoriEkle ‘Bakery Products’, ‘Turkish-Style Pastries’
— HOMEWORK WITH A PRIZE: Chocolate for everyone who solves it…
— A procedure that finds the total amount (in price) the customer with the given ID has spent
SELECT SUM([Order Details].UnitPrice*Quantity*(1-Discount))
FROM [Order Details]
INNER JOIN Orders
ON Orders.OrderID=[Order Details].OrderID
INNER JOIN Customers
ON Customers.CustomerID=Orders.CustomerID
WHERE Customers.CustomerID=’ALFKI’
— First let’s check whether this part works and
— now let’s create the procedure
CREATE PROC MusterininAlisVerisi
@ID char(5)
AS
SELECT SUM([Order Details].UnitPrice*Quantity*(1-Discount))
FROM [Order Details]
INNER JOIN Orders
ON Orders.OrderID=[Order Details].OrderID
INNER JOIN Customers
ON Customers.CustomerID=Orders.CustomerID
WHERE Customers.CustomerID=’ALFKI’
MusterininAlisVerisi ‘OCEAN’
SELECT CustomerID from Customers
— It can be varied…
CREATE PROC MusterininAlisVerisi2
@ID char(5)
AS
SELECT SUM([Order Details].UnitPrice*Quantity*(1-Discount)) AS Toplam
FROM [Order Details]
INNER JOIN Orders
ON Orders.OrderID=[Order Details].OrderID
INNER JOIN Customers
ON Customers.CustomerID=Orders.CustomerID
WHERE Customers.CustomerID=@ID
GROUP BY Customers.CustomerID,CompanyName
ORDER BY Toplam DESC
MusterininAlisVerisi2 ‘QUICK’
SELECT CustomerID from Customers
— orders between two dates:
SELECT OrderID,OrderDate,ShipAddress FROM Orders
WHERE OrderDate BETWEEN ’31/12/1996′ AND ’15/07/1997′
— Now let’s create a procedure..
CREATE PROC TariheGoreSiparis
@Tarih1 smalldatetime,
@Tarih2 smalldatetime
AS
SELECT OrderID,OrderDate,ShipAddress FROM Orders
WHERE OrderDate BETWEEN @Tarih1 AND @Tarih2
TariheGoreSiparis ’01/01/1997′, ’06/06/1997′
— Revenue by year
CREATE PROC YilaGoreCiro
@Yil int
AS
SELECT SUM(Quantity * UnitPrice * (1-Discount)) FROM [Order Details]
INNER JOIN Orders
ON Orders.OrderID=[Order Details].OrderID
WHERE YEAR (Orders.OrderDate)=@Yil
YilaGoreCiro 1997
— Which product did the customer with the given ID buy last..?
— To whom has the employee with the given ID sold the most..?
— How long have we been working with the customer with the given ID..?
— The 5 best-selling products in the category with the given ID..?
— Which products does the supplier with the given ID supply to me..?
CREATE PROC HangiUrun
@ID char(5)
AS
SELECT TOP 1 Customers.CompanyName,Products.ProductName,[Order Details].Quantity,Orders.OrderDate FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
INNER JOIN [Order Details]
ON Orders.OrderID=[Order Details].OrderID
JOIN Products
ON [Order Details].ProductID=Products.ProductID
WHERE Customers.CustomerID=@ID
ORDER BY Orders.OrderDate DESC
HangiUrun ‘QUICK’
— We looked at the related ones in the table..
— Customers who bought a flash disk..
SELECT FROM
— 1. Only the orders that contain that product:
CREATE PROC BunuAlanBunudaAldi
@productID int
AS
SELECT TOP 5 ProductName, SUM(Quantity) as toplam FROM Products
INNER JOIN [Order Details]
ON Products.ProductID=[Order Details].ProductID
WHERE
[Order Details].OrderID
IN
(
SELECT OrderID FROM [Order Details]
WHERE ProductID=@productID
)
AND [Order Details].ProductID !=@productID
GROUP BY ProductName
ORDER BY toplam DESC
BunuAlanBunudaAldi 9
—– FUNCTIONS —–
— CALCULATE THE VAT ON ALL PRICES IN THE PRODUCTS TABLE
— PROCEDURE
— If we say CALCULATE THE VAT ON ANY VALUE, it becomes a FUNCTION.. without depending on any table..
CREATE FUNCTION KDVHesaplayici
(
@Para money
)
RETURNS money
AS
BEGIN
DECLARE @Deger money
SET @Deger= @Para * 1.18
RETURN @Deger
END
SELECT ProductName,UnitPrice, dbo.KDVHesaplayici(UnitPrice) AS KDVDahil
— A function always runs bound to a schema
FROM Products
— Let’s square an int number passed as a parameter..
CREATE FUNCTION KaresiniAl
(
@Sayi int
)
RETURNS int
AS
BEGIN
DECLARE @Sonuc int
SET @Sonuc= @Sayi * @Sayi
RETURN @Sonuc
END
SELECT dbo.KaresiniAl(9)
— OR
CREATE FUNCTION KaresiniAlalim
(
@x int
)
RETURNS int
AS
BEGIN
RETURN @x*@x
END
SELECT dbo.KaresiniAlalim(9)
— A.AKYILDIRIM
SELECT UPPER(‘türkay’)
SELECT LOWER (‘ABDULLAH’)
SELECT LEN(‘Dilara’)
SELECT SUBSTRING(‘BilgeAdam’,1,5)
CREATE FUNCTION ParafOlusturucu
(
@Ad nvarchar(50),
@Soyad nvarchar(50)
)
RETURNS nvarchar(100)
AS
BEGIN
DECLARE @donenDeger nvarchar(100)
SELECT @donenDeger = UPPER(SUBSTRING(@Ad,1,1))+’.’+
UPPER(SUBSTRING(@Soyad,1,1)) +
LOWER(SUBSTRING(@Soyad,2,LEN(@Soyad)))
RETURN @donenDeger
END
SELECT dbo.ParafOlusturucu(‘Ali’,’Akyıldırım’)
SELECT TitleOfCourtesy + ‘ ‘ + dbo.ParafOlusturucu(FirstName,LastName)
FROM Employees
— THERE ARE TWO KINDS OF FUNCTIONS: IN-LINE FUNCTIONS and MULTI-LINE FUNCTIONS.
AS THE NAME SUGGESTS, AN IN-LINE FUNCTION CONTAINS ONLY A SINGLE LINE. IN THIS CASE YOU DON’T HAVE TO USE THE BEGIN/END STRUCTURE. A MULTI-LINE FUNCTION, HOWEVER, MUST START WITH BEGIN AND END WITH END.
CREATE FUNCTION ResultSetOlustur
()
RETURNS TABLE
AS
RETURN SELECT CompanyName, Country FROM Customers
SELECT * FROM dbo.ResultSetOlustur()
— TRANSACTION
BEGIN TRY
END TRY
BEGIN CATCH
END CATCH
— THIS IS THE PATTERN..
BEGIN TRY
Begin Tran
— An Insert, Update or Delete query can be used inside a transaction
commit tran
END TRY
BEGIN CATCH
END CATCH
— Let’s see step by step how it is written..
BEGIN TRY
Begin Tran T1
— An Insert, Update or Delete query can be used inside a transaction
Begin Tran T2
— One more query (T1 runs first, then T2)
commit tran
commit tran
END TRY
BEGIN CATCH
— If an error occurs, roll back the whole transaction:
ROLLBACK TRAN T1
END CATCH
BEGIN TRY
BEGIN TRAN KategoriEkle
insert into Categories(CategoryName,Description)
values (‘Shoes’,’Winter/Summer Men/Women’)
Begin Tran UrunSil
DELETE FROM Products WHERE ProductID=1
commit tran
commit Tran
END TRY
BEGIN CATCH
ROLLBACK TRAN KategoriEkle
END CATCH
— For data safety, if the queries to be executed have to run one after another:
— (Example: first add the order, then add the quantity to the order details, then reduce the stock in the products table)
— Always use a transaction block!
–* FOOTNOTE: Stored procedures are the best place to use a transaction block.
— TRIGGER (fires after an INSERT, UPDATE or DELETE query)—
— LET’S WRITE THE TRIGGER THAT REDUCES PRODUCT STOCK WHEN AN ORDER IS RECEIVED..
CREATE TRIGGER tr_OtomatikStok
ON [Order Details]
for INSERT
— RUN WITH THE INSERT QUERY, FIRE TOGETHER WITH THE INSERT QUERY
AS
— Goal: when a record is entered in the Order Details table, find the stock of the related product in the Products table and reduce it by the quantity.
DECLARE @UrunID int
— We defined a variable
DECLARE @Adet int
— Since it reduces the stock by the quantity, we need a variable called Adet (quantity)..
— Every table has two temporary tables attached to it. They cannot be accessed from outside a trigger. They are called inserted and deleted.
— INSERTED: the row created by an INSERT query is first held in the INSERTED temporary table.
— DELETED: a row being deleted is held in the DELETED temporary table before it is deleted.
SELECT @UrunID=ProductID, @Adet=Quantity
FROM INSERTED
UPDATE Products SET UnitsInStock=UnitsInStock-@Adet
WHERE ProductID=@UrunID
— Once the trigger is created, the stock will be updated automatically when I run the following queries.
INSERT into [Order Details](OrderID, ProductID, Quantity)
values (10323, 1, 2)
SELECT ProductName,UnitsInStock FROM Products
WHERE ProductID=1
— requirement: when someone tries to delete a product from the Products table, set its Discontinued column to 1 (i.e. don’t delete it, update it):
CREATE TRIGGER tr_SatisiDurdur
ON Products
INSTEAD OF delete
— run the trigger without letting the delete run
AS
— All INSTEAD OF triggers are written inside BEGIN – END…
— I’ll set Discontinued to 1 for the product I’m trying to delete:
BEGIN
DECLARE @UrunID int
SELECT @UrunID=ProductID FROM deleted
UPDATE Products SET Discontinued=1
WHERE ProductID=@UrunID
END
SELECT * FROM Products WHERE ProductID=1
DELETE FROM Products WHERE ProductID=1
— INSTEAD OF TRIGGERs run INSTEAD OF the triggering query. Their only distinguishing feature is that they are written between BEGIN and END..
— SAMPLE DATABASE – FOR TRIGGERS —
CREATE TABLE Ogrenciler
(
OgrenciNo int,
Ad nvarchar(50),
Soyad nvarchar(50),
DersNotu tinyint
)
GO
— GO: MEANS GO TO THE NEXT STEP
–CREATE TABLE Gecenler
–(
–OgrenciNo int,
–Ad nvarchar(50),
–Soyad nvarchar(50),
–DersNotu tinyint
–)
–GO
–CREATE TABLE Kalanlar
–(
–OgrenciNo int,
–Ad nvarchar(50),
–Soyad nvarchar(50),
–DersNotu tinyint
–)
—- If the grade is below 50, save it to Kalanlar (failed); if it is above 50, save it to Gecenler (passed).. Let it decide by itself..
–CREATE TRIGGER tr_KararVer
–ON Ogrenciler
–FOR INSERT
–AS
–DECLARE @No int
–DECLARE @Ad nvarchar(50)
–DECLARE @Soyad nvarchar(50)
–DECLARE @DersNotu tinyint
–SELECT @No=OgrenciNo, @Ad=Ad, @Soyad=Soyad, @DersNotu=DersNotu
–FROM INSERTED
—- There is only 1 row in the INSERTED temporary table..
–IF @DersNotu <50
— INSERT into Kalanlar values (@No,@Ad,@Soyad,@DersNotu)
–ELSE
— INSERT into Gecenler values (@No,@Ad,@Soyad,@DersNotu)
–INSERT into Ogrenciler values (1, ‘Mustafa’, ‘Mutlu’, 80)
–SELECT * FROM Ogrenciler
–SELECT * FROM Gecenler
–INSERT into Ogrenciler values (1, ‘Türkay’, ‘Ürkmez’, 40)
–SELECT * FROM Ogrenciler
–SELECT * FROM Kalanlar
— SECURITY and PERFORMANCE..
— DBCC COMMANDS
DBCC ShowContig (‘Customers’)
— A handy command that returns performance details of the tables used in the database..
— 08.12.2009, Tuesday —
— Backup code
— ALTER DATABASE Northwind
— SET RECOVERY FULL
— If I usually back up to the same location, I can register this location in SQL. It’s not required, just a convenience.
— system procedure (sp)
— SQL cannot create a folder by itself while backing up. So create a folder named Yedek (backup) on C: and then run the backup..
sp_addumpdevice ‘DISK’, MyDevice , ‘C:\Yedek\NWFull.bak’
SELECT * FROM sys.backup_devices
BACKUP DATABASE Northwind TO MyDevice
— Backing up without a device:
BACKUP DATABASE Northwind TO DISK = ‘C:\Yedek\NWManuel.bak’
— Backups need to be taken periodically. If, instead of repeating Monday’s full backup on Tuesday, I want to back up only the difference, I need to use a differential backup.
INSERT into Northwind.dbo.Products (ProductName,UnitPrice)
values (‘Ice Cream’,5)
BACKUP DATABASE Northwind TO DISK = ‘C:\Yedek\NWFark.bak’
WITH DIFFERENTIAL
— Why should the transaction log be cleaned..? When INSERT, UPDATE and DELETE queries
run, which query ran when and how it
operated is stored in the .ldf database file. Every time we run
these queries, they are written to the log file. After a while
the log files grow excessively, so the execution
speed of such queries drops. All you need to do to speed them up again is take a LOG BACKUP.
— Let’s back up the LOG file..
UPDATE Northwind.dbo.Products SET UnitPrice=10
WHERE ProductID=81
BACKUP LOG Northwind TO DISK = ‘C:\Yedek\LogDosyasi.bak’
— EVERY GOOD THING COMES TO AN END: 😉
DROP DATABASE Northwind
— but some things can be restored:
RESTORE DATABASE Northwind FROM DISK= ‘C:\Yedek\NWFull.bak’ WITH NORECOVERY
RESTORE DATABASE Northwind FROM DISK= ‘C:\Yedek\NWFark.bak’ WITH NORECOVERY
RESTORE LOG Northwind FROM DISK= ‘C:\Yedek\LogDosyasi.bak’ WITH RECOVERY
— The difference between WITH NORECOVERY and WITH RECOVERY:
— Restoring has a specific order. We run them all at once, but keep the full and differential ones waiting and do the RECOVERY last, together with the LOG..


