DEV Community

Cover image for Basic SQL Select + From Statements with Examples
Ibrahim Enemona Abdulrasheed
Ibrahim Enemona Abdulrasheed

Posted on • Updated on

Basic SQL Select + From Statements with Examples

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.

Table

SELECT + TOP

This is employed to indicate the number of records that should be retrieved from the table.

Example:

SELECT TOP 5
FROM EmployeDemographics
Enter fullscreen mode Exit fullscreen mode

This will select the top 5 data values in every column.

Top 5 on the table

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
Enter fullscreen mode Exit fullscreen mode

This will return the top 5 data values on the GENDER column.

Top 5 gender

SELECT + DISTINCT

The SELECT DISTINCT statement is utilized to retrieve distinct or unique values exclusively.

For Example:

SELECT DISTINCT (GENDER)
FROM EmployeDemographics
Enter fullscreen mode Exit fullscreen mode

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:
distinct Example

Another Example:

SELECT DISTINCT (EmplyoeeID)
FROM EmployeDemographics
Enter fullscreen mode Exit fullscreen mode

The function mentioned will provide the unique value within the EmployeeID Column.

distinct employeeId

SELECT + COUNT

The COUNT() function provides the number of rows that meet a particular condition.

For Example:

SELECT COUNT(FirstName)
FROM EmployeDemographics
Enter fullscreen mode Exit fullscreen mode

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.

count exercise

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
Enter fullscreen mode Exit fullscreen mode

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:

alias LastName

Prior to progressing to the MIN, MAX, AND AVG functions, we'll utilize the following table known as 'EMPLOYEESALARY'.

Employeesalary Table.

SELECT + MIN

The MIN() function provides the minimum value from the chosen column.

For Example:

SELECT MIN(Salary)
FROM EmployeeSalary
Enter fullscreen mode Exit fullscreen mode

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.

minimum salary

SELECT + MAX

The max() function returns the maximum value from the chosen column.

For Example:

SELECT MAX(Salary)
FROM EmployeeSalary
Enter fullscreen mode Exit fullscreen mode

As anticipated, the function will provide the maximum value present in the column.

Max Table

SELECT + AVG

This returns the average of all the values of the Selected numeric column.

For Example:

SELECT AVG(Salary)
FROM EmployeeSalary
Enter fullscreen mode Exit fullscreen mode

The function mentioned above will calculate and yield the average value from our EmployeeSalary Table.

Average 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.

  1. SELECT + TOP: Retrieving a specified number of records from a table.

  2. SELECT + DISTINCT: Retrieving unique values from a column.

  3. SELECT + COUNT: Counting the number of rows meeting a specific condition.

  4. SELECT + AS: Assigning column aliases for custom result column names.

  5. SELECT + MIN: Finding the minimum value in a selected column.

  6. SELECT + MAX: Finding the maximum value in a selected column.

  7. 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)

Collapse
 
mrpercival profile image
Lawrence Cooke

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.

Collapse
 
rashtech profile image
Ibrahim Enemona Abdulrasheed

Sorry i just noticed.
Thanks for the correction 😊

Collapse
 
manchicken profile image
Mike Stemle

It is important to remember that these queries may differ across databases.

Collapse
 
rashtech profile image
Ibrahim Enemona Abdulrasheed

I'll update that.
Thank you so much