About SmartExpert

Levels Tought:
Elementary,Middle School,High School,College,University,PHD

Expertise:

Accounting,Business & Finance See all

Accounting,Business & Finance,Economics,English,HR Management,Math Hide all

Teaching Since:	Apr 2017
Last Sign in:	5 Weeks Ago
Questions Answered:	7559
Tutorials Posted:	7341

Education

BS,MBA, PHD
Adelphi University/Devry
Apr-2000 - Mar-2005

Experience

HOD ,Professor
Adelphi University
Sep-2007 - Apr-2017

Category > Programming Posted 24 Feb 2023 My Price 25.00

Introduction to Relational Database Management Systems Unit 1 Milestone

CIS 111 SOPHIA-STRAYER Introduction to Relational Database Management Systems Unit 1 Milestone-sobtell.com

Click link for Answers All Correct

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 playlist table?

•

insert into playlist (name,playlist_id) values (40, 'Top 40'), (41, 'Top 40'), (42, 'Top 40')

•

insert into playlist (playlist_id, name) values (40, 'Top 40'), (41, 'Top 40'), (42, 'Top 40')

•

insert into playlist (playlist_id, name) values (40, 'Top 40'), (40, 'Top 41'), (40, 'Top 42')

•

insert into playlist (playlist_id, name) values (40, 'Top 40') (41, 'Top 40') (42, 'Top 40')

https://postgres.sophia.org/

Using the WHERE clause, filter the employee table to include individuals that live in Edmonton.

Identify the title of the individual(s) listed.

•

IT Sales Manager

•

Sales Support Agent

•

Andrew

•

General Manager

https://postgres.sophia.org/

Which of the following is a correctly formatted INSERT statement that will successfully add a record into the genre table?

•

insert into genre (genre_id, name) values (30, 'Funk' )

•

insert into genre (genre_id, name) values ('Funk',35)

•

insert into genre (genre_id, name) values (40, Funk)

•

insert into genre (genre_id, name) values (19, 'Funk' )

https://postgres.sophia.org/

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

•

ALTER TABLE customer DROP home_address;

•

ALTER TABLE customer DROP address VARCHAR (100);

•

ALTER TABLE customer DROP address;

•

ALTER TABLE customer ADD address;

https://postgres.sophia.org/

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

•

UPDATE customer

IN postal_code = '33433'

WHERE customer_id = 22

•

UPDATE customer

SET postal_code

WHERE customer_id = '33433'

•

UPDATE customer

WHERE customer_id = 22

SET postal_code = '33433'

•

UPDATE customer

SET postal_code = '33433'

WHERE customer_id = 22

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 invoice;

DROP TABLE invoice_line;

DROP TABLE customer;

•

DROP TABLE playlist_track;

DROP TABLE invoice_line;

DROP TABLE invoice;

•

DROP TABLE playlist_track

DROP TABLE playlist;

DROP TABLE genre;

•

DROP TABLE genre;

DROP TABLE album;

DROP TABLE artist;

https://postgres.sophia.org/

Consider the following new table:

CREATE TABLE surveylist(

surveylist_id SERIAL PRIMARY KEY,

email VARCHAR NOT NULL,

phone VARCHAR

);

Given this new table, which INSERT statement would query from the customer table to insert the email and phone of all customers in the right columns?

•

INSERT INTO surveylist (surveylist_id, email, phone)

SELECT email, phone FROM customer;

•

INSERT INTO surveylist (email, phone)

SELECT phone, email FROM customer;

•

INSERT INTO surveylist (email, phone)

SELECT email, phone FROM customer;

•

INSERT INTO surveylist

SELECT email, phone FROM customer;

https://postgres.sophia.org/

Using the SELECT statement, query the track table to find the average length of a track that has the genre_id equal to 5, rounded to the nearest millisecond.

•

394489

•

134644

•

134643.5

•

134643

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 called 'student'.

•

ALTER TABLE student ADD UNIQUE student_number CONSTRAINT (student_number_unique);

•

ALTER TABLE student ADD CONSTRAINT student_number_unique UNIQUE (student_number);

•

ALTER TABLE student ADD CONSTRAINT student_number UNIQUE (student_number_unique);

•

ALTER TABLE student ADD CONSTRAINT student_number UNIQUE (student_number);

https://postgres.sophia.org/

Using the WHERE and HAVING clauses, filter the track table for the tracks having the milliseconds greater than 100,000, grouped by the album_id having the number of tracks greater than 20. Provide the list of album_id's and the count of tracks that fit these criteria.

