unite 1 milestone 1

Using the AND or OR statement, filter the customer table for customers that has the address that starts with a 1 and lives in the country USA. Identify the last name of the 2nd record. Silk Harris Goyer Smith

Smith

Given the track 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? A ALTER TABLE track ALTER COLUMN name TEXT; B ALTER TABLE track ALTER COLUMN bytes TYPE VARCHAR (1); C ALTER TABLE track ALTER composer TYPE VARCHAR (500); D ALTER TABLE track ALTER COLUMN bytes TYPE VARCHAR (100);

D ALTER TABLE track ALTER COLUMN bytes TYPE VARCHAR (100);

Which of the following is a correctly formatted INSERT statement that will successfully add a record into the artist table? A INSERT INTO artist (artist_id, name) VALUES ('New Artist', 400 ); B INSERT INTO artist (artist_id, name) VALUES (5, 'New Artist' ); C INSERT INTO artist (artist_id, name) VALUES (400, New Artist); D INSERT INTO artist (artist_id, name) VALUES (400, 'New Artist' );

D INSERT INTO artist (artist_id, name) VALUES (400, 'New Artist' );

Which of the following UPDATE statements would successfully update the employee table, where the first name is Robert and the last name is King, to set the reports_to value to 1 and set the title to IT Manager? A UPDATE employee WHERE last_name = 'King' AND first_name = 'Robert' SET reports_to = 1, title = 'IT Manager'; B UPDATE employee SET reports_to = 1 AND title = 'IT Manager' WHERE last_name = 'King' AND first_name = 'Robert'; C UPDATE employee SET reports_to = 1, title = 'IT Manager' WHERE last_name = 'King', first_name = 'Robert'; D UPDATE employee SET reports_to = 1, title = 'IT Manager' WHERE last_name = 'King' AND first_name = 'Robert';

D UPDATE employee SET reports_to = 1, title = 'IT Manager' WHERE last_name = 'King' AND first_name = 'Robert';

Using the LIKE operator in the WHERE clause, filter the customer table to list the individuals that have a first name that starts with F. Identify the 4th individual's customer ID. 16 24 13 37

16

Using the BETWEEN operator, filter the invoice table to find the invoices that were invoiced between 2009-06-01 and 2009-06-30. Identify the 3rd customer ID. 17 13 15 21

17

Select the correctly constructed CHECK constraint to validate the mailing_list column of type char to ensure that values placed into it are either Y or N. CHECK (mailing_list = 'Y' OR 'N') CHECK (mailing_list = Y OR mailing_list = N) CHECK (mailing_list = 'Y' AND mailing_list = 'N') CHECK (mailing_list = 'Y' OR mailing_list = 'N')

CHECK (mailing_list = 'Y' OR mailing_list = 'N')

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 group of terms represents the three main SQL clauses? SOURCE, FOR, and WHERE SOURCE, FOR, and WHAT SELECT, FROM, and WHERE SELECT, FROM, and WHAT

SELECT, FROM, and WHERE

Using the WHERE clause, filter the customer table to include individuals that live in the country of Brazil. Identify the company of the 2nd individual listed. Martins Rocha Prague Woodstock Discos

Woodstock Discos

Using the GROUP BY and HAVING clause, filter the customer table by country. How many countries have more than 5 customers? 2 3 5 4

2

Using the SELECT statement, query the invoice table to find the maximum invoice_date where the billing_country is equal to USA. 2013-12-05 2013-12-06 2013-12-22 2009-01-11

2013-12-05

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 251? 15 9 17 25

25

Using the SELECT statement, query the track table to find the average length of a track that has an album_id equal to 10. 244370.8837 393599.2121 280550.9286 394052.8309

280550.9286

Using the WHERE clause, filter the invoice table to find the invoices that were invoiced on or after June 1st, 2013. Identify the first customer ID of the invoice. 29 31 33 37

29

Identify the line of code that would generate an error in the following CREATE TABLE statement. This statement should create a table called department that consists of the department_id as the primary key, the department_name, and the manager_id. 1 CREATE TABLE department( 2 department_id int PRIMARY KEY, 3 department_name, 4 manager_id int 5 ); 2 3 4 1

3

Using the SELECT statement, query the track table to find the number of tracks that are in the genre_id column and not equal to 3. 2206 3503 3129 374

3129

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 1. 4 40 7 2311

40

Using the SELECT statement, query the invoice table to find the average total cost for all orders purchased by customer_id not equal to 5, rounded to the nearest cent. 5.71 5.704 5.70 5.706

5.70

Using the LIKE operator in the WHERE clause, use the necessary wildcards to filter the tracks table to find the tracks that have Toni anywhere in the composer's field. Remember that capitalization is important in the search query. Identify the 5th track's song. A Estrada Podes Crer Firmamento Berlim

Podes Crer

Using the IN operator, filter the album table to find the artist IDs that include 1, 68, 58, or 27. Identify the title of the 6th record. Fireball The Final Concerts [Disc 2] Come Taste the Band Deep Purple In Rock

The Final Concerts [Disc 2]

The statement should create a table called department that consists of the department_id as the primary key that is auto-incremented, the department_name, and the manager_id. 1 CREATE TABLE department( 2 department_id serial, 3 department_name VARCHAR (100), 4 manager_id int 5 ); 3 1 4 2

