Create and Use a Database with SQL Commands 2
Hi everyone. We continue with my second article on SQL commands. If you haven’t read my first article, you can find it here:
Let’s continue where we left off. We will continue with ready-made functions in this article.
Count
The ‘Count()’ function returns the total number of records in the specified field or table.
USE [School]
GO
Select Count(*) From Student
We have 4 students. By the way, as you can see, since count is a special function, SQL showed the result in its own table. In such cases, we need to name the table title.
USE [School]
GO
Select Count(*) as ‘Total Student’ From Student
We used the “as” keyword for naming.
Sum
The sum of the values in the specified field is obtained with the ‘Sum()’ function.
USE [School]
GO
Select Sum(Exam1) as ‘Total Point’ From ResultsOfExam
It adds up all the values in that column.
Avg
The average of all values in that column is obtained with the ‘Avg()’ function.
It can only be used in numeric fields.
USE [School]
GO
Select Avg(Exam1) as ‘Avg Point’ From ResultsOfExam
Max
The ‘Max()’ function returns the biggest value in the specified field.
USE [School]
GO
Select Max(Exam1) as ‘Max Point’ From ResultsOfExam
Min
The ‘Min()’ function returns the smallest value in the specified field.
USE [School]
GO
Select Min(Exam1) as ‘Min Point’ From ResultsOfExam
Group By
The ‘Group By’ statement groups rows that have the same values into summary rows.
To better understand the ‘Group By’ command, let’s add a few more records to our database first.
USE [School]
GO
INSERT INTO student (First_Name,Last_Name,Phone,School_Number)
VALUES
(‘Kerem’,’Üzer’,’+905354773417',212120023),
(‘Asiye’,’Ekici’,’+905432241694',212120025),
(‘Beyza Nur’,’Ekici’,’+905558030023',212120027),
(‘Ali’,’Nalcı’,’+905442167812',212120029);
Now let’s find out how many of our students have the same last name.
USE [School]
GO
Select Last_Name,count(*) as ‘Total’ from Student group by Last_Name
As you can see, two people have the same last name, while the others have different surnames.
Finally, let’s compare the first names of the students.
USE [School]
GO
Select First_Name as ‘First Name’,count(*) as ‘Total’ from Student group by First_Name
Having
If a condition needs to be used, the ‘Having’ command is used after using the ‘Group By’ command.
The ‘where’ condition is not used after the ‘Group By’ command.
USE [School]
GO
Select First_Name as ‘First Name’,count(*) as ‘Total’ from Student group by First_Name Having count(*)=1
Like
The ‘like’ command checks if an expression contains only the value we want.
USE [School]
GO
Select * from Student where First_Name Like ‘%a%’
Distinct
The ‘distinct’ statement is used to return only distinct (different) values.
Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
USE [School]
GO
Select First_Name From Student
As you can see, there are 2 names of Ali.
USE [School]
GO
Select distinct First_Name From Student
When using the Distinct command, it brings up only one.
In the meantime, we can also learn how many different names there are in total with the Distinct command.
USE [School]
GO
Select Count(Distinct(First_Name)) From Student
Order By
The ‘Order By’ command is used to sort the result-set in ascending or descending order.
USE [School]
GO
Select * from Student order by First_Name
USE [School]
GO
Select * from Student order by First_Name desc
Top
The ‘Top’ command is used to specify the number of records to return.
USE [School]
GO
Select top 4 * From Student
In
The ‘In’ command allows you to specify multiple values in a ‘where’ clause.
The ‘In’ command is a shorthand for multiple ‘Or’ conditions.
USE [School]
GO
Select * From Student where First_Name in (‘Mesut’,’Hakan’,’Asiye’)
Between
The ‘Between’ command selects values within a given range. The values can be numbers, text, or dates.
USE [School]
GO
Select * From Student where First_Name between ‘A’ and ‘K’
I will write a few more articles about SQL and go step by step.
bolŞans…
