Western Governors University DATABASE 0047relational_database_unit_2_milestones_2 UNIT 2 — MILESTONE 2 Score 17/25

Document Content and Description Below

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 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/ 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? <columnname> <= ANY (<subquery>) 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]

Last updated: 1 year ago

Preview 1 out of 23 pages