SQL MIDTERM

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 letter "s" and are at least 3 characters long where customer.country like

(A_%s)

Which wildcard would you use that allows you to select all the columns in a selected table?

*

Match the SQL function with description: 1. returns the number of rows that matches a specified criterion 2. returns the largest value if the selected column 3. returns the average value of a numeric column 4. returns the total sum of a numeric column 5. returns the smallest value of the selected column

1. count() 2. max() 3. avg() 4. sum() 5. min()

Each cell phone has one phone number. Each phone number can only be used on a single phone. What type of relationship does a cell phone have to a phone number?

1:1

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?

1:n with 2 tables

What designation is last in a SQL query to indicate the end of the code for that query? select customers.name, customers.address from sales.customers where customers.state = 'MI'

;

what is the operator for does not equal?

< >

Which command allows you to add a label for the column title rather than the filed name?

AS

Text in a novel is an example of structured data

False

You are able to join on any two tables, regardless of if the field is a primary key

False

You should use a having statement for non-aggregated functions?

False

when using a calculated field in your select statement, what other clause relating to calculated fields is required?

Group By

To filter for the total amount of purchases made, what clause should be used?

Having

Which of the following is decreased when using a relational database?

Information redundancy

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?

One-to-Many

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

The order of tables matters when using left/right joins?

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

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. Which of the following would he use to only include items that has US as the country?

WHERE

When would you utilize the "like" operator in a SQL query?

When you are trying to match / filter for pattern using wildcards

Which wildcard allows you to look for a single character in a range?

[ ]

How many actors can a movie have in it based upon the data model below?

a movie can have many actors in it

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 table of MSU students?

a primary key

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 = table 2.foreignKey;

all records from table2 will be indicated in the results, regardless of whether there are related records in table 1

When performing an order by statement in SQL, what is the default ordering if you do not specify?

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 the results? full outer join schema.customers on invoices.customerID = customers.customerID

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

class

if you want to find the total amount of instances of student APIDS, what aggregated function would you use?

count()

If the following join statement is used to join two tables in a query, which of the following tables would all the tuples in the relation appear in results? right outer join schema.customers on invoices.customerID = customers.customerID

customers

The difference between a data lake and a data warehouse is

data lakes store data in a 'raw' form (original form). Data warehouses typically store date that has been extracted, transformed and loaded (ETL) for purposes of performing analysis

Match the example with the business analytic: ------ Did we make a profit last year? ------ What will our sales be next quarter? ------ Should we make or buy an expensive part for our machines?

descriptive analytic predictive analytic prescriptive analytic

The tables track lakes and water temp readings. Each lake has a unique name (lakeName). Each water temperature reading applies to an individual lake at a specific date and time. Water temp readings are identified by a unique identifier assigned to each reading (readingID). They reside in a schema/database in MySQL called geo. which of the following segments of code could be applied to queries against these tables?

from geo lake inner join geo.watertemperature on water temperature.lakeName = lake.lakeName

Jaclyn 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 aggregation that sum should be executed against?

group by table.businessLine

Pranav wants to filter the results of the query below to only show records that have had total sales (sum(sales.Amount) greater than 100,000 Euros. Fill in the blank for the clause that he would need to add to his code to complete it.

having

What type of track should be used to find all tracks, regardless of invoices? ____ join musicales.invoiceLine on invoiceLine.trackID = track.trackID

left join

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?

left join right join

He wants his query to pull all 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.gameID = games.gameID

Each order has multiple products. Each product can be sold on multiple orders. What kind of relationship is there between products and orders?

m:n

A person can own multiple homes. Each can be owned by multiple individuals. What type of relationship does individuals have with homes and how many tables will be there in the data model?

m:n with 3 tables

What represents no data being stored in a SQL table?

null

to sort by date and then customer name alphabetically, which statement should be used?

order by sales.purchaseDate, customers.customersName

The operator "like" in a SQL statement is used for:

pattern matching

SQL statements/clauses in order

select from join where group by having order by

Jeremy is writing a SQL query. He keeps getting duplicated results in the output. This is because there are multiple rows that match the criteria he is trying to show that are the same

select distinct

which of the following would be the formula and group by needed in your SQL code to calculate the total sales for each customer?

sum(products_has_invoices.unitPrice * product_has_invoices.quantity) & group by customer.custID

When implementing a 1:n relationship, which of the following is true?

the primary key of one table would be put in the other table as a foreign key

Which of the following statements would describe the relationships between the tables, where the 'wing' table contains widgets and the 'cost' table contains customers: select cust.fname, cust.lname, widg.color, widg.length from db.cust inner join db.widg on widg.widgID = cost.widgID where wing.color = 'cyan';

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.customerID order by customer.name ask, payment.date desc;

the results will show all customers, even if they have not made any payments

which of the following would describe the logic? where (inventory.color="red" or inventory.quantity = 2) and inventory.onSale = "Yes"

the results would include products that are red and on sale or products with a quantity of 2 that are in sale