Practical Examples of Using SQL Distinct, WHERE Clause and AND, OR, and NOT operators

Have you ever wanted to view a list of unique values from a particular column in your database table? That's where the "SELECT DISTINCT" statement in SQL comes in handy!

Let's say you have a table called "employees" with columns "id", "first_name", "last_name", and "department". You want to see a list of unique departments in your table. Here's how you would write your SQL query:

SELECT DISTINCT department
FROM employees;

In this example, we are selecting the "department" column from the "employees" table, and using the "DISTINCT" keyword to ensure that we only get a list of unique department names.

And what would the output of this query look like? Here's an example:

As you can see, the output table only includes the unique department names from the "employees" table. This is particularly useful when you have a large table with many repeated values in a certain column, and you want to quickly see a list of unique values without any duplicates.

SQL Where

Let's say you have a table of customer orders called "Orders" with columns for OrderID, CustomerID, OrderDate, and TotalAmount. You want to find all orders that were made on or after a certain date. To do this, you would use the WHERE clause.

Orders Table

Here's an example SQL statement using the WHERE clause to filter the Orders table:

SELECT * FROM Orders
WHERE OrderDate >= '2022-01-04';

Output

As you can see, the output result table includes all the rows where the OrderDate is equal to or greater than January 4th, 2022. This is just one example of how you can use the WHERE clause in SQL to filter and analyze data in a more targeted way.

SQL AND, OR and NOT Operators

SQL operators can be a bit confusing at first, but once you get the hang of them, they can be incredibly powerful tools for managing data in your databases. Let's take a look at the AND, OR, and NOT operators, and see how they work with an example table.

Employees Table

Now, let's use the AND, OR, and NOT operators to query this table.

AND

The AND operator allows us to select rows that meet multiple conditions. For example, we can use the AND operator to find employees who are in the Sales department and are over 25 years old:

SELECT * FROM employees
WHERE department = 'Sales' AND age > 25;

This will give us the following output:

OR

The OR operator allows us to select rows that meet one or more conditions. For example, we can use the OR operator to find employees who are either in the Engineering department or are over 40 years old:

SELECT * FROM employees
WHERE department = 'Engineering' OR age > 40;

This will give us the following output:

NOT

The NOT operator allows us to select rows that do not meet a certain condition. For example, we can use the NOT operator to find employees who are not in the Sales department:

SELECT * FROM employees
WHERE department <> 'Sales';

This will give us the following output: