Question 12 PointsWhat type of JOIN returns all rows from the first table and any matching rows from the second table? FULL OUTER JOIN LEFT OUTER JOIN INNER JOIN RIGHT OUTER JOIN Question 22 PointsWhi

FULL OUTER JOIN LEFT OUTER JOIN INNER JOIN RIGHT OUTER JOIN Question 2 2 Points Which records will the following FROM clause select?

FROM Faculty LEFT JOIN Classes All Faculty records and any Class records that are related to them Any Class records that are related to Faculty records All Class records and any Faculty records that are related to them Any Faculty records that are related to Class records Question 3 2 Points When testing for NULL values to find Customers who have no Orders, which column is best to include in the WHERE clause below:

WHERE __________ IS NULL Any foreign key from the Customers table The primary key from the Customers table The OrderDate column from the Orders table The primary key from the Orders table 12/6/21, 5:00 PM View Assessment https://blackboard.sacredheart.edu/ultra/courses/_43462_1/outline/assessment/_2466761_1/overview/attempt/_5275671_1?courseId=_43462_1 2/12 Question 4 2 Points What type of JOIN returns all rows from the second table and any matching rows from the first table?

RIGHT OUTER JOIN FULL OUTER JOIN INNER JOIN LEFT OUTER JOIN Question 5 2 Points What clause can be placed after the UNION keyword to indicate that you want the UNION performed by comparing columns that have the same name in each result set?

GROUP BY CORRESPONDING HAVING WHERE Question 6 2 Points Where is the ORDER BY clause placed to sort the results of a UNION?

The results of a UNION cannot be sorted At the end of each SELECT statement At the end of the last SELECT statement At the end of the first SELECT statement Question 7 2 Points 12/6/21, 5:00 PM View Assessment https://blackboard.sacredheart.edu/ultra/courses/_43462_1/outline/assessment/_2466761_1/overview/attempt/_5275671_1?courseId=_43462_1 3/12 When the _________ keyword is left out of a UNION, the database system automatically eliminates any duplicate rows.

EXCEPT ANY ALL DISTINCT Question 8 2 Points Which of the following is a requirement for the columns of the SELECT statements in a UNION?

Must have the same number of output columns specified.

Neither of these are requirements.

Each corresponding column must have a comparable value.

Both of these are requirements.

Question 9 2 Points A SELECT expression that is embedded within one of the clauses of a SELECT statement is a(n) ____________.

embedded SELECT sub-SELECT embedded query subquery Question 10 2 Points Which of the following is a request that can be answered using a subquery?

Create a list that combines agents and entertainers. 12/6/21, 5:00 PM View Assessment https://blackboard.sacredheart.edu/ultra/courses/_43462_1/outline/assessment/_2466761_1/overview/attempt/_5275671_1?courseId=_43462_1 4/12 Create an index list of all the recipe classes, recipe titles, and ingredients.

Display products and the latest date the product was ordered.

Show me the students who have a grade of 85 or better in Art together with the faculty members who teach Art and have a proficiency rating of 9 or better .

Question 11 2 Points What type of subquery is an embedded SELECT expression that returns only one column and no more than one row?

Table subquery Row subquery Scalar subquery Question 12 2 Points Which aggregate function calculates the mean for a value expression?

MAX(value expression) MIN(value expression) SUM(value expression) A VG(value expression) Question 13 2 Points When an aggregate function is used as a search condition it must be placed in (a) __________.

brackets [] subquery parentheses () braces {} 12/6/21, 5:00 PM View Assessment https://blackboard.sacredheart.edu/ultra/courses/_43462_1/outline/assessment/_2466761_1/overview/attempt/_5275671_1?courseId=_43462_1 5/12 Question 14 2 Points When using an aggregate function, what is the name of the column in the final result set?

The name is generated by the database system.

The name assigned in the AS clause.

None of these are possible names for the column.

All of these are possible names for the column.

The column has no name.

Question 15 2 Points What character is used to separate the columns referenced in the GROUP BY clause?

Comma , Period .

Colon :

Semicolon ; Question 16 2 Points Which clause is used to group matching values of a specified column together?

WHERE GROUP BY FROM HAVING SELECT 2Points 12/6/21, 5:00 PM View Assessment https://blackboard.sacredheart.edu/ultra/courses/_43462_1/outline/assessment/_2466761_1/overview/attempt/_5275671_1?courseId=_43462_1 6/12 Question 17 2 Points When the GROUP BY clause is used without aggregate functions it will give you the same result as what keyword?

DISTINCT HAVING WHERE FROM Question 18 2 Points Columns referenced in the WHERE clause must be a part of what?

The columns referenced in the ORDER BY clause.

Tables defined in the FROM clause.

The columns referenced in the SELECT clause.

The columns used in aggregate functions.

Question 19 2 Points Columns referenced in the HA VING clause must also be referenced where?