Which of the following queries would provide the correct results?

•

SELECT album_id, count(track_id)

FROM track

WHERE count(track_id) > 20

GROUP BY album_id

HAVING milliseconds > 100000

•

SELECT album_id, sum(track_id)

FROM track

WHERE milliseconds > 100000

GROUP BY album_id

HAVING sum(track_id) > 20

•

SELECT album_id, count(track_id)

FROM track

WHERE milliseconds > 100000

GROUP BY album_id

HAVING count(track_id) > 20

•

SELECT album_id, count(track_id)

FROM track

GROUP BY album_id

HAVING count(track_id) > 20

WHERE milliseconds > 100000

https://postgres.sophia.org/

Which of these SELECT statements would successfully display exactly five columns of data from the customer table?

•

SELECT customer_id, city_id, state_id, phone_id, company_id

FROM customer;

•

SELECT customer_id + city + state + phone + company

FROM customer

•

SELECT *

FROM customer;

•

SELECT customer_id, city, state, phone, company

FROM customer;

https://postgres.sophia.org/

Using the SELECT statement, query the invoice table to find the average total cost for all orders placed between 2010-01-01 and 2011-01-01.

•

5.85542168674698

•

5.66265060240963

•

5.7063106796116505

https://postgres.sophia.org/

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?

•

https://postgres.sophia.org/

Using the SELECT statement, query the invoice table to find the maximum invoice_date where the billing_country is equal to Canada.

•

2009-01-01

•

2013-12-22

•

2013-12-21

•

2013-12-06

https://postgres.sophia.org/

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')

•

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

•

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

•

CHECK (date_of_birth > 1850-01-01)

https://postgres.sophia.org/

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.

•

Out of Exile

•

Minha Historia

•

Big Ones

•

Coda

https://postgres.sophia.org/

Consider the following table:

