Basic SQL Select + From Statements with Examples
Within SQL, the SELECT statement serves the purpose of retrieving data from a database. Occasionally, we have specific preferences for how the data should be presented when retrieved from the database, like obtaining the highest number, lowest number, and other variations. This write-up includes SELECT + statements for the following clauses and functions:
Table of Content
- SELECT + TOP
- SELECT + DISTINCT
- SELECT + COUNT
- SELECT + AS
- SELECT + MAX
- SELECT + MIN
- SELECT + AVG
The table named "EmployeDemographics" presented below will be the focus of our examination. I'm utilizing the MSSQL database management system for this example. Additionally, it's important to note that the SQL functions are not affected by letter cases when typed, and these queries might differ on other databases.
SELECT + TOP
This is employed to indicate the number of records that should be retrieved from the table.
Example:
SELECT TOP 5
FROM EmployeDemographics
This will select the top 5 data values in every column.
The provided illustration gives you the first five data from the complete table, with the option to indicate the specific column you desire to retrieve.
For Example:
SELECT TOP 5(GENDER)
FROM EmployeDemographics
This will return the top 5 data values on the GENDER column.
SELECT + DISTINCT
The SELECT DISTINCT statement is utilized to retrieve distinct or unique values exclusively.
For Example:
SELECT DISTINCT (GENDER)
FROM EmployeDemographics
The function used retrieves unique values from the gender column. The outcome shows that there are only two distinct genders in the returned table.
The result:
Another Example:
SELECT DISTINCT (EmplyoeeID)
FROM EmployeDemographics
The function mentioned will provide the unique value within the EmployeeID Column.
SELECT + COUNT
The COUNT() function provides the number of rows that meet a particular condition.
For Example:
SELECT COUNT(FirstName)
FROM EmployeDemographics
The function calculates the count of non-null values for the FirstName column, resulting in a value of 9. Also, if there's a NULL value in the column, it's treated as a count of zero.
SELECT + AS
This statement or function assigns a column alias, providing an alternate name that can be employed in the resulting set.
In the previous example, you might have observed that the outcome displayed was 9, yet the label assigned to the result set above the column was noted as (No Column name). However, this can be modified.
Let us use another column.
For Example:
SELECT COUNT (EmplyoeeID) AS LastName
FROM EmployeDemographics
You have the flexibility to choose any name that suits your result set. The function above calculates the count of EmployeeID, which is 9, and labels it as LastName. This is an exploration of how the SELECT AS function can be beneficial.
The result:
Prior to progressing to the MIN, MAX, AND AVG functions, we'll utilize the following table known as 'EMPLOYEESALARY'.
.
SELECT + MIN
The MIN() function provides the minimum value from the chosen column.
For Example:
SELECT MIN(Salary)
FROM EmployeeSalary
Observing the column, we can see that the lowest value is 20000, and this particular minimum value is anticipated to be the outcome when returned.
SELECT + MAX
The max() function returns the maximum value from the chosen column.
For Example:
SELECT MAX(Salary)
FROM EmployeeSalary
As anticipated, the function will provide the maximum value present in the column.
SELECT + AVG
This returns the average of all the values of the Selected numeric column.
For Example:
SELECT AVG(Salary)
FROM EmployeeSalary
The function mentioned above will calculate and yield the average value from our EmployeeSalary Table.
Conclusion
This article serves as a valuable educational tool for individuals who are in the process of learning SQL by offering clear explanations, illustrative visuals, and practical examples for understanding the fundamental SQL select commands.
SELECT + TOP: Retrieving a specified number of records from a table.
SELECT + DISTINCT: Retrieving unique values from a column.
SELECT + COUNT: Counting the number of rows meeting a specific condition.
SELECT + AS: Assigning column aliases for custom result column names.
SELECT + MIN: Finding the minimum value in a selected column.
SELECT + MAX: Finding the maximum value in a selected column.
SELECT + AVG: Calculating the average of values in a selected numeric column.
These concepts are illustrated using the "EmployeeDemographics" and "EmployeeSalary" tables.
Top comments (4)
In your article you mention you are using MySQL, It should be noted that SELECT TOP does not work in MySQL, SELECT TOP is a MSSQL only call, In MySQL you would use LIMIT (SELECT * FROM table LIMIT 5) to achieve the same thing.
Sorry i just noticed.
Thanks for the correction 😊
It is important to remember that these queries may differ across databases.
I'll update that.
Thank you so much