Sophia Database
MILESTONE 2 RETAKE GUIDE
Southern New Hampshire University
Actual Questions & Verified Answers with Rationales
100% Guarantee Pass
,5/31/22, 7:27 PM Sophia :: Welcome
Score 18/25
18/25 that's 72%
Retake
18 questions were answered correctly.
7 questions were answered incorrectly.
Milestone Tips
Remember that Milestones are open-book.
Take the Practice Milestone before taking the Milestone.
Take the Milestone at a time and in a place where you can be focused and undisturbed for the entire Milestone time limit.
Milestone Retake
UNIT 2 score
— will
MILESTONE
replace your original score.2
You are allowed one retake per Milestone.
The retake
Make sure you're ready. Review our list of Milestone Tips.
Retake Now Maybe Later
Milestone Retake
Before you begin, please make sure you're in a place where you can be focused and undisturbed for the entire Milestone time limit.
Begin Retake
Cancel
1. Milestone Retakes
Sophia allows one retake per Milestone. If you ever want to retake a Milestone, click this button.
Got it
1
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while
you are working on this assessment.
https://postgres.sophia.org/
Which result set requires a JOIN?
Showing track ID, media type ID, and track name
Showing media type ID with track name
Showing media type name with track name
Showing track name with track ID
CONCEPT
Joins
Report an issue with this question
Reported. Thanks for your feedback.
2
https://app.sophia.org/spcc/introduction-to-relational-databases/milestone_take_feedbacks/14435591 1/16
, 5/31/22, 7:27 PM Sophia :: Welcome
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while
you are working on this assessment.
https://postgres.sophia.org/
Given the following queries, which of these would be the most efficient?
1. SELECT customer.*
FROM invoice
INNER JOIN customer
ON customer.city = invoice.billing_city
WHERE COUNTRY like '%m';
2. SELECT *
FROM customer
WHERE city IN
(SELECT billing_city
FROM invoice
WHERE COUNTRY like '%m');
Query #1 would be more efficient as it is based on primary and foreign keys.
Query #2 would be more efficient as it is based on primary and foreign keys.
Both would be the same as both use the same indices for the join and filter.
Query #1 would be more efficient as it is index indices.
RATIONALE
The join performance will depend on the explain plan. In most cases, a join will be faster if it is using a primary key, foreign key, or other indexed columns. With joins,
they concentrate the operation based on the results of the first two tables so any subsequent joins or filters are done using the result of the first joined tables. You should
use the explain plan query to test the results.
CONCEPT
Subquery Performance
Report an issue with this question
Reported. Thanks for your feedback.
3
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while
you are working on this assessment.
https://postgres.sophia.org/
What type of situation may warrant a foreign key not being connected to a primary key in another table?
A foreign key can be linked to a NOT NULL column.
A foreign key is not needed if the data type is different.
A foreign key may be linked to a unique column that establishes a 1 to 1 relationship.
Foreign keys are not needed when we require referential integrity.
RATIONALE
In most cases, a foreign key should be linked to a candidate key which is generally a primary key or a unique key. Foreign keys could be temporarily disabled to simplify
dropping the tables or moving valid data. Foreign keys could also be added after tables have been created to avoid having to generate them in the right order.
CONCEPT
https://app.sophia.org/spcc/introduction-to-relational-databases/milestone_take_feedbacks/14435591 2/16
MILESTONE 2 RETAKE GUIDE
Southern New Hampshire University
Actual Questions & Verified Answers with Rationales
100% Guarantee Pass
,5/31/22, 7:27 PM Sophia :: Welcome
Score 18/25
18/25 that's 72%
Retake
18 questions were answered correctly.
7 questions were answered incorrectly.
Milestone Tips
Remember that Milestones are open-book.
Take the Practice Milestone before taking the Milestone.
Take the Milestone at a time and in a place where you can be focused and undisturbed for the entire Milestone time limit.
Milestone Retake
UNIT 2 score
— will
MILESTONE
replace your original score.2
You are allowed one retake per Milestone.
The retake
Make sure you're ready. Review our list of Milestone Tips.
Retake Now Maybe Later
Milestone Retake
Before you begin, please make sure you're in a place where you can be focused and undisturbed for the entire Milestone time limit.
Begin Retake
Cancel
1. Milestone Retakes
Sophia allows one retake per Milestone. If you ever want to retake a Milestone, click this button.
Got it
1
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while
you are working on this assessment.
https://postgres.sophia.org/
Which result set requires a JOIN?
Showing track ID, media type ID, and track name
Showing media type ID with track name
Showing media type name with track name
Showing track name with track ID
CONCEPT
Joins
Report an issue with this question
Reported. Thanks for your feedback.
2
https://app.sophia.org/spcc/introduction-to-relational-databases/milestone_take_feedbacks/14435591 1/16
, 5/31/22, 7:27 PM Sophia :: Welcome
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while
you are working on this assessment.
https://postgres.sophia.org/
Given the following queries, which of these would be the most efficient?
1. SELECT customer.*
FROM invoice
INNER JOIN customer
ON customer.city = invoice.billing_city
WHERE COUNTRY like '%m';
2. SELECT *
FROM customer
WHERE city IN
(SELECT billing_city
FROM invoice
WHERE COUNTRY like '%m');
Query #1 would be more efficient as it is based on primary and foreign keys.
Query #2 would be more efficient as it is based on primary and foreign keys.
Both would be the same as both use the same indices for the join and filter.
Query #1 would be more efficient as it is index indices.
RATIONALE
The join performance will depend on the explain plan. In most cases, a join will be faster if it is using a primary key, foreign key, or other indexed columns. With joins,
they concentrate the operation based on the results of the first two tables so any subsequent joins or filters are done using the result of the first joined tables. You should
use the explain plan query to test the results.
CONCEPT
Subquery Performance
Report an issue with this question
Reported. Thanks for your feedback.
3
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while
you are working on this assessment.
https://postgres.sophia.org/
What type of situation may warrant a foreign key not being connected to a primary key in another table?
A foreign key can be linked to a NOT NULL column.
A foreign key is not needed if the data type is different.
A foreign key may be linked to a unique column that establishes a 1 to 1 relationship.
Foreign keys are not needed when we require referential integrity.
RATIONALE
In most cases, a foreign key should be linked to a candidate key which is generally a primary key or a unique key. Foreign keys could be temporarily disabled to simplify
dropping the tables or moving valid data. Foreign keys could also be added after tables have been created to avoid having to generate them in the right order.
CONCEPT
https://app.sophia.org/spcc/introduction-to-relational-databases/milestone_take_feedbacks/14435591 2/16
