INTRODUCTION – Databases and SQL

TEST YOUR KNOWLEDGE: SQL AND DATABASES

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

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

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

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

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

TEST YOUR KNOWLEDGE: SQL QUERIES

1. What is filtering in SQL?

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?

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?

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

5. Which columns will SELECT * return?

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’?

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)?

2. Which operator is most efficient at returning all records with a status other than ‘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?

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?

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?

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

TEST YOUR KNOWLEDGE: SQL JOINS

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

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?

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;

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?

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;

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

MODULE 4 CHALLENGE

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

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

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?

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

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.’?

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?

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?

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?

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 _____.

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?

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;

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?

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?

17. Why might a security analyst use SQL?

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?

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.?

21. What is a primary key?

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

SELECT *
FROM employees
WHERE department = '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?

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

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.

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?

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