Database features

Question 1

If you create a database table and include a primary key in the table, what must you ensure? Select all that apply.

• The primary key has a numeric value
• The primary key is unique
• The primary key's value isn't null or blank
• The primary key isn't a foreign key in another table

Explain: If you create a database table with a primary key, it must be unique and its value must not be null or blank.

Question 2

A table in a relational database can have only one foreign key. True or False?

A. True

B. False

Explain: A table in a relational database is allowed to have multiple foreign keys.

Using metadata as an analyst

What can a data analyst achieve more easily with a metadata repository? Select all that apply.

• Verify that data from an outside source is being used appropriately
• Confirm how or when data was collected
• Identify trustworthy third-party data providers
• Bring together multiple sources of data

Explain: Using a metadata repository, a data analyst can find it easier to bring together multiple sources of data, confirm how or when data was collected, and verify that data from an outside source is being used appropriately.

Importing data from spreadsheets and databases

The WHO data repository may change from time to time. Go to https://www.who.int/data/gho for the latest information and data.

Medical doctors: https://apps.who.int/gho/data/node.main.HWFGRP_0020?lang=en

Fill in the blank: CSV files use plain text and are _____ by characters, such as a comma.

A. detailed

B. described

C. delineated

D. defined

The correct answer is C. delineated. Explain: CSV files use plain text and are delineated by characters, such as a comma. A delineator indicates a boundary or separation between two things.

Sorting and Filtering

Would you like to follow along with the instructor using the same spreadsheet? To use the template for the spreadsheet, click the link below and select "Use Template."

Link to template: Sales Rep Cities, States, and Parts

OR

If you don't have a Google account, you can download the spreadsheet directly from the attachment below

The menu option has slightly changed. Select Data and Sort range, and then choose Advanced range sorting options to view the dialog box shown.

How to use BigQuery

Follow these steps to find and pin the bigquery-public-data.

1. Navigate to the Explorer menu in BigQuery.
2. Type the word public in the search box and enter. 3 Click "Broaden search to all projects"
3. Find the bigquery-public-data and pin it.

The NOAA Lighting Strike dataset is no longer available to all users. You will not need this specific dataset for future activities or quizzes. However, the same process for finding and using a public dataset still applies.

Link datasets: https://console.cloud.google.com/marketplace/details/noaa-public/lightning

Double-check that you have pinned public datasets to your Explorer pane, if it isn't listed, search by name for it.

BigQuery has updated its interface. Instead of clicking the three-dot menu, open the dataset and select the Create Table icon.

BigQuery in action

In an existing company database, the customers table contains the following columns: CustomerId, FirstName, LastName, Company, Address, City, State, Country, PostalCode, Phone, Fax, Email, and SupportRepId.

Create a query to return all the columns in the customer table for only customers in Germany.

SELECT * FROM customers WHERE Country = 'Germany';

Output:

+------------+-----------+------------+---------+-------------------------+-----------+-------+---------+------------+------------------+------+---------------------------+--------------+
| CustomerId | FirstName | LastName   | Company | Address                 | City      | State | Country | PostalCode | Phone            |  Fax | Email                     | SupportRepId |
+------------+-----------+------------+---------+-------------------------+-----------+-------+---------+------------+------------------+------+---------------------------+--------------+
|          2 | Leonie    | Köhler     |    None | Theodor-Heuss-Straße 34 | Stuttgart |  None | Germany | 70174      | +49 0711 2842222 | None | [email protected]     |            5 |
|         36 | Hannah    | Schneider  |    None | Tauentzienstraße 8      | Berlin    |  None | Germany | 10789      | +49 030 26550280 | None | [email protected] |            5 |
|         37 | Fynn      | Zimmermann |    None | Berger Straße 10        | Frankfurt |  None | Germany | 60316      | +49 069 40598889 | None | [email protected]      |            3 |
|         38 | Niklas    | Schröder   |    None | Barbarossastraße 19     | Berlin    |  None | Germany | 10779      | +49 030 2141444  | None | [email protected]       |            3 |
+------------+-----------+------------+---------+-------------------------+-----------+-------+---------+------------+------------------+------+---------------------------+--------------+

What is the last name of the customer in the second row of the results returned from the query? It is Schneider

Explain: Great job. Schneider is the last name of the customer in the second row returned when making the following query:

SELECT * FROM customers WHERE Country = 'Germany'