
Customer’s |
name |
surname |
Date of birth |
city |
gender |
point |
1 |
Ahmet |
Cansever |
1956-02-19 00:00:00.000 |
İstanbul |
M |
64 |
2 |
Mehmet |
Aydın |
1976-02-19 00:00:00.000 |
Samsun |
M |
55 |
3 |
Aliye |
Seven |
1966-06-10 00:00:00.000 |
Konya |
F |
45 |
4 |
Burak |
Sayın |
1996-02-19 00:00:00.000 |
İstanbul |
M |
23 |
5 |
Beyza |
Kılıç |
1955-12-30 00:00:00.000 |
Manisa |
F |
85 |
The information in the table above should be translated into English (For example, Genetics, Name and Surname) and information should be changed according to UK-US
To list the name and surname columns in the customer table;
1 |
Select name, surname FROM customer |
To list all records in the customer table;
1 |
Select *From customer |
A column in a table may contain duplicate values. We can list distinct values with Distinct.
1 |
SELECT DISTINCT city FROM customer; |
With the Where keyword, we can ensure that only records that meet the specified rule are listed. For example, to list records in the customer table whose city column is Istanbul;
1 |
SELECT * FROM customer WHERE city='istanbul' |
or to list records with gender "F";
1 |
SELECT * FROM customer WHERE gender='K' |
Operators we can use with Where:
Operator |
Explanation |
= |
Equal |
<> |
Not Equal. Note: In some versions “!=” can be used. |
> |
Greater |
< |
Smaller . |
>= |
Greater Equal |
<= |
Smaller Equal |
BETWEEN |
between |
LIKE |
Pattern search |
IN |
To specify multiple possible values for a column |
AND Operator works if Condition 1 and Condition 2 are true. For example, to list those whose Gender is "E" and City is "Istanbul" in the customer table;
1 2 3 |
SELECT * FROM customer WHERE city='İstanbul' AND gender='E' |
OR operator works if either Condition 1 or Condition 2 is true. For example, to list the cities Istanbul or Samsun in the customer table;
1 2 3 |
SELECT * FROM customer WHERE city='İstanbul' OR city='Samsun' |
AND and OR operators can also be used together. For example, to list the customers table with gender 'K' and city 'Konya' or 'Manisa'.
1 2 3 |
SELECT * FROM customer WHERE gender='K' AND (City='Konya' OR City='Manisa') |
SQL Where usage ile ilgili daha fazla örnek için tıklayın.
Click for more examples of using SQL Where.
SQL ORDER BY USAGE
ORDER BY sorts records in ascending order by default. You can use the DESC keyword to sort records in descending order. For example, let's sort records in the customer table by the name column in ascending and descending order.
1 2 |
SELECT * FROM customer ORDER BY ad |
Descending sort example;
1 2 |
SELECT * FROM customer ORDER BY ad DESC |
It is used to add a record. For example, let's add a record to the customer table.
1 2 |
INSERT INTO customer(ad, surname, date of birth, city, gender, point) VALUES ('Ali','Şahin','2000-10-12','Burdur','E',68) |
It is used to make changes and updates on records. For example, let's change the point of the record with customerno 3 to 90.
1 2 3 |
UPDATE customers SET point=90 WHERE customer no=3 |
It is used to delete a record from the table. For example, to delete a record with customer no 4
1 2 |
DELETE FROM customers WHERE customer no=4 |
To delete all records from the customers table
1 |
DELETE * FROM customers |
It is used to display a specified number of records. For example, let's list the first 5 records in the customers table.
1 |
SELECT TOP 5 * FROM customers |
It is used to search for a specified value. For example, let's list the records in the customers table whose city starts with S.
1 2 |
SELECT * FROM customers WHERE city LIKE 's%' |
To list records whose city ends with s;
1 2 |
SELECT * FROM customers WHERE city LIKE '%s' |
To list the records that contain "tan" in the city;
1 2 |
SELECT * FROM customers WHERE city LIKE '%tan%' |
To list records whose names start with "al" in the customers table;
1 2 |
SELECT * FROM customers WHERE AD LIKE 'al%' |
To list records whose names end with "al" in the customers table;
1 2 |
SELECT * FROM customers WHERE AD LIKE '%al' |
Let's list the records in the customers table whose name starts with A, the next 2 characters are any letter, and continues with e, and the next letter is unknown. (We will list the ones whose name is Ahmet. :))
1 2 |
SELECT * FROM customers WHERE ad LIKE 'A _ _ e _' |
Let's list the records whose names start with a, b or s.
1 2 |
SELECT * FROM customers WHERE adLIKE '[abs]%' |
Now, let's list the records that do not start with a, b or s.
1 2 |
SELECT * FROM customers WHERE adLIKE '[!abs]%' |
The IN operator allows you to specify multiple values in the WHERE clause. For example, let's list records whose city is Istanbul and Konya.
1 2 |
SELECT * FROM customers WHERE city IN ('İstanbul','Konya') |
The Between operator is used to list records between certain criteria. A number, text or date range can be given. For example, let's list records in the customers table whose points are between 70 and 90.
1 2 |
SELECT * FROM customers WHERE pointNOT BETWEEN 70 AND 90 |
To list people whose birth dates are between 01/01/1996 and 01/01/2006;
1 2 |
SELECT * FROM customers WHERE date of birth BETWEEN '01/01/1996' AND '01/01/2006' |
Let's list the records in the customers table whose names are between C and E.
1 2 |
SELECT * FROM customers WHERE ad BETWEEN 'C' AND 'E' |
It is used to give a temporary name to the columns in the SQL table.
1 2 |
SELECT ad AS NAME, surname AS SURNAME, date of birth AS [DATE OF BIRTH] FROM customers |
Let's examine the example created using the Orders and Customers tables.
OrderID |
CustomerID |
OrderDate |
10308 |
2 |
1996-09-18 |
10309 |
37 |
1996-09-19 |
10310 |
77 |
1996-09-20 |
CustomerID |
CustomerName |
ContactName |
Country |
1 |
Alfreds Futterkiste |
Maria Anders |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Mexico |
3 |
Antonio Moreno Taquería |
Antonio Moreno |
Mexico |
1 2 3 4 |
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID; |
When we run our query
OrderID |
CustomerName |
OrderDate |
10308 |
Ana Trujillo Emparedados y helados |
9/18/1996 |
10365 |
Antonio Moreno Taquería |
11/27/1996 |
10383 |
Around the Horn |
12/16/1996 |
10355 |
Around the Horn |
11/15/1996 |
10278 |
Berglunds snabbköp |
8/12/1996 |
1 2 3 4 5 |
SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName |
1 2 3 4 5 |
SELECT Orders.OrderID, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID ORDER BY Orders.OrderID |
The SELECT INTO statement selects data from a table and adds it to a new table. For example, we can create a backup of the customers table named customersbackup.
1 2 3 |
SELECT * INTO customersbackup FROM customers |
To add records with only the city "Istanbul";
1 2 3 4 |
SELECT * INTO customersbackup FROM customers WHERE city='İstanbul' |
To create a new database;
1 |
SQL CREATE DATABASE dbname |
It is used to create a new table.
1 2 3 4 5 6 7 8 |
CREATE TABLE customers ( id int, ad varchar(255), surname varchar(255), address varchar(255), city varchar(255) ); |
SQL AVG USAGE
The AVG() function returns the average value of a numeric column. For example, let's find the point average in the customers table.
1 |
SELECT AVG(point) FROM customers |
In the query below, the name and surname data of the records whose points are above the point average in the customers table are listed.
1 2 |
SELECT ad, surname FROM customers WHERE point>(SELECT AVG(point) FROM customers); |
The COUNT() function returns the number of rows that match the specified criteria. For example, let's find the total number of records in the customers table.
1 |
SELECT COUNT(*) FROM customers; |
Now let's find out how many different cities there are in the customers table.
1 |
SELECT COUNT(DISTINCT city) FROM customers; |
Now let's find the number of Customers born in February.
1 |
SELECT COUNT (*) FROM customers WHERE MONTH(date of birth)=2 |
The following SQL Statement finds the number of orders for "CustomerID" = 7 from the "Orders" table.
1 2 |
SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders WHERE CustomerID=7 |
Used to round a decimal number.
1 2 |
SELECT productname, ROUND(price,0) AS RoundedPrice FROM products |
The LEN() function returns the length of a value in a text field. For example, let's print the surname lengths next to the name field in the customers table.
1 2 |
SELECT name,LEN(surname) as surnamelength FROM customers; |
The NOW function returns the system date and time. For example, the following SQL statement will select the product name and price for today from the "Products" table.
1 2 |
SELECT ProductName, Price, Now() AS PerDate FROM Products; |
The MAX() function returns the maximum value of the selected column. For example, let's display the highest point in the customers table.
1 |
Select MAX(point) AS TheHighestPoint FROM customers |
Now let's list the customers with the highest points by name and surname.
1 2 3 4 |
SELECT name,surname,point FROM customers WHERE point =(SELECT MAX(point) FROM customers) |
The MIN() function returns the minimum value of the selected column. Let's display the minimum point in the students table.
1 |
Select MIN(point) AS TheLowestPoint FROM students |
Now let's list the students with the lowest scores with their first and last names.
1 2 3 4 |
SELECT name,surname,point FROM students WHERE point =(SELECT MAX(point) FROM students) |
In FULL (OUTER) JOIN usage, if we consider the tables as two sets, it returns all the data of the left side table and the right side table as a result.
As a result of this Join, the matching data is shown in the same row, and the unmatched data is returned as empty or "null".
Sample Query;
* You can run this query in the SQL editor and try the result.
1 2 |
DECLARE @Productler TABLE (Id INT,Product NVARCHAR(50),TurId INT) DECLARE @Types TABLE (Id INT,Tur NVARCHAR(50)) |
||||
1 2 3 4 5 |
INSERT INTO @Productler(Id,Product,TurId) VALUES (1,'Bread',1), (2,'Bagel',1), (3,'Gofret',2) |
|
|||
|
|||||
|
|||||
|
|||||
|
|||||
1 2 3 4 5 |
INSERT INTO @Types(Id,Tur) VALUES (1,'Bakery Products'), (2,'Confectionery'), (3, 'Drink') |
|
|||
|
|||||
|
|||||
|
|||||
|
|||||
1 2 |
SELECT u.Id,u.Product.t.Tur from @Productler u FULL JOIN @Types t on u.TurId = t.Id |
|
|||
|
|||||
|
As we see in the example, Beverage, which is a type that is not in the Products table, is also among our results and since there is no product to match, the Product Id and Name are empty.
Group by performs grouping according to the desired column or columns in the selected data set.
Sample Query;
* You can run this query in the SQL editor and try the result.
1 2 |
DECLARE @Products TABLE (Id INT,Product NVARCHAR(50),TurId INT) DECLARE @Types TABLE (Id INT,Tur NVARCHAR(50)) |
||||
1 2 3 4 5 6 7 8 9 |
INSERT INTO @Products(Id,Product,TurId) VALUES (1,'Bread',1), (2,'Bagel',1), (3, 'Wafer',2), (4, 'Pogaca',1), (5, 'Cracker',2), (6, 'Iced Tea',3), (2,'Savory Bun',1) |
|
|||
|
|||||
|
|||||
|
|||||
|
|||||
|
|||||
|
|||||
|
|||||
|
|||||
1 2 3 4 5 |
INSERT INTO @Types(Id,Tur) VALUES (1, 'Bakery Products'), (2, 'Confectionery'), (3, 'Drink') |
|
|||
|
|||||
|
|||||
|
|||||
|
|||||
1 2
3 4 |
SELECT t.Tur,COUNT(u.Id) as 'Piece' FROM @Products u LEFT JOIN @Types t on u.TurId = t.Id -- HAVING COUNT(u.Id) > 2 Order by 'Piece' desc |
|
|||
|
|||||
|
|||||
|
Having command is used after Group by command for filtering according to your request. If you open having line which is closed in the sample code above, you can see that categories with product count more than 2 are filtered.