Apply filters to SQL queries

Project description

The purpose of this project is to practice querying an organization’s database for information about failed login attempts, and employee data.

Retrieve after-hours failed login attempts

Investigating an after-hours security incident, we can construct a SQL query to look for failed login attempts after 18:00 (end of our business day). We select all columns from the table “log_in_attempts” where the log-in time is greater than 1800 and success is false. This gives us all log-in attempts that match our search criteria of A) Failed log-in attempt B) Time is after hours.

Retrieve login attempts on specific dates

Here is where we can retrieve login attempts on specific dates by filtering for where login_date matches the specific dates that we are looking for. Using the OR operator lets us search for more than one date.

Retrieve login attempts outside of Mexico

We can retrieve all login attempts outside of Mexico by using the NOT operator, LIKE operator, and the % wildcard character since some data had been entered as “MEXICO” and some had been entered as “MEX”

Retrieve employees in Marketing

Next, we want to retrieve all employees in marketing where they are in an office that starts with EAST. So we can get all columns from the employee table that meet the following criteria: The department is “Marketing” and the office starts with “East”

We construct the following query to return these results:

Retrieve employees in Finance or Sales

To retrieve all employees in finance or sales, we can use the OR operator within the “WHERE” part of our query:

Retrieve all employees not in IT

And, if we want to retrieve all employees who are not in the “Information Technology” department, we can use the != (NOT)  operator to filter our database query:

Summary

Throughout this exercise, we successfully filtered for failed login attempts after a certain time, login attempts on certain days, and employee data (with conditional statements).