CECS 323 - Midterm 3 Practice

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/31

encourage image

There's no tags or description

Looks like no tags are added yet.

32 Terms

1
New cards
<p>This query will always produce exactly zero rows:</p>

This query will always produce exactly zero rows:

True

2
New cards
<p>The first query returns 122 rows and the second only returns 100 rows, The reason is (select one</p>

The first query returns 122 rows and the second only returns 100 rows, The reason is (select one

Not every customer has a has a sales representative. The INNER JOIN will only return customers who have a matching row in employees.

3
New cards
<p>The first query will produce fewer rows than the second:</p>

The first query will produce fewer rows than the second:

False

4
New cards
<p>I want to see every customer who has ever made a payment, and I want to see that customer’s name just once. Will the following query do that for me?</p>

I want to see every customer who has ever made a payment, and I want to see that customer’s name just once. Will the following query do that for me?

No, it won’t because JOIN to orders will require that the customers returned must have made at least one payment and at least one order.

5
New cards

To join employeesto employees, we must use the JOIN ON, rather than the JOIN USING because (pick one)

We must use the JOIN ON because the primary key column in the parent (employees) is named employeeNumber, while the corresponding migrated foreign key in employees is named reportsTo.

6
New cards
<p>$lookup has two formats: the first requires from, localField, foreignField and as. The second requires from, let (optional), pipeline, and as. In the second form, pipeline in the $lookup allows us to “condition” (transform, operate on) the data of the foreign collection before adding it to the local collection. <br>The following query:</p>

$lookup has two formats: the first requires from, localField, foreignField and as. The second requires from, let (optional), pipeline, and as. In the second form, pipeline in the $lookup allows us to “condition” (transform, operate on) the data of the foreign collection before adding it to the local collection.
The following query:

Produces the same result as an INNER JOIN between customers and employees ON customers.salesRepEmployeeNumber = employeeNumber, and the inner project removes everything from customers except the customer number, name, and phone number. This reduces the amount of data added to the documents output from the $lookup

7
New cards

By itself, the $lookup is like a RIGHT OUTER JOIN in SQL as far as whether a document in coming into the $lookup from the previous pipeline stage will appear in the output.

False

8
New cards
<p>The following query will remove the employees with no customers because of the $unwind</p>

The following query will remove the employees with no customers because of the $unwind

True

9
New cards
<p>The following query will yield a document for those employees who are not sales representatives to any customers.</p>

The following query will yield a document for those employees who are not sales representatives to any customers.

False

10
New cards
<p>The following query demonstrates all but one of the following features of the $project</p>

The following query demonstrates all but one of the following features of the $project

You can use $project to add fields to the output and remove them in the same $project aggregation pipeline stage.

11
New cards
<p></p>


True

12
New cards
<p>Select the one false statement about this query:</p>

Select the one false statement about this query:

customerName cannot appear in the SELECT clause because it is not in the GROUP BY clause.

13
New cards
term image

STRING_AGG(sub.lastName || ', ' || sub.firstName, ' | ' ORDER BY sub.lastName, sub.firstName)

14
New cards
term image

No, because you cannot compound aggregation functions like this. The only way to do this is to use a subquery

15
New cards

Comparing the GROUP BY HAVING clause to the WHERE clause in a SELECT statement, we can say (pick one):

HAVING filters the output after the GROUP BY aggregation has occurred but WHERE filters before the GROUP BY aggregation.

16
New cards
term image

Output one document for each job title in each office with the number of employees who have that title.

17
New cards
term image

A single document with nothing in the _id field, the minimum unit price across all orders, and the total number of payments across all orders.

18
New cards
term image

Add state and country to the _id of the $group, and include them into the $project.

19
New cards
term image

Outputs one document for each product line within each order and the number of products within that order within that product line.

20
New cards

The second $group pipeline stage:

Outputs one document for each order, the number of product lines represented in that order, and the total number of products included in that order.

21
New cards
<p>Will always report out zero rows.</p>

Will always report out zero rows.

False

22
New cards

The outputs from two SELECT statements are UNION compatible when (pick one):

The outputs from the two SELECT statements must have the same number of columns, and each corresponding column must have a compatible datatype. For instance, column three in the first output might be Integer and column 3 in the second output might a float

23
New cards
term image

True

24
New cards
term image

False

25
New cards
<p>Will produce the same results.</p>

Will produce the same results.

True

26
New cards
term image

It outputs just one row for each city within each state within each country, no matter how many customers are in that city.

27
New cards
term image

True

28
New cards
term image

True

29
New cards
term image

Without the EXISTS in the second query, we return all employees, regardless whether they have any subordinates or not. But the first query only returns those employees who have at least one subordinate because of the inner join.

30
New cards
term image

Three employees, and the employee number of their mutual manager. No two lines of the output will have the same three employees in it. But there will be multiple cases in which two of the three employees match from one line to another.

31
New cards
term image

There's nothing wrong with this query as it is.

32
New cards
term image

A report of all employees and their managers where the employee and their manager are based in the same office.