Intro To Relational Databases Unit 1 Milestone 1

Given the customer table and the data that it contains, and assuming that you have the rights to modify the table, which of the following ALTER TABLE statements would work without errors?

ALTER TABLE customer ALTER COLUMN company TYPE TEXT;

Identify the correctly constructed ALTER TABLE statement that removes the address column from the customer table.

ALTER TABLE customer DROP address;

Given the employee table and the data that it contains, and 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 employee_id TYPE VARCHAR (50);

Identify the correctly constructed ALTER TABLE statement to add a UNIQUE constraint to the column driver_license with the constraint name driver_license_unique on the table called 'identification'.

ALTER TABLE identification ADD CONSTRAINT driver_license_unique UNIQUE (driver_license);

Select the correctly constructed CHECK constraint to validate the date_of_birth column of type data, to ensure that values placed into it are greater than 1850-01-01

CHECK (date_of_birth > '1850-01-01')

Using the LIKE operator in the WHERE clause, filter the customer table to list the individuals located in a country that ends with the lowercase 'a.' Identify the 5th individual's country.

Canada

Which of the following statement(s) would successfully delete the playlist_id 8 from the playlist table?

DELETE from playlist_track WHERE playlist_id = 8;DELETE from playlist WHERE playlist_id = 8;

Given the tables provided, which of the following DROP TABLE series of statements would correctly remove the tables without causing an error?

DROP TABLE invoice_line;DROP TABLE playlist_track;DROP TABLE playlist;

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.

Dog Eat Dog

Using the IN operator, filter the album table to find those with the artist ID set to 8, 17, 22, or 3. Identify the title of the 4th record.

Minha Historia

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?

Not The Doctor

Using the WHERE and HAVING clauses, filter the track table for the tracks with 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's 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 trackWHERE media_type_id = 1GROUP BY genre_idHAVING count(track_id) > 10

Identify the SQL command that uses an aggregate function that could be used to find the newest employee in the employee table.

SELECT max(hire_date) FROM employee;

Which of these SELECT statements would successfully display exactly four columns of data from the track table?

SELECT track_id, name, album_id, millisecondsFROM track;

What is the main function of the FROM clause in SQL?

To identify one or more tables as the source for a query

Using the BETWEEN operator, filter the album table to find the albums with an artist ID between 5 and 10. Identify the 5th album ID.

11

Using the GROUP BY clause and the count aggregate function, filter the track table to group the tracks based on album_id. How many tracks are in album 99?

12

Using the SELECT statement, query the track table to find the total price for all tracks that have the genre_id equal to 2.

128.7

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.

15

CREATE TABLE artist(artist_id INTPRIMARYKEY,first_name VARCHAR (50),last_name VARCHAR (50)); Identify the line of code that would generate an error in this CREATE TABLE statement

2

CREATE TABLE artists(artist_id serial INT,first_name VARCHAR (50),last_name VARCHAR (50)); Identify the line of code that would either generate a syntax, logical, or requirements error in this CREATE TABLE statement.

2

Using the WHERE clause, filter the invoice table to find the invoices dated prior to January 19th, 2009. Identify the invoice date closest to that invoice.

2009-01-11

Using the SELECT statement, query the track table to find the maximum bytes where the milliseconds are less than 11650.

319888

Using the AND or OR statement, filter the album table for an album that has Disc 1 in the title or Disc 2 in the title. Identify the album ID of the 4th record.

33

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.

44

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

5

Using the SELECT statement, query the invoice table to find the average total cost for all orders placed in the country France.

5.6285714285714286

Using the SELECT statement, query the customer table to find the number of customers that live in the country Canada.

8

Which of these constraints verifies that data in a column is based on columns in other tables?

FOREIGN KEY

Consider the following new table: CREATE TABLE contact(user_id SERIAL PRIMARY KEY,phone VARCHAR NOT NULL); Given this new table, which INSERT statement would query from the customer table to insert the phone number of all customers that have an email address that contains the word "apple" in it?

INSERT INTO contact (phone)SELECT phone FROM customerWHERE email LIKE '%apple%';

Which of the following UPDATE statements would set the postal_code of any customer who lives in the city of Berlin to 10789?

UPDATE customerSET postal_code = '10789'WHERE city = 'Berlin'

Which of the following would set the postal_code of the customer with the customer_id equal to 22, to 33433.

UPDATE customerSET postal_code = '33433'WHERE customer_id = 22

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

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'), (552, 'Taylor Swift')

Consider the following table:CREATE TABLE artwork(artwork_id SERIAL PRIMARY KEY,artwork_name VARCHAR NOT NULL); 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 this table?

insert into artwork (artwork_name) values ('Mona Lisa' )

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 (30, 'New Age Playlist' )