Posted on Aug 16, 2023 • Updated on Nov 10, 2023

Basic SQL Select + From Statements with Examples

#sql #database #programming #beginners

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.

For more SQL articles let's connect on X and LinkedIn

Top comments (4)

Lawrence Cooke • Aug 16 '23

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.