DATABASES AND SQL

SQL filtering versus Linux

filtering
In this reading, you'll explore the differences between the two tools as they relate to
filtering. You'll also learn that one way to access SQL is through the Linux command
line.

Accessing SQL

There are many interfaces for accessing SQL and many different versions of SQL. One
way to access SQL is through the Linux command line.

To access SQL from Linux, you need to type in a command for the version of SQL that
you want to use. For example, if you want to access SQLite, you can enter the
command sqlite3 in the command line.

After this, any commands typed in the command line will be directed to SQL instead of
Linux commands.

Differences between Linux and SQL filtering

Although both Linux and SQL allow you to filter through data, there are some
differences that affect which one you should choose.

Purpose
Linux filters data in the context of files and directories on a computer system. It’s used
for tasks like searching for specific files, manipulating file permissions, or managing
processes.

SQL is used to filter data within a database management system. It’s used for querying
and manipulating data stored in tables and retrieving specific information based on
defined criteria.

Syntax

Linux uses various commands and command-line options specific to each filtering tool.
Syntax varies depending on the tool and purpose. Some examples of Linux commands
are find, sed, cut, e grep

SQL uses the Structured Query Language (SQL), a standardized language with specific
keywords and clauses for filtering data across different SQL databases. Some
examples of SQL keywords and clauses are WHERE, SELECT, JOIN

Structure

SQL offers a lot more structure than Linux, which is more free-form and not as tidy.

For example, if you wanted to access a log of employee log-in attempts, SQL would
have each record separated into columns. Linux would print the data as a line of text
without this organization. As a result, selecting a specific column to analyze would be
easier and more efficient in SQL.

In terms of structure, SQL provides results that are more easily readable and that can
be adjusted more quickly than when using Linux.

Joining tables

Some security-related decisions require information from different tables. SQL allows
the analyst to join multiple tables together when returning data. Linux doesn’t have that
same functionality; it doesn’t allow data to be connected to other information on your
computer. This is more restrictive for an analyst going through security logs.

Best uses

As a security analyst, it’s important to understand when you can use which tool.
Although SQL has a more organized structure and allows you to join tables, this doesn’t
mean that there aren’t situations that would require you to filter data in Linux.

A lot of data used in cybersecurity will be stored in a database format that works with
SQL. However, other logs might be in a format that is not compatible with SQL. For
instance, if the data is stored in a text file, you cannot search through it with SQL. In
those cases, it is useful to know how to filter in Linux.

Key takeaways

Linux filtering focuses on managing files and directories on a system, while SQL filtering
focuses on structured data manipulation within databases. To work with SQL, you can
access it from multiple different interfaces, such as the Linux command line. Both SQL
and Linux allow you to filter for specific data, but SQL offers the advantages of
structuring the data and allowing you to join data from multiple tables

Query a database
In this reading, you’ll review those basic SQL queries and learn a new keyword that will
help you organize your output. You'll also learn about the Chinook database, which this
course uses for queries in readings and quizzes.

Why We Use a Ready-Made Database: Creating your own database from scratch is a
lot like building a car instead of just learning how to drive one. It is a difficult process
because you have to manually set up all the rules for how information is stored, how to
keep it from getting lost, and how to make sure the computer can find specific data
quickly. Instead of spending weeks building that complicated "engine," we use the
Chinook database so you can get straight to the important part: learning how to ask
questions and get answers from data.

Basic SQL query

There are two essential keywords in any SQL query: SELECT and FROM. You will use
these keywords every time you want to query a SQL database. Using them together
helps SQL identify what data you need from a database and the table you are returning
it from.

The video demonstrated this SQL query:

SELECT employee_id, device_id

FROM employees;

In readings and quizzes, this course uses a sample database called the Chinook
database to run queries. The Chinook database includes data that might be created at
a digital media company. A security analyst employed by this company might need to
query this data. For example, the database contains eleven tables, including an
employees table, a customers table, and an invoices table. These tables include
data such as names and addresses.

As an example, you can run this query to return data from the customers table of the
Chinook database:

SELECT customerid, city, country

FROM customers;
Reset

SELECT

The SELECT keyword indicates which columns to return. For example, you can return
the customerid column from the Chinook database with

SELECT customerid

You can also select multiple columns by separating them with a comma. For example, if
you want to return both the customerid and city columns, you should write SELECT
customerid, city.

If you want to return all columns in a table, you can follow the SELECT keyword with an
asterisk (*). The first line in the query will be SELECT *.

Note: Although the tables you're querying in this course are relatively small, using
SELECT * may not be advisable when working with large databases and tables; in
those cases, the final output may be difficult to understand and might be slow to run.

FROM

The SELECT keyword always comes with the FROM keyword. FROM indicates which table
to query. To use the FROM keyword, you should write it after the SELECT keyword, often
on a new line, and follow it with the name of the table you’re querying. If you want to
return all columns from the customers table, you can write:

SELECT *

FROM customers;

When you want to end the query here, you put a semicolon (;) at the end to tell SQL
that this is the entire query.
Note: Line breaks are not necessary in SQL queries, but are often used to make the
query easier to understand. If you prefer, you can also write the previous query on one
line as

SELECT * FROM customers;

ORDER BY

Database tables are often very complicated, and this is where other SQL keywords
come in handy. ORDER BY is an important keyword for organizing the data you extract
from a table.

ORDER BY sequences the records returned by a query based on a specified column or

columns. This can be in either ascending or descending order.

Sorting in ascending order

To use the ORDER BY keyword, write it at the end of the query and specify a column to
base the sort on. In this example, SQL will return the customerid, city, and country
columns from the customers table, and the records will be sequenced by the city
column:

SELECT customerid, city, country

FROM customers

ORDER BY city;

Reset

The ORDER BY keyword sorts the records based on the column specified after this
keyword. By default, as shown in this example, the sequence will be in ascending order.
This means
 ●​ if you choose a column containing numeric data, it sorts the output from the
smallest to largest. For example, if sorting on customerid, the ID numbers are
sorted from smallest to largest.
●​ if the column contains alphabetic characters, such as in the example with the
city column, it orders the records from the beginning of the alphabet to the end.

Sorting in descending order

You can also use the ORDER BY with the DESC keyword to sort in descending order. The
DESC keyword is short for "descending" and tells SQL to sort numbers from largest to
smallest, or alphabetically from Z to A. This can be done by following ORDER BY with
the DESC keyword. For example, you can run this query to examine how the results
differ when DESC is applied:

SELECT customerid, city, country

FROM customers

ORDER BY city DESC;

Reset

Now, cities at the end of the alphabet are listed first.

Sorting based on multiple columns

You can also choose multiple columns to order by. For example, you might first choose
the country and then the city column. SQL then sorts the output by country, and for
rows with the same country, it sorts them based on city. You can run this to explore
how SQL displays this:
SELECT customerid, city, country

FROM customers

ORDER BY country, city;

Reset

Key takeaways

SELECT and FROM are important keywords in SQL queries. You use SELECT to indicate
which columns to return and FROM to indicate which table to query. You can also include
ORDER BY in your query to organize the output. These foundational SQL skills will
support you as you move into more advanced queries.

Resources for completing SQL

labs
This course features hands-on lab activities where you’ll have the opportunity to
practice using SQL queries in the terminal. You’ll use a platform called Qwiklabs to
complete these labs. In this reading, you’ll learn how to use Qwiklabs.

This reading first provides a section on how to use Qwiklabs, which includes details on
how to launch a lab, how to interact within the Qwiklabs environment, and how to end a
lab. This is followed by another section on helpful navigation tips and keyboard
shortcuts; these may be useful when working in the terminal.

Note: You will not launch Qwiklabs directly from this reading and instead will do this
through lab activities and exemplars that you encounter throughout the course.
How to use Qwiklabs

Launching Qwiklabs

When you select a lab, you start from a Coursera page. You will need to click Launch
App on that page. After you click Launch App, a new tab will open with a Qwiklabs page
that contains instructions for that particular lab.

Start Lab button

On the Qwiklabs page, you must click Start Lab to open a temporary terminal. The
instructions for the lab will move to the right side of the screen.

Read the instructions and complete all the tasks in the lab by entering commands in the
terminal.

Note: It may take a moment for the terminal to start.

Lab control dialog box

After you click Start Lab, the lab control dialog box opens. It contains the End
Lab button, the timer, and the Open Linux Console button.

You can hide or unhide the dialog box by clicking the following icon in the red box:
The timer

The timer starts when the terminal has loaded. The timer keeps track of the amount of
time you have left to complete a lab. The timer counts down until it reaches 00:00:00.
When it does, your temporary terminal and resources are deleted.

