Module 4: Databases and SQL

Contents hide

1 INTRODUCTION – Databases and SQL

2 Learning Objectives

3 TEST YOUR KNOWLEDGE: SQL AND DATABASES

4 TEST YOUR KNOWLEDGE: SQL QUERIES

5 TEST YOUR KNOWLEDGE: MORE SQL FILTERS

6 TEST YOUR KNOWLEDGE: SQL JOINS

7 MODULE 4 CHALLENGE

8 CONCLUSION – Databases and SQL

Spread the love

INTRODUCTION – Databases and SQL

Thus, partaking in such overview is where people will get to carry out exercise to develop a practice of SQL communication with databases. The core of this module is the practical aspect of querying databases, especially filtering results to access information of interest. Participants will also learn how to concatenate multiple databases using SQL and enrich this feature into the database query initiated by them.

In this way, learners will be able to identify the theory behind SQL and, in practice, understand how it would apply to actual occurrence in the immediate world with these exercises. Practical application being the focus of these efforts will enable participants to comprehendfully use SQL to any complexity when interacting with databases. Progressing through this overview, there will be an increase in understanding of the capabilities of SQL plus the hands-on skill necessary to index and manipulate databases effectively. This will be an important stepping-stone on the journey toward proficiency in data analytics and allied fields.

Learning Objectives

Explain the importance of SQL in security functions.
Explain the organization of a relational database.
Use SQL to process requests for data from database systems.
Apply filters in SQL queries.
Together with other SQL commands, use joins to combine multiple tables into a single query across several tables.

TEST YOUR KNOWLEDGE: SQL AND DATABASES

1. Which statement accurately describes the organization of a relational database?

Relational databases consist of a single table containing related information.
Relational databases contain tables that are related to each other through primary and foreign keys. (CORRECT)
Relational databases contain primary keys with at least two duplicate values.
Relational databases consist of a single table with one primary key and one foreign key.

Relational databases, which comprise a structured database, constitute tables that store the data. Each of these tables has a primary key attached that relates them to other tables, creating a set of foreign keys among them. This arrangement allows easy retrieval and manipulation of records, thereby allowing linking of records from one table to another, ensuring consistency and integrity.

2. What is SQL used for? Select two answers.

Creating, interacting with, and requesting information from a database (CORRECT)
Securing an organization’s systems and networks
Finding data to support security-related decisions and analysis (CORRECT)
Allowing users to access a specific machine

The Structured Query Language (SQL) is a programming language that creates databases, manipulates them, and provides clients the ability to query the databases. All database operations such as retrieving, updating, inserting, and deleting can be accomplished with SQL command statements. Its filtering abilities like the WHERE clause can help us to retrieve just specific data, which is an essential part of data isolation for security professionals to draw conclusions from monitored specific information, such as the detection of breaches in security or access pattern monitoring.

3. A record of attempts to connect to an organization’s network is one example of a log.

True (CORRECT)
False

A log is like a document wherein actions performed on an organization system like tries to connect to its network can be recorded.

4. Fill in the blank: A request for data from a database table or a combination of tables is called a _____.

query (CORRECT)
log
key
row

A query is an inquiry that seeks information from a table in the database or from a combination of tables.

5. How do security analysts commonly use SQL in their work?

To find relevant information to support cybersecurity-related decisions (CORRECT)
To block malicious actors
To authenticate users
To install updates

SQL is frequently utilized by security analysts to extract pertinent information that supports their decisions on issues related to cybersecurity.

TEST YOUR KNOWLEDGE: SQL QUERIES

1. What is filtering in SQL?

Changing a table to match a condition
Selecting data that match a certain condition (CORRECT)
Removing invalid records
Removing unnecessary data from the database

Filtering in SQL means selecting records based on specific conditions, which is a process mostly done by analysts to extract the relevant data from it.

2. You are working with the Chinook database and want to return the firstname, lastname, and phone of all employees. Replace –??? with the missing information to complete the query. (If you want to undo your changes to the query, you can click the Reset button.)

What is Andrew Adams’ phone number?

+1 (403) 262-3443
+1 (780) 428-9482 (CORRECT)
+1 (780) 836-9987
+1 (403) 467-3351

This query assumes that the table storing contact information is called contacts and has the fields firstname, lastname, and phone. If necessary, adapt the names of the tables and fields in your database schema appropriately.

3. A security analyst wants to filter the log_in_attempts table for records where the value in the country column is ‘Canada’. What is a valid query for this?

SELECT *
FROM log_in_attempts
WHERE country = ‘Canada’; (CORRECT)

