UNIT 2 — MILESTONE 2
Score 16/25
You passed this Milestone
16 questions were answered correctly.
9 questions were answered incorrectly.
1
In each milestone, you may want or need to use the database and query tool t
...
UNIT 2 — MILESTONE 2
Score 16/25
You passed this Milestone
16 questions were answered correctly.
9 questions were answered incorrectly.
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/
Given the initial tables in our example database, if we wanted to delete tracks, albums, and artists that HAVE been purchased before, in what order do we need
to delete data from our tables?
artist
album
track
artist
album
track
media_type
genre
invoice_line
invoice
track
album
artist
track
album
artist
When deleting data from tables that have foreign keys, it is important to consider the order of the tables as data must be deleted in the order that they are referenced,
starting with the table with the most references going down to the one with the least. This is the case as long as no other data that references them still exists. Since these
tracks have not been purchased before, we need to delete those invoice_lines and invoices first before we can delete them from the track, album, and artist.
CONCEPT
Foreign Keys & Referential Data
2
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 of the following data models appropriately models the relationship of recipes and their ingredients?
recipe
recipe_id
recipe_name
ingredient_name_1
1/1/22, 3:39 AM Sophia :: Welcome
2/15
ingredient_amount_1
ingredient_name_2
ingredient_amount_2
recipe
recipe_id
recipe_name
ingredient_id (FK)
ingredient
ingredient_id
ingredient_name
ingredient_amount
ingredient
ingredient_id
recipe_name
ingredient_name
ingredient_amount
recipe
recipe_id
recipe_name
ingredient
ingredient_id
recipe_id (FK)
ingredient_name
ingredient_amount
Allows students to identify that a Foreign Key is needed, and where it should belong in terms of the relationship. Common mistakes include not realizing the necessity of
a second table with an FK relationship (distractor 1, 2) as well as not understanding the mechanics of the key that references versus the key that is the "source" of the
relationship.
CONCEPT
Foreign Keys & Creating Tables
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/
Which of the following statements would be valid DROP VIEW statements to remove the two views, which will also display an error if either view doesn't exist?
DROP VIEW IF EXISTS album_cost, album_artist_names;
DROP VIEW album_cost AND album_artist_names;
DROP VIEW album_cost, album_artist_names CASCADE;
DROP VIEW album_cost, album_artist_names;
Common issues when dropping tables include the order of the clauses, if there should be an error displayed when the view is dropped, if other objects that depend on the
view should also be dropped, or prevent the dropping of the view if any objects depend on them.
CONCEPT
DROP VIEW to Remove Views
4
1/1/22, 3:39 AM Sophia :: Welcome
3/15
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 of the following statements would calculate the average bytes per millisecond grouped by the media_type_id in the track table?
SELECT media_type_id, (bytes/milliseconds)
FROM track
GROUP BY media_type_id;
SELECT media_type_id, AVG(bytes/milliseconds)
FROM track;
SELECT media_type_id, AVG(bytes/milliseconds)
FROM track
GROUP BY media_type_id;
SELECT media_type_id, AVG(milliseconds/bytes)
FROM track
GROUP BY media_type_id;
Common mistakes include using the wrong columns, ordering of the columns within the calculation, not grouping the statements, not using the correct aggregate
functions, and not including the right criteria.
CONCEPT
Calculations in SELECT Statements
5
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 of the following statements would create a UNION between all of the cities we have customers, employees, or invoices in?
SELECT billing_city
FROM invoice
SELECT city
FROM customer
SELECT city
FROM employee;
SELECT city
FROM invoice
UNION
SELECT city
FROM customer
UNION
SELECT city
FROM employee;
SELECT billing_city
FROM invoice
UNION
SELECT city
FROM customer
UNION
SELECT city
FROM employee;
1/1/22, 3:39 AM Sophia :: Welcome
4/15
SELECT billing_city
FROM invoice
SELECT city
FROM customer
SELECT city
FROM employee
UNION;
For the UNION, two or more separate queries should have the same column output in the same order. Each statement combined should have the keyword UNION
between them.
CONCEPT
UNION to Combine Results
6
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/
Why is the following query valid for a NATURAL join?
SELECT album.title, artist.name
FROM album
NATURAL JOIN artist;
The query is not a valid NATURAL JOIN query and must use a JOIN... ON instead.
The tables have a foreign key relationship.
The tables have a shared column, but their column names are not identical which makes it a candidate for a NATURAL JOIN.
The two tables share identical columns/fields.
A common mistake is thinking that the actual names given have any special meaning other than being the exact same.
CONCEPT
Natural Joins
7
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 any foreign key.
Tables could be created first to avoid having to create them in order.
A foreign key should always be linked to a primary key of another table.
A foreign key can be linked to any foreign key.
1/1/22, 3:39 AM Sophia :: Welcome
5/15
In most cases, a foreign key should be linked to a candidate key that 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
Foreign and Primary Keys
8
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 *
FROM invoice
WHERE customer_id IN
(SELECT customer_id
FROM customer
WHERE country like 'U%');
2. SELECT invoice.*
FROM invoice
INNER JOIN customer
ON customer.customer_id = invoice.customer_id
WHERE country like 'U%';
Query #2 would be more efficient as it is based on primary and foreign keys.
Query #1 would be more efficient as it is based on primary and foreign keys.
Query #2 would be more efficient as it is not using indexed columns.
Both would be the same as both use the same indices for the join and filter.
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
9
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 of the following query does NOT correctly use aliases?
SELECT i.customer_id, i.total, c.last_name
FROM invoice AS i
JOIN customer AS c
USING (customer_id);
SELECT c.customer_id, c.total, c.last_name
FROM invoice AS i
JOIN customer AS c
USING (customer_id);
SELECT i.customer_id, total, last_name
FROM invoice AS i
1/1/22, 3:39 AM Sophia :: Welcome
6/15
JOIN customer AS c
USING (customer_id);
SELECT c.customer_id, i.total, c.last_name
FROM invoice AS i
JOIN customer AS c
USING (customer_id);
It's important that students recognize aliases CAN be used to specify all columns, but they can also be left out if the columns are not identically named between two joined
tables.
CONCEPT
AS/ALIAS to Rename Tables and Columns
10
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 of the following is a correctly formatted SELECT statement to show the following result set with the track's name, the track's millisecond, and the
playlist_track's playlist_id?
SELECT name, milliseconds, playlist_id
FROM track
JOIN playlist
ON track.playlist_id = playlist_track.playlist_id;
SELECT name, milliseconds, playlist_id
FROM track
JOIN playlist
ON track.track_id = playlist_track.track_id;
SELECT name, milliseconds, playlist_id
FROM track
JOIN playlist
ON track.trackid = playlisttrack.trackid;
SELECT name, milliseconds, playlist_id
FROM track
JOIN playlist
ON track_id;
Since JOIN ON is the most generic form of a JOIN, and the USING keyword is not possible here. It's the "last resort" JOIN syntax, so students should be able to
recognize when a JOIN ON is the only option. Additionally, common mistakes include not knowing which columns to use in the ON clause, misspelling one of the
columns in the ON clause, forgetting table names, or forgetting parentheses.
CONCEPT
JOIN ON to Link Tables
11
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/
Genre
genre_id name
1 Broadway
2 Rock
3 Classical
1/1/22, 3:39 AM Sophia :: Welcome
7/15
genre_id name
4 Salsa
Track
track_id name genre_id
1 Highway to Hell 2
2 Everlong 2
3 Smells Like Teen Spirit 2
Given the above genres and tracks, how many results will be returned for the following query?
SELECT genre_name, track_name
FROM track
RIGHT JOIN genre
USING (genre_id);
4 6 3 5
A right join will combine the data between two tables. The right join starts by selecting data from the right table of the query and matching it with rows on the left table.
The right join includes all rows that are in the right table even if they do not have matching rows from the left table. Common mistakes include only displaying the
matching rows or doing a left join.
CONCEPT
Right Joins
12
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 tables have been created without foreign keys added, which of the following ALTER TABLE statements would create a foreign key on the
customer_id in the department table to reference the customer_id in the customer table?
ALTER TABLE customer
ADD CONSTRAINT fk_department
FOREIGN KEY (customer_id)
REFERENCES department (customer_id);
ALTER TABLE department
ADD CONSTRAINT
FOREIGN KEY (customer_id)
REFERENCES customer (customer_id);
ALTER TABLE department
ADD CONSTRAINT fk_department
FOREIGN KEY customer (customer_id)
REFERENCES customer_id;
ALTER TABLE department
ADD CONSTRAINT fk_department
1/1/22, 3:39 AM Sophia :: Welcome
8/15
FOREIGN KEY (customer_id)
REFERENCES customer (customer_id);
Common errors when altering tables to add foreign key include not having the constraint name, having the clauses in the wrong order, not altering the correct table to add
the foreign key, and not referencing the correct columns.
CONCEPT
Foreign Keys & Altering Tables
13
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 of the following queries will use a subquery to find all of the rows in the track table that has the composer as Miles Davis and has the length of the song in
milliseconds shorter than the maximum track length of all songs where the media_type_id = 1?
SELECT *
FROM TRACK
WHERE milliseconds >
(SELECT max(milliseconds)
FROM track
WHERE media_type_id = 1)
AND composer = 'Miles Davis';
SELECT *
FROM TRACK
WHERE milliseconds >
SELECT MIN(milliseconds)
FROM track
WHERE media_type_id = 1
AND composer = 'Miles Davis';
SELECT *
FROM TRACK
WHERE milliseconds <
(SELECT max(milliseconds)
FROM track
WHERE media_type_id = 1)
AND composer = 'Miles Davis';
SELECT *
FROM TRACK
WHERE milliseconds >
(SELECT MIN(milliseconds)
FROM track
WHERE media_type_id = 1)
AND composer = 'Miles Davis';
Common issues include not using ( and ) around the subquery, not having the condition in the right WHERE clause, having more columns being returned in the subquery
than in the column comparison list, or not using the aggregate function in the subquery.
CONCEPT
Subqueries
14
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/
In trying to delete from the playlist table, what is the cause of this error?
"Query failed because of: error: update or delete on table "playlist" violates foreign key constraint "playlist_track_playlist_id_fkey" on table "playlist_track"
1/1/22, 3:39 AM Sophia :: Welcome
9/15
The track has to be deleted first before the playlist is deleted.
The playlist_id doesn't exist in the playlist_track table.
The playlist_track table has a reference to the playlist_id that is being deleted.
The playlist_id doesn't exist in the playlist table.
When inserting or deleting data from tables, it is important to understand the errors that can arise from the statement. The error message will indicate which table the
foreign key is referencing the table that we're trying to delete from or the constraint that is being violated when we try to insert data into a table.
CONCEPT
Foreign Key Errors
15
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 results would show if the artist table LEFT OUTER JOINed the album table?
All artists, even those with no albums in the album table
All rows from the album table, even those that have NULL artist_id's
Only rows from the artist table that do not have related albums in the album table
Only artists from the artist table that have albums
The hardest thing to understand with OUTER JOINs is just the pure mechanics of the result set returned: how it differs from an INNER join and what the primary unique
characteristics of the result set are. Students are often hung up on the order mattering—it does matter which table is in the FROM clause vs. in the JOIN clause (the
FROM clause is "prioritized" in the result set).
CONCEPT
Left Joins
16
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 customer names with invoice_dates
Showing invoice_dates with invoice totals
Showing invoice totals, invoice_dates, and customer_id's
1/1/22, 3:39 AM Sophia :: Welcome
10/15
Showing invoice_dates with customer_id's
CONCEPT
Joins
17
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/
Animal
animal_id
name
adopter_id
Adopter
adopter_id
name
Given the above data for an adoption agency, what does the result set for the following query represent?
SELECT adopter.name, animal.name
FROM Animal
CROSS JOIN Adopter;
It represents all adopters regardless of whether they have claimed an animal in the animal table.
It represents each animal, with the name of their adopter if that has been specified via a Foreign Key.
It represents every single Animal in the animal table regardless of whether they have been adopted or not.
It represents every single animal matches with every single adopter.
Determining the number of results in a result set for a CROSS JOIN is as simple as the product of the number of records in each table. Common mistakes include thinking
that the result set is determined by one of the tables (as in an OUTER JOIN) hence distractors 2 and 3. Another common mistake is thinking that there should only be as
many results as the sum of the rows in both tables (rather than the product).
CONCEPT
Cross Joins
18
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 would you need to create or replace a view?
The view is no longer being used.
The view needs to have update, insert, and delete statements allowed.
A view has already been created with the same name but needs to be changed.
On a daily basis so that the data is refreshed.
1/1/22, 3:39 AM Sophia :: Welcome
11/15
Views represent SELECT statements over the data so they do not need to be refreshed. There are other types of views like materialized views that do store data. Views
should be replaced if the underlying SQL statement is no longer valid as is. Examples of these include columns being added to the tables referenced in the views that have
the same name as another column, additional columns need to be added or have aliases used especially with calculated fields. If the underlying data needs to be updated to
reference other tables, the view also should be replaced. In addition, if the SQL statement needs to be performance-tuned by rewriting the SQL, the view would have to be
replaced.
CONCEPT
CREATE OR REPLACE VIEW to Update Views
19
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 of the following is the valid syntax for creating a VIEW to view a subset of a table?
CREATE VIEW USA_customers
AS
SELECT *
FROM customer
WHERE country = 'USA';
CREATE VIEW USA_customers
AS customer
SELECT *
WHERE country = 'USA';
CREATE VIEW USA_customers
AS customer
WHERE country = 'USA';
CREATE VIEW USA_customers
SELECT *
FROM customer
WHERE country = 'USA';
Common mistakes include- forgetting the "AS", not doing a select statement, and not including the FROM clause.
CONCEPT
VIEW to Provide a Subset
20
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 view that has been created, how would you query the view to list the artist names in ascending order and album titles in desc order?
CREATE VIEW album_artist_names
AS
SELECT album.title, artist.name
FROM album
INNER JOIN artist
ON album.artist_id = artist.artist_id;
SELECT *
FROM album_artist_names
ORDER BY name ASC, title DESC;
SELECT *
FROM album_artist_names
ORDER BY name, title DESC;
1/1/22, 3:39 AM Sophia :: Welcome
12/15
SELECT *
FROM album_artist_names
ORDER BY name DESC, title;
SELECT *
FROM album_artist_names
ORDER BY name DESC, title ASC;
Common errors include not referencing the correct column or view tables, not using the right criteria or sorting order, or the incorrect syntax for a SELECT statement.
CONCEPT
VIEW & Complex Queries
21
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 of the following statements will be able to show the following result set?
SELECT name, title
FROM album, track;
SELECT name, title
FROM album
JOIN track
WHERE album_id != NULL;
SELECT name, title
FROM album
JOIN track
USING (album_id);
SELECT name, title
FROM album
JOIN track
USING (track_id);
It's common for students to not realize a JOIN USING (or any kind of JOIN) is needed. The syntax for USING is also unique from ON, and so it's common for students to
confuse the two.
1/1/22, 3:39 AM Sophia :: Welcome
13/15
CONCEPT
JOIN USING to Link By Column
22
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 of the following is the valid syntax for creating a VIEW to view data from multiple tables?
CREATE VIEW album_artist_names
AS
SELECT album.title, artist.name
FROM album
INNER JOIN artist
ON album.artist_id = artist.artist_id;
CREATE VIEW album_artist_names
SELECT album.title, artist.name
FROM album
INNER JOIN artist
ON album.artist_id = artist.artist_id;
CREATE VIEW album_artist_names
AS
SELECT album.title, artist.name
FROM album
ON album.artist_id = artist.artist_id;
CREATE VIEW album artist names
AS
SELECT album.title, artist.name
FROM album
INNER JOIN artist
ON album.artist_id = artist.artist_id;
Many of the same issues arise when creating a VIEW to view a JOINed query as when the VIEW is just a subset of the table. Common mistakes include- forgetting the
"AS", not doing a select statement, adding spaces in the view name, and not including the FROM clause.
CONCEPT
VIEW to Simplify Queries
23
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/
Use the following data model for this question:
Recipe
recipe_id
title
Ingredient
ingredient_id
name
Recipe_Ingredient
recipe_ingredient_id
recipe_id
ingredient_id
Which of the following is a situation where an OUTER JOIN could be useful?
1/1/22, 3:39 AM Sophia :: Welcome
14/15
To view all ingredients in the database even if they are not being used for a particular recipe
To view recipes that have ingredients in the ingredients table
To view only ingredients that are being utilized for particular recipes
To view recipes that have the word "banana" in their title
Common issues are thinking of outer joins as similar in purpose to inner joins, thinking outer joins return only "unmatched" records, and confusing the need for an outer
join where a WHERE clause would be sufficient.
CONCEPT
Outer Joins
24
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 of the following queries would check for duplicates of first names in the customer table and how many there are of each?
SELECT first_name, COUNT(*)
FROM customer
WHERE COUNT(*) > 1;
SELECT first_name
FROM customer
GROUP BY first_name
HAVING COUNT(*) > 1;
SELECT first_name, COUNT(*)
FROM customer
GROUP BY first_name
HAVING COUNT(*) > 1;
SELECT first_name
FROM customer
GROUP BY first_name
HAVING COUNT(*) > 0;
To find duplicate rows or data, you must list the columns that you want to check on for grouping in the GROUP BY clause and use the HAVING clause check the count
for more than 1. If you wanted to display the count of each, it must also be listed in the SELECT clause. It is important that the COUNT check on either * for all columns
or based on a NOT NULL column as NULL columns are not counted.
CONCEPT
Find Duplicate Rows
25
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 will be the result of the query based on the following criteria?
= ALL ()
1/1/22, 3:39 AM Sophia :: Welcome
15/15
Returns true if the value is not equal to any values returned by the subquery.
Returns true if the value is equal to any value returned by the subquery.
Returns true if the value is not equal to any value returned by the subquery.
Returns true if the value is equal to any values returned by the subquery.
Common mistakes include not differentiating ANY and ALL. With ANY, any of the values in the subquery can be used to compare whereas ALL refers to all values being
retuned to compare. It is also important to differentiate greater than and less than with the equal to as a comparison.
CONCEPT
ANY and ALL Operators
About
Contact Us
Privacy Policy
Terms of Use
[Show More]
.png)