You will have ample time to complete the labs. But, stay focused on completing the
tasks to ensure you use your time well.

Open Linux Console button

When you click the button to Open Linux Console, the terminal opens in a new browser
window:
Use this feature if you want a full-screen view of the terminal. You can close this window
at any time. Closing the window does not end your lab, and you can continue working in
the terminal in the original tab.

Check progress

You can check your progress by clicking Check my progress at the end of each task.

If you haven’t yet completed a task, you’ll receive hints on what you must do to
complete it.

You can click Check my progress whenever you want to check the completion status of
a task or receive a hint.

Using copy/paste commands

The first time you try to use copy or paste keyboard shortcuts (such as CTRL + C),
you’ll receive a pop-up requesting permission to use your device’s clipboard:
“[Link] wants to see text and images copied to the clipboard.”
Please click Allow if you would like to be able to use these shortcuts in the Qwiklabs
platform. If you choose not to allow Qwiklabs access to your clipboard, you cannot use
keyboard shortcuts but you can still complete the lab.

Code block

Certain steps may include a code block. Click the copy button to copy the code provided
and then paste it into the terminal.
To paste code or other text content that you have copied from the instructions into the
terminal, activate the terminal by clicking anywhere inside it. The terminal is active when
the cursor in the terminal changes from a static empty outline to a flashing solid block.

Once the terminal is active, use the keyboard shortcut CTRL + V (hold down the CTRL
key and press the V key) to insert the copied text into the terminal at the location of the
flashing cursor.

Scrolling

In certain situations, you may want to scroll within the terminal window. To do so, use
the scroll wheel on your mouse or the touchpad of your computer.

End Lab button

Finally, click End Lab when you’ve completed the tasks in the lab.
Note: Don't click End Lab until you're finished; you'll lose access to the work you've
done throughout the lab.

Tracking progress on Coursera

If you complete a lab but your progress hasn’t been tracked on Coursera, you may need
to refresh the page for your progress to be registered. Once you complete the lab and
refresh the page, the green check mark should appear.

Helpful navigation tips and keyboard shortcuts

The following contains a list of navigation tips and keyboard shortcuts you may find
useful when completing your SQL labs. Your cursor must be in the terminal window to
use these navigation tips and keyboard shortcuts.

●​ CTRL + C: Terminates a command that is currently running; from the instructions

portion of Qwiklabs, you can use CTRL + C to copy, but within the terminal, it will
only terminate a command and if one isn't running, it will exit out of the MariaDB
shell; if you unintentionally exit, you can reconnect by running the sudo mysql
organization command
●​ CTRL + V: Pastes text
●​ CTRL + L: Clears the terminal screen; within MariaDB, you must use CTRL + L
and cannot use clear
●​ \c + Enter: Clears the current input
●​ CTRL + A: Sets your cursor at the beginning of a command
●​ CTRL + E: Sets your cursor at the end of a command
●​ Left arrow key: Moves left within a command
●​ Right arrow key: Moves right within a command
●​ Up arrow key: Provides the last command you entered into the command line;
can be entered multiple times to go through multiple commands from the
command history
 ●​ Down arrow key: Provides the next command in the command history; must be
after using the up arrow key
●​ Tab key: Provides available suggestions for completing your text

Note: If you unintentionally exit the organization database in the MariaDB shell, you
can reconnect by running the sudo mysql organization command.

Key takeaways

Knowing how to navigate Qwiklabs will be useful as you complete the labs throughout
this course. These labs can help you practice what you’ve learned in an interactive
environment.

Activity: Perform a SQL query

Introduction

In this lab, you’ll learn how to retrieve information from a database using SQL. You’ll be
using the MariaDB shell to run your SQL queries.

Disclaimer: For optimal performance and compatibility, it is recommended to use either

Google Chrome or Mozilla Firefox browsers while accessing the labs.

What you’ll do

You have multiple tasks in this lab:

 ●​ Return information on employee devices
●​ Examine login attempts
●​ Sort the data returned from a query

Lab instructions

Start the lab

Before you start, you can review the Resources for completing SQL labs. Then from this
page, click Launch App. A Qwiklabs page will open and from that page, click Start Lab
to begin the activity!

You may attempt this lab a maximum of 5 times, and you will have 60 minutes to
complete this lab during each attempt.

End the lab

From within the lab, click End Lab to end your lab.

Additionally, sometimes you need to refresh your Coursera page in order for your
progress to be registered. If you refresh this page after you complete your lab, the green
check mark should appear.

Best practices for completing labs:

 ●​ Make sure your browser is up to date with the latest version.
●​ Make sure your internet connection is stable.
●​ After you complete the lab, leave the lab window open for at least 10 minutes in
order to allow the system to record your progress.
●​ If you run into issues connecting to the lab, try logging into Coursera in an
Incognito mode and completing the lab there.
●​ Review Lab tips and troubleshooting steps for more information.

Exemplar: Perform an SQL

query
Activity overview

Previously, you learned how to use basic SQL queries to retrieve information from a
database. You have also learned about using the ORDER BY keyword to sort data
returned in an ascending or a descending order.

In this lab activity, you’ll use SELECT and FROM in SQL to return the information you
need from a database. You’ll also use the ORDER BY keyword to sequence the
information returned by a query based on a specified column.

It's important to know how to query information from a database because this is a
common task you might encounter as a security analyst. You should know how to get
the information you need to improve security and keep data safe.

With that in mind, it's time to explore the scenario.

This exemplar is a walkthrough of the previous Qwiklab activity, including detailed

instructions and solutions. You may use this exemplar if you were unable to complete
the lab and/or you need extra guidance in competing lab tasks. You may also refer to
this exemplar to prepare for the graded quiz in this module.

This exemplar is a walkthrough of the previous Qwiklab activity, including detailed

instructions and solutions. You may use this exemplar if you were unable to complete
the lab and/or you need extra guidance in competing lab tasks. You may also refer to
this exemplar to prepare for the graded quiz in this module.

Note: The terms row and record are used interchangeably in this lab activity.

Scenario

In this scenario, you have to determine which employee devices must be updated. You
also need to investigate user login activity to explore if any unusual activity has
occurred.

The information you need is located in the machines and login_attempts tables in
the organization database.

Here’s how you’ll do this task: First, you’ll obtain information on the employee devices
that must be updated. Next, you’ll examine the login attempts for unusual activity.
Finally, you’ll use the ORDER BY keyword to sort the data returned by your SQL queries.

OK, let’s get ready to practice running your very first SQL queries!

Note: In this lab you’ll be working with the organization database and the tables it
contains.

The lab starts with the organization database in the MariaDB shell that is already open.
This means you can start with the tasks as soon as you click the Start Lab button.

If you unintentionally exit the organization database in the MariaDB shell, you can
reconnect by running the sudo mysql organization command.
Task 1. Retrieve employee device data

In this task, you need to obtain information on employee devices because your team
needs to update them. The information you need is in the machines table in the
organization database.

First, you need to retrieve all the information about the employee devices.

1.​ Run the following query to select all device information from the machines table:

SELECT *

FROM machines;

Note: Using the asterisk (*) returns all data from the specified table. Also, table names
in MySQL are case-sensitive.

The output returns all the contents of the machines table:

+--------------+------------------+----------------+---------------+-------------+

| device_id | operating_system | email_client | OS_patch_date | employee_id |

+--------------+------------------+----------------+---------------+-------------+

| a184b775c707 | OS 1 | Email Client 1 | 2021-09-01 | 1156 |

| a192b174c940 | OS 2 | Email Client 1 | 2021-06-01 | 1052 |

| a305b818c708 | OS 3 | Email Client 2 | 2021-06-01 | 1182 |

| a317b635c465 | OS 1 | Email Client 2 | 2021-03-01 | 1130 |

| a320b137c219 | OS 2 | Email Client 2 | 2021-03-01 | 1000 |

|... | | | | |

+--------------+------------------+----------------+---------------+-------------+

200 rows in set (0.356 sec)

Next, you want to focus on the email client running on various devices.

2. Run the following query to select only the device_id and email_client columns
from the machines table. Replace X with device_id and Y with email_client:

SELECT X, Y FROM machines;

The correct query to solve this step:

SELECT device_id, email_client

FROM machines;

The output should return only the selected columns of the machines table:

+--------------+----------------+

| device_id | email_client |

+--------------+----------------+

| a184b775c707 | Email Client 1 |

| a192b174c940 | Email Client 1 |

| a305b818c708 | Email Client 2 |

| a317b635c465 | Email Client 2 |

| a320b137c219 | Email Client 2 |

|... | |

+--------------+----------------+

200 rows in set (0.015 sec)

