Applying SQL filters to queries

In this article I'll cover applying different filters to SQL queries in order to retreive different information from database tables.

The log_in_attempts table and the employees table have the following columns:

log_in_attempts columns:

• event_id - Identification number assigned to each login event

• username - Username of the employee

• login_date - Date the login attempt was recorded

• login_time - Time the login attempt occured

• country - Country where the login attempt occured

• ip_address - IP address of the employees' machine

• success - The success of the login attempt; FALSE inticates a failed attempt

employees columns:

• employee_id - Contains the ID of the employee

• device_id - Contains the ID of the employee's device

• username - Contains the employee's username

• department - Contains the department in which the employee currently works at

• office - The physical location of the office in which the employee currently works at

We're going to retrieve different kinds of information:

• Retrieve failed login attempts

• Retrieve login attempts on specific dates

• Retrieve login attempts from Mexico

• Retrieve employees in Marketing

• Retrieve employees in Finance or Sales

• Retrieve all employees not in IT

Let's start from the beginning:

As we can see there are multiple different columns, each containing different kinds of data.
First, we want to Retrieve failed login attempts, we can do so by typing the following command: SELECT * FROM log_in_attempts WHERE success = FALSE;

Now we can see all of the failed login attempts (see success is 0, which in SQL is FALSE), we used the WHERE clause and the = operator to do so.

Now let's Retrieve login attempts on specific dates, we can do this by typing the following command: SELECT * FROM log_in_attempts WHERE login_date BETWEEN '2022-05-09' AND '2022-05-11';

The correct dates are shown according to our command, we used the BETWEEN and AND operators to specify a specific range of dates.

The next step is to Retrieve login attempts from Mexico, which we can easily do using the following command: SELECT * FROM log_in_attempts WHERE country LIKE 'MEX%';

Using the % symbol (the wildcard character) and the LIKE operator we can specify our country of choice, using the % symbol is essential in order to include all of the rows containing mexico, if we were to type country = 'MEXICO' instead, we wouldn't get all of our required entries.

Next up is to Retrieve employees in Marketing, should be easy enough right?
Let's have a look at the employees table:

Using the command: SELECT * FROM employees WHERE department = 'Marketing';

Again, as we can see using the WHERE clause and = operator we can see the employees that work in marketing.

Now we can Retrieve employees in Finance or Sales:
In our terminal we'll type SELECT * FROM employees WHERE department = 'Finance' OR department = 'Sales';

This is a great example of using the OR operator, so now we can see employees that work either in Sales or Finance!

Lastly, let's Retrieve all employees not in IT!
In order to exclude all of the employees working in IT, we'll type the following command: SELECT * FROM employees WHERE NOT department = 'Information Technology';

As you can see we used the NOT operator which is great for excluding any irrelevant entries you'd might not want to see, which in our case are employees working in 'Information Technology'.

This was a lot of fun and I learned a lot about SQL doing these activities, they were a great way to practice SQL database filtering!

The example information used in this article is taken from a Google Cybersecurity Professional Certificate activity