tools of the trade linux and sql coursera weekly challenge 4 answers

Test your knowledge: SQL and databases

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

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

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

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

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

  • True
  • False

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

  • query
  • log
  • key
  • row

Test your knowledge: SQL queries

5. What is filtering in SQL?

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

6. 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
  • +1 (780) 836-9987
  • +1 (403) 467-3351

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

  • WHERE country  = ‘Canada’

    SELECT *

    FROM log_in_attempts;

  • SELECT *

    FROM log_in_attempts

    WHERE country  = ‘Canada’;

  • SELECT WHERE country = ‘Canada’

    FROM log_in_attempts;

  • SELECT *

    FROM log_in_attempts

    WHERE country = Canada;

8. Which pattern matches with any string that starts with the character 'A'?

  • ‘%A%’
  • ‘%A’
  • ‘A%’
  • ‘A’

Test your knowledge: More SQL filters

9. 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′ AND ’01-04-2015′;
  • WHERE date BETWEEN ’01-01-2015′, ’01-04-2015′;
  • WHERE date < ’01-04-2015′;
  • WHERE date > ’01-01-2015′;

10. Which operator is most efficient at returning all records with a status other than 'successful'?

  • OR
  • NOT
  • BETWEEN
  • AND

11. 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
  • 6
  • 1
  • 4

12. 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 have a value of ‘USA’ in the country column and a value of ‘NV’ in the state column.
  • 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 have a value of ‘USA’ in the country column or 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.

Test your knowledge: SQL joins

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

  • RIGHT JOIN
  • INNER JOIN
  • FULL OUTER JOIN
  • LEFT JOIN

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

  • 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;

  • 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;

15. 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
  • INNER JOIN
  • LEFT JOIN

16. 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
  • 3
  • 2
  • 449

Weekly challenge 4

17. Why might a security analyst use SQL?

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

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

  • primary key
  • database key
  • foreign key
  • relational key

19. Which of these SQL statements queries the log_in_attempts table? Select all that apply.

  • SELECT *

    FROM log_in_attempts;

  • SELECT event_id, username

    FROM log_in_attempts

    WHERE event_id < 150;

  • SELECT log_in_attempts

    FROM *;

  • SELECT log_in_attempts

    FROM event_id;

20. What does INNER JOIN do?

  • 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
  • Filter databases to return only columns that exist in every table
  • Return every row in joined tables

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

  • FROM
  • SELECT
  • INNER JOIN
  • WHERE

22. 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 LIKE ‘Dr.%’;
  • WHERE name = ‘Dr.%’;
  • WHERE name = ‘Dr._’;
  • WHERE name LIKE ‘Dr._’;

23. 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 table and the records from  employees and machines that match on device_id
  • All columns and records from the employees and machines tables
  • All columns of the employees and machines table, all records from the employees table, and the records from machines that match on device_id
  • All columns of the employees and machines table, all records from the machines table, and the records from employees that match on device_id

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

  • Germany
  • Czech Republic
  • Brazil
  • United States

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

  • 2
  • 4
  • 1
  • 3

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

  • 3
  • 2
  • 4
  • 1

27. 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. 
  • The analyst can efficiently find the login data they need.
  • SQL will change authentication permissions to prevent unauthorized logins.
  • SQL will automatically distribute a report on suspicious login attempts.

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

  • SELECT *

    FROM machines;

  • SELECT device_id, operating_system

    FROM machines

    WHERE operating_system = ‘OS 2’;

  • SELECT machines

    FROM *;

  • SELECT machines

    FROM operating_system;

29. What does WHERE department = 'Sales' indicate in the following SQL query?

SELECT *

FROM employees

WHERE department = 'Sales';

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

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

    LEFT 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

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

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

  • 8
  • 2
  • 6
  • 4

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

  • 4
  • 1
  • 3
  • 2

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

    RIGHT 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

    FULL OUTER JOIN machines ON employees.device_id = machines.device_id

  • SELECT *

    FROM employees

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

34. You are working with the Chinook database. You want to return the lastname and title 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 title of the employee with the last name of Callahan?

  • IT Manager
  • IT Staff
  • Sales Manager
  • General Manager

35. 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
  • 2
  • 3
  • 1

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

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

37. 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
  • INNER JOIN
  • BETWEEN
  • WHERE

38. 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 = ‘a%’;

  • SELECT name

    FROM employees

    WHERE name LIKE ‘%a’;

  • SELECT name

    FROM employees

    WHERE name LIKE ‘a%’;

  • SELECT name

    FROM employees

    WHERE name LIKE ‘a’;

Leave a Reply