What email client is returned in the third row?

Answer: The email client returned in the third row is Email Client 2.

Now, you need information on the operating systems used on various devices and their
last patch date.

3. Complete the query to return only the device_id, operating_system, and

OS_patch_date columns from the machines table. Replace X, Y, and Z with the
columns that you need to return:

SELECT X, Y, Z FROM machines;

The correct query to solve this step:

SELECT device_id, operating_system, OS_patch_date

FROM machines;

What is the patch date of the first entry?

Answer: The patch date of the first entry is 2021-09-01.

Task 2. Investigate login activity

In this task, you need to analyze the information from the log_in_attempts table to
determine if any unusual activity has occurred.

First, you need to investigate the locations where login attempts were made to ensure
that they’re in expected areas (the United States, Canada, or Mexico).

1.​ Write a SQL query to select the event_id and country columns from the
log_in_attempts table.
The correct query to solve this step:

SELECT event_id, country

FROM log_in_attempts;

Were any login attempts made from Australia?

Answer: No. Login attempts were not made from Australia.

Next, you need to check if login attempts were made outside of the organization's
working hours.

2. Write a SQL query that selects the username, login_date, and login_time
columns from the log_in_attempts table.

The correct query to solve this step:

SELECT username, login_date, login_time

FROM log_in_attempts;

What username is returned in the fifth row?

Answer: The username returned in the fifth row is jrafael.

Now, you need to get a complete picture of all login attempts.

3. Write a SQL query that selects all columns from the log_in_attempts table, using
a single symbol after the SELECT keyword.

The correct query to solve this step:

FROM log_in_attempts;
Task 3. Order login attempts data

In this task, you need to use the ORDER BY keyword. You'll sequence the data that your
query returns according to the login date and time.

First, you need to sort the information by date.

1.​ Run the following query, which orders log_in_attempts data by login_date:

FROM log_in_attempts

ORDER BY login_date;

SELECT *

What are the username and login date of the first record returned?

Answer: The first record returned contains a username of ivelasco and a login date of
2022-05-08.

Now, you need to further organize the previous results by ordering them by login_time.

2. Modify the query from the previous step by adding the login time to the ORDER BY
clause. You must replace X with the appropriate column name:

ORDER BY login_date, X;

The correct query to solve this step:

ORDER BY login_date, login_time;

FROM log_in_attempts

SELECT *
What are the username and login date of the first record returned?

Answer: The first record returned contains a username of ivelasco and a login date of
2022-05-08.

Now, you need to further organize the previous results by ordering them by
login_time.

2. Modify the query from the previous step by adding the login time to the ORDER BY
clause. You must replace X with the appropriate column name:

SELECT *

FROM log_in_attempts

ORDER BY login_date, X;

The correct query to solve this step:

SELECT *

FROM log_in_attempts

ORDER BY login_date, login_time;

What are the username and login time of the first record returned by the above query?

Answer: The first record returned contains a username of bsand and a login time of
00:19:11.

Conclusion

Great work!
You have completed this activity, and you now have practical experience in running
basic SQL queries to

●​ select specific columns from a table,

●​ select all columns from a table by using an asterisk (*), and
●​ sort query results using the ORDER BY keyword.

These basic queries form the foundation for running more advanced queries and
applying filters later.

The WHERE clause and basic

operators
Previously, you focused on how to refine your SQL queries by using the WHERE clause
to filter results. In this reading, you’ll further explore how to use the WHERE clause, the
LIKE operator and the percentage sign (%) wildcard. You’ll also be introduced to the
underscore (_), another wildcard that can help you filter queries.

How filtering helps

As a security analyst, you'll often be responsible for working with very large and
complicated security logs. To find the information you need, you'll often need to use
SQL to filter the logs.

In a cybersecurity context, you might use filters to find the login attempts of a specific
user or all login attempts made at the time of a security issue. As another example, you
might filter to find the devices that are running a specific version of an application.
WHERE

To create a filter in SQL, you need to use the keyword WHERE. WHERE indicates the
condition for a filter.

If you needed to email employees with a title of IT Staff, you might use a query like the
one in the following example. You can run this example to examine what it returns:

SELECT firstname, lastname, title, email

FROM employees

WHERE title = 'IT Staff';

Reset

Rather than returning all records in the employees table, this WHERE clause instructs
SQL to return only those that contain 'IT Staff' in the title column. It uses the
equals sign (=) operator to set this condition.

Note: You should place the semicolon (;) where the query ends. When you add a filter
to a basic query, the semicolon is after the filter.

Filtering for patterns

You can also filter based on a pattern. For example, you can identify entries that start or
end with a certain character or characters. Filtering for a pattern requires incorporating
two more elements into your WHERE clause:

●​ a wildcard
●​ the LIKE operator

Wildcards
A wildcard is a special character that can be substituted with any other character. Two
of the most useful wildcards are the percentage sign (%) and the underscore (_):

●​ The percentage sign substitutes for any number of other characters.

●​ The underscore symbol only substitutes for one other character.

These wildcards can be placed after a string, before a string, or in both locations
depending on the pattern you’re filtering for.

The following table includes these wildcards applied to the string 'a' and examples of
what each pattern would return.

Pattern Results that could be returned

'a%' apple123, art, a

'a_' as, an, a7

'a__' ant, add, a1c

'%a' pizza, Z6ra, a

'_a' ma, 1a, Ha

 '%a%' Again, back, a

'_a_' Car, ban, ea7

LIKE

To apply wildcards to the filter, you need to use the LIKE operator instead of an equals
sign (=). LIKE is used with WHERE to search for a pattern in a column.

For instance, if you want to email employees with a title of either 'IT Staff' or 'IT
Manager', you can use LIKE operator combined with the % wildcard:

SELECT lastname, firstname, title, email

FROM employees

WHERE title LIKE 'IT%';

Reset

This query returns all records with values in the title column that start with the pattern
of 'IT'. This means both 'IT Staff' and 'IT Manager' are returned.

As another example, if you want to search through the invoices table to find all
customers located in states with an abbreviation of 'NY', 'NV', 'NS' or 'NT', you can
use the 'N_' pattern on the state column:

SELECT firstname,lastname, state, country

FROM customers

WHERE state LIKE 'N_';

Reset

This returns all the records with state abbreviations that follow this pattern.

Key takeaways

Filters are important when refining what your query returns. WHERE is an essential
keyword for adding a filter to your query. You can also filter for patterns by combining
the LIKE operator with the percentage sign (%) and the underscore (_) wildcards.

Activity: Filter a SQL query

Introduction

In this lab, you’ll apply basic filters to SQL queries to retrieve information from a
database. You’ll use SQL to get specific information about employees, their machines,
and the departments they’re in. You’ll be using the MariaDB shell to run SQL queries.

Disclaimer: For optimal performance and compatibility, it is recommended to use either

Google Chrome or Mozilla Firefox browsers while accessing the labs.

What you’ll do

You have multiple tasks in this lab:

●​ Return information on machines and their operating systems

 ●​ Filter for machines with a specific operating system
●​ Filter for employees in specific departments
●​ Filter for employees who use specific machines

Lab instructions

Start the lab

Before you start, you can review the Resources for completing SQL labs. Then from this
page, click Launch App. A Qwiklabs page will open and from that page, click Start Lab
to begin the activity!

You may attempt this lab a maximum of 5 times, and you will have 60 minutes to
complete this lab during each attempt.

End the lab

From within the lab, click End Lab to end your lab.

Additionally, sometimes you need to refresh your Coursera page in order for your
progress to be registered. If you refresh this page after you complete your lab, the green
check mark should appear.

Best practices for completing labs:

 ●​ Make sure your browser is up to date with the latest version.
●​ Make sure your internet connection is stable.
●​ After you complete the lab, leave the lab window open for at least 10 minutes in
order to allow the system to record your progress.
●​ If you run into issues connecting to the lab, try logging into Coursera in an
Incognito mode and completing the lab there.
●​ Review Lab tips and troubleshooting steps for more information.

This course uses a third-party app, Activity: Filter a SQL query, to enhance your learning
experience. The app will reference basic information like your name, email, and
Coursera ID.

Exemplar: Filter a SQL query

Activity overview

As a security analyst, knowing how to make better queries to retrieve specific pieces of
data can help you find the security-related information you need more efficiently.

In this lab activity, you’ll apply basic filters to SQL queries to retrieve information from a
MariaDB database.

MariaDB is a popular open source relational database that is compatible with MySQL.

This activity provides you with a great opportunity to apply what you’ve learned and add
filters to SQL queries.

This exemplar is a walkthrough of the previous Qwiklab activity, including detailed