SELECT *
FROM log_in_attempts
WHERE country = Canada;

SELECT WHERE country = ‘Canada’
FROM log_in_attempts;

WHERE country = ‘Canada’
SELECT *
FROM log_in_attempts;

Using the below query, the security analyst can retrieve records from the log_in_attempts table where the country column is enabled to canada:

SELECT *
FROM log_in_attempts
WHERE country = ‘Canada’;

4. Which pattern matches with any string that starts with the character ‘A’?

‘%A’
‘%A%
‘A’
‘A%’ (CORRECT)

In a database pattern, the percentage sign represents a wildcard for any character sequence. With the pattern “A%”, the % stands for any string starting with the letter A, ending with and consisting of any set of characters or none at all.

5. Which columns will SELECT * return?

All columns from the specified table (CORRECT)
The first five columns from the specified table
The first column from the specified table
The last column from the specified table

The SELECT * statement in SQL retrieves all columns from the given table.

6. Which WHERE clause contains the correct syntax to return all records that contain a value in the username column that starts with the character ‘a’?

WHERE username = ‘a%’;
WHERE username LIKE ‘a’;
WHERE username = ‘a’;
WHERE username LIKE ‘a%’; (CORRECT)

The aforementioned query uses the LIKE operator appended to the WHERE condition in order to search for a matching pattern in the username column. The % wildcard stands for zero or more characters of any type, meaning it can match usernames that begin with the letter a.

TEST YOUR KNOWLEDGE: MORE SQL FILTERS

1. Which filter outputs all records with values in the date column between ’01-01-2015′ (January 1, 2015) and ’01-04-2015′ (April 1, 2015)?

WHERE date BETWEEN ’01-01-2015′, ’01-04-2015′;
WHERE date > ’01-01-2015′;
WHERE date < ’01-04-2015′;
WHERE date BETWEEN ’01-01-2015′ AND ’01-04-2015′; (CORRECT)

bring back all the records which have a value in the date column that is within the boundary of ’01-01-2015′ to ’01-04-2015′ inclusive. For filtering out the data, the BETWEEN operator is used between the desired dates. The initial and final value (’01-01-2015′ and ’01-04-2015′ respectively) also falls in the result set.

2. Which operator is most efficient at returning all records with a status other than ‘successful’?

OR
BETWEEN
NOT (CORRECT)
AND

.” In this way, the note operator nullifies the condition so that it gives all records with anything other than ‘successful’ placed in the status column. In effect, this is a more efficient way to omit particular values from the result set.

3. You are working with the Chinook database. You want to find the first and last names of customers who have a value in the country column of either ‘Brazil’ or ‘Argentina’. Replace –??? with the missing information to complete the query. (If you want to undo your changes to the query, you can click the Reset button.)

How many customers are from Brazil or Argentina?

5
4
1
6 (CORRECT)

Here, the condition in the WHERE clause limits the records to those profiles that will contain only customers coming from Brazil or Argentina, while the COUNT(*) function counts the number of such records.

4. While working as an analyst, you encounter a query that includes the following filter:

SELECT *
FROM customers
WHERE country = 'USA' AND state = 'NV';

What will this query return?

Information about customers who do not have a value of ‘USA’ in the country column but do have a value of ‘NV’ in the state column.
Information about customers who do not have a value of ‘USA’ in the country column or do not have a value of ‘NV’ in the state column.
Information about customers who have a value of ‘USA’ in the country column and a value of ‘NV’ in the state column. (CORRECT)
Information about customers who have a value of ‘USA’ in the country column or a value of ‘NV’ in the state column.

Use of the AND operator in this query would ensure that both conditions are true simultaneously. Hence, the country and state meet the condition of being included in the results of this query: USA and NV, respectively.

5. Which WHERE clause has the correct syntax to return all records that have a value of 5, 6, 7, or 8 in the event_id column?

WHERE event_id BETWEEN 5,8;
WHERE event_id BETWEEN 4 AND 9;
WHERE event_id BETWEEN 5 AND 8; (CORRECT)
WHERE event_id BETWEEN 4,9;

BETWEEN will therefore allow a user to query for values such that the query is applicable between both upper and lower limits (or boundaries) in this case, 5 and 8. As implied by the BETWEEN statement, an AND operator is inserted for defining the range from the first value (5) to the last (8).

6. Why might a security analyst use the OR operator?

To find all customers who are from the U.S. and do not have a certain device ID
To find the ID numbers of all employees working in either the U.S. or Canada (CORRECT)
To find all machines running both a certain operating system and a certain email client
To find all device IDs that start and end with a certain letter

