Module 5 Quiz

Which of these are true about NULL values? Select all that apply.

A NULL value is different from a zero value or an empty string. One NULL is never equal to another.

Your study group has access to the databases of some organizations working on substance abuse treatment and rehabilitation. For an assignment, your professor asks you to query one such database in particular a table, Opioid Addiction, that exists in a database. This table provides the estimated percentage of opioid addiction for each New England state in the population and the year of record. StateYearPercentageA20160.024B20170.032C20160.043D20180.034E20160.036F20180.066 MySQL query statements to produce different data from this table are listed below as are the resultant data themselves. Match each query statement to the correct data. Instructions: Choose your responses from the drop-down menus below. Response options cannot be used more than once.

1.SELECT MIN (Year) FROM Opioid Addiction - 2016 2.SELECT MAX (Percentage) FROM Opioid Addiction- 0.066 3. SELECT COUNT (*) FROM Opioid Addiction WHERE Percentage <0.04- 4 4. SELECT AVG (Percentage) FROM Opioid Addiction - 0.039

When a table is in third normal form, which of these is true? Select one

All non-key columns depend on the whole primary key and on no other columns.

True or false: A logical operator always evaluates to NULL when one or both operands are NULL.

False

Denormalization intentionally introduces redundancy by merging tables and therefore improves query performance. Which types of normal form tables does it result in? Select all that apply.

First or Second Form

All study groups in your cohort have access to the database of a fantasy trading app for fans of cryptocurrency. For an assignment, your professor asks all groups to query its database, in particular a table titled CurrencyCounter. This table provides the highest estimated percentage of all players that will choose to play in each of three cryptocurrencies and its per-token value in United States dollars (USD) for the next year. CurrencyCounter CryptocurrencyPerTokenValue (USD)PercentageEthereum9530BitcoinCash10135Litecoin4510ZCash6310Monero7415 Another study group has produced these results from their query: Litecoin and ZCash. Which of the following is their query? Select one.

SELECT * FROM CurrencyCounter WHERE (Percentage >= 10 AND Percentage <= 30) AND PerTokenValue < 65

You are analyzing data for a new sports stats website and have access to their databases related to the Major League Baseball (MLB) games. A coworker has created the table Attendance, which estimates the average attendance at home games next season for franchises in each division of the National League (NL) and the American League (AL). League Division Average NL East 41,253 NL West 36,857 NL Central 37,625 AL East 45,303 AL West 39,758 AL Central 32,506 Suppose you need to extract a list of the highest average attendance for each league and each division in it, then order it by league and then division. Which of the following should be your query? Select one.

SELECT League, Division, MAX (Average) FROM Attendance GROUP by League, Division ORDER by League, Division

In a Jeopardy-style online quiz you are playing, the "answers" to the bonus points question on normal forms are listed as the following: "All non-key columns must depend on the whole primary key." "A table with a simple primary key is automatically in this form." "This form does not eliminate all redundancies." To which normal form do these answers apply? Select one.

Second normal form

You and a classmate are interning with a fantasy games developer that markets mobile games related to the National Football League (NFL). Their database contains a table, SuperBowlStats, that provides details about the winning team names that were most common among their players and the number of players who chose that name in the previous year. SuperBowlStats Franchise NameCityGamePlayersNoblesBoston41,050WombatsSt. Louis35,327PacersCleveland47,738MaraudersChicago33,672 For a data analytics project, your classmate has generated several queries to return results of various types. The queries and their results are listed. Match each query to its result. Instructions: Choose your responses from the drop-down menus below. Response options cannot be used more than once.

SELECT REPLACE ("St. Louis", "St.", "Saint") - Saint Loius SELECT SUBSTRING ("St. Louis", 1, 3) - St. SELECT CONCAT (FranchiseName, City) FROM SuperBowlStats WHERE GamePlayers > 45,000 - PacersCleveland SELECT CONCAT (City, FranchiseName) FROM Attendance WHERE GamePlayers < 35,000 - SELECT ChicagoMarauders

Four properties of the LIKE operator that is used together with the WHERE clause to match text against a pattern are listed below, as are the specific uses of the property. Match the property with its use. Instructions: Choose your responses from the drop-down menus below. Response options cannot be used more than once.

The wildcard character _ - to match exactly one character The wildcard character % - to match any number of characters LIKE by default - LIKE by default LIKE followed by BINARY- LIKE followed by BINARY