instructions and solutions. You may use this exemplar if you were unable to complete
the lab and/or you need extra guidance in competing lab tasks. You may also refer to
this exemplar to prepare for the graded quiz in this module.

This exemplar is a walkthrough of the previous Qwiklab activity,

including detailed instructions and solutions. You may use this
exemplar if you were unable to complete the lab and/or you need
extra guidance in competing lab tasks. You may also refer to this
exemplar to prepare for the graded quiz in this module.

Note: The terms row and record are used interchangeably in this lab activity.

Scenario

In this scenario, you need to get specific information about employees, their machines,
and the departments they’re in. Your team needs this data to perform various tasks,
such as running updates, posting a privacy notice in certain departments, and sending
an alert to an employee with an issue on a machine.

You are responsible for finding the required information by querying a database. You’ll
add filters to your queries to locate the information more quickly.

Here’s how you’ll do this task: First, you’ll list all organization machines and their
operating systems. Second, you’ll list all machines with the operating system OS 2.
Third, you’ll list all the employees in the Finance and Sales departments. Fourth, you’ll
obtain information about machines.

You’re ready to add filters to SQL queries.

Note: In this lab you’ll be working with the organization database and the tables it
contains.

The lab starts with the organization database in the MariaDB shell that is already open.
This means you can start with the tasks as soon as you click the Start Lab button.
If you unintentionally exit the organization database in the MariaDB shell, you can
reconnect by running the sudo mysql organization command.

Task 1. List all organization machines

In this task, you need to get a list of all organization machines and their operating
systems. The data is contained in the machines table. You’ll need to use the SELECT
keyword to return specific columns.

●​ Run a SQL query to retrieve only the device_id and operating_system

columns from the machines table.

The command to complete this step:

SELECT device_id, operating_system

FROM machines;

The output lists only the selected columns from all the rows in the machines table:

200 rows in set (0.028 sec)

+--------------+------------------+

|... |

+--------------+------------------+

| device_id | operating_system |

+--------------+------------------+

| a184b775c707 | OS 1 |

| a192b174c940 | OS 2 |
| a305b818c708 | OS 3 |

| a317b635c465 | OS 1 |

| a320b137c219 | OS 2 |

| a398b471c573 | OS 3 |

How many rows were returned from the machines table? (You can view the number of
rows at the bottom of the output.)

Answer: The machines table returned 200 rows.

Task 2. Retrieve a list of the machines with OS 2

In this task, you need to obtain a list of all machines with the 'OS 2' operating system
because these machines need an update. To get this information, you’ll run your first
SQL query with a filter.

●​ Select all the records from the machines table with a value of 'OS 2' in the
operating_system column. Replace the value X with the correct string:

SELECT device_id, operating_system

FROM machines

WHERE operating_system = 'X';

The command to complete this step:

SELECT device_id, operating_system

FROM machines

WHERE operating_system = 'OS 2';

Note: The WHERE clause allows you to filter the results returned by a query by returning
only the records that satisfy the condition.

The output displays the selected columns of the machines table, filtered by the
operating system:

| a821b452c176 | OS 2 |

| b157c491d493 | OS 2 |

| b264c773d977 | OS 2 |

|... |

+--------------+------------------+

80 rows in set (0.264 sec)

How many machines in the database use the OS 2 operating system?

Answer: There are 80 machines in the database that use the OS 2 operating system.

Task 3. List employees in specific departments

In this task, you need to retrieve a list of all the employees in the Finance and Sales
departments to obtain their office numbers. A notice about handling confidential
financial information will be posted to these offices.

1.​ Filter the rows returned from department column in the employees table to
include only employees from the 'Finance' department. Replace X with the
appropriate column name and Y with the appropriate value to complete the filter:

WHERE X = 'Y';
The correct query to solve this step:

SELECT *

FROM employees

WHERE department = 'Finance';

The output displays the contents of the employees table, including only employees in
the Finance department.

What is the employee_id of the first row returned?

Answer: The employee_id of the first row returned is 1003.

2. Modify the previous query so that it returns employees who are in the 'Sales'
department.

The correct query to solve this step:

WHERE department = 'Sales';

The output will display the contents of the employees table, including only employees
in the Sales department.

How many employees work in the Sales department?

Answer: There are 33 employees who work in the Sales department.

Task 4. Identify employee machines

Your team recently discovered that there are issues with machines in the South building.
In this task, you need to obtain certain employee and computer information.

A machine in 'South-109' has an issue. You need to determine which employee uses
that computer so you can send them an alert.
 1.​ Write a query to identify which employee uses the office in 'South-109'. (The
data must be returned from the office column in the employees table.)

The correct query to solve this step:

WHERE office = 'South-109';

Which of the following employees uses the computer with the issue?

Answer: The user ID of the employee with the computer issue is jlansky.

Next, your team has determined that there is an issue with all the machines in the South
building. Offices in the organization are named with the building name, a hyphen, and
the office number in that building (for example, 'South-109').

2. Modify the query you used in the previous step so that it returns information on all the
employees in the 'South' building. Use the LIKE operator with % in this query.

The correct query to solve this step:

WHERE office LIKE 'South%';

Note: The LIKE keyword in SQL performs simple string matches. The matching pattern
may include the wildcard % to represent a string of any length. This wildcard may be
placed both before and after the targeted substring.

Which department does the first employee listed in the South building belong to?

Answer: The first employee on the list returned works in the Finance department.

Conclusion

Great work!

You now have practical experience in using SQL to

 ●​ apply the WHERE clause to filter what a SQL query returns and
●​ use the LIKE operator to filter for patterns.

You’re well on your way to running SQL queries to get specific data from a database.

Operators for filtering dates and

numbers
Previously, you examined operators like less than (<) or greater than (>) and explored
how they can be used in filtering numeric and date and time data types. This reading
summarizes what you learned and provides new examples of using operators in filters.

Numbers, dates, and times in cybersecurity

Security analysts work with more than just string data, or data consisting of an ordered
sequence of characters.

They also frequently work with numeric data, or data consisting of numbers. A few
examples of numeric data that you might encounter in your work as a security analyst
include:

●​ the number of login attempts

●​ the count of a specific type of log entry
●​ the volume of data being sent from a source
●​ the volume of data being sent to a destination
You'll also encounter date and time data, or data representing a date and/or time. As a
first example, logs will generally timestamp every record. Other time and date data
might include:

●​ login dates
●​ login times
●​ dates for patches
●​ the duration of a connection

Comparison operators

In SQL, filtering numeric and date and time data often involves operators. You can use
the following operators in your filters to make sure you return only the rows you need:

operator use

< less than

> greater than

= equal to

<= less than or equal to

 >= greater than or equal to

<> not equal to

Note: You can also use != as an alternative operator for not equal to.

Incorporating operators into filters

These comparison operators are used in the WHERE clause at the end of a query. The
following query uses the > operator to filter the birthdate column. You can run this
query to explore its output:

SELECT firstname, lastname, birthdate

FROM employees

WHERE birthdate > '1970-01-01';

Reset

This query returns the first and last names of employees born after, but not on,
'1970-01-01' (or January 1, 1970). If you were to use the >= operator instead, the
results would also include results on exactly '1970-01-01'.

In other words, the > operator is exclusive and the >= operator is inclusive. An
exclusive operator is an operator that does not include the value of comparison. An
inclusive operator is an operator that includes the value of comparison.

BETWEEN

Another operator used for numeric data as well as date and time data is the BETWEEN
operator. BETWEEN filters for numbers or dates within a range. For example, if you want
to find the first and last names of all employees hired between January 1, 2002 and
January 1, 2003, you can use the BETWEEN operator as follows:

SELECT firstname, lastname, hiredate

FROM employees

WHERE hiredate BETWEEN '2002-01-01' AND '2003-01-01';

Reset

Note: The BETWEEN operator is inclusive. This means records with a hiredate of
January 1, 2002 or January 1, 2003 are included in the results of the previous query.

Key takeaways

Operators are important when filtering numeric and date and time data. These include
exclusive operators such as < and inclusive operators such as <=. The BETWEEN
operator, another inclusive operator, helps you return the data you need within a range.

Activity: Apply more filters in

SQL
Introduction
In this lab, you’ll apply more filters to SQL queries to retrieve information from a
database. You’ll use common operators in SQL to filter by specific dates and times.
You’ll be using the MariaDB shell to run your SQL queries.

Disclaimer: For optimal performance and compatibility, it is recommended to use either

Google Chrome or Mozilla Firefox browsers while accessing the labs.

What you’ll do

You have multiple tasks in this lab:

●​ Filter for login attempts made after a certain date

