Course 4 – TOOLS OF THE TRADE: LINUX AND SQL
Module 4: Databases and SQL
GOOGLE CYBERSECURITY PROFESSIONAL CERTIFICATE
Coursera Study Guide
INTRODUCTION – Databases and SQL
In this overview, participants will engage in hands-on exercises to develop proficiency in utilizing SQL for effective communication with databases. The module delves into the practical aspects of querying databases, emphasizing the essential skill of filtering results to extract relevant information. Participants will also explore the power of SQL in joining multiple tables, gaining valuable insights into how this capability enhances the complexity and depth of database queries.
Through a series of structured exercises, learners will not only understand the theoretical underpinnings of SQL but will also acquire practical skills that are directly applicable in real-world scenarios. The focus on practical application ensures that participants can confidently navigate the intricacies of database interactions using SQL. As participants progress through this overview, they will not only gain a theoretical understanding of SQL’s capabilities but also develop the hands-on expertise required to effectively query and manipulate databases, making this module a vital component in their journey toward proficiency in data analytics and related fields.
Learning Objectives
- Discuss how SQL is used within the security profession.
- Describe how a relational database is organized.
- Use SQL to retrieve information from a database.
- Apply filters to SQL queries.
- Use SQL joins to combine multiple tables into a query.
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.
A relational database is a structured database containing tables that are related to each other through primary and foreign keys.
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
SQL is a programming language used to create, interact with, and request information from a database. SQL’s filtering can be used to find data to support security-related decisions.
3. A record of attempts to connect to an organization’s network is one example of a log.
- True (CORRECT)
- False
A record of attempts to connect to an organization’s network is one example of a log. Logs are records of events that occur within an organization’s systems.
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 request for data from a database table or a combination of tables is called a query.
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
Security analysts commonly use SQL to find relevant information to support cybersecurity-related decisions.
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 is selecting data that match a certain condition. Analysts use filters in SQL to return the data they need.
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
Andrew Adams’ phone number is +1 (780) 428-9482. By replacing –??? with SELECT firstname, lastname, phone, you can complete the query and return this result.
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;
The security analyst can use the following query to filter the log_in_attempts table for records where the value in the country column is ‘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)
The percentage sign (%) is a wildcard that substitutes for any number of other characters. The pattern ‘A%’ matches with any string that starts with the character ‘A’.
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
SELECT * instructs SQL to return all columns from the specified 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)
WHERE username LIKE ‘a%’; contains the correct syntax to return all records that contain a value in the username column that starts with the character ‘a’. The LIKE operator is used with WHERE to search for a pattern in a column. The % wildcard substitutes for any number of other characters.
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)
The filter WHERE date BETWEEN ’01-01-2015′ AND ’01-04-2015′; outputs all records with values in the date column between ’01-01-2015′ and ’01-04-2015′.
2. Which operator is most efficient at returning all records with a status other than ‘successful’?
- OR
- BETWEEN
- NOT (CORRECT)
- AND
NOT is most efficient at returning all records with a status other than ‘successful’. The NOT operator negates a condition. In this case, it can be used in a filter of WHERE NOT status = ‘successful’;.
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)
6 customers are from Brazil or Argentina. By replacing –??? with WHERE country = ‘Brazil’ OR country = ‘Argentina’;, you can complete the query and return this result.
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.
The query returns information about customers who have a value of ‘USA’ in the country column and a value of ‘NV’ in the state column. The AND operator specifies that both conditions must be met simultaneously.
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;
WHERE event_id BETWEEN 5 AND 8; returns all records that have a value of 5, 6, 7, or 8 in the event_id column. The BETWEEN operator filters for values within a range. The BETWEEN operator is placed before the first value to be included in the range. This is followed by the AND operator and the last value to be included in the range.
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
A security analyst might use the OR operator to find the ID numbers of all employees working in either the U.S. or Canada. The OR operator specifies that either condition can be met.
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)
LEFT JOIN and RIGHT JOIN return all rows from only one of the tables being joined. LEFT JOIN returns all the records of the first table, but only returns rows of the second table that match on a specified column. 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.
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;
It specifies the left table after FROM, then specifies the right table after INNER JOIN, and then uses the correct syntax after ON when indicating the column to join on.
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 the employees table, but only records that match on employee_id from the machines table. Because it is located after FROM, the employees table is the left table.
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
2 is the value in the trackid column of the first row returned from this query. By replacing –??? with INNER JOIN invoice_items ON invoices.invoiceid = invoice_items.invoiceid;, you can complete the query and return this result.
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)
This query will return all rows in the log_in_attempts and employees tables that match on username and all columns in the log_in_attempts and employees tables. INNER JOIN returns rows matching on a specified column that exists in more than one table. It returns all columns that are indicated following the SELECT keyword. In this case, SELECT * indicates to return all columns.
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?
- 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?
- 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.)
What is the employee ID number of the employee with an email of laura@chinookcorp.com?
- 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.?
- 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.)
What are the first names of the customers that live in the USA and the state with an abbreviation of CA?
- 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?
- 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 provides participants with a comprehensive understanding of SQL and its pivotal role in effective database communication. Through practical exercises and hands-on experiences, learners have acquired the essential skills needed to navigate and manipulate databases using SQL commands. The emphasis on querying, filtering results, and joining tables has equipped participants with practical insights that are directly applicable in real-world scenarios.
As students move forward, they are well-positioned to apply their newfound knowledge and skills in data analytics, contributing to their overall proficiency in this critical domain. This module serves as a solid foundation for participants seeking to leverage SQL effectively in their journey towards data-driven decision-making and analysis.
Subscribe to our site
Get new content delivered directly to your inbox.
Quiztudy Top Courses
Popular in Coursera
- Google Advanced Data Analytics
- Google Cybersecurity Professional Certificate
- Meta Marketing Analytics Professional Certificate
- Google Digital Marketing & E-commerce Professional Certificate
- Google UX Design Professional Certificate
- Meta Social Media Marketing Professional Certificate
- Google Project Management Professional Certificate
- Meta Front-End Developer Professional Certificate
Liking our content? Then, don’t forget to ad us to your BOOKMARKS so you can find us easily!