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:	4 Weeks Ago, 4 Days 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 2 Milestone

CIS 111 SOPHIA-STRAYER Introduction to Relational Database Management Systems Unit 2 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 queries will use a subquery to find all of the rows in the track table that has the genre_id equal to 2 and has the length of the song in milliseconds longer than the maximum track length of all songs where the genre_id = 3?

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT MAX(milliseconds)

FROM track

WHERE genre_id = 3)

AND genre_id = 2;

•

SELECT *

FROM TRACK

WHERE milliseconds >

SELECT MAX(milliseconds)

FROM track

WHERE genre_id = 3

AND genre_id = 2;

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT MAX(milliseconds)

FROM track

WHERE genre_id = 2)

AND genre_id = 3;

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT MIN(milliseconds)

FROM track

WHERE genre_id = 3)

AND genre_id = 2;

https://postgres.sophia.org/

Which of the following query does NOT correctly use aliases?

•

SELECT i.customer_id, total, last_name

FROM invoice AS i

JOIN customer AS c

USING (customer_id);

•

SELECT i.customer_id, i.total, c.last_name

FROM invoice AS i

JOIN customer AS c

USING (customer_id);

•

SELECT c.customer_id, c.total, c.last_name

FROM invoice AS i

JOIN customer AS c

USING (customer_id);

•

SELECT c.customer_id, i.total, c.last_name

FROM invoice AS i

JOIN customer AS c

USING (customer_id);

https://postgres.sophia.org/

Use the following data model for this question:

Outfit

outfit_id

name

ClothingPiece

piece_id

name

OutfitPiece

outfit_piece_id

outfit_id

piece_id

Which of the following is a situation where an OUTER JOIN could be useful?

•

To view all the clothing pieces, even if they haven't been associated with an outfit in the outfits table

•

To view clothing pieces that are assigned to multiple outfits

•

To view outfits with just one clothing piece

•

To view clothing pieces that have already been assigned to outfits

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

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 order

WHERE SUM(total) > 30;

•

SELECT *

FROM customer_order

WHERE total < 30;

•

SELECT *

FROM customer_order

WHERE total > 30;

•

SELECT *

FROM customer_order

WHERE lifetime_total > 30;

https://postgres.sophia.org/

Genre

genre_id name

1 Broadway

2 Rock

3 Classical

4 Salsa

Track

track_id name genre_id

1 Highway to Hell 2

2 Everlong 2

3 Smells Like Teen Spirit 2

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

•

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 coordinator_id in the email table to reference the coordinator_id in the coordinator table?

•

ALTER TABLE email

ADD CONSTRAINT fk_email

FOREIGN KEY coordinator (coordinator_id)

REFERENCES coordinator_id;

•

ALTER TABLE coordinator

ADD CONSTRAINT fk_email

FOREIGN KEY (coordinator_id)

REFERENCES email (coordinator_id);

•

ALTER TABLE email

ADD CONSTRAINT

FOREIGN KEY (coordinator_id)

REFERENCES coordinator (coordinator_id);

•

ALTER TABLE email

ADD CONSTRAINT fk_email

FOREIGN KEY (coordinator_id)

REFERENCES coordinator (coordinator_id);

https://postgres.sophia.org/

Given the following queries, which of these would be the most efficient?

1. SELECT *

FROM invoice

WHERE customer_id IN

(SELECT customer_id

FROM customer

WHERE country like 'U%');

2. SELECT invoice.*

FROM invoice

INNER JOIN customer

ON customer.customer_id = invoice.customer_id

WHERE country like 'U%';

•

Both would be the same as both use the same indices for the join and filter.

•

Query #1 would be more efficient as it is based on primary and foreign keys.

•

Query #2 would be more efficient as it is not using indexed columns.

•

Query #2 would be more efficient as it is based on primary and foreign keys.

Check other also

https://postgres.sophia.org/

Why is the following query NOT valid as a NATURAL JOIN?

SELECT customer.first_name, customer.last_name, employee.first_name, employee.last_name

FROM customer

NATURAL JOIN employee;

•

The tables have a shared column, but their column names are not identical, so a JOIN... ON is needed.

•

The syntax used for NATURAL JOIN is not correct.

•

The tables do not have a foreign key relationship.

•

These two tables do not share any identical columns/fields.

https://postgres.sophia.org/

Which of the following statements would create a UNION between all of the columns of invoice where the total of the invoice is greater than 10, combined with the invoices that have an invoice_date greater or equal to 2013-08-01, and combined with the billing country in the USA with the billing state in FL?