●​ Filter for login attempts made in a certain date range
●​ Filter for login attempts made at a certain time
●​ Filter for login attempts by ID

Lab instructions

Start the lab

Before you start, you can review the Resources for completing SQL labs. Then from this
page, click Launch App. A Qwiklabs page will open and from that page, click Start Lab
to begin the activity!
You may attempt this lab a maximum of 5 times, and you will have 60 minutes to
complete this lab during each attempt.

End the lab

From within the lab, click End Lab to end your lab.

Additionally, sometimes you need to refresh your Coursera page in order for your
progress to be registered. If you refresh this page after you complete your lab, the green
check mark should appear.

Best practices for completing labs:

●​ Make sure your browser is up to date with the latest version.

●​ Make sure your internet connection is stable.
●​ After you complete the lab, leave the lab window open for at least 10 minutes in
order to allow the system to record your progress.
●​ If you run into issues connecting to the lab, try logging into Coursera in an
Incognito mode and completing the lab there.
●​ Review Lab tips and troubleshooting steps for more information.

This course uses a third-party app, Activity: Apply more filters in SQL, to enhance your
learning experience. The app will reference basic information like your name, email, and
Coursera ID
Exemplar: Apply more filters in
SQL

Activity Overview

This exemplar provides a detailed walk through and solutions for the "Apply more filters
in SQL" lab activity. As a security analyst, you'll often need to refine your data retrieval
by filtering based on specific dates, times, and ranges. This exemplar will guide you
through using SQL operators like >, >=, <, <=, BETWEEN, and filtering by specific
IDs.

Scenario

Continuing your work with the organization database, your team requires more precise
information from the login attempt logs. You need to retrieve records based on specific
time frames and individual login identifiers for investigation and analysis.
Task 1. Filter for login attempts made after a certain date

Your team is interested in login attempts that occurred after January 15th, 2023, to
investigate recent activity.

The login_date column in the log_in_attempts table stores the date of each
attempt.

Use the > (greater than) operator to filter for records where the login_date is after the
specified date. Replace YYYY-MM-DD with the correct date.

SELECT *

FROM log_in_attempts

WHERE login_date > 'YYYY-MM-DD';

The correct query to solve this step:

SELECT *

FROM log_in_attempts

WHERE login_date > '2023-01-15';

How many login attempts occurred after January 15th, 2023?

Answer: There were [Insert Example Answer Here, e.g., 112] login attempts after
January 15th, 2023.

Task 2. Filter for login attempts made in a certain date range

Your team needs to analyze login activity during the first week of February 2023,
specifically from February 1st to February 7th (inclusive).

The login_date column in the log_in_attempts table contains the date.

Use the BETWEEN operator to filter for records within the specified start and end dates.
Replace YYYY-MM-DD with the correct dates.

SELECT *

FROM log_in_attempts

WHERE login_date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD';

The correct query to solve this step:

SELECT *

FROM log_in_attempts

WHERE login_date BETWEEN '2023-02-01' AND '2023-02-07';

How many login attempts were made between February 1st and February 7th, 2023?
Answer: There were [Insert Example Answer Here, e.g., 88] login attempts in that date
range.

Task 3. Filter for login attempts made at a certain time

Your team is investigating a potential issue that occurred around 09:30 AM. You need to
retrieve all login attempts that happened at exactly this time.

The login_time column in the log_in_attempts table stores the time of each
attempt.

Use the = (equals) operator to filter for records where the login_time matches the
specific time. Replace HH:MM:SS with the correct time.

SELECT *

FROM log_in_attempts

WHERE login_time = 'HH:MM:SS';

The correct query to solve this step:

SELECT *

FROM log_in_attempts

WHERE login_time = '09:30:00';

How many login attempts occurred at 09:30:00?

Answer: There were [Insert Example Answer Here, e.g., 5] login attempts at 09:30:00.

Task 4. Filter for login attempts by ID

You need to retrieve the details of a specific login attempt with the login_id of 503.

The login_id column in the log_in_attempts table uniquely identifies each login
attempt.
Use the = (equals) operator to filter for the record where the login_id matches the
specified value. Replace ID_Value with the correct ID.

SELECT *

FROM log_in_attempts

WHERE login_id = ID_Value;

The correct query to solve this step:

SELECT *

FROM log_in_attempts

WHERE login_id = 503;

What was the login_date for the login attempt with login_id 503?
Answer: The login_date for login ID 503 was [Insert Example Answer Here, e.g.,
2023-02-10].

Conclusion

Excellent work! You've now practiced applying more specific filters in SQL using
comparison operators (>, >=, <, <=, =) and the BETWEEN operator to retrieve data
based on dates, times, and unique identifiers. These skills are essential for conducting
thorough investigations and extracting targeted information from security logs and
databases. You are becoming more adept at using SQL to analyze and understand
security-related data.

More on filters with AND, OR,

and NOT

Previously, you explored how to add filters containing the AND, OR, and NOT operators to
your SQL queries. In this reading, you'll continue to explore how these operators can
help you refine your queries.
Logical operators

AND, OR, and NOT allow you to filter your queries to return the specific information that
will help you in your work as a security analyst. They are all considered logical
operators.

AND

First, AND is used to filter on two conditions. AND specifies that both conditions must be
met simultaneously.

As an example, a cybersecurity concern might affect only those customer accounts that
meet both the condition of being handled by a support representative with an ID of 5
and the condition of being located in the USA. To find the names and emails of those
specific customers, you should place the two conditions on either side of the AND
operator in the WHERE clause:

SELECT firstname, lastname, email, country, supportrepid

FROM customers

WHERE supportrepid = 5 AND country = 'USA';

Reset

Running this query returns four rows of information about the customers. You can use
this information to contact them about the security concern.

OR

The OR operator also connects two conditions, but OR specifies that either condition can
be met. It returns results where the first condition, the second condition, or both are met.

For example, if you are responsible for finding all customers who are either in the USA
or Canada so that you can communicate information about a security update, you can
use an OR operator to find all the needed records. As the following query demonstrates,
you should place the two conditions on either side of the OR operator in the WHERE
clause:

SELECT firstname, lastname, email, country

FROM customers

WHERE country = 'Canada' OR country = 'USA';

Reset

The query returns all customers in either the US or Canada.

Note: Even if both conditions are based on the same column, you need to write out both
full conditions. For instance, the query in the previous example contains the filter WHERE
country = 'Canada' OR country = 'USA'.

NOT

Unlike the previous two operators, the NOT operator only works on a single condition,
and not on multiple ones. The NOT operator negates a condition. This means that SQL
returns all records that don’t match the condition specified in the query.

For example, if a cybersecurity issue doesn't affect customers in the USA but might
affect those in other countries, you can return all customers who are not in the USA.
This would be more efficient than creating individual conditions for all of the other
countries. To use the NOT operator for this task, write the following query and place NOT
directly after WHERE:
SELECT firstname, lastname, email, country

FROM customers

WHERE NOT country = 'USA';

Reset

SQL returns every entry where the customers are not from the USA.

Pro tip: Another way of finding values that are not equal to a certain value is by using
the <> operator or the != operator. For example, WHERE country <> 'USA' and
WHERE country != 'USA' are the same filters as WHERE NOT country = 'USA'.

Combining logical operators

Logical operators can be combined in filters. For example, if you know that both the
USA and Canada are not affected by a cybersecurity issue, you can combine operators
to return customers in all countries besides these two. In the following query, NOT is
placed before the first condition, it's joined to a second condition with AND, and then NOT
is also placed before that second condition. You can run it to explore what it returns:
SELECT firstname, lastname, email, country

FROM customers

WHERE NOT country = 'Canada' AND NOT country = 'USA';

Reset

Key takeaways

Logical operators allow you to create more specific filters that target the security-related
information you need. The AND operator requires two conditions to be true
simultaneously, the OR operator requires either one or both conditions to be true, and
the NOT operator negates a condition. Logical operators can be combined together to
create even more specific queries.
Activity: Filter with AND, OR,
and NOT
Introduction

In this lab, you’ll use the AND, OR, and NOT operators in SQL to filter for information.
You’ll use SQL to get specific information about employees, their machines, and the
departments they’re in. You’ll be using the MariaDB shell to run SQL queries.

Disclaimer: For optimal performance and compatibility, it is recommended to use either

Google Chrome or Mozilla Firefox browsers while accessing the labs.

What you’ll do

You have multiple tasks in this lab:

●​ Filter for login attempts that occurred after hours

●​ Filter for login attempts on specific dates
●​ Filter for login attempts from specific locations
●​ Filter for information on employees in specific departments
 ●​ Filter for information on employees not in a specific department

Lab instructions

Start the lab