CREATE TABLE video(

video_id SERIAL PRIMARY KEY,

video_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 video (video_id, video_name) values (1, 'home video - first day of school' )

•

insert into video (video_name) values ('home video - first day of school' )

•

insert into video (video_name) values (home video - first day of school)

•

insert into video (video_id, video_name) values (nextval, 'home video - first day of school')

https://postgres.sophia.org/

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

•

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 a year starting with 19 in the title.

Identify the 4th album ID.

•

196

•

104

•

123

•

146

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;

https://postgres.sophia.org/

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

•

1284.03

•

3552.27

•

0.99

•

2396.94

https://postgres.sophia.org/

Which of these constraints uses multiple fields as a primary key?

•

COMPOSITE KEY

•

CHECK

•

FOREIGN KEY

•

UNIQUE

https://postgres.sophia.org/

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

•

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

•

To apply conditions to filter the dataset

•

To retrieve zero or more rows from one or more database columns

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.

•

Murray

•

Woodstock Discos

•

Martins

•

null

https://postgres.sophia.org/

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 create an error?

•

ALTER TABLE customer ALTER COLUMN postal_code TYPE VARCHAR (100);

•

ALTER TABLE customer ALTER COLUMN state TYPE VARCHAR (100);

•

ALTER TABLE customer ALTER COLUMN city TYPE VARCHAR (100);

•

ALTER TABLE customer ALTER COLUMN state VARCHAR (100);

https://postgres.sophia.org/

The following CREATE TABLE statement creates a table called 'users' that consists of the user_id as the primary key, the username, and the password.

CREATE TABLE users(

user_id int PRIMARY KEY,

username VARCHAR 50,

password VARCHAR (50)

);

Identify the line of code that would generate an error in this CREATE TABLE statement.

•

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 min(birth_date) FROM employee;

•

SELECT max(birth_date) FROM employee;

•

SELECT count(birth_date) FROM employee;

•

SELECT max birth_date FROM employee;

https://postgres.sophia.org/

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.

•

https://postgres.sophia.org/

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.

•

Australia

•

Canada

•

Austria

•

USA

https://postgres.sophia.org/

Using the WHERE clause, filter the invoice table to find the invoices that were dated after March 1st, 2009.

Identify the first customer ID of the invoice.

•

https://postgres.sophia.org/

USING the SELECT statement, query the track table ordered by the track_id. Set the LIMIT to 5 and OFFSET to 18.

What is the name of the last row returned?

•

Problem Child

•

Love In An Elevator

•

Whole Lotta Rosie

•

Walk on Water

https://postgres.sophia.org/

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 work without errors?

•

ALTER TABLE employee ALTER employee_id TYPE VARCHAR (100);

•

ALTER TABLE employee ALTER COLUMN email TYPE int;

•

ALTER TABLE employee ALTER COLUMN email TYPE TEXT;

•

ALTER TABLE customer ALTER COLUMN city TYPE int;

https://postgres.sophia.org/

Which of the following would set the company of the customers that live in the city of Prague to Humor Inc.?

•

UPDATE customer

SET company = 'Humor Inc.'

WHERE city = 'Prague'

•

UPDATE customer

SET company = Humor Inc.

WHERE city = 'Prague'

•

UPDATE customer

SET company = 'Humor Inc.'

AND city = 'Prague'

•

UPDATE customer

WHERE city = 'Prague'

SET company = 'Humor Inc.'

https://postgres.sophia.org/

The following CREATE TABLE statement creates a table called 'artist' that consists of the artist_id as the primary key that is auto-incremented, the first_name, and the last_name.

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.

•

https://postgres.sophia.org/

Using the AND or OR statement, filter the employee table for employees who have a title starting with Sales and an address containing Ave.

Identify the last name of the 3rd record.

•

Margaret

•

Peacock

•

Steve

•

Johnson

https://postgres.sophia.org/

Using the GROUP BY and HAVING clauses, filter the customer table by country.

How many countries have less than 5 customers?

•

https://postgres.sophia.org/

Which of the following would set the track name to "A New Road" for the track with a track_id equal to 5?

•

UPDATE track

set name = A New Road

where track_id = 5

•

UPDATE track

set name in 'A New Road'

where track_id = 5

•

UPDATE track

set name = 'A New Road'

where track_id = 5

•

UPDATE track

set name = 'A New Road'

where album_id = 5

https://postgres.sophia.org/

Consider the following new table:

CREATE TABLE locationlist(

locationlist_id SERIAL PRIMARY KEY,

city VARCHAR NOT NULL,

country VARCHAR NOT NULL,

postal_code VARCHAR

);

Given this new table, which INSERT statement would query from the customer table to insert the city, country, and postal_code of all customers in the right columns?

•

INSERT INTO locationlist (city, country, postal_code)

SELECT city, country, postal_code FROM customer;

•

INSERT INTO locationlist (postal_code, country, city)

SELECT city, country, postal_code FROM customer;

•

INSERT INTO locationlist

SELECT city, country, postal_code FROM customer;

•

INSERT INTO locationlist (locationlist_id, city, country, postal_code)

SELECT city, country, postal_code FROM customer;

https://postgres.sophia.org/

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 8?

•

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

•

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

https://postgres.sophia.org/

Using the GROUP BY and HAVING clauses, filter the customer table by country.

How many countries have less than 2 customers?

•

https://postgres.sophia.org/

The following CREATE TABLE statement creates a table called 'album' that consists of the album_id as the primary key, the title, and the artist_id.

CREATE TABLE album(

album_id int PRIMARY KEY

title VARCHAR (160),

artist_id int

);

Identify the missing item that would generate an error in this CREATE TABLE statement.

•

Missing comma

•

Missing column name

•

Missing data type

•

Missing parentheses

https://postgres.sophia.org/

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_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' )

•

insert into artwork (artwork_name) values (Mona Lisa)

https://postgres.sophia.org/

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

•

PRIMARY KEY

•

CHECK

•

FOREIGN KEY

•

UNIQUE

https://postgres.sophia.org/

Which of the following would set the media_type_id to 1 on all tracks where the album_id is set to 3?

•

UPDATE track

set media_type_id = 1

where album_id = 3

•

UPDATE track

where album_id = 3

set media_type_id = 1

•

UPDATE track

set media_type_id = 1

where track_id = 3

•

UPDATE track

set media_type_id = 3

where album_id = 1

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.

•

https://postgres.sophia.org/

Using the ORDER BY clause, sort the customer table by the last name of the customer in descending order and identify the 14th name in the list from among the answer options.

•

Michelle

•

Sampaio

•

Goyer

•

Brooks

https://postgres.sophia.org/

Given the invoice 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 invoice ALTER COLUMN billing_state TYPE BOOLEAN;

•

ALTER TABLE customer ALTER COLUMN billing_city TYPE VARCHAR (1);

•

ALTER TABLE invoice ALTER COLUMN total TYPE VARCHAR (100);

•

ALTER TABLE invoice ALTER total TYPE VARCHAR (1);

https://postgres.sophia.org/

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

•

ALTER TABLE user DROP age int;

•

ALTER TABLE user ADD age int;

•

ALTER TABLE user DROP age;

•

ALTER TABLE user DROP age_now;

https://postgres.sophia.org/

Using the SELECT statement, query the track table to find the total cost of the tracks on album_id 10, rounded to the nearest cent.

•

13.9

•

13.86

https://postgres.sophia.org/

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 track

WHERE media_type_id = 1

HAVING count(track_id) > 10

GROUP BY genre_id

•

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, sum(track_id)

FROM track

WHERE media_type_id = 1

GROUP BY genre_id

HAVING sum(track_id) > 10

•

SELECT genre_id, count(track_id)

FROM track

WHERE count(track_id) > 10

GROUP BY genre_id

HAVING media_type_id = 1

https://postgres.sophia.org/

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 (driver_license_unique);

•

ALTER TABLE identification ADD CONSTRAINT driver_license UNIQUE (driver_license);

•

ALTER TABLE identification ADD UNIQUE driver_license CONSTRAINT (driver_license_unique);

•

ALTER TABLE identification ADD CONSTRAINT driver_license_unique UNIQUE (driver_license);

https://postgres.sophia.org/

Using the SELECT statement, query the invoice table to find the average total cost for all orders placed between 2011-01-01 and 2012-01-01.

•

5.7063106796116505

•

5.7357723577235772

•

5.8095238095238095

•

https://postgres.sophia.org/

Using the LIKE operator in the WHERE clause, filter the customer table to list the individuals that have a phone number ending with 88.

Identify the 2nd individual's country.

•

Canada

•

Toronto

•

India

•

USA

https://postgres.sophia.org/

Using the IN operator, filter the invoice table to find those with the billing city set to Paris, London, New York, or Brussels.

Identify the billing postal code of the 4th record.

•

75009

•

SW1V 3EN

•

1000

•

75002

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 is equal to 2.

•

2313

•

https://postgres.sophia.org/

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.

•

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 AND 2025-01-01)

