UNIT 2 — MILESTONE 2
Score 17/25
You passed this Milestone
17 questions were answered correctly.
8 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 17/25
You passed this Milestone
17 questions were answered correctly.
8 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/
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 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 MIN(milliseconds)
FROM track
WHERE media_type_id = 1)
AND composer = 'Miles Davis';
CONCEPT
Subqueries
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/
In trying to insert into the playlist_track table, what is the cause of this error?
Query failed because of: error: insert or update on table "playlist_track" violates foreign key constraint
"playlist_track_playlist_id_fkey"
The playlist_id in the playlist_track table doesn't exist yet.
The playlist_track_id is not unique.
The playlist_id needs to be added to the playlist table first.
The playlist_id being referenced doesn't exist in the playlist table.
CONCEPT
Foreign Key Errors
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 result set requires a JOIN?
Showing artist_id with album title
Showing all album titles with both artist ID and album ID
Showing all album_id's with artist names
Showing artist names with artist_id's
CONCEPT
Joins
4
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 needs to have update, insert, and delete statements allowed.
The view is no longer being used.
On a daily basis so that the data is refreshed.
A view has already been created with the same name but needs to be changed.
CONCEPT
CREATE OR REPLACE VIEW to Update Views
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/
Select the query that properly uses table aliases to show the genres of each track in the Album with the id
of 6.
SELECT g(name), t(name)
FROM track AS t
JOIN genre AS g
USING (genre_id)
WHERE t(album_id)=6;
SELECT g.name, t.name
FROM t AS track
JOIN g AS genre
USING (genre_id)
WHERE t.album_id=6;
SELECT g.name, t.name
FROM track AS t
JOIN genre AS g
USING (genre_id)
WHERE t.album_id=6;
SELECT genre.name, track.name
FROM track
JOIN genre
USING (genre_id)
WHERE track.album_id=6;
CONCEPT
AS/ALIAS to Rename Tables and Columns
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/
Which of the following statements would be valid DROP VIEW statements?
DROP VIEW invoice_verification CASCADE;
DROP VIEW invoice_verification;
DROP VIEW CASCADE invoice_verification;
DROP VIEW IF EXISTS invoice_verification;
DROP VIEW to Remove Views
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 may be linked to a unique column that establishes a 1 to 1 relationship.
Foreign keys are not needed when we require referential integrity.
A foreign key can be linked to a NOT NULL column.
Foreign keys are not needed when we require referential integrity.
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/
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
UNION
SELECT city
FROM customer
UNION
SELECT city
FROM employee;
SELECT billing_city
FROM invoice
SELECT city
FROM customer
SELECT city
FROM employee
UNION;
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;
CONCEPT
UNION to Combine Results
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 results would show if the genre table LEFT OUTER JOINed the track table?
Only genres with tracks in the track table
Only genres that don't have tracks in the track table
All genres, even those with no tracks in the track table
All rows from the track table, even those that have NULL genre_id's
CONCEPT
Left Joins
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 data models appropriately models the relationship of recipes and their
ingredients?
recipe
recipe_id
recipe_name
ingredient
ingredient_id
recipe_id (FK)
ingredient_name
ingredient_amount
recipe
recipe_id
recipe_name
ingredient_name_1
ingredient_amount_1
ingredient_name_2
ingredient_amount_2
ingredient
ingredient_id
recipe_name
ingredient_name
ingredient_amount
recipe
recipe_id
recipe_name
ingredient_id (FK)
ingredient
ingredient_id
ingredient_name
ingredient_amount
CONCEPT
Foreign Keys & Creating 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/
Which of the following statements will be able to show the following result set?
SELECT playlist_id, name
FROM track, playlist_track;
SELECT playlist_id, name
FROM track
JOIN playlist_track
USING (track.name = playlist.name);
SELECT playlist_id, name
FROM track
JOIN playlist_track
WHERE track_id != NULL;
SELECT playlist_id, name
FROM track
JOIN playlist_track
USING (track_id);
CONCEPT
JOIN USING to Link By Column
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/
Genre
genre_id name
1 Broadway
2 Rock
3 Classical
Track
track_id name genre_id
1 Highway to Hell 2
2 Symphony #5 3
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);
3 2 4 1
CONCEPT
Right Joins
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/
Given the tables have been created without foreign keys added, which of the following ALTER TABLE
statements would create a foreign key on the organization_id in the donor table to reference the
organization_id in the organization table?
ALTER TABLE organization
ADD CONSTRAINT fk_donor
FOREIGN KEY (organization_id)
REFERENCES donor (organization_id);
ALTER TABLE donor
ADD CONSTRAINT fk_donor
FOREIGN KEY (organization_id)
REFERENCES organization (organization_id);
ALTER TABLE donor
ADD CONSTRAINT fk_donor
FOREIGN KEY organization (organization_id)
REFERENCES organization_id;
ALTER TABLE donor
ADD CONSTRAINT
FOREIGN KEY (organization_id)
REFERENCES organization (organization_id);
CONCEPT
Foreign Keys & Altering Tables
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/
Given the following view that has been created, how would you query it to find the customers that have
ordered more than $30 over their lifetime as a customer?
CREATE VIEW customer_order
AS
SELECT invoice.customer_id, first_name, last_name, SUM(total) as total
FROM invoice
INNER JOIN customer
ON invoice.customer_id = customer.customer_id
GROUP BY invoice.customer_id, first_name, last_name;
SELECT *
FROM customer_order
WHERE total < 30;
SELECT *
FROM customer_order
WHERE lifetime_total > 30;
SELECT *
FROM customer_order
WHERE total > 30;
SELECT *
FROM order
WHERE SUM(total) > 30;
CONCEPT
VIEW & Complex Queries
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/
Given the initial tables in our example database, the organization would like to remove a playlist.
What order should the table data be deleted from?
playlist
playlist_track
playlist_track
playlist
track
playlist_track
playlist
playlist
playlist_track
track
CONCEPT
Foreign Keys & Referential Data
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/
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 every single animal matches with every single adopter.
It represents every single Animal in the animal table regardless of whether they have been adopted or not.
It represents each animal, with the name of their adopter if that has been specified via a Foreign Key.
It represents all adopters regardless of whether they have claimed an animal in the animal table.
CONCEPT
Cross 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/
What will be the result of the query based on the following criteria?
<= ANY ()
Returns true if the value is less than the smallest value returned by the subquery.
Returns true if the value is less than or equal to the smallest value returned by the subquery.
Returns true if the value is less than any of the values returned by the subquery.
Returns true if the value is less than or equal to any of the values returned by the subquery.
CONCEPT
ANY and ALL Operators
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/
Which query would be more efficient?
1. SELECT *
FROM customer
WHERE city IN
(SELECT city
FROM employee
WHERE reports_to = 2);
2. SELECT customer.*
FROM customer
INNER JOIN employee
ON customer.city = employee.city
WHERE reports_to = 2;
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 #1 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.
CONCEPT
Subquery Performance
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/
When is a natural join possible between two tables?
When two tables have a foreign key relationship
When the tables being joined both contain a column with the same name and data type
When the tables being joined have only one column each other than the primary key
When columns in two separate tables contain the same data
CONCEPT
Natural Joins
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/
Which of the following is the valid syntax for creating a VIEW to view data from multiple tables?
CREATE VIEW playlist_track_names
AS
SELECT playlist.name, track.name
FROM playlist
INNER JOIN playlist_track
ON playlist.playlist_id = playlist_track.playlist_id
INNER JOIN track
ON playlist_track.track_id = track.track_id;
CREATE VIEW playlist track names
AS
SELECT playlist.name, track.name
FROM playlist
INNER JOIN playlist_track
ON playlist.playlist_id = playlist_track.playlist_id
INNER JOIN track
ON playlist_track.track_id = track.track_id;
CREATE VIEW playlist_track_names
SELECT playlist.name as playlist_name, track.name as track_name
FROM playlist
INNER JOIN playlist_track
ON playlist.playlist_id = playlist_track.playlist_id
INNER JOIN track
ON playlist_track.track_id = track.track_id;
CREATE VIEW playlist_track_names
AS
SELECT playlist.name as playlist_name, track.name as track_name
FROM playlist
INNER JOIN playlist_track
ON playlist.playlist_id = playlist_track.playlist_id
INNER JOIN track
ON playlist_track.track_id = track.track_id;
CONCEPT
VIEW to Simplify 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 is the valid syntax for creating a VIEW to view a subset of a table?
CREATE VIEW USA_customers
SELECT *
FROM customer
WHERE country = 'USA';
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';
CONCEPT
VIEW to Provide a Subset
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/
Use the following data model for this question:
Fish
fish_id
name
DailyCatch
catch_id
date
Fish_Catch
fish_catch_id
catch_id
fish_id
Which of the following is a situation where an OUTER JOIN could be useful?
When a fisherman wants to see all fish in the database including those that she has caught and those that
she has not caught before
When a fisherman wants to see the fish that belong to a certain species
When a fisherman wants to see only fish in the database that she has caught
When a fisherman wants to see only the fish that she has not caught
CONCEPT
Outer Joins
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/
Which of the following is a correctly formatted SELECT statement to show the following result set with
the media type's name and the track's name?
SELECT media_type.name, track.name
FROM track
JOIN media_type
ON media_type.media_type.id = track.media_type.id;
SELECT media_type.name, track.name
FROM track
JOIN media_type
ON media_type.media_type_id = track.media_type_id;
SELECT media_type.name, track.name
FROM track
JOIN media_type
ON media_type.media_type_id = track.track_id;
SELECT media_type.name, track.name
FROM track
JOIN media_type
ON mediatype.media_type_id = track.media_type_id;
CONCEPT
JOIN ON to Link Tables
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 the duplicate of reports_to WITHOUT returning the
count of each?
SELECT reports_to, count(*)
FROM employee
GROUP BY reports_to
HAVING COUNT(*) > 1;
SELECT reports_to
FROM employee
HAVING COUNT(*) > 1;
SELECT reports_to
FROM employee
GROUP BY reports_to
HAVING COUNT(*) > 1;
SELECT reports_to, count(*)
FROM employee
GROUP BY reports_to
HAVING > 1;
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/
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, AVG(bytes/milliseconds)
FROM track
GROUP BY media_type_id;
SELECT media_type_id, AVG(milliseconds/bytes)
FROM track
GROUP BY media_type_id;
SELECT media_type_id, (bytes/milliseconds)
FROM track
GROUP BY media_type_id;
SELECT media_type_id, AVG(bytes/milliseconds)
FROM track;
CONCEPT
Calculations in SELECT Statements
About
Contact Us
Privacy Policy
Terms of Use
[Show More]