The above query uses the OR operator to specify that either of these two conditions can be satisfied (for instance: working in the U.S./Canada). Thus, the query well returns employee_id of those employees who are working in the U.S. or Canada. The OR operator streamlines the recording where the country can be either ‘USA’ or ‘Canada’.

TEST YOUR KNOWLEDGE: SQL JOINS

1. Which join types return all rows from only one of the tables being joined? Select all that apply.

FULL OUTER JOIN
INNER JOIN
LEFT JOIN (CORRECT)
RIGHT JOIN (CORRECT)

TÓIS” or LEFT ON returns all rows of a table and matches the columns from the second one for joining on a specified column. If no such match is found, then NULL values appear in the columns belonging to that second table. On the other hand, a RIGHT JOIN operation returns all rows from the second table and matching rows from the first table only based on the specified column. If no match exists, NULLs populate the columns belonging to the first table.

2. You are performing an INNER JOIN on two tables on the employee_id column. The left table is employees, and the right table is machines. Which of the following queries has the correct INNER JOIN syntax?

INNER JOIN machines ON employees.employee_id = machines.employee_id
SELECT *
FROM employees;

SELECT *
FROM employees
INNER JOIN machines ON employees.employee_id = machines.employee_id; (CORRECT)

SELECT *
FROM employees
INNER JOIN machines WHERE employees.employee_id = machines.employee_id;

SELECT *
FROM employees
INNER JOIN ON employees.employee_id = machines.employee_id;

The following query has the correct syntax for the INNER JOIN:

SELECT *
FROM employees
INNER JOIN machines ON employees.employee_id = machines.employee_id;

The left table is said to be mentioned after the FROM clause and followed by the right table after the INNER JOIN clause. The use of the ON keyword indicates the column on which it will join.

3. In the following query, which join returns all records from the employees table, but only records that match on employee_id from the machines table?

SELECT *
FROM employees
_____ machines ON employees.employee_id = machines.employee_id;

FULL OUTER JOIN
RIGHT JOIN
LEFT JOIN (CORRECT)
INNER JOIN

LEFT JOIN returns all records from employees table. The only matching records from machines table are based on employee_id. Since it is put after FROM, the table considered the left table is employees.

4. As a security analyst, you are responsible for performing an INNER JOIN on the invoices and invoice_items tables of the Chinook database. These tables can be connected through the invoiceid column. Replace –??? with the missing information to complete the query. (If you want to undo your changes to the query, you can click the Reset button.)

What is the value in the trackid column of the first row that is returned from this query?

1
2 (CORRECT)
449
3

When -??? is replaced by INNER JOIN invoice_items ON invoices.invoiceid = invoice_items.invoiceid;, the query links the invoices table to the invoice_items table on the common invoiceid column . With this query, trackid will be able to return a value of 2 from the very first row.

5. If you run the following query, what will it return? Select all that apply.

SELECT *
FROM log_in_attempts
INNER JOIN employees ON log_in_attempts.username = employees.username;

SELECT *
FROM log_in_attempts
INNER JOIN employees ON log_in_attempts.username = employees.username;

Only columns from the employees table
All rows in the log_in_attempts and employees tables that match on username (CORRECT)
Only columns from the log_in_attempts table
All columns in the log_in_attempts and employees tables (CORRECT)

Fetches all the records from the two tables named log_in_attempts and employees where the column name username is equal between those two tables. It is perform by the command INNER JOIN which helps in retrieving records from these two tables based on a common column included in the clause, which in this case is the username. Select * will ensure that all columns from both log_in_attempts and employees are present in the result set.

6. What is the difference between an inner join and an outer join?

Outer joins only return rows that match on a specified column, but inner joins return all rows from both tables.
Inner joins require the keyword ON, but outer joins do not.
Inner joins only return rows that match on a specified column, but outer joins also return rows that don’t match on the specified column. (CORRECT)
Inner joins involve a left and right table, but outer joins do not.
Inner joins only return rows that match on a specified column, but outer joins also return rows that don’t match on the specified column.

MODULE 4 CHALLENGE

1. A security analyst queries a database related to security patches. How can SQL help this analyst with their work?

SQL will automatically produce a report when the patches have been installed.
The analyst can efficiently find the data they need. (CORRECT)
The analyst can directly install patches from SQL.
SQL will send out a spreadsheet about the patches.

2. What is true about the values in the primary key column? Select all that apply.