•

CHECK (signup_date BETWEEN '2020-01-01' OR '2025-01-01')

•

CHECK (signup_date BETWEEN '2020-01-01' AND '2025-01-01')

•

CHECK (signup_date IN '2020-01-01' AND '2025-01-01')

https://postgres.sophia.org/

Given the invoice 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 invoice ALTER COLUMN billing_state TYPE VARCHAR (100);

•

ALTER TABLE invoice ALTER COLUMN billing_city TYPE VARCHAR (100);

•

ALTER TABLE invoice ALTER COLUMN invoice_id TYPE VARCHAR (50);

•

ALTER TABLE invoice ALTER COLUMN total TYPE VARCHAR (100);

https://postgres.sophia.org/

USING the SELECT statement, query the track table ordered by the bytes. Set the LIMIT to 5 and OFFSET to 5.

What is the name of the last row returned?

•

Bossa

•

Deixa Entrar

•

Snowballed

•

Evil Walks

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 genre;

DROP TABLE album;

DROP TABLE artist;

•

DROP TABLE genre;

DROP TABLE album;

DROP TABLE artist;

•

DROP TABLE customer;

DROP TABLE invoice;

DROP TABLE invoice_line;

•

DROP TABLE invoice_line;

DROP TABLE playlist_track;

DROP TABLE playlist;

https://postgres.sophia.org/

Using the WHERE clause, filter the invoice table to find the invoices dated January 15th, 2011.

Identify the 2nd customer ID of the invoices.

•

https://postgres.sophia.org/

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

•

https://postgres.sophia.org/

Which of these SELECT statements would successfully display exactly three columns of data from the employee table?

•

SELECT *

FROM employee;

•

SELECT hiredate, postalcode, employeeid

FROM employee;

•

SELECT hire_date, postal_code, employee_id

FROM employee;

•

SELECT hire_date;

postal_code;

employee_id

FROM employee;

https://postgres.sophia.org/

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

•

To apply conditions to filter the dataset

•

To retrieve rows from one or more table columns

•

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

https://postgres.sophia.org/

Which of the following is a correctly formatted INSERT statement that will insert three records into the album table?

•

insert into album (artist_id, title, album_id) values (1, 'Genesis', 450), (1, 'Self-Titled', 451), (1, 'Lyrics', 452)

•

insert into album (album_id, title, artist_id) values (1, 'Genesis', 450), (1, 'Self-Titled', 451), (1, 'Lyrics', 452)

•

insert into album (artist_id, title, album_id) values (1, 'Genesis', 450), (1, 'Self-Titled', 451), (1, 'Lyrics', 450)

•