•

SELECT *

FROM invoice

WHERE total > 10

SELECT *

FROM invoice

WHERE invoice_date >= '2013-08-01'

UNION

SELECT *

FROM invoice

WHERE billing_country = 'USA' AND billing_state = 'FL';

•

SELECT *

FROM invoice

UNION

WHERE total > 10

SELECT *

FROM invoice

UNION

WHERE invoice_date >= '2013-08-01'

SELECT *

FROM invoice

WHERE billing_country = 'USA' AND billing_state = 'FL';

•

SELECT *

FROM invoice

WHERE total > 10

UNION

SELECT *

FROM invoice

WHERE invoice_date >= '2013-08-01'

UNION

SELECT *

FROM invoice

WHERE billing_country = 'USA' AND billing_state = 'FL';

•

SELECT *

FROM invoice

WHERE total > 10

SELECT *

FROM invoice

WHERE invoice_date >= '2013-08-01'

SELECT *

FROM invoice

WHERE billing_country = 'USA' AND billing_state = 'FL';

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 needs to be added to the playlist table first.

•

The playlist_track_id is not unique.

•

The playlist_id being referenced doesn't exist in the playlist table.

•

The playlist_id in the playlist_track table doesn't exist yet.

https://postgres.sophia.org/

Which results would show if the employee table LEFT JOINed the customer table?

•

Only customers that have employees associated with them and vice-versa

•

All rows from the customer table even if they don't have an employee supporting them

•

All employees, even those that aren't supporting customers

•

Only employees that have customers that they support

Which of the following statements would be a valid DROP VIEW statement for an invoice_verification table that would prevent the removal of a view if there are any objects depending on it?

•

DROP VIEW invoice_verification RESTRICT;

•

DROP VIEW IF EXISTS invoice_verification;

•

DROP VIEW CASCADE invoice_verification;

•

DROP VIEW invoice_verification;

https://postgres.sophia.org/

Which of the following statements would query the invoice_line table to sum up the totals by multiplying the unit_price with the quantity grouped by track_id?

•

SELECT track_id, SUM(quantity * unit_price) AS total

FROM invoice_line

GROUP BY track_id

ORDER BY total DESC;

•

SELECT track_id, (quantity * unit_price) AS total

FROM invoice_line

GROUP BY track_id

ORDER BY total DESC;

•

SELECT track_id, SUM(quantity * unit_price) AS total

FROM invoice_line

ORDER BY total DESC;

•

SELECT track_id, (quantity / unit_price) AS total

FROM invoice_line

GROUP BY track_id

ORDER BY total DESC;

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 in the animal table regardless of whether they have been adopted or not.

•

It represents all adopters regardless of whether they have claimed an animal in the animal table.

•

It represents each animal, with the name of their adopter if that has been specified via a Foreign Key.

•

It represents every single animal matches with every single adopter.

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_id (FK)

ingredient

ingredient_id

ingredient_name

ingredient_amount

•

recipe

recipe_id

recipe_name

ingredient_name_1

ingredient_amount_1

ingredient_name_2

ingredient_amount_2

•

recipe

recipe_id

recipe_name

ingredient

ingredient_id

recipe_id (FK)

ingredient_name

ingredient_amount

•

ingredient

ingredient_id

recipe_name

ingredient_name

ingredient_amount

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 or equal to any of the values returned by the subquery.

•

Returns true if the value is less than any of the values returned by the subquery.

https://postgres.sophia.org/

Which of the following is true of foreign keys?

•

A foreign key can be linked to a NOT NULL column.

•

A foreign key may be linked to a unique column that establishes a 1 to 1 relationship.

•

A foreign key is not needed if the data type is different.

•

Foreign keys are not needed when we require referential integrity.

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

GROUP BY reports_to

HAVING COUNT(*) > 1;

•

SELECT reports_to, count(*)

FROM employee

GROUP BY reports_to

HAVING > 1;

•

SELECT reports_to

FROM employee

HAVING COUNT(*) > 1;

Find Duplicate Rows

Report an issue with this question

https://postgres.sophia.org/

Which of the following statements will show the following result set?

•

SELECT track_id, name

FROM track

JOIN playlist_track

USING (track.name = playlist.name);

•

SELECT track_id, name

FROM track

JOIN playlist_track

WHERE track_id != NULL;

•

SELECT track_id, name

FROM track

JOIN playlist_track

USING (track_id);

•

SELECT track_id, name