Before you start, you can review the Resources for completing SQL labs. Then from this
page, click Launch App. A Qwiklabs page will open and from that page, click Start Lab
to begin the activity!

You may attempt this lab a maximum of 5 times, and you will have 60 minutes to
complete this lab during each attempt.

End the lab

From within the lab, click End Lab to end your lab.

Additionally, sometimes you need to refresh your Coursera page in order for your
progress to be registered. If you refresh this page after you complete your lab, the green
check mark should appear.

Best practices for completing labs:

●​ Make sure your browser is up to date with the latest version.

 ●​ Make sure your internet connection is stable.
●​ After you complete the lab, leave the lab window open for at least 10 minutes in
order to allow the system to record your progress.
●​ If you run into issues connecting to the lab, try logging into Coursera in an
Incognito mode and completing the lab there.
●​ Review Lab tips and troubleshooting steps for more information.

This course uses a third-party app, Activity: Filter with AND, OR, and NOT, to enhance
your learning experience. The app will reference basic information like your name,
email, and Coursera ID.

Exemplar: Filter with AND, OR,

and NOT
Activity overview

As a security analyst, you’ll likely need to analyze data. And often finding the specific
data you’ll need depends on more than one factor.

To retrieve specific pieces of information from the database, you can filter for multiple
conditions. You can also filter for what does not match a particular condition.

In this lab activity, you’ll use the AND, OR, and NOT operators to create more complex
filters for SQL queries.

Get ready to practice running a few complex SQL queries!

This exemplar is a walkthrough of the previous Qwiklab activity, including detailed

instructions and solutions. You may use this exemplar if you were unable to complete
the lab and/or you need extra guidance in competing lab tasks. You may also refer to
this exemplar to prepare for the graded quiz in this module.

Scenario

In this scenario, you need to obtain specific information about employees, their
machines, and the departments they belong to from the database.

Your team needs data to investigate potential security issues and to update computers.

You are responsible for filtering the required information from the database.

Here’s how you’ll do this task: First, you’ll retrieve all failed login attempts after business
hours. Second, you’ll retrieve all login attempts that occurred on specific dates. Third,
you’ll retrieve logins that didn't originate in Mexico. Fourth, you’ll retrieve information
about certain employees in the Marketing department. Fifth, you’ll retrieve information
about employees in the Finance or the Sales department. Finally, you’ll obtain
information about employees who are not in the Information Technology department.

Note: In this lab you’ll be working with the organization database and the tables it
contains.

The lab starts with the organization database in the MariaDB shell that is already open.
This means you can start with the tasks as soon as you click the Start Lab button.

If you unintentionally exit the organization database in the MariaDB shell, you can
reconnect by running the sudo mysql organization command.

Task 1. Retrieve after hours failed login attempts

Your team is investigating failed login attempts that were made after business hours.
You want to retrieve this information from the login activity. You’ll identify all
unsuccessful attempts after 18:00.
The login_time column in the log_in_attempts table contains information on when
login attempts were made. Office hours end at '18:00'.

The success column in the log_in_attempts table contains values of TRUE or FALSE
to indicate whether the login was successful. MySQL stores Boolean values as 1 for
TRUE, and 0 for FALSE. This means that TRUE is represented as 1, and FALSE
represented as 0 in the success column.

●​ Use the AND operator to retrieve the failed login attempts that occurred after
business hours. Replace the X and Y with the correct values to filter for the
records you need:

SELECT *

FROM log_in_attempts

WHERE login_time > 'X' AND success = Y;

Note: Values of TRUE and FALSE are not placed in single quotes because they are not
string data. They are Boolean data, which is another data type.

The command to complete this step:

SELECT *

FROM log_in_attempts

WHERE login_time > '18:00' AND success = FALSE;

How many failed login attempts occurred after 18:00?

Answer: There are 19 failed login attempts that occurred after 18:00.

Task 2. Retrieve login attempts on specific dates

Your team is investigating a suspicious event that occurred on '2022-05-09'. You
want to retrieve all login attempts that occurred on this day and the day before
('2022-05-08').

The login_date column in the log_in_attempts table contains information on the

dates when login attempts were made.

●​ Use the OR operator to retrieve the failed login attempts on the specified days.
Replace the X and Y with the correct values to filter for the records you need:

SELECT *

FROM log_in_attempts

WHERE login_date = 'X' OR login_date = 'Y';

The correct query to solve this step:

SELECT *

FROM log_in_attempts

WHERE login_date = '2022-05-09' OR login_date = '2022-05-08';

How many login attempts were made on these two days?

Answer: There are 75 login attempts in these two days.

Task 3. Retrieve login attempts outside of Mexico

Now, your team is investigating logins that did not originate in Mexico, and you need to
find this information. Note that the country field includes entries with 'MEX' and
'MEXICO'. You should use the NOT and LIKE operators and the matching pattern
'MEX%'.

●​ Run the following SQL query to retrieve login attempts that did not originate in
Mexico. Replace X with the correct operator and Y with the correct pattern to filter
for the information you need:

SELECT *

FROM log_in_attempts

WHERE X country LIKE 'Y';

The correct query to solve this step:

SELECT *

FROM log_in_attempts

WHERE NOT country LIKE 'MEX%';

How many login attempts were made outside of Mexico?

Answer: There are 144 login attempts made outside of Mexico.

Task 4. Retrieve employees in Marketing

For tasks 4, 5 and 6 you need to retrieve the information from the department and
office columns in the employees table.

You can run the following SQL query if you need to view the columns and values in the
employees table:

SELECT *

FROM employees;
Your team is updating employee machines, and you need to obtain the information
about employees in the 'Marketing' department who are located in all offices in the
East building (such as 'East-170' or 'East-320').

●​ Write a SQL query to retrieve this information from the employees table. Select
all columns and include filters on the department and office columns to return
only the needed records.

Note: You’ll need to use the AND and LIKE operators to satisfy both of these criteria.

The correct query to solve this step:

SELECT *

FROM employees

WHERE department = 'Marketing' AND office LIKE 'East%';

What is the username of the first employee in the Marketing department in the East
building?

Answer: The username of the first employee in the Marketing department in the East
building is elarson.

Task 5. Retrieve employees in Finance or Sales

Now, your team needs to perform a different update to the computers of all employees
in the Finance or the Sales department, and you need to locate information on these
employees.

●​ Write a SQL query to retrieve records for employees in the 'Finance' or the
'Sales' department.
Note: Even though both conditions are based on the same column, you need to write
out both full conditions. This means that you must specify department as the column in
both conditions.

The correct query to solve this step:

SELECT *

FROM employees

WHERE department = 'Finance' OR department = 'Sales';

What is the username of the first employee in the Sales department returned by the
query?

Answer: The username of the first employee in the Sales department is lrodriqu.

Task 6. Retrieve all employees not in IT

Your team needs to make one more update. This update was already made to
employee computers in the Information Technology department. The team needs
information about employees who are not in that department. You should use the NOT
operator to identify these employees.

●​ Write a SQL query to retrieve records for employees who are not in the
'Information Technology' department.

The correct query to solve this step:

SELECT *

FROM employees

WHERE NOT department = 'Information Technology';

How many employees are not in the Information Technology department?

Answer: There are 161 employees who aren’t in the Information Technology
department.

Conclusion

Great work!

You now have practical experience in using SQL to

●​ run SQL queries to retrieve information from a database and

●​ apply AND, OR, and NOT operators to filter SQL queries.

You’re well on your way to running complex SQL queries to get specific data from a
database.

Portfolio Activity Exemplar:

Apply filters to SQL queries
Here is a completed exemplar along with an explanation of how the exemplar fulfills the
expectations for the activity.

Completed Exemplar

To review the exemplar for this course item, click the following link and select Use
Template.
Link to exemplar: Apply filters to SQL queries

OR

If you don’t have a Google account, you can download the exemplar directly from the
following attachment.

Assessment of Exemplar

Compare the exemplar to your completed activity. Review your work using each of the
criteria in the exemplar. What did you do well? Where can you improve? Use your
answers to these questions to revise your project as needed and guide you as you
continue to progress through the certificate program.

Note: The exemplar represents one possible way to complete the Apply filters to SQL
queries portfolio activity. Yours will likely differ in certain ways. What’s important is that
you understand how to use SQL queries to apply filters.

The exemplar uses details from the given scenario and includes the following:

●​ Screenshots of queries or typed versions of the queries

●​ Explanations of queries
●​ A project description at the beginning
●​ A summary at the end
●​ Details on using LIKE to search for a pattern
●​ Details on filtering for dates and times
●​ Details on using AND and OR to filter on multiple conditions
●​ Details on using NOT in filters
Compare types of joins