They cannot be null (or empty). (CORRECT)
Each row must have a unique value. (CORRECT)
They should never contain numeric data.
They do not need to be unique.

3. Which of these SQL statements queries the machines table? Select all that apply.

SELECT *
FROM machines; (CORRECT)

SELECT device_id, operating_system
FROM machines
WHERE operating_system = 'OS 2'; (CORRECT)

SELECT machines
FROM *;

SELECT machines
FROM operating_system;

4. What does INNER JOIN do?

Return every row in joined tables
Filter databases to return only columns that exist in every table
Combine tables and save them as a new table
Compare tables and return only the rows that have a matching value in a specified column (CORRECT)

5. Which SQL keyword indicates the condition for a filter?

SELECT
FROM
WHERE (CORRECT)
INNER JOIN

6. You work with a table that has one column for name. Some of these names have prefixes. You want to identify all of the doctors. Which query will return every name that starts with the prefix ‘Dr.’?

WHERE name = ‘Dr.%’;
WHERE name = ‘Dr._’;
WHERE name LIKE ‘Dr.%’; (CORRECT)
WHERE name LIKE ‘Dr._’;

7. You are working with the Chinook database. You want to return the company and country columns from the customers table. Replace –??? with the missing information to complete the query. (If you want to undo your changes to the query, you can click the Reset button.)

In what country is JetBrains s.r.o. located?

United States
Brazil
Czech Republic (CORRECT)
Germany

8. You are working with the Chinook database and want to filter on the hiredate column to find all employees hired on or after ‘2003-10-17’ (October 17, 2003). Replace –??? with the missing information to complete the query. (If you want to undo your changes to the query, you can click the Reset button.)

How many employees were hired on or after October 17, 2003?

Chinook database - hiredate column - Answer

4 (CORRECT)
1
3
2

9. You are working with the Chinook database and are responsible for filtering for the customers that have a value of ‘USA’ in the country column and have a value of ‘Frank’ in the firstname column. Replace –??? with the missing information to complete the query. (If you want to undo your changes to the query, you can click the Reset button.)

How many customers live in the USA and have the name Frank?

Chinook database - firstname column - Answer

3
4
1
2 (CORRECT)

10. Fill in the blank: A column in which every row has a unique entry and which is used to identify a table is called a _____.

foreign key
relational key
primary key (CORRECT)
database key

11. Which of these SQL statements queries the employees table? Select all that apply.

SELECT employees 
FROM *;

SELECT employee_id, device_id 
FROM employees 
WHERE employee_id > 1100; (CORRECT)

SELECT employees 
FROM employee_id;

SELECT * 
FROM employees; (CORRECT)

12. What type of join compares tables and returns only the rows that have a matching value in a specified column?

FULL OUTER JOIN
RIGHT JOIN
INNER JOIN (CORRECT)
LEFT JOIN

13. Which query returns all records that start with the character ‘a’ from the name column in the employees table?

SELECT name 
FROM employees 
WHERE name LIKE ‘a%’; (CORRECT)

SELECT name 
FROM employees 
WHERE name = ‘a%’;

SELECT name 
FROM employees 
WHERE name LIKE ‘%a’;

SELECT name 
FROM employees 
WHERE name LIKE ‘a’;

14. What does the following query return?

SELECT *
FROM employees
RIGHT JOIN machines ON employees.device_id = machines.device_id;

All columns of the employees and machines tables, all records from the machines table, and the records from employees that match on device_id (CORRECT)
All columns of the employees and machines tables, all records from the employees table, and the records from machines that match on device_id
All columns of the employees and machines tables and the records from employees and machines that match on device_id
All columns and records from the employees and machines tables

15. You are working with the Chinook database. You want to return the employeeid and email columns from the employees table. Replace –??? with the missing information to complete the query. (If you want to undo your changes to the query, you can click the Reset button.)

Chinook database - employeeid email column

What is the employee ID number of the employee with an email of laura@chinookcorp.com?

Chinook database - employeeid email column - Answer

2
4
6
8 (CORRECT)

16. You are working with the Chinook database and are responsible for filtering for invoices with a total that is more than 20. Replace –??? with the missing information to complete the query. (If you want to undo your changes to the query, you can click the Reset button.)

How many invoices have a total that is more than 20?

4 (CORRECT)
3
1
2

17. Why might a security analyst use SQL?

To store data in a spreadsheet
To assign new passwords to users
To create new files on their computer
To efficiently find needed data in security logs (CORRECT)

18. Both an employees table and a machines table contain an employee_id column, and you want to return only the records that share a value in this column. Which keyword should be part of your query?