FROM track, playlist_track;

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 mediatype.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 media_type.media_type.id = track.media_type.id;

https://postgres.sophia.org/

Which result set requires a JOIN?

•

Showing invoice_dates with invoice totals

•

Showing invoice totals, invoice_dates, and customer_id's

•

Showing customer names with invoice_dates

•

Showing invoice_dates with customer_id's

CONCEPT

Joins

Report an issue with this question

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 album_cost

AS track

GROUP BY album_id;

•

CREATE VIEW album_cost

SELECT album_id, SUM(unit_price)

FROM track

GROUP BY album_id;

•

CREATE VIEW album_cost

SELECT album_id, SUM(unit_price)

FROM track

GROUP BY album_id;

•

CREATE VIEW album_cost

SELECT album_id, SUM(unit_price)

GROUP BY album_id;

https://postgres.sophia.org/

Given the initial tables in our example database, if we wanted to delete artists, tracks, and albums that HAVE NOT been purchased before, in what order do we need to delete data from our tables?

•

track

album

artist

•

artist

album

track

media_type

genre

•

invoice_line

invoice

track

album

artist

•

artist

album

track

https://postgres.sophia.org/

What type of situation would you need to create or replace a view?

•

The view is no longer being used.

•

Data has been imported from other databases.

•

The underlying query is not efficient and needs to be updated.

•

On a daily basis so that the data is refreshed.

https://postgres.sophia.org/

Which of the following is the valid syntax for creating a VIEW to view data from multiple tables?

•

CREATE VIEW customer_order

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;

•

CREATE VIEW customer_order

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;

•

CREATE VIEW customer_order

SELECT invoice.customer_id, first_name, last_name, SUM(total) as total

ON invoice.customer_id = customer.customer_id

GROUP BY invoice.customer_id, first_name, last_name;

•

CREATE VIEW customer order

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;

https://postgres.sophia.org/

Which of the following statements would create a UNION between all of the countries we have customers, employees, or invoices in?

•

SELECT billing_country

FROM invoice

UNION

SELECT country

FROM customer

UNION

SELECT country

FROM employee;

•

SELECT billing_country

FROM invoice

SELECT country

FROM customer

SELECT country

FROM employee

UNION;

•

SELECT country

FROM invoice

UNION

SELECT country

FROM customer

UNION

SELECT country

FROM employee;

•

SELECT billing_country

FROM invoice

SELECT country

FROM customer

SELECT country

FROM employee;

https://postgres.sophia.org/

When is a natural join possible between two tables?

•

When columns in two separate tables contain the same data

•

When the tables being joined both contain a column with the same name and data type

•

When two tables have a foreign key relationship

•

When the tables being joined have only one column each other than the primary key

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;

•

SELECT media_type_id, (bytes/milliseconds)

FROM track

GROUP BY media_type_id;

•

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;

https://postgres.sophia.org/

Which of the following statements will be able to show the following result set?

•

SELECT name, title

FROM album

JOIN track

WHERE album_id != NULL;

•

SELECT name, title

FROM album

JOIN track

USING (track_id);

•

SELECT name, title

FROM album

JOIN track

USING (album_id);

•

SELECT name, title

FROM album, track;

https://postgres.sophia.org/

Genre

genre_id name

1 Broadway

2 Rock

3 Classical

4 Salsa

Track

track_id name genre_id

1 Highway to Hell 2

2 Everlong 2

3 Smells Like Teen Spirit 2

Given the above genres and tracks, how many results will be returned for the following query?

SELECT genre.name, track.name

FROM genre

RIGHT JOIN track

USING (genre_id);

•

https://postgres.sophia.org/

Which of the following data models appropriately models the relationship of coordinators and their email addresses?

•

email_id

coordinator_name

email_type

email_address

•

Coordinator

coordinator_id

coordinator_name

email_id

coordinator_id (FK)

email_type

email_address

•

Coordinator

coordinator_id

coordinator_name

email_type_1

email_address_1

email_type_2

email_address_2

•

Coordinator

coordinator_id

coordinator_name

email_id (FK)

email_id

email_type

email_address

https://postgres.sophia.org/

Which result set requires a JOIN?

•

Showing track name with track ID

•

Showing media type name with track name

•

Showing media type ID with track name

•

Showing track ID, media type ID, and track name

https://postgres.sophia.org/

Which of the following is the valid syntax for creating a VIEW to view data from multiple tables?

•

CREATE VIEW album_artist_names

SELECT album.title, artist.name

FROM album

INNER JOIN artist

