Note: Udemy'den satın aldığım SQL kursuna ait notlarımı içerir.
--BASIC QUERIES (TEMEL SORGULAR)
Select * from Products;
Select ProductID as ID, ProductName as Name from Products;
Select p.ProductName, p.UnitPrice * p.UnitsInStock as Total from Products p;
Select p.ProductName + '-' + p.QuantityPerUnit from Products p;
Select 'Engin Demiroğ' as Eğitmen
Select 9*8 as Sonuç;
Select p.ProductName, p.UnitsInStock, p.UnitsOnOrder from Products p where UnitsInStock=0 and UnitsOnOrder>0;
Select * from Products where UnitsInStock=0 or UnitsOnOrder=0;
Select * from Products where not ProductName = 'Perth Pasties' and UnitsInStock = 0;
Select * from Products where not ProductName = 'Perth Pasties' and UnitsInStock = 0 and UnitsOnOrder > 0;
Select * from Products order by UnitPrice asc, ProductName desc;
Select ProductName from Products where ProductName like 'ch%';
Select ProductName, UnitPrice from Products where UnitPrice between 10 and 46 order by UnitPrice desc;
Select ProductID, ProductName, CategoryID from Products where CategoryID in (1,2);
--AGGREGATION FUNCTIONS (KÜMÜLATİF FONKSİYONLAR)
Select Count (*) as [Product Count] from Products;
Select Count (ProductName) as [Product Name Count from All Products] from Products;
Select Count (Region) as [Region Count from All Customers] from Customers;
Select Min (UnitPrice) as [Min. Unite Price in Products] from Products;
Select Max (UnitPrice) as [Max. Unit Price in Products] from Products;
Select Avg (UnitPrice) as [Avg. Unit Price in Products] from Products;
Select Sum (UnitPrice * Quantity) as [Total Sales Amount] from [Order Details];
Select Rand()
Select Left ('001Engin-Demiroğ', 3) as [Insturctor Name Code];
Select Left (ProductName, 4) as [First Four Letters of All Products] from Products;
Select Right ('Onur-Yürük001', 3) as [Learner Name Code];
Select Right (ContactName, 5) as [Last Five Letters of All Contact Names] from Customers;
Select LEN ('Engin Demiroğ') as [Length of the Name Typed];
Select ProductName, LEN (ProductName) as [Length of the Product Name] from Products;
Select ProductName, LEN (ProductName) as [Length of the Product Name] from Products where LEN(ProductName) <= 10;
Select Lower ('OnUr YüRüK');
Select Lower (ProductName) as [Products] from Products;
Select Upper ('OnUr YüRüK');
Select Upper (ContactName) as [Contacts] from Customers;
Select Trim (' Onur Yürük ') as [Full Name];
Select LTrim (' Onur Yürük');
Select RTrim ('Onur Yürük ');
Select Trim (ProductName) as [Product Name] from Products where Trim (ProductName) = 'Chai';
Select Reverse ('Onur Yürük');
Select Charindex ('ü', 'Onur Yürük', 8);
Select ProductName from Products where Charindex ('anton', ProductName, 1) > 5;
Select Replace ('Onur Yürük', ' ', '_');
Select Replace (ProductName, ' ', '_') as [Products] from Products
Select Substring ('Onur Yürük', 6, 5) as [Surname];
Select Ascii ('A') as [ASCII Value of A];
Select Char ('65') as [CHAR Value of ASCII Value A];
Select Distinct Country, City from Customers order by Country
Select Country, City, Count (*) as [Count of Country & City] from Customers where City != 'Nantes' group by Country, City having count(*) > 1 order by Country; --***
--- ***INNER JOIN - LEFT JOIN - RIGHT JOIN - FULL JOIN***
Select p.ProductID, p.ProductName, p.UnitPrice, p.UnitsInStock, c.CategoryName, c.Description from Products p inner join Categories c on p.CategoryID = c.CategoryID
Select p.ProductID, p.ProductName, c.CategoryName, p.UnitPrice, p.UnitsInStock from Products p inner join Categories c on p.CategoryID = c.CategoryID where p.UnitPrice > 10 order by p.ProductName;
Select * from Products p left join [Order Details] od on p.ProductID = od.ProductID where od.ProductID is null; --Hiç satılamayan ürünler nelerdir?
Select * from Customers c left join Orders o on c.CustomerID = o.CustomerID where o.CustomerID is null; --Müşterilerde olup hiç sipariş vermeyen müşteriler kimdir?
Select * from Orders o right join Customers c on o.CustomerID = c.CustomerID where o.CustomerID is null;
Select * from Customers c full join Orders o on c.CustomerID = o.CustomerID;-+
Select p.ProductName, count(*) as Amount from Products p inner join [Order Details] od on p.ProductID = od.ProductID where od.Discount > 0 group by ProductName
------------------------------------------------------------
Insert into Categories (CategoryName, Description) values ('Test Cateegory2', 'Test Category Description2');
Insert into [Order Details] values (10248,12,12,10,0);
Update Categories set CategoryName = 'Test Category 3', Description = 'Test Category 3 Description' where CategoryID >=9; --Burada where çok önemli yoksa tüm kolonunu değiştirir!!!
Update Territories set TerritoryDescription
= 'İç Anadolu';
Delete from Categories where CategoryID >= 9;
------------------------------------------------------------
Select * from CustomersWork;
Insert into CustomersWork (CustomerID, CompanyName, ContactName) select CustomerID, CompanyName, ContactName from Customers;
Insert into CustomersWork (CustomerID, CompanyName, ContactName) select CustomerID, CompanyName, ContactName from Customers where ContactName like '%test1%'
Update Customers set CompanyName = CustomersWork.CompanyName from Customers inner join CustomersWork on Customers.CustomerID = CustomersWork.CustomerID where CustomersWork.CompanyName like '%Test1%'
Select CustomerID, CompanyName, ContactName, from Customers union all select * from CustomersWork --Mükerrer kayıtları da listeler (Also lists duplicate records.)
ＴＫ・秀樹さん｜泽睿
