Mastering SELECT Statements and Basic Filtering - Quick Office Pointe
Quick Office Pointe Logo
Quick Office Pointe Logo
Quick Office Pointe Logo
Database Decoded

Mastering SELECT Statements and Basic Filtering

by isaac Muteru Feb 10, 2025
Mastering SELECT Statements and Basic Filtering

In continuation of last week’s foundational guide to SQL Server Management Studio (SSMS), this week we delve deeper into querying data in SQL Server. SQL queries are the backbone of database interactions, allowing us to retrieve, filter, sort, and analyze data. Let’s explore the essential query techniques you need to master.

Topics Covered:

  1. Retrieving Data with SELECT
  2. Filtering Results with WHERE
  3. Sorting Data with ORDER BY
  4. Using Aggregate Functions

1. Retrieving Data with SELECT

The SELECT statement is fundamental in SQL and allows you to retrieve data from one or more tables in a database.

Syntax:

SELECT * FROM TableName;

SELECT*FROM Employees;

This retrieves all columns from the specified table. For example:

SELECT * FROM Employees;

Output:


Selecting Specific Columns:

To retrieve specific columns, specify them after the SELECT keyword:

SELECT FirstName, Salary FROM Employees;

Output:



2. Filtering Results with WHERE

The WHERE clause is used to filter rows based on specific conditions.

Filter by Department:

Retrieve employees in the IT department:

SELECT * FROM Employees WHERE Department = 'IT';

Output:


Filter by Salary:

Find employees earning more than 55,000:

SELECT * FROM Employees WHERE Salary > 55000;

Combining Filters:

Use AND or OR to combine conditions:

SELECT * FROM Employees WHERE Department = 'HR' AND Salary < 55000;


3. Sorting Data with ORDER BY

The ORDER BY clause allows you to sort results in ascending or descending order.

Sorting by Salary in Descending Order:

SELECT * FROM Employees ORDER BY Salary DESC;

4. Aggregate Functions

Aggregate functions summarize data, making them invaluable for analysis.

Counting Rows:

Find the total number of employees:

SELECT COUNT(*) AS TotalEmployees FROM Employees;

Output:

Calculating the Average Salary:

Determine the average salary of all employees:

SELECT AVG(Salary) AS AvgSalary FROM Employees;

Output:

Summing Salaries:

Calculate the total salary expenditure:

SELECT SUM(Salary) AS TotalSalaries FROM Employees;

Output:

TotalSalaries
165000.00


Practice Tasks

  1. Filter Employees: Write a query to find all employees in the HR department earning less than 55,000.

    SELECT * FROM Employees WHERE Department = 'HR' AND Salary < 55000;

  2. Total Salary Expenditure: Calculate the total salary expenditure for the IT department.

    SELECT SUM(Salary) AS ITSalaryExpenditure FROM Employees WHERE Department = 'IT';

Conclusion

This week’s focus was on mastering the SELECT statement, filtering results with WHERE, sorting data using ORDER BY, and applying aggregate functions like COUNT, SUM, and AVG. These skills form the foundation for querying and analyzing data in SQL Server.!

13 views