ON album.artist_id = artist.artist_id;

•

CREATE VIEW album artist names

SELECT album.title, artist.name

FROM album

INNER JOIN artist

ON album.artist_id = artist.artist_id;

•

CREATE VIEW album_artist_names

SELECT album.title, artist.name

FROM album

INNER JOIN artist

ON album.artist_id = artist.artist_id;

•

CREATE VIEW album_artist_names

SELECT album.title, artist.name

FROM album

ON album.artist_id = artist.artist_id;

https://postgres.sophia.org/

Given the initial tables in our example database, if we wanted to delete tracks, albums, and artists that HAVE been purchased before, in what order do we need to delete data from our tables?

•

invoice_line

invoice

track

album

artist

•

artist

album

track

media_type

genre

•

track

album

artist

•

artist

album

track

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 customer_id in the department table to reference the customer_id in the customer table?

•

ALTER TABLE department

ADD CONSTRAINT

FOREIGN KEY (customer_id)

REFERENCES customer (customer_id);

•

ALTER TABLE department

ADD CONSTRAINT fk_department

FOREIGN KEY (customer_id)

REFERENCES customer (customer_id);

•

ALTER TABLE customer

ADD CONSTRAINT fk_department

FOREIGN KEY (customer_id)

REFERENCES department (customer_id);

•

ALTER TABLE department

ADD CONSTRAINT fk_department

FOREIGN KEY customer (customer_id)

REFERENCES customer_id;

https://postgres.sophia.org/

Which of the following statements would be a valid DROP VIEW statement to remove the two views, which will also display an error if either view doesn't exist?

•

DROP VIEW album_cost, album_artist_names CASCADE;

•

DROP VIEW album_cost, album_artist_names;

•

DROP VIEW album_cost AND album_artist_names;

•

DROP VIEW IF EXISTS album_cost, album_artist_names;

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

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 a unit_price of 0.99 and has the length of the song in milliseconds that is longer than the AVG track length of all tracks in the album_id between 5 and 10?

•

SELECT *

FROM TRACK

WHERE milliseconds >

SELECT AVG(milliseconds)

FROM track

WHERE album_id BETWEEN 5 AND 10

AND unit_price = 0.99;

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT AVG(milliseconds)

FROM track

WHERE album_id BETWEEN 5 AND 10)

AND unit_price = 0.99;

•

SELECT AVG(milliseconds)

FROM TRACK

WHERE milliseconds >

(SELECT *

FROM track

WHERE album_id BETWEEN 5 AND 10)

AND unit_price = 0.99;

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT AVG(milliseconds)

FROM track

WHERE unit_price = 0.99)

AND album_id BETWEEN 5 AND 10;

https://postgres.sophia.org/

Which of the following is a correctly formatted SELECT statement to show the following result set with the invoice total, customer's customer_id, and the invoice's billing_state?

•

SELECT total, invoice.customer_id, billing_state

FROM invoice

JOIN customer

ON invoice.customer_id = customer.customer_id;

•

SELECT total, invoice.customer_id, billing_state

FROM invoice

JOIN customer

ON invoice.customer_id = consumer.customer_id;

•

SELECT invoice_total, invoice.customer_id, billing_state

FROM invoice

JOIN customer

ON invoice.invoice_id = customer.customer_id;

•

SELECT invoice_total, invoice.customer_id, billing_state

FROM invoice

JOIN customer

ON invoice.id = customer.id;

https://postgres.sophia.org/

What is incorrect regarding the following statement intended to create a VIEW?

CREATE VIEW priority_invoices AS

FROM invoice

WHERE total > 100;

•

The name "priority_invoices" is not a table that exists in the database.

•

The name of the VIEW belongs after the word "AS".

•

It's not possible to create a view that only shows a subset of the data from a table.

•

The fields that should belong in the result set are not specified.

https://postgres.sophia.org/

Which results would show if the genre table LEFT JOINed the track table?

•

All rows from the track table, even those that have NULL genre_id's

•

Only genres that don't have tracks in the track table

•

All genres, even those with no tracks in the track table

•

Only genres with tracks in the track table

https://postgres.sophia.org/

Use the following data model for this question:

Recipe

recipe_id

title

Ingredient

ingredient_id

name

Recipe_Ingredient

recipe_ingredient_id

recipe_id

ingredient_id

Which of the following is a situation where an OUTER JOIN could be useful?

•

To view only ingredients that are being utilized for particular recipes

•

To view recipes that have ingredients in the ingredients table

•

