Western Governors University DATABASE 0047 Sophia Database Milestone 2 UNIT 2 — MILESTONE 2 Score 16/25

Document Content and Description Below

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 dat... abase 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? <columnname> = ALL (<subquery>) 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]

Last updated: 1 year ago

Preview 1 out of 15 pages