SQL Midterm
how many rows will return from select count(consultants.ID) from teleworks.consultants;
1 row
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 th following line: where avg(studentClass.grade) >= 3.0
FALSE
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 below a 3.0, You would utilize the following line in your query: where avg(studentClass.grade) <= 3.0
FALSE
Text in a novel is an example of structured data.
FALSE
How would you sort MySQL query results by a field called "lastName" from a table called "cashiers" from a database named "store"?
ORDER BY cashiers.lastName;
Lauren is querying a data set and the results she keeps getting has a lot of duplicate rows returned. She would like to remove duplicates from the results and only display unique rows of data. What function in SQL would she use in her query?
select distinct
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?
university
When is having used instead of where?
when groups are present through the use of an aggregate function (such as avg, count, etc.) and conductions need to be applied to the groups
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.
where customers.state = 'MI' group by customers.city having count(customers.customerID) > 10
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?
1:n
Which of the following would be an example of prescriptive analytics?
Amazon utilizing an algorithm to identify what product to advertise to you based upon your purchase history when you login
Jill has been asked to identify the number of invoices in a database that match certain criteria. Which of the following is she most likely to use in her query?
COUNT()
Which of the following keywords when used in a SQL select statement will remove duplicate records from the results?
DISTINCT
Joe has a document that contains all of the metadata about the data elements in a database. This would be an example of which of the Following?
Data dictionary
The difference between a data lake and a data warehouse is:
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.
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?
Descriptive
Kevin wants to add "Halloween" into the title of his holiday column. Select all of the proper ways for him, to add the title to the holiday column in his select statement?
Events.holiday as "Halloween" (x2?)
The three factors of the variety of data are:
Form, function and source
Sofia is writing a SQL statement using a calculation. Which of the following does she also need to include to indicate the level of granularity / level of detail for the calculation to be performed?
GROUP BY
Which area of code allows you you make a condition that uses aggregate functions?
HAVING
Which of the following is decreased when using a relational database?
Information redundancy
A car dealership sells new cars to customers. A new car can be sold only once. Subsequent sales of that car would then be considered a used car. What kind of relationship would you have between new cars and customers? How many tables are needed to represent the data and relationship?
One to Many (1 :n) / 2 tables
Which order?
SELECT FROM INNER JOIN WHERE GROUP BY HAVING ORDER BY ;
For the following piece of code, we would use the having function: count(payment.payments) > 10
TRUE
Text in a novel is an example of unstructured data.
TRUE
Which of the following describes a scenario in which a concatenated key would be utilized?
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 one table will appear as a foreign key in the other table
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.customerID order by customer.name asc, payment.date desc;
The results will show all customers, even if they have not made any payments.
A null value means:
The value is unknown or does not exist
Which of the following describes the veracity characteristic of big data?
Uncertainty and or untrustworthiness of data
Paris Fashions Inc (PFI) is an upcoming clothing retailer on the Internet. Their sales continue to climb exponentially every day. They ar getting competitive advantage from a very interactive website. It collects information about their customers, such as what they viewer what they purchased, what they added to their cart, and what they decided not to buy as just examples of the hundreds of consumer behaviors in their database. The data is being collected in an exponentially faster and faster pace and they are now implementing natural language processing to capture customer service call recordings for analysis. They have so much data that they have had to significantly increase their capacity to store the data. They believe they may be approaching a point where they are now dealing with big data. Which of the föllowing V's related to Big Data are described in their circumstances above? (Select ALL that apply)
Variety, Velocity, Volume
If an individual would like to place a non-aggregate condition (such as country = 'US' in code that only results rows that fit certain criteria, which of the following areas would this code be placed?
WHERE
When would you utilize the LIKE operator in a SQL query?
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?
[]
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.customerID = customers.customerID
both customers and invoices
If you saw the following query, which of the following would be true about the query and the information it is pulling based upon the names of the fields and the structure of the query? (select all that apply) select materials.description, materials.vendor, materials.standardCost, products.finalProduct, materials.standardCost * 1.2 as 'laborCost', assembly.sequence from erp.materials inner join erp.assembly on assembly.materialID = materials.materiallD inner join erp.products on products.productID = assembly.materiallD group by materials.materiallD order by products.finalProduct DESC, assembly.sequence ASC;
calculates the cost of labor by using a percentage of the standard cost of the raw material, materials and products have a many to many relationship, the bill of materials, orders the assembly steps in order numerically for products
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?
customers
Collecting information from many sources and storing them together into a single location is referred to as:
data aggregation
Data was previously stored in a flat format. What was one disadvantage to flat databases?
data redundancy
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.
from geo.lake inner join geo.waterTemperature on waterTemperature.lakeName = lake.lakeName
Mel is the owner of a prominent campground in Michigan. The campground has a database with a table containing the campers (campers) and a table containing the reservations (reservations). Each reservation has an attribute for the number of nights for that reservation. A camper can have many reservations. Mel wrote a query to pull the campers and add the total number of nights that a camper has during the year. She wants to filter for campers that reserved more than 10 nights during the year.
having sum(reservations.nights) > 10
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 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?
inner join schema .highScores on highScores.gamelD = games.gamelD
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 on highscores.gamelD = games.gamelD
What type of join would you want to use to find all the information that comes from one table and the matched records from the other table?
left join, full outer join
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?
n:m & 3 tables
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)
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
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
The operator like in a SQL statement is used for:
pattern matching