insert into album (artist_id, title, album_id) values (1, 'Genesis', 450) (1, 'Self-Titled', 451) (1, 'Lyrics', 452)

https://postgres.sophia.org/

Which of the following statement(s) would successfully delete the invoice_line_id 5 from the invoice_line table?

•

DELETE FROM invoice_line WHERE invoice_line_id = 5;

DELETE FROM invoice WHERE invoice_line_id = 5;

•

DELETE FROM invoice_line WHERE invoice_line_id = 5;

•

DELETE FROM invoice WHERE invoice_line_id = 353;

•

DELETE FROM invoice WHERE invoice_line_id = 5;

DELETE FROM invoice_line WHERE invoiceline__id = 5;

https://postgres.sophia.org/

Using the WHERE clause, filter the employee table to include individuals that report to Andrew Adams, who has an employee_id of 1.

Identify the title of the 2nd individual listed.

•

Sales Support Agent

•

IT Manager

•

General Manager

•

Sales Manager

https://postgres.sophia.org/

Using the BETWEEN operator, filter the album table to find the albums with an artist ID between 18 and 30.

Identify the 7th album ID.

•

127

https://postgres.sophia.org/

Identify the SQL command that uses an aggregate function that could be used to find the employee in the employee table who has been at the company the longest.

•

SELECT max(hire_date) FROM employee;

•

SELECT large(hire_date) FROM employee;

•

SELECT small(hire_date) FROM employee;

•

SELECT min(hire_date) FROM employee;

https://postgres.sophia.org/

The following CREATE TABLE statement creates a table called 'album' that consists of the album_id as the primary key that is auto-incremented, the title, and the artist_id.

CREATE TABLE albums(

album_id serial PRIMARY KEY,

title serial VARCHAR,

artist_id int

);

Identify the line of code that would either generate a syntax, logical, or requirements error in this CREATE TABLE statement.

•

https://postgres.sophia.org/

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

•

1059546140

•

387360

•

319888

•

38747

https://postgres.sophia.org/

Using the AND or OR statement, filter the employee table for employees who have a title starting with Sales and an address containing Ave.

Identify the last name of the 3rd record.

•

Peacock

•

Johnson

•

Margaret

•

Steve

https://postgres.sophia.org/

Select the correctly constructed CHECK constraint to validate the phone_type column of type string, to ensure that values placed into it are either work, home, or mobile.

•

CHECK (phone_type IN ('work', 'home', 'mobile'))

•

CHECK (phone_type = 'work' OR 'home' OR 'mobile')

•

CHECK (phone_type IN 'work', 'home', 'mobile')

•

CHECK (phone_type IN (work, home, mobile))

https://postgres.sophia.org/

Which of the following would set the company of the customer with the customer_id equal to 24, to Apple Inc.?

•

UPDATE customer

SET company = 'Apple Inc.'

AND customer_id = 24

•

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

https://postgres.sophia.org/

Using the WHERE clause, filter the employee table to include individuals that report to Andrew Adams, who has an employee_id of 1.

Identify the title of the 2nd individual listed.

•

Sales Manager

•

IT Manager

•

Sales Support Agent

•

General Manager

https://postgres.sophia.org/

Using the SELECT statement, query the track table to find the average length of a track that has the genre_id equal to 5, rounded to the nearest millisecond.

•

134643

•

394489

•

134644

•

134643.5

https://postgres.sophia.org/

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

•

https://postgres.sophia.org/

Given the invoice 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 invoice ALTER COLUMN total TYPE VARCHAR (100);

•

ALTER TABLE invoice ALTER total TYPE VARCHAR (1);

•

ALTER TABLE customer ALTER COLUMN billing_city TYPE VARCHAR (1);

•

ALTER TABLE invoice ALTER COLUMN billing_state TYPE BOOLEAN;

https://postgres.sophia.org/

Which of the following queries would provide the correct results?

•

SELECT genre_id, sum(track_id)

FROM track

WHERE media_type_id = 1

GROUP BY genre_id

HAVING sum(track_id) > 10

•

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, 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

https://postgres.sophia.org/

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

•

38747

•

319888

•

1059546140

•

387360

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 genre;

DROP TABLE album;

DROP TABLE artist;

•

DROP TABLE genre;

DROP TABLE album;

DROP TABLE artist;

•

DROP TABLE customer;

DROP TABLE invoice;

DROP TABLE invoice_line;

•

DROP TABLE invoice_line;

DROP TABLE playlist_track;

DROP TABLE playlist;

