Sophia __ Welcome milestone 2 test
pdf
keyboard_arrow_up
School
Southern New Hampshire University *
*We aren’t endorsed by this school
Course
CS-210
Subject
Information Systems
Date
Feb 20, 2024
Type
Pages
35
Uploaded by Matthewdbills
1
18/25
that's 72%
This Milestone has been retaken.
18 questions were answered correctly
.
7 questions were answered incorrectly
.
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, title DESC;
UNIT 2 — MILESTONE 2
18/25
CONCEPT
→
VIEW & Complex Queries
2
RATIONALE
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.
Report an issue with this question
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 is incorrect regarding the following statement intended to
create a VIEW?
●
●
SELECT *
FROM album, artist
ORDER BY name ASC, title DESC;
●
SELECT *
FROM album_artist_names
ORDER BY name DESC, title ASC;
●
SELECT *
FROM album_artist_names
ORDER BY name DESC, title;
UNIT 2 — MILESTONE 2
18/25
CONCEPT
→
VIEW to Provide a Subset
3
CREATE VIEW priority_invoices AS
FROM invoice
WHERE total > 100;
RATIONALE
Views will persist in the database after they've been created.
The data that's accessed within a VIEW is the same as if we
were accessing the data from the corresponding tables. The
fields that belong to the result set should be specified.
Report an issue with this question
●
●
The fields that should belong in
the result set are not specified.
It's not possible to create a view
that only shows a subset of the
data from a table.
●
The name "priority_invoices" is
not a table that exists in the
database.
●
The name of the VIEW belongs
after the word "AS".
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
DROP VIEW to Remove Views
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 a valid DROP VIEW
statement to remove the two views, which will also display an
error if either view doesn't exist?
RATIONALE
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.
DROP VIEW IF EXISTS
album_cost, album_artist_names;
●
DROP VIEW album_cost,
album_artist_names CASCADE;
●
DROP VIEW album_cost AND
album_artist_names;
●
DROP VIEW album_cost,
album_artist_names;
UNIT 2 — MILESTONE 2
18/25
4
Report an issue with this question
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 true of foreign keys?
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
●
A foreign key may be linked to a
unique column that establishes a
1 to 1 relationship.
●
A foreign key is not needed if the
data type is different.
●
Foreign keys are not needed
when we require referential
integrity.
●
A foreign key can be linked to a
NOT NULL column.
UNIT 2 — MILESTONE 2
18/25
CONCEPT
→
Foreign and Primary Keys
5
after tables have been created to avoid having to generate
them in the right order.
Report an issue with this question
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:
Outfit
outfit_id
name
ClothingPiece
piece_id
name
OutfitPiece
outfit_piece_id
outfit_id
piece_id
Which of the following is a situation where an OUTER JOIN
could be useful?
●
●
To view outfits with just one
clothing piece
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
Outer Joins
6
RATIONALE
Remember that the full outer join will return the rows that
match between two tables, as well as the rows from each
individual table that do not have matches in the other table.
Report an issue with this question
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?
●
To view all the clothing pieces,
even if they haven't been
associated with an outfit in the
outfits table
●
To view clothing pieces that are
assigned to multiple outfits
●
To view clothing pieces that have
already been assigned to outfits
UNIT 2 — MILESTONE 2
18/25
CONCEPT
→
JOIN USING to Link By Column
RATIONALE
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.
●
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);
UNIT 2 — MILESTONE 2
18/25
7
Report an issue with this question
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 ±_donor
FOREIGN KEY (organization_id)
REFERENCES donor
(organization_id);
●
ALTER TABLE donor
ADD CONSTRAINT
FOREIGN KEY (organization_id)
REFERENCES organization
(organization_id);
ALTER TABLE donor
ADD CONSTRAINT ±_donor
FOREIGN KEY (organization_id)
REFERENCES organization
(organization_id);
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
Foreign Keys & Altering Tables
8
RATIONALE
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.
Report an issue with this question
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?
●
●
ALTER TABLE donor
ADD CONSTRAINT ±_donor
FOREIGN KEY organization
(organization_id)
REFERENCES organization_id;
●
SELECT media_type_id,
AVG(bytes/milliseconds)
FROM track;
UNIT 2 — MILESTONE 2
18/25
CONCEPT
→
Calculations in SELECT Statements
9
RATIONALE
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.
Report an issue with this question
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 media_type_id,
(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,
AVG(bytes/milliseconds)
FROM track
GROUP BY media_type_id;
UNIT 2 — MILESTONE 2
18/25
Genre
genre_id
name
1
Broadway
2
Rock
3
Classical
4
Salsa
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);
●
1
●
2
4
●
3
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
Right Joins
10
RATIONALE
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.
Report an issue with this question
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 media type ID with track
name
●
Showing media type name with
track name
Showing track ID, media type ID,
and track name
UNIT 2 — MILESTONE 2
18/25
CONCEPT
→
Joins
11
Report an issue with this question
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?
●
●
Showing track name with track 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 mediatype.media_type_id =
track.media_type_id;
UNIT 2 — MILESTONE 2
18/25
CONCEPT
→
JOIN ON to Link Tables
12
RATIONALE
Common mistakes with JOIN ON 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.
Report an issue with this question
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 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;
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
Foreign Keys & Referential Data
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?
RATIONALE
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. As it is only a playlist that should be removed,
the playlist_tracks need to be removed first and then the
playlist.
Report an issue with this question
●
playlist
playlist_track
track
playlist
playlist_track
●
track
playlist_track
playlist
●
playlist_track
playlist
UNIT 2 — MILESTONE 2
18/25
13
CONCEPT
→
Natural Joins
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?
RATIONALE
Remember that the natural join only allows you to combine
data between two or more tables that have common columns.
●
●
When two tables have a foreign
key relationship
●
When columns in two separate
tables contain the same data
●
When the tables being joined
have only one column each other
than the primary key
When the tables being joined
both contain a column with the
same name and data type
UNIT 2 — MILESTONE 2
18/25
14
Report an issue with this question
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 coordinators and their email addresses?
●
●
Email
email_id
coordinator_name
email_type
email_address
Coordinator
coordinator_id
coordinator_name
Email
email_id
coordinator_id (FK)
email_type
email_address
●
Coordinator
coordinator_id
coordinator_name
email_type_1
email_address_1
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
Foreign Keys & Creating Tables
15
RATIONALE
Solving this question involves identifying that a foreign key is
needed, and where it should belong in terms of the
relationship. Here, the coordinator table is the parent table, and
the email table is the child table, because the coordinator is the
"source" of the relationship. The primary key of the coordinator
table is coordinator_id. In the email table, coordinator_id is the
foreign key, which relates the two tables.
Report an issue with this question
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/
●
email_type_2
email_address_2
●
Coordinator
coordinator_id
coordinator_name
email_id (FK)
Email
email_id
email_type
email_address
UNIT 2 — MILESTONE 2
18/25
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
UNIT 2 — MILESTONE 2
18/25
CONCEPT
→
Subqueries
16
RATIONALE
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.
Report an issue with this question
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 employee table LEFT JOINed
the customer table?
●
WHERE media_type_id = 1)
AND composer = 'Miles Davis';
All employees, even those that
aren't supporting customers
●
Only customers that have
employees associated with them
and vice-versa
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
Left Joins
17
RATIONALE
The hardest thing to understand with LEFT JOINs is the pure
mechanics of the result set returned. It matters which table is in
the FROM clause vs. in the JOIN clause (the FROM clause is
"prioritized" in the result set).
Report an issue with this question
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?
●
●
Only employees that have
customers that they support
●
All rows from the customer table
even if they don't have an
employee supporting them
CREATE VIEW customer_order
AS
SELECT invoice.customer_id,
first_name, last_name, SUM(total)
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
as total
FROM invoice
INNER JOIN customer
ON invoice.customer_id =
customer.customer_id
GROUP BY invoice.customer_id,
first_name, last_name;
●
CREATE VIEW customer_order
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;
●
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;
●
CREATE VIEW customer_order
AS
SELECT invoice.customer_id,
first_name, last_name, SUM(total)
as total
ON invoice.customer_id =
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
VIEW to Simplify Queries
18
RATIONALE
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.
Report an issue with this question
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?
<columnname> < ALL (<subquery>)
●
customer.customer_id
GROUP BY invoice.customer_id,
first_name, last_name;
●
Returns true if the value is less
than any of the values returned
by the subquery.
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
ANY and ALL Operators
19
RATIONALE
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 returned to
compare. It is also important to differentiate greater than and
less than with the equal to as a comparison.
Report an issue with this question
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/
●
●
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 or equal to any of the values
returned by the subquery.
Returns true if the value is less
than the smallest value returned
by the subquery.
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
Find Duplicate Rows
Which of the following queries would check for duplicates of a
track's composer in the track table and how many there are of
each?
RATIONALE
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. The COUNT can check on * for all
columns.
SELECT composer, COUNT(*)
FROM track
GROUP BY composer
HAVING COUNT(*) > 1;
●
SELECT composer, COUNT(*)
FROM track
HAVING COUNT(*) > 1;
●
SELECT composer
FROM track
GROUP BY composer
HAVING COUNT(*) > 1;
●
SELECT track_id, COUNT(*)
FROM track
GROUP BY track_id
HAVING COUNT(*) > 1;
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
20
Report an issue with this question
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 #1 would be more efficient
as it is not using indexed
columns.
Query #2 would be more efficient
as it is based on primary and
foreign keys.
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
Subquery Performance
21
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.
Report an issue with this question
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?
●
●
Query #1 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.
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
CREATE OR REPLACE VIEW to Update Views
22
RATIONALE
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.
Report an issue with this question
●
●
On a daily basis so that the data
is refreshed.
●
The view is no longer being used.
●
Data has been imported from
other databases.
The underlying query is not
efficient and needs to be
updated.
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
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?
RATIONALE
●
SELECT i.customer_id, i.total,
c.last_name
FROM invoice AS i
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);
●
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
JOIN customer AS c
USING (customer_id);
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
AS/ALIAS to Rename Tables and Columns
23
It is important to recognize that 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.
Report an issue with this question
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_±ey" on table "playlist_track"
●
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.
●
The track has to be deleted first
before the playlist is deleted.
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
Foreign Key Errors
24
RATIONALE
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.
Report an issue with this question
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/
Ingredient
ID: 1, Name: Cayenne Pepper
ID: 2, Name: Pasta noodle
ID: 3, Name: Fresh tomato
ID: 4, Name: Sugar
Recipe
ID: 1, Name: Lasagna
ID: 2, Name: Chocolate Chip Cookies
Given the above data for a recipe database, how many records
would be included in the result set for the following query?
●
●
The playlist_id doesn't exist in
the playlist_track table.
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
Cross Joins
25
SELECT Recipe.name, Ingredient.name
FROM Recipe
CROSS JOIN Ingredient;
RATIONALE
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. 4 x 2 = 8.
Report an issue with this question
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/
●
8
●
6
●
2
●
4
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Which of the following statements would create a UNION
between all of the countries we have customers, employees, or
invoices in?
●
SELECT billing_country
FROM invoice
SELECT country
FROM customer
SELECT country
FROM employee
UNION;
●
SELECT billing_country
FROM invoice
UNION
SELECT country
FROM customer
UNION
SELECT country
FROM employee;
●
SELECT billing_country
FROM invoice
SELECT country
FROM customer
SELECT country
FROM employee;
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
UNION to Combine Results
RATIONALE
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.
Report an issue with this question
SELECT country
FROM invoice
UNION
SELECT country
FROM customer
UNION
SELECT country
FROM employee;
About
Contact Us
Privacy Policy
Cookie Policy
Terms of Use
Your Privacy Choices
© 2024 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC.
UNIT 2 — MILESTONE 2
18/25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help