3-SQL Joins,Boolean Operators,Like,AggreGate Functions,Insert,Update,Delete

SQL JOINS

SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.

SQL JOIN

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

Tables in a database are often related to each other with keys.

A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.

Look at the "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Note that the "P_Id" column is the primary key in the "Persons" table. This means that no two rows can have the same P_Id. The P_Id distinguishes two persons even if they have the same name.

Next, we have the "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Note that the "O_Id" column is the primary key in the "Orders" table and that the "P_Id" column refers to the persons in the "Persons" table without using their names.

Notice that the relationship between the two tables above is the "P_Id" column.

Different SQL JOINs

Before we continue with examples, we will list the types of JOIN you can use, and the differences between them.

JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables

USE YazOkulu

--inner join

select Ad,Soyadi,RolAdi

from Kullanici INNER JOIN Rol

Kullanici.RolId=Rol.ID

--join yapmadan

--select Ad,Soyadi,RolAdi from Rol,Kullanici where Kullanici.RolId=Rol.ID

USE YazOkulu

--outer join

select Urun.Ad,Kategori.Ad

from Urun left outer join Kategori

Urun.KategoriId=Kategori.ID

--outer join

select Urun.Ad,Kategori.Ad

from Urun right outer join Kategori

Urun.KategoriId=Kategori.ID

--full outer join

select Urun.Ad,Kategori.Ad

from Urun full outer join Kategori

Urun.KategoriId=Kategori.ID

*= left outer joine denktir.

--CROSSS JOIN

--İki tablonun kartezyen çarpimini oluşturur.

select *from Urun

cross join Kategori

--cross join yerine

select * from Urun,Kategori

--kolonun isminde deðþikilik yapmak için as kullanýlýr

select Urun.Ad as 'Ürünün adý',Kategori.Ad

from Urun left outer join Kategori

Urun.KategoriId=Kategori.ID

BOLEAN OPERATORS

---boolean operatorler

--NOT

--AND

--OR

select Ad from Kullanici where Ad !='a' --buyuk küçük harf duyarlilği yok

select Ad from Kullanici where Ad <>'a' --buyuk küçük harf d duyarlilği var

AND & OR Operators

The AND & OR operators are used to filter records based on more than one condition.

The AND operator displays a record if both the first condition and the second condition is true.

The OR operator displays a record if either the first condition or the second condition is true.

AND Operator Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Now we want to select only the persons with the first name equal to "Tove" AND the last name equal to "Svendson":

We use the following SELECT statement:

SELECT * FROM Persons
WHERE FirstName='Tove'
AND LastName='Svendson'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes

OR Operator Example

Now we want to select only the persons with the first name equal to "Tove" OR the first name equal to "Ola":

We use the following SELECT statement:

SELECT * FROM Persons
WHERE FirstName='Tove'
OR FirstName='Ola'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes

Combining AND & OR

You can also combine AND and OR (use parenthesis to form complex expressions).

Now we want to select only the persons with the last name equal to "Svendson" AND the first name equal to "Tove" OR to "Ola":

We use the following SELECT statement:

SELECT * FROM Persons WHERE
LastName='Svendson'
AND (FirstName='Tove' OR FirstName='Ola')

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes

BETWEEN

Select * from Urun where Ad between '.' and ','

--between istediğimiz iki değer arasi sonuçlari geri döndürür.

LIKE

--Like operatoru

--: % kaç karakter ve hangi karakter olursa olsun anlamındadır.

--: _ ise tek bir karakteri temsil eder.

--:[a-z] a ile z arasindakilerden bir değer geri döndürür

--:[ab] a ya da b olanları geri döndür.

--:soru:ikinci karkter a ve son karakteri b olani bul.

select *from Urun where Ad like '_a%b'

--örnek:son harfi a olan veriler:

select *from Urun where Ad like '%a'

--örnek:ilk harfi a ya da b olan veriler:

select *from Urun where Ad like '[ab]%'

--örnek:ilk harfi a ya da b olmayan veriler:

select *from Urun where Ad like '[^ab]%'

--örnek:tüm veriler:

select *from Urun where Ad like '%' -- null geri donmedi

--örnek:ilk harfi b-z arasında olmayan veriler:

select *from Urun where Ad like '[^b-z]%'

ORDER BY

--ORDER BY Ada göre siralar(default olarak azalan sirdada(asc), desc yazilirsa artan sirada yazar)

select * from ParcaSiparis order by ParcaSiparis.ParcaId desc

TOP

Sorgudan belirtilen sayıda en baştaki kayıtları görüntüler.

select top 2 * from ParcaSiparis order by ParcaSiparis.ParcaId asc

AGGREGATE FUNCTIONS IN SQL

--AVG:Sutunun Ortalamasini alir

--COUNT:Kayit sayar

--MAX ve MIN Sutundaki en buyuk ve en küçük deðerleri geri döndürür

--SUM: Sutunun toplamasini alir

--HAVING :Satilari gruplarken şart koşar çünkü aggregate functionlar kullanilacaksa where koşulu kullanilamaz.

SQL COUNT(column_name) Example

We have the following "Orders" table:

O_Id	OrderDate	OrderPrice	Customer
1	2008/11/12	1000	Hansen
2	2008/10/23	1600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2000	Jensen
6	2008/10/04	100	Nilsen

Now we want to count the number of orders from "Customer Nilsen".

We use the following SQL statement:

SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Nilsen'

The result of the SQL statement above will be 2, because the customer Nilsen has made 2 orders in total:

CustomerNilsen

DISTINCT

--DISTINCT: tekrarli satirlari göstermez.

INSERT,DELETE,UPDATE

--INSERT INTO <table_name> (stun1,stn2) values (deger1,deger2) tabloya yeni satir ekler.

insert into Urun(KategoriId,Ad)

values (2,1,'Coca-Cola')

-- UPDATE <table_name> tabloyu günceller.

--SET sutun1=deger1,sutun2=deger2

Update Urun set KategoriId=3,MarkaId=2 where ID=3

---DELETE FROM<table_name> tablodan satir siler.

--WHERE alanad=deger

delete from Urun where ID=3

Aslında databaselerde update işlemi yoktur Update işlemi delete ve insert işlemlerinin ard arda sıralanmasından oluşur.

Author Description