FULL OUTER JOIN
WHERE
BETWEEN
INNER JOIN (CORRECT)

19. You need to perform a SQL join. You want to return all the columns with records matching on the device_id column between the employees and machines tables. You also want to return all records from the employees table. Which of the following queries would you use?

SELECT * 
FROM employees 
FULL OUTER JOIN machines ON employees.device_id = machines.device_id

SELECT *
FROM employees
INNER JOIN machines ON employees.device_id = machines.device_id;

SELECT *
FROM employees
RIGHT JOIN machines ON employees.device_id = machines.device_id;

SELECT *
FROM employees
LEFT JOIN machines ON employees.device_id = machines.device_id; (CORRECT)

20. You are working with the Chinook database and are responsible for filtering for the customers that live in the city of ‘Mountain View’ and work for the company of ‘Google Inc.’ Replace –??? with the missing information to complete the query. (If you want to undo your changes to the query, you can click the Reset button.)

How many customers live in Mountain View and work for Google Inc.?

Chinook database - Mountain View - Answer

4
1
2 (CORRECT)
3

21. What is a primary key?

The first column in every table
A column where every row has a unique entry (CORRECT)
A column that contains null values
The first row in every table

22. What does WHERE department = ‘Sales’ indicate in the following SQL query?

SELECT *
FROM employees
WHERE department = 'Sales';

To only return the department column
To highlight the department column in the results
To only return rows that match the filter (CORRECT)
To change all the values in the department column to ‘Sales’

23. You need to perform a SQL join. You want to return all the columns with records matching on the employee_id column between the employees and machines tables. You also want to return all records from the machines table. Which of the following queries would you use?

SELECT * 
FROM employees 
INNER JOIN machines ON employees.employee_id = machines.employee_id;

SELECT * 
FROM employees 
FULL OUTER JOIN machines ON employees.employee_id = machines.employee_id;

SELECT * 
FROM employees 
LEFT JOIN machines ON employees.employee_id = machines.employee_id;

SELECT * 
FROM employees 
RIGHT JOIN machines ON employees.employee_id = machines.employee_id; (CORRECT)

24. You are working with the Chinook database and are responsible for filtering for customers that live in the country of ‘USA’ and the state with an abbreviation of ‘CA’. Replace –??? with the missing information to complete the query. (If you want to undo your changes to the query, you can click the Reset button.)

Chinook database - country USA - state CA

What are the first names of the customers that live in the USA and the state with an abbreviation of CA?

Chinook database - country USA - state CA - Answer

Kathy, Michelle, Frank
John, Michelle, Julia, Patrick
Frank, Tim, Dan, Heather, Kathy
Frank, Tim, Dan (CORRECT)

25. A security analyst queries a table related to login attempts. How can SQL help this analyst with their work?

The analyst will get a live update on new login attempts.
SQL will change authentication permissions to prevent unauthorized logins.
The analyst can efficiently find the login data they need. (CORRECT)
SQL will automatically distribute a report on suspicious login attempts.

26. A security professional uses SQL to return records from a table and applies a filter to the query. Which of the following keywords would they need to use in their query? Select all that apply.

FROM (CORRECT)
SELECT (CORRECT)
WHERE (CORRECT)
ON

27. You are working with the Chinook database and are responsible for filtering for the employees with a birthdate that is on or after ‘1973-01-01’ (January 1, 1973). Replace –??? with the missing information to complete the query. (If you want to undo your changes to the query, you can click the Reset button.)

How many employees were born on or after January 1, 1973?

Chinook database - employees birthdate - Answer

3
4
1
2 (CORRECT)

28. Which query returns all records that contain the character ‘z’ from the name column in the employees table?

SELECT name 
FROM employees 
WHERE name LIKE ‘z’;

SELECT name 
FROM employees 
WHERE name LIKE ‘%z’;

SELECT name 
FROM employees 
WHERE name LIKE ‘%z%’; (CORRECT)

SELECT name 
FROM employees 
WHERE name = ‘z%’;

CONCLUSION – Databases and SQL

This module prepares participants to understand SQL to communicate efficiently with databases. With practical sessions and hands-on experiences, students have been able to acquire skills essential for using SQL commands to navigate and manipulate databases effectively. Students have learned to query the data, filter the results, and join tables, allowing them to use these skills in the real-world context.

During this stage, students can use their newfound skills and knowledge in data analytics to improve their overall competencies within the discipline. This module serves as a starting point for anyone who wants to leverage SQL in their quest to base their decision-making and analyses on data.