To view all ingredients in the database even if they are not being used for a particular recipe

•

To view recipes that have the word "banana" in their title

https://postgres.sophia.org/

Given the following queries, which of these would be the most efficient?

1. SELECT customer.*

FROM invoice

INNER JOIN customer

ON customer.city = invoice.billing_city

WHERE COUNTRY like '%m';

2. SELECT *

FROM customer

WHERE city IN

(SELECT billing_city

FROM invoice

WHERE COUNTRY like '%m');

•

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 index indices.

•

Both would be the same as both use the same indices for the join and filter.

Check other also

https://postgres.sophia.org/

Which of the following queries would check for duplicates of first names in the customer table and how many there are of each?

•

SELECT first_name, COUNT(*)

FROM customer

WHERE COUNT(*) > 1;

•

SELECT first_name

FROM customer

GROUP BY first_name

HAVING COUNT(*) > 0;

•

SELECT first_name

FROM customer

GROUP BY first_name

HAVING COUNT(*) > 1;

•

SELECT first_name, COUNT(*)

FROM customer

GROUP BY first_name

HAVING COUNT(*) > 1;

https://postgres.sophia.org/

What will be the result of the query based on the following criteria?

<columnname> = ALL (<subquery>)

•

Returns true if the value is equal to every value returned by the subquery.

•

Returns true if the value is not equal to any values returned by the subquery.

•

Returns true if the value is not equal to any value returned by the subquery.

•

Returns true if the value is equal to any value returned by the subquery.

https://postgres.sophia.org/

What type of situation would you need to create or replace a view?

•

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.

•

The view is no longer being used.

•

The view needs to have update, insert, and delete statements allowed.

https://postgres.sophia.org/

Outfit

ID: 1, Name: Rainy day outfit

ID: 2, Name: Important meeting outfit

ID: 3, Name: Fancy event outfit

ID: 4, Name: Beach outfit

Piece

ID: 1, Name: Gray button-up shirt

ID: 2, Name: Rainboots

ID: 3, Name: Velvet pants

Given the above data for an outfit generator, how many records would be included in the result set for the following query?

SELECT Outfit.name, Piece.name

FROM Outfit

CROSS JOIN Piece;

•

https://postgres.sophia.org/

Which of the following is true of foreign keys?

•

A foreign key could be linked to a candidate key of a table.

•

A foreign key can be linked to any foreign key.

•

A foreign key should always be linked to a primary key of another table.

•

A foreign key is not needed if the data type is different.

https://postgres.sophia.org/

Given the following view that has been created, how would you query the view to list the artist names in ascending order and album titles in desc order?

CREATE VIEW album_artist_names

SELECT album.title, artist.name

FROM album

INNER JOIN artist

ON album.artist_id = artist.artist_id;

•

SELECT *

FROM album, artist

ORDER BY name ASC, title DESC;

•

SELECT *

FROM album_artist_names

ORDER BY name DESC, title;

•

SELECT *

FROM album_artist_names

ORDER BY name, title DESC;

•

SELECT *

FROM album_artist_names

ORDER BY name DESC, title ASC;

https://postgres.sophia.org/

In trying to delete from the playlist table, what is the cause of this error?

"Query failed because of: error: update or delete on table "playlist" violates foreign key constraint "playlist_track_playlist_id_fkey" on table "playlist_track"

•

The playlist_id doesn't exist in the playlist table.

•

The track has to be deleted first before the playlist is deleted.

•

The playlist_id doesn't exist in the playlist_track table.

•

The playlist_track table has a reference to the playlist_id that is being deleted.

https://postgres.sophia.org/

Which of the following is true of foreign keys?

•

A foreign key should always be linked to a primary key of another table.

•

A foreign key can be linked to any foreign key.

•

Tables could be created first to avoid having to create foreign keys in order.

•

A foreign key is not needed if the data type is different.

https://postgres.sophia.org/

Which of the following statements would create a UNION between all of the countries we have customers, employees, or invoices in?

•

SELECT country

FROM invoice

UNION

SELECT country

FROM customer

UNION

SELECT country

FROM employee;

•

SELECT billing_country

FROM invoice

SELECT country

FROM customer

SELECT country

FROM employee;

•

SELECT billing_country

FROM invoice

SELECT country

FROM customer

SELECT country

FROM employee

UNION;

•

SELECT billing_country

FROM invoice

UNION

SELECT country

FROM customer

UNION

SELECT country

FROM employee;

https://postgres.sophia.org/

•

ALTER TABLE customer