https://postgres.sophia.org/

The following CREATE TABLE statement creates a table called 'users' that consists of the user_id as the primary key, the username, and the password.

CREATE TABLE users(

user_id int PRIMARY KEY,

username VARCHAR 50,

password VARCHAR (50)

);

Identify the line of code that would generate an error in this CREATE TABLE statement.

•

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 (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')

•

insert into artist (artist_id, name) values (550, 'Lady Gaga'), (551, 'Ed Sheeran'), (551, 'Taylor Swift')

https://postgres.sophia.org/

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

•

To retrieve zero or more rows from one or more database columns

•

To apply conditions to filter the dataset

•

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

https://postgres.sophia.org/

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 179?

•

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.

•

https://postgres.sophia.org/

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

•

DELETE FROM playlist_track WHERE playlist_id_id = 8;

•

DELETE FROM playlist_id WHERE playlist_id_id = 8;

DELETE FROM playlist_track WHERE playlist_id_id = 8;

•

DELETE from playlist_track WHERE playlist_id = 8;

DELETE from playlist WHERE playlist_id = 8;

•

DELETE FROM playlist_id WHERE playlist_id_id = 8;

https://postgres.sophia.org/

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.

•

https://postgres.sophia.org/

Consider the following table:

CREATE TABLE video(

video_id SERIAL PRIMARY KEY,

video_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 video (video_id, video_name) values (1, 'home video - first day of school' )

•

insert into video (video_name) values (home video - first day of school)

•

insert into video (video_name) values ('home video - first day of school' )

•

insert into video (video_id, video_name) values (nextval, 'home video - first day of school')

https://postgres.sophia.org/

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

•

SELECT large(hire_date) FROM employee;

•

SELECT max(hire_date) FROM employee;

•

SELECT small(hire_date) FROM employee;

•

SELECT min(hire_date) FROM employee;

https://postgres.sophia.org/

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

•

SELECT *

FROM track;

•

SELECT track_id, name, album_id, milliseconds

FROM track;

•

SELECT Track_id and name and album_id and milliseconds

FROM track;

•

SELECT track_id

name

album_id

milliseconds

FROM track;

https://postgres.sophia.org/

Using the GROUP BY and HAVING clauses, filter the customer table by country.

How many countries have less than 5 customers?

•

https://postgres.sophia.org/

Identify the correctly constructed ALTER TABLE statement to add a UNIQUE constraint to the column reference_number with the constraint name reference_number_unique on the table called 'document'.

•

ALTER TABLE document ADD CONSTRAINT reference_number_unique UNIQUE (reference_number);

•

ALTER TABLE document ADD UNIQUE reference_number CONSTRAINT (reference_number_unique);

•

ALTER TABLE document ADD CONSTRAINT reference_number UNIQUE (reference_number_unique);

•

ALTER TABLE document ADD CONSTRAINT reference_number UNIQUE (reference_number);

https://postgres.sophia.org/

The following CREATE TABLE statement creates a table called 'album' that consists of the album_id as the primary key that is auto-incremented, the title, and the artist_id.

CREATE TABLE albums(

album_id serial PRIMARY KEY,

title serial VARCHAR,

artist_id int

);

Identify the line of code that would either generate a syntax, logical, or requirements error in this CREATE TABLE statement.

•

https://postgres.sophia.org/

Using the ORDER BY clause, sort the customer table by the first name of the customer in descending order and identify the 12th name in the list from among the answer options.

•

Patrick

•

Ellie

•

Gray

•

Sullivan

https://postgres.sophia.org/

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

•

0.99

•

2396.94

•

1284.03

•

3552.27

https://postgres.sophia.org/

Using the IN operator, filter the album table to find those with an artist ID of 11, 18, 9, or 4.

Identify the title of the 1st record.

•

BackBeat Soundtrack

•

Jagged Little Pill

•

Afrociberdelia

•

Alcohol Fueled Brewtality Live! [Disc 1]

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.8021978021978022

•

5.7135278514588859

•

5.64

https://postgres.sophia.org/

Which of the following would set the company of the customers that live in the city of Prague to Humor Inc.?

•

UPDATE customer

WHERE city = 'Prague'

SET company = 'Humor Inc.'

•

UPDATE customer

SET company = Humor Inc.

WHERE city = 'Prague'

•

UPDATE customer

SET company = 'Humor Inc.'

WHERE city = 'Prague'

•

UPDATE customer

SET company = 'Humor Inc.'

AND city = 'Prague'

https://postgres.sophia.org/

Consider the following new table:

CREATE TABLE locationlist(

locationlist_id SERIAL PRIMARY KEY,

city VARCHAR NOT NULL,

country VARCHAR NOT NULL,

postal_code VARCHAR

);

Given this new table, which INSERT statement would query from the customer table to insert the city, country, and postal_code of all customers in the right columns?

•

INSERT INTO locationlist (postal_code, country, city)

SELECT city, country, postal_code FROM customer;

•

INSERT INTO locationlist (city, country, postal_code)

SELECT city, country, postal_code FROM customer;

•

INSERT INTO locationlist

SELECT city, country, postal_code FROM customer;

•

INSERT INTO locationlist (locationlist_id, city, country, postal_code)

SELECT city, country, postal_code FROM customer;

https://postgres.sophia.org/

Using the WHERE clause, filter the invoice table to find the invoices that were dated after March 1st, 2009.

Identify the first customer ID of the invoice.

•

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

•

Not The Doctor

•

Right Through You

•

Ironic

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

•

insert into playlist (playlist_id, name) values ('New Age Playlist', 35)

•

insert into playlist (playlist_id, name) values (40, New Age Playlist)

•

insert into playlist (playlist_id, name) values ('New Age Playlist' )

https://postgres.sophia.org/

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 city TYPE VARCHAR (100);

•

ALTER TABLE employee ALTER COLUMN email TYPE VARCHAR (100);

•

ALTER TABLE employee ALTER COLUMN employee_id TYPE VARCHAR (50);

•

ALTER TABLE employee ALTER COLUMN postal_code TYPE VARCHAR (100);

https://postgres.sophia.org/

Which of these constraints uses multiple fields as a primary key?

•

UNIQUE

•

COMPOSITE KEY

•

CHECK

•

FOREIGN KEY

https://postgres.sophia.org/

Using the LIKE operator in the WHERE clause, filter the customer table to list the individuals who have an email ending with gmail.com.

Identify the 4th individual's city.

•

Chicago

•

Orlando

•

Prague

•

Salt Lake City

https://postgres.sophia.org/

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

•

ALTER TABLE customer DROP home_address;

•

ALTER TABLE customer DROP address;

•

ALTER TABLE customer ADD address;

•

ALTER TABLE customer DROP address VARCHAR (100);

https://postgres.sophia.org/

Which of the following is a correctly formatted INSERT statement that will successfully add a record into the artist table?

•

insert into artist (artist_id, name) values (900, Special Artist)

•

insert into artist (artist_id, name) values ('Special Artist',900 )

•

insert into artist (artist_id, name) values (900, 'Special Artist' )

•

insert into artist (artist_id, name) values (19, 'Special Artist' )

https://postgres.sophia.org/

Using the BETWEEN operator, filter the invoice table to find the invoices dated between 2010-01-01 and 2010-01-15.

Identify the 3rd customer ID.

•

https://postgres.sophia.org/

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

•

UPDATE customer

WHERE city = 'Berlin'

SET postal_code = '10789'

•

UPDATE customer

SET postal_code

WHERE city = '10789'

•

UPDATE customer

SET postal_code = '10789'

WHERE city = 'Berlin'

•

UPDATE customer

IN postal_code = '10789'

WHERE city = 'Berlin'

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

•

14.85

•

https://postgres.sophia.org/

Using the LIKE operator in the WHERE clause, use the necessary wildcards to filter the tracks table to find the tracks that have at least three composers by checking for two / in the composer field.

Identify the 3rd track's song.

•

Please Mr. Postman

•

My Time After Awhile

•

Girassol

•

Long Tall Sally

https://postgres.sophia.org/

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 customer

WHERE email LIKE '%apple%';

•

INSERT INTO contact (user_id, phone)

SELECT phone FROM customer

WHERE email LIKE %apple%;

•

INSERT INTO contact (phone)

SELECT phone FROM customer;

•

INSERT INTO contact

SELECT phone FROM customer

WHERE email LIKE '%apple%';

https://postgres.sophia.org/

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-19

•

2009-02-01

•

2009-01-01

•

2009-01-11

https://postgres.sophia.org/

Consider the following table:

CREATE TABLE event(

event_id SERIAL PRIMARY KEY,

event_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 event (event_id, event_name) values (1, 'Homework Due' )

•

insert into event (event_id, event_name) values (nextval, 'Homework Due')

•

insert into event (event_name) values (Homework Due)

•

insert into event (event_name) values ('Homework Due' )

https://postgres.sophia.org/

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;

•

ALTER TABLE customer ALTER postal_code TYPE VARCHAR (1);

•

ALTER TABLE customer ALTER COLUMN state TYPE int;

•

ALTER TABLE customer ALTER COLUMN city VARCHAR (100);

https://postgres.sophia.org/

Using the GROUP BY and HAVING clauses, filter the customer table by country.

How many countries have more than 3 customers?

•

https://postgres.sophia.org/

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

•

0.99

•

3552.27

•

130

https://postgres.sophia.org/

Using the WHERE and HAVING clauses, filter the track table for the tracks having the genre_id set to 1 or 2, grouped by the album_id with a number of tracks greater than 15. Provide the list of album_id's and the sum of unit_price that fit these criteria.

Which of the following queries would provide the correct results?

•

SELECT album_id, sum(unit_price)

FROM track

WHERE count(track_id) > 15

GROUP BY album_id

HAVING genre_id in (1,2)

•

SELECT album_id, sum(unit_price)

FROM track

HAVING count(track_id) > 15

WHERE genre_id in (1,2)

GROUP BY album_id

•

SELECT album_id, count(unit_price)

FROM track

WHERE genre_id in (1,2)

GROUP BY album_id

HAVING sum(track_id) > 15

•

SELECT album_id, sum(unit_price)

FROM track

WHERE genre_id in (1,2)

GROUP BY album_id

HAVING count(track_id) > 15

https://postgres.sophia.org/

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

•

To apply conditions to filter the dataset

•

To retrieve zero or more rows from one or more database columns

•

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

Answers

SmartExpert

(118)

Status NEW Posted 24 Feb 2023 03:02 AM My Price 25.00

CIS----------- 11-----------1 S-----------OPH-----------IA------------STR-----------AYE-----------R I-----------ntr-----------odu-----------cti-----------on -----------to -----------Rel-----------ati-----------ona-----------l D-----------ata-----------bas-----------e M-----------ana-----------gem-----------ent----------- Sy-----------ste-----------ms -----------Uni-----------t 1----------- Mi-----------les-----------ton-----------e-s-----------obt-----------ell-----------.co-----------m -----------Cli-----------ck -----------lin-----------k f-----------or -----------Ans-----------wer-----------s A-----------ll -----------Cor-----------rec-----------t-----------

Attachments

file 1677208573-Unit 1 Milestone Introduction to Relational Database Management Systems.doc preview (18530 words )

C-----------EU@-----------ABb-----------K ----------- ----------- ----------- OA----------- jb-----------jbj----------- .j-----------hhb----------- F-----------FFF-----------FZZ-----------ZZZ-----------@ZF-----------MMM-----------sss-----------sAL-----------Fss-----------sss-----------FFM-----------sKK-----------KsF-----------MFs-----------KsK-----------KZ0-----------kMP-----------5Gw-----------4z0-----------@7z-----------AKA-----------kkA-----------F,s-----------sKs-----------sss-----------sKs-----------ss@-----------sss-----------sAs-----------sss-----------sss-----------ss -----------X ----------- DC-----------IS -----------111----------- SO-----------PHI-----------A-S-----------TRA-----------YER----------- In-----------tro-----------duc-----------tio-----------n t-----------o R-----------ela-----------tio-----------nal----------- Da-----------tab-----------ase----------- Ma-----------nag-----------eme-----------nt -----------Sys-----------tem-----------s U-----------nit----------- 1 -----------Mil-----------est-----------one------------so-----------bte-----------ll.-----------com----------- Cl-----------ick----------- li-----------nk -----------for----------- An-----------swe-----------rs -----------All----------- Co-----------rre-----------ct ----------- 1 -----------In -----------eac-----------h m-----------ile-----------sto-----------ne,----------- yo-----------u m-----------ay -----------wan-----------t o-----------r n-----------eed----------- to----------- us-----------e t-----------he -----------dat-----------aba-----------se -----------and----------- qu-----------ery----------- to-----------ol -----------to -----------ans-----------wer----------- so-----------me -----------of -----------the----------- qu-----------est-----------ion-----------s. -----------We -----------sug-----------ges-----------t y-----------ou -----------ope-----------n t-----------he -----------too-----------l i-----------n a-----------not-----------her----------- br-----------ows-----------er

Not Rated(0)

Buy Answer

Hire Dedicated Virtual Team / Business Solution for SMEs.

SmartExpert

(118)

About SmartExpert

Education

Experience

Introduction to Relational Database Management Systems Unit 1 Milestone

Answers

Attachments

Are You a Business Owner?