Western Governors University DATABASE 0047relational_database_unit_1_milestones_1 UNIT 1 — MILESTONE 1 Score 35/36

Document Content and Description Below

UNIT 1 — MILESTONE 1 Score 35/36 You passed this Milestone 35 questions were answered correctly. 1 question was answered incorrectly. 1 In each milestone, you may want or need to use the datab... ase 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 would be a type of constraint? Declaring a data type Naming the table Creating a table Naming the columns CONCEPT Table Constraints 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/ What is the main function of the SELECT clause in SQL? To identify one or more tables as the source for a query To apply conditions to filter the dataset To retrieve rows from one or more table columns CONCEPT SQL Clauses 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 is a correctly formatted INSERT statement that will insert three records into the artist table? insert into artist (artist_id, name) values (550, 'Lady Gaga'), (551, 'Ed Sheeran'), (551, 'Taylor Swift') insert into artist (name, artist_id) values (550, 'Lady Gaga'), (551, 'Ed Sheeran'), (552, 'Taylor Swift') insert into artist (artist_id, name) values (550, 'Lady Gaga') (551, 'Ed Sheeran') (552, 'Taylor Swift') insert into artist (artist_id, name) values (550, 'Lady Gaga'), (551, 'Ed Sheeran'), (552, 'Taylor Swift') CONCEPT INSERT INTO to Add Multiple Rows 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/ Using the ORDER BY clause, sort the Customer table by the company name of the customer in ascending order and identify the 10th company name in the list from among the answer options. Woodstock Discos Murray Martins null CONCEPT ORDER BY to Sort Data 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/ Given the new table which insert statement would query from the customer table to insert the email, fax, and email of all customers in the right columns? CREATE TABLE surveylist( surveylist_id SERIAL PRIMARY KEY, email VARCHAR NOT NULL, fax VARCHAR NOT NULL, email VARCHAR ); INSERT INTO surveylist (email, fax, email) SELECT email, fax, email FROM customer; INSERT INTO surveylist (email, email, fax) SELECT email, fax, email FROM customer; INSERT INTO surveylist SELECT email, fax, email FROM customer; INSERT INTO surveylist (surveylist_id, email, fax, email) SELECT email, fax, email FROM customer; CONCEPT INSERT to Add Queried Data 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/ Using the WHERE clause, filter the track table to include the tracks that are on album_id 4. Identify the name of the 2nd track listed. Go Down AC/DC Dog eat Dog Let there Be Rock CONCEPT WHERE to Filter Data 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/ Identify the correctly constructed ALTER TABLE statement that removes the column age from the user table. ALTER TABLE user DROP COLUMN age; ALTER TABLE user DROP COLUMN age_now; ALTER TABLE user DROP age int; ALTER TABLE user ADD COLUMN age int; CONCEPT ALTER TABLE to Change Columns: Add/Drop 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/ Using the WHERE and HAVING clauses, filter the track table for the tracks having the media_type_id set to 1 grouped by the genre_id having the number of tracks greater than 10. Provide the list of genre_id and the count of tracks that fit these criteria. Which of the following queries would provide the correct results? SELECT genre_id, count(track_id) FROM track WHERE media_type_id = 1 GROUP BY genre_id HAVING count(track_id) > 10 SELECT genre_id, count(track_id) FROM track WHERE media_type_id = 1 HAVING count(track_id) > 10 GROUP BY genre_id SELECT genre_id, count(track_id) FROM track WHERE count(track_id) > 10 GROUP BY genre_id HAVING media_type_id = 1 SELECT genre_id, sum(track_id) FROM track WHERE media_type_id = 1 GROUP BY genre_id HAVING sum(track_id) > 10 CONCEPT Filters to Specify Data 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/ Using the SELECT statement, query the track table to find the max bytes where the milliseconds is less than 11650. 319888 1059546140 387360 38747 CONCEPT MAX & MIN to Find Extremes 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 would set the company of the customer with the customer_id = 24 to Apple Inc.? UPDATE customer WHERE customer_id = 24 SET company = 'Apple Inc.' UPDATE customer SET company = 'Apple Inc.' WHERE customer_id = 24 UPDATE customer SET company = Apple Inc. WHERE customer_id = 24 UPDATE customer SET company = 'Apple Inc.' AND customer_id = 24 CONCEPT UPDATE to Edit Row 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/ Identify the line of code that would generate an error in the following CREATE TABLE statement. This statement creates a table called users that consists of the user_id as the primary key, the username, and password. CREATE TABLE user( user_id int PRIMARY KEY, username VARCHAR 50, password VARCHAR (50) ); 1 3 2 4 using ( ) around the entire table set. CONCEPT CREATE TABLE Syntax 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/ Using the AND or OR statement, filter the employee table for employees that has the title that starts with Sales and has the address that has Ave in it. Identify the last name of the 3rd record. Steve Margaret Johnson should apply. CONCEPT Multiple Filters 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/ Identify the line of code that would either generate a syntax, logical, or requirements error in the following CREATE TABLE statement. The statement should create a table called 'album' that consists of the album_id as the primary key that is auto-incremented, the title, and artist_id. CREATE TABLE album( album_id serial PRIMARY KEY, title VARCHAR, artist_id int ); 2 4 3 1 CONCEPT Primary Key and Auto-increment 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/ Using the SELECT statement, query the customer table to find the number of customers that do not live in the country France. CONCEPT COUNT to Count Records 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 invoice table and the data that it contains, assuming that you have the rights to modify the table, which of the following ALTER TABLE statements would work without errors? ALTER TABLE invoice ALTER COLUMN billing_state TYPE BOOLEAN; ALTER TABLE customer ALTER COLUMN billing_city TYPE VARCHAR (1); ALTER TABLE invoice ALTER total TYPE VARCHAR (100); ALTER TABLE invoice ALTER COLUMN total TYPE VARCHAR (100); CONCEPT ALTER TABLE to Change Columns: Data Type 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/ Given the employee table and the data that it contains, assuming that you have the rights to modify the table, which of the following ALTER TABLE statements would create an error. ALTER TABLE employee ALTER COLUMN city TYPE VARCHAR (100); ALTER TABLE employee ALTER COLUMN employee_id TYPE VARCHAR (50); ALTER TABLE employee ALTER COLUMN postal_code TYPE VARCHAR (100); ALTER TABLE employee ALTER COLUMN email TYPE VARCHAR (100); CONCEPT ALTER TABLE to Change Columns: Data Characteristics 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/ Using the SELECT statement, query the track table to find the total cost of the tracks on album_id 5 rounded to the nearest dollar. 14.9 15 14.85 14 CONCEPT ROUND to Round Numbers 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/ Using the GROUP BY clause and the count aggregate function, filter the track table grouped based on album_id. How many tracks are in album 99? 11 12 10 13 CONCEPT GROUP BY to Combine Data 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/ Identify the correctly constructed ALTER TABLE statement to add a UNIQUE constraint to the column student_number with the constraint name student_number_unique on the table student. ALTER TABLE student ADD CONSTRAINT student_number UNIQUE (student_number); ALTER TABLE student ADD CONSTRAINT student_number UNIQUE (student_number_unique); ALTER TABLE student ADD CONSTRAINT student_number_unique UNIQUE (student_number); ALTER TABLE student ADD UNIQUE student_number CONSTRAINT (student_number_unique); CONCEPT UNIQUE to Validate Data 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/ USING the SELECT statement, query the track table ordered by the track_id. Set the LIMIT to 8 and OFFSET to 40. What is the name of the last row returned? Hand In My Pocket Ironic Not The Doctor Right Through You CONCEPT LIMIT and OFFSET to Cap Results 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/ Select the correctly constructed CHECK constraint to validate the signup_date column of type data to ensure that values placed into it are between 2020-01-01 and 2025-01-01. CHECK (signup_date BETWEEN '2020-01-01' OR '2025-01-01') CHECK (signup_date IN '2020-01-01' AND '2025-01-01') CHECK (signup_date BETWEEN '2020-01-01' AND '2025-01-01') CHECK (signup_date BETWEEN 2020-01-01 AND 2025-01-01) CONCEPT CHECK to Validate Data 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/ Using the SELECT statement, query the invoice table to find the average total cost for all orders placed in the country France. 5.6285714285714286 5.7135278514588859 5.8021978021978022 5.64 CONCEPT AVG to Average Values 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 INSERT statement that will successfully add a new record that uses the auto-incremented primary key into the following table? CREATE TABLE artwork( artwork_id SERIAL PRIMARY KEY, artwork_name VARCHAR NOT NULL ); insert into artwork (artwork_name) values ('Mona Lisa' ) insert into artwork (artwork_id, artwork_name) values (nextval, 'Mona Lisa') insert into artwork (artwork_id, artwork_name) values (1, 'Mona Lisa' ) insert into artwork (artwork_name) values (Mona Lisa) CONCEPT INSERT to Add Data 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 these SELECT statements would successfully display exactly 4 columns of data from the track table? SELECT track_id name album_id milliseconds FROM track; SELECT * FROM track; SELECT track_id, name, album_id, milliseconds FROM track; SELECT Track_id and name and album_id and milliseconds FROM track; CONCEPT SELECT to Display Data 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/ Using the LIKE operator in the WHERE clause, use the necessary wildcards to filter the album table to find the albums that have Disc 1 in the title. Identify the 6th album ID. CONCEPT LIKE Wildcards 26 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 provided, which of the following DROP TABLE series of statements would correctly remove the tables without causing an error? DROP TABLE playlist_track; DROP TABLE invoice_line; DROP TABLE invoice; DROP TABLE genre; DROP TABLE album; DROP TABLE artist; DROP TABLE playlist_track DROP TABLE playlist; DROP TABLE genre; DROP TABLE invoice; DROP TABLE invoice_line; DROP TABLE customer; CONCEPT DROP TABLE to Remove Tables 27 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/ Identify the SQL command that uses an aggregate function that could be used to find the youngest employee in the employee table. SELECT max birth_date FROM employee; SELECT max(birth_date) FROM employee; SELECT min(birth_date) FROM employee; SELECT count(birth_date) FROM employee; CONCEPT Aggregate Functions 28 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/ Using the WHERE clause, filter the invoice table to find the invoices that were invoiced on January 15th, 2011. Identify the second customer ID of the invoice. 27 35 29 31 CONCEPT Filter by Date 29 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 would set the company of the customers that live in the city Prague to Humor Inc.? UPDATE customer SET company = Humor Inc. WHERE city = 'Prague' UPDATE customer SET company = 'Humor Inc.' AND city = 'Prague' UPDATE customer SET company = 'Humor Inc.' WHERE city = 'Prague' UPDATE customer WHERE city = 'Prague' SET company = 'Humor Inc.' CONCEPT UPDATE to Edit Multiple Rows 30 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/ Using the BETWEEN operator, filter the album table to find the albums with the artist ID between 18 and 30. Identify the 7th album ID. 29 30 127 31 CONCEPT BETWEEN to Filter Data 31 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 statement(s) would successfully delete the invoice_id 280 from the invoice table? DELETE FROM invoice WHERE invoice_id = 280; DELETE FROM invoice_line WHERE invoice_id = 280; DELETE FROM invoice WHERE invoice_id = 280; DELETE FROM invoice_line WHERE invoice_id = 280; DELETE FROM invoice WHERE invoice_id = 280; DELETE FROM invoice_line WHERE invoice_id = 280; CONCEPT DELETE FROM to Remove Row 32 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/ Using the SELECT statement, query the invoice table to find the total cost for all orders placed by the customer_id that's equal to 2. 2313 2 7 38 CONCEPT SUM to Add Values 33 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 INSERT statement that will successfully add a record into the playlist table? insert into playlist (playlist_id, name) values (40, New Age Playlist) insert into playlist (playlist_id, name) values ('New Age Playlist',35) insert into playlist (playlist_id, name) values (19, 'New Age Playlist' ) insert into playlist (playlist_id, name) values (30, 'New Age Playlist' ) CONCEPT INSERT INTO to Add Row 34 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/ Using the LIKE operator in the WHERE clause, filter the customer table to list the individuals that have the country that ends with the lowercase 'a.' Identify the 5th individual's country. Australia Canada Austria USA CONCEPT LIKE to Search Data 35 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/ Using the IN operator, filter the album table to find the artist ID of 11, 18, 9, or 4. Identify the title of the 1st record. Alcohol Fueled Brewtality Live! [Disc 1] Jagged Little Pill BackBeat Soundtrack Afrociberdelia CONCEPT IN to Filter Data 36 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/ Using the GROUP BY and HAVING clause, filter the customer table by country. How many countries have less than 2 customers? 18 0 15 17 CONCEPT HAVING to Filter On Aggregates About Contact Us Privacy Policy Terms of Use [Show More]

Last updated: 1 year ago

Preview 1 out of 26 pages