Previously, you explored SQL joins and how to use them to join data from multiple
tables when these tables share a common column. You also examined how there are
different types of joins, and each of them returns different rows from the tables being
joined. In this reading, you'll review these concepts and more closely analyze the syntax
needed for each type of join.

Inner joins

The first type of join that you might perform is an inner join. INNER JOIN returns rows
matching on a specified column that exists in more than one table.
It only returns the rows where there is a match, but like other types of joins, it returns all
specified columns from all joined tables. For example, if the query joins two tables with
SELECT *, all columns in both of the tables are returned.

Note: If a column exists in both of the tables, it is returned twice when SELECT * is
used.

The syntax of an inner join

To write a query using INNER JOIN, you can use the following syntax:

SELECT *
FROM employees

INNER JOIN machines ON employees.device_id = machines.device_id;

You must specify the two tables to join by including the first or left table after FROM and
the second or right table after INNER JOIN.

After the name of the right table, use the ON keyword and the = operator to indicate the
column you are joining the tables on. It's important that you specify both the table and
column names in this portion of the join by placing a period (.) between the table and
the column.

In addition to selecting all columns, you can select only certain columns. For example,
if you only want the join to return the username, operating_system and device_id
columns, you can write this query:

SELECT username, operating_system, employees.device_id

FROM employees

INNER JOIN machines ON employees.device_id = machines.device_id;

Note: In the example query, username and operating_system only appear in one of
the two tables, so they are written with just the column name. On the other hand,
because device_id appears in both tables, it's necessary to indicate which one to
return by specifying both the table and column name (employees.device_id).

Outer joins

Outer joins expand what is returned from a join. Each type of outer join returns all rows
from either one table or both tables.

Left joins

When joining two tables, LEFT JOIN returns all the records of the first table, but only
returns rows of the second table that match on a specified column.
The syntax for using LEFT JOIN is demonstrated in the following query:

SELECT *

FROM employees

LEFT JOIN machines ON employees.device_id = machines.device_id;

As with all joins, you should specify the first or left table as the table that comes after
FROM and the second or right table as the table that comes after LEFT JOIN. In the
example query, because employees is the left table, all of its records are returned. Only
records that match on the device_id column are returned from the right table,
machines.
Right joins

When joining two tables, RIGHT JOIN returns all of the records of the second table, but
only returns rows from the first table that match on a specified column.

The following query demonstrates the syntax for RIGHT JOIN:

SELECT *

FROM employees

RIGHT JOIN machines ON employees.device_id = machines.device_id;

RIGHT JOIN has the same syntax as LEFT JOIN, with the only difference being the
keyword RIGHT JOIN instructs SQL to produce different output. The query returns all
records from machines, which is the second or right table. Only matching records are
returned from employees, which is the first or left table.

Note: You can use LEFT JOIN and RIGHT JOIN and return the exact same results if
you use the tables in reverse order. The following RIGHT JOIN query returns the exact
same result as the LEFT JOIN query demonstrated in the previous section:

SELECT *

FROM machines

RIGHT JOIN employees ON employees.device_id = machines.device_id;

All that you have to do is switch the order of the tables that appear before and after the
keyword used for the join, and you will have swapped the left and right tables.

Full outer joins

FULL OUTER JOIN returns all records from both tables. You can think of it as a way of
completely merging two tables.
You can review the syntax for using FULL OUTER JOIN in the following query:

SELECT *

FROM employees

FULL OUTER JOIN machines ON employees.device_id =

machines.device_id;

The results of a FULL OUTER JOIN query include all records from both tables. Similar
to INNER JOIN, the order of tables does not change the results of the query.
Key takeaways

When working in SQL, there are multiple ways to join tables. All joins return the records
that match on a specified column. INNER JOIN will return only these records. Outer
joins also return all other records from one or both of the tables. LEFT JOIN returns all
records from the first or left table, RIGHT JOIN returns all records from the second or
right table, and FULL OUTER JOIN returns all records from both tables.

Activity: Complete a join

Introduction

In this lab, you’ll use INNER JOIN, LEFT JOIN, and RIGHT JOIN in SQL to retrieve
information from two different tables. You’ll use these different types of SQL joins to join
data from separate machines, employees, and login attempts tables. You’ll be using the
MariaDB shell to run SQL queries.

Disclaimer: For optimal performance and compatibility, it is recommended to use either

Google Chrome or Mozilla Firefox browsers while accessing the labs.

What you’ll do
You have multiple tasks in this lab:

●​ Use an inner join to find information on employees and their machines

●​ Use a left join and right join to find information on employees and their machines
●​ Use an inner join to find information on employees and their login attempts

Lab instructions

Start the lab

Before you start, you can review the Resources for completing SQL labs. Then from this
page, click Launch App. A Qwiklabs page will open and from that page, click Start Lab
to begin the activity!

You may attempt this lab a maximum of 5 times, and you will have 60 minutes to
complete this lab during each attempt.

End the lab

From within the lab, click End Lab to end your lab.

Additionally, sometimes you need to refresh your Coursera page in order for your
progress to be registered. If you refresh this page after you complete your lab, the green
check mark should appear.
Best practices for completing labs:

●​ Make sure your browser is up to date with the latest version.

●​ Make sure your internet connection is stable.
●​ After you complete the lab, leave the lab window open for at least 10 minutes in
order to allow the system to record your progress.
●​ If you run into issues connecting to the lab, try logging into Coursera in an
Incognito mode and completing the lab there.
●​ Review Lab tips and troubleshooting steps for more information.

This course uses a third-party app, Activity: Complete a join, to enhance your learning
experience. The app will reference basic information like your name, email, and
Coursera ID.

Exemplar: Complete a join

Activity overview

As a security analyst, you’ll often find that you need data from more than one table.

Previously, you learned that a relational database is a structured database containing

tables that are related to each other.

SQL joins enable you to combine tables that contain a shared column. This is helpful
when you need to connect information that appears in different tables.
In this lab activity, you’ll use SQL joins to connect separate tables and retrieve needed
information.

Get ready to apply what you’ve learned and join some data!

This exemplar is a walkthrough of the previous Qwiklab activity, including detailed

instructions and solutions. You may use this exemplar if you were unable to complete
the lab and/or you need extra guidance in competing lab tasks. You may also refer to
this exemplar to prepare for the graded quiz in this module.

Note: The terms row and record are used interchangeably.

Scenario

In this scenario, you’ll investigate a recent security incident that compromised some
machines.

You are responsible for getting the required information from the database for the
investigation.

Here’s how you’ll do this task: First, you’ll use an inner join to identify which employees
are using which machines. Second, you’ll use left and right joins to find machines that
do not belong to any specific user and users who do not have any specific machine
assigned to them. Finally, you’ll use an inner join to list all login attempts made by all
employees.

You’re ready to join tables in SQL!

Note: In this lab you’ll be working with the organization database and the tables it
contains.

The lab starts with the organization database in the MariaDB shell that is already open.
This means you can start with the tasks as soon as you click the Start Lab button.
If you unintentionally exit the organization database in the MariaDB shell, you can
reconnect by running the sudo mysql organization command.

Task 1. Match employees to their machines

First, you must identify which employees are using which machines. The data is located
in the machines and employees tables.

You must use a SQL inner join to return the records you need based on a connecting
column. In the scenario, both tables include the device_id column, which you’ll use to
perform the join.

1.​ Run the following query to retrieve all records from the machines table:

SELECT *

FROM machines;

You’ll note that this query is not sufficient to perform the join and retrieve the information
you need.

2. Complete the query to perform an inner join between the machines and employees
tables on the device_id column. Replace X and Y with this column name:

SELECT *

FROM machines

INNER JOIN employees ON machines.X = employees.Y;

Note: Placing the employees table after INNER JOIN makes it the right table.
To complete a join you need to link the joined tables on a common column. In the case
of the employees and machines tables, the device_id column is common.

The correct query to solve this step:

SELECT *

FROM machines

INNER JOIN employees ON machines.device_id = employees.device_id;

How many rows did the inner join return?

Answer: The inner join query returned 185 rows.

Task 2. Return more data

You now must return the information on all machines and the employees who have
machines. Next, you must do the reverse and retrieve the information of all employees
and any machines that are assigned to them.

To achieve this, you’ll complete a left join and a right join on the employees and
machines tables. The results will include all records from one or the other table. You
must link these tables using the common device_id column.

1.​ Run the following SQL query to connect the machines and employees tables
through a left join. You must replace the keyword X in the query:

SELECT *

FROM machines

X JOIN employees ON machines.device_id = employees.device_id;

The correct query to solve this step:

SELECT *

FROM machines

