SQL SELECT, WHERE and ORDER BY: A Practical Guide for Beginners

SQL SELECT, WHERE and ORDER BY: A Practical Guide for Beginners

Introduction

SQL is one of the most important skills for anyone who wants to become a Data Analyst, SQL Developer, or Database Developer. In this tutorial, we will learn how to retrieve, filter, and sort data using SELECT, WHERE, AND, OR, and ORDER BY clauses. All examples use PostgreSQL syntax.

1. What Is the SELECT Statement?

The SELECT statement retrieves data from a database table. To retrieve all columns from the employees table, use:

SELECT *

FROM employees;

The asterisk (*) represents all columns. To retrieve only employee names and salaries, use:

SELECT employee_name, salary

FROM employees;

This query returns only the employee_name and salary columns.

2. What Is the WHERE Clause?

The WHERE clause filters records based on a condition. To find employees whose salary is greater than 40000, use:

SELECT *

FROM employees

WHERE salary > 40000;

To find employees who work in the IT department, use:

SELECT *

FROM employees

WHERE department = 'IT';

To find employees earning less than 40000, use:

SELECT *

FROM employees

WHERE salary < 40000;

To find employees who do not work in the IT department, use:

SELECT *

FROM employees

WHERE department <> 'IT';

The WHERE clause helps retrieve only the records that match your requirements.

3. Using AND in SQL

The AND operator is used when all specified conditions must be true. To find IT employees earning more than 50000, use:

SELECT *

FROM employees

WHERE department = 'IT'

AND salary > 50000;

Both conditions must be satisfied for a record to appear in the result.

4. Using OR in SQL

The OR operator is used when at least one specified condition must be true. To find employees who work in either the IT or Analytics department, use:

SELECT *

FROM employees

WHERE department = 'IT'

OR department = 'Analytics';

Difference between AND and OR: AND requires all specified conditions to be true. OR requires at least one condition to be true.

5. What Is the ORDER BY Clause?

The ORDER BY clause sorts query results.

Ascending Order

To sort salaries from lowest to highest, use:

SELECT *

FROM employees

ORDER BY salary ASC;

Descending Order

To sort salaries from highest to lowest, use:

SELECT *

FROM employees

ORDER BY salary DESC;

To sort employee names alphabetically, use:

SELECT *

FROM employees

ORDER BY employee_name ASC;

Note: ASC is the default sorting order when you do not specify ASC or DESC.

6. Using WHERE and ORDER BY Together

You can combine filtering and sorting in one query. To find employees earning more than 40000 and display the highest salaries first, use:

SELECT employee_name, department, salary

FROM employees

WHERE salary > 40000

ORDER BY salary DESC;

The WHERE clause filters employees whose salary is greater than 40000. The ORDER BY clause sorts the results from highest salary to lowest salary.

7. Combining Conditions with Parentheses

Parentheses help group conditions when combining AND and OR. To find IT employees earning at least 50000, use:

SELECT *

FROM employees

WHERE department = 'IT'

AND (salary > 50000 OR salary = 50000);

This query returns IT employees whose salary is greater than or equal to 50000.

8. Practical Business Example

Imagine you work as a Data Analyst. Your manager asks you to identify employees earning more than 40000 and sort them from the highest salary to the lowest. You can use:

SELECT employee_name, department, salary

FROM employees

WHERE salary > 40000

ORDER BY salary DESC;

This query helps identify employees who meet the salary condition. Similar SQL queries are used in employee analysis, sales reports, financial reporting, and business dashboards.

9. Common SQL Mistakes Beginners Should Avoid

Mistake 1: Forgetting quotation marks around text values.

Incorrect:

SELECT *

FROM employees

WHERE department = IT;

Correct:

SELECT *

FROM employees

WHERE department = 'IT';

Mistake 2: Placing ORDER BY before WHERE. The WHERE clause must appear before ORDER BY.

Correct query:

SELECT *

FROM employees

WHERE salary > 40000

ORDER BY salary DESC;

Mistake 3: Confusing ASC and DESC. ASC sorts numbers from lowest to highest, while DESC sorts numbers from highest to lowest.

Mistake 4: Using the wrong comparison operator. Use = for equality, > for greater than, < for less than, and <> for not equal to.

10. SQL Practice Exercises

Try solving these exercises using the employees table from the previous tutorial.

Exercise 1: Retrieve all records from the employees table.

Exercise 2: Display only employee names and salaries.

Exercise 3: Find employees whose salary is greater than 45000.

Exercise 4: Find employees who work in the Analytics department.

Exercise 5: Find IT employees earning more than 50000.

Exercise 6: Find employees who work in either the IT or Finance department.

Exercise 7: Display all employees sorted by salary in descending order.

Exercise 8: Display employee names and salaries for employees earning more than 40000, sorted by salary in ascending order.

Try writing each