Lesson 3: Working with SQL Queries – SELECT, WHERE, and ORDER BY

Estimated Reading Time: 10-12 minutes

1. Introduction

In SQL, the most fundamental operation is retrieving data from a database. We use the SELECT statement to do this. It allows us to query a database and return specific columns, rows, or entire tables.

In this lesson, we will cover:

• SELECT: The basic command to retrieve data.
• WHERE: Filtering data with conditions.
• ORDER BY: Sorting data in ascending or descending order.

By the end of this lesson, you’ll be able to retrieve, filter, and sort data in a database with ease.

2. SELECT Statement

The SELECT statement is used to query the database and retrieve data. You can select one or more columns from a table.

Syntax:

SELECT column1, column2, ... FROM table_name;

If you want to retrieve all columns from a table, you can use the asterisk *.

Example:

SELECT * FROM Employees;

This will retrieve all data (all columns and rows) from the Employees table.

Output Example:

EmployeeID	Name	Department	Salary
1	John Doe	HR	50000
2	Jane Smith	IT	60000

3. WHERE Clause

The WHERE clause is used to filter the results and specify conditions. You can retrieve data based on conditions such as equal to, greater than, less than, and more.

Syntax:

SELECT column1, column2, ... FROM table_name WHERE condition;

Example:

SELECT * FROM Employees WHERE Department = 'HR';

This will retrieve only the rows where the Department is ‘HR’.

Output Example:

EmployeeID	Name	Department	Salary
1	John Doe	HR	50000

4. ORDER BY Clause

The ORDER BY clause is used to sort the result set. You can order the results by one or more columns, and specify the order as ascending (ASC) or descending (DESC).

Syntax:

SELECT column1, column2, ... FROM table_name ORDER BY column_name [ASC|DESC];

Example:

SELECT * FROM Employees ORDER BY Salary DESC;

This will retrieve all employees, sorted by their salary in descending order.

Output Example:

EmployeeID	Name	Department	Salary
2	Jane Smith	IT	60000
1	John Doe	HR	50000

5. Combining SELECT, WHERE, and ORDER BY

You can combine SELECT, WHERE, and ORDER BY to retrieve filtered and sorted data. Here’s an example:

Example:

SELECT * FROM Employees WHERE Department = 'HR' ORDER BY Salary DESC;

This will retrieve all employees in the ‘HR’ department and sort them by their salary in descending order.

Output Example:

EmployeeID	Name	Department	Salary
1	John Doe	HR	50000

6. Practice Exercise

Try these exercises to practice the concepts you’ve learned:

1. Write a SQL query to retrieve all columns from the Employees table.
2. Write a SQL query to get all employees whose salary is greater than 50000.
3. Write a SQL query to list employees from the ‘HR’ department, ordered by their names in ascending order.
4. Write a SQL query to get the details of employees from the ‘IT’ department with a salary between 40000 and 70000.

7. Conclusion

In this lesson, we learned how to:

• Use the SELECT statement to retrieve data.
• Filter data with the WHERE clause.
• Sort data with the ORDER BY clause.

Practice these queries in your database and explore how they interact to get the data you need.