SQL Basics 2: SELECT + FROM Statements
SQL
The SELECT and FROM statements are the building blocks of SQL queries. Mastering their usage is crucial for data retrieval and analysis. So, start exploring SQL queries with SELECT and FROM and take your first step towards becoming a proficient data explorer.
Hello everyone, in this blog post we are going over the SELECT and FROM statements.
On my last post, we created 2 tables:
1. EmployeeDemographics
2. EmployeeSalary
and we inserted data into these tables.
Today, we are walking through SELECT + FROM statements using both tables.
So… If you haven’t done the first exercise and want to check it out to follow along with today’s exercise, just click on the link below:
SQL Basics 1: How to Create Table and Add Data in SSMS
Ok… Let’s start saying that the SELECT statement is a fundamental and powerful tool in SQL (Structured Query Language) that allows users to retrieve data from a database.
Whether you are a beginner or an experienced SQL user, understanding various aspects of the SELECT statement can significantly enhance your data retrieval skills.
In SQL, the "FROM" clause is used to specify the table or tables from which you want to retrieve data. It is an essential part of most SQL queries as it tells the database where to look for the data you need.
The general syntax of the SQL SELECT statement with the FROM clause is as follows:
Here's what each part of the statement means:
SELECT: This keyword is used to indicate what you want to retrieve data from the database.
column1, column2, ...: These are the names of the columns you want to retrieve data from. You can specify multiple column names separated by commas, or you can use the asterisk (*) to select all columns in the table.
FROM: This keyword is used to indicate that you are specifying the source table from which you want to retrieve data.
table_name: This is the name of the table where your data resides. You need to replace "table_name" with the actual name of the table you want to query.
In this blog post, we'll explore the usage of SELECT + FROM statements along with some essential clauses and functions like *, TOP, DISTINCT, COUNT, AS, MAX, MIN, and AVG to fetch data efficiently and effectively.
1. The Asterisk (*) Wildcard:
The (*), also known as wildcard, is a versatile feature of the SELECT statement that allows you to retrieve all columns from a table.
For example, the query "SELECT * FROM EmployeeDemographics" -> EXECUTE
The outcome will return all columns and rolls in the "EmployeesDemographics" table.
If we only wanted to show the first name, the query would be “SELECT FirstName FROM EmployeeDemographics” -> EXECUTE
If we want first name and last name, we will use a comma like "SELECT FirstName, LastName FROM EmployeeDemographics" -> EXECUTE
NOTE: It's recommended to use this wildcard wisely, especially when dealing with large tables, as it might retrieve unnecessary data and affect query performance.
That is why we have the following functions:
2. TOP:
The TOP clause is used to limit the number of rows returned in the result set.
It is particularly useful when you only want to see a specific number of rows, like the top 5 highest sales or the top 10 performing employees.
For example, we can "SELECT TOP 5 * FROM EmployeeDemographics” -> EXECUTE
3. DISTINCT:
The DISTINCT keyword is employed to remove duplicate rows from the result set.
This ensures that the query output only contains unique rows based on the specified columns.
For example, if we query “SELECT DISTINCT (EmployeeID) FROM EmployeeDemographics” -> EXECUTE
As outcome, all rows should return as they all are unique.
If we select gender instead, “SELECT DISTINCT (Gender) FROM EmployeeDemographics” -> EXECUTE
The outcome is only two unique options, male and female will come up.
4. COUNT:
The COUNT function is a widely used aggregate function in SQL (Structured Query Language) that allows you to count the number of rows or non-null values in a specified column of a table.
It provides valuable insights into the size of a dataset or the occurrences of certain values within a column. The COUNT function can be used in combination with other clauses like GROUP BY to perform more complex analyses.
NOTE: We will go over more complex combination in different posts. Let’s keep it simple for now.
If we want to count the last name in our table, “SELECT COUNT (LastName) FROM EmployeeDemographics” -> EXECUTE
It returned 9 because we have 9 last names in the list, however, if some last name was left out or null, it would return 8 or 7 depending on how many were actually inserted. If the entire row was null, it would be returned as zero.
If you look again, there is (No column name) for this column. The reason is that this information was based off the last name. If we want to name the column, we can use the function AS.
5. AS (Alias):
The AS keyword is used to assign an alias or a temporary name to a column or a table. Aliases can make the output more readable and are handy when working with calculated fields.
For this particular example, let’s say we want to name to new column as CountLastName, so the query would be “SELECT COUNT (LastName) as CountLastName FROM EmployeeDemographics” -> EXECUTE
6. MAX and MIN:
MAX and MIN are aggregate functions used to find the highest and lowest values in a column, respectively. They are frequently utilized to identify extremes in data.
Looking at our EmployeeSalary table, let’s find the highest salary by using the query, “SELECT MAX (Salary) FROM EmployeeSalary” -> EXECUTE
If we want to know the lowest salary, “SELECT MIN (Salary) FROM EmployeeSalary” -> EXECUTE
7. AVG:
The AVG function calculates the average of values in a specified column. It is beneficial for understanding the average performance, scores, or ratings.
To find the average salary among our list, “SELECT AVG (Salary) FROM EmployeeSalary” -> EXECUTE
As we just learned, the SELECT statement is the cornerstone of SQL data retrieval, and mastering its various components like *, TOP, DISTINCT, COUNT, AS, MAX, MIN, and AVG will significantly improve your ability to fetch and analyse data from databases.
Understanding when and how to use these features appropriately will empower you to write efficient and concise SQL queries to meet your specific data requirements. So go ahead and practice these concepts in your own SQL queries to become a proficient data explorer!
If you like this content and want to learn more, you might also read:
Essential Technical Skills for Data Analysts
SQL Basics 1: How to Create Table and Add Data in SSMS
SQL Basics 4: GROUP BY and ORDER BY Statements
SQL Intermediate 1: INNER and OUTER Joins
SQL Intermediate 2: UNION Operator:
SQL Intermediate 3: CASE Statement
SQL Intermediate 4: HAVING Clause
SQL Intermediate 5: UPDATE and DELETE Data
SQL Intermediate 6: AS - Aliasing
SQL Intermediate 7: PARTITION BY Clause
SQL Advanced 1: CTEs - Common Table Expressions
SQL Advanced 3: Stored Procedures
SQL Advanced 4: SUBQUERIES - In Select, From and Where
SQL Advanced 5: String Functions - TRIM, LTRIM, RTRIM, REPLACE, SUBSTRING, UPPER, and LOWER