itm sql exam
Heather is writing a query in SQL. One of the fields she wants to filter in a where is for countries that start with the letter 'A' end with the letter 's' and are at least 3 characters long. Fill in the blank for how she would complete the where statement in her query: where customer.country like (BLANK)
A_%s
text in a novel is an example of structured data? -true -false
-false
Did we make a profit last year?
descriptive analytic
Which of the following is decreased when using a relational database? -Scalability -Security - Information redundancy -Flexibility
- Information redundancy
In the following, the table inventory has fields for the quantity available for sale (inventory quantity), the inventory item's color (inventory.color) and whether the item is on sale (inventory. onSale) as a "Yes" or "No". where (inventory.color="red" or inventory.quantity = 2) and inventory.onSale = "Yes" Which of the following would describe the logic? - The results would include products that that are red and on sale or products with a quantity of 2 that are on sale. -The results would include products where the quantity is 2, the color is red and they are on sale. -The results would include products where the color is red and the quantity is 2, or products that are on sale. -The results would include products that are red and on sale, or products that have a quantity 2.
- The results would include products that that are red and on sale or products with a quantity of 2 that are on sale.
Which wildcard would you use that allows you to select all the columns in a selected table? -* -# -% -&
-*
Robert is writing a query to pull data from two tables. One table (games) contains a list of video games. The field games.gameID is the primary key of games and the field highScores.gameID is the foreign key in the highScores table. The other table (highScores) contains player high scores. He has written the following in his query so far: SELECT games.title, games.releaseDate, games.platform, highScores.score, highScores.player FROM schema.games ....... WHERE highScores.score > 100000 He wants his query to pull all the games that have a corresponding high score in the table highScores. What would be the join statement below that Robert would utilize? ---outer join schema.highScores on highscores.gameID = games.gameID ----right join schema.highScores on highscores.gameID = games.gameID ---inner join schema.highScores on highScores.gameID = games.gameID ----inner join schema.highScores having highScores.gameID = games.gameID ---inner join schema.highScores where highScores.gameID = games.gameID ----left join schema.highScores having highscores.gameID = games.gameID ----left join schema.highScores on highscores.gameID = games.gameID
---inner join schema.highScores on highScores.gameID = games.gameID
How many rows will you return in the following statement of code: select count(consultants.ID) from teleworks.consultants; -1 -Greater than 1 row
-1
Each cell phone has one phone number. Each phone number can only be used on a single cell phone. What type of relationship does a cell phone have to a phone number? -n:m -1:1 -n:1 -n:n -1:n
-1:1
Zara is ready to buy a house, so she decides to hire a real estate agent from East Lansing Realty. Each real estate agent works with several customers and the agent receives a commission on the sale of each house. However, East Lansing Realty has a policy that each customer can be assigned to no more than one real estate agent. This is an example of what type of relationship between customer and agent? -0:1 -1:1 -1:n -n:1 -n:m
-1:n
A customer can have multiple orders, however each order can only have a single customer. What kind of relationship is there between customers and orders and how many tables are needed for the relationship? -m:n with 4 tables -1:1 with 2 tables -1:n with 3 tables -1:n with 2 tables -m:n with 3 tables -m:n with 2 tables
-1:n with 2 tables
What designation is last in a SQL query to indicate the end of the code for that query? For example, what would you put at the end of the following query to signify the end of the code for that query: select customers.name, customers.address from sales.customers where customers.state = 'MI' -; -* -# -EOF
-;
For every student at MSU, they have a unique identifying number which is known as their APID. What would the APID likely be used for in a: -A primary key -The table name -A foreign key -A concatenated key
-A primary key
Which command allows you to add a label for the column title rather than the field name? -AS -HAVING -WHERE -LIKE
-AS
If you saw the following SQL code, which of the following statements is true? select table1.fieldA, table1.fieldB, table2.fieldZ, table2.fieldY from schema.table1 right join schema.table2 on table1.primaryKey = table2.foreignKey; -All records from table2 will be included in the results, regardless of whether there are related records in table1 -All records from table1 will be included in the results, regardless of whether there are related records in table2 -All records from table 1 and table 2 will be included in the results, regardless of whether there are related records in either table -Only records where there are related records between table1 and table will be included in the results
-All records from table2 will be included in the results, regardless of whether there are related records in table1
Which of the following would be an example of prescriptive analytics? -An energy company analyzing the top reasons for power outages over the past 5 years -Companies forecasting earnings based upon prior period performance and correlation to market indicators -Airlines using past trends to predict fuel consumption for the next year -Amazon utilizing an algorithm to identify what product to advertise to you based upon your purchase history when you
-Amazon utilizing an algorithm to identify what product to advertise to you based upon your purchase history when you
The columns in a table represent which of the following about an entity described in the table? -Relationship -Data Dictionary -Metadata -Attributes
-Attributes
Joe has a document that contains all of the metadata about the data elements in a database (relationship rules). This would be an example of which of the following? -Data Model -Data Element -Data Dictionary -Data Aggregation
-Data Dictionary
The difference between a data lake and a data warehouse is: -Data warehouses typically contain live production data. Data lakes contain a copy of the production data. -Data warehouses contain data from multiple sources and data lakes collect data from only a single source. -Data lakes and data warehouses are the same. The terms are used interchangeably. -Data lakes store data in 'raw' form (i.e. the original form). Data warehouses typically store data that has been extracted, transformed and loaded (ETL) for purposes of performing analysis.
-Data lakes store data in 'raw' form (i.e. the original form). Data warehouses typically store data that has been extracted, transformed and loaded (ETL) for purposes of performing analysis.
Data was previously stored in a flat format. What was one disadvantage to flat databases? -Broken relationships -Data aggregation -Key identification -Data redundancy
-Data redundancy
While going through the newspaper, you find a chart showing the historical rates of infections for various viruses worldwide. This type of visualization is an example of which of the following types of analytics? -Predictive -Prescriptive -Pro-forma -Descriptive
-Descriptive
Which of the following keywords when used in a SQL select statement will remove duplicate records from the results? -Remdup -Discrete -Distinct -Unique
-Distinct
In a SQL query, if - ...........You were averaging grade points from a table of students grades for each of the classes they took (field studentClass.grade) and ...........Want to list those equal to or above a 3.0 You would utilize the following line in your query: where avg(studentClass.grade) >= 3.0 -True -False
-False
On a ranch, a person can own many horses. However, each horse is owned by a single person. What type of relationship do we see here? -One to Many (1:n) -One to One (1:1) -Many to Many (m:n)
-One to Many (1:n)
If a person can own multiple cars, but each car can only have one owner, what is the type of relationship between cars and owners? -Many-to-Many -One-to-Many -One-to-One
-One-to-Many
Which of the following is true about the following SQL statement selecting data collected by the census (table census in a schema named 'us'), where:- name is the individual's name- income is the individual's annual income- city is the city the individual resides in- age is the age of the individual select census.name, avg(census.income), census.city, census.age from us.census where census.age > 50 group by census.city having avg(census.income) >= 35000 order by avg(census.income) DESC Which of the following are true (select ALL that apply)? -Only cities where the average incomes (with age filter applied) are equal to or above 35000 are listed -The query lists individuals 50 and over -Only cities with an average age over 50 will be listed -The city with the highest average income based upon the query will be listed first
-Only cities where the average incomes (with age filter applied) are equal to or above 35000 are listed -The city with the highest average income based upon the query will be listed first
Which of the following describes a scenario in which a concatenated key would be utilized? -Tables where one item in the table can relate to only one item in a corresponding table (i.e. one to one relationship) -Tables where only a combination of two attributes combined can make a unique identifier for rows (tuples) in a table -Tables where one item in the table can relate to multiple items in a corresponding table (i.e. one to many relationship) -Tables where there are no foreign keys to identify unique records.
-Tables where only a combination of two attributes combined can make a unique identifier for rows (tuples) in a table
When implementing a 1:n relationship, which of the following is true? -The primary key of both tables will be the same value -A concatenated key will always be needed -A third table is necessary to create the relationship -The primary key of one table will appear as a foreign key in the other table
-The primary key of one table will appear as a foreign key in the other table
When implementing a 1:n relationship, which of the following is true? -The primary key of each table is put into each of the corresponding tables. -The primary key of each table will be put into a third table as a concatenated key. -The primary key for the many (n) table will be null. -The primary key of one table would be put in the other table as a foreign key
-The primary key of one table would be put in the other table as a foreign key
Esther wrote the following code. Which of the following statements would describe the relationships between the tables, where the 'widg' table contains widgets and the 'cust' table contains customers: select cust. fname, cust. Iname, cust.address, widg.color. widg.length from db.cust inner join db.widg on widg.widgID = cust.widgID where widg.color = 'cyan'; -The relationship is a one to many. A customer may have multiple widgets. A widget may only have one customer. v6 -The relationship is a one to many. A widget may have multiple customers. A customer may only have one widget -The relationship is a many to many. A customer may have multiple widgets. A widget may have multiple customers. -None of the above describe the relationship between widgets and customers.
-The relationship is a one to many. A widget may have multiple customers. A customer may only have one widget
Which of the following is true regarding this query: select customer.name, payment.date, payment.amount from customer left join payment on customer.customerID = payment.customer|D order by customer.name asc, payment.date desc; --The results will be ordered by payment date and then by customer name (if two customers had payments on the same day) ----The results will only show customers who have made payments. ------The results will show all customers, even if they have not made any payments. ---------Customer John Adams will appear in the results before customer Jim Beasley.
-The results will show all customers, even if they have not made any payments.
A null value means: -The value is 0. -A field can have multiple values. -A data set which the sum of the fields adds to null. -The value is unknown or does not exist.
-The value is unknown or does not exist.
Text in a novel is an example of unstructured data. -True -False
-True
When you write a string in a piece of SQL code to label your column heading, you will always need to use apostrophe (' ' ) or quotation (" ") around it? -True -False
-True
Which of the following describes the veracity characteristic of big data? -The analysis of data as it travels around the Internet -Massive volume created by machines and networks -Uncertainty and or untrustworthiness of data -Different forms of structured and unstructured data
-Uncertainty and or untrustworthiness of data
Bill is writing a query that involves location information. He has a field for country and wants to filter for only those in the United States (US). Which of the following would he use to only include items that have US as the country? -WHERE -FROM -ORDER BY -HAVING
-WHERE
Bill is writing a query that involves location information. He has a field for country and wants to filter for only those in the United States (US). Which of the following would he use to only include items that have US as the country? -ORDER BY -WHERE -HAVING -FROM
-WHERE
When would you utilize the like operator in a SQL query? -When performing a right join between two tables -When you are sorting by a specific pattern -When performing a query that has tables that are one-to-many. -When you are trying to match / filter for a pattern using wildcards
-When you are trying to match / filter for a pattern using wildcards
Which wildcard allows you to look for a single character in a range? -* -_ -% -[]
-[]
When performing an order by statement in SQL, what is the default ordering if you do not specify? -asc (ascending) -desc (descending) -n/a - it will error if you do not specify as (ascending) or desc (descending)
-asc (ascending)
If the following join statement is used to join two tables in a query, which of the following tables would all of the tuples in the relation appear in results? full outer join schema.customers on invoices.customer|D = customers. customer|D -both schema and invoices -customers -invoices -both customers and invoices
-both customers and invoices
If the following join statement is used to join two tables in a query, which of the following tables would all of the tuples in the relation appear in results? left outer join university .students on class.classID = students.classID -university -none of the choices -class -both university and class
-class
Which aggregation function shows the number of records that meet a set of criteria? -avg -distinct -countof -count
-count
If the following join statement is used to join two tables in a query, which of the following tables would all bf the tuples in the relation appear in results? right outer join schema.customers on invoices.customerID = customers.customerID -schema -customers -invoices -customerID
-customers
There are two tables: lake and waterTemperature The tables track lakes and water temperature readings. Each lake has a unique name (lakeName). Each water temperature reading applies to an individual lake at a specific date and time. Water temperature readings are identified by a unique identifier assigned to each reading (reading|D). They reside in a schema/database in MySQL called geo. Based upon the information above, which of the following segments of code could be applied to queries against these tables? -from geo.lake inner join geo.waterTemperature on waterTemperature.readingID = lake. lakeName -from geo.lake inner join geo.waterTemperature on waterTemperature.lakeName = lake.lakeName -from geo.lake inner join geo.waterTemperature as waterTemperature.readingID = lake.readingID -from geo.lake inner join geo.waterTemperature as waterTemperature.lakeName = lake.lakeName -from geo.lake inner join geo.waterTemperature on waterTemperature.readingID = lake.reading|D
-from geo.lake inner join geo.waterTemperature on waterTemperature.lakeName = lake.lakeName
Emily is working with a large data set in SQL. As she completes a query, she notices several redundant rows returned. If you are a coworker of Emily's, what SQL function could you suggest to Emily to reduce duplicates from her query results to only return unique rows of data? -where unique -group by unique -group by distinct -select unique -select distinct
-group by distinct
Jacyln is writing a query that sums the profit by business line for her organization. For the sum to performed against each business line, she would use which of the following to specify the level of aggregation that sum should be executed against? Note: The field for business line is table.businessLine and the field for profit is table.profit. -having sum(table.profit) by table.businessLine -having table.businessLine DESC -group by table.businessLine -group by sum(table.profit)
-group by table.businessLine
Robert is writing a query to pull data from two tables. One table (games) contains a list of video games. The field games.gamelD is the primary key of games and the field highScores.gamelD is the foreign key in the highScores table. The other table (highScores) contains player high scores. He has written the following in his query so far: SELECT games.title, games.releaseDate, games.platform, highScores.score, highScores.player FROM schema.games WHERE highScores.score > 100000 He wants his query to pull all the games, even if there are no high scores listed in the table highScores. What would be the join statement below that Robert would utilize? -left join schema.highScores having highscores.gamelD = games. gamelD -inner join schema.highScores having highScores.gamelD = games.gamelD -inner join schema.highScores where highScores.gamelD = games.gamelD -inner join schema.highScores on highscores.gamelD = games.gamelD -left join schema.highScores on highscores.gamelD = games.gamelD -outer join schema.highScores on highscores.gamelD = games.gamelD -right join schema.highScores on highscores.gamelD = games.gamelD
-left join schema.highScores on highscores.gamelD = games.gamelD
Each order has multiple products. Each product can be sold on multiple orders. What kind of relationship is there between products and orders? -n:m -1:1 -n:1 -m:n -1:n
-m:n
A person can own multiple homes. Each home can be owned by multiple individuals. What type of relationship does individuals have with homes and how many tables will there be in the data model? -m:n with 4 tables -1:1 with 2 tables -1:n with 3 tables -1:n with 2 tables -m:n with 3 tables -m:n with 2 tables
-m:n with 3 tables
Jeff is creating a database to track TV shows and actors. Actors can be in multiple shows and shows can have multiple actors. Which of the following is the type of relationship and number of tables needed to implement this in a relational database? -1:n & 3 tables -1:n & 2 tables -1:1 & 2 tables -n:m & 2 tables -n:m & 3 tables
-n:m & 3 tables
The operator like in a SQL statement is used for: -joining two attributes in the results -finding where foreign keys equal primary keys -pattern matching -comparing the contents of two tables
-pattern matching
What type of join(s) would you want to use to find all the information that comes from one table and only the matched records from the other table? -inner join -left join -full outer join -right join
-right join -left join
what is the correct order of sql statements?
-select -from -join (inner/right/outer/left) -where -group by -having -order by
What SQL statement could he use to remove the duplicate rows so that each unique row only shows once? -select only -show distinct -select distinct -select unique -show unique
-select distinct
When is having used instead of where? -None of the above -when groups are present through the use of an aggregate function (such as avg, count, etc.) and conditions need to be applied to the groups -when a relationship in the database is not specified using keys -when pattern matching is the only way to identify a tuple
-when groups are present through the use of an aggregate function (such as avg, count, etc.) and conditions need to be applied to the groups
Pranav wants to filter the results of the query below to only show records that have had total sales (calculation sum(sales.Amount) greater than 100,000 Euros. Fill in the blank for the clause (one word) that he would need to add to his code to complete it. select empl.name, empl.region, empl.country, sum(sales.Amount) as 'Total Sales' from crm.empl inner join sales on empl.emplID = sales.emplID group by empl.emplID (BLANK) sum(sales.Amount) >= 100000 order by sum(sales.Amount) desc;
HAVING
There are two tables: lake and waterTemperature The tables track lakes and water temperature readings. Each lake has a unique name (lakeName). Each water temperature reading applies to an individual lake at a specific date and time. Water temperature readings are identified by a unique identifier assigned to each reading (readingID). They reside in a schema/database in MySQL called geo. Based upon the information above, which of the following segments of code could be applied to queries against these tables? -from geo.lake inner join geo.waterTemperature as waterTemperature.lakeName = lake.lakeName -from geo.lake inner join geo.waterTemperature on waterTemperature.readingID = lake.lakeName -from geo.lake inner join geo.waterTemperature on waterTemperature.lakeName = lake.lakeName -from geo.lake inner join geo.waterTemperature on waterTemperature.readingID = lake.readingID -from geo.lake inner join geo.waterTemperature as waterTemperature.readingID = lake.readingID
from geo.lake inner join geo.waterTemperature on waterTemperature.lakeName = lake.lakeName
what will our sales be like next quarter?
predictive analytic
should we make or buy a part for our machines?
prescriptive analytic
avg()
returns the average value of a numeric column
max()
returns the largest value of the selected column
count()
returns the number of rows that matches a specific criterion
min()
returns the smallest value of the selected column
sum()
returns the total sum of a numeric column
Alex is conducting a query against a database of customers. The query counts the number of customers in each city [count(customers.customerID) ]. There is also a field for state (ex. MI). He wants to filter for cities that have over 10 customers in Michigan. Which of the following would be the correct code for him to include in his query? -group by customers.citywhere customers.state = 'MI' and count(customers.customerID) > 10 -where count(customers.customerID) > 10 group by customers.cityhaving customers.state = 'MI' -group by customers.city having count(customers.customerID) > 10 and where customers.state = 'MI' -where customers.state = 'MI' group by customers.city having count(customers.customerID) > 10
where customers.state = 'MI' group by customers.city having count(customers.customerID) > 10