LEFT JOIN employees ON machines.device_id = employees.device_id;

Note: In a left join, all records from the table referenced after FROM and before LEFT
JOIN are included in the result. In this case, all records from the machines table are
included, regardless of whether they are assigned to an employee or not.

What is the value in the username column for the last record returned?

Answer: The last username returned is NULL.

2. Run the following SQL query to connect the machines and employees tables
through a right join. You must replace the keyword X in the query to solve the problem:

SELECT *

FROM machines

X JOIN employees ON machines.device_id = employees.device_id;

Note: In a right join, all records from the table referenced after RIGHT JOIN are included
in the result. In this case, all records from the employees table are included, regardless
of whether they have a machine or not.

The correct query to solve this step:

SELECT *

FROM machines

RIGHT JOIN employees ON machines.device_id = employees.device_id;

What is the value in the username column for the last record returned?

Answer: The value in the username column for the last record returned is areyes.

Task 3. Retrieve login attempt data

To continue investigating the security incident, you must retrieve the information on all
employees who have made login attempts. To achieve this, you’ll perform an inner join
on the employees and log_in_attempts tables, linking them on the common
username column.

●​ Run the following SQL query to perform an inner join on the employees and
log_in_attempts tables. Replace X with the name of the right table. Then
replace Y and Z with the name of the column that connects the two tables:

SELECT *

FROM employees

INNER JOIN X ON Y = Z;

Note: You must specify the table name with the column name ([Link]) when
joining the tables.

The correct query to solve this step:

SELECT *

FROM employees

INNER JOIN log_in_attempts ON [Link] =

log_in_attempts.username;
How many records are returned by this inner join?

Answer: There are 200 records returned by the inner join.

Conclusion

Great work!

You have completed this activity and should be able to use joins to combine data from
multiple tables in a database.

You now have practical experience in using

●​ INNER JOIN,
●​ LEFT JOIN, and
●​ RIGHT JOIN.

Great work using SQL joins to obtain the precise data you need.

Continuous learning in SQL

You've explored a lot about SQL, including applying filters to SQL queries and joining
multiple tables together in a query. There's still more that you can do with SQL. This
reading will explore an example of something new you can add to your SQL toolbox:
aggregate functions. You'll then focus on how you can continue learning about this and
other SQL topics on your own.

Aggregate functions
In SQL, aggregate functions are functions that perform a calculation over multiple data
points and return the result of the calculation. The actual data is not returned.

There are various aggregate functions that perform different calculations:

●​ COUNT returns a single number that represents the number of rows returned from
your query.
●​ AVG returns a single number that represents the average of the numerical data in
a column.
●​ SUM returns a single number that represents the sum of the numerical data in a
column.

Aggregate function syntax

To use an aggregate function, place the keyword for it after the SELECT keyword, and
then in parentheses, indicate the column you want to perform the calculation on.

For example, when working with the customers table, you can use aggregate functions
to summarize important information about the table. If you want to find out how many
customers there are in total, you can use the COUNT function on any column, and SQL
will return the total number of records, excluding NULL values. You can run this query
and explore its output:

SELECT COUNT(firstname)

FROM customers;

Reset

The result is a table with one column titled COUNT(firstname) and one row that
indicates the count.

If you want to find the number of customers from a specific country, you can add a filter
to your query:
SELECT COUNT(firstname)

FROM customers

WHERE country = 'USA';

Reset

With this filter, the count is lower because it only includes the records where the
country column contains a value of 'USA'.

There are a lot of other aggregate functions in SQL. The syntax of placing them after
SELECT is exactly the same as the COUNT function.

Continuing to learn SQL

SQL is a widely used querying language, with many more keywords and applications.
You can continue to learn more about aggregate functions and other aspects of using
SQL on your own.

Most importantly, approach new tasks with curiosity and a willingness to find new ways
to apply SQL to your work as a security analyst. Identify the data results that you need
and try to use SQL to obtain these results.

Fortunately, SQL is one of the most important tools for working with databases and
analyzing data, so you'll find a lot of support in trying to learn SQL online. First, try
searching for the concepts you've already learned and practiced to find resources that
have accurate easy-to-follow explanations. When you identify these resources, you can
use them to extend your knowledge.

Continuing your practical experience with SQL is also important. You can also search
for new databases that allow you to perform SQL queries using what you've learned.
Key takeaways

Aggregate functions like COUNT, SUM, and AVG allow you to work with SQL in new ways.
There are many other additional aspects of SQL that could be useful to you as an
analyst. By continuing to explore SQL on your own, you can expand the ways you can
apply SQL in a cybersecurity context.

Reference guide: SQL

The SQL reference guide contains keywords for SQL queries. Security analysts can use
these keywords to query databases and find data to support security-related decisions.
The reference guide is divided into four different categories of SQL keywords for
security-related tasks:

●​ Query a database
●​ Apply filters to SQL queries
●​ Join tables
●​ Perform calculations

Within each category, commands are organized alphabetically.

Access and save the guide

You can save a copy of this guide for future reference. You can use it as a resource for
additional practice or in your future professional projects.

To access a downloadable version of this course item, click the following link and select
Use Template.

Reference guide: SQL

OR

If you don’t have a Google account, you can download the item directly from the
following attachment.

Glossary terms from module 4

Terms and definitions from Course 4, Module 4

Database: An organized collection of information or data

Date and time data: Data representing a date and/or time

Exclusive operator: An operator that does not include the value of comparison

Filtering: Selecting data that match a certain condition

Foreign key: A column in a table that is a primary key in another table

Inclusive operator: An operator that includes the value of comparison

Log: A record of events that occur within an organization's systems

Numeric data: Data consisting of numbers

Operator: A symbol or keyword that represents an operation

Primary key: A column where every row has a unique entry

Query: A request for data from a database table or a combination of tables

Relational database: A structured database containing tables that are related to each
other

String data: Data consisting of an ordered sequence of characters

SQL (Structured Query Language): A programming language used to create, interact

with, and request information from a database

Syntax: The rules that determine what is correctly structured in a computing language

Wildcard: A special character that can be substituted with any other character

Reflect and connect with peers

Congratulations on completing the Tools of the Trade: Linux and SQL course!

You learned Linux and SQL, which will help you manage servers, investigate incidents,
and analyze data.
Want to discuss further and learn from others? Learning is more rewarding when you're
part of a supportive community. As a reminder, you can connect with fellow learners by
accessing the Google Cybersecurity Community to discuss course content and expand
your network.

Get started on the next course

Congratulations on completing Course 4 of the Google Cybersecurity Certificate: Tools
of the Trade: Linux and SQL! In this part of the program, you learned about computing
skills that will support your work as an analyst. First, you learned about operating
systems. Then, you communicated with the Linux operating system through the Bash
shell in order to complete security-related tasks. Finally, you performed SQL queries
that returned information to support security-related decisions.

The Google Cybersecurity Certificate has nine courses:

1.​ Foundations of Cybersecurity — Explore the cybersecurity profession, including

significant events that led to the development of the cybersecurity field and its
continued importance to organizational operations. Learn about entry-level
cybersecurity roles and responsibilities.
2.​ Play It Safe: Manage Security Risks — Identify how cybersecurity professionals
use frameworks and controls to protect business operations, and explore
common cybersecurity tools.
 3.​ Connect and Protect: Networks and Network Security — Gain an understanding
of network-level vulnerabilities and how to secure networks.
4.​ Tools of the Trade: Linux and SQL — Explore foundational computing skills,
including communicating with the Linux operating system through the command
line and querying databases with SQL. (This is the course you just completed.
Well done!)
5.​ Assets, Threats, and Vulnerabilities — Learn about the importance of security
controls and developing a threat actor mindset to protect and defend an
organization’s assets from various threats, risks, and vulnerabilities.
6.​ Sound the Alarm: Detection and Response — Understand the incident response
lifecycle and practice using tools to detect and respond to cybersecurity
incidents.
7.​ Automate Cybersecurity Tasks with Python — Explore the Python programming
language and write code to automate cybersecurity tasks.
8.​ Put It to Work: Prepare for Cybersecurity Jobs — Learn about incident
classification, escalation, and ways to communicate with stakeholders. This
course closes out the program with tips on how to engage with the cybersecurity
community and prepare for your job search.
9.​ Accelerate Your Job Search with AI — Gain practical job search strategies and
learn how to leverage AI tools (like Gemini and NotebookLM) to uncover your
most valuable skills, create a job search plan, manage your applications, and
practice for interviews as you navigate your path to your next role.

Now that you have completed this course, you’re ready to move on to the next course:
Assets, Threats, and Vulnerabilities.

Keep up the great work!