ADD CONSTRAINT fk_department

FOREIGN KEY (customer_id)

REFERENCES department (customer_id);

•

ALTER TABLE department

ADD CONSTRAINT

FOREIGN KEY (customer_id)

REFERENCES customer (customer_id);

•

ALTER TABLE department

ADD CONSTRAINT fk_department

FOREIGN KEY (customer_id)

REFERENCES customer (customer_id);

•

ALTER TABLE department

ADD CONSTRAINT fk_department

FOREIGN KEY customer (customer_id)

REFERENCES customer_id;

https://postgres.sophia.org/

Given the initial tables in our example database, if we wanted to delete tracks, albums, and artists that HAVE been purchased before, in what order do we need to delete data from our tables?

•

track

album

artist

•

artist

album

track

•

invoice_line

invoice

track

album

artist

•

artist

album

track

media_type

genre

https://postgres.sophia.org/

Which of the following query does NOT correctly use aliases?

•

SELECT i.customer_id, i.total, c.last_name

FROM invoice AS i

JOIN customer AS c

USING (customer_id);

•

SELECT c.customer_id, i.total, c.last_name

FROM invoice AS i

JOIN customer AS c

USING (customer_id);

•

SELECT i.customer_id, total, last_name

FROM invoice AS i

JOIN customer AS c

USING (customer_id);

•

SELECT c.customer_id, c.total, c.last_name

FROM invoice AS i

JOIN customer AS c

USING (customer_id);

https://postgres.sophia.org/

Which of the following statements will show the following result set?

•

SELECT track_id, name

FROM track

JOIN playlist_track

WHERE track_id != NULL;

•

SELECT track_id, name

FROM track

JOIN playlist_track

USING (track.name = playlist.name);

•

SELECT track_id, name

FROM track

JOIN playlist_track

USING (track_id);

•

SELECT track_id, name

FROM track, playlist_track;

https://postgres.sophia.org/

Which result set requires a JOIN?

•

Showing all album titles with both artist ID and album ID

•

Showing artist_id with album title

•

Showing all album_id's with artist names

•

Showing artist names with artist_id's

https://postgres.sophia.org/

Which results would show if the artist table LEFT JOINed the album table?

•

All artists, even those with no albums in the album table

•

All rows from the album table, even those that have NULL artist_id's

•

Only artists from the artist table that have albums

•

Only rows from the artist table that do not have related albums in the album table

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

AS customer

SELECT *

WHERE country = 'USA';

•

CREATE VIEW USA_customers

SELECT *

FROM customer

WHERE country = 'USA';

•

CREATE VIEW USA_customers

SELECT *

FROM customer

WHERE country = 'USA';

•

CREATE VIEW USA_customers

AS customer

WHERE country = 'USA';

Which of the following statements would be a valid DROP VIEW statement for an invoice_verification table that would prevent the removal of a view if there are any objects depending on it?

•

DROP VIEW CASCADE invoice_verification;

•

DROP VIEW invoice_verification RESTRICT;

•

DROP VIEW IF EXISTS invoice_verification;

•

DROP VIEW invoice_verification;

https://postgres.sophia.org/

Why is the following query valid for a NATURAL join?

SELECT album.title, artist.name

FROM album

NATURAL JOIN artist;

•

The two tables share identical columns.

•

The tables do not have a foreign key relationship.

•

The tables have a shared column, but their column names are not identical which makes it a candidate for a NATURAL JOIN.

•

The query is not a valid NATURAL JOIN query and must use a JOIN... ON instead.

https://postgres.sophia.org/

Given the following view that has been created, how would you query the playlist names and track names both in descending order?

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;

•

SELECT *

FROM playlist_track_names

ORDER BY track_name, playlist_name;

•

SELECT *

FROM playlist_track_names

ORDER BY playlist_name, track_name DESC;

•

SELECT *

FROM playlist_track_names

ORDER BY playlist_name DESC, track_name DESC;

•

SELECT *

FROM playlist_track_names

ORDER BY playlist_name ASC, track_name ASC;

https://postgres.sophia.org/

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT MAX(milliseconds)

FROM track

WHERE genre_id = 2)

AND genre_id = 3;

•

SELECT *

FROM TRACK

WHERE milliseconds >

SELECT MAX(milliseconds)

FROM track

WHERE genre_id = 3

AND genre_id = 2;

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT MIN(milliseconds)

FROM track

WHERE genre_id = 3)

AND genre_id = 2;

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT MAX(milliseconds)

FROM track