Enclosed within an aggregate function Neither in the GROUP BY clause nor enclosed within an aggregate function In the GROUP BY clause Either in the GROUP BY clause or enclosed within an aggregate function Question 20 2 Points When does the WHERE clause act on rows? 12/6/21, 5:00 PM View Assessment https://blackboard.sacredheart.edu/ultra/courses/_43462_1/outline/assessment/_2466761_1/overview/attempt/_5275671_1?courseId=_43462_1 7/12 After the rows have been grouped Before the rows are grouped During the grouping of rows Question 21 2 Points What SQL feature enables you to undo a series of changes to the data?

System Updates UPDATE Undo Transactions UPDA TE statements Question 22 2 Points What character is used to separate multiple assignment statements in the SET clause?

Colon :

Semicolon ; Comma , Period .

Question 23 2 Points What need would NOT be addressed with an UPDA TE statement?

Increasing the price of all sports supplements by 3% Creating a list of all supplements that contain Creatine by manufacturer Appending the word ?powder? to all ItemDescriptions for MuscleMilk products Changing the category of all sports supplements to ?Nutrition? 12/6/21, 5:00 PM View Assessment https://blackboard.sacredheart.edu/ultra/courses/_43462_1/outline/assessment/_2466761_1/overview/attempt/_5275671_1?courseId=_43462_1 8/12 Question 24 2 Points If the new value of a column involves the old or current value of the column, the column must be referenced where?

The left side of the equal sign The UPDA TE clause The WHERE clause Both sides of the equal sign Question 25 2 Points Data can be inserted into a table or what other database item?

View Report Function Stored procedure Question 26 2 Points When the INSER T statement is fetching rows of data to be placed into a table, the SELECT expression is used in place of what?

The VALUES clause The value expression The column name list The table name Question 27 2 Points 12/6/21, 5:00 PM View Assessment https://blackboard.sacredheart.edu/ultra/courses/_43462_1/outline/assessment/_2466761_1/overview/attempt/_5275671_1?courseId=_43462_1 9/12 In an INSER T statement, the column name list can be omitted if you are doing what?

Adding only a NULL value Adding only a default value Adding data only to the primary key column Supplying a value for every column in the table Question 28 2 Points Which clause is used to limit the rows deleted in the DELETE statement?

DELETE HAVING WHERE FROM Question 29 2 Points What would keep the DELETE statement without the WHERE clause from deleting all the rows in a table?

System logs Referential integrity constraints Transactions Question 30 2 Points What is a possible method for ensuring that the correct rows are deleted from the database before the DELETE statement is run?

Using transactions Creating a SELECT statement and then transferring the WHERE clause to the DELETE statement Using system logs 12/6/21, 5:00 PM View Assessment https://blackboard.sacredheart.edu/ultra/courses/_43462_1/outline/assessment/_2466761_1/overview/attempt/_5275671_1?courseId=_43462_1 10/12 Question 31 10 Points W e are planning our 1st Quarter marketing budget and need you to provide some historical sales data from the SalesOrdersModify database. Provide summarized data regarding the orders we received during the 1st quarter of 2018 (Jan.-Mar .) broken out by customer city and state. Include customer city and state names along with the count of all orders, the average order total, smallest and largest order totals and the sum of all orders from that city and state. Arrange them by state and then city . Please make sure the average is formatted with two digits after the decimal place. (20 rows) Question 32 10 Points We are planning a postcard mailing to promote our performers who play Show Tunes or Standards. Using the EntertainmentAgencyModify database, list the full name and mailing address for all customers who did not book any performers in 2018 who play Show Tunes or Standards so that we can include them in the mailing. (Hint: create a subquery to list all customers who DID book performers who play those musical styles and then test for NULL to see which customers are not in that list) You must use the names of the musical styles, not the styleID numbers. (7 rows) 12/6/21, 5:00 PM View Assessment https://blackboard.sacredheart.edu/ultra/courses/_43462_1/outline/assessment/_2466761_1/overview/attempt/_5275671_1?courseId=_43462_1 11/12 Question 33 10 Points Our EntertainmentAgencyModify database is encountering performance issues because of its size. Archive all Engagements that both started and ended prior to March 1, 2018 into the Engagements_Archive table. After archiving the old Engagements, remove them from the original Engagements table to reduce the size of that table. Remember to use transactions for each of these two queries to protect your data. (45 rows) (Note : Refer to the schema in Question 32 for relationships.) Question 34 10 Points We are looking for customer endorsements of the performer "Modern Dance". Provide a list of names and phone numbers for any customers in the EntertainmentAgencyModify database who have ever booked this performer . Remember that some of these engagements may now be archived. Put the list of customers in alphabetical order by last name and first name. (Hint: use a SQL command that will allow you to combine the results of two similar queries, one for Engagements and one for Engagements_Archive, into a single result set.) (8 rows) 12/6/21, 5:00 PM View Assessment https://blackboard.sacredheart.edu/ultra/courses/_43462_1/outline/assessment/_2466761_1/overview/attempt/_5275671_1?courseId=_43462_1 12/12 (Note : Refer to the schema in Question 32 for relationships.)