2

Which of the following is a correctly formatted INSERT statement that will insert three records into the album table? A INSERT INTO album (artist_id, title, album_id) VALUES (1, 'My Album', 348), (1, 'Newest Album', 349), (1, 'Great Album', 350); B INSERT INTO album (artist_id, title, album_id) VALUES (1, 'My Album', 348), (1, 'Newest Album','Song Writer') (1, 'Great Album', 350); C INSERT INTO album (artist_id, title, album_id) VALUES (1, 'My Album', 348) (1, 'Newest Album', 349) (1, 'Great Album', 350); D INSERT INTO album (album_id, title, artist_id) VALUES (1, 'My Album', 348), (1, 'New Album', 349), (1, 'Great Album', 350);

A INSERT INTO album (artist_id, title, album_id) VALUES (1, 'My Album', 348), (1, 'Newest Album', 349), (1, 'Great Album', 350);

Identify the correctly constructed ALTER TABLE statement to add a UNIQUE constraint to the column address with the constraint name address_unique on the table employee. ALTER TABLE employee ADD CONSTRAINT address UNIQUE (address); ALTER TABLE employee ADD CONSTRAINT address UNIQUE (address_unique); ALTER TABLE employee ADD CONSTRAINT address_unique UNIQUE (address); ALTER TABLE employee ADD UNIQUE address CONSTRAINT (address_unique);

ALTER TABLE employee ADD CONSTRAINT address_unique UNIQUE (address);

Given the track 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 track ALTER COLUMN track_id TYPE VARCHAR (50);

Identify the correctly constructed ALTER TABLE statement that removes the column password from the user table.

ALTER TABLE user DROP COLUMN password;

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 song( song_id SERIAL PRIMARY KEY, song_name VARCHAR NOT NULL ); A INSERT INTO song (song_id, song_name) VALUES (nextval, 'Happy Birthday'); B INSERT INTO song (song_name) VALUES ('Happy Birthday' ); C INSERT INTO song (song_id, song_name) VALUES (1, 'Happy Birthday' ); D INSERT INTO song (song_name) VALUES (Happy Birthday);

B INSERT INTO song (song_name) VALUES ('Happy Birthday' );

Using the ORDER BY clause, sort the Customer table by the city of the customer in ascending order and identify the 9th row's name in the list from among the answer options. Brazil São José dos Campos Budapest Stockholm

Budapest

Which of the following statement(s) would successfully delete all of the employees with the title IT Staff from the employee table? A DELETE FROM employee; B DELETE FROM employee WHERE title = IT Staff; C DELETE FROM employee WHERE title = 'IT Staff'; D DELETE FROM employee WHERE employee_id = 'IT Staff';

C DELETE FROM employee WHERE title = 'IT Staff';

Given the tables provided, which of the following DROP TABLE series of statements would correctly remove the tables without causing an error? A DROP TABLE genre; DROP TABLE album; DROP TABLE artist; B DROP TABLE playlist_track DROP TABLE playlist; DROP TABLE genre; C DROP TABLE track; DROP TABLE playlist_track; DROP TABLE playlist; D DROP TABLE invoice_line; DROP TABLE playlist_track; DROP TABLE invoice;

D DROP TABLE invoice_line; DROP TABLE playlist_track; DROP TABLE invoice;

Which of the following UPDATE statements would update the unit_price to 1.29 where the genre_id is set between 2 and 5? A UPDATE track WHERE genre_id BETWEEN 2 AND 5 SET unit_price = 1.29; B UPDATE track SET unit_price = 1.29 WHERE track_id BETWEEN 2 AND 5; C UPDATE track SET unit_price = 1.29 WHERE genre_id BETWEEN 2 AND 5; D UPDATE track SET unit_price = 1.29 WHERE genre_id BETWEEN 2, 5;

DUPDATE trackSET unit_price = 1.29WHERE genre_id BETWEEN 2, 5; ...

USING the SELECT statement, query the track table ordered by the track_id. Set the LIMIT to 5 and OFFSET to 5. What is the name of the last row returned? Princess of the Dawn Put The Finger On You Evil Walks Snowballed

Evil Walks

Given the new table, which insert statement would query from the customer table to insert the phone of all customers that live in the country Canada? CREATE TABLE contact( user_id SERIAL PRIMARY KEY, phone VARCHAR NOT NULL );

INSERT INTO contact (phone) SELECT phone FROM customer WHERE country = 'Canada';

Using the WHERE and HAVING clauses, filter the invoice table for the billing_address starting with the number 1, and grouped by the country having the minimum total of orders less than 2. Provide the list of countries and the sum of the totals that fit these criteria. Which of the following queries would provide the correct results?

SELECT billing_country, SUM(total) FROM invoice WHERE billing_address LIKE '1%' GROUP BY billing_country HAVING MIN(total) < 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 these SELECT statements would successfully display exactly two columns of data from the Invoice table?

SELECT invoice_id, total FROM invoice;

Identify the SQL command that uses an aggregate function that could be used to find the oldest (by age) employee in the employee table.

SELECT min(birth_date) FROM employee;

Which of these constraints ensures that all values in a column are different, although they may be empty? UNIQUE NOT NULL PRIMARY KEY FOREIGN KEY

UNIQUE