WHERE genre_id = 3)

AND genre_id = 2;

https://postgres.sophia.org/

Use the following data model for this question:

Outfit

outfit_id

name

ClothingPiece

piece_id

name

OutfitPiece

outfit_piece_id

outfit_id

piece_id

Which of the following is a situation where an OUTER JOIN could be useful?

•

To view outfits with just one clothing piece

•

To view all the clothing pieces, even if they haven't been associated with an outfit in the outfits table

•

To view clothing pieces that are assigned to multiple outfits

•

To view clothing pieces that have already been assigned to outfits

https://postgres.sophia.org/

Which of the following queries would check for duplicates of a track's composer in the track table and how many there are of each?

•

SELECT composer, COUNT(*)

FROM track

GROUP BY composer

HAVING COUNT(*) > 1;

•

SELECT composer, COUNT(*)

FROM track

HAVING COUNT(*) > 1;

•

SELECT composer

FROM track

GROUP BY composer

HAVING COUNT(*) > 1;

•

SELECT track_id, COUNT(*)

FROM track

GROUP BY track_id

HAVING COUNT(*) > 1;

https://postgres.sophia.org/

Given the following queries, which of these would be the most efficient?

1. SELECT *

FROM invoice

WHERE customer_id IN

(SELECT customer_id

FROM customer

WHERE country like 'U%');

2. SELECT invoice.*

FROM invoice

INNER JOIN customer

ON customer.customer_id = invoice.customer_id

WHERE country like 'U%';

•

Both would be the same as both use the same indices for the join and filter.

•

Query #1 would be more efficient as it is based on primary and foreign keys.

•

Query #2 would be more efficient as it is not using indexed columns.

•

Query #2 would be more efficient as it is based on primary and foreign keys.

Check other also

https://postgres.sophia.org/

Which of the following is the valid syntax for creating a VIEW to view data from multiple tables?

•

CREATE VIEW album_artist_names

SELECT album.title, artist.name

FROM album

ON album.artist_id = artist.artist_id;

•

CREATE VIEW album_artist_names

SELECT album.title, artist.name

FROM album

INNER JOIN artist

ON album.artist_id = artist.artist_id;

•

CREATE VIEW album artist names

SELECT album.title, artist.name

FROM album

INNER JOIN artist

ON album.artist_id = artist.artist_id;

•

CREATE VIEW album_artist_names

SELECT album.title, artist.name

FROM album

INNER JOIN artist

ON album.artist_id = artist.artist_id;

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 needs to be added to the playlist table first.

•

The playlist_id being referenced doesn't exist in the playlist table.

•

The playlist_track_id is not unique.

•

The playlist_id in the playlist_track table doesn't exist yet.

https://postgres.sophia.org/

Which of the following data models appropriately models the relationship of companies and their phone numbers?

•

Company

company_id

company_name

phone_id (FK)

Phone

phone_id

phone_type

phone_number

•

Company

company_id

company_name

Phone

phone_id

company_id (FK)

phone_type

phone_number

•

Phone

phone_id

company_name

phone_type

phone_number

•

Company

company_id

company_name

phone_type_1

phone_number_1

phone_type_2

phone_number_2

https://postgres.sophia.org/

Genre

genre_id name

1 Broadway

2 Rock

3 Classical

4 Salsa

Track

track_id name genre_id

1 Highway to Hell 2

2 Everlong 2

3 Smells Like Teen Spirit 2

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

•

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 or equal to any of the values 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 the smallest value returned by the subquery.

•

Returns true if the value is less than the smallest value returned by the subquery.

https://postgres.sophia.org/

Which of the following statements would query the invoice_line table to sum up the totals by multiplying the unit_price with the quantity grouped by track_id?

•

SELECT track_id, (quantity * unit_price) AS total

FROM invoice_line

GROUP BY track_id

ORDER BY total DESC;

•

SELECT track_id, SUM(quantity * unit_price) AS total

FROM invoice_line

ORDER BY total DESC;

•

SELECT track_id, SUM(quantity * unit_price) AS total

FROM invoice_line

GROUP BY track_id

ORDER BY total DESC;

•

SELECT track_id, (quantity / unit_price) AS total

FROM invoice_line

GROUP BY track_id

ORDER BY total DESC;

https://postgres.sophia.org/

What type of situation would you need to create or replace a view?

•

On a daily basis so that the data is refreshed.

•

The view needs to have update, insert, and delete statements allowed.

•

The view is no longer being used.

•

