All SQL Database Queries and Commands

SQL Database Queries Clauses

DISTINCT: This expression is used to not list rows that are identical to each other.

select distinct uyeadi from uyeler

IN: We use it to specify a condition. For example, to list
information of workers whose names are netrobin, ali or mehmet.

select * from uyeler where uyeadi in (’netrobin’,’ali’,’mehmet’)

LIKE: If we cannot make a complete comparison to find the record we are looking for

select * from uyeler where uyeadi like ’%a’

SUM Finds the sum of the selected values. To see the total wages received by workers

select sum(ucret) from uyeler

select MAX(UCRET ), MIN(UCRET ), AVG(UCRET ) from uyeler where tarih>’01.01.1999’

ORDER BY ASC: Used to alphabetize the records we selected from the table.

select * from uyeler order by asc

ORDER BY DESC: It is used to sort the records we selected from the table according to the last record order.

select * from uyeler order by desc

GROUP BY: Generally used for statistical purposes. For example, to find out how many workers were recruited on which date.

ALIAS: In general, we can change the name of the table or column as if it were something else.
We made the name of the uyeadi column in the Members table a name, so we can call it a name.

SELECT uyeadi AS isim FROM uyeler

SELECT * FROM uyeler,detaylar WHERE uyeler.id=detaylar.uyeno
SELECT * FROM uyeler INNER JOİN detaylar ON uyeler.id=detaylar.uyeno
SELECT * FROM uyeler LEFT JOİN detaylar ON uyeler.id=detaylar.uyeno

UNION and UNION ALL : Similar to the Join method but only connects tables with the same columns. It queries the names in the table Uyeler and Detylar.

Select uyeadi from uyeler UNION Select uyeadi from detaylar

If we use only UNION, it repeats even if the two tables have the same names.
If we use UNION ALL, it queries only one of them even if the two tables have the same names.

Select uyeadi from uyeler UNION ALL Select uyeadi from detaylar

SQL Functions

Sql contains many functions, which can be used for counting and calculation.

Function syntax organization;
SELECT function(column) FROM table

Function types;
sql functions have several basic types and categories. Basic function types:

• Aggregate functions
• Scalar (singular value) functions

This type of function works with many values, but the result is a single value.

These functions are suitable for MS Access:

AVG(column ): returns the arithmetic mean value of the numbers in the entered column
COUNT(column ): Returns the number of rows in the entered column, excluding empty values
COUNT(* ): Returns the number of rows in the given table
FIRST(column ): returns the first value in the entered column
LAST(column ): returns the last value in the entered column
MAX(column ): returns the highest value in the entered column
MIN(column ): returns the lowest value in the entered column
STDEV(column ): returns the simple statistical standard deviation in the entered column
STDEVP(column ): returns the population statistical standard deviation in the entered column
SUM(column ): returns the sum of the numbers in the entered column

These functions are suitable for sql Server

AVG(column ): returns the arithmetic mean value of the numbers in the entered column
BINARY_CHECKSUM: returns the checksum value in the given row of the table in binary
CHECKSUM: returns the checksum value in the given row of the table
CHECKSUM_AGG: sends the checksum value of the data except empty values
COUNT(column ): Sends the number of rows in the entered column, except empty values
COUNT(* ): Sends the number of rows in the given table
COUNT(DISTINCT column ): Returns the number of rows in the given table, but counts rows with duplicate data once
FIRST(column ): returns the first value in the entered column
LAST(column ): returns the last value in the entered column
MAX(column ): returns the highest value in the entered column
MIN(column ): returns the lowest value in the entered column
STDEV(column ): returns the simple statistical standard deviation in the entered column
STDEVP(column ): returns the population statistical standard deviation in the entered column
SUM(column ): returns the sum of the numbers in the entered column

Scalar (singular value) functions

It works with a value according to the value entered and sends a value as a result. These functions are suitable for MS Access:
UCASE(c ): capitalizes all characters in the region
LCASE(c ): reduces all characters in the region
MID(c,start[,end] ): executes characters from the text field
INSTR(c ): displays characters from the text field
LEFT(c,characterNumarasi ): returns the text field up to the number entered (counts from the left)
RIGHT(c,number_of_char ): returns the text field up to the entered number (counts from the right)
ROUND(c,precision ): rounds the number field to the given precision
MOD(x,y ): displays the remainder of a division operation (does mod operation )
NOW( ): displays the current system time
FORMAT(c,format ): changes the display format of the field
DATEDIFF(d,firstDate,secondDate ): used to calculate dates.