A view has already been created with the same name but needs to be changed.

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 all adopters regardless of whether they have claimed an animal in the animal table.

•

It represents each animal, with the name of their adopter if that has been specified via a Foreign Key.

•

It represents every single Animal in the animal table regardless of whether they have been adopted or not.

•

It represents every single animal matches with every single adopter.

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.track_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 mediatype.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;

https://postgres.sophia.org/

Which of the following statements will be able to show the following result set?

•

SELECT invoice_id, billing_state, state

FROM invoice, customer;

•

SELECT invoice_id, billing_state, state

FROM inuoice

JOIN consumer

USING (customer_id);

•

SELECT invoice_id, billing_state, state

FROM invoice

WHERE customer_id != NULL;

•

SELECT invoice_id, billing_state, state

FROM invoice

JOIN customer

USING (customer_id);

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

SELECT city

FROM customer

SELECT city

FROM employee

UNION;

•

SELECT city

FROM invoice

UNION

SELECT city

FROM customer

UNION

SELECT city

FROM employee;

•

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;

https://postgres.sophia.org/

Which of the following is a correctly formatted SELECT statement to show the following result set with the track's name, the track's millisecond, and the playlist_track's playlist_id?

•

SELECT name, milliseconds, playlist_id

FROM track

JOIN playlist_track

ON track_id;

•

SELECT name, milliseconds, playlist_id

FROM track

JOIN playlist_track

ON track.trackid = playlisttrack.trackid;

•

SELECT name, milliseconds, playlist_id

FROM track

JOIN playlist_track

ON track.playlist_id = playlist_track.playlist_id;

•

SELECT name, milliseconds, playlist_id

FROM track

JOIN playlist_track

ON track.track_id = playlist_track.track_id;

https://postgres.sophia.org/

Which of the following statements would calculate the max bytes per millisecond grouped by the album_id in the track table?

•

SELECT album_id, MAX(bytes/milliseconds)

FROM track

GROUP BY album_id;

•

SELECT album_id, MAX(milliseconds/bytes)

FROM track

GROUP BY album_id;

•

SELECT album_id, MIN(bytes/milliseconds) FROM track

GROUP BY album_id;

•

SELECT album_id, SUM(bytes/milliseconds)

FROM track

GROUP BY album_id;

https://postgres.sophia.org/

What type of situation would you need to create or replace a view?

•

On a daily basis so that the data is refreshed.

•

The view's underlying data has to be changed to other tables.

•

Data has been imported from other databases.

•

The data in the tables have changed.

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 donor

ADD CONSTRAINT

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

https://postgres.sophia.org/

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT AVG(milliseconds)

FROM track

WHERE unit_price = 0.99)

AND album_id BETWEEN 5 AND 10;

•

SELECT AVG(milliseconds)

FROM TRACK

WHERE milliseconds >

(SELECT *

FROM track

WHERE album_id BETWEEN 5 AND 10)

AND unit_price = 0.99;

•

SELECT *

FROM TRACK

WHERE milliseconds >

SELECT AVG(milliseconds)

FROM track

WHERE album_id BETWEEN 5 AND 10

AND unit_price = 0.99;

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT AVG(milliseconds)

FROM track

WHERE album_id BETWEEN 5 AND 10)

AND unit_price = 0.99;

https://postgres.sophia.org/

Genre

genre_id name

1 Broadway

2 Rock

3 Classical

4 Salsa

Track

track_id name genre_id

1 Highway to Hell 2

2 Everlong 2

3 Smells Like Teen Spirit 2

Given the above genres and tracks, how many results will be returned for the following query?

SELECT genre.name, track.name

FROM genre

RIGHT JOIN track

USING (genre_id);

•

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 2----------- 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 1677209101-Unit 2 Milestone Introduction to Relational Database Management Systems.doc preview (13931 words )

_F----------- -----------A 0-----------bjb-----------j .-----------hh(----------- N-----------NNN-----------Nbb-----------bb4----------- b -----------@@@-----------4Xt-----------ttA-----------CCC-----------CCC-----------LgN-----------twt-----------ttt-----------gNN-----------@4X-----------Ctz-----------N@N-----------4XA-----------tA@-----------pxG-----------m -----------n-0-----------n3N-----------0tt-----------ttt-----------ttg-----------gWt-----------ttt-----------ttt-----------ttt-----------ttt-----------ttt----------- 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----------- 2 -----------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 -----------tab----------- wh-----------ile----------- yo-----------u

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 2 Milestone

Answers

Attachments

Are You a Business Owner?